Oracle Monitoring a Data Pump Job

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Viewing Data Pump Jobs

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

OWNER_NAME : User that initiated the job

JOB_NAME : Name of the job

OPERATION : Type of operation being performed

JOB_MODE : FULL, TABLE, SCHEMA, or TABLESPACE

STATE : UNDEFINED, DEFINING, EXECUTING, and NOT RUNNING.

DEGREE : Number of worker processes performing the operation

ATTACHED_SESSIONS : Number of sessions attached to the job.

Viewing Data Pump Sessions

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

JOB_NAME : Name of the job

SADDR : Address of the session attached to the job.

Viewing Data Pump Job Progress

Use V$SESSION_LONGOPS to monitor the progress of an export/import job.

TOTALWORK : shows the total estimated number of megabytes in the job.

SOFAR : megabytes transferred thus far in the job.

UNITS : stands for megabytes.

OPNAME : shows the Data Pump job name.

Creating External Tables for Data Population - Features of External Table Population Operations

o You can use the ORACLE_LOADER or ORACLE_DATAPUMP access drivers to perform data loads. You can use only the new ORACLE_DATA_PUMP access driver for unloading data (populating external tables).

o No DML or indexes are possible for external tables.

o You can use the datafiles created for an external table in the same database or a different database.

Creating External Tables

CREATE OR REPLACE DIRECTORY employee_data AS
'C:\employee_data'
CREATE TABLE employee_ext
(empid NUMBER(8),
emp_name VARCHAR2(30),
dept_name VARCHAR2(20),
hire_date date)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER -- or ORACLE_DATAPUMP
DEFAULT DIRECTORY employee_data
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL)
LOCATION ('emp.dat')
)
REJECT LIMIT UNLIMITED

Loading and Unloading Data

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.

CREATE TABLE dept_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_tab_dir1
LOCATION ('dept_xt.dmp')
)
AS SELECT * FROM scott.DEPT

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:

SET LONG 2000
SELECT
DBMS_METADATA.GET_DDL('TABLE','EXTRACT_CUST')
FROM DUAL

Parallel Population of External Tables

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.

CREATE TABLE inventories_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATA PUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv.dmp1',�inv.dmp2�,inv.dmp3�)
)
PARALLEL
AS SELECT * FROM inventories

Defining External Table Properties

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

TABLE_NAME

TYPE_OWNER - Owner of the implementation type for the external table access driver

TYPE_NAME - Name of the implementation type for the external table access driver

DEFAULT_DIRECTORY_OWNER

DEFAULT_DIRECTORY_NAME

REJECT_LIMIT - Reject limit for the external table

ACCESS_TYPE - Type of access parameters for the external table:

BLOB or CLOB

ACCESS_PARAMETERS - Access parameters for the external table

PROPERTY - Property of the external table:

o REFERENCED - Referenced columns

o ALL (default)- All columns

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.

To change the PROPERTY value for a table:

ALTER TABLE dept_xt
PROJECT COLUMN REFERENCED