1. Symptom:
The code is as follows: |
Copy code |
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. Cause:
The code is as follows: |
Copy code |
SQL>! Oer [oracle @ rac1 ~] $ Oerr ora 69 00069,000 00, "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,201 3, Oracle. All rights reserved. Connected: 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. Solution:
The code is as follows: |
Copy code |
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> |
Further investigation reveals that DBAs mistakenly regard table_lock as the statistic information to determine whether to lock the field. The table_lock of most tables that do not lock the statistics information is enable, therefore, you can use alter table disable table lock to lock statistics.
To check whether the statistics are locked, dba_tab_statistics.stattype = 'all' indicates that the statistics are locked.