ORA-00069: Cannot Acquire Lock -- Table Locks Disabled

刘泽宇

现象：

Drop table  generates ora-69 error:



drop table <MYTAB> force

*

ERROR at line 1:

ORA-00069: cannot acquire lock -- table locks disabled for MYTAB

Then when we try to enable the table lock, it hangs:

alter table <MYTAB> enable table lock;

To diagnose:

Run query to get other objects owned by the same schema:

sql>

select object_name, object_id

from dba_objects

where owner='<schema owner of problem table>'

and object_type='TABLE';

Then select any 2 objects other than the problem table to include in the query below:



sql>

select x. object_name,obj#, flags

from sys.tab$,(

select object_name, object_id

from dba_objects

where owner='<ME>'

and object_name

in ('<MYTAB>','<YOURTAB>','<OURTAB>')

and object_type = 'TABLE') x

where obj# = x.object_id;



OBJECT_NAME     OBJ#    FLAGS

------------------------------ ------------ ------------

OURTAB                78348    1073742353

YOURTAB              78350    1073742353

MYTAB                   78535    1073742355

Note:

The other 2 objects selected are just used as a point of reference, so it could be any table owned by same schema.

原因：

Error is caused by inconsistent value in the flag column of the object in tab$.

The flag value of the object MYTAB in tab$ is 1073742355 instead of 1073742353 seen in the other objects.

Note:

The flag value will vary by database, thus the use of the 2 other objects.

处理方法：

To re-enable table locking, all active sessions must complete.  This is best accomplished by:

1] shutdown database gracefully
2] startup restrict
3] alter table <MYTAB> enable table lock;
4] alter system disable restricted session;