ORA-06575: 程式包或函數 NO_VM_DROP_PROC 處於無效狀態，wndproc函數

ORA-06575: 程式包或函數 NO_VM_DROP_PROC 處於無效狀態，wndproc函數
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 2ORA-00604: error occurred at recursive SQL level 1ORA-06576: not a valid function or procedure nameORA-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 sysdbaSQL> 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;

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

Microsoft OLE DB Provider for Oracle 錯誤 '80004005' ORA-06575: 程式包或函數 F_GETCXZYMC 處於無效狀
oracle中的儲存函數出於無效狀態，這是為何？（ORA-06575）新手教

說明函數編譯有問題，重新編譯一下看有什麼問題，有沒有語法錯誤？
在oracle裡模數要用mod函數，取商的整數部分要用floor函數
faculty_ge := mod(faculty_number,10);
faculty_shi := floor(faculty_numbe/10);
下面一句stu_id_var裡將number串連起來，會有個強制類型轉換，最好能明確的用to_char函數轉換