Re: truncate partition drop storage doesn't deallocate space

dinos5_at_yahoo.com (cc) wrote in message news:<7c675a3b.0306120102.758158e7_at_posting.google.com>...
> Hi all,
>
> I am trying to truncate a partiton which has 4 extents. After issuing
> alter table... truncate partition ... drop storage statement I see
> again 4 extents for this partition in user_extents ...
>
> parition's minextent value = 1
>
> any idea?
>
> thanks in advance.

What version of ORACLE?
Are you querying the partitions from the same 'segment'? No problem with 9i.

SQL> select EXTENT_ID,blocks from user_extents where SEGMENT_NAME='PAR_TEST' and PARTITION_NAME='P2 ';

 EXTENT_ID BLOCKS
---------- ----------

         0          8
         1          8
         2          8
         3          8
         4          8

SQL> alter table par_test truncate partition p2 drop storage;

Table truncated.

SQL> select EXTENT_ID,blocks from user_extents where SEGMENT_NAME='PAR_TEST' and PARTITION_NAME='P2 ';

 EXTENT_ID BLOCKS
---------- ----------

         0 8

SQL> SQL> select EXTENT_ID,blocks from user_extents where SEGMENT_NAME='PAR_TEST' and PARTITION_NAME='P3 ';

 EXTENT_ID BLOCKS
---------- ----------

         0          8
         1          8
         2          8
         3          8
         4          8

SQL> alter table par_test truncate partition p3 reuse storage;

Table truncated.

SQL> select EXTENT_ID,blocks from user_extents where SEGMENT_NAME='PAR_TEST' and PARTITION_NAME='P3 ';

 EXTENT_ID BLOCKS
---------- ----------

         0          8
         1          8
         2          8
         3          8
         4          8

SQL>

• Jusung Yang