Re: how to prevent drop on a specific table

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 26 Jun 2006 15:34:30 -0700
Message-ID: <1151361273.441960@bubbleator.drizzle.com>

PMDORAIS_at_gmail.com wrote:

> Anurag Varma a écrit :
> 

>> PMDORAIS_at_gmail.com wrote:

>>> DA Morgan a écrit :
>>>
>>>> PMDORAIS_at_gmail.com wrote:
>>>>> HI, i'm not a big wiz with oracle but i would like to prevent dropping
>>>>> a specfic table only for all users.
>>>>>
>>>>> How can i do that??  ps. i use Oracle9i Enterprise Edition Release
>>>>> 9.2.0.5.0 .
>>>>>
>>>>>
>>>>> Thanks!!!!
>>>> There are code examples of how to prevent this with DDL Event
>>>> Triggers in Morgan's Library at www.psoug.org. Look under DDL
>>>> Event Triggers.
>>>>
>>>> Also look at the same technique for preventing TRUNCATE and ALTER.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damorgan_at_x.washington.edu
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Group
>>>> www.psoug.org
>>>
>>> *****
>>>
>>> WHAT A GREAT SITE!!!  Thanks a lot!!!  Works like a charm!!  here is my
>>> script, if someone needs to do the same:
>>>
>>> CREATE OR REPLACE TRIGGER DBM_NODROP
>>> BEFORE DROP
>>> ON SCHEMA
>>>
>>> DECLARE
>>>  x user_tables.table_name%TYPE;
>>> BEGIN
>>>   SELECT ora_dict_obj_name
>>>   INTO x
>>>   FROM dual;
>>>
>>>   IF x ='P_TEST' THEN
>>>     RAISE_APPLICATION_ERROR(-XXXXX, 'Table Names Can Not
>>>     BE DELETED');
>>>   END IF;
>>> END no_xtabs;
>>> /

>> ORA92> create table p_test (a number);

>>

>> Table created.

>>

>> ORA92> drop table p_test;
>> drop table p_test
>> *
>> ERROR at line 1:
>> ORA-00604: error occurred at recursive SQL level 1
>> ORA-20001: Table Names Can Not
>> BE DELETED
>> ORA-06512: at line 8

>>
>>

>> ORA92> rename p_test to b_test;

>>

>> Table renamed.

>>

>> ORA92> drop table b_test;

>>

>> Table dropped.

>>

>> ORA92> create table p_test (a number);

>>

>> Table created.

>>

>> ORA92> alter trigger DBM_NODROP disable;

>>

>> Trigger altered.

>>

>> ORA92> drop table p_test;

>>

>> Table dropped.

> ****
> 
> i know... but i just wanted to protect the table to be dropped by
> inadvertency....
> 
> so it is fine with me!!!

As I point out in my post you can use DDL triggers to prevent the renaming and prevent the ALTER TRIGGER too.

Well constructed event triggers are very difficult to circumvent.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jun 26 2006 - 17:34:30 CDT