臨時表空間操作總結

一、 臨時表空間理論

在9i之前，如果一個資料庫使用者沒有被指定預設臨時表空間，那麼oracle就會使用system表空間作為該使用者的臨時表空間，這是很危險的。在9i裡面，database可以被指定一個預設臨時表空間。這樣如果資料庫使用者沒有被明確指定臨時表空間，oracle 9i就會自動指定database的預設臨時表空間作為該使用者的臨時表空間。

預設臨時表空間的限制：

1.1. 預設臨時表空間必須是TEMPORARY的:
SQL> alter database default temporary tablespace EXAMPLE;
ORA-12901: default temporary tablespace must be of TEMPORARY type

1.2. 預設臨時表空間一旦被指定，將無法在改成PERMANET：
SQL> alter tablespace tmp01 permanent;
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

1.3. 在刪除預設臨時表空間必須先重新指定預設臨時表空間：
SQL> drop tablespace temp including contents and datafiles;
ORA-12906: cannot drop default temporary tablespace
SQL> create temporary tablespace tmp01 tempfile '+DATA' size 10m autoextend  off;
Tablespace created.
SQL> alter database default temporary tablespace TMP01;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.

1.4. 預設臨時表空間無法OFFLINE：
SQL> alter tablespace temp offline;
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

1.5. 使用者的臨時表空間必須是TEMPORARY的(在9i之前沒有這個限制，可以是PERMANENT)： 
SQL> alter user TEST temporary tablespace tmp01;
User altered.

1.6. 修改資料庫預設臨時表空間
SQL> alter database default temporary tablespace tmp_grp;
Database altered.

1.7. 如果刪除了使用者的臨時表空間，而這個臨時表空間又不是資料庫的預設臨時表空間（如果是資料庫的預設臨時表空間是刪不掉的），使用者的臨時表空間不會自動轉換到資料庫的預設臨時表空間上：
SQL> select tablespace_name, contents from dba_tablespaces where contents like 'TEMPORARY%';
TABLESPACE CONTENTS
---------- ---------
TEMP TEMPORARY
TMP01 TEMPORARY
SQL> select TEMPORARY_TABLESPACE from dba_users where username='TEST';
TEMPORARY_TABLESPACE
------------------------------
TMP01

SQL> drop tablespace TMP01 including contents and datafiles;
Tablespace dropped.

SQL> select TEMPORARY_TABLESPACE from dba_users where username='TEST';
TEMPORARY_TABLESPACE
------------------------------
TMP01

二、 臨時表空間實戰

2.1 查詢臨時表空間使用率
SELECT temp_used.tablespace_name, 
total - used as "Free_M", 
total as "Total_M", 
round(nvl(total - used, 0) * 100 / total, 3) "Free percent" 
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used 
FROM GV_$TEMP_SPACE_HEADER 
GROUP BY tablespace_name) temp_used, 
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total 
FROM dba_temp_files 
GROUP BY tablespace_name) temp_total 
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

2.2 臨時表空間擴容

--2.2.0檢視臨時表空間及大小
SQL> 
col FILE_NAME for a40;
col TABLESPACE_NAME for a10;
select tablespace_name,file_name,bytes/1024/1204 m from dba_temp_files;
TABLESPACE FILE_NAME M
---------- ---------------------------------------- ----------
TEMP +DATA/devdb/tempfile/temp.264.936769423 24.6644518

--2.2.1查詢當前預設臨時表空間
col PROPERTY_VALUE for a15;
col DESCRIPTION for a25;
select * from database_properties where property_name like 'DEFAULT_TEMP_%';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ --------------- -------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

--2.2.2resize臨時表空間檔案
SQL> alter database tempfile '+DATA/devdb/tempfile/temp.264.936769423' resize 30m;
Database altered.

--2.2.3檢視系統檔案大小,已經修改成功
+ASM1@rac1 /home/oracle$ export ORACLE_SID=+ASM1
+ASM1@rac1 /home/oracle$ asmcmd
ASMCMD> ls -ls +DATA/devdb/tempfile/temp.264.936769423
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
TEMPFILE UNPROT COARSE AUG 04 18:00:00 Y 8192 3841 31465472 32505856 temp.264.936769423

三、 重建臨時表空間

3.1 先建 
SQL> create temporary tablespace tmp01 tempfile '+DATA' size 2m tablespace group tmp_grp;
Tablespace created.

SQL> create temporary tablespace tmp02 tempfile '+DATA' size 2m;
Tablespace created.

SQL> alter tablespace tmp02 tablespace group tmp_grp;
Tablespace altered.

SQL> alter database default temporary tablespace tmp_grp;
Database altered.

3.2 觀察系統執行情況與告警日誌資訊，無異常就刪除舊的臨時表空間的資料檔案。