• 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 Roll Forward a standby database using RMAN incremental backup in 11g
  2. Oracle 12c: Migrate non-CDB and Convert to a PDB using 12c RMAN Active Database Duplication
  3. ORA-39170: Schema expression ‘OPS’ does not correspond to any schemas
  4. IPv4 Subnet Basics and Oracle Clusterware
  5. How to determine the required archivelog files needed for a guaranteed restore point before running flashback database
  6. How to relocate the redo log files to a different location on disk
  7. Oracle Database : How to set Environment Variables Using Srvctl
  8. Oracle 11g – New ASM features
  9. How to Restore a Dropped Pluggable Database (PDB) in Multitenant Environment
  10. Oracle RAC Interview Questions – Highly Available IP (HAIP)

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright