Prevent table from altering

Recently I had a problem where I needed to prevent dropping or altering some tables. Initially I have created db trigger which was monitoring this activities and then allowed me to see who and what happened. This was a pretty bad workaround because I was unable to prevent altering tables/indexes but just to see what happened. And then later fix things manually.

The worst of all was that all mine intervention was after the damage happened so I was just fixing the problem - not preventing them, as it would be nice.

For the first time this was all I could do (as I thought) ... until today when I found alter table ... disable table lock statement, which prevent DDL operations on the table. And this was it-the better solution for mine problems.

The solution

Usage is more then easy ... Let me show some examples.

SQL> CREATE TABLE SPECIAL_TABLE (COL1  NUMBER, COL2  VARCHAR2(10 CHAR));

Table created.

Alter table, to disable table locks:

SQL> alter table SPECIAL_TABLE disable table lock;

Table altered.

SQL>

Now your table is secured against any kind of DDL actions. See following examples what is prohibited:

1. Table can't be droped (!!!)

   SQL> drop table SPECIAL_TABLE;
   drop table SPECIAL_TABLE
              *
   ERROR at line 1:
   ORA-00069: cannot acquire lock -- table locks disabled for SPECIAL_TABLE
   

   SQL> drop table SPECIAL_TABLE cascade constraints;
   drop table SPECIAL_TABLE cascade constraints
              *
   ERROR at line 1:
   ORA-00069: cannot acquire lock -- table locks disabled for SPECIAL_TABLE
   

2. Index can't be added

   SQL> CREATE INDEX SPECIAL_TABLE_IX ON SPECIAL_TABLE
     2  (COL1)
     3  LOGGING
     4  PCTFREE    0
     5  ;
   CREATE INDEX SPECIAL_TABLE_IX ON SPECIAL_TABLE
                                     *
   ERROR at line 1:
   ORA-00069: cannot acquire lock -- table locks disabled for SPECIAL_TABLE
   

3. Columns can’t be modified

   SQL> alter table SPECIAL_TABLE modify col2 varchar2(100 CHAR);
   alter table SPECIAL_TABLE modify col2 varchar2(100 CHAR)
   *
   ERROR at line 1:
   ORA-00069: cannot acquire lock -- table locks disabled for SPECIAL_TABLE
   

4. Columns can’t be dropped

   SQL> alter table SPECIAL_TABLE drop column col2;
   alter table SPECIAL_TABLE drop column col2
   *
   ERROR at line 1:
   ORA-00069: cannot acquire lock -- table locks disabled for SPECIAL_TABLE
   

But in the same time almost everything for normal work is allowed:

1. Columns can be added

   SQL> alter table SPECIAL_TABLE add col3 varchar2(10 CHAR);
   
   Table altered.
   
   SQL> desc  SPECIAL_TABLE
    Name                 Null?    Type
    -------------------- -------- ------------------
    COL1                          NUMBER
    COL2                          VARCHAR2(10 CHAR)
    COL3                          VARCHAR2(10 CHAR)
   

2. INSERT/UPDATE/DELETE/SELECT FOR UPDATE are allowed

   SQL> insert into SPECIAL_TABLE values (1,'One');
   
   1 row created.
   
   SQL> commit;
   
   Commit complete.
   
   SQL>
   

   SQL> update SPECIAL_TABLE set col2='change one' where col1=1;
   
   1 row updated.
   
   SQL> commit;
   
   Commit complete.
   
   SQL> select * from SPECIAL_TABLE for update;
   
         COL1 COL2
   ---------- ----------
            1 One
            
   SQL> select * from SPECIAL_TABLE;
   
         COL1 COL2
   ---------- ----------
            1 change one
   
   SQL>
   

   SQL> delete SPECIAL_TABLE;
   
   1 row deleted.
   
   SQL> commit;
   
   Commit complete.
   
   SQL>
   

3. table can be truncated

   SQL> truncate table SPECIAL_TABLE drop storage;
   
   Table truncated.
   

Experiment further for some other special situations that you might have.

The end

I as always in Oracle world, every new day is a good day to learn something. This was really nice feature to implement in a cases like described-clean and efficient. The best of alll is that even dba (sysdba) cannot alter table before re enable table locks) so this is really nice.

If you need to make some DDL (like adding an index), just re enable table locks:

SQL> alter table SPECIAL_TABLE enable table lock;

Table altered.

and do the job. Later again, disable table lock to place table in protected state.

Hopes this helps someone.

Cheers!