oracle跑批日志,Oracle ORA-06512&ORA-08103对象已不存在之查询期间表上索引被删除

最近，遇到一个很奇怪的问题。一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、被操作对象上的索引被删除，操作使用到了对象上的索引