Prev: dbora not start automatically database
Next: Oracle Reports Server 10g problem
From: Mark D Powell on 21 Jun 2007 09:23
On Jun 21, 1:22 am, "Vladimir M. Zakharychev"
<vladimir.zakharyc...(a)gmail.com> wrote:
> 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- 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
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 --

From: klabu on 21 Jun 2007 12:31

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


From: sybrandb on 21 Jun 2007 14:56
On Thu, 21 Jun 2007 12:31:30 -0400, "klabu" <klabu(a)mailinator.com>
wrote:

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

--

Sybrand Bakker
Senior Oracle DBA
First  |  Prev  | 
Pages: 1 2
Prev: dbora not start automatically database
Next: Oracle Reports Server 10g problem