Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

25
DBMS_DATAPUMP

The DBMS_DATAPUMP package is used to move all, or part of, a database between databases, including both data and metadata.

See Also:

Oracle Database Utilities for more information on the concepts behind the DBMS_DATAPUMP API, how it works, and how it is implemented in the Data Pump Export and Import utilities

This chapter contains the following topics:


Using DBMS_DATAPUMP


Overview

The support and functionality provided by DBMS_DATAPUMP is as follows:


Security Model

Security for the DBMS_DATAPUMP package is implemented through roles.

Roles

The existing EXP_FULL_DATABASE and IMP_FULL_DATABASE roles will be used to allow privileged users to take full advantage of the API. The Data Pump API will use these roles to determine whether privileged application roles should be assigned to the processes comprising the job.

EXP_FULL_DATABASE

The EXP_FULL_DATABASE role affects only Export operations. It allows users running these operations to do the following:

Although the SYS schema does not have the EXP_FULL_DATABASE role assigned to it, all security checks performed by Data Pump that require the EXP_FULL_DATABASE role will also grant access to the SYS schema.

IMP_FULL_DATABASE

The IMP_FULL_DATABASE role affects only Import and SQL_FILE operations. It allows users running these operations to do the following:

Although the SYS schema does not have the IMP_FULL_DATABASE role assigned to it, all security checks performed by Data Pump that require the IMP_FULL_DATABASE role will also grant access to the SYS schema.


Constants

There are several public constants defined for use with the DBMS_DATAPUMP.GET_STATUS procedure. All such constants are defined as part of the DBMS_DATAPUMP package. Any references to these constants must be prefixed by DBMS_DATAPUMP. and followed by the symbols in the following lists:

Mask Bit Definitions

The following mask bit definitions are used for controlling the return of data through the DBMS_DATAPUMP.GET_STATUS procedure.

Dump File Type Definitions

The following definitions are used for identifying types of dump files returned through the DBMS_DATAPUMP.GET_STATUS procedure.


Types

The types described in this section are defined in the SYS schema for use by the GET_STATUS function. The way in which these types are defined and used may be different than what you are accustomed to. Be sure to read this section carefully.

The collection of types defined for use with the GET_STATUS procedure are version-specific and include version information in the names of the types. Once introduced, these types will always be provided and supported in future versions of Oracle Database and will not change. However, in future releases of Oracle Database, new versions of these types might be created that provide new or different information. The new versions of these types will have different version information embedded in the type names.

For example, in Oracle Database 10g, release 1 (10.1), there is a sys.ku$_Status1010 type, and in the next Oracle Database release, there could be a sys.ku$_Status1110 type defined. Both types could be used with the GET_STATUS procedure.

Public synonyms have been defined for each of the types used with the GET_STATUS procedure. This makes it easier to use the types and means that you do not have to be concerned with changes to the actual type names or schemas where they reside. Oracle recommends that you use these synonyms whenever possible.

For each of the types, there is a version-specific synonym and a generic synonym. For example, the version-specific synonym ku$_Status1010 is defined for the sys.ku$_Status1010 type.

The generic synonym always describes the latest version of that type. For example, in Oracle Database 10g, release 1, the generic synonym ku$_Status is defined as ku$_Status1010. In a future release, there might be a ku$_Status1110 synonym for sys.ku$Status1110. Because the ku$_Status generic synonym always points to the latest definition, it would now point to ku$_Status1110 rather than to ku$_Status1010.

The choice of whether to use version-specific synonyms or generic synonyms makes a significant difference in how you work. Using version-specific names protects your code from changes in future releases of Oracle Database because those types will continue to exist and be supported. However, access to new information will require code changes to use new synonym names for each of the types. Using the generic names implies that you always want the latest definition of the types and are prepared to deal with changes in different releases of Oracle Database.

When the version of Oracle Database that you are using changes, any C code that accesses types through generic synonym names will need to be recompiled.


Note:

Languages other than PL/SQL must ensure that their type definitions are properly aligned with the version-specific definitions.


See Also:

GET_STATUS Procedure for additional information about how types are used

Worker Status Types

The worker status types describe what each worker process in a job is doing. The schema, object name, and object type of an object being processed will be provided. For workers processing user data, the partition name for a partitioned table (if any), the number of bytes processed in the partition, and the number of rows processed in the partition are also returned. Workers processing metadata provide status on the last object that was processed. No status for idle threads is returned.

The percent_done refers to the amount completed for the current data item being processed. It is not updated for metadata objects.

The worker status types are defined as follows:

CREATE TYPE sys.ku$_WorkerStatus1010 AS OBJECT (
worker_number      NUMBER,
process_name       VARCHAR2(30),
state              VARCHAR2(30),
schema             VARCHAR2(30),
name               VARCHAR2(4000),
object_type        VARCHAR2(200),
partition          VARCHAR2(30),
completed_objects  NUMBER,
total_objects      NUMBER,
completed_rows     NUMBER,
completed_bytes    NUMBER,
percent_done       NUMBER )
/
CREATE PUBLIC SYNONYM ku$_WorkerStatus1010 FOR sys.ku$_WorkerStatus1010;
CREATE TYPE sys.ku$_WorkerStatusList1010 AS TABLE OF sys.ku$_WorkerStatus1010
/
CREATE PUBLIC SYNONYM ku$_WorkerStatusList1010 FOR sys.ku$_WorkerStatusList1010;

Log Entry and Error Types

These types provide informational and error text to attached clients and the log stream. The ku$LogLine.errorNumber type is set to NULL for informational messages but is specified for error messages. Each log entry may contain several lines of text messages.

The log entry and error types are defined as follows:

CREATE TYPE sys.ku$_LogLine1010 AS OBJECT (
logLineNumber   NUMBER,
errorNumber     NUMBER,
LogText         VARCHAR2(2000) )
/
CREATE PUBLIC SYNONYM ku$_LogLine1010 FOR sys.ku$_LogLine1010;
CREATE TYPE sys.ku$_LogEntry1010 AS TABLE OF sys.ku$_LogLine1010
/
CREATE PUBLIC SYNONYM ku$_LogEntry1010 FOR sys.ku$_LogEntry1010;

Job Status Types

The job status type returns status about a job. Usually, the status concerns a running job but it could also be about a stopped job when a client attaches. It is typically requested at attach time, when the client explicitly requests status from interactive mode and every N seconds when the client has requested status periodically.

The job status types are defined as follows (percent_done applies to data only):

CREATE TYPE sys.ku$_DumpFile1010 AS OBJECT (
file_name          VARCHAR2(4000),                
file_type          NUMBER,
file_size          NUMBER,
file_bytes_written NUMBER)

CREATE TYPE sys.ku$_DumpFileSet1010 AS TABLE OF sys.ku$_DumpFile1010;

CREATE TYPE sys.ku$_JobStatus1010 AS OBJECT (
job_name           VARCHAR2(30),
operation          VARCHAR2(30),
job_mode           VARCHAR2(30),
bytes_processed    NUMBER,
total_bytes        NUMBER,
percent_done       NUMBER,
degree             NUMBER,
error_count        NUMBER,
state              VARCHAR2(30),
phase              NUMBER,
restart_count      NUMBER,
worker_status_list ku$_WorkerStatusList1010,
files              ku$_DumpFileSet1010)

CREATE PUBLIC SYNONYM ku$_JobStatus1010 
FOR sys.ku$_JobStatus1010;

Job Description Types

The job description type holds all the environmental information about the job such as parameter settings and dump file set members. There are a couple of subordinate types required as well.

The job description types are defined as follows:

CREATE TYPE sys.ku$_ParamValue1010 AS OBJECT (
param_name     VARCHAR2(30),
param_op       VARCHAR2(30),
param_type     VARCHAR2(30),
param_length   NUMBER,
param_value_n  NUMBER,
param_value_t  VARCHAR2(2000) );

CREATE TYPE sys.ku$_ParamValues1010 AS TABLE OF sys.ku$_ParamValue1010;

CREATE TYPE sys.ku$_JobDesc1010 AS OBJECT (
job_name       VARCHAR2(30),
guid           RAW(16),
operation      VARCHAR2(30),
job_mode       VARCHAR2(30),
remote_link    VARCHAR2(4000),
owner          VARCHAR2(30),
instance       VARCHAR2(16),
db_version     VARCHAR2(30),
creator_privs  VARCHAR2(30),
start_time     DATE,
max_degree     NUMBER,
log_file       VARCHAR2(4000),
sql_file       VARCHAR2(4000),
params         ku$_ParamValues1010)

Status Types

The status type is an aggregate of some the previous types defined and is the return value for the GET_STATUS call. The mask attribute indicates which types of information are being returned to the caller. It is created by a client's shadow process from information it retrieves off the status queue or directly from the master table.

For errors, the ku$_LogEntry that is returned has already had its log lines ordered for proper output. That is, the original ku$_LogEntry objects have been ordered from outermost context to innermost.

The status types are defined as follows:

CREATE TYPE sys.ku$_Status1010 AS OBJECT
(
mask               NUMBER,         /* Indicates which status types are present*/
wip                ku$_LogEntry1010,  /* Work-In-Progress: std. exp/imp msgs */
job_description    ku$_JobDesc1010,   /* Complete job description */
job_status         ku$_JobStatus1010, /* Detailed job status + per-worker sts */
error              ku$_LogEntry1010   /* Multi-level contextual errors */
)

Summary of DBMS_DATAPUMP Subprograms

Table 25-1  DBMS_DATAPUMP Package Subprograms
Subprogram Description

ADD_FILE Procedure

Adds dump files to the dump file set for an Export, Import, or SQL_FILE operation. In addition to dump files, other types of files can also be added by using the FILETYPE parameter provided with this procedure

ATTACH Function

Used to gain access to a Data Pump job that is in the Defining, Executing, Idling, or Stopped state

DATA_FILTER Procedures

Specifies restrictions on the rows that are to be retrieved

DETACH Procedure

Specifies that the user has no further interest in using the handle

GET_STATUS Procedure

Monitors the status of a job or waits for the completion of a job or for more details on API errors

LOG_ENTRY Procedure

Inserts a message into the log file

METADATA_FILTER Procedure

Provides filters that allow you to restrict the items that are included in a job

METADATA_TRANSFORM Procedure

Specifies transformations to be applied to objects as they are processed in the specified job

METADATA_REMAP Procedure

Specifies a remapping to be applied to objects as they are processed in the specified job

OPEN Function

Declares a new job using the Data Pump API, the handle returned being used as a parameter for calls to all other procedures except ATTACH

SET_PARALLEL Procedure

Adjusts the degree of parallelism within a job

SET_PARAMETER Procedures

Specifies job-processing options

START_JOB Procedure

Begins or resumes execution of a job

STOP_JOB Procedure

Terminates a job, but optionally, preserves the state of the job


ADD_FILE Procedure

This procedure adds files to the dump file set for an Export, Import, or SQL_FILE operation or specifies the log file or the output file for a SQL_FILE operation.

Syntax

DBMS_DATAPUMP.ADD_FILE (
   handle    IN NUMBER,
   filename   IN VARCHAR2,
   directory  IN VARCHAR2,
   filesize   IN VARCHAR2 DEFAULT NULL,
   filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

Parameters

Table 25-2  ADD_FILE Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN or ATTACH call.

filename

The name of the file being added. filename must be a simple filename without any directory path information. For dump files, the filename can include a substitution variable, %U, which indicates that multiple files may be generated with the specified filename as a template. The %U is expanded in the resulting file names into a two-character, fixed-width, incrementing integer starting at 01. For example, the dump filename of export%U would cause export01, export02, export03, ... to be created depending on how many files are needed to perform the export. For filenames containing the % character, the % must be represented as %% to avoid ambiguity. Any % in a filename must be followed by either a % or a U.

directory

The name of a directory object within the database that is used to locate filename. A directory must be specified. See the Data Pump Export chapter in Oracle Database Utilities for information about the DIRECTORY command-line parameter.

filesize

The size of the dump file that is being added. It may be specified as the number of bytes, number of kilobytes (if followed by K), number of megabytes (if followed by M) or number of gigabytes (if followed by G). An Export operation will write no more than the specified number of bytes to the file. Once the file is full, it will be closed. If there is insufficient space on the device to write the specified number of bytes, the Export operation will fail, but it can be restarted. If not specified, filesize will default to an unlimited size. For Import and SQL_FILE operations, filesize is ignored. The minimum value for filesize is ten times the default Data Pump block size, which is 4 kilobytes. filesize may only be specified for dump files.

filetype

The type of the file to be added. The legal values are as follows and must be preceded by DBMS_DATAPUMP.:

  • KU$_FILE_TYPE_DUMP_FILE (dump file for a job)
  • KU$_FILE_TYPE_LOG_FILE (log file for a job)
  • KU$_FILE_TYPE_SQL_FILE (output for SQL_FILE job)

Exceptions

Usage Notes


ATTACH Function

This function gains access to a previously-created job.

Syntax

DBMS_DATAPUMP.ATTACH(
   job_name    IN VARCHAR2 DEFAULT NULL,
   job_owner    IN VARCHAR2 DEFAULT NULL) 
 RETURN NUMBER;

Parameters

Table 25-3  ATTACH Function Parameters
Parameter Description

job_name

The name of the job. The default is the job name owned by the user who is specified in the job_owner parameter (assuming that user has only one job in the Defining, Executing, or Idling states).

job_owner

The user who originally started the job. If NULL, the value defaults to the owner of the current session. To specify a job owner other than yourself, you must have either the EXP_FULL_DATABASE role (for export operations) or the IMP_FULL_DATABASE role (for import and SQL_FILE operations). Being a privileged user allows you to monitor another user's job, but you cannot restart another user's job.

Return Values

An opaque handle for the job. This handle is used as input to the following procedures: ADD_FILE, DATA_FILTER, DETACH, STOP_JOB, GET_STATUS, LOG_ENTRY, METADATA_FILTER, METADATA_REMAP, METADATA_TRANSFORM, SET_PARALLEL, and START_JOB.

Exceptions

Usage Notes


DATA_FILTER Procedures

This procedure specifies restrictions on the rows that are to be retrieved.

Syntax

DBMS_DATAPUMP.DATA_FILTER (
   handle     IN NUMBER,
   name        IN VARCHAR2,
   value       IN NUMBER,
   table_name  IN VARCHAR2 DEFAULT NULL,
   schema_name IN VARCHAR2 DEFAULT NULL);

DBMS_DATAPUMP.DATA_FILTER(
  handle      IN NUMBER,
  name        IN VARCHAR2,
  value       IN VARCHAR2,
  table_name  IN VARCHAR2 DEFAULT NULL,
  schema_name IN VARCHAR2 DEFAULT NULL);

Parameters

Table 25-4  DATA_FILTER Procedure Parameters
Parameter Description

handle

The handle that is returned from the OPEN procedure.

name

The name of the filter.

value

The value of the filter.

table_name

The name of the table on which the data filter is applied. If no table name is supplied, the filter applies to all tables in the job.

schema_name

The name of the schema that owns the table on which the filter is applied. If no schema name is specified, the filter applies to all schemas in the job. If you supply a schema name you must also supply a table name.

Exceptions

Usage Notes


DETACH Procedure

This procedure specifies that the user has no further interest in using the handle.

Syntax

DBMS_DATAPUMP.DETACH(
   handle  IN NUMBER);

Parameters

Table 25-6  DETACH Procedure Parameters
Parameter Description

handle

The handle of the job. The current session must have previously attached to the handle through an OPEN or ATTACH call.

Exceptions

Usage Notes


GET_STATUS Procedure

This procedure monitors the status of a job or wait for the completion of a job.

Syntax

DBMS_DATAPUMP.GET_STATUS(
   handle    IN NUMBER,
   mask      IN BINARY_INTEGER,
   timeout   IN NUMBER DEFAULT NULL,
   job_state OUT VARCHAR2,
   status    OUT ku$_Status1010);

Parameters

Table 25-7  GET_STATUS Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN or ATTACH call. A null handle can be used to retrieve error information after OPEN and ATTACH failures.

mask

A bit mask that indicates which of four types of information to return:

  • KU$_STATUS_WIP
  • KU$_STATUS_JOB_DESC
  • KU$_STATUS_JOB_STATUS
  • KU$_STATUS_JOB_ERROR

Each status has a numerical value. You can request multiple types of information by adding together different combinations of values. See Types.

timeout

Maximum number of seconds to wait before returning to the user. A value of 0 requests an immediate return. A value of -1 requests an infinite wait. If KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR information is requested and becomes available during the timeout period, then the procedure returns before the timeout period is over.

job_state

Current state of the job. If only job state is needed, it is much more efficient to use this parameter than to retrieve the full ku$_Status1010 structure.

status

A ku$_Status is returned. The ku$_Status mask indicates what kind of information is included. This could be none if only KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR information is requested and the timeout period expires.

Exceptions

Usage Notes

The GET_STATUS procedure is used to monitor the progress of an ongoing job and to receive error notification. You can request various type of information using the mask parameter. The KU$_STATUS_JOB_DESC and KU$_STATUS_JOB_STATUS values are classified as synchronous information because the information resides in the master table. The KU$_STATUS_WIP and KU$_STATUS_JOB_ERROR values are classified as asynchronous because the messages that embody these types of information can be generated at any time by various layers in the Data Pump architecture.

Error Handling

There are two types of error scenarios that need to be handled using the GET_STATUS procedure:

After a job has begun, a client's main processing loop will typically consist of a call to GET_STATUS with an infinite timeout (-1) "listening" for KU$_STATUS_WIP and KU$_STATUS_JOB_ERROR messages. If status was requested, then JOB_STATUS information will also be in the request.

When the ku$_Status is interpreted, the following guidelines should be used:


LOG_ENTRY Procedure

This procedure inserts a message into the log file.

Syntax

DBMS_DATAPUMP.LOG_ENTRY(
   handle IN NUMBER,
   message IN VARCHAR2     
   log_file_only IN NUMBER DEFAULT 0);

Parameters

Table 25-8  LOG_ENTRY Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN or ATTACH call.

message

A text line to be added to the log file.

log_file_only

Specified text should be written only to the log file. It should not be returned in GET_STATUS work-in-progress (KU$_STATUS_WIP) messages.

Exceptions

Usage Notes

The message is added to the log file. If log_file_only is zero (the default), the message is also broadcast as a KU$_STATUS_WIP message through the GET_STATUS procedure to all users attached to the job.

The LOG_ENTRY procedure allows applications to tailor the log stream to match the abstractions provided by the application. For example, the command-line interface supports INCLUDE and EXCLUDE parameters defined by the user. Identifying these values as calls to the underlying METADATA_FILTER procedure would be confusing to users. Instead, the command-line interface can enter text into the log describing the settings for the INCLUDE and EXCLUDE parameters.

Lines entered in the log stream from LOG_ENTRY are prefixed by the string, ";;; "


METADATA_FILTER Procedure

This procedure provides filters that allow you to restrict the items that are included in a job.

Syntax

DBMS_DATAPUMP.METADATA_FILTER(
   handle       IN NUMBER,
   name         IN VARCHAR2,
   value        IN VARCHAR2,
   object_path  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 25-9  METADATA_FILTER Procedure Parameters
Parameter Description

handle

The handle returned from the OPEN procedure.

name

The name of the filter. See Table 25-10 for descriptions of the available filters.

value

The value of the filter.

object_path

The object path to which the filter applies. If the default is used, the filter applies to all applicable objects. Lists of the object paths supported for each mode are contained in the catalog views for DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS. (Note that the TABLE_EXPORT_OBJECTS view is applicable to both Table and Tablespace mode because their object paths are the same.)

For an import operation, object paths reference the mode used to create the dump file rather than the mode being used for the import.

Table 25-10 describes the name, object type, datatype, operations that support the filter, and the meaning of the filters available with the METADATA_FILTER procedure. The datatype for all the filters is a text expression. All operations support all filters.

Table 25-10  Filters Provided by METADATA_FILTER Procedure
Name Object Type Meaning

INCLUDE_NAME_EXPR

and

EXCLUDE_NAME_EXPR

Named objects

Defines which object names are included in (INCLUDE_NAME_EXPR), or excluded from (EXCLUDE_NAME_EXPR), the job. You use the object_type parameter to limit the filter to a particular object type.

For Table mode, identifies which tables are to be processed.

SCHEMA_EXPR

Schema objects

Restricts the job to objects whose owning schema name is satisfied by the expression.

For Table mode, only a single SCHEMA_EXPR filter is supported. If specified, it must only specify a single schema (for example, 'IN (''SCOTT'')').

For Schema mode, identifies which users are to be processed.

TABLESPACE_EXPR

TABLE, CLUSTER, INDEX, ROLLBACK_SEGMENT

Restricts the job to objects stored in a tablespace whose name is satisfied by the expression.

For Tablespace mode, identifies which tablespaces are to be processed. If a partition of an object is stored in the tablespace, the entire object is added to the job.

For Transportable mode, identifies which tablespaces are to be processed. If a table has a single partition in the tablespace set, all partitions must be in the tablespace set. An index is not included within the tablespace set unless all of its partitions are in the tablespace set. A domain index is not included in the tablespace set unless all of its secondary objects are included in the tablespace set.

INCLUDE_PATH_EXPR and EXCLUDE_PATH_EXPR

All

Defines which object paths are included in, or excluded from, the job. You use these filters to select only certain object types from the database or dump file set. Objects of paths satisfying the condition are included (INCLUDE_PATH_EXPR) or excluded (EXCLUDE_PATH_EXPR) from the operation. The object_path parameter is not supported for these filters.

Exceptions

Usage Notes


METADATA_REMAP Procedure

This procedure specifies a remapping to be applied to objects as they are processed in the specified job.

Syntax

DBMS_DATAPUMP.METADATA_REMAP (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   old_value   IN VARCHAR2,
   value       IN VARCHAR2,
   object_type IN VARCHAR2 DEFAULT NULL);

Parameters

Table 25-11  METADATA_REMAP Procedure Parameters
Parameter Description

handle

The handle for the current job. The current session must have previously attached to the handle through a call to the OPEN procedure.

name

The name of the remap. See Table 25-12 for descriptions of the available remaps.

old_value

Specifies which value in the dump file set should be reset to value.

value

The value of the parameter for the remap. This signifies the new value that old_value should be translated into.

object_type

Designates the object type to which the remap applies. The list of object types supported for each mode are contained in the DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS, and TABLESPACE_EXPORT_OBJECTS catalog views.

By default, the remap applies to all applicable objects within the job. The object_type parameter allows a caller to specify different parameters for different object types within a job. Remaps that explicitly specify an object type override remaps that apply to all object types.

Table 25-12 describes the remaps provided by the METADATA_REMAP procedure.

Table 25-12 Remaps Provided by the METADATA_REMAP Procedure
Name Datatype Object Type Meaning

REMAP_SCHEMA

Text

Schema objects

Any schema object in the job that matches the object_type parameter and was located in the old_value schema will be moved to the value schema.

REMAP_TABLESPACE

Text

TABLE, INDEX, ROLLBACK_SEGMENT, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG,TABLE_SPACE

Any storage segment in the job that matches the object_type parameter and was located in the old_value tablespace will be relocated to the value tablespace.

REMAP_DATAFILE

Text

LIBRARY, TABLESPACE, DIRECTORY

Any datafile reference in the job that matches the object_type parameter and referenced the old_value datafile will be redefined to use the value datafile.

Exceptions

Usage Notes


METADATA_TRANSFORM Procedure

This procedure specifies transformations to be applied to objects as they are processed in the specified job.

Syntax

DBMS_DATAPUMP.METADATA_TRANSFORM (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN VARCHAR2,
   object_type IN VARCHAR2 DEFAULT NULL);

Parameters

Table 25-13  METADATA_TRANSFORM Procedure Parameters
Parameter Description

handle

The handle for the current job. The current session must have previously attached to the handle through a call to the OPEN procedure.

name

The name of the transformation. See Table 25-14 for descriptions of the available transforms.

value

The value of the parameter for the transform.

object_type

Designates the object type to which the transform applies. The list of object types supported for each mode are contained in the DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS, and TABLESPACE_EXPORT_OBJECTS catalog views.

By default, the transform applies to all applicable objects within the job. The object_type parameter allows a caller to specify different transform parameters for different object types within a job. Transforms that explicitly specify an object type override transforms that apply to all object types.

Table 25-14 describes the transforms provided by the METADATA_TRANSFORM procedure.

Table 25-14  Transforms Provided by the METADATA_TRANFORM Procedure
Name Datatype Object Type Meaning

SEGMENT_ATTRIBUTES

NUMBER

TABLE, INDEX

If nonzero (TRUE), emit storage segment parameters. Defaults to 1.

STORAGE

NUMBER

TABLE

If nonzero (TRUE), emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is zero.) Defaults to nonzero (TRUE).

Exceptions

Usage Notes


OPEN Function

This function is used to declare a new job using the Data Pump API. The handle that is returned is used as a parameter for calls to all other procedures except ATTACH.

Syntax

DBMS_DATAPUMP.OPEN (
   operation   IN VARCHAR2,
   mode         IN VARCHAR2,
   remote_link  IN VARCHAR2 DEFAULT NULL,
   job_name     IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE') 
 RETURN NUMBER;

Parameters

Table 25-15  OPEN Function Parameters
Parameter Meaning

operation

The type of operation to be performed. Table 25-16 contains descriptions of valid operation types.

mode

The scope of the operation to be performed. Table 25-17 contains descriptions of valid modes. Specifying NULL generates an error.

remote_link

If the value of this parameter is non-null, it provides the name of a database link to the remote database that will be the source of data and metadata for the current job.

job_name

The name of the job. The name is limited to 30 characters; it will be truncated if more than 30 characters are used. It may consist of printable characters and spaces. It is implicitly qualified by the schema of the user executing the OPEN procedure and must be unique to that schema (that is, there cannot be other Data Pump jobs using the same name).

The name is used to identify the job both within the API and with other database components such as identifying the job in the DBA_RESUMABLE view if the job becomes suspended through lack of resources. If no name is supplied, a system generated name will be provided for the job in the following format: "SYS_<OPERATION>_<MODE>_%N".

The default job name is formed where %N expands to a two-digit incrementing integer starting at '01' (for example, "SYS_IMPORT_FULL_03"). The name supplied for the job will also be used to name the master table and other resources associated with the job.

version

The version of database objects to be extracted. This option is only valid for Export, network Import, and SQL_FILE operations. Database objects or attributes that are incompatible with the version will not be extracted. Legal values for this parameter are as follows:

  • COMPATIBLE - (default) the version of the metadata corresponds to the database compatibility level and the compatibility release level for feature (as given in the V$COMPATIBILITY view). Database compatibility must be set to 9.2 or higher.
  • LATEST - the version of the metadata corresponds to the database version.
  • A specific database version, for example, '10.0.0'. In Oracle Database10g, this value cannot be lower than 10.0.0.

Table 25-16 describes the valid operation types for the OPEN procedure.

Table 25-16  Valid Operation Types for the OPEN Procedure
Operation Description

EXPORT

Saves data and metadata to a dump file set or obtains an estimate of the size of the data for an operation.

IMPORT

Restores data and metadata from a dump file set or across a database link.

SQL_FILE

Displays the metadata within a dump file set, or from across a network link, as a SQL script. The location of the SQL script is specified through the ADD_FILE procedure.

Table 25-17 describes the valid modes for the OPEN procedure.

Table 25-17  Valid Modes for the OPEN Procedure
Mode Description

FULL

Operates on the full database or full dump file set except for the SYS, XDB,ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, and LBACSYS schemas.

SCHEMA

Operates on a set of selected schemas. Defaults to the schema of the current user. All objects in the selected schemas are processed. Users cannot specify SYS, XDB, ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, or LBACSYS schemas for this mode.

TABLE

Operates on a set of selected tables. Defaults to all of the tables in the current user's schema. Only tables and their dependent objects are processed.

TABLESPACE

Operates on a set of selected tablespaces. No defaulting is performed. Tables that have storage in the specified tablespaces are processed in the same manner as in Table mode.

TRANSPORTABLE

Operates on metadata for tables (and their dependent objects) within a set of selected tablespaces to perform a transportable tablespace export/import.

Return Values

Exceptions

Usage Notes


SET_PARALLEL Procedure

This procedure adjusts the degree of parallelism within a job.

Syntax

DBMS_DATAPUMP.SET_PARALLEL(
   handle      IN NUMBER,
   degree       IN NUMBER);

Parameters

Table 25-18  SET_PARALLEL Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN or ATTACH call.

degree

The maximum number of worker processes that can be used for the job. You use this parameter to adjust the amount of resources used for a job.

Exceptions

Usage Notes


SET_PARAMETER Procedures

This procedure is used to specify job-processing options.

Syntax

DBMS_DATAPUMP.SET_PARAMETER(
   handle      IN NUMBER,
   name         IN VARCHAR2,
   value        IN VARCHAR2);

DBMS_DATAPUMP.SET_PARAMETER (
   handle      IN NUMBER,
   name         IN VARCHAR2,
  value        IN NUMBER);

Parameters

Table 25-19  SET_PARAMETER Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN call.

name

The name of the parameter. Table 25-20 describes the valid parameter names.

value

The value for the specified parameter.

Table 25-20 describes the valid options for the name parameter of the SET_PARAMETER procedure.

Table 25-20  Valid Options for the name Parameter in the SET_PARAMETER Procedure
Parameter Name Datatype Supported Operations Meaning

CLIENT_COMMAND

Text

All

An opaque string used to describe the current operation from the client's perspective. The command-line procedures will use this string to store the original command used to invoke the job.

ESTIMATE

Text

Export and Import

Specifies that the estimate method for the size of the tables should be performed before starting the job.

If BLOCKS, a size estimate for the user tables is calculated using the count of blocks allocated to the user tables.

If STATISTICS, a size estimate for the user tables is calculated using the statistics associated with each table. If no statistics are available for a table, the size of the table is estimated using BLOCKS.

The ESTIMATE parameter cannot be used in Transportable Tablespace mode.

Default=BLOCKS.

ESTIMATE_ONLY

Number

Export

Specifies that only the estimation portion of an export job should be performed. This option is useful for estimating the size of dump files when the size of the export is unknown.

FLASHBACK_SCN

NUMBER

Export and network Import

System change number (SCN) to serve as transactionally consistent point for reading user data. If neither FLASHBACK_SCN nor FLASHBACK_TIME is specified, there will be no transactional consistency between partitions, except for logical standby databases and Streams targets. FLASHBACK_SCN is not supported in Transportable mode.

FLASHBACK_TIME

Text

Export and network Import

Either the date and time used to determine a consistent point for reading user data or a string of the form TO_TIMESTAMP(...).

If neither FLASHBACK_SCN nor FLASHBACK_TIME is specified, there will be no transactional consistency between partitions.

FLASHBACK_SCN and FLASHBACK_TIME cannot both be specified for the same job. FLASHBACK_TIME is not supported in Transportable mode.

INCLUDE_METADATA

NUMBER

Export and Import

If nonzero, metadata for objects will be moved in addition to user table data.

If zero, metadata for objects will not moved. This parameter converts an Export operation into an unload of user data and an Import operation into a load of user data.

INCLUDE_METADATA is not supported in Transportable mode.

Default=1.

REUSE_DATAFILES

NUMBER

Import

If nonzero, tablespace data files can be created using preexisting data files for tablespace creations. REUSE_DATAFILES is only supported in Full mode.

Default=0

SKIP_UNUSABLE_INDEXES

NUMBER

Import

If nonzero, rows will be inserted into tables having unusable indexes. SKIP_UNUSABLE_INDEXES is not supported in Transportable mode.

Default=1

TABLE_EXISTS_ACTION

Text

Import

Specifies the action to be performed when data is loaded into a preexisting table. The possible actions are: TRUNCATE, REPLACE, APPEND, and SKIP.

If INCLUDE_METADATA=0, only TRUNCATE and APPEND are supported.

If TRUNCATE, rows are removed from a preexisting table before inserting rows from the Import.

Note that if TRUNCATE is specified on tables referenced by foreign key constraints, the TRUNCATE will be modified into a REPLACE.

If REPLACE, preexisting tables are replaced with new definitions. Before creating the new table, the old table is dropped.

If APPEND, new rows are added to the existing rows in the table.

If SKIP, the preexisting table is left unchanged.

TABLE_EXISTS_ACTION is not supported in Transportable mode.

The default is SKIP if metadata is included in the import. The default is APPEND if INCLUDE_METADATA is set to 0.

TABLESPACE_DATAFILE

Text

Import

Specifies the full file specification for a datafile in the transportable tablespace set. TABLESPACE_DATAFILE is only valid for transportable mode imports.

TABLESPACE_DATAFILE can be specified multiple times, but the value specified for each occurrence must be different.

TTS_FULL_CHECK

NUMBER

Export

If nonzero, verifies that a transportable tablespace set has no dependencies (specifically, IN pointers) on objects outside the set, and vice versa. Only valid for Transportable mode Exports.

Default=0.

USER_METADATA

NUMBER

Export and network Import

For schema-mode operations, specifies that the metadata to re-create the users' schemas (for example, privilege grants to the exported schemas) should also be part of the operation if set to nonzero. Users must be privileged to explicitly set this parameter.

The USER_METADATA parameter cannot be used in Table, Tablespace, or Transportable Tablespace mode.

Default=1 if user has EXP_FULL_DATABASE role; 0 otherwise.

Exceptions

Usage Notes


START_JOB Procedure

This procedure begins or resumes execution of a job.

Syntax

DBMS_DATAPUMP.START_JOB (
   handle       IN NUMBER,
   skip_current IN NUMBER DEFAULT 0);

Parameters

Table 25-21  START_JOB Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through either the OPEN or ATTACH procedure.

skip_current

If nonzero, causes actions that were 'in progress' on a previous execution of the job to be skipped when the job restarts. The skip will only be honored for Import jobs. This mechanism allows the user to skip actions that trigger fatal bugs and cause the premature termination of a job. Multiple actions can be skipped on a restart. The log file will identify which actions are skipped. If a domain index was being processed, all pieces of the domain index are skipped even if the error occurred in only a subcomponent of the domain index.

A description of the actions skipped is entered into the log file. skip_current is ignored for the initial START_JOB in a job.

If zero, no data or metadata is lost upon a restart.

Exceptions

Usage Notes


STOP_JOB Procedure

This procedure terminates a job, but optionally, preserves the state of the job.

Syntax

DBMS_DATAPUMP.STOP_JOB (
   handle     IN NUMBER,
   immediate   IN NUMBER DEFAULT 0,
   keep_master IN NUMBER DEFAULT NULL,
   delay       IN NUMBER DEFAULT 0);

Parameters

Table 25-22  STOP_JOB Procedure Parameters
Parameter Description

handle

The handle of a job. The current session must have previously attached to the handle through an OPEN or ATTACH call. At the end of the procedure, the user is detached from the handle.

immediate

If nonzero, the worker processes are aborted immediately. If zero, the worker processes are allowed to complete their current work item (either metadata or table data) before they are terminated.

Performing an immediate shutdown halts the job more quickly, but parts of the job will have to be rerun if the job is ever restarted.

keep_master

If nonzero, the master table is retained when the job is stopped. If zero, the master table is dropped when the job is stopped. If the master table is dropped, the job will not be restartable. If the master table is dropped during an export job, the created dump files are deleted.

delay

The number of seconds to wait until other attached sessions are forcibly detached. The delay allows other sessions attached to the job to be notified that a stop has been performed. The job keeps running until either all clients have detached or the delay has been satisfied. If no delay is specified, then the default delay is 60 seconds. If a shorter delay is used, clients might not be able to retrieve the final messages for the job through the GET_STATUS procedure.

Exceptions

Usage Notes