How to prevent table from Altering

How to prevent table from Altering

If you need to secure your table from any kind of DDL actions then following example will help you to understand actually what happens when locks are disabled on the TABLE.

SQL> alter table temp_payroll disable table lock;
Table altered.

Now no one can drop as well as truncate the table as table locks are disabling.
SQL> drop table temp_payroll;
drop table temp_payroll
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

SQL> truncate table temp_payroll;
truncate table temp_payroll
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

Also you are not able to modify and drop the column but you are able to add the column.

SQL> alter table temp_payroll modify amount Number (10,2);
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

SQL> alter table temp_payroll drop column Amount Number (10,2)
alter table temp_payroll drop column Amount Number (10,2)
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

But you are able to run the DML operation such as insert/update/delete.
SQL> delete from temp_payroll;
1 row deleted.

SQL> alter table temp_payroll add amount Number (10,2);
Table altered.

You can easily enable the table lock to perform any kind of DDL operation again.

SQL> alter table temp_payroll enable table lock;
Table altered.

SQL> drop table temp_payroll;
Table dropped.

Enable table lock allowing DDL operations on the table. All currently executing statements must commit or rollbacks before oracle database enable the table lock. To check the pending transactions:

Select * from dba_2pc_pending;

Select * from dba_2pc_neighbors;