In occasions we need have tables that are not modified in its structure by anyone, to do this, the first thing to do is configure or define a policy-based security schemes, privileges and roles, this should be sufficient to that can not be altered structures. We will see another method, less known and not almost never used, and also serves to perform such functions. We can even say that goes beyond, because although we have privileges on a table do not want to touch it by accident.
Oracle provides the clause “DISABLE TABLE LOCK” to perform this function, this tells the engine that must disable table locks, which are necessary to perform actions of type DDL.
The command is as follows;
ALTER TABLE TAB1 DISABLE TABLE LOCK;
DDL operations as below will be failed;
- DROP TABLE TAB1
- ALTER TABLE TAB1 MODIFY COL1 VARCHAR2(5)
- ALTER TABLE TAB1 DROP COLUMN COL1
- TRUNCATE TABLE TAB1
If you run any of the above commands will receive the following error;
ORA-00069: can not acquire lock – table locks disabled for tab1
Although in my opinion, add a column to a table is a DDL command (it should fail), in this case for Oracle is possible to change the table structure.
HTH – Antonio NAVARRO