ORA-00069

A colleague asked what the table_lock column in dba_tables was for. I did not know so I decided to investigate.First I looked at the distribution of table_lock values in one of our test databases:

SQL> select table_lock, count(*)
  2  from dba_tables
  3  group by table_lock
  4  /

TABLE_LOCK  COUNT(*)
---------- ----------
ENABLED          825

SQL>
 

This suggested to me that the default setting is enabled so I tried this out by creating a test table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> 

As expected, the table_lock column was set to enabled for this new table:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
ENABLED

SQL> 

You can alter this as follows:

SQL> alter table andrew disable table lock
  2  /

Table altered.

SQL>

And this sets the table_lock value to disabled:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
DISABLED

SQL> 

Setting table_lock to disabled stops you locking that table:

SQL> lock table andrew in share mode
  2  /
lock table andrew in share mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL> 

It also stops you running other DDL against it:

SQL> rename andrew to fred
  2  /
rename andrew to fred
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL>

And setting it back to enabled allows you to lock the table:

SQL> alter table andrew enable table lock
  2  /

Table altered.

SQL>

And run other DDL on the table:

SQL> rename andrew to fred
  2  /

Table renamed.

SQL>

So the main purpose of the table_lock column is to show whether you are allowed to lock a table or not.