| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Script to overcome ORA-54 during DDL
Edited to reduce clutter.
If I remember correctly what support has told me in the past while working on dbms_lock related bugs is that the relatively expensive comment is largely related to the shared pool memory requirements. How true this concern is, is beyond my experience. We have not experienced any problems with lock related memory but we only have about 30 UL locks in use at any one time.
Warning in the Oracle 9.2 something upgrade scripts Oracle fail to drop the sys.dbms_lock_allocated table prior to attempting to recreate it. This means all existing user lock definitions for want of a better term are retained through the upgrade. The sequence,DBMS_LOCK_ID, however is reset resulting in the possibility that a duplicate ID will be issued for two different named locks. To get around this problem you can follow support's advice to truncate the base table (when no user locks are active!) or do as I did and increment the sequence larger than the max(lockid)value.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Friday, June 25, 2004 4:32 PM
To: oracle-l_at_freelists.org
Subject: Re: Script to overcome ORA-54 during DDL
UL locks are also a relatively expensive resource according to the Oracle documentation and requiring every session to take a share level UL lock on a table and then release it for OLTP type work would be a lot of overhead relative to the work being done.
>> I think the 'relatively expensive' comes from Oracle's
>> vague warning that is supposed to stop people from
>> taking out UL enqueues at the rate they could take
>> out row-locks. A UL enqueue is no more expensive
>> than a TM enqueue because it's exactly the same
>> type of thing.- so all you would be doing is taking
>> two "TM locks" to update a table.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
Sent: Friday, June 25, 2004 3:21 PM
To: oracle-l_at_freelists.org
Subject: RE: Script to overcome ORA-54 during DDL
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 25 2004 - 16:13:51 CDT
 |
 |