查看当前默认的临时表空间
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