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