Re: DML blocking

Thank You Jonathan.

Yes, the workarounds in the doc are little extreme ones. So basically as you suggested , the appropriate ones seems to be avoid the peak time for increasing size we will definitely consider. Regarding other point doing it in smaller chunks(say 100GB) and with some gap. So if i understand it correctly , doing it in smaller chunks will make this blocking happen for smaller duration, but talking about having some duration gap between two 'alter' statement, is there any technical cause around this or its just to give some time to wait and watch for any app blocking session?

On Mon, 11 Jul 2022, 3:33 pm Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

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