删除临时表空间并删除对应的OS文件
view plain
SQL> drop tablespace temp2 including contents and datafiles;
Tablespace dropped
使用ALTER DATABASE更改临时文件
将临时文件offline,online
view plain
SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' ONLINE;
Database altered
SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2;
NAME STATUS
-------------------------------------------------------------------------------- -------
+DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE
+DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE
+DGROUP1/oralife/tempfile/lmtemp.271.769965767 ONLINE
SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' OFFLINE;
Database altered
SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2;
NAME STATUS
-------------------------------------------------------------------------------- -------
+DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE
+DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE
+DGROUP1/oralife/tempfile/lmtemp.271.769965767 OFFLINE
调整表空间大小
view plain
SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' RESIZE 18M;
Database altered
删除临时文件并删除它的OS文件
view plain
SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' DROP INCLUDING DATAFILES;
Database altered
SQL> SELECT t2.NAME FROM v$tempfile t2;
NAME
--------------------------------------------------------------------------------
+DGROUP1/oralife/tempfile/temp.265.762803345
+DGROUP1/oralife/tempfile/lmtemp.290.769965543
tempfile 数据文件重命名的步骤：
（1）将tempfile offline
（2）在操作系统上重命名tempfile
（3）使用alter database rename file 更新控制文件。
临时表空间满时的处理方法
添加数据文件
如果Temporary tablespace还不大，那么我们可以增加一些数据文件。SQL 语句如下：
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/ORADATA/NEWCCS/TEMP02.DBF' SIZE 100M AUTOEXTEND OFF;
一般来说，Temp tablespace 和Undo Tablespace 是不建议设置为自增长，设置自增长可能会把磁盘给撑满。
修改数据文件大小
可以将原来的数据文件改大一点，如：
SQL>ALTER DATABASE TEMPFILE 'D:/ ORADATA/NEWCCS/TEMP02.DBF' RESIZE 100M;
Temp 表空间过大的处理方法
1.查看目前Temp 表空间的信息
view plain
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DGROUP1/oralife/tempfile/temp.286.769967665
+DGROUP1/oralife/tempfile/temp2.301.769968357
+DGROUP1/oralife/tempfile/temp3.302.769968373
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
TEST2 TEMP
OCP