Truncate the table and table partition in Oracle

Leave a reply

Remove data from table and table partition in Oracle

Truncate statement is used to remove all data from a table. It is worked same as DELETE statement but delete statement also have option to use WHERE clause to limits the delete rows.
Syntax

TRUNCATE TABLE

DROP|REUSE STORAGE;

-- Example
SQL> TRUNCATE TABLE emp;


DROP STORAGE: DROP STORAGE to deallocate all space from the deleted rows from the table.

TRUNCATE TABLE emp DROP STROAGE;

REUSE STORAGE: If you don’t want the TRUNCATE statement to de-allocate the currently allocated extents.

TRUNCATE TABLE emp REUSE STORAGE;

TRUNCATE the partition in Partition table
TRUNCATE statement is often used when working with partitioned tables.

ALTER TABLE f_sales TRUNCATE partition p_2012;

Preserve Materialized View Log

TRUNCATE TABLE f_sales PRESERVE MATERIALIZED VIEW LOG;

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

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