Solved

data copy using sql loader or data pump or cntrolfile

Posted on 2011-02-21
29
653 Views
Last Modified: 2012-11-19
hi,
we are copying massive data from different tables(around 1 million rows  in that tables we need only few columns) using sql query which is having 5-6 tables(with 15 columns) data with some where conditions.
both source database and dest db  are oracle databases, so can you please suggest which method is better for
this data migration and please give the steps to do the following
0
Comment
Question by:nagavenuiluri
  • 12
  • 7
  • 4
  • +3
29 Comments
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 125 total points
ID: 34948006
< around 1 million rows  in that tables we need only few columns

Create a DBlink and copy the tables/columns over the link from source to destination.

The dblink can handle 1 million rows easily.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 34948011
Can you create a database link between them?  If so: insert into tableA(col1,coln) (select col1,coln from tableA@remotedb);

Second would be creating a CSV and use sql loader.

I don't think datapump can export specific columns.

You might also look into materialized views and moving them over.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34948046
Guess I should have also mentioned you can cut down on redo using database links with a pl/sql insert loop and commit every batch.  Where the batch size is up to you.
0
 
LVL 7

Assisted Solution

by:MrNed
MrNed earned 125 total points
ID: 34948073
Or create table as select blah, the use expdp/impdp to copy it over. If you need to repeat the process you would then have a dump file ready to go again.

But as sventhan mentioned, 1M rows is nothing.
0
 

Author Comment

by:nagavenuiluri
ID: 34948276
no actaully destination db is SFDC(sales force application) so we cant create any db links beween them, so only option is sql loader or data pump(if it is possible with specific columns) and pls send me the steps for sql loader or data pump
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34948297
What parts?

There are tons of links on creating a delimited file using sqlplus.  I prefer the XML method.

There are also tons of links on how to use sql loader.

What part do you need help with?
0
 
LVL 4

Accepted Solution

by:
pinkuray earned 125 total points
ID: 34948641
You can go with datapump export and import:
Check the below link which explains about using datapump utility in Oracle:

http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

And for SQL Loader please find this URL which clearly explains you how to use SQL Loader utility in details:

http://dataman42.com/dataman42/bookcase/EBooks/oracle/O%27Reilly%20-%20Oracle%20sqlloader.pdf

As per your requirement you can 1st save the output from your select query with conditional way to a csv file and then load it by using SQL Loader ... its bit fast. As you said you dont want all the columns to be loaded on your target table.

So best way to save the file by using spool and load using sql loader.




0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34951189
>>You can go with datapump export and import:

nagavenuiluri asked "(if it is possible with specific columns)".

pinkuray,
Please explain how this article explains how to export specific columns from a table?
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 34952148
I clearly mentioned my comments by saying you can use datapump but by reading the details from thr URL provided by me and also commented in end what is the best process i.e.:

"As per your requirement you can 1st save the output from your select query with conditional way to a csv file and then load it by using SQL Loader ... its bit fast. As you said you dont want all the columns to be loaded on your target table.

So best way to save the file by using spool and load using sql loader. "

I think we all are here to help and to get some solution rather then arguing...
If you know the best solution for solving  @nagavenuiluri issue then please post the correct answer.

No hard feelings @slightwv .. as per my knowledge you also provided many solutions to me but if you know the right answer then you or anyone is allowed to provide the solution.

Now its @nagavenuiluri decision what works for him.

Thanks ....
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34952163
I'm just saying datapump will not work for the question asked so why provide a link for the syntax?
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 34952899
TRUE.....but for what I was trying to explain is exporting the data into a CSV and then importing it into a table through SQL LOADER will solve the problem and I think it will be faster enough.

Do you agree on this ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34953080
Yes which is why I already mentioned CSV and sqlloader.
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 34958231
@slightwv

sorry for that i will keep in my mind and from next time i will try to past the link.

@nagavenuiluri
As per is requirement i taught this link will be more suitable
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34960503
That Tom Kyte code is pretty old.  I would look at the XML approach to creating delimited values:

http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822

In 11gR2 there is also a LISTAGG function that does all this for you.
0
 

Author Comment

by:nagavenuiluri
ID: 34963310
Thanks for ur responces,
we have concluded following methods, can pls suggest whether it is possible datapump or exp/imp with only few columns in a table abd what is ASCII FILE and can u pls give the steps to data loader in sales force.

1. plain ascii file delimeetrs as ","
2. data pump if which is non ASCII , but is db on SFDC(dest db sales force side)

pls confirm that we are geeting columns from 5-6 tables(only few columns) with where conditions whetehr data pump works or not.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963344
datapump is the new version of export/import (exp/imp).  I'm pretty sure you cannot export specific columns.

For us to provide any more information than we already have, we would need more information about your exact requirements.

Table names, column formats, data in the columns (can the data in the columns contains commas?  

if so, we need to know before we set up the control file and data extract)

What table is the data going to be loaded into?

We can dummy up some simple CSV extracts, sql loader control files but they won't be much better than what is already in the links posted here.
0
 

Author Comment

by:nagavenuiluri
ID: 34967852
hey guys, i need to get the output data in CSV format, that what i need it.

giving me options using toad and using the server directly.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34969794
>>i need to get the output data in CSV format

Did you not review what I posted in:  http:#a34960503

If you need help getting the XML syntax for your specific tables, please post your table definitions and I'll see what I can do.
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 34994360
Generate the CSV file from your query for specific columns with conditional data can be done using UTL file concept or by suing a simple spool :

For looking into an example you can check the below URL which has provided a good example :

http://forums.oracle.com/forums/thread.jspa?threadID=834051

another good example is here:

http://plsql.wikidot.com/forum/t-41518


For Spooling data into CSV , here is a good example :

http://www.experts-exchange.com/Programming/Languages/Q_24463878.html?sfQueryTermInfo=1+10+30+csv+from+plsql

And is you like to use TOAD then you can run the query and generate output by clicking right button and saving the data into a CSV file.



0
 

Author Comment

by:nagavenuiluri
ID: 35013381
when we are using toad right clock and save it is ok, but in the columns i have comma separated vales so it is treating lie a new columns how to avaiod tat problem
ex: suppose party_name: boos, crooks so it will treat boos is one column and treat is one column how to avaoid this prob and save the file into CSV
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35013472
How to use Toad to export a CSV with optional enclosing values is a different question and needs to be asked as one.
0
 

Author Comment

by:nagavenuiluri
ID: 35013999
yes, but we came to know this issue after we gave the data to the end  people, and in that data we have for columns we hace comma vales
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35014035
And?  It still needs to be a separate question.

This question is specific to: exporting specific columns.  Anything more needs to be a different question.
0
 

Author Comment

by:nagavenuiluri
ID: 35014062
thats ok, if you want me ask in diff question i am fine i can ask like that, but is there any rule that i cant ask one more question whcih is related to same topic in one question.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35017076
>>but is there any rule that i cant ask one more question whcih is related to same topic in one question.

http://www.experts-exchange.com/help.jsp#hs=23&hi=23

There is also a link embedded in that link on asking a 'Related Question' that provides further details.
0
 

Author Comment

by:nagavenuiluri
ID: 35019744
Thanks a lot, i will ask separately for each question , so that we will get better answers.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

760 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

20 Experts available now in Live!

Get 1:1 Help Now