Temporary Tablespace 생성과 Default Temporary Tablespace 지정

실습 #1 현재 Temporary Tablespace 설정 확인
[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 2 10:12:04 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              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col file_name format a50
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> col property_name format a30
SQL> col property_value format a20
SQL> col description format a40
SQL> select * from database_properties where property_name like '%TEMP%';

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

SQL>



실습 #2 Temporary Tablespace 생성과 Default Temporary Tablespace 지정
SQL> create temporary tablespace tmp
  2  tempfile '/home/oracle/oradata/testdb/tmp01.dbf' size 10m
  3  autoextend on;

Tablespace created.

SQL> !ls -al /home/oracle/oradata/testdb/* | grep mp
-rw-r-----  1 oracle dba 20979712  2월  4 11:46 /home/oracle/oradata/testdb/temp01.dbf
-rw-r-----  1 oracle dba 10493952  3월  2 11:54 /home/oracle/oradata/testdb/tmp01.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
TMP                              10485760 /home/oracle/oradata/testdb/tmp01.dbf

SQL> alter database default temporary tablespace tmp;

Database altered.

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>



실습 #3 Temporary Tablespace 삭제
SQL> drop tablespace tmp;
drop tablespace tmp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> drop tablespace temp;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
TMP                              10485760 /home/oracle/oradata/testdb/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>