作者归档：惜分飞

在oracle数据库中删除用户遭遇ORA-00069: cannot acquire lock — table locks disabled for HR_XXX_01错误

SQL>  drop user XFF cascade;
 drop user XFF cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01

关于ORA-00069错误解释

[oracle@xifenfei.com ~]$ oerr ora 00069
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.

尝试lock表,直接hang,强制终止

SQL> alter table XFF.HR_XXX_01 enable table lock; 



^Calter table XFF.HR_XXX_01 enable table lock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

查询tab$.flags的值

SQL> col object_name for a30
SQL> set lines 150
SQL> select x. object_name,obj#, flags
  2  from sys.tab$,(
  3  select object_name, object_id
  4  from dba_objects
  5  where owner='XFF'
  6  and object_name in ('HR_XXX_01','HR_XXXCONTROL','XXXLZB_JD1')
  7  and object_type = 'TABLE') x
  8  where obj# = x.object_id;

OBJECT_NAME                          OBJ#      FLAGS
------------------------------ ---------- ----------
XXXLZB_JD1                         246416 1073742353
HR_XXXCONTROL                      246421 1073742353
HR_XXX_01                          246424 1073742359

发现报错表的flags和其他表不一样(其他表为1073742353,而报错表为1073742359),对于这种情况官方给出来的解决方法,关闭库,确保没有任何额外会话连接上来

[oracle@xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 14 20:29:28 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.
SQL>  drop user XFF cascade;
 drop user FZHR cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01


SQL> alter table XFF.HR_XXX_01 enable table lock; 

Table altered.

SQL>  drop user XFF cascade;

User dropped.

SQL>