?
Solved

bcp out .csv file importing into Oracle 10g

Posted on 2009-02-10
11
Medium Priority
?
1,779 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:gudii9
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 700 total points
ID: 23605685
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 700 total points
ID: 23605877
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
 
LVL 7

Author Comment

by:gudii9
ID: 23606099
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 23606223
2 gig is not a big size for a database. SQL loader shd do the job for you.
0
 
LVL 7

Author Comment

by:gudii9
ID: 23606398
can we do it in say around 2-4 hors. We do not want to do more than half day.Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 23606739
what is the difference between conventional and direct load. I am not sure on that.  Can you please elaborate on that.
0
 
LVL 10

Accepted Solution

by:
ravindran_eee earned 600 total points
ID: 23608076
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
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 700 total points
ID: 23614191
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
 
LVL 7

Author Comment

by:gudii9
ID: 23626147
As in  23605877 how to exactly use 'External Tables'. How does it work?. Can you please explain me more details. Thanks in advance.
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 700 total points
ID: 23626304
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
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31545309
Thank you very much. I appreciate your help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

839 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