Table of Contents
- Title and Copyright Information
- Preface
-
Part I Oracle Data Pump
-
1
Overview of Oracle Data Pump
- 1.1 Oracle Data Pump Components
- 1.2 How Does Oracle Data Pump Move Data?
- 1.3 Using Oracle Data Pump With CDBs
- 1.4 Cloud Premigration Advisor Tool
- 1.5 Required Roles for Oracle Data Pump Export and Import Operations
-
1.6
What Happens During the Processing of an
Oracle Data Pump Job?
- 1.6.1 Coordination of an Oracle Data Pump Job
- 1.6.2 Tracking Progress Within an Oracle Data Pump Job
- 1.6.3 Filtering Data and Metadata During an Oracle Data Pump Job
- 1.6.4 Transforming Metadata During an Oracle Data Pump Job
- 1.6.5 Maximizing Job Performance of Oracle Data Pump
- 1.6.6 Loading and Unloading Data with Oracle Data Pump
- 1.7 How to Monitor Status of Oracle Data Pump Jobs
- 1.8 How to Monitor the Progress of Running Jobs with V$SESSION_LONGOPS
- 1.9 File Allocation with Oracle Data Pump
- 1.10 Exporting and Importing Between Different Oracle Database Releases
- 1.11 Exporting and Importing Blockchain Tables with Oracle Data Pump
- 1.12 Unload and Load Vectors Using Oracle Data Pump
- 1.13 Managing SecureFiles Large Object Exports with Oracle Data Pump
- 1.14 Oracle Data Pump Process Exit Codes
- 1.15 How Oracle Data Pump Manages Dump File Blocks
- 1.16 How to Monitor Oracle Data Pump Jobs with Unified Auditing
- 1.17 Encrypted Data Security Warnings for Oracle Data Pump Operations
- 1.18 How Does Oracle Data Pump Handle Timestamp Data?
- 1.19 Character Set and Globalization Support Considerations
- 1.20 Oracle Data Pump Behavior with Data-Bound Collation
-
2
Oracle Data Pump Export
- 2.1 What Is Oracle Data Pump Export?
- 2.2 Starting Oracle Data Pump Export
- 2.3 Filtering During Export Operations
-
2.4
Parameters Available in Data Pump Export
Command-Line Mode
- 2.4.1 About Oracle Data Pump Export Parameters
- 2.4.2 ABORT_STEP
- 2.4.3 ACCESS_METHOD
- 2.4.4 ATTACH
- 2.4.5 CHECKSUM
- 2.4.6 CHECKSUM_ALGORITM
- 2.4.7 CLUSTER
- 2.4.8 COMPRESSION
- 2.4.9 COMPRESSION_ALGORITHM
- 2.4.10 CONTENT
- 2.4.11 CREDENTIAL
- 2.4.12 DATA_OPTIONS
- 2.4.13 DIRECTORY
- 2.4.14 DUMPFILE
- 2.4.15 ENABLE_SECURE_ROLES
- 2.4.16 ENCRYPTION
- 2.4.17 ENCRYPTION_ALGORITHM
- 2.4.18 ENCRYPTION_MODE
- 2.4.19 ENCRYPTION_PASSWORD
- 2.4.20 ENCRYPTION_PWD_PROMPT
- 2.4.21 ESTIMATE
- 2.4.22 ESTIMATE_ONLY
- 2.4.23 EXCLUDE
- 2.4.24 FILESIZE
- 2.4.25 FLASHBACK_SCN
- 2.4.26 FLASHBACK_TIME
- 2.4.27 FULL
- 2.4.28 HELP
- 2.4.29 INCLUDE
- 2.4.30 JOB_NAME
- 2.4.31 KEEP_MASTER
- 2.4.32 LOGFILE
- 2.4.33 LOGTIME
- 2.4.34 METRICS
- 2.4.35 NETWORK_LINK
- 2.4.36 NOLOGFILE
- 2.4.37 PARALLEL
- 2.4.38 PARALLEL_THRESHOLD
- 2.4.39 PARFILE
- 2.4.40 QUERY
- 2.4.41 REMAP_DATA
- 2.4.42 REUSE_DUMPFILES
- 2.4.43 SAMPLE
- 2.4.44 SCHEMAS
- 2.4.45 SERVICE_NAME
- 2.4.46 SOURCE_EDITION
- 2.4.47 STATUS
- 2.4.48 TABLES
- 2.4.49 TABLESPACES
- 2.4.50 TRANSPORT_DATAFILES_LOG
- 2.4.51 TRANSPORT_FULL_CHECK
- 2.4.52 TRANSPORT_TABLESPACES
- 2.4.53 TRANSPORTABLE
- 2.4.54 TTS_CLOSURE_CHECK
- 2.4.55 VERSION
- 2.4.56 VIEWS_AS_TABLES
- 2.5 Commands Available in Data Pump Export Interactive-Command Mode
-
2.6
Examples of Using Oracle Data Pump
Export
- 2.6.1 Performing a Table-Mode Export
- 2.6.2 Data-Only Unload of Selected Tables and Rows
- 2.6.3 Estimating Disk Space Needed in a Table-Mode Export
- 2.6.4 Performing a Schema-Mode Export
- 2.6.5 Performing a Parallel Full Database Export
- 2.6.6 Using Interactive Mode to Stop and Reattach to a Job
- 2.6.7 Continuing Table Loads when LOB Data Type Corruptions are Found
- 2.7 Syntax Diagrams for Oracle Data Pump Export
-
3
Oracle Data Pump Import
- 3.1 What Is Oracle Data Pump Import?
- 3.2 Starting Oracle Data Pump Import
- 3.3 Filtering During Import Operations
-
3.4
Parameters Available in Oracle Data Pump
Import Command-Line Mode
- 3.4.1 About Import Command-Line Mode
- 3.4.2 ABORT_STEP
- 3.4.3 ACCESS_METHOD
- 3.4.4 ATTACH
- 3.4.5 CLUSTER
- 3.4.6 CONTENT
- 3.4.7 CREDENTIAL
- 3.4.8 DATA_OPTIONS
- 3.4.9 DIRECTORY
- 3.4.10 DUMPFILE
- 3.4.11 ENABLE_SECURE_ROLES
- 3.4.12 ENCRYPTION_PASSWORD
- 3.4.13 ENCRYPTION_PWD_PROMPT
- 3.4.14 ESTIMATE
- 3.4.15 EXCLUDE
- 3.4.16 FLASHBACK_SCN
- 3.4.17 FLASHBACK_TIME
- 3.4.18 FULL
- 3.4.19 HELP
- 3.4.20 INCLUDE
- 3.4.21 INDEX_THRESHOLD
- 3.4.22 JOB_NAME
- 3.4.23 KEEP_MASTER
- 3.4.24 LOGFILE
- 3.4.25 LOGTIME
- 3.4.26 MASTER_ONLY
- 3.4.27 METRICS
- 3.4.28 NETWORK_LINK
- 3.4.29 NOLOGFILE
- 3.4.30 ONESTEP_INDEX
- 3.4.31 PARALLEL
- 3.4.32 PARALLEL_THRESHOLD
- 3.4.33 PARFILE
- 3.4.34 PARTITION_OPTIONS
- 3.4.35 QUERY
- 3.4.36 REMAP_DATA
- 3.4.37 REMAP_DATAFILE
- 3.4.38 REMAP_DIRECTORY
- 3.4.39 REMAP_SCHEMA
- 3.4.40 REMAP_TABLE
- 3.4.41 REMAP_TABLESPACE
- 3.4.42 SCHEMAS
- 3.4.43 SERVICE_NAME
- 3.4.44 SKIP_UNUSABLE_INDEXES
- 3.4.45 SOURCE_EDITION
- 3.4.46 SQLFILE
- 3.4.47 STATUS
- 3.4.48 STREAMS_CONFIGURATION
- 3.4.49 TABLE_EXISTS_ACTION
- 3.4.50 REUSE_DATAFILES
- 3.4.51 TABLES
- 3.4.52 TABLESPACES
- 3.4.53 TARGET_EDITION
- 3.4.54 TRANSFORM
- 3.4.55 TRANSPORT_DATAFILES
- 3.4.56 TRANSPORT_FULL_CHECK
- 3.4.57 TRANSPORT_TABLESPACES
- 3.4.58 TRANSPORTABLE
- 3.4.59 VERIFY_CHECKSUM
- 3.4.60 VERIFY_ONLY
- 3.4.61 VERSION
- 3.4.62 VIEWS_AS_TABLES (Network Import)
- 3.5 Commands Available in Oracle Data Pump Import Interactive-Command Mode
- 3.6 Examples of Using Oracle Data Pump Import
- 3.7 Syntax Diagrams for Oracle Data Pump Import
- 4 Oracle Data Pump Legacy Mode
- 5 Oracle Data Pump Performance
-
6
Using the Oracle Data Pump API
- 6.1 How Does the Oracle Data Pump Client Interface API Work?
- 6.2 DBMS_DATAPUMP Job States
- 6.3 What Are the Basic Steps in Using the Oracle Data Pump API?
-
6.4
Examples of Using the Oracle Data
Pump API
- 6.4.1 Using the Oracle Data Pump API Examples with Your Database
- 6.4.2 Performing a Simple Schema Export with Oracle Data Pump
- 6.4.3 Performing a Table Mode Export to Object Store with Oracle Data Pump
- 6.4.4 Importing a Dump File and Remapping All Schema Objects
- 6.4.5 Importing a Table from an Object Store Using Oracle Data Pump
- 6.4.6 Using Exception Handling During a Simple Schema Export
- 6.4.7 Displaying Dump File Information for Oracle Data Pump Jobs
- 6.4.8 Network Mode and Schema Mode Import Over a Network Link
-
1
Overview of Oracle Data Pump
-
Part II SQL*Loader
-
7
Understanding How to Use SQL*Loader
- 7.1 SQL*Loader Features
- 7.2 SQL*Loader Parameters
- 7.3 SQL*Loader Control File
- 7.4 Input Data and Data Fields in SQL*Loader
- 7.5 LOBFILEs and Secondary Data Files (SDFs)
- 7.6 Data Conversion and Data Type Specification
- 7.7 SQL*Loader Discarded and Rejected Records
- 7.8 Log File and Logging Information
- 7.9 Conventional Path Loads, Direct Path Loads, and External Table Loads
- 7.10 Loading Objects, Collections, and LOBs with SQL*Loader
- 7.11 Partitioned Object Support in SQL*Loader
- 7.12 Application Development: Direct Path Load API
- 7.13 SQL*Loader Case Studies
-
8
SQL*Loader Command-Line Reference
- 8.1 Starting SQL*Loader
-
8.2
Command-Line Parameters for SQL*Loader
- 8.2.1 BAD
- 8.2.2 BINDSIZE
- 8.2.3 COLUMNARRAYROWS
- 8.2.4 COMPRESS_STREAM
- 8.2.5 CONTROL
- 8.2.6 CREDENTIAL
- 8.2.7 DATA
- 8.2.8 DATE_CACHE
- 8.2.9 DEFAULTS
- 8.2.10 DEGREE_OF_PARALLELISM
- 8.2.11 DIRECT
- 8.2.12 DIRECT_PATH_LOCK_WAIT
- 8.2.13 DISCARD
- 8.2.14 DISCARDMAX
- 8.2.15 DNFS_ENABLE
- 8.2.16 DNFS_READBUFFERS
- 8.2.17 EMPTY_LOBS_ARE_NULL
- 8.2.18 ERRORS
- 8.2.19 EXTERNAL_TABLE
- 8.2.20 FILE
- 8.2.21 GRANULE_SIZE
- 8.2.22 GSM_HOST
- 8.2.23 GSM_NAME
- 8.2.24 GSM_PORT
- 8.2.25 HELP
- 8.2.26 LOAD
- 8.2.27 LOAD_SHARDS
- 8.2.28 LOG
- 8.2.29 MULTITHREADING
- 8.2.30 NO_INDEX_ERRORS
- 8.2.31 OPTIMIZE PARALLEL
- 8.2.32 PARALLEL
- 8.2.33 PARFILE
- 8.2.34 PARTITION_MEMORY
- 8.2.35 READER_COUNT
- 8.2.36 READSIZE
- 8.2.37 RESUMABLE
- 8.2.38 RESUMABLE_NAME
- 8.2.39 RESUMABLE_TIMEOUT
- 8.2.40 ROWS
- 8.2.41 SDF_PREFIX
- 8.2.42 SILENT
- 8.2.43 SKIP
- 8.2.44 SKIP_INDEX_MAINTENANCE
- 8.2.45 SKIP_UNUSABLE_INDEXES
- 8.2.46 STREAMSIZE
- 8.2.47 TRIM
- 8.2.48 USERID
- 8.3 Exit Codes for Inspection and Display
-
9
SQL*Loader Control File Reference
- 9.1 Control File Contents
- 9.2 Comments in the Control File
- 9.3 Specifying Command-Line Parameters in the Control File
- 9.4 Specifying File Names and Object Names
- 9.5 Identifying XMLType Tables
- 9.6 Specifying Field Order
- 9.7 Specifying Data Files
- 9.8 Specifying CSV Format Files
- 9.9 Loading VECTOR Columns from Character Data and fvec Format Files
- 9.10 Identifying Data in the Control File with BEGINDATA
- 9.11 Specifying Data File Format and Buffering
- 9.12 Specifying the Bad File
-
9.13
Specifying the Discard
File
- 9.13.1 Understanding and Specifying the Discard File
- 9.13.2 Specifying the Discard File in the Control File
- 9.13.3 Limiting the Number of Discard Records
- 9.13.4 Examples of Specifying a Discard File Name
- 9.13.5 Criteria for Discarded Records
- 9.13.6 How Discard Files Are Handled with LOBFILEs and SDFs
- 9.13.7 Specifying the Discard File from the Command Line
- 9.14 Specifying a NULLIF Clause At the Table Level
- 9.15 Specifying Datetime Formats At the Table Level
- 9.16 Handling Different Character Encoding Schemes
- 9.17 Interrupted SQL*Loader Loads
- 9.18 Assembling Logical Records from Physical Records
-
9.19
Loading Logical Records into Tables
- 9.19.1 Specifying Table Names
- 9.19.2 INTO TABLE Clause
- 9.19.3 Table-Specific Loading Method
- 9.19.4 Loading Data into Empty Tables with INSERT
- 9.19.5 Loading Data into Nonempty Tables
- 9.19.6 Table-Specific OPTIONS Parameter
- 9.19.7 Loading Records Based on a Condition
- 9.19.8 Using the WHEN Clause with LOBFILEs and SDFs
- 9.19.9 Specifying Default Data Delimiters
- 9.19.10 Handling Records with Missing Specified Fields
- 9.20 Index Options with SQL*Loader
-
9.21
Benefits of Using Multiple INTO
TABLE Clauses
- 9.21.1 Understanding the SQL*Loader INTO TABLE Clause
- 9.21.2 Distinguishing Different Input Record Formats
- 9.21.3 Relative Positioning Based on the POSITION Parameter
- 9.21.4 Distinguishing Different Input Row Object Subtypes
- 9.21.5 Loading Data into Multiple Tables
- 9.21.6 Summary of Using Multiple INTO TABLE Clauses
- 9.21.7 Extracting Multiple Logical Records
-
9.22
Bind Arrays and Conventional Path
Loads
- 9.22.1 Differences Between Bind Arrays and Conventional Path Loads
- 9.22.2 Size Requirements for Bind Arrays
- 9.22.3 Performance Implications of Bind Arrays
- 9.22.4 Specifying Number of Rows Versus Size of Bind Array
- 9.22.5 Setting Up SQL*Loader Bind Arrays
- 9.22.6 Minimizing Memory Requirements for Bind Arrays
- 9.22.7 Calculating Bind Array Size for Multiple INTO TABLE Clauses
-
10
SQL*Loader Field List Reference
- 10.1 Field List Contents
- 10.2 Specifying the Position of a Data Field.
- 10.3 Specifying Columns and Fields
-
10.4
SQL*Loader Data Types
- 10.4.1 Portable and Nonportable Data Type Differences
- 10.4.2 Nonportable Data Types
-
10.4.3
Portable Data Types
- 10.4.3.1 Categories of Portable Data Types
- 10.4.3.2 CHAR
- 10.4.3.3 Datetime and Interval
- 10.4.3.4 GRAPHIC
- 10.4.3.5 GRAPHIC EXTERNAL
- 10.4.3.6 Numeric EXTERNAL
- 10.4.3.7 RAW
- 10.4.3.8 VARCHARC
- 10.4.3.9 VARRAWC
- 10.4.3.10 Conflicting Native Data Type Field Lengths
- 10.4.3.11 Field Lengths for Length-Value Data Types
- 10.4.4 SODA Collection Data Types
- 10.4.5 Data Type Conversions
- 10.4.6 Data Type Conversions for Datetime and Interval Data Types
- 10.4.7 Specifying Delimiters
- 10.4.8 How Delimited Data Is Processed
- 10.4.9 Conflicting Field Lengths for Character Data Types
- 10.5 Specifying Field Conditions
- 10.6 Using the WHEN, NULLIF, and DEFAULTIF Clauses
- 10.7 Examples of Using the WHEN, NULLIF, and DEFAULTIF Clauses
- 10.8 Loading Data Across Different Platforms
- 10.9 Understanding how SQL*Loader Manages Byte Ordering
- 10.10 Loading All-Blank Fields
- 10.11 Trimming Whitespace
- 10.12 How the PRESERVE BLANKS Option Affects Whitespace Trimming
- 10.13 How [NO] PRESERVE BLANKS Works with Delimiter Clauses
- 10.14 Applying SQL Operators to Fields
- 10.15 Using SQL*Loader to Generate Data for Input
-
11
Loading Objects, LOBs, and Collections with
SQL*Loader
-
11.1
Loading Column Objects
- 11.1.1 Understanding Column Object Attributes
- 11.1.2 Loading Column Objects in Stream Record Format
- 11.1.3 Loading Column Objects in Variable Record Format
- 11.1.4 Loading Nested Column Objects
- 11.1.5 Loading Column Objects with a Derived Subtype
- 11.1.6 Specifying Null Values for Objects
- 11.1.7 Loading Column Objects with User-Defined Constructors
- 11.2 Loading Object Tables with SQL*Loader
- 11.3 Loading REF Columns with SQL*Loader
-
11.4
Loading LOBs with SQL*Loader
- 11.4.1 Overview of Loading LOBs with SQL*Loader
- 11.4.2 Options for Using SQL*Loader to Load LOBs
- 11.4.3 Loading LOB Data from a Primary Data File
- 11.4.4 Loading LOB Data from LOBFILEs
- 11.4.5 Loading Data Files that Contain LLS Fields
- 11.5 Loading BFILE Columns with SQL*Loader
- 11.6 Loading Collections (Nested Tables and VARRAYs)
- 11.7 Choosing Dynamic or Static SDF Specifications
- 11.8 Loading a Parent Table Separately from Its Child Table
-
11.9
Loading Modes and Options for SODA
Collections
- 11.9.1 SQL*Loader and SODA_COLLECTION
- 11.9.2 Loading Empty SODA Collections Using INSERT
- 11.9.3 Loading Empty SODA Collections Using APPEND
- 11.9.4 Loading Empty SODA Collections Using REPLACE and TRUNCATE
- 11.9.5 Permitted SQL*Loader Command-Line Parameters for SODA Collections
- 11.9.6 Examples of Loading SODA Collections
- 11.10 Load Character Vector Data Using SQL*Loader Example
- 11.11 Load Binary Vector Data Using SQL*Loader Example
-
11.1
Loading Column Objects
-
12
Conventional and Direct Path Loads
- 12.1 Data Loading Methods
- 12.2 Loading ROWID Columns
- 12.3 Conventional Path Loads
-
12.4
Direct Path Loads
- 12.4.1 About SQL*Loader Direct Path Load
- 12.4.2 Loading into Synonyms
- 12.4.3 Field Defaults on the Direct Path
- 12.4.4 Integrity Constraints
- 12.4.5 When to Use a Direct Path Load
- 12.4.6 Restrictions on a Direct Path Load of a Single Partition
- 12.4.7 Restrictions on Using Direct Path Loads
- 12.4.8 Advantages of a Direct Path Load
- 12.4.9 Direct Path Load of a Single Partition or Subpartition
- 12.4.10 Direct Path Load of a Partitioned or Subpartitioned Table
- 12.4.11 Data Conversion During Direct Path Loads
- 12.5 Automatic Parallel Load of Table Data with SQL*Loader
- 12.6 Loading Modes and Options for Automatic Parallel Loads
-
12.7
Using Direct Path Load
- 12.7.1 Setting Up for Direct Path Loads
- 12.7.2 Specifying a Direct Path Load
- 12.7.3 Building Indexes
- 12.7.4 Indexes Left in an Unusable State
- 12.7.5 Preventing Data Loss with Data Saves
- 12.7.6 Data Recovery During Direct Path Loads
- 12.7.7 Loading Long Data Fields
- 12.7.8 Loading Data As PIECED
- 12.7.9 Auditing SQL*Loader Operations That Use Direct Path Mode
-
12.8
Optimizing Performance of Manual
Direct Path Loads
- 12.8.1 Minimizing Time and Space Required for Direct Path Loads
- 12.8.2 Preallocating Storage for Faster Loading
- 12.8.3 Presorting Data for Faster Indexing
- 12.8.4 Infrequent Data Saves
- 12.8.5 Minimizing Use of the Redo Log
- 12.8.6 Specifying the Number of Column Array Rows and Size of Stream Buffers
- 12.8.7 Specifying a Value for DATE_CACHE
- 12.9 Optimizing Direct Path Loads on Multiple-CPU Systems
- 12.10 Avoiding Index Maintenance
- 12.11 Direct Path Loads, Integrity Constraints, and Triggers
-
12.12
Optimizing Performance of Direct
Path Loads
- 12.12.1 Restrictions on Automatic and Manual Parallel Direct Path Loads
- 12.12.2 About SQL*Loader Parallel Data Loading Models
- 12.12.3 Concurrent Conventional Path Loads
- 12.12.4 Intersegment Concurrency with Direct Path
- 12.12.5 Intrasegment Concurrency with Direct Path
- 12.12.6 Restrictions on Manual Parallel Direct Path Loads
- 12.12.7 Initiating Multiple SQL*Loader Sessions Manually
- 12.12.8 Parameters for Manual Parallel Direct Path Loads
- 12.12.9 Enabling Constraints After a Parallel Direct Path Load
- 12.12.10 PRIMARY KEY and UNIQUE KEY Constraints
- 12.13 General Performance Improvement Hints
-
13
SQL*Loader Express
- 13.1 What is SQL*Loader Express Mode?
- 13.2 Using SQL*Loader Express Mode
-
13.3
SQL*Loader Express Mode Parameter Reference
- 13.3.1 BAD
- 13.3.2 CHARACTERSET
- 13.3.3 CSV
- 13.3.4 DATA
- 13.3.5 DATE_FORMAT
- 13.3.6 DEGREE_OF_PARALLELISM
- 13.3.7 DIRECT
- 13.3.8 DNFS_ENABLE
- 13.3.9 DNFS_READBUFFERS
- 13.3.10 ENCLOSED_BY
- 13.3.11 EXTERNAL_TABLE
- 13.3.12 FIELD_NAMES
- 13.3.13 LOAD
- 13.3.14 NULLIF
- 13.3.15 OPTIONALLY_ENCLOSED_BY
- 13.3.16 PARFILE
- 13.3.17 SILENT
- 13.3.18 TABLE
- 13.3.19 TERMINATED_BY
- 13.3.20 TIMESTAMP_FORMAT
- 13.3.21 TRIM
- 13.3.22 USERID
- 13.4 SQL*Loader Express Mode Command-Line Parameters for SODA Collections
- 13.5 SQL*Loader Express Mode Syntax Diagrams
-
7
Understanding How to Use SQL*Loader
-
Part III External Tables
- 14 External Tables Concepts
-
15
The ORACLE_LOADER Access Driver
- 15.1 About the ORACLE_LOADER Access Driver
- 15.2 access_parameters Clause
-
15.3
record_format_info Clause
- 15.3.1 Overview of record_format_info Clause
- 15.3.2 FIXED Length
- 15.3.3 VARIABLE size
- 15.3.4 DELIMITED BY
- 15.3.5 XMLTAG
- 15.3.6 CHARACTERSET
- 15.3.7 PREPROCESSOR
- 15.3.8 PREPROCESSOR_TIMEOUT
- 15.3.9 EXTERNAL VARIABLE DATA
- 15.3.10 LANGUAGE
- 15.3.11 TERRITORY
- 15.3.12 DATA IS...ENDIAN
- 15.3.13 BYTEORDERMARK [CHECK | NOCHECK]
- 15.3.14 STRING SIZES ARE IN
- 15.3.15 LOAD WHEN
- 15.3.16 BADFILE | NOBADFILE
- 15.3.17 DISCARDFILE | NODISCARDFILE
- 15.3.18 LOGFILE | NOLOGFILE
- 15.3.19 SKIP
- 15.3.20 FIELD NAMES
- 15.3.21 READSIZE
- 15.3.22 DATE_CACHE
- 15.3.23 string
- 15.3.24 condition_spec
- 15.3.25 [directory object name:] [filename]
- 15.3.26 condition
- 15.3.27 IO_OPTIONS clause
- 15.3.28 DNFS_DISABLE | DNFS_ENABLE
- 15.3.29 DNFS_READBUFFERS
-
15.4
field_definitions Clause
- 15.4.1 Overview of field_definitions Clause
- 15.4.2 delim_spec
- 15.4.3 trim_spec
- 15.4.4 MISSING FIELD VALUES ARE NULL
- 15.4.5 field_list
- 15.4.6 pos_spec Clause
-
15.4.7
datatype_spec Clause
- 15.4.7.1 datatype_spec Clause Syntax
- 15.4.7.2 [UNSIGNED] INTEGER [EXTERNAL] [(len)]
- 15.4.7.3 DECIMAL [EXTERNAL] and ZONED [EXTERNAL]
- 15.4.7.4 ORACLE_DATE
- 15.4.7.5 ORACLE_NUMBER
- 15.4.7.6 Floating-Point Numbers
- 15.4.7.7 DOUBLE
- 15.4.7.8 FLOAT [EXTERNAL]
- 15.4.7.9 BINARY_DOUBLE
- 15.4.7.10 BINARY_FLOAT
- 15.4.7.11 RAW
- 15.4.7.12 CHAR
- 15.4.7.13 date_format_spec
- 15.4.7.14 VARCHAR and VARRAW
- 15.4.7.15 VARCHARC and VARRAWC
- 15.4.8 init_spec Clause
- 15.4.9 LLS Clause
- 15.5 column_transforms Clause
- 15.6 Parallel Loading Considerations for the ORACLE_LOADER Access Driver
- 15.7 Performance Hints When Using the ORACLE_LOADER Access Driver
- 15.8 Restrictions When Using the ORACLE_LOADER Access Driver
- 15.9 Reserved Words for the ORACLE_LOADER Access Driver
-
16
The ORACLE_DATAPUMP Access Driver
- 16.1 Using the ORACLE_DATAPUMP Access Driver
- 16.2 access_parameters Clause
- 16.3 Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver
- 16.4 Supported Data Types
- 16.5 Unsupported Data Types
- 16.6 Performance Hints When Using the ORACLE_DATAPUMP Access Driver
- 16.7 Restrictions When Using the ORACLE_DATAPUMP Access Driver
- 16.8 Reserved Words for the ORACLE_DATAPUMP Access Driver
-
17
ORACLE_BIGDATA Access
Driver
- 17.1 Accessing External Data Using the ORACLE_BIGDATA Driver
- 17.2 Enabling and Configuring your Database for Object Storage Access
- 17.3 Setting Up Credentials and Location Parameters for Object Stores
-
17.4
ORACLE_BIGDATA Accessing
Files
- 17.4.1 Syntax Rules for Specifying Properties
- 17.4.2 ORACLE_BIGDATA Common Access Parameters
-
17.4.3
ORACLE_BIGDATA Specific Access
Parameters
- 17.4.3.1 Avro-Specific Access Parameters
- 17.4.3.2 Examples of Creating External Tables with Avro Files
- 17.4.3.3 Parquet-Specific Access Parameters
- 17.4.3.4 Examples of Creating External Tables with Avro Files
- 17.4.3.5 Textfile and CSV-Specific Access Parameters
- 17.4.3.6 Examples of Creating External Tables
-
17.5
ORACLE_BIGDATA Accessing Apache
Iceberg
- 17.5.1 Apache Iceberg Tables Overview
- 17.5.2 Supported Configurations for Apache Iceberg
- 17.5.3 Iceberg-Specific Access Parameters
-
17.5.4
Examples of Table Creation and
Inline External Table SQL for Iceberg Tables
- 17.5.4.1 Creating a Table Pointing to the Manifest File
- 17.5.4.2 Inline External Table Query (Manifest File Reference)
- 17.5.4.3 Creating a Table Using DBMS_CLOUD
- 17.5.4.4 Creating a Table Using AWS Glue as a Catalog
- 17.5.4.5 Inline External Table Query Using AWS Glue Catalog
- 17.5.4.6 Creating an External Table Using DBMS_CLOUD with AWS Glue Catalog
- 17.6 ORACLE_BIGDATA Accessing Delta Sharing
- 17.7 ORACLE_BIGDATA Accessing JSON Documents File Type
-
18
External Tables Examples for
Oracle Database
- 18.1 Using the ORACLE_LOADER Access Driver to Create Partitioned External Tables
- 18.2 Using the ORACLE_LOADER Access Driver to Create Partitioned Hybrid Tables
- 18.3 Using the ORACLE_DATAPUMP Access Driver to Create Partitioned External Tables
- 18.4 Using the ORACLE_BIGDATA Access Driver to Create Partitioned External Tables
- 18.5 Using the ORA_PARTITION_VALIDATION Function to Validate Partitioned External Tables
- 18.6 Using SQL*Loader for External Tables with Partition Values in File Paths
- 18.7 Loading LOBs with External Tables
- 18.8 Loading CSV Files From External Tables
- 18.9 Using Vector Data Types in External Tables
-
Part IV Other Utilities
-
19
Cloud Premigration Advisor
Tool
- 19.1 What is the Cloud Premigration Advisor Tool
- 19.2 Prerequisites for Using the Cloud Premigration Advisor Tool
- 19.3 Downloading and Configuring Cloud Premigration Advisor Tool
- 19.4 Getting Started with the Cloud Premigration Advisor Tool (CPAT)
- 19.5 Connection Strings for Cloud Premigration Advisor Tool
- 19.6 Required Command-Line Strings for Cloud Premigration Advisor Tool
- 19.7 FULL Mode and SCHEMA Mode
- 19.8 Interpreting Cloud Premigration Advisor Tool (CPAT) Report Data
-
19.9
Command-Line Syntax and
Properties
- 19.9.1 Premigration Advisor Tool Command-Line Syntax
-
19.9.2
Premigration Advisor Tool
Command-Line Properties
- 19.9.2.1 analysisprops
- 19.9.2.2 connectstring
- 19.9.2.3 excludeschemas
- 19.9.2.4 full
- 19.9.2.5 gettargetprops
- 19.9.2.6 help
- 19.9.2.7 logginglevel
- 19.9.2.8 maxrelevantobjects
- 19.9.2.9 maxtextdatarows
- 19.9.2.10 migrationmethod
- 19.9.2.11 outdir
- 19.9.2.12 outfileprefix
- 19.9.2.13 pdbname
- 19.9.2.14 reportformat
- 19.9.2.15 schemas
- 19.9.2.16 sqltext
- 19.9.2.17 sysdba
- 19.9.2.18 targetcloud
- 19.9.2.19 username
- 19.9.2.20 version
- 19.9.2.21 updatecheck
-
19.10
List of Checks Performed By the
Premigration Advisor Tool
- 19.10.1 dp_has_low_streams_pool_size
- 19.10.2 gg_enabled_replication
- 19.10.3 gg_force_logging
- 19.10.4 gg_has_low_streams_pool_size
- 19.10.5 gg_not_unique
- 19.10.6 gg_not_unique_bad_col_no
- 19.10.7 gg_not_unique_bad_col_yes
- 19.10.8 gg_objects_not_supported
- 19.10.9 gg_supplemental_log_data_min
- 19.10.10 gg_tables_not_supported
- 19.10.11 gg_tables_not_supported
- 19.10.12 gg_user_objects_in_ggadmin_schemas
- 19.10.13 has_absent_default_tablespace
- 19.10.14 has_absent_temp_tablespace
- 19.10.15 has_active_data_guard_dedicated
- 19.10.16 has_active_data_guard_serverless
- 19.10.17 has_basic_file_lobs
- 19.10.18 has_clustered_tables
- 19.10.19 has_columns_of_rowid_type
- 19.10.20 has_columns_with_local_timezone
- 19.10.21 has_columns_with_media_data_types_adb
- 19.10.22 has_columns_with_media_data_types_default
- 19.10.23 has_columns_with_spatial_data_types
- 19.10.24 has_common_objects
- 19.10.25 has_compression_disabled_for_objects
- 19.10.26 has_csmig_schema
- 19.10.27 has_data_in_other_tablespaces_dedicated
- 19.10.28 has_data_in_other_tablespaces_serverless
- 19.10.29 has_db_link_synonyms
- 19.10.30 has_db_links
- 19.10.31 has_dbms_credentials
- 19.10.32 has_dbms_credentials
- 19.10.33 has_directories
- 19.10.34 has_enabled_scheduler_jobs
- 19.10.35 has_external_tables_dedicated
- 19.10.36 has_external_tables_default
- 19.10.37 has_external_tables_serverless
- 19.10.38 has_fmw_registry_in_system
- 19.10.39 has_illegal_characters_in_comments
- 19.10.40 has_ilm_ado_policies
- 19.10.41 has_incompatible_jobs
- 19.10.42 has_index_organized_tables
- 19.10.43 has_java_objects
- 19.10.44 has_java_source
- 19.10.45 has_libraries
- 19.10.46 has_logging_off_for_partitions
- 19.10.47 has_logging_off_for_subpartitions
- 19.10.48 has_logging_off_for_tables
- 19.10.49 has_low_streams_pool_size
- 19.10.50 has_noexport_object_grants
- 19.10.51 has_oracle_streams
- 19.10.52 has_parallel_indexes_enabled
- 19.10.53 has_profile_not_default
- 19.10.54 has_public_synonyms
- 19.10.55 has_refs_to_restricted_packages_dedicated
- 19.10.56 has_refs_to_restricted_packages_serverless
- 19.10.57 has_refs_to_user_objects_in_sys
- 19.10.58 has_role_privileges
- 19.10.59 has_sqlt_objects_adb
- 19.10.60 has_sqlt_objects_default
- 19.10.61 has_sys_privileges
- 19.10.62 has_tables_that_fail_with_dblink
- 19.10.63 has_tables_with_long_raw_datatype
- 19.10.64 has_tables_with_xmltype_column
- 19.10.65 has_trusted_server_entries
- 19.10.66 has_unstructured_xml_indexes Check
- 19.10.67 has_user_defined_objects_in_sys
- 19.10.68 has_user_defined_objects_in_system
- 19.10.69 has_user_defined_objects_no_quota
- 19.10.70 has_user_defined_pvfs
- 19.10.71 has_users_with_10g_password_version
- 19.10.72 has_xmlschema_objects
- 19.10.73 has_xmltype_tables
- 19.10.74 modified_db_parameters_dedicated
- 19.10.75 modified_db_parameters_serverless
- 19.10.76 nls_character_set_conversion
- 19.10.77 nls_national_character_set
- 19.10.78 nls_nchar_ora_910
- 19.10.79 options_in_use_not_available_dedicated
- 19.10.80 options_in_use_not_available_serverless
- 19.10.81 standard_traditional_audit_adb
- 19.10.82 standard_traditional_audit_default
- 19.10.83 timezone_table_compatibility_higher_dedicated
- 19.10.84 timezone_table_compatibility_higher_default
- 19.10.85 timezone_table_compatibility_higher_serverless
- 19.10.86 unified_and_standard_traditional_audit_adb
- 19.10.87 unified_and_standard_traditional_audit_default
- 19.10.88 xdb_resource_view_has_entries Check
- 19.11 Best Practices for Using the Premigration Advisor Tool
-
20
DBMS_CLOUD Family of
Packages
- 20.1 Using the DBMS_CLOUD Family of Packages
- 20.2 Installing DBMS_CLOUD
- 20.3 Create SSL Wallet with Certificates
- 20.4 Configure Your Environment to Use the New SSL Wallet
- 20.5 Configure the Database with ACEs for DBMS_CLOUD
- 20.6 Verify Configuration of DBMS_CLOUD
- 20.7 Configuring Users or Roles to use DBMS_CLOUD
-
21
Oracle SQL Access to Kafka
- 21.1 About Oracle SQL Access to Kafka Version 2
- 21.2 Global Tables and Views for Oracle SQL Access to Kafka
- 21.3 Understanding how Oracle SQL Access to Kafka Queries are Performed
- 21.4 Streaming Kafka Data Into Oracle Database
- 21.5 Querying Kafka Data Records by Timestamp
- 21.6 About the Kafka Database Administrator Role
- 21.7 Enable Kafka Database Access to Users
- 21.8 Data Formats Supported with Oracle SQL Access to Kafka
- 21.9 Configuring Access to a Kafka Cluster
- 21.10 Creating Oracle SQL Access to Kafka Applications
- 21.11 Security for Kafka Cluster Connections
- 21.12 Configuring Access to Unsecured Kafka Clusters
- 21.13 Configuring Access to Secure Kafka Clusters
- 21.14 Administering Oracle SQL Access to Kafka Clusters
- 21.15 Guidelines for Using Kafka Data with Oracle SQL Access to Kafka
- 21.16 Choosing a Kafka Cluster Access Mode for Applications
- 21.17 Creating Oracle SQL Access to Kafka Applications
- 21.18 Using Kafka Cluster Access for Applications
-
22
ADRCI: ADR Command Interpreter
- 22.1 About the ADR Command Interpreter (ADRCI) Utility
- 22.2 Definitions for Oracle Database ADRC
- 22.3 Starting ADRCI and Getting Help
- 22.4 Setting the ADRCI Homepath Before Using ADRCI Commands
- 22.5 Viewing the Alert Log
- 22.6 Finding Trace Files
- 22.7 Viewing Incidents
- 22.8 Packaging Incidents
-
22.9
ADRCI Command Reference
- 22.9.1 CREATE REPORT
- 22.9.2 ECHO
- 22.9.3 EXIT
- 22.9.4 HOST
-
22.9.5
IPS
- 22.9.5.1 Using the <ADR_HOME> and <ADR_BASE> Variables in IPS Commands
- 22.9.5.2 IPS ADD
- 22.9.5.3 IPS ADD FILE
- 22.9.5.4 IPS ADD NEW INCIDENTS
- 22.9.5.5 IPS COPY IN FILE
- 22.9.5.6 IPS COPY OUT FILE
- 22.9.5.7 IPS CREATE PACKAGE
- 22.9.5.8 IPS DELETE PACKAGE
- 22.9.5.9 IPS FINALIZE
- 22.9.5.10 IPS GENERATE PACKAGE
- 22.9.5.11 IPS GET MANIFEST
- 22.9.5.12 IPS GET METADATA
- 22.9.5.13 IPS PACK
- 22.9.5.14 IPS REMOVE
- 22.9.5.15 IPS REMOVE FILE
- 22.9.5.16 IPS SET CONFIGURATION
- 22.9.5.17 IPS SHOW CONFIGURATION
- 22.9.5.18 IPS SHOW FILES
- 22.9.5.19 IPS SHOW INCIDENTS
- 22.9.5.20 IPS SHOW PACKAGE
- 22.9.5.21 IPS UNPACK FILE
- 22.9.6 PURGE
- 22.9.7 QUIT
- 22.9.8 RUN
- 22.9.9 SELECT
- 22.9.10 SET BASE
- 22.9.11 SET BROWSER
- 22.9.12 SET CONTROL
- 22.9.13 SET ECHO
- 22.9.14 SET EDITOR
- 22.9.15 SET HOMEPATH
- 22.9.16 SET TERMOUT
- 22.9.17 SHOW ALERT
- 22.9.18 SHOW BASE
- 22.9.19 SHOW CONTROL
- 22.9.20 SHOW HM_RUN
- 22.9.21 SHOW HOMEPATH
- 22.9.22 SHOW HOMES
- 22.9.23 SHOW INCDIR
- 22.9.24 SHOW INCIDENT
- 22.9.25 SHOW LOG
- 22.9.26 SHOW PROBLEM
- 22.9.27 SHOW REPORT
- 22.9.28 SHOW TRACEFILE
- 22.9.29 SPOOL
- 22.10 Troubleshooting ADRCI
- 23 DBVERIFY: Offline Database Verification Utility
- 24 DBNEWID Utility
-
25
Using LogMiner to Analyze Redo Log Files
- 25.1 LogMiner Benefits
- 25.2 Introduction to LogMiner
- 25.3 Using LogMiner in a CDB
- 25.4 How to Configure Supplemental Logging for Oracle GoldenGate
- 25.5 LogMiner Dictionary Files and Redo Log Files
- 25.6 Starting LogMiner
- 25.7 Querying V$LOGMNR_CONTENTS for Redo Data of Interest
- 25.8 Filtering and Formatting Data Returned to V$LOGMNR_CONTENTS
- 25.9 Reapplying DDL Statements Returned to V$LOGMNR_CONTENTS
- 25.10 Calling DBMS_LOGMNR.START_LOGMNR Multiple Times
-
25.11
LogMiner and Supplemental
Logging
- 25.11.1 Understanding Supplemental Logging and LogMiner
- 25.11.2 Database-Level Supplemental Logging
- 25.11.3 Disabling Database-Level Supplemental Logging
- 25.11.4 Table-Level Supplemental Logging
- 25.11.5 Tracking DDL Statements in the LogMiner Dictionary
- 25.11.6 DDL_DICT_TRACKING and Supplemental Logging Settings
- 25.11.7 DDL_DICT_TRACKING and Specified Time or SCN Ranges
- 25.12 Accessing LogMiner Operational Information in Views
-
25.13
Steps in a Typical LogMiner
Session
- 25.13.1 Understanding How to Run LogMiner Sessions
- 25.13.2 Typical LogMiner Session Task 1: Enable Supplemental Logging
- 25.13.3 Typical LogMiner Session Task 2: Extract a LogMiner Dictionary
- 25.13.4 Typical LogMiner Session Task 3: Specify Redo Log Files for Analysis
- 25.13.5 Start LogMiner
- 25.13.6 Query V$LOGMNR_CONTENTS
- 25.13.7 Typical LogMiner Session Task 6: End the LogMiner Session
-
25.14
Examples Using LogMiner
-
25.14.1
Examples of Mining by Explicitly Specifying the Redo Log Files of Interest
- 25.14.1.1 Example 1: Finding All Modifications in the Last Archived Redo Log File
- 25.14.1.2 Example 2: Grouping DML Statements into Committed Transactions
- 25.14.1.3 Example 3: Formatting the Reconstructed SQL
- 25.14.1.4 Example 4: Using the LogMiner Dictionary in the Redo Log Files
- 25.14.1.5 Example 5: Tracking DDL Statements in the Internal Dictionary
- 25.14.1.6 Example 6: Filtering Output by Time Range
- 25.14.2 LogMiner Use Case Scenarios
-
25.14.1
Examples of Mining by Explicitly Specifying the Redo Log Files of Interest
- 25.15 Supported Data Types, Storage Attributes, and Database and Redo Log File Versions
-
26
Using the Metadata APIs
- 26.1 Why Use the DBMS_METADATA API?
- 26.2 Overview of the DBMS_METADATA API
- 26.3 Using the DBMS_METADATA API to Retrieve an Object's Metadata
- 26.4 Using the DBMS_METADATA API to Recreate a Retrieved Object
- 26.5 Using the DBMS_METADATA API to Retrieve Collections of Different Object Types
- 26.6 Filtering the Return of Heterogeneous Object Types
- 26.7 Using the DBMS_METADATA_DIFF API to Compare Object Metadata
- 26.8 Performance Tips for the Programmatic Interface of the DBMS_METADATA API
- 26.9 Example Usage of the DBMS_METADATA API
- 26.10 Summary of DBMS_METADATA Procedures
- 26.11 Summary of DBMS_METADATA_DIFF Procedures
-
27
Original Import
- 27.1 What Is the Import Utility?
- 27.2 Table Objects: Order of Import
- 27.3 Before Using Import
- 27.4 Importing into Existing Tables
- 27.5 Effect of Schema and Database Triggers on Import Operations
- 27.6 Invoking Import
- 27.7 Import Modes
-
27.8
Import Parameters
- 27.8.1 BUFFER
- 27.8.2 COMMIT
- 27.8.3 COMPILE
- 27.8.4 CONSTRAINTS
- 27.8.5 DATA_ONLY
- 27.8.6 DATAFILES
- 27.8.7 DESTROY
- 27.8.8 FEEDBACK
- 27.8.9 FILE
- 27.8.10 FILESIZE
- 27.8.11 FROMUSER
- 27.8.12 FULL
- 27.8.13 GRANTS
- 27.8.14 HELP
- 27.8.15 IGNORE
- 27.8.16 INDEXES
- 27.8.17 INDEXFILE
- 27.8.18 LOG
- 27.8.19 PARFILE
- 27.8.20 RECORDLENGTH
- 27.8.21 RESUMABLE
- 27.8.22 RESUMABLE_NAME
- 27.8.23 RESUMABLE_TIMEOUT
- 27.8.24 ROWS
- 27.8.25 SHOW
- 27.8.26 SKIP_UNUSABLE_INDEXES
- 27.8.27 STATISTICS
- 27.8.28 STREAMS_CONFIGURATION
- 27.8.29 STREAMS_INSTANTIATION
- 27.8.30 TABLES
- 27.8.31 TABLESPACES
- 27.8.32 TOID_NOVALIDATE
- 27.8.33 TOUSER
- 27.8.34 TRANSPORT_TABLESPACE
- 27.8.35 TTS_OWNERS
- 27.8.36 USERID (username/password)
- 27.8.37 VOLSIZE
-
27.9
Example Import Sessions
- 27.9.1 Example Import of Selected Tables for a Specific User
- 27.9.2 Example Import of Tables Exported by Another User
- 27.9.3 Example Import of Tables from One User to Another
- 27.9.4 Example Import Session Using Partition-Level Import
- 27.9.5 Example Import Using Pattern Matching to Import Various Tables
- 27.10 Exit Codes for Inspection and Display
- 27.11 Error Handling During an Import
- 27.12 Table-Level and Partition-Level Import
- 27.13 Controlling Index Creation and Maintenance
- 27.14 Network Considerations for Using Oracle Net with Original Import
- 27.15 Character Set and Globalization Support Considerations
- 27.16 Using Instance Affinity
-
27.17
Considerations When Importing Database Objects
- 27.17.1 Importing Object Identifiers
- 27.17.2 Importing Existing Object Tables and Tables That Contain Object Types
- 27.17.3 Importing Nested Tables
- 27.17.4 Importing REF Data
- 27.17.5 Importing BFILE Columns and Directory Aliases
- 27.17.6 Importing Foreign Function Libraries
- 27.17.7 Importing Stored Procedures, Functions, and Packages
- 27.17.8 Importing Java Objects
- 27.17.9 Importing External Tables
- 27.17.10 Importing Advanced Queue (AQ) Tables
- 27.17.11 Importing LONG Columns
- 27.17.12 Importing LOB Columns When Triggers Are Present
- 27.17.13 Importing Views
- 27.17.14 Importing Partitioned Tables
- 27.18 Support for Fine-Grained Access Control
- 27.19 Snapshots and Snapshot Logs
- 27.20 Transportable Tablespaces
- 27.21 Storage Parameters
- 27.22 Read-Only Tablespaces
- 27.23 Dropping a Tablespace
- 27.24 Reorganizing Tablespaces
- 27.25 Importing Statistics
- 27.26 Using Export and Import to Partition a Database Migration
- 27.27 Tuning Considerations for Import Operations
- 27.28 Using Different Releases of Export and Import
-
19
Cloud Premigration Advisor
Tool
-
Appendices
-
A
Instant Client for SQL*Loader, Export, and Import
- A.1 What is the Tools Instant Client?
- A.2 Choosing Which Instant Client to Install
- A.3 Installing Instant Client Tools by Downloading from OTN
- A.4 Installing Tools Instant Client from the Client Release Media
- A.5 List of Oracle Instant Client Tools Files
- A.6 Configuring Tools Instant Client Package
- A.7 Connecting to a Database with the Tools Instant Client Package
- A.8 Uninstalling Tools Instant Client Package and Instant Client
- B SQL*Loader Syntax Diagrams
-
A
Instant Client for SQL*Loader, Export, and Import