Next: The Import Up: Exporting databases with Data Previous: Exporting databases with Data Contents

The Export

The first part of an export is the creation of a directory reference to put the dumpfile the will be created by the export utility. This is done with a "CREATE OR REPLACE DIRECTORY" statement. You can name the directory reference anything you like, here I call it "exportdir". Once its created, we grant read and write privs on that directory to the user that will be performing the export, in this case the user system.

bash-2.05$ echo $ORACLE_SID
test
bash-2.05$ su
Password: 
# mkdir /export/oracle_exports
# chown oracle:dba /export/oracle_exports
# exit
bash-2.05$ sqlplus /nolog  
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 8 11:35:26 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect sys/passwd as sysdba;
Connected.
SQL> create or replace directory exportdir as '/export/oracle_exports';
Directory created.

SQL> grant read, write on directory exportdir to system;
Grant succeeded.

SQL> quit

Now that the directory is ready and Oracle can write to it we can actually do the export. There are two binaries for exports: the traditional exp export utility and the Data Pump version expdp. Both work the same way but Data Pump offers significantly more features and performance than the traditional tool. (For instance, you can pause import or exports using Data Pump... hit Control-C while it's running and poke around. It won't stop the operation.) There are more options avalible than we'll use here, but we'll be performing a FULL export to the specified DUMPFILE and direct logging to the noted logfile. Notice that we write the path in the form "directory:filename.dmp", where "directory" is the Oracle reference name to the directory we setup in the previous step.

bash-2.05$ expdp system/passwd FULL=y \
> DUMPFILE=exportdir:Oracle-Oct11-fullexp.dmp \
> LOGFILE=exportdir:Oracle-Oct11-fullexp.log

Export: Release 10.1.0.2.0 on Monday, 11 October, 2004 17:49

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** FULL=y 
  DUMPFILE=exportdir:Oracle-Oct11-fullexp.dmp 
  LOGFILE=exportdir:Oracle-Oct11-fullexp.log 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.68 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/DE_SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
  ............
. . exported "WMSYS"."WM$VERSION_TABLE"               0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE"             0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE"  0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
***********************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /export/oracle_exports/Oracle-Oct11-fullexp.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 2 error(s) at 18:03

bash-2.05$

The export is now complete and ready to be used.

Next: The Import Up: Exporting databases with Data Previous: Exporting databases with Data Contents

2005-02-10