1. Utilities
  2. Oracle Data Pump
  3. Oracle Data Pump Export
  4. Starting Oracle Data Pump Export

2.2 Starting Oracle Data Pump Export

Start the Oracle Data Pump Export utility by using the expdp command.

The characteristics of the Oracle Data Pump export operation are determined by the Export parameters that you specify. You can specify these parameters either on the command line, or in a parameter file.

Caution:

Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.

  • Oracle Data Pump Export Interfaces
    You can interact with Oracle Data Pump Export by using a command line, a parameter file, or an interactive-command mode.
  • Oracle Data Pump Export Modes
    Export provides different modes for unloading different portions of Oracle Database data.
  • Network Considerations for Oracle Data Pump Export
    Learn how Oracle Data Pump Export utility expdp identifies instances with connect identifiers in the connection string using Oracle*Net or a net service name, and how they are different from export operations using the NETWORK_LINK parameter.

Parent topic: Oracle Data Pump Export

2.2.1 Oracle Data Pump Export Interfaces

You can interact with Oracle Data Pump Export by using a command line, a parameter file, or an interactive-command mode.

Choose among the three options:

  • Command-Line Interface: Enables you to specify most of the Export parameters directly on the command line.

  • Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter, because parameter files cannot be nested. If you are using parameters whose values require quotation marks, then Oracle recommends that you use parameter files.

  • Interactive-Command Interface: Stops logging to the terminal and displays the Export prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an export operation started with the command-line interface, or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job.

Parent topic: Starting Oracle Data Pump Export

2.2.2 Oracle Data Pump Export Modes

Export provides different modes for unloading different portions of Oracle Database data.

Specify export modes on the command line, using the appropriate parameter.

Note:

You cannot export several Oracle-managed system schemas for Oracle Database, because they are not user schemas; they contain Oracle-managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS.

  • Full Export Mode
    You can use Oracle Data Pump to carry out a full database export by using the FULL parameter.
  • Schema Mode
    You can specify a schema export with Data Pump by using the SCHEMAS parameter. A schema export is the default export mode.
  • Table Mode
    You can use Oracle Data Pump to carry out a table mode export by specifying the table using the TABLES parameter.
  • Tablespace Mode
    You can use Data Pump to carry out a tablespace export by specifying tables using the TABLESPACES parameter.
  • Transportable Tablespace Mode
    You can use Oracle Data Pump to carry out a transportable tablespace export by using the TRANSPORT_TABLESPACES parameter.

Parent topic: Starting Oracle Data Pump Export

2.2.2.1 Full Export Mode

You can use Oracle Data Pump to carry out a full database export by using the FULL parameter.

In a full database export, the entire database is unloaded. This mode requires that you have the DATAPUMP_EXP_FULL_DATABASE role.

Using the Transportable Option During Full Mode Exports

If you specify the TRANSPORTABLE=ALWAYS parameter along with the FULL parameter, then Data Pump performs a full transportable export. A full transportable export exports all objects and data necessary to create a complete copy of the database. A mix of data movement methods is used:

  • Objects residing in transportable tablespaces have only their metadata unloaded into the dump file set; the data itself is moved when you copy the data files to the target database. The data files that must be copied are listed at the end of the log file for the export operation.

  • Objects residing in non-transportable tablespaces (for example, SYSTEM and SYSAUX) have both their metadata and data unloaded into the dump file set, using direct path unload and external tables.

Restrictions

Performing a full transportable export has the following restrictions:

  • The user performing a full transportable export requires the DATAPUMP_EXP_FULL_DATABASE privilege.

  • The default tablespace of the user performing the export must not be set to one of the tablespaces being transported.

  • If the database being exported contains either encrypted tablespaces or tables with encrypted columns (either Transparent Data Encryption (TDE) columns or SecureFiles LOB columns), then the ENCRYPTION_PASSWORD parameter must also be supplied.

  • The source and target databases must be on platforms with the same endianness if there are encrypted tablespaces in the source database.

  • If the source platform and the target platform are of different endianness, then you must convert the data being transported so that it is in the format of the target platform. You can use the DBMS_FILE_TRANSFER package or the RMAN CONVERT command to convert the data.

  • All objects with storage that are selected for export must have all of their storage segments either entirely within administrative, non-transportable tablespaces (SYSTEM/SYSAUX) or entirely within user-defined, transportable tablespaces. Storage for a single object cannot straddle the two kinds of tablespaces.

  • When transporting a database over the network using full transportable export, auditing cannot be enabled for tables stored in an administrative tablespace (such as SYSTEM and SYSAUX) if the audit trail information itself is stored in a user-defined tablespace.

  • If both the source and target databases are running Oracle Database 12c, then to perform a full transportable export, either the Oracle Data Pump VERSION parameter must be set to at least 12.0. or the COMPATIBLE database initialization parameter must be set to at least 12.0 or later.

Full Exports from Oracle Database 11.2.0.3

Full transportable exports are supported from a source database running at least release 11.2.0.3. To run full transportable exports set the Oracle Data Pump VERSION parameter to at least 12.0, as shown in the following syntax example, where user_name is the user performing a full transportable export: 

> expdp user_name FULL=y DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir 
      TRANSPORTABLE=always VERSION=12.0 LOGFILE=export.log

Full Exports and Imports Using Extensibility Filters

In the following example, you use a full export to copy just the audit_trails metadata and data from the source database to the target database: 

> expdp user/pwd directory=mydir full=y include=AUDIT_TRAILS
> impdp user/pwd directory=mydir

If you have completed an export from the source database in Full mode, then you can also import just the audit trails from the full export: 

> expdp user/pwd directory=mydir full=y
> impdp user/pwd directory=mydir include=AUDIT_TRAILS

To obtain a list of valid extensibility tags, use this query:

SELECT OBJECT_PATH FROM DATABASE_EXPORT_PATHS WHERE tag=1 ORDER BY 1;

Related Topics

Parent topic: Oracle Data Pump Export Modes

2.2.2.2 Schema Mode

You can specify a schema export with Data Pump by using the SCHEMAS parameter. A schema export is the default export mode.

If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_DATABASE role, then you can export only your own schema.

The SYS schema cannot be used as a source schema for export jobs.

Cross-schema references are not exported unless the referenced schema is also specified in the list of schemas to be exported. For example, a trigger defined on a table within one of the specified schemas, but that resides in a schema not explicitly specified, is not exported. Also, external type definitions upon which tables in the specified schemas depend are not exported. In such a case, it is expected that the type definitions already exist in the target instance at import time.

Related Topics

Parent topic: Oracle Data Pump Export Modes

2.2.2.3 Table Mode

You can use Oracle Data Pump to carry out a table mode export by specifying the table using the TABLES parameter.

In table mode, only a specified set of tables, partitions, and their dependent objects are unloaded. Any object required to create the table, such as the owning schema, or types for columns, must already exist.

If you specify the TRANSPORTABLE=ALWAYS parameter with the TABLES parameter, then only object metadata is unloaded. To move the actual data, you copy the data files to the target database. This results in quicker export times. If you are moving data files between releases or platforms, then the data files need to be processed by Oracle Recovery Manager (RMAN).

You must have the DATAPUMP_EXP_FULL_DATABASE role to specify tables that are not in your own schema. Note that type definitions for columns are not exported in table mode. It is expected that the type definitions already exist in the target instance at import time. Also, as in schema exports, cross-schema references are not exported.

To recover tables and table partitions, you can also use RMAN backups and the RMAN RECOVER TABLE command. During this process, RMAN creates (and optionally imports) a Data Pump export dump file that contains the recovered objects. Refer to Oracle Database Backup and Recovery Guide for more information about transporting data across platforms.

Carrying out a table mode export has the following restriction:

  • When using TRANSPORTABLE=ALWAYS parameter with the TABLES parameter, the ENCRYPTION_PASSWORD parameter must also be used if the table being exported contains encrypted columns, either Transparent Data Encryption (TDE) columns or SecureFiles LOB columns.

Related Topics

Parent topic: Oracle Data Pump Export Modes

2.2.2.4 Tablespace Mode

You can use Data Pump to carry out a tablespace export by specifying tables using the TABLESPACES parameter.

In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, then its dependent objects are also unloaded. Both object metadata and data are unloaded. In tablespace mode, if any part of a table resides in the specified set, then that table and all of its dependent objects are exported. Privileged users get all tables. Unprivileged users get only the tables in their own schemas.

Related Topics

Parent topic: Oracle Data Pump Export Modes

2.2.2.5 Transportable Tablespace Mode

You can use Oracle Data Pump to carry out a transportable tablespace export by using the TRANSPORT_TABLESPACES parameter.

In transportable tablespace mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces is exported. The tablespace data files are copied in a separate operation. Then, a transportable tablespace import is performed to import the dump file containing the metadata and to specify the data files to use.

Transportable tablespace mode requires that the specified tables be completely self-contained. That is, all storage segments of all tables (and their indexes) defined within the tablespace set must also be contained within the set. If there are self-containment violations, then Export identifies all of the problems without actually performing the export.

Type definitions for columns of tables in the specified tablespaces are exported and imported.  The schemas owning those types must be present in the target instance.

Starting with Oracle Database 21c, transportable tablespace exports can be done with degrees of parallelism greater than 1.

Note:

You cannot export transportable tablespaces and then import them into a database at a lower release level. The target database must be at the same or later release level as the source database.

Using Oracle Data Pump to carry out a transportable tablespace export has the following restrictions:

  • If any of the tablespaces being exported contains tables with encrypted columns, either Transparent Data Encryption (TDE) columns or SecureFiles LOB columns, then the ENCRYPTION_PASSWORD parameter must also be supplied..

  • If any of the tablespaces being exported is encrypted, then the use of the ENCRYPTION_PASSWORD is optional but recommended. If the ENCRYPTION_PASSWORD is omitted in this case, then the following warning message is displayed: 

    ORA-39396: Warning: exporting encrypted data using transportable option without password

    This warning points out that in order to successfully import such a transportable tablespace job, the target database wallet must contain a copy of the same database access key used in the source database when performing the export. Using the ENCRYPTION_PASSWORD parameter during the export and import eliminates this requirement.

Related Topics

Parent topic: Oracle Data Pump Export Modes

2.2.3 Network Considerations for Oracle Data Pump Export

Learn how Oracle Data Pump Export utility expdp identifies instances with connect identifiers in the connection string using Oracle*Net or a net service name, and how they are different from export operations using the NETWORK_LINK parameter.

When you start expdp, you can specify a connect identifier in the connect string that can be different from the current instance identified by the current Oracle System ID (SID).

To specify a connect identifier manually by using either an Oracle*Net connect descriptor, or an Easy Connect identifier, or a net service name (usually defined in the tnsnames.ora file) that maps to a connect descriptor.

To use a connect identifier, you must have Oracle Net Listener running (to start the default listener, enter lsnrctl start ). The following example shows this type of connection, in which inst1 is the connect identifier: 

expdp hr@inst1 DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

Export then prompts you for a password:

Password: password

To specify an Easy Connect string, the connect string must be an escaped quoted string. The Easy Connect string in its simplest form consists of a string database_host[:port][/[service_name]. For example, if the host is inst1, and you run Export on pdb1, then the Easy Connect string can be: 

expdp hr@\"inst1@example.com/pdb1" DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

If you prefer to use an unquoted string, then you can specify the Easy Connect connect string in a parameter file.

The local Export client connects to the database instance defined by the connect identifier inst1 (a Net service name), retrieves data from inst1, and writes it to the dump file hr.dmp on inst1.

Specifying a connect identifier when you start the Export utility is different from performing an export operation using the NETWORK_LINK parameter. When you start an export operation and specify a connect identifier, the local Export client connects to the database instance identified by the connect identifier, retrieves data from that database instance, and writes it to a dump file set on that database instance. By contrast, when you perform an export using the NETWORK_LINK parameter, the export is performed using a database link. (A database link is a connection between two physical database servers that allows a client to access them as one logical database.)

Related Topics

Parent topic: Starting Oracle Data Pump Export