Exporting data from Oracle Databases

by Andrew Albertson
by Andrew Albertson

There are two ways to export data from an Oracle 10g database. Data Pump or the Export utility.

The export utility has been around for quite some time, and prior to 10g was the only way to export data. But 10g introduced a new method called data pump.

Data Pump seems to be allot faster and more useful, but export is still necessary if you plan to then import the data into an older database. Although I have found you might have to use the version of export utility that is compatible with the database you plan to import into, rather than the ‘new’ version that comes with 10g.

The other thing to note is, that the command line syntax is very different.

With the export utility, to import or export a table you would do something like this.

For Table Exports

exp scott/tiger@db10g OWNER=jones TABLES=scott.emp,

But using data pump, it is like this,

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

For Schema Exports

exp scott/tiger@db10g OWNER=jones

Vs

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp

For Full Database Exports/Imports

exp scott/tiger@db10g FULL=Y

Vs

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp

There are of course lots of other options for both export/dump commands so you might want to check this links before rushing ahead.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm
ORACLE-BASE – Oracle Database 10g Data Pump.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s