自己写的处理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;
自己写的处理ora-8103 错误的脚本,对象实际已经删除了,但相关信息还是保留在数据字典了,需要修改数据字典,最后是alter system flush shared_pool;因为数据字典环缓存是放在共享池里的。create or replace procedure del_dictionary(p_obj in number) Authid Current_User as