Go to main content
1/77
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 2 (12.2.0.1)
New Features
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, SYSKM, and SYSRAC
1.5.2.5
The DBA Role
1.6
Database Administrator Authentication
1.6.1
Administrative Privileges
1.6.2
Operations Authorized by Administrative Privileges
1.6.3
Authentication Methods for Database Administrators
1.6.3.1
About Authentication Methods for Database Administrators
1.6.3.2
Nonsecure Remote Connections
1.6.3.3
Local Connections and Secure Remote Connections
1.6.4
Using Operating System Authentication
1.6.4.1
Operating System Groups
1.6.4.2
Preparing to Use Operating System Authentication
1.6.4.3
Connecting Using Operating System Authentication
1.6.5
Using Password File Authentication
1.6.5.1
Preparing to Use Password File Authentication
1.6.5.2
Connecting Using Password File Authentication
1.7
Creating and Maintaining a Database Password File
1.7.1
ORAPWD Syntax and Command Line Argument Descriptions
1.7.2
Creating a Database Password File with ORAPWD
1.7.3
Sharing and Disabling the Database Password File
1.7.4
Keeping Administrator Passwords Synchronized with the Data Dictionary
1.7.5
Adding Users to a Database Password File
1.7.6
Granting and Revoking Administrative Privileges
1.7.7
Viewing Database Password File Members
1.7.8
Removing a Database Password File
1.8
Data Utilities
2
Creating and Configuring an Oracle Database
2.1
About Creating an Oracle Database
2.2
Considerations Before Creating the Database
2.2.1
Planning for Database Creation
2.2.2
About Selecting a Character Set
2.2.3
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
File System Server Support in the Database
2.9.6
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.13
Database Configuration Assistant Command Reference for Silent Mode
2.13.1
createDatabase
2.13.2
createDuplicateDB
2.13.3
configureDatabase
2.13.4
createTemplateFromDB
2.13.5
createCloneTemplate
2.13.6
generateScripts
2.13.7
deleteDatabase
2.13.8
createPluggableDatabase
2.13.9
unplugDatabase
2.13.10
deletePluggableDatabase
2.13.11
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
3.6
Delaying Instance Abort
4
Configuring Automatic Restart of an Oracle Database
4.1
About Oracle Restart
4.1.1
Oracle Restart Overview
4.1.2
About Startup Dependencies
4.1.3
About Starting and Stopping Components with Oracle Restart
4.1.4
About Starting and Stopping Oracle Restart
4.1.5
Oracle Restart Configuration
4.1.6
Oracle Restart Integration with Oracle Data Guard
4.1.7
Fast Application Notification with Oracle Restart
4.1.7.1
Overview of Fast Application Notification
4.1.7.2
Application High Availability with Services and FAN
4.1.7.2.1
Managing Unplanned Outages
4.1.7.2.2
Managing Planned Outages
4.1.7.2.3
Fast Application Notification High Availability Events
4.1.7.2.4
Using Fast Application Notification Callouts
4.1.7.2.5
Oracle Clients That Are Integrated with Fast Application Notification
4.2
Configuring Oracle Restart
4.2.1
About Configuring Oracle Restart
4.2.2
Preparing to Run SRVCTL
4.2.3
Obtaining Help for SRVCTL
4.2.4
Adding Components to the Oracle Restart Configuration
4.2.5
Removing Components from the Oracle Restart Configuration
4.2.6
Disabling and Enabling Oracle Restart Management for a Component
4.2.7
Viewing Component Status
4.2.8
Viewing the Oracle Restart Configuration for a Component
4.2.9
Modifying the Oracle Restart Configuration for a Component
4.2.10
Managing Environment Variables in the Oracle Restart Configuration
4.2.10.1
About Environment Variables in the Oracle Restart Configuration
4.2.10.2
Setting and Unsetting Environment Variables
4.2.10.3
Viewing Environment Variables
4.2.11
Creating and Deleting Database Services with SRVCTL
4.2.12
Enabling FAN Events in an Oracle Restart Environment
4.2.13
Automating the Failover of Connections Between Primary and Standby Databases
4.2.14
Enabling Clients for Fast Connection Failover
4.2.14.1
About Enabling Clients for Fast Connection Failover
4.2.14.2
Enabling Fast Connection Failover for JDBC Clients
4.2.14.3
Enabling Fast Connection Failover for Oracle Call Interface Clients
4.2.14.4
Enabling Fast Connection Failover for ODP.NET Clients
4.3
Starting and Stopping Components Managed by Oracle Restart
4.4
Stopping and Restarting Oracle Restart for Maintenance Operations
4.5
SRVCTL Command Reference for Oracle Restart
4.5.1
add
4.5.1.1
srvctl add asm
4.5.1.1.1
Syntax and Options
4.5.1.1.2
Example
4.5.1.2
srvctl add database
4.5.1.2.1
Syntax and Options
4.5.1.2.2
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.4.4
Determining the States of Connections in the Connection Pool
5.5
About Oracle Database Background Processes
5.6
Managing Prespawned Processes
5.6.1
About Managing Prespawned Processes
5.6.2
Managing Pools for Prespawned Processes
5.7
Managing Processes for Parallel SQL Execution
5.7.1
About Parallel Execution Servers
5.7.2
Altering Parallel Execution for a Session
5.7.2.1
Disabling Parallel SQL Execution
5.7.2.2
Enabling Parallel SQL Execution
5.7.2.3
Forcing Parallel SQL Execution
5.8
Managing Processes for External Procedures
5.8.1
About External Procedures
5.8.2
DBA Tasks to Enable External Procedure Calls
5.9
Terminating Sessions
5.9.1
About Terminating Sessions
5.9.2
Identifying Which Session to Terminate
5.9.3
Terminating an Active Session
5.9.4
Terminating an Inactive Session
5.10
Process and Session Data Dictionary Views
6
Managing Memory
6.1
About Memory Management
6.2
Memory Architecture Overview
6.3
Using Automatic Memory Management
6.3.1
About Automatic Memory Management
6.3.2
Enabling Automatic Memory Management
6.3.3
Monitoring and Tuning Automatic Memory Management
6.4
Configuring Memory Manually
6.4.1
About Manual Memory Management
6.4.2
Using Automatic Shared Memory Management
6.4.2.1
About Automatic Shared Memory Management
6.4.2.2
Components and Granules in the SGA
6.4.2.3
Setting Maximum SGA Size
6.4.2.4
Setting SGA Target Size
6.4.2.4.1
The SGA Target and Automatically Sized SGA Components
6.4.2.4.2
SGA and Virtual Memory
6.4.2.4.3
Monitoring and Tuning SGA Target Size
6.4.2.5
Enabling Automatic Shared Memory Management
6.4.2.6
Setting Minimums for Automatically Sized SGA Components
6.4.2.7
Dynamic Modification of SGA_TARGET
6.4.2.8
Modifying Parameters for Automatically Sized Components
6.4.2.9
Modifying Parameters for Manually Sized Components
6.4.3
Using Manual Shared Memory Management
6.4.3.1
About Manual Shared Memory Management
6.4.3.2
Enabling Manual Shared Memory Management
6.4.3.3
Setting the Buffer Cache Initialization Parameters
6.4.3.3.1
Example of Setting Block and Cache Sizes
6.4.3.3.2
Multiple Buffer Pools
6.4.3.4
Specifying the Shared Pool Size
6.4.3.4.1
The Result Cache and Shared Pool Size
6.4.3.5
Specifying the Large Pool Size
6.4.3.6
Specifying the Java Pool Size
6.4.3.7
Specifying the Streams Pool Size
6.4.3.8
Specifying the Result Cache Maximum Size
6.4.3.9
Specifying Miscellaneous SGA Initialization Parameters
6.4.3.9.1
Physical Memory
6.4.3.9.2
SGA Starting Address
6.4.4
Using Automatic PGA Memory Management
6.4.5
Using Manual PGA Memory Management
6.5
Using Force Full Database Caching Mode
6.5.1
About Force Full Database Caching Mode
6.5.2
Before Enabling Force Full Database Caching Mode
6.5.3
Enabling Force Full Database Caching Mode
6.5.4
Disabling Force Full Database Caching Mode
6.6
Configuring Database Smart Flash Cache
6.6.1
When to Configure Database Smart Flash Cache
6.6.2
Sizing Database Smart Flash Cache
6.6.3
Tuning Memory for Database Smart Flash Cache
6.6.4
Database Smart Flash Cache Initialization Parameters
6.6.5
Database Smart Flash Cache in an Oracle Real Applications Clusters Environment
6.7
Improving Query Performance with the In-Memory Column Store
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
8.3
Monitoring Quarantined Objects
8.3.1
About Object Quarantine
8.3.2
Viewing Quarantined Objects
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
Expanding Alternate Destinations with Log Archive Destination Groups
12.4.2.1
About Log Archive Destination Groups
12.4.2.2
Specifying Log Archive Destination Groups
12.4.3
Understanding Archive Destination Status
12.4.4
Specifying Alternate Destinations
12.5
About Log Transmission Modes
12.5.1
Normal Transmission Mode
12.5.2
Standby Transmission Mode
12.6
Managing Archive Destination Failure
12.6.1
Specifying the Minimum Number of Successful Destinations
12.6.1.1
Specifying Mandatory and Optional Destinations
12.6.1.2
Specifying the Number of Successful Destinations: Scenarios
12.6.1.2.1
Scenario for Archiving to Optional Local Destinations
12.6.1.2.2
Scenario for Archiving to Both Mandatory and Optional Destinations
12.6.2
Rearchiving to a Failed Destination
12.7
Controlling Trace Output Generated by the Archivelog Process
12.8
Viewing Information About the Archived Redo Log
12.8.1
Archived Redo Log Files Views
12.8.2
Using the ARCHIVE LOG LIST Command
13
Managing Tablespaces
13.1
Guidelines for Managing Tablespaces
13.1.1
Use Multiple Tablespaces
13.1.2
Assign Tablespace Quotas to Users
13.2
Creating Tablespaces
13.2.1
About Creating Tablespaces
13.2.2
Locally Managed Tablespaces
13.2.2.1
About Locally Managed Tablespaces
13.2.2.2
Creating a Locally Managed Tablespace
13.2.2.3
Specifying Segment Space Management in Locally Managed Tablespaces
13.2.3
Bigfile Tablespaces
13.2.3.1
About Bigfile Tablespaces
13.2.3.2
Creating a Bigfile Tablespace
13.2.3.3
Identifying a Bigfile Tablespace
13.2.4
Tablespaces with Default Compression Attributes
13.2.4.1
About Tablespaces with Default Compression Attributes
13.2.4.2
Creating Tablespaces with Default Compression Attributes
13.2.5
Encrypted Tablespaces
13.2.5.1
About Encrypted Tablespaces
13.2.5.2
Creating Encrypted Tablespaces
13.2.5.3
Viewing Information About Encrypted Tablespaces
13.2.6
Temporary Tablespaces
13.2.6.1
About Temporary Tablespaces
13.2.6.2
Creating a Locally Managed Temporary Tablespace
13.2.6.3
Creating a Bigfile Temporary Tablespace
13.2.6.4
Viewing Space Usage for Temporary Tablespaces
13.2.7
Temporary Tablespace Groups
13.2.7.1
Multiple Temporary Tablespaces: Using Tablespace Groups
13.2.7.2
Creating a Tablespace Group
13.2.7.3
Changing Members of a Tablespace Group
13.2.7.4
Assigning a Tablespace Group as the Default Temporary Tablespace
13.3
Consider Storing Tablespaces in the In-Memory Column Store
13.4
Specifying Nonstandard Block Sizes for Tablespaces
13.5
Controlling the Writing of Redo Records
13.6
Altering Tablespace Availability
13.6.1
Taking Tablespaces Offline
13.6.2
Bringing Tablespaces Online
13.7
Using Read-Only Tablespaces
13.7.1
About Read-Only Tablespaces
13.7.2
Making a Tablespace Read-Only
13.7.3
Making a Read-Only Tablespace Writable
13.7.4
Creating a Read-Only Tablespace on a WORM Device
13.7.5
Delaying the Opening of Data Files in Read-Only Tablespaces
13.8
Altering and Maintaining Tablespaces
13.8.1
Increasing the Size of a Tablespace
13.8.2
Altering a Locally Managed Tablespace
13.8.3
Altering a Bigfile Tablespace
13.8.4
Altering a Locally Managed Temporary Tablespace
13.8.5
Shrinking a Locally Managed Temporary Tablespace
13.9
Renaming Tablespaces
13.10
Dropping Tablespaces
13.11
Managing 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
Optimizing the Performance of Bulk Updates
20.6
Automatically Collecting Statistics on Tables
20.7
Altering Tables
20.7.1
Reasons for Using the ALTER TABLE Statement
20.7.2
Altering Physical Attributes of a Table
20.7.3
Moving a Table to a New Segment or Tablespace
20.7.3.1
About Moving a Table to a New Segment or Tablespace
20.7.3.2
Moving a Table
20.7.3.3
Moving a Table Partition or Subpartition Online
20.7.4
Manually Allocating Storage for a Table
20.7.5
Modifying an Existing Column Definition
20.7.6
Adding Table Columns
20.7.7
Renaming Table Columns
20.7.8
Dropping Table Columns
20.7.8.1
Removing Columns from Tables
20.7.8.2
Marking Columns Unused
20.7.8.3
Removing Unused Columns
20.7.8.4
Dropping Columns in Compressed Tables
20.7.9
Placing a Table in Read-Only Mode
20.8
Redefining Tables Online
20.8.1
About Redefining Tables Online
20.8.2
Features of Online Table Redefinition
20.8.3
Privileges Required for the DBMS_REDEFINITION Package
20.8.4
Restrictions for Online Redefinition of Tables
20.8.5
Performing Online Redefinition with the REDEF_TABLE Procedure
20.8.6
Redefining Tables Online with Multiple Procedures in DBMS_REDEFINITION
20.8.6.1
Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION
20.8.6.2
Constructing a Column Mapping String
20.8.6.3
Handling Virtual Private Database (VPD) Policies During Online Redefinition
20.8.6.4
Creating Dependent Objects Automatically
20.8.6.5
Creating Dependent Objects Manually
20.8.7
Results of the Redefinition Process
20.8.8
Performing Intermediate Synchronization
20.8.9
Refreshing Dependent Materialized Views During Online Table Redefinition
20.8.10
Monitoring Online Table Redefinition Progress
20.8.11
Restarting Online Table Redefinition After a Failure
20.8.12
Rolling Back Online Table Redefinition
20.8.12.1
About Online Table Redefinition Rollback
20.8.12.2
Performing Online Table Redefinition Rollback
20.8.13
Aborting Online Table Redefinition and Cleaning Up After Errors
20.8.14
Online Redefinition of One or More Partitions
20.8.14.1
Rules for Online Redefinition of a Single Partition
20.8.15
Online Table Redefinition Examples
20.9
Researching and Reversing Erroneous Table Changes
20.10
Recovering Tables Using Oracle Flashback Table
20.11
Dropping Tables
20.12
Using Flashback Drop and Managing the Recycle Bin
20.12.1
What Is the Recycle Bin?
20.12.2
Enabling and Disabling the Recycle Bin
20.12.3
Viewing and Querying Objects in the Recycle Bin
20.12.4
Purging Objects in the Recycle Bin
20.12.5
Restoring Tables from the Recycle Bin
20.13
Managing Index-Organized Tables
20.13.1
What Are Index-Organized Tables?
20.13.2
Creating Index-Organized Tables
20.13.2.1
About Creating Index-Organized Tables
20.13.2.2
Example: Creating an Index-Organized Table
20.13.2.3
Restrictions for Index-Organized Tables
20.13.2.4
Creating Index-Organized Tables That Contain Object Types
20.13.2.5
Choosing and Monitoring a Threshold Value
20.13.2.6
Using the INCLUDING Clause
20.13.2.7
Parallelizing Index-Organized Table Creation
20.13.2.8
Using Prefix Compression
20.13.3
Maintaining Index-Organized Tables
20.13.3.1
Altering Index-Organized Tables
20.13.3.2
Moving (Rebuilding) Index-Organized Tables
20.13.4
Creating Secondary Indexes on Index-Organized Tables
20.13.4.1
About Secondary Indexes on Index-Organized Tables
20.13.4.2
Creating a Secondary Index on an Index-Organized Table
20.13.4.3
Maintaining Physical Guesses in Logical Rowids
20.13.4.4
Specifying Bitmap Indexes on Index-Organized Tables
20.13.5
Analyzing Index-Organized Tables
20.13.5.1
Collecting Optimizer Statistics for Index-Organized Tables
20.13.5.2
Validating the Structure of Index-Organized Tables
20.13.6
Using the ORDER BY Clause with Index-Organized Tables
20.13.7
Converting Index-Organized Tables to Regular Tables
20.14
Managing External Tables
20.14.1
About External Tables
20.14.2
Creating External Tables
20.14.3
Altering External Tables
20.14.4
Preprocessing External Tables
20.14.5
Overriding Parameters for External Tables in a Query
20.14.6
Partitioning External Tables
20.14.6.1
About Partitioning External Tables
20.14.6.2
Restrictions for Partitioned External Tables
20.14.6.3
Creating a Partitioned External Table
20.14.6.4
Altering a Partitioned External Table
20.14.7
Dropping External Tables
20.14.8
System and Object Privileges for External Tables
20.15
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
Program Global Area (PGA)
27.3.5
Runaway Queries
27.3.5.1
Automatic Consumer Group Switching
27.3.5.2
Canceling SQL and Terminating Sessions
27.3.5.3
Execution Time Limit
27.3.6
Active Session Pool with Queuing
27.3.7
Undo Pool
27.3.8
Idle Time Limit
27.4
Creating a Simple Resource Plan
27.5
Creating a Complex Resource Plan
27.5.1
About the Pending Area
27.5.2
Creating a Pending Area
27.5.3
Creating Resource Consumer Groups
27.5.4
Mapping Sessions to Consumer Groups
27.5.5
Creating a Resource Plan
27.5.5.1
About the RATIO CPU Allocation Method
27.5.6
Creating Resource Plan Directives
27.5.6.1
Conflicting Resource Plan Directives
27.5.7
Validating the Pending Area
27.5.8
Submitting the Pending Area
27.5.9
Clearing the Pending Area
27.6
Enabling Oracle Database Resource Manager and Switching Plans
27.7
Putting It All Together: Oracle Database Resource Manager 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.2.12
Incompatibilities
28.3
More About Jobs
28.3.1
Job Categories
28.3.1.1
Database Jobs
28.3.1.1.1
About Database Jobs
28.3.1.1.2
Local Database Jobs
28.3.1.1.3
Remote Database Job
28.3.1.2
External Jobs
28.3.1.2.1
About External Jobs
28.3.1.2.2
About Local External Jobs
28.3.1.2.3
About Remote External Jobs
28.3.1.3
Multiple-Destination Jobs
28.3.1.4
Chain Jobs
28.3.1.5
Detached Jobs
28.3.1.6
Lightweight Jobs
28.3.1.7
In-Memory Jobs
28.3.1.8
Script Jobs
28.3.2
Job Instances
28.3.3
Job Arguments
28.3.4
How Programs, Jobs, and Schedules are Related
28.4
Scheduler Architecture
28.4.1
Scheduler Components
28.4.2
The Job Table
28.4.3
The Job Coordinator
28.4.3.1
About The Job Coordinator
28.4.3.2
Job Coordinator Actions
28.4.3.3
Maximum Number of Scheduler Job Processes
28.4.4
How Jobs Execute
28.4.5
After Jobs Complete
28.4.6
Using the Scheduler in Real Application Clusters Environments
28.4.6.1
The Scheduler and Real Application Clusters
28.4.6.2
Service Affinity when Using the Scheduler
28.5
Scheduler Support for Oracle Data Guard
29
Scheduling Jobs with Oracle Scheduler
29.1
About Scheduler Objects and Their Naming
29.2
Creating, Running, and Managing Jobs
29.2.1
Job Tasks and Their Procedures
29.2.2
Creating Jobs
29.2.2.1
Overview of Creating Jobs
29.2.2.2
Specifying Job Actions, Schedules, Programs, and Styles
29.2.2.2.1
Creating Jobs Using a Named Program
29.2.2.2.2
Creating Jobs Using a Named Program and Job Styles
29.2.2.2.3
Creating Jobs Using a Named Schedule
29.2.2.2.4
Creating Jobs Using Named Programs and Schedules
29.2.2.3
Specifying Scheduler Job Credentials
29.2.2.4
Specifying Destinations
29.2.2.4.1
Destination Tasks and Their Procedures
29.2.2.4.2
Creating Destinations
29.2.2.4.3
Creating Destination Groups for Multiple-Destination Jobs
29.2.2.4.4
Example: Creating a Remote Database Job
29.2.2.5
Creating Multiple-Destination Jobs
29.2.2.6
Setting Job Arguments
29.2.2.7
Setting Additional Job Attributes
29.2.2.8
Creating Detached Jobs
29.2.2.9
Creating Multiple Jobs in a Single Transaction
29.2.2.10
Techniques for External Jobs
29.2.3
Altering Jobs
29.2.4
Running Jobs
29.2.5
Stopping Jobs
29.2.6
Stopping External Jobs
29.2.7
Stopping a Chain Job
29.2.8
Dropping Jobs
29.2.9
Dropping Running Jobs
29.2.10
Dropping Multiple Jobs
29.2.11
Disabling Jobs
29.2.12
Enabling Jobs
29.2.13
Copying Jobs
29.3
Creating and Managing Programs to Define Jobs
29.3.1
Program Tasks and Their Procedures
29.3.2
Creating Programs with Scheduler
29.3.2.1
Creating Programs
29.3.2.2
Defining Program Arguments
29.3.3
Altering Programs
29.3.4
Dropping Programs
29.3.5
Disabling Programs
29.3.6
Enabling Programs
29.4
Creating and Managing Schedules to Define Jobs
29.4.1
Schedule Tasks and Their Procedures
29.4.2
Creating Schedules
29.4.3
Altering Schedules
29.4.4
Dropping Schedules
29.4.5
Setting the Repeat Interval
29.4.5.1
About Setting the Repeat Interval
29.4.5.2
Using the Scheduler Calendaring Syntax
29.4.5.3
Using a PL/SQL Expression
29.4.5.4
Differences Between PL/SQL Expression and Calendaring Syntax Behavior
29.4.5.5
Repeat Intervals and Daylight Savings
29.5
Using Events to Start Jobs
29.5.1
About Events
29.5.2
Starting Jobs with Events Raised by Your Application
29.5.2.1
About Events Raised by Your Application
29.5.2.2
Creating an Event-Based Job
29.5.2.2.1
Specifying Event Information as Job Attributes
29.5.2.2.2
Specifying Event Information in an Event Schedule
29.5.2.3
Altering an Event-Based Job
29.5.2.4
Creating an Event Schedule
29.5.2.5
Altering an Event Schedule
29.5.2.6
Passing Event Messages into an Event-Based Job
29.5.3
Starting a Job When a File Arrives on a System
29.5.3.1
About File Watchers
29.5.3.2
Enabling File Arrival Events from Remote Systems
29.5.3.3
Creating File Watchers and File Watcher Jobs
29.5.3.4
File Arrival Example
29.5.3.5
Managing File Watchers
29.5.3.5.1
Enabling File Watchers
29.5.3.5.2
Altering File Watchers
29.5.3.5.3
Disabling and Dropping File Watchers
29.5.3.5.4
Changing the File Arrival Detection Interval
29.5.3.6
Viewing File Watcher Information
29.6
Creating and Managing Job Chains
29.6.1
About Creating and Managing Job Chains
29.6.2
Chain Tasks and Their Procedures
29.6.3
Creating Chains
29.6.4
Defining Chain Steps
29.6.5
Adding Rules to a Chain
29.6.6
Setting an Evaluation Interval for Chain Rules
29.6.7
Enabling Chains
29.6.8
Creating Jobs for Chains
29.6.9
Dropping Chains
29.6.10
Running Chains
29.6.11
Dropping Chain Rules
29.6.12
Disabling Chains
29.6.13
Dropping Chain Steps
29.6.14
Stopping Chains
29.6.15
Stopping Individual Chain Steps
29.6.16
Pausing Chains
29.6.17
Skipping Chain Steps
29.6.18
Running Part of a Chain
29.6.19
Monitoring Running Chains
29.6.20
Handling Stalled Chains
29.7
Using Incompatibility Definitions
29.7.1
Creating a Job or Program Incompatibility
29.7.2
Adding a Job or Program to an Incompatibility
29.7.3
Removing a Job or Program from an Incompatibility
29.7.4
Dropping an Incompatibility
29.8
Managing Job Resources
29.8.1
Creating or Dropping a Resource
29.8.2
Altering a Resource
29.8.3
Setting a Resource Constraint for a Job
29.9
Prioritizing Jobs
29.9.1
Managing Job Priorities with Job Classes
29.9.1.1
Job Class Tasks and Their Procedures
29.9.1.2
Creating Job Classes
29.9.1.3
Altering Job Classes
29.9.1.4
Dropping Job Classes
29.9.2
Setting Relative Job Priorities Within a Job Class
29.9.3
Managing Job Scheduling and Job Priorities with Windows
29.9.3.1
About Job Scheduling and Job Priorities with Windows
29.9.3.2
Window Tasks and Their Procedures
29.9.3.3
Creating Windows
29.9.3.4
Altering Windows
29.9.3.5
Opening Windows
29.9.3.6
Closing Windows
29.9.3.7
Dropping Windows
29.9.3.8
Disabling Windows
29.9.3.9
Enabling Windows
29.9.4
Managing Job Scheduling and Job Priorities with Window Groups
29.9.4.1
Window Group Tasks and Their Procedures
29.9.4.2
Creating Window Groups
29.9.4.3
Dropping Window Groups
29.9.4.4
Adding a Member to a Window Group
29.9.4.5
Removing a Member from a Window Group
29.9.4.6
Enabling a Window Group
29.9.4.7
Disabling a Window Group
29.9.5
Allocating Resources Among Jobs Using Resource Manager
29.9.6
Example of Resource Allocation for Jobs
29.10
Monitoring Jobs
29.10.1
About Monitoring Jobs
29.10.2
The Job Log
29.10.2.1
Viewing the Job Log
29.10.2.2
Run Details
29.10.2.3
Precedence of Logging Levels in Jobs and Job Classes
29.10.3
Monitoring Multiple Destination Jobs
29.10.4
Monitoring Job State with Events Raised by the Scheduler
29.10.4.1
About Job State Events
29.10.4.2
Altering a Job to Raise Job State Events
29.10.4.3
Consuming Job State Events with your Application
29.10.5
Monitoring Job State with E-mail Notifications
29.10.5.1
About E-mail Notifications
29.10.5.2
Adding E-mail Notifications for a Job
29.10.5.3
Removing E-mail Notifications for a Job
29.10.5.4
Viewing Information About E-mail Notifications
30
Administering Oracle Scheduler
30.1
Configuring Oracle Scheduler
30.1.1
Setting Oracle Scheduler Privileges
30.1.2
Setting Scheduler Preferences
30.1.3
Using the Oracle Scheduler Agent to Run Remote Jobs
30.1.3.1
Enabling and Disabling Databases for Remote Jobs
30.1.3.1.1
Setting up Databases for Remote Jobs
30.1.3.1.2
Disabling Remote Jobs
30.1.3.2
Installing and Configuring the Scheduler Agent on a Remote Host
30.1.3.3
Performing Tasks with the Scheduler Agent
30.1.3.3.1
About the schagent Utility
30.1.3.3.2
Using the Scheduler Agent on Windows
30.1.3.3.3
Starting the Scheduler Agent
30.1.3.3.4
Stopping the Scheduler Agent
30.1.3.3.5
Registering Scheduler Agents with Databases
30.2
Monitoring and Managing the Scheduler
30.2.1
Viewing the Currently Active Window and Resource Plan
30.2.2
Finding Information About Currently Running Jobs
30.2.3
Monitoring and Managing Window and Job Logs
30.2.3.1
Job Log
30.2.3.2
Window Log
30.2.3.3
Purging Logs
30.2.4
Managing Scheduler Security
30.3
Import/Export and the Scheduler
30.4
Troubleshooting the Scheduler
30.4.1
A Job Does Not Run
30.4.1.1
About Job States
30.4.1.1.1
Failed Jobs
30.4.1.1.2
Broken Jobs
30.4.1.1.3
Disabled Jobs
30.4.1.1.4
Completed Jobs
30.4.1.2
Viewing the Job Log
30.4.1.3
Troubleshooting Remote Jobs
30.4.1.4
About Job Recovery After a Failure
30.4.2
A Program Becomes Disabled
30.4.3
A Window Fails to Take Effect
30.5
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
PL/SQL and Remote Procedure Calls (RPCs)
31.5.3
Distributed Query Optimization
31.6
Character Set Support for Distributed Environments
31.6.1
About Character Set Support for Distributed Environments
31.6.2
Client/Server Environment
31.6.3
Homogeneous Distributed Environment
31.6.4
Heterogeneous Distributed Environment
32
Managing a Distributed Database
32.1
Managing Global Names in a Distributed System
32.1.1
Understanding How Global Database Names Are Formed
32.1.2
Determining Whether Global Naming Is Enforced
32.1.3
Viewing a Global Database Name
32.1.4
Changing the Domain in a Global Database Name
32.1.5
Changing a Global Database Name: Scenario
32.2
Creating Database Links
32.2.1
Obtaining Privileges Necessary for Creating Database Links
32.2.2
Specifying Link Types
32.2.2.1
Creating Private Database Links
32.2.2.2
Creating Public Database Links
32.2.2.3
Creating Global Database Links
32.2.3
Specifying Link Users
32.2.3.1
Creating Fixed User Database Links
32.2.3.2
Creating Connected User and Current User Database Links
32.2.3.2.1
Creating a Connected User Database Link
32.2.3.2.2
Creating a Current User Database Link
32.2.4
Using Connection Qualifiers to Specify Service Names Within Link Names
32.3
Using Shared Database Links
32.3.1
Determining Whether to Use Shared Database Links
32.3.2
Creating Shared Database Links
32.3.3
Configuring Shared Database Links
32.3.3.1
Creating Shared Links to Dedicated Servers
32.3.3.2
Creating Shared Links to Shared Servers
32.4
Managing Database Links
32.4.1
Closing Database Links
32.4.2
Dropping Database Links
32.4.2.1
Dropping a Private Database Link
32.4.2.2
Dropping a Public Database Link
32.4.3
Limiting the Number of Active Database Link Connections
32.5
Viewing Information About Database Links
32.5.1
Determining Which Links Are in the Database
32.5.2
Determining Which Link Connections Are Open
32.5.3
Determining the Host of Outgoing Database Links
32.5.4
Determining Information About Incoming Database Links
32.5.5
Determining the Source of High SCN Activity for Incoming Database Links
32.6
Creating Location Transparency
32.6.1
Using Views to Create Location Transparency
32.6.2
Using Synonyms to Create Location Transparency
32.6.2.1
Creating Synonyms
32.6.2.2
Managing Privileges and Synonyms
32.6.3
Using Procedures to Create Location Transparency
32.6.3.1
Using Local Procedures to Reference Remote Data
32.6.3.2
Using Local Procedures to Call Remote Procedures
32.6.3.3
Using Local Synonyms to Reference Remote Procedures
32.6.3.4
Managing Procedures and Privileges
32.7
Managing Statement Transparency
32.8
Managing a Distributed Database: 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.1.4
Application Containers
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 CDB 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 CDB Seed's Data Files
37.4.1.4
About the CDB’s Undo Mode
37.4.2
Step 1: Specify an Instance Identifier (SID)
37.4.3
Step 2: Ensure That the Required Environment Variables Are Set
37.4.4
Step 3: Choose a Database Administrator Authentication Method
37.4.5
Step 4: Create the Initialization Parameter File
37.4.6
Step 5: (Windows Only) Create an Instance
37.4.7
Step 6: Connect to the Instance
37.4.8
Step 7: Create a Server Parameter File
37.4.9
Step 8: Start the Instance
37.4.10
Step 9: Create the 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
Renaming a Service
38.1.2.6
Temp File Reuse
38.1.2.7
Default Tablespace
38.1.2.8
User Tablespaces
38.1.2.9
PDB Tablespace Logging
38.1.2.10
PDB Inclusion in Standby CDBs
38.1.2.11
Excluding Data When Cloning a PDB
38.1.2.12
Parallelize PDB Creation
38.1.2.13
PDB Refresh
38.1.2.14
Application Container and Application Seed
38.1.2.14.1
AS APPLICATION CONTAINER Clause
38.1.2.14.2
AS SEED Clause
38.1.2.15
PDB Listener Host Name and Port Number
38.1.2.15.1
HOST Clause
38.1.2.15.2
PORT Clause
38.1.3
Summary of Clauses for Creating 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 Relocating It
38.5.1
About Relocating a PDB
38.5.2
Relocating a PDB
38.6
Creating a PDB by Plugging an Unplugged PDB into a CDB
38.6.1
About Plugging In an Unplugged PDB
38.6.2
Plugging In an Unplugged PDB
38.6.3
After Plugging in an Unplugged PDB
38.7
Creating a PDB as a Proxy PDB
38.7.1
About Creating a Proxy PDB
38.7.2
Creating a Proxy PDB
38.8
Creating a PDB Using a Non-CDB
38.8.1
About Creating a PDB Using a Non-CDB
38.8.2
Using the DBMS_PDB Package on a Non-CDB
38.8.2.1
About Using the DBMS_PDB Package on a Non-CDB
38.8.2.2
Using the DBMS_PDB Package to Create an Unplugged PDB
38.9
Unplugging a PDB from a CDB
38.9.1
About Unplugging a PDB
38.9.2
Unplugging a PDB
38.10
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
Creating and Removing Application Containers and Seeds with SQL*Plus
40.1
Creating and Removing Application Containers with SQL*Plus
40.1.1
Creating Application Containers
40.1.1.1
About Creating an Application Container
40.1.1.2
Preparing for Application Containers
40.1.1.3
Creating an Application Container
40.1.2
Unplugging an Application Container from a CDB
40.1.2.1
About Unplugging an Application Container
40.1.2.2
Unplugging an Application Container
40.1.3
Dropping an Application Container
40.2
Creating and Removing Application Seeds with SQL*Plus
40.2.1
Creating Application Seeds
40.2.1.1
About Creating an Application Seed
40.2.1.2
Preparing for an Application Seed
40.2.1.3
Creating an Application Seed
40.2.2
Unplugging an Application Seed from an Application Container
40.2.2.1
About Unplugging an Application Seed
40.2.2.2
Unplugging an Application Seed
40.2.3
Dropping an Application Seed
40.3
Creating an Application PDB
41
Administering a CDB with SQL*Plus
41.1
About Administering a CDB
41.1.1
About the Current Container
41.1.2
About Administrative Tasks in a CDB
41.1.3
About Using Manageability Features in a CDB
41.1.4
About Managing Database Objects in a CDB
41.1.5
About Flashing Back a PDB
41.1.6
About Restricting PDB Users for Enhanced Security
41.2
Accessing a Container in a CDB with SQL*Plus
41.2.1
About Accessing a Container in a CDB with SQL*Plus
41.2.2
Connecting to a Container Using the SQL*Plus CONNECT Command
41.2.2.1
Connecting to the Root Using the SQL*Plus CONNECT Command
41.2.2.2
Connecting to a PDB Using the SQL*Plus CONNECT Command
41.2.3
Switching to a Container Using the ALTER SESSION Statement
41.3
Executing Code in Containers Using the DBMS_SQL Package
41.4
Modifying a CDB
41.4.1
About Modifying a CDB
41.4.2
About the Statements That Modify a CDB
41.4.3
Managing Tablespaces in a CDB
41.4.3.1
About Managing Tablespaces in a CDB
41.4.3.2
About Managing Permanent Tablespaces in a CDB
41.4.3.3
About Managing Temporary Tablespaces in a CDB
41.4.4
Modifying an Entire CDB
41.4.5
Modifying the Root
41.4.6
Modifying the Open Mode of PDBs
41.4.6.1
About the Open Mode of a PDB
41.4.6.2
About Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
41.4.6.3
About Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
41.4.6.4
Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
41.4.6.5
Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
41.4.7
Preserving or Discarding the Open Mode of PDBs When the CDB Restarts
41.5
Using the ALTER SYSTEM SET Statement in a CDB
41.6
Issuing DML Statements on Containers in a CDB
41.6.1
About Issuing DML Statements on Containers in a CDB
41.6.2
Specifying the Default Container for DML Statements in a CDB
41.7
Executing DDL Statements in a CDB
41.7.1
About Executing DDL Statements in a CDB
41.7.2
Executing a DDL Statement in the Current Container
41.7.3
Executing a DDL Statement in All Containers in a CDB
41.8
Managing the CDB Undo Mode
41.8.1
About the CDB Undo Mode
41.8.2
Configuring a CDB to Use Local Undo Mode
41.8.3
Configuring a CDB to Use Shared Undo Mode
41.9
Running Oracle-Supplied SQL Scripts in a CDB
41.9.1
About Running Oracle-Supplied SQL Scripts in a CDB
41.9.2
Syntax and Parameters for catcon.pl
41.9.3
Running the catcon.pl Script
41.10
Shutting Down a CDB Instance
42
Administering CDBs and PDBs with Cloud Control
42.1
Administering CDB Storage and Schema Objects with Cloud Control
42.1.1
About Managing and Monitoring CDB Storage and Schema Objects
42.1.2
Managing CDB Storage and Schema Objects
42.1.3
Managing Per-Container Storage and Schema Objects
42.1.4
Monitoring Storage and Schema Alerts
42.2
Administering PDBs with Cloud Control
42.2.1
Switching Between PDBs
42.2.2
Altering the Open Mode of a PDB
43
Administering PDBs with SQL*Plus
43.1
About Administering PDBs
43.2
Connecting to a PDB with SQL*Plus
43.3
Modifying a PDB
43.3.1
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
43.3.1.1
About Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
43.3.1.2
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
43.3.1.3
Changing the Global Database Name of a PDB
43.3.2
Modifying a PDB with the SQL*Plus STARTUP and SHUTDOWN Commands
43.3.2.1
Using the STARTUP SQL*Plus Command on a PDB
43.3.2.2
Using the SQL*Plus SHUTDOWN Command on a PDB
43.4
Using the ALTER SYSTEM Statement to Modify a PDB
43.4.1
About Using the ALTER SYSTEM Statement on a PDB
43.4.2
Using the ALTER SYSTEM Statement on a PDB
43.5
Managing Services Associated with PDBs
43.5.1
About Services Associated with PDBs
43.5.2
Creating, Modifying, or Removing a Service for a PDB
43.6
Refreshing a PDB
43.7
Modifying the Listener Settings of a Referenced PDB
43.7.1
Altering the Listener Host Name of a Referenced PDB
43.7.2
Altering the Listener Port Number of a Referenced PDB
44
Administering Application Containers with SQL*Plus
44.1
About Administering Application Containers with SQL*Plus
44.2
About Modifying an Application Root
44.3
Managing Applications in an Application Container
44.3.1
About Managing Applications in an Application Container
44.3.2
Installing Applications in an Application Container
44.3.2.1
About Installing Applications in an Application Container
44.3.2.2
Installing an Application in an Application Container with Automated Propagation
44.3.3
Upgrading Applications in an Application Container
44.3.3.1
About Upgrading Applications in an Application Container
44.3.3.2
Upgrading an Application in an Application Container
44.3.4
Patching Applications in an Application Container
44.3.4.1
About Patching Applications in an Application Container
44.3.4.2
Patching an Application in an Application Container with Automated Propagation
44.3.5
Migrating an Existing Application to an Application Container
44.3.5.1
About Migrating an Existing Application to an Application Container
44.3.5.2
Creating an Application Root Using an Existing PDB
44.3.5.3
Creating an Application PDB Using an Existing PDB
44.3.6
Synchronizing Applications in an Application PDB
44.3.7
Synchronizing an Application Root Replica with a Proxy PDB
44.3.7.1
About Synchronizing an Application Root Replica with a Proxy PDB
44.3.7.2
Creating a Proxy PDB That References an Application Root Replica
44.3.8
Setting the Compatibility Version of an Application
44.3.9
Performing Bulk Inserts During Application Install, Upgrade, and Patch Operations
44.3.10
Uninstalling Applications from an Application Container
44.3.10.1
About Uninstalling Applications from an Application Container
44.3.10.2
Uninstalling an Application from an Application Container
44.4
Managing Application Common Objects
44.4.1
About Application Common Objects
44.4.2
Restrictions for Application Common Objects
44.4.3
Creating Application Common Objects
44.4.4
Issuing DML Statements on Application Common Objects
44.4.5
Modifying Application Common Objects with DDL Statements
44.5
Issuing DML Statements on Containers in an Application Container
44.5.1
About Issuing DML Statements on Containers in an Application Container
44.5.2
Specifying the Default Container for DML Statements in an Application Container
44.6
Partitioning by PDB with Container Maps
44.6.1
About Partitioning by PDB with Container Maps
44.6.2
Creating a Container Map
45
Viewing Information About CDBs and PDBs with SQL*Plus
45.1
About CDB and Container Information in Views
45.2
About Viewing Information When the Current Container Is a Not the CDB Root
45.3
About Viewing Information When the Current Container Is the CDB Root
45.4
Views for a CDB
45.5
Determining Whether a Database Is a CDB
45.6
Viewing Information About the Containers in a CDB
45.7
Viewing Information About PDBs
45.8
Viewing the Open Mode of Each PDB
45.9
Querying Container Data Objects
45.10
Querying Across Containers with the CONTAINERS Clause
45.10.1
About Querying Across Containers with the CONTAINERS Clause
45.10.2
Querying User-Created Tables and Views Across All Containers
45.10.3
Querying Application Common Objects Across Application PDBs
45.11
Determining the Current Container ID or Name
45.12
Listing the Initialization Parameters That Are Modifiable in PDBs
45.13
Viewing the History of PDBs
45.14
Viewing Information About Applications in Application Containers
45.14.1
Viewing Information About Applications
45.14.2
Viewing Information About Application Status
45.14.3
Viewing Information About Application Statements
45.14.4
Viewing Information About Application Versions
45.14.5
Viewing Information About Application Patches
45.14.6
Viewing Information About Application Errors
45.14.7
Listing the Shared Database Objects in an Application Container
45.14.8
Listing the Extended Data-Linked Objects in an Application Container
46
Using Oracle Resource Manager for PDBs with SQL*Plus
46.1
About Using Oracle Resource Manager with CDBs and PDBs
46.1.1
About Managing Resources at the CDB Level and PDB Level
46.1.2
What Solutions Does Resource Manager Provide for a CDB?
46.1.3
CDB Resource Plans
46.1.3.1
About CDB Resource Plans
46.1.3.2
Shares for Allocating Resources to PDBs
46.1.3.3
Utilization Limits for PDBs
46.1.3.4
The Default Directive for PDBs
46.1.4
PDB Resource Plans
46.1.5
Background and Administrative Tasks and Consumer Groups
46.1.6
Initialization Parameters That Control Memory for PDBs
46.1.7
Initialization Parameters That Limit I/O for PDBs
46.2
Prerequisites for Using Resource Manager with a CDB
46.3
Creating a CDB Resource Plan for Individual PDBs
46.3.1
Creating a CDB Resource Plan for Individual PDBs: A Scenario
46.4
Creating a CDB Resource Plan with PDB Performance Profiles
46.4.1
Creating a CDB Resource Plan for PDB Performance Profiles: A Scenario
46.5
Enabling and Disabling a CDB Resource Plan
46.5.1
Enabling a CDB Resource Plan
46.5.2
Disabling a CDB Resource Plan
46.6
Creating a PDB Resource Plan
46.7
Enabling and Disabling a PDB Resource Plan
46.7.1
Enabling a PDB Resource Plan
46.7.2
Disabling a PDB Resource Plan
46.8
Maintaining Plans and Directives in a CDB
46.8.1
Managing a CDB Resource Plan
46.8.1.1
Updating a CDB Resource Plan
46.8.1.2
Managing CDB Resource Plan Directives for a PDB
46.8.1.2.1
Creating New CDB Resource Plan Directives for a PDB
46.8.1.2.2
Updating CDB Resource Plan Directives for a PDB
46.8.1.2.3
Deleting CDB Resource Plan Directives for a PDB
46.8.1.3
Managing CDB Resource Plan Directives for a PDB Performance Profile
46.8.1.3.1
Creating New CDB Resource Plan Directives for a PDB Performance Profile
46.8.1.3.2
Updating CDB Resource Plan Directives for a PDB Performance Profile
46.8.1.3.3
Deleting CDB Resource Plan Directives for a PDB Performance Profile
46.8.1.4
Updating the Default Directive for PDBs in a CDB Resource Plan
46.8.1.5
Updating the Default Directive for Maintenance Tasks in a CDB Resource Plan
46.8.1.6
Deleting a CDB Resource Plan
46.8.2
Modifying a PDB Resource Plan
46.9
Viewing Information About Plans and Directives in a CDB
46.9.1
Viewing CDB Resource Plans
46.9.2
Viewing CDB Resource Plan Directives
46.10
Monitoring PDBs Managed by Oracle Database Resource Manager
46.10.1
Monitoring CPU Usage for PDBs
46.10.2
Monitoring Parallel Execution for PDBs
46.10.3
Monitoring the I/O Generated by PDBs
46.10.4
Monitoring Memory Usage for PDBs
47
Using Oracle Resource Manager for PDBs with Cloud Control
47.1
About CDB Resource Manager and Cloud Control
47.2
Creating a CDB Resource Plan with Cloud Control
47.3
Creating a PDB Resource Plan with Cloud Control
48
Using Oracle Scheduler with a CDB
48.1
DBMS_SCHEDULER Invocations in a CDB
48.2
Job Coordinator and Slave Processes in a CDB
48.3
Using DBMS_JOB
48.4
Processes to Close a PDB
48.5
New and Changed Views
Part VII Sharded Database Management
49
Overview of Oracle Sharding
49.1
About Sharding
49.2
Benefits of Sharding
49.3
Components of the Oracle Sharding Architecture
49.4
Application Suitability for Sharding
50
Sharded Database Schema Design
50.1
Sharded Table
50.2
Sharded Table Family
50.3
Duplicated Tables
50.4
Non-Table Objects Created on All Shards
50.5
DDL Execution in a Sharded Database
50.6
DDL Syntax Extensions for the Sharded Database
51
Physical Organization of a Sharded Database
51.1
Sharding as Distributed Partitioning
51.2
Partitions, Tablespaces, and Chunks
52
Sharding Methods
52.1
System-Managed Sharding
52.2
Composite Sharding
52.3
Using Subpartitions with Sharding
53
Design Considerations for Sharded Database Applications
53.1
Considerations for Sharded Database Schema Design
53.2
Developing Applications for Oracle Sharding
54
Routing in an SDB Environment
54.1
Direct Routing to a Shard
54.1.1
About Direct Routing to a Shard
54.1.2
Sharding APIs
54.2
Queries and DMLs via Proxy Routing in an SDB
54.2.1
About Proxy Routing in a Sharded Database
54.2.2
Oracle Sharding Coordinator
54.2.3
Querying and DMLs Using Proxy Routing
54.2.4
Proxy Routing for Single-Shard Queries
54.2.5
Proxy Routing for Multi-Shard Queries
54.2.6
Resiliency of Proxy Routing
54.2.7
Supported Query Shapes in Proxy Routing
55
Shard-Level High Availability
55.1
About Sharding and Replication
55.2
Using Oracle Data Guard with an SDB
56
Sharded Database Deployment
56.1
Introduction to SDB Deployment
56.2
Oracle Sharding Prerequisites
56.3
Installing Oracle Database Software
56.4
Installing the Shard Director Software
56.5
Creating the Shard Catalog Database
56.6
Setting Up the Oracle Sharding Management and Routing Tier
56.7
Creating a System-Managed SDB
56.7.1
Deploying a System-Managed SDB
56.7.2
Creating a Schema for a System-Managed SDB
56.7.3
System-Managed SDB Demo Application
56.8
Deploying a Composite SDB
56.8.1
Deploying a Composite SDB
56.8.2
Creating a Schema for a Composite SDB
57
Sharded Database Lifecycle Management
57.1
Monitoring a Sharded Database
57.1.1
Monitoring a Sharded Database with GDSCTL
57.1.2
Monitoring a Sharded Database with Enterprise Manager Cloud Control
57.1.2.1
Discovering Sharded Database Components
57.2
Backing Up and Recovering a Sharded Database
57.3
Patching a Sharded Database
57.4
Modifying a Sharded Database Schema
57.5
Shard Management
57.5.1
About Adding Shards
57.5.2
Resharding and Hot Spot Elimination
57.5.3
Removing a Shard From the Pool
57.5.4
Adding Standby Shards
57.5.5
Managing Shards with Oracle Enterprise Manager Cloud Control
57.5.5.1
Validating a Shard
57.5.5.2
Adding Primary Shards
57.5.5.3
Adding Standby Shards
57.5.5.4
Deploying Shards
57.5.6
Managing Shards with GDSCTL
57.5.6.1
Validating a Shard
57.5.6.2
Adding Shards to a System-Managed SDB
57.6
Chunk Management
57.6.1
About Moving Chunks
57.6.2
Moving Chunks
57.6.3
About Splitting Chunks
57.6.4
Splitting Chunks
57.7
Shard Director Management
57.7.1
Creating a Shard Director
57.7.2
Editing a Shard Director Configuration
57.7.3
Removing a Shard Director
57.8
Region Management
57.8.1
Creating a Region
57.8.2
Editing a Region Configuration
57.8.3
Removing a Region
57.9
Shardspace Management
57.9.1
Creating a Shardspace
57.10
Shardgroup Management
57.10.1
Creating a Shardgroup
57.11
Services Management
57.11.1
Creating a Service
Part VIII Managing Read-Only Materialized Views
58
Read-Only Materialized View Concepts
58.1
Replication Databases
58.2
Read-Only Materialized Views
58.3
The Uses of Materialized Views
58.3.1
Ease Network Loads
58.3.2
Enable Data Subsetting
58.3.3
Enable Disconnected Computing
58.4
Available Materialized Views
58.4.1
About the Available Materialized Views
58.4.2
Primary Key Materialized Views
58.4.3
Object Materialized Views
58.4.4
ROWID Materialized Views
58.4.5
Complex Materialized Views
58.4.5.1
About Complex Materialized Views
58.4.5.2
A Comparison of Simple and Complex Materialized Views
58.5
Users and Privileges Related to Materialized Views
58.5.1
Required Privileges for Materialized View Operations
58.5.2
Creator Is Owner
58.5.3
Creator Is Not Owner
58.5.4
Refresher Is Owner
58.5.5
Refresher Is Not Owner
58.6
Data Subsetting with Materialized Views
58.6.1
About Data Subsetting with Materialized Views
58.6.2
Materialized Views with Subqueries
58.6.2.1
Many to One Subqueries
58.6.2.2
One to Many Subqueries
58.6.2.3
Many to Many Subqueries
58.6.2.4
Materialized Views with Subqueries and Unions
58.6.3
Restrictions for Materialized Views with Subqueries
58.6.4
Restrictions for Materialized Views with Unions Containing Subqueries
58.6.4.1
Examples of Materialized Views with Unions Containing Subqueries
58.7
Materialized View Refresh
58.8
Refresh Groups
58.9
Materialized View Log
58.10
Materialized Views and User-Defined Data Types
58.10.1
How Materialized Views Work with Object Types and Collections
58.10.2
Type Agreement at Replication Databases
58.10.3
Column Subsetting of Masters with Column Objects
58.10.4
Materialized Views Based on Object Tables
58.10.4.1
About Materialized Views Based on Object Tables
58.10.4.2
Materialized Views Based on Object Tables Created Without Using the OF
type
Clause
58.10.4.3
OID Preservation in Object Materialized Views
58.10.5
Materialized Views with Collection Columns
58.10.5.1
Restrictions for Materialized Views with Collection Columns
58.10.6
Materialized Views with REF Columns
58.10.6.1
About Materialized Views with REF Columns
58.10.6.2
Scoped REF Columns
58.10.6.3
Unscoped REF Columns
58.10.6.4
Logging REF Columns in the Materialized View Log
58.10.6.5
REFs Created Using the WITH ROWID Clause
58.11
Materialized View Registration at a Master Database
58.11.1
Viewing Information about Registered Materialized Views
58.11.2
Internal Mechanisms
58.11.3
Manual Materialized View Registration
59
Read-Only Materialized View Architecture
59.1
Master Database Mechanisms
59.1.1
Master Database Objects
59.1.2
Master Table
59.1.3
Internal Trigger for the Materialized View Log
59.1.4
Materialized View Logs
59.1.4.1
About Materialized View Logs
59.1.4.2
Columns Logged in the Materialized View Log
59.1.4.3
Restriction on Import of Materialized View Logs to a Different Schema
59.2
Materialized View Database Mechanisms
59.2.1
Indexes for Materialized Views
59.3
Organizational Mechanisms
59.3.1
Refresh Groups
59.3.2
Refresh Group Size
59.4
Refresh Process
59.4.1
About the Refresh Process
59.4.2
Refresh Types
59.4.2.1
Complete Refresh
59.4.2.2
Fast Refresh
59.4.2.3
Force Refresh
59.4.3
Initiating a Refresh
59.4.3.1
Scheduled Refresh
59.4.3.2
On-Demand Refresh
59.4.4
Constraints and Refresh
60
Planning for Read-Only Materialized Views
60.1
Considerations for Master Tables
60.1.1
Primary Keys and Master Tables
60.1.2
Foreign Keys and Master Tables
60.1.3
Data Type Considerations for Master Tables
60.1.4
Unsupported Table Types
60.2
Planning for Master Databases and Materialized View Databases
60.2.1
Characteristics of Master Databases and Materialized View Databases
60.2.2
Advantages of Master Databases
60.2.3
Advantages of Materialized View Databases
60.2.4
Preparing for Materialized Views
60.2.4.1
Required Schemas at Materialized View Database
60.2.4.2
Required Database Links for Materialized Views
60.2.4.3
Required Privileges
60.2.4.4
Sufficient Job Processes
60.2.5
Creating Materialized View Logs
60.2.6
Logging Columns in a Materialized View Log
61
Creating and Managing Read-Only Materialized Views
61.1
Creating Read-Only Materialized Views
61.2
Creating Refresh Groups
61.3
Refreshing Materialized Views
61.4
Determining the Fast Refresh Capabilities of a Materialized View
61.5
Adding a New Materialized View Database
61.6
Monitoring Materialized View Logs
61.6.1
Listing Information About the Materialized View Logs at a Master Database
61.6.2
Listing the Materialized Views that Use a Materialized View Log
61.7
Monitoring Materialized Views
61.7.1
Listing Information About Materialized Views
61.7.1.1
Listing Master Database Information For Materialized Views
61.7.1.2
Listing the Properties of Materialized Views
61.7.2
Listing Information About the Refresh Groups at a Materialized View Database
61.7.3
Determining the Job ID for Each Refresh Job at a Materialized View Database
61.7.4
Determining Which Materialized Views Are Currently Refreshing
62
Troubleshooting Problems with Read-Only Materialized Views
62.1
Diagnosing Problems with Database Links
62.2
Problems Creating Materialized Views
62.3
Refresh Problems
62.3.1
Common Refresh Problems
62.3.2
Automatic Refresh Retries
62.3.3
Fast Refresh Errors at New Materialized View Databases
62.3.4
Materialized Views Continually Refreshing
62.3.5
Materialized View Logs Growing Too Large
62.4
Advanced Troubleshooting of Refresh Problems
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.