1. 现象：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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. 原因：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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. 解决方法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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’，才是表明统计信息是锁定的。