批量處理時臨時增加回滾表空間臨時表空間檔案

redhouser發表於2011-09-28

在批量處理中,經常因undo表空間、臨時表空間不足導致操作失敗。可以臨時給相應表空間增加檔案,使用完成後再刪除來解決。下面給出相關sql。

1,undo表空間增刪檔案
1.1增加檔案
SQL> alter tablespace UNDOTBS1 add datafile '/bnetgts/undo_file4.dat' size 1G;

結果:
SQL> select file_name,file_id,bytes,status,online_status from dba_data_files where tablespace_name='UNDOTBS1';
/dev/rora_rbs  2 2144337920 AVAILABLE RECOVER
/bnetgts/undo_file4.dat 21 1073741824 AVAILABLE RECOVER

1.2刪除檔案過程
(1)建立undo表空間
SQL>create undo tablespace UNDOTBS2
 datafile 'M:\undotbs2_1.dbf' size 10M autoextend on maxsize 10000M;
(2)切換UNDO表空間為新的UNDO表空間
SQL> alter system set undo_tablespace=undotbs2 scope=both;
(3)檢查事務狀態
SELECT rs.usn,
       rs.xacts,
       rs.status,
       rs.rssize,
       rs.hwmsize
  FROM v$rollstat rs;
(4)所有原來表空間內rollback segment狀態為offline後,刪除原來表空間
SQL> drop tablespace undotbs1 including contents;

1.3刪除檔案
***危險,下列操作雖然看起來合理,但因沒有徹底刪除檔案,應用會報錯:ORA-00376: file 19 cannot be read at this time
(1)確認該檔案沒有被使用:
SQL> select file_id,count(*) from dba_rollback_segs group by file_id;
1 1

不能通過修改表空間刪除:
SQL> alter tablespace undotbs1 drop datafile '/bnetgts/undo_file4.dat';
*
ERROR at line 1:
ORA-03262: the file is non-empty

(2) offline
不能通過修改表空間offline(該命令是針對整個表空間offline,不能針對某個檔案),通過修改資料庫offline:
SQL> alter database datafile '/bnetgts/undo_file4.dat' offline;
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

在非歸檔模式下必須offline for drop。
SQL> select log_mode from v$database;
NOARCHIVELOG

offline for drop:
SQL> alter database datafile '/bnetgts/undo_file4.dat' offline for drop;

從檔案系統刪除檔案:
SQL> host rm /bnetgts/undo_file4.dat

當前狀態:
SQL> select file_name,file_id,bytes,status,online_status from dba_data_files where tablespace_name='UNDOTBS1';
/dev/rora_rbs  2 2144337920 AVAILABLE ONLINE
/bnetgts/undo_file4.dat 21 AVAILABLE RECOVER


(3)嘗試刪除:
SQL> alter tablespace undotbs1 drop datafile '/bnetgts/undo_file4.dat';
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace


1.4相關查詢
*可用空間
SQL> select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;

*並行dml操作過程回滾段使用情況
SQL> select xid,ptx_xid,round(used_ublk*8/1024),used_urec from v$transaction order by ptx_xid,used_ublk;

*大dml操作過程回滾段使用情況
SQL> select round(used_ublk*8/1024),used_urec from v$transaction where used_ublk>100;

*當前系統活動監控
SQL> select event,count(*) from v$session where wait_class<>'Idle' group by event;


2,臨時表空間增刪檔案:
2.1增加檔案
SQL> alter tablespace temp add tempfile '/bnetgts/temp_file2.dat' size 2G;

SQL> select file_name,file_id,tablespace_name,status from dba_temp_files;
/dev/rora_tmp 1 TEMP AVAILABLE
/bnetgts/temp_file2.dat 1 TEMP AVAILABLE

2.2刪除
SQL> alter tablespace temp drop tempfile '/bnetgts/temp_file2.dat';

SQL> select file_name,file_id,tablespace_name,status from dba_temp_files;
/dev/rora_tmp 1 TEMP AVAILABLE
/bnetgts/temp_file2.dat 1 TEMP AVAILABLE
會在以上檢視中存在,大小為空,狀態為available,檔案系統中也需要手工刪除。

SQL> host rm /bnetgts/temp_file2.dat

2.3徹底刪除
SQL> alter database tempfile '/bnetgts/temp_file2.dat' drop;

SQL> select file_name,file_id,tablespace_name,status from dba_temp_files;
/dev/rora_tmp 1 TEMP AVAILABLE

2.4臨時表空間相關查詢:
*檢視臨時表空間的使用情況(GV_$TEMP_SPACE_HEADER檢視必須在sys使用者下才能查詢)
GV_$TEMP_SPACE_HEADER檢視記錄了臨時表空間的使用大小與未使用的大小
dba_temp_files檢視的bytes欄位記錄的是臨時表空間的總大小
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

*查詢消耗資源比較的sql語句
Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid
 
*檢視當前臨時表空間使用大小與正在佔用臨時表空間的sql語句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-708473/,如需轉載,請註明出處,否則將追究法律責任。

相關文章