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!

Is Apex in use?

Application Express (APEX) is installed by default with the 11.1 and higher database. This document describes a couple of methods that can be used to determine whether APEX is in use, or if it can be deleted.

Solution

Determine the number of workspaces in the instance by logging in to SQLPLUS as either SYS or SYSTEM and run the following query:

select workspace, workspace_id from apex_workspaces;

This should return a single workspace: INTERNAL / 10 for a brand new installation. If only this workspace is returned, then you can be reasonably certain that this installation is not actively being used, since there are no applications defined. If the query returns more than one workspace, then that indicates that this installation has been active at some point in time.
Then run next query as SYS

select workspace, view_date, seconds_ago from apex_workspace_activity_log;

This will show if anyone has used APEX in the past. For non active Apex, no rows should be returned.
If both queries return mentioned results you might suppose that Apex is not in used. But to be sure, you have to make another check.
Check dba_source (and all other sources that you have) that they do not reference something like %WWV_FLOW% or %APEX% in int's content.

The End

Assuming that there are no indications that the installation is active, you can remove APEX by following the instructions Application Express Installation Guide > Cleaning Up After a Failed Installation > Removing Oracle Application Express from the Database.

Hope this helps someone.

Cheers!