1. 现象：
| 代码如下 |
复制代码 |
|
SQL> drop user test cascade;
drop user test cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for T1
SQL> conn test/test
Connected.
SQL>
SQL>
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
A DATE
SQL>
SQL>
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1
|
2. 原因：
| 代码如下 |
复制代码 |
|
SQL> !
oer[oracle@rac1 ~]$ oerr ora 69
00069, 00000, "cannot acquire lock -- table locks disabled for %s"
// *Cause: A command was issued that tried to lock the table indicated in
// the message. Examples of commands that can lock tables are:
// LOCK TABLE, ALTER TABLE ... ADD (...), and so on.
// *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry
// the command.
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 19 11:29:19 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> select owner,table_name,table_lock from dba_tables where table_name='T1'
SQL> /
OWNER TABLE_NAME TABLE_LO
------------------------------ ------------------------------ --------
TEST T1 DISABLED
SQL>
SQL>
SQL>
SQL>
|
3. 解决方法：
| 代码如下 |
复制代码 |
|
SQL> alter table test.t1 enable table lock;
Table altered.
SQL>
SQL> select owner,table_name,table_lock from dba_tables where table_name='T1';
OWNER TABLE_NAME TABLE_LO
------------------------------ ------------------------------ --------
TEST T1 ENABLED
SQL>
SQL> drop user test cascade;
User dropped.
SQL>
|
再进一步查，发现是DBA误将table_lock认为是看做统计信息是否锁定字段，看到大部分没有锁定统计信息的表的table_lock都是enable，所以通过alter table disable table lock来锁定统计信息。
其实看统计信息是否锁定，要看dba_tab_statistics.stattype=’ALL’，才是表明统计信息是锁定的。
