Tablespace
TABLESPACE의 종류
1) SYSTEM TABLESPACE
• DATABASE 생성시 자동으로 생성되는 것이며, DATABASE 관리와 작업을 위해 반드시 필요한 TABLESPACE이다.
• Data dictionary에 대한 정보가 저장되며, stored PROCEDURE와 FUNCTION, PACKAGE, TRIGGER 등에 대한 정의도 저장된다.
• 하나의 SYSTEM UNDO SEGMENT를 반드시 포함하고 있다.
• USER DATA를 포함할 수 있지만, DATABASE의 성능을 고려하여 포함하지 않는 것이 좋다.
2) NON-SYSTEM TABLESPACE
• User data를 포함한다.
• UNDO SEGMENT, TEMPORARY SEGMENT, Application data, INDEX를 저장할 수 있다.
CREATE TABLESPACE <tablespace명>
DATAFILE 'datafile 경로와 이름' SIZE 숫자 [K ¦ M] [,file명...,,]
[MINIMUM EXTENT 숫자 [K ¦ M]]
[BLOCKSIZE 크기 [K ¦ M]]
[DEFAULT storage 절]
[PERMANENT ¦ TEMPORARY]
[ONLINE ¦ OFFLINE]
[EXTENT_MANAGEMENT 절]
[SEGMENT_MANAGEMENT 절]
[LOGGING ¦ NOLOGGING];
여기서
storage 절= STORAGE ([INITIAL 숫자 [K ¦ M]]
[NEXT 숫자 [K ¦ M]]
[MINEXTENTS 숫자]
[MAXEXTENTS {숫자 ¦ UNLIMITED}]
[PCTINCREASE 숫자]
)
$ sqlplus '/as sysdba'
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- ---------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL> create tablespace users2
2 datafile '/export/home/oracle/app/oracle/oradata/orcl/users2.dbf' size 500k,
3 '/export/home/oracle/app/oracle/oradata/orcl/users202.dbf' size 500k
4 minimum extent 10k
5 default storage (initial 40k
6 next 40k
7 maxextents 50
8 pctincrease 0);
Tablespace created.
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
USERS2 /export/home/oracle/app/oracle/oradata/orcl/users2.dbf
USERS2 /export/home/oracle/app/oracle/oradata/orcl/users202.dbf
6 rows selected.
SQL>
SQL> create tablespace exe1
2 datafile '/export/home/oracle/app/oracle/oradata/orcl/exe01.dbf' size 200k
3 extent management local
4 uniform size 10k;
Tablespace created.
SQL> select * from dba_tablespaces
2 where tablespace_name='EXE1';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
EXE1 8192 16384 16384 1 2147483645 0 16384 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
SQL>
TEMPORARY TABLESPACE는 Sorting을 위해서 사용되는 TABLESPACE로 일반적인 Object는 저장될 수 없다.
| TEMPORARY TABLESPACE | PERMANENT TABLESPACE |
| TEMPORARY SEGMENT만 포함되어 사용할 수 있으며, TEMPORARY SEGMENT의 사용이 종료된 후 할당해제(deallocate)되지 않고 재사용됨
| 일반 SEGMENT와 함께 TEMPORARY SEGMENT가 포함되어 사용할 수 있고, 사용이 종료된 후, BACKGROUND process인 SMON에 의해 할당 해제됨 |
• TABLESPACE를 생성할 때 permanent 옵션을 사용하여 PERMANENT TABLESPACE를,
TEMPORARY 옵션을 사용하여 TEMPORARY TABLESPACE를 만들 수 있다.
• TEMPORARY TABLESPACE는 Sorting을 위해 사용되고, PERMANENT object를 가질 수 없다.
• 기존의 PERMANENT TABLESPACE도 ALTER TABLESPACE 문을 사용하여 TEMPORARY TABLESPACE로 변경할 수 있다.
단, TABLESPACE 내에는 PERMANENT object가 하나라도 존재해선 않된다.
• temporary TABLESPACE를 생성하는 방법에도 일반적으로 생성하는 CREATE TABLESPACE TEMPORARY 문을 사용하는 방법외에도
Locally management temporary tablespace로 생성하는 방법이 있다.
CREAGTE TEMPORARY TABLESPACE <tablespace명>
TEMPFILE 'datafile 경로와 이름' SIZE 크기[K ¦ M]
EXTENT MANAGEMENT LOCAL
AUTOALLOCATE ¦ UNIFORM SIZE 크기 [K ¦ M]
• 성능 최적화를 위해서 UNIFORM SIZE는 SORT_AREA_SIZE의 배수로 설정한다.
• TEMPORARY TABLESPACE에 하나의 TEMPORARY SEGMENT를 생성하여 Sort 작업이 발생할 경우 할당되며, 여러 user가 공유한다.
• INSTANCE가 종료될 때 TEMPORARY TABLESPACE가 deallocate된다.
DBA_TABLESPACES 뷰를 통해 TABLESPACE의 TYPE이 PERMANENT인지 TEMPORARY인지 알 수 있다.
SQL> SELECT tablespace_name, contents FROM DBA_TABLESPACES;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL>
SQL> create temporary tablespace temp2
2 tempfile '/export/home/oracle/app/oracle/oradata/orcl/temp2.dbf' size 200k
3 extent management local uniform size 64k;
Tablespace created.
SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL> SELECT TABLESPACE_NAME, CONTENTS,ALLOCATION_TYPE FROM DBA_TABLESPACES;
TABLESPACE_NAME CONTENTS ALLOCATIO
------------------------------ --------- ---------
SYSTEM PERMANENT SYSTEM
UNDOTBS1 UNDO SYSTEM
SYSAUX PERMANENT SYSTEM
TEMP TEMPORARY UNIFORM
USERS PERMANENT SYSTEM
TEMP2 TEMPORARY UNIFORM
6 rows selected.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/export/home/oracle/app/oracle/oradata/orcl/temp01.dbf 1 TEMP 20971520 2560 AVAILABLE 1 YES 3.4360E+10 4194302 80 19922944 2432
/export/home/oracle/app/oracle/oradata/orcl/temp2.dbf 2 TEMP2 204800 25 AVAILABLE 1 NO 0 0 0 131072 16
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/export/home/oracle/app/oracle/oradata/orcl/temp01.dbf 1 TEMP 20971520 2560 AVAILABLE 1 YES 3.4360E+10 4194302 80 19922944 2432
SQL> SELECT TABLESPACE_NAME, CONTENTS,ALLOCATION_TYPE FROM DBA_TABLESPACES;
TABLESPACE_NAME CONTENTS ALLOCATIO
------------------------------ --------- ---------
SYSTEM PERMANENT SYSTEM
UNDOTBS1 UNDO SYSTEM
SYSAUX PERMANENT SYSTEM
TEMP TEMPORARY UNIFORM
USERS PERMANENT SYSTEM
SQL>
| DEFAULT TEMPORARY TABLESPACE의 생성 | |
Default temporary tablespace가 지정되지 않으면 특정 temporary tablespace를 지정하지 않은 사용자가 생성한 임시 데이터는 자동으로 SYATEM TABLESPACE에 할당한다.
SYSTEM TABLESPACE는 가장 분주한 data dictionary까지 포함하고 있기 때문에 일반 사용자의 임시 데이터까지 할당한다면, 전체적인 성능의 저하를 초래한다.
이러한 것을 방지하기 위하여 database 생성시나 또는 생성 후에 default temporary tablespace를 지정한다.
• default temporary tablespace를 database 생성시 지정하는 경우 Locally management 방식으로 지정한다.
• default temporary tablespace는 OFFLINE 상태가 될 수 없다.
• default temporary tablespace는 Permanent tablespace로 변경 될 수 없다.
• 새로운 default temporary tablespace가 생성되어 가용한 상태가 되기 전까지는 삭제될 수 없다.
• 새로운 default temporary tablespace가 가용한 상태가 되면 이전의 default temporary tablespace에 할당 되었던 사용자들은 자동으로 새로운 default temporary tablespace로 재할당 된다.
SQL> select * from database_properties
2 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------ -------------- -------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------------------- -----------------------
/export/home/oracle/app/oracle/oradata/orcl/temp01.dbf TEMP
SQL> create temporary tablespace temp3
2 tempfile '/export/home/oracle/app/oracle/oradata/orcl/temp03.dbf' SIZE 200K
3 extent management local uniform size 10k;
Tablespace created.
SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------- ------------------
/export/home/oracle/app/oracle/oradata/orcl/temp01.dbf TEMP
/export/home/oracle/app/oracle/oradata/orcl/temp03.dbf TEMP3
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;
Database altered.
SQL> select * from database_properties
2 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------ -------------- -------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP3 Name of default temporary tablespace
SQL>
1) ONLINE/OFFLINE으로 tablespace를 변경
tablespace를 활성화한다는 것은 online한다는 것을 의미하며, 이는 사용자가 접근할 수 있음을 의미하고,
offline시키면 사용자가 해당 테이블스페이스를 접근할 수 없게 된다.
online/offline의 변경은 alter tablespace...online...문을 사용하여 변경이 가능하다.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP3 ONLINE
6 rows selected.
SQL>
2) READ [ONLY ¦ WRITE]으로 tablespace를 변경
테이블스페이스가 read only는 읽기 전용이고, read write는 읽고 쓰기가 가능함을 의미한다.
【형식】
ALTER TABLESPACE <tablespace명> READ [ONLY ¦ WRITE];
• TABLESPACE가 READ ONLY로 되면 더 이상 쓰기 작업을 할 수 없다.
• Active transaction이 존재해도 READ ONLY로 변경이 가능하다.
단, 현재 작업중인 transaction은 COMMIT과 ROLLBACK만 가능하며, 이후의 작업부터 적용된다.
• READ ONLY로 전환한 경우 TABLESPACE 내의 TABLE과 INDEX는 삭제할 수 있다.
이것은 DROP 문이 TABLESPACE를 구성하는 실제 file에 영향을 주는 것이 아니라 data dictionary만을 갱신하기 때문이다.
• locally management TABLESPACE의 경우 삭제된 SEGMENT는 TEMPORARY SEGMENT로 변경되어 BITMAP이 변경되는 것을 방지한다.
• TABLESPACE가 online이어야 READ ONLY용 TABLESPACE로 변경할 수 있다.
• TABLESPACE에 활성화된 UNDO SEGMENT가 없어야 한다.
• TABLESPACE가 현재 Online Backup을 하지 않아야 한다.
【예제】
SQL> column name format a60;
SQL> select name,enabled from v$datafile;
NAME ENABLED
------------------------------------------------------------ ----------
/export/home/oracle/app/oracle/oradata/orcl/system01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/users01.dbf READ WRITE
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP3 ONLINE
6 rows selected.
SQL> ALTER TABLESPACE users READ ONLY;
Tablespace altered.
SQL> select name,enabled from v$datafile;
NAME ENABLED
------------------------------------------------------------ ----------
/export/home/oracle/app/oracle/oradata/orcl/system01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/users01.dbf READ ONLY
SQL> ALTER TABLESPACE users READ WRITE;
Tablespace altered.
SQL> select name,enabled from v$datafile;
NAME ENABLED
------------------------------------------------------------ ----------
/export/home/oracle/app/oracle/oradata/orcl/system01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/users01.dbf READ WRITE
SQL>
TABLESPACE는 필요하지 않을 경우 DROP TABLESPACE 문을 사용하여 삭제할 수 있다.
【형식】
DROP TABLESPACE <tablespace명>
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS] ];
【예제】
SQL> select name,enabled from v$datafile;
NAME ENABLED
------------------------------------------------------------ ----------
/export/home/oracle/app/oracle/oradata/orcl/system01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf READ WRITE
/export/home/oracle/app/oracle/oradata/orcl/users01.dbf READ WRITE
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP3 ONLINE
6 rows selected.
SQL> create temporary tablespace temp2
2 tempfile '/export/home/oracle/app/oracle/oradata/orcl/temp2.dbf' size 200k
3 extent management local uniform size 64k;
Tablespace created.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP2 ONLINE
TEMP3 ONLINE
7 rows selected.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP3 ONLINE
6 rows selected.
SQL>