RE: Script to overcome ORA-54 during DDL
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 17:16:20 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Friday, June 25, 2004 4:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Script to overcome ORA-54 during DDL
----- Original Message -----
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 25, 2004 8:33 PM
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Friday, June 25, 2004 3:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Script to overcome ORA-54 during DDL
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
