Oracle Database: ORA-00604 and ORA-20000

***************************************************************************************
* ORA-00604: error occurred at recursive SQL level 1
* ORA-20000: Trigger xdb_installation_trigger does not support object creation of type SNAPSHOT *
***************************************************************************************

Solution:

A trigger named xdb_installation_trigger that prevent you from creating your sequence.
Disable it before the import, you reeanable it after, if you really need it.

What is XDB ?

Oracle XML DB is a feature of the Oracle Database. It provides a high-performance,
native XML storage and retrieval technology. It fully absorbs the W3C XML data model
into the Oracle Database, and provides new standard access methods for navigating
and querying XML. With Oracle XML DB, you get all the advantages of relational
database technology plus the advantages of XML.

In Oracle Database 11g Release 2, new capabilities, such as XMLIndex structured
component and partitioning, as well as numerous enhancements of Oracle XML DB are
introduced to dramatically improve its performance, scalability,
XML schema management, and XML information lifecycle management.

$ su – orancmst
orancmst’s Password:

$ export ORACLE_SID=ctstrng
$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 27 17:22:29 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CTSTRNG READ WRITE

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CTSTRNG READ WRITE

SQL> desc dba_triggers
Name Null? Type
———————————————————————————– ——– ——————————————————–
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
CROSSEDITION VARCHAR2(7)
BEFORE_STATEMENT VARCHAR2(3)
BEFORE_ROW VARCHAR2(3)
AFTER_ROW VARCHAR2(3)
AFTER_STATEMENT VARCHAR2(3)
INSTEAD_OF_ROW VARCHAR2(3)
FIRE_ONCE VARCHAR2(3)
APPLY_SERVER_ONLY VARCHAR2(3)

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME
—————————— —————————— —————- ——————————
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT

SQL> desc dba_registry
Name Null? Type
———————————————————————————– ——– ——————————————————–
COMP_ID NOT NULL VARCHAR2(30)
COMP_NAME VARCHAR2(255)
VERSION VARCHAR2(30)
STATUS VARCHAR2(11)
MODIFIED VARCHAR2(20)
NAMESPACE NOT NULL VARCHAR2(30)
CONTROL NOT NULL VARCHAR2(30)
SCHEMA NOT NULL VARCHAR2(30)
PROCEDURE VARCHAR2(61)
STARTUP VARCHAR2(8)
PARENT_ID VARCHAR2(30)
OTHER_SCHEMAS VARCHAR2(4000)

SQL> select COMP_ID,STATUS from dba_registry;

COMP_ID STATUS
—————————— ———–
XDB LOADING
CATALOG VALID
CATPROC VALID
JAVAVM VALID
XML VALID
CATJAVA VALID
APS VALID
XOQ INVALID

8 rows selected.

SQL> alter trigger XDB_INSTALLATION_TRIGGER disable;

Trigger altered.

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME
—————————— —————————— —————- ——————————
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT

SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS from dba_triggers where TRIGGER_NAME=’XDB_INSTALLATION_TRIGGER’;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
—————————— —————————— —————- —————————— ——–
SYS XDB_INSTALLATION_TRIGGER BEFORE EVENT DISABLED