Re: Question on large objects
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 27 Sep 2023 21:16:04 -0400
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:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6
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
Other related posts:
