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@xxxxxxxxxxxxxxx>
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
