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.
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;
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-----Ã
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;
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');
