Home » RDBMS Server » Performance Tuning » Protecting a Table from Truncated or Delete
Protecting a Table from Truncated or Delete [message #65645] Sun, 21 November 2004 20:14 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Hi All,

I have seen this script provided by Mahesh in reply to the Question (Logging DDL statements, etc)(Page No.-9) on this forum.

mag@mutation_mutation > truncate table emp;
truncate table emp
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20111: truncate is not allowed .... table is protected by DBA
ORA-06512: at line 7

My question is how we can protect a table from Truncate or Delete. Are there any grants or privileges that are to be revoked from the user so that he will not be able to perform Delete or Truncate.

Pls. calrify for the same.

Re: Protecting a Table from Truncated or Delete [message #65646 is a reply to message #65645] Sun, 21 November 2004 23:04 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
Revoking drop any table would be a good start to prevent truncating. (assuming the user is not the owner of the table)
Not granting delete on the table will prevent deletion (d'oh), again assuming the user is not the owner.
To prevent the owner of the table from deleting rows or truncating (bad idea, better not use this user in your application!) you'd have to write ddl-triggers.
(See docs)

Re: Protecting a Table from Truncated or Delete [message #65648 is a reply to message #65645] Mon, 22 November 2004 08:07 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
have a look here..and also with the follow-ups on the posting.

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic: Intial Extent parameter for Indexes
Next Topic: Data insertion in LOBs cause table to Grow Rapidly
Goto Forum:

Current Time: Tue Sep 29 08:24:37 CDT 2020