changing database object extents ? how ?

[viztek]

Hello,

As u all must be aware
select * from user_extents;
gives the space allocatted to user objects.
One such table has a size of 500 MB.

I need to change the size to some what lesses size.

I want to do it without dropping anything.
Is there anything like alter table object with options to change its extents ??

Please help !

 

[rcurva]

The closest that could suit your requirement is to deallocate unused space for table, index, and cluster.

The syntax is;

ALTER TABLE test_table DEALLOCATE UNUSED;
ALTER TABLE test_table DEALLOCATE UNUSED KEEP 100M;

The KEEP parameter specifies the number of bytes above the
high water mark the object needs to retain, even if there are no rows in that space.

But, if you really want to decrease the extents, dropping is inevitable.

Export-drop-Import

 

[carp]

Or export/TRUNCATE/import (assuming the extent you want to change isn't your first extent).