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

P1 = convert to hex = 0x5453 0003: the top half is equivalent to 'TS'; the 0003 is the v$lock mode (which means it's not about creating or dropping segments which require mode 6

P2 = 0x0003 0003 -> the top half says it's con_id 3 (which usually means the first PDB after the seed - see v$pdbs to check), the bottom half is the tablespace id (ts# from v$tablespaces in the PDB).

p3 is (should be) the tablespace relative block number, which means it's block 2 you're waiting for.

A block number that low would have to come from the one file allocated to a "bigfile" tablespace, so if the tablespace isn't bigfile then there's another interpretation of p2 and p3 that I don't know about.

Block 2 of the file is the "File Space Header" - so the next step is to find out why a session got stuck waiting for it ... possibly something to do with the file being extended and a process losing track of what was going on and not clearing a flag - or maybe space allocation from the O/S just took a very long time. I don't have any further ideas at the moment. It does give you a pointer to questions you could ask of Oracle Support.

Regards
Jonathan Lewis

On Fri, 10 Nov 2023 at 18:46, Hameed, Amir <amir.hameed_at_sleepnumber.com> 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?
>
>
>
> Event = enq: TS – contention
>
> P1 = 1414725635
>
> P2= 196611
>
> P3= 2
>
>
>
> Thank you,
>
> Amir
>
>

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