• 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

Truncate Table Statement: REUSE STORAGE VS DROP STORAGE

by admin

Question: If we want to truncate any large table and reclaim space, which is the truncate command out of below 3 which can be used?

TRUNCATE TABLE table_name;

OR

TRUNCATE TABLE table_name DROP STORAGE;

OR

TRUNCATE TABLE table_name REUSE STORAGE;

REUSE STORAGE VS DROP STORAGE

Here is the difference between drop and reuse storage:

DROP STORAGE

Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.

REUSE STORAGE

Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.

So, if you want to release the space to tablespace you can use drop else you can use reuse.

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Disable os-prober in CentOS/RHEL 7
  2. How to use Flashback Database in Oracle Data Guard Configuration
  3. How to Change Timezone Settings on Exadata
  4. Whats is PL/SQL
  5. Queries to find out the SQL which is using these temporary tablespace
  6. How to configure Partitioned Block Devices (Non-ASMLIB) And Assign Them To ASM
  7. How to Resize the Undo Tablespace in Oracle Database
  8. How to move a Datafile to a different Location on a Physical Standby Database
  9. How to set the db_file_name_convert and log_file_name_convert parameters
  10. How long does Oracle retain the statistics for

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright