what is the use of direct=true option in sql loader

Posted on 2009-02-19
Last Modified: 2013-12-18
I am loading the .csv files into oracle 10g using sqlloader using commands like

sqlldr schemaname/password@IPaddress:1521/orcl  contrlofilename_location.ctl ogfilename_location.log badfilename_location.log datafilenameLocation.csv

But for loading some of csv files( around 500 mb of data) it is taking around 4 hours. I read some where using direct=true reduces thes time. My question is does using direct=true option along with sqlloader removes some data? or does it has any side effects? what exactly does direct=true do. Any other easy ways to improve the performance of the load. Any ideas, links, resources, syntax, code highly appreciated. Thanks in advance
Question by:gudii9
    LVL 7

    Accepted Solution

    Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements.  The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format.  The fact that SQL is not being issued makes the entire process much less taxing on the database.  There are certain cases, however, in which direct path loads cannot be used (clustered tables).  To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.
    LVL 7

    Author Closing Comment

    thank you very much for your help. I appreciate it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now