Re: Remove DBMS_LOCK Locks

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 systemassigned  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 Received on Thu Jun 21 2007 - 00:22:49 CDT