8 Replies Latest reply on Feb 27, 2020 1:47 PM by John_K

    Gather schema statistics error out in R12.1.3

    3510875

      Dear Experts,

       

      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

       

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

       

      Thank you

        • 1. Re: Gather schema statistics error out in R12.1.3
          Maaz Khan

          Hello,

           

          It seems that statistics for table - "XXAHC"."XXAHC_GL_INT_CHK_TBL1" were locked. However you have dropped table now.

          Please share output for below -

          select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'
          and owner like 'APPS';

           

           

          Also please refer below -

          Concurrent Request 'Gather Schema Statistics' Errors With ORA-20000: Unable to Analyze TABLE "APPS"."<object>", Insufficient Privileges or Does Not Exist (Doc ID 2354215.1)

           

           

          Regards,

          Maaz

          • 2. Re: Gather schema statistics error out in R12.1.3
            3510875

            Dear Mazz Khan,

             

            Please find the requested output.Even though we are dropping it,and rerean the  GSS late night,it again fails with error message and we are again able to see the table.

             

            requested output

            SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'

            and owner like 'APPS';  2

             

             

            no rows selected

             

             

            SQL>

            • 3. Re: Gather schema statistics error out in R12.1.3
              Maaz Khan

              Hello,

               

              Can you please let us know if there are any invalid objects as of now. Do you have any custom schema? can you please run same query with owner as 'custom schema'

               

              select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'

              and owner like '<custom_schemaname>';

               

               

              Regards,

              Maaz

              • 4. Re: Gather schema statistics error out in R12.1.3
                3510875

                We have invalid objects but not related to these objects

                we have customs chema

                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

                 

                 

                SQL>

                • 5. Re: Gather schema statistics error out in R12.1.3
                  Maaz Khan

                  Do we have any stats locked in any other schema?

                  select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL';

                   

                  Do we have invalid objects under APPS,'XXAHC' schemas? Also, one workaround you can try is like as follows -

                   

                  1. Execute GSS tonight and it will fail obviously.

                  2. You will see the table recreated - "XXAHC"."XXAHC_GL_INT_CHECK_TBL"

                  3. Check statistics for table "XXAHC"."XXAHC_GL_INT_CHECK_TBL" if they are locked.

                  select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL' and owner like 'XXAHC';

                  select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'

                  and owner like 'APPS';

                  4. Unlock stats -

                  exec dbms_stats.unlock_table_stats('XXAHC','XXAHC_GL_INT_CHECK_TBL');

                  5. Run GSS again without dropping table.

                   

                   

                  Regards,

                  Maaz

                  • 6. Re: Gather schema statistics error out in R12.1.3
                    3510875

                    Thank you.We will try and update here.

                     

                    Between,requested output

                     

                    select OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED from DBA_TAB_STATISTICS where STATTYPE_LOCKED like 'ALL'

                    SQL> /

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    DEV_UMS              ORASDPMENGINEPENDRCVQT                            ALL

                    SYS                  SCHEDULER$_EVENT_QTAB                             ALL

                    SYSTEM               TBLMIG_MSG_QTAB                                   ALL

                    SYS                  SYS$SERVICE_METRICS_TAB                           ALL

                    SYS                  SCHEDULER_FILEWATCHER_QT                          ALL

                    SYS                  AQ_EVENT_TABLE                                    ALL

                    SYS                  AQ_PROP_TABLE                                     ALL

                    SYSTEM               DEF$_AQERROR                                      ALL

                    SYS                  SCHEDULER$_REMDB_JOBQTAB                          ALL

                    SYS                  KUPC$DATAPUMP_QUETAB                              ALL

                    SYSTEM               DEF$_AQCALL                                       ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  AQ$_MEM_MC                                        ALL

                    SYS                  ALERT_QT                                          ALL

                    DEV_UMS              ORASDPMDRIVERDEFSNDT1                             ALL

                    DEV_UMS              ORASDPMWSRCVT1                                    ALL

                    DEV_UMS              ORASDPMENGINESNDT1                                ALL

                    DEV_UMS              ORASDPMENGINECMDT                                 ALL

                    DEV_UMS              ORASDPMAPPDEFRCVT1                                ALL

                    DEV_UMS              ORASDPMENGINERCVT1                                ALL

                    SYS                  KUPC$DATAPUMP_QUETAB_1                            ALL

                    SYS                  WRH$_FILESTATXS                24-OCT-07          ALL

                    SYS                  WRH$_SQLSTAT                   24-OCT-07          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_SYSTEM_EVENT              24-OCT-07          ALL

                    SYS                  WRH$_WAITSTAT                  24-OCT-07          ALL

                    SYS                  WRH$_LATCH                     24-OCT-07          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      24-OCT-07          ALL

                    SYS                  WRH$_DB_CACHE_ADVICE           24-OCT-07          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          24-OCT-07          ALL

                    SYS                  WRH$_SGASTAT                   24-OCT-07          ALL

                    SYS                  WRH$_SYSSTAT                   24-OCT-07          ALL

                    SYS                  WRH$_PARAMETER                 24-OCT-07          ALL

                    SYS                  WRH$_SEG_STAT                  24-OCT-07          ALL

                    SYS                  WRH$_SERVICE_STAT              24-OCT-07          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    24-OCT-07          ALL

                    SYS                  WRH$_TABLESPACE_STAT           24-OCT-07          ALL

                    SYS                  WRH$_OSSTAT                    24-OCT-07          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            24-OCT-07          ALL

                    SYS                  WRH$_SERVICE_WAIT_CLASS        24-OCT-07          ALL

                    SYS                  WRH$_FILESTATXS                11-FEB-07          ALL

                    SYS                  WRH$_SQLSTAT                   11-FEB-07          ALL

                    SYS                  WRH$_SYSTEM_EVENT              11-FEB-07          ALL

                    SYS                  WRH$_WAITSTAT                  11-FEB-07          ALL

                    SYS                  WRH$_LATCH                     11-FEB-07          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      11-FEB-07          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_DB_CACHE_ADVICE           11-FEB-07          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          11-FEB-07          ALL

                    SYS                  WRH$_SGASTAT                   11-FEB-07          ALL

                    SYS                  WRH$_SYSSTAT                   11-FEB-07          ALL

                    SYS                  WRH$_PARAMETER                 11-FEB-07          ALL

                    SYS                  WRH$_SEG_STAT                  11-FEB-07          ALL

                    SYS                  WRH$_SERVICE_STAT              11-FEB-07          ALL

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    11-FEB-07          ALL

                    SYS                  WRH$_TABLESPACE_STAT           11-FEB-07          ALL

                    SYS                  WRH$_OSSTAT                    11-FEB-07          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            11-FEB-07          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_SERVICE_WAIT_CLASS        11-FEB-07          ALL

                    SYS                  WRH$_FILESTATXS                01-JAN-07          ALL

                    SYS                  WRH$_SQLSTAT                   01-JAN-07          ALL

                    SYS                  WRH$_SYSTEM_EVENT              01-JAN-07          ALL

                    SYS                  WRH$_WAITSTAT                  01-JAN-07          ALL

                    SYS                  WRH$_LATCH                     01-JAN-07          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      01-JAN-07          ALL

                    SYS                  WRH$_DB_CACHE_ADVICE           01-JAN-07          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          01-JAN-07          ALL

                    SYS                  WRH$_SGASTAT                   01-JAN-07          ALL

                    SYS                  WRH$_SYSSTAT                   01-JAN-07          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_PARAMETER                 01-JAN-07          ALL

                    SYS                  WRH$_SEG_STAT                  01-JAN-07          ALL

                    SYS                  WRH$_SERVICE_STAT              01-JAN-07          ALL

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    01-JAN-07          ALL

                    SYS                  WRH$_TABLESPACE_STAT           01-JAN-07          ALL

                    SYS                  WRH$_OSSTAT                    01-JAN-07          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            01-JAN-07          ALL

                    SYS                  WRH$_SERVICE_WAIT_CLASS        01-JAN-07          ALL

                    SYS                  WRH$_FILESTATXS                26-NOV-08          ALL

                    SYS                  WRH$_SQLSTAT                   26-NOV-08          ALL

                    SYS                  WRH$_SYSTEM_EVENT              26-NOV-08          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_WAITSTAT                  26-NOV-08          ALL

                    SYS                  WRH$_LATCH                     26-NOV-08          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      26-NOV-08          ALL

                    SYS                  WRH$_DB_CACHE_ADVICE           26-NOV-08          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          26-NOV-08          ALL

                    SYS                  WRH$_SGASTAT                   26-NOV-08          ALL

                    SYS                  WRH$_SYSSTAT                   26-NOV-08          ALL

                    SYS                  WRH$_PARAMETER                 26-NOV-08          ALL

                    SYS                  WRH$_SEG_STAT                  26-NOV-08          ALL

                    SYS                  WRH$_SERVICE_STAT              26-NOV-08          ALL

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    26-NOV-08          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_TABLESPACE_STAT           26-NOV-08          ALL

                    SYS                  WRH$_OSSTAT                    26-NOV-08          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            26-NOV-08          ALL

                    SYS                  WRH$_SERVICE_WAIT_CLASS        26-NOV-08          ALL

                    SYS                  WRH$_EVENT_HISTOGRAM                              ALL

                    SYS                  WRH$_FILESTATXS                03-APR-09          ALL

                    SYS                  WRH$_SQLSTAT                   03-APR-09          ALL

                    SYS                  WRH$_SYSTEM_EVENT              03-APR-09          ALL

                    SYS                  WRH$_WAITSTAT                  03-APR-09          ALL

                    SYS                  WRH$_LATCH                     03-APR-09          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      03-APR-09          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_DB_CACHE_ADVICE           03-APR-09          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          03-APR-09          ALL

                    SYS                  WRH$_SGASTAT                   03-APR-09          ALL

                    SYS                  WRH$_SYSSTAT                   03-APR-09          ALL

                    SYS                  WRH$_PARAMETER                 03-APR-09          ALL

                    SYS                  WRH$_SEG_STAT                  03-APR-09          ALL

                    SYS                  WRH$_SERVICE_STAT              03-APR-09          ALL

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    03-APR-09          ALL

                    SYS                  WRH$_TABLESPACE_STAT           03-APR-09          ALL

                    SYS                  WRH$_OSSTAT                    03-APR-09          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            03-APR-09          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_SERVICE_WAIT_CLASS        03-APR-09          ALL

                    SYS                  WRH$_EVENT_HISTOGRAM           03-APR-09          ALL

                    SYS                  WRH$_SYSTEM_EVENT              27-NOV-12          ALL

                    SYS                  WRH$_WAITSTAT                  27-NOV-12          ALL

                    SYS                  WRH$_LATCH                     27-NOV-12          ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY      27-NOV-12          ALL

                    SYS                  WRH$_DB_CACHE_ADVICE           27-NOV-12          ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY          27-NOV-12          ALL

                    SYS                  WRH$_SGASTAT                   27-NOV-12          ALL

                    SYS                  WRH$_SYSSTAT                   27-NOV-12          ALL

                    SYS                  WRH$_PARAMETER                 27-NOV-12          ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_SEG_STAT                  27-NOV-12          ALL

                    SYS                  WRH$_SERVICE_STAT              27-NOV-12          ALL

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY    27-NOV-12          ALL

                    SYS                  WRH$_TABLESPACE_STAT           27-NOV-12          ALL

                    SYS                  WRH$_OSSTAT                    27-NOV-12          ALL

                    SYS                  WRH$_SYS_TIME_MODEL            27-NOV-12          ALL

                    SYS                  WRH$_SERVICE_WAIT_CLASS        27-NOV-12          ALL

                    SYS                  WRH$_EVENT_HISTOGRAM           27-NOV-12          ALL

                    SYS                  WRH$_MVPARAMETER                                  ALL

                    SYS                  WRH$_FILESTATXS                                   ALL

                    SYS                  WRH$_SQLSTAT                                      ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_SYSTEM_EVENT                                 ALL

                    SYS                  WRH$_WAITSTAT                                     ALL

                    SYS                  WRH$_LATCH                                        ALL

                    SYS                  WRH$_LATCH_MISSES_SUMMARY                         ALL

                    SYS                  WRH$_DB_CACHE_ADVICE                              ALL

                    SYS                  WRH$_ROWCACHE_SUMMARY                             ALL

                    SYS                  WRH$_SGASTAT                                      ALL

                    SYS                  WRH$_SYSSTAT                                      ALL

                    SYS                  WRH$_PARAMETER                                    ALL

                    SYS                  WRH$_SEG_STAT                                     ALL

                    SYS                  WRH$_SERVICE_STAT                                 ALL

                     

                     

                    OWNER                TABLE_NAME                     LAST_ANALYZED      STATT

                    -------------------- ------------------------------ ------------------ -----

                    SYS                  WRH$_ACTIVE_SESSION_HISTORY                       ALL

                    SYS                  WRH$_TABLESPACE_STAT                              ALL

                    SYS                  WRH$_OSSTAT                                       ALL

                    SYS                  WRH$_SYS_TIME_MODEL                               ALL

                    SYS                  WRH$_SERVICE_WAIT_CLASS                           ALL

                    SYS                  WRH$_EVENT_HISTOGRAM                              ALL

                    SYS                  WRH$_MVPARAMETER                                  ALL

                    SYS                  WRH$_FILESTATXS                27-NOV-12          ALL

                    SYS                  WRH$_SQLSTAT                   27-NOV-12          ALL

                     

                     

                    152 rows selected.

                     

                     

                    SQL>We have invalids in custom schema but not related to the objects listed in gss logfile

                     

                    Thank you

                    • 7. Re: Gather schema statistics error out in R12.1.3
                      3510875

                      What about other table..it is not existing but it is reflecting in GSS logfile

                       

                      SQL>  select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='XXAHC_GL_INT_CHECK_TBL1';

                       

                      no rows selected

                      • 8. Re: Gather schema statistics error out in R12.1.3
                        John_K

                        Have you created your custom table in accordance with apps standards?

                         

                        1) Create the table in the custom schema

                        2) Grant all to apps (with grant option)

                        3) Create synonym for apps pointing to take in custom schema