Prev: dbora not start automatically database
Next: Oracle Reports Server 10g problem
From: klabu on 20 Jun 2007 15:01
10gR2

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


From: sybrandb on 20 Jun 2007 15:11
On Wed, 20 Jun 2007 15:01:23 -0400, "klabu" <klabu(a)mailinator.com>
wrote:

>10gR2
>
>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
>

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
From: klabu on 20 Jun 2007 15:41
<sybrandb(a)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


From: sybrandb on 20 Jun 2007 17:34
On Wed, 20 Jun 2007 15:41:52 -0400, "klabu" <klabu(a)mailinator.com>
wrote:

><sybrandb(a)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
>

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.

--
Sybrand Bakker
Senior Oracle DBA
From: Vladimir M. Zakharychev on 21 Jun 2007 01:22
On Jun 20, 11:41 pm, "klabu" <k...(a)mailinator.com> wrote:
> <sybra...(a)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

 |  Next  |  Last
Pages: 1 2
Prev: dbora not start automatically database
Next: Oracle Reports Server 10g problem