Oracle PL/SQL for DBAs

Spis treści książki

• Oracle PL/SQL for DBAs
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Preface
    • PL/SQL for DBAs
      • Securing the Database
      • Optimizing Performance
      • Fully Leveraging Oracle Utilities and Features
      • Mentoring New Developers and DBAs
    • About This Book
    • Conventions Used in This Book
    • About PL/SQL Versions
    • Resources for Developing PL/SQL Expertise
      • The OReilly PL/SQL Series
      • PL/SQL on the Internet
    • About the Code
    • Using Code Examples
    • Comments and Questions
    • Safari Enabled
    • Acknowledgments
  • 1. Introduction to PL/SQL
    • 1.1. What Is PL/SQL?
    • 1.2. Basic PL/SQL Syntax Elements
      • 1.2.1. PL/SQL Block Structure
        • 1.2.1.1. Sections of the block
        • 1.2.1.2. Anonymous blocks
      • 1.2.2. The PL/SQL Character Set
      • 1.2.3. Identifiers
        • 1.2.3.1. NULLs
      • 1.2.4. Literals
        • 1.2.4.1. String literals
        • 1.2.4.2. Numeric literals
        • 1.2.4.3. Boolean literals
      • 1.2.5. The Semicolon Delimiter
      • 1.2.6. Comments
        • 1.2.6.1. Single-line comment syntax
        • 1.2.6.2. Multi-line comment syntax
    • 1.3. Program Data
      • 1.3.1. Types of PL/SQL Datatypes
        • 1.3.1.1. Character data
        • 1.3.1.2. Numbers
        • 1.3.1.3. Dates, timestamps, and intervals
        • 1.3.1.4. Booleans
        • 1.3.1.5. Binary data
        • 1.3.1.6. ROWIDs
        • 1.3.1.7. REF CURSOR datatype
        • 1.3.1.8. Internet datatypes
        • 1.3.1.9. "Any" datatypes
      • 1.3.2. Declaring Program Data
        • 1.3.2.1. Declaring a variable
        • 1.3.2.2. Declaring constants
        • 1.3.2.3. Anchored declarations
    • 1.4. Control Statements
      • 1.4.1. IF Statements
      • 1.4.2. CASE Statements and Expressions
        • 1.4.2.1. Simple CASE statement
        • 1.4.2.2. Searched CASE statement
    • 1.5. Loops in PL/SQL
      • 1.5.1. Simple Loop
      • 1.5.2. FOR Loop
      • 1.5.3. WHILE Loop
    • 1.6. Exception Handling
      • 1.6.1. Defining Exceptions
      • 1.6.2. Raising Exceptions
        • 1.6.2.1. RAISE statement
        • 1.6.2.2. Using RAISE_APPLICATION_ERROR
      • 1.6.3. Handling Exceptions
        • 1.6.3.1. Built-in error functions
        • 1.6.3.2. Unhandled exceptions
        • 1.6.3.3. Propagation of an unhandled exception
    • 1.7. Records
      • 1.7.1. Declaring Records
      • 1.7.2. Working with Records
        • 1.7.2.1. Record-level operations
        • 1.7.2.2. Field-level operations
    • 1.8. Collections
      • 1.8.1. Types of Collections
      • 1.8.2. Working with Collections
        • 1.8.2.1. Using an associative array
        • 1.8.2.2. Using a nested table
        • 1.8.2.3. Using a VARRAY
      • 1.8.3. Collection Methods (Built-ins)
    • 1.9. Procedures, Functions, and Packages
      • 1.9.1. Procedures
        • 1.9.1.1. Structure of a procedure
        • 1.9.1.2. Calling a procedure
      • 1.9.2. Functions
        • 1.9.2.1. Structure of a function
        • 1.9.2.2. Calling a function
      • 1.9.3. Parameters
        • 1.9.3.1. Defining parameters
        • 1.9.3.2. Actual and formal parameters
        • 1.9.3.3. Parameter modes
      • 1.9.4. Packages
        • 1.9.4.1. Rules for building packages
        • 1.9.4.2. Rules for calling packaged elements
        • 1.9.4.3. Package data
    • 1.10. Querying Data
      • 1.10.1. Typical Query Operations
      • 1.10.2. Cursor Attributes
      • 1.10.3. Implicit Cursors
        • 1.10.3.1. Error handling with implicit cursors
        • 1.10.3.2. Implicit SQL cursor attributes
      • 1.10.4. Explicit Cursors
      • 1.10.5. BULK COLLECT
        • 1.10.5.1. Limiting rows retrieved with BULK COLLECT
      • 1.10.6. Cursor Variables and REF Cursors
        • 1.10.6.1. Declaring REF CURSOR types
        • 1.10.6.2. Declaring cursor variables
        • 1.10.6.3. Opening cursor variables
        • 1.10.6.4. Fetching from cursor variables
    • 1.11. Changing Data
      • 1.11.1. The INSERT Statement
      • 1.11.2. The UPDATE Statement
      • 1.11.3. The DELETE Statement
      • 1.11.4. Cursor Attributes for DML Operations
      • 1.11.5. DML and Exception Handling
      • 1.11.6. Bulk DML with the FORALL Statement
        • 1.11.6.1. Syntax of the FORALL Statement
        • 1.11.6.2. FORALL Examples
    • 1.12. Managing Transactions in PL/SQL
      • 1.12.1. The COMMIT Statement
      • 1.12.2. The ROLLBACK Statement
      • 1.12.3. Autonomous Transactions
    • 1.13. Database Triggers
      • 1.13.1. DML Triggers
        • 1.13.1.1. Transaction participation
        • 1.13.1.2. Creating a DML trigger
        • 1.13.1.3. The WHEN clause
        • 1.13.1.4. Working with NEW and OLD pseudo -records
        • 1.13.1.5. Determining the DML action within a trigger
      • 1.13.2. DDL Triggers
        • 1.13.2.1. Creating a DDL Trigger
      • 1.13.3. Database Event Triggers
        • 1.13.3.1. Creating a database event trigger
    • 1.14. Dynamic SQL and Dynamic PL/SQL
      • 1.14.1. The EXECUTE IMMEDIATE Statement
      • 1.14.2. The OPEN FOR Statement
      • 1.14.3. Dynamic PL/SQL
    • 1.15. Conclusion: From Fundamentals to Applying PL/SQL
  • 2. Cursors
    • 2.1. Reusing Cursors
      • 2.1.1. Hard Parsing and Soft Parsing
      • 2.1.2. Planning Cursor Use
      • 2.1.3. How Oracle Decides To Share
      • 2.1.4. PL/SQL Cursor Reformatting
      • 2.1.5. Literal Values
      • 2.1.6. Matching Algorithms
      • 2.1.7. Text Matching Might Not Be Enough
    • 2.2. Using Explicit Cursors Versus Implicit Cursors
      • 2.2.1. What's the Difference?
      • 2.2.2. Cursor Attributes
      • 2.2.3. Cursor Parameters
      • 2.2.4. Mixing but not Matching
    • 2.3. Soft-Closing Cursors
      • 2.3.1. Explicit and Implicit Open Cursors
      • 2.3.2. Native Dynamic SQL
    • 2.4. Using Cursors for More Than Queries
      • 2.4.1. Bulking Up
      • 2.4.2. REF Cursors
        • 2.4.2.1. Strong vs. weak REF cursors
        • 2.4.2.2. REF cursor attributes
        • 2.4.2.3. Dynamic data access
      • 2.4.3. Cursor Parameters
      • 2.4.4. Cursor Expressions
    • 2.5. Conclusion
  • 3. Table Functions
    • 3.1. Why Table Functions?
      • 3.1.1. A Simple Example
      • 3.1.2. Calling a Table Function
      • 3.1.3. Defining the Result Set Structure
    • 3.2. Cursors, Pipelining, and Nesting
      • 3.2.1. Cursors
      • 3.2.2. Pipelined Table Functions
      • 3.2.3. Nested Table Functions
    • 3.3. Parallelizing Table Functions
      • 3.3.1. Taking Advantage of Parallel Query
      • 3.3.2. Distributing Records
        • 3.3.2.1. Random partitioning (PARTITION BY ANY)
        • 3.3.2.2. Range partitioning (PARTITION BY RANGE)
        • 3.3.2.3. Order streaming (ORDER)
        • 3.3.2.4. Hash partitioning (PARTITION BY HASH)
        • 3.3.2.5. Cluster streaming (CLUSTER)
      • 3.3.3. Which Options Should You Choose?
      • 3.3.4. What Does Oracle Do?
      • 3.3.5. Number of PQ Servers to Use
    • 3.4. Using Table Functions
      • 3.4.1. The Function Header
      • 3.4.2. The Basic Loop
      • 3.4.3. Bulk Fetching the Criteria
      • 3.4.4. Identifying Potential Repeats
      • 3.4.5. Finding Genuine Repeats
      • 3.4.6. The Final Function
      • 3.4.7. Running the Function
      • 3.4.8. The Totaling Function
    • 3.5. Table Function Examples
      • 3.5.1. Tracing
      • 3.5.2. Establishing Time Limits
      • 3.5.3. Enabling Nested Cursors
    • 3.6. Tips for Working with Table Functions
      • 3.6.1. The Case Against SYS_REFCURSOR
      • 3.6.2. REF Cursors and Nesting
      • 3.6.3. Applying Criteria
      • 3.6.4. Standardizing Object and Collection Names
      • 3.6.5. Beware of Unhandled Exceptions
      • 3.6.6. Passing Objects Instead Of Cursors
      • 3.6.7. Not Read Committed
    • 3.7. Conclusion
  • 4. Data Encryption and Hashing
    • 4.1. Introduction to Encryption
      • 4.1.1. Encryption Components
      • 4.1.2. The Effects of Key Length
      • 4.1.3. Symmetric Encryption Versus Asymmetric Encryption
      • 4.1.4. Encryption Algorithms
      • 4.1.5. Padding and Chaining
    • 4.2. Encryption in Oracle9i Database
      • 4.2.1. Encrypting Data
      • 4.2.2. Specifying an Initialization Vector
      • 4.2.3. Decrypting Data
      • 4.2.4. Encrypting RAW Data
      • 4.2.5. Performing Multi-Pass Encryption
      • 4.2.6. Putting It Together
      • 4.2.7. Generating Keys
        • 4.2.7.1. Using DES3GETKEY
        • 4.2.7.2. Using the key in encryption
      • 4.2.8. A Practical Encryption Example
      • 4.2.9. Storing the Keys
    • 4.3. Encryption in Oracle Database 10g
      • 4.3.1. Differences Between DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT
      • 4.3.2. Generating Keys
      • 4.3.3. Encrypting Data
        • 4.3.3.1. Specifying the encryption type
        • 4.3.3.2. Specifying chaining
        • 4.3.3.3. Specifying padding
        • 4.3.3.4. Combining options in the typ parameter
        • 4.3.3.5. Handling and converting RAW data
        • 4.3.3.6. Specifying the encryption algorithm
        • 4.3.3.7. Putting it together
      • 4.3.4. Decrypting Data
    • 4.4. Key Management in Oracle Database 10g
      • 4.4.1. Using a Single Key
      • 4.4.2. Using a Key for Each Row
      • 4.4.3. Using a Combined Approach
    • 4.5. Transparent Data Encryption in Oracle Database 10g Release 2
      • 4.5.1. Setting Up TDE
      • 4.5.2. Adding TDE to Existing Tables
      • 4.5.3. Performing TDE Key and Password Management
      • 4.5.4. Adding Salt
    • 4.6. Cryptographic Hashing
      • 4.6.1. The Case of the Suspicious Sandwich
      • 4.6.2. MD5 Hashing in Oracle9i Database
      • 4.6.3. SHA-1 Hashing in Oracle Database 10g
      • 4.6.4. Other Uses of Hashing
      • 4.6.5. Message Authentication Code in Oracle Database 10g
    • 4.7. Building a Practical Encryption System
    • 4.8. Conclusion
  • 5. Row-Level Security
    • 5.1. Introduction to RLS
      • 5.1.1. Why Learn About RLS?
      • 5.1.2. A Simple Example
    • 5.2. Using RLS
      • 5.2.1. Performing an Update Check
      • 5.2.2. Static RLS Policies
        • 5.2.2.1. Problems with static policies
        • 5.2.2.2. Using a pragma
      • 5.2.3. Defining a Dynamic Policy
      • 5.2.4. Improving Performance
      • 5.2.5. Controlling the Type of Table Access
    • 5.3. RLS in Oracle Database 10g
      • 5.3.1. Column-Sensitive RLS
      • 5.3.2. Other Classes of Dynamism
        • 5.3.2.1. Shared static policy
        • 5.3.2.2. Context-sensitive policy
        • 5.3.2.3. Shared context-sensitive policy
    • 5.4. Troubleshooting RLS
      • 5.4.1. Interpreting Errors
      • 5.4.2. Direct-Path Operations
      • 5.4.3. Checking the Query Rewrite
        • 5.4.3.1. Data dictionary view
        • 5.4.3.2. Event-based tracing
    • 5.5. RLS Interactions with Other Oracle Features
    • 5.6. Application Contexts
      • 5.6.1. A Simple Example
      • 5.6.2. The Security of Application Contexts
      • 5.6.3. Contexts as Predicates in RLS
      • 5.6.4. Identifying Non-Database Users
    • 5.7. Conclusion
  • 6. Fine-Grained Auditing
    • 6.1. Introduction to FGA
      • 6.1.1. What Is Auditing?
      • 6.1.2. Why Learn About FGA?
      • 6.1.3. A Simple Example
      • 6.1.4. Auditing Differences Between Oracle Database Versions
      • 6.1.5. What Else Does FGA Capture?
      • 6.1.6. Using FGA with Flashback Query
    • 6.2. Customizing FGA
      • 6.2.1. Specifying Audit Columns
      • 6.2.2. Specifying Audit Conditions
      • 6.2.3. Recording Bind Variables
        • 6.2.3.1. Turning off bind variable capture
      • 6.2.4. Specifying a Handler Module
        • 6.2.4.1. Drawbacks with the default FGA approach
        • 6.2.4.2. Creating a user-defined audit facility
    • 6.3. Administering FGA
      • 6.3.1. The DBA_AUDIT_POLICIES View
      • 6.3.2. Using DBMS_FGA Procedures
        • 6.3.2.1. The ADD_POLICY procedure
        • 6.3.2.2. The DROP_POLICY procedure
        • 6.3.2.3. The DISABLE_POLICY procedure
        • 6.3.2.4. The ENABLE_POLICY procedure
    • 6.4. FGA in Oracle Database 10g
      • 6.4.1. Additional DML Statements
      • 6.4.2. Additional Data Dictionary Views and Columns
        • 6.4.2.1. The DBA_FGA_AUDIT_TRAIL view
        • 6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
      • 6.4.3. Combination of Columns
    • 6.5. FGA and Other Oracle Auditing Techniques
      • 6.5.1. FGA Compared with Triggers
        • 6.5.1.1. The case for FGA
        • 6.5.1.2. The case for triggers
      • 6.5.2. FGA Compared with Regular Auditing
    • 6.6. The Challenge of Non-Database Users
      • 6.6.1. Client Identifier
      • 6.6.2. Application Contexts
    • 6.7. Troubleshooting FGA
    • 6.8. Conclusion
  • 7. Generating Random Values
    • 7.1. Generating Random Numbers
      • 7.1.1. Generating Positive Numbers
        • 7.1.1.1. Controlling the precision
        • 7.1.1.2. Controlling the range
      • 7.1.2. Generating Negative Numbers
        • 7.1.2.1. The RANDOM function
      • 7.1.3. Seeding Random Number Generation
    • 7.2. Generating Characters
    • 7.3. Checking for Randomness
    • 7.4. Following Statistical Patterns
      • 7.4.1. Generating Strings
      • 7.4.2. Generating Random Values with NULLs
      • 7.4.3. Generating Random Strings of Random Length
      • 7.4.4. Putting It All Together
    • 7.5. Conclusion
  • 8. Scheduling
    • 8.1. Why Schedule Jobs from Within Oracle?
    • 8.2. Managing Jobs
      • 8.2.1. A Simple Example
        • 8.2.1.1. Running OS executables and anonymous blocks
        • 8.2.1.2. DBA_SCHEDULER_JOBS view
      • 8.2.2. Simple Job Management
        • 8.2.2.1. Enabling and disabling jobs
        • 8.2.2.2. Stopping running jobs
        • 8.2.2.3. Running a job
        • 8.2.2.4. Dropping a job
    • 8.3. Managing the Calendar and Schedule
      • 8.3.1. Calendar Strings
        • 8.3.1.1. Examples of calendar strings
        • 8.3.1.2. Determining future calendar strings
      • 8.3.2. Named Schedules
      • 8.3.3. Owner of the Schedule
    • 8.4. Managing Named Programs
      • 8.4.1. Creating a Program
      • 8.4.2. Running Other Users' Programs
    • 8.5. Managing Priorities
      • 8.5.1. Using the Resource Manager
      • 8.5.2. Job Class
    • 8.6. Managing Windows
      • 8.6.1. Creating a Window
      • 8.6.2. Prioritizing Windows
      • 8.6.3. Specifying an End Date for Windows
      • 8.6.4. Getting Information About Windows
      • 8.6.5. Dropping Windows
      • 8.6.6. Disabling and Enabling Windows
      • 8.6.7. Forcing Windows Open and Closed
      • 8.6.8. Window Groups
    • 8.7. Managing Logging
      • 8.7.1. Job Logs
        • 8.7.1.1. DBA_SCHEDULER_JOB_LOG
        • 8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS
        • 8.7.1.3. Pruning the job log
        • 8.7.1.4. Log levels
        • 8.7.1.5. Setting the retention period
      • 8.7.2. Window Logs
        • 8.7.2.1. DBA_SCHEDULER_WINDOW_LOG
        • 8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS
    • 8.8. Managing Attributes
      • 8.8.1. Jobs
      • 8.8.2. Job Classes
      • 8.8.3. Schedules
      • 8.8.4. Programs
      • 8.8.5. Windows
      • 8.8.6. Window Groups
    • 8.9. Conclusion
  • A. Quick Reference
    • A.1. DBMS_OBFUSCATION_TOOLKIT
      • DES3GETKEY
      • DESGETKEY
      • DES3ENCRYPT
      • DESENCRYPT
      • DES3DECRYPT
      • DESDECRYPT
      • MD5
    • A.2. DBMS_CRYPTO
      • GETRANDOMBYTES
      • ENCRYPT
      • DECRYPT
      • HASH
      • MAC
    • A.3. DBMS_RLS
      • Reference Section
        • ADD_POLICY
      • Reference Section
        • DROP_POLICY
      • Reference Section
        • ENABLE_POLICY
      • Reference Section
        • REFRESH_POLICY
      • A.3.1. RLS Data Dictionary Views
        • DBA_POLICIES
    • A.4. DBMS_FGA
      • Reference Section
        • ADD_POLICY
      • Reference Section
        • DROP_POLICY
      • Reference Section
        • DISABLE_POLICY
      • Reference Section
        • ENABLE_POLICY
      • A.4.1. FGA Data Dictionary Views
        • DBA_AUDIT_POLICIES
        • DBA_FGA_AUDIT_TRAIL
        • FLASHBACK_TRANSACTION_QUERY
    • A.5. DBMS_RANDOM
      • SEED
      • VALUE
      • STRING
      • NORMAL
    • A.6. DBMS_SCHEDULER
      • Reference Section
        • CREATE_JOB
      • Reference Section
        • CREATE_JOB_CLASS
      • Reference Section
        • STOP_JOB
      • Reference Section
        • RUN_JOB
      • Reference Section
        • COPY_JOB
      • Reference Section
        • DISABLE
      • Reference Section
        • ENABLE
      • Reference Section
        • DROP_JOB
      • Reference Section
        • DROP_JOB_CLASS
      • Reference Section
        • CREATE_SCHEDULE
      • Reference Section
        • DROP_SCHEDULE
      • Reference Section
        • CREATE_WINDOW
      • Reference Section
        • CREATE_WINDOW_GROUP
      • Reference Section
        • ADD_WINDOW_GROUP_MEMBER
      • Reference Section
        • DROP_WINDOW
      • Reference Section
        • OPEN_WINDOW
      • Reference Section
        • CLOSE_WINDOW
      • A.6.1. Scheduler Data Dictionary Views
        • DBA_SCHEDULER_JOBS
        • DBA_SCHEDULER_WINDOWS
        • DBA_SCHEDULER_SCHEDULES
        • DBA_SCHEDULER_PROGRAMS
        • DBA_SCHEDULER_JOB_CLASSES
        • DBA_SCHEDULER_WINDOW_GROUPS
        • DBA_SCHEDULER_WINGROUP_MEMBERS
        • DBA_SCHEDULER_JOB_LOG
        • DBA_SCHEDULER_JOB_RUN_DETAILS
        • DBA_SCHEDULER_RUNNING_JOBS
  • About the Authors
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje