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
