1、创建临时表空间temptest

SQL> create temporary tablespace temptest tempfile ‘/u19/app/oracle/oradata/ORCL/temptest01.dbf’ size 10m;

Tablespace created.
2、修改默认表空间temptest
SQL> alter database default temporary tablespace temptest;
3、查询
SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME STATUS

     1 /u19/app/oracle/oradata/ORCL/t TEMP                           ONLINE
       emp01.dbf

     4 /u19/app/oracle/oradata/ORCL/t TEMPTEST                       ONLINE
       emptest01.dbf

     5 /u19/app/oracle/oradata/ORCL/t TEMPTEST                       ONLINE
       emptest02.dbf

SQL> select PROPERTY_NAME,PROPERTY_value from database_properties where property_name like ‘%TEMP%’;

PROPERTY_NAME PROPERTY_VALUE

DEFAULT_TEMP_TABLESPACE TEMPTEST

4、删除temp表空间
SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is
blocked due to sort segments