自己写的处理ora-8103 错误的脚本，对象实际已经删除了，但相关信息还是保留在数据字典了，需要修改数据字典，最后是alter system flush shared_pool;因为数据字典环
缓存是放在共享池里的。
create or replace procedure del_dictionary(p_obj in number)
Authid Current_User
as
v_file number;
v_block number;
v_cnt number :=2;
v_type number;
v_bo number;
v_count number;
begin
select count(1) into v_count from obj$ where obj#=p_obj;
--v_type=19 table partition
--v_type=20 index partition
if v_count=0 then
dbms_output.put_line('The obj# '||p_obj||' is alreay deleted!');
else
select type# into v_type from obj$ where obj#=p_obj;
if v_type=19 then
select bo#,file#,block# into v_bo,v_file,v_block from tabpart$ where obj#=p_obj;
dbms_output.put_line('file# is:'||v_file||' block# is:'||v_block);
dbms_output.put_line('The block is table partion');
delete from obj$ where obj#=p_obj;
delete from tabpart$ where obj#=p_obj;
delete from seg$ where file#=v_file and block#=v_block;
update partobj$ set partcnt =v_cnt where obj# =v_bo;
else
select bo#,file#,block# into v_bo,v_file,v_block from indpart$ where obj#=p_obj;
dbms_output.put_line('file# is:'||v_file||' block# is:'||v_block);
dbms_output.put_line('The block is index partion');
delete from obj$ where obj#=p_obj;
delete from indpart$ where obj#=p_obj;
delete from seg$ where file#=v_file and block#=v_block;
update partobj$ set partcnt =v_cnt where obj# =v_bo;
end if;
end if;
commit;
EXCEPTION
--WHEN v_raise THEN
-- RAISE_APPLICATION_ERROR(-20010, 'object_id not exists!');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20011, 'ERROR：'||p_obj||' 不存在！');
end;
转载请注明原文地址: https://ju.6miu.com/read-1308967.html
