问题概述
查询某张表时抛出错误
ORA-08103: OBJECT NO LONGER EXISTS IN AQ TABLES
问题原因
可能的原因
1.内存不一致
2.索引坏块
3.表数据坏块
检查方法
analyze table xxx validate structure;
解决方案
1.内存不一致的这种情况，可能存在在节点1能查询，在节点2查询抛出ORA-8103
最好的办法就是重启两个节点。
2.索引坏块，直接重建索引
3.数据坏块只能通过备份恢复，如果没有备份可以通过数据抽取
数据抽取
使用如下脚本进行对表rowid 方式抽取:
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_tablespace varchar2(30) := upper('&tablespace');
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 '||'tablespace '||v_tablespace||' '||' 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 '||'tablespace '||v_tablespace||' '||' 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))'||' '||'tablespace'||' '||v_tablespace;
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))'||' '||'tablespace'||' '||v_tablespace;
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复制上面脚本导服务器save_u.sql
SQL> @save_u
Enter value for owner_name: test —表的用户
Enter value for table_name: tab1 --有坏块的表
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)
Enter value for owner_name: test —表的用户
Enter value for table_name: B tab1 --有坏块的表
Saved 18800 records in tab1_SAVED. 抽出来没有问题的数据
35 bad records in tab1_BAD. 抽出来有问题的表
PL/SQL procedure successfully completed.
