'oracle'에 해당되는 글 47건

  1. 2010.02.27 Tablespace 실습 #6
  2. 2010.02.27 Tablespace 실습 #5
  3. 2010.02.27 Tablespace 실습 #4
  4. 2010.02.27 Tablespace 실습 #3
  5. 2010.02.27 Tablespace 실습 #2
  6. 2010.02.27 Tablespace 실습 #1
  7. 2010.02.27 Tablespace 관련 명령어

Tablespace 실습 #6

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 21:02

테이블스페이스의 삭제

SQL> drop tablespace insa
  2  including contents and datafiles cascade constraints;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Undo Tablespace 생성과 관리 (9i)  (0) 2010.03.02
Temporary Tablespace 생성과 Default Temporary Tablespace 지정  (0) 2010.03.02
Tablespace 실습 #5  (0) 2010.02.27
Tablespace 실습 #4  (0) 2010.02.27
Tablespace 실습 #3  (0) 2010.02.27

Tablespace 실습 #5

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 20:59

Mount 상태에서 Data file 이동

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mv /home/oracle/oradata/testdb/system01.dbf /home/oracle/oradata/system01.dbf

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/home/oracle/oradata/system01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/insa02.dbf

7 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Temporary Tablespace 생성과 Default Temporary Tablespace 지정  (0) 2010.03.02
Tablespace 실습 #6  (0) 2010.02.27
Tablespace 실습 #4  (0) 2010.02.27
Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27

Tablespace 실습 #4

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 20:48

OPEN 상태에서 Data file 이동

SQL> alter tablespace insa offline;

Tablespace altered.

SQL> !mv /home/oracle/oradata/testdb/insa02.dbf /home/oracle/oradata/

SQL> alter tablespace insa rename
  2  datafile '/home/oracle/oradata/testdb/insa02.dbf'
  3  to '/home/oracle/oradata/insa02.dbf';

Tablespace altered.

SQL> alter tablespace insa online;

Tablespace altered.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/insa02.dbf

7 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Tablespace 실습 #6  (0) 2010.02.27
Tablespace 실습 #5  (0) 2010.02.27
Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27

Tablespace 실습 #3

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 20:44

사용자용 테이블스페이스 확장

SQL> alter database datafile '/home/oracle/oradata/testdb/insa01.dbf' resize 20m;

Database altered.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf

6 rows selected.

SQL> alter tablespace insa
  2  add datafile '/home/oracle/oradata/testdb/insa02.dbf' size 20m;

Tablespace altered.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa02.dbf

7 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Tablespace 실습 #5  (0) 2010.02.27
Tablespace 실습 #4  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27
Redo log group 변경 두 번째! (3그룹 2멤버)  (0) 2010.02.24

Tablespace 실습 #2

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 20:40

사용자용 테이블스페이스 생성

SQL> create tablespace insa
  2  datafile '/home/oracle/oradata/testdb/insa01.dbf' size 10m
  3  segment space management auto;

Tablespace created.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               10485760 /home/oracle/oradata/testdb/insa01.dbf

6 rows selected.

SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME  STATUS    CONTENTS  EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
SYSTEM           ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1         ONLINE    UNDO      LOCAL      MANUAL
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
TEMP             ONLINE    TEMPORARY LOCAL      MANUAL
USERS            ONLINE    PERMANENT LOCAL      AUTO
EXAMPLE          ONLINE    PERMANENT LOCAL      AUTO
INSA             ONLINE    PERMANENT LOCAL      AUTO

7 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Tablespace 실습 #4  (0) 2010.02.27
Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27
Redo log group 변경 두 번째! (3그룹 2멤버)  (0) 2010.02.24
Redo log group 변경 (디렉토리, 사이즈)  (0) 2010.02.24

Tablespace 실습 #1

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 27. 20:37

현재 데이터베이스의 데이터 파일과 테이블스페이스 상태 조회

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 27 20:37:46 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> set line 200
SQL> col tablespace_name format a16
SQL> col file_name format a50
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME  STATUS    CONTENTS  EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
SYSTEM           ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1         ONLINE    UNDO      LOCAL      MANUAL
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
TEMP             ONLINE    TEMPORARY LOCAL      MANUAL
USERS            ONLINE    PERMANENT LOCAL      AUTO
EXAMPLE          ONLINE    PERMANENT LOCAL      AUTO

6 rows selected.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
TEMP               20971520 /home/oracle/oradata/testdb/temp01.dbf

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Redo log group 변경 두 번째! (3그룹 2멤버)  (0) 2010.02.24
Redo log group 변경 (디렉토리, 사이즈)  (0) 2010.02.24
Redo log group & member 추가/삭제  (0) 2010.02.24

Tablespace 관련 명령어

Oracle 10g/10g - Admin I
posted by I유령I 2010. 2. 27. 20:26

■ Data file 및 Tablespace 조회
SQL> select tablespace_name, bytes, file_name from dba_data_files;

SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;


■ Tablespace 생성
- 일반 Tablespace
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  segment space management auto;

SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  extent space management local;

- UNDO Tablespace
SQL> create undo tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

- TEMP Tablespace
SQL> create temporary tablespace tablespace_name
  2  tempfile 'path/data_file_name' size 크기;

- Tablespace Block 지정
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  blocksize [2K | 4K | 8K | 16K | 32K]
  4  segment space management auto;


■ Tablespace 확장
SQL> alter tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

SQL> alter database datafile 'path/data_file_name' resize 크기;


■ Tablespace 관리
SQL> alter tablespace tablespace_name offline;
SQL> alter tablespace tablespace_name online;

SQL> alter tablespace tablespace_name rename
  2  datafile 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';

SQL> alter database rename
  2  file 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';


■ Tablespace 삭제
SQL> drop tablespace tablespace_name
  2  including contents and datafiles cascade constraints;

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - Admin I' 카테고리의 다른 글

프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05
Index (인덱스)  (0) 2010.03.05
Table (테이블)  (0) 2010.03.03
Redo log group & member 관련 명령어  (0) 2010.02.24

티스토리툴바