corrupt block

来源：互联网发布：淘宝女包店铺排名编辑：程序博客网时间：2024/06/05 20:22

/*
   This script prompts for an owner and a tbale name. It then created two new tables
   - orignal_table_name_BAD and orignal_table_name_SAVED. If either if these tables exist,
   they are dropped and then recreated. The script then gets a list of extents and blocks
   associated with the table and loops through each block, attempting to select records by
   the rowid and inserts them into the orignical_table_name_SAVED table. Errors are inserted
   into the original_table_name_BAD table.

Kerry Osborne - Enkitec
*/

prompt
Prompt WARNING: This script may issue a DROP TABLE command. Do not execute it unless you have read through it
Prompt and are comfortable you know what it does.
Prompt
accept a prompt "Ready? (hit ctl-C to quit) "
set serveroutput on format wrapped
declare

debug_flag varchar2(1) :='N';
x number := 0;
y number := 0;
v_maxrows number := 200; -- 200 is maximum number of rows per block
v_old_rowid varchar2(30);
v_owner_name varchar2(30) := upper('&owner_name');
v_table_name varchar2(30) := upper('&table_name');
v_bad_table varchar2(40);
v_saved_table varchar2(40);
v_full_table_name varchar2(30);
v_object_id number := 0;
v_sql_insert_saved varchar(256);
v_temp number;
v_last_block number := 0;

e_invalid_rowid exception;
pragma exception_init(e_invalid_rowid,-1410);
e_missing_file exception;
pragma exception_init(e_missing_file,-376);
e_obj_no_longer_exists exception;
pragma exception_init(e_obj_no_longer_exists,-8103);

cursor v_sql_get_blocks is
select file_id, block_id as start_block, (block_id + blocks - 1) as end_block
from dba_extents where upper(owner) = v_owner_name and segment_name = v_table_name
order by file_id, block_id
;

begin
v_bad_table := v_table_name||'_BAD';
v_saved_table := v_table_name||'_SAVED';

select object_id into v_object_id
from dba_objects
where owner = v_owner_name
and object_name = v_table_name
;

begin -- block to create saved table
select 1 into v_temp
from dba_tables
where owner = v_owner_name
and table_name = v_saved_table;
if debug_flag = 'Y' then dbms_output.put_line('dropping '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'drop table '||v_owner_name||'.'||v_table_name||'_SAVED';
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_SAVED as select * from '||v_owner_name||'.'||v_table_name||
' where 1=2';
exception when no_data_found then
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_SAVED as select * from '||v_owner_name||'.'||v_table_name||
' where 1=2';
end;

begin -- block to create bad table
select 1 into v_temp
from dba_tables
where owner = v_owner_name
and table_name = v_bad_table;
if debug_flag = 'Y' then dbms_output.put_line('dropping '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'drop table '||v_owner_name||'.'||v_table_name||'_BAD';
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_BAD (old_rowid varchar2(30), '||
' old_file number, old_object number, old_block number, old_row number, error_message varchar2(300))';
exception when no_data_found then
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_BAD (old_rowid varchar2(30), '||
' old_file number, old_object number, old_block number, old_row number, error_message varchar2(300))';
end;

v_full_table_name := v_owner_name||'.'||v_table_name;

v_sql_insert_saved := 'insert into '||v_owner_name||'.'||v_saved_table||' select * from '||v_full_table_name||
' where ROWID = dbms_rowid.rowid_create(1, :b_obj, :b_file, :b_blk, :v_row)';

for v_uetrec in v_sql_get_blocks loop

        for v_blk in v_uetrec.start_block..v_uetrec.end_block loop
                 if debug_flag = 'Y' then dbms_output.put_line(v_uetrec.file_id||'.'||v_blk); end if;
                 for v_row in 0..v_maxrows loop
                        v_old_rowid := v_uetrec.file_id||'.'||v_blk||'.'||v_row;
                        begin
                             execute immediate v_sql_insert_saved
                               using v_object_id, v_uetrec.file_id, v_blk, v_row;
                             if debug_flag = 'Y' then
                               dbms_output.put_line(v_old_rowid);
                             end if;
                        exception when e_missing_file then
                          if debug_flag = 'Y' then
                            DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK||' '||v_old_rowid);
                          else
                            if v_blk != v_last_block then -- only insert one bad record per block
                               execute immediate
                                  'insert into '||v_owner_name||'.'||v_bad_table||' values'||
                                  '(:b_rowid,:b_obj,:b_file,:b_blk,:b_row,:b_error)'
                                  using v_old_rowid, v_object_id, v_uetrec.file_id, v_blk, v_row, DBMS_UTILITY.FORMAT_ERROR_STACK;
                               y := y+1;
                               v_last_block := v_blk;
                            end if;
                          end if;
                        when e_invalid_rowid then null;
                        when e_obj_no_longer_exists then null;
                        when no_data_found then null;
                        when others then
                          if debug_flag = 'Y' then
                            DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK||' '||v_old_rowid);
                          else
                               execute immediate
                                  'insert into '||v_owner_name||'.'||v_bad_table||' values'||
                                  '(:b_rowid,:b_obj,:b_file,:b_blk,:b_row,:b_error)'
                                  using v_old_rowid, v_object_id, v_uetrec.file_id, v_blk, v_row, DBMS_UTILITY.FORMAT_ERROR_STACK;
                               y := y+1;
                          end if;
                        end;
                  end loop; -- end of row-loop
         commit; -- save after each block
         end loop; -- end of block-loop
end loop; -- end of uet-loop

execute immediate
'select count(*) from '||v_owner_name||'.'||v_saved_table
into x;

dbms_output.put_line(chr(0));
dbms_output.new_line;
dbms_output.put_line('Saved '||x||' records in '||v_saved_table||'.');
dbms_output.put_line(y||' bads records in '||v_bad_table||'.');

end;
/
undef a

SQL> @d:/save_rows_for_me.sql

WARNING: This script may issue a DROP TABLE command. Do not execute it unless you have read through it
and are comfortable you know what it does.

Ready? (hit ctl-C to quit)
输入 owner_name 的值: SYS
原值    8: v_owner_name varchar2(30) := upper('&owner_name');
新值    8: v_owner_name varchar2(30) := upper('SYS');
输入 table_name 的值: TB_CORRUPT
原值    9: v_table_name varchar2(30) := upper('&table_name');
新值    9: v_table_name varchar2(30) := upper('TB_CORRUPT');

Saved 7755 records in TB_CORRUPT_SAVED.
603 bads records in TB_CORRUPT_BAD.

PL/SQL 过程已成功完成。

SQL>

============

select /*+ index (messed_up_object mess_up_object_pk) */ indexed_column1, indexed_column2
from messed_up_object
where rowid in (select dbms_rowid.rowid_create(1,object,file,block,row) from bad_rows_table);

==========

========$$$$save========