批量處理時臨時增加回滾表空間臨時表空間檔案
在批量處理中,經常因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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法!Oracle
- Oracle Temp臨時表空間處理Oracle
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- mysql臨時表,臨時表空間,ibtmp1表空間暴增原因初探MySql
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案(續)
- 刪除臨時表空間hang處理
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- 臨時表空間的建立、刪除,設定預設臨時表空間
- 臨時表空間和回滾表空間使用率查詢
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 臨時表空間的增刪改查
- 移動資料檔案、系統表空間檔案、臨時表空間檔案
- oracle臨時表空間過大的原因&&處理Oracle
- oracle臨時表空間相關Oracle
- MySQL InnoDB臨時表空間配置MySql
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- ORACLE臨時表空間的清理Oracle
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查:Oracle
- oracle臨時表空間的增刪改查Oracle
- oracle 表空間,臨時表空間使用率查詢Oracle
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle清理和重建臨時表空間Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle