【BLOCK】Oracle 块管理常用SQL

块管理

查看坏块

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

坏块处理

1. --可通过rman 备份 修复坏块，或者填充为空块
2. blockrecover datafile 5 block 19;
4. --跳过坏块
5. BEGIN
6. DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME => 'HR',
7. OBJECT_NAME => 'EMP2',
8. OBJECT_TYPE => dbms_repair.table_object,
9. FLAGS => dbms_repair.skip_flag);
10. END;
11. /
12. --取消跳过坏块
13. 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;
5. --cts
6. create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid <
7. '<low_rid>';
8. create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '<high_rid>';
10. --检查坏块是否处于表段头,如果extent_id 等于0，表示段头
11. select file_id,block_id,blocks,extent_id from dba_extents where owner='' and segment_name='' and segment_type='TABLE' order by extent_id;
13. --非空，从索引抢救数据 Fast Full Scan 访问方式
14. select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid';
15. --有空值，从索引抢救数据 Range Scan 访问方式
16. 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>;;
19. --对象所占用的块
20. 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;
4. --设置offline
5. alter system set "_offline_rollback_segments"=() scope=spfile;

LOB坏块

1. create table corrupt_lobs (corrupt_rowid rowid, err_num number);
3. --分析坏块
4. declare
5. error_1578 exception;
6. error_1555 exception;
7. error_22922 exception;
8. pragma exception_init(error_1578,-1578);
9. pragma exception_init(error_1555,-1555);
10. pragma exception_init(error_22922,-22922);
11. n number;
12. begin
13. for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
14. begin
15. n:=dbms_lob.instr(cursor_lob.&&lob_column, hextoraw ('889911'));
16. exception
17. when error_1578 then
18. insert into corrupt_lobs values (cursor_lob.r, 1578);
19. commit;
20. when error_1555 then
21. insert into corrupt_lobs values (cursor_lob.r, 1555);
22. commit;
23. when error_22922 then
24. insert into corrupt_lobs values (cursor_lob.r, 22922);
25. commit;
26. end;
27. end loop;
28. end;
29. /
31. --查看损坏的lob信息
32. select * from corrupt_lobs;
34. --清空损坏的lob行
35. update EMP
36. set EMP_XML = empty_blob()
37. where rowid in (select corrupted_rowid
38. from corrupt_lobs);
39. commit;
41. --导出
42. expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP
43. query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"