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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx]
Sent: 26 January 2016 23:12
To: thomas.kellerer@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________________
From: Jonathan Lewis
Sent: 25 January 2016 16:25
To: thomas.kellerer@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
@jloracle
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l