Oracle logical backup... for use in MySQL or SQL Server

Question: Is logical backup output script is only for use in Oracle only, or it could be prepared for use in MySQL or SQL Server when backup is being produced in Oracle?

If it is for use in Oracle only, are there utilities with MySQL, SQL Server, etc. to modify the backup script before it is executed in MySQL, SQL Server?

Thank you.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If you are referring to a script that has a TON of DDL and DML statements, then it is probably database specific.

How a table/index/key is created is probably product specific.

Based on your previous question on this topic:
What exactly are you trying to do?

If someone provided you an Oracle backup, you'll need Oracle to recover.

If someone create a script with a TON of create table/index scripts with even MORE INSERT statements, you'll need to port them.  I don't know of a tool that will to this.

It gets worse if there are stored procedures/functions/triggers/etc...
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you for your good comments.

Here is the situation.

The client has a Oracle database with number of tables. I want a backup script to create these tables with its data content less PK, FK, Triggers, etc.

Why; I am only interested in their data content. I am figuring that after I execute the script and created the tables even without PKs and FKs I can do what like to do with the data. It is read only situation and data will not be modified.

Of course if PKs and FKs could be included in the original script, that will be great because the script will be identical in MySQL for joins (I suppose).

As far as data type is concerned, that also could be handled with a global find/replace actions.

If a script described above not possible, then the owner of the database possibly could write an stored proc to export the tables as table1.txt, table2.txt (table1 or table2 are oracle table names).

Now, I could easily (I suppose) could upload them to MySQL or SQL Express. With entity diagram printout at hand, if necessary, I could install the required PKs and FKs.

Do you see any chance of success in either of above methods? With this background, how would you suggest it should be done knowing that,

Oracle will not be purchased.
It is a short fused project (quick and dirty) but reliable.

Thanks again,

slightwv (䄆 Netminder) Commented:
How much data are you talking about?

Oracle XE is free.  One CPU, 11G max size and a few other restrictions that you can find in the License Agreement but still free.

Never used it but it looks like MySQL has a migration tool that may do everything:

If that doesn't work:
Check out dbms_metadata.get_ddl in Oracle for the tables and object DDL.  Then a global search/replace would probably work to pre-create the tables.

For the data, you will likely need a a text file (fixed width or delimited) or a generated script of INSERT statements.

I would look at Oracle to Oracle XE.  then an export/import would be pretty simple/easy.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.