Lesson 3	Running Export
Objective	Explain how to use Export.

Running Oracle Export for Database Backup

This lesson will introduce you to the various options available using the Export utility. You can run Export two ways. The first is to call the utility directly from the operating system by executing exp80.exe from within the BIN directory of your Oracle installation. You can do this as an interactive dialog or by providing a parameter file. The second is by using Data Manager from within Oracle Enterprise Manager. For demonstration purposes, we will be using MS Windows for all examples within this course; you may be using a different operating system and will thus have some differences.
Oracle Export Utility
To use the Export utility, you

must have the create session privilege on your database;
should have the exp_full_database role enabled;
should have run the script ccatalog.sql under the schema SYS; and
have sufficient space to export your data.

There are three modes of exporting:

Table mode export: A user can export tables with his or her schema.
User mode export: A user can export all objects within his or her schema.
Full database mode: All objects can be exported.

Let us review the different methods to invoke Export.

Oracle RMAN Backup Recovery

Operating System Level

The Export utility can be found in the /bin directory under your oracle_home, such as C:\Oracle\bin. You invoke the Export utility as the user system, which has the default password of manager with the following command:

exp80 system/manager

This command invokes Export in an interactive mode. The following series of images demonstrates the export of an entire database using this procedure:

Export Database — Figure 1 - Exporting a Database

1) This starts Export in interactive mode and includes the entry of the interactive export options. — For Oracle 11g, you would use the `expdp` (Data Pump Export) utility instead of `exp`, as it is the recommended export tool in later versions of Oracle. The `expdp` command provides more options, flexibility, and improved performance over the older `exp` utility. Here's how the equivalent script might look in Oracle 11g:

C:\oracle11g\BIN>expdp system/manager DIRECTORY=backup_dir DUMPFILE=expdat.dmp FULL=Y LOGFILE=expdat.log Export: Release 11.2.0.1.0 - Production on Mon Dec 11 12:23:29 2023 Copyright (c) 1982, 2010, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining, and Real Application Testing options

**Explanation of Parameters**

**expdp**: The Data Pump Export utility command for Oracle 11g.

**system/manager**: Specifies the username and password for the export.

**DIRECTORY=backup_dir**: Specifies a database directory object where the dump file will be stored. You need to create this directory object in Oracle beforehand (e.g., `CREATE DIRECTORY backup_dir AS 'D:\backup';`).

**DUMPFILE=expdat.dmp**: Specifies the name of the export dump file.

**FULL=Y**: Exports the entire database. If you only want certain users or tables, you can replace this with `SCHEMAS=someuser` or `TABLES=someuser.sometable`.

**LOGFILE=expdat.log**: Specifies a log file to record the export process details.

**Additional Notes**

**Directory Object**: In Oracle 11g, you cannot directly specify the path for `DUMPFILE`. Instead, you must create a directory object in Oracle that points to the desired path on the filesystem. For example:

CREATE DIRECTORY backup_dir AS 'D:\backup'; GRANT READ, WRITE ON DIRECTORY backup_dir TO system;

**Data Pump vs. Traditional Export**: `expdp` is a more advanced tool than `exp` and offers better performance, network capabilities, and control over export/import operations.

This displays the beginning of your database export.

Displays the SCOTT AND DEMO tables being exported.

This displays the end of export after all tables have been exported.

Export utility

The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects such as

indexes,

comments, and

grants.

The extracted data is written to an Export file, as illustrated in Figure 1.
The interactive method demonstrated in the above images allows for few export options. A better method would be to specify a *parameter file* as part of the command itself. This command would look like this:
**Deprecated syntax used with Oracle 8**
C:\Oracle\bin> exp80 system/manager
PARFILE=backup.dat

This *parameter file* can contain any option that you might need to specify export information from your database. You can overwrite the parameters in this file by including them after the `PARFILE` entry.
An example of this is:

C:\Oracle\bin> exp80 system/manager PARFILE=backup.dat FULL=N

When performing a full database export, write all user data and database creation statements (data files, tablespaces, indexes, and the like) to your export file except for the database user SYS information.

**Oracle Enterprise Manager:**
A more graphical method to export data is provided by Oracle Enterprise Manager (OEM). By using the Data Manager component of OEM, you can invoke a wizard that will walk you through either the export or the import process. The first page of the Export Wizard is shown below. Please refer to the online OEM documentation that is included on the release CDs for further information.

First page of the Export Wizard.

Running Export - Quiz

Click the Quiz link below to check your understanding of the Export utility.
**Running Export - Quiz**
In the next lesson, you will learn to use the Export utility to perform a complete backup of your database.

2) This displays the beginning of your database export. — For Oracle 11g, you would use the `expdp` (Data Pump Export) utility instead of `exp`, as it is the recommended export tool in later versions of Oracle. The `expdp` command provides more options, flexibility, and improved performance over the older `exp` utility. Here's how the equivalent script might look in Oracle 11g:

C:\oracle11g\BIN>expdp system/manager DIRECTORY=backup_dir DUMPFILE=expdat.dmp FULL=Y LOGFILE=expdat.log Export: Release 11.2.0.1.0 - Production on Mon Dec 11 12:23:29 2023 Copyright (c) 1982, 2010, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining, and Real Application Testing options

**Explanation of Parameters**

**expdp**: The Data Pump Export utility command for Oracle 11g.

**system/manager**: Specifies the username and password for the export.

**DIRECTORY=backup_dir**: Specifies a database directory object where the dump file will be stored. You need to create this directory object in Oracle beforehand (e.g., `CREATE DIRECTORY backup_dir AS 'D:\backup';`).

**DUMPFILE=expdat.dmp**: Specifies the name of the export dump file.

**FULL=Y**: Exports the entire database. If you only want certain users or tables, you can replace this with `SCHEMAS=someuser` or `TABLES=someuser.sometable`.

**LOGFILE=expdat.log**: Specifies a log file to record the export process details.

**Additional Notes**

**Directory Object**: In Oracle 11g, you cannot directly specify the path for `DUMPFILE`. Instead, you must create a directory object in Oracle that points to the desired path on the filesystem. For example:

CREATE DIRECTORY backup_dir AS 'D:\backup'; GRANT READ, WRITE ON DIRECTORY backup_dir TO system;

**Data Pump vs. Traditional Export**: `expdp` is a more advanced tool than `exp` and offers better performance, network capabilities, and control over export/import operations.

This displays the beginning of your database export.

Displays the SCOTT AND DEMO tables being exported.

This displays the end of export after all tables have been exported.

Export utility

The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects such as

indexes,

comments, and

grants.

The extracted data is written to an Export file, as illustrated in Figure 1.
The interactive method demonstrated in the above images allows for few export options. A better method would be to specify a *parameter file* as part of the command itself. This command would look like this:
**Deprecated syntax used with Oracle 8**
C:\Oracle\bin> exp80 system/manager
PARFILE=backup.dat

This *parameter file* can contain any option that you might need to specify export information from your database. You can overwrite the parameters in this file by including them after the `PARFILE` entry.
An example of this is:

C:\Oracle\bin> exp80 system/manager PARFILE=backup.dat FULL=N

When performing a full database export, write all user data and database creation statements (data files, tablespaces, indexes, and the like) to your export file except for the database user SYS information.

**Oracle Enterprise Manager:**
A more graphical method to export data is provided by Oracle Enterprise Manager (OEM). By using the Data Manager component of OEM, you can invoke a wizard that will walk you through either the export or the import process. The first page of the Export Wizard is shown below. Please refer to the online OEM documentation that is included on the release CDs for further information.

First page of the Export Wizard.

Running Export - Quiz

Click the Quiz link below to check your understanding of the Export utility.
**Running Export - Quiz**
In the next lesson, you will learn to use the Export utility to perform a complete backup of your database.

Running Oracle Export for Database Backup

Operating System Level

Export utility

Running Export - Quiz