exporting oracle database (creating a .sql file of data )

Question

I have an Oracle database on one PC. I have to migrate it to my other PC. Both use separate oracle installations. Now how can I export it from my one PC and then import it into other PC?

What are the commands for exporting and then importing it?

I want structure as well as data to be exported and then imported. I am using Oracle db.

Answer 1

Doesn´t seem to be a programming question. Or do you want to program a tool for the task?

Answer 2

You can export the data from your source database and import it into your new one.

A good link demonstrating the usage of these commands can be found here

It boils down to something like the following for exporting a full database:

%> exp USERID=<username>/<password> FULL=Y FILE=dbExport.dmp
%> imp USERID=<username>/<password> FILE=dbExport.dmp FULL=Y

There are a multitude of options for both commands to tailor it to your needs. For example, you can restrict the import command to only import certain tables via the TABLES parameter or you can move database object between users with TOUSER, FROMUSER parameters. There are also options on whether to export or import constraints, indexes, etc. You can find all the valid parameters for both commands by executing either:

%> exp help=Y
%> imp help=Y

Answer 3

You don't say which version of the database you are using. This is important information, because new features get added to Oracle with every release. For instance, in 10g Oracle introduced a new utility, DataPump, which replaces the older IMP and EXP (those utilities are still included in the install, they are just deprecated).

One of the neat things about DataPump is that we can execute from inside the database as well as from an OS command line. I recently posted an example of using DataPump from PL/SQL in this SO thread. `Oracle provide comprehensive documentation.

edit

Neither old fashioned IMP/EXP nor DataPump generate an SQL file (*). If that is really what you want (as opposed to just porting the schema and data somehow) then things get a bit trickier. Oracle has a package DBMS_METADATA which we can use to generate DDL scripts, but that won't deal with the data. To generate actual INSERT statements, your best bet is to use an IDE; Quest's TOAD will do this as will Oracle's (free) SQL Developer tool. Both IDEs will also generate DDL scripts for us.

(*) A Datapump import can derive the DDL statements from a prior Datapump export file, using the SQLFILE= parameter. But that is just the structure not the data.