Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

data copy using sql loader or data pump or cntrolfile

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
nagavenuiluri
Asked:
nagavenuiluri
  • 12
  • 7
  • 4
  • +3
4 Solutions
 
sventhanCommented:
< 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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
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!

 
MrNedCommented:
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
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
pinkurayCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
pinkurayCommented:
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
 
slightwv (䄆 Netminder) Commented:
I'm just saying datapump will not work for the question asked so why provide a link for the syntax?
0
 
pinkurayCommented:
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
 
slightwv (䄆 Netminder) Commented:
Yes which is why I already mentioned CSV and sqlloader.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
@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
 
slightwv (䄆 Netminder) Commented:
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
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
pinkurayCommented:
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
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
How to use Toad to export a CSV with optional enclosing values is a different question and needs to be asked as one.
0
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
nagavenuiluriAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
nagavenuiluriAuthor Commented:
Thanks a lot, i will ask separately for each question , so that we will get better answers.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 12
  • 7
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now