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’，才是表明統計資訊是鎖定的。
