[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Migrating a MySQL table using Oracle SQL Developer 1.5, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Jayaram Krishnaswamy | January 2010 |
MySQL Oracle
Oracle SQL Developer Tool is a stand alone graphic database developer tool that connects to Oracle as well as third-party databases which can be used to perform a variety of tasks from running simple queries to migration of databases from third party vendor products to Oracle.
This article by Dr. Jayaram Krishnaswamy, shows how the reader may use Oracle's most recent tool, the Oracle SQL Developer 1.5 to work with the MySQL database. An example of migrating a table in MySQL to Oracle 10G XE is also described.
The Oracle SQL Developer Tool has steadily improved from its beginnings in version 1.1. The earlier versions are briefly explained here. The latest version, SQL Developer 1.5.4 released in March 2009 was described in this article.
The SQL Developer tool[(1.5.4.59.40)] bundle can be downloaded from Oracle's web site, Oracle Technology Products. When you unzip the bundle you are ready to start using this tool. You may get an even more recent version of this tool as it is continuously updated.
It is assumed that you have a MySQL Server that you can connect to and that you have the required credentials. The MySQL server used in developing this article was installed when the XAMPP bundle was installed. Reader will benefit by reading earlier MySQL articles 1, 2, 3 on the Packt site.
Connecting to MySQL
Out of the box Oracle SQL Developer 1.5.4 only supports Oracle and MS Access. The product documents clearly says that it can connect to other database products. This article will show how this is achieved. In order to install products from Oracle you must have username and password for the Oracle web Account.
Bring up the Oracle SQL Developer application by clicking the executable. The program starts up and after a while the user interface gets displayed as shown.
Right click on Connection, the New Connection page opens as shown displaying the default connection to the resident Oracle 10G XE server.
Click the menu item Help and choose "Check for Updates". This brings up the wizard displaying the Welcome screen as shown in the next figure.
Click Next. The "Source" page of the wizard shows up as shown.
The updates for Oracle SQL Developer is already chosen. Place a check mark for "Third Party SQL Developer Extensions". You can choose to install looking for updates on the internet or from the downloaded bundle, if it exists. First try the internet and click Next. This brings up the "Updates" page of the wizard as shown in the next figure.
Read the warning on this window. The extensions are not evaluated by Oracle but available. The details of available extensions are as follows: OrindaBuild Java Code Generator version 6.1.20090331 shown in the next figure.
The JTDS DBC Driver version 11.1.58.17 shown in the next figure.
The MYSQL JDBC driver shown in the next figure:
The last one is a patch for the Oracle SQL Developer to fix some of the import, LDAP and performance issues as shown.
For this article only the JTDS JDBC driver for MS SQL Server and the MySQL JDBC options were checked. The License agreements are for the JTDS drivers. Click Next. The License agreements must be accepted. Click I Agree. Click Next. This is the download step of the wizard. To proceed further you must have the Oracle Web Account username and password. Here you have the option to signup as well. After a while the new extensions are downloaded as shown in the next figure.
Click Finish to close the wizard. You need to restart SQL Developer to complete the installation of the extensions. Click Yes on the "Confirm Exit" window that shows up.
Now, when you click New Connection to create a new connection you display the "New / Select Database Connection" as shown. You can now see that other 3rd party databases are added to the window. Choose the tab for MySQL.
Fill in the required details as shown in the next figure appropriate for your MySQL installation. You must provide a name for the connection. Herein the connection is named, My_MySQL. The credentials must be provided as shown or that which is appropriate for your installation.
The port is the default designated for this server when you install the product. You may accept the other defaults on this page and click Test. The word "success" gets displayed in the status label at bottom left. The connection name and connection details gets added to the page shown above.
MySQL Database and Objects
When you go back to the main window of Oracle SQL Developer you can expand the connection node to reveal the two connections, one to Oracle 10G Xe and the other to the MySQL Server as shown. If you do not have Oracle 10G XE or you have not worked with Oracle 10G XE search the blog, http://hodentek.blogspot.com for Oracle 10G XE as there are links to a large number of beginners article on Oracle 10G XE.
The objects can be expanded to show even finer details. For example the tables node in the MySQL Server is expanded to reveal the table data as shown in the next figure.
Migrating from MySQL to Oracle 10G XE
The easiest way to migrate tables from MySQL to Oracle is to use the Quick Migrate menu item in the Oracle SQL Developer 1.5. Quick Migrate is a wizard based migration tool which is very easy to use. It is accessed as shown in the next figure.
It is important that connections exist for both MySQL and Oracle 10G XE in the SQL Developer 1.5. As seen earlier we have both these connections established and tested to be working. We have MyOra10G connection for Oracle 10G XE and My_MySQL for MySQL.
In what follows we will be taking a table from2k8 in MySQL to the Oracle 10G XE. The table is shown in the following figure.
Highlight the from2k8 table in the My_MySQL Connection in the TestMove database as shown. From Migration | Quick Migrate, the Migration wizard with 6 steps can be launched as shown in the next figure, the first step of the wizard. The Source Connection is the connection to the database from which the table will be migrated. In this case it is the My_MySQL connection.
Clicking the Next button will bring up the following window where you can select the Target Connection which connects to the database into which the table will be migrated. This operation needs several permissions which are shown in the numbered bullets for single schema. For multi-schema migration additional permissions will be required. The target connection will be to Oracle 10G XE as seen in this window.
Clicking the Next button will bring up the Step 3 of the migration process as shown.
Migration process requires a repository which may or may not be retained after migration. The default is not to keep the repository. This option is chosen since we do not wish to work with the repository for this example. Clicking the Next button will bring up the Step4 of the wizard as shown. In here a pre-migration check will be made to verify the requirements and if successful migration will take place.
Click on the Verify button. The verification command will be processed and the results are reported in the same window as shown in the next figure. However before this can happen you get a Privilege Warning message as shown relating to multi-schema migration.
Click on the OK button. The results from verification are reported as shown.
Click on the Next button. This brings up the Migration Type page of the wizard as shown. In here, you can choose to "Migrate Tables", "Migrate Tables and Data" or "Migrate Everything". Here "Migrate Tables and Data" Is selected.
Click on the Next button. The "Summary" page of the wizard gets displayed as shown. The choices made are shown.
Clicking the Finish button puts the wizard in the processing mode wherein a number of steps are completed as shown from creating the repository to capturing the table information and converting the information for Oracle; and moving the data. The repository is removed as this was chosen. If the repository is chosen to be persisted then a number of other necessary objects will remain in the Oracle 10G Database.
When the processing is completed the table from2k8 gets migrated to Oracle 10G Xe as shown in the next figure.
It may be noted that in case the migration is not effectively concluded or user terminated for any reason there is a possibility of repository remaining in the Oracle database. This can be deleted using the repository management menu item under Migration. Also, the wizard can be completed in just one step by right clicking the from2k8 table and choosing Quick Migrate. This brings up a single large screen where the choices made in the step-by-step process can be made.
Summary
The article describes in detail the steps you need to take in order to work with MySQL using Oracle's latest developer tool, the Oracle SQL Developer 1.5. The article also describes the process of taking over a table in MySQL to Oracle 10G XE using this tool.
If you have read this article you may be interested to view :
- MySQL Data Transfer using SQL Server Integration Services (SSIS)
- Transferring Data from MS Access 2003 to SQL Server 2008
- Migrating from MS SQL Server 2008 to EnterpriseDB
- MySQL Linked Server on SQL Server 2008
About the Author :
Jayaram Krishnaswamy
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.
Books From Packt
|
|
Post new comment