Remove DBMS_LOCK Locks
klabu
there're many many records in "dbms_lock_allocated" from my previous
testing, and I recognize the names
I know they're useless now
If they can/should be removed, how to do it ?
DBMS_LOCK.RELEASE(<lockid>) ;
returns " Parameter error"
SQL> desc sys.dbms_lock_allocated
Name Type Nullable Default Comments
---------- ------------- -------- ------- --------
NAME VARCHAR2(128)
LOCKID INTEGER Y
EXPIRATION DATE Y
thanks
sybr...@hccnet.nl
wrote:
From your post it is unclear how you are calling dbms_lock.release.
You didn't type *litterally*
exec dbms_lock.release(<lockid>);
did you?
--
Sybrand Bakker
Senior Oracle DBA
klabu
> From your post it is unclear how you are calling dbms_lock.release.
> You didn't type *litterally*
> exec dbms_lock.release(<lockid>);
> did you?
>
> --
> Sybrand Bakker
> Senior Oracle DBA
oh NO ! haha
this is the real spool here:
select * from sys.dbms_lock_allocated ;
NAME LOCKID EXPIRATION
---------------- ------------------ ----------------
REINSCHUNKING28 1073742457 18-Mar-2007
REINSCHUNKING301 1073742458 18-Mar-2007
REINSCHUNKING302 1073742459 18-Mar-2007
REINSCHUNKING303 1073742460 18-Mar-2007
REINSCHUNKING304 1073742461 18-Mar-2007
REINSCHUNKING305 1073742462 18-Mar-2007
<snip>
SQL> exec dbms_output.put_line(DBMS_LOCK.RELEASE(1073742457));
3
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(DBMS_LOCK.RELEASE(1073742459));
3
PL/SQL procedure successfully completed
sybr...@hccnet.nl
wrote:
So it looks like you need to find out from the documentation (you
know, that thingy you refuse to read) what a return status of *3*
means.
Vladimir M. Zakharychev
> <sybra...@hccnet.nl>
The lock identifiers in the list are not valid for
dbms_lock.release(): valid range for user-assigned lock identifiers is
0-1073741823, everything past that range is reserved for system-
assigned identifiers allocated with dbms_lock.allocate_unique().
That's why you're getting that "Parameter error" back. Did you try
using lock handles instead of lock identifiers (this should work)? Did
you try to reconnect the session that owns the locks (this should
release all locks held by that session automagically)? Did you try to
RTFM more thoroughly? ;)
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Mark D Powell
>
> - Show quoted text -
There is generally no need to clean out sys.dbms_lock_allocated for
expired user locks or locknames that will not be used again.
When you perform major upgrades the table is truncated and the
sequence is reset. In fact with one release Oracle failed to perform
the truncate but reset the sequence and since there is no unique index
on the lockid column duplicate lockid values could exist resulting in
false reports the lock name was in use. One of the fixs from Oracle
was to truncate the table. My idea was to just reset the sequence to
use values above the highest existing user lock id (which was not that
far from the starting point to begin with)
>From a test db version 9.2.0.6 on AIX 5.2
UT1 > desc sys.dbms_lock_allocated
Name Null? Type
----------------------------------------- --------
----------------------------
NAME NOT NULL VARCHAR2(128)
LOCKID NUMBER(38)
EXPIRATION DATE
UT1 > @obj/idx_tbl
Enter table name: dbms_lock_allocated
Enter owner name: sys
Index Name U Typ Tablespace Initial
Next %Incr
------------------------------ - --- ------------ ----------
---------- -----
SYS_C008663 U NOR SYSTEM 12K
12K 50
UT1 > @obj/idx_col
Enter value for index: sys_c008663
Idx Name Column POS Idx
Owner
------------------------------ ------------------------------ ----
------------
SYS_C008663 NAME 1 SYS
Because of this if you took a maintenance window and as SYS deleted
the test locks no harm is likely to result; however, I do not
recommend doing this on a production instance. Just wait for the next
major upgrade to clean up the table
HTH -- Mark D Powell --
klabu
<sybr...@hccnet.nl>
> that thingy you refuse to read) what a return status of *3*
> means.
umm I know what it means as I indicated in Post#1
thanks for not reading !
sybr...@hccnet.nl
wrote:
I just read the post of Vladimir, and I must say your assertion 'I
know what it means' seems to be not correct.
Obviously I should thank YOU for not reading.
O heck, I remember, you are just a clown AND you are also lazy.
klabu
<sybr...@hccnet.nl>
> I just read the post of Vladimir,
Don't read Vladimirs, read mine - his is helpful thou, unlike yours (as
always).
>and I must say your assertion 'I
> know what it means' seems to be not correct.
I know if means "Parameter Error" <shrug>
> O heck, I remember, you are just a clown AND you are also lazy.
And I know you've been a fucking nearly-useless, sarcastic, RTFM Dutch
prick around here forever.