Error #1: ERROR: While GATHER_TABLE_STATS: object_name=PJI.PJI_PA_PROJ_EVENTS_LOG***ORA-20000: index “PJI”.”PJI_PA_PROJ_EVENTS_LOG_N1″ or partition of such index is in unusable state***

+—————————————————————————+
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 19-JUL-2013 20:03:22
+—————————————————————————+
**Starts**19-JUL-2013 20:03:22
**Ends**20-JUL-2013 07:13:36
ORA-0000: normal, successful completion
+—————————————————————————+
Start of log messages from FND_FILE
+—————————————————————————+
In GATHER_SCHEMA_STATS ,
schema_name= ALL percent= 10
degree = 2 internal_flag= NOBACKUP
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=PJI.PJI_PA_PROJ_EVENTS_LOG***ORA-20000: index
“PJI”.”PJI_PA_PROJ_EVENTS_LOG_N1″
or partition of such index is in unusable state***
+—————————————————————————+
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 20-JUL-2013 07:13:37
+—————————————————————————+

Solution:

To check table has been locked or not:

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

Unlock the particular table:

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

To check the Index status:

select INDEX_NAME,INDEX_TYPE,BLEVEL, STATUS, LAST_ANALYZED, OWNER from dba_indexes where
upper(index_name) like’%PA_PROJ_EVENTS%’

Rebuiding the particular index:

ALTER INDEX PJI.PJI_PA_PROJ_EVENTS_LOG_N1 REBUILD ONLINE;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s