1/1018

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Syntax Diagrams
• Conventions

Changes in This Release for Oracle Database Utilities

• Changes in Oracle Database 12c Release 1 (12.1.0.2)
• Changes in Oracle Database 12c Release 1 (12.1.0.1)
  • New Features
  • Deprecated Features
  • Desupported Features

Part I Oracle Data Pump

1 Overview of Oracle Data Pump

• Data Pump Components
• How Does Data Pump Move Data?
  • Using Data File Copying to Move Data
  • Using Direct Path to Move Data
  • Using External Tables to Move Data
  • Using Conventional Path to Move Data
  • Using Network Link Import to Move Data
• Using Data Pump With CDBs
  • Using Data Pump to Move Databases Into a CDB
  • Using Data Pump to Move PDBs Within Or Between CDBs
• Required Roles for Data Pump Export and Import Operations
• What Happens During Execution of a Data Pump Job?
  • Coordination of a Job
  • Tracking Progress Within a Job
  • Filtering Data and Metadata During a Job
  • Transforming Metadata During a Job
  • Maximizing Job Performance
  • Loading and Unloading of Data
• Monitoring Job Status
  • Monitoring the Progress of Executing Jobs
• File Allocation
  • Specifying Files and Adding Additional Dump Files
  • Default Locations for Dump, Log, and SQL Files
  • Using Substitution Variables
• Exporting and Importing Between Different Database Releases
• SecureFiles LOB Considerations
• Data Pump Exit Codes
• Auditing Data Pump Jobs
• How Does Data Pump Handle Timestamp Data?
  • TIMESTAMP WITH TIME ZONE Restrictions
  • TIMESTAMP WITH LOCAL TIME ZONE Restrictions
• Character Set and Globalization Support Considerations
  • User Data
  • Data Definition Language (DDL)
  • Single-Byte Character Sets and Export and Import
  • Multibyte Character Sets and Export and Import

2 Data Pump Export

• What Is Data Pump Export?
• Invoking Data Pump Export
  • Data Pump Export Interfaces
  • Data Pump Export Modes
  • Network Considerations
• Filtering During Export Operations
  • Data Filters
  • Metadata Filters
• Parameters Available in Export's Command-Line Mode
  • ABORT_STEP
  • ACCESS_METHOD
  • ATTACH
  • CLUSTER
  • COMPRESSION
  • COMPRESSION_ALGORITHM
  • CONTENT
  • DATA_OPTIONS
  • DIRECTORY
  • DUMPFILE
  • ENCRYPTION
  • ENCRYPTION_ALGORITHM
  • ENCRYPTION_MODE
  • ENCRYPTION_PASSWORD
  • ENCRYPTION_PWD_PROMPT
  • ESTIMATE
  • ESTIMATE_ONLY
  • EXCLUDE
  • FILESIZE
  • FLASHBACK_SCN
  • FLASHBACK_TIME
  • FULL
  • HELP
  • INCLUDE
  • JOB_NAME
  • KEEP_MASTER
  • LOGFILE
  • LOGTIME
  • METRICS
  • NETWORK_LINK
  • NOLOGFILE
  • PARALLEL
  • PARFILE
  • QUERY
  • REMAP_DATA
  • REUSE_DUMPFILES
  • SAMPLE
  • SCHEMAS
  • SERVICE_NAME
  • SOURCE_EDITION
  • STATUS
  • TABLES
  • TABLESPACES
  • TRANSPORT_FULL_CHECK
  • TRANSPORT_TABLESPACES
  • TRANSPORTABLE
  • VERSION
  • VIEWS_AS_TABLES
• Commands Available in Export's Interactive-Command Mode
  • ADD_FILE
  • CONTINUE_CLIENT
  • EXIT_CLIENT
  • FILESIZE
  • HELP
  • KILL_JOB
  • PARALLEL
  • START_JOB
  • STATUS
  • STOP_JOB
• 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
• Syntax Diagrams for Data Pump Export

3 Data Pump Import

• What Is Data Pump Import?
• Invoking Data Pump Import
  • Data Pump Import Interfaces
  • Data Pump Import Modes
  • Network Considerations
• Filtering During Import Operations
  • Data Filters
  • Metadata Filters
• Parameters Available in Import's Command-Line Mode
  • ABORT_STEP
  • ACCESS_METHOD
  • ATTACH
  • CLUSTER
  • CONTENT
  • DATA_OPTIONS
  • DIRECTORY
  • DUMPFILE
  • ENCRYPTION_PASSWORD
  • ENCRYPTION_PWD_PROMPT
  • ESTIMATE
  • EXCLUDE
  • FLASHBACK_SCN
  • FLASHBACK_TIME
  • FULL
  • HELP
  • INCLUDE
  • JOB_NAME
  • KEEP_MASTER
  • LOGFILE
  • LOGTIME
  • MASTER_ONLY
  • METRICS
  • NETWORK_LINK
  • NOLOGFILE
  • PARALLEL
  • PARFILE
  • PARTITION_OPTIONS
  • QUERY
  • REMAP_DATA
  • REMAP_DATAFILE
  • REMAP_SCHEMA
  • REMAP_TABLE
  • REMAP_TABLESPACE
  • REUSE_DATAFILES
  • SCHEMAS
  • SERVICE_NAME
  • SKIP_UNUSABLE_INDEXES
  • SOURCE_EDITION
  • SQLFILE
  • STATUS
  • STREAMS_CONFIGURATION
  • TABLE_EXISTS_ACTION
  • TABLES
  • TABLESPACES
  • TARGET_EDITION
  • TRANSFORM
  • TRANSPORT_DATAFILES
  • TRANSPORT_FULL_CHECK
  • TRANSPORT_TABLESPACES
  • TRANSPORTABLE
  • VERSION
  • VIEWS_AS_TABLES (Network Import)
  • VIEWS_AS_TABLES (Non-Network Import)
• Commands Available in Import's Interactive-Command Mode
  • CONTINUE_CLIENT
  • EXIT_CLIENT
  • HELP
  • KILL_JOB
  • PARALLEL
  • START_JOB
  • STATUS
  • STOP_JOB
• Examples of Using Data Pump Import
  • Performing a Data-Only Table-Mode Import
  • Performing a Schema-Mode Import
  • Performing a Network-Mode Import
• Syntax Diagrams for Data Pump Import

4 Data Pump Legacy Mode

• Parameter Mappings
  • Using Original Export Parameters with Data Pump
  • Using Original Import Parameters with Data Pump
• Management of File Locations in Data Pump Legacy Mode
• Adjusting Existing Scripts for Data Pump Log Files and Errors
  • Log Files
  • Error Cases
  • Exit Status

5 Data Pump Performance

• Data Performance Improvements for Data Pump Export and Import
• Tuning Performance
  • Controlling Resource Consumption
  • Effect of Compression and Encryption on Performance
  • Memory Considerations When Exporting and Importing Statistics
• Initialization Parameters That Affect Data Pump Performance
  • Setting the Size Of the Buffer Cache In a Streams Environment

6 The Data Pump API

• How Does the Client Interface to the Data Pump API Work?
  • Job States
• What Are the Basic Steps in Using the Data Pump API?
• Examples of Using the Data Pump API

Part II SQL*Loader

7 SQL*Loader Concepts

• SQL*Loader Features
• SQL*Loader Parameters
• SQL*Loader Control File
• Input Data and Data Files
  • Fixed Record Format
  • Variable Record Format
  • Stream Record Format
  • Logical Records
  • Data Fields
• LOBFILEs and Secondary Data Files (SDFs)
• Data Conversion and Data Type Specification
• Discarded and Rejected Records
  • The Bad File
  • The Discard File
• Log File and Logging Information
• Conventional Path Loads, Direct Path Loads, and External Table Loads
  • Conventional Path Loads
  • Direct Path Loads
  • External Table Loads
  • Choosing External Tables Versus SQL*Loader
  • Behavior Differences Between SQL*Loader and External Tables
• Loading Objects, Collections, and LOBs
  • Supported Object Types
  • Supported Collection Types
  • Supported LOB Data Types
• Partitioned Object Support
• Application Development: Direct Path Load API
• SQL*Loader Case Studies
  • Case Study Files
  • Running the Case Studies
  • Case Study Log Files
  • Checking the Results of a Case Study

8 SQL*Loader Command-Line Reference

• Invoking SQL*Loader
  • Specifying Parameters on the Command Line
  • Alternative Ways to Specify SQL*Loader Parameters
  • Using SQL*Loader to Load Data Across a Network
• Command-Line Parameters for SQL*Loader
  • BAD
  • BINDSIZE
  • COLUMNARRAYROWS
  • CONTROL
  • DATA
  • DATE_CACHE
  • DEGREE_OF_PARALLELISM
  • DIRECT
  • DISCARD
  • DISCARDMAX
  • DNFS_ENABLE
  • DNFS_READBUFFERS
  • ERRORS
  • EXTERNAL_TABLE
  • FILE
  • LOAD
  • LOG
  • MULTITHREADING
  • NO_INDEX_ERRORS
  • PARALLEL
  • PARFILE
  • PARTITION_MEMORY
  • READSIZE
  • RESUMABLE
  • RESUMABLE_NAME
  • RESUMABLE_TIMEOUT
  • ROWS
  • SILENT
  • SKIP
  • SKIP_INDEX_MAINTENANCE
  • SKIP_UNUSABLE_INDEXES
  • STREAMSIZE
  • TRIM
  • USERID
• Exit Codes for Inspection and Display

9 SQL*Loader Control File Reference

• Control File Contents
  • Comments in the Control File
• Specifying Command-Line Parameters in the Control File
  • OPTIONS Clause
• Specifying File Names and Object Names
  • File Names That Conflict with SQL and SQL*Loader Reserved Words
  • Specifying SQL Strings
  • Operating System Considerations
• Identifying XMLType Tables
• Specifying Field Order
• Specifying Data Files
  • Examples of INFILE Syntax
  • Specifying Multiple Data Files
• Specifying CSV Format Files
• Identifying Data in the Control File with BEGINDATA
• Specifying Data File 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
  • Examples of Specifying a Discard File Name
  • Criteria for Discarded Records
  • How Discard Files Are Handled with LOBFILEs and SDFs
  • Specifying the Discard File from the Command Line
• Specifying a NULLIF Clause At the Table Level
• Specifying Datetime Formats At the Table Level
• Handling Different Character Encoding Schemes
  • Multibyte (Asian) Character Sets
  • Unicode Character Sets
  • Database Character Sets
  • Data File Character Sets
  • Input Character Conversion
  • Shift-sensitive Character Data
• Interrupted Loads
  • Discontinued Conventional Path Loads
  • Discontinued Direct Path Loads
  • 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
  • Table-Specific Loading Method
  • Table-Specific OPTIONS Parameter
  • Loading Records Based on a Condition
  • Specifying Default Data Delimiters
  • Handling Short Records with Missing Data
• Index Options
  • SORTED INDEXES Clause
  • SINGLEROW Option
• Benefits of Using Multiple INTO TABLE Clauses
  • Extracting Multiple Logical Records
  • Distinguishing Different Input Record Formats
  • Distinguishing Different Input Row Object Subtypes
  • Loading Data into Multiple Tables
  • Summary of Using Multiple INTO TABLE Clauses
• 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
  • Minimizing Memory Requirements for Bind Arrays
  • Calculating Bind Array Size for Multiple INTO TABLE Clauses

10 SQL*Loader Field List Reference

• 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 Data Type of a Data Field
• SQL*Loader Data Types
  • Nonportable Data Types
  • Portable Data Types
  • Data Type Conversions
  • Data Type Conversions for Datetime and Interval Data Types
  • Specifying Delimiters
  • How Delimited Data Is Processed
  • Conflicting Field Lengths for Character Data Types
• Specifying Field Conditions
  • Comparing Fields to BLANKS
  • Comparing Fields to Literals
• Using the WHEN, NULLIF, and DEFAULTIF Clauses
  • Examples of Using the WHEN, NULLIF, and DEFAULTIF Clauses
• Loading Data Across Different Platforms
• Byte Ordering
  • Specifying Byte Order
  • Using Byte Order Marks (BOMs)
• Loading All-Blank Fields
• Trimming Whitespace
  • Data Types for Which Whitespace Can Be Trimmed
  • Specifying Field Length for Data Types for Which Whitespace Can Be Trimmed
  • Relative Positioning of Fields
  • Leading Whitespace
  • Trimming Trailing Whitespace
  • Trimming Enclosed Fields
• How the PRESERVE BLANKS Option Affects Whitespace Trimming
  • How [NO] PRESERVE BLANKS Works with Delimiter Clauses
• 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 Strings to Load the ANYDATA Database Type
• Using SQL*Loader to Generate Data for Input
  • Loading Data Without Files
  • Setting a Column to a Constant Value
  • Setting a Column to an Expression Value
  • Setting a Column to the Data File Record Number
  • Setting a Column to the Current Date
  • Setting a Column to a Unique Sequence Number
  • Generating Sequence Numbers for Multiple Tables

11 Loading Objects, LOBs, and Collections

• 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
  • Loading Column Objects with User-Defined Constructors
• Loading Object Tables
  • Loading Object Tables with a Subtype
• Loading REF Columns
  • Specifying Table Names in a REF Clause
  • System-Generated OID REF Columns
  • Primary Key REF Columns
  • Unscoped REF Columns That Allow Primary Keys
• Loading LOBs
  • Loading LOB Data from a Primary Data File
  • Loading LOB Data from LOBFILEs
• Loading BFILE Columns
• Loading Collections (Nested Tables and VARRAYs)
  • Restrictions in Nested Tables and VARRAYs
  • Secondary Data Files (SDFs)
• Dynamic Versus Static SDF Specifications
• Loading a Parent Table Separately from Its Child Table
  • Memory Issues When Loading VARRAY Columns

12 Conventional and Direct Path Loads

• Data Loading Methods
  • Loading ROWID Columns
• 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
  • Indexes Left in an Unusable State
  • Using Data Saves to Protect Against Data Loss
  • Data Recovery During Direct Path Loads
  • Loading Long Data Fields
  • Auditing SQL*Loader Operations That Use Direct Path Mode
• Optimizing Performance of Direct Path Loads
  • Preallocating Storage for Faster Loading
  • Presorting Data for Faster Indexing
  • Infrequent Data Saves
  • Minimizing Use of the Redo Log
  • 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 Path Loads, Integrity Constraints, and Triggers
  • Integrity Constraints
  • Database Insert Triggers
  • 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
  • Enabling Constraints After a Parallel Direct Path Load
  • PRIMARY KEY and UNIQUE KEY Constraints
• General Performance Improvement Hints

13 SQL*Loader Express

• What is SQL*Loader Express Mode?
• Using SQL*Loader Express Mode
  • Default Values Used by SQL*Loader Express Mode
• SQL*Loader Express Mode Parameter Reference
  • BAD
  • CHARACTERSET
  • CSV
  • DATA
  • DATE_FORMAT
  • DEGREE_OF_PARALLELISM
  • DIRECT
  • DNFS_ENABLE
  • DNFS_READBUFFERS
  • ENCLOSED_BY
  • EXTERNAL_TABLE
  • FIELD_NAMES
  • LOAD
  • NULLIF
  • OPTIONALLY_ENCLOSED_BY
  • PARFILE
  • SILENT
  • TABLE
  • TERMINATED_BY
  • TIMESTAMP_FORMAT
  • TRIM
  • USERID
• SQL*Loader Express Mode Syntax Diagrams

Part III External Tables

14 External Tables Concepts

• How Are External Tables Created?
  • Location of Data Files and Output Files
  • Access Parameters
• Data Type Conversion During External Table Use

15 The ORACLE_LOADER Access Driver

• access_parameters Clause
• record_format_info Clause
  • FIXED length
  • VARIABLE size
  • DELIMITED BY
  • CHARACTERSET
  • EXTERNAL VARIABLE DATA
  • PREPROCESSOR
  • LANGUAGE
  • TERRITORY
  • DATA IS...ENDIAN
  • BYTEORDERMARK (CHECK | NOCHECK)
  • STRING SIZES ARE IN
  • LOAD WHEN
  • BADFILE | NOBADFILE
  • DISCARDFILE | NODISCARDFILE
  • LOGFILE | NOLOGFILE
  • SKIP
  • FIELD NAMES
  • READSIZE
  • DISABLE_DIRECTORY_LINK_CHECK
  • DATE_CACHE
  • string
  • condition_spec
  • [directory object name:] [filename]
  • condition
  • IO_OPTIONS clause
  • DNFS_DISABLE | DNFS_ENABLE
  • DNFS_READBUFFERS
• field_definitions Clause
  • delim_spec
  • trim_spec
  • MISSING FIELD VALUES ARE NULL
  • field_list
  • pos_spec Clause
  • datatype_spec Clause
  • init_spec Clause
• column_transforms Clause
  • transform
• Parallel Loading Considerations for the ORACLE_LOADER Access Driver
• Performance Hints When Using the ORACLE_LOADER Access Driver
• Restrictions When Using the ORACLE_LOADER Access Driver
• Reserved Words for the ORACLE_LOADER Access Driver

16 The ORACLE_DATAPUMP Access Driver

• access_parameters Clause
  • comments
  • COMPRESSION
  • ENCRYPTION
  • LOGFILE | NOLOGFILE
  • VERSION Clause
  • Effects of Using the SQL ENCRYPT Clause
• Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver
  • Parallel Loading and Unloading
  • Combining Dump Files
• Supported Data Types
• Unsupported Data Types
  • Unloading and Loading BFILE Data Types
  • Unloading LONG and LONG RAW Data Types
  • Unloading and Loading Columns Containing Final Object Types
  • Tables of Final Object Types
• Performance Hints When Using the ORACLE_DATAPUMP Access Driver
• Restrictions When Using the ORACLE_DATAPUMP Access Driver
• Reserved Words for the ORACLE_DATAPUMP Access Driver

Part IV Other Utilities

17 ADRCI: ADR Command Interpreter

• About the ADR Command Interpreter (ADRCI) Utility
• Definitions
• Starting ADRCI and Getting Help
  • Using ADRCI in Interactive Mode
  • Getting Help
  • Using ADRCI in Batch Mode
• Setting the ADRCI Homepath Before Using ADRCI Commands
• Viewing the Alert Log
• Finding Trace Files
• Viewing Incidents
• Packaging Incidents
  • About Packaging Incidents
  • Creating Incident Packages
• ADRCI Command Reference
  • CREATE REPORT
  • ECHO
  • EXIT
  • HOST
  • IPS
  • PURGE
  • QUIT
  • RUN
  • SELECT
  • SET BASE
  • SET BROWSER
  • SET CONTROL
  • SET ECHO
  • SET EDITOR
  • SET HOMEPATH
  • SET TERMOUT
  • SHOW ALERT
  • SHOW BASE
  • SHOW CONTROL
  • SHOW HM_RUN
  • SHOW HOMEPATH
  • SHOW HOMES
  • SHOW INCDIR
  • SHOW INCIDENT
  • SHOW LOG
  • SHOW PROBLEM
  • SHOW REPORT
  • SHOW TRACEFILE
  • SPOOL
• Troubleshooting ADRCI

18 DBVERIFY: Offline Database Verification Utility

• Using DBVERIFY to Validate Disk Blocks of a Single Data File
  • DBVERIFY Syntax When Validating Blocks of a Single File
  • DBVERIFY Parameters When Validating Blocks of a Single File
  • Sample DBVERIFY Output For a Single Data File
• Using DBVERIFY to Validate a Segment
  • DBVERIFY Syntax When Validating a Segment
  • DBVERIFY Parameters When Validating a Single Segment
  • Sample DBVERIFY Output For a Validated Segment

19 DBNEWID Utility

• What Is the DBNEWID Utility?
• Ramifications of Changing the DBID and DBNAME
  • Considerations for Global Database Names
• Changing the DBID and DBNAME of a Database
  • Changing the DBID and Database Name
  • Changing Only the Database ID
  • Changing Only the Database Name
  • Troubleshooting DBNEWID
• DBNEWID Syntax
  • DBNEWID Parameters
  • Restrictions and Usage Notes
  • Additional Restrictions for Releases Earlier Than Oracle Database 10g

20 Using LogMiner to Analyze Redo Log Files

• LogMiner Benefits
• Introduction to LogMiner
  • LogMiner Configuration
  • Directing LogMiner Operations and Retrieving Data of Interest
• Using LogMiner in a CDB
  • LogMiner V$ Views and DBA Views in a CDB
  • The V$LOGMNR_CONTENTS View in a CDB
  • Enabling Supplemental Logging in a CDB
  • Using a Flat File Dictionary in a CDB
• LogMiner Dictionary Files and Redo Log Files
  • LogMiner Dictionary Options
  • Redo Log File Options
• Starting LogMiner
• Querying V$LOGMNR_CONTENTS for Redo Data of Interest
  • How the V$LOGMNR_CONTENTS View Is Populated
  • Querying V$LOGMNR_CONTENTS Based on Column Values
  • Querying V$LOGMNR_CONTENTS Based on XMLType Columns and Tables
• Filtering and Formatting Data Returned to V$LOGMNR_CONTENTS
  • Showing Only Committed Transactions
  • Skipping Redo Corruptions
  • Filtering Data by Time
  • Filtering Data by SCN
  • Formatting Reconstructed SQL Statements for Re-execution
  • Formatting the Appearance of Returned Data for Readability
• Reapplying DDL Statements Returned to V$LOGMNR_CONTENTS
• Calling DBMS_LOGMNR.START_LOGMNR Multiple Times
• Supplemental Logging
  • Database-Level Supplemental Logging
  • Disabling Database-Level Supplemental Logging
  • Table-Level Supplemental Logging
  • Tracking DDL Statements in the LogMiner Dictionary
  • DDL_DICT_TRACKING and Supplemental Logging Settings
  • DDL_DICT_TRACKING and Specified Time or SCN Ranges
• Accessing LogMiner Operational Information in Views
  • Querying V$LOGMNR_LOGS
  • Querying Views for Supplemental Logging Settings
• Steps in a Typical LogMiner Session
  • Typical LogMiner Session Task 1: Enable Supplemental Logging
  • Typical LogMiner Session Task 2: Extract a LogMiner Dictionary
  • Typical LogMiner Session Task 3: Specify Redo Log Files for Analysis
  • Typical LogMiner Session Task 4: Start LogMiner
  • Typical LogMiner Session Task 5: Query V$LOGMNR_CONTENTS
  • Typical LogMiner Session Task 6: End the LogMiner Session
• Examples Using LogMiner
  • Examples of Mining by Explicitly Specifying the Redo Log Files of Interest
  • Examples of Mining Without Specifying the List of Redo Log Files Explicitly
  • Example Scenarios
• Supported Data Types, Storage Attributes, and Database and Redo Log File Versions
  • Supported Data Types and Table Storage Attributes
  • Unsupported Data Types and Table Storage Attributes
  • Supported Databases and Redo Log File Versions
  • SecureFiles LOB Considerations

21 Using the Metadata APIs

• Why Use the DBMS_METADATA API?
• Overview of the DBMS_METADATA API
• Using the DBMS_METADATA API to Retrieve an Object's Metadata
  • Typical Steps Used for Basic Metadata Retrieval
  • Retrieving Multiple Objects
  • Placing Conditions on Transforms
  • Accessing Specific Metadata Attributes
• Using the DBMS_METADATA API to Re-Create a Retrieved Object
• Using the DBMS_METADATA API to Retrieve Collections of Different Object Types
  • Filtering the Return of Heterogeneous Object Types
• Using the DBMS_METADATA_DIFF API to Compare Object Metadata
• Performance Tips for the Programmatic Interface of the DBMS_METADATA API
• Example Usage of the DBMS_METADATA API
  • What Does the DBMS_METADATA Example Do?
  • Output Generated from the GET_PAYROLL_TABLES Procedure
• Summary of DBMS_METADATA Procedures
• Summary of DBMS_METADATA_DIFF Procedures

22 Original Export

• What is the Export Utility?
• Before Using Export
  • Running catexp.sql or catalog.sql
  • Ensuring Sufficient Disk Space for Export Operations
  • Verifying Access Privileges for Export and Import Operations
• Invoking Export
  • Invoking Export as SYSDBA
  • Command-Line Entries
  • Parameter Files
  • Interactive Mode
  • Getting Online Help
• Export Modes
  • Table-Level and Partition-Level Export
• Export Parameters
  • BUFFER
  • COMPRESS
  • CONSISTENT
  • CONSTRAINTS
  • DIRECT
  • FEEDBACK
  • FILE
  • FILESIZE
  • FLASHBACK_SCN
  • FLASHBACK_TIME
  • FULL
  • GRANTS
  • HELP
  • INDEXES
  • LOG
  • OBJECT_CONSISTENT
  • OWNER
  • PARFILE
  • QUERY
  • RECORDLENGTH
  • RESUMABLE
  • RESUMABLE_NAME
  • RESUMABLE_TIMEOUT
  • ROWS
  • STATISTICS
  • TABLES
  • TABLESPACES
  • TRANSPORT_TABLESPACE
  • TRIGGERS
  • TTS_FULL_CHECK
  • USERID (username/password)
  • VOLSIZE
• 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
• 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 Considerations for Direct Path Exports
  • Restrictions for Direct Path Exports
• Network Considerations
  • Transporting Export Files Across a Network
  • Exporting with Oracle Net
• Character Set and Globalization Support Considerations
  • User Data
  • Data Definition Language (DDL)
  • Single-Byte Character Sets and Export and Import
  • Multibyte Character Sets and Export and Import
• Using Instance Affinity with Export and Import
• Considerations When Exporting Database Objects
  • Exporting Sequences
  • Exporting LONG and LOB Data Types
  • Exporting Foreign Function Libraries
  • Exporting Offline Locally Managed Tablespaces
  • Exporting Directory Aliases
  • Exporting BFILE Columns and Attributes
  • Exporting 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 of Export and Import
  • Restrictions When Using Different Releases of Export and Import
  • Examples of Using Different Releases of Export and Import

23 Original Import

• What Is the Import Utility?
  • Table Objects: Order of Import
• Before Using Import
  • Running catexp.sql or catalog.sql
  • Verifying Access Privileges for Import Operations
  • Processing Restrictions
• 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
  • Parameter Files
  • Interactive Mode
  • Invoking Import As SYSDBA
  • Getting Online Help
• Import Modes
• Import Parameters
  • BUFFER
  • COMMIT
  • COMPILE
  • CONSTRAINTS
  • DATA_ONLY
  • DATAFILES
  • DESTROY
  • FEEDBACK
  • FILE
  • FILESIZE
  • FROMUSER
  • FULL
  • GRANTS
  • HELP
  • IGNORE
  • INDEXES
  • INDEXFILE
  • LOG
  • PARFILE
  • RECORDLENGTH
  • RESUMABLE
  • RESUMABLE_NAME
  • RESUMABLE_TIMEOUT
  • ROWS
  • SHOW
  • SKIP_UNUSABLE_INDEXES
  • STATISTICS
  • STREAMS_CONFIGURATION
  • STREAMS_INSTANTIATION
  • TABLES
  • TABLESPACES
  • TOID_NOVALIDATE
  • TOUSER
  • TRANSPORT_TABLESPACE
  • TTS_OWNERS
  • USERID (username/password)
  • VOLSIZE
• 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 Import Using Pattern Matching to Import Various Tables
• Exit Codes for Inspection and Display
• Error Handling During an Import
  • Row Errors
  • Errors Importing Database Objects
• 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
• Network Considerations
• Character Set and Globalization Support Considerations
  • User Data
  • Data Definition Language (DDL)
  • Single-Byte Character Sets
  • Multibyte Character Sets
• Using Instance Affinity
• 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 LOB Columns When Triggers Are Present
  • Importing Views
  • Importing Partitioned Tables
• Support for Fine-Grained Access Control
• Snapshots and Snapshot Logs
  • Snapshot Log
  • Snapshots
• Transportable Tablespaces
• Storage Parameters
  • The OPTIMAL Parameter
  • Storage Parameters for OID Indexes and LOB Columns
  • Overriding Storage Parameters
• 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
• Tuning Considerations for Import Operations
  • Changing System-Level Options
  • Changing Initialization Parameters
  • Changing Import Options
  • Dealing with Large Amounts of LOB Data
  • Dealing with Large Amounts of LONG Data
• Using Different Releases of Export and Import
  • Restrictions When Using Different Releases of Export and Import
  • Examples of Using Different Releases of Export and Import

Part V Appendixes

A SQL*Loader Syntax Diagrams

Index