重建temporary tablespace

1) 检查目前的defalut temp 和 datafile
SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP
SYS                            TEMP
SYSTEM                         TEMP
DBSNMP                         TEMP
SCOTT                          TEMP
MYTEST                         TEMP
SYSMAN                         TEMP
OUTLN                          TEMP
MDSYS                          TEMP
ORDSYS                         TEMP
EXFSYS                         TEMP

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
DMSYS                          TEMP
WMSYS                          TEMP
CTXSYS                         TEMP
ANONYMOUS                      TEMP
XDB                            TEMP
ORDPLUGINS                     TEMP
SI_INFORMTN_SCHEMA             TEMP
OLAPSYS                        TEMP
TSMSYS                         TEMP
BI                             TEMP
PM                             TEMP

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MDDATA                         TEMP
IX                             TEMP
SH                             TEMP
DIP                            TEMP
OE                             TEMP
HR                             TEMP

已选择28行。

SQL> select name from v$tempfile;

NAME
-----------------------------------------------------------------------------------
D:\ORACLE_DATA\TEMP01.DBF

 

2) 创建新的temp tablespace

SQL> create temporary tablespace temp_new
  2  tempfile 'G:\ora_data\temp_new.dbf' size 100M
  3  extent management local uniform size 10m;

表空间已创建。

 

3) 修改系统的default temp

SQL> alter database default temporary tablespace temp_new;

数据库已更改。

 

4) 删除原TEMP Tablespace

SQL> drop tablespace temp including contents and datafiles;

表空间已删除。

5) 删除数据文件