Note: Deletes perform
normal DML. That is, they take locks on rows, they generate redo (lots of it), and they
require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If
a mistake is made a rollback can be issued to restore the records prior to a commit. A
delete does not relinquish segment space thus a table in which all records have been
deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the
table back to zero. No row-level locks are taken, no redo or rollback is generated.
All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to
anything other than 1, then that number of extents is retained rather than just the
initial). By re-positioning the high water mark, they prevent reading of any table data,
so they have the same effect as a delete, but without all the overhead. Just one slight
problem: a truncate is a DDL command, so you can't roll it back if you decide you made a
mistake. (It's also true that you can't selectively truncate -no "WHERE" clause
is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so
they it has the same effect as a delete, but without the overhead. There is, however, one
aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a
COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is
Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified.
Oracle Database SQL Reference documentation for versions 11.1, 10.2, 10.1
and 9.2 all state that "DROP STORAGE" is default option for TRUNCATE. That is:
"DROP STORAGE: Specify DROP STORAGE to deallocate all space from the deleted
rows from the table or cluster 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 also sets the NEXT storage parameter
to the size of the last extent removed from the segment in the truncation process. This is the
If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment.
This procedure was provided to the library by JP Vijaykumar. The second parameter in this function, "p__storage BOOLEAN DEFAULT FALSE" was suggested by Mariusz Mroz, who indicated that it's important for huge tables and full table scans operations.
In applications, users need truncate privileges on third party tables. The owner of the table can truncate his/her own tables.
A user with DBA role granted can truncate a third party table. Any user with "drop any table" system privilege can truncate a third
It is not recommended in Production environments to grant powerful roles like DBA role or privileges like "drop any table" privilege
to accomplish this purpose.
This procedure need to be created in each schema. The schema owner need to grant execute permission to the user,
and the delete permission on specified tables.
Then the user can execute the procedure to truncate tables User A owns the table