Oracle Database 12c The Complete Reference

Book description

Master the Cutting-Edge Features of Oracle Database 12c

Maintain a scalable, highly available enterprise platform and reduce complexity by leveraging the powerful new tools and cloud enhancements of Oracle Database 12c. This authoritative Oracle Press guide offers complete coverage of installation, configuration, tuning, and administration. Find out how to build and populate Oracle databases, perform effective queries, design applications, and secure your enterprise data. Oracle Database 12c: The Complete Reference also contains a comprehensive appendix covering commands, keywords, features, and functions.

  • Set up Oracle Database 12c or upgrade from an earlier version
  • Design Oracle databases and plan for application implementation
  • Construct SQL and SQL*Plus statements and execute powerful queries
  • Secure data with roles, privileges, virtualization, and encryption
  • Move data with SQL*Loader and Oracle Data Pump
  • Restore databases using flashback and the Oracle Database Automatic Undo Management feature
  • Build and deploy PL/SQL triggers, procedures, and packages
  • Work with Oracle pluggable and container databases
  • Develop database applications using Java, JDBC, and XML
  • Optimize performance with Oracle Real Application Clusters

Table of contents

  1. Cover
  2. Oracle Database 12c: The Complete Reference
  3. Copyright Page
  4. About the Authors
  5. Dedication
  6. Contents at a Contents
  7. Contents
  8. Acknowledgments
  9. PART I Critical Database Concepts
    1. 1 Oracle Database 12c Architecture Options
      1. Databases and Instances
      2. Inside the Database
        1. Storing the Data
        2. Guarding the Data
        3. Programmatic Structures
      3. Choosing Architectures and Options
    2. 2 Installing Oracle Database 12c and Creating a Database
      1. Overview of Licensing and Installation Options
      2. Installing the Oracle Software
        1. Installing the Grid Infrastructure
        2. Installing the Database
    3. 3 Upgrading to Oracle Database 12c
      1. Choosing an Upgrade Method
      2. Before Upgrading
      3. Upgrading the Grid Infrastructure and Database
        1. Grid Infrastructure Upgrade
        2. Database Upgrade
      4. After Upgrading
    4. 4 Planning Oracle Applications—Approaches, Risks, and Standards
      1. The Cooperative Approach
      2. Everyone Has “Data”
      3. The Familiar Language of Oracle
        1. Tables of Information
        2. Structured Query Language
        3. A Simple Oracle Query
        4. Why It Is Called “Relational”
      4. Some Common, Everyday Examples
      5. What Are the Risks?
      6. The Importance of the New Vision
        1. Changing Environments
        2. Codes, Abbreviations, and Naming Standards
      7. How to Reduce the Confusion
        1. Normalization
        2. English Names for Tables and Columns
        3. English Words for the Data
      8. Capitalization in Names and Data
      9. Normalizing Names
      10. Good Design Has a Human Touch
        1. Understanding the Application Tasks
        2. Outline of Tasks
      11. Understanding the Data
        1. The Atomic Data Models
        2. The Atomic Business Model
        3. The Business Model
        4. Data Entry
        5. Query and Reporting
      12. Toward Object Name Normalization
        1. Level-Name Integrity
        2. Foreign Keys
        3. Singular Names
        4. Brevity
        5. Object Name Thesaurus
      13. Intelligent Keys and Column Values
      14. The Commandments
  10. PART II SQL and SQL*Plus
    1. 5 The Basic Parts of Speech in SQL
      1. Style
      2. Creating the NEWSPAPER Table
      3. Using SQL to Select Data from Tables
      4. SELECT, FROM, WHERE, and ORDER BY
      5. Logic and Value
        1. Single-Value Tests
        2. LIKE
        3. Simple Tests Against a List of Values
        4. Combining Logic
      6. Another Use for WHERE: Subqueries
        1. Single Values from a Subquery
        2. Lists of Values from a Subquery
      7. Combining Tables
      8. Creating a View
      9. Expanding the View
    2. 6 Basic SQL*Plus Reports and Commands
      1. Building a Simple Report
        1. Remark
        2. SET HEADSEP
        3. TTITLE and BTITLE
        4. COLUMN
        5. BREAK ON
        6. COMPUTE AVG
        7. SET LINESIZE
        8. SET PAGESIZE
        9. SET NEWPAGE
        10. SPOOL
        11. /* */
        12. Some Clarification on Column Headings
      2. Other Features
        1. Command Line Editor
        2. SET PAUSE
        3. SAVE
        4. Store
        5. Editing
        6. HOST
        7. Adding SQL*Plus Commands
        8. START
      3. Checking the SQL*Plus Environment
      4. Building Blocks
    3. 7 Getting Text Information and Changing It
      1. Datatypes
      2. What Is a String?
      3. Notation
      4. Concatenation ( || )
      5. How to Cut and Paste Strings
        1. RPAD AND LPAD
        2. LTRIM, RTRIM, and TRIM
        3. Combining Two Functions
        4. Using the TRIM Function
        5. Adding One More Function
        6. LOWER, UPPER, and INITCAP
        7. LENGTH
        8. SUBSTR
        9. INSTR
        10. ASCII and CHR
      6. Using ORDER BY and WHERE with String Functions
        1. Soundex
        2. National Language Support
        3. Regular Expression Support
      7. Review
    4. 8 Searching for Regular Expressions
      1. Search Strings
      2. REGEXP_SUBSTR
        1. Regexp_Instr
        2. REGEXP_LIKE
        3. REPLACE and REGEXP_REPLACE
        4. REGEXP_COUNT
    5. 9 Playing the Numbers
      1. The Three Classes of Number Functions
      2. Notation
      3. Single-Value Functions
        1. Addition (+), Subtraction (–), Multiplication (*), and Division (/)
        2. NULL
        3. NVL: NULL-Value Substitution
        4. ABS: Absolute Value
        5. CEIL
        6. FLOOR
        7. MOD
        8. POWER
        9. SQRT: Square Root
        10. EXP, LN, and LOG
        11. ROUND and TRUNC
        12. SIGN
        13. SIN, SINH, COS, COSH, TAN, TANH, ACOS, ATAN, ATAN2, and ASIN
      4. Aggregate Functions
        1. Nulls in Group-Value Functions
        2. Examples of Single- and Group-Value Functions
        3. DISTINCT in Group Functions
      5. List Functions
      6. Finding Rows with MAX or MIN
      7. Precedence and Parentheses
      8. Review
    6. 10 Dates: Then, Now, and the Difference
      1. Date Arithmetic
      2. SYSDATE, CURRENT_DATE, and SYSTIMESTAMP
        1. The Difference Between Two Dates
        2. Adding Months
        3. Subtracting Months
        4. GREATEST and LEAST
        5. NEXT_DAY
        6. LAST_DAY
        7. MONTHS_BETWEEN Two Dates
        8. Combining Date Functions
      3. ROUND and TRUNC in Date Calculations
      4. TO_DATE and TO_CHAR Formatting
        1. The Most Common To_Char Error
        2. NEW_TIME: Switching Time Zones
        3. TO_DATE Calculations
      5. Dates in WHERE Clauses
      6. Dealing with Multiple Centuries
      7. Using the EXTRACT Function
      8. Using the TIMESTAMP Datatypes
    7. 11 Conversion and Transformation Functions
      1. Elementary Conversion Functions
        1. Automatic Conversion of Datatypes
        2. A Warning About Automatic Conversion
      2. Specialized Conversion Functions
      3. Transformation Functions
        1. Translate
        2. DECODE
      4. Review
    8. 12 Grouping Things Together
      1. Using Group by and Having
        1. Adding an ORDER BY
        2. Order of Execution
      2. Views of Groups
      3. Renaming Columns with Aliases
      4. The Power of Views of Groups
        1. Using Order BY in Views
        2. Logic in the HAVING Clause
        3. Using ORDER BY with Columns and Group Functions
        4. Joining Columns
      5. More Grouping Possibilities
    9. 13 When One Query Depends on Another
      1. Advanced Subqueries
        1. Correlated Subqueries
        2. Coordinating Logical Tests
        3. Using EXISTS and Its Correlated Subquery
      2. Outer Joins
        1. Pre-Oracle9i Syntax for Outer Joins
        2. Current Syntax for Outer Joins
        3. Replacing NOT IN with an Outer Join
        4. Replacing NOT IN with NOT EXISTS
      3. Natural and Inner Joins
      4. UNION, INTERSECT, and MINUS
        1. IN Subqueries
        2. Restrictions on UNION, INTERSECT, and MINUS
    10. 14 Some Complex Possibilities
      1. Complex Groupings
      2. Using Temporary Tables
      3. Using ROLLUP, GROUPING, and CUBE
      4. Family Trees and CONNECT BY
        1. Excluding Individuals and Branches
        2. Traveling Toward the Roots
        3. Finding the Leaves
        4. The Basic Rules
    11. 15 Changing Data: INSERT, DELETE, UPDATE and MERGE
      1. Insert
        1. Inserting a Time
        2. INSERT with SELECT
        3. Using the APPEND Hint to Improve INSERT Performance
      2. ROLLBACK, COMMIT, and AUTOCOMMIT
        1. Using Savepoints
        2. Implicit COMMIT
        3. Auto ROLLBACK
      3. Multitable INSERTs
      4. DELETE
      5. UPDATE
        1. Update with Embedded Select
        2. UPDATE with NULL
      6. MERGE
      7. Handling Errors
    12. 16 DECODE and CASE: IF, THEN, and ELSE in SQL
      1. If, Then, Else
      2. Replacing Values via DECODE
      3. DECODE Within DECODE
      4. Greater Than and Less Than in DECODE
      5. Using CASE
      6. Using PIVOT
    13. 17 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences
      1. Creating a Table
        1. Character Width and Number Precision
        2. Rounding During Insertion
        3. Defining Constraints within CREATE TABLE
        4. Designating Index Tablespaces
        5. Naming Constraints
      2. Dropping Tables
      3. Altering Tables
        1. The Rules for Adding or Modifying a Column
        2. Creating Read-Only Tables
        3. Altering Actively Used Tables
        4. Creating Virtual Columns
        5. Dropping a Column
      4. Creating a Table from a Table
      5. Creating an Index-Organized Table
      6. Creating a View
        1. View Stability
        2. Using ORDER BY in Views
        3. Creating a Read-Only View
      7. Indexes
        1. Creating an Index
        2. Enforcing Uniqueness
        3. Creating a Unique Index
        4. Creating a Bitmap Index
        5. When to Create an Index
        6. Creating Invisible Indexes
        7. Variety in Indexed Columns
        8. How Many Indexes to Use on a Table
        9. Placing an Index in the Database
        10. Rebuilding an Index
        11. Function-Based Indexes
      8. Clusters
      9. Sequences
    14. 18 Partitioning
      1. Creating a Partitioned Table
      2. List Partitioning
      3. Creating Subpartitions
      4. Creating Reference and Interval Partitions
        1. Reference Partitioning
        2. Interval Partitioning
      5. Indexing Partitions
      6. Managing Partitioned Tables
    15. 19 Basic Oracle Security
      1. Users, Roles, and Privileges
        1. Creating a User
        2. Password Management
        3. Standard Roles
        4. Format for the GRANT Command
        5. Revoking Privileges
      2. What Users Can Grant
        1. Moving to Another User with Connect
        2. CREATE SYNONYM
        3. Using Ungranted Privileges
        4. Passing Privileges
        5. Creating a Role
        6. Granting Privileges to a Role
        7. Granting a Role to Another Role
        8. Granting a Role to Users
        9. Adding a Password to a Role
        10. Removing a Password from a Role
        11. Enabling and Disabling Roles
        12. Revoking Privileges from a Role
        13. Dropping a Role
        14. Granting UPDATE to Specific Columns
        15. Revoking Object Privileges
        16. Defining Security by User
        17. Granting Access to the Public
      3. Granting Access to Limited Resources
  11. PART III Beyond the Basics
    1. 20 Advanced Security—Virtual Private Databases
      1. Initial Configuration
      2. Create an Application Context
      3. Create a Logon Trigger
      4. Create a Security Policy
      5. Apply the Security Policy to Tables
      6. Test the VPD Security Policy
      7. How to Implement Column-Level VPD
      8. How to Disable VPD
      9. How to Use Policy Groups
    2. 21 Advanced Security—Transparent Data Encryption
      1. Transparent Data Encryption for Columns
        1. Setting Up TDE for Columns
        2. Additional Setup for RAC Databases
        3. Opening and Closing the Wallet
        4. Encrypting and Decrypting Columns
      2. Transparent Data Encryption for Tablespaces
        1. Setting Up TDE on Tablespaces
        2. Creating an Encrypted Tablespace
    3. 22 Working with Tablespaces
      1. Tablespaces and the Structure of the Database
        1. Tablespace Contents
        2. RECYCLEBIN Space in Tablespaces
        3. Read-Only Tablespaces
        4. NOLOGGING Tablespaces
        5. Temporary Tablespaces
        6. Tablespaces for System-Managed Undo
        7. BIGFILE Tablespaces
        8. Encrypted Tablespaces
        9. Supporting the FLASHBACK DATABASE Command
        10. Transporting Tablespaces
      2. Planning Your Tablespace Usage
        1. Separate Active and Static Tables
        2. Separate Indexes and Tables
        3. Separate Large and Small Objects
        4. Separate Application Tables from Core Objects
    4. 23 Using SQL*Loader to Load Data
      1. The Control File
        1. Loading Variable-Length Data
      2. Starting the Load
      3. Logical and Physical Records
      4. Control File Syntax Notes
      5. Managing Data Loads
      6. Repeating Data Loads
      7. Tuning Data Loads
      8. Direct Path Loading
      9. SQL*Loader Express Mode
      10. Additional Features
    5. 24 Using Data Pump Export and Import
      1. Creating a Directory
      2. Data Pump Export Options
      3. Starting a Data Pump Export Job
        1. Stopping and Restarting Running Jobs
        2. Exporting from Another Database
        3. Using EXCLUDE, INCLUDE, and QUERY
      4. Data Pump Import Options
      5. Starting a Data Pump Import Job
        1. Stopping and Restarting Running Jobs
        2. EXCLUDE, INCLUDE, and QUERY
        3. Generating SQL
    6. 25 Accessing Remote Data
      1. Database Links
        1. How a Database Link Works
        2. Using a Database Link for Remote Queries
        3. Using a Database Link for Synonyms and Views
        4. Using a Database Link for Remote Updates
        5. Syntax for Database Links
      2. Using Synonyms for Location Transparency
      3. Using the USER Pseudo-Column in Views
    7. 26 Using Materialized Views
      1. Functionality
      2. Required System Privileges
      3. Required Table Privileges
      4. Read-Only vs. Updatable
      5. CREATE MATERIALIZED VIEW Syntax
        1. Types of Materialized Views
        2. ROWID vs. Primary Key–Based Materialized Views
        3. Using Prebuilt Tables
        4. Indexing Materialized View Tables
      6. Using Materialized Views to Alter Query Execution Paths
      7. Using DBMS_ADVISOR
      8. Refreshing Materialized Views
        1. What Kind of Refreshes Can Be Performed?
        2. FAST Refresh with CONSIDER FRESH
        3. Automatic Refreshes
        4. Manual Refreshes
      9. CREATE MATERIALIZED VIEW Log Syntax
      10. Altering Materialized Views and Logs
      11. Dropping Materialized Views and Logs
    8. 27 Using Oracle Text for Text Searches
      1. Adding Text to the Database
      2. Text Queries and Text Indexes
        1. Text Queries
        2. Available Text Query Expressions
        3. Searching for an Exact Match of a Word
        4. Searching for an Exact Match of Multiple Words
        5. Searching for an Exact Match of a Phrase
        6. Searching for Words That Are Near Each Other
        7. Using Wildcards During Searches
        8. Searching for Words That Share the Same Stem
        9. Searching for Fuzzy Matches
        10. Searching for Words That Sound Like Other Words
        11. Nesting Search Operators
        12. Summarizing the Search Options
        13. Using the ABOUT Operator
        14. Index Synchronization
      3. Index Sets
    9. 28 Using External Tables
      1. Accessing the External Data
      2. Creating an External Table
        1. External Table Creation Options
        2. Loading External Tables on Creation
      3. Altering External Tables
        1. Access Parameters
        2. ADD COLUMN
        3. DEFAULT DIRECTORY
        4. DROP COLUMN
        5. LOCATION
        6. MODIFY COLUMN
        7. PARALLEL
        8. PROJECT COLUMN
        9. REJECT LIMIT
        10. RENAME TO
      4. Limitations, Benefits, and Potential Uses of External Tables
    10. 29 Using Flashback Queries
      1. Time-Based Flashback Example
      2. Saving the Data
      3. SCN-Based Flashback Example
      4. What If the Flashback Query Fails?
      5. What SCN Is Associated with Each Row?
      6. Flashback Version Queries
      7. Planning for Flashbacks
    11. 30 Flashback—Tables and Databases
      1. The Flashback Table Command
        1. Privileges Required
        2. Recovering Dropped Tables
        3. Enabling and Disabling the Recycle Bin
        4. Flashing Back to SCN or Timestamp
        5. Indexes and Statistics
      2. The FLASHBACK DATABASE Command
    12. 31 SQL Replay
      1. High-level Configuration
        1. Isolation and Links
        2. Creating a Workload Directory
      2. Capturing the Workload
        1. Defining Filters
        2. Starting the Capture
        3. Stopping the Capture
        4. Exporting AWR Data
      3. Processing the Workload
      4. Replaying the Workload
        1. Controlling and Starting the Replay Clients
        2. Initializing and Running the Replay
        3. Exporting AWR Data
  12. PART IV PL/SQL
    1. 32 An Introduction to PL/SQL
      1. PL/SQL Overview
      2. Declarations Section
      3. Executable Commands Section
        1. Conditional Logic
        2. Loops
        3. CASE Statements
      4. Exception Handling Section
    2. 33 Online Application Upgrades
      1. Highly Available Databases
        1. Oracle Data Guard Architecture
        2. Creating the Standby Database Configuration
        3. Managing Roles—Switchovers and Failovers
      2. Making Low-Impact DDL Changes
        1. Creating Virtual Columns
        2. Altering Actively Used Tables
        3. Adding NOT NULL Columns
        4. Online Object Reorganizations
        5. Dropping a Column
    3. 34 Triggers
      1. Required System Privileges
      2. Required Table Privileges
      3. Types of Triggers
        1. Row-Level Triggers
        2. Statement-Level Triggers
        3. BEFORE and AFTER Triggers
        4. INSTEAD OF Triggers
        5. Schema Triggers
        6. Database-Level Triggers
        7. Compound Triggers
      4. Trigger Syntax
        1. Combining DML Trigger Types
        2. Setting Inserted Values
        3. Maintaining Duplicated Data
        4. Customizing Error Conditions
        5. Calling Procedures Within Triggers
        6. Naming Triggers
        7. Creating DDL Event Triggers
        8. Creating Database Event Triggers
        9. Creating Compound Triggers
      5. Enabling and Disabling Triggers
      6. Replacing Triggers
      7. Dropping Triggers
    4. 35 Procedures, Functions, and Packages
      1. Required System Privileges
      2. Required Table Privileges
      3. Procedures vs. Functions
      4. Procedures vs. Packages
      5. CREATE PROCEDURESyntax
      6. CREATE FUNCTIONSyntax
        1. Referencing Remote Tables in Procedures
        2. Debugging Procedures
        3. Creating Your Own Functions
        4. Customizing Error Conditions
        5. Naming Procedures and Functions
      7. CREATE PACKAGE Syntax
      8. Viewing Source Code for Procedural Objects
      9. Compiling Procedures, Functions, and Packages
      10. Replacing Procedures, Functions, and Packages
      11. Dropping Procedures, Functions, and Packages
    5. 36 Using Native Dynamic SQL and DBMS_SQL
      1. Using Execute Immediate
      2. Using Bind Variables
      3. Using DBMS_SQL
        1. Open_Cursor
        2. PARSE
        3. BIND_VARIABLE and BIND_ARRAY
        4. EXECUTE
        5. DEFINE_COLUMN
        6. FETCH_ROWS, EXECUTE_AND_FETCH, and COLUMN_VALUE
        7. CLOSE_CURSOR
    6. 37 PL/SQL Tuning
      1. Tune the SQL
      2. Steps for Tuning the PL/SQL
      3. Use PL/SQL Within SQL Statements
      4. Use DBMS_PROFILER to Identify Problems
      5. Use PL/SQL Features for Bulk Operations
        1. Forall
        2. BULK COLLECT
  13. PART V Object-Relational Databases
    1. 38 Implementing Object Types, Object Views, and Methods
      1. Working with Object Types
        1. Security for Object Types
        2. Indexing Object Type Attributes
      2. Implementing Object Views
        1. Manipulating Data via Object Views
        2. Using INSTEAD OF Triggers
      3. Methods
        1. Syntax for Creating Methods
        2. Managing Methods
    2. 39 Collectors (Nested Tables and Varying Arrays)
      1. Varying Arrays
        1. Creating a Varying Array
        2. Describing the Varying Array
        3. Inserting Rows into the Varying Array
        4. Selecting Data from Varying Arrays
      2. Nested Tables
        1. Specifying Tablespaces for Nested Tables
        2. Inserting Rows into a Nested Table
        3. Working with Nested Tables
      3. Additional Functions for Nested Tables andVarying Arrays
      4. Management Issues for Nested Tables andVaryingArrays
        1. Variability in Collectors
        2. Location of the Data
    3. 40 Using Large Objects
      1. Available Datatypes
      2. Specifying Storage for LOB Data
      3. Manipulating and Selecting LOB Values
        1. Initializing Values
        2. Using INSERT with Subqueries
        3. Updating LOB Values
        4. Using String Functions to Manipulate LOB Values
        5. Using DBMS_LOB to Manipulate LOB Values
        6. Deleting LOBs
    4. 41 Advanced Object-Oriented Concepts
      1. Row Objects vs. Column Objects
      2. Object Tables and OIDs
        1. Inserting Rows into Object Tables
        2. Selecting Values from Object Tables
        3. Updating and Deleting from Object Tables
        4. Using the REF Function
        5. Using the DEREF Function
        6. Using the VALUE Function
        7. Invalid References
      3. Object Views with REFs
        1. A Quick Review of Object Views
        2. Object Views Involving References
      4. Object PL/SQL
      5. Objects in the Database
  14. PART VI Java in Oracle
    1. 42 An Introduction to Java
      1. Java vs. PL/SQL: An Overview
      2. Getting Started
      3. Declarations
      4. Executable Commands
        1. Conditional Logic
        2. Loops
        3. Exception Handling
        4. Reserved Words
      5. Classes
    2. 43 JDBC Programming
      1. Using the JDBC Classes
      2. Using JDBC for Data Manipulation
    3. 44 Java Stored Procedures
      1. Loading the Class into the Database
      2. How to Access the Class
        1. Calling Java Stored Procedures Directly
        2. Where to Perform Commands
  15. PART VII Hitchhiker’s Guides
    1. 45 The Hitchhiker’s Guide to the Oracle Data Dictionary
      1. A Note about Nomenclature
      2. New Views Introduced in Oracle Database 12c
      3. New Views Introduced in Oracle Database 11g
      4. The Road Maps: DICTIONARY (DICT) and DICT_COLUMNS
      5. Things You Select From: Tables (and Columns), Views, Synonyms, and Sequences
        1. Catalog: User_Catalog (CAT)
        2. Objects: USER_OBJECTS (OBJ)
        3. Tables: USER_TABLES (TABS)
        4. Columns: USER_TAB_COLUMNS (COLS)
        5. Views: USER_VIEWS
        6. Synonyms: USER_SYNONYMS (SYN)
        7. Sequences: USER_SEQUENCES (SEQ)
      6. Recycle Bin: USER_RECYCLEBIN and DBA_RECYCLEBIN
      7. Constraints and Comments
        1. Constraints: User_Constraints
        2. Constraint Columns: USER_CONS_COLUMNS
        3. Constraint Exceptions: EXCEPTIONS
        4. Table Comments: USER_TAB_COMMENTS
        5. Column Comments: USER_COL_COMMENTS
      8. Indexes and Clusters
        1. Indexes: User_Indexes (IND)
        2. Indexed Columns: USER_IND_COLUMNS
        3. Clusters: USER_CLUSTERS (CLU)
        4. Cluster Columns: USER_CLU_COLUMNS
      9. Abstract Datatypes and LOBs
        1. Abstract Datatypes: User_Types
        2. LOBs: USER_LOBS
      10. Database Links and Materialized Views
        1. Database Links: User_Db_Links
        2. Materialized Views
        3. Materialized View Logs: USER_MVIEW_LOGS
      11. Triggers, Procedures, Functions, and Packages
        1. Triggers: User_Triggers
        2. Procedures, Functions, and Packages: USER_SOURCE
      12. Dimensions
      13. Space Allocation and Usage, Including Partitions and Subpartitions
        1. Tablespaces: User_Tablespaces
        2. Space Quotas: USER_TS_QUOTAS
        3. Segments and Extents: USER_SEGMENTS and USER_EXTENTS
        4. Partitions and Subpartitions
        5. Free Space: USER_FREE_SPACE
      14. Users and Privileges
        1. Users: User_Users
        2. Resource Limits: USER_RESOURCE_LIMITS
        3. Table Privileges: USER_TAB_PRIVS
        4. Column Privileges: USER_COL_PRIVS
        5. System Privileges: USER_SYS_PRIVS
      15. Roles
      16. Auditing
        1. Auditing Privileges, DML, and Authentication
        2. Monitoring Object Usage
      17. Miscellaneous
        1. Monitoring: The V$ Dynamic Performance Tables
        2. CHAINED_ROWS
        3. PLAN_TABLE
        4. Interdependencies: USER_DEPENDENCIES and IDEPTREE
        5. DBA-Only Views
        6. Oracle Label Security
        7. SQL*Loader Direct Load Views
        8. Globalization Support Views
        9. Libraries
        10. Heterogeneous Services
        11. Indextypes and Operators
        12. Outlines
        13. Advisors
        14. Schedulers
    2. 46 The Hitchhiker’s Guide to Tuning Applications and SQL
      1. An Overview of Oracle Database Tuning Features
      2. Tuning—Best Practices
        1. Do as Little as Possible
        2. Do It as Simply as Possible
        3. Tell the Database What It Needs to Know
        4. Maximize the Throughput in the Environment
        5. Divide and Conquer Your Data
        6. Test Correctly
      3. Generating and Reading Explain Plans
        1. Using Set Autotrace On
        2. Using EXPLAIN PLAN
      4. Major Operations Within Explain Plans
        1. Table Access Full
        2. TABLE ACCESS BY INDEX ROWID
        3. Related Hints
        4. Operations That Use Indexes
        5. When Indexes Are Used
        6. Operations That Manipulate Data Sets
        7. Operations That Perform Joins
        8. How Oracle Handles Joins of More Than Two Tables
        9. Parallelism and Cache Issues
      5. Review
    3. 47 The Hitchhiker’s Guide to SQL Result Cache and Client-Side Query Cache
      1. Database Parameter Settings for SQL Result Cache
      2. The DBMS_RESULT_CACHE Package
      3. Dictionary Views for the SQL Result Cache
      4. Additional Details for SQL Result Cache
      5. Oracle Call Interface (OCI) Client Query Cache
      6. Oracle Call Interface (OCI) Client Query Cache Restrictions
    4. 48 Case Studies in Tuning
      1. Case Study 1: Waits, Waits, and More Waits
      2. Case Study 2: Application-Killing Queries
        1. Using the 10053 Trace Event
      3. Case Study 3: Long-Running Batch Jobs
    5. 49 The Hitchhiker’s Guide to Pluggable Databases
      1. Pluggable Database Concepts
        1. Container Database Components
        2. CDB User Accounts
        3. Prerequisites
      2. Creating Container and Pluggable Databases
        1. Creating the Container Database
        2. Creating a Pluggable Database
      3. Managing Pluggable Databases
        1. Unplugging a Pluggable Database
        2. Dropping a Pluggable Database
        3. Plugging In a Pluggable Database
        4. Monitoring Pluggable Databases
    6. 50 The Hitchhiker’s Guide to Oracle Real Application Clusters
      1. Preinstallation Steps
      2. Installing RAC
        1. Storage
        2. Initialization Parameters
      3. Starting and Stopping RAC Instances
      4. Transparent Application Failover
      5. Adding Nodes and Instances to the Cluster
    7. 51 The Hitchhiker’s Guide to Database Administration
      1. Creating a Database
        1. Using the Oracle Enterprise Manager
      2. Starting and Stopping the Database
      3. Sizing and Managing Memory Areas
        1. The Initialization Parameter File
      4. Allocating and Managing Space for the Objects
        1. Implications of the Storage Clause
        2. Table Segments
        3. Index Segments
        4. System-Managed Undo
        5. Temporary Segments
        6. Free Space
        7. Sizing Database Objects
      5. Monitoring an Undo Tablespace
      6. Automating Storage Management
        1. Configuring ASM
      7. Segment Space Management
      8. Transporting Tablespaces
        1. Generating a Transportable Tablespace Set
        2. Plugging in the Transportable Tablespace Set
      9. Performing Backups
        1. Data Pump Export and Import
        2. Offline Backups
        3. Online Backups
        4. Recovery Manager
      10. Where to Go from Here
    8. 52 The Hitchhiker’s Guide to XML in Oracle
      1. Document Type Definitions, Elements, andAttributes
      2. XML Schema
      3. Using XSU to Select, Insert, Update, and Delete XML Values
        1. Insert, Update, and Delete Processing with XSU
        2. XSU and Java
        3. Customizing the Query Process
      4. Using XMLType
      5. Other Features
  16. PART VIII Alphabetical Reference
  17. About the Downloadable Code
  18. Index

Product information

  • Title: Oracle Database 12c The Complete Reference
  • Author(s): Kevin Loney, Bob Bryla
  • Release date: August 2013
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071801744