Oracle8i Utilities
Release 8.1.5

A67792-01

Library

Product

Index

Next

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Export

What is the Export Utility?
New in this Release
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
Specifying Export Parameters on the Command Line or in the Parameter File
Export Parameters
BUFFER
COMPRESS
CONSISTENT
CONSTRAINTS
DIRECT
FEEDBACK
FILE
FILESIZE
FULL
GRANTS
HELP
INCTYPE
INDEXES
LOG
OWNER
PARFILE
QUERY
RECORD
RECORDLENGTH
ROWS
STATISTICS
TABLES
TABLESPACES
TRANSPORT_TABLESPACE
USERID
VOLSIZE
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
Incremental, Cumulative, and Complete Exports
Base Backups
Incremental Exports
Cumulative Exports
Complete Exports
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
Multi-Byte Character Sets and Export and Import
Instance Affinity and Export
Fine-Grained Access Support
Considerations in 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
Exporting Object Type Definitions
Exporting Nested Tables
Exporting Advanced Queue (AQ) Tables
Transportable Tablespaces
Using Different Versions of Export
Using a Previous Version of Export
Using a Higher Version Export
Creating Oracle Release 8.0 Export Files from an Oracle8i Database
Creating Oracle Release 7 Export Files from an Oracle8i Database
Excluded Objects

2
Import

What is the Import Utility?
New in this Release
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
CONSTRAINTS
DATAFILES
DESTROY
FEEDBACK
FILE
FILESIZE
FROMUSER
FULL
GRANTS
HELP
IGNORE
INCTYPE
INDEXES
INDEXFILE
LOG
PARFILE
RECALCULATE_STATISTICS
RECORDLENGTH
ROWS
SHOW
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES
TOID_NOVALIDATE
TOUSER
TRANSPORT_TABLESPACE
TTS_OWNERS
USERID
VOLSIZE
Using Table-Level and Partition-Level Export and Import
Guidelines for Using Partition-Level Import
Migrating Data Across Partitions and Tables
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 and Materialized Views
Import and Instance Affinity
Fine-Grained Access Support
Storage Parameters
Read-Only Tablespaces
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 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 Advanced Queue (AQ) Tables
Importing LONG Columns
Importing Views
Importing Tables
Transportable Tablespaces
Importing Statistics
Using Export Files from a Previous Oracle Release
Using Oracle Version 7 Export Files
Using Oracle Version 6 Export Files
Using Oracle Version 5 Export Files
The CHARSET Parameter

3 SQL*Loader Concepts

SQL*Loader Basics
SQL*Loader Control File
Input Data and Datafiles
Logical Records
Datafields
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
Loading Objects, Collections, and LOBs
Supported Object Types
Supported Collection Types
Supported LOB Types
New SQL*Loader DDL Behavior and Restrictions
New SQL*Loader DDL Support for Objects, Collections, and LOBs
Partitioned and Sub-Partitioned Object Support
Application Development: Direct Path Load API

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 Partitioned Tables
Control File
Table Creation
Input Data File
Invoking SQL*Loader
Log File
Case 9: Loading LOBFILEs (CLOBs)
Control File
Input Data Files
Invoking SQL*Loader
Log File
Case 10: Loading REF Fields and VARRAYs
Control File
Invoking SQL*Loader
Log File

5 SQL*Loader Control File Reference

SQL*Loader's Data Definition Language (DDL) Syntax Diagrams
The SQL*Loader Control File
SQL*Loader DDL Syntax Diagram Notation
High-Level Syntax Diagrams
Expanded DDL Syntax
Position Specification
Field Condition
Column Name
Precision vs. Length
Date Mask
Delimiter Specification
Control File Basics
Comments in the Control File
Specifying Command-Line Parameters in the Control File
OPTIONS
Specifying Filenames and Objects Names
Filenames that Conflict with SQL and SQL*Loader Reserved Words
Specifying SQL Strings
Operating System Considerations
Identifying Data in the Control File with BEGINDATA
INFILE: Specifying Datafiles
Naming the File
Specifying Multiple Datafiles
Specifying READBUFFERS
Specifying Datafile Format and Buffering
File Processing Example
BADFILE: Specifying the Bad File
Rejected Records
Specifying the Discard File
Handling Different Character Encoding Schemes
Multi-Byte (Asian) Character Sets
Input Character Conversion
Loading into Empty and Non-Empty Tables
Loading into Empty Tables
Loading into Non-Empty Tables
APPEND
REPLACE
TRUNCATE
Continuing an Interrupted Load
Assembling Logical Records from Physical Records
Using 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 Filler 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
SQL*Loader Datatypes
Non-Portable Datatypes
Portable Datatypes
Numeric External Datatypes
Datatype Conversions
Specifying Delimiters
Conflicting Character Datatype Field Lengths
Loading Data Across Different Platforms
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
Loading Column Objects
Loading Column Objects in Stream Record Format
Loading Column Objects in Variable Record Format
Loading Nested Column Objects
Specifying NULL Values for Objects
Loading Object Tables
Loading REF Columns
Loading LOBs
Internal LOBs (BLOB, CLOB, NCLOB)
External LOB (BFILE)
Loading Collections (Nested Tables and VARRAYs)
Memory Issues when Loading VARRAY Columns

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)
READSIZE (read buffer)
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
Oracle 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

9 Offline Database Verification Utility

DBVERIFY
Restrictions
Syntax
Sample DBVERIFY Output

A SQL*Loader Reserved Words

Reserved Word List and Information

B DB2/DXT User Notes

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

Index



Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Index