ORACLE 11G DATAPUMP OVERVIEW-PART I

Overview of Oracle Data Pump

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.

Data Pump Components

Oracle Data Pump is made up of three distinct parts:

• The command-line clients, expdp and impdp
• The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
• The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command-line.

 

Note:  All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string. This means that, for nonprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system. For privileged users, a default directory object is available.

When data is moved, Data Pump automatically uses either direct path load (or unload) or the external tables mechanism, or a combination of both. When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and resubmission of dictionary metadata.

Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features, except for XML schemas and XML schema-based tables.

The following are the major new features that provide this increased performance, as well as enhanced ease of use:

• The ability to specify the maximum number of threads of active execution operating on behalf of the Data Pump job. This enables you to adjust resource consumption versus elapsed time.

·        The ability to restart Data Pump jobs.

·        The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time; however, you can have multiple clients or jobs running at one time. You can also have multiple clients attached to the same job.

• Support for export and import operations over the network, in which the source of each operation is a remote instance.
• The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced.
• Enhanced support for remapping tablespaces during an import operation.
• Support for filtering the metadata that is exported and imported, based upon objects and object types.
• Support for an interactive-command mode that allows monitoring of and interaction with ongoing jobs.
• The ability to estimate how much space an export job would consume, without actually performing the export.
• The ability to specify the version of database objects to be moved. In export jobs, VERSION applies to the version of the database objects to be exported. In import jobs, VERSION applies only to operations over the network. This means that VERSION applies to the version of database objects to be extracted from the source database.
• Most Data Pump export and import operations occur on the Oracle database server. To make full use of Data Pump technology, you must be a privileged user. Privileged users have the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.

How Does Data Pump Access Data?

Data Pump supports two access methods to load and unload table row data: direct path and external tables. Because both methods support the same external data representation, data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the fastest method appropriate for each table.

Note:  Data Pump will not load tables with disabled unique indexes. If the data needs to be loaded into the table, the indexes must be either dropped or reenabled.

Direct Path Loads and Unloads:

 Data Pump technology enhances direct path technology in the following ways:

• Supports of a direct path, proprietary format unload.
• Improved performance through elimination of unnecessary conversions. This is possible because the direct path internal stream format is used as the format stored in the Data Pump dump files.
• Support of additional datatypes and transformations.

The default method that Data Pump uses for loading and unloading data is direct path, when the structure of a table allows it. Note that if the table has any columns of datatype LONG, then direct path must be used.

Situations in Which Direct Path Load Is Not Used:

If any of the following conditions exist for a table, Data Pump uses external tables rather than direct path to load the data for that table:

• A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.
• A domain index exists for a LOB column.
• A table is in a cluster.
• There is an active trigger on a pre-existing table.
• Fine-grained access control is enabled in insert mode on a pre-existing table.
• A table contains BFILE columns or columns of opaque types.
• A referential integrity constraint is present on a pre-existing table.
• A table contains VARRAY columns with an embedded opaque type.
• The table has encrypted columns
• The table into which data is being imported is a pre-existing table and at least one of the following conditions exists:
1. There is an active trigger
2. The table is partitioned
3. fgac is in insert mode
4. A referential integrity constraint exists
5. A unique index exists.

Supplemental logging is enabled and the table has at least 1 LOB column.

Situations in Which Direct Path Unload Is Not Used:

• If any of the following conditions exist for a table, Data Pump uses the external table method to unload data, rather than direct path:
• Fine-grained access control for SELECT is enabled.
• The table is a queue table.
• The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns.
• The table contains encrypted columns.
• A column of an evolved type that needs upgrading.
• A column of type LONG or LONG RAW that is not last.

External Tables

As of Oracle Database 10g, the external tables feature also supports writing database data to destinations external to the database. Data Pump provides an external tables access driver (ORACLE_DATAPUMP) that reads and writes files. The format of the files is the same format used with the direct path method. This allows for high-speed loading and unloading of database tables. Data Pump uses external tables as the data access mechanism in the following situations:

• Loading and unloading very large tables and partitions in situations where parallel SQL can be used to advantage
• Loading tables with global or domain indexes defined on them, including partitioned object tables
• Loading tables with active triggers or clustered tables
• Loading and unloading tables with encrypted columns
• Loading tables with fine-grained access control enabled for inserts
• Loading tables that are partitioned differently at load time and unload time

Note:   When Data Pump uses external tables as the data access mechanism, it uses the ORACLE_DATAPUMP access driver. However, it is important to understand that the files that Data Pump creates when it uses external tables are not compatible with files created when you manually create an external table using the SQL CREATE TABLE ... ORGANIZATION EXTERNAL statement. One of the reasons for this is that a manually created external table unloads only data (no metadata), whereas Data Pump maintains both data and metadata information for all objects involved.

Accessing Data Over a Database Link

When you perform an export over a database link, the data from the source database instance is written to dump files on the connected database instance. In addition, the source database can be a read-only database. When you perform an import over a database link, the import source is a database, not a dump file set, and the data is imported to the connected database instance. Because the link can identify a remotely networked database, the terms database link and network link are used interchangeably.