/*
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========
/*
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
========$$$$obj========
select file_id, block_id as start_block, (block_id + blocks - 1) as end_block, blocks
from dba_extents
where owner like nvl('&owner',owner)
and segment_name like nvl('&segment_name','doda')
order by file_id, start_block
;
========$$$$========
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
