Unable to DDL due to Oracle Spatial and XDB

October 30, 2009 by 1 Comment 

I’ve just encountered an interesting Oracle database issue that was not described properly on the Internet. So, I’ve decided to give a solution in this post.

The issue is that you simply can not execute certain DDL (Data Definition Language) SQL statements due to the strange behavior of Oracle triggers and Oracle error ORA-00604 and ORA-04098.

Below are possible SQL with Oracle error you can experience:

SQL> drop table tab1;
drop table tab1
*
ERROR at line 1:
ORA-04098: trigger 'SYS.SDO_TOPO_DROP_FTBL' is invalid and failed re-validation
SQL> create sequence seq1…
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Trigger xdb_installation_trigger does not support object creation of
type SEQUENCE
ORA-06512: at line 32

The first suspect is a SYS trigger SDO_TOPO_DROP_FTBL. What caused that Oracle trigger to go invalid?
Let’s see the actual trigger errors below:

SQL> show errors trigger sys.SDO_TOPO_DROP_FTBL
Errors for TRIGGER SYS.SDO_TOPO_DROP_FTBL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
18/9 PL/SQL: Statement ignored
18/9 PLS-00201: identifier 'MDSYS.MDERR' must be declared
SQL> DROP TABLE CCM_SUPPLIER_INPUT;
DROP TABLE CCM_SUPPLIER_INPUT
*
ERROR at line 1:
ORA-04098: trigger 'SYS.SDO_TOPO_DROP_FTBL' is invalid and failed re-validation

This Oracle error refers to MDSYS schema that belongs to Oracle Spatial option. So the problem is somebody accidentally installed Oracle Spatial into SYS schema and not in MDSYS as required.

Another issue when xdb_installation_trigger is avoiding the creation of sequences caused by the incorrect XML Database (Oracle XDB) installation.

Solution is the following:

– You have to clean up Oracle Spatial objects in SYS schema and reinstall it properly in MDSYS schema. Check a cleanup script in Oracle Note 413693.1

– As the temporary solution for the Oracle XDB issue you can drop that trigger and then when you get a chance – re-install Oracle XDB.

Once that is done, you can proceed with your DDL…

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

One Response to “Unable to DDL due to Oracle Spatial and XDB”
  1. Harald Wolf says:

    Hi Kirill,
    I ran into the “Unable to DDL due to Oracle Spatial and XDB” problem and hit your really nice Homepage. And your post helped me immediately out of my troubles.

    Viele Grüsse
    Harald Wolf, Oracle Germany

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email