3 Replies Latest reply on Aug 18, 2009 8:01 PM by JorgeB-Oracle

    'ORA-13199:  APP-Load:CODE=-4098' error when using SEM_APIS.BULK_LOAD...

    712796
      Hello,

      I am trying to load a bulk of several millions triples to our Oracle11g database.

      I created a staging table using the following DDL:

      CREATE TABLE "WIZRDF"."STAGE_TABLE"
      *(     "RDF$STC_SUB" VARCHAR2(4000 BYTE) NOT NULL ENABLE,*
      *"RDF$STC_PRED" VARCHAR2(4000 BYTE) NOT NULL ENABLE,*
      *"RDF$STC_OBJ" VARCHAR2(4000 BYTE) NOT NULL ENABLE,*
      *"RDF$STC_CANON_EXT" VARCHAR2(64 BYTE),*
      *"RDF$STC_OBJ_EXT" VARCHAR2(64 BYTE),*
      *"RDF$STC_PRED_EXT" VARCHAR2(64 BYTE),*
      *"RDF$STC_SUB_EXT" VARCHAR2(64 BYTE)*
      *) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING*
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "WIZRDF" ;
      I inserted 1000 triples to the staging table using good old "INSERT" SQL command.

      I used the following sem_apis command to load the bulk from the staging table to the application table:
      *"EXECUTE SEM_APIS.BULK_LOAD_FROM_STAGING_TABLE('FIRST_MODEL','WIZRDF','STAGE_TABLE');"*
      The command executed for 30 seconds!!!

      I executed the same command after inserting 5000 triples to the staging table.

      After a very long period of time I got the following error message:

      "*Error starting at line 1 in command:*
      *EXECUTE SEM_APIS.BULK_LOAD_FROM_STAGING_TABLE('FIRST_MODEL','WIZRDF','STAGE_TABLE');*
      *Error report:*
      *ORA-13199: APP-Load:CODE=-4098*
      *ORA-06512: "MDSYS.SDO_RDF_INTERNAL", in line 2245*
      *ORA-06512: "MDSYS.SDO_RDF", in line 294*
      *ORA-06512: "MDSYS.RDF_APIS", in line 542*
      *ORA-06512: in line 1*
      *13199. 00000 - "%s"*
      **Cause: This is an internal error.*
      **Action: Contact Oracle Support Services."*
      I am using the following hardware configuration:
      CPU: Intel Xeon CPU X5460 @3.15 GHz (2 processors).
      64bit operation system.
      Memory: 32GB

      It might be important to state that I all ready loaded approximately 20 million triples to the same Database under a different model.

      What did I do wrong? Please help!!!

      Doron B.
        • 1. Re: 'ORA-13199:  APP-Load:CODE=-4098' error when using SEM_APIS.BULK_LOAD...
          JorgeB-Oracle
          Seems the error ORA 4098 is:

          Error: ORA 4098
          Text: trigger <name> is invalid and failed re-validation

          can you post the output from:

          select trigger_name, status
          from dba_triggers
          where owner='MDSYS';

          Regards!
          Jorge
          • 2. Re: 'ORA-13199:  APP-Load:CODE=-4098' error when using SEM_APIS.BULK_LOAD...
            712796
            Dear Jorge,

            Your wish is my command.

            The output of "select trigger_nameselect trigger_name, status from dba_triggers where owner='MDSYS';" is:
            RDFR_5_UPDTRIG     ENABLED
            RDFR_5_DELTRIG     ENABLED
            RDFR_5_INSTRIG     ENABLED
            RDFR_4_UPDTRIG     ENABLED
            RDFR_4_DELTRIG     ENABLED
            RDFR_4_INSTRIG     ENABLED
            RDFR_3_UPDTRIG     ENABLED
            RDFR_3_DELTRIG     ENABLED
            RDFR_3_INSTRIG     ENABLED
            RDFR_2_UPDTRIG     ENABLED
            RDFR_2_DELTRIG     ENABLED
            RDFR_1_UPDTRIG     ENABLED
            RDFR_1_DELTRIG     ENABLED
            RDFR_1_INSTRIG     ENABLED
            RDFR_2_INSTRIG     ENABLED
            SDO_NETWORK_UD_UPD_TRIG     ENABLED
            SDO_NETWORK_UD_DEL_TRIG     ENABLED
            SDO_NETWORK_LOCKS_UPD_TRIG     ENABLED
            SDO_NETWORK_LOCKS_DEL_TRIG     ENABLED
            SDO_NETWORK_LOCKS_INS_TRIG     ENABLED
            SDO_NETWORK_UD_INS_TRIG     ENABLED
            SDO_NETWORK_CONS_UPD_TRIG     ENABLED
            SDO_NETWORK_CONS_DEL_TRIG     ENABLED
            SDO_NETWORK_CONS_INS_TRIG     ENABLED
            SDO_NETWORK_TRIG_UPD     ENABLED
            SDO_NETWORK_TRIG_DEL     ENABLED
            SDO_NETWORK_TRIG_INS     ENABLED
            SDO_COORD_OP_PARAM_VAL_TRIGG2     ENABLED
            SDO_COORD_OP_PARAM_VAL_TRIGGER     ENABLED
            SDO_CRS_DELETE_TRIGGER     ENABLED
            SDO_CRS_INSERT_TRIGGER     ENABLED
            CS_SRS_TRIGGER     ENABLED
            SDO_PREFERRED_OPS_USER_TRIGGER     ENABLED
            SDO_COORD_OP_METHODS_TRIGGER     ENABLED
            SDO_UNITS_OF_MEASURE_TRIGGER     ENABLED
            SDO_TOPO_TRIG_INS1     ENABLED
            SDO_LRS_TRIG_UPD     ENABLED
            SDO_LRS_TRIG_DEL     ENABLED
            SDO_GEOM_TRIG_UPD1     ENABLED
            SDO_GEOM_TRIG_DEL1     ENABLED
            SDO_GEOM_TRIG_INS1     ENABLED
            SDO_LRS_TRIG_INS     ENABLED
            SDO_GEOR_TRIG_DEL1     ENABLED
            SDO_GEOR_TRIG_INS1     ENABLED
            SDO_ANNOT_TRIG_INS1     ENABLED
            OGIS_CRS_DELETE_TRIGGER     ENABLED
            OGIS_CRS_INSERT_TRIGGER     ENABLED
            SDO_COORD_OP_PATHS_TRIGGER     ENABLED
            SDO_PREFERRED_OPS_SYS_TRIGGER     ENABLED
            SDO_GEOR_TRIG_UPD1     ENABLED
            LUBM_ABOX_INS     ENABLED
            LUBM_ABOX_DEL     ENABLED
            LUBM_ABOX_DML     ENABLED
            LUBM_TBOX_F_DML     ENABLED
            LUBM_TBOX_F_DEL     ENABLED
            LUBM_TBOX_A_DEL     ENABLED
            LUBM_TBOX_A_DML     ENABLED
            LUBM_TBOX_A_INS     ENABLED
            LUBM_TBOX_F_INS     ENABLED
            WS_ONTOLOGY_DEL     ENABLED
            WS_ONTOLOGY_INS     ENABLED
            WS_ONTOLOGY_DML     ENABLED
            FIRST_MODEL_INS     ENABLED
            FIRST_MODEL_DEL     ENABLED
            FIRST_MODEL_DML     ENABLED
            WISDOMSTONE_INS     ENABLED
            WISDOMSTONE_DEL     ENABLED
            WISDOMSTONE_DML     ENABLED
            SDO_DROP_USER     ENABLED
            SDO_GEOR_BDDL_TRIGGER     ENABLED
            SDO_GEOR_ERR_TRIGGER     ENABLED
            SDO_ST_SYN_CREATE     ENABLED
            SDO_TOPO_DROP_FTBL     ENABLED
            SDO_GEOR_ADDL_TRIGGER     ENABLED
            SDO_NETWORK_DROP_USER     ENABLED
            SDO_NETWORK_CONS_DROP_TRIG     ENABLED
            SDO_NETWORK_LOCKS_DROP_TRIG     ENABLED
            SDO_NETWORK_UD_DROP_TRIG     ENABLED
            SDO_SEM_ALTER_TRIGGER     ENABLED
            SDO_SEM_DROP_TRIGGER     ENABLED

            Regards,

            Doron
            • 3. Re: 'ORA-13199:  APP-Load:CODE=-4098' error when using SEM_APIS.BULK_LOAD...
              JorgeB-Oracle
              Hi Doron,

              From the output you sent the triggers involved are all fine:

              FIRST_MODEL_INS     ENABLED
              FIRST_MODEL_DEL     ENABLED
              FIRST_MODEL_DML     ENABLED

              ---

              What is your Oracle Version?

              If on 11.1.0.6.0. Upgrade to 11.1.0.7.0 then apply patch 7600122
              ---

              On 11.1.0.7.0 and patch 7600122, we can use some tracing to figure out what is going on.

              * Recording Event Traces during Bulk Loading

              If a table named RDF$ET_TAB exists in the invoker’s schema and if the MDSYS user
              has been granted the INSERT and UPDATE privileges on this table, event traces for
              some of the tasks performed during executions of the
              SEM_APIS.BULK_LOAD_FROM_STAGING_TABLE procedure will be added to the table.
              You may find the content of this table useful if you ever need to report any
              problems in bulk load.

              The RDF$ET_TAB table must be created as follows:

              CREATE TABLE RDF$ET_TAB (
              proc_sid VARCHAR2(30),
              proc_sig VARCHAR2(200),
              event_name varchar2(200),
              start_time timestamp,
              end_time timestamp,
              start_comment varchar2(1000) DEFAULT NULL,
              end_comment varchar2(1000) DEFAULT NULL
              );
              GRANT INSERT, UPDATE on RDF$ET_TAB to MDSYS;


              With the event trace content, it will be easier for us to investigate the performance issue.
              Oracle 11.1.0.7 w/ patch 7600122 contains enhancements for bulk-load (and bulk-append) performance (including simple hint specifications such as MBV_JOIN_HINT=USE_NL, MBT_JOIN_HINT=USE_HASH, etc. as identified in

              http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28397/sem_apis_ref.htm#insertedID6


              To download patch 7600122:

              Log into Metalink: https://metalink.oracle.com
              Click on the Patches & Updates tab
              Select "Simple Search"
              Search by "Patch Number(s)"
              Patch Number ==> 7600122
              Platform or Language ==> <your platform>
              Click GO
              ---------------

              If you can't have 11.1.0.7.0 with 7600122 then:

              Try:

              conn / as sysdba
              ALTER TRIGGER MDSYS.FIRST_MODEL_INS COMPILE;
              ALTER TRIGGER MDSYS.FIRST_MODEL_DEL COMPILE;
              ALTER TRIGGER MDSYS.FIRST_MODEL_DML COMPILE;

              -- Try the bulk_load again

              -- It will be good to see the contents of the event trace tables in case of failure:
              spool event_info.txt
              select * from MDSYS.RDF$ET_<model_id_for_FIRST_MODEL>_....
              spool off