Table of Contents
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle Database Administrator's Guide
-
Part I Basic Database Administration
-
1
Getting Started with Database Administration
- 1.1 Types of Oracle Database Users
-
1.2
Tasks of a Database Administrator
- 1.2.1 Task 1: Evaluate the Database Server Hardware
- 1.2.2 Task 2: Install the Oracle Database Software
- 1.2.3 Task 3: Plan the Database
- 1.2.4 Task 4: Create and Open the Database
- 1.2.5 Task 5: Back Up the Database
- 1.2.6 Task 6: Enroll System Users
- 1.2.7 Task 7: Implement the Database Design
- 1.2.8 Task 8: Back Up the Fully Functional Database
- 1.2.9 Task 9: Tune Database Performance
- 1.2.10 Task 10: Download and Install Release Updates and Release Update Revisions
- 1.2.11 Task 11: Roll Out to Additional Hosts
- 1.3 SQL Statements
- 1.4 Identifying Your Oracle Database Software Release
- 1.5 About Database Administrator Security and Privileges
- 1.6 Database Administrator 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.3 Creating a Database with DBCA
-
2.4
Creating a Database with the CREATE DATABASE Statement
- 2.4.1 About Creating a Database with the CREATE DATABASE Statement
- 2.4.2 Step 1: Specify an Instance Identifier (SID)
- 2.4.3 Step 2: Ensure That the Required Environment Variables Are Set
- 2.4.4 Step 3: Choose a Database Administrator Authentication Method
- 2.4.5 Step 4: Create the Initialization Parameter File
- 2.4.6 Step 5: (Windows Only) Create an Instance
- 2.4.7 Step 6: Connect to the Instance
- 2.4.8 Step 7: Create a Server Parameter File
- 2.4.9 Step 8: Start the Instance
- 2.4.10 Step 9: Issue the CREATE DATABASE Statement
- 2.4.11 Step 10: Create Additional Tablespaces
- 2.4.12 Step 11: Run Scripts to Build Data Dictionary Views
- 2.4.13 Step 12: (Optional) Run Scripts to Install Additional Options
- 2.4.14 Step 13: Back Up the Database
- 2.4.15 Step 14: (Optional) Enable Automatic Instance Startup
-
2.5
Specifying CREATE DATABASE Statement Clauses
- 2.5.1 About CREATE DATABASE Statement Clauses
- 2.5.2 Protecting Your Database: Specifying Passwords for SYS and SYSTEM Users
- 2.5.3 Creating a Locally Managed SYSTEM Tablespace
- 2.5.4 Specify Data File Attributes for the SYSAUX Tablespace
- 2.5.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.10 Specifying the Database Time Zone and Time Zone File
- 2.5.11 Specifying FORCE LOGGING Mode
-
2.6
Specifying Initialization Parameters
- 2.6.1 About Initialization Parameters and Initialization Parameter Files
- 2.6.2 Determining the Global Database Name
- 2.6.3 Specifying a Fast Recovery Area
- 2.6.4 Specifying Control Files
- 2.6.5 Specifying Database 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.9 Specifying the Database Compatibility Level
- 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.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.9 Considerations After Creating a Database
- 2.10 Cloning a Database
- 2.11 Dropping a Database
- 2.12 Database Data Dictionary Views
-
2.13
Database Configuration Assistant Command Reference for Silent Mode
- 2.13.1 DBCA Command-Line Syntax Overview
- 2.13.2 About DBCA Templates
- 2.13.3 Database User Authentication in DBCA Commands Using Oracle Wallet
-
2.13.4
DBCA Silent Mode Commands
- 2.13.4.1 createDatabase
- 2.13.4.2 createDuplicateDB
- 2.13.4.3 configureDatabase
- 2.13.4.4 createTemplateFromDB
- 2.13.4.5 createTemplateFromTemplate
- 2.13.4.6 createCloneTemplate
- 2.13.4.7 deleteTemplate
- 2.13.4.8 generateScripts
- 2.13.4.9 deleteDatabase
- 2.13.4.10 createPluggableDatabase
- 2.13.4.11 unplugDatabase
- 2.13.4.12 deletePluggableDatabase
- 2.13.4.13 relocatePDB
- 2.13.4.14 configurePluggableDatabase
- 2.13.4.15 addInstance
- 2.13.4.16 deleteInstance
- 2.13.4.17 executePrereqs
-
3
Starting Up and Shutting Down
-
3.1
Starting Up a Database
- 3.1.1 About Database Startup Options
- 3.1.2 Specifying Initialization Parameters at Startup
- 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.3 Shutting Down a Database
- 3.4 Quiescing a Database
- 3.5 Suspending and Resuming a Database
- 3.6 Delaying Instance Abort
-
3.1
Starting Up a Database
-
4
Configuring Automatic Restart of an Oracle Database
-
4.1
About Oracle Restart
- 4.1.1 Oracle Restart Overview
- 4.1.2 About Startup Dependencies
- 4.1.3 About Starting and Stopping Components with Oracle Restart
- 4.1.4 About Starting and Stopping Oracle Restart
- 4.1.5 Oracle Restart Configuration
- 4.1.6 Oracle Restart Integration with Oracle Data Guard
- 4.1.7 Fast Application Notification with Oracle Restart
-
4.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.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.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.2 config
- 4.5.3 disable
- 4.5.4 downgrade
- 4.5.5 enable
- 4.5.6 getenv
- 4.5.7 modify
- 4.5.8 remove
- 4.5.9 setenv
- 4.5.10 start
- 4.5.11 status
- 4.5.12 stop
- 4.5.13 unsetenv
- 4.5.14 update
- 4.5.15 upgrade
- 4.6 CRSCTL Command Reference
-
4.1
About Oracle Restart
-
5
Managing Processes
- 5.1 About Dedicated and Shared Server Processes
- 5.2 About Database Resident Connection Pooling
- 5.3 About Proxy Resident Connection Pooling
- 5.4 Configuring Oracle Database for Shared Server
- 5.5 Configuring Database Resident Connection Pooling
- 5.6 About Oracle Database Background Processes
- 5.7 Managing Prespawned Processes
- 5.8 Managing Processes for Parallel SQL Execution
- 5.9 Managing Processes for External Procedures
- 5.10 Terminating Sessions
- 5.11 Process and Session Data Dictionary Views
-
6
Managing Memory
- 6.1 About Memory Management
- 6.2 Memory Architecture Overview
- 6.3 Using Automatic Memory Management
-
6.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.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.4 Specifying the 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.4 Using Automatic PGA Memory Management
- 6.4.5 Using Manual PGA Memory Management
- 6.5 Using Force Full Database Caching Mode
- 6.6 Configuring Database Smart Flash Cache
- 6.7 Improving Query Performance with Oracle Database In-Memory
- 6.8 Enabling High Performance Data Streaming with the Memoptimized Rowstore
- 6.9 Memory Management Reference
- 7 Managing Users and Securing the Database
-
8
Monitoring the Database
-
8.1
Monitoring Errors and Alerts
- 8.1.1 Monitoring Errors with Trace Files and the Alert Log
- 8.1.2 Monitoring a Database with Server-Generated Alerts
- 8.2 Monitoring Performance
- 8.3 Monitoring Quarantined Objects
-
8.1
Monitoring Errors and Alerts
-
9
Diagnosing and Resolving Problems
-
9.1
About the Oracle Database Fault Diagnosability Infrastructure
- 9.1.1 Fault Diagnosability Infrastructure Overview
- 9.1.2 Incidents and Problems
- 9.1.3 Fault Diagnosability Infrastructure Components
- 9.1.4 Structure, Contents, and Location of the Automatic Diagnostic Repository
-
9.2
About Investigating, Reporting, and Resolving a Problem
- 9.2.1 Roadmap — Investigating, Reporting, and Resolving a Problem
- 9.2.2 Task 1: View Critical Error Alerts in Cloud Control
- 9.2.3 Task 2: View Problem Details
- 9.2.4 Task 3: (Optional) Gather Additional Diagnostic Information
- 9.2.5 Task 4: (Optional) Create a Service Request
- 9.2.6 Task 5: Package and Upload Diagnostic Data to Oracle Support
- 9.2.7 Task 6: Track the Service Request and Implement Any Repairs
-
9.3
Diagnosing Problems
- 9.3.1 Identifying Problems Reactively
- 9.3.2 Identifying Problems Proactively with Health Monitor
- 9.3.3 Gathering Additional Diagnostic Data
- 9.3.4 Creating Test Cases with SQL Test Case Builder
- 9.4 Reporting Problems
-
9.5
Resolving Problems
-
9.5.1
Repairing SQL Failures with the SQL Repair Advisor
- 9.5.1.1 About the SQL Repair Advisor
- 9.5.1.2 Running the SQL Repair Advisor Using Cloud Control
- 9.5.1.3 Running the SQL Repair Advisor Using the DBMS_SQLDIAG Package Subprograms
- 9.5.1.4 Viewing, Disabling, or Removing a SQL Patch Using Cloud Control
- 9.5.1.5 Disabling or Removing a SQL Patch Using DBMS_SQLDIAG Package Subprograms
- 9.5.1.6 Exporting and Importing a Patch Using DBMS_SQLDIAG Package Subprograms
- 9.5.2 Repairing Data Corruptions with the Data Recovery Advisor
-
9.5.3
Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources
- 9.5.3.1 About Quarantine for Execution Plans for SQL Statements
- 9.5.3.2 Creating a Quarantine Configuration for an Execution Plan of a SQL Statement
- 9.5.3.3 Specifying Quarantine Thresholds in a Quarantine Configuration
- 9.5.3.4 Enabling and Disabling a Quarantine Configuration
- 9.5.3.5 Viewing the Details of a Quarantine Configuration
- 9.5.3.6 Deleting a Quarantine Configuration
- 9.5.3.7 Viewing the Details of Quarantined Execution Plans of SQL Statements
- 9.5.3.8 Transferring Quarantine Configurations from One Database to Another Database
- 9.5.3.9 Example: Quarantine for an Execution Plan of a SQL Statement Consuming Excessive System Resources
-
9.5.1
Repairing SQL Failures with the SQL Repair Advisor
-
9.1
About the Oracle Database Fault Diagnosability Infrastructure
-
1
Getting Started with Database Administration
-
Part II Oracle Database Structure and Storage
- 10 Managing Control Files
-
11
Managing the Redo Log
- 11.1 What Is the Redo Log?
- 11.2 Planning the Redo Log
- 11.3 Creating Redo Log Groups and Members
- 11.4 Relocating and Renaming Redo Log Members
- 11.5 Dropping Redo Log Groups and 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.3 Controlling Archiving
- 12.4 Specifying Archive Destinations
- 12.5 About Log Transmission Modes
- 12.6 Managing Archive Destination Failure
- 12.7 Controlling Trace Output Generated by the Archivelog Process
- 12.8 Viewing Information About the Archived Redo Log
-
13
Managing Tablespaces
- 13.1 Guidelines for Managing Tablespaces
- 13.2 Creating Tablespaces
- 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.7 Using Read-Only Tablespaces
- 13.8 Altering and Maintaining Tablespaces
- 13.9 Renaming Tablespaces
- 13.10 Dropping Tablespaces
-
13.11
Managing Lost Write Protection with Shadow Tablespaces
- 13.11.1 About Shadow Lost Write Protection
- 13.11.2 Creating Shadow Tablespaces for Shadow Lost Write Protection
- 13.11.3 Enabling Shadow Lost Write Protection for a Database
- 13.11.4 Enabling Shadow Lost Write Protection for Tablespaces and Data Files
- 13.11.5 Disabling Shadow Lost Write Protection for a Database
- 13.11.6 Removing or Suspending Shadow Lost Write Protection
- 13.11.7 Dropping a Shadow Tablespace
- 13.12 Managing the SYSAUX Tablespace
-
13.13
Correcting Problems with Locally Managed Tablespaces
- 13.13.1 Diagnosing and Repairing Locally Managed Tablespace Problems
- 13.13.2 Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)
- 13.13.3 Scenario 2: Dropping a Corrupted Segment
- 13.13.4 Scenario 3: Fixing Bitmap Where Overlap is Reported
- 13.13.5 Scenario 4: Correcting Media Corruption of Bitmap Blocks
- 13.13.6 Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace
- 13.14 Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
- 13.15 Viewing Information About Tablespaces
-
14
Managing Data Files and Temp Files
- 14.1 Guidelines for Managing Data Files
- 14.2 Creating Data Files and Adding Data Files to a Tablespace
- 14.3 Changing Data File Size
- 14.4 Altering Data File Availability
- 14.5 Renaming and Relocating Data Files
- 14.6 Dropping Data Files
- 14.7 Verifying Data Blocks in Data Files
- 14.8 Copying Files Using the Database Server
- 14.9 Mapping Files to Physical Devices
- 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.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.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.5 Converting Data Between Platforms
- 15.6 Guidelines for Transferring Data Files
-
15.1
About Transporting Data
-
16
Managing Undo
- 16.1 What Is Undo?
- 16.2 Introduction to Automatic Undo Management
- 16.3 Setting the Minimum Undo Retention Period
- 16.4 Sizing a Fixed-Size Undo Tablespace
- 16.5 Managing Undo Tablespaces
- 16.6 Migrating to Automatic Undo Management
- 16.7 Managing Temporary Undo
- 16.8 Undo Space Data Dictionary Views
-
17
Using Oracle Managed Files
- 17.1 About Oracle Managed Files
- 17.2 Enabling the Creation and Use of Oracle Managed Files
-
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.5 Creating Temp Files for Temporary Tablespaces Using Oracle Managed Files
- 17.3.6 Creating Control Files Using Oracle Managed Files
- 17.3.7 Creating Redo Log Files Using Oracle Managed Files
- 17.3.8 Creating Archived Logs Using Oracle Managed Files
- 17.4 Operation of Oracle Managed Files
- 17.5 Scenarios for Using Oracle Managed Files
-
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.3 Truncating Tables and Clusters
- 18.4 Enabling and Disabling Triggers
- 18.5 Managing Integrity Constraints
- 18.6 Renaming Schema Objects
- 18.7 Managing Object Dependencies
- 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.6 Using an Edition
- 18.10.7 Editions Data Dictionary Views
- 18.11 Displaying Information About Schema Objects
-
19
Managing Space for Schema Objects
- 19.1 Managing Tablespace Alerts
-
19.2
Managing Resumable Space Allocation
- 19.2.1 Resumable Space Allocation Overview
- 19.2.2 Enabling and Disabling Resumable Space Allocation
- 19.2.3 Using a LOGON Trigger to Set Default Resumable Mode
- 19.2.4 Detecting Suspended Statements
- 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.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.7 Capacity Planning for Database Objects
-
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.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.4
Loading Tables
- 20.4.1 Methods for Loading Tables
- 20.4.2 Improving INSERT Performance 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.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.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.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.13 Aborting Online Table Redefinition and Cleaning Up After Errors
- 20.8.14 Online Redefinition of One or More Partitions
- 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.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.4 Creating Secondary Indexes on Index-Organized Tables
- 20.13.5 Analyzing Index-Organized Tables
- 20.13.6 Using the ORDER BY Clause with Index-Organized Tables
- 20.13.7 Converting Index-Organized Tables to Regular Tables
- 20.14 Managing Partitioned Tables
-
20.15
Managing External Tables
- 20.15.1 About External Tables
- 20.15.2 Creating External Tables
- 20.15.3 Altering External Tables
- 20.15.4 Preprocessing External Tables
- 20.15.5 Overriding Parameters for External Tables in a Query
- 20.15.6 Using Inline External Tables
- 20.15.7 Partitioning External Tables
- 20.15.8 Dropping External Tables
- 20.15.9 System and Object Privileges for External Tables
- 20.16 Managing Hybrid Partitioned Tables
- 20.17 Tables Data Dictionary Views
-
21
Managing Indexes
- 21.1 About Indexes
-
21.2
Guidelines for Managing Indexes
- 21.2.1 Create Indexes After Inserting Table Data
- 21.2.2 Index the Correct Tables and Columns
- 21.2.3 Order Index Columns for Performance
- 21.2.4 Limit the Number of Indexes for Each Table
- 21.2.5 Drop Indexes That Are No Longer Required
- 21.2.6 Indexes and Deferred Segment Creation
- 21.2.7 Estimate Index Size and Set Storage Parameters
- 21.2.8 Specify the Tablespace for Each Index
- 21.2.9 Consider Parallelizing Index Creation
- 21.2.10 Consider Creating Indexes with NOLOGGING
- 21.2.11 Understand When to Use Unusable or Invisible Indexes
- 21.2.12 Understand When to Create Multiple Indexes on the Same Set of Columns
- 21.2.13 Consider Costs and Benefits of Coalescing or Rebuilding Indexes
- 21.2.14 Consider Cost Before Disabling or Dropping Constraints
- 21.2.15 Consider Using the In-Memory Column Store to Reduce the Number of Indexes
-
21.3
Creating Indexes
- 21.3.1 Prerequisites for Creating Indexes
- 21.3.2 Creating an Index Explicitly
- 21.3.3 Creating a Unique Index Explicitly
- 21.3.4 Creating an Index Associated with a Constraint
- 21.3.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.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.5 Monitoring Space Use of Indexes
- 21.6 Dropping Indexes
- 21.7 Managing Auto Indexes
- 21.8 Indexes Data Dictionary Views
-
22
Managing Clusters
- 22.1 About Clusters
-
22.2
Guidelines for Managing Clusters
- 22.2.1 Choose Appropriate Tables for the Cluster
- 22.2.2 Choose Appropriate Columns for the Cluster Key
- 22.2.3 Specify the Space Required by an Average Cluster Key and Its Associated Rows
- 22.2.4 Specify the Location of Each Cluster and Cluster Index Rows
- 22.2.5 Estimate Cluster Size and Set Storage Parameters
- 22.3 Creating Clusters and Objects That Use Them
- 22.4 Altering Clusters and Objects That Use Them
- 22.5 Dropping Clusters and Objects That Use Them
- 22.6 Clusters Data Dictionary Views
-
23
Managing Hash Clusters
- 23.1 About Hash Clusters
- 23.2 When to Use Hash Clusters
- 23.3 Creating Different Types of 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.3 Replacing Views
- 24.1.4 Using Views in Queries
- 24.1.5 DML Statements and Join Views
- 24.1.6 Altering Views
- 24.1.7 Dropping Views
- 24.2 Managing Sequences
- 24.3 Managing Synonyms
- 24.4 Views, Synonyms, and Sequences Data Dictionary Views
-
24.1
Managing Views
-
25
Repairing Corrupted Data
- 25.1 Options for Repairing Data Block Corruption
- 25.2 About the DBMS_REPAIR Package
- 25.3 Using the DBMS_REPAIR Package
- 25.4 DBMS_REPAIR Examples
-
18
Managing Schema Objects
-
Part IV Database Resource Management and Task Scheduling
- 26 Managing Automated Database Maintenance Tasks
-
27
Managing Resources with Oracle Database Resource Manager
- 27.1 About Oracle Database Resource Manager
-
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.4 Switching Resource Consumer Groups
- 27.2.5 Specifying Automatic Consumer Group Switching
- 27.2.6 Granting and Revoking the Switch Privilege
- 27.3 The Types of Resources Managed by the Resource Manager
- 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.6 Creating 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.8 Managing Multiple Database Instances on a Single Server
- 27.9 Maintaining Consumer Groups, Plans, and Directives
- 27.10 Viewing Database Resource Manager Configuration and Status
- 27.11 Monitoring Oracle Database Resource Manager
- 27.12 Interacting with Operating-System Resource Control
- 27.13 Oracle Database Resource Manager Reference
-
28
Oracle Scheduler Concepts
- 28.1 Overview of Oracle Scheduler
- 28.2 Jobs and Supporting Scheduler Objects
-
28.3
More About Jobs
- 28.3.1 Job Categories
- 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.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.3 Specifying Scheduler Job Credentials
- 29.2.2.4 Specifying Destinations
- 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.4 Creating and Managing Schedules to Define Jobs
-
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.3 Starting a Job When a File Arrives on a System
-
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.8 Managing Job Resources
-
29.9
Prioritizing Jobs
- 29.9.1 Managing Job Priorities with 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.4 Managing Job Scheduling and Job Priorities with Window Groups
- 29.9.5 Allocating Resources Among Jobs Using Resource Manager
- 29.9.6 Example of Resource Allocation for Jobs
- 29.10 Monitoring Jobs
-
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.2 Monitoring and Managing the Scheduler
- 30.3 Import/Export and the Scheduler
- 30.4 Troubleshooting the Scheduler
- 30.5 Examples of Using the Scheduler
- 30.6 Scheduler Reference
-
30.1
Configuring Oracle Scheduler
-
Part V Distributed Database Management
-
31
Distributed Database Concepts
- 31.1 Distributed 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.9 Creation of Database Links: Examples
- 31.2.10 Schema Objects and Database Links
- 31.2.11 Database Link Restrictions
-
31.3
Distributed Database Administration
- 31.3.1 Site Autonomy
- 31.3.2 Distributed Database Security
- 31.3.3 Auditing Database Links
- 31.3.4 Administration Tools
-
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.8 Schema Object Name Resolution
- 31.4.9 Global Name Resolution in Views, Synonyms, and Procedures
- 31.5 Distributed Database Application Development
- 31.6 Character Set Support for Distributed Environments
-
32
Managing a Distributed Database
- 32.1 Managing Global Names in a Distributed System
- 32.2 Creating Database Links
- 32.3 Using Shared Database Links
- 32.4 Managing Database Links
- 32.5 Viewing Information About Database Links
- 32.6 Creating Location Transparency
- 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.5 Handling Errors in Remote Procedures
-
34
Distributed Transactions Concepts
- 34.1 What Are Distributed Transactions?
- 34.2 Session Trees for Distributed Transactions
- 34.3 Two-Phase Commit Mechanism
- 34.4 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.4 Deciding How to Handle In-Doubt Transactions
- 35.5 Manually Overriding In-Doubt Transactions
- 35.6 Purging Pending Rows from the Data Dictionary
- 35.7 Manually Committing an In-Doubt Transaction: Example
- 35.8 Data Access Failures Due to Locks
- 35.9 Simulating Distributed Transaction Failure
- 35.10 Managing Read Consistency
-
31
Distributed Database Concepts
-
Part VI Managing Read-Only Materialized Views
-
36
Read-Only Materialized View Concepts
- 36.1 Replication Databases
- 36.2 Read-Only Materialized Views
- 36.3 The Uses of Materialized Views
- 36.4 Available Materialized Views
- 36.5 Users and Privileges Related to Materialized Views
- 36.6 Data Subsetting with Materialized Views
- 36.7 Materialized View Refresh
- 36.8 Refresh Groups
- 36.9 Materialized View Log
-
36.10
Materialized Views and User-Defined Data Types
- 36.10.1 How Materialized Views Work with Object Types and Collections
- 36.10.2 Type Agreement at Replication Databases
- 36.10.3 Column Subsetting of Masters with Column Objects
- 36.10.4 Materialized Views Based on Object Tables
- 36.10.5 Materialized Views with Collection Columns
- 36.10.6 Materialized Views with REF Columns
- 36.11 Materialized View Registration at a Master Database
- 37 Read-Only Materialized View Architecture
-
38
Planning for Read-Only Materialized Views
- 38.1 Considerations for Master Tables
- 38.2 Planning for Master Databases and Materialized View Databases
-
39
Creating and Managing Read-Only Materialized Views
- 39.1 Creating Read-Only Materialized Views
- 39.2 Creating Refresh Groups
- 39.3 Refreshing Materialized Views
- 39.4 Determining the Fast Refresh Capabilities of a Materialized View
- 39.5 Adding a New Materialized View Database
- 39.6 Monitoring Materialized View Logs
- 39.7 Monitoring Materialized Views
- 40 Troubleshooting Problems with Read-Only Materialized Views
-
36
Read-Only Materialized View Concepts
- Appendixes
- Index