RE: Oracle 12c extended VARCHAR - LOB segment grows indefinitely

Thomas,

I said you would get the same problem with a CLOB because I ran your model on 12.1.0.2 then changed the varchar2(32000) to CLOB and got the same size problem. 

I've just run the test on 11.2.0.4 - and the same thing occurs - massive overallocation for ASSM, relatively small overallocation for freelist management.
How have you defined the CLOB in 11.2, and what's the tablespace definition ? (and which version of 11.2?)



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Thomas Kellerer [thomas.kellerer_at_mgm-tp.com]
Sent: 25 January 2016 16:04
To: oracle-l_at_freelists.org
Subject: Re: Oracle 12c extended VARCHAR - LOB segment grows indefinitely

Jonathan,

> A CLOB column will have the same problem. I think I came across this
> problem quite a long time ago so it may be logged as a bug
> somewhere.

We have been running this on 11.2 for years now using a CLOB column
and never encountered a problem.


> If you can't find it as a bug on MoS raise it.

I already searched for it and there either is no bug, or I am not able
to find the right search terms ;)

> If you're licensed for the partition option you could avoid the growth and the down-time
> by adding in a step to recreate the table:
> (I haven't tested this with a CLOB/extended varchar2 in place):
>
> Create the table as a hash partitioned table with one partition
> Create a non-partitioned clone
> Do your delete/insert
> truncate the clone
> insert append from the partition to the clone
> exchange partition with table - online (12c feature).


Thanks for the hint, I will test that. But I think if this indeed is a bug,
then we will probably switch back to CLOBs rather than changing the process
that fills the tables.

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


--