Gather Schema Statistics" program error with Locks and Duplicate columns

on Friday, 22 March 2013



Gather Schema Statistics" program 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.

0 comments:

Post a Comment