Re: Remove DBMS_LOCK Locks

On Jun 21, 1:22 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Jun 20, 11:41 pm, "klabu" <k..._at_mailinator.com> wrote:
>
>
>
> > <sybra..._at_hccnet.nl>
>
> > > 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
>
> 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- Hide quoted text -
>
> - 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

------------------------------ ------------------------------ ----
------------
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 -- Received on Thu Jun 21 2007 - 08:23:06 CDT