bcp out .csv file importing into Oracle 10g

Hi,
Is there is a way we can import .csv files into Oracle 10g directly using imp command.
we usually use import command with dump files like
imp schemaname/password full=y file=filename
Since the sqlloader approach to load .csv file into oracle 10g taking long time.  Our Oracle 10g DB is running on AIX OS.
Any ideas, links, resources highly appreciated. Thanks in advance.
LVL 7
gudii9Asked:
Who is Participating?
 
ravindran_eeeCommented:
A conventional path load executes SQL INSERT statement(s) to populate table(s) in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load, therefore, does not compete with other users for database resources so it can usually load data at near disk speed

You should be going for Direct load for faster performance.

Below link provides exhaustive details on the same

http://www.cs.umbc.edu/help/oracle8/server.815/a67792/ch08.htm
0
 
sathyagiriCommented:
You cannot use imp to load .csv files.

Two methods

1. Use sql loader.

2. Use PL/SQL, DBMS_file methods.

SQL loader is probably the easiest way. Are you using conventional load or direct load?

How big is the file?
0
 
johnsoneSenior Oracle DBACommented:
There is also external tables.  This allows a file loaded with SQL*Loader to look like a real table, but it is not stored in the database.  You can use and insert to create table as select to move the data from the external table into another location.  The advantage of using just SQL*Loader is if you need to modify the data, the process becomes a little easier.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gudii9Author Commented:
I have about 100 tables in schema total size of schema is around 2 gig. Some tables big and some tables small amount of data. Average size of table is say 200 mb
0
 
sathyagiriCommented:
2 gig is not a big size for a database. SQL loader shd do the job for you.
0
 
gudii9Author Commented:
can we do it in say around 2-4 hors. We do not want to do more than half day.Please advise
0
 
gudii9Author Commented:
what is the difference between conventional and direct load. I am not sure on that.  Can you please elaborate on that.
0
 
sathyagiriCommented:
The only catch with direct path is you will have to disable/drop any constraints or indexes on the table before loading and recreate them after loading.
0
 
gudii9Author Commented:
As in  23605877 how to exactly use 'External Tables'. How does it work?. Can you please explain me more details. Thanks in advance.
0
 
johnsoneSenior Oracle DBACommented:
I have not used external tables a lot.

The basic idea is that the definition of the table is the SQL*Loader control file.  When the external table is accessed, the data is temporarily "loaded" and accessed as a table.  I believe the only access is a full table scan, I do not believe you can index them.

Saves a step of loading into a temporary table and all operations can be initiated from the database.
0
 
gudii9Author Commented:
Thank you very much. I appreciate your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.