Truncate a range-list partitioned table

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 Received on Fri Aug 18 2006 - 07:12:11 CDT