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.
Syntax of TRUNCATE TABLE
TRUNCATE TABLE [schema_name.]table_name [PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG] [DROP STORAGE | REUSE STORAGE];
- Name of the schema to which the table belongs. It is optional.
- 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 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 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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!