Oracle7 Server Utilities

Example Export Sessions

The following example export session shows you how the command line and interactive methods in the full database, user, and table modes might be used.

Example Export Session in Full Database Mode

Only users with the DBA role, the EXP_FULL_DATABASE role, or the BECOME USER privilege can export in full database mode. In this example, an entire database is exported to file DBA.DMP with all GRANTs and all data.

Command Line Method

> exp system/manager full=Y file=dba.dmp

Interactive Method

> exp system/manager

...

Enter array fetch buffer size: 4096 >  (RETURN)

Export file: EXPDAT.DMP>  dba.dmp

E(ntire database), U(sers), T(ables): U> e

Export grants (Y/N): Y> y

Export table data (Y/N): Y> y

Compress extents (Y/N): Y> y

Export Messages

About to export the entire database...

. exporting tablespace definitions

. exporting profiles

. exporting user definitions

. exporting roles

. exporting resource costs

. exporting rollback segment definitions

. exporting database links

. exporting sequence numbers

. exporting job queues

. exporting refresh groups and children

. exporting cluster definitions

. about to export SYSTEM's tables ...

. .exporting table                DEF$_CALL       0 rows exported

. .exporting table            DEF$_CALLDEST       0 rows exported

. .exporting table         DEF$_DEFAULTDEST       0 rows exported

. .exporting table               DEF$_ERROR       0 rows exported

. .exporting table            DEF$_SCHEDULE       0 rows exported

. .exporting table                DEF$_TRAN       0 rows exported

. .exporting table            DEF$_TRANDEST       0 rows exported

. about to export SCOTT's tables ...

. . exporting table                   BONUS        0 rows exported

. . exporting table                    DEPT        5 rows exported

. . exporting table                     EMP       14 rows exported

. . exporting table                SALGRADE        5 rows exported

. about to export ADAMS's tables ...

. about to export JONES's tables ...

. about to export CLARK's tables ...

. about to export BLAKE's tables ...

. exporting referential integrity constraints

. exporting posttables actions

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting triggers

. exporting default and system auditing options

Export terminated successfully without warnings.

Example Export Session in User Mode

Exports in user mode can back up one database user. For example, aDBA may want to back up the tables of a deleted user 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.

This example shows user SCOTT exporting all his tables in user mode.

Command Line Method

exp scott/tiger file=scott.dmp

Interactive Method

exp scott/tiger

...

Enter array fetch buffer size: 4096 >    (RETURN)

Export file: EXPDAT.DMP>    scott.dmp

U(sers), or T(ables): U>  u

Export grants (Y/N): Y>  y

Export table data (Y/N): Y>   y

Compress extents (Y/N): Y> y

Export Messages

About to export SCOTT's objects ...

. exporting job queues

. exporting refresh groups and children

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. exporting stored procedures

. about to export SCOTT's tables ...

. exporting table                   BONUS        0 rows exported

. exporting table                    DEPT        7 rows exported

. exporting table                     EMP       22 rows exported

. exporting table                    PROJ        0 rows exported

. exporting table                SALGRADE        5 rows exported

. exporting synonyms

. exporting views

. exporting referential integrity constraints

. exporting triggers

. exporting posttables actions

Export terminated successfully without warnings.

Example Export Sessions in Table Mode

In table mode, you can export table data or the table definitions. (If no data is exported, the CREATE TABLE statement is placed in the export file, with grants and indexes, if they are specified.)

A user with the EXP_FULL_DATABASE role can use this mode to export tables from any user's schema by specifying TABLES=(schema.table). If schema is not specified, it defaults to the previous schema from which an object was exported. If there is not a previous object, it defaults to the exporter's schema. In the following example, schema defaults to SYSTEM for table A and to SCOTT for table C:

> exp system/manager tables=(a, scott.b, c, mary.d)

A user without the EXP_FULL_DATABASE role can export only tables that he or she owns.

Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Thus, you can use Export to uncluster tables when there is not enough free space in your database.

Example 1

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

Command Line Method

> exp system/manager tables=(mort.bar, mary.app) grants=Y indexes=Y

Interactive Method

> exp system/manager

...

Enter array fetch buffer size: 4096 >    (RETURN)

Export file: EXPDAT.DMP >   (RETURN)

E(ntire database), U(sers), T(ables): U>  t

Export table data (Y/N): Y>    (RETURN)

Compress extents (Y/N): Y> y

Export Messages

About to export specified tables ...

Table Name:  mort.bar            [interactive session only]

Current user changed to MORT

. exporting table                 BAR   2355 rows exported

Table Name:  mary.app             [interactive session only]

Current user changed to MARY

. exporting table                 APP   14947 rows exported

Table Name:  (RETURN)             [interactive session only]

[Export writes file]

Example 2

In this example, user LEWIS exports selected tables that he owns.

Command Line Method

> exp lewis/newyork file=lew.dmp tables=(credits, debits)

Interactive Method

exp lewis/newyork

...

Enter array fetch  buffer size: 4096 >    (RETURN)

Export file: EXPDAT.DMP>    lew.dmp

U(sers), T(ables): U>  T

Export table data (Y/N): Y> y

Compress extents (Y/N): Y> y

Export Messages

About to export specified tables ...

Table Name:  credits                    [interactive session only]

. exporting table           CREDITS  423 rows exported

Table Name:  debits                     [interactive session only]

. exporting table           DEBITS   423 rows exported

Table Name:  (RETURN)                   [interactive session only]

[Export writes file]