Re: Truncate a range-list partitioned table

Alex Schonlinner wrote:
> 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

This has little to do with the problem as you present it, but ...

Questions:
How did you arrive at a 100K extent size? What is the block size of the database and of the tablespaces of interest?
What is the underlying RAID stripe size? What is the value for the parameter db_file_multiblock_read_count?

100 KB extent sizes sounds like a hold-over from v7.3 days.

-bdbafh Received on Fri Aug 18 2006 - 12:18:11 CDT