Oracle Administrator Guide - Apuntes de Psicología

Oracle9

Database Administrator’s Guide

Release 2 (9.2)

March 2002

Part No. A96521-01

Vista previa parcial del texto

¡Descarga Oracle Administrator Guide y más Apuntes en PDF de Psicología solo en Docsity!

Oracle9i

Database Administrator’s Guide

Release 2 (9.2)

March 2002 Part No. A96521-

Oracle9 i Database Administrator’s Guide, Release 2 (9.2) Part No. A96521- Copyright © 2001, 2002 Oracle Corporation. All rights reserved. Primary Author: Ruth Baylis Contributing Authors: Kathy Rich Graphic Designer: Valarie Moore Contributors: Lance Ashdown, Allen Brumm, Michele Cyran, Mary Ann Davidson, Harvey Eneman, Amit Ganesh, Carolyn Gray, Wei Huang, Robert Jenkins, Mark Kennedy, Sushil Kumar, Bill Lee, Yunrui Li, Diana Lorentz, Sujatha Muthulingam, Gary Ngai, Waleed Ojeil, Lois Price, Ananth Raghavan, Ann Rhee, Rajiv Sinha, Jags Srinivasan, Anh-Tuan Tran, Deborah Steiner, Janet Stern, Michael Stewart, Alex Tsukerman, Kothanda Umamageswaran, Steven Wertheimer, Daniel Wong The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent and other intellectual and industrial property laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on behalf of the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs, including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle is a registered trademark, and Oracle9 i , Oracle8 i , Oracle8, Oracle7, Oracle Names, Oracle Store, Oracle Transparent Gateway, PL/SQL, SQLNet, and SQLPlus are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners.

iv

Part II Oracle Server Processes and Storage Structure

xiii

Part III Schema Objects

xix

Task 8: Back Up the Fully Functional Database 1-
Task 9: Tune Database Performance 1-
Identifying Your Oracle Database Software Release 1-
- Release Number Format 1-
- Checking Your Current Release Number 1-
Database Administrator Security and Privileges 1-
- The Database Administrator’s Operating System Account 1-
- Database Administrator Usernames 1-
Database Administrator Authentication 1-
- Administrative Privileges.......................................................................................................... 1-
- Selecting an Authentication Method 1-
- Using Operating System (OS) Authentication 1-
- Using Password File Authentication 1-
Creating and Maintaining a Password File 1-
- Using ORAPWD 1-
- Setting REMOTE_LOGIN_ PASSWORDFILE........................................................................ 1-
- Adding Users to a Password File 1-
- Maintaining a Password File..................................................................................................... 1-
Database Administrator Utilities 1-
- SQL*Loader 1-
- Export and Import 1-
Considerations Before Creating a Database 2- 2 Creating an Oracle Database
- Planning for Database Creation.................................................................................................. 2-
- Meeting Creation Prerequisites 2-
- Deciding How to Create an Oracle Database 2-
Using the Database Configuration Assistant 2-
- Advantages of Using DBCA 2-
- Creating a Database Using DBCA.............................................................................................. 2-
- Configuring Database Options 2-
- Deleting a Database Using DBCA 2-
- Managing DBCA Templates 2-
- Using DBCA Silent Mode 2-
Manually Creating an Oracle Database 2-
- Step 1: Decide on Your Instance Identifier (SID) 2- v
- Step 2: Establish the Database Administrator Authentication Method.............................. 2-
- Step 3: Create the Initialization Parameter File 2-
- Step 4: Connect to the Instance 2-
- Step 5: Start the Instance............................................................................................................ 2-
- Step 6: Issue the CREATE DATABASE Statement 2-
- Step 7: Create Additional Tablespaces 2-
- Step 8: Run Scripts to Build Data Dictionary Views.............................................................. 2-
- Step 9: Run Scripts to Install Additional Options (Optional)............................................... 2-
- Step 10: Create a Server Parameter File (Recommended) 2-
- Step 11: Back Up the Database.................................................................................................. 2-
Understanding the CREATE DATABASE Statement 2-
- Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM................ 2-
- Clauses that Simplify Database Creation and Management................................................ 2-
- Creating a Locally Managed SYSTEM Tablespace 2-
- Specifying the Database Time Zone and Time Zone File 2-
- Specifying FORCE LOGGING Mode 2-
Troubleshooting Database Creation 2-
Dropping a Database 2-
Considerations After Creating a Database 2-
- Some Security Considerations 2-
- Installing Oracle’s Sample Schemas......................................................................................... 2-
Initialization Parameters and Database Creation 2-
- Determining the Global Database Name 2-
- Specifying Control Files............................................................................................................. 2-
- Specifying Database Block Sizes............................................................................................... 2-
- Setting Initialization Parameters that Affect the Size of the SGA 2-
- Specifying the Maximum Number of Processes 2-
- Specifying the Method of Undo Space Management............................................................ 2-
- Setting License Parameters........................................................................................................ 2-
Managing Initialization Parameters Using a Server Parameter File 2-
- What is a Server Parameter File?.............................................................................................. 2-
- Migrating to a Server Parameter File....................................................................................... 2-
- Creating a Server Parameter File.............................................................................................. 2-
- The SPFILE Initialization Parameter........................................................................................ 2-
- Using ALTER SYSTEM to Change Initialization Parameter Values 2- vi
- Exporting the Server Parameter File 2-
- Backing Up the Server Parameter File 2-
- Errors and Recovery for the Server Parameter File 2-
- Viewing Parameter Settings 2-
What are Oracle-Managed Files? 3- 3 Using Oracle-Managed Files
- Who Can Use Oracle-Managed Files? 3-
- Benefits of Using Oracle-Managed Files 3-
- Oracle-Managed Files and Existing Functionality................................................................... 3-
Enabling the Creation and Use of Oracle-Managed Files 3-
- Setting the DB_CREATE_FILE_DEST Initialization Parameter............................................. 3-
- Setting the DB_CREATE_ONLINE_LOG_DEST_ n Initialization Parameter 3-
Creating Oracle-Managed Files 3-
- How Oracle-Managed Files are Named 3-
- Creating Oracle-Managed Files at Database Creation 3-
- Creating Datafiles for Tablespaces 3-
- Creating Tempfiles for Temporary Tablespaces 3-
- Creating Control Files 3-
- Creating Online Redo Log Files................................................................................................ 3-
Behavior of Oracle-Managed Files 3-
- Dropping Datafiles and Tempfiles........................................................................................... 3-
- Dropping Online Redo Log Files.............................................................................................. 3-
- Renaming Files 3-
- Managing Standby Databases................................................................................................... 3-
Scenarios for Using Oracle-Managed Files 3-
- Scenario 1: Create and Manage a Database with Multiplexed Online Redo Logs............ 3-
- Scenario 2: Add Oracle-Managed Files to an Existing Database 3-
Starting Up a Database 4- 4 Starting Up and Shutting Down
- Options for Starting Up a Database 4-
- Preparing to Start an Instance..................................................................................................... 4-
- Using SQL*Plus to Start Up a Database 4-
- Starting an Instance: Scenarios 4- vii
Altering Database Availability 4-
- Mounting a Database to an Instance.......................................................................................... 4-
- Opening a Closed Database 4-
- Opening a Database in Read-Only Mode 4-
- Restricting Access to an Open Database 4-
Shutting Down a Database 4-
- Shutting Down with the NORMAL Option 4-
- Shutting Down with the IMMEDIATE Option 4-
- Shutting Down with the TRANSACTIONAL Option 4-
- Shutting Down with the ABORT Option 4-
Quiescing a Database 4-
- Placing a Database into a Quiesced State................................................................................ 4-
- Restoring the System to Normal Operation 4-
- Viewing the Quiesce State of an Instance 4-
Suspending and Resuming a Database 4-
Server Processes 5- 5 Managing Oracle Processes
- Dedicated Server Processes......................................................................................................... 5-
- Shared Server Processes............................................................................................................... 5-
Configuring Oracle for the Shared Server 5-
- Initialization Parameters for Shared Server.............................................................................. 5-
- Setting the Initial Number of Dispatchers (DISPATCHERS)................................................. 5-
- Setting the Initial Number of Shared Servers (SHARED_SERVERS) 5-
- Modifying Dispatcher and Server Processes 5-
- Monitoring Shared Server 5-
About Oracle Background Processes 5-
Monitoring the Processes of an Oracle Instance 5-
- Process and Session Views 5-
- Monitoring Locks........................................................................................................................ 5-
- Trace Files and the Alert File 5-
Managing Processes for Parallel Execution 5-
- Managing the Parallel Execution Servers................................................................................ 5- viii
- Altering Parallel Execution for a Session 5-
Managing Processes for External Procedures 5-
Terminating Sessions 5-
- Identifying Which Session to Terminate 5-
- Terminating an Active Session 5-
- Terminating an Inactive Session............................................................................................... 5-
What Is a Control File? 6- 6 Managing Control Files
Guidelines for Control Files 6-
- Provide Filenames for the Control Files 6-
- Multiplex Control Files on Different Disks............................................................................... 6-
- Place Control Files Appropriately.............................................................................................. 6-
- Back Up Control Files................................................................................................................... 6-
- Manage the Size of Control Files 6-
Creating Control Files 6-
- Creating Initial Control Files....................................................................................................... 6-
- Creating Additional Copies, Renaming, and Relocating Control Files 6-
- Creating New Control Files......................................................................................................... 6-
Troubleshooting After Creating Control Files 6-
- Checking for Missing or Extra Files 6-
- Handling Errors During CREATE CONTROLFILE 6-
Backing Up Control Files 6-
Recovering a Control File Using a Current Copy 6-
- Recovering from Control File Corruption Using a Control File Copy 6-
- Recovering from Permanent Media Failure Using a Control File Copy 6-
Dropping Control Files 6-
Displaying Control File Information 6-
What Is the Online Redo Log? 7- 7 Managing the Online Redo Log
- Redo Threads................................................................................................................................. 7-
- Online Redo Log Contents 7-
- How Oracle Writes to the Online Redo Log 7-
Planning the Online Redo Log 7- ix
- Multiplexing Online Redo Log Files.......................................................................................... 7-
- Placing Online Redo Log Members on Different Disks 7-
- Setting the Size of Online Redo Log Members......................................................................... 7-
- Choosing the Number of Online Redo Log Files................................................................... 7-
- Controlling Archive Lag............................................................................................................ 7-
Creating Online Redo Log Groups and Members 7-
- Creating Online Redo Log Groups 7-
- Creating Online Redo Log Members 7-
Relocating and Renaming Online Redo Log Members 7-
Dropping Online Redo Log Groups and Members 7-
- Dropping Log Groups................................................................................................................ 7-
- Dropping Online Redo Log Members 7-
Forcing Log Switches 7-
Verifying Blocks in Redo Log Files 7-
Clearing an Online Redo Log File 7-
Viewing Online Redo Log Information 7-
What Is the Archived Redo Log? 8- 8 Managing Archived Redo Logs
Choosing Between NOARCHIVELOG and ARCHIVELOG Mode 8-
- Running a Database in NOARCHIVELOG Mode................................................................... 8-
- Running a Database in ARCHIVELOG Mode 8-
Controlling Archiving 8-
- Setting the Initial Database Archiving Mode 8-
- Changing the Database Archiving Mode.................................................................................. 8-
- Enabling Automatic Archiving................................................................................................... 8-
- Disabling Automatic Archiving 8-
- Performing Manual Archiving 8-
Specifying the Archive Destination 8-
- Specifying Archive Destinations 8-
- Understanding Archive Destination Status............................................................................ 8-
Specifying the Mode of Log Transmission 8-
- Normal Transmission Mode 8-
- Standby Transmission Mode 8-
Managing Archive Destination Failure 8- x
- Specifying the Minimum Number of Successful Destinations 8-
- Re-Archiving to a Failed Destination....................................................................................... 8-
Tuning Archive Performance by Specifying Multiple ARC n Processes 8-
Controlling Trace Output Generated by the Archivelog Process 8-
Viewing Information About the Archived Redo Log 8-
- Dynamic Performance Views.................................................................................................... 8-
- The ARCHIVE LOG LIST Command 8-
Potential Uses for Data Stored in Redo Logs 9- 9 Using LogMiner to Analyze Redo Logs
Accessing Information Stored in Redo Logs 9-
Redo Logs and Dictionary Files 9-
- Redo Logs....................................................................................................................................... 9-
- Dictionary Options 9-
- Tracking DDL Statements............................................................................................................ 9-
LogMiner Recommendations and Restrictions 9-
- Recommendations 9-
- Restrictions................................................................................................................................... 9-
Filtering Data That is Returned 9-
- Showing Only Committed Transactions 9-
- Skipping Redo Corruptions 9-
- Filtering Data By Time 9-
- Filtering Data By SCN 9-
Accessing LogMiner Information 9-
Querying V$LOGMNR_CONTENTS 9-
- Executing Reconstructed SQL Statements 9-
- Formatting of Returned Data 9-
Extracting Actual Data Values from Redo Logs 9-
- NULL Returns From the MINE_VALUE Function 9-
- Usage Rules for the MINE_VALUE and COLUMN_PRESENT Functions 9-
Supplemental Logging 9-
- Database Supplemental Logging.............................................................................................. 9-
- Table Supplemental Logging 9-
Steps in a Typical LogMiner Session 9-
- Perform Initial Setup Activities 9- xi
- Extract a Dictionary.................................................................................................................... 9-
- Specify Redo Logs for Analysis 9-
- Start a LogMiner Session 9-
- Query V$LOGMNR_CONTENTS 9-
- End a LogMiner Session 9-
Example Uses of LogMiner 9-
- Example: Using LogMiner to Track Changes Made By a Specific User............................. 9-
- Example: Using LogMiner to Calculate Table Access Statistics 9-
Enabling Processes Used for Executing Jobs 10- 10 Managing Job Queues
Managing Job Queues 10-
- The DBMS_JOB Package............................................................................................................ 10-
- Submitting a Job to the Job Queue 10-
- How Jobs Execute 10-
- Removing a Job from the Job Queue 10-
- Altering a Job............................................................................................................................. 10-
- Broken Jobs 10-
- Forcing a Job to Execute........................................................................................................... 10-
- Terminating a Job 10-
Viewing Job Queue Information 10-
- Displaying Information About a Job 10-
- Displaying Information About Running Jobs 10-
Guidelines for Managing Tablespaces 11- 11 Managing Tablespaces
- Use Multiple Tablespaces.......................................................................................................... 11-
- Specify Tablespace Default Storage Parameters 11-
- Assign Tablespace Quotas to Users 11-
Creating Tablespaces 11-
- Locally Managed Tablespaces 11-
- Dictionary-Managed Tablespaces 11-
- Temporary Tablespaces 11-
Coalescing Free Space in Dictionary-Managed Tablespaces 11-
- How Oracle Coalesces Free Space.......................................................................................... 11- xii
- Manually Coalescing Free Space 11-
- Monitoring Free Space 11-
Specifying Nonstandard Block Sizes for Tablespaces 11-
Controlling the Writing of Redo Records 11-
Altering Tablespace Availability 11-
- Taking Tablespaces Offline 11-
- Bringing Tablespaces Online................................................................................................... 11-
- Altering the Availability of Datafiles or Tempfiles.............................................................. 11-
Using Read-Only Tablespaces 11-
- Making a Tablespace Read-Only............................................................................................ 11-
- Making a Read-Only Tablespace Writable 11-
- Creating a Read-Only Tablespace on a WORM Device 11-
- Delaying the Opening of Datafiles in Read Only Tablespaces 11-
Dropping Tablespaces 11-
Diagnosing and Repairing Locally Managed Tablespace Problems 11-
- Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap) 11-
- Scenario 2: Dropping a Corrupted Segment......................................................................... 11-
- Scenario 3: Fixing Bitmap Where Overlap is Reported....................................................... 11-
- Scenario 4: Correcting Media Corruption of Bitmap Blocks 11-
- Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace 11-
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace 11-
Transporting Tablespaces Between Databases 11-
- Introduction to Transportable Tablespaces 11-
- Limitations 11-
- Compatibility Considerations for Transportable Tablespaces........................................... 11-
- Transporting Tablespaces Between Databases: A Procedure 11-
- Object Behaviors 11-
- Using Transportable Tablespaces........................................................................................... 11-
Viewing Tablespace Information 11-
- Listing Tablespaces and Default Storage Parameters: Example 11-
- Listing the Datafiles and Associated Tablespaces of a Database: Example 11-
- Displaying Statistics for Free Space (Extents) of Each Tablespace: Example................... 11-
Guidelines for Managing Datafiles 12- 12 Managing Datafiles - Determine the Number of Datafiles......................................................................................... 12- - Determine the Size of Datafiles 12- - Place Datafiles Appropriately................................................................................................... 12- - Store Datafiles Separate from Redo Log Files 12-
Creating Datafiles and Adding Datafiles to a Tablespace 12-
Changing a Datafile’s Size 12- - Enabling and Disabling Automatic Extension for a Datafile 12- - Manually Resizing a Datafile.................................................................................................... 12-
Altering Datafile Availability 12- - Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode 12- - Taking Datafiles Offline in NOARCHIVELOG Mode 12- - Altering the Availability of All Datafiles or Tempfiles in a Tablespace............................. 12-
Renaming and Relocating Datafiles 12- - Renaming and Relocating Datafiles for a Single Tablespace 12- - Renaming and Relocating Datafiles for Multiple Tablespaces 12-
Dropping Datafiles 12-
Verifying Data Blocks in Datafiles 12-
Mapping Files to Physical Devices 12- - Overview of Oracle’s File Mapping Interface 12- - How Oracle’s File Mapping Interface Works....................................................................... 12- - Using Oracle’s File Mapping Interface 12- - File Mapping Examples 12-
- Viewing Datafile Information 12-
What is Undo? 13- 13 Managing Undo Space
Specifying the Mode for Undo Space Management 13- - Starting an Instance in Automatic Undo Management Mode 13- - Starting an Instance in Manual Undo Management Mode 13-
Managing Undo Tablespaces 13- - Creating an Undo Tablespace................................................................................................... 13- - Altering an Undo Tablespace 13- - Dropping an Undo Tablespace................................................................................................. 13-
- Switching Undo Tablespaces 13- xiv
- Establishing User Quotas for Undo Space 13-
- Specifying the Retention Period for Undo Information........................................................ 13-
- Viewing Information About Undo Space.............................................................................. 13-
Managing Rollback Segments 13-
- Guidelines for Managing Rollback Segments 13-
- Creating Rollback Segments.................................................................................................... 13-
- Altering Rollback Segments 13-
- Explicitly Assigning a Transaction to a Rollback Segment 13-
- Dropping Rollback Segments.................................................................................................. 13-
- Viewing Rollback Segment Information 13-
Managing Space in Data Blocks 14- 14 Managing Space for Schema Objects
- Specifying the PCTFREE Parameter 14-
- Specifying the PCTUSED Parameter 14-
- Selecting Associated PCTUSED and PCTFREE Values 14-
- Specifying the Transaction Entry Parameters: INITRANS and MAXTRANS................... 14-
Setting Storage Parameters 14-
- Identifying the Storage Parameters.......................................................................................... 14-
- Setting Default Storage Parameters for Segments in a Tablespace 14-
- Setting Storage Parameters for Data Segments 14-
- Setting Storage Parameters for Index Segments................................................................... 14-
- Setting Storage Parameters for LOBs, Varrays, and Nested Tables 14-
- Changing Values for Storage Parameters 14-
- Understanding Precedence in Storage Parameters.............................................................. 14-
- Example of How Storage Parameters Effect Space Allocation 14-
Managing Resumable Space Allocation 14-
- Resumable Space Allocation Overview................................................................................. 14-
- Enabling and Disabling Resumable Space Allocation......................................................... 14-
- Detecting Suspended Statements 14-
- Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger........ 14-
Deallocating Space 14-
- Viewing the High Water Mark 14- xv
- Issuing Space Deallocation Statements 14-
- Examples of Deallocating Space............................................................................................. 14-
Understanding Space Use of Datatypes 14-
Guidelines for Managing Tables 15- 15 Managing Tables
- Design Tables Before Creating Them 15-
- Specify How Data Block Space Is to Be Used......................................................................... 15-
- Specify the Location of Each Table 15-
- Consider Parallelizing Table Creation..................................................................................... 15-
- Consider Using NOLOGGING When Creating Tables 15-
- Estimate Table Size and Set Storage Parameters 15-
- Plan for Large Tables 15-
- Table Restrictions........................................................................................................................ 15-
Creating Tables 15-
- Creating a Table 15-
- Creating a Temporary Table 15-
- Parallelizing Table Creation...................................................................................................... 15-
- Automatically Collecting Statistics on Tables 15-
Altering Tables 15-
- Altering Physical Attributes of a Table 15-
- Moving a Table to a New Segment or Tablespace............................................................... 15-
- Manually Allocating Storage for a Table 15-
- Modifying an Existing Column’s Definition 15-
- Adding Table Columns 15-
- Renaming Table Columns 15-
- Dropping Table Columns........................................................................................................ 15-
Redefining Tables Online 15-
- Features of Online Table Redefinition................................................................................... 15-
- The DBMS_REDEFINITION Package 15-
- Steps for Online Redefinition of Tables................................................................................. 15-
- Intermediate Synchronization 15-
- Abort and Cleanup After Errors............................................................................................. 15-
- Example of Online Table Redefinition 15-
- Restrictions................................................................................................................................. 15- xvi
Dropping Tables 15-
Managing Index-Organized Tables 15-
- What are Index-Organized Tables.......................................................................................... 15-
- Creating Index-Organized Tables 15-
- Maintaining Index-Organized Tables.................................................................................... 15-
- Analyzing Index-Organized Tables 15-
- Using the ORDER BY Clause with Index-Organized Tables 15-
- Converting Index-Organized Tables to Regular Tables...................................................... 15-
Managing External Tables 15-
- Creating External Tables.......................................................................................................... 15-
- Altering External Tables 15-
- Dropping External Tables........................................................................................................ 15-
- System and Object Privileges for External Tables................................................................ 15-
Viewing Information About Tables 15-
Guidelines for Managing Indexes 16- 16 Managing Indexes
- Create Indexes After Inserting Table Data.............................................................................. 16-
- Index the Correct Tables and Columns 16-
- Order Index Columns for Performance................................................................................... 16-
- Limit the Number of Indexes for Each Table.......................................................................... 16-
- Drop Indexes That Are No Longer Required 16-
- Specify Index Block Space Use.................................................................................................. 16-
- Estimate Index Size and Set Storage Parameters 16-
- Specify the Tablespace for Each Index..................................................................................... 16-
- Consider Parallelizing Index Creation 16-
- Consider Creating Indexes with NOLOGGING 16-
- Consider Costs and Benefits of Coalescing or Rebuilding Indexes..................................... 16-
- Consider Cost Before Disabling or Dropping Constraints 16-
Creating Indexes 16-
- Creating an Index Explicitly.................................................................................................... 16-
- Creating a Unique Index Explicitly........................................................................................ 16-
- Creating an Index Associated with a Constraint 16-
- Collecting Incidental Statistics when Creating an Index 16-
- Creating a Large Index 16- xvii
- Creating an Index Online 16-
- Creating a Function-Based Index 16-
- Creating a Key-Compressed Index 16-
Altering Indexes 16-
- Altering Storage Characteristics of an Index 16-
- Rebuilding an Existing Index.................................................................................................. 16-
- Monitoring Index Usage.......................................................................................................... 16-
Monitoring Space Use of Indexes 16-
Dropping Indexes 16-
Viewing Index Information 16-
What Are Partitioned Tables and Indexes? 17- 17 Managing Partitioned Tables and Indexes
Partitioning Methods 17-
- When to Use the Range Partitioning Method......................................................................... 17-
- When to Use the Hash Partitioning Method 17-
- When to Use the List Partitioning Method 17-
- When to Use the Composite Range-Hash Partitioning Method.......................................... 17-
- When to Use the Composite Range-List Partitioning Method 17-
Creating Partitioned Tables 17-
- Creating Range-Partitioned Tables 17-
- Creating Hash-Partitioned Tables.......................................................................................... 17-
- Creating List-Partitioned Tables............................................................................................. 17-
- Creating Composite Range-Hash Partitioned Tables 17-
- Creating Composite Range-List Partitioned Tables 17-
- Using Subpartition Templates to Describe Composite Partitioned Tables...................... 17-
- Creating Partitioned Index-Organized Tables 17-
- Partitioning Restrictions for Multiple Block Sizes 17-
Maintaining Partitioned Tables 17-
- Updating Global Indexes Automatically 17-
- Adding Partitions 17-
- Coalescing Partitions................................................................................................................ 17-
- Dropping Partitions.................................................................................................................. 17-
- Exchanging Partitions 17-
- Merging Partitions 17- xviii
- Modifying Default Attributes 17-
- Modifying Real Attributes of Partitions 17-
- Modifying List Partitions: Adding Values 17-
- Modifying List Partitions: Dropping Values 17-
- Modifying a Subpartition Template....................................................................................... 17-
- Moving Partitions 17-
- Rebuilding Index Partitions 17-
- Renaming Partitions 17-
- Splitting Partitions 17-
- Truncating Partitions................................................................................................................ 17-
Partitioned Tables and Indexes Examples 17-
- Moving the Time Window in a Historical Table.................................................................. 17-
- Converting a Partition View into a Partitioned Table......................................................... 17-
Viewing Information About Partitioned Tables and Indexes 17-
Guidelines for Managing Clusters 18- 18 Managing Clusters
- Choose Appropriate Tables for the Cluster 18-
- Choose Appropriate Columns for the Cluster Key 18-
- Specify Data Block Space Use 18-
- Specify the Space Required by an Average Cluster Key and Its Associated Rows 18-
- Specify the Location of Each Cluster and Cluster Index Rows............................................ 18-
- Estimate Cluster Size and Set Storage Parameters................................................................. 18-
Creating Clusters 18-
- Creating Clustered Tables 18-
- Creating Cluster Indexes 18-
Altering Clusters 18-
- Altering Clustered Tables.......................................................................................................... 18-
- Altering Cluster Indexes.......................................................................................................... 18-
Dropping Clusters 18-
- Dropping Clustered Tables 18-
- Dropping Cluster Indexes 18-
Viewing Information About Clusters 18-
When to Use Hash Clusters 19- 19 Managing Hash Clusters
- Situations Where Hashing Is Useful 19-
- Situations Where Hashing Is Not Advantageous.................................................................. 19-
Creating Hash Clusters 19-
- Creating Single-Table Hash Clusters....................................................................................... 19-
- Controlling Space Use Within a Hash Cluster 19-
- Estimating Size Required by Hash Clusters 19-
Altering Hash Clusters 19-
Dropping Hash Clusters 19-
Viewing Information About Hash Clusters 19-
Managing Views 20- 20 Managing Views, Sequences, and Synonyms
- Creating Views............................................................................................................................ 20-
- Updating a Join View................................................................................................................. 20-
- Altering Views 20-
- Dropping Views........................................................................................................................ 20-
- Replacing Views 20-
Managing Sequences 20-
- Creating Sequences 20-
- Altering Sequences 20-
- Dropping Sequences 20-
Managing Synonyms 20-
- Creating Synonyms 20-
- Dropping Synonyms 20-
Viewing Information About Views, Synonyms, and Sequences 20-
Creating Multiple Tables and Views in a Single Operation 21- 21 General Management of Schema Objects
Renaming Schema Objects 21-
Analyzing Tables, Indexes, and Clusters 21-
- Collecting Statistics for Tables, Indexes, and Clusters 21-
- Validating Tables, Indexes, Clusters, and Materialized Views 21-
- Listing Chained Rows of Tables and Clusters........................................................................ 21- xx
Truncating Tables and Clusters 21-
- Using DELETE 21-
- Using DROP and CREATE...................................................................................................... 21-
- Using TRUNCATE 21-
Enabling and Disabling Triggers 21-
- Enabling Triggers...................................................................................................................... 21-
- Disabling Triggers 21-
Managing Integrity Constraints 21-
- Integrity Constraint States....................................................................................................... 21-
- Setting Integrity Constraints Upon Definition 21-
- Modifying, Renaming, or Dropping Existing Integrity Constraints 21-
- Deferring Constraint Checks................................................................................................... 21-
- Reporting Constraint Exceptions............................................................................................ 21-
- Viewing Constraint Information 21-
Managing Object Dependencies 21-
- Manually Recompiling Views................................................................................................. 21-
- Manually Recompiling Procedures and Functions.............................................................. 21-
- Manually Recompiling Packages 21-
Managing Object Name Resolution 21-
Changing Storage Parameters for the Data Dictionary 21-
- Structures in the Data Dictionary 21-
- Errors that Require Changing Data Dictionary Storage 21-
Displaying Information About Schema Objects 21-
- Using PL/SQL Packages to Display Information About Schema Objects 21-
- Using Views to Display Information About Schema Objects 21-
Options for Repairing Data Block Corruption 22- 22 Detecting and Repairing Data Block Corruption
About the DBMS_REPAIR Package 22-
- DBMS_REPAIR Procedures 22-
- Limitations and Restrictions 22-
Using the DBMS_REPAIR Package 22-
- Task 1: Detect and Report Corruptions 22-
- Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR 22-

Oracle Administrator Guide, Apuntes de Psicología

Documentos relacionados

Vista previa parcial del texto

¡Descarga Oracle Administrator Guide y más Apuntes en PDF de Psicología solo en Docsity!

Oracle9i

Database Administrator’s Guide

Release 2 (9.2)

iv