Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!
nice bit of detective work there Tim.
Jared
"Johnston, Tim" <TJohnston_at_quallaby.com>
Sent by: root_at_fatcity.com
05/14/2003 05:00 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: Subject: DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!
Ok... I think I figured it out... catalog and catproc are run during the upgrade... If you look in catproc, you will see that it calls prvtlock.plb... Look in prvtlock.plb and you will see the following...
create table dbms_lock_allocated(
name varchar2(128) primary key, lockid integer, expiration date)
The table already exists since this was a 9.2.0.2 database... But, it
drops
and recreates the sequence... The next lock allocate is therefore given
an
id that ALREADY EXISTS in the dbms_lock_allocated table!!! i.e.
Notice the table was created several months ago...
select object_name, created, last_ddl_time from dba_objects where object_name = 'DBMS_LOCK_ALLOCATED';
OBJECT_NAME CREATED LAST_DDL_TIME DBMS_LOCK_ALLOCATED 11/15/2002 16:03:32 11/15/2002 16:03:32
But, the sequence was just created when I upgraded...
select object_name, created, last_ddl_time from dba_objects where object_name = 'DBMS_LOCK_ID';
OBJECT_NAME CREATED LAST_DDL_TIME DBMS_LOCK_ID 05/06/2003 17:56:33 05/06/200317:56:33
And of course it is starting over...
select sequence_name, last_number from dba_sequences where sequence_name = 'DBMS_LOCK_ID';
SEQUENCE_NAME LAST_NUMBER DBMS_LOCK_ID 1073741855
But, there are already values in dbms_lock_allocated that are greater then the values it is issuing...
select count(*) from sys.dbms_lock_allocated where lockid > 1073741855;
COUNT(*)
116
And, here is the relevant part of the catproc log that shows this occurring...
Package body created.
create table dbms_lock_allocated(
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Sequence dropped.
Sequence created.
Now, if you look in a prvtlock.plb from a 8.1.7.4 upgrade you see the following...
drop table dbms_lock_allocated
/
create table dbms_lock_allocated(
name varchar2(128) primary key, lockid integer, expiration date)
So, it looks like someone removed the drop table dbms_lock_allocated from the 9.2.0.3 prvtlock.plb....
HTH
Tim
-----Original Message-----
Sent: Wednesday, May 14, 2003 6:02 PM
To: Multiple recipients of list ORACLE-L
Hi Everyone...
I've encountered a strange issue and wanted to know if anyone else have
ever seen something like this... I've had this issue occur on two
different
systems... In each case the systems Solaris 9 running 9.2.0.2 and then
upgraded to 9.2.0.3... The issue is when you supply a string to
DBMS_LOCK.ALLOCATE_UNIQUE you should get a unique lock handle back... For
example...
SQL> Declare
2 sLockName1 Varchar2(100) :=
'PARTITION_C02_NRAW_000_H0_2003050400_LCK';
3 sLockName2 Varchar2(100) := 'PVM_VIEWS_NRAW_000_LCK'; 4 sLockHandle1 Varchar2(100); 5 sLockHandle2 Varchar2(100);
7 Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 ); 8 dbms_output.put_line(sLockHandle1); 9 Dbms_Lock.Allocate_Unique( sLockName2, sLockHandle2 ); 10 dbms_output.put_line(sLockHandle2);11 End;
10737418261073741826149
PL/SQL procedure successfully completed.
But, I am encountering a situation where two DIFFERENT lock names are producing the EXACT SAME lock handle... i.e.
SQL> Declare
2 sLockName1 Varchar2(100) :=
'PARTITION_C02_NRAW_000_H0_2003050500_LCK';
3 sLockName2 Varchar2(100) := 'PVM_VIEWS_NRAW_000_LCK'; 4 sLockHandle1 Varchar2(100); 5 sLockHandle2 Varchar2(100);
7 Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 ); 8 dbms_output.put_line(sLockHandle1); 9 Dbms_Lock.Allocate_Unique( sLockName2, sLockHandle2 ); 10 dbms_output.put_line(sLockHandle2);11 End;
10737418261073741826149
PL/SQL procedure successfully completed.
Has anyone seen anything like this before?
Regards,
Tim Johnston
Tel: 978-322-4226
Fax: 978-322-4100
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnston, Tim INET: TJohnston_at_quallaby.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnston, Tim INET: TJohnston_at_quallaby.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 14 2003 - 21:06:58 CDT