修改临时表空间

查看当前默认的临时表空间

SQL> select PROPERTY_NAME,PROPERTY_VALUE
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                                   
------------------------------ ------------------------------                   
DEFAULT_TEMP_TABLESPACE        TEMP 

创建新的临时表空间mytemp

SQL> create temporary tablespace mytemp
  2  tempfile '/u01/app/oracle/oradata/aircy/mytemp01.dbf' size 100m;

Tablespace created.

给mytemp临时表空间增加数据文件

SQL> alter tablespace mytemp
  2  add tempfile  '/u01/app/oracle/oradata/aircy/mytemp02.dbf' size 50m;

Tablespace altered.

修改默认临时表空间

SQL> alter database default temporary tablespace mytemp;

Database altered.

验证一下是否生效

SQL> select property_name,property_value
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                                   
------------------------------ ------------------------------                   
DEFAULT_TEMP_TABLESPACE        MYTEMP  

查看当前所有的临时表空间及表空间文件

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME              
-------------------------------------------------- --------------------         
/u01/app/oracle/oradata/aircy/temp01.dbf           TEMP                         
/u01/app/oracle/oradata/aircy/mytemp01.dbf         MYTEMP                       
/u01/app/oracle/oradata/aircy/mytemp02.dbf         MYTEMP

删除临时表空间temp

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

验证一下是否生效

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME              
-------------------------------------------------- --------------------         
/u01/app/oracle/oradata/aircy/mytemp01.dbf         MYTEMP                       
/u01/app/oracle/oradata/aircy/mytemp02.dbf         MYTEMP