Oracle 11gR2: Truncate Drop Storage Vs Drop All Storage Clauses

advertisements

_____________________________________________________________________________________________________________________

Drop Storage clause is to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter in the table. This deallocated space will move to free space category and it can be used for the other objects in the tablespace. So in this drop storage clause there would be some space which is equal to the MINEXTENTS used for the table after the truncate command execution.

11gR2 Drop All Storage clause will deallocate all the space allocated for the table including deleted rows space and MINEXTENTS space. All the segments allocated for the table will be deallocated after the truncate command execution. This option is available from Oracle 11g Release 2.

See some Examples Here.

I am getting weird result in Oracle 11.2.0.3. As per oracle it should deallocate all the space allocated for the table if you specify drop all storage clause. But I am seeing a different result in Oracle 11.2.0.3.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 22 08:37:17 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select * from v$version;

BANNER

-------------------------------------------------------------

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

SQL> conn user1/user1

Connected.

SQL> show parameter def

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------------------

deferred_segment_creation            boolean     TRUE

SQL> create table tst_tbl as select * from all_tables where rownum<10000;

Table created.

SQL> select sum(bytes)/1024/1024 from user_segments

where segment_name ='TST_TBL';

SUM(BYTES)/1024/1024

--------------------

                 .75

SQL> truncate table TST_TBL;

Table truncated.

SQL> select sum(bytes)/1024/1024 from user_segments

where segment_name ='TST_TBL';

SUM(BYTES)/1024/1024

--------------------

               .0625

SQL> drop table tst_tbl purge;

Table dropped.

SQL>  create table tst_tbl as select * from all_tables

where rownum<10000;

Table created.

SQL> truncate table TST_TBL drop all storage;

Table truncated.

SQL> select sum(bytes)/1024/1024 from user_segments

where segment_name ='TST_TBL';

SUM(BYTES)/1024/1024

--------------------

               .0625

It must be a null value as per oracle as it will delete all the extents. I have made the deferred_segment_creation parameter value to FALSE also. But it is giving the same result. L

_____________________________________________________________________________________________________________________

Website Stats