您当前位置：首页 > 互联网 > ORA-06575: 程序包或函数 NO_VM_DROP_PROC 处于无效状态

ORA-06575: 程序包或函数 NO_VM_DROP_PROC 处于无效状态

来源：程序员人生发布时间：2014-09-17 16:17:03 阅读次数：6494次

SQL> drop user aaa ;

drop user aaa

ORA-00604: 递归 SQL 级别 1 出现错误
ORA-06575: 程序包或函数 NO_VM_DROP_PROC 处于无效状态
ORA-06512: 在 line 21

SQL> alter procedure WMSYS.NO_VM_DROP_PROC disable;

alter procedure WMSYS.NO_VM_DROP_PROC disable

ORA-00922: 选项缺失或无效

SQL> alter procedure WMSYS.NO_VM_DROP_PROC DISABLE;

alter procedure WMSYS.NO_VM_DROP_PROC DISABLE

ORA-00922: 选项缺失或无效

SQL> ALTER TRIGGER wmsys.NO_VM_DDL DISABLE;

Trigger altered

SQL> ALTER TRIGGER wmsys.NO_VM_DROP_A DISABLE;

ALTER TRIGGER wmsys.NO_VM_DROP_A DISABLE

ORA-04080: 触发器 'NO_VM_DROP_A' 不存在

SQL> drop user aaa ;

User dropped

SQL> purge recyclebin;

Done

SQL>

metlink引用文档

ORA-06576 error during Drop User (文档 ID 1498610.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

Dropping a user schema results in below errors:

SQL> drop user GG_ADMIN;

DROP USER "GG_ADMIN"
Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-06576: not a valid function or procedure name
ORA-06512: at line 21 gg_admin cascade;

CAUSE

A DDL trigger is defined on the drop statement.
Next query will get you the definition of the DDL Triggers in the system.

SQL> connect / as sysdba

SQL> SELECT a.obj#, a.sys_evts, b.name FROM trigger$ a,obj$ b WHERE a.sys_evts > 0 AND a.obj#=b.obj# AND baseobject = 0;

  OBJ#       SYS_EVTS NAME
---------- ---------- ------------------------------
  81794             8 LOGON_DATE
  81795          8416 NO_VM_DDL
  81796           128 NO_VM_DROP_A
  13177          8192 AW_REN_TRG
  13179           128 AW_DROP_TRG
  11990        524256 LOGMNRGGC_TRIGGER
  13175          4096 AW_TRUNC_TRG
  71787             1 MGMT_STARTUP

Get an errorstack for ORA-06576 error:

SQL> alter system set events='6576 trace name errorstack level 3';

SQL> drop user <username>

When executing 'drop user gg_admin', the resultant trace file shows the failing statement is a call to wmsys.no_vm_drop_proc('USER', 'GG_ADMIN', '').

From errorstack trace file we could observe the following:

 if (s_event='CREATE') then
   execute immediate 'call wmsys.no_vm_create_proc(''' || sys.dictionary_obj_type || ''', ''' || 
      sys.dictionary_obj_name || ''', ''' || sys.dictionary_obj_owner || ''')' ;
 elsif (s_event='DROP') then
   execute immediate 'call wmsys.no_vm_drop_proc(''' || sys.dictionary_obj_type || ''', ''' || 
      sys.dictionary_obj_name || ''', ''' || sys.dictionary_obj_owner || ''')' ;

The triggers enabled for this were in this case NO_VM_DDL and NO_VM_DROP_A

SOLUTION

Check if there are any DROP BEFORE triggers enabled. Once you drop the trigger, it will allow you to drop the user.

Workaround would be:

SQL> ALTER TRIGGER NO_VM_DDL DISABLE;
SQL> ALTER TRIGGER NO_VM_DROP_A DISABLE;
SQL> drop user gg_admin;

REFERENCES

NOTE:75206.1 - OERR: ORA-6576 not a function or procedure

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生

------分隔线----------------------------

上一篇 CakePHP 安装

下一篇 腾讯游戏运营管理中心总监刘栖铜：腾讯游戏云的理想与实践

分享到:

------分隔线----------------------------

为码而活

积分：4237

15粉丝

7关注

栏目热点