Oracle9i Database Utilities -- Contents

What Is the Export Utility?

Before Using Export

Running catexp.sql or catalog.sql

Ensuring Sufficient Disk Space

Verifying Access Privileges

Invoking Export

Command-Line Entries

Interactive Export Prompts

Parameter Files

Invoking Export As SYSDBA

Export Modes

Table-Level and Partition-Level Export

Table-Level Export

Partition-Level Export

Processing Restrictions

Getting Online Help

Export Parameters

Example Export Sessions

Example Export Session in Full Database Mode

Example Export Session in User Mode

Example Export Sessions in Table Mode

Example 1: DBA Exporting Tables for Two Users

Example 2: User Exports Tables That He Owns

Example 3: Using Pattern Matching to Export Various Tables

Example Export Session Using Partition-Level Export

Example 1: Exporting a Table Without Specifying a Partition

Example 2: Exporting a Table with a Specified Partition

Example 3: Exporting a Composite Partition

Using the Interactive Method

Restrictions

Warning, Error, and Completion Messages

Log File

Warning Messages

Nonrecoverable Error Messages

Completion Messages

Exit Codes for Inspection and Display

Conventional Path Export Versus Direct Path Export

Invoking a Direct Path Export

Security Considerations for Direct Path Exports

Performance Issues for Direct Path Exports

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Oracle Net

Character Set and Globalization Support Considerations

Character Set Conversion

Effect of Character Set Sorting Order on Conversions

Multibyte Character Sets and Export and Import

Instance Affinity and Export

Considerations When Exporting Database Objects

Exporting Sequences

Exporting LONG and LOB Datatypes

Exporting Foreign Function Libraries

Exporting Offline Bitmapped Tablespaces

Exporting Directory Aliases

Exporting BFILE Columns and Attributes

External Tables

Exporting Object Type Definitions

Exporting Nested Tables

Exporting Advanced Queue (AQ) Tables

Exporting Synonyms

Possible Export Errors Related to Java Synonyms

Support for Fine-Grained Access Control

Transportable Tablespaces

Exporting from a Read-Only Database

Using Export and Import to Partition a Database Migration

Advantages of Partitioning a Migration

Disadvantages of Partitioning a Migration

How to Use Export and Import to Partition a Database Migration

Using Different Releases and Versions of Export

Restrictions When Using Different Releases and Versions of Export and Import

Examples of Using Different Releases of Export and Import

Creating Oracle Release 8.0 Export Files from an Oracle9i Database

Possible Errors When Using Different Releases and Versions

EXP-24

EXP-23

EXP-37

What Is the Import Utility?

Table Objects: Order of Import

Before Using Import

Running catexp.sql or catalog.sql

Verifying Access Privileges

Importing Objects into Your Own Schema

Importing Grants

Importing Objects into Other Schemas

Importing System Objects

Importing into Existing Tables

Manually Creating Tables Before Importing Data

Disabling Referential Constraints

Manually Ordering the Import

Effect of Schema and Database Triggers on Import Operations

Invoking Import

Command-Line Entries

Interactive Import Prompts

Parameter Files

Invoking Import As SYSDBA

Import Modes

Getting Online Help

Import Parameters

Example Import Sessions

Example Import of Selected Tables for a Specific User

Example Import of Tables Exported by Another User

Example Import of Tables from One User to Another

Example Import Session Using Partition-Level Import

Example 1: A Partition-Level Import

Example 2: A Partition-Level Import of a Composite Partitioned Table

Example 3: Repartitioning a Table on a Different Column

Example Import of Using Pattern Matching to Import Various Tables

Using the Interactive Method

Warning, Error, and Completion Messages

Log File

Warning Messages

Nonrecoverable Error Messages

Completion Messages

Exit Codes for Inspection and Display

Error Handling During an Import

Row Errors

Failed Integrity Constraints

Invalid Data

Errors Importing Database Objects

Object Already Exists

Sequences

Resource Errors

Domain Index Metadata

Table-Level and Partition-Level Import

Guidelines for Using Table-Level Import

Guidelines for Using Partition-Level Import

Migrating Data Across Partitions and Tables

Controlling Index Creation and Maintenance

Delaying Index Creation

Index Creation and Maintenance Controls

Example of Postponing Index Maintenance

Reducing Database Fragmentation

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Oracle Net

Character Set and Globalization Support Considerations

Character Set Conversion

User Data

Data Definition Language (DDL)

Import and Single-Byte Character Sets

Import and Multibyte Character Sets

Considerations When Importing Database Objects

Importing Object Identifiers

Importing Existing Object Tables and Tables That Contain Object Types

Importing Nested Tables

Importing REF Data

Importing BFILE Columns and Directory Aliases

Importing Foreign Function Libraries

Importing Stored Procedures, Functions, and Packages

Importing Java Objects

Importing External Tables

Importing Advanced Queue (AQ) Tables

Importing LONG Columns

Importing Views

Importing Partitioned Tables

Support for Fine-Grained Access Control

Materialized Views and Snapshots

Snapshot Log

Snapshots

Importing a Snapshot

Importing a Snapshot into a Different Schema

Transportable Tablespaces

Storage Parameters

The OPTIMAL Parameter

Storage Parameters for OID Indexes and LOB Columns

Overriding Storage Parameters

The Export COMPRESS Parameter

Read-Only Tablespaces

Dropping a Tablespace

Reorganizing Tablespaces

Importing Statistics

Using Export and Import to Partition a Database Migration

Advantages of Partitioning a Migration

Disadvantages of Partitioning a Migration

How to Use Export and Import to Partition a Database Migration

Using Export Files from a Previous Oracle Release

Using Oracle Version 7 Export Files

Check Constraints on DATE Columns

Using Oracle Version 6 Export Files

User Privileges

CHAR Columns

Status of Integrity Constraints

Length of Default Column Values

Using Oracle Version 5 Export Files

Restrictions When Using Different Releases and Versions of Export and Import

The CHARSET Parameter

SQL*Loader Features

SQL*Loader Control File

Input Data and Datafiles

Fixed Record Format

Variable Record Format

Stream Record Format

Logical Records

Data Fields

LOBFILEs and Secondary Datafiles (SDFs)

Data Conversion and Datatype Specification

Discarded and Rejected Records

Log File and Logging Information

Conventional Path Loads, Direct Path Loads, and External Table Loads

Conventional Path Loads

Direct Path Loads

Parallel Direct Path

External Table Loads

Loading Objects, Collections, and LOBs

Supported Object Types

column-objects

row objects

Supported Collection Types

Nested Tables

VARRAYs

Supported LOB Types

Partitioned Object Support

Application Development: Direct Path Load API

Invoking SQL*Loader

Specifying Parameters in the Control File

Command-Line Parameters

Exit Codes for Inspection and Display

Control File Contents

Comments in the Control File

Specifying Command-Line Parameters in the Control File

OPTIONS Clause

Specifying Filenames and Object Names

Filenames That Conflict with SQL and SQL*Loader Reserved Words

Specifying SQL Strings

Operating System Considerations

Specifying a Complete Path

Backslash Escape Character

Nonportable Strings

Escaping the Backslash

Escape Character Is Sometimes Disallowed

Specifying Datafiles

Examples of INFILE Syntax

Specifying Multiple Datafiles

Identifying Data in the Control File with BEGINDATA

Specifying Datafile Format and Buffering

Specifying the Bad File

Examples of Specifying a Bad File Name

How Bad Files Are Handled with LOBFILEs and SDFs

Criteria for Rejected Records

Specifying the Discard File

Specifying the Discard File in the Control File

Specifying the Discard File from the Command Line

Examples of Specifying a Discard File Name

Criteria for Discarded Records

How Discard Files Are Handled with LOBFILEs and SDFs

Limiting the Number of Discarded Records

Handling Different Character Encoding Schemes

Multibyte (Asian) Character Sets

Unicode Character Sets

Database Character Sets

Datafile Character Sets

Input Character Conversion

CHARACTERSET Parameter

Control File Character Set

Character-Length Semantics

Interrupted Loads

Discontinued Conventional Path Loads

Discontinued Direct Path Loads

Load Discontinued Because of Space Errors

Load Discontinued Because Maximum Number of Errors Exceeded

Load Discontinued Because of Fatal Errors

Load Discontinued Because a Control+C Was Issued

Status of Tables and Indexes After an Interrupted Load

Using the Log File to Determine Load Status

Continuing Single-Table Loads

Assembling Logical Records from Physical Records

Using CONCATENATE to Assemble Logical Records

Using CONTINUEIF to Assemble Logical Records

Loading Logical Records into Tables

Specifying Table Names

INTO TABLE Clause

Table-Specific Loading Method

Loading Data into Empty Tables

Loading Data into Nonempty Tables

Table-Specific OPTIONS Parameter

Loading Records Based on a Condition

Using the WHEN Clause with LOBFILEs and SDFs

Specifying Default Data Delimiters

fields_spec

termination_spec

enclosure_spec

Handling Short Records with Missing Data

TRAILING NULLCOLS Clause

Index Options

SORTED INDEXES Clause

SINGLEROW Option

Benefits of Using Multiple INTO TABLE Clauses

Extracting Multiple Logical Records

Relative Positioning Based on Delimiters

Distinguishing Different Input Record Formats

Relative Positioning Based on the POSITION Parameter

Distinguishing Different Input Row Object Subtypes

Loading Data into Multiple Tables

Summary

Bind Arrays and Conventional Path Loads

Size Requirements for Bind Arrays

Performance Implications of Bind Arrays

Specifying Number of Rows Versus Size of Bind Array

Calculations to Determine Bind Array Size

Determining the Size of the Length Indicator

Calculating the Size of Field Buffers

Minimizing Memory Requirements for Bind Arrays

Calculating Bind Array Size for Multiple INTO TABLE Clauses

Field List Contents

Specifying the Position of a Data Field

Using POSITION with Data Containing Tabs

Using POSITION with Multiple Table Loads

Examples of Using POSITION

Specifying Columns and Fields

Specifying Filler Fields

Specifying the Datatype of a Data Field

SQL*Loader Datatypes

Specifying Field Conditions

Comparing Fields to BLANKS

Comparing Fields to Literals

Using the WHEN, NULLIF, and DEFAULTIF Clauses

Loading Data Across Different Platforms

Byte Ordering

Specifying Byte Order

Using Byte Order Marks (BOMs)

Suppressing Checks for BOMs

Loading All-Blank Fields

Trimming Whitespace

Datatypes for Which Whitespace Can Be Trimmed

Field Length Specifications for Datatypes for Which Whitespace Can Be Trimmed

Predetermined Size Fields

Delimited Fields

Relative Positioning of Fields

No Start Position Specified for a Field

Previous Field Terminated by a Delimiter

Previous Field Has Both Enclosure and Termination Delimiters

Leading Whitespace

Previous Field Terminated by Whitespace

Optional Enclosure Delimiters

Trailing Whitespace

Enclosed Fields

Preserving Whitespace

PRESERVE BLANKS Option

Terminated by Whitespace

Applying SQL Operators to Fields

Referencing Fields

Common Uses of SQL Operators in Field Specifications

Combinations of SQL Operators

Using SQL Strings with a Date Mask

Interpreting Formatted Fields

Using SQL*Loader to Generate Data for Input

Loading Data Without Files

Setting a Column to a Constant Value

CONSTANT Parameter

Setting a Column to an Expression Value

EXPRESSION Parameter

Setting a Column to the Datafile Record Number

RECNUM Parameter

Setting a Column to the Current Date

SYSDATE Parameter

Setting a Column to a Unique Sequence Number

SEQUENCE Parameter

Generating Sequence Numbers for Multiple Tables

Example: Generating Different Sequence Numbers for Each Insert

Loading Column Objects

Loading Column Objects in Stream Record Format

Loading Column Objects in Variable Record Format

Loading Nested Column Objects

Loading Column Objects with a Derived Subtype

Specifying Null Values for Objects

Specifying Attribute Nulls

Specifying Atomic Nulls

Loading Column Objects with User-Defined Constructors

Loading Object Tables

Loading Object Tables with a Subtype

Loading REF Columns

Real REF Columns

Primary Key REF Columns

Unscoped REF Columns That Allow Primary Keys

Loading LOBs

Loading LOB Data from a Primary Datafile

LOB Data in Predetermined Size Fields

LOB Data in Delimited Fields

LOB Data in Length-Value Pair Fields

Loading LOB Data from an External LOBFILE (BFILE)

Loading LOB Data from LOBFILEs

Dynamic Versus Static LOBFILE Specifications

Examples of Loading LOB Data from LOBFILEs

Considerations When Loading LOBs from LOBFILEs

Loading Collections (Nested Tables and VARRAYs)

Restrictions in Nested Tables and VARRAYs

Secondary Datafiles (SDFs)

Dynamic Versus Static SDF Specifications

Loading a Parent Table Separately from Its Child Table

Memory Issues When Loading VARRAY Columns

Header Information

Global Information

Table Information

Datafile Information

Table Load Information

Summary Statistics

Oracle Statistics That Are Logged

Information About Single-Partition Loads

Statistics for Loading a Table

Additional Summary Statistics for Direct Path Loads and Multithreading

Log File Created When EXTERNAL_TABLE=GENERATE_ONLY

Data Loading Methods

Conventional Path Load

Conventional Path Load of a Single Partition

When to Use a Conventional Path Load

Direct Path Load

Data Conversion During Direct Path Loads

Direct Path Load of a Partitioned or Subpartitioned Table

Direct Path Load of a Single Partition or Subpartition

Advantages of a Direct Path Load

Restrictions on Using Direct Path Loads

Restrictions on a Direct Path Load of a Single Partition

When to Use a Direct Path Load

Integrity Constraints

Field Defaults on the Direct Path

Loading into Synonyms

Using Direct Path Load

Setting Up for Direct Path Loads

Specifying a Direct Path Load

Building Indexes

Improving Performance

Temporary Segment Storage Requirements

Indexes Left in an Unusable State

Using Data Saves to Protect Against Data Loss

Using the ROWS Parameter

Data Save Versus Commit

Data Recovery During Direct Path Loads

Media Recovery and Direct Path Loads

Instance Recovery and Direct Path Loads

Loading LONG Data Fields

Loading Data As PIECED

Optimizing Performance of Direct Path Loads

Preallocating Storage for Faster Loading

Presorting Data for Faster Indexing

SORTED INDEXES Clause

Unsorted Data

Multiple-Column Indexes

Choosing the Best Sort Order

Infrequent Data Saves

Minimizing Use of the Redo Log

Disabling Archiving

Specifying the UNRECOVERABLE Parameter

Setting the NOLOG Attribute

Specifying the Number of Column Array Rows and Size of Stream Buffers

Specifying a Value for the Date Cache

Optimizing Direct Path Loads on Multiple-CPU Systems

Avoiding Index Maintenance

Direct Loads, Integrity Constraints, and Triggers

Integrity Constraints

Enabled Constraints

Disabled Constraints

Reenable Constraints

Database Insert Triggers

Replacing Insert Triggers with Integrity Constraints

When Automatic Constraints Cannot Be Used

Preparation

Using an Update Trigger

Duplicating the Effects of Exception Conditions

Using a Stored Procedure

Permanently Disabled Triggers and Constraints

Increasing Performance with Concurrent Conventional Path Loads

Parallel Data Loading Models

Concurrent Conventional Path Loads

Intersegment Concurrency with Direct Path

Intrasegment Concurrency with Direct Path

Restrictions on Parallel Direct Path Loads

Initiating Multiple SQL*Loader Sessions

Parameters for Parallel Direct Path Loads

Specifying Temporary Segments

Enabling Constraints After a Parallel Direct Path Load

PRIMARY KEY and UNIQUE KEY Constraints

General Performance Improvement Hints

The Case Studies

Case Study Files

Tables Used in the Case Studies

Contents of Table emp

Contents of Table dept

Checking the Results of a Load

References and Notes

Case Study 1: Loading Variable-Length Data

Control File for Case Study 1

Running Case Study 1

Log File for Case Study 1

Case Study 2: Loading Fixed-Format Fields

Control File for Case Study 2

Datafile for Case Study 2

Running Case Study 2

Log File for Case Study 2

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

Control File for Case Study 3

Running Case Study 3

Log File for Case Study 3

Case Study 4: Loading Combined Physical Records

Control File for Case Study 4

Datafile for Case Study 4

Rejected Records

Running Case Study 4

Log File for Case Study 4

Bad File for Case Study 4

Case Study 5: Loading Data into Multiple Tables

Control File for Case Study 5

Datafile for Case Study 5

Running Case Study 5

Log File for Case Study 5

Loaded Tables for Case Study 5

Case Study 6: Loading Data Using the Direct Path Load Method

Control File for Case Study 6

Datafile for Case Study 6

Running Case Study 6

Log File for Case Study 6

Case Study 7: Extracting Data from a Formatted Report

Creating a BEFORE INSERT Trigger

Control File for Case Study 7

Datafile for Case Study 7

Running Case Study 7

Log File for Case Study 7

Case Study 8: Loading Partitioned Tables

Control File for Case Study 8

Table Creation

Datafile for Case Study 8

Running Case Study 8

Log File for Case Study 8

Case Study 9: Loading LOBFILEs (CLOBs)

Control File for Case Study 9

Datafiles for Case Study 9

Running Case Study 9

Log File for Case Study 9

Case Study 10: Loading REF Fields and VARRAYs

Control File for Case Study 10

Running Case Study 10

Log File for Case Study 10

Case Study 11: Loading Data in the Unicode Character Set

Control File for Case Study 11

Datafile for Case Study 11

Running Case Study 11

Log File for Case Study 11

Loaded Tables for Case Study 11

The Access Driver

External Table Restrictions

Location of Datafiles and Output Files

Using External Tables to Load Data

Parallel Access to External Tables

Performance Hints When Using External Tables

Behavior Differences Between SQL*Loader and External Tables

Multiple Primary Input Datafiles

Syntax and Datatypes

Rejected Rows

Byte-Order Marks

Default Character Sets and Date Masks

access_parameters Clause

record_format_info Clause

BYTE ORDER MARK (CHECK | NOCHECK)

STRING SIZES ARE IN

LOAD WHEN

BADFILE | NOBADFILE

DISCARDFILE | NODISCARDFILE

[directory object name:] filename

condition

range start : range end

field_definitions Clause

Using DBVERIFY to Validate Disk Blocks of a Single Datafile

Syntax

Parameters

Command-Line Interface

Sample DBVERIFY Output

Using DBVERIFY to Validate a Segment

Syntax

Parameters

Command-Line Interface

What Is the DBNEWID Utility?

Ramifications of Changing the DBID and DBNAME

Changing the DBID and DBNAME of a Database

Changing the DBID and Database Name

Changing Only the Database Name

Troubleshooting a DBID Change Operation

Troubleshooting a Database Name Change Operation

DBNEWID Syntax

Parameters

Restrictions and Usage Notes

Examples of Using DBNEWID

Changing Only the DBID

Changing the DBID and Database Name

Changing Only the Database Name

Introduction to the Metadata API

Previous Methods Used to Extract Metadata

Metadata API Components

Metadata API Features

Internet Computing

How Is the Metadata API Implemented?

DBMS_METADATA and Security

DBMS_METADATA Programmatic Interface

Using the DBMS_METADATA.FETCH_XML Procedure

Using the DBMS_METADATA.FETCH_DDL Procedure

Performance Tips for the Programmatic Interface of the Metadata API

DBMS_METADATA Browsing Interface

Example: Using the DBMS_METADATA Browsing Interface

Metadata API Example

mddemo.sql

PAYROLL_DEMO Output

Using the DB2 RESUME Option

Inclusions for Compatibility

LOG Statement

WORKDDN Statement

SORTDEVT and SORTNUM Statements

DISCARD Specification

Restrictions

SQL*Loader Syntax with DB2-Compatible Statements

Contents

Title and Copyright Information

List of Examples

List of Figures

List of Tables

Send Us Your Comments

Preface

What's New in Database Utilities?

Part I Export and Import

1 Export

2 Import

Part II SQL*Loader

3 SQL*Loader Concepts

4 SQL*Loader Command-Line Reference

5 SQL*Loader Control File Reference

6 Field List Reference

7 Loading Objects, LOBs, and Collections

8 SQL*Loader Log File Reference

9 Conventional and Direct Path Loads

10 SQL*Loader Case Studies

Part III External Tables

11 External Tables Concepts

12 External Tables Access Parameters

Part IV Other Utilities

13 DBVERIFY: Offline Database Verification Utility

14 DBNEWID Utility

15 Using the Metadata API

Part V Appendixes

A SQL*Loader Syntax Diagrams

B DB2/DXT User Notes

C Backus-Naur Form Syntax

Index