Gather Schema concurrent request is failed

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

Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FNDGSCST module: Gather Schema Statistics

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

Current system time is 26-JUN-2013 08:16:48

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

**Starts**26-JUN-2013 08:16:49

**Ends**26-JUN-2013 08:49:42

ORA-0000: normal, successful completion

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

Start of log messages from FND_FILE

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

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 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

stats on table GAT_REQ_QTBL 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

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

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

Executing request completion options...

Output file size:

0

Finished executing request completion options.

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

Concurrent request completed

Current system time is 26-JUN-2013 08:49:42

SOLUTION:

1.       Please also review "ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G [ID 375351.1]" for the messages

2.       Please review "Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade [ID 781813.1]" which appears to match your issue.

Cause

There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

Following SQL should have returned one row , not two.

SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS 
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :

 dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME 
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR 
COLUMNS SOURCE SIZE 254');

 Above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In above examples you can use FII_FIN_ITEM_HIERARCHIES.

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;

Solution

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.

-- 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
  );

1-----à

Cause

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:

    select owner, table_name, stattype_locked
    from dba_tab_statistics
    where stattype_locked is not null; 

Solution

Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner'); 

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('AR'); 
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');