Use Truncate to drops the segment

These TRUNCATE options are:

DROP STORAGE, the default option, reduces the number of extents allocated to the resulting table to the original setting for MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.

DROP ALL STORAGE drops the segment. In addition to the TRUNCATE TABLE statement,
DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE (SUB)PARTITION statement.
This option also drops any dependent object segments associated with the partition being truncated.

DROP ALL STORAGE is not supported for clusters.

Note:
This functionality is available with Oracle Database 11g release 2 (11.2.0.2).

    TRUNCATE TABLE emp DROP ALL STORAGE;
    

REUSE STORAGE specifies that all space currently allocated for the table or cluster remains allocated to it.
For example, the following statement truncates the emp_dept cluster, leaving all extents previously allocated
for the cluster available for subsequent inserts and deletes:

    TRUNCATE CLUSTER emp_dept REUSE STORAGE;
    
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s