Enable or Disable Table Locks in Oracle Database

Table Locks are an important part of Oracle Working Mechanism as they provide security from unwanted deletion or modification of table’s data which can sometimes cost you more than you can think. Whether to enable or disable a table lock depends upon your requirement. If you are allowing your users to delete or drop the table, then enable the table locks. If you only want them to perform DML (Data Manipulation Language) operations like INSERT, UPDATE or DELETE on the tables, then disable the table locks.

Note: In case of Disabled Table Locks, you can’t update or remove a column name but you can add a new column in the table.
SQL Query to Disable Table Locks:

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 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:

SQL> Select * from dba_2pc_pending;
SQL> Select * from dba_2pc_neighbors;

Ref: https://www.dba-career.com/2015/05/enable-or-disable-table-locks-in-oracle.html

Related posts: