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

Continuing that thought, if you make the table a single-partition partitioned table, 12c allows you to do an online move of a partition.  (I haven't checked - or tested - whether extended varchar2() makes a difference to the move, though).

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 Jonathan Lewis [jonathan@jlcomp.demon.co.uk]
Sent: 26 January 2016 23:12
To: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org
Subject: RE: Oracle 12c extended VARCHAR - LOB segment grows indefinitely

Another thought, that depends on what you're users are prepared to put up with and the limit you want to impose on the effort/risk, you could do simply:

alter table blob_test move;
alter index bt_pk rebuild;

after the commit.

The move locks the table, as does the rebuild, but the LOB segment for the extended varchar gets moved at the same time as the rest of the table (which is not true for normal LOB segments).

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

________________________________________
From: Jonathan Lewis
Sent: 25 January 2016 16:25
To: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org
Subject: 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

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


--