1 2 Previous Next 26 Replies Latest reply: Feb 12, 2013 3:54 PM by odie_63 RSS

    ORA-04030: out of process memory Loading Large XML File

    Kevin_K
      Experts: I am trying to load a 2.1G XML file into an Object Relational table . The xml schema document xsd is already registered successfully. It fails with the following error :
      ORA-04030: out of process memory when trying to allocate 4032 bytes
      (qmxtgCreateBuf,kghsseg: kolaslCreateCtx)
      ORA-06512: at "SYS.XMLTYPE", line 296
      ORA-06512: at line 1
      I am able to load the document successfully in SECUREFILE BINARY XML table but that would not work as I need to create relational view on top of this table which does not work with SECUREFILE BINARY XML storage.

      Please suggest what may be the workaround here?

      Thanks
      Kevin
        • 1. Re: ORA-04030: out of process memory Loading Large XML File
          mdrake-Oracle
          Database release ?

          Can you try loading it via the createResource() api or the FTP protocol..
          • 2. Re: ORA-04030: out of process memory Loading Large XML File
            mdrake-Oracle
            The other question that comes to mind is do you need to load it as one file, or are you going to be building the relational view over a repeating element within the file...
            • 3. Re: ORA-04030: out of process memory Loading Large XML File
              Kevin_K
              MDrake: I am trying to load like this:
               insert into TEST_HUGE_XML
                  values(
                    xmltype(
                      bfilename('XMLDIR', 'huge_xmldoc.xml')
                    , nls_charset_id('AL32UTF8')
                    , 'huge_xmldoc.xsd'
                    )
              db version: 11.2.0.3
              I saw an example of loading using createresource API here:
              http://www.oracle-developer.net/display.php?id=416
              
              
              SQL> DECLARE
                2     v_return BOOLEAN;
                3  BEGIN
                4     v_return := DBMS_XDB.CREATERESOURCE(
                5                    abspath => '/public/demo/xml/db_objects.xml',
                6                    data    => BFILENAME('XML_DIR', 'db_objects.xml')
                7                    );
                8     COMMIT;
                9  END;
               10  /
              
              PL/SQL procedure successfully completed.
              How do i load the huge xml document in my custom object relational table which was created like this:
              CREATE TABLE HUGE_XML OF XMLTYPE
                  XMLTYPE STORE AS OBJECT RELATIONAL
                  XMLSCHEMA "huge_xmldoc.xsd"
                  ELEMENT "root_element"
              }
              
              Thanks
              
              Edited by: Kevin_K on Feb 8, 2013 9:35 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 4. Re: ORA-04030: out of process memory Loading Large XML File
                Kevin_K
                MDrake: I need to load the all the content from one the huge xml file to the Oracle OR table.

                My xmlstructure looks like this:
                <?xml version="1.0" encoding="UTF-8" standalone="no" ?> 
                <EMPLOYEES CREATED="2013-02-06T12:33:00" xsi:noNamespaceSchemaLocation="http://supporthtml.oracle.com/huge_xmldocument.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                 <EMPLOYEE_TRANS MODE="A" emp_id="1">
                  <emp_nm>SCOTT</emp_nm> 
                  <emp_dept>FN</emp_dept> 
                  <mgr>DON</mgr>
                  <sal>4000</sal>
                  <updt_ts>2013-02-06T12:28:00</updt_ts> 
                  </EMPLOYEE_TRANS>
                <EMPLOYEE_TRANS MODE="A" emp_id="2">
                  <emp_nm>KEVIN</emp_nm> 
                  <emp_dept>HR</emp_dept> 
                  <mgr>MIKE</mgr>
                  <sal>3000</sal>
                  <updt_ts>2013-02-06T12:29:00</updt_ts> 
                  </EMPLOYEE_TRANS>
                 ........ 
                  
                </EMPLOYEES>
                Thanks
                • 5. Re: ORA-04030: out of process memory Loading Large XML File
                  mdrake-Oracle
                  Are you sure about this, or do you really just need to load all of the EMPLOYEE_TRANS elements ?.

                  Is there any value to have an table containing an EMPLOYEES document that contains 1000's of EMPLOYEE_TRANS elements, Vs a table that simply contains 1000's of EMPLOYEE_TRANS documents
                  • 6. Re: ORA-04030: out of process memory Loading Large XML File
                    mdrake-Oracle
                    To answer your earlier question

                    You allow register schema to create the table for you by annotating the XML Schema to have xdb:defaultTable="HUGE_XML" on the "root_element" element.

                    Then as long as your instance document uses XMLSchemaInstance tags to identify the XMLSchema it is associated with XMLDB will automatically store the content of the document in the HUGE_XML table when createResource or ftp is used to store the document in the XML DB repository. With this technique I have seen documents over 13G loaded. There are certain optimizations we can do when working with large documents via FTP and createResource that we cannot do with a direct insert.

                    My earlier question still stands, are you sure you really need to load this as one large document..
                    • 7. Re: ORA-04030: out of process memory Loading Large XML File
                      Kevin_K
                      MDrake:
                      My earlier question still stands, are you sure you really need to load this as one large document..
                      My options are still open. I would prefer to do whatever is the best practice for storing large documents. Please suggest me the options per Oracle's best practices guidelines. Are there any drawbacks for storing 2G xml document into Object Relational XML table?

                      Thanks
                      • 8. Re: ORA-04030: out of process memory Loading Large XML File
                        Kevin_K
                        Are you sure about this, or do you really just need to load all of the EMPLOYEE_TRANS elements ?. 
                        
                        Is there any value to have an table containing an EMPLOYEES document that contains 1000's of EMPLOYEE_TRANS elements, Vs a table that simply contains 1000's of EMPLOYEE_TRANS documents
                        We don't have any control over the structure of the EMPLOYEES document as this is sent to us from a third party. We are required to store the data for each element/attribute into an Oracle XML table so the users can run plain vanilla SQL on those.

                        Thanks
                        • 9. Re: ORA-04030: out of process memory Loading Large XML File
                          odie_63
                          Kevin_K wrote:
                          I am able to load the document successfully in SECUREFILE BINARY XML table but that would not work as I need to create relational view on top of this table which does not work with SECUREFILE BINARY XML storage.
                          What doesn't work when using Binary XML? Any errors?

                          Maybe we should first focus on solving this issue before jumping to another solution.
                          • 10. Re: ORA-04030: out of process memory Loading Large XML File
                            Kevin_K
                            Odie_63:
                            The insert works.
                            
                            CREATE TABLE HUGE_XML
                                    (xml_col XMLTYPE)
                                  XMLTYPE xml_col STORE AS SECUREFILE BINARY XML
                                  TABLESPACE XML_TBS
                             
                            
                            
                            INSERT INTO HUGE_XML
                            VALUES (xmltype(bfilename('XMLDIR','huge_xmldoc.XML'), nls_charset_id('AL32UTF8')))
                            -- Should be using AL32UTF8 for DB character set with XML
                            
                            commit;
                            
                            -- This succeeeds.
                            
                            -- this fails:
                            
                            select  x1.* 
                               from HUGE_XML T,
                                  XMLTABLE('/employees'
                                        PASSING T.object_value
                                        COLUMNS CREATED TIMESTAMP  PATH '@CREATED'
                            ) x1
                            
                            [Error] Execution (61: 21): ORA-00904: "T"."OBJECT_VALUE": invalid identifier
                            Thanks
                            • 11. Re: ORA-04030: out of process memory Loading Large XML File
                              odie_63
                              The column name is "XML_COL", not "OBJECT_VALUE".

                              OBJECT_VALUE is a pseudocolumn used to access the content of an object table, an XMLType table for example.
                              Here, you have a regular relational table, with an XMLType column named XML_COL.
                              select  x1.* 
                                 from HUGE_XML T,
                                    XMLTABLE('/EMPLOYEES'
                                          PASSING T.xml_col
                                          COLUMNS CREATED TIMESTAMP  PATH '@CREATED'
                              ) x1
                              • 12. Re: ORA-04030: out of process memory Loading Large XML File
                                Kevin_K
                                Odie: Thanks for this.

                                Questions:

                                1)

                                If I try to add another column , i get this syntax errror:
                                select  x1.* 
                                   from HUGE_XML T,
                                      XMLTABLE('/EMPLOYEES'
                                            PASSING T.xml_col
                                            COLUMNS CREATED TIMESTAMP  PATH '@CREATED',
                                             COLUMNS LOADED TIMESTAMP  PATH '@LOADED
                                ) x1
                                
                                [Error] Execution (64: 38): ORA-00907: missing right parenthesis
                                Sorry to bother you with these syntax questions.

                                2) Which option is better for performnace Storing as Object Relations or as SECUREFILE BINARY XML for loading large volumes of data into XMLTYPE tables and then running simple SQLs on those via views?

                                Thanks
                                • 13. Re: ORA-04030: out of process memory Loading Large XML File
                                  mdrake-Oracle
                                  select  x1.* 
                                     from HUGE_XML T,
                                        XMLTABLE('/EMPLOYEES'
                                              PASSING T.xml_col
                                              COLUMNS 
                                                 CREATED TIMESTAMP  PATH '@CREATED',
                                                 LOADED TIMESTAMP  PATH '@LOADED
                                  ) x1
                                  And then Assuming you are going to want to go after the EMPLOYEE information
                                  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
                                               MODE VARCHAR2(1) path '@MODE',
                                               ID      NUMBER(10)   path '@emp_id'
                                               NAME VARCHAR2(32), PATH 'emp_nm',
                                               ...
                                               TS    TIMESTAMP PATH 'updt_ts'
                                          ) x2 
                                  Basically Secure File Binary XML Storage will store the document in one ROW (LOB) when it is stored. Object relational will store it as multiple rows, one for each EMPLOYEE_TRANS element. So Binary XML will store the document much more quickly than OR. However when you query the document relationally the rows will have to be generated by processing the contents of the Secure File column and creating a psuedo row with column values from each EMPLOYEE_TRANS element. With OR the rows will already be stored as rows in a relational table under the cover so when you query the table you will have a relational query over relational data, which be much faster.

                                  So basically which is more important to you, fast initial storage or fast relational access to the contents of the EMPLOYEE_TRANS elements. If you are only going to run one relational query over the content then Secure File Binary Storage may be better, (eg you are going to do a CTAS to populate a relational table of your own from the document - Note that will probably take approxamately the same time as the load into OR storage). If you are going to run many relational (or even XQuery operations that access the leaf level nodes) then OR storage is probably better.

                                  BTW has you disabled DOM Fidelity when you do the initial load into OR storage.
                                  • 14. Re: ORA-04030: out of process memory Loading Large XML File
                                    mdrake-Oracle
                                    And I will still solve this problem by creating a table with columns

                                    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.
                                    1 2 Previous Next