ora-8103 解决脚本

最新推荐文章于 2022-07-17 10:51:37 发布

原创最新推荐文章于 2022-07-17 10:51:37 发布 · 670 阅读

0 ·

CC 4.0 BY-SA版权

scripts 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个用于处理Oracle数据库中ORA-8103错误的自定义脚本。该脚本能够检查并删除数据字典中残留的对象信息，适用于已被删除但仍遗留相关信息的情况。通过判断对象类型为表分区或索引分区来执行相应的清理操作。

自己写的处理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;