1.查询用户现在所使用的临时表空间
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP2
SYS TEMP2
SYSTEM TEMP2
DBSNMP TEMP2
SYSMAN TEMP2
SCOTT TEMP2
OUTLN TEMP2
MDSYS TEMP2
ORDSYS TEMP2
CTXSYS TEMP2
ANONYMOUS TEMP2
EXFSYS TEMP2
DMSYS TEMP2
WMSYS TEMP2
XDB TEMP2
ORDPLUGINS TEMP2
SI_INFORMTN_SCHEMA TEMP2
OLAPSYS TEMP2
MDDATA TEMP2
DIP TEMP2
2.查询临时表空间所使用的数据文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP02.DBF
3.创建新的临时表空间
SQL> create temporary tablespace temp tempfile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP01.DBF' size 20m;
Tablespace created
4.切换到新创建后的临时表空间
SQL> alter database default temporary tablespace temp;
Database altered
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
SCOTT TEMP
OUTLN TEMP
MDSYS TEMP
ORDSYS TEMP
CTXSYS TEMP
ANONYMOUS TEMP
EXFSYS TEMP
DMSYS TEMP
WMSYS TEMP
XDB TEMP
ORDPLUGINS TEMP
SI_INFORMTN_SCHEMA TEMP
OLAPSYS TEMP
MDDATA TEMP
DIP TEMP
5.删除原临时表空间
SQL> drop tablespace temp2 including contents and datafiles;
Tablespace dropped
SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible
2 from dba_temp_files
3 /
FILE_NAME TABLESPACE_NAME MB AUTOEXTENSIBLE
-------------------------------------------------------------------------------- ------------------------------ ---------- --------------
E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP01.DBF TEMP 20 NO