Are you putting the LOB Segment in an ASSM Tablespace
("segment_space_management='AUTO' " ) ?
In that case, use Manual Segment Space Management for the LOB Segment
Tablespace.
I have done a few "migrations" of a Table with LONG RAW into another Table
with the column as a BLOB
and have, unfortunately, suffered significantly with ASSM Tablespaces --
monitoring the growth of the
segment as I am running the INSERT I find that it keeps slowing down.
In the database, where after some wisdom struck me, I created the
Tablespace with Manual SSM,
the INSERT was much faster.
(using INSERT /*+ APPEND */ into new_table_with_BLOB as SELECT /*+
PARALLEL (ot 4) */
col1, col2, to_lob(longrawcol), col4 from old_table_with_LONGRAW ot; )
At 01:03 AM Friday, Zhu,Chao wrote:
hi, guys,
We have a case , where we need to reorg some of our partitions to reclaim disk space due to inproper pctused and delete.
case like;
oracle 9205/solaris8.
subpartition part1_01 with 18 columns , 1LOB column. nocache/nologging/out of line/default pctversion;
subpartition table segment 2gb, lob segment about 6gb.
do a CTAS for table without lob segment can be done pretty quick (less than 10 minutes, detailed data at office computer), but with LOB column, it alwasy fails with snapshot too old after 1-2 hours. (we have undo_retention to 3 hours). We used parallel 4 for create and parallel 4 for select. with nologging and multiblock_count=128;
it is quite sure LOB is slowing down the SQL. Is there any way to speed up the CTAS? Or we can use alter table move subpartition to move (did not test it yet as can't play with prod), but suspect it should allso be very slow.
Is there any method to speed up the reorg(either move or ctas, and then exchange subpartition?), also it looks like the SQL is failing at LOB read consistency, is there any way to prove the LOB is causng the snapshot too old?
--
Regards
Zhu Chao
<http://www.cnoug.org>www.cnoug.org
Hemant K Chitale http://web.singnet.com.sg/~hkchital
-- //www.freelists.org/webpage/oracle-l
