Chapter 9. Moving Data between Oracle Databases

Pleasure in the job puts perfection in the work. - Aristotle

This chapter describes how to move data from Oracle Database 10g Express Edition to Oracle Database 11g Edition using Oracle's Export/Import utilities. Oracle Database XE supports traditional Export/Import utilities and Data Pump Export/Import utilities. This chapter discusses these approaches in more detail, covering the following topics:

• Exporting and importing data

• Traditional Export/Import (exp/imp) utilities

• Data Pump Export/Import (expdp/impdp) utilities

• Moving data between Oracle Database 10g XE and Oracle Database 11g XE

Exporting and importing data

Oracle's Export and Import utilities are used to move the data between different machines, databases, and schema. When exporting, database objects are dumped into a proprietary binary file. This binary file is then used to import database objects into another Oracle database.

Oracle's Export and Import utilities are also used to perform...

Exporting and importing data

Oracle's Export and Import utilities are used to move the data between different machines, databases, and schema. When exporting, database objects are dumped into a proprietary binary file. This binary file is then used to import database objects into another Oracle database.

Oracle's Export and Import utilities are also used to perform logical database backup and recovery.

Traditional Export and Import (exp/imp) utilities

The traditional Export and Import utilities provide a simple way to move your data between Oracle databases. The Export utility extracts data from the database and generates a binary file as the output file. We can then import data into the destination database using this binary file.

The Export and Import utilities are invoked using the exp and imp commands respectively. These utilities can be used to move data between Oracle databases even if they reside on different platforms (hardware and software configurations).

The Export and Import utilities...

Moving data between Oracle Database 10g XE and Oracle Database 11g XE

Follow the guidelines presented in this section if you want to do either of the following:

• Move data between Oracle Database 10g XE and Oracle Database 11g XE

• Upgrade your Oracle Database 10g XE to Oracle Database 11g XE

The steps are as follows:

1. 1. Create a database directory object by connecting to Oracle Database 10g XE as SYS user:

   SQL> CREATE OR REPLACE DIRECTORY dump_dir AS 'c:\xetest';
   SQL> GRANT READ,WRITE ON DIRECTORY dump_dir TO system;
   
   

2. 2. Export the data from Oracle Database 10g XE:

   expdp system/******** full=Y EXCLUDE=SCHEMA:\'LIKE \'APEX_%\'\',SCHEMA:\'LIKE \'FLOWS_%\'\' directory=DUMP_DIR dumpfile=DB10G.dmp logfile=DB10G_expdp.log
   expdp system/******** TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ directory=DUMP_DIR dumpfile=DB10G2.dmp logfile=DB10G_expdp.log
   

3. 3. Uninstall Oracle Database 10g XE.

4. 4. Install Oracle Database 11g XE.

5. 5. Create a database directory object by connecting as SYS user:

   SQL> CREATE...

Exporting data using SQL Developer

Exporting data and metadata using SQL Developer is very simple. From the menu go to Tools | Data Export. This launches the Data Export wizard. You may choose to export both the metadata and data, or either of the two. It's pretty straightforward from the interface.

You should choose an appropriate method to import metadata or data depending on how the object was exported.

Summary

In this chapter we explored Oracle database utilities such as traditional Export/Import and Data Pump Export/Import to load and unload data from an Oracle database. We also learned to move data between Oracle Database 10g Express Edition and Oracle Database 11g Express Edition.

The next chapter will talk about database migration. We will learn to migrate our XE database to other database editions. The chapter provides step-by-step instructions on migrating from XE to Enterprise Edition.