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.
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;