While running Concurrent Program, Gather Schema Statitics the request failed with error
20005: object statistics are locked (stattype = ALL)***
Logfile:
start of log messages from FND_FILE
+----------------------------------------------------------------------
-----+
In GATHER_SCHEMA_STATS , schema_name= APPS percent= 10 degree = 2
internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.CZ_MESSAGE_QENTRIES***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_INQUEUE***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_IN_OAG_Q_TABLE***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #4: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_OUTQUEUE***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #5: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWD_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #6: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWD_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #7: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWL_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #8: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWL_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #9: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_GTREQ_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #10: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_GTRES_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #11: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_HM000A_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #12: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_HM000A_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #13: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_JTF_REQ_Q_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #14: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_JTF_RES_Q_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #15: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_BP_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #16: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_B_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #17: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_ER_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #18: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_MP_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #19: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_M_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #20: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_IH_BULK_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #21: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_PF_LOGGING_TABLE***ORA-
20005: object statistics are locked (stattype = ALL)***
+----------------------------------------------------------------------
-----+
End of log messages from FND_FILE
+----------------------------------------------------------------------
-----+
Solution:
This error comes when analyzing tables in oracle. Basic issue in this error is when you
import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:
select table_name, stattype_locked
from dba_tab_statistics
where owner = ‘APPS’ and stattype_locked is not null;
Then how to unlock them, following query will help us to sort this issue:
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name
||”’);’ from dba_tab_statistics where owner = ‘MBS’ and stattype_locked is not null;
or you can do that for every single table:
exec DBMS_STATS.UNLOCK_TABLE_STATS(‘owner’,'table name’);
20005: object statistics are locked (stattype = ALL)***
Logfile:
start of log messages from FND_FILE
+----------------------------------------------------------------------
-----+
In GATHER_SCHEMA_STATS , schema_name= APPS percent= 10 degree = 2
internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.CZ_MESSAGE_QENTRIES***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_INQUEUE***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_IN_OAG_Q_TABLE***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #4: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.ECX_OUTQUEUE***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #5: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWD_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #6: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWD_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #7: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWL_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #8: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_CRMXRWL_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #9: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_GTREQ_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #10: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_GTRES_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #11: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_HM000A_REQ_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #12: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_HM000A_RES_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #13: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_JTF_REQ_Q_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #14: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_JTF_RES_Q_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #15: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_BP_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #16: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_B_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #17: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_ER_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #18: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_MP_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #19: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_FM_RAPID_M_QTBL***ORA-
20005: object statistics are locked (stattype = ALL)***
Error #20: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_IH_BULK_QTBL***ORA-20005:
object statistics are locked (stattype = ALL)***
Error #21: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.JTF_PF_LOGGING_TABLE***ORA-
20005: object statistics are locked (stattype = ALL)***
+----------------------------------------------------------------------
-----+
End of log messages from FND_FILE
+----------------------------------------------------------------------
-----+
Solution:
This error comes when analyzing tables in oracle. Basic issue in this error is when you
import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:
select table_name, stattype_locked
from dba_tab_statistics
where owner = ‘APPS’ and stattype_locked is not null;
Then how to unlock them, following query will help us to sort this issue:
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name
||”’);’ from dba_tab_statistics where owner = ‘MBS’ and stattype_locked is not null;
or you can do that for every single table:
exec DBMS_STATS.UNLOCK_TABLE_STATS(‘owner’,'table name’);
No comments:
Post a Comment
Was this Post Helpful?
Feel free to suggest your opinions in the comments section!