Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marcell.

Asked: October 26, 2003 - 10:58 am UTC

Last updated: October 10, 2024 - 5:06 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I just simply can't drop one user in sqlplus.

SQL> drop user a;
drop user a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL ldwvel 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

I also checked there is no table or other objects created by a user.

Can you solve the problem for me? Thanks,

and Tom said...

hmm, ldwvel?

hate cut and pastes that are not really cut and pastes. makes me suspicious :)


Anyway, it appears you have a "DROP" trigger and on line 7, it is doing something "bad", something that is not working.

ops$tkyte@ORA920> create or replace trigger drop_trigger
2 before drop on database
3 declare
4 n number;
5 begin
6 execute immediate 'select count(*) from not_a_table' into n;
7 end;
8 /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create user a identified by a;

User created.

ops$tkyte@ORA920> drop user a;
drop user a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 4


ops$tkyte@ORA920> drop trigger drop_trigger;

Trigger dropped.

ops$tkyte@ORA920> drop user a;

User dropped.


So, look in DBA_TRIGGERS for a triggering_event = 'DROP' trigger and then fix it.



Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Trigger while dropping user

Muhammad Khan, July 07, 2008 - 11:58 am UTC

Hi,
That was a very good solution for 9.2 but here I'm having the same problem in Oracle 10.1.0.4

SQL> drop user khamu02;
drop user khamu02
*
ERROR at line 1:
ORA-04098: trigger 'SYS.SDO_TOPO_DROP_FTBL' is invalid and failed re-validation

How can I fix the code of SDO_TOPO_DROP_FTBL trigger here?


Thanks,
Khan

Tom Kyte
July 07, 2008 - 12:14 pm UTC

what did you do in order to make this happen, what caused the trigger to go invalid, what did you change.


show errors trigger SDO_TOPO_DROP_FTBL

what does that show you (as sys of course)

Trigger while dropping user

Muhammad Khan, July 07, 2008 - 12:41 pm UTC

Hi Tom,
Thanks for your reply.
I really didnt do anything as far as the trigger is concerned. This is a new database. The catalog scripts (catalog.sql and catproc.sql) are already run. Now I'm trying to drop the user.

Here is what I get when I try to recompile the trigger:

SQL> alter trigger SYS.SDO_TOPO_DROP_FTBL compile;

Warning: Trigger altered with compilation errors.

SQL> show err
Errors for TRIGGER SYS.SDO_TOPO_DROP_FTBL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
35/9     PL/SQL: Statement ignored
35/9     PLS-00201: identifier 'MDSYS.MDERR' must be declared


I also get the same trigger error when I try to drop a table inside any user.

Tom Kyte
July 07, 2008 - 12:44 pm UTC

did you use dbca to create the database.

It is not installed correctly there.

Trigger while dropping user

Muhammad Khan, July 07, 2008 - 1:22 pm UTC

No I didnt create DBCA to create database. I ran scripts for the database creation which I've run on the other databases as well that are running properly with no problem. I'm reviewing the DB creation logs though.
Also, I had handovered the database to users and the application has been running without any issue for last one month. So apparently there is no other problem in database.
Just one more info, the db character set and NLS_LANG are JA16SJIS, if there are any issues with it. (I got one issue while importing data when the character set was JA16EUC)

Thanks for all your help.
Khan


Tom Kyte
July 07, 2008 - 2:11 pm UTC

you missed something - do check your logs please.

Trigger while dropping user

dadbm.com, October 28, 2009 - 4:49 am UTC

I've just encounter the same issue and as I could not find exact fix in Internet, here is the solution.
The problem is that you accidentally installed SPATIAL option into SYS schema and not in MDSYS.

You have to clean up spatial objects in SYS schema and reinstall it properly.

-- Kirill Loifman - dadbm.com --



Karan, June 13, 2011 - 7:42 am UTC

Hi Tom,

I have a situation in which I have two "AFTER DDL ON DATABASE" triggers in two different schemas and both of them are invalid. And i cannot even create a table to compile any one of them.

Now when i try to disable or drop one of them, it restricts me saying that the trigger on the other schema is invalid and vice versa.

So basically im stuck in a loop. I cannot create schemas, drop any schema, carry out any ddl on my database....

How can i resolve this mess !!! Hope you can help.

Regards,
Tom Kyte
June 17, 2011 - 11:28 am UTC

a%ORA9IR2> @connect "/ as sysdba"
sys%ORA9IR2> drop trigger b.after_ddl;
drop trigger b.after_ddl
             *
ERROR at line 1:
ORA-04098: trigger 'A.AFTER_DDL' is invalid and failed re-validation


sys%ORA9IR2> drop trigger a.after_ddl;
drop trigger a.after_ddl
             *
ERROR at line 1:
ORA-04098: trigger 'B.AFTER_DDL' is invalid and failed re-validation


sys%ORA9IR2> alter system set "_system_trig_enabled"=false;

System altered.

sys%ORA9IR2> drop trigger a.after_ddl;

Trigger dropped.

sys%ORA9IR2> drop trigger b.after_ddl;

Trigger dropped.



temporarily disable system triggers.

Followup

Karan, June 13, 2011 - 8:02 am UTC

As a follow up to the previous question.. im on "Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production".

unable to drop user

aditya bisht, October 10, 2024 - 10:36 am UTC

SQL> drop user wacsmdms cascade;
drop user wacsmdms cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Oracle Text error:
DRG-11338: policy WACSMDMS"."SDP_DIM_MAIL_ADDRESS_IDX does not exist
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.CTX_ADM", line 280
ORA-06512: at line 1



Chris Saxon
October 10, 2024 - 5:06 pm UTC

Oracle Text metadata has become inconsistent. The index was dropped, but the meta data not updated.

You'll need to remove this information from the CTXSYS tables with statements like:

delete from ctxsys.dr$index_value where IXV_IDX_ID = ?;
delete from ctxsys.dr$index_object where IXO_IDX_ID = ?;
delete from ctxsys.dr$index where idx_id = ?;

See MOS note 2877418.1 for more details. If you're still struggling contact Oracle support.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library