ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier

When Using Gather Auto Option Errors - Is an Invalid Identifier (Doc ID 1363044.1)


APPLIES TO:

Oracle EBS Applications Performance - Version 12.1.2 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 12.1.2 version, when attempting to run concurrent program "Gather Schema Statistics" with
GATHER AUTO option, the following error occurs:

ORACLE error 20001 in FDPSTP

Cause: FDPSTP failed due to ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier
ORA-06512: at "APPS.FND_STATS", line 774
ORA-06512: at line 1
.

The SQL statement being executed at the time of the error was: a
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
ORA-20001: SYS_NTIJR7UZJGXWFGRAAUTZQHMQ== is an invalid identifier
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
CP-GENERIC ORACLE ERROR (ERROR=ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REA)
(ROUTINE=FND_CONCURRENT.INIT_REQUEST)
Concurrent request completed


CAUSE

This is explained in the following bug:

Bug 9542112 - ERROR IN R12 FOR GATHER SCHEMA STATS WITH 'GATHER AUTO' OPTION.

As per bug 9542112, Issue is happening when running Gather schema stats with gather auto option.
actually in Gather auto option we have another cursor ( empty stats cursor)which selects tables with stale stats
( tables whose last analyzed date is NULL).

Empty cursor will be executed after completing gather auto option, that means stats will be gathered on all the  tables before going to empty cursor( where it is throwing this error). It will throw error when gathering stats  on tables with stale stats( that too only for tables which are not there in dba_tables).

SOLUTION

To implement the solution, please

1. Apply patch 9542112 ERROR IN R12 FOR GATHER SCHEMA STATS WITH GATHER AUTO OPTION to a non critical environment.

2. Retest the issue.

3. Check the file versions on file system and database

AFSTATSB.pls120.12.12010000.14

AFSTATSS.pls120.7.12010000.10


[appl@sujeet ~]$ sqlplus apps/****** 

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Dec 13 05:01:49 2017

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select fnd_release.minor_version from dual; 

MINOR_VERSION
-------------
1

SQL> select fnd_release.major_version from dual; 

MAJOR_VERSION
-------------
12

SQL> !
[appl@sujeet ~]$ adident Header $FND_TOP/patch/115/sql/AFSTATSB.pls 
/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/AFSTATSB.pls:
$Header AFSTATSB.pls 120.12.12010000.14 2009/12/17 11:59:49 msaleem ship $ 

[appl@sujeet ~]$ adident Header $FND_TOP/patch/115/sql/AFSTATSS.pls 

/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/AFSTATSS.pls:
$Header AFSTATSS.pls 120.7.12010000.10 2009/12/16 12:32:58 msaleem ship $ 

4. Migrate the solution as appropriate to other environments.

REFERENCES

BUG:9542112 - ERROR IN R12 FOR GATHER SCHEMA STATS WITH GATHER AUTO OPTION