1/39

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in Oracle Database 12c Release 2 (12.2)

• New Features
• Deprecated Features
• Desupported Features

Part I Getting Started

1 Introduction to Large Objects and SecureFiles

• What Are Large Objects?
• Why Use Large Objects?
  • Data Types that Use Large Objects
  • LOBs Used for Semistructured Data
  • LOBs Used for Unstructured Data
• Why Not Use LONGs?
• Different Kinds of LOBs
  • Internal LOBs
  • External LOBs and the BFILE Data Type
• LOB Locators
• Database Semantics for Internal and External LOBs
• Large Object Data Types
• About Object Data Types and LOBs
• Storage and Creation of Other Data Types with LOBs
  • VARRAYs Stored as LOBs
  • LOBs Used in Oracle Multimedia
• BasicFiles and SecureFiles LOBs
• Database File System (DBFS)

2 Working with LOBs

• LOB Column States
• Locking a Row Containing a LOB
• LOB Open and Close Operations
• LOB Locator and LOB Value
  • Using the Data Interface for LOBs
  • Use the LOB Locator to Access and Modify LOB Values
• LOB Locators and BFILE Locators
  • Table for LOB Examples: The PM Schema print_media Table
  • LOB Column Initialization
    • Initializing a Persistent LOB Column
    • Initializing BFILEs
• LOB Access
  • Accessing a LOB Using SQL
  • Accessing a LOB Using the Data Interface
  • Accessing a LOB Using the Locator Interface
• LOB Rules and Restrictions
  • Rules for LOB Columns
  • Restrictions for LOB Operations

3 Using Oracle LOB Storage

• LOB Storage
  • BasicFiles LOB Storage
  • SecureFiles LOB Storage
    • About Advanced LOB Compression
    • About Advanced LOB Deduplication
    • About SecureFiles Encryption
• CREATE TABLE with LOB Storage
  • CREATE TABLE LOB Storage Parameters
  • CREATE TABLE and SecureFiles LOB Features
    • CREATE TABLE with Advanced LOB Compression
      • Usage Notes for Advanced LOB Compression
      • Examples of CREATE TABLE and Advanced LOB Compression
    • CREATE TABLE with Advanced LOB Deduplication
      • Usage Notes for Advanced LOB Deduplication
      • Examples of CREATE TABLE and Advanced LOB Deduplication
    • CREATE TABLE with SecureFiles Encryption
      • Usage Notes for SecureFiles Encryption
      • Examples of CREATE TABLE and SecureFiles Encryption
• ALTER TABLE with LOB Storage
  • About ALTER TABLE and LOB Storage
  • BNF for the ALTER TABLE Statement
  • ALTER TABLE LOB Storage Parameters
  • ALTER TABLE SecureFiles LOB Features
    • ALTER TABLE with Advanced LOB Compression
      • Usage Notes for Advanced LOB Compression
      • Examples of ALTER TABLE and Advanced LOB Compression
    • ALTER TABLE with Advanced LOB Deduplication
      • Usage Notes for Advanced LOB Deduplication
      • Examples of ALTER TABLE and Advanced LOB Deduplication
    • ALTER TABLE with SecureFiles Encryption
      • Usage Notes for SecureFiles Encryption
      • Examples of ALTER TABLE and SecureFiles Encryption
• Initialization, Compatibility, and Upgrading
  • Compatibility and Upgrading
  • Initialization Parameter for SecureFiles LOBs
• Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
  • Preventing Generation of REDO Data When Migrating to SecureFiles LOBs
  • Online Redefinition for BasicFiles LOBs
  • Online Redefinition Example for Migrating Tables with BasicFiles LOBs
  • Redefining a SecureFiles LOB in Parallel
• PL/SQL Packages for LOBs and DBFS
  • The DBMS_LOB Package Used with SecureFiles LOBs and DBFS
  • DBMS_LOB Constants Used with SecureFiles LOBs and DBFS
  • DBMS_LOB Subprograms Used with SecureFiles LOBs and DBFS
  • DBMS_SPACE Package
    • DBMS_SPACE.SPACE_USAGE()

4 Operations Specific to Persistent and Temporary LOBs

• Persistent LOB Operations
  • Inserting a LOB into a Table
  • Selecting a LOB from a Table
• Temporary LOB Operations
  • Creating and Freeing a Temporary LOB
• Creating Persistent and Temporary LOBs in PL/SQL
• Freeing Temporary LOBs in OCI

5 Distributed LOBs

• Working with Remote LOBs
  • Working with Remote LOB Columns
    • Create table as select or insert as select
    • Functions on remote LOBs returning scalars
    • Data Interface for remote LOBs
  • Working with Remote Locator
    • Using Local and Remote locators as bind with queries and DML on remote tables
    • Restrictions when using remote LOB locators
• SQL Semantics with LOBs in Remote Tables
  • Built-in Functions for Remote LOBs and BFILEs
  • Passing Remote Locator to Built in SQL Functions
• Working with Remote LOBs in PL/SQL
  • PL/SQL Functions for Remote LOBs and BFILEs
    • Restrictions on Remote User-Defined Functions
    • Remote Functions in PL/SQL, OCI, and JDBC
  • Using Remote Locator in PL/SQL
  • Using Remote Locators with DBMS_LOB
    • Restrictions on Using Remote Locators with DBMS_LOB
• Using Remote Locators with OCILOB API

6 DDL and DML Statements with LOBs

• Creating a Table Containing One or More LOB Columns
• Creating a Nested Table Containing a LOB
• Inserting a Row by Selecting a LOB From Another Table
• Inserting a LOB Value Into a Table
• Inserting a Row by Initializing a LOB Locator Bind Variable
  • About Inserting Rows with LOB Locator Bind Variables
  • PL/SQL: Inserting a Row by Initializing a LOB Locator Bind Variable
  • C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable
  • COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable
  • C/C++ (Pro*C/C++): Inserting a Row by Initializing a LOB Locator Bind Variable
  • Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable
• Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()
• Updating a Row by Selecting a LOB From Another Table

Part II Value Semantics LOBs

7 SQL Semantics and LOBs

• About Using LOBs in SQL
• SQL Functions and Operators Supported for Use with LOBs
  • About SQL Functions and Operators for LOBs
  • Implicit Conversion of CLOB to CHAR Types
  • CLOBs and NCLOBs Do Not Follow Session Collation Settings
  • UNICODE Support
  • Codepoint Semantics
  • Return Values for SQL Semantics on LOBs
  • LENGTH Return Value for LOBs
• Implicit Conversion of LOB Data Types in SQL
  • Implicit Conversion Between CLOB and NCLOB Data Types in SQL
• Unsupported Use of LOBs in SQL
• VARCHAR2 and RAW Semantics for LOBs
  • About VARCHAR2 and RAW Semantics for LOBs
  • LOBs Returned from SQL Functions
  • IS NULL and IS NOT NULL Usage with VARCHAR2s and CLOBs
  • WHERE Clause Usage with LOBs
• Built-in Functions for Remote LOBs and BFILEs

8 PL/SQL Semantics for LOBs

• PL/SQL Statements and Variables
• Implicit Conversions Between CLOB and VARCHAR2
• Explicit Conversion Functions
  • VARCHAR2 and CLOB in PL/SQL Built-In Functions
• PL/SQL Functions for Remote LOBs and BFILEs

9 Data Interface for Persistent LOBs

• Overview of the Data Interface for Persistent LOBs
• Benefits of Using the Data Interface for Persistent LOBs
• Using the Data Interface for Persistent LOBs in PL/SQL
  • About Using the Data Interface for Persistent LOBs in PL/SQL
  • Guidelines for Accessing LOB Columns Using the Data Interface in SQL and PL/SQL
  • Implicit Assignment and Parameter Passing
  • Passing CLOBs to SQL and PL/SQL Built-In Functions
  • Explicit Conversion Functions
  • Calling PL/SQL and C Procedures from SQL
  • Calling PL/SQL and C Procedures from PL/SQL
  • Binds of All Sizes in INSERT and UPDATE Operations
  • 4000 Byte Limit on Results of a SQL Operator
  • Example of 4000 Byte Result Limit of a SQL Operator
  • Restrictions on Binds of More Than 4000 Bytes
  • Parallel DML (PDML) Support for LOBs
  • Example: PL/SQL - Using Binds of More Than 4000 Bytes in INSERT and UPDATE
  • Using the Data Interface for LOBs with INSERT, UPDATE, and SELECT Operations
  • Using the Data Interface for LOBs in Assignments and Parameter Passing
  • Using the Data Interface for LOBs with PL/SQL Built-In Functions
• The Data Interface Used for Persistent LOBs in OCI
  • LOB Data Types Bound in OCI
  • LOB Data Types Defined in OCI
  • Multibyte Character Sets Used in OCI with the Data Interface for LOBs
  • OCI Functions Used to Perform INSERT or UPDATE on LOB Columns
    • Performing Simple INSERTs or UPDATEs in One Piece
    • Using Piecewise INSERTs and UPDATEs with Polling
    • Performing Piecewise INSERTs and UPDATEs with Callback
    • Array INSERT and UPDATE Operations
  • The Data Interface Used to Fetch LOB Data in OCI
    • Simple Fetch in One Piece
    • Performing a Piecewise Fetch with Polling
    • Performing a Piecewise with Callback
    • Array Fetch
  • PL/SQL and C Binds from OCI
  • Example: C (OCI) - Binds of More than 4000 Bytes for INSERT and UPDATE
  • Using the Data Interface for LOBs in PL/SQL Binds from OCI on LOBs
  • Binding LONG Data for LOB Columns in Binds Greater Than 4000 Bytes
  • Binding LONG Data to LOB Columns Using Piecewise INSERT with Polling
  • Binding LONG Data to LOB Columns Using Piecewise INSERT with Callback
  • Binding LONG Data to LOB Columns Using an Array INSERT
  • Selecting a LOB Column into a LONG Buffer Using a Simple Fetch
  • Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Polling
  • Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Callback
  • Selecting a LOB Column into a LONG Buffer Using an Array Fetch
• The Data Interface Used with Persistent LOBs in Java
• The Data Interface Used with Remote LOBs
  • About the Data Interface with Remote LOBs
  • Non-Supported Syntax
  • Remote Data Interface Example in PL/SQL
  • Remote Data Interface Example in OCI
  • Remote Data Interface Examples in JDBC

Part III Reference Semantics LOBs

10 Overview of Supplied LOB APIs

• Programmatic Environments That Support LOBs
• Comparing the LOB Interfaces
• Using PL/SQL (DBMS_LOB Package) to Work With LOBs
  • Provide a LOB Locator Before Running the DBMS_LOB Routine
  • Guidelines for Offset and Amount Parameters in DBMS_LOB Operations
  • Determining Character Set ID
  • PL/SQL Functions and Procedures for LOBs
  • PL/SQL Functions and Procedures to Modify LOB Values
  • PL/SQL Functions and Procedures for Introspection of LOBs
  • PL/SQL Operations on Temporary LOBs
  • PL/SQL Read-Only Functions and Procedures for BFILEs
  • PL/SQL Functions and Procedures to Open and Close Internal and External LOBs
• Using OCI to Work With LOBs
  • Prefetching of LOB Data, Length, and Chunk Size
  • Setting the CSID Parameter for OCI LOB APIs
  • Fixed-Width and Varying-Width Character Set Rules for OCI
    • Other Operations
    • NCLOBs in OCI
  • OCILobLoadFromFile2() Amount Parameter
  • OCILobRead2() Amount Parameter
  • OCILobLocator Pointer Assignment
  • LOB Locators in Defines and Out-Bind Variables in OCI
  • OCI Functions That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
  • OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
  • OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values
  • OCI Functions for Temporary LOBs
  • OCI Read-Only Functions for BFILEs
  • OCI LOB Locator Functions
  • OCI LOB-Buffering Functions
  • OCI Functions to Open and Close Internal and External LOBs
  • OCI LOB Examples
  • Further Information About OCI
• Using C++ (OCCI) to Work With LOBs
  • OCCI Classes for LOBs
    • Clob Class
    • Blob Class
    • Bfile Class
  • Fixed-Width Character Set Rules
  • Varying-Width Character Set Rules
  • Offset and Amount Parameters for Other OCCI Operations
    • NCLOBs in OCCI
  • Amount Parameter for OCCI LOB copy() Methods
  • Amount Parameter for OCCI read() Operations
  • Further Information About OCCI
  • OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs
  • OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
  • OCCI Methods to Read or Examine Persistent LOB and BFILE Values
  • OCCI Read-Only Methods for BFILEs
  • Other OCCI LOB Methods
  • OCCI Methods to Open and Close Internal and External LOBs
• Using C/C++ (Pro*C) to Work With LOBs
  • Providing an Allocated Input Locator Pointer That Represents LOB
  • Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
  • Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values
  • Pro*C/C++ Embedded SQL Statements for Introspection of LOBs
  • Pro*C/C++ Embedded SQL Statements for Temporary LOBs
  • Pro*C/C++ Embedded SQL Statements for BFILEs
  • Pro*C/C++ Embedded SQL Statements for LOB Locators
  • Pro*C/C++ Embedded SQL Statements for LOB Buffering
  • Pro*C/C++ Embedded SQL Statements to Open and Close LOBs
• Using COBOL (Pro*COBOL) to Work With LOBs
  • Providing an Allocated Input Locator Pointer That Represents LOB
  • Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
  • Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values
  • Pro*COBOL Embedded SQL Statements for Introspection of LOBs
  • Pro*COBOL Embedded SQL Statements for Temporary LOBs
  • Pro*COBOL Embedded SQL Statements for BFILEs
  • Pro*COBOL Embedded SQL Statements for LOB Locators
  • Pro*COBOL Embedded SQL Statements for LOB Buffering
  • Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs
• Using Java (JDBC) to Work With LOBs
  • Modifying Internal Persistent LOBs Using Java
  • Reading Internal Persistent LOBs and External LOBs (BFILEs) With Java
    • BLOB, CLOB, and BFILE Classes
  • Calling DBMS_LOB Package from Java (JDBC)
  • Prefetching LOBs to Improve Performance
  • Zero-Copy Input/Output for SecureFiles to Improve Performance
    • Zero-Copy Input/Output on the Server
    • Zero-Copy Input/Output in the JDBC Thin Driver
    • JDBC-OCI Driver Considerations
  • Referencing LOBs Using Java (JDBC)
    • Using OracleResultSet: BLOB and CLOB Objects Retrieved
  • JDBC Syntax References and Further Information
  • JDBC Methods for Operating on LOBs
  • JDBC oracle.sql.BLOB Methods to Modify BLOB Values
  • JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values
  • JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering
  • JDBC oracle.sql.CLOB Methods to Modify CLOB Values
  • JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value
  • JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering
  • JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values
  • JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering
  • JDBC Temporary LOB APIs
  • JDBC: Opening and Closing LOBs
  • JDBC: Opening and Closing BLOBs
    • Opening the BLOB Using JDBC
    • Checking If the BLOB Is Open Using JDBC
    • Closing the BLOB Using JDBC
  • JDBC: Opening and Closing CLOBs
    • Opening the CLOB Using JDBC
    • Checking If the CLOB Is Open Using JDBC
    • Closing the CLOB Using JDBC
  • JDBC: Opening and Closing BFILEs
    • Opening BFILEs
    • Checking If the BFILE Is Open
    • Closing the BFILE
    • Usage Example (OpenCloseLob.java)
  • Truncating LOBs Using JDBC
    • JDBC: Truncating BLOBs
    • JDBC: Truncating CLOBs
  • JDBC BLOB Streaming APIs
  • JDBC CLOB Streaming APIs
  • BFILE Streaming APIs
    • JDBC BFILE Streaming Example (NewStreamLob.java)
  • JDBC and Empty LOBs
• Oracle Provider for OLE DB (OraOLEDB)
• Overview of Oracle Data Provider for .NET (ODP.NET)

11 LOB APIs for BFILE Operations

• Supported Environments for BFILE APIs
• About Accessing BFILEs
• Directory Objects
  • Initializing a BFILE Locator
  • How to Associate Operating System Files with a BFILE
• BFILENAME and Initialization
• Characteristics of the BFILE Data Type
  • DIRECTORY Name Specification
    • On Windows Platforms
• BFILE Security
  • Ownership and Privileges
  • Read Permission on a DIRECTORY Object
  • SQL DDL for BFILE Security
  • SQL DML for BFILE Security
  • Catalog Views on Directories
  • Guidelines for DIRECTORY Usage
  • BFILEs in Shared Server (Multithreaded Server) Mode
  • External LOB (BFILE) Locators
    • When Two Rows in a BFILE Table Refer to the Same File
    • BFILE Locator Variable
    • Guidelines for BFILEs
• About Loading a LOB with BFILE Data
• About Opening a BFILE with OPEN
• About Opening a BFILE with FILEOPEN
• About Determining Whether a BFILE Is Open Using ISOPEN
• About Determining Whether a BFILE Is Open with FILEISOPEN
• About Displaying BFILE Data
• About Reading Data from a BFILE
• About Reading a Portion of BFILE Data Using SUBSTR
• Comparing All or Parts of Two BFILES
• Checking If a Pattern Exists in a BFILE Using INSTR
• Determining Whether a BFILE Exists
• Getting the Length of a BFILE
• About Assigning a BFILE Locator
• Getting Directory Object Name and File Name of a BFILE
• About Updating a BFILE by Initializing a BFILE Locator
• Closing a BFILE with FILECLOSE
• Closing a BFILE with CLOSE
• Closing All Open BFILEs with FILECLOSEALL
• About Inserting a Row Containing a BFILE

12 Using LOB APIs

• Supported Environments
• About Appending One LOB to Another
• About Determining Character Set Form
• About Determining Character Set ID
• Loading a LOB with Data from a BFILE
• About Loading a BLOB with Data from a BFILE
• Loading a CLOB or NCLOB with Data from a BFILE
  • About PL/SQL: Loading Character Data from a BFILE into a LOB
  • About PL/SQL: Loading Segments of Character Data into Different LOBs
• Determining Whether a LOB is Open
  • Java (JDBC): Checking If a LOB Is Open
    • Checking If a CLOB Is Open
    • Checking If a BLOB Is Open
• About Displaying LOB Data
• About Reading Data from a LOB
• About LOB Array Read
• Reading a Portion of a LOB (SUBSTR)
• Comparing All or Part of Two LOBs
• Patterns: Checking for Patterns in a LOB Using INSTR
• Length: Determining the Length of a LOB
• Copying All or Part of One LOB to Another LOB
• Copying a LOB Locator
• Equality: Checking If One LOB Locator Is Equal to Another
• About Determining Whether LOB Locator Is Initialized
• About Appending to a LOB
• About Writing Data to a LOB
• LOB Array Write
• About Trimming LOB Data
• About Erasing Part of a LOB
• Enabling LOB Buffering
• About Flushing the Buffer
• About Disabling LOB Buffering
• Determining Whether a LOB instance Is Temporary
  • Java (JDBC): Determining Whether a BLOB Is Temporary
• Converting a BLOB to a CLOB
• Converting a CLOB to a BLOB
• Ensuring Read Consistency

Part IV Application Design with LOBs

13 LOB Storage with Applications

• Tables That Contain LOBs
  • Persistent LOBs Initialized to NULL or Empty
    • Setting a Persistent LOB to NULL
    • Setting a Persistent LOB to Empty
  • Initializing LOBs
  • Initializing Persistent LOB Columns and Attributes to a Value
  • Initializing BFILEs to NULL or a File Name
  • Restriction on First Extent of a LOB Segment
• Data Types for LOB Columns
  • LOBs Compared to LONG and LONG RAW Types
  • Varying-Width Character Data Storage in LOBs
  • Converting Character Sets Implicitly with LOBs
• LOB Storage Parameters
  • Inline and Out-of-Line LOB Storage
  • Defining Tablespace and Storage Characteristics for Persistent LOBs
    • Assigning a LOB Data Segment Name
  • LOB Storage Characteristics for LOB Column or Attribute
  • TABLESPACE and LOB Index
    • Tablespace for LOB Index in Non-Partitioned Table
  • PCTVERSION
  • RETENTION Parameter for BasicFiles LOBs
  • RETENTION Parameter for SecureFiles LOBs
  • CACHE / NOCACHE / CACHE READS
    • CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache
  • LOGGING / NOLOGGING Parameter for BasicFiles LOBs
    • LOBs Always Generate Undo for LOB Index Pages
    • When LOGGING is Set Oracle Generates Full Redo for LOB Data Pages
      • NOLOGGING is Useful for Bulk Loads or Inserts.
  • LOGGING/FILESYSTEM_LIKE_LOGGING for SecureFiles LOBs
    • CACHE Implies LOGGING
    • SecureFiles and an Efficient Method of Generating REDO and UNDO
    • FILESYSTEM_LIKE_LOGGING is Useful for Bulk Loads or Inserts
  • CHUNK
    • The Value of CHUNK
      • Space Considerations
      • Performance Considerations
    • Set INITIAL and NEXT to Larger than CHUNK
  • ENABLE or DISABLE STORAGE IN ROW Clause
  • Guidelines for ENABLE or DISABLE STORAGE IN ROW
• LOB Columns Indexing
  • Domain Indexing on LOB Columns
  • Text Indexes on LOB Columns
  • Function-Based Indexes on LOBs
  • Extensible Indexing on LOB Columns
    • Extensible Optimizer
  • Oracle Text Indexing Support for XML
• LOB Manipulation in Partitioned Tables
  • About Manipulating LOBs in Partitioned Tables
  • Partitioning a Table Containing LOB Columns
  • Creating an Index on a Table Containing Partitioned LOB Columns
  • Moving Partitions Containing LOBs
  • Splitting Partitions Containing LOBs
  • Merging Partitions Containing LOBs
• LOBs in Index Organized Tables
• Restrictions for LOBs in Partitioned Index-Organized Tables
• Updating LOBs in Nested Tables

14 Advanced Design Considerations

• LOB Buffering Subsystem
  • Advantages of LOB Buffering
  • Guidelines for Using LOB Buffering
  • LOB Buffering Subsystem Usage
    • LOB Buffer Physical Structure
    • LOB Buffering Subsystem Usage Scenario
  • About Flushing the LOB Buffer
  • Flushing the LOB Buffer
  • About Flushing the Updated LOB
  • Flushing the Updated LOB
  • Use of Buffer-Enabled Locators
  • Saving Locator State to Avoid a Reselect
  • OCI Example of LOB Buffering
• Opening Persistent LOBs with the OPEN and CLOSE Interfaces
  • Index Performance Benefits of Explicitly Opening a LOB
  • Closing Explicitly Open LOB Instances
• Read-Consistent Locators
  • A Selected Locator Becomes a Read-Consistent Locator
  • Example of Updating LOBs and Read-Consistency
  • Example of Updating LOBs Through Updated Locators
  • Example of Updating a LOB Using SQL DML and DBMS_LOB
  • Example of Using One Locator to Update the Same LOB Value
  • Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable
• LOB Locators and Transaction Boundaries
  • About LOB Locators and Transaction Boundaries
  • Read and Write Operations on a LOB Using Locators
  • Selecting the Locator Outside of the Transaction Boundary
  • Selecting the Locator Within a Transaction Boundary
  • LOB Locators Cannot Span Transactions
  • Example of Locator Not Spanning a Transaction
• LOBs in the Object Cache
• Terabyte-Size LOB Support
  • About Terabyte-Size LOB Support
  • Maximum Storage Limit for Terabyte-Size LOBs
  • Using Terabyte-Size LOBs with JDBC
  • Using Terabyte-Size LOBs with the DBMS_LOB Package
  • Using Terabyte-Size LOBs with OCI
• Guidelines for Creating Gigabyte LOBs
  • Creating a Tablespace and Table to Store Gigabyte LOBs

15 Performance Guidelines

• LOB Performance Guidelines
  • Chunk Size
  • Performance Guidelines for Small BasicFiles LOBs
  • General Performance Guidelines for BasicFiles LOBs
  • Temporary LOB Performance Guidelines
  • Performance Considerations for SQL Semantics and LOBs
• Moving Data to LOBs in a Threaded Environment
• LOB Access Statistics
  • Example of Retrieving LOB Access Statistics

Part V LOB Administration

16 Managing LOBs: Database Administration

• Database Utilities for Loading Data into LOBs
  • About Using SQL*Loader to Load LOBs
  • About Using SQL*Loader to Populate a BFILE Column
  • About Using Oracle Data Pump to Transfer LOB Data
• Temporary LOB Management
• BFILEs Management
  • Rules for Using Directory Objects and BFILEs
  • Setting Maximum Number of Open BFILEs
• Changing Tablespace Storage for a LOB

17 Migrating Columns from LONGs to LOBs

• Benefits of Migrating LONG Columns to LOB Columns
• Preconditions for Migrating LONG Columns to LOB Columns
  • Dropping a Domain Index on a LONG Column Before Converting to a LOB
  • Preventing Generation of Redo Space on Tables Converted to LOB Data Types
• Determining how to Optimize the Application Using utldtree.sql
• Converting Tables from LONG to LOB Data Types
  • Migration Issues
  • Using ALTER TABLE to Convert LONG Columns to LOB Columns
  • Copying a LONG to a LOB Column Using the TO_LOB Operator
  • Online Redefinition of Tables with LONG Columns
  • Using Oracle Data Pump to Migrate a Database
• Migrating Applications from LONGs to LOBs
  • About Migrating Applications from Longs to LOBs
  • LOB Columns Are Not Allowed in Clustered Tables
  • LOB Columns Are Not Allowed in AFTER UPDATE OF Triggers
  • Rebuilding Indexes on Columns Converted from LONG to LOB Data Types
  • Empty LOBs Compared to NULL and Zero Length LONGs
  • Overloading with Anchored Types
  • Some Implicit Conversions Are Not Supported for LOB Data Types

Part VI Oracle File System (OFS) Server

18 Introducing Network File System (NFS)

• Prerequisites to Access Storage Through NFS Server
• NFS Security
  • Kerberos
    • Configuring Kerberos Server in Linux

19 Using OFS

• Limitations of using OFS
• OFS Configuration Parameters
• OFS Client Interface
  • DBMS_FS Package
  • Views for OFS

Part VII Database File System (DBFS)

20 Introducing the Database File System

• Why a Database File System?
• What Is Database File System (DBFS)?
  • About DBFS
  • DBFS Server
  • DBFS Client
• What Is a Content Store?

21 DBFS SecureFiles Store

• Setting Up a SecureFiles Store
  • About Managing Permissions
  • Creating or Setting Permissions
  • Creating a SecureFiles File System Store
  • Accessing Tables that Hold SecureFiles System Store Data
  • Initializing SecureFiles Store File Systems
  • Comparison of SecureFiles LOBs to BasicFiles LOBs
• Using a DBFS SecureFiles Store File System
  • DBFS Content API Working Example
  • Dropping SecureFiles Store File Systems
• About DBFS SecureFiles Store Package, DBMS_DBFS_SFS
• Database File System (DBFS)— POSIX File Locking
  • About Advisory Locking
  • About Mandatory Locking
  • File Locking Support
  • Compatibility and Migration Factors of Database Filesystem—File Locking
  • Examples of Database Filesystem—File Locking
  • File Locking Behavior
  • Scheduling File Locks
    • Greedy Scheduling
    • Fair Scheduling

22 DBFS Hierarchical Store

• About the Hierarchical Store Package, DBMS_DBFS_HS
• Ways to Use DBFS Hierarchial Store
• Setting up the Store
  • Managing a HS Store Wallet
  • Creating, Registering, and Mounting the Store
• Using the Hierarchical Store
  • Using Hierarchical Store as a File System
  • Using Hierarchical Store as an Archive Solution For SecureFiles LOBs
  • Dropping a Hierarchical Store
  • Compression to Use with the Hierarchical Store
  • Program Example Using Tape
  • Program Example Using Amazon S3
• Database File System Links
  • About Database File System Links
  • Ways to Create Database File System Links
  • Database File System Links Copy
  • Copying a Linked LOB Between Tables
  • Online Redefinition and DBFS Links
  • Transparent Read
• The DBMS_DBFS_HS Package
  • Constants for DBMS_DBFS_HS Package
  • Methods for DBMS_DBFS_HS Package
• Views for DBFS Hierarchical Store
  • DBA Views
  • User Views

23 DBFS Content API

• Overview of DBFS Content API
• Stores and DBFS Content API
• Getting Started with DBMS_DBFS_CONTENT Package
  • DBFS Content API Role
  • Path Name Constants and Types
  • Path Properties
  • Content IDs
  • Path Name Types
  • Store Features
  • Lock Types
  • Standard Properties
  • Optional Properties
  • User-Defined Properties
  • Property Access Flags
  • Exceptions
  • Property Bundles
  • Store Descriptors
• Administrative and Query APIs
  • Registering a Content Store
  • Unregistering a Content Store
  • Mounting a Registered Store
  • Unmounting a Previously Mounted Store
  • Listing all Available Stores and Their Features
  • Listing all Available Mount Points
  • Looking Up Specific Stores and Their Features
• Querying DBFS Content API Space Usage
• DBFS Content API Session Defaults
• DBFS Content API Interface Versioning
• Notes on DBFS Content API Path Names
• DBFS Content API Creation Operations
• DBFS Content API Deletion Operations
• DBFS Content API Path Get and Put Operations
• DBFS Content API Rename and Move Operations
• Directory Listings
• DBFS Content API Directory Navigation and Search
• DBFS Content API Locking Operations
• DBFS Content API Access Checks
• DBFS Content API Abstract Operations
• DBFS Content API Path Normalization
• DBFS Content API Statistics Support
• DBFS Content API Tracing Support
• Resource and Property Views

24 Creating Your Own DBFS Store

• Overview of DBFS Store Creation and Use
• DBFS Content Store Provider Interface (DBFS Content SPI)
• Creating a Custom Provider
  • Mechanics
    • Installation and Setup
    • TBFS Use
    • TBFS Internals
  • TBFS.SQL
  • TBL.SQL
  • spec.sql
  • body.sql
  • capi.sql

25 Using DBFS

• DBFS Installation
• Creating a DBFS File System
  • Privileges Required to Create a DBFS File System
  • Advantages of Non-Partitioned Versus Partitioned DBFS File Systems
  • Creating a Non-Partitioned File System
  • Creating a Partitioned File System
  • Dropping a File System
• DBFS File System Access
  • DBFS Client Prerequisites
  • DBFS Client Command-Line Interface Operations
    • About the DBFS Client Command-Line Interface
    • Creating Content Store Paths
    • Creating a Directory
    • Listing a Directory
    • Copying Files and Directories
    • Removing Files and Directories
  • DBFS Mounting Interface (Linux and Solaris Only)
    • Installing FUSE on Solaris 11 SRU7 and Later
    • Mounting the DBFS Store
    • Solaris-Specific Privileges
    • About the Mount Command for Solaris and Linux
    • Mounting a File System with a Wallet
    • Mounting a File System with Password at Command Prompt
    • Mounting a File System with Password Read from a File
    • Unmounting a File System
    • Mounting DBFS Through fstab Utility for Linux
    • Mounting DBFS Through the vfstab Utility for Solaris
    • Restrictions on Mounted File Systems
  • File System Security Model
    • About the File System Security Model
    • Enabling Shared Root Access
    • About DBFS Access Among Multiple Database Users
    • Establishing DBFS Access Sharing Across Multiple Database Users
  • HTTP, WebDAV, and FTP Access to DBFS
    • Internet Access to DBFS Through XDB
    • Web Distributed Authoring and Versioning (WebDAV) Access
    • FTP Access to DBFS
    • HTTP Access to DBFS
• DBFS Administration
  • Using Oracle Wallet with DBFS Client
  • DBFS Diagnostics
  • Preventing Data Loss During Failover Events
  • Bypassing Client-Side Write Caching
  • Backing up DBFS
    • DBFS Backup at the Database Level
    • DBFS Backup Through a File System Utility
  • Small File Performance of DBFS
  • Enabling Advanced SecureFiles LOB Features for DBFS
• Shrinking and Reorganizing DBFS Filesystems
  • About Changing DBFS Filesystems
  • Advantages of Online Filesystem Reorganization
  • Determining Availability of Online Filesystem Reorganization
  • Invoking Online Filesystem Reorganization

A LOB Demonstration Files

• PL/SQL LOB Demonstration Files
• OCI LOB Demonstration Files
• Java LOB Demonstration Files

Glossary

Index