ISSUES in GATHER SCHEMA STATISTICS

There is a familiar error with Locks and Duplicate columns:-

Error

*******

**Starts**14-APR-2012 23:58:43

**Ends**15-APR-2012 05:34:48

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 35759846 to start at 22-APR-2012 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

               

From DB node run below to unlock tables

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

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

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

                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

   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

   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

   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

   );

3. Run "Gather Schema Statistics" program again.

 

Voila... It will  be successful