Home » SQL & PL/SQL » SQL & PL/SQL » Best way to move table (Oracle 11.2 ,Linux)
Best way to move table [message #552148] Mon, 23 April 2012 23:48 Go to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
Hi,

We want to provide data dump from 3 tables Like A,B,C.

In C we have more than 3 million records.

What is the best way to move this data from our DB to customer DB.

Customer is running on MySql

Regards
Premkumar R S
Re: Best way to move table [message #552149 is a reply to message #552148] Mon, 23 April 2012 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
7premkumar wrote on Mon, 23 April 2012 21:48
Hi,

We want to provide data dump from 3 tables Like A,B,C.

In C we have more than 3 million records.

What is the best way to move this data from our DB to customer DB.

Customer is running on MySql


So please explain & clarify why you are posting in an Oracle forum
Re: Best way to move table [message #552150 is a reply to message #552149] Mon, 23 April 2012 23:53 Go to previous messageGo to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
Hi ,

I am having doubt on Mysql .So I need to know which way is best . we are using ORACLE 11.2

Regards
Premkumar
Re: Best way to move table [message #552153 is a reply to message #552150] Tue, 24 April 2012 00:02 Go to previous messageGo to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
I would like to know how can i export a table from oracle so that I can import it in mysql?

Premkumar
Re: Best way to move table [message #552154 is a reply to message #552153] Tue, 24 April 2012 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What capabilities does MySQL have (in order to "import" data)? If it is something like SQL*Loader that accepts plain text files (CSV), then creating such an output from an Oracle table might be one way to do that. 3 million records? Spool probably won't be able to do it, but UTL_FILE might.
Re: Best way to move table [message #552157 is a reply to message #552154] Tue, 24 April 2012 00:49 Go to previous messageGo to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
Could you please give me some example ,

Regards
Premkumar R S
Re: Best way to move table [message #552160 is a reply to message #552157] Tue, 24 April 2012 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What kind of an example? I guess that it is useless to produce any example until you know - for sure - how to import data, in what format, into MySQL.
Re: Best way to move table [message #552161 is a reply to message #552154] Tue, 24 April 2012 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
3 million records? Spool probably won't be able to do it, but UTL_FILE might.


Why? I think there is no difference between spool and utl_file in writing files, the limit is OS one but I may miss something.

Regards
Michel

[Updated on: Tue, 24 April 2012 01:04]

Report message to a moderator

Re: Best way to move table [message #552163 is a reply to message #552161] Tue, 24 April 2012 01:10 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OS file size limit is what I had on mind.

EDIT: So what? Spool & UTL_FILE will produce files which will have the same size. Which means that what I said is stupid. Sorry.

[Updated on: Tue, 24 April 2012 01:11]

Report message to a moderator

Re: Best way to move table [message #552164 is a reply to message #552160] Tue, 24 April 2012 01:11 Go to previous messageGo to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
Hi,

Oracle 11g
============
SQL>spool c.csv

SQL>select * from C;

Now, import the data to MySQL database using the following command.

mysql>LOAD DATA INFILE "/u01/oracle/c.csv" Into TABLE 'sh.c'
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r';


Note: The above command will help to import the CSV file to MySQL by command prompts


The above example is not suitable for 3 million records
so I need some other method

Regards
Premkumar
Re: Best way to move table [message #552168 is a reply to message #552164] Tue, 24 April 2012 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The above example is not suitable for 3 million records


At Oracle or MySQL side? And why?

Regards
Michel
Re: Best way to move table [message #552170 is a reply to message #552168] Tue, 24 April 2012 01:52 Go to previous messageGo to next message
7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI
Junior Member
Michel,

I mean it`s not suitable for my side .,it takes time more to run select statement because of huge data. I want to know is there is any other better way to import the data faster.

Regards
Premkumar R S
Re: Best way to move table [message #552171 is a reply to message #552170] Tue, 24 April 2012 01:58 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
MySQL Migration Toolkit
Quote:

is a graphical tool provided by MySQL AB for migrating schema and data from various relational database systems to MySQL (...) At the moment, MySQL Migration Toolkit is only available for Windows.

You specified you use Linux, but - if there's a Windows machine that is capable of running that product, maybe you could use it.
Previous Topic: Delete Redundant Table in Oracle
Next Topic: Varying in list in PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 18:36:35 CDT 2024