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