Environment:
Oracle EBS R12.2.5, database 12c
I have one object that is missing and I don't know what it is. There is a database job that is failing to be executed successfully due to the fact the object is missing. Bear with me
and go through my whole post. My question is at the end of this post.
I didn't write that pl/sql block nor created the database job. I came to find about it when I reviewed the database log today. So, I tried to investigate.
I found below errors in the database log:
alert_SID.log file
Mon Apr 13 07:07:56 2020
Errors in file /u01/app/oracle/product/12.1.0/dbhome_1/admin/PROD_rippedbl1/diag/rdbms/prod/PROD/trace/PROD_j000_7587.trc:
ORA-12012: error on auto execute of job 5077
ORA-00942: table or view does not exist
ORA-06512: at line 1
As per the trace file that is mentioned in the alert_SID.log file above:
Trace file /u01/app/oracle/product/12.1.0/dbhome_1/admin/PROD_rippedbl1/diag/rdbms/prod/PROD/trace/PROD_j000_15705.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: <node_name_here>
Release: 3.10.0-693.17.1.el7.x86_64
Version: #1 SMP Sun Jan 14 10:36:03 EST 2018
Machine: x86_64
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 71
Unix process pid: 15705, image: oracle@<hostname_here> (J000)
*** 2020-04-12 22:35:31.213
*** SESSION ID:(687.47834) 2020-04-12 22:35:31.213
*** CLIENT ID:() 2020-04-12 22:35:31.213
*** SERVICE NAME:(SYS$USERS) 2020-04-12 22:35:31.213
*** MODULE NAME:() 2020-04-12 22:35:31.213
*** CLIENT DRIVER:() 2020-04-12 22:35:31.213
*** ACTION NAME:() 2020-04-12 22:35:31.213
ORA-12012: error on auto execute of job 5077
ORA-00942: table or view does not exist
ORA-06512: at line 1
In trace file, I couldn't interpret the file well. However, I have the job ID that is failing which is 5077.
So I went to query from dba_jobs
select log_user,schema_user,next_date,next_sec,broken,failures,what from dba_jobs where job=5077
Here is the output I got as shown below:
As per the above screenshot, "what" column shows the plsql body and what it does. As per the error, it shows that the
job is trying to grant select on object GL_CONS_INTERFACE_1713271 to apps_query.
The error in the trace file says table or view doesn't exist. So I tried to query GL_CONS_INTERFACE_1713271
to make sure it exists
SQL> select count(*) from dba_objects where object_name='GL_CONS_INTERFACE_1713271';
COUNT(*)
----------
0
No records was returned.
That is the reason the job is failing with the following:
ORA-12012: error on auto execute of job 5077
ORA-00942: table or view does not exist
ORA-06512: at line 1
The question is: What is object "GL_CONS_INTERFACE_1713271" in Oracle EBS as I am unable to find it via Google?Is it standard or custom object?