【BLOCK】Oracle 块管理常用SQL

来源：这里教程网时间：2026-03-03 16:53:42 作者：

### 块管理
#### 查看坏块
``` 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 2000
set linesize 280
SELECT 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#
UNION
SELECT 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 - 1
UNION
SELECT 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#;
```
#### 坏块处理
``` sql
--可通过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扫描方法
``` sql
--定位坏块
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;
--cts
create 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坏块
``` sql
--跳过
alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;
--设置offline
alter system set "_offline_rollback_segments"=() scope=spfile;
```
#### LOB坏块
``` sql
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 EMP
set EMP_XML = empty_blob()
where rowid in (select corrupted_rowid
from 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\'$\"
```
**参考**
- 坏块参考 mos:1526911.1