Oracle資料庫暫存資料表空間問題

筆者在日常系統檢查時，通過Oracle Enterprise Manager Console檢查資料庫資料表空間使用方式，發現系統資料表空間使用率100% ，而且每個使用者所對應暫存資料表空間為系統資料表空間。發現此情況後，為了不影響系統正常運行對此問題進行檢查。
1、通過sql查看資料庫暫存資料表空間、使用者對應的暫存資料表空間、系統預設暫存資料表空間
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files; autoextensible 自動擴充列
select username,default_tablespace,temporary_tablespace from dba_users where username = 'SYSTEM';
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
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、 為每個使用者建立暫存資料表空間
create temporary tablespace oastemp tempfile ‘/dev/vgoracle/rdboastemp.dbf’ size 1024M;
create temporary tablespace eletemp tempfile ‘/dev/vgoracle/rdbeletemp.dbf’ size 500M;
create temporary tablespace tcmtemp tempfile ‘/dev/vgoracle/rdbtcmtemp.dbf’ size 500M;
並且指定對應的使用者。
3、建立系統預設資料表空間
//建立一個中轉暫存資料表空間：
(1)>create temporary tablespace temp2
tempfile 'D:\oracle\oradata\test\temp2.dbf' size 512M
reuse autoextend on next 100M maxsize 2048M;
(2) >alter database default temporary tablespace temp2;
(3) >drop tablespace temp including contents and datafiles;

//重建立立一個新的暫存資料表空間：
(1)>create temporary tablespace temp
tempfile 'D:\oracle\oradata\test\temp01.dbf' size 512M
reuse autoextend on next 100M maxsize 1024M;
(2) >alter database default temporary tablespace temp;//修改使用者對應的資料表空間
(3) >drop tablespace temp2 including contents and datafiles;

對以上操作最好做下控制檔案備份方法如下：

1、將控制檔案備份為二進位檔案

SQL>alter database backup controlfile to 'i:\oracle\backup\control.bkp';

2、將控制檔案備份為文字檔(備份到oracle\base\admin\sid\udump目錄下的追蹤檔案中，將在追蹤檔案中產生一個SQL指令碼)

SQL>alter database backup controlfile to trace;

3、通過spfile產生pfile檔案備份控制檔案

SQL>create pfile='/pfile_backup.ora' from spfile='/home/oracle/product/10.2.0/db_1/dbs/spfileSID.ora';

Oracle 暫存資料表空間的管理與受損恢複

Oracle 暫存資料表空間過大問題解決 

解決ORA-14450:試圖訪問已經在使用的交易處理暫存資料表 

Oracle建立基於事務和基於會話的暫存資料表及暫存資料表建索引的實驗

Oracle 暫存資料表之暫存資料表空間組（TTG）