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.