Oracle 12.1 数据库管理指南

需积分: 9 62 浏览量更新于2024-07-20 收藏 21.92MB PDF 举报

"Oracle 12.1 DB admin 是一份针对Oracle数据库12c第一版（12.1）管理的入门级文档，旨在提供易于学习的资料。文档由多位作者共同编写，包括Randy Urbano作为主要作者，以及其他贡献者如Leo Cloutier、Steve Fogel、Janis Greenberg等。此文档旨在帮助数据库管理员理解并管理Oracle 12.1数据库系统，涵盖了版权信息以及对Mark Townsend的致敬。" 在Oracle 12.1数据库管理员指南中，读者可以期待学习到一系列关键的数据库管理概念和技术。以下是该文档可能涉及的一些核心知识点： 1. **安装与配置**：如何安装Oracle数据库12.1，包括选择适当的安装选项、创建数据库实例以及进行必要的系统设置。 2. **数据库架构**：介绍Oracle数据库的基本结构，如表空间、数据文件、控制文件、重做日志文件等，以及它们在数据库中的角色。 3. **性能监控与调优**：如何使用Oracle的性能分析工具，如SQL*Plus、V$视图和AWR报告，来识别性能瓶颈并进行优化。 4. **备份与恢复**：涵盖数据库备份策略，包括物理备份、逻辑备份以及使用RMAN（恢复管理器）进行备份和恢复的流程。 5. **安全性**：讨论如何创建和管理用户、权限和角色，以及实施数据库审计和加密技术来保护数据安全。 6. **故障排查**：提供解决数据库错误和问题的步骤，包括查看报警日志、使用trace文件和诊断工具。 7. **高可用性**：介绍Oracle数据库的高可用性解决方案，如RAC（实时应用集群）、数据守护和闪回技术。 8. **数据库升级与迁移**：如何将旧版本的Oracle数据库迁移到12.1版本，以及升级过程中的注意事项。 9. **存储与内存管理**：解释SGA（系统全局区）和PGA（程序全局区）的工作原理，以及如何调整内存参数以优化性能。 10. **数据库维护任务**：包括定期执行的DBA任务，如空间管理、性能监控、数据库备份和更新补丁。 11. **数据库实例与服务**：阐述如何创建、启动和关闭数据库实例，以及管理服务以支持多租户架构。 12. **SQL与PL/SQL**：基础的SQL查询语法以及PL/SQL编程，用于创建存储过程、函数和触发器。通过深入阅读和实践《Oracle Database Administrator's Guide 12c Release 1 (12.1)》，数据库管理员能够掌握Oracle 12.1的核心管理技能，从而有效地维护和优化数据库环境。这份文档是初学者和有经验的DBA巩固基础、学习新功能的宝贵资源。

xvi

Schema Objects Space Usage Data Dictionary Views.............................................................. 19-30

Capacity Planning for Database Objects ....................................................................................... 19-32

Estimating the Space Use of a Table .......................................................................................... 19-32

Estimating the Space Use of an Index ....................................................................................... 19-33

Obtaining Object Growth Trends .............................................................................................. 19-33

20 Managing Tables

About Tables .......................................................................................................................................... 20-1

Guidelines for Managing Tables ....................................................................................................... 20-2

Design Tables Before Creating Them........................................................................................... 20-3

Specify the Type of Table to Create.............................................................................................. 20-3

Specify the Location of Each Table............................................................................................... 20-4

Consider Parallelizing Table Creation ......................................................................................... 20-4

Consider Using NOLOGGING When Creating Tables ............................................................ 20-4

Consider Using Table Compression............................................................................................. 20-5

Consider Using Segment-Level and Row-Level Compression Tiering ................................ 20-13

Understand Invisible Columns ................................................................................................... 20-14

Consider Encrypting Columns That Contain Sensitive Data................................................. 20-16

Understand Deferred Segment Creation ................................................................................... 20-17

Materializing Segments................................................................................................................ 20-19

Estimate Table Size and Plan Accordingly................................................................................ 20-20

Restrictions to Consider When Creating Tables....................................................................... 20-20

Creating Tables.................................................................................................................................... 20-20

Example: Creating a Table........................................................................................................... 20-21

Creating a Temporary Table........................................................................................................ 20-22

Parallelizing Table Creation ........................................................................................................ 20-24

Loading Tables..................................................................................................................................... 20-24

Methods for Loading Tables........................................................................................................ 20-25

Improving INSERT Performance with Direct-Path INSERT.................................................. 20-26

Using Conventional Inserts to Load Tables .............................................................................. 20-30

Avoiding Bulk INSERT Failures with DML Error Logging ................................................... 20-31

Automatically Collecting Statistics on Tables............................................................................... 20-34

Altering Tables .................................................................................................................................... 20-35

Reasons for Using the ALTER TABLE Statement .................................................................... 20-36

Altering Physical Attributes of a Table...................................................................................... 20-36

Moving a Table to a New Segment or Tablespace ................................................................... 20-37

Manually Allocating Storage for a Table................................................................................... 20-39

Modifying an Existing Column Definition................................................................................ 20-39

Adding Table Columns................................................................................................................ 20-40

Renaming Table Columns............................................................................................................ 20-40

Dropping Table Columns ........................................................................................................... 20-41

Placing a Table in Read-Only Mode...........................................................................................

20-42

Redefining Tables Online ................................................................................................................. 20-43

Features of Online Table Redefinition ....................................................................................... 20-44

Performing Online Redefinition with the REDEF_TABLE Procedure.................................. 20-45

Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION... 20-46

Results of the Redefinition Process............................................................................................. 20-51

xvii

Performing Intermediate Synchronization................................................................................ 20-52

Aborting Online Table Redefinition and Cleaning Up After Errors ..................................... 20-52

Restrictions for Online Redefinition of Tables.......................................................................... 20-53

Online Redefinition of One or More Partitions ........................................................................ 20-54

Online Table Redefinition Examples.......................................................................................... 20-56

Privileges Required for the DBMS_REDEFINITION Package............................................... 20-77

Researching and Reversing Erroneous Table Changes ............................................................... 20-77

Recovering Tables Using Oracle Flashback Table ....................................................................... 20-78

Dropping Tables.................................................................................................................................. 20-79

Using Flashback Drop and Managing the Recycle Bin ............................................................... 20-79

What Is the Recycle Bin? .............................................................................................................. 20-80

Enabling and Disabling the Recycle Bin.................................................................................... 20-81

Viewing and Querying Objects in the Recycle Bin .................................................................. 20-82

Purging Objects in the Recycle Bin............................................................................................. 20-82

Restoring Tables from the Recycle Bin....................................................................................... 20-83

Managing Index-Organized Tables ................................................................................................ 20-84

What Are Index-Organized Tables?........................................................................................... 20-84

Creating Index-Organized Tables............................................................................................... 20-85

Maintaining Index-Organized Tables ........................................................................................ 20-89

Creating Secondary Indexes on Index-Organized Tables....................................................... 20-91

Analyzing Index-Organized Tables ........................................................................................... 20-92

Using the ORDER BY Clause with Index-Organized Tables.................................................. 20-93

Converting Index-Organized Tables to Regular Tables.......................................................... 20-93

Managing External Tables................................................................................................................. 20-93

About External Tables .................................................................................................................. 20-94

Creating External Tables.............................................................................................................. 20-95

Altering External Tables............................................................................................................... 20-97

Preprocessing External Tables..................................................................................................... 20-98

Dropping External Tables............................................................................................................ 20-99

System and Object Privileges for External Tables.................................................................... 20-99

Tables Data Dictionary Views ....................................................................................................... 20-100

21 Managing Indexes

About Indexes ........................................................................................................................................ 21-1

Guidelines for Managing Indexes ..................................................................................................... 21-2

Create Indexes After Inserting Table Data .................................................................................. 21-2

Index the Correct Tables and Columns ....................................................................................... 21-3

Order Index Columns for Performance....................................................................................... 21-4

Limit the Number of Indexes for Each Table.............................................................................. 21-4

Drop Indexes That Are No Longer Required ............................................................................ 21-4

Indexes and Deferred Segment Creation..................................................................................... 21-4

Estimate Index Size and Set Storage Parameters........................................................................ 21-4

Specify the Tablespace for Each Index......................................................................................... 21-5

Consider Parallelizing Index Creation......................................................................................... 21-5

Consider Creating Indexes with NOLOGGING ........................................................................ 21-5

Understand When to Use Unusable or Invisible Indexes ......................................................... 21-6

Understand When to Create Multiple Indexes on the Same Set of Columns ........................ 21-7

xviii

Consider Costs and Benefits of Coalescing or Rebuilding Indexes......................................... 21-8

Consider Cost Before Disabling or Dropping Constraints........................................................ 21-9

Creating Indexes.................................................................................................................................... 21-9

Creating an Index Explicitly........................................................................................................ 21-10

Creating a Unique Index Explicitly ............................................................................................ 21-10

Creating an Index Associated with a Constraint...................................................................... 21-10

Creating a Large Index................................................................................................................. 21-12

Creating an Index Online............................................................................................................. 21-12

Creating a Function-Based Index................................................................................................ 21-12

Creating a Key-Compressed Index............................................................................................. 21-13

Creating an Unusable Index........................................................................................................ 21-14

Creating an Invisible Index.......................................................................................................... 21-15

Creating Multiple Indexes on the Same Set of Columns......................................................... 21-16

Altering Indexes .................................................................................................................................. 21-17

Altering Storage Characteristics of an Index............................................................................. 21-17

Rebuilding an Existing Index ...................................................................................................... 21-18

Making an Index Unusable.......................................................................................................... 21-19

Making an Index Invisible or Visible ......................................................................................... 21-20

Renaming an Index....................................................................................................................... 21-21

Monitoring Index Usage .............................................................................................................. 21-21

Monitoring Space Use of Indexes .................................................................................................... 21-22

Dropping Indexes................................................................................................................................ 21-22

Indexes Data Dictionary Views........................................................................................................ 21-23

22 Managing Clusters

About Clusters ....................................................................................................................................... 22-1

Guidelines for Managing Clusters .................................................................................................... 22-2

Choose Appropriate Tables for the Cluster ................................................................................ 22-3

Choose Appropriate Columns for the Cluster Key.................................................................... 22-3

Specify the Space Required by an Average Cluster Key and Its Associated Rows .............. 22-3

Specify the Location of Each Cluster and Cluster Index Rows ................................................ 22-4

Estimate Cluster Size and Set Storage Parameters..................................................................... 22-4

Creating Clusters................................................................................................................................... 22-4

Creating Clustered Tables.............................................................................................................. 22-5

Creating Cluster Indexes................................................................................................................ 22-5

Altering Clusters ................................................................................................................................... 22-6

Altering Clustered Tables .............................................................................................................. 22-6

Altering Cluster Indexes ................................................................................................................ 22-7

Dropping Clusters................................................................................................................................. 22-7

Dropping Clustered Tables............................................................................................................ 22-7

Dropping Cluster Indexes.............................................................................................................. 22-8

Clusters Data Dictionary Views......................................................................................................... 22-8

23 Managing Hash Clusters

About Hash Clusters............................................................................................................................. 23-1

When to Use Hash Clusters................................................................................................................. 23-2

Situations Where Hashing Is Useful............................................................................................. 23-2

xix

Situations Where Hashing Is Not Advantageous ...................................................................... 23-2

Creating Hash Clusters ........................................................................................................................ 23-2

Creating a Sorted Hash Cluster..................................................................................................... 23-3

Creating Single-Table Hash Clusters ........................................................................................... 23-6

Controlling Space Use Within a Hash Cluster............................................................................ 23-6

Estimating Size Required by Hash Clusters................................................................................ 23-8

Altering Hash Clusters......................................................................................................................... 23-9

Dropping Hash Clusters ...................................................................................................................... 23-9

Hash Clusters Data Dictionary Views .............................................................................................. 23-9

24 Managing Views, Sequences, and Synonyms

Managing Views.................................................................................................................................... 24-1

About Views .................................................................................................................................... 24-1

Creating Views ................................................................................................................................ 24-2

Replacing Views.............................................................................................................................. 24-4

Using Views in Queries ................................................................................................................. 24-5

Updating a Join View ..................................................................................................................... 24-6

Altering Views............................................................................................................................... 24-12

Dropping Views ............................................................................................................................ 24-12

Managing Sequences.......................................................................................................................... 24-13

About Sequences ........................................................................................................................... 24-13

Creating Sequences....................................................................................................................... 24-13

Altering Sequences........................................................................................................................ 24-14

Using Sequences............................................................................................................................ 24-14

Dropping Sequences..................................................................................................................... 24-17

Managing Synonyms.......................................................................................................................... 24-17

About Synonyms........................................................................................................................... 24-17

Creating Synonyms....................................................................................................................... 24-18

Using Synonyms in DML Statements ....................................................................................... 24-18

Dropping Synonyms..................................................................................................................... 24-19

Views, Synonyms, and Sequences Data Dictionary Views........................................................ 24-19

25 Repairing Corrupted Data

Options for Repairing Data Block Corruption................................................................................ 25-1

About the DBMS_REPAIR Package .................................................................................................. 25-2

DBMS_REPAIR Procedures........................................................................................................... 25-2

Limitations and Restrictions.......................................................................................................... 25-2

Using the DBMS_REPAIR Package................................................................................................... 25-2

Task 1: Detect and Report Corruptions ....................................................................................... 25-3

Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR.......................................... 25-4

Task 3: Make Objects Usable ......................................................................................................... 25-5

Task 4: Repair Corruptions and Rebuild Lost Data................................................................... 25-5

DBMS_REPAIR Examples................................................................................................................... 25-5

Examples: Building a Repair Table or Orphan Key Table ........................................................ 25-6

Example: Detecting Corruption .................................................................................................... 25-7

Example: Fixing Corrupt Blocks ................................................................................................... 25-8

Example: Finding Index Entries Pointing to Corrupt Data Blocks.......................................... 25-9

Example: Skipping Corrupt Blocks .............................................................................................. 25-9

Part IV Database Resource Management and Task Scheduling

26 Managing Automated Database Maintenance Tasks

About Automated Maintenance Tasks ............................................................................................. 26-1

About Maintenance Windows ........................................................................................................... 26-2

Configuring Automated Maintenance Tasks .................................................................................. 26-3

Enabling and Disabling Maintenance Tasks for all Maintenance Windows.......................... 26-3

Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows ................ 26-4

Configuring Maintenance Windows................................................................................................. 26-4

Modifying a Maintenance Window.............................................................................................. 26-4

Creating a New Maintenance Window ....................................................................................... 26-5

Removing a Maintenance Window.............................................................................................. 26-5

Configuring Resource Allocations for Automated Maintenance Tasks .................................... 26-6

About Resource Allocations for Automated Maintenance Tasks............................................ 26-6

Changing Resource Allocations for Automated Maintenance Tasks...................................... 26-7

Automated Maintenance Tasks Reference....................................................................................... 26-7

Predefined Maintenance Windows .............................................................................................. 26-7

Automated Maintenance Tasks Database Dictionary Views.................................................... 26-7

27 Managing Resources with Oracle Database Resource Manager

About Oracle Database Resource Manager...................................................................................... 27-1

What Solutions Does the Resource Manager Provide for Workload Management?............ 27-2

Elements of the Resource Manager .............................................................................................. 27-3

About Resource Manager Administration Privileges................................................................ 27-7

Assigning Sessions to Resource Consumer Groups....................................................................... 27-8

Overview of Assigning Sessions to Resource Consumer Groups ........................................... 27-8

Assigning an Initial Resource Consumer Group........................................................................ 27-8

Specifying Session-to–Consumer Group Mapping Rules......................................................... 27-9

Switching Resource Consumer Groups..................................................................................... 27-12

Specifying Automatic Consumer Group Switching ................................................................ 27-14

Granting and Revoking the Switch Privilege............................................................................ 27-18

The Types of Resources Managed by the Resource Manager.................................................... 27-19

CPU ................................................................................................................................................. 27-19

Exadata I/O.................................................................................................................................... 27-22

Parallel Execution Servers............................................................................................................ 27-22

Runaway Queries.......................................................................................................................... 27-25

Active Session Pool with Queuing ............................................................................................. 27-25

Undo Pool....................................................................................................................................... 27-26

Idle Time Limit .............................................................................................................................. 27-26

Creating a Simple Resource Plan .................................................................................................... 27-26

Creating a Complex Resource Plan.................................................................................................. 27-27

About the Pending Area .............................................................................................................. 27-28

Creating a Pending Area.............................................................................................................. 27-29

剩余1359页未读，继续阅读

michael_yzq123

粉丝: 3
资源: 75

Oracle 12.1 数据库管理指南

Oracle ODBC 64位驱动 Ver12.1.0.2.0

SQL SERVER 安装ORACLE provider for OLE DB 的连接服务驱动

1instantclient-basic-linux.x64-12.1.0.1.0.zip

Oracle敏捷客户端

ORACLE 12.2 实战操作

Oracle 12.2 CHMs.rar

Oracle-client_32bits

Oracle 12c实战加密表空间

Oracle 11g rman 异机还原再升级到 12c PDB

PLSQL Developer 10 汉化注册绿色版20131030(免安装Oracle直连)

最新资源