Skip to Main Content
  • Questions

  • ORA-04021: timeout occurred while waiting to lock object ORA-06512: at "SYS.DBMS_STATS",

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, venkat.

Asked: March 16, 2017 - 7:48 pm UTC

Last updated: March 18, 2017 - 5:13 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production

Viewed 10K+ times! This question is

You Asked

Hi,

I am performing the below tasks by using ODI(Oracle data integrator):

Step-1: Truncate the Table1
Step2: Truncate table 2
Step-3: Drop the INdex's on Table1 & Table 2
Step4: Insert the data into Table1
Commit
Step-5: Insert the data into Table2
Commit
In Step-6: Performing below steps (in sequential order)
create an index index1 on Table1
create an index index2 on Table1
create an index index3 on Table2
create an index index4 on Table2
collect gather stats on table1
collect gather stats on table2

Issue:
Till Step-6 create an Index index4 on table2 its working fine.
But while performing the gather stats on table1 getting the below error
ORA-04021: timeout occurred while waiting to lock object ORA-06512: at "SYS.DBMS_STATS"

Observation: We have observed the above issue is happening on every Thursday(so far repeated on Mar 9th and 16th). But in other days its working fine.

Could you please through some light on it. Thanks.


and Connor said...

Check out MOS Note 1486712.1 which lets you diagnose the ora-4021 further to get the offending objects/sessions etc. You may have hit a bug.

During the diagnosis period, you can alter of value of parameter "_kgl_time_to_wait_for_locks" so you dont have to wait 15mins, but get the blessing of Support before doing that.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.