【BLOCK】Oracle 塊管理常用SQL
--檢查資料檔案是否正常dbv file=F:\oracle\product\10.2.0\oradata\movo\BLOCK.DBF blocksize=8192--rman驗證validate datafile 1; --or validate database; 可以並行--檢視壞塊select * from v$database_block_corruption;--檢視壞塊物件select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=4 and 35 between block_id and block_id+blocks-1;--or 具體資訊,檢查哪個物件set pagesize 2000set linesize 280SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest(e.block_id, c.block#) corr_start_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) - greatest(e.block_id, c.block#) + 1 blocks_corrupted , corruption_type description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#UNIONSELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted , corruption_type||' Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1UNIONSELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest(f.block_id, c.block#) corr_start_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted , 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#order by file#, corr_start_block#;
壞塊處理
--可透過rman 備份 修復壞塊,或者填充為空塊blockrecover datafile 5 block 19;--跳過壞塊BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME => 'HR', OBJECT_NAME => 'EMP2', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag);END;/--取消跳過壞塊execute dbms_repair.skip_corrupt_block(username,tablename,flags=>dbms_repair.noskip_flag);
rowid掃描方法
--定位壞塊select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>,0) low_rid from dual;select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>+1,0) low_rid from dual;--ctscreate table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid < '<low_rid>';create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '<high_rid>';--檢查壞塊是否處於表段頭,如果extent_id 等於0,表示段頭select file_id,block_id,blocks,extent_id from dba_extents where owner='' and segment_name='' and segment_type='TABLE' order by extent_id;--非空,從索引搶救資料 Fast Full Scan 訪問方式select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid';--有空值,從索引搶救資料 Range Scan 訪問方式select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid' and index_col1 >= <min_col1_value>;;--物件所佔用的塊 select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from BOOTSTRAP$;
UNDO壞塊
--跳過alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;--設定offlinealter system set "_offline_rollback_segments"=() scope=spfile;
LOB壞塊
create table corrupt_lobs (corrupt_rowid rowid, err_num number);--分析壞塊declare error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); n number;begin for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop begin n:=dbms_lob.instr(cursor_lob.&&lob_column, hextoraw ('889911')); exception when error_1578 then insert into corrupt_lobs values (cursor_lob.r, 1578); commit; when error_1555 then insert into corrupt_lobs values (cursor_lob.r, 1555); commit; when error_22922 then insert into corrupt_lobs values (cursor_lob.r, 22922); commit; end; end loop;end;/--檢視損壞的lob資訊select * from corrupt_lobs;--清空損壞的lob行update EMPset EMP_XML = empty_blob()where rowid in (select corrupted_rowidfrom corrupt_lobs);commit;--匯出expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70014851/viewspace-2867401/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Block Cleanouts 塊清除OracleBloC
- oracle壞塊Block CorruptionsOracleBloC
- Oracle資料塊blockOracleBloC
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- Oracle (block clean out) oracle的塊清除OracleBloC
- oracle block cleanout塊清除_延遲塊清除OracleBloC
- oracle的塊 db_block_sizeOracleBloC
- oracle corrupt block壞塊處理OracleBloC
- oracle block資料塊itl小記OracleBloC
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- Block Change Tracking (Oracle 塊修改跟蹤)BloCOracle
- oracle 塊延遲清除(delayed block cleanout) 理解OracleBloC
- oracle block資料塊結構之itcOracleBloC
- oracle block資料塊結構續(一)OracleBloC
- zt_oracle block資料塊精講OracleBloC
- Oracle 常用SQLOracleSQL
- ORACLE DBA常用SQL指令碼工具->管理篇(zt)OracleSQL指令碼
- ORACLE DBA常用SQL指令碼工具->管理篇(1) (轉)OracleSQL指令碼
- IOS Block 塊用法iOSBloC
- ORACLE 常用的SQLOracleSQL
- Oracle 塊修改跟蹤 (Block Change Tracking) 說明OracleBloC
- ORACLE 使用records_per_block降低表資料塊熱塊機率OracleBloC
- oracle block header_tail資料塊頭與資料塊尾OracleBloCHeaderAI
- 【Oracle-記憶體管理】-Multiple Block SizesOracle記憶體BloC
- SQL Server 管理常用的SQL和T-SQLSQLServer
- oracle常用SQL查詢OracleSQL
- oracle常用SQL語句OracleSQL
- Oracle常用監控SQLOracleSQL
- Oracle DBA常用sql分享OracleSQL
- oracle 維護常用SQLOracleSQL
- Oracle 常用SQL筆記OracleSQL筆記
- BBED (Oracle Block Brower and EDitor Tool) :資料塊修復工具OracleBloC
- oracle block_size 為非標準塊的時候OracleBloC
- Oracle PL/SQL塊簡介OracleSQL
- ORACLE之常用FAQ:SQL&PL/SQLOracleSQL
- Oracle DG運維常用SQLOracle運維SQL
- Oracle 常用SQL查詢列表OracleSQL
- 常用ORACLE查詢命令SQLOracleSQL