TEMP 테이블스페이스 생성 및 삭제

1. TEMP 파일 확인

SQL> select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- ---------------------------------------------
TEMP                             32505856 /oracle/oradata/orcl/temp01.dbf

SQL> desc database_properties
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 PROPERTY_NAME                                                                                                     NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                                                                                             VARCHAR2(4000)
 DESCRIPTION                                                                                                                VARCHAR2(4000)

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace

2.새로운 temp파일 추가 및 확인

SQL> create temporary tablespace tmp tempfile '/oracle/oradata/orcl/tmp01.dbf' size 10m autoextend on;

Tablespace created.

SQL> !ls -al /oracle/oradata/orcl/* | grep mp
-rw-r----- 1 oracle dba 104865792 Mar 26 03:48 /oracle/oradata/orcl/example01.dbf
-rw-r----- 1 oracle dba  32514048 Mar 26 01:20 /oracle/oradata/orcl/temp01.dbf
-rw-r----- 1 oracle dba  10493952 Mar 26 05:15 /oracle/oradata/orcl/tmp01.dbf

SQL> select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- ---------------------------------------------
TEMP                             32505856 /oracle/oradata/orcl/temp01.dbf
TMP                              10485760 /oracle/oradata/orcl/tmp01.dbf

3.Default temporary tablespace 변경

SQL> alter database default temporary tablespace tmp;

Database altered.

SQL> select * from database_properties where property_name like '%TEMP%'
  2  ;

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

3.기존에 temp tablespace 삭제

SQL> drop tablespace tmp;   <--새로 디폴트로 추가한 temp파일은 drop해도 에러가 나면서 삭제되지 않는다(디폴트이기 때문에...)
drop tablespace tmp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> drop tablespace temp;

Tablespace dropped.

4.변경된 temp파일 확인

SQL> select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- ---------------------------------------------
TMP                              10485760 /oracle/oradata/orcl/tmp01.dbf

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

SQL>