OraFAQ Forum: SQL & PL/SQL » Best way to move table

Home » SQL & PL/SQL » SQL & PL/SQL » Best way to move table (Oracle 11.2 ,Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Best way to move table [message #552148]

Mon, 23 April 2012 23:48

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

Report message to a moderator

Re: Best way to move table [message #552149 is a reply to message #552148]

Mon, 23 April 2012 23:49

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

Report message to a moderator

Re: Best way to move table [message #552150 is a reply to message #552149]

Mon, 23 April 2012 23:53

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

Report message to a moderator

Re: Best way to move table [message #552153 is a reply to message #552150]

Tue, 24 April 2012 00:02

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

Report message to a moderator

Re: Best way to move table [message #552154 is a reply to message #552153]

Tue, 24 April 2012 00:10

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Best way to move table [message #552157 is a reply to message #552154]

Tue, 24 April 2012 00:49

7premkumar
Messages: 11
Registered: March 2012
Location: CHENNAI

Junior Member

Could you please give me some example ,

Regards
Premkumar R S

Report message to a moderator

Re: Best way to move table [message #552160 is a reply to message #552157]

Tue, 24 April 2012 01:02

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Best way to move table [message #552161 is a reply to message #552154]

Tue, 24 April 2012 01:03

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Littlefoot
Messages: 21823
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

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

Report message to a moderator

Re: Best way to move table [message #552168 is a reply to message #552164]

Tue, 24 April 2012 01:43

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The above example is not suitable for 3 million records

At Oracle or MySQL side? And why?

Regards
Michel

Report message to a moderator

Re: Best way to move table [message #552170 is a reply to message #552168]

Tue, 24 April 2012 01:52

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

Report message to a moderator

Re: Best way to move table [message #552171 is a reply to message #552170]

Tue, 24 April 2012 01:58

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	Delete Redundant Table in Oracle
Next Topic:	Varying in list in PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:52:48 CST 2024