Mostly the source would probably be in Oracle Core. If not then in the
notes on my laptop of tests I've done in the past.
The meaning of the parameters is listed in v$event_name (though some of the
descriptions may be a little out of date) Enabling event 10704 at various
levels used to produce interesting traces of locks but from 11g you can
execute (e.g.): *alter session set events 'trace[ksq] disk=high'* then do
some things that require tablespace space management to see what TS enqueue
information appears in the trace files.
The note you've referenced is very old and has various bits of information
that are out of date, and links to other notes that are out of date or
wrong, so I wouldn't put much faith in it - despite "update dates" being in
the recent past.
Regards
Jonathan Lewis
On Fri, 10 Nov 2023 at 21:15, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
On 11/10/23 15:58, Jonathan Lewis wrote:
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
Jonathan, can you please provide source for this? I found the reference
note, but it doesn't contain the information about P1/2/3. Can you please
post the document id?
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
