?
Solved

Best method to load 50Mb xml file into an Oracle table to search on?

Posted on 2014-07-28
6
Medium Priority
?
1,252 Views
Last Modified: 2014-07-28
What is the best method (binary, lob, clob, external table, etc) to load this 50Mb xml file into an Oracle table to search on?

I've been reading through the Oracle XML DB Developer's Guide11g Release 2 (11.2). I've tried various methods of loading a very large 50mb XML file of XMLTYPE  into a table (CLOB, LOB, varchar2, etc) so I can perform a SQL query on the table to load Latitude and longitude values into an Oracle geospatial procedure I have set up.  The next method I want to try is Binary XML Storage,

create table XMLBIN of XMLType
XMLTYPE store as BINARY XML;

but I wanted to check here first since I've spend a lot of time on this already and I don't want to damage our heavily used test db.

I'm trying to use sqlldr to load a 50 mb xml file into an Oracle table. For each method I use, the locally managed tablespace grows over 10GB before stopping after 300 records loaded.  The error messages vary between method used but they all consume of 10GB of tablespace before stopping.  I had 16GB allocated but this seems like way too much memory being used.



CREATE TABLE TABLE_XML_DATA
(
  COL_XML_DATA  XMLTYPE
)
TABLESPACE ASM_LIMIT_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

---------------

sqlldr control file,

load data
infile 'c:\Geospat.xml'
into table TEST_USER.TABLE_XML_DATA
REPLACE
FIELDS TERMINATED BY ','
(
COL_XML_DATA lobfile (CONSTANT' c:\Geospat.xml' ) terminated by eof
)
0
Comment
Question by:talahi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40224471
I would first try BFILE and parse the XML from there.

Then I would probably load the XML file into a temp table using dbms_lob.loadclobfromfile:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#i998978

Once you can see the XML, I would use XMLTABLE to parse it.  The extract and extractvalue functions are deprecated and not very efficient.  XMLTABLE seems to be pretty decent.
0
 

Author Comment

by:talahi
ID: 40224490
Should I use a locally managed tablespace and what size memory would I expect it to use to store this in an XMLTABLE?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40224508
XMLTABLE is a function that parses an XMLTYPE data type and returns data as a 'normal' table.

When storing the file in an XMLTYPE data type locally or dictionary managed really shouldn't matter.

As far as the amount of memory to provide for the SGA, I really can't say.  I would start with whatever you have and see if Oracle complains that it cannot allocate more memory.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:talahi
ID: 40224516
ok thanks will give it a try.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40224524
Don't you want to leave this open until you have a working solution?

If it doesn't work, you'll end up with a duplicate question.
0
 

Author Comment

by:talahi
ID: 40224559
I won't get back to this until later and by then there might be others responding.  I don't want to waste anyone's time while I'm busy on other things.  You've given me enough to keep busy with for now and it confirms some things I've thought about.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question