C/C++教程

【BLOCK】Oracle 块管理常用SQL

本文主要是介绍【BLOCK】Oracle 块管理常用SQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

块管理

查看坏块

  1. --检查数据文件是否正常
  2. dbv file=F:\oracle\product\10.2.0\oradata\movo\BLOCK.DBF blocksize=8192
  3. --rman验证
  4. validate datafile 1; --or validate database; 可以并行
  5. --查看坏块
  6. select * from v$database_block_corruption;
  7. --查看坏块对象
  8. select tablespace_name,segment_type,owner,segment_name
  9. from dba_extents
  10. where file_id=4 and 35 between block_id and block_id+blocks-1;
  11. --or 具体信息,检查哪个对象
  12. set pagesize 2000
  13. set linesize 280
  14. SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  15. , greatest(e.block_id, c.block#) corr_start_block#
  16. , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  17. , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  18. - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  19. , corruption_type description
  20. FROM dba_extents e, v$database_block_corruption c
  21. WHERE e.file_id = c.file#
  22. AND e.block_id <= c.block# + c.blocks - 1
  23. AND e.block_id + e.blocks - 1 >= c.block#
  24. UNION
  25. SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
  26. , header_block corr_start_block#
  27. , header_block corr_end_block#
  28. , 1 blocks_corrupted
  29. , corruption_type||' Segment Header' description
  30. FROM dba_segments s, v$database_block_corruption c
  31. WHERE s.header_file = c.file#
  32. AND s.header_block between c.block# and c.block# + c.blocks - 1
  33. UNION
  34. SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  35. , greatest(f.block_id, c.block#) corr_start_block#
  36. , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  37. , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  38. - greatest(f.block_id, c.block#) + 1 blocks_corrupted
  39. , 'Free Block' description
  40. FROM dba_free_space f, v$database_block_corruption c
  41. WHERE f.file_id = c.file#
  42. AND f.block_id <= c.block# + c.blocks - 1
  43. AND f.block_id + f.blocks - 1 >= c.block#
  44. order by file#, corr_start_block#;

坏块处理

  1. --可通过rman 备份 修复坏块,或者填充为空块
  2. blockrecover datafile 5 block 19;
  3. --跳过坏块
  4. BEGIN
  5. DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME => 'HR',
  6. OBJECT_NAME => 'EMP2',
  7. OBJECT_TYPE => dbms_repair.table_object,
  8. FLAGS => dbms_repair.skip_flag);
  9. END;
  10. /
  11. --取消跳过坏块
  12. execute dbms_repair.skip_corrupt_block(username,tablename,flags=>dbms_repair.noskip_flag);

rowid扫描方法

  1. --定位坏块
  2. select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>,0) low_rid from dual;
  3. select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>+1,0) low_rid from dual;
  4. --cts
  5. create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid <
  6. '<low_rid>';
  7. create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '<high_rid>';
  8. --检查坏块是否处于表段头,如果extent_id 等于0,表示段头
  9. select file_id,block_id,blocks,extent_id from dba_extents where owner='' and segment_name='' and segment_type='TABLE' order by extent_id;
  10. --非空,从索引抢救数据 Fast Full Scan 访问方式
  11. select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid';
  12. --有空值,从索引抢救数据 Range Scan 访问方式
  13. 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>;;
  14. --对象所占用的块
  15. select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from BOOTSTRAP$;

UNDO坏块

  1. --跳过
  2. alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;
  3. --设置offline
  4. alter system set "_offline_rollback_segments"=() scope=spfile;

LOB坏块

  1. create table corrupt_lobs (corrupt_rowid rowid, err_num number);
  2. --分析坏块
  3. declare
  4. error_1578 exception;
  5. error_1555 exception;
  6. error_22922 exception;
  7. pragma exception_init(error_1578,-1578);
  8. pragma exception_init(error_1555,-1555);
  9. pragma exception_init(error_22922,-22922);
  10. n number;
  11. begin
  12. for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
  13. begin
  14. n:=dbms_lob.instr(cursor_lob.&&lob_column, hextoraw ('889911'));
  15. exception
  16. when error_1578 then
  17. insert into corrupt_lobs values (cursor_lob.r, 1578);
  18. commit;
  19. when error_1555 then
  20. insert into corrupt_lobs values (cursor_lob.r, 1555);
  21. commit;
  22. when error_22922 then
  23. insert into corrupt_lobs values (cursor_lob.r, 22922);
  24. commit;
  25. end;
  26. end loop;
  27. end;
  28. /
  29. --查看损坏的lob信息
  30. select * from corrupt_lobs;
  31. --清空损坏的lob行
  32. update EMP
  33. set EMP_XML = empty_blob()
  34. where rowid in (select corrupted_rowid
  35. from corrupt_lobs);
  36. commit;
  37. --导出
  38. expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP
  39. query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
这篇关于【BLOCK】Oracle 块管理常用SQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!