聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

drop user cascade报错ora-69

2016-05-19 22:50 浏览: 1133367 次 我要评论(0 条) 字号:

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



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复