GSS Errorred out

[Satish]

Dear Erman,
 
Gather schema statistics is errored out daily with below errors:
 
In GATHER_SCHEMA_STATS , schema_name= ALL percent=  degree = 4 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=XXAHC.XXAHC.XXAHC_GL_FIN_PERIOD***ORA-20001: XXAHC.XXAHC_GL_FIN_PERIOD is an invalid identifier***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=XXAHC.XXAHC_gl_int_check_tbl***ORA-20000: Unable to analyze TABLE "XXAHC"."XXAHC_GL_INT_CHECK_TBL", insufficient privileges or does not exist***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=XXAHC.XXAHC_gl_int_chk_tbl1***ORA-20000: Unable to analyze TABLE "XXAHC"."XXAHC_GL_INT_CHK_TBL1", insufficient privileges or does not exist*
 
 
SQL> select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='XXAHC_GL_INT_CHECK_TBL';
 
OWNER       OBJECT_NAME
APPS        XXAHC_GL_INT_CHECK_TBL
 
SQL>  select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='XXAHC_GL_INT_CHECK_TBL1';
 
no rows selected

XXAHC_GL_INT_CHECK_TBL1 is not existing to drop but log is showing this table

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'
and owner like 'APPS';  
no rows selected

SQL>select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'
and owner like 'XXAHC';  
no rows selected

 
We dropped the table XXAHC_GL_INT_CHECK_TBL and ran GSS late night and again faced the same issue...We could see the table exist again.Can you please suggest some way to fix this?


Thank you

[ErmanArslansOracleBlog]

That table is a custom table. Check your processes that create and drop those table... Don't try to gather stats of it when it is dropped.

But there is also a bug  . Read this -> Gather Schema Statistics GATHER AUTO Errors: ORA-20000 Unable To Analyze TABLE Insufficient Privileges or Does Not Exist (Doc ID 1471034.1)

[satish]

Thanks erman.
The table ia being created in apps user.I asked to few developers and they said they didnt own the table.

From our end,is there any process to check who is creating the table.(from program or oaf or any else)

Thank you

[ErmanArslansOracleBlog]

If you have audit configured, then yes you can easily find it.
If not, still you have other opportunities, Log miner is one of them. Using Log miner you can find the SQL used for the related DDL. This might give you a clue.

You can also check the DBA_SOURCe for finding the PLSQL that is programmed to create that table.. You can query dba_source with a that table name used as a keyword and try to find the PLSQL program responsible for creating it. Once you find it, you can go further and build the relationship with the functional process executing it.

Normally, this should not be the case. It is a Custom table and the answer should already be provided by your developers. Make them check their documentation for these customizations and get back to you.