Oracle ORA-06512&ORA-08103物件已不存在之查詢期間表上索引被刪除

清風艾艾發表於2020-03-08

    最近,遇到一個很奇怪的問題。一Oracle資料庫的兩個儲存過程,單獨跑不會有問題,但是同時跑其中一個儲存過程

就會遇到ORA08103報錯。

    一、問題報錯資訊如下:

報錯 ORA-08103: 物件不再存在,ORA-06512: 在 "REPD.ETL_PPACKAGE", line 7901

--根據報錯提示,報錯涉及的SQL語句是一個Insert into ...... select

 V_SQL2:= ' INSERT INTO DATA_T (
         C1   ,
         C2  ,
        C3
          )
        SELECT
          b.T1,
          b.T2,
          b.T3,
          FROM data_b;

    二、問題分析

    1、第一印象,可能是跨使用者資料訪問,表被宿主刪掉或者報錯表上發生了truncate操作。於是,使用ddl審計監控資料

庫發生的DDL操作。

--首先sys確認觸發器相關的引數
show parameter _system_trig_enabled
--如果_system_trig_enabled引數值為false,修改為true
alter system set "_system_trig_enabled"=true; 
--確認觸發器相關的引數_system_trig_enabled引數值為true
show parameter _system_trig_enabled
--SYS登陸資料庫建立審計表
-- Create table
create table AUDIT_DDL_OBJ
(
  opr_time    DATE,
  session_id  NUMBER,
  os_user     VARCHAR2(200),
  ip_address  VARCHAR2(200),
  terminal    VARCHAR2(200),
  host        VARCHAR2(200),
  user_name   VARCHAR2(30),
  ddl_type    VARCHAR2(30),
  ddl_sql     CLOB,
  object_type VARCHAR2(18),
  owner       VARCHAR2(30),
  object_name VARCHAR2(128)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
--建立DDL審計觸發器
CREATE OR REPLACE TRIGGER DDL_Audit_Trigger
AFTER drop or alter or truncate ON DATABASE
/*
||名稱:DDL事件審計觸發器
||說明:
*/
DECLARE
   Session_Id_Var   NUMBER;          /* 會話ID */
   Os_User_Var      VARCHAR2(200);   /* 終端OS使用者 */
   IP_Address_Var   VARCHAR2(200);   /* 終端IP */
   Terminal_Var     VARCHAR2(200);   /* 終端 */
   Host_Var         VARCHAR2(200);   /* 終端主機名 */
   Cut              NUMBER;          /* SQL列表長度 */
   Sql_Text         ORA_NAME_LIST_T; /* SQL_TEXT 列表 */
   L_Trace          NUMBER;          /* 迴圈執行條件 */
   DDL_Sql_Var      VARCHAR2(2000);  /* DDL語句 */
   stmt     clob := NULL;
   n        NUMBER;
BEGIN
   /* 獲取操作使用者資訊 */
   SELECT   SYS_CONTEXT('USERENV','SESSIONID'),
            SYS_CONTEXT('USERENV','OS_USER'),
            SYS_CONTEXT('USERENV','IP_ADDRESS'),
            SYS_CONTEXT('USERENV','TERMINAL'),
            SYS_CONTEXT('USERENV','HOST')
   INTO     Session_Id_Var,
            Os_User_Var,
            IP_Address_Var,
            Terminal_Var,
            Host_Var
   FROM     DUAL;
   /* 獲取DDL SQL語句 */
   /*BEGIN
    SELECT sql_text
      INTO DDL_Sql_Var
      FROM sys.v_$sql a, sys.v_$session b
     WHERE a.hash_value = b.sql_hash_value
       AND b.status = 'active'
       AND b.audsid = SYS_CONTEXT('userenv', 'sessionid');
   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;*/
   n := ora_sql_txt(sql_text);
   FOR i IN 1 .. n LOOP
    stmt := stmt || sql_text(i);
   END LOOP;
   /* 記錄登陸審計資訊 */
   INSERT INTO Audit_DDL_OBJ(
               Opr_Time,     /* 操作時間 */
               Session_Id,   /* 會話ID */
               OS_User,      /* 終端OS使用者 */
               IP_Address,   /* 終端IP地址 */
               Terminal,     /* 終端 */
               Host,         /* 終端主機名 */
               User_Name,    /* ORACLE 使用者名稱*/
               DDL_Type,     /* DDL操作型別 */
               DDL_Sql,      /* DDL語句 */
               Object_Type,  /* 操作物件型別 */
               Owner,        /* 物件擁有者 */
               Object_Name   /* 物件名稱 */
              )
       VALUES( SYSDATE,
               Session_Id_Var,
               Os_User_Var,
               IP_Address_Var,
               Terminal_Var,
               Host_Var,
               ORA_LOGIN_USER,
               ORA_SYSEVENT,
               stmt,
               ORA_DICT_OBJ_TYPE,
               ORA_DICT_OBJ_OWNER,
               ORA_DICT_OBJ_NAME);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END DDL_Audit_Trigger;
========
觸發器後期清理
drop triger DDL_Audit_Trigger;
drop table AUDIT_DDL_OBJ;
alter system set "_system_trig_enabled"=false;

    2、分析報錯的

    根據DDL審計表AUDIT_DDL_OBJ資訊,沒有發現DATA_T相關的DROP、TRUNCATE操作。

    3、經與系統負責人溝通,採用10046跟蹤執行跑批報錯的會話

1 查詢要跟蹤的使用者會話資訊
select sid,serial#,username from v$session where username='REPD';
2 開啟10046跟蹤
exec dbms_system.set_ev(sid,serial#,10046,12,'');
3 程式丟擲錯誤後結束10046跟蹤
exec dbms_system.set_ev(sid,serial#,10046,0,'');
4 獲取10046跟蹤日誌檔案,第1步中查詢到的sid號
 select p.tracefile from V$PROCESS p,v$session s where s.paddr=p.addr and sid=&sid;

    4、根據10046跟蹤檔案獲取到ORA08103報錯物件的資料檔案號和資料塊號

    5、根據10046跟蹤檔案ORA08103提示的資料檔案號和資料塊號,確定到insert into ..... select相關表的一個索引物件

    6、再次審查DDL審計觸發器,在儲存過程跑批之前,確實有該索引的drop操作

    7、至此ORA08103問題根源定位到

    導致ORA08103報錯的原因就是其中一個儲存過程在執行insert into ..... select操作過程中,另外一個儲存過程對insert 

into ... select的select部分的表上的索引執行了drop,導致select查詢異常報錯ORA08103退出。

    8、排查兩個同時執行報錯的儲存過程,其中一個儲存過程確實對審計表監控的索引執行了drop操作

三、問題處理

    經與客戶溝通後,遮蔽儲存過程中drop索引的操作,再執行儲存過程,不再有ORA08103報錯。


四、補充

    導致ORA08103報錯的可能原因有:

    1、被操作的物件確實被其他使用者刪除掉

    2、被操作的物件被執行了truncate操作

    3、系統表空間資料塊損壞導致的物件字典資訊與表所在表空間資料物件資訊不一致

    4、被操作物件發生了DDL例如加欄位操作

    5、被操作物件上的索引被刪除,操作使用到了物件上的索引




來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2679055/,如需轉載,請註明出處,否則將追究法律責任。

相關文章