Oracle Database Utilities

Oracle® Database

Utilities

10g Release 1 (10.1)

Part No. B10825-01

December 2003

Oracle Database Utilities, 10g Release 1 (10.1)

Part No. B10825-01

Primary Author: Kathy Rich

Contributors: Lee Barton, Ellen Batbouta, Janet Blowney, George Claborn, Jay Davison, Steve DiPirro,

Bill Fisher, Dean Gagne, John Galanes, John Kalogeropoulos, Jonathan Klein, Cindy Lim, Eric Magrath,

Brian McCarthy, Rod Payne, Ray Pfau, Rich Phillips, Paul Reilly, Mike Sakayeda, Francisco Sanchez,

Marilyn Saunders, Jim Stenoish, Carol Tagliaferri

The Programs (which include both the software and documentation) contain proprietary information of

Oracle Corporation; they are provided under a license agreement containing restrictions on use and

disclosure and are also protected by copyright, patent and other intellectual and industrial property

laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required

to obtain interoperability with other independently created software or as speciﬁed by law, is prohibited.

The information contained in this document is subject to change without notice. If you ﬁnd any problems

in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this

document is error-free. Except as may be expressly permitted in your license agreement for these

Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,

electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.

If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on

behalf of the U.S. Government, the following notice is applicable:

Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial

computer software" and use, duplication, and disclosure of the Programs, including documentation,

shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.

Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer

software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR

52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500

Oracle Parkway, Redwood City, CA 94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently

dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,

redundancy, and other measures to ensure the safe use of such applications if the Programs are used for

such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the

Programs.

Oracle is a registered trademark, and Oracle Store, Oracle8, Oracle8i, Oracle9i, PL/SQL, SQL*Net, and

SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Other names may be

trademarks of their respective owners.

iii

Contents

Send Us Your Comments ................................................................................................................. xli

Preface.......................................................................................................................................................... xliii

Audience ............................................................................................................................................... xliii

Documentation Accessibility ............................................................................................................ xliv

Organization........................................................................................................................................ xliv

Related Documentation .................................................................................................................... xlvii

Conventions........................................................................................................................................ xlviii

What's New in Database Utilities?................................................................................................ liii

New Features in Oracle Database 10g ................................................................................................ liii

Volume 1

Part I Oracle Data Pump

1 Overview of Oracle Data Pump

Data Pump Components ................................................................................................................... 1-1

What New Features Do Data Pump Export and Import Provide?............................................. 1-2

How Does Data Pump Access Data?............................................................................................... 1-4

Direct Path Loads and Unloads.................................................................................................. 1-5

External Tables.............................................................................................................................. 1-6

Accessing Data Over a Network...................................................................................................... 1-6

What Happens During Execution of a Data Pump Job? ............................................................. 1-7

Coordination of a Job ................................................................................................................... 1-7

Tracking Progress Within a Job .................................................................................................. 1-7

Filtering Data During a Job ......................................................................................................... 1-8

Transforming Metadata During a Job........................................................................................ 1-8

Maximizing Job Performance...................................................................................................... 1-8

Loading and Unloading of Data................................................................................................. 1-9

Monitoring Job Status........................................................................................................................ 1-9

The DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS Views............................... 1-10

The DBA_DATAPUMP_SESSIONS View .............................................................................. 1-11

Monitoring the Progress of Executing Jobs............................................................................. 1-11

File Allocation.................................................................................................................................... 1-12

Specifying Files and Adding Additional Dump Files........................................................... 1-12

Default Locations for Dump, Log, and SQL Files.................................................................. 1-13

Using Directory Objects When Automatic Storage Management Is Enabled ............ 1-14

Setting Parallelism...................................................................................................................... 1-15

Using Substitution Variables..................................................................................................... 1-16

Original Export and Import Versus Data Pump Export and Import....................................... 1-16

2 Data Pump Export

What Is Data Pump Export?.............................................................................................................. 2-1

Invoking Data Pump Export............................................................................................................. 2-2

Data Pump Export Interfaces...................................................................................................... 2-3

Data Pump Export Modes........................................................................................................... 2-3

Full Export Mode................................................................................................................... 2-4

Schema Mode......................................................................................................................... 2-4

Table Mode............................................................................................................................. 2-4

Tablespace Mode ................................................................................................................... 2-4

Transportable Tablespace Mode ......................................................................................... 2-5

Network Considerations.............................................................................................................. 2-5

Filtering During Export Operations................................................................................................ 2-6

Data Filters..................................................................................................................................... 2-6

Metadata Filters............................................................................................................................. 2-6

Parameters Available in Export's Command-Line Mode............................................................ 2-8

ATTACH........................................................................................................................................ 2-9

CONTENT ................................................................................................................................... 2-10

DIRECTORY................................................................................................................................ 2-10

DUMPFILE .................................................................................................................................. 2-12

ESTIMATE................................................................................................................................... 2-14

ESTIMATE_ONLY ..................................................................................................................... 2-14

EXCLUDE.................................................................................................................................... 2-15

FILESIZE...................................................................................................................................... 2-17

FLASHBACK_SCN .................................................................................................................... 2-18

FLASHBACK_TIME................................................................................................................... 2-18

FULL............................................................................................................................................. 2-19

HELP ............................................................................................................................................ 2-20

INCLUDE .................................................................................................................................... 2-20

JOB_NAME.................................................................................................................................. 2-22

LOGFILE...................................................................................................................................... 2-22

NETWORK_LINK...................................................................................................................... 2-23

NOLOGFILE................................................................................................................................ 2-24

PARALLEL.................................................................................................................................. 2-25

PARFILE ...................................................................................................................................... 2-27

QUERY......................................................................................................................................... 2-27

SCHEMAS ................................................................................................................................... 2-29

STATUS........................................................................................................................................ 2-29

TABLES........................................................................................................................................ 2-30

TABLESPACES ........................................................................................................................... 2-31

TRANSPORT_FULL_CHECK .................................................................................................. 2-32

TRANSPORT_TABLESPACES................................................................................................. 2-33

VERSION ..................................................................................................................................... 2-34

How Data Pump Export Parameters Map to Those of the Original Export Utility ............. 2-35

Commands Available in Export's Interactive-Command Mode.............................................. 2-37

ADD_FILE ................................................................................................................................... 2-38

CONTINUE_CLIENT ................................................................................................................ 2-39

EXIT_CLIENT ............................................................................................................................. 2-39

HELP ............................................................................................................................................ 2-39

KILL_JOB..................................................................................................................................... 2-40

PARALLEL.................................................................................................................................. 2-40

START_JOB ................................................................................................................................. 2-41

STATUS........................................................................................................................................ 2-41

STOP_JOB .................................................................................................................................... 2-42

Examples of Using Data Pump Export.......................................................................................... 2-43

Performing a Table-Mode Export............................................................................................. 2-43

Data-Only Unload of Selected Tables and Rows ................................................................... 2-43

Estimating Disk Space Needed in a Table-Mode Export...................................................... 2-44

Performing a Schema-Mode Export......................................................................................... 2-44

Performing a Parallel Full Database Export ........................................................................... 2-45

Using Interactive Mode to Stop and Reattach to a Job.......................................................... 2-45

Syntax Diagrams for Data Pump Export ...................................................................................... 2-46

3 Data Pump Import

What Is Data Pump Import? ............................................................................................................. 3-1

Invoking Data Pump Import ............................................................................................................ 3-2

Data Pump Import Interfaces...................................................................................................... 3-2

Data Pump Import Modes........................................................................................................... 3-3

Full Import Mode .................................................................................................................. 3-4

Schema Mode......................................................................................................................... 3-4

Table Mode............................................................................................................................. 3-4

Tablespace Mode ................................................................................................................... 3-4

Transportable Tablespace Mode ......................................................................................... 3-5

Network Considerations.............................................................................................................. 3-5

Filtering During Import Operations ............................................................................................... 3-6

Data Filters..................................................................................................................................... 3-6

Metadata Filters............................................................................................................................. 3-6

Parameters Available in Import's Command-Line Mode ........................................................... 3-7

ATTACH........................................................................................................................................ 3-8

CONTENT ..................................................................................................................................... 3-9

DIRECTORY................................................................................................................................ 3-10

DUMPFILE .................................................................................................................................. 3-11

ESTIMATE................................................................................................................................... 3-12

EXCLUDE .................................................................................................................................... 3-13

FLASHBACK_SCN..................................................................................................................... 3-15

FLASHBACK_TIME................................................................................................................... 3-16

FULL............................................................................................................................................. 3-17

HELP............................................................................................................................................. 3-18

vii

INCLUDE .................................................................................................................................... 3-18

JOB_NAME.................................................................................................................................. 3-20

LOGFILE...................................................................................................................................... 3-20

NETWORK_LINK...................................................................................................................... 3-22

NOLOGFILE................................................................................................................................ 3-23

PARALLEL.................................................................................................................................. 3-23

PARFILE ...................................................................................................................................... 3-24

QUERY......................................................................................................................................... 3-25

REMAP_DATAFILE .................................................................................................................. 3-27

REMAP_SCHEMA..................................................................................................................... 3-27

REMAP_TABLESPACE............................................................................................................. 3-29

REUSE_DATAFILES.................................................................................................................. 3-30

SCHEMAS ................................................................................................................................... 3-30

SKIP_UNUSABLE_INDEXES................................................................................................... 3-31

SQLFILE....................................................................................................................................... 3-32

STATUS........................................................................................................................................ 3-33

STREAMS_CONFIGURATION ............................................................................................... 3-33

TABLE_EXISTS_ACTION......................................................................................................... 3-34

TABLES........................................................................................................................................ 3-35

TABLESPACES ........................................................................................................................... 3-36

TRANSFORM.............................................................................................................................. 3-37

TRANSPORT_DATAFILES ...................................................................................................... 3-39

TRANSPORT_FULL_CHECK .................................................................................................. 3-40

TRANSPORT_TABLESPACES................................................................................................. 3-41

VERSION ..................................................................................................................................... 3-41

How Data Pump Import Parameters Map to Those of the Original Import Utility............ 3-42

Commands Available in Import's Interactive-Command Mode ............................................. 3-44

CONTINUE_CLIENT ................................................................................................................ 3-45

EXIT_CLIENT ............................................................................................................................. 3-46

HELP ............................................................................................................................................ 3-46

KILL_JOB..................................................................................................................................... 3-47

PARALLEL.................................................................................................................................. 3-47

START_JOB ................................................................................................................................. 3-48

STATUS........................................................................................................................................ 3-48

STOP_JOB.................................................................................................................................... 3-49

viii

Examples of Using Data Pump Import......................................................................................... 3-49

Performing a Data-Only Table-Mode Import......................................................................... 3-50

Performing a Schema-Mode Import ........................................................................................ 3-50

Performing a Network-Mode Import...................................................................................... 3-50

Syntax Diagrams for Data Pump Import...................................................................................... 3-51

4 Data Pump Performance

Data Performance Improvements for Data Pump Export and Import...................................... 4-1

Tuning Performance ........................................................................................................................... 4-2

Controlling Resource Consumption .......................................................................................... 4-2

Initialization Parameters That Affect Data Pump Performance................................................ 4-3

5 The Data Pump API

How Does the Client Interface to the Data Pump API Work? ................................................... 5-1

Job States ........................................................................................................................................ 5-2

What Are the Basic Steps in Using the Data Pump API?............................................................ 5-4

Examples of Using the Data Pump API.......................................................................................... 5-4

Part II SQL*Loader

6 SQL*Loader Concepts

SQL*Loader Features.......................................................................................................................... 6-1

SQL*Loader Parameters..................................................................................................................... 6-3

SQL*Loader Control File................................................................................................................... 6-4

Input Data and Dataﬁles.................................................................................................................... 6-5

Fixed Record Format.................................................................................................................... 6-5

Variable Record Format............................................................................................................... 6-6

Stream Record Format ................................................................................................................ 6-7

Logical Records............................................................................................................................. 6-8

Data Fields ..................................................................................................................................... 6-9

LOBFILEs and Secondary Dataﬁles (SDFs)................................................................................... 6-9

Data Conversion and Datatype Speciﬁcation.............................................................................. 6-10

Discarded and Rejected Records.................................................................................................... 6-10

The Bad File ................................................................................................................................. 6-11

SQL*Loader Rejects............................................................................................................. 6-11

Oracle Database Rejects...................................................................................................... 6-11

The Discard File .......................................................................................................................... 6-11

Log File and Logging Information ................................................................................................ 6-12

Conventional Path Loads, Direct Path Loads, and External Table Loads.............................. 6-12

Conventional Path Loads .......................................................................................................... 6-12

Direct Path Loads ....................................................................................................................... 6-13

Parallel Direct Path.............................................................................................................. 6-13

External Table Loads.................................................................................................................. 6-13

Choosing External Tables Versus SQL*Loader...................................................................... 6-14

Loading Objects, Collections, and LOBs ..................................................................................... 6-14

Supported Object Types ............................................................................................................ 6-14

column objects ..................................................................................................................... 6-14

row objects............................................................................................................................ 6-15

Supported Collection Types...................................................................................................... 6-15

Nested Tables....................................................................................................................... 6-15

VARRAYs............................................................................................................................. 6-15

Supported LOB Types................................................................................................................ 6-15

Partitioned Object Support............................................................................................................. 6-16

Application Development: Direct Path Load API...................................................................... 6-16

7 SQL*Loader Command-Line Reference

Invoking SQL*Loader........................................................................................................................ 7-1

Alternative Ways to Specify Parameters................................................................................... 7-3

Command-Line Parameters .............................................................................................................. 7-3

BAD (bad file)................................................................................................................................ 7-3

BINDSIZE (maximum size)......................................................................................................... 7-4

COLUMNARRAYROWS ............................................................................................................ 7-4

CONTROL (control file) .............................................................................................................. 7-4

DATA (datafile) ............................................................................................................................ 7-5

DATE_CACHE ............................................................................................................................. 7-5

DIRECT (data path)...................................................................................................................... 7-6

DISCARD (filename).................................................................................................................... 7-6

DISCARDMAX (integer) ............................................................................................................. 7-6

ERRORS (errors to allow)............................................................................................................ 7-6

EXTERNAL_TABLE..................................................................................................................... 7-7

Restrictions When Using EXTERNAL_TABLE................................................................. 7-8

FILE (file to load into) .................................................................................................................. 7-9

LOAD (records to load) ............................................................................................................... 7-9

LOG (log file)................................................................................................................................. 7-9

MULTITHREADING ................................................................................................................... 7-9

PARALLEL (parallel load) ........................................................................................................ 7-10

PARFILE (parameter file) .......................................................................................................... 7-10

READSIZE (read buffer size) .................................................................................................... 7-10

RESUMABLE............................................................................................................................... 7-11

RESUMABLE_NAME................................................................................................................ 7-12

RESUMABLE_TIMEOUT.......................................................................................................... 7-12

ROWS (rows per commit).......................................................................................................... 7-12

SILENT (feedback mode)........................................................................................................... 7-13

SKIP (records to skip)................................................................................................................. 7-14

SKIP_INDEX_MAINTENANCE.............................................................................................. 7-14

SKIP_UNUSABLE_INDEXES................................................................................................... 7-15

STREAMSIZE .............................................................................................................................. 7-15

USERID (username/password)................................................................................................ 7-16

Exit Codes for Inspection and Display......................................................................................... 7-16

8 SQL*Loader Control File Reference

Control File Contents......................................................................................................................... 8-2

Comments in the Control File..................................................................................................... 8-4

Specifying Command-Line Parameters in the Control File ....................................................... 8-4

OPTIONS Clause .......................................................................................................................... 8-4

Specifying Filenames and Object Names ...................................................................................... 8-5

Filenames That Conflict with SQL and SQL*Loader Reserved Words ................................ 8-5

Specifying SQL Strings................................................................................................................. 8-5

Operating System Considerations.............................................................................................. 8-5

Specifying a Complete Path................................................................................................. 8-6

Backslash Escape Character ................................................................................................. 8-6

Nonportable Strings.............................................................................................................. 8-6

Using the Backslash as an Escape Character..................................................................... 8-6

Escape Character Is Sometimes Disallowed...................................................................... 8-7

Identifying XML Type Tables........................................................................................................... 8-7

Specifying Dataﬁles ........................................................................................................................... 8-8

Examples of INFILE Syntax ...................................................................................................... 8-10

Specifying Multiple Datafiles ................................................................................................... 8-10

Identifying Data in the Control File with BEGINDATA.......................................................... 8-11

Specifying Dataﬁle Format and Buffering................................................................................... 8-12

Specifying the Bad File.................................................................................................................... 8-12

Examples of Specifying a Bad File Name................................................................................ 8-13

How Bad Files Are Handled with LOBFILEs and SDFs....................................................... 8-14

Criteria for Rejected Records .................................................................................................... 8-14

Specifying the Discard File............................................................................................................. 8-14

Specifying the Discard File in the Control File....................................................................... 8-15

Specifying the Discard File from the Command Line........................................................... 8-15

Examples of Specifying a Discard File Name......................................................................... 8-16

Criteria for Discarded Records................................................................................................. 8-16

How Discard Files Are Handled with LOBFILEs and SDFs................................................ 8-16

Limiting the Number of Discarded Records .......................................................................... 8-16

Handling Different Character Encoding Schemes..................................................................... 8-17

Multibyte (Asian) Character Sets ............................................................................................. 8-17

Unicode Character Sets.............................................................................................................. 8-18

Database Character Sets............................................................................................................. 8-18

Datafile Character Sets............................................................................................................... 8-19

Input Character Conversion...................................................................................................... 8-19

Considerations When Loading Data into VARRAYs or Primary-Key-Based REFs.. 8-20

CHARACTERSET Parameter ............................................................................................ 8-20

Control File Character Set .................................................................................................. 8-22

Character-Length Semantics.............................................................................................. 8-23

Interrupted Loads ............................................................................................................................. 8-24

Discontinued Conventional Path Loads ................................................................................. 8-25

Discontinued Direct Path Loads............................................................................................... 8-25

Load Discontinued Because of Space Errors .................................................................. 8-25

Load Discontinued Because Maximum Number of Errors Exceeded......................... 8-26

Load Discontinued Because of Fatal Errors..................................................................... 8-26

Load Discontinued Because a Ctrl+C Was Issued.......................................................... 8-26

Status of Tables and Indexes After an Interrupted Load...................................................... 8-26

xii

Using the Log File to Determine Load Status......................................................................... 8-27

Continuing Single-Table Loads ................................................................................................ 8-27

Assembling Logical Records from Physical Records................................................................. 8-27

Using CONCATENATE to Assemble Logical Records ........................................................ 8-28

Using CONTINUEIF to Assemble Logical Records .............................................................. 8-28

Loading Logical Records into Tables ............................................................................................ 8-32

Specifying Table Names ............................................................................................................ 8-32

INTO TABLE Clause........................................................................................................... 8-33

Table-Specific Loading Method................................................................................................ 8-34

Loading Data into Empty Tables....................................................................................... 8-34

Loading Data into Nonempty Tables ............................................................................... 8-34

Table-Specific OPTIONS Parameter ........................................................................................ 8-35

Loading Records Based on a Condition .................................................................................. 8-36

Using the WHEN Clause with LOBFILEs and SDFs...................................................... 8-36

Specifying Default Data Delimiters ...................................................................................... 8-36

fields_spec............................................................................................................................. 8-37

termination_spec.................................................................................................................. 8-37

enclosure_spec ..................................................................................................................... 8-37

Handling Short Records with Missing Data........................................................................... 8-38

TRAILING NULLCOLS Clause ........................................................................................ 8-38

Index Options.................................................................................................................................... 8-39

SORTED INDEXES Clause........................................................................................................ 8-39

SINGLEROW Option ................................................................................................................. 8-39

Beneﬁts of Using Multiple INTO TABLE Clauses..................................................................... 8-40

Extracting Multiple Logical Records........................................................................................ 8-40

Relative Positioning Based on Delimiters........................................................................ 8-41

Distinguishing Different Input Record Formats.................................................................... 8-41

Relative Positioning Based on the POSITION Parameter ............................................ 8-42

Distinguishing Different Input Row Object Subtypes .......................................................... 8-42

Loading Data into Multiple Tables .......................................................................................... 8-44

Summary...................................................................................................................................... 8-44

Bind Arrays and Conventional Path Loads.................................................................................. 8-45

Size Requirements for Bind Arrays.......................................................................................... 8-45

Performance Implications of Bind Arrays............................................................................... 8-45

Specifying Number of Rows Versus Size of Bind Array....................................................... 8-46

xiii

Calculations to Determine Bind Array Size............................................................................ 8-46

Determining the Size of the Length Indicator................................................................. 8-48

Calculating the Size of Field Buffers................................................................................. 8-48

Minimizing Memory Requirements for Bind Arrays............................................................ 8-50

Calculating Bind Array Size for Multiple INTO TABLE Clauses ....................................... 8-51

9 Field List Reference

Field List Contents.............................................................................................................................. 9-1

Specifying the Position of a Data Field.......................................................................................... 9-3

Using POSITION with Data Containing Tabs.......................................................................... 9-4

Using POSITION with Multiple Table Loads .......................................................................... 9-4

Examples of Using POSITION.................................................................................................... 9-4

Specifying Columns and Fields ...................................................................................................... 9-5

Specifying Filler Fields................................................................................................................. 9-6

Specifying the Datatype of a Data Field.................................................................................... 9-7

SQL*Loader Datatypes ...................................................................................................................... 9-8

Nonportable Datatypes................................................................................................................ 9-8

INTEGER(n) ........................................................................................................................... 9-9

SMALLINT........................................................................................................................... 9-10

FLOAT................................................................................................................................... 9-10

DOUBLE ............................................................................................................................... 9-10

BYTEINT .............................................................................................................................. 9-11

ZONED ................................................................................................................................. 9-11

DECIMAL............................................................................................................................. 9-11

VARGRAPHIC .................................................................................................................... 9-12

VARCHAR ........................................................................................................................... 9-13

VARRAW ............................................................................................................................. 9-14

LONG VARRAW ................................................................................................................ 9-15

Portable Datatypes ..................................................................................................................... 9-15

CHAR.................................................................................................................................... 9-15

Datetime and Interval Datatypes...................................................................................... 9-16

GRAPHIC............................................................................................................................. 9-19

GRAPHIC EXTERNAL ...................................................................................................... 9-20

Numeric EXTERNAL.......................................................................................................... 9-21

RAW...................................................................................................................................... 9-21

xiv

VARCHARC......................................................................................................................... 9-22

VARRAWC........................................................................................................................... 9-22

Conflicting Native Datatype Field Lengths..................................................................... 9-23

Field Lengths for Length-Value Datatypes...................................................................... 9-23

Datatype Conversions................................................................................................................ 9-24

Datatype Conversions for Datetime and Interval Datatypes............................................... 9-24

Specifying Delimiters................................................................................................................. 9-25

TERMINATED Fields ......................................................................................................... 9-26

ENCLOSED Fields............................................................................................................... 9-26

Syntax for Termination and Enclosure Specification..................................................... 9-26

Delimiter Marks in the Data............................................................................................... 9-28

Maximum Length of Delimited Data .............................................................................. 9-29

Loading Trailing Blanks with Delimiters......................................................................... 9-29

Conflicting Field Lengths for Character Datatypes............................................................... 9-29

Predetermined Size Fields.................................................................................................. 9-29

Delimited Fields................................................................................................................... 9-30

Date Field Masks ................................................................................................................. 9-30

Specifying Field Conditions........................................................................................................... 9-31

Comparing Fields to BLANKS.................................................................................................. 9-32

Comparing Fields to Literals..................................................................................................... 9-33

Using the WHEN, NULLIF, and DEFAULTIF Clauses .............................................................. 9-33

Examples of Using the WHEN, NULLIF, and DEFAULTIF Clauses.................................. 9-36

Loading Data Across Different Platforms.................................................................................... 9-38

Byte Ordering .................................................................................................................................... 9-39

Specifying Byte Order................................................................................................................ 9-40

Using Byte Order Marks (BOMs) ............................................................................................. 9-41

Suppressing Checks for BOMs .......................................................................................... 9-43

Loading All-Blank Fields ................................................................................................................ 9-44

Trimming Whitespace ...................................................................................................................... 9-44

Datatypes for Which Whitespace Can Be Trimmed.............................................................. 9-47

Specifying Field Length for Datatypes for Which Whitespace Can Be Trimmed............. 9-47

Predetermined Size Fields ................................................................................................. 9-47

Delimited Fields................................................................................................................... 9-48

Relative Positioning of Fields.................................................................................................... 9-48

No Start Position Specified for a Field.............................................................................. 9-48

Previous Field Terminated by a Delimiter ...................................................................... 9-49

Previous Field Has Both Enclosure and Termination Delimiters ................................ 9-49

Leading Whitespace ................................................................................................................ 9-49

Previous Field Terminated by Whitespace ................................................................... 9-50

Optional Enclosure Delimiters ......................................................................................... 9-50

Trimming Trailing Whitespace ................................................................................................ 9-51

Trimming Enclosed Fields......................................................................................................... 9-51

How the PRESERVE BLANKS Option Affects Whitespace Trimming ................................. 9-51

How [NO] PRESERVE BLANKS Works with Delimiter Clauses ....................................... 9-52

Applying SQL Operators to Fields................................................................................................ 9-52

Referencing Fields ...................................................................................................................... 9-54

Common Uses of SQL Operators in Field Specifications ..................................................... 9-55

Combinations of SQL Operators.............................................................................................. 9-56

Using SQL Strings with a Date Mask ...................................................................................... 9-56

Interpreting Formatted Fields................................................................................................... 9-56

Using SQL Strings to Load the ANYDATA Database Type ................................................ 9-57

Using SQL*Loader to Generate Data for Input .......................................................................... 9-58

Loading Data Without Files ..................................................................................................... 9-58

Setting a Column to a Constant Value .................................................................................... 9-58

CONSTANT Parameter...................................................................................................... 9-58

Setting a Column to an Expression Value............................................................................... 9-59

EXPRESSION Parameter.................................................................................................... 9-59

Setting a Column to the Datafile Record Number................................................................. 9-59

RECNUM Parameter .......................................................................................................... 9-60

Setting a Column to the Current Date ................................................................................. 9-60

SYSDATE Parameter........................................................................................................... 9-60

Setting a Column to a Unique Sequence Number................................................................. 9-60

SEQUENCE Parameter....................................................................................................... 9-60

Generating Sequence Numbers for Multiple Tables............................................................. 9-61

Example: Generating Different Sequence Numbers for Each Insert............................ 9-62

10 Loading Objects, LOBs, and Collections

Loading Column Objects ................................................................................................................ 10-1

Loading Column Objects in Stream Record Format.............................................................. 10-2

Loading Column Objects in Variable Record Format........................................................... 10-3

xvi

Loading Nested Column Objects ............................................................................................. 10-4

Loading Column Objects with a Derived Subtype................................................................ 10-4

Specifying Null Values for Objects .......................................................................................... 10-6

Specifying Attribute Nulls ................................................................................................. 10-6

Specifying Atomic Nulls..................................................................................................... 10-7

Loading Column Objects with User-Defined Constructors ................................................. 10-8

Loading Object Tables ................................................................................................................... 10-12

Loading Object Tables with a Subtype.................................................................................. 10-13

Loading REF Columns................................................................................................................... 10-14

System-Generated OID REF Columns................................................................................... 10-15

Primary Key REF Columns ..................................................................................................... 10-15

Unscoped REF Columns That Allow Primary Keys............................................................ 10-16

Loading LOBs.................................................................................................................................. 10-18

Loading LOB Data from a Primary Datafile ........................................................................ 10-19

LOB Data in Predetermined Size Fields......................................................................... 10-19

LOB Data in Delimited Fields.......................................................................................... 10-20

LOB Data in Length-Value Pair Fields........................................................................... 10-21

Loading LOB Data from LOBFILEs ....................................................................................... 10-22

Dynamic Versus Static LOBFILE Specifications ........................................................... 10-23

Examples of Loading LOB Data from LOBFILEs ......................................................... 10-23

Considerations When Loading LOBs from LOBFILEs ................................................ 10-27

Loading BFILE Columns ............................................................................................................... 10-28

Loading Collections (Nested Tables and VARRAYs)............................................................... 10-29

Restrictions in Nested Tables and VARRAYs ...................................................................... 10-30

Secondary Datafiles (SDFs) ..................................................................................................... 10-32

Dynamic Versus Static SDF Speciﬁcations................................................................................ 10-33

Loading a Parent Table Separately from Its Child Table ........................................................ 10-33

Memory Issues When Loading VARRAY Columns............................................................ 10-35

11 Conventional and Direct Path Loads

Data Loading Methods..................................................................................................................... 11-1

Loading ROWID Columns........................................................................................................ 11-4

Conventional Path Load .................................................................................................................. 11-4

Conventional Path Load of a Single Partition ........................................................................ 11-4

When to Use a Conventional Path Load ................................................................................. 11-4

xvii

Direct Path Load................................................................................................................................ 11-5

Data Conversion During Direct Path Loads........................................................................... 11-6

Direct Path Load of a Partitioned or Subpartitioned Table.................................................. 11-7

Direct Path Load of a Single Partition or Subpartition ......................................................... 11-7

Advantages of a Direct Path Load ........................................................................................... 11-8

Restrictions on Using Direct Path Loads................................................................................. 11-9

Restrictions on a Direct Path Load of a Single Partition....................................................... 11-9

When to Use a Direct Path Load ............................................................................................ 11-10

Integrity Constraints ................................................................................................................ 11-10

Field Defaults on the Direct Path ........................................................................................... 11-10

Loading into Synonyms........................................................................................................... 11-10

Using Direct Path Load.................................................................................................................. 11-11

Setting Up for Direct Path Loads ........................................................................................... 11-11

Specifying a Direct Path Load................................................................................................. 11-11

Building Indexes....................................................................................................................... 11-11

Improving Performance ................................................................................................... 11-12

Temporary Segment Storage Requirements.................................................................. 11-12

Indexes Left in an Unusable State.......................................................................................... 11-13

Using Data Saves to Protect Against Data Loss................................................................... 11-13

Using the ROWS Parameter............................................................................................. 11-14

Data Save Versus Commit ............................................................................................... 11-14

Data Recovery During Direct Path Loads............................................................................. 11-15

Media Recovery and Direct Path Loads......................................................................... 11-15

Instance Recovery and Direct Path Loads ..................................................................... 11-15

Loading Long Data Fields....................................................................................................... 11-16

Loading Data As PIECED ................................................................................................ 11-16

Optimizing Performance of Direct Path Loads......................................................................... 11-17

Preallocating Storage for Faster Loading.............................................................................. 11-17

Presorting Data for Faster Indexing....................................................................................... 11-18

SORTED INDEXES Clause............................................................................................... 11-18

Unsorted Data.................................................................................................................... 11-18

Multiple-Column Indexes................................................................................................ 11-19

Choosing the Best Sort Order .......................................................................................... 11-19

Infrequent Data Saves.............................................................................................................. 11-20

Minimizing Use of the Redo Log ........................................................................................... 11-20

xviii

Disabling Archiving.......................................................................................................... 11-20

Specifying the SQL*Loader UNRECOVERABLE Clause............................................ 11-20

Setting the SQL NOLOGGING Parameter .................................................................... 11-21

Specifying the Number of Column Array Rows and Size of Stream Buffers .................. 11-21

Specifying a Value for the Date Cache .................................................................................. 11-22

Optimizing Direct Path Loads on Multiple-CPU Systems..................................................... 11-23

Avoiding Index Maintenance ....................................................................................................... 11-24

Direct Loads, Integrity Constraints, and Triggers .................................................................... 11-25

Integrity Constraints ................................................................................................................ 11-25

Enabled Constraints .......................................................................................................... 11-25

Disabled Constraints ........................................................................................................ 11-26

Reenable Constraints ........................................................................................................ 11-26

Database Insert Triggers.......................................................................................................... 11-28

Replacing Insert Triggers with Integrity Constraints................................................... 11-28

When Automatic Constraints Cannot Be Used............................................................. 11-28

Preparation ......................................................................................................................... 11-28

Using an Update Trigger.................................................................................................. 11-29

Duplicating the Effects of Exception Conditions.......................................................... 11-29

Using a Stored Procedure................................................................................................. 11-30

Permanently Disabled Triggers and Constraints................................................................. 11-30

Increasing Performance with Concurrent Conventional Path Loads ............................... 11-31

Parallel Data Loading Models...................................................................................................... 11-31

Concurrent Conventional Path Loads ................................................................................... 11-31

Intersegment Concurrency with Direct Path........................................................................ 11-32

Intrasegment Concurrency with Direct Path........................................................................ 11-32

Restrictions on Parallel Direct Path Loads............................................................................ 11-32

Initiating Multiple SQL*Loader Sessions.............................................................................. 11-33

Parameters for Parallel Direct Path Loads ............................................................................ 11-34

Using the FILE Parameter to Specify Temporary Segments....................................... 11-34

Enabling Constraints After a Parallel Direct Path Load ..................................................... 11-35

PRIMARY KEY and UNIQUE KEY Constraints.................................................................. 11-35

General Performance Improvement Hints................................................................................. 11-35

12 SQL*Loader Case Studies

The Case Studies............................................................................................................................... 12-2

xix

Case Study Files................................................................................................................................ 12-3

Tables Used in the Case Studies .................................................................................................... 12-4

Contents of Table emp............................................................................................................... 12-4

Contents of Table dept............................................................................................................... 12-4

Checking the Results of a Load...................................................................................................... 12-4

References and Notes....................................................................................................................... 12-5

Case Study 1: Loading Variable-Length Data ............................................................................. 12-5

Control File for Case Study 1.................................................................................................... 12-5

Running Case Study 1................................................................................................................ 12-6

Log File for Case Study 1........................................................................................................... 12-6

Case Study 2: Loading Fixed-Format Fields ................................................................................ 12-8

Control File for Case Study 2.................................................................................................... 12-8

Datafile for Case Study 2........................................................................................................... 12-9

Running Case Study 2................................................................................................................ 12-9

Log File for Case Study 2......................................................................................................... 12-10

Case Study 3: Loading a Delimited, Free-Format File............................................................. 12-11

Control File for Case Study 3.................................................................................................. 12-11

Running Case Study 3.............................................................................................................. 12-13

Log File for Case Study 3......................................................................................................... 12-13

Case Study 4: Loading Combined Physical Records ............................................................... 12-14

Control File for Case Study 4.................................................................................................. 12-15

Datafile for Case Study 4......................................................................................................... 12-16

Rejected Records................................................................................................................ 12-16

Running Case Study 4.............................................................................................................. 12-16

Log File for Case Study 4......................................................................................................... 12-17

Bad File for Case Study 4......................................................................................................... 12-18

Case Study 5: Loading Data into Multiple Tables.................................................................... 12-18

Control File for Case Study 5.................................................................................................. 12-19

Datafile for Case Study 5......................................................................................................... 12-20

Running Case Study 5.............................................................................................................. 12-20

Log File for Case Study 5......................................................................................................... 12-21

Loaded Tables for Case Study 5 ............................................................................................. 12-23

Case Study 6: Loading Data Using the Direct Path Load Method ........................................ 12-24

Control File for Case Study 6.................................................................................................. 12-25

Datafile for Case Study 6......................................................................................................... 12-25

Running Case Study 6.............................................................................................................. 12-26

Log File for Case Study 6......................................................................................................... 12-26

Case Study 7: Extracting Data from a Formatted Report......................................................... 12-28

Creating a BEFORE INSERT Trigger..................................................................................... 12-28

Control File for Case Study 7 .................................................................................................. 12-29

Datafile for Case Study 7 ......................................................................................................... 12-31

Running Case Study 7.............................................................................................................. 12-31

Log File for Case Study 7......................................................................................................... 12-32

Case Study 8: Loading Partitioned Tables.................................................................................. 12-34

Control File for Case Study 8 .................................................................................................. 12-34

Table Creation ........................................................................................................................... 12-35

Datafile for Case Study 8 ......................................................................................................... 12-35

Running Case Study 8.............................................................................................................. 12-36

Log File for Case Study 8......................................................................................................... 12-37

Case Study 9: Loading LOBFILEs (CLOBs) ............................................................................... 12-38

Control File for Case Study 9 .................................................................................................. 12-39

Datafiles for Case Study 9........................................................................................................ 12-39

Running Case Study 9.............................................................................................................. 12-41

Log File for Case Study 9......................................................................................................... 12-42

Case Study 10: Loading REF Fields and VARRAYs.................................................................. 12-43

Control File for Case Study 10 ................................................................................................ 12-43

Running Case Study 10............................................................................................................ 12-45

Log File for Case Study 10....................................................................................................... 12-45

Case Study 11: Loading Data in the Unicode Character Set ................................................... 12-47

Control File for Case Study 11 ................................................................................................ 12-48

Datafile for Case Study 11 ....................................................................................................... 12-49

Running Case Study 11............................................................................................................ 12-49

Log File for Case Study 11....................................................................................................... 12-50

Loaded Tables for Case Study 11 ........................................................................................... 12-51

Volume 2

Part III External Tables

xxi

13 External Tables Concepts

How Are External Tables Created?................................................................................................ 13-2

Access Parameters ...................................................................................................................... 13-3

Location of Datafiles and Output Files.................................................................................... 13-3

Example: Creating and Loading an External Table Using ORACLE_LOADER............... 13-4

Using External Tables to Load and Unload Data........................................................................ 13-6

Loading Data............................................................................................................................... 13-6

Unloading Data Using the ORACLE_DATAPUMP Access Driver.................................... 13-6

Dealing with Column Objects................................................................................................... 13-7

Datatype Conversion During External Table Use ...................................................................... 13-7

Parallel Access to External Tables.................................................................................................. 13-9

Parallel Access with ORACLE_LOADER............................................................................... 13-9

Parallel Access with ORACLE_DATAPUMP ........................................................................ 13-9

Performance Hints When Using External Tables ..................................................................... 13-10

Performance Hints Specific to the ORACLE_LOADER Access Driver............................ 13-10

External Table Restrictions ........................................................................................................... 13-11

Restrictions Specific to the ORACLE_DATAPUMP Access Driver.................................. 13-12

Behavior Differences Between SQL*Loader and External Tables ........................................ 13-12

Multiple Primary Input Datafiles........................................................................................... 13-13

Syntax and Datatypes .............................................................................................................. 13-13

Byte-Order Marks..................................................................................................................... 13-13

Default Character Sets and Date Masks................................................................................ 13-13

Use of the Backslash Escape Character ................................................................................. 13-13

14 The ORACLE_LOADER Access Driver

access_parameters Clause................................................................................................................ 14-2

record_format_info Clause.............................................................................................................. 14-3

FIXED length............................................................................................................................... 14-4

VARIABLE size........................................................................................................................... 14-5

DELIMITED BY........................................................................................................................... 14-6

CHARACTERSET....................................................................................................................... 14-7

DATA IS...ENDIAN ................................................................................................................... 14-7

BYTEORDERMARK (CHECK | NOCHECK) ....................................................................... 14-8

STRING SIZES ARE IN.............................................................................................................. 14-8

LOAD WHEN ............................................................................................................................. 14-9

xxii

BADFILE | NOBADFILE .......................................................................................................... 14-9

DISCARDFILE | NODISCARDFILE..................................................................................... 14-10

LOG FILE | NOLOGFILE ....................................................................................................... 14-10

SKIP ............................................................................................................................................ 14-10

READSIZE ................................................................................................................................. 14-10

DATE_CACHE.......................................................................................................................... 14-11

string........................................................................................................................................... 14-11

condition_spec........................................................................................................................... 14-12

[directory object name:] filename........................................................................................... 14-13

condition .................................................................................................................................... 14-13

range start : range end ...................................................................................................... 14-14

ﬁeld_deﬁnitions Clause................................................................................................................. 14-15

delim_spec ................................................................................................................................. 14-16

Example: External Table with Terminating Delimiters ............................................... 14-18

Example: External Table with Enclosure and Terminator Delimiters....................... 14-18

Example: External Table with Optional Enclosure Delimiters ................................... 14-19

trim_spec.................................................................................................................................... 14-19

MISSING FIELD VALUES ARE NULL................................................................................. 14-20

field_list...................................................................................................................................... 14-21

pos_spec Clause ........................................................................................................................ 14-22

start ...................................................................................................................................... 14-23

*............................................................................................................................................. 14-23

increment ............................................................................................................................ 14-23

end ....................................................................................................................................... 14-23

length................................................................................................................................... 14-23

datatype_spec Clause............................................................................................................... 14-24

[UNSIGNED] INTEGER [EXTERNAL] [(len)].............................................................. 14-26

DECIMAL [EXTERNAL] and ZONED [EXTERNAL]................................................. 14-26

ORACLE_DATE ................................................................................................................ 14-26

ORACLE_NUMBER.......................................................................................................... 14-27

Floating-Point Numbers................................................................................................... 14-27

DOUBLE ............................................................................................................................. 14-27

FLOAT [EXTERNAL]........................................................................................................ 14-27

BINARY_DOUBLE............................................................................................................ 14-28

BINARY_FLOAT............................................................................................................... 14-28

xxiii

RAW.................................................................................................................................... 14-28

CHAR.................................................................................................................................. 14-28

date_format_spec............................................................................................................... 14-29

VARCHAR and VARRAW.............................................................................................. 14-30

VARCHARC and VARRAWC ........................................................................................ 14-31

init_spec Clause ........................................................................................................................ 14-32

column_transforms Clause ........................................................................................................... 14-33

transform.................................................................................................................................... 14-33

column_name..................................................................................................................... 14-34

NULL................................................................................................................................... 14-34

CONSTANT ...................................................................................................................... 14-34

CONCAT ............................................................................................................................ 14-34

LOBFILE ............................................................................................................................. 14-34

lobfile_attr_list ................................................................................................................... 14-35

Reserved Words for the ORACLE_LOADER Access Driver.................................................. 14-36

15 The ORACLE_DATAPUMP Access Driver

access_parameters Clause................................................................................................................ 15-2

comments..................................................................................................................................... 15-2

LOGFILE | NOLOGFILE.......................................................................................................... 15-2

Filenames for LOGFILE...................................................................................................... 15-3

Example of LOGFILE Usage for ORACLE_DATAPUMP............................................. 15-3

VERSION Clause........................................................................................................................ 15-3

Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver................... 15-4

Parallel Loading and Unloading .............................................................................................. 15-8

Combining Dump Files.............................................................................................................. 15-9

Supported Datatypes...................................................................................................................... 15-10

Unsupported Datatypes................................................................................................................. 15-11

Unloading and Loading BFILE Datatypes............................................................................ 15-11

Unloading LONG and LONG RAW Datatypes................................................................... 15-14

Unloading and Loading Columns Containing Final Object Types................................... 15-15

Tables of Final Object Types ................................................................................................... 15-16

Reserved Words for the ORACLE_DATAPUMP Access Driver............................................ 15-18

Part IV Other Utilities

xxiv

16 DBVERIFY: Offline Database Verification Utility

Using DBVERIFY to Validate Disk Blocks of a Single Dataﬁle.............................................. 16-1

Syntax ........................................................................................................................................... 16-2

Parameters ................................................................................................................................... 16-2

Command-Line Interface........................................................................................................... 16-3

Sample DBVERIFY Output ....................................................................................................... 16-3

Using DBVERIFY to Validate a Segment..................................................................................... 16-4

Syntax ........................................................................................................................................... 16-5

Parameters ................................................................................................................................... 16-5

Command-Line Interface........................................................................................................... 16-6

17 DBNEWID Utility

What Is the DBNEWID Utility? ..................................................................................................... 17-1

Ramiﬁcations of Changing the DBID and DBNAME ............................................................... 17-2

Considerations for Global Database Names........................................................................... 17-2

Changing the DBID and DBNAME of a Database .................................................................... 17-3

Changing the DBID and Database Name................................................................................ 17-3

Changing Only the Database ID............................................................................................... 17-6

Changing Only the Database Name ........................................................................................ 17-7

Troubleshooting DBNEWID ..................................................................................................... 17-9

DBNEWID Syntax .......................................................................................................................... 17-10

Parameters ................................................................................................................................. 17-11

Restrictions and Usage Notes ................................................................................................. 17-12

Additional Restrictions for Releases Prior to Oracle Database 10g................................... 17-13

18 Using the Metadata API

Why Use the Metadata API?........................................................................................................... 18-1

Overview of the Metadata API....................................................................................................... 18-2

Using the Metadata API to Retrieve an Object's Metadata ...................................................... 18-3

Typical Steps Used for Basic Metadata Retrieval................................................................... 18-3

Retrieving Multiple Objects ...................................................................................................... 18-5

Placing Conditions on Transforms........................................................................................... 18-7

Accessing Specific Metadata Attributes ................................................................................ 18-10

Using the Metadata API to Re-Create a Retrieved Object...................................................... 18-12

xxv

Retrieving Collections of Different Object Types.................................................................... 18-15

Filtering the Return of Heterogeneous Object Types.......................................................... 18-17

Performance Tips for the Programmatic Interface of the Metadata API.............................. 18-19

Example Usage of the Metadata API........................................................................................... 18-19

What Does the Metadata API Example Do?......................................................................... 18-21

Output Generated from the GET_PAYROLL_TABLES Procedure ................................. 18-23

Summary of DBMS_METADATA Procedures.......................................................................... 18-25

19 Using LogMiner to Analyze Redo Log Files

LogMiner Beneﬁts ............................................................................................................................ 19-2

Introduction to LogMiner ............................................................................................................... 19-3

LogMiner Configuration ........................................................................................................... 19-3

Sample Configuration......................................................................................................... 19-4

Requirements ....................................................................................................................... 19-4

Directing LogMiner Operations and Retrieving Data of Interest........................................ 19-6

LogMiner Dictionary Files and Redo Log Files.......................................................................... 19-7

LogMiner Dictionary Options .................................................................................................. 19-7

Using the Online Catalog................................................................................................... 19-8

Extracting a LogMiner Dictionary to the Redo Log Files.............................................. 19-9

Extracting the LogMiner Dictionary to a Flat File ....................................................... 19-10

Redo Log File Options ............................................................................................................. 19-11

Starting LogMiner .......................................................................................................................... 19-13

Querying V$LOGMNR_CONTENTS for Redo Data of Interest.......................................... 19-14

How the V$LOGMNR_CONTENTS View Is Populated.................................................... 19-16

Querying V$LOGMNR_CONTENTS Based on Column Values ...................................... 19-17

The Meaning of NULL Values Returned by the MINE_VALUE Function .............. 19-18

Usage Rules for the MINE_VALUE and COLUMN_PRESENT Functions.............. 19-18

Filtering and Formatting Data Returned to V$LOGMNR_CONTENTS............................. 19-19

Showing Only Committed Transactions............................................................................... 19-19

Skipping Redo Corruptions .................................................................................................... 19-22

Filtering Data by Time............................................................................................................. 19-23

Filtering Data by SCN.............................................................................................................. 19-24

Formatting Reconstructed SQL Statements for Reexecution............................................. 19-24

Formatting the Appearance of Returned Data for Readability ......................................... 19-25

Reapplying DDL Statements Returned to V$LOGMNR_CONTENTS .............................. 19-26

xxvi

Calling DBMS_LOGMNR.START_LOGMNR Multiple Times ........................................... 19-26

Supplemental Logging................................................................................................................... 19-28

Database-Level Supplemental Logging................................................................................. 19-29

Minimal Supplemental Logging...................................................................................... 19-29

Database-Level Identification Key Logging .................................................................. 19-30

Disabling Database-Level Supplemental Logging............................................................... 19-32

Table-Level Supplemental Logging....................................................................................... 19-32

Table-Level Identification Key Logging......................................................................... 19-33

Table-Level User-Defined Supplemental Log Groups................................................. 19-33

Usage Notes for User-Defined Supplemental Log Groups......................................... 19-35

Tracking DDL Statements in the LogMiner Dictionary...................................................... 19-35

DDL_DICT_TRACKING and Supplemental Logging Settings......................................... 19-37

DDL_DICT_TRACKING and Specified Time or SCN Ranges ......................................... 19-38

Accessing LogMiner Operational Information in Views........................................................ 19-39

Querying V$LOGMNR_LOGS ............................................................................................... 19-40

Querying Views for Supplemental Logging Settings.......................................................... 19-41

Steps in a Typical LogMiner Session.......................................................................................... 19-43

Enable Supplemental Logging................................................................................................ 19-44

Extract a LogMiner Dictionary ............................................................................................... 19-44

Specify Redo Log Files for Analysis....................................................................................... 19-45

Start LogMiner .......................................................................................................................... 19-46

Query V$LOGMNR_CONTENTS.......................................................................................... 19-47

End the LogMiner Session....................................................................................................... 19-48

Examples Using LogMiner............................................................................................................ 19-48

Examples of Mining by Explicitly Specifying the Redo Log Files of Interest.................. 19-49

Example 1: Finding All Modifications in the Last Archived Redo Log File............. 19-49

Example 2: Grouping DML Statements into Committed Transactions .................... 19-52

Example 3: Formatting the Reconstructed SQL ............................................................ 19-55

Example 4: Using the LogMiner Dictionary in the Redo Log Files............................ 19-58

Example 5: Tracking DDL Statements in the Internal Dictionary.............................. 19-69

Example 6: Filtering Output by Time Range................................................................. 19-73

Examples of Mining Without Specifying the List of Redo Log Files Explicitly .............. 19-76

Example 1: Mining Redo Log Files in a Given Time Range........................................ 19-76

Example 2: Mining the Redo Log Files in a Given SCN Range .................................. 19-79

Example 3: Using Continuous Mining to Include Future Values in a Query .......... 19-81

xxvii

Example Scenarios.................................................................................................................... 19-82

Scenario 1: Using LogMiner to Track Changes Made by a Specific User ................. 19-82

Scenario 2: Using LogMiner to Calculate Table Access Statistics .............................. 19-84

Supported Datatypes, Storage Attributes, and Database and Redo Log File Versions .... 19-85

Supported Datatypes and Table Storage Attributes............................................................ 19-85

Unsupported Datatypes and Table Storage Attributes....................................................... 19-86

Supported Databases and Redo Log File Versions.............................................................. 19-86

20 Original Export and Import

What Are the Export and Import Utilities?.................................................................................. 20-3

Before Using Export and Import.................................................................................................... 20-3

Running catexp.sql or catalog.sql............................................................................................. 20-4

Ensuring Sufficient Disk Space for Export Operations......................................................... 20-4

Verifying Access Privileges for Export and Import Operations.......................................... 20-5

Invoking Export and Import........................................................................................................... 20-5

Invoking Export and Import As SYSDBA............................................................................... 20-6

Command-Line Entries.............................................................................................................. 20-6

Parameter Files............................................................................................................................ 20-7

Interactive Mode......................................................................................................................... 20-8

Restrictions When Using Export's Interactive Method.................................................. 20-8

Getting Online Help................................................................................................................... 20-9

Importing Objects into Your Own Schema ................................................................................. 20-9

Importing Grants...................................................................................................................... 20-10

Importing Objects into Other Schemas ................................................................................. 20-11

Importing System Objects ....................................................................................................... 20-11

Processing Restrictions ............................................................................................................ 20-11

Table Objects: Order of Import.................................................................................................... 20-12

Importing into Existing Tables..................................................................................................... 20-13

Manually Creating Tables Before Importing Data .............................................................. 20-13

Disabling Referential Constraints .......................................................................................... 20-14

Manually Ordering the Import............................................................................................... 20-14

Effect of Schema and Database Triggers on Import Operations ........................................... 20-14

Export and Import Modes ............................................................................................................. 20-15

Table-Level and Partition-Level Export................................................................................ 20-19

Table-Level Export............................................................................................................ 20-20

xxviii

Partition-Level Export....................................................................................................... 20-20

Table-Level and Partition-Level Import................................................................................ 20-20

Guidelines for Using Table-Level Import...................................................................... 20-20

Guidelines for Using Partition-Level Import ................................................................ 20-21

Migrating Data Across Partitions and Tables................................................................ 20-22

Export Parameters .......................................................................................................................... 20-22

BUFFER ...................................................................................................................................... 20-22

Example: Calculating Buffer Size.................................................................................... 20-23

COMPRESS................................................................................................................................ 20-23

CONSISTENT............................................................................................................................ 20-24

CONSTRAINTS ........................................................................................................................ 20-26

DIRECT ...................................................................................................................................... 20-26

FEEDBACK................................................................................................................................ 20-26

FILE............................................................................................................................................. 20-26

FILESIZE .................................................................................................................................... 20-27

FLASHBACK_SCN................................................................................................................... 20-28

FLASHBACK_TIME................................................................................................................. 20-28

FULL........................................................................................................................................... 20-29

Points to Consider for Full Database Exports and Imports......................................... 20-29

GRANTS..................................................................................................................................... 20-30

HELP........................................................................................................................................... 20-31

INDEXES.................................................................................................................................... 20-31

LOG............................................................................................................................................. 20-31

OBJECT_CONSISTENT........................................................................................................... 20-31

OWNER...................................................................................................................................... 20-31

PARFILE..................................................................................................................................... 20-32

QUERY ....................................................................................................................................... 20-32

Restrictions When Using the QUERY Parameter ......................................................... 20-33

RECORDLENGTH ................................................................................................................... 20-33

RESUMABLE............................................................................................................................. 20-34

RESUMABLE_NAME.............................................................................................................. 20-34

RESUMABLE_TIMEOUT........................................................................................................ 20-34

ROWS ......................................................................................................................................... 20-35

STATISTICS............................................................................................................................... 20-35

TABLES ...................................................................................................................................... 20-35

xxix

Table Name Restrictions................................................................................................... 20-36

TABLESPACES ......................................................................................................................... 20-37

TRANSPORT_TABLESPACE................................................................................................. 20-38

TRIGGERS ................................................................................................................................. 20-38

TTS_FULL_CHECK.................................................................................................................. 20-38

USERID (username/password).............................................................................................. 20-38

VOLSIZE.................................................................................................................................... 20-39

Import Parameters .......................................................................................................................... 20-39

BUFFER...................................................................................................................................... 20-39

COMMIT.................................................................................................................................... 20-40

COMPILE................................................................................................................................... 20-40

CONSTRAINTS ........................................................................................................................ 20-41

DATAFILES............................................................................................................................... 20-41

DESTROY................................................................................................................................... 20-41

FEEDBACK................................................................................................................................ 20-42

FILE............................................................................................................................................. 20-42

FILESIZE.................................................................................................................................... 20-42

FROMUSER............................................................................................................................... 20-43

FULL........................................................................................................................................... 20-44

GRANTS .................................................................................................................................... 20-44

HELP .......................................................................................................................................... 20-44

IGNORE..................................................................................................................................... 20-44

INDEXES.................................................................................................................................... 20-45

INDEXFILE................................................................................................................................ 20-45

LOG ............................................................................................................................................ 20-46

PARFILE .................................................................................................................................... 20-46

RECORDLENGTH ................................................................................................................... 20-46

RESUMABLE............................................................................................................................. 20-47

RESUMABLE_NAME.............................................................................................................. 20-47

RESUMABLE_TIMEOUT........................................................................................................ 20-47

ROWS ......................................................................................................................................... 20-48

SHOW......................................................................................................................................... 20-48

SKIP_UNUSABLE_INDEXES................................................................................................. 20-48

STATISTICS............................................................................................................................... 20-49

STREAMS_CONFIGURATION ............................................................................................. 20-50

xxx

STREAMS_INSTANTIATION................................................................................................ 20-50

TABLES ...................................................................................................................................... 20-50

Table Name Restrictions................................................................................................... 20-52

TABLESPACES ......................................................................................................................... 20-53

TOID_NOVALIDATE.............................................................................................................. 20-53

TOUSER ..................................................................................................................................... 20-54

TRANSPORT_TABLESPACE................................................................................................. 20-55

TTS_OWNERS........................................................................................................................... 20-55

USERID (username/password).............................................................................................. 20-55

VOLSIZE .................................................................................................................................... 20-56

Example Export Sessions............................................................................................................... 20-56

Example Export Session in Full Database Mode.................................................................. 20-57

Example Export Session in User Mode.................................................................................. 20-57

Example Export Sessions in Table Mode .............................................................................. 20-58

Example 1: DBA Exporting Tables for Two Users........................................................ 20-59

Example 2: User Exports Tables That He Owns ........................................................... 20-59

Example 3: Using Pattern Matching to Export Various Tables .................................. 20-60

Example Export Session Using Partition-Level Export....................................................... 20-61

Example 1: Exporting a Table Without Specifying a Partition ................................... 20-61

Example 2: Exporting a Table with a Specified Partition ............................................ 20-62

Example 3: Exporting a Composite Partition ................................................................ 20-62

Example Import Sessions ............................................................................................................. 20-63

Example Import of Selected Tables for a Specific User....................................................... 20-64

Example Import of Tables Exported by Another User........................................................ 20-64

Example Import of Tables from One User to Another........................................................ 20-65

Example Import Session Using Partition-Level Import...................................................... 20-66

Example 1: A Partition-Level Import.............................................................................. 20-66

Example 2: A Partition-Level Import of a Composite Partitioned Table ................. 20-67

Example 3: Repartitioning a Table on a Different Column......................................... 20-69

Example Import Using Pattern Matching to Import Various Tables................................ 20-71

Using Export and Import to Move a Database Between Platforms....................................... 20-72

Warning, Error, and Completion Messages ............................................................................... 20-73

Log File ...................................................................................................................................... 20-73

Warning Messages.................................................................................................................... 20-73

Nonrecoverable Error Messages............................................................................................. 20-73

xxxi

Completion Messages.............................................................................................................. 20-73

Exit Codes for Inspection and Display....................................................................................... 20-74

Network Considerations ............................................................................................................... 20-74

Transporting Export Files Across a Network....................................................................... 20-74

Exporting and Importing with Oracle Net ........................................................................... 20-75

Character Set and Globalization Support Considerations..................................................... 20-75

User Data ................................................................................................................................... 20-75

Effect of Character Set Sorting Order on Conversions ................................................ 20-75

Data Definition Language (DDL)........................................................................................... 20-76

Single-Byte Character Sets and Export and Import............................................................. 20-77

Multibyte Character Sets and Export and Import .............................................................. 20-77

Materialized Views and Snapshots............................................................................................. 20-77

Snapshot Log............................................................................................................................. 20-78

Snapshots................................................................................................................................... 20-78

Importing a Snapshot ....................................................................................................... 20-78

Importing a Snapshot into a Different Schema............................................................. 20-79

Transportable Tablespaces............................................................................................................ 20-79

Read-Only Tablespaces.................................................................................................................. 20-80

Dropping a Tablespace .................................................................................................................. 20-80

Reorganizing Tablespaces............................................................................................................. 20-80

Support for Fine-Grained Access Control ................................................................................. 20-81

Using Instance Afﬁnity with Export and Import...................................................................... 20-82

Reducing Database Fragmentation ............................................................................................. 20-82

Using Storage Parameters with Export and Import ................................................................. 20-82

The OPTIMAL Parameter ....................................................................................................... 20-83

Storage Parameters for OID Indexes and LOB Columns.................................................... 20-83

Overriding Storage Parameters.............................................................................................. 20-83

The Export COMPRESS Parameter........................................................................................ 20-83

Information Speciﬁc to Export ..................................................................................................... 20-84

Conventional Path Export Versus Direct Path Export........................................................ 20-84

Invoking a Direct Path Export ................................................................................................ 20-84

Security Considerations for Direct Path Exports.......................................................... 20-85

Performance Considerations for Direct Path Exports.................................................. 20-85

Restrictions for Direct Path Exports ............................................................................... 20-86

Exporting from a Read-Only Database ................................................................................. 20-86

xxxii

Considerations When Exporting Database Objects............................................................. 20-87

Exporting Sequences......................................................................................................... 20-87

Exporting LONG and LOB Datatypes............................................................................ 20-87

Exporting Foreign Function Libraries ............................................................................ 20-87

Exporting Offline Locally Managed Tablespaces......................................................... 20-87

Exporting Directory Aliases............................................................................................. 20-88

Exporting BFILE Columns and Attributes..................................................................... 20-88

Exporting External Tables................................................................................................ 20-88

Exporting Object Type Definitions ................................................................................. 20-88

Exporting Nested Tables .................................................................................................. 20-89

Exporting Advanced Queue (AQ) Tables...................................................................... 20-89

Exporting Synonyms......................................................................................................... 20-89

Possible Export Errors Related to Java Synonyms ....................................................... 20-90

Information Speciﬁc to Import..................................................................................................... 20-90

Error Handling During an Import Operation ...................................................................... 20-90

Row Errors.......................................................................................................................... 20-90

Errors Importing Database Objects................................................................................. 20-91

Controlling Index Creation and Maintenance...................................................................... 20-92

Delaying Index Creation................................................................................................... 20-92

Index Creation and Maintenance Controls ................................................................... 20-93

Importing Statistics................................................................................................................... 20-94

Tuning Considerations for Import Operations .................................................................... 20-95

Changing System-Level Options..................................................................................... 20-95

Changing Initialization Parameters................................................................................ 20-96

Changing Import Options................................................................................................ 20-97

Dealing with Large Amounts of LOB Data ................................................................... 20-97

Dealing with Large Amounts of LONG Data ............................................................... 20-97

Considerations When Importing Database Objects............................................................. 20-98

Importing Object Identifiers............................................................................................. 20-98

Importing Existing Object Tables and Tables That Contain Object Types ............... 20-99

Importing Nested Tables............................................................................................... 20-100

Importing REF Data ....................................................................................................... 20-101

Importing BFILE Columns and Directory Aliases..................................................... 20-101

Importing Foreign Function Libraries......................................................................... 20-101

Importing Stored Procedures, Functions, and Packages ......................................... 20-101

xxxiii

Importing Java Objects ................................................................................................... 20-102

Importing External Tables.............................................................................................. 20-102

Importing Advanced Queue (AQ) Tables ................................................................... 20-102

Importing LONG Columns............................................................................................ 20-102

Importing LOB Columns When Triggers Are Present .............................................. 20-103

Importing Views.............................................................................................................. 20-103

Importing Partitioned Tables......................................................................................... 20-104

Using Export and Import to Partition a Database Migration............................................... 20-104

Advantages of Partitioning a Migration ............................................................................. 20-104

Disadvantages of Partitioning a Migration......................................................................... 20-105

How to Use Export and Import to Partition a Database Migration ............................... 20-105

Using Different Releases and Versions of Export.................................................................. 20-105

Restrictions When Using Different Releases and Versions of Export and Import ....... 20-106

Examples of Using Different Releases of Export and Import .......................................... 20-106

Creating Oracle Release 8.0 Export Files from an Oracle9i Database............................. 20-107

Part V Appendixes

A SQL*Loader Syntax Diagrams

B Backus-Naur Form Syntax

Index

xxxiv

xxxv

List of Examples

2–1 Performing a Table-Mode Export ..................................................................................... 2-43

2–2 Data-Only Unload of Selected Tables and Rows............................................................ 2-43

2–3 Estimating Disk Space Needed in a Schema-Mode Export........................................... 2-44

2–4 Performing a Schema Mode Export.................................................................................. 2-44

2–5 Parallel Full Export.............................................................................................................. 2-45

2–6 Attaching to a Stopped Job ................................................................................................ 2-45

3–1 Performing a Data-Only Table-Mode Import ................................................................. 3-50

3–2 Performing a Schema-Mode Import................................................................................. 3-50

3–3 Network-Mode Import of Schemas.................................................................................. 3-50

5–1 Performing a Simple Schema Export.................................................................................. 5-5

5–2 Importing a Dump File and Remapping All Schema Objects ........................................ 5-7

5–3 Using Exception Handling During a Simple Schema Export......................................... 5-9

6–1 Loading Data in Fixed Record Format............................................................................... 6-5

6–2 Loading Data in Variable Record Format.......................................................................... 6-6

6–3 Loading Data in Stream Record Format ............................................................................ 6-8

8–1 Sample Control File............................................................................................................... 8-2

8–2 Identifying XML Type Tables in the SQL*Loader Control File...................................... 8-7

8–3 CONTINUEIF THIS Without the PRESERVE Parameter.............................................. 8-30

8–4 CONTINUEIF THIS with the PRESERVE Parameter ................................................... 8-31

8–5 CONTINUEIF NEXT Without the PRESERVE Parameter ........................................... 8-31

8–6 CONTINUEIF NEXT with the PRESERVE Parameter ................................................. 8-32

9–1 Field List Section of Sample Control File........................................................................... 9-2

9–2 DEFAULTIF Clause Is Not Evaluated.............................................................................. 9-36

9–3 DEFAULTIF Clause Is Evaluated ..................................................................................... 9-37

9–4 DEFAULTIF Clause Specifies a Position.......................................................................... 9-37

9–5 DEFAULTIF Clause Specifies a Field Name ................................................................... 9-38

10–1 Loading Column Objects in Stream Record Format ...................................................... 10-2

10–2 Loading Column Objects in Variable Record Format.................................................... 10-3

10–3 Loading Nested Column Objects...................................................................................... 10-4

10–4 Loading Column Objects with a Subtype........................................................................ 10-5

10–5 Specifying Attribute Nulls Using the NULLIF Clause .................................................. 10-6

10–6 Loading Data Using Filler Fields ...................................................................................... 10-7

10–7 Loading a Column Object with Constructors That Match............................................ 10-8

10–8 Loading a Column Object with Constructors That Do Not Match............................ 10-10

10–9 Using SQL to Load Column Objects When Constructors Do Not Match................. 10-11

10–10 Loading an Object Table with Primary Key OIDs........................................................ 10-12

10–11 Loading OIDs..................................................................................................................... 10-13

10–12 Loading an Object Table with a Subtype....................................................................... 10-13

10–13 Loading System-Generated REF Columns.................................................................... 10-15

xxxvi

10–14 Loading Primary Key REF Columns .............................................................................. 10-16

10–15 Loading LOB Data in Predetermined Size Fields ......................................................... 10-19

10–16 Loading LOB Data in Delimited Fields.......................................................................... 10-21

10–17 Loading LOB Data in Length-Value Pair Fields ........................................................... 10-21

10–18 Loading LOB DATA with One LOB per LOBFILE....................................................... 10-23

10–19 Loading LOB Data Using Predetermined Size LOBs................................................... 10-24

10–20 Loading LOB Data Using Delimited LOBs.................................................................... 10-25

10–21 Loading LOB Data Using Length-Value Pair Specified LOBs.................................... 10-26

10–22 Loading Data Using BFILEs: Only Filename Specified Dynamically........................ 10-28

10–23 Loading Data Using BFILEs: Filename and Directory Specified Dynamically........ 10-29

10–24 Loading a VARRAY and a Nested Table....................................................................... 10-30

10–25 Loading a Parent Table with User-Provided SIDs........................................................ 10-34

10–26 Loading a Child Table with User-Provided SIDs ......................................................... 10-34

11–1 Setting the Date Format in the SQL*Loader Control File .............................................. 11-6

11–2 Setting an NLS_DATE_FORMAT Environment Variable............................................. 11-6

18–1 Using the DBMS_METADATA Programmatic Interface to Retrieve Data ................ 18-4

18–2 Using the DBMS_METADATA Browsing Interface to Retrieve Data......................... 18-5

18–3 Retrieving Multiple Objects ............................................................................................... 18-6

18–4 Placing Conditions on Transforms.................................................................................... 18-7

18–5 Modifying an XML Document .......................................................................................... 18-8

18–6 Using Parse Items to Access Specific Metadata Attributes ......................................... 18-10

18–7 Using the Submit Interface to Re-Create a Retrieved Object ...................................... 18-13

18–8 Retrieving Heterogeneous Object Types........................................................................ 18-16

18–9 Filtering the Return of Heterogeneous Object Types................................................... 18-17

xxxvii

List of Figures

6–1 SQL*Loader Overview.......................................................................................................... 6-3

9–1 Example of Field Conversion ........................................................................................... 9-45

9–2 Relative Positioning After a Fixed Field ......................................................................... 9-48

9–3 Relative Positioning After a Delimited Field ................................................................. 9-49

9–4 Relative Positioning After Enclosure Delimiters ........................................................... 9-49

9–5 Fields Terminated by Whitespace..................................................................................... 9-50

9–6 Fields Terminated by Optional Enclosure Delimiters ................................................... 9-50

11–1 Database Writes on SQL*Loader Direct Path and Conventional Path........................ 11-3

19–1 Sample LogMiner Database Configuration..................................................................... 19-4

19–2 Decision Tree for Choosing a LogMiner Dictionary ...................................................... 19-8

xxxviii

xxxix

List of Tables

1–1 DBA_DATAPUMP_JOBS View and USER_DATAPUMP_JOBS View...................... 1-10

1–2 The DBA_DATAPUMP_SESSIONS View...................................................................... 1-11

2–1 Original Export Parameters and Their Counterparts in Data Pump Export............. 2-35

2–2 Supported Activities in Data Pump Export's Interactive-Command Mode.............. 2-37

3–1 Original Import Parameters and Their Counterparts in Data Pump Import............ 3-42

3–2 Supported Activities in Data Pump Import's Interactive-Command Mode ............. 3-45

5–1 Valid Job States in Which DBMS_DATAPUMP Procedures Can Be Executed .......... 5-3

7–1 Exit Codes for SQL*Loader............................................................................................... 7-17

8–1 Parameters for the INFILE Keyword................................................................................. 8-9

8–2 Parameters for the CONTINUEIF Clause....................................................................... 8-29

8–3 Fixed-Length Fields............................................................................................................ 8-49

8–4 Nongraphic Fields............................................................................................................... 8-49

8–5 Graphic Fields..................................................................................................................... 8-49

8–6 Variable-Length Fields ...................................................................................................... 8-50

9–1 Parameters for the Position Specification Clause ............................................................ 9-3

9–2 Datatype Conversions for Datetime and Interval Datatypes....................................... 9-25

9–3 Parameters Used for Specifying Delimiters.................................................................... 9-27

9–4 Parameters for the Field Condition Clause .................................................................... 9-32

9–5 Behavior Summary for Trimming Whitespace.............................................................. 9-46

9–6 Parameters Used for Column Specification.................................................................... 9-61

12–1 Case Studies and Their Related Files............................................................................... 12-3

17–1 Parameters for the DBNEWID Utility........................................................................... 17-11

18–1 DBMS_METADATA Procedures Used for Retrieving Multiple Objects................. 18-26

18–2 DBMS_METADATA Procedures Used for the Browsing Interface.......................... 18-27

18–3 DBMS_METADATA Procedures and Functions for Submitting XML Data........... 18-27

20–1 Privileges Required to Import Objects into Your Own Schema.................................. 20-9

20–2 Privileges Required to Import Grants ........................................................................... 20-10

20–3 Objects Exported and Imported in Each Mode............................................................ 20-16

20–4 Sequence of Events During Updates by Two Users.................................................... 20-24

20–5 Maximum Size for Dump Files....................................................................................... 20-28

20–6 Exit Codes for Export and Import.................................................................................. 20-74

20–7 Using Different Releases of Export and Import......................................................... 20-107

B–1 Symbols and Conventions for Backus-Naur Form Syntax............................................. B-1

xli

Send Us Your Comments

Oracle Database Utilities, 10g Release 1 (10.1)

Part No. B10825-01

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this

publication. Your input is an important part of the information used for revision.

■ Did you ﬁnd any errors?

■ Is the information clearly presented?

■ Do you need more information? If so, where?

■ Are the examples correct? Do you need more examples?

■ What features did you like most about this manual?

If you ﬁnd any errors or have any other suggestions for improvement, please indicate the title and

part number of the documentation and the chapter, section, and page number (if available). You can

send comments to us in the following ways:

■ Electronic mail: [email protected]

■ FAX: 603-897-3825 Attn: Oracle Database Utilities Documentation

■ Postal service:

Oracle Corporation

Oracle Database Utilities Documentation

One Oracle Drive

Nashua, NH 03062-2804

USA

If you would like a reply, please give your name, address, telephone number, and electronic mail

address (optional).

If you have problems with the software, please contact your local Oracle Support Services.

xlii

xliii

Preface

This document describes how to use the Oracle Database utilities for data transfer,

data maintenance, and database administration. The preface contains these topics:

■ Audience

■ Documentation Accessibility

■ Organization

■ Related Documentation

■ Conventions

Audience

Oracle Database Utilities is intended for database administrators (DBAs), application

programmers, security administrators, system operators, and other Oracle users

who perform the following tasks:

■ Archive data, back up an Oracle database, or move data between Oracle

databases using the Export and Import utilities (both the original versions and

the Data Pump versions)

■ Load data into Oracle tables from operating system ﬁles using SQL*Loader or

from external sources using the external tables feature

■ Perform a physical data structure integrity check on an ofﬂine database, using

the DBVERIFY utility

■ Maintain the internal database identiﬁer (DBID) and the database name

(DBNAME) for an operational database, using the DBNEWID utility

xliv

■ Extract and manipulate complete representations of the metadata for database

objects, using the Metadata API

■ Query and analyze redo log ﬁles (through a SQL interface) using the LogMiner

utility

To use this manual, you need a working knowledge of SQL and Oracle

fundamentals, information that is contained in Oracle Database Concepts. In addition,

SQL*Loader requires that you know how to use the ﬁle management facilities of

your operating system.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation

accessible, with good usability, to the disabled community. To that end, our

documentation includes features that make information available to users of

assistive technology. This documentation is available in HTML format, and contains

markup to facilitate access by the disabled community. Standards will continue to

evolve over time, and Oracle is actively engaged with other market-leading

technology vendors to address technical obstacles so that our documentation can be

accessible to all of our customers. For additional information, visit the Oracle

Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation JAWS, a Windows screen

reader, may not always correctly read the code examples in this document. The

conventions for writing code require that closing braces should appear on an

otherwise empty line; however, JAWS may not always read a line of text that

consists solely of a bracket or brace.

Organization

This document contains:

Part I, "Oracle Data Pump"

Chapter 1, "Overview of Oracle Data Pump"

This chapter provides an overview of Oracle Data Pump technology, which enables

very high-speed movement of data and metadata from one database to another.

xlv

Chapter 2, "Data Pump Export"

This chapter describes the Oracle Data Pump Export utility, which is used to unload

data and metadata into a set of operating system ﬁles called a dump ﬁle set.

Chapter 3, "Data Pump Import"

This chapter describes the Oracle Data Pump Import utility, which is used to load

an export dump ﬁle set into a target system. It also describes how to perform a

network import to load a target database directly from a source database with no

intervening ﬁles.

Chapter 4, "Data Pump Performance"

This chapter discusses why the performance of Data Pump Export and Import is

better than that of original Export and Import. It also suggests speciﬁc steps you can

take to enhance performance of export and import operations.

Chapter 5, "The Data Pump API"

This chapter describes how the Data Pump API, DBMS_DATAPUMP, works.

Part II, "SQL*Loader"

Chapter 6, "SQL*Loader Concepts"

This chapter introduces SQL*Loader and describes its features. It also introduces

data loading concepts (including object support). It discusses input to SQL*Loader,

database preparation, and output from SQL*Loader.

Chapter 7, "SQL*Loader Command-Line Reference"

This chapter describes the command-line syntax used by SQL*Loader. It discusses

invoking SQL*Loader, command-line arguments, and exit codes.

Chapter 8, "SQL*Loader Control File Reference"

This chapter describes the control ﬁle syntax you use to conﬁgure SQL*Loader and

to describe to SQL*Loader how to map your data to Oracle format. It provides

detailed syntax diagrams and information about specifying dataﬁles, tables and

columns, the location of data, the type and format of data to be loaded, and more.

xlvi

Chapter 9, "Field List Reference"

This chapter describes the ﬁeld list section of a SQL*Loader control ﬁle. The ﬁeld list

provides information about ﬁelds being loaded, such as position, datatype,

conditions, and delimiters.

Chapter 10, "Loading Objects, LOBs, and Collections"

This chapter describes how to load column objects in various formats. It also

discusses how to load object tables, REF columns, LOBs, and collections.

Chapter 11, "Conventional and Direct Path Loads"

This chapter describes the differences between a conventional path load and a direct

path load. A direct path load is a high-performance option that signiﬁcantly reduces

the time required to load large quantities of data.

Chapter 12, "SQL*Loader Case Studies"

This chapter presents case studies that illustrate some of the features of

SQL*Loader. Some of the features demonstrated are the loading of variable-length

data, ﬁxed-format records, free-format ﬁles, multiple physical records as one logical

record, and multiple tables, as well as performing direct path loads, and loading

objects, collections, and REF columns.

Part III, "External Tables"

Chapter 13, "External Tables Concepts"

This chapter describes basic concepts about external tables.

Chapter 14, "The ORACLE_LOADER Access Driver"

This chapter describes the access parameters used to interface with the ORACLE_

LOADER access driver.

Chapter 15, "The ORACLE_DATAPUMP Access Driver"

This chapter describes the ORACLE_DATAPUMP access driver, including its

parameters, and information about loading and unloading supported datatypes.

xlvii

Part IV, "Other Utilities"

Chapter 16, "DBVERIFY: Ofﬂine Database Veriﬁcation Utility"

This chapter describes how to use the ofﬂine database veriﬁcation utility,

DBVERIFY.

Chapter 17, "DBNEWID Utility"

This chapter describes how to use the DBNEWID utility to change the name or ID,

or both, for a database.

Chapter 18, "Using the Metadata API"

This chapter describes the Metadata API, which you can use to extract and

manipulate complete representations of the metadata for database objects.

Chapter 19, "Using LogMiner to Analyze Redo Log Files"

This chapter describes the LogMiner utility, which you can use to query redo logs

through a SQL interface.

Chapter 20, "Original Export and Import"

This chapter describes how to use the original Export and Import utilities to write

data from an Oracle database into transportable ﬁles, and then to read that data into

an Oracle database. It discusses guidelines, export and import modes, and available

parameters. It also provides examples of various types of export and import

operations.

Part V, "Appendixes"

Appendix A, "SQL*Loader Syntax Diagrams"

This appendix provides diagrams of the SQL*Loader syntax.

Appendix B, "Backus-Naur Form Syntax"

This appendix explains the symbols and conventions of the variant of Backus-Naur

Form (BNF) used in text descriptions of syntax diagrams.

See Also:

■ NETWORK_LINK on page 2-23 for information about

performing exports over the network

■ NETWORK_LINK on page 3-22 for information about

performing imports over the network

What Happens During Execution of a Data Pump Job?

Overview of Oracle Data Pump 1-7

What Happens During Execution of a Data Pump Job?

Data Pump jobs use a master table, a master process, and worker processes to

perform the work and keep track of progress.

Coordination of a Job

For every Data Pump Export job and Data Pump Import job, a master process is

created. The master process controls the entire job, including communicating with

the clients, creating and controlling a pool of worker processes, and performing

logging operations.

Tracking Progress Within a Job

While the data and metadata are being transferred, a master table is used to track

the progress within a job. The master table is implemented as a user table within the

database. The speciﬁc function of the master table for export and import jobs is as

follows:

■ For export jobs, the master table records the location of database objects within

a dump ﬁle set. Export builds and maintains the master table for the duration of

the job. At the end of an export job, the content of the master table is written to

a ﬁle in the dump ﬁle set.

■ For import jobs, the master table is loaded from the dump ﬁle set and is used to

control the sequence of operations for locating objects that need to be imported

into the target database.

The master table is created in the schema of the current user performing the export

or import operation. Therefore, that user must have sufﬁcient tablespace quota for

its creation. The name of the master table is the same as the name of the job that

created it. Therefore, you cannot explicitly give a Data Pump job the same name as a

preexisting table or view.

For all operations, the information in the master table is used to restart a job.

The master table is either retained or dropped, depending on the circumstances, as

follows:

■ Upon successful job completion, the master table is dropped.

■ If a job is stopped using the STOP_JOB interactive command, the master table is

retained for use in restarting the job.

What Happens During Execution of a Data Pump Job?

1-8 Oracle Database Utilities

■ If a job is killed using the KILL_JOB interactive command, the master table is

dropped and the job cannot be restarted.

■ If a job terminates unexpectedly, the master table is retained. You can delete it if

you do not intend to restart the job.

Filtering Data During a Job

Within the master table, speciﬁc objects are assigned attributes such as name or

owning schema. Objects also belong to a class of objects (such as TABLE, INDEX, or

DIRECTORY). The class of an object is called its object type. You can use the

EXCLUDE and INCLUDE parameters to restrict the types of objects that are exported

and imported. The objects can be based upon the name of the object or the name of

the schema that owns the object.

Transforming Metadata During a Job

When you are moving data from one database to another, it is often useful to

perform transformations on the metadata for remapping storage between

tablespaces or redeﬁning the owner of a particular set of objects. This is done using

the following Data Pump Import parameters: REMAP_DATAFILE, REMAP_SCHEMA,

REMAP_TABLESPACE, and TRANSFORM.

Maximizing Job Performance

To improve throughput of a job, you can use the PARALLEL parameter to set a

degree of parallelism that takes maximum advantage of current conditions. For

example, to limit the effect of a job on a production system, the database

administrator (DBA) might wish to restrict the parallelism. The degree of

parallelism can be reset at any time during a job. For example, PARALLEL could be

set to 2 during production hours to restrict a particular job to only two degrees of

See Also: JOB_NAME on page 2-22 for more information about

how job names are formed.

See Also:

■ REMAP_DATAFILE on page 3-27

■ REMAP_SCHEMA on page 3-27

■ REMAP_TABLESPACE on page 3-29

■ TRANSFORM on page 3-37

Monitoring Job Status

Overview of Oracle Data Pump 1-9

parallelism, and during nonproduction hours it could be reset to 8. The parallelism

setting is enforced by a master process, which allocates work to be executed to a set

of worker processes that perform the data and metadata processing within an

operation. These worker processes operate in parallel.

Loading and Unloading of Data

The worker processes are the ones that actually unload and load metadata and table

data in parallel. The number of worker processes created is equal to the value

supplied for the PARALLEL command-line parameter. The number of worker

processes can be reset throughout the life of a job.

When a worker process is assigned the task of loading or unloading a very large

table or partition, it may choose to use the external tables access method to make

maximum use of parallel execution. In such a case, the worker process becomes a

parallel execution coordinator. The actual loading and unloading work is divided

among some number of parallel I/O execution processes (sometimes called slaves)

allocated from the instancewide pool of parallel I/O execution processes.

Monitoring Job Status

During the execution of a job, a log ﬁle will be optionally written. The log ﬁle

summarizes the progress of the job and any errors that were encountered along the

way. Whereas the log ﬁle records the completion status of the job, real-time status

can be obtained by using the STATUS command in interactive mode of Data Pump

Export or Import. Cumulative status for the job is returned, along with a description

of the current operation. In addition, an estimate for the completion percentage of

the current job is also returned. If the job is done, the state will be listed as Stopped

or Completed.

Note: The ability to adjust the degree of parallelism is available

only in the Enterprise Edition of Oracle Database.

Note: The value of PARALLEL is restricted to 1 in the Standard

Edition of Oracle Database 10g.

Monitoring Job Status

1-10 Oracle Database Utilities

An alternative way to determine job status or to get other information about Data

Pump jobs, would be to query the DBA_DATAPUMP_JOBS, USER_DATAPUMP_JOBS,

or DBA_DATAPUMP_SESSIONS views.

The DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS Views

The DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS views identify all active

Data Pump jobs, regardless of their state, on an instance (or on all instances for Real

Application Clusters). They also show all Data Pump master tables not currently

associated with an active job. You can use the job information to attach to an active

job. Once you are attached to the job, you can stop it, change its parallelism, or

monitor its progress. You can use the master table information to restart a stopped

job or to remove any master tables that are no longer needed.

Table 1–1 describes the columns in the DBA_DATAPUMP_JOBS view and the USER_

DATAPUMP_JOBS view.

See Also:

■ STATUS on page 2-41 for information about the STATUS

command in interactive mode of Data Pump Export

■ STATUS on page 3-48 for information about the STATUS

command in interactive mode of Data Pump Import

Table 1–1 DBA_DATAPUMP_JOBS View and USER_DATAPUMP_JOBS View

Column Datatype Description

OWNER_NAME VARCHAR2(30)

User who initiated the job (valid only for DBA_

DATAPUMP_JOBS)

JOB_NAME VARCHAR2(30)

User-supplied name for the job (or the default name

generated by the server)

OPERATION VARCHAR2(30)

Type of job

JOB_MODE VARCHAR2(30)

Mode of job

STATE VARCHAR2(30)

State of the job

DEGREE NUMBER

Number of worker processes performing the operation

ATTACHED_SESSIONS NUMBER

Number of sessions attached to the job

Monitoring Job Status

Overview of Oracle Data Pump 1-11

The DBA_DATAPUMP_SESSIONS View

The DBA_DATAPUMP_SESSIONS view identiﬁes the user sessions that are attached

to a job. The information in this view is useful for determining why a stopped

operation has not gone away.

Table 1–2 describes the columns in the DBA_DATAPUMP_SESSIONS view.

Monitoring the Progress of Executing Jobs

Data Pump operations that transfer table data (export and import) maintain an

entry in the V$SESSION_LONGOPS dynamic performance view indicating the job

progress (in megabytes of table data transferred). The entry contains the estimated

transfer size and is periodically updated to reﬂect the actual amount of data

transferred.

Note: The information returned is obtained from dynamic

performance views associated with the executing jobs and from the

database schema information concerning the master tables. A query

on these views can return multiple rows for a single Data Pump job

(same owner and job name) if the query is executed while the job is

transitioning between an Executing state and the Not Running

state.

Table 1–2 The DBA_DATAPUMP_SESSIONS View

Column Datatype Description

OWNER_NAME VARCHAR2(30)

User who initiated the job.

JOB_NAME VARCHAR2(30)

User-supplied name for the job (or the default name

generated by the server).

SADDR RAW(4) (RAW(8) on

64-bit systems)

Address of session attached to the job. Can be used

with V$SESSION view.

Note: The usefulness of the estimate value for export operations

depends on the type of estimation requested when the operation

was initiated, and it is updated as required if exceeded by the

actual transfer amount. The estimate value for import operations is

exact.

File Allocation

1-12 Oracle Database Utilities

The V$SESSION_LONGOPS columns that are relevant to a Data Pump job are as

follows:

■ USERNAME - job owner

■ OPNAME - job name

■ TARGET_DESC - job operation

■ SOFAR - megabytes (MB) transferred thus far during the job

■ TOTALWORK - estimated number of megabytes (MB) in the job

■ UNITS - 'MB'

■ MESSAGE - a formatted status message of the form:

'<job_name>: <operation_name> : nnn out of mmm MB done'

File Allocation

There are three types of ﬁles managed by Data Pump jobs:

■ Dump ﬁles to contain the data and metadata that is being moved

■ Log ﬁles to record the messages associated with an operation

■ SQL ﬁles to record the output of a SQLFILE operation. A SQLFILE operation is

invoked using the Data Pump Import SQLFILE parameter and results in all of

the SQL DDL that Import will be executing based on other parameters, being

written to a SQL ﬁle. See SQLFILE on page 3-32 for more information.

An understanding of how Data Pump allocates and handles these ﬁles will help

you to use Export and Import to their fullest advantage.

Specifying Files and Adding Additional Dump Files

For export operations, you can specify dump ﬁles at the time the job is deﬁned, as

well as at a later time during the operation. For example, if you discover that space

is running low during an export operation, you can add additional dump ﬁles by

using the Data Pump Export ADD_FILE command in interactive mode.

For import operations, all dump ﬁles must be speciﬁed at the time the job is

deﬁned.

Log ﬁles and SQL ﬁles will overwrite previously existing ﬁles. Dump ﬁles will

never overwrite previously existing ﬁles. Instead, an error will be generated.

File Allocation

Overview of Oracle Data Pump 1-13

Default Locations for Dump, Log, and SQL Files

Because Data Pump is server-based, rather than client-based, dump ﬁles, log ﬁles,

and SQL ﬁles are accessed relative to server-based directory paths. Data Pump

requires you to specify directory paths as directory objects. A directory object maps

a name to a directory path on the ﬁle system.

For example, the following SQL statement creates a directory object named dpump_

dir1 that is mapped to a directory located at /usr/apps/datafiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

The reason that a directory object is required is to ensure data security and integrity.

For example:

■ If you were allowed to specify a directory path location for an input ﬁle, you

might be able to read data that the server has access to, but to which you should

not.

■ If you were allowed to specify a directory path location for an output ﬁle, the

server might overwrite a ﬁle that you might not normally have privileges to

delete.

Before you can run Data Pump Export or Data Pump Import, a directory object

must be created by a database administrator (DBA) or by any user with the CREATE

ANY DIRECTORY privilege. Then, when you are using Export or Import, you specify

the directory object with the DIRECTORY parameter.

After a directory is created, the user creating the directory object needs to grant

READ or WRITE permission on the directory to other users. For example, to allow

the Oracle database to read and write ﬁles on behalf of user hr in the directory

named by dpump_dir1, the DBA must execute the following command:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

Note that READ or WRITE permission to a directory object only means that the

Oracle database will read or write that ﬁle on your behalf. You are not given direct

access to those ﬁles outside of the Oracle database unless you have the appropriate

operating system privileges. Similarly, the Oracle database requires permission

from the operating system to read and write ﬁles in the directories.

Data Pump Export and Import use the following order of precedence to determine a

ﬁle's location:

File Allocation

1-14 Oracle Database Utilities

If a directory object is speciﬁed as part of the ﬁle speciﬁcation, then the location

speciﬁed by that directory object is used. (The directory object must be

separated from the ﬁlename by a colon.)

2. If a directory object is not speciﬁed for a ﬁle, then the directory object named by

the DIRECTORY parameter is used.

3. If a directory object is not speciﬁed, and if no directory object was named by the

DIRECTORY parameter, then the value of the environment variable, DATA_

PUMP_DIR, is used. This environment variable is deﬁned using operating

system commands on the client system where the Data Pump Export and

Import utilities are run. The value assigned to this client-based environment

variable must be the name of a server-based directory object, which must ﬁrst

be created on the server system by a DBA. For example, the following SQL

statement creates a directory object on the server system. The name of the

directory object is DUMP_FILES1, and it is located at

'/usr/apps/dumpfiles1'.

SQL> CREATE DIRECTORY DUMP_FILES1 AS '/usr/apps/dumpfiles1';

Then, a user on a UNIX-based client system using csh can assign the value

DUMP_FILES1 to the environment variable DATA_PUMP_DIR. The

DIRECTORY parameter can then be omitted from the command line. The

dump ﬁle employees.dmp, as well as the log ﬁle export.log, will be

written to '/usr/apps/dumpfiles1'.

%setenv DATA_PUMP_DIR DUMP_FILES1

%expdp hr/hr TABLES=employees DUMPFILE=employees.dmp

4. If none of the previous three conditions yields a directory object and you are a

privileged user, then Data Pump attempts to use the value of the default

server-based directory object, DATA_PUMP_DIR. It is important to understand

that Data Pump does not create the DATA_PUMP_DIR directory object; it merely

attempts to use its value when a privileged user has not provided a directory

object using any of the mechanisms previously described. This default directory

object must ﬁrst be created by a DBA. Do not confuse this with the client-based

environment variable of the same name.

Using Directory Objects When Automatic Storage Management Is Enabled

If you use Data Pump Export or Import with Automatic Storage Management

(ASM) enabled, you must deﬁne the directory object used for the dump ﬁle so that

the ASM disk-group name is used (instead of an operating system directory path).

A separate directory object, which points to an operating system directory path,

File Allocation

Overview of Oracle Data Pump 1-15

should be used for the log ﬁle. For example, you would create a directory object for

the ASM dump ﬁle as follows:

SQL> CREATE or REPLACE DIRECTORY dpump_dir as '+DATAFILES/';

Then you would create a separate directory object for the log ﬁle:

SQL> CREATE or REPLACE DIRECTORY dpump_log as '/homedir/user1/';

To enable user hr to have access to these directory objects, you would assign the

necessary privileges, for example:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO hr;

SQL> GRANT READ, WRITE ON DIRECTORY dpump_log TO hr;

You would then use the following Data Pump Export command:

> expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr.dmp LOGFILE=dpump_log:hr.log

Setting Parallelism

For export and import operations, the parallelism setting (speciﬁed with the

PARALLEL parameter) should be less than or equal to the number of dump ﬁles in

the dump ﬁle set. If there are not enough dump ﬁles, the performance will not be

optimal because multiple threads of execution will be trying to access the same

dump ﬁle.

The PARALLEL parameter is valid only in the Enterprise Edition of Oracle Database

10g.

See Also:

■ DIRECTORY on page 2-10 for information about using this

parameter in Data Pump Export

■ DIRECTORY on page 3-10 for information about using this

parameter in Data Pump Import

■ Oracle Database SQL Reference for information about the CREATE

DIRECTORY command

■ Oracle Database Administrator's Guide for more information

about Automatic Storage Management (ASM)

Original Export and Import Versus Data Pump Export and Import

1-16 Oracle Database Utilities

Using Substitution Variables

Instead of, or in addition to, listing speciﬁc ﬁlenames, you can use the DUMPFILE

parameter during export operations to specify multiple dump ﬁles, by using a

substitution variable (%U) in the ﬁlename. This is called a dump ﬁle template. The

new dump ﬁles are created as they are needed, beginning with 01 for %U, then

using 02, 03, and so on. Enough dump ﬁles are created to allow all processes

speciﬁed by the current setting of the PARALLEL parameter to be active. If one of

the dump ﬁles becomes full because its size has reached the maximum size

speciﬁed by the FILESIZE parameter, it is closed, and a new dump ﬁle (with a new

generated name) is created to take its place.

If multiple dump ﬁle templates are provided, they are used to generate dump ﬁles

in a round-robin fashion. For example, if expa%U, expb%U, and expc%U were all

speciﬁed for a job having a parallelism of 6, the initial dump ﬁles created would be

expa01.dmp, expb01.dmp, expc01.dmp, expa02.dmp, expb02.dmp, and

expc02.dmp.

For import and SQLFILE operations, if dump ﬁle speciﬁcations expa%U, expb%U,

and expc%U are speciﬁed, then the operation will begin by attempting to open the

dump ﬁles expa01.dmp, expb01.dmp, and expc01.dmp. If the dump ﬁle

containing the master table is not found in this set, the operation expands its search

for dump ﬁles by incrementing the substitution variable and looking up the new

ﬁlenames (for example, expa02.dmp, expb02.dmp, and expc02.dmp). The search

continues until the dump ﬁle containing the master table is located. If a dump ﬁle

does not exist, the operation stops incrementing the substitution variable for the

dump ﬁle speciﬁcation that was in error. For example, if expb01.dmp and

expb02.dmp are found but expb03.dmp is not found, then no more ﬁles are

searched for using the expb%U speciﬁcation. Once the master table is found, it is

used to determine whether all dump ﬁles in the dump ﬁle set have been located.

Original Export and Import Versus Data Pump Export and Import

If you are familiar with the original Export (exp) and Import (imp) utilities, it is

important to understand that many of the concepts behind them do not apply to

Data Pump Export (expdp) and Data Pump Import (impdp). In particular:

■ Data Pump Export and Import operate on a group of ﬁles called a dump ﬁle set

rather than on a single sequential dump ﬁle.

■ Data Pump Export and Import access ﬁles on the server rather than on the

client. This results in improved performance. It also means that directory

objects are required when you specify ﬁle locations.

Original Export and Import Versus Data Pump Export and Import

Overview of Oracle Data Pump 1-17

■ Data Pump Export and Import use parallel execution rather than a single

stream of execution, for improved performance. This means that the order of

data within dump ﬁle sets is more variable.

■ Data Pump Export and Import represent metadata in the dump ﬁle set as XML

documents rather than as DDL commands. This provides improved ﬂexibility

for transforming the metadata at import time.

■ Data Pump Export and Import are self-tuning utilities. Tuning parameters that

were used in original Export and Import, such as BUFFER and RECORDLENGTH,

are neither required nor supported by Data Pump Export and Import.

■ At import time there is no option to perform interim commits during the

restoration of a partition. This was provided by the COMMIT parameter in

original Import.

■ There is no option to merge extents when you re-create tables. In original

Import, this was provided by the COMPRESS parameter. Instead, extents are

reallocated according to storage parameters for the target table.

■ Sequential media, such as tapes and pipes, are not supported.

■ When you are importing data into an existing table using either APPEND or

TRUNCATE, if any row violates an active constraint, the load is discontinued and

no data is loaded. This is different from original Import, which logs any rows

that are in violation and continues with the load.

See Also: For a comparison of Data Pump Export and Import

parameters to the parameters of original Export and Import, see the

following:

■ How Data Pump Export Parameters Map to Those of the

Original Export Utility on page 2-35

■ How Data Pump Import Parameters Map to Those of the

Original Import Utility on page 3-42

Original Export and Import Versus Data Pump Export and Import

1-18 Oracle Database Utilities

Data Pump Export 2-1

Data Pump Export

This chapter describes the Oracle Data Pump Export utility. The following topics are

discussed:

■ What Is Data Pump Export?

■ Invoking Data Pump Export

■ Filtering During Export Operations

■ Parameters Available in Export's Command-Line Mode

■ How Data Pump Export Parameters Map to Those of the Original Export Utility

■ Commands Available in Export's Interactive-Command Mode

■ Examples of Using Data Pump Export

■ Syntax Diagrams for Data Pump Export

What Is Data Pump Export?

Data Pump Export (hereinafter referred to as Export for ease of reading) is a utility

for unloading data and metadata into a set of operating system ﬁles called a dump

ﬁle set. The dump ﬁle set can be imported only by the Data Pump Import utility.

Note: Data Pump Export (invoked with the expdp command) is a

new utility as of Oracle Database 10g. Although its functionality

and its parameters are similar to those of the original Export utility

(exp), they are completely separate utilities and their ﬁles are not

compatible. See Chapter 20, "Original Export and Import" for a

description of the original Export utility.

Invoking Data Pump Export

2-2 Oracle Database Utilities

The dump ﬁle set can be imported on the same system or it can be moved to

another system and loaded there.

The dump ﬁle set is made up of one or more disk ﬁles that contain table data,

database object metadata, and control information. The ﬁles are written in a

proprietary, binary format. During an import operation, the Data Pump Import

utility uses these ﬁles to locate each database object in the dump ﬁle set.

Because the dump ﬁles are written by the server, rather than by the client, the data

base administrator (DBA) must create directory objects. See Default Locations for

Dump, Log, and SQL Files on page 1-13 for more information about directory

objects.

Data Pump Export enables you to specify that a job should move a subset of the

data and metadata, as determined by the export mode. This is done using data

ﬁlters and metadata ﬁlters, which are speciﬁed through Export parameters. See

Filtering During Export Operations on page 2-6.

To see some examples of the various ways in which you can use Data Pump Export,

refer to Examples of Using Data Pump Export on page 2-43.

Invoking Data Pump Export

The Data Pump Export utility is invoked using the expdp command. The

characteristics of the export operation are determined by the Export parameters you

specify. These parameters can be speciﬁed either on the command line or in a

parameter ﬁle.

The following sections contain more information about invoking Export:

■ Data Pump Export Interfaces on page 2-3

■ Data Pump Export Modes on page 2-3

■ Network Considerations on page 2-5

Note: Do not invoke 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.

Invoking Data Pump Export

Data Pump Export 2-3

Data Pump Export Interfaces

You can interact with Data Pump Export by using a command line, a parameter ﬁle,

or an interactive-command mode.

■ Command-Line Interface: Enables you to specify most of the Export parameters

directly on the command line. For a complete description of the parameters

available in the command-line interface, see Parameters Available in Export's

Command-Line Mode on page 2-8.

■ Parameter File Interface: Enables you to specify command-line parameters in a

parameter ﬁle. The only exception is the PARFILE parameter, because

parameter ﬁles cannot be nested.

■ Interactive-Command Interface: Stops logging to the terminal and displays the

Export prompt, from which you can enter various commands, some of which

are speciﬁc 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 ﬁle interface. Interactive-command mode is also enabled when

you attach to an executing or stopped job.

For a complete description of the commands available in interactive-command

mode, see Commands Available in Export's Interactive-Command Mode on

page 2-37.

Data Pump Export Modes

One of the most signiﬁcant characteristics of an export operation is its mode,

because the mode largely determines what is exported. Export provides different

modes for unloading different portions of the database. The mode is speciﬁed on

the command line, using the appropriate parameter. The available modes are as

follows:

■ Full Export Mode on page 2-4

■ Schema Mode on page 2-4

■ Table Mode on page 2-4

■ Tablespace Mode on page 2-4

■ Transportable Tablespace Mode on page 2-5

See Also: Examples of Using Data Pump Export on page 2-43

Invoking Data Pump Export

2-4 Oracle Database Utilities

Full Export Mode

A full export is speciﬁed using the FULL parameter. In a full database export, the

entire database is unloaded. This mode requires that you have the EXP_FULL_

DATABASE role.

Schema Mode

A schema export is speciﬁed using the SCHEMAS parameter. This is the default

export mode. If you have the EXP_FULL_DATABASE role, then you can specify a list

of schemas and optionally include the schema deﬁnitions themselves, as well as

system privilege grants to those schemas. If you do not have the EXP_FULL_

DATABASE role, you can export only your own schema.

Cross-schema references are not exported unless the referenced schema is also

speciﬁed in the list of schemas to be exported. For example, a trigger deﬁned on a

table within one of the speciﬁed schemas, but that resides in a schema not explicitly

speciﬁed, is not exported. This is also true for external type deﬁnitions upon which

tables in the speciﬁed schemas depend. In such a case, it is expected that the type

deﬁnitions already exist in the target instance at import time.

Table Mode

A table export is speciﬁed using the TABLES parameter. In table mode, only a

speciﬁed set of tables, partitions, and their dependent objects are unloaded. You

must have the EXP_FULL_DATABASE role to specify tables that are not in your own

schema, and only one schema can be speciﬁed. Note that type deﬁnitions for

columns are not exported in table mode. It is expected that the type deﬁnitions

already exist in the target instance at import time. Also, as in schema exports,

cross-schema references are not exported.

Tablespace Mode

A tablespace export is speciﬁed using the TABLESPACES parameter. In tablespace

mode, only the tables contained in a speciﬁed set of tablespaces are unloaded. If a

table is unloaded, 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 speciﬁed

set, then that table and all of its dependent objects are exported. You must have the

EXP_FULL_DATABASE role to use tablespace mode.

See Also:

■ TABLESPACES on page 2-31

■ Oracle Database Administrator's Guide for detailed information

about transporting tablespaces between databases

See Also: TRANSPORT_TABLESPACES on page 2-33

Filtering During Export Operations

2-6 Oracle Database Utilities

parameter, retrieves data from it, and writes the data to a dump ﬁle set back on the

local system.

Filtering During Export Operations

Data Pump Export provides much greater data and metadata ﬁltering capability

than was provided by the original Export utility.

Data Filters

Data ﬁlters specify restrictions on the rows that are to be exported. These

restrictions can be based on partition names and on the results of subqueries.

Each data ﬁlter can be speciﬁed once per table within a job. If different ﬁlters using

the same name are applied to both a particular table and to the whole job, the ﬁlter

parameter supplied for the speciﬁc table will take precedence.

Metadata Filters

Metadata ﬁltering is implemented through the EXCLUDE and INCLUDE parameters.

The EXCLUDE and INCLUDE parameters are mutually exclusive.

Metadata ﬁlters identify a set of objects to be included or excluded from an Export

or Import operation. For example, you could request a full export, but without

Package Speciﬁcations or Package Bodies.

To use ﬁlters correctly and to get the results you expect, remember that dependent

objects of an identiﬁed object are processed along with the identiﬁed object. For example, if

a ﬁlter speciﬁes that an index is to be included in an operation, then statistics from

that index will also be included. Likewise, if a table is excluded by a ﬁlter, then

indexes, constraints, grants, and triggers upon the table will also be excluded by the

ﬁlter.

If multiple ﬁlters are speciﬁed for an object type, an implicit AND operation is

applied to them. That is, objects pertaining to the job must pass all of the ﬁlters

applied to their object types.

See Also:

■ NETWORK_LINK on page 2-23

■ Oracle Net Services Administrator's Guide

■ Oracle Database Heterogeneous Connectivity Administrator's Guide

Filtering During Export Operations

Data Pump Export 2-7

The same metadata ﬁlter name can be speciﬁed multiple times within a job.

To see which objects can be ﬁltered, you can perform queries on the following

views: DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_

EXPORT_OBJECTS. For example, you could perform the following query:

SQL> SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS

2 WHERE OBJECT_PATH LIKE '%GRANT';

The output of this query looks similar to the following:

OBJECT_PATH

--------------------------------------------------------------------------------

COMMENTS

--------------------------------------------------------------------------------

GRANT

Grants on objects in the selected schemas

OBJECT_GRANT

Grants on objects in the selected schemas

PROCDEPOBJ_GRANT

Grants on instance procedural objects in the selected schemas

OBJECT_PATH

--------------------------------------------------------------------------------

COMMENTS

--------------------------------------------------------------------------------

PROCOBJ_GRANT

Schema procedural object grants in the selected schemas

ROLE_GRANT

Role grants to users associated with the selected schemas

SYSTEM_GRANT

System privileges granted to users associated with the selected schemas

6 rows selected.

See Also: EXCLUDE on page 2-15 and INCLUDE on page 2-20

Parameters Available in Export's Command-Line Mode

2-8 Oracle Database Utilities

Parameters Available in Export's Command-Line Mode

This section provides descriptions of the parameters available in the command-line

mode of Data Pump Export. Many of the descriptions include an example of how to

use the parameter.

Using the Export Parameter Examples

If you try running the examples that are provided for each parameter, be aware of

the following requirements:

■ Most of the examples use the sample schemas of the seed database, which is

installed by default when you install Oracle Database. In particular, the human

resources (hr) schema is often used.

■ The examples assume that the directory objects, dpump_dir1 and dpump_

dir2, already exist and that READ and WRITE privileges have been granted to

the hr schema for these directory objects. See Default Locations for Dump, Log,

and SQL Files on page 1-13 for information about creating directory objects and

assigning privileges to them.

■ Some of the examples require the EXP_FULL_DATABASE and IMP_FULL_

DATABASE roles. The examples assume that the hr schema has been granted

these roles.

If necessary, ask your DBA for help in creating these directory objects and assigning

the necessary privileges and roles.

Syntax diagrams of these parameters are provided in Syntax Diagrams for Data

Pump Export on page 2-46.

Unless speciﬁcally noted, these parameters can also be speciﬁed in a parameter ﬁle.

Command-Line Escape Characters Used in Examples

Some of the examples in this chapter (such as for the EXCLUDE parameter) may

show certain clauses enclosed in quotation marks and backslashes. This is because

those clauses contain a blank, a situation for which most operating systems require

that the entire string be placed in quotation marks or marked as a literal by some

method. Some operating systems also require that quotation marks on the

command line be preceded by an escape character, such as the backslash. If the

backslashes were not present, the command-line parser that Export uses would not

understand the quotation marks and would remove them. Some examples in this

chapter may use escape characters to show you how they would be used. However,

in general, Oracle recommends that you place such statements in a parameter ﬁle

because escape characters are not necessary in parameter ﬁles.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-9

ATTACH

Default: job currently in the user's schema, if there is only one

Purpose

Attaches the client session to an existing export job and automatically places you in

the interactive-command interface. Export displays a description of the job to which

you are attached and also displays the Export prompt.

Syntax and Description

ATTACH [=[schema_name.]job_name]

The schema_name is optional. To specify a schema other than your own, you must

have the EXP_FULL_DATABASE role.

The job_name is optional if only one export job is associated with your schema and

the job is active. To attach to a stopped job, you must supply the job name. To see a

list of Data Pump job names, you can query the DBA_DATAPUMP_JOBS view or the

USER_DATAPUMP_JOBS view.

When you are attached to the job, Export displays a description of the job and then

displays the Export prompt.

Restrictions

When you specify the ATTACH parameter, you cannot specify any other parameters

except for the connection string (user/password).

See Also:

■ Default Locations for Dump, Log, and SQL Files on page 1-13

for information about creating default directory objects

■ Examples of Using Data Pump Export on page 2-43

■ Oracle Database Sample Schemas

Note: If you are accustomed to using the original Export utility

(exp), you may be wondering which Data Pump parameters are

used to perform the operations you used to perform with original

Export. For a comparison, see How Data Pump Export Parameters

Map to Those of the Original Export Utility on page 2-35.

Parameters Available in Export's Command-Line Mode

2-10 Oracle Database Utilities

You cannot attach to a job in another schema unless it is already running.

Example

The following is an example of using the ATTACH parameter. It assumes that the job,

hr.export_job, already exists.

> expdp hr/hr ATTACH=hr.export_job

CONTENT

Default: ALL

Purpose

Enables you to ﬁlter what Export unloads: data only, metadata only, or both.

Syntax and Description

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

■ ALL unloads both data and metadata. This is the default.

■ DATA_ONLY unloads only table row data; no database object deﬁnitions are

unloaded.

■ METADATA_ONLY unloads only database object deﬁnitions; no table row data is

unloaded.

Example

The following is an example of using the CONTENT parameter:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp CONTENT=METADATA_ONLY

This command will execute a schema-mode export that will unload only the

metadata associated with the hr schema. It defaults to a schema-mode export of the

hr schema because no export mode is speciﬁed.

See Also:

■ Default Locations for Dump, Log, and SQL Files on page 1-13

for more information about default directory objects

■ Oracle Database SQL Reference for information about the CREATE

DIRECTORY command

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-13

time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp,

exp02aa02.dmp, and so forth.

If the FILESIZE parameter is speciﬁed, each dump ﬁle will have a maximum of

that size in bytes and be nonextensible. If more space is required for the dump ﬁle

set and a template with a substitution variable (%U) was supplied, a new dump ﬁle

is automatically created of the size speciﬁed by FILESIZE, if there is room on the

device.

If templates with a substitution variable (%U) were speciﬁed along with the

PARALLEL parameter, then one ﬁle for each template is initially created. More ﬁles

are created from the templates as they are needed based on how much data is being

exported and how many parallel processes are given work to perform during the

job.

As each ﬁle speciﬁcation or ﬁle template containing a substitution variable is

deﬁned, it is instantiated into one fully qualiﬁed ﬁlename and Export attempts to

create it. The ﬁle speciﬁcations are processed in the order in which they are

speciﬁed. If the job needs extra ﬁles because the maximum ﬁle size is reached, or to

keep parallel workers active, then additional ﬁles are created if ﬁle templates with

substitution variables were speciﬁed.

Restrictions

If there are preexisting ﬁles that match the resulting ﬁlenames, an error is generated.

The existing dump ﬁles will not be overwritten.

Example

The following is an example of using the DUMPFILE parameter:

> expdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp,

exp2%U.dmp PARALLEL=3

The dump ﬁle, exp1.dmp, will be written to the path associated with the directory

object dpump_dir2 because dpump_dir2 was speciﬁed as part of the dump ﬁle

name, and therefore overrides the directory object speciﬁed with the DIRECTORY

parameter. Because all three parallel processes will be given work to perform

during this job, the exp201.dmp and exp202.dmp dump ﬁles will be created and

they will be written to the path associated with the directory object, dpump_dir1,

that was speciﬁed with the DIRECTORY parameter.

See Also:

■ File Allocation on page 1-12

Parameters Available in Export's Command-Line Mode

2-14 Oracle Database Utilities

ESTIMATE

Default: BLOCKS

Purpose

Speciﬁes the method that Export will use to estimate how much disk space each

table in the export job will consume (in bytes). The estimate is printed in the log ﬁle

and displayed on the client's standard output device. The estimate is for table row

data only; it does not include metadata.

Syntax and Description

ESTIMATE={BLOCKS | STATISTICS}

■ BLOCKS - The estimate is calculated by multiplying the number of database

blocks used by the target objects times the appropriate block sizes.

■ STATISTICS - The estimate is calculated using statistics for each table. For this

method to be as accurate as possible, all tables should have been analyzed

recently.

Example

The following example shows a use of the ESTIMATE parameter in which the

estimate is calculated using statistics for the employees table:

> expdp hr/hr TABLES=employees ESTIMATE=STATISTICS DIRECTORY=dpump_dir1

DUMPFILE=estimate_stat.dmp

ESTIMATE_ONLY

Default: n

Purpose

Instructs Export to estimate the space that a job would consume, without actually

performing the export operation.

Syntax and Description

ESTIMATE_ONLY={y | n}

If ESTIMATE_ONLY=y, then Export estimates the space that would be consumed,

but quits without actually performing the export operation.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-15

Example

The following shows an example of using the ESTIMATE_ONLY parameter to

determine how much space an export of the HR schema will take.

> expdp hr/hr ESTIMATE_ONLY=y NOLOGFILE=y

EXCLUDE

Default: none

Purpose

Enables you to ﬁlter the metadata that is exported by specifying objects and object

types that you want excluded from the export operation.

Syntax and Description

EXCLUDE=object_type[:name_clause] [, ...]

All object types for the given mode of export will be included except those speciﬁed

in an EXCLUDE statement. If an object is excluded, all of its dependent objects are

also excluded. For example, excluding a table will also exclude all indexes and

triggers on the table.

The name_clause is optional. It allows selection of speciﬁc objects within an object

type. It is a SQL expression used as a ﬁlter on the type's object names. It consists of a

SQL operator and the values against which the object names of the speciﬁed type

are to be compared. The name clause applies only to object types whose instances

have names (for example, it is applicable to TABLE, but not to GRANT). The name

clause must be separated from the object type with a colon and enclosed in double

quotation marks, because single-quotation marks are required to delimit the name

strings. For example, you could set EXCLUDE=INDEX:"LIKE 'EMP%'" to exclude

all indexes whose names start with emp. This is shown in the following example:

> expdp hr/hr EXCLUDE=INDEX:\"LIKE \'EMP%\'\" DUMPFILE=dpump_dir1:exp.dmp

NOLOGFILE=y

If no name_clause is provided, all objects of the speciﬁed type are excluded.

More than one EXCLUDE statement can be speciﬁed.

Oracle recommends that you place EXCLUDE clauses in a parameter ﬁle to avoid

having to use escape characters on the command line. This example shows how the

use of the backslash escape character would be necessary if you speciﬁed this

EXCLUDE clause on the command line, rather than in a parameter ﬁle.

Parameters Available in Export's Command-Line Mode

2-16 Oracle Database Utilities

If the object_type you specify is CONSTRAINT, GRANT, or USER, you should be

aware of the effects this will have, as described in the following paragraphs.

Excluding Constraints

The following constraints cannot be excluded:

■ NOT NULL constraints

■ Constraints needed for the table to be created and loaded successfully; for

example, primary key constraints for index-organized tables, or REF SCOPE and

WITH ROWID constraints for tables with REF columns

This means that the following EXCLUDE statements will be interpreted as follows:

■ EXCLUDE=CONSTRAINT will exclude all (nonreferential) constraints, except for

NOT NULL constraints and any constraints needed for successful table creation

and loading.

■ EXCLUDE=REF_CONSTRAINT will exclude referential integrity (foreign key)

constraints.

Excluding Grants and Users

Specifying EXCLUDE=GRANT excludes object grants on all object types and system

privilege grants.

Specifying EXCLUDE=USER excludes only the deﬁnitions of users, not the objects

contained within users' schemas.

To exclude a speciﬁc user and all objects of that user, specify a ﬁlter such as the

following (where hr is the schema name of the user you want to exclude):

EXCLUDE=SCHEMA:"='HR'"

If you try to exclude a user by using a statement such as

EXCLUDE=USER:"='HR'", then only CREATE USER hr DDL statements will be

excluded, and you may not get the results you expect.

See Also:

■ INCLUDE on page 2-20 for an example of using a parameter

ﬁle

■ Command-Line Escape Characters Used in Examples on

page 2-8

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-17

Restrictions

The EXCLUDE and INCLUDE parameters are mutually exclusive.

Neither EXCLUDE nor INCLUDE can be used if the CONTENT=DATA_ONLY

parameter is speciﬁed, because that implies export of table row data only.

Example

The following is an example of using the EXCLUDE statement.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW,

PACKAGE, FUNCTION

This will result in a schema-mode export in which all of the hr schema will be

exported except its views, packages, and functions.

FILESIZE

Default: 0 (unlimited)

Purpose

Speciﬁes the maximum size of each export dump ﬁle. If the size is reached for any

member of the dump ﬁle set, that ﬁle is closed and an attempt is made to create a

new ﬁle, if the ﬁle speciﬁcation contains a substitution variable. If there is

insufﬁcient space on the device to write a ﬁle of the speciﬁed size, the export

operation will stop. It can be restarted after the situation is corrected.

Syntax and Description

FILESIZE=integer[B | K | M | G]

The integer can be followed by B, K, M, or G (indicating bytes, kilobytes,

megabytes, and gigabytes respectively). Bytes is the default. The actual size of the

resulting ﬁle may be rounded down slightly to match the size of the internal blocks

used in dump ﬁles.

Restrictions

The minimum size for a ﬁle is ten times the default Data Pump block size, which is

4 kilobytes.

See Also:

■ Filtering During Export Operations on page 2-6 for more

information about the effects of using the EXCLUDE parameter

Parameters Available in Export's Command-Line Mode

2-18 Oracle Database Utilities

Example

The following shows an example in which the size of the dump ﬁle is set to 3

megabytes:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m.dmp FILESIZE=3M

FLASHBACK_SCN

Default: none

Purpose

Speciﬁes the system change number (SCN) that Export will use to enable the

Flashback utility. The export operation is performed with data that is consistent as

of this SCN. If the NETWORK_LINK parameter is speciﬁed, the SCN refers to the

SCN of the source database.

Syntax and Description

FLASHBACK_SCN=scn_value

Restrictions

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.

Example

The following example assumes that an existing SCN value of 384632 exists. It

exports the hr schema up to SCN 384632.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632

FLASHBACK_TIME

Default: none

Note: If you are on a logical standby system, the FLASHBACK_SCN

parameter is ignored because SCNs are selected by logical standby.

See Oracle Data Guard Concepts and Administration for information

about logical standby databases.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-19

Purpose

The SCN that most closely matches the speciﬁed time is found, and this SCN is

used to enable the Flashback utility. The export operation is performed with data

that is consistent as of this SCN.

Syntax and Description

FLASHBACK_TIME="TO_TIMESTAMP(time-value)"

Restrictions

FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.

Example

You can specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_

TIME procedure accepts, for example:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_time.dmp

FLASHBACK_TIME="TO_TIMESTAMP('25-08-2003 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

This example may require the use of escape characters, depending on your

operating system. See Command-Line Escape Characters Used in Examples on

page 2-8.

FULL

Default: n

Purpose

Speciﬁes that you want to perform a full database mode export.

Syntax and Description

FULL={y | n}

If FULL=y, all data and metadata are exported.

To perform a full export, you must have the EXP_FULL_DATABASE role.

See Also: Oracle Database Application Developer's Guide -

Fundamentals for information about using ﬂashback

Parameters Available in Export's Command-Line Mode

2-20 Oracle Database Utilities

Restrictions

The following system schemas are not exported as part of a Full export because the

metadata they contain is exported as part of other objects in the dump ﬁle set: SYS,

ORDSYS, ORDPLUGINS, CTXSYS, MDSYS, LBACSYS, and XDB. Also excluded are any

tables that are registered in the SYS.KU_NOEXP_TAB dictionary table.

Example

The following is an example of using the FULL parameter. The dump ﬁle,

expfull.dmp is written to the dpump_dir2 directory.

> expdp hr/hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=y NOLOGFILE=y

HELP

Default: N

Purpose

Displays online help for the Export utility.

Syntax and Description

HELP = {y | n}

If HELP=y is speciﬁed, Export displays a summary of all Export command-line

parameters and interactive commands.

Example

> expdp HELP = y

This example will display a brief description of all Export parameters and

commands.

INCLUDE

Default: none

Purpose

Enables you to ﬁlter the metadata that is exported by specifying objects and object

types for the current export mode. The speciﬁed objects and all their dependent

objects are exported. Grants on these objects are also exported.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-21

Syntax and Description

INCLUDE = object_type[:name_clause] [, ...]

Only object types explicitly speciﬁed in INCLUDE statements are exported. No other

object types, including the schema deﬁnition information that is normally part of a

schema-mode export when you have the EXP_FULL_DATABASE role, are exported.

To see a list of valid object type path names for use with the INCLUDE parameter,

you can query the following views: DATABASE_EXPORT_OBJECTS, SCHEMA_

EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.

The name_clause is optional. It allows ﬁne-grained selection of speciﬁc objects

within an object type. It is a SQL expression used as a ﬁlter on the object names of

the type. It consists of a SQL operator and the values against which the object

names of the speciﬁed type are to be compared. The name clause applies only to

object types whose instances have names (for example, it is applicable to TABLE,

but not to GRANT). The optional name clause must be separated from the object type

with a colon and enclosed in double quotation marks, because single-quotation

marks are required to delimit the name strings.

Oracle recommends that INCLUDE statements be placed in a parameter ﬁle so that

you can avoid having to use operating system-speciﬁc escape characters on the

command line. For example, suppose you have a parameter ﬁle named hr.par

with the following content:

SCHEMAS=HR

DUMPFILE=expinclude.dmp

DIRECTORY=dpump_dir1

LOGFILE=expinclude.log

INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

INCLUDE=PROCEDURE

INCLUDE=INDEX:"LIKE 'EMP%'"

You could then use the hr.par ﬁle to start an export operation, without having to

enter any other parameters on the command line:

> expdp hr/hr parfile=hr.par

Restrictions

The INCLUDE and EXCLUDE parameters are mutually exclusive. Additionally,

neither one can be used if the CONTENT=DATA_ONLY parameter is speciﬁed,

because that implies export of table rows only.

Parameters Available in Export's Command-Line Mode

2-22 Oracle Database Utilities

Example

The following example performs an export of all tables (and their dependent

objects) in the hr schema:

> expdp hr/hr INCLUDE=TABLE DUMPFILE=dpump_dir1:exp_inc.dmp NOLOGFILE=y

JOB_NAME

Default: system-generated name of the form SYS_<operation>_<mode>_NN

Purpose

Speciﬁes a name for the export job. The job name is used to identify the export job

in subsequent actions, such as when the ATTACH parameter is used to attach to a

job. The job name becomes the name of the master table in the current user's

schema. The master table is used to control the export job.

Syntax and Description

JOB_NAME=jobname_string

The jobname_string speciﬁes a name of up to 30 bytes for this export job. The

bytes must represent printable characters and spaces. If spaces are included, the

name must be enclosed in single quotation marks (for example, 'Thursday Export').

The job name is implicitly qualiﬁed by the schema of the user performing the export

operation.

The default job name is system-generated in the form SYS_<operation>_

<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01. An

example of a default name is 'SYS_EXPORT_TABLESPACE_02'.

Example

The following example shows an export operation that is assigned a job name of

exp_job:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME='exp_job'

NOLOGFILE=y

LOGFILE

Default: export.log

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-23

Purpose

Speciﬁes the name, and optionally, a directory, for the log ﬁle of the export job.

Syntax and Description

LOGFILE=[directory_object:]file_name

You can specify a database directory_object previously established by the

DBA. This overrides the directory object speciﬁed with the DIRECTORY parameter.

The file_name speciﬁes a name for the log ﬁle. The default behavior is to create a

ﬁle named export.log in the directory referenced by the directory object speciﬁed

in the DIRECTORY parameter.

All messages regarding work in progress, work completed, and errors encountered

are written to the log ﬁle. (For a real-time status of the job, use the STATUS

command in interactive mode.)

A log ﬁle is always created for an export job unless the NOLOGFILE parameter is

speciﬁed. As with the dump ﬁle set, the log ﬁle is relative to the server and not the

client.

An existing ﬁle matching the ﬁlename will be overwritten.

Example

The following example shows how to specify a log ﬁle name if you do not want to

use the default:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp LOGFILE=hr_export.log

NETWORK_LINK

Default: none

Note: Data Pump Export writes the log ﬁle using the database

character set. If your client NLS_LANG environment setting sets up

a different client character set from the database character set, then

it is possible that table names may be different in the log ﬁle than

they are when displayed on the client output screen.

See Also:

■ DUMPFILE on page 2-12

■ Commands Available in Export's Interactive-Command Mode

on page 2-37

■ Performing a Parallel Full Database Export on page 2-45

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-27

PARFILE

Default: none

Purpose

Speciﬁes the name of an export parameter ﬁle.

Syntax and Description

PARFILE=[directory_path]file_name

Unlike dump and log ﬁles, which are created and written by the Oracle database,

the parameter ﬁle is opened and read by the client running the expdp image.

Therefore, a directory object name is neither required nor appropriate. The directory

path is an operating system-speciﬁc directory speciﬁcation. The default is the user's

current directory.

Restrictions

The PARFILE parameter cannot be speciﬁed within a parameter ﬁle.

Example

The content of an example parameter ﬁle, hr.par, might be as follows:

SCHEMAS=HR

DUMPFILE=exp.dmp

DIRECTORY=dpump_dir1

LOGFILE=exp.log

You could then issue the following Export command to specify the parameter ﬁle:

> expdp hr/hr parfile=hr.par

QUERY

Default: none

Purpose

Enables you to ﬁlter the data that is exported by specifying a clause for a SQL

SELECT statement, which is applied to all tables in the export job or to a speciﬁc

table.

Parameters Available in Export's Command-Line Mode

2-28 Oracle Database Utilities

Syntax and Description

QUERY = [schema.][table_name:] query_clause

The query_clause is typically a WHERE clause for ﬁne-grained row selection, but

could be any SQL clause. For example, an ORDER BY clause could be used to speed

up a migration from a heap-organized table to an index-organized table. If a

[schema.]table_name is not supplied, the query is applied to (and must be valid

for) all tables in the export job. A table-speciﬁc query overrides a query applied to

all tables.

When the query is to be applied to a speciﬁc table, a colon must separate the table

name from the query clause. More than one table-speciﬁc query can be speciﬁed,

but only one can be speciﬁed per table. Oracle highly recommends that you place

QUERY speciﬁcations in a parameter ﬁle so that you can avoid having to use

operating system-speciﬁc escape characters on the command line.

The query must be enclosed in single or double quotation marks.

When the QUERY parameter is used, the external tables method (rather than the

direct path method) is used for data access.

To specify a schema other than your own in a table-speciﬁc query, you need the

EXP_FULL_DATABASE role.

Restrictions

The QUERY parameter cannot be used in conjunction with the following parameters:

■ CONTENT=METADATA_ONLY

■ ESTIMATE_ONLY

■ TRANSPORT_TABLESPACES

Example

The following is an example of using the QUERY parameter:

> expdp hr/hr QUERY=employees:'"WHERE department_id > 10 AND salary > 10000"'

NOLOGFILE=y DIRECTORY=dpump_dir1 DUMPFILE=exp1.dmp

This example unloads all tables in the hr schema, but only the rows that ﬁt the

query expression. In this case, all rows in all tables (except employees) in the hr

schema will be unloaded. For the employees table, only rows that meet the query

criteria are unloaded.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-29

SCHEMAS

Default: current user's schema

Purpose

Speciﬁes that you want to perform a schema-mode export. This is the default mode

for Export.

Syntax and Description

SCHEMAS=schema_name [, ...]

If you have the EXP_FULL_DATABASE role, then you can specify a single schema

other than your own or a list of schema names. The EXP_FULL_DATABASE role also

allows you to export additional nonschema object information for each speciﬁed

schema so that the schemas can be re-created at import time. This additional

information includes the user deﬁnitions themselves and all associated system and

role grants, user password history, and so on.

If no mode is speciﬁed, all objects in the current user's schema are exported.

Restrictions

If you do not have the EXP_FULL_DATABASE role, then you can specify only your

own schema.

Any tables that are registered in the SYS.KU_NOEXP_TAB dictionary table are

excluded in schema mode.

Example

The following is an example of using the SCHEMAS parameter. Note that user hr is

allowed to specify more than one schema because the EXP_FULL_DATABASE role

was previously assigned to it for the purpose of these examples.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

This results in a schema-mode export in which the schemas, hr, sh, and oe will be

written to the expdat.dmp dump ﬁle located in the dpump_dir1 directory.

STATUS

Default: 0

Parameters Available in Export's Command-Line Mode

2-30 Oracle Database Utilities

Purpose

Displays detailed status of the job, along with a description of the current operation.

An estimated completion percentage for the job is also returned

Syntax and Description

STATUS=[integer]

If you supply a value for integer, it speciﬁes how frequently, in seconds, job

status should be displayed in logging mode. If no value is entered or if the default

value of 0 is used, information is displayed only upon completion of each object

type, table, or partition.

This status information is written only to your standard output device, not to the

log ﬁle (if one is in effect).

Example

The following is an example of using the STATUS parameter.

> expdp hr/hr DIRECTORY=dpump_dir1 SCHEMAS=hr,sh STATUS=300

This example will export the hr and sh schemas and display the status of the

export every 5 minutes (60 seconds x 5 = 300 seconds).

TABLES

Default: none

Purpose

Speciﬁes that you want to perform a table-mode export.

Syntax and Description

TABLES=[schema_name.]table_name[:partition_name] [, ...]

You can ﬁlter the data and metadata that is exported, by specifying a

comma-delimited list of tables and partitions or subpartitions. If a partition name is

speciﬁed, it must be the name of a partition or subpartition in the associated table.

Only the speciﬁed set of tables, partitions, and their dependent objects are

unloaded.

The table name that you specify can be preceded by a qualifying schema name. All

table names speciﬁed must reside in the same schema. The schema defaults to that

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-31

of the current user. To specify a schema other than your own, you must have the

EXP_FULL_DATABASE role.

The use of wildcards is supported for one table name per export operation. For

example, TABLES=emp% would export all tables having names that start with 'EMP.'

Restrictions

Cross-schema references are not exported. For example, a trigger deﬁned on a table

within one of the speciﬁed schemas, but that resides in a schema not explicitly

speciﬁed, is not exported.

Types used by the table are not exported in table mode. This means that if you

subsequently import the dump ﬁle and the TYPE does not already exist in the

destination database, the table creation will fail.

The use of synonyms as values for the TABLES parameter is not supported. For

example, if the regions table in the hr schema had a synonym of regn, it would

not be valid to use TABLES=regn. An error would be returned.

The export of partitions is not supported over network links.

The export of tables that include wildcards in the table name is not supported if the

table has partitions.

Examples

The following example shows a simple use of the TABLES parameter to export three

tables found in the hr schema: employees, jobs, and departments. Because

user hr is exporting tables found in the hr schema, the schema name is not needed

before the table names.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp

TABLES=employees,jobs,departments

The following example shows the use of the TABLES parameter to export partitions:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables_part.dmp

TABLES=sh.sales:sales_Q1_2000,sh.sales:sales_Q2_2000

This example exports the partitions, sales_Q1_2000 and sales_Q2_2000, from

the table sales in the schema sh.

TABLESPACES

Default: none

Parameters Available in Export's Command-Line Mode

2-32 Oracle Database Utilities

Purpose

Speciﬁes a list of tablespace names to be exported in tablespace mode.

Syntax and Description

TABLESPACES=tablespace_name [, ...]

In tablespace mode, only the tables contained in a speciﬁed set of tablespaces are

unloaded. If a table is unloaded, its dependent objects are also unloaded. If any part

of a table resides in the speciﬁed set, then that table and all of its dependent objects

are exported.

Example

The following is an example of using the TABLESPACES parameter. The example

assumes that tablespaces tbs_4, tbs_5, and tbs_6 already exist.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp

TABLESPACES=tbs_4, tbs_5, tbs_6

This results in a tablespace export in which objects (and their dependent objects)

from the speciﬁed tablespaces (tbs_4, tbs_5, and tbs_6) will be unloaded.

TRANSPORT_FULL_CHECK

Default: n

Purpose

Speciﬁes whether or not to check for dependencies between those objects inside the

transportable set and those outside the transportable set. This parameter is

applicable only to a transportable-tablespace mode export.

Syntax and Description

TRANSPORT_FULL_CHECK={y | n}

If TRANSPORT_FULL_CHECK=y, then Export veriﬁes that there are no dependencies

between those objects inside the transportable set and those outside the

transportable set. The check addresses two-way dependencies. For example, if a

table is inside the transportable set but its index is not, a failure is returned and the

export operation is terminated. Similarly, a failure is also returned if an index is in

the transportable set but the table is not.

Parameters Available in Export's Command-Line Mode

Data Pump Export 2-33

If TRANSPORT_FULL_CHECK=n, then Export veriﬁes only that there are no objects

within the transportable set that are dependent on objects outside the transportable

set. This check addresses a one-way dependency. For example, a table is not

dependent on an index, but an index is dependent on a table, because an index

without a table has no meaning. Therefore, if the transportable set contains a table,

but not its index, then this check succeeds. However, if the transportable set

contains an index, but not the table, the export operation is terminated.

In addition to this check, Export always veriﬁes that all storage segments of all

tables (and their indexes) deﬁned within the tablespace set speciﬁed by

TRANSPORT_TABLESPACES are actually contained within the tablespace set.

Example

The following is an example of using the TRANSPORT_FULL_CHECK parameter. It

assumes that tablespace tbs_1 exists.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp

TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log

TRANSPORT_TABLESPACES

Default: none

Purpose

Speciﬁes that you want to perform a transportable-tablespace-mode export.

Syntax and Description

TRANSPORT_TABLESPACES=tablespace_name [, ...]

Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace names

for which object metadata will be exported from the source database into the target

database.

Restrictions

Transportable jobs are not restartable.

Transportable jobs are restricted to a degree of parallelism of 1.

Transportable tablespace mode requires that you have the EXP_FULL_DATABASE

role.

Parameters Available in Export's Command-Line Mode

2-34 Oracle Database Utilities

Example

The following is an example of using the TRANSPORT_TABLESPACES parameter. It

assumes that tablespace tbs_1 exists.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp

TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log

VERSION

Default: COMPATIBLE

Purpose

Speciﬁes the version of database objects to be exported. This can be used to create a

dump ﬁle set that is compatible with a previous release of Oracle Database.

Syntax and Description

VERSION={COMPATIBLE | LATEST | version_string}

The legal values for the VERSION parameter are as follows:

■ COMPATIBLE - This is the default value. The version of the metadata

corresponds to the database compatibility level. Database compatibility must be

set to 9.2 or higher.

■ LATEST - The version of the metadata corresponds to the database version.

■ version_string - A speciﬁc database version (for example, 10.0.0). In Oracle

Database 10g, this value cannot be lower than 9.2.

Database objects or attributes that are incompatible with the speciﬁed version will

not be exported. For example, tables containing new datatypes that are not

supported in the speciﬁed version will not be exported.

Example

The following example shows an export for which the version of the metadata will

correspond to the database version:

See Also:

■ Transportable Tablespace Mode on page 2-5

■ Oracle Database Administrator's Guide for detailed information

about transporting tablespaces between databases

How Data Pump Export Parameters Map to Those of the Original Export Utility

Data Pump Export 2-35

> expdp hr/hr TABLES=hr.employees VERSION=LATEST DIRECTORY=dpump_dir1

DUMPFILE=emp.dmp NOLOGFILE=y

How Data Pump Export Parameters Map to Those of the Original Export

Utility

Table 2–1 maps, as closely as possible, Data Pump Export parameters to original

Export parameters. In some cases, because of feature redesign, the original Export

parameter is no longer needed, so there is no Data Pump parameter to compare it

to. Also, as shown in the table, some of the parameter names may be the same, but

the functionality is slightly different.

Table 2–1 Original Export Parameters and Their Counterparts in Data Pump Export

Original Export Parameter Comparable Data Pump Export Parameter

BUFFER

A parameter comparable to BUFFER is not needed.

COMPRESS

A parameter comparable to COMPRESS is not needed.

CONSISTENT

A parameter comparable to CONSISTENT is not needed.

Use FLASHBACK_SCN and FLASHBACK_TIME for this

functionality.

CONSTRAINTS

EXCLUDE=CONSTRAINT and INCLUDE=CONSTRAINT

DIRECT

A parameter comparable to DIRECT is not needed. Data

Pump Export automatically chooses the best method

(direct path mode or external tables mode).

FEEDBACK

STATUS

FILE

DUMPFILE

FILESIZE

FLASHBACK_SCN

FLASHBACK_TIME

FULL

GRANTS

EXCLUDE=GRANT and INCLUDE=GRANT

HELP

INDEXES

EXCLUDE=INDEX and INCLUDE=INDEX

LOG

LOGFILE

How Data Pump Export Parameters Map to Those of the Original Export Utility

2-36 Oracle Database Utilities

This table does not list all Data Pump Export command-line parameters. For

information about all Export command-line parameters, see Parameters Available in

Export's Command-Line Mode on page 2-8.

OBJECT_CONSISTENT

A parameter comparable to OBJECT_CONSISTENT is not

needed.

OWNER

SCHEMAS

PARFILE

QUERY

RECORDLENGTH

A parameter comparable to RECORDLENGTH is not needed

because sizing is done automatically.

RESUMABLE

A parameter comparable to RESUMABLE is not needed.

This functionality is automatically provided.

RESUMABLE_NAME

A parameter comparable to RESUMABLE_NAME is not

needed. This functionality is automatically provided.

RESUMABLE_TIMEOUT

A parameter comparable to RESUMABLE_TIMEOUT is not

needed. This functionality is automatically provided.

ROWS=N

CONTENT=METADATA_ONLY

ROWS=Y

CONTENT=ALL

STATISTICS

A parameter comparable to STATISTICS is not needed.

Statistics are always saved for tables.

TABLES

TABLESPACES

TABLESPACES (Same parameter; slightly different

behavior)

TRANSPORT_TABLESPACE

TRANSPORT_TABLESPACES (Same parameter; slightly

different behavior)

TRIGGERS

EXCLUDE=TRIGGER and INCLUDE=TRIGGER

TTS_FULL_CHECK

TRANSPORT_FULL_CHECK

USERID

A parameter comparable to USERID is not needed. This

information is supplied as the username/password

when you invoke Export.

VOLSIZE

A parameter comparable to VOLSIZE is not needed.

Table 2–1 (Cont.) Original Export Parameters and Their Counterparts in Data Pump

Original Export Parameter Comparable Data Pump Export Parameter

Commands Available in Export's Interactive-Command Mode

Data Pump Export 2-37

Commands Available in Export's Interactive-Command Mode

In interactive-command mode, the current job continues running, but logging to the

terminal is suspended and the Export prompt is displayed.

To start interactive-command mode, do one of the following:

■ From an attached client, press Ctrl+C.

■ From a terminal other than the one on which the job is running, specify the

ATTACH parameter in an expdp command to attach to the job. This is a useful

feature in situations in which you start a job at one location and need to check

on it at a later time from a different location.

Table 2–2 lists the activities you can perform for the current job from the Data Pump

Export prompt in interactive-command mode.

See Also: Chapter 20, "Original Export and Import" for details

about original Export

Note: Data Pump Export interactive-command mode is different

from the interactive mode for original Export, in which Export

prompted you for input. See Interactive Mode on page 20-8 for

information about interactive mode in original Export.

Table 2–2 Supported Activities in Data Pump Export's Interactive-Command Mode

Activity Command Used

Add additional dump ﬁles. ADD_FILE on page 2-38

Exit interactive mode and enter logging mode. CONTINUE_CLIENT on

page 2-39

Stop the export client session, but leave the job running. EXIT_CLIENT on page 2-39

Display a summary of available commands. HELP on page 2-39

Detach all currently attached client sessions and kill the

current job.

KILL_JOB on page 2-40

Increase or decrease the number of active worker processes

for the current job. This command is valid only in the

Enterprise Edition of Oracle Database 10g.

PARALLEL on page 2-40

Restart a stopped job to which you are attached. START_JOB on page 2-41

Commands Available in Export's Interactive-Command Mode

2-38 Oracle Database Utilities

The following are descriptions of the commands available in the

interactive-command mode of Data Pump Export.

ADD_FILE

Purpose

Adds additional ﬁles or wildcard ﬁle templates to the export dump ﬁle set.

Syntax and Description

ADD_FILE=[directory_object]file_name [,...]

The file_name must not contain any directory path information. However, it can

include a substitution variable, %U, which indicates that multiple ﬁles may be

generated using the speciﬁed ﬁlename as a template. It can also specify another

directory_object.

The size of the ﬁle being added is determined by the setting of the FILESIZE

parameter.

Example

The following example adds two dump ﬁles to the dump ﬁle set. A directory object

is not speciﬁed for the dump ﬁle named hr2.dmp so the default directory object of

dpump_dir1 is assumed. A different directory object, dpump_dir2, is speciﬁed for

the dump ﬁle named hr3.dmp.

expdp> ADD_FILE=hr2.dmp, dpump_dir2:hr3.dmp

Display detailed status for the current job and/or set status

interval.

STATUS on page 2-41

Stop the current job for later restart. STOP_JOB on page 2-42

See Also:

■ DUMPFILE on page 2-12

■ FILESIZE on page 2-17

■ File Allocation on page 1-12 for information about the effects of

using substitution variables

Table 2–2 (Cont.) Supported Activities in Data Pump Export's Interactive-Command

Activity Command Used

Commands Available in Export's Interactive-Command Mode

Data Pump Export 2-39

CONTINUE_CLIENT

Purpose

Changes the Export mode from interactive-command mode to logging mode.

Syntax and Description

CONTINUE_CLIENT

In logging mode, status is continually output to the terminal. If the job is currently

stopped, then CONTINUE_CLIENT will also cause the client to attempt to start the

job.

Example

expdp> CONTINUE_CLIENT

EXIT_CLIENT

Purpose

Stops the export client session, exits Export, and discontinues logging to the

terminal, but leaves the current job running.

Syntax and Description

EXIT_CLIENT

Because EXIT_CLIENT stops the client session but leaves the job running, you can

attach to the job at a later time. To see the status of the job, you can monitor the log

ﬁle for the job or you can query the USER_DATAPUMP_JOBS view or the

V$SESSION_LONGOPS view.

Example

expdp> EXIT_CLIENT

HELP

Purpose

Provides information about Data Pump Export commands available in

interactive-command mode.

Commands Available in Export's Interactive-Command Mode

2-40 Oracle Database Utilities

Syntax and Description

HELP

Displays information about the commands available in interactive-command mode.

Example

expdp> HELP

KILL_JOB

Purpose

Detaches all currently attached client sessions and then kills the current job. It exits

Export and returns to the terminal prompt.

Syntax and Description

KILL_JOB

A job that is killed using KILL_JOB cannot be restarted. All attached clients,

including the one issuing the KILL_JOB command, receive a warning that the job is

being killed by the current user and are then detached. After all clients are

detached, the job's process structure is immediately run down and the master table

and dump ﬁles are deleted. Log ﬁles are not deleted.

Example

expdp> KILL_JOB

PARALLEL

Purpose

Enables you to increase or decrease the number of active worker processes for the

current job.

Syntax and Description

PARALLEL=integer

PARALLEL is available as both a command-line parameter and an

interactive-command mode parameter. You set it to the desired number of parallel

processes. An increase takes effect immediately if there are sufﬁcient ﬁles and

Commands Available in Export's Interactive-Command Mode

Data Pump Export 2-41

resources. A decrease does not take effect until an existing process ﬁnishes its

current task. If the value is decreased, workers are idled but not deleted until the job

exits.

Example

PARALLEL=10

START_JOB

Purpose

Starts the current job to which you are attached.

Syntax and Description

START_JOB

The START_JOB command restarts the current job to which you are attached (the

job cannot be currently executing). The job is restarted with no data loss or

corruption after an unexpected failure or after you issued a STOP_JOB command,

provided the dump ﬁle set and master table remain undisturbed.

Transportable-tablespace-mode exports are not restartable.

Example

expdp> START_JOB

STATUS

Purpose

Displays cumulative status of the job, along with a description of the current

operation. An estimated completion percentage for the job is also returned.

Syntax and Description

STATUS[=integer]

See Also: PARALLEL on page 2-25 for more information about

parallelism

Commands Available in Export's Interactive-Command Mode

2-42 Oracle Database Utilities

You have the option of specifying how frequently, in seconds, this status should be

displayed in logging mode. If no value is entered or if the default value of 0 is used,

the periodic status display is turned off and status is displayed only once.

This status information is written only to your standard output device, not to the

log ﬁle (even if one is in effect).

Example

The following example will display the status every ﬁve minutes (300 seconds):

STATUS=300

STOP_JOB

Purpose

Stops the current job either immediately or after an orderly shutdown, and exits

Export.

Syntax and Description

STOP_JOB[=IMMEDIATE]

If the master table and dump ﬁle set are not disturbed when or after the STOP_JOB

command is issued, the job can be attached to and restarted at a later time with the

START_JOB command.

To perform an orderly shutdown, use STOP_JOB (without any associated value). A

warning requiring conﬁrmation will be issued. An orderly shutdown stops the job

after worker processes have ﬁnished their current tasks.

To perform an immediate shutdown, specify STOP_JOB=IMMEDIATE. A warning

requiring conﬁrmation will be issued. All attached clients, including the one issuing

the STOP_JOB command, receive a warning that the job is being stopped by the

current user and they will be detached. After all clients are detached, the process

structure of the job is immediately run down. That is, the master process will not

wait for the worker processes to ﬁnish their current tasks. There is no risk of

corruption or data loss when you specify STOP_JOB=IMMEDIATE. However, some

tasks that were incomplete at the time of shutdown may have to be redone at restart

time.

Example

expdp> STOP_JOB=IMMEDIATE

Examples of Using Data Pump Export

Data Pump Export 2-43

Examples of Using Data Pump Export

This section provides the following examples of using Data Pump Export:

■ Performing a Table-Mode Export

■ Data-Only Unload of Selected Tables and Rows

■ Estimating Disk Space Needed in a Table-Mode Export

■ Performing a Schema-Mode Export

■ Performing a Parallel Full Database Export

■ Using Interactive Mode to Stop and Reattach to a Job

For information that will help you to successfully use these examples, see Using the

Export Parameter Examples on page 2-8.

Performing a Table-Mode Export

Example 2–1 shows a table-mode export, speciﬁed using the TABLES parameter.

Issue the following Data Pump export command to perform a table export of the

tables employees and jobs from the human resources (hr) schema:

Example 2–1 Performing a Table-Mode Export

expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y

Because user hr is exporting tables in his own schema, it is not necessary to specify

the schema name for the tables. The NOLOGFILE=y parameter indicates that an

Export log ﬁle of the operation will not be generated.

Data-Only Unload of Selected Tables and Rows

Example 2–2 shows the contents of a parameter ﬁle (exp.par) that you could use

to perform a data-only unload of all tables in the human resources (hr) schema

except for the tables countries, locations, and regions. All rows in the

employees table are unloaded except those with a department_id not equal to

50. The rows are ordered by employee_id.

Example 2–2 Data-Only Unload of Selected Tables and Rows

DIRECTORY=dpump_dir1

DUMPFILE=dataonly.dmp

CONTENT=DATA_ONLY

Examples of Using Data Pump Export

2-44 Oracle Database Utilities

EXCLUDE=TABLE:"IN ('COUNTRIES', 'LOCATIONS', 'REGIONS')"

QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"

You can issue the following command to execute the exp.par parameter ﬁle:

> expdp hr/hr PARFILE=exp.par

A schema-mode export (the default mode) is performed, but the CONTENT

parameter effectively limits the export to an unload of just the tables. The DBA

previously created the directory object dpump_dir1 which points to the directory

on the server where user hr is authorized to read and write export dump ﬁles. The

dump ﬁle dataonly.dmp is created in dpump_dir1.

Estimating Disk Space Needed in a Table-Mode Export

Example 2–3 shows the use of the ESTIMATE_ONLY parameter to estimate the space

that would be consumed in a table-mode export, without actually performing the

export operation. Issue the following command to use the BLOCKS method to

estimate the number of bytes required to export the data in the following three

tables located in the human resource (hr) schema: employees, departments, and

locations.

Example 2–3 Estimating Disk Space Needed in a Schema-Mode Export

> expdp hr/hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees,

departments, locations LOGFILE=estimate.log

The estimate is printed in the log ﬁle and displayed on the client's standard output

device. The estimate is for table row data only; it does not include metadata.

Performing a Schema-Mode Export

Example 2–4 shows a schema-mode export of the hr schema. In a schema-mode

export, only objects belonging to the corresponding schemas are unloaded. Because

schema mode is the default mode, it is not necessary to specify the SCHEMAS

parameter on the command line, unless you are specifying more than one schema

or a schema other than your own.

Example 2–4 Performing a Schema Mode Export

> expdp hr/hr DUMPFILE=dpump_dir1:expschema.dmp LOGFILE=dpump_dir1:expschema.log

Examples of Using Data Pump Export

Data Pump Export 2-45

Performing a Parallel Full Database Export

Example 2–5 shows a full database Export that will have 3 parallel worker

processes.

Example 2–5 Parallel Full Export

> expdp hr/hr FULL=y DUMPFILE=dpump_dir1:full1%U.dmp, dpump_dir2:full2%U.dmp

FILESIZE=2G PARALLEL=3 LOGFILE=dpump_dir1:expfull.log JOB_NAME=expfull

Because this is a full database export, all data and metadata in the database will be

exported. Dump ﬁles full101.dmp, full201.dmp, full102.dmp, and so on will

be created in a round-robin fashion in the directories pointed to by the dpump_

dir1 and dpump_dir2 directory objects. For best performance, these should be on

separate I/O channels. Each ﬁle will be up to 2 gigabytes in size, as necessary.

Initially, up to three ﬁles will be created. More ﬁles will be created, if needed. The

job and master table will have a name of expfull. The log ﬁle will be written to

expfull.log in the dpump_dir1 directory.

Using Interactive Mode to Stop and Reattach to a Job

To start this example, reexecute the parallel full export in Example 2–5. While the

export is running, press Ctrl+C. This will start the interactive-command interface of

Data Pump Export. In the interactive interface, logging to the terminal stops and the

Export prompt is displayed.

Issue the following command to stop the job:

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([y]/n): y

The job is placed in a stopped state and exits the client. Example 2–6 shows how to

reattach to the job.

Example 2–6 Attaching to a Stopped Job

Enter the following command to reattach to the job you just stopped:

> expdp hr/hr ATTACH=EXPFULL

After the job status is displayed, you can issue the CONTINUE_CLIENT command to

resume logging mode and restart the expfull job.

Export> CONTINUE_CLIENT

Syntax Diagrams for Data Pump Export

2-46 Oracle Database Utilities

A message is displayed that the job has been reopened, and processing status is

output to the client.

Syntax Diagrams for Data Pump Export

This section provides syntax diagrams for Data Pump Export. These diagrams use

standard SQL syntax notation. For more information about SQL syntax notation, see

Oracle Database SQL Reference.

ExpInit

ExpStart

expdp

HELP = Y

username / password

@ connect_string AS SYSDBA

ExpStart

ExpModes ExpOpts

ATTACH

schema_name .

job_name

Syntax Diagrams for Data Pump Export

Data Pump Export 2-47

ExpModes

FULL = Y

SCHEMAS = schema_name

TABLES =

schema_name .

table_name

: partition_name

TABLESPACES = tablespace_name

TRANSPORT_TABLESPACES = tablespace_name

TRANSPORT_FULL_CHECK =

Syntax Diagrams for Data Pump Export

2-48 Oracle Database Utilities

ExpOpts

ExpFileOpts

CONTENT =

ALL

DATA_ONLY

METADATA_ONLY

ESTIMATE =

BLOCKS

STATISTICS

ESTIMATE_ONLY =

EXCLUDE = object_type

: name_clause

FLASHBACK_SCN = scn_value

FLASHBACK_TIME = date

INCLUDE = object_type

: name_clause

JOB_NAME = jobname_string

NETWORK_LINK = database_link

PARALLEL = integer

QUERY =

schema_name .

table_name :

query_clause

STATUS = integer

VERSION =

COMPATIBLE

LATEST

version_string

Syntax Diagrams for Data Pump Export

Data Pump Export 2-49

ExpFileOpts

ExpDynOpts

DIRECTORY = directory_object

DUMPFILE =

directory_object :

file_name

FILESIZE = number_of_bytes

LOGFILE =

directory_object :

file_name

NOLOGFILE =

PARFILE =

directory_path

file_name

ADD_FILE =

directory_object :

file_name

CONTINUE_CLIENT

EXIT_CLIENT

HELP

KILL_JOB

PARALLEL = integer

START_JOB

STATUS

= integer

STOP_JOB

= IMMEDIATE

Syntax Diagrams for Data Pump Export

2-50 Oracle Database Utilities

Data Pump Import 3-1

Data Pump Import

This chapter describes the Oracle Data Pump Import utility. The following topics

are discussed:

■ What Is Data Pump Import?

■ Invoking Data Pump Import

■ Filtering During Import Operations

■ Parameters Available in Import's Command-Line Mode

■ How Data Pump Import Parameters Map to Those of the Original Import

Utility

■ Commands Available in Import's Interactive-Command Mode

■ Examples of Using Data Pump Import

■ Syntax Diagrams for Data Pump Import

What Is Data Pump Import?

Data Pump Import (hereinafter referred to as Import for ease of reading) is a utility

for loading an export dump ﬁle set into a target system. The dump ﬁle set is made

Note: Data Pump Import (invoked with the impdp command) is a

new utility as of Oracle Database 10g. Although its functionality

and its parameters are similar to those of the original Import utility

(imp), they are completely separate utilities and their ﬁles are not

compatible. See Chapter 20, "Original Export and Import" for a

description of the original Import utility.

Invoking Data Pump Import

3-2 Oracle Database Utilities

up of one or more disk ﬁles that contain table data, database object metadata, and

control information. The ﬁles are written in a proprietary, binary format. During an

import operation, the Data Pump Import utility uses these ﬁles to locate each

database object in the dump ﬁle set.

Import can also be used to load a target database directly from a source database

with no intervening dump ﬁles. This allows export and import operations to run

concurrently, minimizing total elapsed time. This is known as a network import.

Data Pump Import enables you to specify whether a job should move a subset of

the data and metadata from the dump ﬁle set or the source database (in the case of a

network import), as determined by the import mode. This is done using data ﬁlters

and metadata ﬁlters, which are implemented through Import commands. See

Filtering During Import Operations on page 3-6.

To see some examples of the various ways in which you can use Import, refer to

Examples of Using Data Pump Import on page 3-49.

Invoking Data Pump Import

The Data Pump Import utility is invoked using the impdp command. The

characteristics of the import operation are determined by the import parameters

you specify. These parameters can be speciﬁed either on the command line or in a

parameter ﬁle.

The following sections contain more information about invoking Import:

■ Data Pump Import Interfaces on page 3-2

■ Data Pump Import Modes on page 3-3

■ Network Considerations on page 3-5

Data Pump Import Interfaces

You can interact with Data Pump Import by using a command line, a parameter ﬁle,

or an interactive-command mode.

Note: Do not invoke Import 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.

Invoking Data Pump Import

Data Pump Import 3-3

■ Command-Line Interface: Enables you to specify the Import parameters directly

on the command line. For a complete description of the parameters available in

the command-line interface, see Parameters Available in Import's

Command-Line Mode on page 3-7.

■ Parameter File Interface: Enables you to specify command-line parameters in a

parameter ﬁle. The only exception is the PARFILE parameter because

parameter ﬁles cannot be nested.

■ Interactive-Command Interface: Stops logging to the terminal and displays the

Import prompt, from which you can enter various commands, some of which

are speciﬁc to interactive-command mode. This mode is enabled by pressing

Ctrl+C during an import operation started with the command-line interface or

the parameter ﬁle interface. Interactive-command mode is also enabled when

you attach to an executing or stopped job.

For a complete description of the commands available in interactive-command

mode, see Commands Available in Import's Interactive-Command Mode on

page 3-44.

Data Pump Import Modes

One of the most signiﬁcant characteristics of an import operation is its mode,

because the mode largely determines what is imported. The speciﬁed mode applies

to the source of the operation, either a dump ﬁle set or another database if the

NETWORK_LINK parameter is speciﬁed.

When the source of the import operation is a dump ﬁle set, specifying a mode is

optional. If no mode is speciﬁed, then Import attempts to load the entire dump ﬁle

set in the mode in which the export operation was run.

The mode is speciﬁed on the command line, using the appropriate parameter. The

available modes are as follows:

■ Full Import Mode on page 3-4

■ Schema Mode on page 3-4

■ Table Mode on page 3-4

■ Tablespace Mode on page 3-4

■ Transportable Tablespace Mode on page 3-5

Invoking Data Pump Import

3-4 Oracle Database Utilities

Full Import Mode

A full import is speciﬁed using the FULL parameter. In full import mode, the entire

content of the source (dump ﬁle set or another database) is loaded into the target

database. This is the default for ﬁle-based imports. You must have the IMP_FULL_

DATABASE role if the source is another database or if the export operation that

generated the dump ﬁle set required the EXP_FULL_DATABASE role.

Schema Mode

A schema import is speciﬁed using the SCHEMAS parameter. In a schema import,

only objects owned by the current user are loaded. The source can be a full or

schema-mode export dump ﬁle set or another database. If you have the IMP_FULL_

DATABASE role, then a single schema other than your own schema, or a list of

schemas can be speciﬁed and the schemas themselves (including system privilege

grants) are created in the database in addition to the objects contained within those

schemas.

Cross-schema references are not imported. For example, a trigger deﬁned on a table

within one of the speciﬁed schemas, but residing in a schema not explicitly

speciﬁed, is not imported.

Table Mode

A table-mode import is speciﬁed using the TABLES parameter. In table mode, only

the speciﬁed set of tables, partitions, and their dependent objects are loaded. The

source can be a full, schema, tablespace, or table-mode export dump ﬁle set or

another database. You must have the IMP_FULL_DATABASE role to specify tables

that are not in your own schema.

Tablespace Mode

A tablespace-mode import is speciﬁed using the TABLESPACES parameter. In

tablespace mode, all objects contained within the speciﬁed set of tablespaces are

loaded. The source can be a full, schema, tablespace, or table-mode export dump ﬁle

set or another database.

See Also:

■ TRANSPORT_TABLESPACES on page 3-41

■ TRANSPORT_FULL_CHECK on page 3-40

See Also:

■ NETWORK_LINK on page 3-22

■ Oracle Net Services Administrator's Guide

■ Oracle Database Heterogeneous Connectivity Administrator's Guide

Filtering During Import Operations

3-6 Oracle Database Utilities

Filtering During Import Operations

Data Pump Import provides much greater data and metadata ﬁltering capability

than was provided by the original Import utility.

Data Filters

Data ﬁlters specify restrictions on the rows that are to be imported. These

restrictions can be based on partition names and on the results of subqueries.

Each data ﬁlter can only be speciﬁed once per table and once per job. If different

ﬁlters using the same name are applied to both a particular table and to the whole

job, the ﬁlter parameter supplied for the speciﬁc table will take precedence.

Metadata Filters

Data Pump Import provides much greater metadata ﬁltering capability than was

provided by the original Import utility. Metadata ﬁltering is implemented through

the EXCLUDE and INCLUDE parameters. The EXCLUDE and INCLUDE parameters

are mutually exclusive.

Metadata ﬁlters identify a set of objects to be included or excluded from a Data

Pump operation. For example, you could request a full import, but without Package

Speciﬁcations or Package Bodies.

To use ﬁlters correctly and to get the results you expect, remember that dependent

objects of an identiﬁed object are processed along with the identiﬁed object. For example, if

a ﬁlter speciﬁes that a package is to be included in an operation, then grants upon

that package will also be included. Likewise, if a table is excluded by a ﬁlter, then

indexes, constraints, grants, and triggers upon the table will also be excluded by the

ﬁlter.

If multiple ﬁlters are speciﬁed for an object type, an implicit AND operation is

applied to them. That is, objects participating in the job must pass all of the ﬁlters

applied to their object types.

The same ﬁlter name can be speciﬁed multiple times within a job.

To see which objects can be ﬁltered, you can perform queries on the following

views: DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_

EXPORT_OBJECTS. For an example of this, see Metadata Filters on page 2-6.

See Also: EXCLUDE on page 3-13 and INCLUDE on page 3-18

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-7

Parameters Available in Import's Command-Line Mode

This section provides descriptions of the parameters available in the command-line

mode of Data Pump Import. Many of the descriptions include an example of how to

use the parameter.

Using the Import Parameter Examples

If you try running the examples that are provided for each parameter, be aware of

the following requirements:

■ Most of the examples use the sample schemas of the seed database, which is

installed by default when you install Oracle Database. In particular, the human

resources (hr) schema is often used.

■ Examples that specify a dump ﬁle to import assume that the dump ﬁle exists.

Wherever possible, the examples use dump ﬁles that are generated when you

run the Export examples in Chapter 2.

■ The examples assume that the directory objects, dpump_dir1 and dpump_

dir2, already exist and that READ and WRITE privileges have been granted to

the hr schema for these directory objects. See Default Locations for Dump, Log,

and SQL Files on page 1-13 for information about creating directory objects and

assigning privileges to them.

■ Some of the examples require the EXP_FULL_DATABASE and IMP_FULL_

DATABASE roles. The examples assume that the hr schema has been granted

these roles.

If necessary, ask your DBA for help in creating these directory objects and assigning

the necessary privileges and roles.

Syntax diagrams of these parameters are provided in Syntax Diagrams for Data

Pump Import on page 3-51.

Unless speciﬁcally noted, these parameters can also be speciﬁed in a parameter ﬁle.

Command-Line Escape Characters in Examples

Some of the examples in this chapter may show certain clauses enclosed in

quotation marks and backslashes. This is because those clauses contain a blank, a

situation for which most operating systems require that the entire string be placed

in quotation marks or marked as a literal by some method. Some operating systems

also require that quotation marks on the command line be preceded by an escape

character, such as the backslash. If the backslashes were not present, the

command-line parser that Import uses would not understand the quotation marks

Parameters Available in Import's Command-Line Mode

3-8 Oracle Database Utilities

and would remove them. Some examples in this chapter may use escape characters

to show you how they would be used. However, in general, Oracle recommends

that you place such statements in a parameter ﬁle because escape characters are not

necessary in parameter ﬁles.

ATTACH

Default: current job in user's schema, if there is only one running job

Purpose

Attaches the client session to an existing import job and automatically places you in

interactive-command mode.

Syntax and Description

ATTACH [=[schema_name.]job_name]

Specify a schema_name if the schema to which you are attaching is not your own.

You must have the IMP_FULL_DATABASE role to do this.

A job_name does not have to be speciﬁed if only one running job is associated

with your schema and the job is active. If the job you are attaching to is stopped,

you must supply the job name. To see a list of Data Pump job names, you can query

the DBA_DATAPUMP_JOBS view or the USER_DATAPUMP_JOBS view.

When you are attached to the job, Import displays a description of the job and then

displays the Import prompt.

See Also:

■ Default Locations for Dump, Log, and SQL Files on page 1-13

for information about creating default directory objects

■ Examples of Using Data Pump Import on page 3-49

■ Oracle Database Sample Schemas

Note: If you are accustomed to using the original Import utility,

you may be wondering which Data Pump parameters are used to

perform the operations you used to perform with original Import.

For a comparison, see How Data Pump Import Parameters Map to

Those of the Original Import Utility on page 3-42.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-9

Restrictions

When you specify the ATTACH parameter, you cannot specify any other parameters

except for the connection string (user/password).

You cannot attach to a job in another schema unless it is already running.

Example

The following is an example of using the ATTACH parameter.

> impdp hr/hr ATTACH=import_job

This example assumes that a job named import_job exists in the hr schema.

CONTENT

Default: ALL

Purpose

Enables you to ﬁlter what is loaded during the import operation.

Syntax and Description

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

■ ALL loads any data and metadata contained in the source. This is the default.

■ DATA_ONLY loads only table row data into existing tables; no database objects

are created.

■ METADATA_ONLY loads only database object deﬁnitions; no table row data is

loaded.

Example

The following is an example of using the CONTENT parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp CONTENT=METADATA_ONLY

See Also: Commands Available in Import's Interactive-Command

Mode on page 3-44

Parameters Available in Import's Command-Line Mode

3-10 Oracle Database Utilities

This command will execute a full import that will load only the metadata in the

expfull.dmp dump ﬁle. It executes a full import because that is the default for

ﬁle-based imports in which no import mode is speciﬁed.

See Also:

■ Default Locations for Dump, Log, and SQL Files on page 1-13

for more information about default directory objects

■ Oracle Database SQL Reference for more information about the

CREATE DIRECTORY command

Parameters Available in Import's Command-Line Mode

3-12 Oracle Database Utilities

Because a directory object (dpump_dir2) is speciﬁed for the exp1.dmp dump ﬁle,

the import job will look there for the ﬁle. It will also look in dpump_dir1 for any

dump ﬁles of the form exp2<nn>.dmp. The log ﬁle will be written to dpump_dir1.

ESTIMATE

Default: BLOCKS

Purpose

Instructs the source system in a network import operation to estimate how much

data will be generated.

Syntax and Description

ESTIMATE={BLOCKS | STATISTICS}

The valid choices for the ESTIMATE parameter are as follows:

■ BLOCKS - The estimate is calculated by multiplying the number of database

blocks used by the target objects times the appropriate block sizes.

■ STATISTICS - The estimate is calculated using statistics for each table. For this

method to be as accurate as possible, all tables should have been analyzed

recently.

The estimate that is generated can be used to determine a percentage complete

throughout the execution of the import job. When the import source is a dump ﬁle

set, the amount of data to be loaded is already known, so the percentage complete is

automatically calculated.

Restrictions

The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is

also speciﬁed.

Example

In the following example, source_database_link would be replaced with the

name of a valid link to the source database.

See Also:

■ File Allocation on page 1-12

■ Performing a Data-Only Table-Mode Import on page 3-50

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-13

> impdp hr/hr TABLES=job_history NETWORK_LINK=source_database_link

DIRECTORY=dpump_dir1 ESTIMATE=statistics

The job_history table in the hr schema is imported from the source database. A

log ﬁle is created by default and written to the directory pointed to by the dpump_

dir1 directory object. When the job begins, an estimate for the job is calculated

based on table statistics.

EXCLUDE

Default: none

Purpose

Enables you to ﬁlter the metadata that is imported by specifying objects and object

types that you want to exclude from the import job.

Syntax and Description

EXCLUDE=object_type[:name_clause] [, ...]

For the given mode of import, all object types contained within the source (and

their dependents) are included, except those speciﬁed in an EXCLUDE statement. If

an object is excluded, all of its dependent objects are also excluded. For example,

excluding a table will also exclude all indexes and triggers on the table.

The name_clause is optional. It allows ﬁne-grained selection of speciﬁc objects

within an object type. It is a SQL expression used as a ﬁlter on the object names of

the type. It consists of a SQL operator and the values against which the object

names of the speciﬁed type are to be compared. The name clause applies only to

object types whose instances have names (for example, it is applicable to TABLEand

VIEW, but not to GRANT). The optional name clause must be separated from the

object type with a colon and enclosed in double quotation marks, because

single-quotation marks are required to delimit the name strings. For example, you

could set EXCLUDE=INDEX:"LIKE 'DEPT%'" to exclude all indexes whose names

start with dept.

More than one EXCLUDE statement can be speciﬁed. Oracle recommends that you

place EXCLUDE statements in a parameter ﬁle to avoid having to use operating

system-speciﬁc escape characters on the command line.

As explained in the following sections, you should be aware of the effects of

specifying certain objects for exclusion, in particular, CONSTRAINT, GRANT, and

USER.

Parameters Available in Import's Command-Line Mode

3-14 Oracle Database Utilities

Excluding Constraints

The following constraints cannot be excluded:

■ NOT NULL constraints.

■ Constraints needed for the table to be created and loaded successfully (for

example, primary key constraints for index-organized tables or REF SCOPE and

WITH ROWID constraints for tables with REF columns).

This means that the following EXCLUDE statements will be interpreted as follows:

■ EXCLUDE=CONSTRAINT will exclude all nonreferential constraints, except for

NOT NULL constraints and any constraints needed for successful table creation

and loading.

■ EXCLUDE=REF_CONSTRAINT will exclude referential integrity (foreign key)

constraints.

Excluding Grants and Users

Specifying EXCLUDE=GRANT excludes object grants on all object types and system

privilege grants.

Specifying EXCLUDE=USER excludes only the deﬁnitions of users, not the objects

contained within users' schemas.

To exclude a speciﬁc user and all objects of that user, specify a ﬁlter such as the

following (where hr is the schema name of the user you want to exclude):

EXCLUDE=SCHEMA:"= 'HR'"

If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'HR'",

only CREATE USER hr DDL statements will be excluded, and you may not get the

results you expect.

Restrictions

The EXCLUDE and INCLUDE parameters are mutually exclusive.

Neither EXCLUDE nor INCLUDE can be used if the CONTENT=DATA_ONLY

parameter is speciﬁed, because that implies import of row data only.

Example

Assume the following is in a parameter ﬁle, exclude.par, being used by a DBA or

some other user with the IMP_FULL_DATABASE role. (If you want to try the

example, you will need to create this ﬁle.)

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-15

EXCLUDE=FUNCTION

EXCLUDE=PROCEDURE

EXCLUDE=PACKAGE

EXCLUDE=INDEX:"LIKE 'EMP%' "

You could then issue the following command. You can create the expfull.dmp

dump ﬁle used in this command by running the example provided for the Export

FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp PARFILE=exclude.par

All data from the expfull.dmp dump ﬁle will be loaded except for functions,

procedures, packages, and indexes whose names start with emp.

FLASHBACK_SCN

Default: none

Purpose

Speciﬁes the system change number (SCN) that Import will use to enable the

Flashback utility.

Syntax and Description

FLASHBACK_SCN=scn_number

The import operation is performed with data that is consistent as of the speciﬁed

scn_number.

See Also: Filtering During Import Operations on page 3-6 for

more information about the effects of using the EXCLUDE

parameter

Note: If you are on a logical standby system, the FLASHBACK_SCN

parameter is ignored because SCNs are selected by logical standby.

See Oracle Data Guard Concepts and Administration for information

about logical standby databases.

Parameters Available in Import's Command-Line Mode

3-16 Oracle Database Utilities

Restrictions

The FLASHBACK_SCN parameter is valid only when the NETWORK_LINK parameter

is also speciﬁed. This is because the value is passed to the source system to provide

an SCN-consistent data extraction.

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.

Example

The following is an example of using the FLASHBACK_SCN parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 FLASHBACK_SCN=scn_number

NETWORK_LINK=source_database_link

The source_database_link in this example would be replaced with the name of

a source database from which you were importing data. Also, an actual integer

value would be supplied for the scn_number.

FLASHBACK_TIME

Default: none

Purpose

Speciﬁes the time of a particular SCN.

Syntax and Description

FLASHBACK_TIME="TO_TIMESTAMP()"

The SCN that most closely matches the speciﬁed time is found, and this SCN is

used to enable the Flashback utility. The import operation is performed with data

that is consistent as of this SCN.

Restrictions

This parameter is valid only when the NETWORK_LINK parameter is also speciﬁed.

This is because the value is passed to the source system to provide a time-consistent

import.

Note: If you are on a logical standby system, the FLASHBACK_

TIME parameter is ignored because SCNs are selected by logical

standby. See Oracle Data Guard Concepts and Administration for

information about logical standby databases.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-17

FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.

Example

You can specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_

TIME procedure accepts, for example:

> impdp hr/hr DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link

FLASHBACK_TIME="TO_TIMESTAMP('25-08-2003 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

This example may require the use of escape characters, depending on your

operating system. See Command-Line Escape Characters in Examples on page 3-7.

FULL

Default: Y

Purpose

Speciﬁes that you want to perform a full database import.

Syntax and Description

FULL=y

If you specify FULL=y, then everything from the source (either a dump ﬁle set or

another database) is imported.

The IMP_FULL_DATABASE role is required if the NETWORK_LINK parameter is also

used or if the export operation that generated the dump ﬁle set required the EXP_

FULL_DATABASE role.

Example

The following is an example of using the FULL parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y

LOGFILE=dpump_dir2:full_imp.log

This example imports everything from the expfull.dmp dump ﬁle. In this

example, a DIRECTORY parameter is not provided. Therefore, a directory object

See Also: Oracle Database Application Developer's Guide -

Fundamentals for information about using ﬂashback

Parameters Available in Import's Command-Line Mode

3-18 Oracle Database Utilities

must be provided on both the DUMPFILE parameter and the LOGFILE parameter.

The directory objects can be different, as shown in this example.

HELP

Default: n

Purpose

Displays online help for the Import utility.

Syntax and Description

HELP=y

If HELP=y is speciﬁed, Import displays a summary of all Import command-line

parameters and interactive commands.

Example

> impdp HELP = Y

This example will display a brief description of all Import parameters and

commands.

INCLUDE

Default: none

Purpose

Enables you to ﬁlter the metadata that is imported by specifying objects and object

types for the current import mode.

Syntax and Description

INCLUDE = object_type[:name_clause] [, ...]

Only object types in the source that are explicitly speciﬁed in the INCLUDE

statement are imported.

The name_clause is optional. It allows ﬁne-grained selection of speciﬁc objects

within an object type. It is a SQL expression used as a ﬁlter on the object names of

the type. It consists of a SQL operator and the values against which the object

names of the speciﬁed type are to be compared. The name clause applies only to

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-19

object types whose instances have names (for example, it is applicable to TABLE,

but not to GRANT). The optional name clause must be separated from the object type

with a colon and enclosed in double quotation marks, because single-quotation

marks are required to delimit the name strings.

More than one INCLUDE statement can be speciﬁed. Oracle recommends that you

place INCLUDE statements in a parameter ﬁle to avoid having to use operating

system-speciﬁc escape characters on the command line.

To see a list of valid paths for use with the INCLUDE parameter, you can query the

following views: DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and

TABLE_EXPORT_OBJECTS.

Restrictions

The INCLUDE and EXCLUDE parameters are mutually exclusive.

Neither INCLUDE nor EXCLUDE can be used if the CONTENT=DATA_ONLY

parameter is speciﬁed, because that implies import of table row data only.

Example

Assume the following is in a parameter ﬁle, imp_include.par, being used by a

DBA or some other user with the IMP_FULL_DATABASE role:

INCLUDE=FUNCTION

INCLUDE=PROCEDURE

INCLUDE=PACKAGE

INCLUDE=INDEX:"LIKE 'EMP%' "

You can then issue the following command:

> impdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

PARFILE=imp_include.par

You can create the expfull.dmp dump ﬁle used in this example by running the

example provided for the Export FULL parameter. See FULL on page 2-19.

The Import operation will load only functions, procedures, and packages from the

hr schema and indexes whose names start with EMP. Although this is a

privileged-mode import (the user must have the IMP_FULL_DATABASE role), the

schema deﬁnition is not imported, because the USER object type was not speciﬁed

in an INCLUDE statement.

Parameters Available in Import's Command-Line Mode

3-20 Oracle Database Utilities

JOB_NAME

Default: system-generated name of the form SYS_<operation>_<mode>_NN

Purpose

Speciﬁes a name for the import job. The job name is used to identify the import job

in subsequent actions, such as ATTACH. The job name becomes the name of the

master table in the current user's schema. The master table controls the import job.

Syntax and Description

JOB_NAME=jobname_string

The jobname_string speciﬁes a name of up to 30 bytes for this import job. The

bytes must represent printable characters and spaces. If spaces are included, the

name must be enclosed in single quotation marks (for example, 'Thursday Import').

The job name is implicitly qualiﬁed by the schema of the user performing the

import operation.

The default job name is system-generated in the form SYS_<operation>_

<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01. An

example of a default name is 'SYS_IMPORT_TABLESPACE_02'.

Example

The following is an example of using the JOB_NAME parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp JOB_NAME=impjob01

LOGFILE

Default: import.log

Purpose

Speciﬁes the name, and optionally, a directory object, for the log ﬁle of the import

job.

Syntax and Description

LOGFILE=[directory_object:]file_name

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-21

If you specify a directory_object, it must be one that was previously

established by the DBA. This overrides the directory object speciﬁed with the

DIRECTORY parameter. The default behavior is to create import.log in the

directory referenced by the directory object speciﬁed in the DIRECTORY parameter.

If the file_name you specify already exists, it will be overwritten.

All messages regarding work in progress, work completed, and errors encountered

are written to the log ﬁle. (For a real-time status of the job, use the STATUS

command in interactive mode.)

A log ﬁle is always created unless the NOLOGFILE parameter is speciﬁed. As with

the dump ﬁle set, the log ﬁle is relative to the server and not the client.

Example

The following is an example of using the LOGFILE parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr SCHEMAS=HR DIRECTORY=dpump_dir2 LOGFILE=imp.log

DUMPFILE=dpump_dir1:expfull.dmp

The DUMPFILE parameter speciﬁes a directory object (dpump_dir1) and a dump

ﬁle name (expfull.dmp). The directory object speciﬁed on the DUMPFILE

parameter overrides the directory object speciﬁed on the DIRECTORY parameter.

Because no directory object is speciﬁed on the LOGFILE parameter, the log ﬁle is

written to the directory object speciﬁed on the DIRECTORY parameter.

Note: Data Pump Import writes the log ﬁle using the database

character set. If your client NLS_LANG environment sets up a

different client character set from the database character set, then it

is possible that table names may be different in the log ﬁle than

they are when displayed on the client output screen.

See Also:

■ STATUS on page 3-48

■ Using Directory Objects When Automatic Storage Management

Is Enabled on page 1-14 for information about the effect of

Automatic Storage Management on the location of log ﬁles

Parameters Available in Import's Command-Line Mode

3-22 Oracle Database Utilities

NETWORK_LINK

Default: none

Purpose

Enables a network import when you specify the name of a valid database link to a

source system.

Syntax and Description

NETWORK_LINK=source_database_link

The NETWORK_LINK parameter initiates a network import. This means that the

impdp client initiates the import request, typically to the local database. That server

contacts the remote source database referenced by source_database_link,

retrieves the data, and writes it directly back to the target database. There are no

dump ﬁles involved.

The source_database_link provided must be the name of a valid link to a

source database. If the database does not already have a database link, you or your

DBA must create one. For more information about the CREATE DATABASE LINK

statement, see Oracle Database SQL Reference.

If the source database is read-only, then the user on the source database must have a

locally-managed tablespace assigned as a default temporary tablespace. Otherwise,

the job will fail. For further details about this, see the information about creating

locally managed temporary tablespaces in the Oracle Database Administrator's Guide.

This parameter is required when any of the following parameters are speciﬁed:

FLASHBACK_SCN, FLASHBACK_TIME, ESTIMATE, or TRANSPORT_TABLESPACES.

Restrictions

Network imports do not support the use of evolved types.

Example

In the following example, the source_database_link would be replaced with

the name of a valid database link that must already exist.

> impdp hr/hr TABLES=employees DIRECTORY=dpump_dir1

NETWORK_LINK=source_database_link EXCLUDE=CONSTRAINT

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-23

This example results in an import of the employees table (excluding constraints)

from the source database. The log ﬁle is written to dpump_dir1, speciﬁed on the

DIRECTORY parameter.

NOLOGFILE

Default: n

Purpose

Speciﬁes whether or not to suppress the default behavior of creating a log ﬁle.

Syntax and Description

NOLOGFILE={y | n}

If you specify NOLOGFILE=Y to suppress creation of a log ﬁle, progress and error

information is still written to the standard output device of any attached clients,

including the client that started the original export operation. If there are no clients

attached to a running job and you specify NOLOGFILE=Y, you run the risk of losing

important progress and error information.

Example

The following is an example of using the NOLOGFILE parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp NOLOGFILE=Y

This command results in a full mode import (the default for ﬁle-based imports) of

the expfull.dmp dump ﬁle. No log ﬁle is written because NOLOGFILE is set to y.

PARALLEL

Default: 1

Purpose

Speciﬁes the maximum number of threads of active execution operating on behalf

of the import job.

Syntax and Description

PARALLEL=integer

Parameters Available in Import's Command-Line Mode

3-24 Oracle Database Utilities

The value you specify for integer speciﬁes the maximum number of threads of

active execution operating on behalf of the import job. This execution set consists of

a combination of worker processes and parallel I/O server processes. The master

control process, idle workers, and worker processes acting as parallel execution

coordinators in parallel I/O operations do not count toward this total. This

parameter enables you to make trade-offs between resource consumption and

elapsed time.

If the source of the import is a dump ﬁle set consisting of ﬁles, multiple processes

can read from the same ﬁle, but performance may be limited by I/O contention.

To increase or decrease the value of PARALLEL during job execution, use

interactive-command mode.

Parallelism is used to best advantage for loading user data and package bodies, and

for building indexes.

Restrictions

This parameter is valid only in the Enterprise Edition of Oracle Database 10g.

Example

The following is an example of using the PARALLEL parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_import.log

JOB_NAME=imp_par3 DUMPFILE=par_exp%U.dmp PARALLEL=3

This command imports the dump ﬁle set that is created when you run the example

for the Export PARALLEL parameter. (See PARALLEL on page 2-25.) The names of

the dump ﬁles are par_exp01.dmp, par_exp02.dmp, and par_exp03.dmp.

The import job looks for the dump ﬁles in the location indicated by the dpump_

dir1 directory object speciﬁed on the DIRECTORY parameter. This is also the

location to which the log ﬁle is written.

PARFILE

Default: none

Purpose

Speciﬁes the name of an import parameter ﬁle.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-25

Syntax and Description

PARFILE=[directory_path]file_name

Unlike dump ﬁles, log ﬁles, and SQL ﬁles which are created and written by the

server, the parameter ﬁle is opened and read by the client running the impdp

image. Therefore, a directory object name is neither required nor appropriate. The

default is the user's current directory.

Restrictions

The PARFILE parameter cannot be speciﬁed within a parameter ﬁle.

Example

The content of an example parameter ﬁle, hr_imp.par, might be as follows:

TABLES= countries, locations, regions

DUMPFILE=dpump_dir2:exp1.dmp,exp2%U.dmp

DIRECTORY=dpump_dir1

PARALLEL=3

You could then issue the following command to execute the parameter ﬁle:

> impdp hr/hr PARFILE=hr_imp.par

The tables named countries, locations, and regions will be imported from

the dump ﬁle set that is created when you run the example for the Export

DUMPFILE parameter. (See DUMPFILE on page 2-12.) The import job looks for the

exp1.dmp ﬁle in the location pointed to by dpump_dir2. It looks for any dump

ﬁles of the form exp2<nn>.dmp in the location pointed to by dpump_dir1. The log

ﬁle for the job will also be written to dpump_dir1.

QUERY

Default: none

Purpose

Enables you to ﬁlter the data that is imported by specifying a SQL SELECT

statement, which is applied to all tables in the import job or to a speciﬁc table.

Syntax and Description

QUERY=[[schema_name.]table_name:]query_clause

Parameters Available in Import's Command-Line Mode

3-26 Oracle Database Utilities

The query_clause is typically a WHERE clause for ﬁne-grained row selection, but

could be any SQL clause.

If a schema and table are not supplied, the query is applied to (and must be valid

for) all tables in the source dump ﬁle set or database.

When the query is to be applied to a speciﬁc table, a colon must separate the table

name from the query clause. More than one table-speciﬁc query can be speciﬁed,

but only one can be speciﬁed per table.

The query must be enclosed in single or double quotation marks. Double quotation

marks are recommended, because strings within the clause must be enclosed in

single quotation marks.

Oracle recommends that you place QUERY speciﬁcations in a parameter ﬁle to avoid

having to use operating system-speciﬁc escape characters on the command line.

When the QUERY parameter is used, the external tables method (rather than the

direct path method) is used for data access.

To specify a schema other than your own in a table-speciﬁc query, you need the

IMP_FULL_DATABASE role.

Restrictions

The QUERY parameter cannot be used in conjunction with the following parameters:

■ CONTENT=METADATA_ONLY

■ SQLFILE

■ TRANSPORT_TABLESPACES

Example

The following is an example of using the QUERY parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

QUERY=departments:'"WHERE department_id < 120"' NOLOGFILE=Y

Only data in expfull.dmp that meets the criteria speciﬁed in the QUERY

parameter is imported.

This example may require the use of escape characters, depending on your

operating system. See Command-Line Escape Characters in Examples on page 3-7.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-27

REMAP_DATAFILE

Default: none

Purpose

Changes the name of the source dataﬁle to the target dataﬁle name in all SQL

statements where the source dataﬁle is referenced: CREATE TABLESPACE, CREATE

LIBRARY, and CREATE DIRECTORY.

Syntax and Description

REMAP_DATAFILE=source_datafile:target_datafile

Remapping dataﬁles is useful when you move databases between platforms that

have different ﬁle naming conventions. The source_datafile and target_

datafile names should be exactly as you want them to appear in the SQL

statements where they are referenced. Oracle recommends that you enclose dataﬁle

names in quotation marks to eliminate ambiguity on platforms for which a colon is

a valid ﬁle speciﬁcation character.

You must have the IMP_FULL_DATABASE role to specify this parameter.

Example

The following is an example of using the REMAP_DATAFILE parameter.

> 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 example remaps a VMS ﬁle speciﬁcation

(DR1$:[HRDATA.PAYROLL]tbs6.f) to a UNIX ﬁle speciﬁcation,

(/db1/hrdata/payroll/tbs6.f) for all SQL DDL statements during the import.

The dump ﬁle, db_full.dmp, is located in the directory object, dpump_dir1.

REMAP_SCHEMA

Default: none

Purpose

Loads all objects from the source schema into a target schema.

Syntax and Description

REMAP_SCHEMA=source_schema:target_schema

Parameters Available in Import's Command-Line Mode

3-28 Oracle Database Utilities

Multiple REMAP_SCHEMA lines can be speciﬁed, but the source schema must be

different for each one. However, different source schemas can map to the same

target schema. The mapping may not be 100 percent complete, because there are

certain schema references that Import is not capable of ﬁnding. For example, Import

will not ﬁnd schema references embedded within the body of deﬁnitions of types,

views, procedures, and packages.

This parameter requires the IMP_FULL_DATABASE role.

If the schema you are remapping to does not already exist, the import operation

creates it, provided the dump ﬁle set contains the necessary CREATEUSER metadata

and you are importing with enough privileges. For example, the following Export

commands would create the dump ﬁle sets with the necessary metadata to create a

schema, because the user SYSTEM has the necessary privileges:

> expdp SYSTEM/password SCHEMAS=hr

> expdp SYSTEM/password FULL=y

If you do not have enough privileges to perform an import that creates dump ﬁles

containing the metadata necessary to create a schema, then you must create the

target schema before performing the import operation. This is because the dump

ﬁles do not contain the necessary information for the import to create the schema

automatically.

If the import operation does create the schema, then after the import is complete,

you must assign it a valid password in order to connect to it. The SQL statement to

do this, which requires privileges, is:

SQL> ALTER USER [schema_name] IDENTIFIED BY [new_pswd]

When you remap a schema, it is possible to constrain the rows loaded into the

destination table by specifying a query. However, Data Pump Import performs

remapping before applying any queries. This means that by the time the query is

applied, the schema has already been remapped. Therefore, to constrain the rows

that are loaded, the query must be applied to the target schema table rather than to

the source schema table.

Example

Suppose that you execute the following Export and Import commands to remap the

hr schema into the blake schema:

> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-29

> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import

REMAP_SCHEMA command will add objects from the hr schema into the existing

scott schema. You can connect to the scott schema after the import by using the

existing password (without resetting it).

If user scott does not exist before you execute the import operation, Import

automatically creates it with an unusable password. This is possible because the

dump ﬁle, hr.dmp, was created by SYSTEM, which has the privileges necessary to

create a dump ﬁle that contains the metadata needed to create a schema. However,

you cannot connect to scott on completion of the import, unless you reset the

password for scott on the target database after the import completes.

REMAP_TABLESPACE

Default: none

Purpose

Remaps all objects selected for import with persistent data in the source tablespace

to be created in the target tablespace.

Syntax and Description

REMAP_TABLESPACE=source_tablespace:target_tablespace

Multiple REMAP_TABLESPACE parameters can be speciﬁed, but no two can have

the same source tablespace. The target schema must have sufﬁcient quota in the

target tablespace.

Note that use of the REMAP_TABLESPACE parameter is the only way to remap a

tablespace in Data Pump Import. This is a simpler and cleaner method than the one

provided in the original Import utility. In original Import, if you wanted to change

the default tablespace for a user, you had to perform several steps, including

exporting and dropping the user, creating the user in the new tablespace, and

importing the user from the dump ﬁle. That method was subject to many

restrictions (including the number of tablespace subclauses) which sometimes

resulted in the failure of some DDL commands.

By contrast, the Data Pump Import method of using the REMAP_TABLESPACE

parameter works for all objects, including the user, and it works regardless of how

many tablespace subclauses are in the DDL statement.

Parameters Available in Import's Command-Line Mode

3-30 Oracle Database Utilities

Example

The following is an example of using the REMAP_TABLESPACE parameter.

> impdp hr/hr REMAP_TABLESPACE='tbs_1':'tbs_6' DIRECTORY=dpump_dir1 PARALLEL=2

JOB_NAME=cf1n02 DUMPFILE=employees.dmp NOLOGFILE=Y

REUSE_DATAFILES

Default: n

Purpose

Speciﬁes whether or not the import job should reuse existing dataﬁles for tablespace

creation.

Syntax and Description

REUSE_DATAFILES={y | n}

If the default (n) is used and the dataﬁles speciﬁed in CREATE TABLESPACE

statements already exist, an error message from the failing CREATE TABLESPACE

statement is issued, but the import job continues.

If this parameter is speciﬁed as y, a warning is issued and the existing dataﬁles are

reinitialized. Be aware that specifying Y can result in a loss of data.

Example

The following is an example of using the REUSE_DATAFILES parameter. You can

create the expfull.dmp dump ﬁle used in this example by running the example

provided for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=reuse.log

REUSE_DATAFILES=Y

This example reinitializes dataﬁles referenced by CREATE TABLESPACE statements

in the expfull.dmp ﬁle.

SCHEMAS

Default: none

Purpose

Speciﬁes that a schema-mode import is to be performed.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-31

Syntax and Description

SCHEMAS=schema_name [,...]

If you have the IMP_FULL_DATABASE role, you can use this parameter to perform

a schema-mode import by specifying a single schema other than your own or a list

of schemas to import. First, the schemas themselves are created (if they do not

already exist), including system and role grants, password history, and so on. Then

all objects contained within the schemas are imported. Nonprivileged users can

specify only their own schemas. In that case, no information about the schema

deﬁnition is imported, only the objects contained within it.

Example

The following is an example of using the SCHEMAS parameter. You can create the

expdat.dmp ﬁle used in this example by running the example provided for the

Export SCHEMAS parameter. See SCHEMAS on page 2-29.

> impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log

DUMPFILE=expdat.dmp

The hr and oe schemas are imported from the expdat.dmp ﬁle. The log ﬁle,

schemas.log, is written to dpump_dir1.

SKIP_UNUSABLE_INDEXES

Default: n

Purpose

Speciﬁes whether or not Import skips loading tables that have indexes that were set

to the Index Unusable state (by either the system or the user).

Syntax and Description

SKIP_UNUSABLE_INDEXES={y | n}

If SKIP_UNUSABLE_INDEXES is set to y, then the import job does not load tables

that have indexes that were previously set to an Index Unusable state. Other tables,

not previously set Unusable, continue to be updated as rows are inserted.

This parameter enables you to postpone index maintenance on selected index

partitions until after row data has been inserted. You then have the responsibility to

rebuild the affected index partitions after the import.

Parameters Available in Import's Command-Line Mode

3-32 Oracle Database Utilities

If SKIP_UNUSABLE_INDEXES is set to n (the default) and an index in the Unusable

state is encountered, the load of that table or partition will fail.

Example

The following is an example of using the SKIP_UNUSABLE_INDEXES parameter.

You can create the expfull.dmp dump ﬁle used in this example by running the

example provided for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=skip.log

SKIP_UNUSABLE_INDEXES=y

SQLFILE

Default: none

Purpose

Speciﬁes a ﬁle into which all of the SQL DDL that Import would have executed,

based on other parameters, is written.

Syntax and Description

SQLFILE=[directory_object:]file_name

The file_name speciﬁes where the import job will write the DDL that would be

executed during the job. The SQL is not actually executed, and the target system

remains unchanged. The ﬁle is written to the directory object speciﬁed in the

DIRECTORY parameter, unless another directory_object is explicitly speciﬁed

here. Any existing ﬁle that has a name matching the one speciﬁed with this

parameter is overwritten.

Note that passwords are commented out in the SQL ﬁle. For example, if a CONNECT

statement is part of the DDL that was executed, it will be commented out and the

schema name will be shown but the password will not. In the following example,

the dashes indicate that a comment follows, and the hr schema name is shown, but

not the password.

-- CONNECT hr

Therefore, before you can execute the SQL ﬁle, you must edit it by removing the

dashes indicating a comment and adding the password for the hr schema (in this

case, the password is also hr), as follows:

CONNECT hr/hr

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-33

Example

The following is an example of using the SQLFILE parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

SQLFILE=dpump_dir2:expfull.sql

A SQL ﬁle named expfull.sql is written to dpump_dir2.

STATUS

Default: 0

Purpose

Displays detailed status of the job, along with a description of the current operation.

An estimated completion percentage for the job is also returned.

Syntax and Description

STATUS[=integer]

If you supply a value for integer, it speciﬁes how frequently, in seconds, job

status should be displayed in logging mode. If no value is entered or if the default

value of 0 is used, information is displayed only upon completion of each object

type, table, or partition

This status information is written only to your standard output device, not to the

log ﬁle (if one is in effect).

Example

The following is an example of using the STATUS parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr NOLOGFILE=y STATUS=120 DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

In this example, the status is shown every two minutes (120 seconds).

STREAMS_CONFIGURATION

Default: y

Parameters Available in Import's Command-Line Mode

3-34 Oracle Database Utilities

Purpose

Speciﬁes whether or not to import any general Streams metadata that may be

present in the export dump ﬁle.

Syntax and Description

STREAMS_CONFIGURATION={y | n}

Example

The following is an example of using the STREAMS_CONFIGURATION parameter.

You can create the expfull.dmp dump ﬁle used in this example by running the

example provided for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp STREAMS_CONFIGURATION=n

TABLE_EXISTS_ACTION

Default: SKIP (Note that if CONTENT=DATA_ONLY is speciﬁed, the default is

APPEND, not SKIP.)

Purpose

Tells Import what to do if the table it is trying to create already exists.

Syntax and Description

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

The possible values have the following effects:

■ SKIP leaves the table as is and moves on to the next object. This is not a valid

option if the CONTENT parameter is set to DATA_ONLY.

■ APPEND loads rows from the source and leaves existing rows unchanged.

■ TRUNCATE deletes existing rows and then loads rows from the source.

■ REPLACE drops the existing table and then creates and loads it from the source.

This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

■ When you use TRUNCATE or REPLACE, make sure that rows in the affected

tables are not targets of any referential constraints.

See Also: Oracle Streams Replication Administrator's Guide

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-35

■ When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects

in the source, such as indexes, grants, triggers, and constraints, are ignored. For

REPLACE, the dependent objects are dropped and re-created from the source, if

they were not explicitly or implicitly excluded (using EXCLUDE) and they exist

in the source dump ﬁle or system.

■ When you use APPEND or TRUNCATE, checks are made to ensure that rows from

the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method because the

external tables feature honors active constraints and triggers. However, be

aware that if any row violates an active constraint, the load fails and no data is

loaded.

If you have data that must be loaded, but may cause constraint violations,

consider disabling the constraints, loading the data, and then deleting the

problem rows before reenabling the constraints.

■ When you use APPEND, the data is always loaded into new space; existing

space, even if available, is not reused. For this reason, you may wish to

compress your data after the load.

Restrictions

TRUNCATE cannot be used on clustered tables or over network links.

Example

The following is an example of using the TABLE_EXISTS_ACTION parameter. You

can create the expfull.dmp dump ﬁle used in this example by running the

example provided for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

TABLE_EXISTS_ACTION=REPLACE

TABLES

Default: none

Purpose

Speciﬁes that you want to perform a table-mode import.

Syntax and Description

TABLES=[schema_name.]table_name[:partition_name]

Parameters Available in Import's Command-Line Mode

3-36 Oracle Database Utilities

In a table-mode import, you can ﬁlter the data that is imported from the source by

specifying a comma-delimited list of tables and partitions or subpartitions.

If you do not supply a schema_name, it defaults to that of the current user. To

specify a schema other than your own, you must have the IMP_FULL_DATABASE

role.

If a partition_name is speciﬁed, it must be the name of a partition or

subpartition in the associated table.

The use of wildcards with table names is also supported. For example,

TABLES=emp% would import all tables having names that start with 'EMP'.

Restrictions

The use of synonyms as values for the TABLES parameter is not supported. For

example, if the regions table in the hr schema had a synonym of regn, it would

not be valid to use TABLES=regn. An error would be returned.

If you specify more than one table_name, they must all reside in the same schema.

Example

The following example shows a simple use of the TABLES parameter to import only

the employees and jobs tables from the expfull.dmp ﬁle. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

The following example shows the use of the TABLES parameter to import

partitions:

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp

TABLES=sh.sales:sales_Q1_2000,sh.sales:sales_Q2_2000

This example imports the partitions sales_Q1_2000 and sales_Q2_2000 for the

table sales in the schema sh.

TABLESPACES

Default: none

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-37

Purpose

Speciﬁes that you want to perform a tablespace-mode import.

Syntax and Description

TABLESPACES=tablespace_name [, ...]

Use TABLESPACES to specify a list of tablespace names whose tables and

dependent objects are to be imported from the source (full, schema, tablespace, or

table-mode export dump ﬁle set or another database).

Example

The following is an example of using the TABLESPACES parameter. It assumes that

the tablespaces already exist. You can create the expfull.dmp dump ﬁle used in

this example by running the example provided for the Export FULL parameter. See

FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLESPACES=tbs_1,tbs_2,

tbs_3,tbs_4

TRANSFORM

Default: none

Purpose

Enables you to alter object creation DDL for speciﬁc objects, as well as for all

applicable objects being loaded.

Syntax and Description

TRANSFORM = transform_name:value[:object_type]

The transform_name speciﬁes the name of the transform. There are two possible

options:

■ SEGMENT_ATTRIBUTES - if the value is speciﬁed as y, then segment attributes

(physical attributes, storage attributes, tablespaces, and logging) are included,

with appropriate DDL. The default is y.

■ STORAGE - If the value is speciﬁed as y, the storage clauses are included, with

appropriate DDL. The default is y. This parameter is ignored if SEGMENT_

ATTRIBUTES=n.

Parameters Available in Import's Command-Line Mode

3-38 Oracle Database Utilities

The value speciﬁes whether to include (y)oromit(n) segment or storage attributes

(as speciﬁed by the transform_name) on applicable object DDL.

The object_type is optional. If supplied, it designates the object type to which

the transform will be applied. If no object type is speciﬁed then the transform

applies to all object types. The object type must be either TABLE or INDEX.

Example

For the following example, assume that you have exported the employees table in

the hr schema. The SQL CREATE TABLE statement that results when you then

import the table is similar to the following:

CREATE TABLE "HR"."EMPLOYEES"

( "EMPLOYEE_ID" NUMBER(6,0),

"FIRST_NAME" VARCHAR2(20),

"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,

"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,

"PHONE_NUMBER" VARCHAR2(20),

"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,

"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,

"SALARY" NUMBER(8,2),

"COMMISSION_PCT" NUMBER(2,2),

"MANAGER_ID" NUMBER(6,0),

"DEPARTMENT_ID" NUMBER(4,0)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121

PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "SYSTEM" ;

If you do not want to retain the STORAGE clause or TABLESPACE clause, you can

remove them from the CREATE STATEMENT by using the Import TRANSFORM

parameter. Specify the value of SEGMENT_ATTRIBUTES as n. This results in the

exclusion of segment attributes (both storage and tablespace) from the table.

> impdp hr/hr TABLES=hr.employees \

DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp \

TRANSFORM=SEGMENT_ATTRIBUTES:n:table

The resulting CREATE TABLE statement for the employees table would then look

similar to the following. It does not contain a STORAGE or TABLESPACE clause; the

attributes for the default tablespace for the HR schema will be used instead.

CREATE TABLE "HR"."EMPLOYEES"

( "EMPLOYEE_ID" NUMBER(6,0),

"FIRST_NAME" VARCHAR2(20),

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-39

"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,

"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,

"PHONE_NUMBER" VARCHAR2(20),

"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,

"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,

"SALARY" NUMBER(8,2),

"COMMISSION_PCT" NUMBER(2,2),

"MANAGER_ID" NUMBER(6,0),

"DEPARTMENT_ID" NUMBER(4,0)

);

As shown in the previous example, the SEGMENT_ATTRIBUTES transform applies

to both storage and tablespace attributes. To omit only the STORAGE clause and

retain the TABLESPACE clause, you can use the STORAGE transform, as follows:

> impdp hr/hr TABLES=hr.employees \

DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp \

TRANSFORM=STORAGE:n:table

The SEGMENT_ATTRIBUTES and STORAGE transforms can be applied to all

applicable table and index objects by not specifying the object type on the

TRANSFORM parameter, as shown in the following command:

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp \

SCHEMAS=hr TRANSFORM=SEGMENT_ATTRIBUTES:n

TRANSPORT_DATAFILES

Default: none

Purpose

Speciﬁes a list of dataﬁles to be imported into the target database by a

transportable-mode import. The ﬁles must already have been copied from the

source database system.

Syntax and Description

TRANSPORT_DATAFILES=datafile_name

The datafile_name must include an absolute directory path speciﬁcation (not a

directory object name) that is valid on the system where the target database resides.

Parameters Available in Import's Command-Line Mode

3-40 Oracle Database Utilities

Example

The following is an example of using the TRANSPORT_DATAFILES parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp

TRANSPORT_DATAFILES='/user01/data/tbs1.f'

TRANSPORT_FULL_CHECK

Default: n

Purpose

Speciﬁes whether or not to verify that the speciﬁed transportable tablespace set has

no dependencies.

Syntax and Description

TRANSPORT_FULL_CHECK={y | n}

If TRANSPORT_FULL_CHECK=y, then Import veriﬁes that there are no dependencies

between those objects inside the transportable set and those outside the

transportable set. The check addresses two-way dependencies. For example, if a

table is inside the transportable set but its index is not, a failure is returned and the

import operation is terminated. Similarly, a failure is also returned if an index is in

the transportable set but the table is not.

If TRANSPORT_FULL_CHECK=n, then Import veriﬁes only that there are no objects

within the transportable set that are dependent on objects outside the transportable

set. This check addresses a one-way dependency. For example, a table is not

dependent on an index, but an index is dependent on a table, because an index

without a table has no meaning. Therefore, if the transportable set contains a table,

but not its index, then this check succeeds. However, if the transportable set

contains an index, but not the table, the import operation is terminated.

In addition to this check, Import always veriﬁes that all storage segments of all

tables (and their indexes) deﬁned within the tablespace set speciﬁed by

TRANSPORT_TABLESPACES are actually contained within the tablespace set.

Restrictions

This parameter is valid for transportable mode only when the NETWORK_LINK

parameter is speciﬁed.

Parameters Available in Import's Command-Line Mode

Data Pump Import 3-41

Example

In the following example, source_database_link would be replaced with the

name of a valid database link. The example also assumes that a dataﬁle named

tbs6.f already exists.

> impdp hr/hr DIRECTORY=dpump_dir1 TRANSPORT_TABLESPACES=tbs_6

NETWORK_LINK=source_database_link TRANSPORT_FULL_CHECK=y

TRANSPORT_DATAFILES='/wkdir/data/tbs6.f'

TRANSPORT_TABLESPACES

Default: none

Purpose

Speciﬁes that you want to perform a transportable-tablespace-mode import.

Syntax and Description

TRANSPORT_TABLESPACES=tablespace_name [, ...]

Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace names

for which object metadata will be imported from the source database into the target

database.

Example

In the following example, the source_database_link would be replaced with

the name of a valid database link. The example also assumes that a dataﬁle named

tbs6.f already exists.

> impdp hr/hr DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link

TRANSPORT_TABLESPACES=tbs_6 TRANSPORT_FULL_CHECK=n

TRANSPORT_DATAFILES='user01/data/tbs6.f'

VERSION

Default: COMPATIBLE

Purpose

Speciﬁes the version of database objects to be imported.

Syntax and Description

VERSION={COMPATIBLE | LATEST | version_string}

How Data Pump Import Parameters Map to Those of the Original Import Utility

3-42 Oracle Database Utilities

This parameter can be used to load a target system whose Oracle database is at an

earlier version than that of the source system. Database objects or attributes on the

source system that are incompatible with the speciﬁed version will not be moved to

the target. For example, tables containing new datatypes that are not supported in

the speciﬁed version will not be imported. Legal values for this parameter are as

follows:

■ COMPATIBLE - This is the default value. The version of the metadata

corresponds to the database compatibility level. Database compatibility must be

set to 9.2.0 or higher.

■ LATEST - The version of the metadata corresponds to the database version.

■ version_string - A speciﬁc database version (for example, 10.0.0). In Oracle

Database 10g, this value cannot be lower than 10.0.0.

Example

The following is an example of using the VERSION parameter. You can create the

expfull.dmp dump ﬁle used in this example by running the example provided

for the Export FULL parameter. See FULL on page 2-19.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees

VERSION=LATEST

How Data Pump Import Parameters Map to Those of the Original Import

Utility

Table 3–1 maps, as closely as possible, Data Pump Import parameters to original

Import parameters. In some cases, because of feature redesign, the original Import

parameter is no longer needed so there is no Data Pump command to compare it to.

Also, as shown in the table, some of the parameter names may be the same, but the

functionality is slightly different.

Table 3–1 Original Import Parameters and Their Counterparts in Data Pump Import

Original Import Parameter Comparable Data Pump Import Parameter

BUFFER

A parameter comparable to BUFFER is not needed.

CHARSET

A parameter comparable to CHARSET is not needed.

COMMIT

A parameter comparable to COMMIT is not supported.

COMPILE

A parameter comparable to COMPILE is not supported.

How Data Pump Import Parameters Map to Those of the Original Import Utility

Data Pump Import 3-43

CONSTRAINTS

EXCLUDE=CONSTRAINT and INCLUDE=CONSTRAINT

DATAFILES

TRANSPORT_DATAFILES

DESTROY

REUSE_DATAFILES

FEEDBACK

STATUS

FILE

DUMPFILE

FILESIZE

Not necessary. It is included in the dump ﬁle set.

FROMUSER

SCHEMAS

FULL

GRANTS

EXCLUDE=GRANT and INCLUDE=GRANT

HELP

IGNORE

TABLE_EXISTS_ACTION

INDEXES

EXCLUDE=INDEX and INCLUDE=INDEX

INDEXFILE

SQLFILE

LOG

LOGFILE

PARFILE

RECORDLENGTH

A parameter comparable to RECORDLENGTH is not

needed.

RESUMABLE

A parameter comparable to RESUMABLE is not needed. It

is automatically defaulted.

RESUMABLE_NAME

A parameter comparable to RESUMABLE_NAME is not

needed. It is automatically defaulted.

RESUMABLE_TIMEOUT

A parameter comparable to RESUMABLE_TIMEOUT is not

needed. It is automatically defaulted.

ROWS=N

CONTENT=METADATA_ONLY

ROWS=Y

CONTENT=ALL

SHOW

SQLFILE

SKIP_UNUSABLE_INDEXES

STATISTICS

A parameter comparable to STATISTICS is not needed. If

the source table has statistics, they are imported.

Table 3–1 (Cont.) Original Import Parameters and Their Counterparts in Data Pump

Original Import Parameter Comparable Data Pump Import Parameter

Commands Available in Import's Interactive-Command Mode

3-44 Oracle Database Utilities

This table does not list all Data Pump Import command-line parameters. For

information about all Import command-line parameters, see Commands Available

in Import's Interactive-Command Mode on page 3-44.

Commands Available in Import's Interactive-Command Mode

In interactive-command mode, the current job continues running, but logging to the

terminal is stopped and the Import prompt is displayed.

To start interactive-command mode, do one of the following:

STREAMS_CONFIGURATION

STREAMS_INSTANTIATION

A parameter comparable to STREAMS_INSTANTIATIONis

not needed.

TABLES

TABLESPACES

This parameter still exists, but some of its functionality is

now performed using the TRANSPORT_TABLESPACES

parameter.

TOID_NOVALIDATE

A command comparable to TOID_NOVALIDATE is not

needed. OIDs are no longer used for type validation.

TOUSER

REMAP_SCHEMA

TRANSPORT_TABLESPACE

TRANSPORT_TABLESPACES (see command description)

TTS_OWNERS

A parameter comparable to TTS_OWNERS is not needed

because the information is stored in the dump ﬁle set.

USERID

A parameter comparable to USERID is not needed. This

information is supplied as the username/password

when you invoke Import.

VOLSIZE

A parameter comparable to VOLSIZE is not needed

because tapes are not supported.

Note: Data Pump Import interactive-command mode is different

from the interactive mode for original Import, in which Import

prompted you for input. See Interactive Mode on page 20-8 for

information about interactive mode in original Import.

Table 3–1 (Cont.) Original Import Parameters and Their Counterparts in Data Pump

Original Import Parameter Comparable Data Pump Import Parameter

Commands Available in Import's Interactive-Command Mode

Data Pump Import 3-45

■ From an attached client, press Ctrl+C.

■ From a terminal other than the one on which the job is running, use the ATTACH

parameter to attach to the job. This is a useful feature in situations in which you

start a job at one location and need to check on it at a later time from a different

location.

Table 3–2 lists the activities you can perform for the current job from the Data Pump

Import prompt in interactive-command mode.

The following are descriptions of the commands available in the

interactive-command mode of Data Pump Import.

CONTINUE_CLIENT

Purpose

Changes the mode from interactive-command mode to logging mode.

Syntax and Description

CONTINUE_CLIENT

Table 3–2 Supported Activities in Data Pump Import's Interactive-Command Mode

Activity Command Used

Exit interactive-command mode CONTINUE_CLIENT on

page 3-45

Stop the import client session, but leave the current job

running

EXIT_CLIENT on page 3-46

Display a summary of available commands HELP on page 3-46

Detach all currently attached client sessions and kill the

current job

KILL_JOB on page 3-47

Increase or decrease the number of active worker processes

for the current job. This command is valid only in the

Enterprise Edition of Oracle Database 10g.

PARALLEL on page 3-47

Restart a stopped job to which you are attached START_JOB on page 3-48

Display detailed status for the current job STATUS on page 3-48

Stop the current job STOP_JOB on page 3-49

Commands Available in Import's Interactive-Command Mode

3-46 Oracle Database Utilities

In logging mode, the job status is continually output to the terminal. If the job is

currently stopped, then CONTINUE_CLIENT will also cause the client to attempt to

start the job.

Example

impdp> CONTINUE_CLIENT

EXIT_CLIENT

Purpose

Stops the import client session, exits Import, and discontinues logging to the

terminal, but leaves the current job running.

Syntax and Description

EXIT_CLIENT

Because EXIT_CLIENT stops the client session but leaves the job running, you can

attach to the job at a later time if it is still executing or in a stopped state. To see the

status of the job, you can monitor the log ﬁle for the job or you can query the USER_

DATAPUMP_JOBS view or the V$SESSION_LONGOPS view.

Example

impdp> EXIT_CLIENT

HELP

Purpose

Provides information about Data Pump Import commands available in

interactive-command mode.

Syntax and Description

HELP

Displays information about the commands available in interactive-command mode.

Example

impdp> HELP

Commands Available in Import's Interactive-Command Mode

Data Pump Import 3-47

KILL_JOB

Purpose

Detaches all currently attached client sessions and then kills the current job. It exits

Import and returns to the terminal prompt.

Syntax and Description

KILL_JOB

A job that is killed using KILL_JOB cannot be restarted. All attached clients,

including the one issuing the KILL_JOB command, receive a warning that the job is

being killed by the current user and are then detached. After all clients are

detached, the job's process structure is immediately run down and the master table

and dump ﬁles are deleted. Log ﬁles are not deleted.

Example

impdp> KILL_JOB

PARALLEL

Purpose

Enables you to increase or decrease the number of active worker processes for the

current job.

Syntax and Description

PARALLEL=integer

PARALLEL is available as both a command-line parameter and an interactive-mode

parameter. You set it to the desired number of parallel processes. An increase takes

effect immediately if there are sufﬁcient ﬁles and resources. A decrease does not

take effect until an existing process ﬁnishes its current task. If the integer value is

decreased, workers are idled but not deleted until the job exits.

Example

PARALLEL=10

See Also: PARALLEL on page 3-23 for more information about

parallelism

Commands Available in Import's Interactive-Command Mode

3-48 Oracle Database Utilities

START_JOB

Purpose

Starts the current job to which you are attached.

Syntax and Description

START_JOB[=SKIP_CURRENT]

The START_JOB command restarts the job to which you are currently attached (the

job cannot be currently executing). The job is restarted with no data loss or

corruption after an unexpected failure or after you issue a STOP_JOB command,

provided the dump ﬁle set and master table remain undisturbed.

The SKIP_CURRENT option allows you to restart a job that previously failed to

restart because execution of some DDL statement failed. The failing statement is

skipped and the job is restarted from the next work item.

Transportable-tablespace-mode imports are not restartable.

Example

impdp> START_JOB

STATUS

Purpose

Displays the cumulative status of the job, along with a description of the current

operation. A completion percentage for the job is also returned.

Syntax and Description

STATUS[=integer]

You have the option of specifying how frequently, in seconds, this status should be

displayed in logging mode. If no value is entered or if the default value of 0 is used,

the periodic status display is turned off and status is displayed only once.

This status information is written only to your standard output device, not to the

log ﬁle (even if one is in effect).

Example

The following example will display the status every two minutes (120 seconds).

Examples of Using Data Pump Import

Data Pump Import 3-49

STATUS=120

STOP_JOB

Purpose

Stops the current job either immediately or after an orderly shutdown, and exits

Import.