Re: DML blocking

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_at_gmail.com> 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
>
>
>

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