This page contains materials that were copied from Oracle's on-line documents.

The Export utility can back up logical database objects while the database is open and available for use. It writes a read-consistent view of the database's objects to an operating system file. System audit options are not exported. The Import utility can restore the database information held in previously created Export files. It is the complement utility to Export.

Before you begin using Export, be sure you take care of the following items (described in detail in the following sections):

bulletTo use Export, you must run the script catexp.sql or catalog.sql (which runs catexp.sql) after the database has been created. catexp.sql or catalog.sql needs to be run only once on a database.
bulletEnsure there is sufficient disk or tape storage to write the export file. You can use table sizes to estimate the maximum space needed: 

SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

bulletVerify that you have the required access privileges. To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

The following provides examples of the different types of Export sessions

bulletOnly users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data. 

exp SYSTEM/password FULL=y FILE=dba.dmp GRANTS=y ROWS=y

bulletUser mode exports can be used to back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another. In this example, user scott is exporting his own tables. 

exp scott/tiger FILE=scott.dmp OWNER=scott GRANTS=y ROWS=y COMPRESS=y

bulletIn this example, a DBA exports specified tables for two users.

exp SYSTEM/password FILE=expdat.dmp TABLES=(scott.emp,blake.dept) GRANTS=y INDEXES=y 

bulletIn this example, user blake exports selected tables that he owns.

exp blake/paper FILE=blake.dmp TABLES=(dept, manager) ROWS=y COMPRESS=y

The following provides examples of the different types of Import sessions

bulletIn this example, using a full database export file, an administrator imports the dept and emp tables into the scott schema. 

imp SYSTEM/password FILE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

bulletIn this example, a DBA imports all tables belonging to scott into user blake's account. 

imp SYSTEM/password FILE=scott.dmp FROMUSER=scott TOUSER=blake TABLES=(*)

Back to CS643 schedule