ORA-06575: the package or function NO_VM_DROP_PROC is invalid, wndproc Function
SQL> drop user aaa;
Drop user aaa
ORA-00604: recursive SQL Level 1 error
ORA-06575: the package or function NO_VM_DROP_PROC is invalid
ORA-06512: In line 21
SQL> alter procedure WMSYS. NO_VM_DROP_PROC disable;
Alter procedure WMSYS. NO_VM_DROP_PROC disable
ORA-00922: Option Missing or invalid
SQL> alter procedure WMSYS. NO_VM_DROP_PROC DISABLE;
Alter procedure WMSYS. NO_VM_DROP_PROC DISABLE
ORA-00922: Option Missing or invalid
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: trigger 'no _ VM_DROP_A 'does not exist
SQL> drop user aaa;
User dropped
SQL> purge recyclebin;
Done
SQL>
Metlink references
|
ORA-06576 error during Drop User (Document ID 1498610.1) |
In this Document
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 a 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 cocould 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 wocould 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 error '000000' ORA-06575: the package or function F_GETCXZYMC is invalid
The storage functions in oracle are invalid. Why? (ORA-06575) newbie
It indicates that there is a problem with function compilation. Let's re-compile it to see if there is any problem and whether there is a syntax error?
Mod function is used for modulo in oracle, and floor function is used for the integer part of the operator.
Faculty_ge: = mod (faculty_number, 10 );
Faculty_shi: = floor (faculty_numbe/10 );
In the following sentence, stu_id_var connects the number and a forced type conversion occurs. It is better to use the to_char function to convert the number.