Re: Truncate a range-list partitioned table

Alex Schonlinner schrieb:
> Hi,
>
> I have a problem with a range-list partitioned table:
>
> Oracle Version: 10.2.0.2 on HP/UX 64 bit
>
> Suppose the following:
> There is a range-list partitioned table with 4 range partitions, each
> range partitions has 7 list partitions. We have uniform sized
> tablespaces. The table lies on a tablespace whose segments are 100M in
> size.
>
> So after we truncate that table it still occupies 4*7*100M, i.e. round
> about 3 GB space although there is no data in it. This is clear.
>
> So I wanted to reduce the amount of space needed for the empty table:
> We have another tablespace with 100K extent sizes. So I tried to move
> the table to that tablespace by issuing the following:
> truncate table test_table;
> alter table test_table move subpartition part1_1 tablespace DEF_100K
> ....
> alter table test_table move subpartition part4_7 tablespace DEF_100K
>
> And guess what? Each subpartition now has 1000 extents: Oracle placed
> the complete empty 100M segment onto the 100K segment tablespace, thus
> 1000 extents, thus the table still occupies about 3 GB.
>
> I tried everything I can think of, but nothing helps reducing the
> number of extents: I tried another truncate table test_table, tried to
> truncate the subpartitions separately, with shrink space, etc., nothing
> helps.
>
> Am I doing it the wrong way?
>
> Regards,
> Alex
>

Both,
ALTER TABLE test_table MODIFY SUBPARTITION part1_1 DEALLOCATE UNUSED KEEP 0;
or
ALTER TABLE test_table DEALLOCATE UNUSED KEEP 0;

should work.

Best regards

Maxim Received on Fri Aug 18 2006 - 07:56:52 CDT