Skip to Main Content
  • Questions
  • ORA-04021: timeout occurred while waiting to lock object on SYS.DBMS_AQADM.CREATE_QUEUE

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vincenzo.

Asked: November 18, 2016 - 9:42 am UTC

Last updated: November 19, 2016 - 4:21 am UTC

Version: 11.2.0.4.0 - 64bit Production RAC

Viewed 10K+ times! This question is

You Asked

Hi,
i have a problem during SYS.DBMS_AQADM.CREATE_QUEUE.
After 15 minutes Oracle writes :

ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3647
ORA-06512: at "SYS.DBMS_AQADM", line 130

In order to create the queue correctly, i have to restart all the instance of Oracle RAC.

When i drop the oracle user that create the queue and i recreate the same user with the same privileges , etc. the creation of queue, whit the same queue name, fails.
If i change the name of queue it works. It seems that after user drop, Oracle "remember" the queue name

Thanks and sorry for my little english




10:14:23 SQL> BEGIN
10:14:23 2
10:14:23 3 SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
10:14:23 4 (
10:14:23 5 QUEUE_TABLE => 'WPO_MSG_TRIGGER_TABLE'
10:14:23 6 ,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
10:14:23 7 ,COMPATIBLE => '10.0.0'
10:14:23 8 ,MULTIPLE_CONSUMERS => TRUE
10:14:23 9 ,SORT_LIST => 'ENQ_TIME'
10:14:23 10 ,MESSAGE_GROUPING => 0
10:14:23 11 ,SECURE => FALSE
10:14:23 12 ,primary_instance => '0'
10:14:23 13 ,secondary_instance => '0'
10:14:23 14 );
10:14:23 15 END;
10:14:23 16 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.29
10:14:26 SQL>
10:14:26 SQL> BEGIN
10:14:26 2
10:14:26 3 SYS.DBMS_AQADM.CREATE_QUEUE
10:14:26 4 (
10:14:26 5 QUEUE_NAME => 'WPO_MESSAGE_TRIGGER'
10:14:26 6 ,QUEUE_TABLE => 'WPO_MSG_TRIGGER_TABLE'
10:14:26 7 ,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
10:14:26 8 ,MAX_RETRIES => 5
10:14:26 9 ,RETRY_DELAY => 0
10:14:26 10 ,RETENTION_TIME => 0
10:14:26 11 );
10:14:26 12 END;
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3647
ORA-06512: at "SYS.DBMS_AQADM", line 130

and Connor said...

Your english is a lot better then my italian :-)

Before doing DROP_QUEUE, try doing STOP_QUEUE, and also try DROP_QUEUE_TABLE with force=>true, to make sure we are cleaning up all remnants of the previous queue.

If that doesn't work, 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 but.

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.

Hope this helps.

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library