重建控制檔案後,對臨時表空間(temporary tablespace)進行重建

重建控制檔案後,對臨時表空間(temporary tablespace)進行重建;有時日常管理中需要對臨時表空間進行維護[@more@]

查詢修改前使用者的屬性

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
RMAN TEMP
OUTLN TEMP
MDSYS TEMP
ORDSYS TEMP
CTXSYS TEMP
ANONYMOUS TEMP

查詢臨時表空間的位置

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SDRS02/datafile/o1_mf_temp_424otkpt_.tmp

SQL> create temporary tablespace tmp tempfile '/u01/app/oracle/oradata/SDRS02/datafile/TEMP01.DBF' size 10240m;

Tablespace created.

對臨時表空間進行轉換

SQL> alter database default temporary tablespace tmp;

Database altered.

刪除舊的表空間及其資料檔案

SQL> drop tablespace temp including contents;

Tablespace dropped.

對比顯示修改後的結果

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TMP
SYS TMP
SYSTEM TMP
DBSNMP TMP
SYSMAN TMP
RMAN TMP
OUTLN TMP
MDSYS TMP
ORDSYS TMP
CTXSYS TMP
ANONYMOUS TMP

新增資料檔案

SQL> alter tablespace tmp add tempfile '/u01/app/oracle/oradata/SDRS03/datafile/TEMP02.DBF' size 5120m;

Tablespace altered.