Gather Schema Statistics "program error with Locks and Duplicate columns"

Oracle E-Business Suite Release 12 uses cost–based optimization in order to choose the most efficient execution plan for SQL statements.Gather Schema Statistics is  the favourite one among Apps DBA's and developers. Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

On Oracle Applications 12.1.3,

Gather schema statistics (GSS) is completing with error in our instances. 

When attempting to run Gather Schema Statistics request, the following error occurs:

Error

*******

**Starts**13-APR-2019 23:59:45

**Ends**14-APR-2019 05:30:40

ORA-0000: normal, successful completion

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 24 internal_flag= NOBACKUP

stats on table AQ$_WF_CONTROL_P is locked

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

Successfully resubmitted concurrent program FNDGSCST with request ID  4981147 to start at 21-APR-2019 00:00:00 (ROUTINE=AFPSRS)

Solution

***********

1. Please follow Doc ID 375351.1 to unlock the tables:

                AQ$_WF_CONTROL_P

                FND_CP_GSM_IPC_AQTBL

                FND_SOA_JMS_IN

                FND_SOA_JMS_OUT

Do the following select from dba_tab_stats to show all tables with locked statistics on them:

    SQL> select owner, table_name, stattype_locked from dba_tab_statistics 
           where stattype_locked is not null 
           and owner not in ('SYS');          

From DB node run below to unlock tables

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');

2. Please perform the following action plan from Doc ID 781813.1 :

   Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

   Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

   SQL> create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS;

 -- Identify duplicate rows

  SQL>select table_name, column_name, count(*) from FND_HISTOGRAM_COLS

             group by table_name, column_name 

             having count(*) > 1;

 -- Use above results on the following SQL to delete duplicates

 SQL>delete from FND_HISTOGRAM_COLS

           where table_name = '&TABLE_NAME'

           and column_name = '&COLUMN_NAME'

           and rownum=1;

 -- Use following SQL to delete obsoleted rows

  SQL>delete from FND_HISTOGRAM_COLS

            where (table_name, column_name) in

                  (

            select hc.table_name, hc.column_name

             from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

            where hc.table_name ='&TABLE_NAME'

            and hc.table_name= tc.table_name (+)

            and hc.column_name = tc.column_name (+)

            and tc.column_name is null

                  );

   SQL>commit;

3. Run "Gather Schema Statistics" program again.

    Fix worked successfully for our env.