How to Stop A Table Being Dropped Or Truncated?
January 19th, 2006 By David Aldridge
DDL operations on a table require that an exclusive table lock be taken — this applies to all sorts of operations, such as dropping it, truncating it, dropping an index (although not adding one, curiously enough), exchanging partitions …
You can prevent a table lock from being taken by:
ALTER TABLE MY_TABLE DISABLE TABLE LOCK
This will also prevent other operations that require a table lock though, so use with caution if your application needs to truncate the table, perform partition operations, etc.
January 20th, 2006 at 12:40 am
>> This will also prevent other operations that require a table lock though,
Please list them,
thanks for the nice tip
January 20th, 2006 at 8:11 am
If your application performs any kind of DDL on a table, or you are not sure whether it does, then don’t use this technique.
You can find more information on DDL locks here:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref2101
However, I wouldn’t rely on a documented list, even if one exists somewhere. Always test for yourself.
January 24th, 2006 at 11:49 pm
For safety against dropping a table there is a much better way to implement this. You have to create a dummy table having one field only of length about 30 characters. Insert required table names in this table. And now use a before drop trigger where you will first look into this table to see and match table names.
However, this will give you protection against in-advertantly dropping of tables but not against truncating.