1.查找用户分配临时表空间情况
SQL> select default_tablespace ,temporary_tablespace,username from dba_users;

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME
------------------------------ ------------------------------ ------------------------------
SYSTEM TEMP SYS
SYSTEM TEMP SYSTEM
SYSTEM TEMP DBSNMP
TOOLS TEMP TONG
ZFJ_SYSTEM TEMP CES#SYSTEM
WANG SDGOV_TEMP WANG
TOOLS TEMP RMAN
USERS TEMP HNZX_HQ
SYSTEM TEMP OUTLN
SYSTEM TEMP WMSYS

15 rows selected.

2.查找临时表空间数据文件存放位置

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/opt/oracle/data_ocfs/zfj/temp01.dbf
/opt/oracle/data_ocfs2/sdgov/sdgov_temp01.dbf
/opt/oracle/data_ocfs2/sdgov/sdgov_tmp01.dbf

3.生成新的临时表空间
SQL> create temporary tablespace tmp tempfile '/opt/oracle/data_ocfs2/sdgov/sdgov_tmp01.dbf' size 5120M;

Tablespace created.

4.给新生成的临时表空间增加更多的空间

SQL> alter tablespace tmp add tempfile '/opt/oracle/data_ocfs2/sdgov/sdgov_tmp02.dbf' size 2048m;

Tablespace altered

5.将新的临时表空间挂上去进行更换
SQL> alter database default temporary tablespace tmp;

Database altered.

6.对比更换临时表空间

SQL> select default_tablespace,temporary_tablespace,username from dba_users;

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME
------------------------------ ------------------------------ ------------------------------
SYSTEM TMP SYS
SYSTEM TMP SYSTEM
SYSTEM TMP DBSNMP
TOOLS TMP TONG
ZFJ_SYSTEM TMP CES#SYSTEM
USERS TMP LWG
WANG SDGOV_TEMP WANG
TOOLS TMP RMAN
SYSTEM TMP OUTLN
SYSTEM TMP WMSYS

15 rows selected.

7.查找视图确定旧的表空间已经无用户使用,则可以安全删除旧的表空间

SQL> select * from v$sort_usage;

no rows selected

8.删除旧有的表空间及数据文件
SQL> drop tablespace temp including contents;

在OS下用系统命令进行删除
rm /opt/oracle/data_ocfs/zfj/temp01.dbf