1 2 Previous Next 26 Replies Latest reply: Feb 12, 2013 3:54 PM by odie_63 Go to original post RSS
      • 15. Re: ORA-04030: out of process memory Loading Large XML File
        Marco Gralike
        The error is correct. You are missing the last quot for attribute @LOADED
        select  x1.* 
           from HUGE_XML T,
              XMLTABLE('/EMPLOYEES'
                    PASSING T.xml_col
                    COLUMNS CREATED TIMESTAMP  PATH '@CREATED',
                     COLUMNS LOADED TIMESTAMP  PATH '@LOADED
        ) x1
        should be
        select  x1.* 
           from HUGE_XML T,
              XMLTABLE('/EMPLOYEES'
                    PASSING T.xml_col
                    COLUMNS CREATED TIMESTAMP  PATH '@CREATED',
                     COLUMNS LOADED TIMESTAMP  PATH '@LOADED'
        ) x1
        I urge you to have a read at the whitepaper: "Oracle XML DB : *Choosing the Best XMLType Storage Option for Your Use Case* (PDF) Jan 2010" here: http://www.oracle.com/technetwork/database-features/xmldb/downloads/index.html
        Pro's and cons of the different options are clearly explained in that whitepaper.
        • 16. Re: ORA-04030: out of process memory Loading Large XML File
          odie_63
          Kevin_K wrote:
          COLUMNS LOADED TIMESTAMP  PATH '@LOADED
          *
          [Error] Execution (64: 38): ORA-00907: missing right parenthesis
          Some more coffee? ;)
          • 17. Re: ORA-04030: out of process memory Loading Large XML File
            mdrake
            SQL>
            SQL> set lines 160
            SQL> column created format A28
            SQL> column loaded  format A28
            SQL> column TS      format A28
            SQL> column mode    format  A4
            SQL> with HUGE_XML as
              2  (
              3    select XMLTYPE(
              4  '<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
              5  <EMPLOYEES CREATED="2013-02-06T12:33:00" xsi:noNamespaceSchemaLocation="http://supporthtml.oracle.com/huge_xmldocument.xsd" xmlns:xsi="http://www.w3.org/20
            01/XMLSchema-instance">
              6   <EMPLOYEE_TRANS MODE="A" emp_id="1">
              7    <emp_nm>SCOTT</emp_nm>
              8    <emp_dept>FN</emp_dept>
              9    <mgr>DON</mgr>
             10    <sal>4000</sal>
             11    <updt_ts>2013-02-06T12:28:00</updt_ts>
             12    </EMPLOYEE_TRANS>
             13  <EMPLOYEE_TRANS MODE="A" emp_id="2">
             14    <emp_nm>KEVIN</emp_nm>
             15    <emp_dept>HR</emp_dept>
             16    <mgr>MIKE</mgr>
             17    <sal>3000</sal>
             18    <updt_ts>2013-02-06T12:29:00</updt_ts>
             19    </EMPLOYEE_TRANS>
             20  </EMPLOYEES>') as XML_COL
             21    from dual
             22  )
             23  select x1.CREATED, x1.LOADED, x2.*
             24    from HUGE_XML T,
             25         XMLTABLE(
             26          '/EMPLOYEES'
             27           PASSING T.xml_col
             28           COLUMNS
             29             CREATED        TIMESTAMP PATH '@CREATED',
             30             LOADED         TIMESTAMP PATH '@LOADED',
             31             EMPLOYEE_TRANS   XMLTYPE PATH 'EMPLOYEE_TRANS'
             32          ) x1,
             33         XMLTABLE(
             34          '/EMPLOYEE_TRANS'
             35           passing x1.EMPLOYEE_TRANS
             36           COLUMNS
             37             "MODE"  VARCHAR2(1) path '@MODE',
             38             ID       NUMBER(10) path '@emp_id',
             39             NAME   VARCHAR2(10) PATH 'emp_nm',
             40             TS        TIMESTAMP PATH 'updt_ts'
             41         ) x2
             42   /
            
            CREATED                      LOADED                       MODE         ID NAME       TS
            ---------------------------- ---------------------------- ---- ---------- ---------- ----------------------------
            06-FEB-13 12.33.00.000000 PM                              A             1 SCOTT      06-FEB-13 12.28.00.000000 PM
            06-FEB-13 12.33.00.000000 PM                              A             2 KEVIN      06-FEB-13 12.29.00.000000 PM
            
            SQL>
            SQL>
            • 18. Re: ORA-04030: out of process memory Loading Large XML File
              odie_63
              That was one of Kevin's earlier questions ;)

              {thread:id=2495668}
              • 19. Re: ORA-04030: out of process memory Loading Large XML File
                Kevin_K
                Mdrake: Thanks so much for these valuable tips. You were right. Loading the huge xml into BINARY XML works but querying the document bolws up TOAD.
                select  x1.CREATED, x1.LOADED, x2.* 
                   from HUGE_XML T,
                      XMLTABLE(
                            '/EMPLOYEES'
                            PASSING T.xml_col
                            COLUMNS 
                               CREATED TIMESTAMP  PATH '@CREATED',
                               LOADED TIMESTAMP  PATH '@LOADED'
                               EMPLOYEE_TRANS XMLTYPE PATH 'EMPLOYEE_TRANS'
                            ) x1,
                       XMLTABLE(
                          '/EMPLOYEE_TRANS'
                          passing x1.EMPLOYEE_TRANS
                          COLUMNS
                              NAME VARCHAR2(32), PATH 'emp_nm'            
                        ) x2
                Can you please point me to a document or sample to use SAXLoader to break the incoming document into a set of EMPLOYEE_TRANS records. I need to load once and query many times , hence looks like Object Relational approach would make more sense.
                
                create table T1 (
                CREATED TIMESTAMP,
                LOADED TIMESTAMP,
                EMPLOYEE_TRANS XMLTYPE
                )
                XMLTYPE EMPLOYEE_TRANS
                store as OBJECT_RELATIONAL
                XMLSCHEMA "yourSchemaLocation" ELEMENT "EMPLOYEE_TRANS"
                
                and then use the SAXLoader to break the incoming document into a set of EMPLOYEE_TRANS records. This will allow you to load the relational table in parallel.
                Thanks
                Kevin

                Edited by: Kevin_K on Feb 11, 2013 11:31 AM
                • 20. Re: ORA-04030: out of process memory Loading Large XML File
                  odie_63
                  Kevin_K wrote:
                  Loading the huge xml into BINARY XML works but querying the document bolws up TOAD.
                  You've tried to run the SELECT alone?
                  Of course, the result set is not meant to be fetched entirely, least of all in Toad.

                  Have you tried a single INSERT SELECT into a relational table (with APPEND hint if necessary)?
                  I'd also try a CREATE TABLE AS SELECT with NOLOGGING.

                  Can you please point me to a document or sample to use SAXLoader to break the incoming document into a set of EMPLOYEE_TRANS records.
                  See the Oracle XML DB Sax Loader Utility at the bottom of the XML DB sample code page :
                  http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html

                  and a complete use case by Mark here : {thread:id=461009}
                  • 21. Re: ORA-04030: out of process memory Loading Large XML File
                    Kevin_K
                    You've tried to run the SELECT alone?
                    Of course, the result set is not meant to be fetched entirely, least of all in Toad.
                    
                    Have you tried a single INSERT SELECT into a relational table (with APPEND hint if necessary)?
                    I'd also try a CREATE TABLE AS SELECT with NOLOGGING.
                    Yes, I tried to run the select alone.
                    Yes, I tried a single INSERT select into a relational table but "without" with "APPEND" hint.
                    I will try CREATE TABLE AS SELECT with NOLOGGING and let you know.

                    I will look at Mdrakes SaxLoader example , but looks like it uses JAVA , do I need jeveloper to compile all this code? Any special settings need to be done in XMLDB database to make /enable this SaxLoader work?

                    Thanks
                    • 22. Re: ORA-04030: out of process memory Loading Large XML File
                      Marco Gralike
                      Sorry couldn't resist
                      querying the document bolws up TOAD.
                      blows up Toad should have been TOAD blows ;-) ?
                      • 23. Re: ORA-04030: out of process memory Loading Large XML File
                        Marco Gralike
                        XMLType Object Relational storage might not yet be needed. Create STRUCTURED XMLIndexes on top of the XMLType Binary XML storage to support your queries

                        Also see, the next step,hints and tricks here...

                        "Oracle XML DB : Best Practices to *Get Optimal Performance out of XML Queries* (PDF) Jan 2013" (http://www.oracle.com/technetwork/database-features/xmldb/downloads/index.html)
                        • 24. Re: ORA-04030: out of process memory Loading Large XML File
                          Kevin_K
                          Marco:
                           Create STRUCTURED XMLIndexes on top of the XMLType Binary XML storage to support your queries
                          getting "[Error] Execution (3: 48): ORA-00904: "OBJECT_VALUE": invalid identifier" while creating structured xml index.
                          
                          
                          CREATE TABLE HUGE_XML_BI
                                  (xml_col XMLTYPE)
                                XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
                          
                          table created     
                          
                          sample select : 
                          
                          select x1.CREATED, x1.LOADED, x2.*
                               from HUGE_XML T,
                                    XMLTABLE(
                                     '/EMPLOYEES'
                                      PASSING T.xml_col
                                    COLUMNS
                                        CREATED        TIMESTAMP PATH '@CREATED',
                                        LOADED         TIMESTAMP PATH '@LOADED',
                                        EMPLOYEE_ADRS   XMLTYPE PATH 'EMPLOYEE_ADRS'
                                     ) x1,
                                    XMLTABLE(
                                     '/EMPLOYEE_ADRS'
                                      passing x1.EMPLOYEE_ADRS
                                      COLUMNS
                                        "MODE"  VARCHAR2(1) path '@MODE',
                                        ID       NUMBER(10) path '@emp_id',
                                        NAME   VARCHAR2(10) PATH 'emp_nm',
                                        TS        TIMESTAMP PATH 'updt_ts'
                                    ) x2
                                    
                          Structured index :
                          CREATE INDEX 
                          HUGE_XML_IDX ON HUGE_XML(OBJECT_VALUE)
                          INDEXTYPE IS XDB.XMLindex
                          PARAMETERS ( 
                          'XMLTABLE EMPLOYEES_tab ''/EMPLOYEES/EMPLOYEE_ADRS'' COLUMNS NAME   VARCHAR2(10) PATH ''emp_nm''' )   
                          
                          [Error] Execution (3: 48): ORA-00904: "OBJECT_VALUE": invalid identifier" while creating structured xml index.
                          
                          {code}
                          
                          Please suggest what am I doing wrong here?
                          
                          Thanks
                          
                          Edited by: Kevin_K on Feb 12, 2013 12:48 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                          • 25. Re: ORA-04030: out of process memory Loading Large XML File
                            odie_63
                            [Error] Execution (3: 48): ORA-00904: "OBJECT_VALUE": invalid identifier" while creating structured xml index.
                            
                            {code}
                            Still no OBJECT_VALUE column in your table... The column name is XML_COL.
                            • 26. Re: ORA-04030: out of process memory Loading Large XML File
                              odie_63
                              BTW, if you want to index the whole master/detail relationship, you'll have to use this index :
                              CREATE INDEX huge_xml_sxi ON huge_xml_bi (xml_col)
                              INDEXTYPE IS XDB.XMLIndex
                              PARAMETERS (q'#
                              XMLTable EMPLOYEES_XT
                              '/EMPLOYEES'
                              COLUMNS
                                CREATED        TIMESTAMP PATH '@CREATED'
                              , LOADED         TIMESTAMP PATH '@LOADED'
                              , EMPLOYEE_ADRS  XMLTYPE   PATH 'EMPLOYEE_ADRS' VIRTUAL
                              XMLTable EMPLOYEE_ADRS_XT
                              '/EMPLOYEE_ADRS' 
                              PASSING EMPLOYEE_ADRS
                              COLUMNS
                                MODE1  VARCHAR2(1)  PATH '@MODE'
                              , ID     NUMBER(10)   PATH '@emp_id'
                              , NAME   VARCHAR2(10) PATH 'emp_nm'
                              , TS     TIMESTAMP    PATH 'updt_ts'#');
                              Connected to:
                              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                              
                              SQL> CREATE INDEX huge_xml_sxi ON huge_xml_bi (xml_col)
                                2  INDEXTYPE IS XDB.XMLIndex
                                3  PARAMETERS (q'#
                                4  XMLTable EMPLOYEES_XT
                                5  '/EMPLOYEES'
                                6  COLUMNS
                                7    CREATED        TIMESTAMP PATH '@CREATED'
                                8  , LOADED         TIMESTAMP PATH '@LOADED'
                                9  , EMPLOYEE_ADRS  XMLTYPE   PATH 'EMPLOYEE_ADRS' VIRTUAL
                               10  XMLTable EMPLOYEE_ADRS_XT
                               11  '/EMPLOYEE_ADRS'
                               12  PASSING EMPLOYEE_ADRS
                               13  COLUMNS
                               14    MODE1  VARCHAR2(1)  PATH '@MODE'
                               15  , ID     NUMBER(10)   PATH '@emp_id'
                               16  , NAME   VARCHAR2(10) PATH 'emp_nm'
                               17  , TS     TIMESTAMP    PATH 'updt_ts'#');
                              
                              Index created.
                              
                              SQL> exec dbms_stats.gather_table_stats(user, 'HUGE_XML_BI');
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> set autotrace on explain
                              SQL> set lines 200
                              SQL> select x1.created, x1.loaded, x2.*
                                2  from huge_xml_bi t
                                3     , XMLTable(
                                4         '/EMPLOYEES'
                                5         passing T.xml_col
                                6         columns
                                7           created        timestamp path '@CREATED'
                                8         , loaded         timestamp path '@LOADED'
                                9         , employee_adrs  xmltype   path 'EMPLOYEE_ADRS'
                               10       ) x1
                               11     , XMLTable(
                               12         '/EMPLOYEE_ADRS'
                               13         passing x1.employee_adrs
                               14         columns
                               15           mode1  varchar2(1)  path '@MODE'
                               16         , id     number(10)   path '@emp_id'
                               17         , name   varchar2(10) path 'emp_nm'
                               18         , ts     timestamp    path 'updt_ts'
                               19     ) x2;
                              
                              CREATED                        LOADED               M         ID NAME       TS
                              ------------------------------ -------------------- - ---------- ---------- ----------------------------
                              06/02/13 12:33:00,000000                            A          1 SCOTT      06/02/13 12:28:00,000000
                              06/02/13 12:33:00,000000                            A          2 KEVIN      06/02/13 12:29:00,000000
                              
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 782193527
                              
                              --------------------------------------------------------------------------------------------------------
                              | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                              --------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT              |                        |     2 |   232 |     5   (0)| 00:00:01 |
                              |   1 |  NESTED LOOPS                 |                        |       |       |            |          |
                              |   2 |   NESTED LOOPS                |                        |     2 |   232 |     5   (0)| 00:00:01 |
                              |   3 |    NESTED LOOPS               |                        |     1 |    64 |     4   (0)| 00:00:01 |
                              |   4 |     TABLE ACCESS FULL         | EMPLOYEES_XT           |     1 |    52 |     3   (0)| 00:00:01 |
                              |   5 |     TABLE ACCESS BY USER ROWID| HUGE_XML_BI            |     1 |    12 |     1   (0)| 00:00:01 |
                              |*  6 |    INDEX RANGE SCAN           | SYS29998_30002_PKY_IDX |     2 |       |     0   (0)| 00:00:01 |
                              |   7 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE_ADRS_XT       |     2 |   104 |     1   (0)| 00:00:01 |
                              --------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 6 - access("SYS_SXI_0"."KEY"="SYS_SXI_1"."PKEY")
                              Bear in mind that creating this index on your table will actually execute the query above, on your big XML doc.
                              So, all things considered, if your ultimate goal is to load the data into relational tables, you may just try to run a multitable insert into the target tables.
                              1 2 Previous Next