You missed a line in the document:
*"This is blocking other processes especially the session issuing the
resize command."*
(my emphasis)
If an insert needs to add an extent to a table it has to acquire the
tablespace/file space management block(s) to mark the space as allocated.
So it's not surprising if it has to wait for the session (or Wnnn slaves)
that are extending the tablespace. The document gives a workaround; two
other "damage limitation" options are (1): don't resize a file by a HUGE
amount in one step, use smaller steps with an interval between each step.
(2) resize the tablespace/file only when there's likely to be very little
competing activity,
Regards
Jonathan Lewis
On Mon, 11 Jul 2022 at 10:18, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hi We have a customer database on version 19.11.We saw all of sudden most
of the session for a INSERT query got blocked and the wait event was
showing as "enq: TT - contention [mode=4]" . And the blocking session
appeared to be a session executing "ALTER database sql" as below. However ,
wanted to know if its expected behavior to block application query doing
DML on that same tablespace object or we are hitting any bug here?
I see a doc "Resize Tablespace Hangs with TT Enqueue Contention (Doc ID
2595875.1)", but it says the ALTER statement itself will hang but doesnt
tell anything on, if it will imact any DML on the object residing on that
tablespace. It's a bigfile tablespace.
alter database datafile '+DATA_XXXX/XXXXX/DATAFILE/data.XXX.XXXXXX' resize
10000G
