Re: Question on large objects
Date: Wed, 27 Sep 2023 21:16:04 -0400
Message-ID: <f5ebef8c-fc1d-2ef2-28e2-a6b776835c0e_at_gmail.com>
On 9/27/23 14:41, Pap wrote:
> I wasn't aware of this, so curious. So you mean to say the undo
> records for lob's are written to the same lob segment during any dml
> to the lob column and it's not written to the common undo tablespace?
> And that is causing lot of space consumption because they are not
> overridden/expired like normal undo?
>
Please look at the PCTVERSION option in the SQL Reference manual. This
option is not valid for SECUREFILE storage, but the explanation of that
option tells you all that you need to know. Yes, old versions of the
data are maintained in the LOB itself, probably to avoid undo segment
congestion. Here is the reference note for "CREATE TABLE" statement:
PCTVERSION integer
Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. If the database is running in manual undo mode, then the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
You can specify the |PCTVERSION| parameter whether the database is running in manual or automatic undo mode. |PCTVERSION| is the default in manual undo mode. |RETENTION| is the default in automatic undo mode. You cannot specify both |PCTVERSION| and |RETENTION|.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 28 2023 - 03:16:04 CEST