Not Able To Truncate Table – Why? What to do then?

It is a strange fact that Oracle will not permit a user to truncate a table even after having “ Delete any table “ privilege. The privilege required to truncate a table is Drop any table or Drop on that particular table .

With respect to the security perspective it is not advisable to grant Drop Table privilege to any user. But, we have two workarounds with which a user can truncate a table without giving drop table privilege.

>> Create a procedure to truncate the table and grant execute privilege on the procedure to the user.

>> User can execute delete from table command without a where clause, which will delete all the rows from the table.(Once committed, the changes cannot be rolled back as in case of Truncate, but keep in mind that , since we have performed DELETE and not TRUNCATE, this operation won’t lower the High Water Mark.)

Research and Article by : Anju