Hai, this article will explain how
to fix “ORA-20000: for Gather Schema Statistic Request for Single User
Because of slow performance in Fixed Assests Module, I have run the GCC request for FA user, and it completed with the following error. later I ran "GCC for ALL users", that also completed with same errors. For more information, read the below note it will
provide more detail explanation
Gathering Schema Stats ORA-20000: Index
"FA"."FA_RX_LOV_U1" or Partition of Such Index Is Unusable
(Doc ID 2013352.1)
Log file for GSS request
================================================================================================================
oratest@RUH-ERPAT01 ~]$ cd
/u01/PTEST/inst/apps/PTEST_ruh-erpat01/logs/appl/conc/log/
[oratest@RUH-ERPAT01 log]$ ls *538023*
l538023.req
[oratest@RUH-ERPAT01 log]$ cat
l538023.req
+---------------------------------------------------------------------------+
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 21-MAR-2016 12:44:23
+---------------------------------------------------------------------------+
**Starts**21-MAR-2016 12:44:23
**Ends**21-MAR-2016 12:44:53
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= FA
percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=FA.FA_RX_LOV***ORA-20000: index
"FA"."FA_RX_LOV_U1"
or partition of such index is in unusable state***
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
No completion options were requested.
Output file size:
0
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 21-MAR-2016 12:44:53
+---------------------------------------------------------------------------+
================================================================================================================
SOLUTION:
|
Rebuild the index using the following command.
SQL> alter index
fa.FA_RX_LOV_U1 rebuild online;
Index altered.
And then re-run GSS for FA User and retest.
After altered index and retested the issue, and i found that index changed
from "fa.FA_RX_LOV_U1" to "fa.FA_RX_LOV_U2"
Rebuild the index using the following command.
SQL> alter index
fa.FA_RX_LOV_U2 rebuild online;
Index altered.
And then re-run GSS for FA User and retested the issue
This article helps those who face issue with “Gather Schema Statistics”
Thanks for Reading.
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux
|
DATABASE
|
RMAN
|
RAC
|
EBS
|
EBS DataGuard
| ||||
