Oracle 10g - Loading and Unloading Data

Introduction to the Data Pump Architecture

Using Export and Import Data Pump utilities you can:

export and import data faster than Old export/import utilities
estimate job times
perform fine-grained object selection
monitor jobs effectively
directly load one database from a remote instance
call the utilities from PL/SQL using Data Dump API
stop, resume and restart the utilities
attach a running job to monitor jobs, as well as to modify certain parameters interactively.
have fine-grained data import capability
remap objects of a specific schema to another schema

Note : the export Data Pump user process launches a server-side process that writes data to disks on the server node, not the client that launches the utility.

Note: The new Data Pump technology lets you export data only to disk. You cannot use a tape drive when performing a Data Pump export.

Data Pump Components

The DBMS_DATAPUMP package: this is the main engine of the Data Pump utilities. It contains procedures that do the export and import actions.
The DBMS_METADATA package: this package is used to extract and modify data dictionary metadata.
The command-line clients, expdp and impdp.

Data-Access Methods

Direct path: the direct path internal stream format is the same format as the data stored in Oracle dump files.
External tables: Oracle reads data from and write data to operating system files that lie outside the database.

Data Pump automatically selects the most appropriate access method for each table. It always tries to first use the direct-path method. Under some conditions, such as the following, it may not able to use the direct method:

Clustered tables
Presence of active triggers in the tables
Export of a single partition in a table with a global index
Presence of referential integrity constraints
Presence of domain indexes on LOB columns
Tables with fine-grained access control enabled in the insert mode
Tables with BFILE or opaque type columns

Note: The datafile format is identical in external tables and the direct-access methods.

Data Pump Files

Dump files: These hold the data for the Data Pump job.
Log files: These are the standard files for logging the results of Data Pump operations.
SQL files: Data Pump import uses a special parameter called SQLFILE, which will write all the Data Definition Language (DDL) statements it will execute during the import job to a file.

Using Directory Objects

You can’t use absolute directory path location for Data Pump jobs; you must always use a directory object.

To create a directory, a user must have the CREATE ANY DIRECTORY privilege:

CREATE DIRECTORY dpump_dir1 as 'c:\oracle\product\10.1.0\oradata\export'

In order for a user to use a specific directory, the user must have access privileges to the directory object:

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO salapati

Note: In Oracle 10g Release 2, a directory object named DATA_PUMP_DIR as created by default in the database. In Windows, it is mapped to <ORACLE_BASE>\admin\<sid>\dpdump\ directory. By default, it is available only to privileged users.

Using the DIRECTORY:FILE Notation:

expdp LOGFILE=dpump_dir2:salapati.log ...

Using the DIRECTORY parameter

You can use the DIRECTORY parameter to specify the name of the directory object:

expdp hr/hr DIRECTORY=dpump_dir1 ...

Using the default directory DATA_PUMP_DIR

You can create a default directory with the name DATA_PUMP_DIR, and then not need to specify the DIRECTORY parameter in your export and import commands. Data Pump will write all dump files, SQL files, and log files automatically to the directory specified for DATA_DUMP_DIR.

Using the DATA_DUMP_DIR Environment Variable

You can use the DATA_DUMP_DIR environment variable on the client to point to the directory object on the server. Data Pump will automatically read and/or write its files from that directory object. In Windows, this variable is set in the Registry.

Order of Precedence for File Locations

As in the order indicated above.

The Mechanics of a Data Pump Job

The Master Process

The master process, or more accurately, the Master Control Process (MCP), has a process name of DMnn. The full master process name is of the format <instance>_DMnn_<pid>

The master process performs the following tasks:

Creates jobs and controls them
Creates and manages the worker processes
Monitors the jobs and logs the progress
Maintains the job state and restart information in the master table
Manages the necessary files, including the dump file set

Oracle creates the master table in the schema of the user who is running the Data Pump job at the beginning of every export job. The master table has the same name as the export job, such as SYS_EXPORT_SCHEMA_01. Master table will be automatically deleted by end of a successful export or import job.

Note: The master table contains all the necessary information to restart a stopped job. It is thus the key to Data Pump’s job restart capability, whether the job stoppage is planned or unplanned.

The Worker Process

The worker process is the process that actually performs the heavy-duty work of loading and unloading data, and has the name DWnn (<instance>_DWnn_<pid>).

MCP(DMnn) may create number of DWnn, if you choose the PARALLEL option for load. DWnn process maintains the object rows of the master table.

Shadow Process

The shadow process creates the job consisting of the master table as well as the master process.

Client Processes

The client processes call the Data Pump’s API. You perform export and import with the two clients, expdp and impdp.

Using Data Pump Export and Import

In Data Pump export, you use the interactive-command interface for one purpose only: when you decide you need to change some export parameters midstream, while the job is still running. Note that the export or import job keeps running throughout, without any interruption.
This mode is enabled by pressing [Ctrl] + [C] during an export operation started with the command-line interface or the parameter file interface.

this optional parameter specifies size of export file. The export job will stop if your dump file reaches its size limit.

Note: The directory object is not used by this parameter. The directory path is an operating system-specific directory specification. The default is the user's current directory.

You can use the LOGFLE parameter to specify a log file for your export jobs. If you don’t specify this parameter, Oracle will create a log file named export.log. If you specify the parameter NOLOGFILE, Oracle will not create its log file.

The export mode-related parameters are the FULL, SCHEMAS, TABLES, TABLESPACES, TRANSPORT_TABLESPACES, and TRANSPORT_FULL_CHECK parameters. The TRANSPORT_FULL_CHECK parameter simply checks to make sure that the tablespaces you are trying to transport meet all the conditions to qualify for the job.

Those are mutually exclusive parameters. The EXCLUDE parameter is used to omit specific database object types from an export or import operation. The INCLUDE parameter enables you to include only a specific set of objects.

This parameter lets you selectively export table row data with the help of a SQL statement.

The ESTIMATE parameter will tell you how much space your new export job is going to consume.

When you set ESTIMATE=statistics, Oracle will use the statistics of the database objects to calculate its estimation.

Use this parameter to estimate the required export file size without starting an actual export job.

You can initiate an export job from your server and have Data Pump export data from a remote database to dump files located on the instance from which you initiate the Data Pump export job.

expdp hr/hr DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link DUMPFILE=network_export.dmp

You must be a DBA, or must have EXP_FULL_DATABASE or IMP_FULL_DATABASE roles, in order to attach and control Data Pump jobs of other users.

This parameter will take you out of the interactive mode. Your client connection will still be intact, and you’ll continue to see the export messages on your screen.

This parameter will stop the interactive session, as well as terminate the client session.

This parameter resumes stopped jobs. You can restart any job that is stopped, whether it’s stopped because you issued a STOP_JOB command or due to a system crash, as long as you have access to the master table and an uncorrupted dump file set.

This parameter kills both the client and the Data Pump. If a job is killed using the KILL_JOB interactive command, the master table is dropped and the job cannot be restarted.

This parameter displays detailed status of the job, along with a description of the current operation. An estimated completion percentage for the job is also returned.

In logging mode, you can assign an integer value (n) to this parameter. In this case, job status is displayed on screen every n second.

Use this parameter to provide your own job name for a given Data Pump export/import job. If not provided, Oracle will give it a name of the format <USER>_<OPERATION>_<MODE>_%N.

This parameter lets you specify more than a single active execution thread for your export job. You should specify number of dump files equal to the PARALLEL value.

This parameter applies from Oracle 10.2. It specifies whether to compress metadata before writing to the dump file set. Compression reduces the amount of disk space consumed by dump files.

You’ll need the IMPORT_FULL_DATABASE role to perform an import if the dump file for the import was created using the EXPORT_FULL_DATABASE role.

The Data Pump import utility uses the PARFILE, DIRECTORY, DUMPFILE, LOGFILE, and NOLOGFILE commands in the same way as the Data Pump export utility.

This parameter enables you to extract the DDL from the export dump file, without importing any data.

impdp salapati/sammyy1 DIRECTORY=dpump_dir1 DUMPFILE=finance.dmp SQLFILE=dpump_dir2:finance.sql

This parameter tells Data Pump whether it should use existing datafiles for creating tablespaces during an import.

You can perform a Data Pump import in various modes, using the TABLE, SCHEMAS, TABLESPACES, and FULL parameters, just as in the case of the Data Pump export utility.

The Data Pump import utility uses the CONTENT, EXCLUDE and INCLUDE parameters in the same way as the Data Pump export utility. If you use the CONTENT=DATA_ONLY option, you cannot use either the EXCLUDE or INCLUDE parameter during an import.

QUERY can also be used but in this case Data Pump will use only the external table data method, rather than the direct-path method, to access the data.

The JOB_NAME, STATUS, and PARALLEL parameters carry identical meanings as their Data Pump export counterparts.

You can perform a Data Pump import in various modes, using the TABLES, SCHEMAS, TABLESPACES, and FULL parameters, just as in the case of the Data Pump export utility.

Changes the name of the source datafile to the target datafile name in all SQL statements where the source datafile is referenced: CREATE TABLESPACE, CREATE LIBRARY, and CREATE DIRECTORY.

Remapping datafiles is useful when you move databases between platforms that have different file naming conventions.

impdp hr/hr FULL=y DIRECTORY=dpump_dir1 DUMPFILE=db_full.dmp REMAP_DATAFILE='DB1$:[HRDATA.PAYROLL]tbs6.f':'/db1/hrdata/payroll/tbs6.f'

This parameter enables you to move objects from one tablespace into a different tablespace during an import.

impdp hr/hr REMAP_TABLESPACE='example_tbs':'new_tbs' DIRECTORY=dpump_dir1 PARALLEL=2 JOB_NAME=cf1n02 DUMPFILE=employees.dmp NOLOGFILE=Y

In case of network import, the server contacts the remote source database referenced by the parameter value, retrieves the data, and writes it directly back to the target database. There are no dump files involved.

impdp hr/hr TABLES=employees DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link EXCLUDE=CONSTRAINT

This parameter instructs the Data Pump import job to modify the storage attributes of the DDL that creates the objects during the import job.

If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is y.

If the value is specified as y, the storage clauses are included, with appropriate DDL. The default is y. This parameter is ignored if SEGMENT_ATTRIBUTES=n.

If the value is specified as n, the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default is y.

It accepts a greater-than-zero number. It represents the percentage multiplier used to alter extent allocations and the size of data files.

object_type: It can take one of the following values: CLUSTER,CONSTRAINT,INC_TYPE,INDEX,ROLLBACK_SEGMENT,TABLE,TABLESPACE,TYPE

The DBA_DATAPUMP_JOBS view shows summary information of all currently running Data Pump jobs.

The DBA_DATAPUMP_SESSIONS view identifies the user sessions currently attached to a Data Pump export or import job.

To load an Oracle table from an external table, you use the INSERT INTO ...SELECT clause.

To populate an external table (data unloading), you use the CREATE TABLE AS SELECT clause. In this case, the external table is composed of proprietary format flat files that are operating system independent.

Note: You cannot use an external table population operation with an external table defined to be used with the ORACLE_LOADER access driver.

Note: If you wish to extract the metadata for any object, just use DBMS_METADATA, as shown here:

You can load external tables in a parallel fashion, simply by using the keyword PARALLEL when creating the external table.

The actual degree of parallelism is constrained by the number of dump files you specify under the LOCATION parameter.

The data dictionary view DBA_EXTERNAL_TABLES describes features of all the external tables.

If the PROPERTY column shows the value REFERENCED, this means that only those columns referenced by a SQL statement are processed (parsed and converted) by the Oracle access driver. ALL (the default) means that all the columns will be processed even those not existing in the select list.

In Oracle Database 10g, you can transport tablespaces between different platforms.

Transportable tablespaces are a good way to migrate a database between different platforms.

You must be using the Enterprise Edition of Oracle8i or higher to generate a transportable tablespace set. However, you can use any edition of Oracle8i or higher to plug a transportable tablespace set into an Oracle Database on the same platform.

To plug a transportable tablespace set into an Oracle Database on a different platform, both databases must have compatibility set to at least 10.0.

Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported.

The following statement can be used to determine whether tablespaces sales_1 and sales_2 are self-contained, with referential integrity constraints taken into consideration:

DBMS_TTS.TRANSPORT_SET_CHECK( TS_LIST =>'sales_1,sales_2', INCL_CONSTRAINTS =>TRUE, FULL_CHECK =>TRUE)

Note: You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.

Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database.

Note: By default, Oracle places the converted files in the Flash Recovery Area, without changing the datafile names.

Note: If you have CLOB data on a small-endian system in an Oracle database version before 10g and with a varying-width character set and you are transporting to a database in a big-endian system, the CLOB data must be converted in the destination database. RMAN does not handle the conversion during the CONVERT phase. However, Oracle database automatically handles the conversion while accessing the CLOB data.

If you want to eliminate this run-time conversion cost from this automatic conversion, you can issue the CREATE TABLE AS SELECT command before accessing the data.

ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
INCLUDING INDEXES WITHOUT VALIDATION

A data provider can load a tablespace with data to be published, generate the transportable set, and copy the transportable set to a CD. When customers receive this CD, they can plug it into an existing database without having to copy the datafiles from the CD to disk storage.

Note: In this case, it is highly recommended to set the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE.

You can use transportable tablespaces to mount a tablespace read-only on multiple databases.

You can use the Transport Tablespaces wizard to move a subset of an Oracle database from one Oracle database to another, even across different platforms.

The Transport Tablespaces wizard automates the process of generating a transportable tablespace set, or integrating an existing transportable tablespace set. The wizard uses a job that runs in the Enterprise Manager job system.

You can access the wizard from the Maintenance | Transport Tablespaces link in the Move Database Files section.

You can use the transport tablespace from backup feature to transport tablespaces at a point in time without marking the source tablespaces READ ONLY. This removes the need to set the tablespace set in READ ONLY mode while exporting its metadata which results in a period of unavailability.

The RMAN command TRANSPORT TABLESPACE is used to generate one version of a tablespace set. A tablespace set version comprises the following:

Note: this option is time-consuming compared to the method that requires setting the tablespace in READ ONLY mode.

Following are the steps done by RMAN to implement the transport tablespace from backup:

Note: The tablespace set may be kept online and in READ WRITE mode at the source database during the cloning process.

specifies the name of the generated Data Pump export dump file. Its default value is dmpfile.dmp

specifies the name of the log file for the Data Pump export job. Its default value is explog.log

specifies the name of the sample import script. Its default value is impscrpt.sql. The import script is written to the location specified by the TABLESPACE DESTINATION parameter.

it is a required parameter that specifies the default location for the data files in the recovery set.

The UNTIL clause is used to specify the point-in-time for the tablespace set version. You may specify the point-in-time as an SCN, TIME, or log SEQUENCE.

In Oracle Database 10g Release 2, you can build a repository to store versions of tablespace, referred to as a tablespace rack. The repository may be located in the same database as the tablespaces being versioned, or may be located in a different database. Handling this option is not covered in this document.

The new Load Data wizard enhancements enable you to load data from external flat files into the database. It uses the table name and data file name that you specify, along with other information, to scan the data file and define a usable SQL*Loader control file. The wizard will create the control file for you. It then uses SQL*Loader to load the data.

Note: Not all control file functionality is supported in the Load Data wizard.

You can access the Load Data page from: Maintenance tabbed page | Move Row Data section

This feature (in Release 2) allows bulk DML operations to continue processing, when a DML error occurs, with the ability to log the errors in a DML error logging table.

This can be automatically done by the DBMS_ERRLOG.CREATE_ERROR_LOG procedure. It creates an error logging table with all of the mandatory error description columns plus all of the columns from the named DML table.

You can create the error logging table manually using the normal DDL statements but it must contain the following mandatory columns:

If you do not provide an error logging table name, the database logs to an error logging table with a default name.

You can also specify UNLIMITED for the REJECT LIMIT clause. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back.

This eliminates the wait for an I/O to the redo log but the system must be able to tolerate loss of asynchronously committed transaction.

IMMEDIATE specifies redo should be written immediately by LGWR process when transaction is committed (default)

WAIT specifies commit will not return until redo is persistent in online redo log (default)

COMMIT_WRITE initialization parameter determines default value of COMMIT WRITE statement.