Oracle8 Utilities
Release 8.0

A58244-01

Library

Product

Index

Next

Contents

Title and Copyright Information

Send Us Your Comments

Preface

The Oracle Utilities
Audience
How Oracle8 Utilities Is Organized
Part I: Export/Import
Part II: SQL*Loader
Part III: NLS Utilities
Part IV: Offline Database Verification Utility
Conventions Used in This Manual
Text of the Manual
We Welcome Your Comments

Part I Export/Import

1 Export

What is the Export Utility?
Reading the Contents of an Export File
Access Privileges
Export Modes
Understanding Table-Level and Partition-Level Export
Using Export
Before Using Export
Invoking Export
Getting Online Help
The Parameter File
Export Parameters
BUFFER
COMPRESS
CONSISTENT
CONSTRAINTS
DIRECT
FEEDBACK
FILE
FULL
GRANTS
HELP
INCTYPE
INDEXES
LOG
OWNER
PARFILE
POINT_IN_TIME_RECOVER
RECORD
RECORDLENGTH
RECOVERY_TABLESPACES
ROWS
STATISTICS
TABLES
USERID
Parameter Interactions
Example Export Sessions
Example Export Session in Full Database Mode
Example Export Session in User Mode
Example Export Sessions in Table Mode
Example Export Session Using Partition-Level Export
Using the Interactive Method
Restrictions
Warning, Error, and Completion Messages
Log File
Warning Messages
Fatal Error Messages
Completion Messages
Direct Path Export
Invoking a Direct Path Export
Character Set Conversion
Performance Issues
Restrictions
Incremental, Cumulative, and Complete Exports
Restrictions
Base Backups
Incremental Exports
Cumulative Exports
Complete Exports
Benefits
A Scenario
Which Data Is Exported?
Example Incremental Export Session
System Tables
Network Considerations
Transporting Export Files Across a Network
Exporting and Importing with Net8
Character Set and NLS Considerations
Character Set Conversion
NCHAR Conversion During Export and Import
Single-Byte Character Sets During Export and Import
Multi-Byte Character Sets and Export and Import
Considerations in Exporting Database Objects
Exporting Sequences
Exporting LONG Datatypes
Exporting Foreign Function Libraries
Exporting Directory Aliases
Exporting BFILE Columns and Attributes
Exporting Array Data
Exporting Object Type Definitions
Exporting Advanced Queue (AQ) Tables
Exporting Nested Tables
Using Different Versions of Export
Using a Previous Version of Export
Using a Higher Version Export
Creating Oracle Release 7 Export Files from an Oracle8 Server
Excluded Objects
Exporting to Version 6

2
Import

What is the Import Utility?
Table Objects: Order of Import
Compatibility
Import Modes
Understanding Table-Level and Partition-Level Import
Using Import
Before Using Import
Invoking Import
Getting Online Help
The Parameter File
Privileges Required to Use Import
Access Privileges
Importing Objects into Your Own Schema
Importing Grants
Importing Objects into Other Schemas
Importing System Objects
User Privileges
Importing into Existing Tables
Manually Creating Tables before Importing Data
Disabling Referential Constraints
Manually Ordering the Import
Import Parameters
ANALYZE
BUFFER
CHARSET
COMMIT
DESTROY
FEEDBACK
FILE
FROMUSER
FULL
GRANTS
HELP
IGNORE
INCTYPE
INDEXES
INDEXFILE
LOG
PARFILE
POINT_IN_TIME_RECOVER
RECORDLENGTH
ROWS
SHOW
SKIP_UNUSABLE_INDEXES
TABLES
TOUSER
USERID
Using Table-Level and Partition-Level Export and Import
Guidelines for Using Partition-Level Import
Migrating Data Across Partitions and Tables
Combining Multiple Partitions into One
Reconfiguring Partitions
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
Using the Interactive Method
Importing Incremental, Cumulative, and Complete Export Files
Restoring a Set of Objects
Importing Object Types and Foreign Function Libraries from an Incremental Export File
Controlling Index Creation and Maintenance
Index Creation and Maintenance Controls
Delaying Index Creation
Reducing Database Fragmentation
Warning, Error, and Completion Messages
Error Handling
Row Errors
Errors Importing Database Objects
Fatal Errors
Network Considerations
Transporting Export Files Across a Network
Exporting and Importing with Net8
Import and Snapshots
Master Table
Snapshot Log
Snapshots
Storage Parameters
Read-Only Tablespaces
Rollback Segments
Dropping a Tablespace
Reorganizing Tablespaces
Character Set and NLS Considerations
Character Set Conversion
Import and Single-Byte Character Sets
Import and Multi-Byte Character Sets
Considerations for Importing Database Objects
Importing Object Identifiers
Importing Existing Object Tables and Tables That Contain Object Types
Importing Nested Tables
Importing REF Data
Importing Array Data
Importing BFILE Columns and Directory Aliases
Importing Foreign Function Libraries
Importing Stored Procedures, Functions, and Packages
Importing Advanced Queue (AQ) Tables
Importing LONG Columns
Importing Views
Generating Statistics on Imported Data
Using Oracle7 Export Files
Check Constraints on DATE Columns
Using Oracle Version 6 Export Files
CHAR columns
Syntax of Integrity Constraints
Status of Integrity Constraints
Length of DEFAULT Column Values
Using Oracle Version 5 Export Files

Part II SQL*Loader

3 SQL*Loader Concepts

SQL*Loader Basics
SQL*Loader Control File
Control File Contents and Storage
Data Definition Language (DDL)
Input Data and Datafiles
Input Data Formats
Data Conversion and Datatype Specification
Discarded and Rejected Records
The Bad File
SQL*Loader Discards
Log File and Logging Information
Conventional Path Load versus Direct Path Load
Partitioned Object Support

4 SQL*Loader Case Studies

The Case Studies
Case Study Files
Tables Used in the Case Studies
Contents of Table EMP
Contents of Table DEPT
References and Notes
Running the Case Study SQL Scripts
Case 1: Loading Variable-Length Data
Control File
Invoking SQL*Loader
Log File
Case 2: Loading Fixed-Format Fields
Control File
Datafile
Invoking SQL*Loader
Log File
Case 3: Loading a Delimited, Free-Format File
Control File
Invoking SQL*Loader
Log File
Case 4: Loading Combined Physical Records
Control File
Data File
Invoking SQL*Loader
Log File
Bad File
Case 5: Loading Data into Multiple Tables
Control File
Data File
Invoking SQL*Loader
Log File
Loaded Tables
Case 6: Loading Using the Direct Path Load Method
Control File
Invoking SQL*Loader
Log File
Case 7: Extracting Data from a Formatted Report
Data File
Insert Trigger
Control File
Invoking SQL*Loader
Log File
Dropping the Insert Trigger and the Global-Variable Package
Case 8: Loading a Fixed Record Length Format File
Control File
Table Creation
Input Data File
Invoking SQL*Loader
Log File

5 SQL*Loader Control File Reference

Overview
Data Definition Language (DDL) Syntax
High-Level Syntax Diagrams
Expanded Clauses and Their Functionality
Position Specification
pos_spec
Field Condition
field_condition
Column Name
column_name
Datatype Specification
datatype_spec
Precision vs. Length
precision
length
Date Mask
Delimiter Specification
delimiter_spec
Comments
Specifying Command-Line Parameters in the Control File
OPTIONS
Specifying RECOVERABLE and UNRECOVERABLE
Specifying Filenames and Database Objects
Database Object Names within Double Quotation Marks
SQL String within Double Quotation Marks
Filenames within Single Quotation Marks
Quotation Marks in Quoted Strings
Backslash Escape Character
Using a Backslash in Filenames
Including Data in the Control File with BEGINDATA
Identifying Datafiles
Naming the File
Specifying Multiple Datafiles
Examples of How to Specify a Datafile
Specifying READBUFFERS
Specifying Datafile Format and Buffering
File Processing Example
Specifying the Bad File
Examples of How to Specify a Bad File
Rejected Records
Integrity Constraints
Specifying the Discard File
Using a Control-File Definition
Examples of How to Specify a Discard File
Discarded Records
Limiting the Number of Discards
Handling Different Character Encoding Schemes
Multi-Byte (Asian) Character Sets
Input Character Conversion
Loading into Empty and Non-Empty Tables
How Non-Empty Tables are Affected
INSERT
APPEND
REPLACE
TRUNCATE
Specifying One Method for All Tables
Continuing an Interrupted Load
State of Tables and Indexes
Using the Log File
Dropping Indexes
Continuing Single Table Loads
Continuing Multiple Table Conventional Loads
Continuing Multiple Table Direct Loads
Assembling Logical Records from Physical Records
Examples of How to Specify CONTINUEIF
Loading Logical Records into Tables
Specifying Table Names
Table-Specific Loading Method
Table-Specific OPTIONS keyword
Choosing which Rows to Load
Specifying Default Data Delimiters
Handling Short Records with Missing Data
Index Options
SORTED INDEXES Option
SINGLEROW Option
Specifying Field Conditions
Comparing Fields to BLANKS
Comparing Fields to Literals
Specifying Columns and Fields
Specifying the Datatype of a Data Field
Specifying the Position of a Data Field
Using POSITION with Data Containing TABs
Using POSITION with Multiple Table Loads
Using Multiple INTO TABLE Statements
Extracting Multiple Logical Records
Distinguishing Different Input Record Formats
Loading Data into Multiple Tables
Summary
Generating Data
Loading Data Without Files
Setting a Column to a Constant Value
Setting a Column to the Datafile Record Number
Setting a Column to the Current Date
Setting a Column to a Unique Sequence Number
Generating Sequence Numbers for Multiple Tables
Specifying Datatypes
Datatype Conversions
Native Datatypes
Character Datatypes
Numeric External Datatypes
Specifying Delimiters
Conflicting Character Datatype Field Lengths
Loading Data Across Different Operating Systems
Determining the Size of the Bind Array
Minimum Requirements
Performance Implications
Specifying Number of Rows vs. Size of Bind Array
Calculations
Minimizing Memory Requirements for the Bind Array
Multiple INTO TABLE Statements
Generated Data
Setting a Column to Null or Zero
DEFAULTIF Clause
NULLIF Keyword
Null Columns at the End of a Record
Loading All-Blank Fields
Trimming Blanks and Tabs
Datatypes
Field Length Specifications
Relative Positioning of Fields
Leading Whitespace
Trailing Whitespace
Enclosed Fields
Trimming Whitespace: Summary
Preserving Whitespace
PRESERVE BLANKS Keyword
Applying SQL Operators to Fields
Referencing Fields
Referencing Fields That Are SQL*Loader Keywords
Common Uses
Combinations of Operators
Use with Date Mask
Interpreting Formatted Fields

6 SQL*Loader Command-Line Reference

SQL*Loader Command Line
Using Command-Line Keywords
Specifying Keywords in the Control File
Command-Line Keywords
BAD (bad file)
BINDSIZE (maximum size)
CONTROL (control file)
DATA (data file)
DIRECT (data path)
DISCARD (discard file)
DISCARDMAX (discards to disallow)
ERRORS (errors to allow)
FILE (file to load into)
LOAD (records to load)
LOG (log file)
PARFILE (parameter file)
PARALLEL (parallel load)
ROWS (rows per commit)
SILENT (feedback mode)
SKIP (records to skip)
USERID (username/password)
Index Maintenance Options
SKIP_UNUSABLE_INDEXES
SKIP_INDEX_MAINTENANCE
Exit Codes for Inspection and Display

7 SQL*Loader: Log File Reference

Header Information
Global Information
Table Information
Datafile Information
Table Load Information
Summary Statistics
Oracle8 Statistics Reporting to the Log

8 SQL*Loader: Conventional and Direct Path Loads

Data Loading Methods
Conventional Path Load
Direct Path Load
Using Direct Path Load
Setting Up for Direct Path Loads
Specifying a Direct Path Load
Building Indexes
Indexes Left in Index Unusable State
Data Saves
Recovery
Loading LONG Data Fields
Maximizing Performance of Direct Path Loads
Pre-allocating Storage for Faster Loading
Pre-sorting Data for Faster Indexing
Infrequent Data Saves
Minimizing Use of the Redo Log
Disable Archiving
Specifying UNRECOVERABLE
NOLOG Attribute
Avoiding Index Maintenance
Direct Loads, Integrity Constraints, and Triggers
Integrity Constraints
Database Insert Triggers
Permanently Disabled Triggers & Constraints
Alternative: Concurrent Conventional Path Loads
Parallel Data Loading Models
Concurrent Conventional Path Loads
Inter-Segment Concurrency with Direct Path
Intra-Segment Concurrency with Direct Path
Restrictions on Parallel Direct Path Loads
Initiating Multiple SQL*Loader Sessions
Options Keywords for Parallel Direct Path Loads
Enabling Constraints After a Parallel Direct Path Load
General Performance Improvement Hints

Part III NLS Utilities

9 National Language Support Utilities

NLS Data Installation Utility
Overview
Syntax
Return Codes
Usage
NLS Data Object Files
NLS Configuration Utility
Overview
Syntax
Menus
NLS Calendar Utility
Overview
Syntax
Usage

Part IV Offline Database Verification Utility

10 Offline Database Verification Utility

DB_VERIFY
Restrictions
Syntax
Enterprise Manager
Sample DB_VERIFY Output

A SQL*Loader Reserved Words

B DB2/DXT User Notes

SQL*Loader Extensions to the DB2 Load Utility
Using the DB2 RESUME Option
Inclusions for Compatibility
LOG Statement
WORKDDN Statement
SORTDEVT and SORTNUM Statements
DISCARD Specification
Restrictions
FORMAT Statement
PART Statement
SQL/DS Option
DBCS Graphic Strings
SQL*Loader Syntax with DB2-compatible Statements


Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Index