Go to main content
1/59
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Administrator's Guide
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
New Features
Deprecated Features
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 Patches
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
Release Number Format
1.4.1.1
Major Database Release Number
1.4.1.2
Database Maintenance Release Number
1.4.1.3
Fusion Middleware Release Number
1.4.1.4
Component-Specific Release Number
1.4.1.5
Platform-Specific Release Number
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, and SYSKM
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
Expanding the Number of Database Password File Users
1.7.9
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
Meeting Creation Prerequisites
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.3.3.1
DBCA Examples
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 Users SYS and SYSTEM
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
Considerations After Creating a Database
2.9.1
Some Security Considerations
2.9.2
Transparent Data Encryption
2.9.3
A Secure External Password Store
2.9.4
Transaction Guard and Application Continuity
2.9.5
The Oracle Database Sample Schemas
2.10
Cloning a Database with CloneDB
2.10.1
About Cloning a Database with CloneDB
2.10.2
Cloning a Database with CloneDB
2.10.3
After Cloning a Database with CloneDB
2.11
Dropping a Database
2.12
Database Data Dictionary Views
2.1
Database Configuration Assistant Command Reference for Silent Mode
2.1.1
createDatabase
2.1.2
configureDatabase
2.1.3
createTemplateFromDB
2.1.4
createCloneTemplate
2.1.5
generateScripts
2.1.6
deleteDatabase
2.1.7
createPluggableDatabase
2.1.8
unplugDatabase
2.1.9
deletePluggableDatabase
2.1.10
configurePluggableDatabase
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
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
Examples
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
Configuring Oracle Database for Shared Server
5.3.1
Initialization Parameters for Shared Server
5.3.2
Memory Management for Shared Server
5.3.3
Enabling Shared Server
5.3.3.1
About Determining a Value for SHARED_SERVERS
5.3.3.2
Decreasing the Number of Shared Server Processes
5.3.3.3
Limiting the Number of Shared Server Processes
5.3.3.4
Limiting the Number of Shared Server Sessions
5.3.3.5
Protecting Shared Memory
5.3.4
Configuring Dispatchers
5.3.4.1
DISPATCHERS Initialization Parameter Attributes
5.3.4.2
Determining the Number of Dispatchers
5.3.4.3
Setting the Initial Number of Dispatchers
5.3.4.4
Altering the Number of Dispatchers
5.3.4.4.1
Notes on Altering Dispatchers
5.3.4.5
Shutting Down Specific Dispatcher Processes
5.3.5
Disabling Shared Server
5.3.6
Shared Server Data Dictionary Views
5.4
Configuring Database Resident Connection Pooling
5.4.1
Enabling Database Resident Connection Pooling
5.4.2
Configuring the Connection Pool for Database Resident Connection Pooling
5.4.2.1
Configuration Parameters for Database Resident Connection Pooling
5.4.3
Data Dictionary Views for Database Resident Connection Pooling
5.5
About Oracle Database Background Processes
5.6
Managing Processes for Parallel SQL Execution
5.6.1
About Parallel Execution Servers
5.6.2
Altering Parallel Execution for a Session
5.6.2.1
Disabling Parallel SQL Execution
5.6.2.2
Enabling Parallel SQL Execution
5.6.2.3
Forcing Parallel SQL Execution
5.7
Managing Processes for External Procedures
5.7.1
About External Procedures
5.7.2
DBA Tasks to Enable External Procedure Calls
5.8
Terminating Sessions
5.8.1
About Terminating Sessions
5.8.2
Identifying Which Session to Terminate
5.8.3
Terminating an Active Session
5.8.4
Terminating an Inactive Session
5.9
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.3.9.3
Extended Buffer Cache Mechanism
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
Using the In-Memory Column Store
6.7.1
About the IM Column Store
6.7.1.1
Overview of the IM Column Store
6.7.1.2
IM Column Store Compression Methods
6.7.1.3
IM Column Store Data Population Options
6.7.2
Initialization Parameters Related to the IM Column Store
6.7.3
Enabling the IM Column Store for a Database
6.7.4
Enabling and Disabling Tables for the IM Column Store
6.7.4.1
Examples of Enabling and Disabling the IM Column Store for Tables
6.7.5
Enabling and Disabling Tablespaces for the IM Column Store
6.7.6
Enabling and Disabling Materialized Views for the IM Column Store
6.7.7
Data Pump and the IM Column Store
6.7.8
Using IM Column Store In Enterprise Manager
6.7.8.1
Meeting Prerequisites for Using IM Column Store in Enterprise Manager
6.7.8.2
Using the In-Memory Column Store Central Home Page to Monitor In-Memory Support for Database Objects
6.7.8.3
Specifying In-Memory Details When Creating a Table or Partition
6.7.8.4
Viewing or Editing IM Column Store Details of a Table
6.7.8.5
Viewing or Editing IM Column Store Details of a Partition
6.7.8.6
Specifying IM Column Store Details During Tablespace Creation
6.7.8.7
Viewing and Editing IM Column Store Details of a Tablespace
6.7.8.8
Specifying IM Column Store Details During Materialized View Creation
6.7.8.9
Viewing or Editing IM Column Store Details of a Materialized View
6.8
Memory Management Reference
6.8.1
Platforms That Support Automatic Memory Management
6.8.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
9
Managing Diagnostic Data
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
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
Viewing Problems with the Support Workbench
9.4
Creating a User-Reported Problem
9.5
Viewing the Alert Log
9.6
Finding Trace Files
9.7
Running Health Checks with Health Monitor
9.7.1
About Health Monitor
9.7.1.1
About Health Monitor Checks
9.7.1.2
Types of Health Checks
9.7.2
Running Health Checks Manually
9.7.2.1
Running Health Checks Using the DBMS_HM PL/SQL Package
9.7.2.2
Running Health Checks Using Cloud Control
9.7.3
Viewing Checker Reports
9.7.3.1
About Viewing Checker Reports
9.7.3.2
Viewing Reports Using Cloud Control
9.7.3.3
Viewing Reports Using DBMS_HM
9.7.3.4
Viewing Reports Using the ADRCI Utility
9.7.4
Health Monitor Views
9.7.5
Health Check Parameters Reference
9.8
Repairing SQL Failures with the SQL Repair Advisor
9.8.1
About the SQL Repair Advisor
9.8.2
Running the SQL Repair Advisor
9.8.3
Viewing, Disabling, or Removing a SQL Patch
9.9
Repairing Data Corruptions with the Data Recovery Advisor
9.10
Creating, Editing, and Uploading Custom Incident Packages
9.10.1
Incident Packages
9.10.1.1
About Incident Packages
9.10.1.2
About Correlated Diagnostic Data in Incident Packages
9.10.1.3
About Quick Packaging and Custom Packaging
9.10.1.4
About Correlated Packages
9.10.2
Packaging and Uploading Problems with Custom Packaging
9.10.3
Viewing and Modifying Incident Packages
9.10.3.1
Viewing Package Details
9.10.3.2
Accessing the Customize Package Page
9.10.3.3
Editing Incident Package Files (Copying Out and In)
9.10.3.4
Adding an External File to an Incident Package
9.10.3.5
Removing Incident Package Files
9.10.3.6
Viewing and Updating the Incident Package Activity Log
9.10.4
Creating, Editing, and Uploading Correlated Packages
9.10.5
Deleting Correlated Packages
9.10.6
Setting Incident Packaging Preferences
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
Understanding Archive Destination Status
12.4.3
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
Compressed Tablespaces
13.2.4.1
About Compressed Tablespaces
13.2.4.2
Creating Compressed Tablespaces
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 the SYSAUX Tablespace
13.11.1
Monitoring Occupants of the SYSAUX Tablespace
13.11.2
Moving Occupants Out Of or Into the SYSAUX Tablespace
13.11.3
Controlling the Size of the SYSAUX Tablespace
13.12
Correcting Problems with Locally Managed Tablespaces
13.12.1
Diagnosing and Repairing Locally Managed Tablespace Problems
13.12.2
Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)
13.12.3
Scenario 2: Dropping a Corrupted Segment
13.12.4
Scenario 3: Fixing Bitmap Where Overlap is Reported
13.12.5
Scenario 4: Correcting Media Corruption of Bitmap Blocks
13.12.6
Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace
13.13
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
13.14
Viewing Information About Tablespaces
13.14.1
Tablespace Data Dictionary Views
13.14.2
Example 1: Listing Tablespaces and Default Storage Parameters
13.14.3
Example 2: Listing the Data Files and Associated Tablespaces of a Database
13.14.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 Examples
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: Examples
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: Examples
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
Examples 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.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
Automatically Collecting Statistics on Tables
20.6
Altering Tables
20.6.1
Reasons for Using the ALTER TABLE Statement
20.6.2
Altering Physical Attributes of a Table
20.6.3
Moving a Table to a New Segment or Tablespace
20.6.3.1
About Moving a Table to a New Segment or Tablespace
20.6.3.2
Moving a Table
20.6.3.3
Moving a Table Partition or Subpartition Online
20.6.4
Manually Allocating Storage for a Table
20.6.5
Modifying an Existing Column Definition
20.6.6
Adding Table Columns
20.6.7
Renaming Table Columns
20.6.8
Dropping Table Columns
20.6.8.1
Removing Columns from Tables
20.6.8.2
Marking Columns Unused
20.6.8.3
Removing Unused Columns
20.6.8.4
Dropping Columns in Compressed Tables
20.6.9
Placing a Table in Read-Only Mode
20.7
Redefining Tables Online
20.7.1
About Redefining Tables Online
20.7.2
Features of Online Table Redefinition
20.7.3
Performing Online Redefinition with the REDEF_TABLE Procedure
20.7.4
Redefining Tables Online with Multiple Procedures in DBMS_REDEFINITION
20.7.4.1
Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION
20.7.4.2
Constructing a Column Mapping String
20.7.4.3
Handling Virtual Private Database (VPD) Policies During Online Redefinition
20.7.4.4
Creating Dependent Objects Automatically
20.7.4.5
Creating Dependent Objects Manually
20.7.5
Results of the Redefinition Process
20.7.6
Performing Intermediate Synchronization
20.7.7
Aborting Online Table Redefinition and Cleaning Up After Errors
20.7.8
Restrictions for Online Redefinition of Tables
20.7.9
Online Redefinition of One or More Partitions
20.7.9.1
Rules for Online Redefinition of a Single Partition
20.7.10
Online Table Redefinition Examples
20.7.11
Privileges Required for the DBMS_REDEFINITION Package
20.8
Researching and Reversing Erroneous Table Changes
20.9
Recovering Tables Using Oracle Flashback Table
20.10
Dropping Tables
20.11
Using Flashback Drop and Managing the Recycle Bin
20.11.1
What Is the Recycle Bin?
20.11.2
Enabling and Disabling the Recycle Bin
20.11.3
Viewing and Querying Objects in the Recycle Bin
20.11.4
Purging Objects in the Recycle Bin
20.11.5
Restoring Tables from the Recycle Bin
20.12
Managing Index-Organized Tables
20.12.1
What Are Index-Organized Tables?
20.12.2
Creating Index-Organized Tables
20.12.2.1
About Creating Index-Organized Tables
20.12.2.2
Example: Creating an Index-Organized Table
20.12.2.3
Restrictions for Index-Organized Tables
20.12.2.4
Creating Index-Organized Tables That Contain Object Types
20.12.2.5
Choosing and Monitoring a Threshold Value
20.12.2.6
Using the INCLUDING Clause
20.12.2.7
Parallelizing Index-Organized Table Creation
20.12.2.8
Using Prefix Compression
20.12.3
Maintaining Index-Organized Tables
20.12.3.1
Altering Index-Organized Tables
20.12.3.2
Moving (Rebuilding) Index-Organized Tables
20.12.4
Creating Secondary Indexes on Index-Organized Tables
20.12.4.1
About Secondary Indexes on Index-Organized Tables
20.12.4.2
Creating a Secondary Index on an Index-Organized Table
20.12.4.3
Maintaining Physical Guesses in Logical Rowids
20.12.4.4
Specifying Bitmap Indexes on Index-Organized Tables
20.12.5
Analyzing Index-Organized Tables
20.12.5.1
Collecting Optimizer Statistics for Index-Organized Tables
20.12.5.2
Validating the Structure of Index-Organized Tables
20.12.6
Using the ORDER BY Clause with Index-Organized Tables
20.12.7
Converting Index-Organized Tables to Regular Tables
20.13
Managing External Tables
20.13.1
About External Tables
20.13.2
Creating External Tables
20.13.3
Altering External Tables
20.13.4
Preprocessing External Tables
20.13.5
Dropping External Tables
20.13.6
System and Object Privileges for External Tables
20.14
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
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.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 Examples
25.4.1
Examples: 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
Runaway Queries
27.3.4.1
Automatic Consumer Group Switching
27.3.4.2
Canceling SQL and Terminating Sessions
27.3.4.3
Execution Time Limit
27.3.5
Active Session Pool with Queuing
27.3.6
Undo Pool
27.3.7
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 Examples
27.7.1
Multilevel Plan Example
27.7.2
Examples 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
Examples 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.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
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
Prioritizing Jobs
29.7.1
Managing Job Priorities with Job Classes
29.7.1.1
Job Class Tasks and Their Procedures
29.7.1.2
Creating Job Classes
29.7.1.3
Altering Job Classes
29.7.1.4
Dropping Job Classes
29.7.2
Setting Relative Job Priorities Within a Job Class
29.7.3
Managing Job Scheduling and Job Priorities with Windows
29.7.3.1
About Job Scheduling and Job Priorities with Windows
29.7.3.2
Window Tasks and Their Procedures
29.7.3.3
Creating Windows
29.7.3.4
Altering Windows
29.7.3.5
Opening Windows
29.7.3.6
Closing Windows
29.7.3.7
Dropping Windows
29.7.3.8
Disabling Windows
29.7.3.9
Enabling Windows
29.7.4
Managing Job Scheduling and Job Priorities with Window Groups
29.7.4.1
Window Group Tasks and Their Procedures
29.7.4.2
Creating Window Groups
29.7.4.3
Dropping Window Groups
29.7.4.4
Adding a Member to a Window Group
29.7.4.5
Removing a Member from a Window Group
29.7.4.6
Enabling a Window Group
29.7.4.7
Disabling a Window Group
29.7.5
Allocating Resources Among Jobs Using Resource Manager
29.7.6
Example of Resource Allocation for Jobs
29.8
Monitoring Jobs
29.8.1
About Monitoring Jobs
29.8.2
The Job Log
29.8.2.1
Viewing the Job Log
29.8.2.2
Run Details
29.8.2.3
Precedence of Logging Levels in Jobs and Job Classes
29.8.3
Monitoring Multiple Destination Jobs
29.8.4
Monitoring Job State with Events Raised by the Scheduler
29.8.4.1
About Job State Events
29.8.4.2
Altering a Job to Raise Job State Events
29.8.4.3
Consuming Job State Events with your Application
29.8.5
Monitoring Job State with E-mail Notifications
29.8.5.1
About E-mail Notifications
29.8.5.2
Adding E-mail Notifications for a Job
29.8.5.3
Removing E-mail Notifications for a Job
29.8.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
Examples of Using the Scheduler
30.5.1
Examples of Creating Job Classes
30.5.2
Examples of Setting Attributes
30.5.3
Examples of Creating Chains
30.5.4
Examples 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: Examples
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
Schema-Dependent Global Users
31.3.2.4.3
Schema-Independent Global 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
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.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: Examples
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 a Multitenant Environment
36
Overview of Managing a Multitenant Environment
36.1
About a Multitenant Environment
36.1.1
Components of a CDB
36.1.2
Common Users and Local Users
36.1.3
Separation of Duties in CDB and PDB Administration
36.2
Purpose of a Multitenant Environment
36.3
Prerequisites for a Multitenant Environment
36.4
Tasks and Tools for a Multitenant Environment
36.4.1
Tasks for a Multitenant Environment
36.4.2
Tools for a Multitenant Environment
37
Creating and Configuring a CDB
37.1
About Creating a CDB
37.2
Planning for CDB Creation
37.2.1
Decide How to Configure the CDB
37.2.2
Prerequisites for CDB Creation
37.3
Using DBCA to Create a CDB
37.4
Using the CREATE DATABASE Statement to Create a CDB
37.4.1
About Creating a CDB with the CREATE DATABASE Statement
37.4.1.1
About Enabling PDBs
37.4.1.2
About the Names and Locations of the Root's Files and the Seed's Files
37.4.1.2.1
The SEED FILE_NAME_CONVERT Clause
37.4.1.2.2
Oracle Managed Files
37.4.1.2.3
The PDB_FILE_NAME_CONVERT Initialization Parameter
37.4.1.3
About the Attributes of the Seed's Data Files
37.4.2
Creating a CDB with the CREATE DATABASE Statement
37.5
Configuring EM Express for a CDB
37.6
After Creating a CDB
38
Creating and Removing PDBs with SQL*Plus
38.1
About Creating and Removing PDBs
38.1.1
Techniques for Creating a PDB
38.1.2
The CREATE PLUGGABLE DATABASE Statement
38.1.2.1
Storage Limits
38.1.2.2
File Location of the New PDB
38.1.2.2.1
FILE_NAME_CONVERT Clause
38.1.2.2.2
CREATE_FILE_DEST Clause
38.1.2.3
Restrictions on PDB File Locations
38.1.2.4
Source File Locations When Plugging In an Unplugged PDB
38.1.2.4.1
SOURCE_FILE_NAME_CONVERT Clause
38.1.2.4.2
SOURCE_FILE_DIRECTORY Clause
38.1.2.5
SERVICE_NAME_CONVERT Clause
38.1.2.6
Temp File Reuse
38.1.2.7
User Tablespaces
38.1.2.8
PDB Tablespace Logging
38.1.2.9
PDB Inclusion in Standby CDBs
38.1.2.10
Excluding Data When Cloning a PDB
38.2
Preparing for PDBs
38.3
Creating a PDB Using the Seed
38.3.1
About Creating a PDB from the Seed
38.3.2
Creating a PDB from the Seed
38.4
Creating a PDB by Cloning an Existing PDB or Non-CDB
38.4.1
About Cloning a PDB
38.4.2
Cloning a Local PDB
38.4.3
Cloning a Remote PDB or Non-CDB
38.4.4
After Cloning a PDB
38.5
Creating a PDB by Plugging an Unplugged PDB into a CDB
38.5.1
About Plugging In an Unplugged PDB
38.5.2
Plugging In an Unplugged PDB
38.5.3
After Plugging in an Unplugged PDB
38.6
Creating a PDB Using a Non-CDB
38.6.1
About Creating a PDB Using a Non-CDB
38.6.2
Using the DBMS_PDB Package on a Non-CDB
38.6.2.1
About Using the DBMS_PDB Package on a Non-CDB
38.6.2.2
Using the DBMS_PDB Package to Create an Unplugged PDB
38.7
Unplugging a PDB from a CDB
38.7.1
About Unplugging a PDB
38.7.2
Unplugging a PDB
38.8
Dropping a PDB
39
Creating and Removing PDBs with Cloud Control
39.1
Getting Started with Creating and Removing PDBs with Cloud Control
39.2
Overview of Creating and Removing PDBs with Cloud Control
39.3
Provisioning a PDB with Cloud Control
39.3.1
Creating a New PDB with Cloud Control
39.3.1.1
Prerequisites
39.3.1.2
Procedure
39.3.2
Plugging In an Unplugged PDB with Cloud Control
39.3.2.1
Prerequisites
39.3.2.2
Procedure
39.3.3
Cloning a PDB with Cloud Control
39.3.3.1
Prerequisites
39.3.3.2
Procedure
39.3.4
Migrating a Non-CDB to a PDB with Cloud Control
39.3.4.1
Prerequisites
39.3.4.2
Procedure
39.4
Removing PDBs with Cloud Control
39.4.1
Unplugging and Dropping a PDB with Cloud Control
39.4.1.1
Prerequisites
39.4.1.2
Procedure
39.4.2
Deleting PDBs with Cloud Control
39.4.2.1
Prerequisites
39.4.2.2
Procedure
39.5
Viewing PDB Job Details with Cloud Control
39.5.1
Viewing Create PDB Job Details with Cloud Control
39.5.2
Viewing Unplug PDB Job Details with Cloud Control
39.5.3
Viewing Delete PDB Job Details with Cloud Control
40
Administering a CDB with SQL*Plus
40.1
About Administering a CDB
40.1.1
About the Current Container
40.1.2
About Administrative Tasks in a CDB
40.1.3
About Using Manageability Features in a CDB
40.1.4
About Managing Database Objects in a CDB
40.2
Accessing a Container in a CDB with SQL*Plus
40.2.1
About Accessing a Container in a CDB with SQL*Plus
40.2.2
Connecting to a Container Using the SQL*Plus CONNECT Command
40.2.2.1
Connecting to the Root Using the SQL*Plus CONNECT Command
40.2.2.2
Connecting to a PDB Using the SQL*Plus CONNECT Command
40.2.3
Switching to a Container Using the ALTER SESSION Statement
40.3
Executing Code in Containers Using the DBMS_SQL Package
40.4
Modifying a CDB
40.4.1
About Modifying a CDB
40.4.2
About the Statements That Modify a CDB
40.4.3
Managing Tablespaces in a CDB
40.4.3.1
About Managing Tablespaces in a CDB
40.4.3.2
About Managing Permanent Tablespaces in a CDB
40.4.3.3
About Managing Temporary Tablespaces in a CDB
40.4.4
Modifying an Entire CDB
40.4.5
Modifying the Root
40.4.6
Modifying the Open Mode of PDBs
40.4.6.1
About the Open Mode of a PDB
40.4.6.2
About Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
40.4.6.3
About Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
40.4.6.4
Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
40.4.6.5
Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
40.4.7
Preserving or Discarding the Open Mode of PDBs When the CDB Restarts
40.5
Using the ALTER SYSTEM SET Statement in a CDB
40.6
Executing DDL Statements in a CDB
40.6.1
About Executing DDL Statements in a CDB
40.6.2
Executing a DDL Statement in the Current Container
40.6.3
Executing a DDL Statement in All Containers in a CDB
40.7
Running Oracle-Supplied SQL Scripts in a CDB
40.7.1
About Running Oracle-Supplied SQL Scripts in a CDB
40.7.2
Syntax and Parameters for catcon.pl
40.7.3
Running the catcon.pl Script
40.8
Shutting Down a CDB Instance
41
Administering CDBs and PDBs with Cloud Control
41.1
Administering CDB Storage and Schema Objects with Cloud Control
41.1.1
About Managing and Monitoring CDB Storage and Schema Objects
41.1.2
Managing CDB Storage and Schema Objects
41.1.3
Managing Per-Container Storage and Schema Objects
41.1.4
Monitoring Storage and Schema Alerts
41.2
Administering PDBs with Cloud Control
41.2.1
Switching Between PDBs
41.2.2
Altering the Open Mode of a PDB
42
Administering PDBs with SQL*Plus
42.1
About Administering PDBs
42.2
Connecting to a PDB with SQL*Plus
42.3
Modifying a PDB
42.3.1
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
42.3.1.1
About Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
42.3.1.2
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
42.3.1.3
Changing the Global Database Name of a PDB
42.3.2
Modifying a PDB with the SQL*Plus STARTUP and SHUTDOWN Commands
42.3.2.1
Using the STARTUP SQL*Plus Command on a PDB
42.3.2.2
Using the SQL*Plus SHUTDOWN Command on a PDB
42.4
Using the ALTER SYSTEM Statement to Modify a PDB
42.4.1
About Using the ALTER SYSTEM Statement on a PDB
42.4.2
Using the ALTER SYSTEM Statement on a PDB
42.5
Managing Services Associated with PDBs
42.5.1
About Services Associated with PDBs
42.5.2
Creating, Modifying, or Removing a Service for a PDB
43
Viewing Information About CDBs and PDBs with SQL*Plus
43.1
About CDB and PDB Information in Views
43.2
About Viewing Information When the Current Container Is a PDB
43.3
About Viewing Information When the Current Container Is the Root
43.4
Views for a CDB
43.5
Determining Whether a Database Is a CDB
43.6
Viewing Information About the Containers in a CDB
43.7
Viewing Information About PDBs
43.8
Viewing the Open Mode of Each PDB
43.9
Querying Container Data Objects
43.10
Querying User-Created Tables and Views Across All PDBs
43.11
Determining the Current Container ID or Name
43.12
Listing the Initialization Parameters That Are Modifiable in PDBs
43.13
Viewing the History of PDBs
44
Using Oracle Resource Manager for PDBs with SQL*Plus
44.1
About Using Oracle Resource Manager with CDBs and PDBs
44.1.1
About Managing Resources at the CDB Level and PDB Level
44.1.2
What Solutions Does Resource Manager Provide for a CDB?
44.1.3
CDB Resource Plans
44.1.3.1
About CDB Resource Plans
44.1.3.2
Shares for Allocating Resources to PDBs
44.1.3.3
Utilization Limits for PDBs
44.1.3.4
The Default Directive for PDBs
44.1.4
PDB Resource Plans
44.1.5
Background and Administrative Tasks and Consumer Groups
44.2
Prerequisites for Using Resource Manager with a CDB
44.3
Creating a CDB Resource Plan
44.3.1
Creating a CDB Resource Plan: A Scenario
44.4
Enabling and Disabling a CDB Resource Plan
44.4.1
Enabling a CDB Resource Plan
44.4.2
Disabling a CDB Resource Plan
44.5
Creating a PDB Resource Plan
44.6
Enabling and Disabling a PDB Resource Plan
44.6.1
Enabling a PDB Resource Plan
44.6.2
Disabling a PDB Resource Plan
44.7
Maintaining Plans and Directives in a CDB
44.7.1
Managing a CDB Resource Plan
44.7.1.1
Updating a CDB Resource Plan
44.7.1.2
Creating New CDB Resource Plan Directives for a PDB
44.7.1.3
Updating CDB Resource Plan Directives for a PDB
44.7.1.4
Deleting CDB Resource Plan Directives for a PDB
44.7.1.5
Updating the Default Directive for PDBs in a CDB Resource Plan
44.7.1.6
Updating the Default Directive for Maintenance Tasks in a CDB Resource Plan
44.7.1.7
Deleting a CDB Resource Plan
44.7.2
Modifying a PDB Resource Plan
44.8
Viewing Information About Plans and Directives in a CDB
44.8.1
Viewing CDB Resource Plans
44.8.2
Viewing CDB Resource Plan Directives
45
Using Oracle Resource Manager for PDBs with Cloud Control
45.1
About CDB Resource Manager and Cloud Control
45.2
Creating a CDB Resource Plan with Cloud Control
45.3
Creating a PDB Resource Plan with Cloud Control
46
Using Oracle Scheduler with a CDB
46.1
DBMS_SCHEDULER Invocations in a CDB
46.2
Job Coordinator and Slave Processes in a CDB
46.3
Using DBMS_JOB
46.4
Processes to Close a PDB
46.5
New and Changed Views
Appendixes
A
Support for DBMS_JOB
A.1
Oracle Scheduler Replaces DBMS_JOB
A.1.1
Configuring DBMS_JOB
A.1.2
Using Both DBMS_JOB and Oracle Scheduler
A.2
Moving from DBMS_JOB to Oracle Scheduler
A.2.1
Creating a Job
A.2.2
Altering a Job
A.2.3
Removing a Job from the Job Queue
Index
Scripting on this page enhances content navigation, but does not change the content in any way.