[오라클] TEMPORARY TABLESPACE 관련

[ temporary tablespace ]

DB 재시작시 들어 있던 모든 내용 사라지므로 절대로 일반 데이터를 저장하지 않는다. 일반적으로 정렬 작업 수행 시 PGA 공간이 부족할 때 해당 테이블스페이스를 이용한다. 
하나의 인스턴스에 여러 임시 테이블스페이스를 만들 수 있어서 각 사용자 별로 하나씩 할당해 주는 것이 좋으며 정렬 작업이 크게 일어나는 작업전에 임시 테이블스페이스를 크게 만들어주고 작업하는 것이 성능향상에 좋다.

-- 테이블스페이스 조회 

select file_id, tablespace_name, bytes/1024/1024 mb, file_name
from dba_temp_files;

 

FILE_ID        TABLESPACE_NAME                MB        FILE_NAME                                 
-------------- ------------------------------ --------- ------------------------------------------
             3 TEMP                               15900 +DATA/HIS012/TEMPFILE/temp.2426.1082276147
             4 TEMP                               16100 +DATA/HIS012/TEMPFILE/temp.2427.1082276217
             5 TEMP                               16100 +DATA/HIS012/TEMPFILE/temp.2428.1082276219
             1 TEMP                               30720 +DATA/HIS012/TEMPFILE/temp.283.1074099789 
             2 TEMP                               30720 +DATA/HIS012/TEMPFILE/temp.284.1074099789

-- default tablespace 조회 

SQL> set line 200
SQL> col property_name for a30
SQL> col property_value for a10
SQL> col description for a50
SQL> select * from database_properties
  2  where property_name like '%DEFAULT%';

 

PROPERTY_NAME                  PROPERTY_V DESCRIPTION
------------------------------ ---------- -------------------------------------
DEFAULT_TBS_TYPE               SMALLFILE  Default tablespace type
DEFAULT_EDITION                ORA$BASE   Name of the database default edition
DEFAULT_PERMANENT_TABLESPACE   USERS      Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE        TEMP       Name of default temporary tablespace