TRUNCATE TABLE in Oracle

previous-topic-oraclemine                                                                                                   next-topic-oraclemine

There have been plenty of situations when you needed to empty the table data and fill it with fresh set of data. To perform this action truncate table statement is very useful.

Oracle classifies TRUNCATE as DDL (Data Definition Language) statement.

TRUNCATE TABLE

Syntax of TRUNCATE TABLE

TRUNCATE TABLE [schema_name.]table_name
[PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG]
[DROP STORAGE | REUSE STORAGE];
Parameters Explanation

schema_name

  • Name of the schema to which the table belongs. It is optional.

table_name

  • Name of the table you want to delete. It is mandatory.

PRESERVE MATERIALIZED VIEW LOG

  • If this option is specified then materialized view log is preserved when the table is truncated. By default this option is enabled. It is optional.

PURGE MATERIALIZED VIEW LOG

  • If this option is specified then materialized view log is purged when the table is truncated. It is optional.

DROP STORAGE

  • Drop storage option deallocate and release the extents allocated to the table after truncation. By default the drop storage option is on during truncation. It is optional.

REUSE STORAGE

  • Reuse storage option prevents the release of extents allocated to the table for future re-usability.

Example of TRUNCATE TABLE

TRUNCATE TABLE employees;

This query removes all the records from table employees. Rollback is not possible for these records.

I hope you like the page and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

previous-topic-oraclemine                                                                                                   next-topic-oraclemine

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.