【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 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#;

坏块处理

--可通过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;

--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坏块

--跳过

alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;

--设置offline

alter 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 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\'\)\"

总结

以上是生活随笔为你收集整理的【BLOCK】Oracle 块管理常用SQL的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。