administrator ’s guide

Part I Basic Database Administration

1 Getting Started with Database Administration

1.1 Types of Oracle Database Users

1.1.1 Database Administrators

1.1.2 Security Officers

1.1.3 Network Administrators

1.1.4 Application Developers

1.1.5 Application Administrators

1.1.6 Database Users

1.2 Tasks of a Database Administrator

1.2.1 Task 1: Evaluate the Database Server Hardware

1.2.2 Task 2: Install the Oracle Database Software

1.2.3 Task 3: Plan the Database

1.2.4 Task 4: Create and Open the Database

1.2.5 Task 5: Back Up the Database

1.2.6 Task 6: Enroll System Users

1.2.7 Task 7: Implement the Database Design

1.2.8 Task 8: Back Up the Fully Functional Database

1.2.9 Task 9: Tune Database Performance

1.2.10 Task 10: Download and Install Release Updates and Release Update Revisions

1.2.11 Task 11: Roll Out to Additional Hosts

1.3 SQL Statements

1.3.1 Submitting Commands and SQL to the Database

1.3.2 About SQL*Plus

1.3.3 Connecting to the Database with SQL*Plus

1.3.3.1 About Connecting to the Database with SQL*Plus

1.3.3.2 Step 1: Open a Command Window

1.3.3.3 Step 2: Set Operating System Environment Variables

1.3.3.4 Step 3: Start SQL*Plus

1.3.3.5 Step 4: Submit the SQL*Plus CONNECT Command

1.3.3.5.1 Syntax of the SQL*Plus CONNECT Command

1.4 Identifying Your Oracle Database Software Release

1.4.1 About Oracle Database Release Numbers

1.4.2 Checking Your Current Release Number

1.5 About Database Administrator Security and Privileges

1.5.1 The Database Administrator's Operating System Account

1.5.2 Administrative User Accounts

1.5.2.1 About Administrative User Accounts

1.5.2.2 SYS

1.5.2.3 SYSTEM

1.5.2.4 SYSBACKUP, SYSDG, SYSKM, and SYSRAC

1.5.2.5 The DBA Role

1.6 Database Administrator Authentication

1.6.1 Administrative Privileges

1.6.2 Operations Authorized by Administrative Privileges

1.6.3 Authentication Methods for Database Administrators

1.6.3.1 About Authentication Methods for Database Administrators

1.6.3.2 Nonsecure Remote Connections

1.6.3.3 Local Connections and Secure Remote Connections

1.6.4 Using Operating System Authentication

1.6.4.1 Operating System Groups

1.6.4.2 Preparing to Use Operating System Authentication

1.6.4.3 Connecting Using Operating System Authentication

1.6.5 Using Password File Authentication

1.6.5.1 Preparing to Use Password File Authentication

1.6.5.2 Connecting Using Password File Authentication

1.7 Creating and Maintaining a Database Password File

1.7.1 ORAPWD Syntax and Command Line Argument Descriptions

1.7.2 Creating a Database Password File with ORAPWD

1.7.3 Sharing and Disabling the Database Password File

1.7.4 Keeping Administrator Passwords Synchronized with the Data Dictionary

1.7.5 Adding Users to a Database Password File

1.7.6 Granting and Revoking Administrative Privileges

1.7.7 Viewing Database Password File Members

1.7.8 Removing a Database Password File

1.8 Data Utilities

2 Creating and Configuring an Oracle Database

2.1 About Creating an Oracle Database

2.2 Considerations Before Creating the Database

2.2.1 Planning for Database Creation

2.2.2 About Selecting a Character Set

2.2.3 About Configuring an Oracle Home in Read-Only Mode

2.2.4 Prerequisites for Database Creation

2.3 Creating a Database with DBCA

2.3.1 About Creating a Database with DBCA

2.3.2 About Creating a Database with Interactive DBCA

2.3.3 About Creating a Database with Noninteractive/Silent DBCA

2.4 Creating a Database with the CREATE DATABASE Statement

2.4.1 About Creating a Database with the CREATE DATABASE Statement

2.4.2 Step 1: Specify an Instance Identifier (SID)

2.4.3 Step 2: Ensure That the Required Environment Variables Are Set

2.4.4 Step 3: Choose a Database Administrator Authentication Method

2.4.5 Step 4: Create the Initialization Parameter File

2.4.6 Step 5: (Windows Only) Create an Instance

2.4.7 Step 6: Connect to the Instance

2.4.8 Step 7: Create a Server Parameter File

2.4.9 Step 8: Start the Instance

2.4.10 Step 9: Issue the CREATE DATABASE Statement

2.4.11 Step 10: Create Additional Tablespaces

2.4.12 Step 11: Run Scripts to Build Data Dictionary Views

2.4.13 Step 12: (Optional) Run Scripts to Install Additional Options

2.4.14 Step 13: Back Up the Database

2.4.15 Step 14: (Optional) Enable Automatic Instance Startup

2.5 Specifying CREATE DATABASE Statement Clauses

2.5.1 About CREATE DATABASE Statement Clauses

2.5.2 Protecting Your Database: Specifying Passwords for SYS and SYSTEM Users

2.5.3 Creating a Locally Managed SYSTEM Tablespace

2.5.4 Specify Data File Attributes for the SYSAUX Tablespace

2.5.4.1 About the SYSAUX Tablespace

2.5.5 Using Automatic Undo Management: Creating an Undo Tablespace

2.5.6 Creating a Default Permanent Tablespace

2.5.7 Creating a Default Temporary Tablespace

2.5.8 Specifying Oracle Managed Files at Database Creation

2.5.9 Supporting Bigfile Tablespaces During Database Creation

2.5.9.1 Specifying the Default Tablespace Type

2.5.9.2 Overriding the Default Tablespace Type

2.5.10 Specifying the Database Time Zone and Time Zone File

2.5.10.1 Setting the Database Time Zone

2.5.10.2 About the Database Time Zone Files

2.5.10.3 Specifying the Database Time Zone File

2.5.11 Specifying FORCE LOGGING Mode

2.5.11.1 Using the FORCE LOGGING Clause

2.5.11.2 Performance Considerations of FORCE LOGGING Mode

2.6 Specifying Initialization Parameters

2.6.1 About Initialization Parameters and Initialization Parameter Files

2.6.1.1 Sample Initialization Parameter File

2.6.1.2 Text Initialization Parameter File Format

2.6.2 Determining the Global Database Name

2.6.2.1 DB_NAME Initialization Parameter

2.6.2.2 DB_DOMAIN Initialization Parameter

2.6.3 Specifying a Fast Recovery Area

2.6.4 Specifying Control Files

2.6.5 Specifying Database Block Sizes

2.6.5.1 DB_BLOCK_SIZE Initialization Parameter

2.6.5.2 Nonstandard Block Sizes

2.6.6 Specifying the Maximum Number of Processes

2.6.7 Specifying the DDL Lock Timeout

2.6.8 Specifying the Method of Undo Space Management

2.6.8.1 UNDO_MANAGEMENT Initialization Parameter

2.6.8.2 UNDO_TABLESPACE Initialization Parameter

2.6.9 Specifying the Database Compatibility Level

2.6.9.1 About the COMPATIBLE Initialization Parameter

2.6.10 Setting the License Parameter

2.7 Managing Initialization Parameters Using a Server Parameter File

2.7.1 What Is a Server Parameter File?

2.7.2 Migrating to a Server Parameter File

2.7.3 Server Parameter File Default Names and Locations

2.7.4 Creating a Server Parameter File

2.7.5 The SPFILE Initialization Parameter

2.7.6 Changing Initialization Parameter Values

2.7.6.1 About Changing Initialization Parameter Values

2.7.6.2 Setting or Changing Initialization Parameter Values

2.7.6.2.1 The SCOPE Clause in ALTER SYSTEM SET Statements

2.7.7 Clearing Initialization Parameter Values

2.7.8 Exporting the Server Parameter File

2.7.9 Backing Up the Server Parameter File

2.7.10 Recovering a Lost or Damaged Server Parameter File

2.7.11 Methods for Viewing Parameter Settings

2.8 Managing Application Workloads with Database Services

2.8.1 Database Services

2.8.1.1 About Database Services

2.8.1.2 Database Services and Performance

2.8.1.3 Oracle Database Features That Use Database Services

2.8.1.4 Creating Database Services

2.8.2 Global Data Services

2.8.3 Database Service Data Dictionary Views

2.9 Managing Standard Edition High Availability with Oracle Databases

2.9.1 About Standard Edition High Availability

2.9.2 Requirements for Using Standard Edition High Availability With Oracle Databases

2.9.3 Enabling Standard Edition High Availability for Oracle Databases

2.9.4 Relocating a Standard Edition High Availability Database to Another Node

2.9.5 Adding a Node to a Standard Edition High Availability Database

2.9.6 Removing a Configured Node from a Standard Edition High Availability Database

2.9.7 Starting and Stopping Standard Edition High Availability Databases

2.9.8 Deactivating Standard Edition High Availability for Oracle Databases

2.10 Considerations After Creating a Database

2.10.1 Database Security

2.10.2 Transparent Data Encryption

2.10.3 A Secure External Password Store

2.10.4 Transaction Guard and Application Continuity

2.10.5 File System Server Support in the Database

2.10.6 The Oracle Database Sample Schemas

2.11 Cloning a Database

2.11.1 Cloning a Database with CloneDB in a Non-multitenant Environment

2.11.1.1 About Cloning a Database with CloneDB

2.11.1.2 Cloning a Database with CloneDB

2.11.1.3 After Cloning a Database with CloneDB

2.11.2 Cloning a Database in a Multitenant Environment

2.11.3 Cloning a Database with Oracle Automatic Storage Management (Oracle ASM)

2.12 Dropping a Database

2.13 Database Data Dictionary Views

2.14 Database Configuration Assistant Command Reference for Silent Mode

2.14.1 DBCA Command-Line Syntax Overview

2.14.2 About DBCA Templates

2.14.3 Database User Authentication in DBCA Commands Using Oracle Wallet

2.14.4 DBCA Silent Mode Commands

2.14.4.1 create Database

2.14.4.2 create Duplicate DB

2.14.4.3 configure Database

2.14.4.4 create Template From DB

2.14.4.5 create Template From Template

2.14.4.6 create Clone Template

2.14.4.7 delete Template

2.14.4.8 generate Scripts

2.14.4.9 delete Database

2.14.4.10 create Pluggable Database

2.14.4.11 unplug Database

2.14.4.12 delete Pluggable Database

2.14.4.13 relocate PDB

2.14.4.14 configure Pluggable Database

2.14.4.15 add Instance

2.14.4.16 delete Instance

2.14.4.17 execute Prereqs

2.14.5 DBCA Exit Codes

3 Starting Up and Shutting Down

3.1 Starting Up a Database

3.1.1 About Database Startup Options

3.1.1.1 Starting Up a Database Using SQL*Plus

3.1.1.2 Starting Up a Database Using Recovery Manager

3.1.1.3 Starting Up a Database Using Cloud Control

3.1.1.4 Starting Up a Database Using SRVCTL

3.1.2 Specifying Initialization Parameters at Startup

3.1.2.1 About Initialization Parameter Files and Startup

3.1.2.2 Starting Up with SQL*Plus with a Nondefault Server Parameter File

3.1.2.3 Starting Up with SRVCTL with a Nondefault Server Parameter File

3.1.3 About Automatic Startup of Database Services

3.1.4 Preparing to Start Up an Instance

3.1.5 Starting Up an Instance

3.1.5.1 About Starting Up an Instance

3.1.5.2 Starting an Instance, and Mounting and Opening a Database

3.1.5.3 Starting an Instance Without Mounting a Database

3.1.5.4 Starting an Instance and Mounting a Database

3.1.5.5 Restricting Access to an Instance at Startup

3.1.5.6 Forcing an Instance to Start

3.1.5.7 Starting an Instance, Mounting a Database, and Starting Complete Media Recovery

3.1.5.8 Automatic Database Startup at Operating System Start

3.1.5.9 Starting Remote Instances

3.2 Altering Database Availability

3.2.1 Mounting a Database to an Instance

3.2.2 Opening a Closed Database

3.2.3 Opening a Database in Read-Only Mode

3.2.4 Restricting Access to an Open Database

3.3 Shutting Down a Database

3.3.1 About Shutting Down the Database

3.3.2 Shutting Down with the Normal Mode

3.3.3 Shutting Down with the Immediate Mode

3.3.4 Shutting Down with the Transactional Mode

3.3.5 Shutting Down with the Abort Mode

3.3.6 Shutdown Timeout

3.4 Quiescing a Database

3.4.1 About Quiescing a Database

3.4.2 Placing a Database into a Quiesced State

3.4.3 Restoring the System to Normal Operation

3.4.4 Viewing the Quiesce State of an Instance

3.5 Suspending and Resuming a Database

3.6 Delaying Instance Abort

4 Configuring Automatic Restart of an Oracle Database

4.1 About Oracle Restart

4.1.1 Oracle Restart Overview

4.1.2 About Startup Dependencies

4.1.3 About Starting and Stopping Components with Oracle Restart

4.1.4 About Starting and Stopping Oracle Restart

4.1.5 Oracle Restart Configuration

4.1.6 Oracle Restart Integration with Oracle Data Guard

4.1.7 Fast Application Notification with Oracle Restart

4.1.7.1 Overview of Fast Application Notification

4.1.7.2 Application High Availability with Services and FAN

4.1.7.2.1 Managing Unplanned Outages

4.1.7.2.2 Managing Planned Outages

4.1.7.2.3 Fast Application Notification High Availability Events

4.1.7.2.4 Using Fast Application Notification Callouts

4.1.7.2.5 Oracle Clients That Are Integrated with Fast Application Notification

4.2 Configuring Oracle Restart

4.2.1 About Configuring Oracle Restart

4.2.2 Preparing to Run SRVCTL

4.2.3 Obtaining Help for SRVCTL

4.2.4 Adding Components to the Oracle Restart Configuration

4.2.5 Removing Components from the Oracle Restart Configuration

4.2.6 Disabling and Enabling Oracle Restart Management for a Component

4.2.7 Viewing Component Status

4.2.8 Viewing the Oracle Restart Configuration for a Component

4.2.9 Modifying the Oracle Restart Configuration for a Component

4.2.10 Managing Environment Variables in the Oracle Restart Configuration

4.2.10.1 About Environment Variables in the Oracle Restart Configuration

4.2.10.2 Setting and Unsetting Environment Variables

4.2.10.3 Viewing Environment Variables

4.2.11 Creating and Deleting Database Services with SRVCTL

4.2.12 Enabling FAN Events in an Oracle Restart Environment

4.2.13 Automating the Failover of Connections Between Primary and Standby Databases

4.2.14 Enabling Clients for Fast Connection Failover

4.2.14.1 About Enabling Clients for Fast Connection Failover

4.2.14.2 Enabling Fast Connection Failover for JDBC Clients

4.2.14.3 Enabling Fast Connection Failover for Oracle Call Interface Clients

4.2.14.4 Enabling Fast Connection Failover for ODP.NET Clients

4.3 Starting and Stopping Components Managed by Oracle Restart

4.4 Stopping and Restarting Oracle Restart for Maintenance Operations

4.5 SRVCTL Command Reference for Oracle Restart

4.5.1 add

4.5.1.1 srvctl add asm

4.5.1.1.1 Syntax and Options

4.5.1.1.2 ☆ example

4.5.1.2 srvctl add database

4.5.1.2.1 Syntax and Options

4.5.1.2.2 ☆ example s

4.5.1.3 srvctl add listener

4.5.1.3.1 Syntax and Options

4.5.1.3.2 ☆ example

4.5.1.4 srvctl add ons

4.5.1.4.1 Syntax and Options

4.5.1.5 srvctl add service

4.5.1.5.1 Syntax and Options

4.5.1.5.2 ☆ example

4.5.2 config

4.5.2.1 srvctl config asm

4.5.2.1.1 Syntax and Options

4.5.2.1.2 ☆ example

4.5.2.2 srvctl config database

4.5.2.2.1 Syntax and Options

4.5.2.2.2 ☆ example

4.5.2.3 srvctl config listener

4.5.2.3.1 Syntax and Options

4.5.2.3.2 ☆ example

4.5.2.4 srvctl config ons

4.5.2.4.1 Syntax and Options

4.5.2.5 srvctl config service

4.5.2.5.1 Syntax and Options

4.5.2.5.2 ☆ example

4.5.3 disable

4.5.3.1 srvctl disable asm

4.5.3.1.1 Syntax and Options

4.5.3.2 srvctl disable database

4.5.3.2.1 Syntax and Options

4.5.3.2.2 ☆ example

4.5.3.3 srvctl disable diskgroup

4.5.3.3.1 Syntax and Options

4.5.3.3.2 ☆ example

4.5.3.4 srvctl disable listener

4.5.3.4.1 Syntax and Options

4.5.3.4.2 ☆ example

4.5.3.5 srvctl disable ons

4.5.3.5.1 Syntax and Options

4.5.3.6 srvctl disable service

4.5.3.6.1 Syntax and Options

4.5.3.6.2 ☆ example

4.5.4 downgrade

4.5.4.1 srvctl downgrade database

4.5.4.1.1 Syntax and Options

4.5.5 enable

4.5.5.1 srvctl enable asm

4.5.5.1.1 Syntax and Options

4.5.5.2 srvctl enable database

4.5.5.2.1 Syntax and Options

4.5.5.2.2 ☆ example

4.5.5.3 srvctl enable diskgroup

4.5.5.3.1 Syntax and Options

4.5.5.3.2 ☆ example

4.5.5.4 srvctl enable listener

4.5.5.4.1 Syntax and Options

4.5.5.4.2 ☆ example

4.5.5.5 srvctl enable ons

4.5.5.5.1 Syntax and Options

4.5.5.6 srvctl enable service

4.5.5.6.1 Syntax and Options

4.5.5.6.2 ☆ example

4.5.6 getenv

4.5.6.1 srvctl getenv asm

4.5.6.1.1 Syntax and Options

4.5.6.1.2 ☆ example

4.5.6.2 srvctl getenv database

4.5.6.2.1 Syntax and Options

4.5.6.2.2 ☆ example

4.5.6.3 srvctl getenv listener

4.5.6.3.1 Syntax and Options

4.5.6.3.2 ☆ example

4.5.7 modify

4.5.7.1 srvctl modify asm

4.5.7.1.1 Syntax and Options

4.5.7.1.2 ☆ example

4.5.7.2 srvctl modify database

4.5.7.2.1 Syntax and Options

4.5.7.2.2 ☆ example

4.5.7.3 srvctl modify listener

4.5.7.3.1 Syntax and Options

4.5.7.3.2 ☆ example

4.5.7.4 srvctl modify ons

4.5.7.4.1 Syntax and Options

4.5.7.5 srvctl modify service

4.5.7.5.1 Syntax and Options

4.5.7.5.2 ☆ example

4.5.8 remove

4.5.8.1 srvctl remove asm

4.5.8.1.1 Syntax and Options

4.5.8.1.2 ☆ example

4.5.8.2 srvctl remove database

4.5.8.2.1 Syntax and Options

4.5.8.2.2 ☆ example

4.5.8.3 srvctl remove diskgroup

4.5.8.3.1 Syntax and Options

4.5.8.3.2 ☆ example

4.5.8.4 srvctl remove listener

4.5.8.4.1 Syntax and Options

4.5.8.4.2 ☆ example

4.5.8.5 srvctl remove ons

4.5.8.5.1 Syntax and Options

4.5.8.6 srvctl remove service

4.5.8.6.1 Syntax and Options

4.5.8.6.2 ☆ example

4.5.9 setenv

4.5.9.1 srvctl setenv asm

4.5.9.1.1 Syntax and Options

4.5.9.1.2 ☆ example

4.5.9.2 srvctl setenv database

4.5.9.2.1 Syntax and Options

4.5.9.2.2 ☆ example

4.5.9.3 srvctl setenv listener

4.5.9.3.1 Syntax and Options

4.5.9.3.2 ☆ example

4.5.10 start

4.5.10.1 srvctl start asm

4.5.10.1.1 Syntax and Options

4.5.10.1.2 ☆ example

4.5.10.2 srvctl start database

4.5.10.2.1 Syntax and Options

4.5.10.2.2 ☆ example

4.5.10.3 srvctl start diskgroup

4.5.10.3.1 Syntax and Options

4.5.10.3.2 ☆ example

4.5.10.4 srvctl start home

4.5.10.4.1 Syntax and Options

4.5.10.5 srvctl start listener

4.5.10.5.1 Syntax and Options

4.5.10.5.2 ☆ example

4.5.10.6 srvctl start ons

4.5.10.6.1 Syntax and Options

4.5.10.7 srvctl start service

4.5.10.7.1 Syntax and Options

4.5.10.7.2 ☆ example

4.5.11 status

4.5.11.1 srvctl status asm

4.5.11.1.1 Syntax and Options

4.5.11.1.2 ☆ example

4.5.11.2 srvctl status database

4.5.11.2.1 Syntax and Options

4.5.11.2.2 ☆ example

4.5.11.3 srvctl status diskgroup

4.5.11.3.1 Syntax and Options

4.5.11.3.2 ☆ example

4.5.11.4 srvctl status home

4.5.11.4.1 Syntax and Options

4.5.11.5 srvctl status listener

4.5.11.5.1 Syntax and Options

4.5.11.5.2 ☆ example

4.5.11.6 srvctl status ons

4.5.11.6.1 Syntax and Options

4.5.11.7 srvctl status service

4.5.11.7.1 Syntax and Options

4.5.11.7.2 ☆ example

4.5.12 stop

4.5.12.1 srvctl stop asm

4.5.12.1.1 Syntax and Options

4.5.12.1.2 ☆ example

4.5.12.2 srvctl stop database

4.5.12.2.1 Syntax and Options

4.5.12.2.2 ☆ example

4.5.12.3 srvctl stop diskgroup

4.5.12.3.1 Syntax and Options

4.5.12.3.2 ☆ example

4.5.12.4 srvctl stop home

4.5.12.4.1 Syntax and Options

4.5.12.5 srvctl stop listener

4.5.12.5.1 Syntax and Options

4.5.12.5.2 ☆ example

4.5.12.6 srvctl stop ons

4.5.12.6.1 Syntax and Options

4.5.12.7 srvctl stop service

4.5.12.7.1 Syntax and Options

4.5.12.7.2 ☆ example

4.5.13 unsetenv

4.5.13.1 srvctl unsetenv asm

4.5.13.1.1 Syntax and Options

4.5.13.1.2 ☆ example

4.5.13.2 srvctl unsetenv database

4.5.13.2.1 Syntax and Options

4.5.13.2.2 ☆ example

4.5.13.3 srvctl unsetenv listener

4.5.13.3.1 Syntax and Options

4.5.13.3.2 ☆ example

4.5.14 update

4.5.14.1 srvctl update database

4.5.14.1.1 Syntax and Options

4.5.15 upgrade

4.5.15.1 srvctl upgrade database

4.5.15.1.1 Syntax and Options

4.6 CRSCTL Command Reference

4.6.1 check

4.6.2 config

4.6.3 disable

4.6.4 enable

4.6.5 start

4.6.6 stop

5 Managing Processes

5.1 About Dedicated and Shared Server Processes

5.1.1 Dedicated Server Processes

5.1.2 Shared Server Processes

5.2 About Database Resident Connection Pooling

5.2.1 Comparing DRCP to Dedicated Server and Shared Server

5.3 About Proxy Resident Connection Pooling

5.4 Configuring Oracle Database for Shared Server

5.4.1 Initialization Parameters for Shared Server

5.4.2 Memory Management for Shared Server

5.4.3 Enabling Shared Server

5.4.3.1 About Determining a Value for SHARED_SERVERS

5.4.3.2 Decreasing the Number of Shared Server Processes

5.4.3.3 Limiting the Number of Shared Server Processes

5.4.3.4 Limiting the Number of Shared Server Sessions

5.4.3.5 Protecting Shared Memory

5.4.4 Configuring Dispatchers

5.4.4.1 DISPATCHERS Initialization Parameter Attributes

5.4.4.2 Determining the Number of Dispatchers

5.4.4.3 Setting the Initial Number of Dispatchers

5.4.4.4 Altering the Number of Dispatchers

5.4.4.4.1 Notes on Altering Dispatchers

5.4.4.5 Shutting Down Specific Dispatcher Processes

5.4.5 Disabling Shared Server

5.4.6 Shared Server Data Dictionary Views

5.5 Configuring Database Resident Connection Pooling

5.5.1 Enabling Database Resident Connection Pooling

5.5.2 Configuring the Connection Pool for Database Resident Connection Pooling

5.5.2.1 Configuration Parameters for Database Resident Connection Pooling

5.5.3 Data Dictionary Views for Database Resident Connection Pooling

5.5.4 Determining the States of Connections in the Connection Pool

5.6 About Oracle Database Background Processes

5.7 Managing Prespawned Processes

5.7.1 About Managing Prespawned Processes

5.7.2 Managing Pools for Prespawned Processes

5.8 Managing Processes for Parallel SQL Execution

5.8.1 About Parallel Execution Servers

5.8.2 Altering Parallel Execution for a Session

5.8.2.1 Disabling Parallel SQL Execution

5.8.2.2 Enabling Parallel SQL Execution

5.8.2.3 Forcing Parallel SQL Execution

5.9 Managing Processes for External Procedures

5.9.1 About External Procedures

5.9.2 DBA Tasks to Enable External Procedure Calls

5.10 Terminating Sessions

5.10.1 About Terminating Sessions

5.10.2 Identifying Which Session to Terminate

5.10.3 Terminating an Active Session

5.10.4 Terminating an Inactive Session

5.10.5 Cancelling a SQL Statement in a Session

5.11 Process and Session Data Dictionary Views

6 Managing Memory

6.1 About Memory Management

6.2 Memory Architecture Overview

6.3 Using Automatic Memory Management

6.3.1 About Automatic Memory Management

6.3.2 Enabling Automatic Memory Management

6.3.3 Monitoring and Tuning Automatic Memory Management

6.4 Configuring Memory Manually

6.4.1 About Manual Memory Management

6.4.2 Using Automatic Shared Memory Management

6.4.2.1 About Automatic Shared Memory Management

6.4.2.2 Components and Granules in the SGA

6.4.2.3 Setting Maximum SGA Size

6.4.2.4 Setting SGA Target Size

6.4.2.4.1 The SGA Target and Automatically Sized SGA Components

6.4.2.4.2 SGA and Virtual Memory

6.4.2.4.3 Monitoring and Tuning SGA Target Size

6.4.2.5 Enabling Automatic Shared Memory Management

6.4.2.6 Setting Minimums for Automatically Sized SGA Components

6.4.2.7 Dynamic Modification of SGA_TARGET

6.4.2.8 Modifying Parameters for Automatically Sized Components

6.4.2.9 Modifying Parameters for Manually Sized Components

6.4.3 Using Manual Shared Memory Management

6.4.3.1 About Manual Shared Memory Management

6.4.3.2 Enabling Manual Shared Memory Management

6.4.3.3 Setting the Buffer Cache Initialization Parameters

6.4.3.3.1 ☆ example of Setting Block and Cache Sizes

6.4.3.3.2 Multiple Buffer Pools

6.4.3.4 Specifying the Shared Pool Size

6.4.3.4.1 The Result Cache and Shared Pool Size

6.4.3.5 Specifying the Large Pool Size

6.4.3.6 Specifying the Java Pool Size

6.4.3.7 Specifying the Streams Pool Size

6.4.3.8 Specifying the Result Cache Maximum Size

6.4.3.9 Specifying Miscellaneous SGA Initialization Parameters

6.4.3.9.1 Physical Memory

6.4.3.9.2 SGA Starting Address

6.4.4 Using Automatic PGA Memory Management

6.4.5 Using Manual PGA Memory Management

6.5 Using Force Full Database Caching Mode

6.5.1 About Force Full Database Caching Mode

6.5.2 Before Enabling Force Full Database Caching Mode

6.5.3 Enabling Force Full Database Caching Mode

6.5.4 Disabling Force Full Database Caching Mode

6.6 Configuring Database Smart Flash Cache

6.6.1 When to Configure Database Smart Flash Cache

6.6.2 Sizing Database Smart Flash Cache

6.6.3 Tuning Memory for Database Smart Flash Cache

6.6.4 Database Smart Flash Cache Initialization Parameters

6.6.5 Database Smart Flash Cache in an Oracle Real Applications Clusters Environment

6.7 Improving Query Performance with Oracle Database In-Memory

6.8 Enabling High Performance Data Streaming with the Memoptimized Rowstore

6.9 Memory Management Reference

6.9.1 Platforms That Support Automatic Memory Management

6.9.2 Memory Management Data Dictionary Views

7 Managing Users and Securing the Database

7.1 The Importance of Establishing a Security Policy for Your Database

7.2 Managing Users and Resources

7.3 User Privileges and Roles

7.4 Auditing Database Activity

7.5 Predefined User Accounts

8 Monitoring the Database

8.1 Monitoring Errors and Alerts

8.1.1 Monitoring Errors with Trace Files and the Alert Log

8.1.1.1 About Monitoring Errors with Trace Files and the Alert Log

8.1.1.2 Controlling the Size of an Alert Log

8.1.1.3 Controlling the Size of Trace Files

8.1.1.3.1 Trace File Segmentation and MAX_DUMP_FILE_SIZE

8.1.1.4 Controlling When Oracle Database Writes to Trace Files

8.1.1.5 Reading the Trace File for Shared Server Sessions

8.1.2 Monitoring a Database with Server-Generated Alerts

8.1.2.1 About Monitoring a Database with Server-Generated Alerts

8.1.2.2 Setting and Retrieving Thresholds for Server-Generated Alerts

8.1.2.2.1 Setting Threshold Levels

8.1.2.2.2 Retrieving Threshold Information

8.1.2.3 Viewing Server-Generated Alerts

8.1.2.4 Server-Generated Alerts Data Dictionary Views

8.2 Monitoring Performance

8.2.1 Monitoring Locks

8.2.2 About Monitoring Wait Events

8.2.3 Performance Monitoring Data Dictionary Views

8.3 Monitoring Quarantined Objects

8.3.1 About Object Quarantine

8.3.2 Viewing Quarantined Objects

9 Diagnosing and Resolving Problems

9.1 About the Oracle Database Fault Diagnosability Infrastructure

9.1.1 Fault Diagnosability Infrastructure Overview

9.1.2 Incidents and Problems

9.1.2.1 About Incidents and Problems

9.1.2.2 Incident Flood Control

9.1.2.3 Related Problems Across the Topology

9.1.3 Fault Diagnosability Infrastructure Components

9.1.3.1 Automatic Diagnostic Repository (ADR)

9.1.3.2 Alert Log

9.1.3.3 Trace Files, Dumps, and Core Files

9.1.3.3.1 Trace Files

9.1.3.3.2 Dumps

9.1.3.3.3 Core Files

9.1.3.4 DDL Log

9.1.3.5 Debug Log

9.1.3.6 Other ADR Contents

9.1.3.7 Enterprise Manager Support Workbench

9.1.3.8 ADRCI Command-Line Utility

9.1.4 Structure, Contents, and Location of the Automatic Diagnostic Repository

9.2 About Investigating, Reporting, and Resolving a Problem

9.2.1 Roadmap — Investigating, Reporting, and Resolving a Problem

9.2.2 Task 1: View Critical Error Alerts in Cloud Control

9.2.3 Task 2: View Problem Details

9.2.4 Task 3: (Optional) Gather Additional Diagnostic Information

9.2.5 Task 4: (Optional) Create a Service Request

9.2.6 Task 5: Package and Upload Diagnostic Data to Oracle Support

9.2.7 Task 6: Track the Service Request and Implement Any Repairs

9.3 Diagnosing Problems

9.3.1 Identifying Problems Reactively

9.3.1.1 Viewing Problems with the Support Workbench

9.3.1.2 Adding Problems Manually to the Automatic Diagnostic Repository

9.3.1.3 Creating Incidents Manually

9.3.2 Identifying Problems Proactively with Health Monitor

9.3.2.1 About Health Monitor

9.3.2.1.1 About Health Monitor Checks

9.3.2.1.2 Types of Health Checks

9.3.2.2 Running Health Checks Manually

9.3.2.2.1 Running Health Checks Using the DBMS_HM PL/SQL Package

9.3.2.2.2 Running Health Checks Using Cloud Control

9.3.2.3 Viewing Checker Reports

9.3.2.3.1 About Viewing Checker Reports

9.3.2.3.2 Viewing Reports Using Cloud Control

9.3.2.3.3 Viewing Reports Using DBMS_HM

9.3.2.3.4 Viewing Reports Using the ADRCI Utility

9.3.2.4 Health Monitor Views

9.3.2.5 Health Check Parameters Reference

9.3.3 Gathering Additional Diagnostic Data

9.3.3.1 Viewing the Alert Log

9.3.3.2 Finding Trace Files

9.3.4 Creating Test Cases with SQL Test Case Builder

9.3.4.1 Purpose of SQL Test Case Builder

9.3.4.2 Concepts for SQL Test Case Builder

9.3.4.2.1 SQL Incidents

9.3.4.2.2 What SQL Test Case Builder Captures

9.3.4.2.3 Output of SQL Test Case Builder

9.3.4.3 User Interfaces for SQL Test Case Builder

9.3.4.3.1 Graphical Interface for SQL Test Case Builder

9.3.4.3.1.1 Accessing the Incident Manager

9.3.4.3.1.2 Accessing the Support Workbench

9.3.4.3.2 Command-Line Interface for SQL Test Case Builder

9.3.4.4 Running SQL Test Case Builder

9.4 Reporting Problems

9.4.1 Incident Packages

9.4.1.1 About Incident Packages

9.4.1.2 About Correlated Diagnostic Data in Incident Packages

9.4.1.3 About Quick Packaging and Custom Packaging

9.4.1.4 About Correlated Packages

9.4.2 Packaging and Uploading Problems with Custom Packaging

9.4.3 Viewing and Modifying Incident Packages

9.4.3.1 Viewing Package Details

9.4.3.2 Accessing the Customize Package Page

9.4.3.3 Editing Incident Package Files (Copying Out and In)

9.4.3.4 Adding an External File to an Incident Package

9.4.3.5 Removing Incident Package Files

9.4.3.6 Viewing and Updating the Incident Package Activity Log

9.4.4 Creating, Editing, and Uploading Correlated Packages

9.4.5 Deleting Correlated Packages

9.4.6 Setting Incident Packaging Preferences

9.5 Resolving Problems

9.5.1 Repairing SQL Failures with the SQL Repair Advisor

9.5.1.1 About the SQL Repair Advisor

9.5.1.2 Running the SQL Repair Advisor Using Cloud Control

9.5.1.3 Running the SQL Repair Advisor Using the DBMS_SQLDIAG Package Subprograms

9.5.1.4 Viewing, Disabling, or Removing a SQL Patch Using Cloud Control

9.5.1.5 Disabling or Removing a SQL Patch Using DBMS_SQLDIAG Package Subprograms

9.5.1.6 Exporting and Importing a Patch Using DBMS_SQLDIAG Package Subprograms

9.5.2 Repairing Data Corruptions with the Data Recovery Advisor

9.5.3 Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources

9.5.3.1 About Quarantine for Execution Plans for SQL Statements

9.5.3.2 Creating a Quarantine Configuration for an Execution Plan of a SQL Statement

9.5.3.3 Specifying Quarantine Thresholds in a Quarantine Configuration

9.5.3.4 Enabling and Disabling a Quarantine Configuration

9.5.3.5 Viewing the Details of a Quarantine Configuration

9.5.3.6 Deleting a Quarantine Configuration

9.5.3.7 Viewing the Details of Quarantined Execution Plans of SQL Statements

9.5.3.8 Transferring Quarantine Configurations from One Database to Another Database

9.5.3.9 ☆ example : Quarantine for an Execution Plan of a SQL Statement Consuming Excessive System Resources

Part II Oracle Database Structure and Storage

10 Managing Control Files

10.1 What Is a Control File?

10.2 Guidelines for Control Files

10.2.1 Provide File Names for the Control Files

10.2.2 Multiplex Control Files on Different Disks

10.2.3 Back Up Control Files

10.2.4 Manage the Size of Control Files

10.3 Creating Control Files

10.3.1 Creating Initial Control Files

10.3.2 Creating Additional Copies, Renaming, and Relocating Control Files

10.3.3 Creating New Control Files

10.3.3.1 When to Create New Control Files

10.3.3.2 The CREATE CONTROLFILE Statement

10.3.3.3 Creating New Control Files

10.4 Troubleshooting After Creating Control Files

10.4.1 Checking for Missing or Extra Files

10.4.2 Handling Errors During CREATE CONTROLFILE

10.5 Backing Up Control Files

10.6 Recovering a Control File Using a Current Copy

10.6.1 Recovering from Control File Corruption Using a Control File Copy

10.6.2 Recovering from Permanent Media Failure Using a Control File Copy

10.7 Dropping Control Files

10.8 Control Files Data Dictionary Views

11 Managing the Redo Log

11.1 What Is the Redo Log?

11.1.1 Redo Threads

11.1.2 Redo Log Contents

11.1.3 How Oracle Database Writes to the Redo Log

11.1.3.1 Active (Current) and Inactive Redo Log Files

11.1.3.2 Log Switches and Log Sequence Numbers

11.2 Planning the Redo Log

11.2.1 Multiplexing Redo Log Files

11.2.1.1 Responding to Redo Log Failure

11.2.1.2 Legal and Illegal Configurations

11.2.2 Placing Redo Log Members on Different Disks

11.2.3 Planning the Size of Redo Log Files

11.2.4 Planning the Block Size of Redo Log Files

11.2.5 Choosing the Number of Redo Log Files

11.2.6 Controlling Archive Lag

11.2.6.1 Setting the ARCHIVE_LAG_TARGET Initialization Parameter

11.2.6.2 Factors Affecting the Setting of ARCHIVE_LAG_TARGET

11.3 Creating Redo Log Groups and Members

11.3.1 Creating Redo Log Groups

11.3.2 Creating Redo Log Members

11.4 Relocating and Renaming Redo Log Members

11.5 Dropping Redo Log Groups and Members

11.5.1 Dropping Log Groups

11.5.2 Dropping Redo Log Members

11.6 Forcing Log Switches

11.7 Verifying Blocks in Redo Log Files

11.8 Clearing a Redo Log File

11.9 Precedence of FORCE LOGGING Settings

11.10 Redo Log Data Dictionary Views

12 Managing Archived Redo Log Files

12.1 What Is the Archived Redo Log?

12.2 Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

12.2.1 Running a Database in NOARCHIVELOG Mode

12.2.2 Running a Database in ARCHIVELOG Mode

12.3 Controlling Archiving

12.3.1 Setting the Initial Database Archiving Mode

12.3.2 Changing the Database Archiving Mode

12.3.3 Performing Manual Archiving

12.3.4 Adjusting the Number of Archiver Processes

12.4 Specifying Archive Destinations

12.4.1 Setting Initialization Parameters for Archive Destinations

12.4.1.1 Method 1: Using the LOG_ARCHIVE_DEST_n Parameter

12.4.1.2 Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

12.4.2 Expanding Alternate Destinations with Log Archive Destination Groups12.4.2.1

About Log Archive Destination Groups

12.4.2.2 Specifying Log Archive Destination Groups

12.4.3 Understanding Archive Destination Status

12.4.4 Specifying Alternate Destinations

12.5 About Log Transmission Modes

12.5.1 Normal Transmission Mode

12.5.2 Standby Transmission Mode

12.6 Managing Archive Destination Failure

12.6.1 Specifying the Minimum Number of Successful Destinations

12.6.1.1 Specifying Mandatory and Optional Destinations

12.6.1.2 Specifying the Number of Successful Destinations: Scenarios

12.6.1.2.1 Scenario for Archiving to Optional Local Destinations

12.6.1.2.2 Scenario for Archiving to Both Mandatory and Optional Destinations

12.6.2 Rearchiving to a Failed Destination

12.7 Controlling Trace Output Generated by the Archivelog Process

12.8 Viewing Information About the Archived Redo Log

12.8.1 Archived Redo Log Files Views

12.8.2 Using the ARCHIVE LOG LIST Command

13 Managing Tablespaces

13.1 Guidelines for Managing Tablespaces

13.1.1 Use Multiple Tablespaces

13.1.2 Assign Tablespace Quotas to Users

13.2 Creating Tablespaces

13.2.1 About Creating Tablespaces

13.2.2 Locally Managed Tablespaces

13.2.2.1 About Locally Managed Tablespaces

13.2.2.2 Creating a Locally Managed Tablespace

13.2.2.3 Specifying Segment Space Management in Locally Managed Tablespaces

13.2.3 Bigfile Tablespaces

13.2.3.1 About Bigfile Tablespaces

13.2.3.2 Creating a Bigfile Tablespace

13.2.3.3 Identifying a Bigfile Tablespace

13.2.4 Tablespaces with Default Compression Attributes

13.2.4.1 About Tablespaces with Default Compression Attributes

13.2.4.2 Creating Tablespaces with Default Compression Attributes

13.2.5 Encrypted Tablespaces

13.2.5.1 About Encrypted Tablespaces

13.2.5.2 Creating Encrypted Tablespaces

13.2.5.3 Viewing Information About Encrypted Tablespaces

13.2.6 Temporary Tablespaces

13.2.6.1 About Temporary Tablespaces

13.2.6.2 Creating a Locally Managed Temporary Tablespace

13.2.6.3 Creating a Bigfile Temporary Tablespace

13.2.6.4 Viewing Space Usage for Temporary Tablespaces

13.2.7 Temporary Tablespace Groups

13.2.7.1 Multiple Temporary Tablespaces: Using Tablespace Groups

13.2.7.2 Creating a Tablespace Group

13.2.7.3 Changing Members of a Tablespace Group

13.2.7.4 Assigning a Tablespace Group as the Default Temporary Tablespace

13.3 Consider Storing Tablespaces in the In-Memory Column Store

13.4 Specifying Nonstandard Block Sizes for Tablespaces

13.5 Controlling the Writing of Redo Records

13.6 Altering Tablespace Availability

13.6.1 Taking Tablespaces Offline

13.6.2 Bringing Tablespaces Online

13.7 Using Read-Only Tablespaces

13.7.1 About Read-Only Tablespaces

13.7.2 Making a Tablespace Read-Only

13.7.3 Making a Read-Only Tablespace Writable

13.7.4 Creating a Read-Only Tablespace on a WORM Device

13.7.5 Delaying the Opening of Data Files in Read-Only Tablespaces

13.8 Altering and Maintaining Tablespaces

13.8.1 Increasing the Size of a Tablespace

13.8.2 Altering a Locally Managed Tablespace

13.8.3 Altering a Bigfile Tablespace

13.8.4 Altering a Locally Managed Temporary Tablespace

13.8.5 Shrinking a Locally Managed Temporary Tablespace

13.9 Renaming Tablespaces

13.10 Dropping Tablespaces

13.11 Managing Lost Write Protection with Shadow Tablespaces

13.11.1 About Shadow Lost Write Protection

13.11.2 Creating Shadow Tablespaces for Shadow Lost Write Protection

13.11.3 Enabling Shadow Lost Write Protection for a Database

13.11.4 Enabling Shadow Lost Write Protection for Tablespaces and Data Files

13.11.5 Disabling Shadow Lost Write Protection for a Database

13.11.6 Removing or Suspending Shadow Lost Write Protection

13.11.7 Dropping a Shadow Tablespace

13.12 Managing the SYSAUX Tablespace

13.12.1 Monitoring Occupants of the SYSAUX Tablespace

13.12.2 Moving Occupants Out Of or Into the SYSAUX Tablespace

13.12.3 Controlling the Size of the SYSAUX Tablespace

13.13 Correcting Problems with Locally Managed Tablespaces

13.13.1 Diagnosing and Repairing Locally Managed Tablespace Problems

13.13.2 Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)

13.13.3 Scenario 2: Dropping a Corrupted Segment

13.13.4 Scenario 3: Fixing Bitmap Where Overlap is Reported

13.13.5 Scenario 4: Correcting Media Corruption of Bitmap Blocks

13.13.6 Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace

13.14 Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

13.15 Viewing Information About Tablespaces

13.15.1 Tablespace Data Dictionary Views

13.15.2 ☆ example 1: Listing Tablespaces and Default Storage Parameters

13.15.3 ☆ example 2: Listing the Data Files and Associated Tablespaces of a Database

13.15.4 ☆ example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

14 Managing Data Files and Temp Files

14.1 Guidelines for Managing Data Files

14.1.1 About Data Files

14.1.2 Determine the Number of Data Files

14.1.2.1 About Determining the Number of Data Files

14.1.2.2 Determine a Value for the DB_FILES Initialization Parameter

14.1.2.3 Consider Possible Limitations When Adding Data Files to a Tablespace

14.1.2.4 Consider the Performance Impact of the Number of Data Files

14.1.3 Determine the Size of Data Files

14.1.4 Place Data Files Appropriately

14.1.5 Store Data Files Separate from Redo Log Files

14.2 Creating Data Files and Adding Data Files to a Tablespace

14.3 Changing Data File Size

14.3.1 Enabling and Disabling Automatic Extension for a Data File

14.3.2 Manually Resizing a Data File

14.4 Altering Data File Availability

14.4.1 About Altering Data File Availability

14.4.2 Bringing Data Files Online or Taking Offline in ARCHIVELOG Mode

14.4.3 Taking Data Files Offline in NOARCHIVELOG Mode

14.4.4 Altering the Availability of All Data Files or Temp Files in a Tablespace

14.5 Renaming and Relocating Data Files

14.5.1 Renaming and Relocating Online Data Files

14.5.2 Renaming and Relocating Offline Data Files

14.5.2.1 Procedures for Renaming and Relocating Offline Data Files in a Single Tablespace

14.5.2.1.1 Renaming Offline Data Files in a Single Tablespace

14.5.2.1.2 Relocating Offline Data Files in a Single Tablespace

14.5.2.2 Renaming and Relocating Offline Data Files in Multiple Tablespaces

14.6 Dropping Data Files

14.7 Verifying Data Blocks in Data Files

14.8 Copying Files Using the Database Server

14.8.1 About Copying Files Using the Database Server

14.8.2 Copying a File on a Local File System

14.8.3 Third-Party File Transfer

14.8.4 Advanced File Transfer Mechanisms

14.8.5 File Transfer and the DBMS_SCHEDULER Package

14.9 Mapping Files to Physical Devices

14.9.1 Overview of Oracle Database File Mapping Interface

14.9.2 How the Oracle Database File Mapping Interface Works

14.9.2.1 Components of File Mapping

14.9.2.1.1 FMON

14.9.2.1.2 External Process (FMPUTL)

14.9.2.1.3 Mapping Libraries

14.9.2.2 Mapping Structures

14.9.2.3 ☆ example of Mapping Structures

14.9.2.4 Configuration ID

14.9.3 Using the Oracle Database File Mapping Interface

14.9.3.1 Enabling File Mapping

14.9.3.2 Using the DBMS_STORAGE_MAP Package

14.9.3.3 Obtaining Information from the File Mapping Views

14.9.4 File Mapping ☆ example s

14.9.4.1 ☆ example 1: Map All Database Files that Span a Device

14.9.4.2 ☆ example 2: Map a File Into Its Corresponding Devices

14.9.4.3 ☆ example 3: Map a Database Object

14.10 Data Files Data Dictionary Views

15 Transporting Data

15.1 About Transporting Data

15.1.1 Purpose of Transporting Data

15.1.2 Transporting Data: Scenarios

15.1.2.1 Scenarios for Full Transportable Export/import

15.1.2.1.1 Moving a Non-CDB Into a CDB

15.1.2.1.2 Moving a Database to a New Computer System

15.1.2.1.3 Upgrading to a New Release of Oracle Database

15.1.2.2 Scenarios for Transportable Tablespaces or Transportable Tables

15.1.2.2.1 Scenarios That Apply to Transportable Tablespaces or Transportable Tables

15.1.2.2.2 Transporting and Attaching Partitions for Data Warehousing

15.1.2.2.3 Publishing Structured Data on CDs

15.1.2.2.4 Mounting the Same Tablespace Read-Only on Multiple Databases

15.1.2.2.5 Archiving Historical Data

15.1.2.2.6 Using Transportable Tablespaces to Perform TSPITR

15.1.2.2.7 Copying or Moving Individual Tables

15.1.3 Transporting Data Across Platforms

15.1.4 General Limitations on Transporting Data

15.1.5 Compatibility Considerations for Transporting Data

15.2 Transporting Databases

15.2.1 Introduction to Full Transportable Export/Import

15.2.2 Limitations on Full Transportable Export/import

15.2.3 Transporting a Database Using an Export Dump File

15.2.4 Transporting a Database Over the Network

15.3 Transporting Tablespaces Between Databases

15.3.1 Introduction to Transportable Tablespaces

15.3.2 Limitations on Transportable Tablespaces

15.3.3 Transporting Tablespaces Between Databases

15.3.3.1 Task 1: Pick a Self-Contained Set of Tablespaces

15.3.3.2 Task 2: Generate a Transportable Tablespace Set

15.3.3.3 Task 3: Transport the Export Dump File

15.3.3.4 Task 4: Transport the Tablespace Set

15.3.3.5 Task 5: (Optional) Restore Tablespaces to Read/Write Mode

15.3.3.6 Task 6: Import the Tablespace Set

15.4 Transporting Tables, Partitions, or Subpartitions Between Databases

15.4.1 Introduction to Transportable Tables

15.4.2 Limitations on Transportable Tables

15.4.3 Transporting Tables, Partitions, or Subpartitions Using an Export Dump File

15.4.4 Transporting Tables, Partitions, or Subpartitions Over the Network

15.5 Converting Data Between Platforms

15.5.1 Converting Data Between Platforms Using the DBMS_FILE_TRANSFER Package

15.5.2 Converting Data Between Platforms Using RMAN

15.5.2.1 Converting Tablespaces on the Source System After Export

15.5.2.2 Converting Data Files on the Target System Before Import

15.6 Guidelines for Transferring Data Files

16 Managing Undo

16.1 What Is Undo?

16.2 Introduction to Automatic Undo Management

16.2.1 Overview of Automatic Undo Management

16.2.2 The Undo Retention Period

16.2.2.1 About the Undo Retention Period

16.2.2.2 Automatic Tuning of Undo Retention

16.2.2.3 Retention Guarantee

16.2.2.4 Undo Retention Tuning and Alert Thresholds

16.2.2.5 Tracking the Tuned Undo Retention Period

16.3 Setting the Minimum Undo Retention Period

16.4 Sizing a Fixed-Size Undo Tablespace

16.4.1 Activating the Undo Advisor PL/SQL Interface

16.5 Managing Undo Tablespaces

16.5.1 Creating an Undo Tablespace

16.5.1.1 About Creating an Undo Tablespace

16.5.1.2 Using CREATE DATABASE to Create an Undo Tablespace

16.5.1.3 Using the CREATE UNDO TABLESPACE Statement

16.5.2 Altering an Undo Tablespace

16.5.3 Dropping an Undo Tablespace

16.5.4 Switching Undo Tablespaces

16.5.5 Establishing User Quotas for Undo Space

16.5.6 Managing Space Threshold Alerts for the Undo Tablespace

16.6 Migrating to Automatic Undo Management

16.7 Managing Temporary Undo

16.7.1 About Managing Temporary Undo

16.7.2 Enabling and Disabling Temporary Undo

16.8 Undo Space Data Dictionary Views

17 Using Oracle Managed Files

17.1 About Oracle Managed Files

17.1.1 What Is Oracle Managed Files?

17.1.2 Who Can Use Oracle Managed Files?

17.1.3 What Is a Logical Volume Manager?

17.1.4 What Is a File System?

17.1.5 Benefits of Using Oracle Managed Files

17.1.6 Oracle Managed Files and Existing Functionality

17.2 Enabling the Creation and Use of Oracle Managed Files

17.2.1 Initialization Parameters That Enable Oracle Managed Files

17.2.2 Setting the DB_CREATE_FILE_DEST Initialization Parameter

17.2.3 Setting the DB_RECOVERY_FILE_DEST Parameter

17.2.4 Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameters

17.3 Creating Oracle Managed Files

17.3.1 When Oracle Database Creates Oracle Managed Files

17.3.2 How Oracle Managed Files Are Named

17.3.3 Creating Oracle Managed Files at Database Creation

17.3.3.1 Specifying Control Files at Database Creation

17.3.3.2 Specifying Redo Log Files at Database Creation

17.3.3.3 Specifying the SYSTEM and SYSAUX Tablespace Data Files at Database Creation

17.3.3.4 Specifying the Undo Tablespace Data File at Database Creation

17.3.3.5 Specifying the Default Temporary Tablespace Temp File at Database Creation

17.3.3.6 CREATE DATABASE Statement Using Oracle Managed Files: ☆ example s

17.3.4 Creating Data Files for Tablespaces Using Oracle Managed Files

17.3.4.1 About Creating Data Files for Tablespaces Using Oracle Managed Files

17.3.4.2 CREATE TABLESPACE: ☆ example s

17.3.4.3 CREATE UNDO TABLESPACE: ☆ example

17.3.4.4 ALTER TABLESPACE: ☆ example

17.3.5 Creating Temp Files for Temporary Tablespaces Using Oracle Managed Files

17.3.5.1 About Creating Temp Files for Temporary Tablespaces Using Oracle Managed Files

17.3.5.2 CREATE TEMPORARY TABLESPACE: ☆ example

17.3.5.3 ALTER TABLESPACE... ADD TEMPFILE: ☆ example

17.3.6 Creating Control Files Using Oracle Managed Files

17.3.6.1 About Creating Control Files Using Oracle Managed Files

17.3.6.2 CREATE CONTROLFILE Using NORESETLOGS Keyword: ☆ example

17.3.6.3 CREATE CONTROLFILE Using RESETLOGS Keyword: ☆ example

17.3.7 Creating Redo Log Files Using Oracle Managed Files

17.3.7.1 Using the ALTER DATABASE ADD LOGFILE Statement

17.3.7.2 Using the ALTER DATABASE OPEN RESETLOGS Statement

17.3.8 Creating Archived Logs Using Oracle Managed Files

17.4 Operation of Oracle Managed Files

17.4.1 Dropping Data Files and Temp Files

17.4.2 Dropping Redo Log Files

17.4.3 Renaming Files

17.4.4 Managing Standby Databases

17.5 Scenarios for Using Oracle Managed Files

17.5.1 Scenario 1: Create and Manage a Database with Multiplexed Redo Logs

17.5.2 Scenario 2: Create and Manage a Database with Database and Fast Recovery Areas

17.5.3 Scenario 3: Adding Oracle Managed Files to an Existing Database

Part III Schema Objects

18 Managing Schema Objects

18.1 Creating Multiple Tables and Views in a Single Operation

18.2 Analyzing Tables, Indexes, and Clusters

18.2.1 About Analyzing Tables, Indexes, and Clusters

18.2.2 Using DBMS_STATS to Collect Table and Index Statistics

18.2.3 Validating Tables, Indexes, Clusters, and Materialized Views

18.2.4 Cross Validation of a Table and an Index with a Query

18.2.5 Listing Chained Rows of Tables and Clusters

18.2.5.1 Creating a CHAINED_ROWS Table

18.2.5.2 Eliminating Migrated or Chained Rows in a Table

18.3 Truncating Tables and Clusters

18.3.1 Using DELETE to Truncate a Table

18.3.2 Using DROP and CREATE to Truncate a Table

18.3.3 Using TRUNCATE

18.4 Enabling and Disabling Triggers

18.4.1 About Enabling and Disabling Triggers

18.4.2 Enabling Triggers

18.4.3 Disabling Triggers

18.5 Managing Integrity Constraints

18.5.1 Integrity Constraint States

18.5.1.1 About Integrity Constraint States

18.5.1.2 About Disabling Constraints

18.5.1.3 About Enabling Constraints

18.5.1.4 About the Enable Novalidate Constraint State

18.5.1.5 Efficient Use of Integrity Constraints: A Procedure

18.5.2 Setting Integrity Constraints Upon Definition

18.5.2.1 Disabling Constraints Upon Definition

18.5.2.2 Enabling Constraints Upon Definition

18.5.3 Modifying, Renaming, or Dropping Existing Integrity Constraints

18.5.3.1 Disabling and Enabling Constraints

18.5.3.2 Renaming Constraints

18.5.3.3 Dropping Constraints

18.5.4 Deferring Constraint Checks

18.5.4.1 Set All Constraints Deferred

18.5.4.2 Check the Commit (Optional)

18.5.5 Reporting Constraint Exceptions

18.5.6 Viewing Constraint Information

18.6 Renaming Schema Objects

18.7 Managing Object Dependencies

18.7.1 About Object Dependencies and Object Invalidation

18.7.2 Manually Recompiling Invalid Objects with DDL

18.7.3 Manually Recompiling Invalid Objects with PL/SQL Package Procedures

18.8 Managing Object Name Resolution

18.9 Switching to a Different Schema

18.10 Managing Editions

18.10.1 About Editions and Edition-Based Redefinition

18.10.2 DBA Tasks for Edition-Based Redefinition

18.10.3 Setting the Database Default Edition

18.10.4 Querying the Database Default Edition

18.10.5 Setting the Edition Attribute of a Database Service

18.10.5.1 About Setting the Edition Attribute of a Database Service

18.10.5.2 Setting the Edition Attribute During Database Service Creation

18.10.5.3 Setting the Edition Attribute of an Existing Database Service

18.10.6 Using an Edition

18.10.7 Editions Data Dictionary Views

18.11 Displaying Information About Schema Objects

18.11.1 Using a PL/SQL Package to Display Information About Schema Objects

18.11.2 Schema Objects Data Dictionary Views

18.11.2.1 ☆ example 1: Displaying Schema Objects By Type

18.11.2.2 ☆ example 2: Displaying Dependencies of Views and Synonyms

19 Managing Space for Schema Objects

19.1 Managing Tablespace Alerts

19.1.1 About Managing Tablespace Alerts

19.1.2 Setting Alert Thresholds

19.1.3 Viewing Alerts

19.1.4 Limitations

19.2 Managing Resumable Space Allocation

19.2.1 Resumable Space Allocation Overview

19.2.1.1 How Resumable Space Allocation Works

19.2.1.2 What Operations are Resumable?

19.2.1.3 What Errors are Correctable?

19.2.1.4 Resumable Space Allocation and Distributed Operations

19.2.1.5 Parallel Execution and Resumable Space Allocation

19.2.2 Enabling and Disabling Resumable Space Allocation

19.2.2.1 About Enabling and Disabling Resumable Space Allocation

19.2.2.2 Setting the RESUMABLE_TIMEOUT Initialization Parameter

19.2.2.3 Using ALTER SESSION to Enable and Disable Resumable Space Allocation

19.2.2.3.1 Specifying a Timeout Interval

19.2.2.3.2 Naming Resumable Statements

19.2.3 Using a LOGON Trigger to Set Default Resumable Mode

19.2.4 Detecting Suspended Statements

19.2.4.1 Notifying Users: The AFTER SUSPEND System Event and Trigger

19.2.4.2 Using Views to Obtain Information About Suspended Statements

19.2.4.3 Using the DBMS_RESUMABLE Package

19.2.5 Operation-Suspended Alert

19.2.6 Resumable Space Allocation ☆ example : Registering an AFTER SUSPEND Trigger

19.3 Reclaiming Unused Space

19.3.1 About Reclaimable Unused Space

19.3.2 The Segment Advisor

19.3.2.1 About the Segment Advisor

19.3.2.2 Using the Segment Advisor

19.3.2.3 Automatic Segment Advisor

19.3.2.4 Running the Segment Advisor Manually

19.3.2.4.1 Running the Segment Advisor Manually with Cloud Control

19.3.2.4.2 Running the Segment Advisor Manually with PL/SQL

19.3.2.5 Viewing Segment Advisor Results

19.3.2.5.1 Viewing Segment Advisor Results with Cloud Control

19.3.2.5.2 Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views

19.3.2.5.3 Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS

19.3.2.6 Configuring the Automatic Segment Advisor

19.3.2.7 Viewing Automatic Segment Advisor Information

19.3.3 Shrinking Database Segments Online

19.3.4 Deallocating Unused Space

19.4 Dropping Unused Object Storage

19.5 Understanding Space Usage of Data Types

19.6 Displaying Information About Space Usage for Schema Objects

19.6.1 Using PL/SQL Packages to Display Information About Schema Object Space Usage

19.6.2 Schema Objects Space Usage Data Dictionary Views

19.6.2.1 ☆ example 1: Displaying Segment Information

19.6.2.2 ☆ example 2: Displaying Extent Information

19.6.2.3 ☆ example 3: Displaying the Free Space (Extents) in a Tablespace

19.7 Capacity Planning for Database Objects

19.7.1 Estimating the Space Use of a Table

19.7.2 Estimating the Space Use of an Index

19.7.3 Obtaining Object Growth Trends

20 Managing Tables

20.1 About Tables

20.2 Guidelines for Managing Tables

20.2.1 Design Tables Before Creating Them

20.2.2 Specify the Type of Table to Create

20.2.3 Specify the Location of Each Table

20.2.4 Consider Parallelizing Table Creation

20.2.5 Consider Using NOLOGGING When Creating Tables

20.2.6 Consider Using Table Compression

20.2.6.1 About Table Compression

20.2.6.2 ☆ example s Related to Table Compression

20.2.6.3 Compression and Partitioned Tables

20.2.6.4 Determining If a Table Is Compressed

20.2.6.5 Determining Which Rows Are Compressed

20.2.6.6 Changing the Compression Level

20.2.6.7 Adding and Dropping Columns in Compressed Tables

20.2.6.8 Exporting and Importing Hybrid Columnar Compression Tables

20.2.6.9 Restoring a Hybrid Columnar Compression Table

20.2.6.10 Notes and Restrictions for Compressed Tables

20.2.6.11 Packing Compressed Tables

20.2.7 Managing Table Compression Using Enterprise Manager Cloud Control

20.2.7.1 Table Compression and Enterprise Manager Cloud Control

20.2.7.2 Viewing the Compression Summary at the Database Level

20.2.7.3 Viewing the Compression Summary at the Tablespace Level

20.2.7.4 Estimating the Compression Ratio

20.2.7.5 Compressing an Object

20.2.7.6 Viewing Compression Advice

20.2.7.7 Initiating Automatic Data Optimization on an Object

20.2.8 Consider Using Segment-Level and Row-Level Compression Tiering

20.2.9 Consider Using Attribute-Clustered Tables

20.2.10 Consider Using Zone Maps

20.2.11 Consider Storing Tables in the In-Memory Column Store

20.2.12 Consider Using Invisible Columns

20.2.12.1 Understand Invisible Columns

20.2.12.2 Invisible Columns and Column Ordering

20.2.13 Consider Encrypting Columns That Contain Sensitive Data

20.2.14 Understand Deferred Segment Creation

20.2.15 Materializing Segments

20.2.16 Estimate Table Size and Plan Accordingly

20.2.17 Restrictions to Consider When Creating Tables

20.3 Creating Tables

20.3.1 ☆ example : Creating a Table

20.3.2 Creating a Temporary Table

20.3.2.1 Overview of Temporary Tables

20.3.2.2 Considerations When Creating Temporary Tables

20.3.2.3 Creating Global Temporary Tables

20.3.2.3.1 About Creating Global Temporary Tables

20.3.2.3.2 ☆ example s: Creating a Global Temporary Table

20.3.2.4 Creating Private Temporary Tables

20.3.2.4.1 About Creating Private Temporary Tables

20.3.2.4.2 ☆ example s: Creating a Private Temporary Table

20.3.3 Parallelizing Table Creation

20.4 Loading Tables

20.4.1 Methods for Loading Tables

20.4.2 Improving INSERT Performance with Direct-Path INSERT

20.4.2.1 About Direct-Path INSERT

20.4.2.2 How Direct-Path INSERT Works

20.4.2.2.1 Serial Direct-Path INSERT into Partitioned or Nonpartitioned Tables

20.4.2.2.2 Parallel Direct-Path INSERT into Partitioned Tables

20.4.2.2.3 Parallel Direct-Path INSERT into Nonpartitioned Tables

20.4.2.3 Loading Data with Direct-Path INSERT

20.4.2.3.1 Serial Mode Inserts with SQL Statements

20.4.2.3.2 Parallel Mode Inserts with SQL Statements

20.4.2.4 Logging Modes for Direct-Path INSERT

20.4.2.4.1 Direct-Path INSERT with Logging

20.4.2.4.2 Direct-Path INSERT without Logging

20.4.2.5 Additional Considerations for Direct-Path INSERT

20.4.2.5.1 Compressed Tables and Direct-Path INSERT

20.4.2.5.2 Index Maintenance with Direct-Path INSERT

20.4.2.5.3 Space Considerations with Direct-Path INSERT

20.4.2.5.4 Locking Considerations with Direct-Path INSERT

20.4.3 Using Conventional Inserts to Load Tables

20.4.4 Avoiding Bulk INSERT Failures with DML Error Logging

20.4.4.1 Inserting Data with DML Error Logging

20.4.4.2 Error Logging Table Format

20.4.4.3 Creating an Error Logging Table

20.4.4.3.1 Creating an Error Logging Table Automatically

20.4.4.3.2 Creating an Error Logging Table Manually

20.4.4.4 Error Logging Restrictions and Caveats

20.4.4.4.1 Space Considerations

20.4.4.4.2 Security

20.5 Optimizing the Performance of Bulk Updates

20.6 Automatically Collecting Statistics on Tables

20.7 Altering Tables

20.7.1 Reasons for Using the ALTER TABLE Statement

20.7.2 Altering Physical Attributes of a Table

20.7.3 Moving a Table to a New Segment or Tablespace

20.7.3.1 About Moving a Table to a New Segment or Tablespace

20.7.3.2 Moving a Table

20.7.3.3 Moving a Table Partition or Subpartition Online

20.7.4 Manually Allocating Storage for a Table

20.7.5 Modifying an Existing Column Definition

20.7.6 Adding Table Columns

20.7.7 Renaming Table Columns

20.7.8 Dropping Table Columns

20.7.8.1 Removing Columns from Tables

20.7.8.2 Marking Columns Unused

20.7.8.3 Removing Unused Columns

20.7.8.4 Dropping Columns in Compressed Tables

20.7.9 Placing a Table in Read-Only Mode

20.8 Redefining Tables Online

20.8.1 About Redefining Tables Online

20.8.2 Features of Online Table Redefinition

20.8.3 Privileges Required for the DBMS_REDEFINITION Package

20.8.4 Restrictions for Online Redefinition of Tables

20.8.5 Performing Online Redefinition with the REDEF_TABLE Procedure

20.8.6 Redefining Tables Online with Multiple Procedures in DBMS_REDEFINITION

20.8.6.1 Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION

20.8.6.2 Constructing a Column Mapping String

20.8.6.3 Handling Virtual Private Database (VPD) Policies During Online Redefinition

20.8.6.4 Creating Dependent Objects Automatically

20.8.6.5 Creating Dependent Objects Manually

20.8.7 Results of the Redefinition Process

20.8.8 Performing Intermediate Synchronization

20.8.9 Refreshing Dependent Materialized Views During Online Table Redefinition

20.8.10 Monitoring Online Table Redefinition Progress

20.8.11 Restarting Online Table Redefinition After a Failure

20.8.12 Rolling Back Online Table Redefinition

20.8.12.1 About Online Table Redefinition Rollback

20.8.12.2 Performing Online Table Redefinition Rollback

20.8.13 Aborting Online Table Redefinition and Cleaning Up After Errors

20.8.14 Online Redefinition of One or More Partitions

20.8.14.1 Rules for Online Redefinition of a Single Partition

20.8.15 Online Table Redefinition ☆ example s

20.9 Researching and Reversing Erroneous Table Changes

20.10 Recovering Tables Using Oracle Flashback Table

20.11 Dropping Tables

20.12 Using Flashback Drop and Managing the Recycle Bin

20.12.1 What Is the Recycle Bin?

20.12.2 Enabling and Disabling the Recycle Bin

20.12.3 Viewing and Querying Objects in the Recycle Bin

20.12.4 Purging Objects in the Recycle Bin

20.12.5 Restoring Tables from the Recycle Bin

20.13 Managing Index-Organized Tables

20.13.1 What Are Index-Organized Tables?

20.13.2 Creating Index-Organized Tables

20.13.2.1 About Creating Index-Organized Tables

20.13.2.2 ☆ example : Creating an Index-Organized Table

20.13.2.3 Restrictions for Index-Organized Tables

20.13.2.4 Creating Index-Organized Tables That Contain Object Types

20.13.2.5 Choosing and Monitoring a Threshold Value

20.13.2.6 Using the INCLUDING Clause

20.13.2.7 Parallelizing Index-Organized Table Creation

20.13.2.8 Using Prefix Compression

20.13.3 Maintaining Index-Organized Tables

20.13.3.1 Altering Index-Organized Tables

20.13.3.2 Moving (Rebuilding) Index-Organized Tables

20.13.4 Creating Secondary Indexes on Index-Organized Tables

20.13.4.1 About Secondary Indexes on Index-Organized Tables

20.13.4.2 Creating a Secondary Index on an Index-Organized Table

20.13.4.3 Maintaining Physical Guesses in Logical Rowids

20.13.4.4 Specifying Bitmap Indexes on Index-Organized Tables

20.13.5 Analyzing Index-Organized Tables

20.13.5.1 Collecting Optimizer Statistics for Index-Organized Tables

20.13.5.2 Validating the Structure of Index-Organized Tables

20.13.6 Using the ORDER BY Clause with Index-Organized Tables

20.13.7 Converting Index-Organized Tables to Regular Tables

20.14 Managing Partitioned Tables

20.15 Managing External Tables

20.15.1 About External Tables

20.15.2 Creating External Tables

20.15.3 Altering External Tables

20.15.4 Preprocessing External Tables

20.15.5 Overriding Parameters for External Tables in a Query

20.15.6 Using Inline External Tables

20.15.7 Partitioning External Tables

20.15.7.1 About Partitioning External Tables

20.15.7.2 Restrictions for Partitioned External Tables

20.15.7.3 Creating a Partitioned External Table

20.15.7.4 Altering a Partitioned External Table

20.15.8 Dropping External Tables

20.15.9 System and Object Privileges for External Tables

20.16 Managing Hybrid Partitioned Tables

20.17 Tables Data Dictionary Views

21 Managing Indexes

21.1 About Indexes

21.2 Guidelines for Managing Indexes

21.2.1 Create Indexes After Inserting Table Data

21.2.2 Index the Correct Tables and Columns

21.2.3 Order Index Columns for Performance

21.2.4 Limit the Number of Indexes for Each Table

21.2.5 Drop Indexes That Are No Longer Required

21.2.6 Indexes and Deferred Segment Creation

21.2.7 Estimate Index Size and Set Storage Parameters

21.2.8 Specify the Tablespace for Each Index

21.2.9 Consider Parallelizing Index Creation

21.2.10 Consider Creating Indexes with NOLOGGING

21.2.11 Understand When to Use Unusable or Invisible Indexes

21.2.12 Understand When to Create Multiple Indexes on the Same Set of Columns

21.2.13 Consider Costs and Benefits of Coalescing or Rebuilding Indexes

21.2.14 Consider Cost Before Disabling or Dropping Constraints

21.2.15 Consider Using the In-Memory Column Store to Reduce the Number of Indexes

21.3 Creating Indexes

21.3.1 Prerequisites for Creating Indexes

21.3.2 Creating an Index Explicitly

21.3.3 Creating a Unique Index Explicitly

21.3.4 Creating an Index Associated with a Constraint

21.3.4.1 About Creating an Index Associated with a Constraint

21.3.4.2 Specifying Storage Options for an Index Associated with a Constraint

21.3.4.3 Specifying the Index Associated with a Constraint

21.3.5 Creating a Large Index

21.3.6 Creating an Index Online

21.3.7 Creating a Function-Based Index

21.3.8 Creating a Compressed Index

21.3.8.1 Creating an Index Using Prefix Compression

21.3.8.2 Creating an Index Using Advanced Index Compression

21.3.9 Creating an Unusable Index

21.3.10 Creating an Invisible Index

21.3.11 Creating Multiple Indexes on the Same Set of Columns

21.4 Altering Indexes

21.4.1 About Altering Indexes

21.4.2 Altering Storage Characteristics of an Index

21.4.3 Rebuilding an Existing Index

21.4.4 Making an Index Unusable

21.4.5 Making an Index Invisible or Visible

21.4.6 Renaming an Index

21.4.7 Monitoring Index Usage

21.5 Monitoring Space Use of Indexes

21.6 Dropping Indexes

21.7 Managing Auto Indexes

21.7.1 About Automatic Indexing

21.7.2 How Automatic Indexing Works

21.7.3 Configuring Automatic Indexing in an Oracle Database

21.7.4 Generating Automatic Indexing Reports

21.7.5 Views Containing the Automatic Indexing Information

21.8 Indexes Data Dictionary Views

22 Managing Clusters

22.1 About Clusters

22.2 Guidelines for Managing Clusters

22.2.1 Choose Appropriate Tables for the Cluster

22.2.2 Choose Appropriate Columns for the Cluster Key

22.2.3 Specify the Space Required by an Average Cluster Key and Its Associated Rows

22.2.4 Specify the Location of Each Cluster and Cluster Index Rows

22.2.5 Estimate Cluster Size and Set Storage Parameters

22.3 Creating Clusters and Objects That Use Them

22.3.1 Creating Clusters

22.3.2 Creating Clustered Tables

22.3.3 Creating Cluster Indexes

22.4 Altering Clusters and Objects That Use Them

22.4.1 Altering Clusters

22.4.2 Altering Clustered Tables

22.4.3 Altering Cluster Indexes

22.5 Dropping Clusters and Objects That Use Them

22.5.1 Dropping Clusters

22.5.2 Dropping Clustered Tables

22.5.3 Dropping Cluster Indexes

22.6 Clusters Data Dictionary Views

23 Managing Hash Clusters

23.1 About Hash Clusters

23.2 When to Use Hash Clusters

23.2.1 Situations Where Hashing Is Useful

23.2.2 Situations Where Hashing Is Not Advantageous

23.3 Creating Different Types of Hash Clusters

23.3.1 Creating Hash Clusters

23.3.2 Creating a Sorted Hash Cluster

23.3.3 Creating Single-Table Hash Clusters

23.3.4 Controlling Space Use Within a Hash Cluster

23.3.4.1 Choosing the Key

23.3.4.2 Setting HASH IS

23.3.4.3 Setting SIZE

23.3.4.4 Setting HASHKEYS

23.3.4.5 Controlling Space in Hash Clusters

23.3.4.5.1 Controlling Space in Hash Clusters: ☆ example 1

23.3.4.5.2 Controlling Space in Hash Clusters: ☆ example 2

23.3.5 Estimating Size Required by Hash Clusters

23.4 Altering Hash Clusters

23.5 Dropping Hash Clusters

23.6 Hash Clusters Data Dictionary Views

24 Managing Views, Sequences, and Synonyms

24.1 Managing Views

24.1.1 About Views

24.1.2 Creating Views and Join Views

24.1.2.1 Creating Views

24.1.2.2 Creating Join Views

24.1.2.3 Expansion of Defining Queries at View Creation Time

24.1.2.4 Creating Views with Errors

24.1.3 Replacing Views

24.1.4 Using Views in Queries

24.1.5 DML Statements and Join Views

24.1.5.1 Updating a Join View

24.1.5.2 Key-Preserved Tables

24.1.5.3 Rules for DML Statements and Join Views

24.1.5.3.1 UPDATE Statements and Join Views

24.1.5.3.2 DELETE Statements and Join Views

24.1.5.3.3 INSERT Statements and Join Views

24.1.5.4 Updating Views That Involve Outer Joins

24.1.5.5 Using the UPDATABLE_ COLUMNS Views

24.1.6 Altering Views

24.1.7 Dropping Views

24.2 Managing Sequences

24.2.1 About Sequences

24.2.2 Creating Sequences

24.2.3 Altering Sequences

24.2.4 Using Sequences

24.2.4.1 Referencing a Sequence

24.2.4.1.1 Generating Sequence Numbers with NEXTVAL

24.2.4.1.2 Using Sequence Numbers with CURRVAL

24.2.4.1.3 Uses and Restrictions of NEXTVAL and CURRVAL

24.2.4.2 Caching Sequence Numbers

24.2.4.2.1 About Caching Sequence Numbers

24.2.4.2.2 The Number of Entries in the Sequence Cache

24.2.4.2.3 The Number of Values in Each Sequence Cache Entry

24.2.4.3 Making a Sequence Scalable

24.2.5 Dropping Sequences

24.3 Managing Synonyms

24.3.1 About Synonyms

24.3.2 Creating Synonyms

24.3.3 Using Synonyms in DML Statements

24.3.4 Dropping Synonyms

24.4 Views, Synonyms, and Sequences Data Dictionary Views

25 Repairing Corrupted Data

25.1 Options for Repairing Data Block Corruption

25.2 About the DBMS_REPAIR Package

25.2.1 DBMS_REPAIR Procedures

25.2.2 Limitations and Restrictions for DBMS_REPAIR Procedures

25.3 Using the DBMS_REPAIR Package

25.3.1 Task 1: Detect and Report Corruptions

25.3.1.1 About Detecting and Reporting Corruptions

25.3.1.2 DBMS_REPAIR: Using the CHECK_OBJECT and ADMIN_TABLES Procedures

25.3.1.3 DB_VERIFY: Performing an Offline Database Check

25.3.1.4 ANALYZE: Reporting Corruption

25.3.1.5 DB_BLOCK_CHECKING Initialization Parameter

25.3.2 Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR

25.3.3 Task 3: Make Objects Usable

25.3.3.1 Corruption Repair: Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_BLOCKS Procedures

25.3.3.2 Implications When Skipping Corrupt Blocks

25.3.4 Task 4: Repair Corruptions and Rebuild Lost Data

25.3.4.1 Recover Data Using the DUMP_ORPHAN_KEYS Procedures

25.3.4.2 Fix Segment Bitmaps Using the SEGMENT_FIX_STATUS Procedure

25.4 DBMS_REPAIR ☆ example s

25.4.1 ☆ example s: Building a Repair Table or Orphan Key Table

25.4.1.1 About Repair Tables or Orphan Key Tables

25.4.1.2 ☆ example : Creating a Repair Table

25.4.1.3 ☆ example : Creating an Orphan Key Table

25.4.2 ☆ example : Detecting Corruption

25.4.3 ☆ example : Fixing Corrupt Blocks

25.4.4 ☆ example : Finding Index Entries Pointing to Corrupt Data Blocks

25.4.5 ☆ example : Skipping Corrupt Blocks

Part IV Database Resource Management and Task Scheduling

26 Managing Automated Database Maintenance Tasks

26.1 About Automated Maintenance Tasks

26.2 About Maintenance Windows

26.3 Configuring Automated Maintenance Tasks

26.3.1 Enabling and Disabling Maintenance Tasks for all Maintenance Windows

26.3.2 Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows

26.4 Configuring Maintenance Windows

26.4.1 Modifying a Maintenance Window

26.4.2 Creating a New Maintenance Window

26.4.3 Removing a Maintenance Window

26.5 Configuring Resource Allocations for Automated Maintenance Tasks

26.5.1 About Resource Allocations for Automated Maintenance Tasks

26.5.2 Changing Resource Allocations for Automated Maintenance Tasks

26.6 Automated Maintenance Tasks Reference

26.6.1 Predefined Maintenance Windows

26.6.2 Automated Maintenance Tasks Database Dictionary Views

27 Managing Resources with Oracle Database Resource Manager

27.1 About Oracle Database Resource Manager

27.1.1 What Solutions Does the Resource Manager Provide for Workload Management?

27.1.2 The Elements of Resource Manager

27.1.2.1 About the Elements of Resource Manager

27.1.2.2 About Resource Consumer Groups

27.1.2.3 About Resource Plan Directives

27.1.2.4 About Resource Plans

27.1.2.5 ☆ example : A Simple Resource Plan

27.1.2.6 About Subplans

27.1.2.7 ☆ example : A Resource Plan with Subplans

27.1.3 About Resource Manager Administration Privileges

27.2 Assigning Sessions to Resource Consumer Groups

27.2.1 Overview of Assigning Sessions to Resource Consumer Groups

27.2.2 Assigning an Initial Resource Consumer Group

27.2.3 Specifying Session-to-Consumer Group Mapping Rules

27.2.3.1 About Session-to-Consumer Group Mapping Rules

27.2.3.2 Creating Consumer Group Mapping Rules

27.2.3.3 Modifying and Deleting Consumer Group Mapping Rules

27.2.3.4 Creating Mapping Rule Priorities

27.2.4 Switching Resource Consumer Groups

27.2.4.1 Manually Switching Resource Consumer Groups

27.2.4.1.1 About Manually Switching Resource Consumer Groups

27.2.4.1.2 Switching a Single Session

27.2.4.1.3 Switching All Sessions for a User

27.2.4.2 Enabling Users or Applications to Manually Switch Consumer Groups

27.2.5 Specifying Automatic Consumer Group Switching

27.2.5.1 Specifying Automatic Switching with Mapping Rules

27.2.5.2 Specifying Automatic Switching by Setting Resource Limits

27.2.6 Granting and Revoking the Switch Privilege

27.2.6.1 About Granting and Revoking the Switch Privilege

27.2.6.2 Granting the Switch Privilege

27.2.6.3 Revoking Switch Privileges

27.3 The Types of Resources Managed by the Resource Manager

27.3.1 CPU

27.3.1.1 Management Attributes

27.3.1.2 Utilization Limit

27.3.2 Exadata I/O

27.3.3 Parallel Execution Servers

27.3.3.1 Degree of Parallelism Limit

27.3.3.2 Parallel Server Limit

27.3.3.2.1 Managing Parallel Statement Queuing Using Parallel Server Limit

27.3.3.3 Parallel Queue Timeout

27.3.4 Program Global Area (PGA)

27.3.5 Runaway Queries

27.3.5.1 Automatic Consumer Group Switching

27.3.5.2 Canceling SQL and Terminating Sessions

27.3.5.3 Execution Time Limit

27.3.6 Active Session Pool with Queuing

27.3.7 Undo Pool

27.3.8 Idle Time Limit

27.4 Creating a Simple Resource Plan

27.5 Creating a Complex Resource Plan

27.5.1 About the Pending Area

27.5.2 Creating a Pending Area

27.5.3 Creating Resource Consumer Groups

27.5.4 Mapping Sessions to Consumer Groups

27.5.5 Creating a Resource Plan

27.5.5.1 About the RATIO CPU Allocation Method

27.5.6 Creating Resource Plan Directives

27.5.6.1 Conflicting Resource Plan Directives

27.5.7 Validating the Pending Area

27.5.8 Submitting the Pending Area

27.5.9 Clearing the Pending Area

27.6 Enabling Oracle Database Resource Manager and Switching Plans

27.7 Putting It All Together: Oracle Database Resource Manager ☆ example s

27.7.1 Multilevel Plan ☆ example

27.7.2 ☆ example s of Using the Utilization Limit Attribute

27.7.3 ☆ example of Using Several Resource Allocation Methods

27.7.4 ☆ example of Managing Parallel Statements Using Directive Attributes

27.7.5 An Oracle-Supplied Mixed Workload Plan

27.8 Managing Multiple Database Instances on a Single Server

27.8.1 About Instance Caging

27.8.2 Enabling Instance Caging

27.9 Maintaining Consumer Groups, Plans, and Directives

27.9.1 Updating a Consumer Group

27.9.2 Deleting a Consumer Group

27.9.3 Updating a Plan

27.9.4 Deleting a Plan

27.9.5 Updating a Resource Plan Directive

27.9.6 Deleting a Resource Plan Directive

27.10 Viewing Database Resource Manager Configuration and Status

27.10.1 Viewing Consumer Groups Granted to Users or Roles

27.10.2 Viewing Plan Information

27.10.3 Viewing Current Consumer Groups for Sessions

27.10.4 Viewing the Currently Active Plans

27.11 Monitoring Oracle Database Resource Manager

27.12 Interacting with Operating-System Resource Control

27.12.1 Guidelines for Using Operating-System Resource Control

27.13 Oracle Database Resource Manager Reference

27.13.1 Predefined Resource Plans and Consumer Groups

27.13.2 Predefined Consumer Group Mapping Rules

27.13.3 Resource Manager Data Dictionary Views

28 Oracle Scheduler Concepts

28.1 Overview of Oracle Scheduler

28.2 Jobs and Supporting Scheduler Objects

28.2.1 About Jobs and Supporting Scheduler Objects

28.2.2 Programs

28.2.3 Schedules

28.2.4 Jobs

28.2.4.1 About Jobs

28.2.4.2 Specifying a Job Action

28.2.4.3 Specifying a Job Schedule

28.2.4.4 Specifying a Job Destination

28.2.4.5 Specifying a Job Credential

28.2.5 Destinations

28.2.5.1 About Destinations

28.2.5.2 About Destinations and Scheduler Agents

28.2.5.2.1 External Destinations

28.2.5.2.2 Database Destinations

28.2.6 File Watchers

28.2.7 Credentials

28.2.8 Chains

28.2.9 Job Classes

28.2.10 Windows

28.2.10.1 About Windows

28.2.10.2 Overlapping Windows

28.2.10.2.1 ☆ example s of Overlapping Windows

28.2.11 Groups

28.2.11.1 About Groups

28.2.11.2 Destination Groups

28.2.11.3 Window Groups

28.2.12 Incompatibilities

28.3 More About Jobs

28.3.1 Job Categories

28.3.1.1 Database Jobs

28.3.1.1.1 About Database Jobs

28.3.1.1.2 Local Database Jobs

28.3.1.1.3 Remote Database Job

28.3.1.2 External Jobs

28.3.1.2.1 About External Jobs

28.3.1.2.2 About Local External Jobs

28.3.1.2.3 About Remote External Jobs

28.3.1.3 Multiple-Destination Jobs

28.3.1.4 Chain Jobs

28.3.1.5 Detached Jobs

28.3.1.6 Lightweight Jobs

28.3.1.7 In-Memory Jobs

28.3.1.8 Script Jobs

28.3.2 Job Instances

28.3.3 Job Arguments

28.3.4 How Programs, Jobs, and Schedules are Related

28.4 Scheduler Architecture

28.4.1 Scheduler Components

28.4.2 The Job Table

28.4.3 The Job Coordinator

28.4.3.1 About The Job Coordinator

28.4.3.2 Job Coordinator Actions

28.4.3.3 Maximum Number of Scheduler Job Processes

28.4.4 How Jobs Execute

28.4.5 After Jobs Complete

28.4.6 Using the Scheduler in Real Application Clusters Environments

28.4.6.1 The Scheduler and Real Application Clusters

28.4.6.2 Service Affinity when Using the Scheduler

28.5 Scheduler Support for Oracle Data Guard

29 Scheduling Jobs with Oracle Scheduler

29.1 About Scheduler Objects and Their Naming

29.2 Creating, Running, and Managing Jobs

29.2.1 Job Tasks and Their Procedures

29.2.2 Creating Jobs

29.2.2.1 Overview of Creating Jobs

29.2.2.2 Specifying Job Actions, Schedules, Programs, and Styles

29.2.2.2.1 Creating Jobs Using a Named Program

29.2.2.2.2 Creating Jobs Using a Named Program and Job Styles

29.2.2.2.3 Creating Jobs Using a Named Schedule

29.2.2.2.4 Creating Jobs Using Named Programs and Schedules

29.2.2.3 Specifying Scheduler Job Credentials

29.2.2.4 Specifying Destinations

29.2.2.4.1 Destination Tasks and Their Procedures

29.2.2.4.2 Creating Destinations

29.2.2.4.3 Creating Destination Groups for Multiple-Destination Jobs

29.2.2.4.4 ☆ example : Creating a Remote Database Job

29.2.2.5 Creating Multiple-Destination Jobs

29.2.2.6 Setting Job Arguments

29.2.2.7 Setting Additional Job Attributes

29.2.2.8 Creating Detached Jobs

29.2.2.9 Creating Multiple Jobs in a Single Transaction

29.2.2.10 Techniques for External Jobs

29.2.3 Altering Jobs

29.2.4 Running Jobs

29.2.5 Stopping Jobs

29.2.6 Stopping External Jobs

29.2.7 Stopping a Chain Job

29.2.8 Dropping Jobs

29.2.9 Dropping Running Jobs

29.2.10 Dropping Multiple Jobs

29.2.11 Disabling Jobs

29.2.12 Enabling Jobs

29.2.13 Copying Jobs

29.3 Creating and Managing Programs to Define Jobs

29.3.1 Program Tasks and Their Procedures

29.3.2 Creating Programs with Scheduler

29.3.2.1 Creating Programs

29.3.2.2 Defining Program Arguments

29.3.3 Altering Programs

29.3.4 Dropping Programs

29.3.5 Disabling Programs

29.3.6 Enabling Programs

29.4 Creating and Managing Schedules to Define Jobs

29.4.1 Schedule Tasks and Their Procedures

29.4.2 Creating Schedules

29.4.3 Altering Schedules

29.4.4 Dropping Schedules

29.4.5 Setting the Repeat Interval

29.4.5.1 About Setting the Repeat Interval

29.4.5.2 Using the Scheduler Calendaring Syntax

29.4.5.3 Using a PL/SQL Expression

29.4.5.4 Differences Between PL/SQL Expression and Calendaring Syntax Behavior

29.4.5.5 Repeat Intervals and Daylight Savings

29.5 Using Events to Start Jobs

29.5.1 About Events

29.5.2 Starting Jobs with Events Raised by Your Application

29.5.2.1 About Events Raised by Your Application

29.5.2.2 Creating an Event-Based Job

29.5.2.2.1 Specifying Event Information as Job Attributes

29.5.2.2.2 Specifying Event Information in an Event Schedule

29.5.2.3 Altering an Event-Based Job

29.5.2.4 Creating an Event Schedule

29.5.2.5 Altering an Event Schedule

29.5.2.6 Passing Event Messages into an Event-Based Job

29.5.3 Starting a Job When a File Arrives on a System

29.5.3.1 About File Watchers

29.5.3.2 Enabling File Arrival Events from Remote Systems

29.5.3.3 Creating File Watchers and File Watcher Jobs

29.5.3.4 File Arrival ☆ example

29.5.3.5 Managing File Watchers

29.5.3.5.1 Enabling File Watchers

29.5.3.5.2 Altering File Watchers

29.5.3.5.3 Disabling and Dropping File Watchers

29.5.3.5.4 Changing the File Arrival Detection Interval

29.5.3.6 Viewing File Watcher Information

29.6 Creating and Managing Job Chains

29.6.1 About Creating and Managing Job Chains

29.6.2 Chain Tasks and Their Procedures

29.6.3 Creating Chains

29.6.4 Defining Chain Steps

29.6.5 Adding Rules to a Chain

29.6.6 Setting an Evaluation Interval for Chain Rules

29.6.7 Enabling Chains

29.6.8 Creating Jobs for Chains

29.6.9 Dropping Chains

29.6.10 Running Chains

29.6.11 Dropping Chain Rules

29.6.12 Disabling Chains

29.6.13 Dropping Chain Steps

29.6.14 Stopping Chains

29.6.15 Stopping Individual Chain Steps

29.6.16 Pausing Chains

29.6.17 Skipping Chain Steps

29.6.18 Running Part of a Chain

29.6.19 Monitoring Running Chains

29.6.20 Handling Stalled Chains

29.7 Using Incompatibility Definitions

29.7.1 Creating a Job or Program Incompatibility

29.7.2 Adding a Job or Program to an Incompatibility

29.7.3 Removing a Job or Program from an Incompatibility

29.7.4 Dropping an Incompatibility

29.8 Managing Job Resources

29.8.1 Creating or Dropping a Resource

29.8.2 Altering a Resource

29.8.3 Setting a Resource Constraint for a Job

29.9 Prioritizing Jobs

29.9.1 Managing Job Priorities with Job Classes

29.9.1.1 Job Class Tasks and Their Procedures

29.9.1.2 Creating Job Classes

29.9.1.3 Altering Job Classes

29.9.1.4 Dropping Job Classes

29.9.2 Setting Relative Job Priorities Within a Job Class

29.9.3 Managing Job Scheduling and Job Priorities with Windows

29.9.3.1 About Job Scheduling and Job Priorities with Windows

29.9.3.2 Window Tasks and Their Procedures

29.9.3.3 Creating Windows

29.9.3.4 Altering Windows

29.9.3.5 Opening Windows

29.9.3.6 Closing Windows

29.9.3.7 Dropping Windows

29.9.3.8 Disabling Windows

29.9.3.9 Enabling Windows

29.9.4 Managing Job Scheduling and Job Priorities with Window Groups

29.9.4.1 Window Group Tasks and Their Procedures

29.9.4.2 Creating Window Groups

29.9.4.3 Dropping Window Groups

29.9.4.4 Adding a Member to a Window Group

29.9.4.5 Removing a Member from a Window Group

29.9.4.6 Enabling a Window Group

29.9.4.7 Disabling a Window Group

29.9.5 Allocating Resources Among Jobs Using Resource Manager

29.9.6 ☆ example of Resource Allocation for Jobs

29.10 Monitoring Jobs

29.10.1 About Monitoring Jobs

29.10.2 The Job Log

29.10.2.1 Viewing the Job Log

29.10.2.2 Run Details

29.10.2.3 Precedence of Logging Levels in Jobs and Job Classes

29.10.3 Monitoring Multiple Destination Jobs

29.10.4 Monitoring Job State with Events Raised by the Scheduler

29.10.4.1 About Job State Events

29.10.4.2 Altering a Job to Raise Job State Events

29.10.4.3 Consuming Job State Events with your Application

29.10.5 Monitoring Job State with E-mail Notifications

29.10.5.1 About E-mail Notifications

29.10.5.2 Adding E-mail Notifications for a Job

29.10.5.3 Removing E-mail Notifications for a Job

29.10.5.4 Viewing Information About E-mail Notifications

30 Administering Oracle Scheduler

30.1 Configuring Oracle Scheduler

30.1.1 Setting Oracle Scheduler Privileges

30.1.2 Setting Scheduler Preferences

30.1.3 Using the Oracle Scheduler Agent to Run Remote Jobs

30.1.3.1 Enabling and Disabling Databases for Remote Jobs

30.1.3.1.1 Setting up Databases for Remote Jobs

30.1.3.1.2 Disabling Remote Jobs

30.1.3.2 Installing and Configuring the Scheduler Agent on a Remote Host

30.1.3.3 Performing Tasks with the Scheduler Agent

30.1.3.3.1 About the schagent Utility

30.1.3.3.2 Using the Scheduler Agent on Windows

30.1.3.3.3 Starting the Scheduler Agent

30.1.3.3.4 Stopping the Scheduler Agent

30.1.3.3.5 Registering Scheduler Agents with Databases

30.2 Monitoring and Managing the Scheduler

30.2.1 Viewing the Currently Active Window and Resource Plan

30.2.2 Finding Information About Currently Running Jobs

30.2.3 Monitoring and Managing Window and Job Logs

30.2.3.1 Job Log

30.2.3.2 Window Log

30.2.3.3 Purging Logs

30.2.4 Managing Scheduler Security

30.3 Import/Export and the Scheduler

30.4 Troubleshooting the Scheduler

30.4.1 A Job Does Not Run

30.4.1.1 About Job States

30.4.1.1.1 Failed Jobs

30.4.1.1.2 Broken Jobs

30.4.1.1.3 Disabled Jobs

30.4.1.1.4 Completed Jobs

30.4.1.2 Viewing the Job Log

30.4.1.3 Troubleshooting Remote Jobs

30.4.1.4 About Job Recovery After a Failure

30.4.2 A Program Becomes Disabled

30.4.3 A Window Fails to Take Effect

30.5 ☆ example s of Using the Scheduler

30.5.1 ☆ example s of Creating Job Classes

30.5.2 ☆ example s of Setting Attributes

30.5.3 ☆ example s of Creating Chains

30.5.4 ☆ example s of Creating Jobs and Schedules Based on Events

30.5.5 ☆ example of Creating a Job In an Oracle Data Guard Environment

30.6 Scheduler Reference

30.6.1 Scheduler Privileges

30.6.2 Scheduler Data Dictionary Views

Part V Distributed Database Management

31 Distributed Database Concepts

31.1 Distributed Database Architecture

31.1.1 Homogenous Distributed Database Systems

31.1.1.1 About Homogenous Distributed Database Systems

31.1.1.2 Distributed Databases Versus Distributed Processing

31.1.1.3 Distributed Databases Versus Replicated Databases

31.1.2 Heterogeneous Distributed Database Systems

31.1.2.1 About Heterogeneous Distributed Database Systems

31.1.2.2 Heterogeneous Services

31.1.2.3 Transparent Gateway Agents

31.1.2.4 Generic Connectivity

31.1.3 Client/Server Database Architecture

31.2 Database Links

31.2.1 What Are Database Links?

31.2.2 What Are Shared Database Links?

31.2.3 Why Use Database Links?

31.2.4 Global Database Names in Database Links

31.2.5 Global Name as a Loopback Database Link

31.2.6 Names for Database Links

31.2.7 Types of Database Links

31.2.8 Users of Database Links

31.2.8.1 Overview of Database Link Users

31.2.8.2 Connected User Database Links

31.2.8.3 Fixed User Database Links

31.2.8.4 Current User Database Links

31.2.9 Creation of Database Links: ☆ example s

31.2.10 Schema Objects and Database Links

31.2.10.1 Naming of Schema Objects Using Database Links

31.2.10.2 Authorization for Accessing Remote Schema Objects

31.2.10.3 Synonyms for Schema Objects

31.2.10.4 Schema Object Name Resolution

31.2.11 Database Link Restrictions

31.3 Distributed Database Administration

31.3.1 Site Autonomy

31.3.2 Distributed Database Security

31.3.2.1 Authentication Through Database Links

31.3.2.2 Authentication Without Passwords

31.3.2.3 Supporting User Accounts and Roles

31.3.2.4 Centralized User and Privilege Management

31.3.2.4.1 About Centralized User and Privilege Management

31.3.2.4.2 Exclusively Mapped Global Users

31.3.2.4.3 Shared Schema Users

31.3.2.5 Data Encryption

31.3.3 Auditing Database Links

31.3.4 Administration Tools

31.3.4.1 Cloud Control and Distributed Databases

31.3.4.2 Third-Party Administration Tools

31.3.4.3 SNMP Support

31.4 Transaction Processing in a Distributed System

31.4.1 Remote SQL Statements

31.4.2 Distributed SQL Statements

31.4.3 Shared SQL for Remote and Distributed Statements

31.4.4 Remote Transactions

31.4.5 Distributed Transactions

31.4.6 Two-Phase Commit Mechanism

31.4.7 Database Link Name Resolution

31.4.7.1 About Database Link Name Resolution

31.4.7.2 Name Resolution When the Global Database Name Is Complete

31.4.7.3 Name Resolution When the Global Database Name Is Partial

31.4.7.4 Name Resolution When No Global Database Name Is Specified

31.4.7.5 Terminating the Search for Name Resolution

31.4.8 Schema Object Name Resolution

31.4.8.1 About Schema Object Name Resolution

31.4.8.2 ☆ example of Global Object Name Resolution: Complete Object Name

31.4.8.3 ☆ example of Global Object Name Resolution: Partial Object Name

31.4.9 Global Name Resolution in Views, Synonyms, and Procedures

31.4.9.1 About Global Name Resolution in Views, Synonyms, and Procedures

31.4.9.2 What Happens When Global Names Change

31.4.9.3 Scenarios for Global Name Changes

31.4.9.3.1 Scenario 1: Both Databases Change Names

31.4.9.3.2 Scenario 2: One Database Changes Names

31.5 Distributed Database Application Development

31.5.1 Transparency in a Distributed Database System

31.5.1.1 Location Transparency

31.5.1.2 SQL and COMMIT Transparency

31.5.2 PL/SQL and Remote Procedure Calls (RPCs)

31.5.3 Distributed Query Optimization

31.6 Character Set Support for Distributed Environments

31.6.1 About Character Set Support for Distributed Environments

31.6.2 Client/Server Environment

31.6.3 Homogeneous Distributed Environment

31.6.4 Heterogeneous Distributed Environment

32 Managing a Distributed Database

32.1 Managing Global Names in a Distributed System

32.1.1 Understanding How Global Database Names Are Formed

32.1.2 Determining Whether Global Naming Is Enforced

32.1.3 Viewing a Global Database Name

32.1.4 Changing the Domain in a Global Database Name

32.1.5 Changing a Global Database Name: Scenario

32.2 Creating Database Links

32.2.1 Obtaining Privileges Necessary for Creating Database Links

32.2.2 Specifying Link Types

32.2.2.1 Creating Private Database Links

32.2.2.2 Creating Public Database Links

32.2.2.3 Creating Global Database Links

32.2.3 Specifying Link Users

32.2.3.1 Creating Fixed User Database Links

32.2.3.2 Creating Connected User and Current User Database Links

32.2.3.2.1 Creating a Connected User Database Link

32.2.3.2.2 Creating a Current User Database Link

32.2.4 Using Connection Qualifiers to Specify Service Names Within Link Names

32.3 Using Shared Database Links

32.3.1 Determining Whether to Use Shared Database Links

32.3.2 Creating Shared Database Links

32.3.3 Configuring Shared Database Links

32.3.3.1 Creating Shared Links to Dedicated Servers

32.3.3.2 Creating Shared Links to Shared Servers

32.4 Managing Database Links

32.4.1 Closing Database Links

32.4.2 Dropping Database Links

32.4.2.1 Dropping a Private Database Link

32.4.2.2 Dropping a Public Database Link

32.4.3 Limiting the Number of Active Database Link Connections

32.5 Viewing Information About Database Links

32.5.1 Determining Which Links Are in the Database

32.5.2 Determining Which Link Connections Are Open

32.5.3 Determining the Host of Outgoing Database Links

32.5.4 Determining Information About Incoming Database Links

32.5.5 Determining the Source of High SCN Activity for Incoming Database Links

32.6 Creating Location Transparency

32.6.1 Using Views to Create Location Transparency

32.6.2 Using Synonyms to Create Location Transparency

32.6.2.1 Creating Synonyms

32.6.2.2 Managing Privileges and Synonyms

32.6.3 Using Procedures to Create Location Transparency

32.6.3.1 Using Local Procedures to Reference Remote Data

32.6.3.2 Using Local Procedures to Call Remote Procedures

32.6.3.3 Using Local Synonyms to Reference Remote Procedures

32.6.3.4 Managing Procedures and Privileges

32.7 Managing Statement Transparency

32.8 Managing a Distributed Database: ☆ example s

32.8.1 ☆ example 1: Creating a Public Fixed User Database Link

32.8.2 ☆ example 2: Creating a Public Fixed User Shared Database Link

32.8.3 ☆ example 3: Creating a Public Connected User Database Link

32.8.4 ☆ example 4: Creating a Public Connected User Shared Database Link

32.8.5 ☆ example 5: Creating a Public Current User Database Link

33 Developing Applications for a Distributed Database System

33.1 Managing the Distribution of Application Data

33.2 Controlling Connections Established by Database Links

33.3 Maintaining Referential Integrity in a Distributed System

33.4 Tuning Distributed Queries

33.4.1 Using Collocated Inline Views

33.4.2 Using Cost-Based Optimization

33.4.2.1 How Does Cost-Based Optimization Work?

33.4.2.2 Rewriting Queries for Cost-Based Optimization

33.4.2.3 Setting Up Cost-Based Optimization

33.4.2.3.1 Setting Up the Environment

33.4.2.3.2 Analyzing Tables

33.4.3 Using Hints

33.4.3.1 About Using Hints

33.4.3.2 Using the NO_MERGE Hint

33.4.3.3 Using the DRIVING_SITE Hint

33.4.4 Analyzing the Execution Plan

33.4.4.1 Generating the Execution Plan

33.4.4.2 Viewing the Execution Plan

33.5 Handling Errors in Remote Procedures

34 Distributed Transactions Concepts

34.1 What Are Distributed Transactions?

34.1.1 DML and DDL Transactions

34.1.2 Transaction Control Statements

34.2 Session Trees for Distributed Transactions

34.2.1 About Session Trees for Distributed Transactions

34.2.2 Clients

34.2.3 Database Servers

34.2.4 Local Coordinators

34.2.5 Global Coordinator

34.2.6 Commit Point Site

34.2.6.1 About the Commit Point Site

34.2.6.2 How a Distributed Transaction Commits

34.2.6.3 Commit Point Strength

34.3 Two-Phase Commit Mechanism

34.3.1 About the Two-Phase Commit Mechanism

34.3.2 Prepare Phase

34.3.2.1 About Prepare Phase

34.3.2.2 Types of Responses in the Prepare Phase

34.3.2.2.1 Prepared Response

34.3.2.2.2 Read-Only Response

34.3.2.2.3 Abort Response

34.3.2.3 Steps in the Prepare Phase

34.3.3 Commit Phase

34.3.3.1 Steps in the Commit Phase

34.3.3.2 Guaranteeing Global Database Consistency

34.3.4 Forget Phase

34.4 In-Doubt Transactions

34.4.1 About In-Doubt Transactions

34.4.2 Automatic Resolution of In-Doubt Transactions

34.4.2.1 Failure During the Prepare Phase

34.4.2.2 Failure During the Commit Phase

34.4.3 Manual Resolution of In-Doubt Transactions

34.4.4 Relevance of System Change Numbers for In-Doubt Transactions

34.5 Distributed Transaction Processing: Case Study

34.5.1 About the Distributed Transaction Processing Case Study

34.5.2 Stage 1: Client Application Issues DML Statements

34.5.3 Stage 2: Oracle Database Determines Commit Point Site

34.5.4 Stage 3: Global Coordinator Sends Prepare Response

34.5.5 Stage 4: Commit Point Site Commits

34.5.6 Stage 5: Commit Point Site Informs Global Coordinator of Commit

34.5.7 Stage 6: Global and Local Coordinators Tell All Nodes to Commit

34.5.8 Stage 7: Global Coordinator and Commit Point Site Complete the Commit

35 Managing Distributed Transactions

35.1 Specifying the Commit Point Strength of a Node

35.2 Naming Transactions

35.3 Viewing Information About Distributed Transactions

35.3.1 Determining the ID Number and Status of Prepared Transactions

35.3.2 Tracing the Session Tree of In-Doubt Transactions

35.4 Deciding How to Handle In-Doubt Transactions

35.4.1 Discovering Problems with a Two-Phase Commit

35.4.2 Determining Whether to Perform a Manual Override

35.4.3 Analyzing the Transaction Data

35.4.3.1 Find a Node that Committed or Rolled Back

35.4.3.2 Look for Transaction Comments

35.4.3.3 Look for Transaction Advice

35.5 Manually Overriding In-Doubt Transactions

35.5.1 Manually Committing an In-Doubt Transaction

35.5.1.1 Privileges Required to Commit an In-Doubt Transaction

35.5.1.2 Committing Using Only the Transaction ID

35.5.1.3 Committing Using an SCN

35.5.2 Manually Rolling Back an In-Doubt Transaction

35.6 Purging Pending Rows from the Data Dictionary

35.6.1 About Purging Pending Rows from the Data Dictionary

35.6.2 Executing the PURGE_LOST_DB_ENTRY Procedure

35.6.3 Determining When to Use DBMS_TRANSACTION

35.7 Manually Committing an In-Doubt Transaction: ☆ example

35.7.1 Step 1: Record User Feedback

35.7.2 Step 2: Query DBA_2PC_PENDING

35.7.2.1 Determining the Global Transaction ID

35.7.2.2 Determining the State of the Transaction

35.7.2.3 Looking for Comments or Advice

35.7.3 Step 3: Query DBA_2PC_NEIGHBORS on Local Node

35.7.3.1 Obtaining Database Role and Database Link Information

35.7.3.2 Determining the Commit Point Site

35.7.4 Step 4: Querying Data Dictionary Views on All Nodes

35.7.4.1 Checking the Status of Pending Transactions at sales

35.7.4.2 Determining the Coordinators and Commit Point Site at sales

35.7.4.3 Checking the Status of Pending Transactions at HQ

35.7.5 Step 5: Commit the In-Doubt Transaction

35.7.6 Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING

35.8 Data Access Failures Due to Locks

35.8.1 Transaction Timeouts

35.8.2 Locks from In-Doubt Transactions

35.9 Simulating Distributed Transaction Failure

35.9.1 Forcing a Distributed Transaction to Fail

35.9.2 Disabling and Enabling RECO

35.10 Managing Read Consistency

Part VI Managing Read-Only Materialized Views

36 Read-Only Materialized View Concepts

36.1 Replication Databases

36.2 Read-Only Materialized Views

36.3 The Uses of Materialized Views

36.3.1 Ease Network Loads

36.3.2 Enable Data Subsetting

36.3.3 Enable Disconnected Computing

36.4 Available Materialized Views

36.4.1 About the Available Materialized Views

36.4.2 Primary Key Materialized Views

36.4.3 Object Materialized Views

36.4.4 ROWID Materialized Views

36.4.5 Complex Materialized Views

36.4.5.1 About Complex Materialized Views

36.4.5.2 A Comparison of Simple and Complex Materialized Views

36.5 Users and Privileges Related to Materialized Views

36.5.1 Required Privileges for Materialized View Operations

36.5.2 Creator Is Owner

36.5.3 Creator Is Not Owner

36.5.4 Refresher Is Owner

36.5.5 Refresher Is Not Owner

36.6 Data Subsetting with Materialized Views

36.6.1 About Data Subsetting with Materialized Views

36.6.2 Materialized Views with Subqueries

36.6.2.1 Many to One Subqueries

36.6.2.2 One to Many Subqueries

36.6.2.3 Many to Many Subqueries

36.6.2.4 Materialized Views with Subqueries and Unions

36.6.3 Restrictions for Materialized Views with Subqueries

36.6.4 Restrictions for Materialized Views with Unions Containing Subqueries

36.6.4.1 ☆ example s of Materialized Views with Unions Containing Subqueries

36.7 Materialized View Refresh

36.8 Refresh Groups

36.9 Materialized View Log

36.10 Materialized Views and User-Defined Data Types

36.10.1 How Materialized Views Work with Object Types and Collections

36.10.2 Type Agreement at Replication Databases

36.10.3 Column Subsetting of Masters with Column Objects

36.10.4 Materialized Views Based on Object Tables

36.10.4.1 About Materialized Views Based on Object Tables

36.10.4.2 Materialized Views Based on Object Tables Created Without Using the OF type Clause

36.10.4.3 OID Preservation in Object Materialized Views

36.10.5 Materialized Views with Collection Columns

36.10.5.1 Restrictions for Materialized Views with Collection Columns

36.10.6 Materialized Views with REF Columns

36.10.6.1 About Materialized Views with REF Columns

36.10.6.2 Scoped REF Columns

36.10.6.3 Unscoped REF Columns

36.10.6.4 Logging REF Columns in the Materialized View Log

36.10.6.5 REFs Created Using the WITH ROWID Clause

36.11 Materialized View Registration at a Master Database

36.11.1 Viewing Information about Registered Materialized Views

36.11.2 Internal Mechanisms

36.11.3 Manual Materialized View Registration

37 Read-Only Materialized View Architecture

37.1 Master Database Mechanisms

37.1.1 Master Database Objects

37.1.2 Master Table

37.1.3 Internal Trigger for the Materialized View Log

37.1.4 Materialized View Logs

37.1.4.1 About Materialized View Logs

37.1.4.2 Columns Logged in the Materialized View Log

37.1.4.3 Restriction on Import of Materialized View Logs to a Different Schema

37.2 Materialized View Database Mechanisms

37.2.1 Indexes for Materialized Views

37.3 Organizational Mechanisms

37.3.1 Refresh Groups

37.3.2 Refresh Group Size

37.4 Refresh Process

37.4.1 About the Refresh Process

37.4.2 Refresh Types

37.4.2.1 Complete Refresh

37.4.2.2 Fast Refresh

37.4.2.3 Force Refresh

37.4.3 Initiating a Refresh

37.4.3.1 Scheduled Refresh

37.4.3.2 On-Demand Refresh

37.4.4 Constraints and Refresh

38 Planning for Read-Only Materialized Views

38.1 Considerations for Master Tables

38.1.1 Primary Keys and Master Tables

38.1.2 Foreign Keys and Master Tables

38.1.3 Data Type Considerations for Master Tables

38.1.4 Unsupported Table Types

38.2 Planning for Master Databases and Materialized View Databases

38.2.1 Characteristics of Master Databases and Materialized View Databases

38.2.2 Advantages of Master Databases

38.2.3 Advantages of Materialized View Databases

38.2.4 Preparing for Materialized Views

38.2.4.1 Required Schemas at Materialized View Database

38.2.4.2 Required Database Links for Materialized Views

38.2.4.3 Required Privileges

38.2.4.4 Sufficient Job Processes

38.2.5 Creating Materialized View Logs

38.2.6 Logging Columns in a Materialized View Log

39 Creating and Managing Read-Only Materialized Views

39.1 Creating Read-Only Materialized Views

39.2 Creating Refresh Groups

39.3 Refreshing Materialized Views

39.4 Determining the Fast Refresh Capabilities of a Materialized View

39.5 Adding a New Materialized View Database

39.6 Monitoring Materialized View Logs

39.6.1 Listing Information About the Materialized View Logs at a Master Database

39.6.2 Listing the Materialized Views that Use a Materialized View Log

39.7 Monitoring Materialized Views

39.7.1 Listing Information About Materialized Views

39.7.1.1 Listing Master Database Information For Materialized Views

39.7.1.2 Listing the Properties of Materialized Views

39.7.2 Listing Information About the Refresh Groups at a Materialized View Database

39.7.3 Determining the Job ID for Each Refresh Job at a Materialized View Database

39.7.4 Determining Which Materialized Views Are Currently Refreshing

40 Troubleshooting Problems with Read-Only Materialized Views

40.1 Diagnosing Problems with Database Links

40.2 Problems Creating Materialized Views

40.3 Refresh Problems

40.3.1 Common Refresh Problems

40.3.2 Automatic Refresh Retries

40.3.3 Fast Refresh Errors at New Materialized View Databases

40.3.4 Materialized Views Continually Refreshing

40.3.5 Materialized View Logs Growing Too Large

40.4 Advanced Troubleshooting of Refresh Problems