Re: [EXTERNAL] Re: enq: TS - contention

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 10 Nov 2023 14:52:55 -0500
Message-ID: <479b1560-108a-4038-a409-d58f0fd42e5a_at_gmail.com>

On 11/10/23 13:46, Hameed Amir (amir.hameed) wrote:
>
> Hi Jonathan,
>
> Thank you for the explanation. I looked through DBA_HIST views for
> information on job A for periods when it finished successfully, and I
> found the following statement in all those periods:
>
> DELETE FROM AR_AUTOREM_INTERIM WHERE BATCH_ID = :B1
>
> It is a standard Oracle EBS table. It is partitioned and has no index
> on it.
>
> Can you please show me how to convert P2 into TS name below?
>
Hi Amir!

The reference note for the event is the following:

---

LOCK: TS "Temporary Segment (also TableSpace)" Reference Note (Doc ID 34665.1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Lock: TS 

"Temporary Segment (also TableSpace)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This lock has 

different arguments in Oracle7 and Oracle8. Oracle8 and higher ~~~~~~~~~~~~~~~ The TS lock either protects a temporary segment or a bitmap tablespace. The temporary segment may be either for true TEMP usage or may be a segment which was once, or will become, a real segment. Eg: DROP table converts the TABLE segment to a TEMP segment

which is then cleaned up. TS-id1-id2 arguments ~~~~~~~~~~~~~~~~~~~~ Id1 

Id2 Use ~~~ ~~~ ~~~ Tablespace id Relative DBA TS lock protecting the segment (TS$.TS#) in this tablespace at this RDBA. (See <SupTool:ODBA> to convert an RDBA to a File/Block combination) Tablespace id Instance ID If ID2 is small it is an instance ID This is used for bitmapped The reference note doesn't explain P1/2/3, it directs you to V$LOCK. You should use ID1 as tablespace ID. Regards -- Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 10 2023 - 20:52:55 CET