ORA-06575: the package or function NO_VM_DROP_PROC is invalid, wndproc Function

Source: Internet
Author: User
Tags floor function

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

  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 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.