Dbms_lock.sleep Error
Contents |
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack pls-00201 identifier 'dbms_lock' must be declared oracle Overflow the company Business Learn more about hiring developers or posting ads with us Server dbms_lock.sleep alternative Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network
Oracle Sleep Without Dbms_lock
administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Why can't I
Sys.dbms_lob Must Be Declared
grant exec on dbms_lock.sleep() OR create a procedure using it (but I can run it fine on its own) up vote 0 down vote favorite I am trying to write a small bit of PL/SQL that has a non-CPU burning sleep in it. The following works in sqldeveloper begin dbms_lock.sleep(5); end; BUT (as the same user), I can't do the following: create or replace procedure sleep(seconds in number) is begin dbms_lock.sleep(seconds); end; without dbms_lock.sleep in oracle 11g example the error "identifer "DBMS_LOCK" must be declared... Funny as I could run it without a procedure. Just as strange, when I log in as a DBA, I can run the command grant exec on dbms_lock to public; and I get ERROR at line 1: ORA-00990: missing or invalid privilege This is oracle version "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production" oracle-11g oraclexe share|improve this question asked Sep 13 '12 at 16:37 Richard Green 7114 EXEC is not valid privilege name !!! –user106666 Sep 14 '12 at 14:00 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote [richard@f1 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 14 13:33:18 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> connect sys@xe as sysdba Enter password: ****** Connected. SQL> grant execute on sys.dbms_lock to richard; Grant succeeded. share|improve this answer answered Sep 14 '12 at 12:44 Richard Green 7114 So you use my solution.What about accept my answer? –user106666 Sep 14 '12 at 14:01 As it wasn't the "grant execute..." that was the issue, but it was the sqlplus / nolog followed by the connect sys@xe as sysdba which was the correct way to authenticate to the server with the appropriate
06, 2011 - 10:33 am UTC Category: Database – Version: 8.1.7 Latest Followup You Asked Any comment on this weird behaviour :- SQL> exec dbms_lock.sleep(3500); PL/SQL procedure successfully completed. Elapsed: 00:08:14.18 SQL>
Dbms_lock Must Be Declared Oracle 10g
exec dbms_lock.sleep(1000); PL/SQL procedure successfully completed. Elapsed: 00:03:48.71 SQL> exec dbms_lock.sleep(600); PL/SQL grant execute on dbms_lock procedure successfully completed. Elapsed: 00:09:41.12 Does this mean that dbms_lock.sleep is unreliable ? Thanks Suds and we said... I pls-00201 identifier 'procedure name' must be declared think it means you have a TON more patience then I ever would in a billion years.... I cannot believe you waited 8 hours.... There is an issue with really large sleep http://serverfault.com/questions/427177/why-cant-i-grant-exec-on-dbms-lock-sleep-or-create-a-procedure-using-it-but times. Suggest you sleep no more then 10 minutes or so and for larger "sleeps", loop it, something like this: create procedure my_sleep( SLEEP_TIME IN NUMBER ) as begin for i in 1 .. trunc( SLEEP_TIME/600 ) loop dbms_lock.sleep( 600 ); end loop; dbms_lock.sleep( SLEEP_TIME-trunc(SLEEP_TIME/600)*600 ); end; / Reviews Write a Review October 17, 2002 - 11:19 pm UTC Reviewer: Sudhanshu Kumar Thanks https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6019886867656 Tom for the loop idea. But the timing 00:08:14.18 means 8 minutes & 14 seconds.And I think sqlplus have this default format of giving hundreth of a second when we do set timing on. So that means I only waited for 8 minutes. Followup October 18, 2002 - 8:50 am UTC DOH -- i read that wrong didn't I. but yes, dbms_lock.sleep has problems with really large numbers -- the loop will correct that. DBMS_LOCK package October 23, 2003 - 12:02 pm UTC Reviewer: Denise from Virginia, USA Hi Tom I seem to be pestering you alot these days. I have a lock on a table that I am trying to figure out how to release.(see v$lock output) Oracle usercode Oracle SID Serial nbr Object name Lock type F_ground B_groun ------------------------------ ---------- ---------- --------------- ------------- --------- ------- GUI 8 3 MAILING Row-X (SX) 188 SQL> select * from v$lock where sid = 8; Thu Oct 23 Exclusive locks ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 3A330458 3A330524 8 TX 131138 177618 6 0
running USP_CHECKBATCHJOBSTATUS Technote http://www-01.ibm.com/support/docview.wss?uid=swg21347515 (troubleshooting) Problem(Abstract) Error 'PLS-00201: identifier 'DBMS_LOCK' must be http://info.michael-simons.eu/2010/09/07/oracle-sleep-procedure-dbms_lock-sleep/ declared' when running USP_CHECKBATCHJOBSTATUS. Symptom English: PLS-00201: identifier 'DBMS_LOCK' must be declared German: FUNCTION schemaname.USP_CHECKBATCHJOBSTATUS On line: 14 PLS-00201: Bezeichner 'DBMS_LOCK' muss deklariert werden Cause The Oracle schema (the user used by the must be Controller application server to connect to the Oracle database) does not have access to the DBMS_LOCK package. In other words, the Oracle schema was not created with the required permissions (as per Cognos recommendations). TIP: For full details of the permissions/script for must be declared how to create a new schema, see separate IBM Technote #. Environment Oracle database. Resolving the problem Grant Execute permission to the Oracle user/schema used for the Controller application repository, by running the following script: GRANT execute ON DBMS_LOCK TO
2010 by Michael There's a nice little "sleep" procedure in Oracle: A procedure that stops the execution of the current thread for n seconds. Strangely, this method can be called in SQL*Plus like so: EXEC dbms_lock.sleep(10); but not in another stored procedure or function like so CREATE OR REPLACE PROCEDURE FOOBAR AS BEGIN DBMS_LOCK.SLEEP(1); END; / To use "sleep" in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects: CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS BEGIN DBMS_LOCK.SLEEP(seconds); END; / CREATE OR REPLACE public synonym sleep FOR sleep; GRANT EXECUTE ON sleep TO public; and in your procedure just use "sleep". 14 comments Post a comment | Trackback URI | RSS 2.0 feed for those comments Marc wrote: Man you saved my day! Had exactly that darn problem and couldn't figure out why. Thanks a lot! 🙂 Posted on October 21, 2010 at 3:28 PM | Permalink Michael wrote: You're welcome, Marc. Nice Blog, btw! Posted on October 21, 2010 at 3:49 PM | Permalink Erik Hymel wrote: You can call it from a procedure. I suspect you have execute on dbms_lock through a role, but to call the procedure the permission needs to be granted directly to the user. Posted on April 26, 2011 at 5:47 PM | Permalink Amin Adatia wrote: It seems that in order to use DBMS_LOCK you need to be granted EXECUTE on DBMS_LOCK by SYS (or SYSTEM?). Wht the package is just not publicly available out-of-the-box is unknown. No idea what is so terrible in the package. Posted on February 19, 2012 at 6:57 PM | Permalink Michael wrote: Exactly that didn't work for me. I had to create this superfluous alias, for whatever reason. You see i have grant on this as well. Posted on February 19, 2012 at 7:16 PM | Permalink Muneer Nawaz wrote: This