1、创建新的临时表空间
SQL> create temporary tablespace temptbs tempfile '+DATA' size 30M ;
Tablespace created.
2、设置数据库的新默认表空间为新建临时表空间
SQL> alter database default temporary tablespace temptbs;
Database altered.
3、删除老的临时表空间
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
4、查询验证
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMPTBS Name of default temporary tablespace
TABLESPACE_NAME TABLESPACE_T SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- ------------ ------------ ---------- ------------- ------------ -------------
SYSTEM PERMANENT 220 28160 220 99.80 0
SYSAUX PERMANENT 165 21120 141 85.27 24
USERS PERMANENT 5 640 1 21.20 4
TEST PERMANENT 10 1280 1 10.60 9
UNDO1 UNDO 100 12800 2 2.25 98
UNDO2 UNDO 100 12800 2 2.25 98
TEMPTBS TEMPORARY 30 3840 .00