一聚教程网:一个值得你收藏的教程网站

热门教程

oracle使用drop user cascade报错ora-69解决办法

时间:2022-06-29 09:39:21 编辑:袖梨 来源:一聚教程网

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’,才是表明统计信息是锁定的。

热门栏目