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.

How to Add Conditions After a Where Clause

I went on an Oracle 9i DBA Performance and Tuning course in 2005 and was looking through the course notes recently to find something to blog about. They suggested that if you had conditions after a WHERE clause joined by AND, you should put the test which was most likely to fail first. This would then save Oracle the bother of evaluating the subsequent condition(s). This seemed reasonable so I decided to try it out.

I copied the contents of DBA_TABLES into a table of my own called T1 and duplicated its contents repeatedly until it had over three million rows. Then I checked that every row had TABLE_LOCK set to ENABLED and that no rows had an owner called BLAH.

SQL> create table t1 as select * from dba_tables

  2  /

 

Table created.

 

SQL> begin

  2  for a in 1..11 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select count(*) from t1

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where table_lock = 'ENABLED'

  2  /

 

  COUNT(*)

----------

   3217408

 

SQL> select count(*) from t1 where owner = 'BLAH'

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I ran the first SELECT statement as follows. If the course notes were correct, the first condition should reject every row and the second condition should never be evaluated:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  and table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     13.83

 

SQL>

Then I ran the second SELECT statement shown below. It was identical to the first but the conditions were swapped round. If the course notes were correct, the first condition should accept each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times and got similar results each time. So far so good:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  and owner = 'BLAH'

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.27

 

SQL>

The course notes also suggested that if you had conditions after a WHERE clause joined by OR, you should put the test which was most likely to succeed first. This would then save Oracle the bother of evaluating the subsequent condition(s). I decided to try this out too, using the table T1, which I created above.

I ran the third SELECT statement like this. If the course notes were correct, the first condition should accept every row and the second condition should never be evaluated: 

SQL> alter system flush shared_pool

  2  / 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where table_lock = 'ENABLED'

  3  or owner = 'BLAH'

  4  /

  COUNT(*)

----------

   3217408

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  / 

  CPU Used

----------

     14.17 

SQL>

Finally, I ran the fourth SELECT statement. It was identical to the third but the conditions were swapped round. If the course notes were correct, the first condition should reject each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times as well and got similar results each time. I think this demonstrated that the course notes were correct:

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> conn /

Connected.

SQL> select count(*) from t1

  2  where owner = 'BLAH'

  3  or table_lock = 'ENABLED'

  4  /

 

  COUNT(*)

----------

   3217408

 

SQL> select a.value/100 "CPU Used"

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU Used

----------

     14.79

 

SQL>