• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to reclaim entire space of an oracle database table with “Truncate Table” statement

by admin

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

In the previous release 11gR1 when a truncate was executed Oracle keeps the segment associated with a table. Oracle 11gR1 does provide the DROP STORAGE and REUSE STORAGE clauses but the DROP STORAGE clause only drops extents beyond the minimum extents. Starting with Oracle 11gR2 (11.2.0.2), a TRUNCATE statement can also specify the DROP ALL STORAGE clause to release the space currently allocated for a table to the containing tablespace.

For example:

SQL> create table TEST01 storage (MINEXTENTS 3)
     as select * from dba_source;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

EXTENTS
-----------
67

The “DROP STORAGE” clause is used by default:

SQL> truncate table TEST01;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

EXTENTS
----------
3

Same truncate with “DROP ALL STORAGE” deletes all extents:

SQL> truncate table TEST01 DROP ALL STORAGE;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

no rows selected

The object does not have a segment because deferred segment creation is true. A new extent will be allocated when a new row is inserted on that particular table.

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. MUTATING Table Error and How to Resolve it (ORA-04091)
  2. Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
  3. Script/Queries to Monitor Temporary (TEMP) Tablespace Usage in Oracle Database
  4. Beginners Guide to Oracle Database In-Memory in RAC
  5. How to duplicate a Oracle Database to a previous Incarnation
  6. Oracle Tablespace Transport for a Single Partition
  7. Beginners Guide to Oracle Temporary Tablespace Groups
  8. How To Change SYS user password for oracle database instance
  9. Oracle Interview Questions – Flash Recovery Area
  10. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright