Expert Oracle Database Architecture: Oracle Database 9

Expert Oracle Database Architecture: Oracle Database 9

by
Released July 2010
Publisher(s): Apress
ISBN: 9781430229469

Explore a preview version of Expert Oracle Database Architecture: Oracle Database 9 right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book description

Now in its second edition, this best-selling book by Tom Kyte of Ask Tom fame continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that perform well and deliver correct results. Tom has a simple philosophy: you can treat Oracle as a black box and just stick data into it or you can understand how it works and exploit it as a powerful computing environment. If you choose the latter, then you'll find that there are few information management problems that you cannot solve quickly and elegantly.

This fully revised second edition covers the latest developments in Oracle Database 11g. Each feature is taught in a proof-by-example manner, not only discussing what it is, but also how it works, how to implement software using it, and the common pitfalls associated with it.

Don't treat Oracle Database as a black-box. Get this book. Get under the hood. Turbo-charge your career.

  • Fully-revised to cover Oracle Database 11g

  • Proof-by-example approach: Let the evidence be your guide

  • Dives deeply into Oracle Databases's most powerful features

Table of contents

  1. Copyright
  2. Foreword
  3. Foreword from the First Edition
  4. About the Author
  5. About the Technical Reviewers
  6. Acknowledgments
  7. Introduction
    1. What This Book Is About
    2. Who Should Read This Book
    3. How This Book Is Structured
      1. Chapter 1: Developing Successful Oracle Applications
      2. Chapter 2: Architecture Overview
      3. Chapter 3: Files
      4. Chapter 4: Memory Structures
      5. Chapter 5: Oracle Processes
      6. Chapter 6: Locking and Latching
      7. Chapter 7: Concurrency and Multi-versioning
      8. Chapter 8: Transactions
      9. Chapter 9: Redo and Undo
      10. Chapter 10: Database Tables
      11. Chapter 11: Indexes
      12. Chapter 12: Datatypes
      13. Chapter 13: Partitioning
      14. Chapter 14: Parallel Execution
      15. Chapter 15: Data Loading and Unloading
      16. Chapter 16: Data Encryption
    4. Source Code and Updates
    5. Errata
  8. Setting Up Your Environment
    1. Setting up the SCOTT/TIGER Schema
      1. Executing the Script
      2. Creating the Schema without the Script
    2. Setting Your Environment
    3. Setting up Autotrace in SQL*Plus
      1. Initial Setup
      2. Controlling the Report
    4. Setting up Statspack
    5. Custom Scripts
      1. Runstats
      2. Mystat
      3. Show_Space
      4. Big_Table
    6. Coding Conventions
  9. 1. Developing Successful Oracle Applications
    1. 1.1. My Approach
    2. 1.2. The Black Box Approach
    3. 1.3. How (and How Not) to Develop Database Applications
      1. 1.3.1. Understanding Oracle Architecture
        1. 1.3.1.1. Use a Single Connection in Oracle
        2. 1.3.1.2. Use Bind Variables
      2. 1.3.2. Understanding Concurrency Control
        1. 1.3.2.1. Implementing Locking
        2. 1.3.2.2. Preventing Lost Updates
      3. 1.3.3. Multi-versioning
        1. 1.3.3.1. Flashback
        2. 1.3.3.2. Read Consistency and Non-Blocking Reads
      4. 1.3.4. Database Independence?
        1. 1.3.4.1. The Impact of Standards
        2. 1.3.4.2. Make Sure You Can Adapt
        3. 1.3.4.3. Layered Programming
        4. 1.3.4.4. Features and Functions
        5. 1.3.4.5. Knowing What's Out There
        6. 1.3.4.6. Solving Problems Simply
        7. 1.3.4.7. Openness
      5. 1.3.5. How Do I Make It Run Faster?
      6. 1.3.6. The DBA-Developer Relationship
    4. 1.4. Summary
  10. 2. Architecture Overview
    1. 2.1. Defining Database and Instance
      1. 2.1.1. The SGA and Background Processes
    2. 2.2. Connecting to Oracle
      1. 2.2.1. Dedicated Server
      2. 2.2.2. Shared Server
      3. 2.2.3. Mechanics of Connecting over TCP/IP
    3. 2.3. Summary
  11. 3. Files
    1. 3.1. Parameter Files
      1. 3.1.1. What Are Parameters?
      2. 3.1.2. Legacy init.ora Parameter Files
    2. 3.2. Server Parameter Files (SPFILEs)
      1. 3.2.1. Converting to SPFILEs
        1. 3.2.1.1. Setting Values in SPFILEs
        2. 3.2.1.2. Unsetting Values in SPFILEs
        3. 3.2.1.3. Creating PFILEs from SPFILEs
        4. 3.2.1.4. Fixing Corrupted SPFILEs
        5. 3.2.1.5. Parameter File Wrap-up
    3. 3.3. Trace Files
      1. 3.3.1. Requested Trace Files
        1. 3.3.1.1. File Locations
        2. 3.3.1.2. Naming Convention
        3. 3.3.1.3. Tagging Trace Files
      2. 3.3.2. Trace Files Generated in Response to Internal Errors
      3. 3.3.3. Trace File Wrap-up
    4. 3.4. Alert File
    5. 3.5. Data Files
      1. 3.5.1. A Brief Review of File System Mechanisms
      2. 3.5.2. The Storage Hierarchy in an Oracle Database
        1. 3.5.2.1. Segments
        2. 3.5.2.2. Extents
        3. 3.5.2.3. Blocks
        4. 3.5.2.4. Tablespaces
        5. 3.5.2.5. Storage Hierarchy Summary
      3. 3.5.3. Dictionary-Managed and Locally-Managed Tablespaces
    6. 3.6. Temp Files
    7. 3.7. Control Files
    8. 3.8. Redo Log Files
      1. 3.8.1. Online Redo Log
      2. 3.8.2. Archived Redo Log
    9. 3.9. Password Files
    10. 3.10. Change Tracking File
    11. 3.11. Flashback Logs
      1. 3.11.1. Flashback Database
      2. 3.11.2. Flash Recovery Area
    12. 3.12. DMP Files (EXP/IMP Files)
    13. 3.13. Data Pump Files
    14. 3.14. Flat Files
    15. 3.15. Summary
  12. 4. Memory Structures
    1. 4.1. The Process Global Area and User Global Area
      1. 4.1.1. Manual PGA Memory Management
      2. 4.1.2. Automatic PGA Memory Management
        1. 4.1.2.1. Determining How the Memory Is Allocated
        2. 4.1.2.2. Using PGA_AGGREGATE_TARGET to Control Memory Allocation
      3. 4.1.3. Choosing Between Manual and Auto Memory Management
      4. 4.1.4. PGA and UGA Wrap-up
    2. 4.2. The System Global Area
      1. 4.2.1. Fixed SGA
      2. 4.2.2. Redo Buffer
      3. 4.2.3. Block Buffer Cache
        1. 4.2.3.1. Managing Blocks in the Buffer Cache
        2. 4.2.3.2. Multiple Block Sizes
      4. 4.2.4. Shared Pool
      5. 4.2.5. Large Pool
      6. 4.2.6. Java Pool
      7. 4.2.7. Streams Pool
      8. 4.2.8. Automatic SGA Memory Management
      9. 4.2.9. Automatic Memory Management
    3. 4.3. Summary
  13. 5. Oracle Processes
    1. 5.1. Server Processes
      1. 5.1.1. Dedicated Server Connections
      2. 5.1.2. Shared Server Connections
      3. 5.1.3. Database Resident Connection Pooling (DRCP)
      4. 5.1.4. Connections vs. Sessions
      5. 5.1.5. Dedicated Server vs. Shared Server vs. DRCP
        1. 5.1.5.1. When to Use Dedicated Server
        2. 5.1.5.2. When to Use Shared Server
        3. 5.1.5.3. Potential Benefits of Shared Server
          1. 5.1.5.3.1. Reduces the Number of Operating System Processes/Threads
          2. 5.1.5.3.2. Artificially Limits the Degree of Concurrency
          3. 5.1.5.3.3. Reduces the Memory Needed on the System
        4. 5.1.5.4. DRCP
      6. 5.1.6. Dedicated/Shared Server Wrap-up
    2. 5.2. Background Processes
      1. 5.2.1. Focused Background Processes
        1. 5.2.1.1. PMON: The Process Monitor
        2. 5.2.1.2. SMON: The System Monitor
        3. 5.2.1.3. RECO: Distributed Database Recovery
        4. 5.2.1.4. CKPT: Checkpoint Process
        5. 5.2.1.5. DBWn: Database Block Writer
        6. 5.2.1.6. LGWR: Log Writer
        7. 5.2.1.7. ARCn: Archive Process
        8. 5.2.1.8. DIAG: Diagnosability Process
        9. 5.2.1.9. FBDA: Flashback Data Archiver Process
        10. 5.2.1.10. DBRM: Database ResourceManager Process
        11. 5.2.1.11. GEN0: General Task Execution Process
        12. 5.2.1.12. Remaining Common Focused Processes
      2. 5.2.2. Utility Background Processes
        1. 5.2.2.1. CJQ0 and Jnnn Processes: Job Queues
        2. 5.2.2.2. QMNC and Qnnn: Advanced Queues
        3. 5.2.2.3. EMNC: Event Monitor Processes
        4. 5.2.2.4. MMAN: Memory Manager
        5. 5.2.2.5. MMON, MMNL, and Mnnn: Manageability Monitors
        6. 5.2.2.6. CTWR: Change Tracking Processes
        7. 5.2.2.7. RVWR: Recovery Writer
        8. 5.2.2.8. DMnn/DWnn: Data Pump Master/Worker Processes
        9. 5.2.2.9. Remaining Utility Background Processes
    3. 5.3. Slave Processes
      1. 5.3.1. I/O Slaves
      2. 5.3.2. Pnnn: Parallel Query Execution Servers
    4. 5.4. Summary
  14. 6. Locking and Latching
    1. 6.1. What Are Locks?
    2. 6.2. Locking Issues
      1. 6.2.1. Lost Updates
      2. 6.2.2. Pessimistic Locking
      3. 6.2.3. Optimistic Locking
        1. 6.2.3.1. Optimistic Locking Using a Version Column
        2. 6.2.3.2. Optimistic Locking Using a Checksum
      4. 6.2.4. Optimistic or Pessimistic Locking?
      5. 6.2.5. Blocking
        1. 6.2.5.1. Blocked Inserts
        2. 6.2.5.2. Blocked Merges, Updates, and Deletes
      6. 6.2.6. Deadlocks
      7. 6.2.7. Lock Escalation
    3. 6.3. Lock Types
      1. 6.3.1. DML Locks
        1. 6.3.1.1. TX (Transaction) Locks
        2. 6.3.1.2. TM (DML Enqueue) Locks
      2. 6.3.2. DDL Locks
      3. 6.3.3. Latches
        1. 6.3.3.1. Latch "Spinning"
        2. 6.3.3.2. Measuring the Cost of Latching a Shared Resource
          1. 6.3.3.2.1. Setting Up for the Test
          2. 6.3.3.2.2. Without Bind Variables
          3. 6.3.3.2.3. With Bind Variables
          4. 6.3.3.2.4. Performance/Scalability Comparison
      4. 6.3.4. Mutexes
      5. 6.3.5. Manual Locking and User-Defined Locks
        1. 6.3.5.1. Manual Locking
        2. 6.3.5.2. Creating Your Own Locks
    4. 6.4. Summary
  15. 7. Concurrency and Multi-versioning
    1. 7.1. What Are Concurrency Controls?
    2. 7.2. Transaction Isolation Levels
      1. 7.2.1. READ UNCOMMITTED
      2. 7.2.2. READ COMMITTED
      3. 7.2.3. REPEATABLE READ
        1. 7.2.3.1. Getting a Consistent Answer
        2. 7.2.3.2. Lost Updates: Another Portability Issue
      4. 7.2.4. SERIALIZABLE
      5. 7.2.5. READ ONLY
    3. 7.3. Implications of Multi-version Read Consistency
      1. 7.3.1. A Common Data Warehousing Technique That Fails
      2. 7.3.2. An Explanation for Higher Than Expected I/O on Hot Tables
    4. 7.4. Write Consistency
      1. 7.4.1. Consistent Reads and Current Reads
      2. 7.4.2. Seeing a Restart
      3. 7.4.3. Why Is a Restart Important to Us?
    5. 7.5. Summary
  16. 8. Transactions
    1. 8.1. Transaction Control Statements
    2. 8.2. Atomicity
      1. 8.2.1. Statement-Level Atomicity
      2. 8.2.2. Procedure-Level Atomicity
      3. 8.2.3. Transaction-Level Atomicity
      4. 8.2.4. DDL and Atomicity
    3. 8.3. Durability
      1. 8.3.1. WRITE Extensions to COMMIT
      2. 8.3.2. COMMITS in a Non-Distributed PL/SQL Block
    4. 8.4. Integrity Constraints and Transactions
      1. 8.4.1. IMMEDIATE Constraints
      2. 8.4.2. DEFERRABLE Constraints and Cascading Updates
    5. 8.5. Bad Transaction Habits
      1. 8.5.1. Committing in a Loop
        1. 8.5.1.1. Performance Implications
        2. 8.5.1.2. Snapshot Too Old Error
        3. 8.5.1.3. Restartable Processes Require Complex Logic
      2. 8.5.2. Using Autocommit
    6. 8.6. Distributed Transactions
    7. 8.7. Autonomous Transactions
      1. 8.7.1. How Autonomous Transactions Work
      2. 8.7.2. When to Use Autonomous Transactions
    8. 8.8. Summary
  17. 9. Redo and Undo
    1. 9.1. What Is Redo?
    2. 9.2. What Is Undo?
    3. 9.3. How Redo and Undo Work Together
      1. 9.3.1. Example INSERT-UPDATE-DELETE Scenario
        1. 9.3.1.1. The INSERT
          1. 9.3.1.1.1. Hypothetical Scenario: The System Crashes Right Now
          2. 9.3.1.1.2. Hypothetical Scenario: The Buffer Cache Fills Up Right Now
        2. 9.3.1.2. The UPDATE
          1. 9.3.1.2.1. Hypothetical Scenario: The System Crashes Right Now
          2. 9.3.1.2.2. Hypothetical Scenario: The Application Rolls Back the Transaction
        3. 9.3.1.3. The DELETE
        4. 9.3.1.4. The COMMIT
    4. 9.4. Commit and Rollback Processing
      1. 9.4.1. What Does a COMMIT Do?
      2. 9.4.2. What Does a ROLLBACK Do?
    5. 9.5. Investigating Redo
      1. 9.5.1. Measuring Redo
      2. 9.5.2. Can I Turn Off Redo Log Generation?
        1. 9.5.2.1. Setting NOLOGGING in SQL
        2. 9.5.2.2. Setting NOLOGGING on an Index
        3. 9.5.2.3. NOLOGGING Wrap-up
      3. 9.5.3. Why Can't I Allocate a New Log?
      4. 9.5.4. Block Cleanout
      5. 9.5.5. Log Contention
      6. 9.5.6. Temporary Tables and Redo/Undo
    6. 9.6. Investigating Undo
      1. 9.6.1. What Generates the Most and Least Undo?
      2. 9.6.2. ORA-01555: snapshot too old Error
        1. 9.6.2.1. Undo Segments Are in Fact Too Small
        2. 9.6.2.2. Delayed Block Cleanout
    7. 9.7. Summary
  18. 10. Database Tables
    1. 10.1. Types of Tables
    2. 10.2. Terminology
      1. 10.2.1. Segment
      2. 10.2.2. Segment Space Management
      3. 10.2.3. High-water Mark
      4. 10.2.4. FREELISTS
      5. 10.2.5. PCTFREE and PCTUSED
        1. 10.2.5.1. Row Migration
        2. 10.2.5.2. Setting PCTFREE and PCTUSED Values
      6. 10.2.6. LOGGING and NOLOGGING
      7. 10.2.7. INITRANS and MAXTRANS
    3. 10.3. Heap Organized Tables
    4. 10.4. Index Organized Tables
      1. 10.4.1. Index Organized Tables Wrap-up
    5. 10.5. Index Clustered Tables
      1. 10.5.1. Index Clustered Tables Wrap-up
    6. 10.6. Hash Clustered Tables
      1. 10.6.1. Hash Clustered Tables Wrap-up
    7. 10.7. Sorted Hash Clustered Tables
    8. 10.8. Nested Tables
      1. 10.8.1. Nested Tables Syntax
      2. 10.8.2. Nested Table Storage
      3. 10.8.3. Nested Tables Wrap-up
    9. 10.9. Temporary Tables
      1. 10.9.1. Temporary Tables Wrap-up
    10. 10.10. Object Tables
      1. 10.10.1. Object Tables Wrap-up
    11. 10.11. Summary
  19. 11. Indexes
    1. 11.1. An Overview of Oracle Indexes
    2. 11.2. B*Tree Indexes
      1. 11.2.1. Index Key Compression
      2. 11.2.2. Reverse Key Indexes
      3. 11.2.3. Descending Indexes
      4. 11.2.4. When Should You Use a B*Tree Index?
        1. 11.2.4.1. Physical Organization
        2. 11.2.4.2. The Clustering Factor
      5. 11.2.5. B*Trees Wrap-up
    3. 11.3. Bitmap Indexes
      1. 11.3.1. When Should You Use a Bitmap Index?
      2. 11.3.2. Bitmap Join Indexes
      3. 11.3.3. Bitmap Indexes Wrap-up
    4. 11.4. Function-Based Indexes
      1. 11.4.1. Important Implementation Details
      2. 11.4.2. A Simple Function-Based Index Example
      3. 11.4.3. Indexing Only Some of the Rows
      4. 11.4.4. Implementing Selective Uniqueness
      5. 11.4.5. Caveat Regarding ORA-01743
      6. 11.4.6. Function-Based Indexes Wrap-up
    5. 11.5. Application Domain Indexes
    6. 11.6. Frequently Asked Questions and Myths About Indexes
      1. 11.6.1. Do Indexes Work on Views?
      2. 11.6.2. Do Nulls and Indexes Work Together?
      3. 11.6.3. Should Foreign Keys Be Indexed?
      4. 11.6.4. Why Isn't My Index Getting Used?
        1. 11.6.4.1. Case 1
        2. 11.6.4.2. Case 2
        3. 11.6.4.3. Case 3
        4. 11.6.4.4. Case 4
        5. 11.6.4.5. Case 5
        6. 11.6.4.6. Case 6
        7. 11.6.4.7. Index Case Summary
      5. 11.6.5. Myth: Space Is Never Reused in an Index
      6. 11.6.6. Myth: Most Discriminating Elements Should Be First
    7. 11.7. Summary
  20. 12. Datatypes
    1. 12.1. An Overview of Oracle Datatypes
    2. 12.2. Character and Binary String Types
      1. 12.2.1. NLS Overview
      2. 12.2.2. Character Strings
        1. 12.2.2.1. Character String Syntax
        2. 12.2.2.2. Bytes or Characters
        3. 12.2.2.3. The "N" Variant
    3. 12.3. Binary Strings: RAW Types
    4. 12.4. Number Types
      1. 12.4.1. NUMBER Type Syntax and Usage
      2. 12.4.2. BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage
      3. 12.4.3. Non-native Number Types
      4. 12.4.4. Performance Considerations
    5. 12.5. Long Types
      1. 12.5.1. Restrictions on LONG and LONG RAW Types
      2. 12.5.2. Coping with Legacy LONG Types
    6. 12.6. Dates, Timestamps, and Interval Types
      1. 12.6.1. Formats
      2. 12.6.2. DATE Type
        1. 12.6.2.1. Adding or Subtracting Time from a DATE
        2. 12.6.2.2. Getting the Difference Between Two DATEs
      3. 12.6.3. TIMESTAMP Type
        1. 12.6.3.1. TIMESTAMP
        2. 12.6.3.2. Adding or Subtracting Time from a TIMESTAMP
        3. 12.6.3.3. Getting the Difference Between Two TIMESTAMPs
        4. 12.6.3.4. TIMESTAMP WITH TIME ZONE Type
        5. 12.6.3.5. TIMESTAMP WITH LOCAL TIME ZONE Type
      4. 12.6.4. INTERVAL Type
        1. 12.6.4.1. INTERVAL YEAR TO MONTH
        2. 12.6.4.2. INTERVAL DAY TO SECOND
    7. 12.7. LOB Types
      1. 12.7.1. Internal LOBs
        1. 12.7.1.1. LOB Tablespace
        2. 12.7.1.2. IN ROW Clause
        3. 12.7.1.3. CHUNK Clause
        4. 12.7.1.4. RETENTION Clause
        5. 12.7.1.5. CACHE Clause
        6. 12.7.1.6. LOB STORAGE Clause
      2. 12.7.2. BFILEs
    8. 12.8. ROWID/UROWID Types
    9. 12.9. Summary
  21. 13. Partitioning
    1. 13.1. Partitioning Overview
      1. 13.1.1. Increased Availability
      2. 13.1.2. Reduced Administrative Burden
      3. 13.1.3. Enhanced Statement Performance
        1. 13.1.3.1. Parallel DML
        2. 13.1.3.2. Query Performance
          1. 13.1.3.2.1. OLTP Systems
          2. 13.1.3.2.2. Data Warehouse Systems
    2. 13.2. Table Partitioning Schemes
      1. 13.2.1. Range Partitioning
      2. 13.2.2. Hash Partitioning
        1. 13.2.2.1. How Hash Partitioning Works
        2. 13.2.2.2. Hash Partition Using Powers of Two
      3. 13.2.3. List Partitioning
      4. 13.2.4. Interval Partitioning
      5. 13.2.5. Reference Partitioning
      6. 13.2.6. Composite Partitioning
      7. 13.2.7. Row Movement
      8. 13.2.8. Table Partitioning Schemes Wrap-up
    3. 13.3. Partitioning Indexes
      1. 13.3.1. Local Indexes vs. Global Indexes
      2. 13.3.2. Local Indexes
        1. 13.3.2.1. Partition Elimination Behavior
        2. 13.3.2.2. Local Indexes and Unique Constraints
      3. 13.3.3. Global Indexes
        1. 13.3.3.1. Data Warehousing and Global Indexes
        2. 13.3.3.2. Sliding Windows and Indexes
        3. 13.3.3.3. "Live" Global Index Maintenance
        4. 13.3.3.4. OLTP and Global Indexes
    4. 13.4. Partitioning and Performance, Revisited
    5. 13.5. Auditing and Segment Space Compression
    6. 13.6. Summary
  22. 14. Parallel Execution
    1. 14.1. When to Use Parallel Execution
      1. 14.1.1. A Parallel Processing Analogy
        1. 14.1.1.1. One-Page Summary
        2. 14.1.1.2. Ten-Chapter Report
    2. 14.2. Oracle Exadata
    3. 14.3. Parallel Query
    4. 14.4. Parallel DML
    5. 14.5. Parallel DDL
      1. 14.5.1. Parallel DDL and Data Loading Using External Tables
      2. 14.5.2. Parallel DDL and Extent Trimming
        1. 14.5.2.1. Extent Trimming and Dictionary-Managed Tablespaces
        2. 14.5.2.2. Extent Trimming and Locally-Managed Tablespaces
        3. 14.5.2.3. Setting Up for Locally-Managed Tablespaces
        4. 14.5.2.4. Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces
    6. 14.6. Parallel Recovery
    7. 14.7. Procedural Parallelism
      1. 14.7.1. Parallel Pipelined Functions
      2. 14.7.2. Do-It-Yourself Parallelism
      3. 14.7.3. Old School Do-It-Yourself Parallelism
    8. 14.8. Summary
  23. 15. Data Loading and Unloading
    1. 15.1. SQL*Loader
      1. 15.1.1. Loading Data with SQLLDR FAQs
        1. 15.1.1.1. Why do I receive "exceeds maximum length" in my log file?
        2. 15.1.1.2. How Do I Load Delimited Data?
        3. 15.1.1.3. How Do I Load Fixed Format Data?
        4. 15.1.1.4. How Do I Load Dates?
        5. 15.1.1.5. How Do I Load Data Using Functions?
        6. 15.1.1.6. How Do I Load Data with Embedded Newlines?
          1. 15.1.1.6.1. Use a Character Other Than a Newline
          2. 15.1.1.6.2. Use the FIX Attribute
          3. 15.1.1.6.3. Use the VAR Attribute
          4. 15.1.1.6.4. Use the STR Attribute
          5. 15.1.1.6.5. Embedded Newlines Wrap-up
        7. 15.1.1.7. How Do I Load LOBs?
          1. 15.1.1.7.1. Loading a LOB via PL/SQL
          2. 15.1.1.7.2. Loading LOB Data via SQLLDR
        8. 15.1.1.8. How Do I Call SQLLDR from a Stored Procedure?
      2. 15.1.2. SQLLDR Caveats
        1. 15.1.2.1. TRUNCATE Appears to Work Differently
        2. 15.1.2.2. SQLLDR Defaults to CHAR(255)
        3. 15.1.2.3. Command Line Overrides Control File
      3. 15.1.3. SQLLDR Summary
    2. 15.2. External Tables
      1. 15.2.1. Setting Up External Tables
      2. 15.2.2. Dealing with Errors
      3. 15.2.3. Using an External Table to Load Different Files
      4. 15.2.4. Multiuser Issues
      5. 15.2.5. External Tables Summary
    3. 15.3. Flat File Unload
    4. 15.4. Data Pump Unload
    5. 15.5. Summary
  24. 16. Data Encryption
    1. 16.1. Types of Encryption
      1. 16.1.1. Data in Motion
      2. 16.1.2. Data at Rest
      3. 16.1.3. Manual Application Encryption
      4. 16.1.4. The Oracle Wallet
        1. 16.1.4.1. Understanding the Wallet
        2. 16.1.4.2. Setting up the Oracle Wallet
      5. 16.1.5. Transparent Column Level Encryption
      6. 16.1.6. Transparent Tablespace Encryption
    2. 16.2. What Encryption Is Not About
    3. 16.3. Implementing Manual Application Encryption
      1. 16.3.1. Reasons to Avoid the Manual Approach
      2. 16.3.2. Performance Implications of the Manual Approach
      3. 16.3.3. When to Use the Manual Approach
    4. 16.4. Implementing Column Level Encryption
      1. 16.4.1. How to Use Column Encryption
      2. 16.4.2. Data Storage with Column Encryption
        1. 16.4.2.1. Increased Storage with Column Encryption
        2. 16.4.2.2. Storage in the SGA
      3. 16.4.3. Measuring the Performance Impact of Column Encryption
      4. 16.4.4. Influences on the Magnitude
        1. 16.4.4.1. Understanding What You Measure
        2. 16.4.4.2. Impact of Encryption
        3. 16.4.4.3. Cost of Data Retrieval
        4. 16.4.4.4. Statistics on Encrypted Columns
      5. 16.4.5. Limitations of Column Encryption
    5. 16.5. Implementing Tablespace Encryption
      1. 16.5.1. How to Use Tablespace Encryption
      2. 16.5.2. Data Storage with Tablespace Encryption
        1. 16.5.2.1. Storage on Disk
        2. 16.5.2.2. Storage in the SGA
      3. 16.5.3. Measuring the Performance Impact of Tablespace Encryption
    6. 16.6. Deciding on an Encryption Technique
    7. 16.7. Summary

Product information

  • Title: Expert Oracle Database Architecture: Oracle Database 9
  • Author(s):
  • Release date: July 2010
  • Publisher(s): Apress
  • ISBN: 9781430229469