ITPub博客

19c 作为 Oracle Database 12c 的最终稳定版版，出来也有一些时间了，经过一年多的认真学习及实验，由于内容的快速迭代，从18c开始，将《Database Administrator’s Guide》 分为了两部分，分别为《Database Administrator’s Guide》和《Multitenant Administrator's Guide》两本手册。出于分享和探讨的目的，特开启这两部手册的翻译，一起分享，共同进步，希望对各位同行有所帮助。 原文档信息如下： https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/index.html Oracle® Database\Database Administrator’s Guide \19c \E96348-06\December 2019

Part I Basic Database Administration
1 Getting Started with Database Administration
1.1 Types of Oracle Database Users 1-1
1.1.1 Database Administrators 1-2
1.1.2 Security Officers 1-3
1.1.3 Network Administrators 1-3
1.1.4 Application Developers 1-3
1.1.5 Application Administrators 1-4
1.1.6 Database Users 1-4
1.2 Tasks of a Database Administrator 1-4
1.2.1 Task 1: Evaluate the Database Server Hardware 1-5
1.2.2 Task 2: Install the Oracle Database Software 1-5
1.2.3 Task 3: Plan the Database 1-6
1.2.4 Task 4: Create and Open the Database 1-6
1.2.5 Task 5: Back Up the Database 1-7
iii
1.2.6 Task 6: Enroll System Users 1-7
1.2.7 Task 7: Implement the Database Design 1-7
1.2.8 Task 8: Back Up the Fully Functional Database 1-7
1.2.9 Task 9: Tune Database Performance 1-7
1.2.10 Task 10: Download and Install Release Updates and Release Update
Revisions 1-8
1.2.11 Task 11: Roll Out to Additional Hosts 1-8
1.3 SQL Statements 1-9
1.3.1 Submitting Commands and SQL to the Database 1-9
1.3.2 About SQL*Plus 1-10
1.3.3 Connecting to the Database with SQL*Plus 1-10
1.3.3.1 About Connecting to the Database with SQL*Plus 1-11
1.3.3.2 Step 1: Open a Command Window 1-11
1.3.3.3 Step 2: Set Operating System Environment Variables 1-11
1.3.3.4 Step 3: Start SQL*Plus 1-12
1.3.3.5 Step 4: Submit the SQL*Plus CONNECT Command 1-12
1.4 Identifying Your Oracle Database Software Release 1-17
1.4.1 About Oracle Database Release Numbers 1-17
1.4.2 Checking Your Current Release Number 1-19
1.5 About Database Administrator Security and Privileges 1-19
1.5.1 The Database Administrator's Operating System Account 1-20
1.5.2 Administrative User Accounts 1-20
1.5.2.1 About Administrative User Accounts 1-21
1.5.2.2 SYS 1-21
1.5.2.3 SYSTEM 1-22
1.5.2.4 SYSBACKUP, SYSDG, SYSKM, and SYSRAC 1-22
1.5.2.5 The DBA Role 1-23
1.6 Database Administrator Authentication 1-23
1.6.1 Administrative Privileges 1-24
1.6.2 Operations Authorized by Administrative Privileges 1-25
1.6.3 Authentication Methods for Database Administrators 1-27
1.6.3.1 About Authentication Methods for Database Administrators 1-27
1.6.3.2 Nonsecure Remote Connections 1-29
1.6.3.3 Local Connections and Secure Remote Connections 1-29
1.6.4 Using Operating System Authentication 1-30
1.6.4.1 Operating System Groups 1-30
1.6.4.2 Preparing to Use Operating System Authentication 1-32
1.6.4.3 Connecting Using Operating System Authentication 1-32
1.6.5 Using Password File Authentication 1-33
1.6.5.1 Preparing to Use Password File Authentication 1-33
1.6.5.2 Connecting Using Password File Authentication 1-35
iv
1.7 Creating and Maintaining a Database Password File 1-36
1.7.1 ORAPWD Syntax and Command Line Argument Descriptions 1-36
1.7.2 Creating a Database Password File with ORAPWD 1-43
1.7.3 Sharing and Disabling the Database Password File 1-44
1.7.4 Keeping Administrator Passwords Synchronized with the Data
Dictionary 1-45
1.7.5 Adding Users to a Database Password File 1-46
1.7.6 Granting and Revoking Administrative Privileges 1-47
1.7.7 Viewing Database Password File Members 1-48
1.7.8 Removing a Database Password File 1-48
1.8 Data Utilities 1-49
2 Creating and Configuring an Oracle Database
2.1 About Creating an Oracle Database 2-2
2.2 Considerations Before Creating the Database 2-3
2.2.1 Planning for Database Creation 2-3
2.2.2 About Selecting a Character Set 2-5
2.2.3 About Configuring an Oracle Home in Read-Only Mode 2-6
2.2.4 Prerequisites for Database Creation 2-7
2.3 Creating a Database with DBCA 2-7
2.3.1 About Creating a Database with DBCA 2-8
2.3.2 About Creating a Database with Interactive DBCA 2-8
2.3.3 About Creating a Database with Noninteractive/Silent DBCA 2-8
2.4 Creating a Database with the CREATE DATABASE Statement 2-8
2.4.1 About Creating a Database with the CREATE DATABASE Statement 2-10
2.4.2 Step 1: Specify an Instance Identifier (SID) 2-11
2.4.3 Step 2: Ensure That the Required Environment Variables Are Set 2-12
2.4.4 Step 3: Choose a Database Administrator Authentication Method 2-12
2.4.5 Step 4: Create the Initialization Parameter File 2-13
2.4.6 Step 5: (Windows Only) Create an Instance 2-14
2.4.7 Step 6: Connect to the Instance 2-14
2.4.8 Step 7: Create a Server Parameter File 2-15
2.4.9 Step 8: Start the Instance 2-16
2.4.10 Step 9: Issue the CREATE DATABASE Statement 2-17
2.4.11 Step 10: Create Additional Tablespaces 2-20
2.4.12 Step 11: Run Scripts to Build Data Dictionary Views 2-21
2.4.13 Step 12: (Optional) Run Scripts to Install Additional Options 2-22
2.4.14 Step 13: Back Up the Database 2-22
2.4.15 Step 14: (Optional) Enable Automatic Instance Startup 2-23
2.5 Specifying CREATE DATABASE Statement Clauses 2-23
2.5.1 About CREATE DATABASE Statement Clauses 2-24
v
2.5.2 Protecting Your Database: Specifying Passwords for SYS and SYSTEM
Users 2-24
2.5.3 Creating a Locally Managed SYSTEM Tablespace 2-25
2.5.4 Specify Data File Attributes for the SYSAUX Tablespace 2-26
2.5.4.1 About the SYSAUX Tablespace 2-26
2.5.5 Using Automatic Undo Management: Creating an Undo Tablespace 2-27
2.5.6 Creating a Default Permanent Tablespace 2-27
2.5.7 Creating a Default Temporary Tablespace 2-28
2.5.8 Specifying Oracle Managed Files at Database Creation 2-29
2.5.9 Supporting Bigfile Tablespaces During Database Creation 2-30
2.5.9.1 Specifying the Default Tablespace Type 2-31
2.5.9.2 Overriding the Default Tablespace Type 2-31
2.5.10 Specifying the Database Time Zone and Time Zone File 2-32
2.5.10.1 Setting the Database Time Zone 2-32
2.5.10.2 About the Database Time Zone Files 2-32
2.5.10.3 Specifying the Database Time Zone File 2-33
2.5.11 Specifying FORCE LOGGING Mode 2-33
2.5.11.1 Using the FORCE LOGGING Clause 2-34
2.5.11.2 Performance Considerations of FORCE LOGGING Mode 2-34
2.6 Specifying Initialization Parameters 2-35
2.6.1 About Initialization Parameters and Initialization Parameter Files 2-36
2.6.1.1 Sample Initialization Parameter File 2-37
2.6.1.2 Text Initialization Parameter File Format 2-38
2.6.2 Determining the Global Database Name 2-39
2.6.2.1 DB_NAME Initialization Parameter 2-39
2.6.2.2 DB_DOMAIN Initialization Parameter 2-40
2.6.3 Specifying a Fast Recovery Area 2-40
2.6.4 Specifying Control Files 2-41
2.6.5 Specifying Database Block Sizes 2-41
2.6.5.1 DB_BLOCK_SIZE Initialization Parameter 2-42
2.6.5.2 Nonstandard Block Sizes 2-42
2.6.6 Specifying the Maximum Number of Processes 2-43
2.6.7 Specifying the DDL Lock Timeout 2-43
2.6.8 Specifying the Method of Undo Space Management 2-44
2.6.8.1 UNDO_MANAGEMENT Initialization Parameter 2-45
2.6.8.2 UNDO_TABLESPACE Initialization Parameter 2-45
2.6.9 Specifying the Database Compatibility Level 2-45
2.6.9.1 About the COMPATIBLE Initialization Parameter 2-46
2.6.10 Setting the License Parameter 2-47
2.7 Managing Initialization Parameters Using a Server Parameter File 2-47
2.7.1 What Is a Server Parameter File? 2-48
vi
2.7.2 Migrating to a Server Parameter File 2-49
2.7.3 Server Parameter File Default Names and Locations 2-50
2.7.4 Creating a Server Parameter File 2-51
2.7.5 The SPFILE Initialization Parameter 2-51
2.7.6 Changing Initialization Parameter Values 2-52
2.7.6.1 About Changing Initialization Parameter Values 2-52
2.7.6.2 Setting or Changing Initialization Parameter Values 2-52
2.7.7 Clearing Initialization Parameter Values 2-54
2.7.8 Exporting the Server Parameter File 2-55
2.7.9 Backing Up the Server Parameter File 2-56
2.7.10 Recovering a Lost or Damaged Server Parameter File 2-56
2.7.11 Methods for Viewing Parameter Settings 2-57
2.8 Managing Application Workloads with Database Services 2-58
2.8.1 Database Services 2-58
2.8.1.1 About Database Services 2-59
2.8.1.2 Database Services and Performance 2-60
2.8.1.3 Oracle Database Features That Use Database Services 2-60
2.8.1.4 Creating Database Services 2-61
2.8.2 Global Data Services 2-62
2.8.3 Database Service Data Dictionary Views 2-63
2.9 Considerations After Creating a Database 2-63
2.9.1 Database Security 2-64
2.9.2 Transparent Data Encryption 2-65
2.9.3 A Secure External Password Store 2-66
2.9.4 Transaction Guard and Application Continuity 2-66
2.9.5 File System Server Support in the Database 2-67
2.9.6 The Oracle Database Sample Schemas 2-68
2.10 Cloning a Database 2-68
2.10.1 Cloning a Database with CloneDB in a Non-multitenant Environment 2-69
2.10.1.1 About Cloning a Database with CloneDB 2-69
2.10.1.2 Cloning a Database with CloneDB 2-70
2.10.1.3 After Cloning a Database with CloneDB 2-75
2.10.2 Cloning a Database in a Multitenant Environment 2-75
2.10.3 Cloning a Database with Oracle Automatic Storage Management
(Oracle ASM) 2-76
2.11 Dropping a Database 2-76
2.12 Database Data Dictionary Views 2-76
2.13 Database Configuration Assistant Command Reference for Silent Mode 2-77
2.13.1 DBCA Command-Line Syntax Overview 2-77
2.13.2 About DBCA Templates 2-79
2.13.3 Database User Authentication in DBCA Commands Using Oracle
Wallet 2-79
vii
2.13.4 DBCA Silent Mode Commands 2-81
2.13.4.1 createDatabase 2-82
2.13.4.2 createDuplicateDB 2-89
2.13.4.3 configureDatabase 2-93
2.13.4.4 createTemplateFromDB 2-97
2.13.4.5 createTemplateFromTemplate 2-98
2.13.4.6 createCloneTemplate 2-101
2.13.4.7 deleteTemplate 2-102
2.13.4.8 generateScripts 2-103
2.13.4.9 deleteDatabase 2-110
2.13.4.10 createPluggableDatabase 2-112
2.13.4.11 unplugDatabase 2-117
2.13.4.12 deletePluggableDatabase 2-119
2.13.4.13 relocatePDB 2-119
2.13.4.14 configurePluggableDatabase 2-120
2.13.4.15 addInstance 2-123
2.13.4.16 deleteInstance 2-124
2.13.4.17 executePrereqs 2-125
2.13.5 DBCA Exit Codes 2-126
3 Starting Up and Shutting Down
3.1 Starting Up a Database 3-1
3.1.1 About Database Startup Options 3-2
3.1.1.1 Starting Up a Database Using SQL*Plus 3-3
3.1.1.2 Starting Up a Database Using Recovery Manager 3-3
3.1.1.3 Starting Up a Database Using Cloud Control 3-3
3.1.1.4 Starting Up a Database Using SRVCTL 3-3
3.1.2 Specifying Initialization Parameters at Startup 3-4
3.1.2.1 About Initialization Parameter Files and Startup 3-4
3.1.2.2 Starting Up with SQL*Plus with a Nondefault Server Parameter
File 3-6
3.1.2.3 Starting Up with SRVCTL with a Nondefault Server Parameter
File 3-6
3.1.3 About Automatic Startup of Database Services 3-7
3.1.4 Preparing to Start Up an Instance 3-7
3.1.5 Starting Up an Instance 3-8
3.1.5.1 About Starting Up an Instance 3-9
3.1.5.2 Starting an Instance, and Mounting and Opening a Database 3-10
3.1.5.3 Starting an Instance Without Mounting a Database 3-10
3.1.5.4 Starting an Instance and Mounting a Database 3-11
3.1.5.5 Restricting Access to an Instance at Startup 3-11
viii
3.1.5.6 Forcing an Instance to Start 3-12
3.1.5.7 Starting an Instance, Mounting a Database, and Starting
Complete Media Recovery 3-13
3.1.5.8 Automatic Database Startup at Operating System Start 3-13
3.1.5.9 Starting Remote Instances 3-13
3.2 Altering Database Availability 3-13
3.2.1 Mounting a Database to an Instance 3-14
3.2.2 Opening a Closed Database 3-14
3.2.3 Opening a Database in Read-Only Mode 3-14
3.2.4 Restricting Access to an Open Database 3-16
3.3 Shutting Down a Database 3-16
3.3.1 About Shutting Down the Database 3-17
3.3.2 Shutting Down with the Normal Mode 3-17
3.3.3 Shutting Down with the Immediate Mode 3-18
3.3.4 Shutting Down with the Transactional Mode 3-18
3.3.5 Shutting Down with the Abort Mode 3-19
3.3.6 Shutdown Timeout 3-20
3.4 Quiescing a Database 3-20
3.4.1 About Quiescing a Database 3-20
3.4.2 Placing a Database into a Quiesced State 3-21
3.4.3 Restoring the System to Normal Operation 3-22
3.4.4 Viewing the Quiesce State of an Instance 3-23
3.5 Suspending and Resuming a Database 3-23
3.6 Delaying Instance Abort 3-24
4 Configuring Automatic Restart of an Oracle Database
4.1 About Oracle Restart 4-1
4.1.1 Oracle Restart Overview 4-2
4.1.2 About Startup Dependencies 4-3
4.1.3 About Starting and Stopping Components with Oracle Restart 4-3
4.1.4 About Starting and Stopping Oracle Restart 4-4
4.1.5 Oracle Restart Configuration 4-4
4.1.6 Oracle Restart Integration with Oracle Data Guard 4-6
4.1.7 Fast Application Notification with Oracle Restart 4-7
4.1.7.1 Overview of Fast Application Notification 4-7
4.1.7.2 Application High Availability with Services and FAN 4-8
4.2 Configuring Oracle Restart 4-12
4.2.1 About Configuring Oracle Restart 4-13
4.2.2 Preparing to Run SRVCTL 4-14
4.2.3 Obtaining Help for SRVCTL 4-15
4.2.4 Adding Components to the Oracle Restart Configuration 4-15
ix
4.2.5 Removing Components from the Oracle Restart Configuration 4-17
4.2.6 Disabling and Enabling Oracle Restart Management for a Component 4-18
4.2.7 Viewing Component Status 4-19
4.2.8 Viewing the Oracle Restart Configuration for a Component 4-19
4.2.9 Modifying the Oracle Restart Configuration for a Component 4-20
4.2.10 Managing Environment Variables in the Oracle Restart Configuration 4-21
4.2.10.1 About Environment Variables in the Oracle Restart Configuration 4-21
4.2.10.2 Setting and Unsetting Environment Variables 4-22
4.2.10.3 Viewing Environment Variables 4-22
4.2.11 Creating and Deleting Database Services with SRVCTL 4-23
4.2.12 Enabling FAN Events in an Oracle Restart Environment 4-24
4.2.13 Automating the Failover of Connections Between Primary and Standby
Databases 4-25
4.2.14 Enabling Clients for Fast Connection Failover 4-26
4.2.14.1 About Enabling Clients for Fast Connection Failover 4-26
4.2.14.2 Enabling Fast Connection Failover for JDBC Clients 4-27
4.2.14.3 Enabling Fast Connection Failover for Oracle Call Interface
Clients 4-28
4.2.14.4 Enabling Fast Connection Failover for ODP.NET Clients 4-29
4.3 Starting and Stopping Components Managed by Oracle Restart 4-30
4.4 Stopping and Restarting Oracle Restart for Maintenance Operations 4-33
4.5 SRVCTL Command Reference for Oracle Restart 4-34
4.5.1 add 4-37
4.5.1.1 srvctl add asm 4-38
4.5.1.2 srvctl add database 4-39
4.5.1.3 srvctl add listener 4-41
4.5.1.4 srvctl add ons 4-42
4.5.1.5 srvctl add service 4-43
4.5.2 config 4-47
4.5.2.1 srvctl config asm 4-48
4.5.2.2 srvctl config database 4-48
4.5.2.3 srvctl config listener 4-49
4.5.2.4 srvctl config ons 4-50
4.5.2.5 srvctl config service 4-50
4.5.3 disable 4-51
4.5.3.1 srvctl disable asm 4-52
4.5.3.2 srvctl disable database 4-52
4.5.3.3 srvctl disable diskgroup 4-53
4.5.3.4 srvctl disable listener 4-53
4.5.3.5 srvctl disable ons 4-54
4.5.3.6 srvctl disable service 4-54
4.5.4 downgrade 4-55
x
4.5.4.1 srvctl downgrade database 4-55
4.5.5 enable 4-55
4.5.5.1 srvctl enable asm 4-56
4.5.5.2 srvctl enable database 4-56
4.5.5.3 srvctl enable diskgroup 4-57
4.5.5.4 srvctl enable listener 4-57
4.5.5.5 srvctl enable ons 4-58
4.5.5.6 srvctl enable service 4-58
4.5.6 getenv 4-59
4.5.6.1 srvctl getenv asm 4-59
4.5.6.2 srvctl getenv database 4-60
4.5.6.3 srvctl getenv listener 4-61
4.5.7 modify 4-61
4.5.7.1 srvctl modify asm 4-62
4.5.7.2 srvctl modify database 4-62
4.5.7.3 srvctl modify listener 4-63
4.5.7.4 srvctl modify ons 4-64
4.5.7.5 srvctl modify service 4-65
4.5.8 remove 4-69
4.5.8.1 srvctl remove asm 4-70
4.5.8.2 srvctl remove database 4-70
4.5.8.3 srvctl remove diskgroup 4-71
4.5.8.4 srvctl remove listener 4-72
4.5.8.5 srvctl remove ons 4-72
4.5.8.6 srvctl remove service 4-73
4.5.9 setenv 4-73
4.5.9.1 srvctl setenv asm 4-74
4.5.9.2 srvctl setenv database 4-75
4.5.9.3 srvctl setenv listener 4-75
4.5.10 start 4-76
4.5.10.1 srvctl start asm 4-77
4.5.10.2 srvctl start database 4-77
4.5.10.3 srvctl start diskgroup 4-78
4.5.10.4 srvctl start home 4-79
4.5.10.5 srvctl start listener 4-79
4.5.10.6 srvctl start ons 4-80
4.5.10.7 srvctl start service 4-80
4.5.11 status 4-81
4.5.11.1 srvctl status asm 4-82
4.5.11.2 srvctl status database 4-82
4.5.11.3 srvctl status diskgroup 4-83
xi
4.5.11.4 srvctl status home 4-84
4.5.11.5 srvctl status listener 4-84
4.5.11.6 srvctl status ons 4-85
4.5.11.7 srvctl status service 4-85
4.5.12 stop 4-86
4.5.12.1 srvctl stop asm 4-87
4.5.12.2 srvctl stop database 4-87
4.5.12.3 srvctl stop diskgroup 4-88
4.5.12.4 srvctl stop home 4-89
4.5.12.5 srvctl stop listener 4-90
4.5.12.6 srvctl stop ons 4-90
4.5.12.7 srvctl stop service 4-91
4.5.13 unsetenv 4-92
4.5.13.1 srvctl unsetenv asm 4-93
4.5.13.2 srvctl unsetenv database 4-93
4.5.13.3 srvctl unsetenv listener 4-94
4.5.14 update 4-94
4.5.14.1 srvctl update database 4-95
4.5.15 upgrade 4-95
4.5.15.1 srvctl upgrade database 4-95
4.6 CRSCTL Command Reference 4-96
4.6.1 check 4-97
4.6.2 config 4-97
4.6.3 disable 4-97
4.6.4 enable 4-97
4.6.5 start 4-97
4.6.6 stop 4-97
5 Managing Processes
5.1 About Dedicated and Shared Server Processes 5-2
5.1.1 Dedicated Server Processes 5-2
5.1.2 Shared Server Processes 5-3
5.2 About Database Resident Connection Pooling 5-5
5.2.1 Comparing DRCP to Dedicated Server and Shared Server 5-7
5.3 About Proxy Resident Connection Pooling 5-8
5.4 Configuring Oracle Database for Shared Server 5-9
5.4.1 Initialization Parameters for Shared Server 5-10
5.4.2 Memory Management for Shared Server 5-10
5.4.3 Enabling Shared Server 5-10
5.4.3.1 About Determining a Value for SHARED_SERVERS 5-12
xii
5.4.3.2 Decreasing the Number of Shared Server Processes 5-12
5.4.3.3 Limiting the Number of Shared Server Processes 5-13
5.4.3.4 Limiting the Number of Shared Server Sessions 5-13
5.4.3.5 Protecting Shared Memory 5-14
5.4.4 Configuring Dispatchers 5-14
5.4.4.1 DISPATCHERS Initialization Parameter Attributes 5-15
5.4.4.2 Determining the Number of Dispatchers 5-16
5.4.4.3 Setting the Initial Number of Dispatchers 5-17
5.4.4.4 Altering the Number of Dispatchers 5-18
5.4.4.5 Shutting Down Specific Dispatcher Processes 5-19
5.4.5 Disabling Shared Server 5-20
5.4.6 Shared Server Data Dictionary Views 5-20
5.5 Configuring Database Resident Connection Pooling 5-21
5.5.1 Enabling Database Resident Connection Pooling 5-22
5.5.2 Configuring the Connection Pool for Database Resident Connection
Pooling 5-23
5.5.2.1 Configuration Parameters for Database Resident Connection
Pooling 5-24
5.5.3 Data Dictionary Views for Database Resident Connection Pooling 5-26
5.5.4 Determining the States of Connections in the Connection Pool 5-26
5.6 About Oracle Database Background Processes 5-27
5.7 Managing Prespawned Processes 5-29
5.7.1 About Managing Prespawned Processes 5-29
5.7.2 Managing Pools for Prespawned Processes 5-29
5.8 Managing Processes for Parallel SQL Execution 5-31
5.8.1 About Parallel Execution Servers 5-31
5.8.2 Altering Parallel Execution for a Session 5-32
5.8.2.1 Disabling Parallel SQL Execution 5-33
5.8.2.2 Enabling Parallel SQL Execution 5-33
5.8.2.3 Forcing Parallel SQL Execution 5-33
5.9 Managing Processes for External Procedures 5-34
5.9.1 About External Procedures 5-34
5.9.2 DBA Tasks to Enable External Procedure Calls 5-34
5.10 Terminating Sessions 5-36
5.10.1 About Terminating Sessions 5-36
5.10.2 Identifying Which Session to Terminate 5-37
5.10.3 Terminating an Active Session 5-37
5.10.4 Terminating an Inactive Session 5-38
5.10.5 Cancelling a SQL Statement in a Session 5-39
5.11 Process and Session Data Dictionary Views 5-40
xiii
6 Managing Memory
6.1 About Memory Management 6-2
6.2 Memory Architecture Overview 6-3
6.3 Using Automatic Memory Management 6-5
6.3.1 About Automatic Memory Management 6-5
6.3.2 Enabling Automatic Memory Management 6-6
6.3.3 Monitoring and Tuning Automatic Memory Management 6-9
6.4 Configuring Memory Manually 6-10
6.4.1 About Manual Memory Management 6-10
6.4.2 Using Automatic Shared Memory Management 6-11
6.4.2.1 About Automatic Shared Memory Management 6-12
6.4.2.2 Components and Granules in the SGA 6-12
6.4.2.3 Setting Maximum SGA Size 6-13
6.4.2.4 Setting SGA Target Size 6-13
6.4.2.5 Enabling Automatic Shared Memory Management 6-16
6.4.2.6 Setting Minimums for Automatically Sized SGA Components 6-18
6.4.2.7 Dynamic Modification of SGA_TARGET 6-18
6.4.2.8 Modifying Parameters for Automatically Sized Components 6-19
6.4.2.9 Modifying Parameters for Manually Sized Components 6-20
6.4.3 Using Manual Shared Memory Management 6-20
6.4.3.1 About Manual Shared Memory Management 6-21
6.4.3.2 Enabling Manual Shared Memory Management 6-21
6.4.3.3 Setting the Buffer Cache Initialization Parameters 6-22
6.4.3.4 Specifying the Shared Pool Size 6-24
6.4.3.5 Specifying the Large Pool Size 6-25
6.4.3.6 Specifying the Java Pool Size 6-25
6.4.3.7 Specifying the Streams Pool Size 6-26
6.4.3.8 Specifying the Result Cache Maximum Size 6-26
6.4.3.9 Specifying Miscellaneous SGA Initialization Parameters 6-27
6.4.4 Using Automatic PGA Memory Management 6-28
6.4.5 Using Manual PGA Memory Management 6-29
6.5 Using Force Full Database Caching Mode 6-30
6.5.1 About Force Full Database Caching Mode 6-30
6.5.2 Before Enabling Force Full Database Caching Mode 6-31
6.5.3 Enabling Force Full Database Caching Mode 6-32
6.5.4 Disabling Force Full Database Caching Mode 6-32
6.6 Configuring Database Smart Flash Cache 6-33
6.6.1 When to Configure Database Smart Flash Cache 6-33
6.6.2 Sizing Database Smart Flash Cache 6-34
6.6.3 Tuning Memory for Database Smart Flash Cache 6-34
xiv
6.6.4 Database Smart Flash Cache Initialization Parameters 6-35
6.6.5 Database Smart Flash Cache in an Oracle Real Applications Clusters
Environment 6-36
6.7 Improving Query Performance with Oracle Database In-Memory 6-36
6.8 Enabling High Performance Data Streaming with the Memoptimized Rowstore 6-38
6.9 Memory Management Reference 6-39
6.9.1 Platforms That Support Automatic Memory Management 6-39
6.9.2 Memory Management Data Dictionary Views 6-39
7 Managing Users and Securing the Database
7.1 The Importance of Establishing a Security Policy for Your Database 7-1
7.2 Managing Users and Resources 7-1
7.3 User Privileges and Roles 7-2
7.4 Auditing Database Activity 7-2
7.5 Predefined User Accounts 7-3
8 Monitoring the Database
8.1 Monitoring Errors and Alerts 8-1
8.1.1 Monitoring Errors with Trace Files and the Alert Log 8-1
8.1.1.1 About Monitoring Errors with Trace Files and the Alert Log 8-2
8.1.1.2 Controlling the Size of an Alert Log 8-3
8.1.1.3 Controlling the Size of Trace Files 8-3
8.1.1.4 Controlling When Oracle Database Writes to Trace Files 8-5
8.1.1.5 Reading the Trace File for Shared Server Sessions 8-6
8.1.2 Monitoring a Database with Server-Generated Alerts 8-6
8.1.2.1 About Monitoring a Database with Server-Generated Alerts 8-6
8.1.2.2 Setting and Retrieving Thresholds for Server-Generated Alerts 8-7
8.1.2.3 Viewing Server-Generated Alerts 8-9
8.1.2.4 Server-Generated Alerts Data Dictionary Views 8-10
8.2 Monitoring Performance 8-10
8.2.1 Monitoring Locks 8-11
8.2.2 About Monitoring Wait Events 8-11
8.2.3 Performance Monitoring Data Dictionary Views 8-12
8.3 Monitoring Quarantined Objects 8-12
8.3.1 About Object Quarantine 8-13
8.3.2 Viewing Quarantined Objects 8-13
9 Diagnosing and Resolving Problems
9.1 About the Oracle Database Fault Diagnosability Infrastructure 9-1
xv
9.1.1 Fault Diagnosability Infrastructure Overview 9-2
9.1.2 Incidents and Problems 9-3
9.1.2.1 About Incidents and Problems 9-4
9.1.2.2 Incident Flood Control 9-4
9.1.2.3 Related Problems Across the Topology 9-5
9.1.3 Fault Diagnosability Infrastructure Components 9-5
9.1.3.1 Automatic Diagnostic Repository (ADR) 9-6
9.1.3.2 Alert Log 9-7
9.1.3.3 Trace Files, Dumps, and Core Files 9-7
9.1.3.4 DDL Log 9-9
9.1.3.5 Debug Log 9-9
9.1.3.6 Other ADR Contents 9-10
9.1.3.7 Enterprise Manager Support Workbench 9-10
9.1.3.8 ADRCI Command-Line Utility 9-10
9.1.4 Structure, Contents, and Location of the Automatic Diagnostic
Repository 9-10
9.2 About Investigating, Reporting, and Resolving a Problem 9-15
9.2.1 Roadmap — Investigating, Reporting, and Resolving a Problem 9-16
9.2.2 Task 1: View Critical Error Alerts in Cloud Control 9-17
9.2.3 Task 2: View Problem Details 9-19
9.2.4 Task 3: (Optional) Gather Additional Diagnostic Information 9-19
9.2.5 Task 4: (Optional) Create a Service Request 9-19
9.2.6 Task 5: Package and Upload Diagnostic Data to Oracle Support 9-20
9.2.7 Task 6: Track the Service Request and Implement Any Repairs 9-21
9.3 Diagnosing Problems 9-22
9.3.1 Identifying Problems Reactively 9-23
9.3.1.1 Viewing Problems with the Support Workbench 9-24
9.3.1.2 Adding Problems Manually to the Automatic Diagnostic
Repository 9-25
9.3.1.3 Creating Incidents Manually 9-26
9.3.2 Identifying Problems Proactively with Health Monitor 9-27
9.3.2.1 About Health Monitor 9-27
9.3.2.2 Running Health Checks Manually 9-29
9.3.2.3 Viewing Checker Reports 9-31
9.3.2.4 Health Monitor Views 9-34
9.3.2.5 Health Check Parameters Reference 9-35
9.3.3 Gathering Additional Diagnostic Data 9-36
9.3.3.1 Viewing the Alert Log 9-36
9.3.3.2 Finding Trace Files 9-37
9.3.4 Creating Test Cases with SQL Test Case Builder 9-37
9.3.4.1 Purpose of SQL Test Case Builder 9-38
9.3.4.2 Concepts for SQL Test Case Builder 9-38
xvi
9.3.4.3 User Interfaces for SQL Test Case Builder 9-42
9.3.4.4 Running SQL Test Case Builder 9-45
9.4 Reporting Problems 9-47
9.4.1 Incident Packages 9-48
9.4.1.1 About Incident Packages 9-48
9.4.1.2 About Correlated Diagnostic Data in Incident Packages 9-49
9.4.1.3 About Quick Packaging and Custom Packaging 9-50
9.4.1.4 About Correlated Packages 9-51
9.4.2 Packaging and Uploading Problems with Custom Packaging 9-52
9.4.3 Viewing and Modifying Incident Packages 9-56
9.4.3.1 Viewing Package Details 9-57
9.4.3.2 Accessing the Customize Package Page 9-57
9.4.3.3 Editing Incident Package Files (Copying Out and In) 9-58
9.4.3.4 Adding an External File to an Incident Package 9-59
9.4.3.5 Removing Incident Package Files 9-59
9.4.3.6 Viewing and Updating the Incident Package Activity Log 9-60
9.4.4 Creating, Editing, and Uploading Correlated Packages 9-60
9.4.5 Deleting Correlated Packages 9-61
9.4.6 Setting Incident Packaging Preferences 9-62
9.5 Resolving Problems 9-63
9.5.1 Repairing SQL Failures with the SQL Repair Advisor 9-63
9.5.1.1 About the SQL Repair Advisor 9-64
9.5.1.2 Running the SQL Repair Advisor Using Cloud Control 9-64
9.5.1.3 Running the SQL Repair Advisor Using the DBMS_SQLDIAG
Package Subprograms 9-66
9.5.1.4 Viewing, Disabling, or Removing a SQL Patch Using Cloud
Control 9-68
9.5.1.5 Disabling or Removing a SQL Patch Using DBMS_SQLDIAG
Package Subprograms 9-68
9.5.1.6 Exporting and Importing a Patch Using DBMS_SQLDIAG
Package Subprograms 9-69
9.5.2 Repairing Data Corruptions with the Data Recovery Advisor 9-70
9.5.3 Quarantine for Execution Plans for SQL Statements Consuming
Excessive System Resources 9-71
9.5.3.1 About Quarantine for Execution Plans for SQL Statements 9-72
9.5.3.2 Creating a Quarantine Configuration for an Execution Plan of a
SQL Statement 9-75
9.5.3.3 Specifying Quarantine Thresholds in a Quarantine Configuration 9-76
9.5.3.4 Enabling and Disabling a Quarantine Configuration 9-78
9.5.3.5 Viewing the Details of a Quarantine Configuration 9-78
9.5.3.6 Deleting a Quarantine Configuration 9-79
9.5.3.7 Viewing the Details of Quarantined Execution Plans of SQL
Statements 9-80
xvii
9.5.3.8 Transferring Quarantine Configurations from One Database to
Another Database 9-80
9.5.3.9 Example: Quarantine for an Execution Plan of a SQL Statement
Consuming Excessive System Resources 9-82
Part II Oracle Database Structure and Storage
10
Managing Control Files
10.1 What Is a Control File? 10-1
10.2 Guidelines for Control Files 10-2
10.2.1 Provide File Names for the Control Files 10-2
10.2.2 Multiplex Control Files on Different Disks 10-3
10.2.3 Back Up Control Files 10-3
10.2.4 Manage the Size of Control Files 10-4
10.3 Creating Control Files 10-4
10.3.1 Creating Initial Control Files 10-4
10.3.2 Creating Additional Copies, Renaming, and Relocating Control Files 10-5
10.3.3 Creating New Control Files 10-5
10.3.3.1 When to Create New Control Files 10-6
10.3.3.2 The CREATE CONTROLFILE Statement 10-6
10.3.3.3 Creating New Control Files 10-7
10.4 Troubleshooting After Creating Control Files 10-8
10.4.1 Checking for Missing or Extra Files 10-9
10.4.2 Handling Errors During CREATE CONTROLFILE 10-9
10.5 Backing Up Control Files 10-9
10.6 Recovering a Control File Using a Current Copy 10-10
10.6.1 Recovering from Control File Corruption Using a Control File Copy 10-10
10.6.2 Recovering from Permanent Media Failure Using a Control File Copy 10-10
10.7 Dropping Control Files 10-11
10.8 Control Files Data Dictionary Views 10-11
11
Managing the Redo Log
11.1 What Is the Redo Log? 11-2
11.1.1 Redo Threads 11-2
11.1.2 Redo Log Contents 11-2
11.1.3 How Oracle Database Writes to the Redo Log 11-3
11.1.3.1 Active (Current) and Inactive Redo Log Files 11-4
11.1.3.2 Log Switches and Log Sequence Numbers 11-5
11.2 Planning the Redo Log 11-5
xviii
11.2.1 Multiplexing Redo Log Files 11-6
11.2.1.1 Responding to Redo Log Failure 11-7
11.2.1.2 Legal and Illegal Configurations 11-8
11.2.2 Placing Redo Log Members on Different Disks 11-9
11.2.3 Planning the Size of Redo Log Files 11-10
11.2.4 Planning the Block Size of Redo Log Files 11-10
11.2.5 Choosing the Number of Redo Log Files 11-11
11.2.6 Controlling Archive Lag 11-12
11.2.6.1 Setting the ARCHIVE_LAG_TARGET Initialization Parameter 11-12
11.2.6.2 Factors Affecting the Setting of ARCHIVE_LAG_TARGET 11-13
11.3 Creating Redo Log Groups and Members 11-14
11.3.1 Creating Redo Log Groups 11-14
11.3.2 Creating Redo Log Members 11-15
11.4 Relocating and Renaming Redo Log Members 11-16
11.5 Dropping Redo Log Groups and Members 11-17
11.5.1 Dropping Log Groups 11-17
11.5.2 Dropping Redo Log Members 11-18
11.6 Forcing Log Switches 11-19
11.7 Verifying Blocks in Redo Log Files 11-19
11.8 Clearing a Redo Log File 11-20
11.9 Precedence of FORCE LOGGING Settings 11-21
11.10 Redo Log Data Dictionary Views 11-22
12
Managing Archived Redo Log Files
12.1 What Is the Archived Redo Log? 12-2
12.2 Choosing Between NOARCHIVELOG and ARCHIVELOG Mode 12-2
12.2.1 Running a Database in NOARCHIVELOG Mode 12-3
12.2.2 Running a Database in ARCHIVELOG Mode 12-3
12.3 Controlling Archiving 12-4
12.3.1 Setting the Initial Database Archiving Mode 12-5
12.3.2 Changing the Database Archiving Mode 12-5
12.3.3 Performing Manual Archiving 12-6
12.3.4 Adjusting the Number of Archiver Processes 12-7
12.4 Specifying Archive Destinations 12-7
12.4.1 Setting Initialization Parameters for Archive Destinations 12-8
12.4.1.1 Method 1: Using the LOG_ARCHIVE_DEST_n Parameter 12-9
12.4.1.2 Method 2: Using LOG_ARCHIVE_DEST and
LOG_ARCHIVE_DUPLEX_DEST 12-10
12.4.2 Expanding Alternate Destinations with Log Archive Destination Groups 12-11
12.4.2.1 About Log Archive Destination Groups 12-12
12.4.2.2 Specifying Log Archive Destination Groups 12-13
xix
12.4.3 Understanding Archive Destination Status 12-14
12.4.4 Specifying Alternate Destinations 12-15
12.5 About Log Transmission Modes 12-15
12.5.1 Normal Transmission Mode 12-15
12.5.2 Standby Transmission Mode 12-15
12.6 Managing Archive Destination Failure 12-16
12.6.1 Specifying the Minimum Number of Successful Destinations 12-16
12.6.1.1 Specifying Mandatory and Optional Destinations 12-17
12.6.1.2 Specifying the Number of Successful Destinations: Scenarios 12-17
12.6.2 Rearchiving to a Failed Destination 12-19
12.7 Controlling Trace Output Generated by the Archivelog Process 12-19
12.8 Viewing Information About the Archived Redo Log 12-20
12.8.1 Archived Redo Log Files Views 12-20
12.8.2 Using the ARCHIVE LOG LIST Command 12-21
13
Managing Tablespaces
13.1 Guidelines for Managing Tablespaces 13-2
13.1.1 Use Multiple Tablespaces 13-2
13.1.2 Assign Tablespace Quotas to Users 13-3
13.2 Creating Tablespaces 13-3
13.2.1 About Creating Tablespaces 13-4
13.2.2 Locally Managed Tablespaces 13-5
13.2.2.1 About Locally Managed Tablespaces 13-5
13.2.2.2 Creating a Locally Managed Tablespace 13-6
13.2.2.3 Specifying Segment Space Management in Locally Managed
Tablespaces 13-8
13.2.3 Bigfile Tablespaces 13-9
13.2.3.1 About Bigfile Tablespaces 13-9
13.2.3.2 Creating a Bigfile Tablespace 13-10
13.2.3.3 Identifying a Bigfile Tablespace 13-11
13.2.4 Tablespaces with Default Compression Attributes 13-11
13.2.4.1 About Tablespaces with Default Compression Attributes 13-11
13.2.4.2 Creating Tablespaces with Default Compression Attributes 13-11
13.2.5 Encrypted Tablespaces 13-12
13.2.5.1 About Encrypted Tablespaces 13-12
13.2.5.2 Creating Encrypted Tablespaces 13-14
13.2.5.3 Viewing Information About Encrypted Tablespaces 13-14
13.2.6 Temporary Tablespaces 13-15
13.2.6.1 About Temporary Tablespaces 13-15
13.2.6.2 Creating a Locally Managed Temporary Tablespace 13-17
13.2.6.3 Creating a Bigfile Temporary Tablespace 13-18
xx
13.2.6.4 Viewing Space Usage for Temporary Tablespaces 13-18
13.2.7 Temporary Tablespace Groups 13-19
13.2.7.1 Multiple Temporary Tablespaces: Using Tablespace Groups 13-19
13.2.7.2 Creating a Tablespace Group 13-20
13.2.7.3 Changing Members of a Tablespace Group 13-20
13.2.7.4 Assigning a Tablespace Group as the Default Temporary
Tablespace 13-20
13.3 Consider Storing Tablespaces in the In-Memory Column Store 13-21
13.4 Specifying Nonstandard Block Sizes for Tablespaces 13-21
13.5 Controlling the Writing of Redo Records 13-22
13.6 Altering Tablespace Availability 13-23
13.6.1 Taking Tablespaces Offline 13-23
13.6.2 Bringing Tablespaces Online 13-25
13.7 Using Read-Only Tablespaces 13-25
13.7.1 About Read-Only Tablespaces 13-26
13.7.2 Making a Tablespace Read-Only 13-26
13.7.3 Making a Read-Only Tablespace Writable 13-28
13.7.4 Creating a Read-Only Tablespace on a WORM Device 13-29
13.7.5 Delaying the Opening of Data Files in Read-Only Tablespaces 13-29
13.8 Altering and Maintaining Tablespaces 13-30
13.8.1 Increasing the Size of a Tablespace 13-30
13.8.2 Altering a Locally Managed Tablespace 13-30
13.8.3 Altering a Bigfile Tablespace 13-31
13.8.4 Altering a Locally Managed Temporary Tablespace 13-32
13.8.5 Shrinking a Locally Managed Temporary Tablespace 13-33
13.9 Renaming Tablespaces 13-33
13.10 Dropping Tablespaces 13-34
13.11 Managing Lost Write Protection with Shadow Tablespaces 13-35
13.11.1 About Shadow Lost Write Protection 13-36
13.11.2 Creating Shadow Tablespaces for Shadow Lost Write Protection 13-38
13.11.3 Enabling Shadow Lost Write Protection for a Database 13-39
13.11.4 Enabling Shadow Lost Write Protection for Tablespaces and Data
Files 13-40
13.11.5 Disabling Shadow Lost Write Protection for a Database 13-41
13.11.6 Removing or Suspending Shadow Lost Write Protection 13-42
13.11.7 Dropping a Shadow Tablespace 13-44
13.12 Managing the SYSAUX Tablespace 13-44
13.12.1 Monitoring Occupants of the SYSAUX Tablespace 13-44
13.12.2 Moving Occupants Out Of or Into the SYSAUX Tablespace 13-45
13.12.3 Controlling the Size of the SYSAUX Tablespace 13-45
13.13 Correcting Problems with Locally Managed Tablespaces 13-46
13.13.1 Diagnosing and Repairing Locally Managed Tablespace Problems 13-46
xxi
13.13.2 Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free
(No Overlap) 13-48
13.13.3 Scenario 2: Dropping a Corrupted Segment 13-48
13.13.4 Scenario 3: Fixing Bitmap Where Overlap is Reported 13-49
13.13.5 Scenario 4: Correcting Media Corruption of Bitmap Blocks 13-49
13.13.6 Scenario 5: Migrating from a Dictionary-Managed to a Locally
Managed Tablespace 13-49
13.14 Migrating the SYSTEM Tablespace to a Locally Managed Tablespace 13-50
13.15 Viewing Information About Tablespaces 13-50
13.15.1 Tablespace Data Dictionary Views 13-51
13.15.2 Example 1: Listing Tablespaces and Default Storage Parameters 13-52
13.15.3 Example 2: Listing the Data Files and Associated Tablespaces of a
Database 13-52
13.15.4 Example 3: Displaying Statistics for Free Space (Extents) of Each
Tablespace 13-52
14
Managing Data Files and Temp Files
14.1 Guidelines for Managing Data Files 14-2
14.1.1 About Data Files 14-2
14.1.2 Determine the Number of Data Files 14-3
14.1.2.1 About Determining the Number of Data Files 14-3
14.1.2.2 Determine a Value for the DB_FILES Initialization Parameter 14-4
14.1.2.3 Consider Possible Limitations When Adding Data Files to a
Tablespace 14-4
14.1.2.4 Consider the Performance Impact of the Number of Data Files 14-5
14.1.3 Determine the Size of Data Files 14-5
14.1.4 Place Data Files Appropriately 14-5
14.1.5 Store Data Files Separate from Redo Log Files 14-6
14.2 Creating Data Files and Adding Data Files to a Tablespace 14-6
14.3 Changing Data File Size 14-7
14.3.1 Enabling and Disabling Automatic Extension for a Data File 14-7
14.3.2 Manually Resizing a Data File 14-8
14.4 Altering Data File Availability 14-8
14.4.1 About Altering Data File Availability 14-9
14.4.2 Bringing Data Files Online or Taking Offline in ARCHIVELOG Mode 14-10
14.4.3 Taking Data Files Offline in NOARCHIVELOG Mode 14-10
14.4.4 Altering the Availability of All Data Files or Temp Files in a Tablespace 14-11
14.5 Renaming and Relocating Data Files 14-11
14.5.1 Renaming and Relocating Online Data Files 14-11
14.5.2 Renaming and Relocating Offline Data Files 14-14
14.5.2.1 Procedures for Renaming and Relocating Offline Data Files in a
Single Tablespace 14-14
xxii
14.5.2.2 Renaming and Relocating Offline Data Files in Multiple
Tablespaces 14-16
14.6 Dropping Data Files 14-17
14.7 Verifying Data Blocks in Data Files 14-18
14.8 Copying Files Using the Database Server 14-19
14.8.1 About Copying Files Using the Database Server 14-19
14.8.2 Copying a File on a Local File System 14-20
14.8.3 Third-Party File Transfer 14-21
14.8.4 Advanced File Transfer Mechanisms 14-22
14.8.5 File Transfer and the DBMS_SCHEDULER Package 14-22
14.9 Mapping Files to Physical Devices 14-23
14.9.1 Overview of Oracle Database File Mapping Interface 14-23
14.9.2 How the Oracle Database File Mapping Interface Works 14-24
14.9.2.1 Components of File Mapping 14-24
14.9.2.2 Mapping Structures 14-26
14.9.2.3 Example of Mapping Structures 14-27
14.9.2.4 Configuration ID 14-28
14.9.3 Using the Oracle Database File Mapping Interface 14-28
14.9.3.1 Enabling File Mapping 14-29
14.9.3.2 Using the DBMS_STORAGE_MAP Package 14-30
14.9.3.3 Obtaining Information from the File Mapping Views 14-31
14.9.4 File Mapping Examples 14-32
14.9.4.1 Example 1: Map All Database Files that Span a Device 14-33
14.9.4.2 Example 2: Map a File Into Its Corresponding Devices 14-33
14.9.4.3 Example 3: Map a Database Object 14-34
14.10 Data Files Data Dictionary Views 14-34
15
Transporting Data
15.1 About Transporting Data 15-1
15.1.1 Purpose of Transporting Data 15-2
15.1.2 Transporting Data: Scenarios 15-2
15.1.2.1 Scenarios for Full Transportable Export/import 15-2
15.1.2.2 Scenarios for Transportable Tablespaces or Transportable
Tables 15-4
15.1.3 Transporting Data Across Platforms 15-8
15.1.4 General Limitations on Transporting Data 15-10
15.1.5 Compatibility Considerations for Transporting Data 15-12
15.2 Transporting Databases 15-14
15.2.1 Introduction to Full Transportable Export/Import 15-14
15.2.2 Limitations on Full Transportable Export/import 15-14
15.2.3 Transporting a Database Using an Export Dump File 15-15
xxiii
15.2.4 Transporting a Database Over the Network 15-22
15.3 Transporting Tablespaces Between Databases 15-27
15.3.1 Introduction to Transportable Tablespaces 15-28
15.3.2 Limitations on Transportable Tablespaces 15-29
15.3.3 Transporting Tablespaces Between Databases 15-29
15.3.3.1 Task 1: Pick a Self-Contained Set of Tablespaces 15-31
15.3.3.2 Task 2: Generate a Transportable Tablespace Set 15-33
15.3.3.3 Task 3: Transport the Export Dump File 15-35
15.3.3.4 Task 4: Transport the Tablespace Set 15-35
15.3.3.5 Task 5: (Optional) Restore Tablespaces to Read/Write Mode 15-36
15.3.3.6 Task 6: Import the Tablespace Set 15-36
15.4 Transporting Tables, Partitions, or Subpartitions Between Databases 15-38
15.4.1 Introduction to Transportable Tables 15-38
15.4.2 Limitations on Transportable Tables 15-39
15.4.3 Transporting Tables, Partitions, or Subpartitions Using an Export
Dump File 15-39
15.4.4 Transporting Tables, Partitions, or Subpartitions Over the Network 15-45
15.5 Converting Data Between Platforms 15-50
15.5.1 Converting Data Between Platforms Using the
DBMS_FILE_TRANSFER Package 15-51
15.5.2 Converting Data Between Platforms Using RMAN 15-53
15.5.2.1 Converting Tablespaces on the Source System After Export 15-54
15.5.2.2 Converting Data Files on the Target System Before Import 15-55
15.6 Guidelines for Transferring Data Files 15-56
16
Managing Undo
16.1 What Is Undo? 16-2
16.2 Introduction to Automatic Undo Management 16-2
16.2.1 Overview of Automatic Undo Management 16-3
16.2.2 The Undo Retention Period 16-4
16.2.2.1 About the Undo Retention Period 16-4
16.2.2.2 Automatic Tuning of Undo Retention 16-5
16.2.2.3 Retention Guarantee 16-6
16.2.2.4 Undo Retention Tuning and Alert Thresholds 16-6
16.2.2.5 Tracking the Tuned Undo Retention Period 16-7
16.3 Setting the Minimum Undo Retention Period 16-7
16.4 Sizing a Fixed-Size Undo Tablespace 16-8
16.4.1 Activating the Undo Advisor PL/SQL Interface 16-9
16.5 Managing Undo Tablespaces 16-10
16.5.1 Creating an Undo Tablespace 16-10
16.5.1.1 About Creating an Undo Tablespace 16-11
xxiv
16.5.1.2 Using CREATE DATABASE to Create an Undo Tablespace 16-11
16.5.1.3 Using the CREATE UNDO TABLESPACE Statement 16-12
16.5.2 Altering an Undo Tablespace 16-12
16.5.3 Dropping an Undo Tablespace 16-13
16.5.4 Switching Undo Tablespaces 16-13
16.5.5 Establishing User Quotas for Undo Space 16-14
16.5.6 Managing Space Threshold Alerts for the Undo Tablespace 16-15
16.6 Migrating to Automatic Undo Management 16-15
16.7 Managing Temporary Undo 16-15
16.7.1 About Managing Temporary Undo 16-15
16.7.2 Enabling and Disabling Temporary Undo 16-17
16.8 Undo Space Data Dictionary Views 16-18
17
Using Oracle Managed Files
17.1 About Oracle Managed Files 17-1
17.1.1 What Is Oracle Managed Files? 17-2
17.1.2 Who Can Use Oracle Managed Files? 17-2
17.1.3 What Is a Logical Volume Manager? 17-3
17.1.4 What Is a File System? 17-3
17.1.5 Benefits of Using Oracle Managed Files 17-3
17.1.6 Oracle Managed Files and Existing Functionality 17-4
17.2 Enabling the Creation and Use of Oracle Managed Files 17-4
17.2.1 Initialization Parameters That Enable Oracle Managed Files 17-5
17.2.2 Setting the DB_CREATE_FILE_DEST Initialization Parameter 17-6
17.2.3 Setting the DB_RECOVERY_FILE_DEST Parameter 17-6
17.2.4 Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization
Parameters 17-6
17.3 Creating Oracle Managed Files 17-7
17.3.1 When Oracle Database Creates Oracle Managed Files 17-8
17.3.2 How Oracle Managed Files Are Named 17-8
17.3.3 Creating Oracle Managed Files at Database Creation 17-9
17.3.3.1 Specifying Control Files at Database Creation 17-10
17.3.3.2 Specifying Redo Log Files at Database Creation 17-11
17.3.3.3 Specifying the SYSTEM and SYSAUX Tablespace Data Files at
Database Creation 17-12
17.3.3.4 Specifying the Undo Tablespace Data File at Database Creation 17-13
17.3.3.5 Specifying the Default Temporary Tablespace Temp File at
Database Creation 17-13
17.3.3.6 CREATE DATABASE Statement Using Oracle Managed Files:
Examples 17-13
17.3.4 Creating Data Files for Tablespaces Using Oracle Managed Files 17-16
xxv
17.3.4.1 About Creating Data Files for Tablespaces Using Oracle
Managed Files 17-16
17.3.4.2 CREATE TABLESPACE: Examples 17-17
17.3.4.3 CREATE UNDO TABLESPACE: Example 17-18
17.3.4.4 ALTER TABLESPACE: Example 17-18
17.3.5 Creating Temp Files for Temporary Tablespaces Using Oracle
Managed Files 17-19
17.3.5.1 About Creating Temp Files for Temporary Tablespaces Using
Oracle Managed Files 17-19
17.3.5.2 CREATE TEMPORARY TABLESPACE: Example 17-20
17.3.5.3 ALTER TABLESPACE... ADD TEMPFILE: Example 17-20
17.3.6 Creating Control Files Using Oracle Managed Files 17-20
17.3.6.1 About Creating Control Files Using Oracle Managed Files 17-21
17.3.6.2 CREATE CONTROLFILE Using NORESETLOGS Keyword:
Example 17-22
17.3.6.3 CREATE CONTROLFILE Using RESETLOGS Keyword:
Example 17-22
17.3.7 Creating Redo Log Files Using Oracle Managed Files 17-23
17.3.7.1 Using the ALTER DATABASE ADD LOGFILE Statement 17-23
17.3.7.2 Using the ALTER DATABASE OPEN RESETLOGS Statement 17-24
17.3.8 Creating Archived Logs Using Oracle Managed Files 17-24
17.4 Operation of Oracle Managed Files 17-24
17.4.1 Dropping Data Files and Temp Files 17-25
17.4.2 Dropping Redo Log Files 17-25
17.4.3 Renaming Files 17-26
17.4.4 Managing Standby Databases 17-26
17.5 Scenarios for Using Oracle Managed Files 17-26
17.5.1 Scenario 1: Create and Manage a Database with Multiplexed Redo
Logs 17-26
17.5.2 Scenario 2: Create and Manage a Database with Database and Fast
Recovery Areas 17-30
17.5.3 Scenario 3: Adding Oracle Managed Files to an Existing Database 17-31
Part III Schema Objects
18
Managing Schema Objects
18.1 Creating Multiple Tables and Views in a Single Operation 18-2
18.2 Analyzing Tables, Indexes, and Clusters 18-2
18.2.1 About Analyzing Tables, Indexes, and Clusters 18-3
18.2.2 Using DBMS_STATS to Collect Table and Index Statistics 18-4
18.2.3 Validating Tables, Indexes, Clusters, and Materialized Views 18-4
xxvi
18.2.4 Cross Validation of a Table and an Index with a Query 18-5
18.2.5 Listing Chained Rows of Tables and Clusters 18-6
18.2.5.1 Creating a CHAINED_ROWS Table 18-6
18.2.5.2 Eliminating Migrated or Chained Rows in a Table 18-7
18.3 Truncating Tables and Clusters 18-8
18.3.1 Using DELETE to Truncate a Table 18-8
18.3.2 Using DROP and CREATE to Truncate a Table 18-9
18.3.3 Using TRUNCATE 18-9
18.4 Enabling and Disabling Triggers 18-10
18.4.1 About Enabling and Disabling Triggers 18-11
18.4.2 Enabling Triggers 18-12
18.4.3 Disabling Triggers 18-12
18.5 Managing Integrity Constraints 18-12
18.5.1 Integrity Constraint States 18-13
18.5.1.1 About Integrity Constraint States 18-14
18.5.1.2 About Disabling Constraints 18-14
18.5.1.3 About Enabling Constraints 18-15
18.5.1.4 About the Enable Novalidate Constraint State 18-15
18.5.1.5 Efficient Use of Integrity Constraints: A Procedure 18-15
18.5.2 Setting Integrity Constraints Upon Definition 18-16
18.5.2.1 Disabling Constraints Upon Definition 18-16
18.5.2.2 Enabling Constraints Upon Definition 18-16
18.5.3 Modifying, Renaming, or Dropping Existing Integrity Constraints 18-17
18.5.3.1 Disabling and Enabling Constraints 18-17
18.5.3.2 Renaming Constraints 18-18
18.5.3.3 Dropping Constraints 18-18
18.5.4 Deferring Constraint Checks 18-19
18.5.4.1 Set All Constraints Deferred 18-19
18.5.4.2 Check the Commit (Optional) 18-20
18.5.5 Reporting Constraint Exceptions 18-20
18.5.6 Viewing Constraint Information 18-21
18.6 Renaming Schema Objects 18-22
18.7 Managing Object Dependencies 18-23
18.7.1 About Object Dependencies and Object Invalidation 18-23
18.7.2 Manually Recompiling Invalid Objects with DDL 18-25
18.7.3 Manually Recompiling Invalid Objects with PL/SQL Package
Procedures 18-25
18.8 Managing Object Name Resolution 18-26
18.9 Switching to a Different Schema 18-28
18.10 Managing Editions 18-28
18.10.1 About Editions and Edition-Based Redefinition 18-29
xxvii
18.10.2 DBA Tasks for Edition-Based Redefinition 18-29
18.10.3 Setting the Database Default Edition 18-30
18.10.4 Querying the Database Default Edition 18-30
18.10.5 Setting the Edition Attribute of a Database Service 18-30
18.10.5.1 About Setting the Edition Attribute of a Database Service 18-31
18.10.5.2 Setting the Edition Attribute During Database Service Creation 18-31
18.10.5.3 Setting the Edition Attribute of an Existing Database Service 18-32
18.10.6 Using an Edition 18-32
18.10.7 Editions Data Dictionary Views 18-33
18.11 Displaying Information About Schema Objects 18-33
18.11.1 Using a PL/SQL Package to Display Information About Schema
Objects 18-33
18.11.2 Schema Objects Data Dictionary Views 18-34
18.11.2.1 Example 1: Displaying Schema Objects By Type 18-35
18.11.2.2 Example 2: Displaying Dependencies of Views and Synonyms 18-35
19
Managing Space for Schema Objects
19.1 Managing Tablespace Alerts 19-1
19.1.1 About Managing Tablespace Alerts 19-2
19.1.2 Setting Alert Thresholds 19-3
19.1.3 Viewing Alerts 19-5
19.1.4 Limitations 19-5
19.2 Managing Resumable Space Allocation 19-6
19.2.1 Resumable Space Allocation Overview 19-7
19.2.1.1 How Resumable Space Allocation Works 19-7
19.2.1.2 What Operations are Resumable? 19-8
19.2.1.3 What Errors are Correctable? 19-9
19.2.1.4 Resumable Space Allocation and Distributed Operations 19-9
19.2.1.5 Parallel Execution and Resumable Space Allocation 19-9
19.2.2 Enabling and Disabling Resumable Space Allocation 19-10
19.2.2.1 About Enabling and Disabling Resumable Space Allocation 19-10
19.2.2.2 Setting the RESUMABLE_TIMEOUT Initialization Parameter 19-11
19.2.2.3 Using ALTER SESSION to Enable and Disable Resumable
Space Allocation 19-11
19.2.3 Using a LOGON Trigger to Set Default Resumable Mode 19-13
19.2.4 Detecting Suspended Statements 19-13
19.2.4.1 Notifying Users: The AFTER SUSPEND System Event and
Trigger 19-13
19.2.4.2 Using Views to Obtain Information About Suspended Statements
19-14
19.2.4.3 Using the DBMS_RESUMABLE Package 19-14
xxviii
19.2.5 Operation-Suspended Alert 19-15
19.2.6 Resumable Space Allocation Example: Registering an AFTER
SUSPEND Trigger 19-15
19.3 Reclaiming Unused Space 19-17
19.3.1 About Reclaimable Unused Space 19-17
19.3.2 The Segment Advisor 19-18
19.3.2.1 About the Segment Advisor 19-18
19.3.2.2 Using the Segment Advisor 19-19
19.3.2.3 Automatic Segment Advisor 19-19
19.3.2.4 Running the Segment Advisor Manually 19-20
19.3.2.5 Viewing Segment Advisor Results 19-25
19.3.2.6 Configuring the Automatic Segment Advisor 19-31
19.3.2.7 Viewing Automatic Segment Advisor Information 19-32
19.3.3 Shrinking Database Segments Online 19-33
19.3.4 Deallocating Unused Space 19-35
19.4 Dropping Unused Object Storage 19-36
19.5 Understanding Space Usage of Data Types 19-37
19.6 Displaying Information About Space Usage for Schema Objects 19-37
19.6.1 Using PL/SQL Packages to Display Information About Schema Object
Space Usage 19-37
19.6.2 Schema Objects Space Usage Data Dictionary Views 19-38
19.6.2.1 Example 1: Displaying Segment Information 19-39
19.6.2.2 Example 2: Displaying Extent Information 19-40
19.6.2.3 Example 3: Displaying the Free Space (Extents) in a Tablespace 19-40
19.7 Capacity Planning for Database Objects 19-41
19.7.1 Estimating the Space Use of a Table 19-41
19.7.2 Estimating the Space Use of an Index 19-42
19.7.3 Obtaining Object Growth Trends 19-42
20
Managing Tables
20.1 About Tables 20-2
20.2 Guidelines for Managing Tables 20-3
20.2.1 Design Tables Before Creating Them 20-5
20.2.2 Specify the Type of Table to Create 20-5
20.2.3 Specify the Location of Each Table 20-6
20.2.4 Consider Parallelizing Table Creation 20-7
20.2.5 Consider Using NOLOGGING When Creating Tables 20-7
20.2.6 Consider Using Table Compression 20-7
20.2.6.1 About Table Compression 20-8
20.2.6.2 Examples Related to Table Compression 20-12
20.2.6.3 Compression and Partitioned Tables 20-13
xxix
20.2.6.4 Determining If a Table Is Compressed 20-14
20.2.6.5 Determining Which Rows Are Compressed 20-14
20.2.6.6 Changing the Compression Level 20-15
20.2.6.7 Adding and Dropping Columns in Compressed Tables 20-15
20.2.6.8 Exporting and Importing Hybrid Columnar Compression Tables 20-16
20.2.6.9 Restoring a Hybrid Columnar Compression Table 20-16
20.2.6.10 Notes and Restrictions for Compressed Tables 20-17
20.2.6.11 Packing Compressed Tables 20-18
20.2.7 Managing Table Compression Using Enterprise Manager Cloud
Control 20-19
20.2.7.1 Table Compression and Enterprise Manager Cloud Control 20-19
20.2.7.2 Viewing the Compression Summary at the Database Level 20-20
20.2.7.3 Viewing the Compression Summary at the Tablespace Level 20-20
20.2.7.4 Estimating the Compression Ratio 20-21
20.2.7.5 Compressing an Object 20-21
20.2.7.6 Viewing Compression Advice 20-22
20.2.7.7 Initiating Automatic Data Optimization on an Object 20-22
20.2.8 Consider Using Segment-Level and Row-Level Compression Tiering 20-22
20.2.9 Consider Using Attribute-Clustered Tables 20-24
20.2.10 Consider Using Zone Maps 20-25
20.2.11 Consider Storing Tables in the In-Memory Column Store 20-26
20.2.12 Consider Using Invisible Columns 20-27
20.2.12.1 Understand Invisible Columns 20-27
20.2.12.2 Invisible Columns and Column Ordering 20-28
20.2.13 Consider Encrypting Columns That Contain Sensitive Data 20-29
20.2.14 Understand Deferred Segment Creation 20-31
20.2.15 Materializing Segments 20-33
20.2.16 Estimate Table Size and Plan Accordingly 20-34
20.2.17 Restrictions to Consider When Creating Tables 20-34
20.3 Creating Tables 20-35
20.3.1 Example: Creating a Table 20-35
20.3.2 Creating a Temporary Table 20-37
20.3.2.1 Overview of Temporary Tables 20-37
20.3.2.2 Considerations When Creating Temporary Tables 20-38
20.3.2.3 Creating Global Temporary Tables 20-38
20.3.2.4 Creating Private Temporary Tables 20-40
20.3.3 Parallelizing Table Creation 20-42
20.4 Loading Tables 20-43
20.4.1 Methods for Loading Tables 20-43
20.4.2 Improving INSERT Performance with Direct-Path INSERT 20-45
20.4.2.1 About Direct-Path INSERT 20-45
xxx
20.4.2.2 How Direct-Path INSERT Works 20-46
20.4.2.3 Loading Data with Direct-Path INSERT 20-47
20.4.2.4 Logging Modes for Direct-Path INSERT 20-48
20.4.2.5 Additional Considerations for Direct-Path INSERT 20-50
20.4.3 Using Conventional Inserts to Load Tables 20-51
20.4.4 Avoiding Bulk INSERT Failures with DML Error Logging 20-52
20.4.4.1 Inserting Data with DML Error Logging 20-52
20.4.4.2 Error Logging Table Format 20-54
20.4.4.3 Creating an Error Logging Table 20-55
20.4.4.4 Error Logging Restrictions and Caveats 20-55
20.5 Optimizing the Performance of Bulk Updates 20-56
20.6 Automatically Collecting Statistics on Tables 20-58
20.7 Altering Tables 20-59
20.7.1 Reasons for Using the ALTER TABLE Statement 20-60
20.7.2 Altering Physical Attributes of a Table 20-61
20.7.3 Moving a Table to a New Segment or Tablespace 20-61
20.7.3.1 About Moving a Table to a New Segment or Tablespace 20-61
20.7.3.2 Moving a Table 20-62
20.7.3.3 Moving a Table Partition or Subpartition Online 20-63
20.7.4 Manually Allocating Storage for a Table 20-64
20.7.5 Modifying an Existing Column Definition 20-64
20.7.6 Adding Table Columns 20-65
20.7.7 Renaming Table Columns 20-66
20.7.8 Dropping Table Columns 20-67
20.7.8.1 Removing Columns from Tables 20-68
20.7.8.2 Marking Columns Unused 20-68
20.7.8.3 Removing Unused Columns 20-69
20.7.8.4 Dropping Columns in Compressed Tables 20-69
20.7.9 Placing a Table in Read-Only Mode 20-69
20.8 Redefining Tables Online 20-71
20.8.1 About Redefining Tables Online 20-72
20.8.2 Features of Online Table Redefinition 20-73
20.8.3 Privileges Required for the DBMS_REDEFINITION Package 20-74
20.8.4 Restrictions for Online Redefinition of Tables 20-75
20.8.5 Performing Online Redefinition with the REDEF_TABLE Procedure 20-77
20.8.6 Redefining Tables Online with Multiple Procedures in
DBMS_REDEFINITION 20-78
20.8.6.1 Performing Online Redefinition with Multiple Procedures in
DBMS_REDEFINITION 20-78
20.8.6.2 Constructing a Column Mapping String 20-81
20.8.6.3 Handling Virtual Private Database (VPD) Policies During Online
Redefinition 20-82
xxxi
20.8.6.4 Creating Dependent Objects Automatically 20-83
20.8.6.5 Creating Dependent Objects Manually 20-83
20.8.7 Results of the Redefinition Process 20-85
20.8.8 Performing Intermediate Synchronization 20-85
20.8.9 Refreshing Dependent Materialized Views During Online Table
Redefinition 20-86
20.8.10 Monitoring Online Table Redefinition Progress 20-90
20.8.11 Restarting Online Table Redefinition After a Failure 20-93
20.8.12 Rolling Back Online Table Redefinition 20-96
20.8.12.1 About Online Table Redefinition Rollback 20-97
20.8.12.2 Performing Online Table Redefinition Rollback 20-97
20.8.13 Aborting Online Table Redefinition and Cleaning Up After Errors 20-101
20.8.14 Online Redefinition of One or More Partitions 20-101
20.8.14.1 Rules for Online Redefinition of a Single Partition 20-102
20.8.15 Online Table Redefinition Examples 20-103
20.9 Researching and Reversing Erroneous Table Changes 20-124
20.10 Recovering Tables Using Oracle Flashback Table 20-125
20.11 Dropping Tables 20-126
20.12 Using Flashback Drop and Managing the Recycle Bin 20-127
20.12.1 What Is the Recycle Bin? 20-128
20.12.2 Enabling and Disabling the Recycle Bin 20-129
20.12.3 Viewing and Querying Objects in the Recycle Bin 20-130
20.12.4 Purging Objects in the Recycle Bin 20-130
20.12.5 Restoring Tables from the Recycle Bin 20-131
20.13 Managing Index-Organized Tables 20-133
20.13.1 What Are Index-Organized Tables? 20-133
20.13.2 Creating Index-Organized Tables 20-134
20.13.2.1 About Creating Index-Organized Tables 20-135
20.13.2.2 Example: Creating an Index-Organized Table 20-135
20.13.2.3 Restrictions for Index-Organized Tables 20-136
20.13.2.4 Creating Index-Organized Tables That Contain Object Types 20-137
20.13.2.5 Choosing and Monitoring a Threshold Value 20-138
20.13.2.6 Using the INCLUDING Clause 20-138
20.13.2.7 Parallelizing Index-Organized Table Creation 20-139
20.13.2.8 Using Prefix Compression 20-139
20.13.3 Maintaining Index-Organized Tables 20-140
20.13.3.1 Altering Index-Organized Tables 20-140
20.13.3.2 Moving (Rebuilding) Index-Organized Tables 20-141
20.13.4 Creating Secondary Indexes on Index-Organized Tables 20-142
20.13.4.1 About Secondary Indexes on Index-Organized Tables 20-142
20.13.4.2 Creating a Secondary Index on an Index-Organized Table 20-143
xxxii
20.13.4.3 Maintaining Physical Guesses in Logical Rowids 20-143
20.13.4.4 Specifying Bitmap Indexes on Index-Organized Tables 20-143
20.13.5 Analyzing Index-Organized Tables 20-144
20.13.5.1 Collecting Optimizer Statistics for Index-Organized Tables 20-144
20.13.5.2 Validating the Structure of Index-Organized Tables 20-145
20.13.6 Using the ORDER BY Clause with Index-Organized Tables 20-145
20.13.7 Converting Index-Organized Tables to Regular Tables 20-145
20.14 Managing Partitioned Tables 20-146
20.15 Managing External Tables 20-146
20.15.1 About External Tables 20-147
20.15.2 Creating External Tables 20-148
20.15.3 Altering External Tables 20-151
20.15.4 Preprocessing External Tables 20-153
20.15.5 Overriding Parameters for External Tables in a Query 20-154
20.15.6 Using Inline External Tables 20-155
20.15.7 Partitioning External Tables 20-156
20.15.7.1 About Partitioning External Tables 20-156
20.15.7.2 Restrictions for Partitioned External Tables 20-158
20.15.7.3 Creating a Partitioned External Table 20-159
20.15.7.4 Altering a Partitioned External Table 20-163
20.15.8 Dropping External Tables 20-164
20.15.9 System and Object Privileges for External Tables 20-164
20.16 Managing Hybrid Partitioned Tables 20-164
20.17 Tables Data Dictionary Views 20-165
21
Managing Indexes
21.1 About Indexes 21-1
21.2 Guidelines for Managing Indexes 21-2
21.2.1 Create Indexes After Inserting Table Data 21-4
21.2.2 Index the Correct Tables and Columns 21-4
21.2.3 Order Index Columns for Performance 21-5
21.2.4 Limit the Number of Indexes for Each Table 21-5
21.2.5 Drop Indexes That Are No Longer Required 21-6
21.2.6 Indexes and Deferred Segment Creation 21-6
21.2.7 Estimate Index Size and Set Storage Parameters 21-6
21.2.8 Specify the Tablespace for Each Index 21-7
21.2.9 Consider Parallelizing Index Creation 21-7
21.2.10 Consider Creating Indexes with NOLOGGING 21-8
21.2.11 Understand When to Use Unusable or Invisible Indexes 21-8
21.2.12 Understand When to Create Multiple Indexes on the Same Set of
Columns 21-10
xxxiii
21.2.13 Consider Costs and Benefits of Coalescing or Rebuilding Indexes 21-11
21.2.14 Consider Cost Before Disabling or Dropping Constraints 21-12
21.2.15 Consider Using the In-Memory Column Store to Reduce the Number
of Indexes 21-12
21.3 Creating Indexes 21-13
21.3.1 Prerequisites for Creating Indexes 21-14
21.3.2 Creating an Index Explicitly 21-14
21.3.3 Creating a Unique Index Explicitly 21-15
21.3.4 Creating an Index Associated with a Constraint 21-15
21.3.4.1 About Creating an Index Associated with a Constraint 21-16
21.3.4.2 Specifying Storage Options for an Index Associated with a
Constraint 21-16
21.3.4.3 Specifying the Index Associated with a Constraint 21-16
21.3.5 Creating a Large Index 21-17
21.3.6 Creating an Index Online 21-18
21.3.7 Creating a Function-Based Index 21-18
21.3.8 Creating a Compressed Index 21-19
21.3.8.1 Creating an Index Using Prefix Compression 21-20
21.3.8.2 Creating an Index Using Advanced Index Compression 21-21
21.3.9 Creating an Unusable Index 21-22
21.3.10 Creating an Invisible Index 21-24
21.3.11 Creating Multiple Indexes on the Same Set of Columns 21-24
21.4 Altering Indexes 21-25
21.4.1 About Altering Indexes 21-26
21.4.2 Altering Storage Characteristics of an Index 21-27
21.4.3 Rebuilding an Existing Index 21-27
21.4.4 Making an Index Unusable 21-28
21.4.5 Making an Index Invisible or Visible 21-30
21.4.6 Renaming an Index 21-31
21.4.7 Monitoring Index Usage 21-31
21.5 Monitoring Space Use of Indexes 21-31
21.6 Dropping Indexes 21-32
21.7 Managing Auto Indexes 21-33
21.7.1 About Automatic Indexing 21-33
21.7.2 How Automatic Indexing Works 21-34
21.7.3 Configuring Automatic Indexing in an Oracle Database 21-35
21.7.4 Generating Automatic Indexing Reports 21-39
21.7.5 Views Containing the Automatic Indexing Information 21-40
21.8 Indexes Data Dictionary Views 21-41
xxxiv
22
Managing Clusters
22.1 About Clusters 22-1
22.2 Guidelines for Managing Clusters 22-3
22.2.1 Choose Appropriate Tables for the Cluster 22-3
22.2.2 Choose Appropriate Columns for the Cluster Key 22-4
22.2.3 Specify the Space Required by an Average Cluster Key and Its
Associated Rows 22-4
22.2.4 Specify the Location of Each Cluster and Cluster Index Rows 22-5
22.2.5 Estimate Cluster Size and Set Storage Parameters 22-5
22.3 Creating Clusters and Objects That Use Them 22-5
22.3.1 Creating Clusters 22-6
22.3.2 Creating Clustered Tables 22-6
22.3.3 Creating Cluster Indexes 22-7
22.4 Altering Clusters and Objects That Use Them 22-8
22.4.1 Altering Clusters 22-8
22.4.2 Altering Clustered Tables 22-9
22.4.3 Altering Cluster Indexes 22-9
22.5 Dropping Clusters and Objects That Use Them 22-9
22.5.1 Dropping Clusters 22-10
22.5.2 Dropping Clustered Tables 22-11
22.5.3 Dropping Cluster Indexes 22-11
22.6 Clusters Data Dictionary Views 22-11
23
Managing Hash Clusters
23.1 About Hash Clusters 23-1
23.2 When to Use Hash Clusters 23-2
23.2.1 Situations Where Hashing Is Useful 23-2
23.2.2 Situations Where Hashing Is Not Advantageous 23-3
23.3 Creating Different Types of Hash Clusters 23-3
23.3.1 Creating Hash Clusters 23-4
23.3.2 Creating a Sorted Hash Cluster 23-4
23.3.3 Creating Single-Table Hash Clusters 23-7
23.3.4 Controlling Space Use Within a Hash Cluster 23-7
23.3.4.1 Choosing the Key 23-8
23.3.4.2 Setting HASH IS 23-8
23.3.4.3 Setting SIZE 23-8
23.3.4.4 Setting HASHKEYS 23-9
23.3.4.5 Controlling Space in Hash Clusters 23-9
23.3.5 Estimating Size Required by Hash Clusters 23-10
23.4 Altering Hash Clusters 23-11
xxxv
23.5 Dropping Hash Clusters 23-11
23.6 Hash Clusters Data Dictionary Views 23-12
24
Managing Views, Sequences, and Synonyms
24.1 Managing Views 24-1
24.1.1 About Views 24-2
24.1.2 Creating Views and Join Views 24-2
24.1.2.1 Creating Views 24-3
24.1.2.2 Creating Join Views 24-4
24.1.2.3 Expansion of Defining Queries at View Creation Time 24-4
24.1.2.4 Creating Views with Errors 24-5
24.1.3 Replacing Views 24-5
24.1.4 Using Views in Queries 24-6
24.1.5 DML Statements and Join Views 24-7
24.1.5.1 Updating a Join View 24-8
24.1.5.2 Key-Preserved Tables 24-9
24.1.5.3 Rules for DML Statements and Join Views 24-10
24.1.5.4 Updating Views That Involve Outer Joins 24-13
24.1.5.5 Using the UPDATABLE_ COLUMNS Views 24-15
24.1.6 Altering Views 24-15
24.1.7 Dropping Views 24-16
24.2 Managing Sequences 24-16
24.2.1 About Sequences 24-16
24.2.2 Creating Sequences 24-17
24.2.3 Altering Sequences 24-18
24.2.4 Using Sequences 24-18
24.2.4.1 Referencing a Sequence 24-19
24.2.4.2 Caching Sequence Numbers 24-21
24.2.4.3 Making a Sequence Scalable 24-22
24.2.5 Dropping Sequences 24-25
24.3 Managing Synonyms 24-25
24.3.1 About Synonyms 24-26
24.3.2 Creating Synonyms 24-26
24.3.3 Using Synonyms in DML Statements 24-27
24.3.4 Dropping Synonyms 24-27
24.4 Views, Synonyms, and Sequences Data Dictionary Views 24-28
25
Repairing Corrupted Data
25.1 Options for Repairing Data Block Corruption 25-1
xxxvi
25.2 About the DBMS_REPAIR Package 25-2
25.2.1 DBMS_REPAIR Procedures 25-2
25.2.2 Limitations and Restrictions for DBMS_REPAIR Procedures 25-3
25.3 Using the DBMS_REPAIR Package 25-3
25.3.1 Task 1: Dete

本专栏为图文内容课程。
付费用户可享受文章永久阅读权限。
付费用户可获取折扣购买数据库、架构师大会权限。
本课程为虚拟产品，一经付费概不退款，敬请谅解。