Oracle8i Administrator's Guide -- Contents

Types of Oracle Users

Database Administrators

Security Officers

Application Developers

Application Administrators

Database Users

Network Administrators

Database Administrator Security and Privileges

The Database Administrator's Operating System Account

Database Administrator Usernames

The DBA Role

Database Administrator Authentication

Selecting an Authentication Method

Using Operating System Authentication

OSOPER and OSDBA

Using an Authentication Password File

Password File Administration

Using ORAPWD

Setting REMOTE_LOGIN_ PASSWORDFILE

Adding Users to a Password File

Connecting with Administrator Privileges

Maintaining a Password File

Database Administrator Utilities

SQL*Loader

Export and Import

Priorities of a Database Administrator

Step 1: Install the Oracle Software

Step 2: Evaluate the Database Server Hardware

Step 3: Plan the Database

Step 4: Create and Open the Database

Step 5: Implement the Database Design

Step 6: Back Up the Database

Step 7: Enroll System Users

Step 8: Tune Database Performance

Identifying Your Oracle Database Software Release

Release Number Format

Checking Your Current Release Number

Considerations Before Creating a Database

Planning for Database Creation

Creation Prerequisites

Deciding How to Create an Oracle Database

The Oracle Database Configuration Assistant (DBCA)

Advantages of Using DBCA

DBCA Modes for Database Creation

Identifying Your Database Environment

Selecting the Database Creation Method

Manually Creating an Oracle Database

Steps for Creating an Oracle Database

Examining a Database Creation Script

Troubleshooting Database Creation

Dropping a Database

Installation Parameters

A Sample Initialization File

Editing the Initialization Parameter File

Considerations After Creating a Database

Initial Tuning Guidelines

Allocating Rollback Segments

Choosing the Number of DB_BLOCK_LRU_LATCHES

Distributing I/O

Starting Up a Database

Preparing to Start an Instance

Options for Starting Up a Database

Starting an Instance: Scenarios

Altering Database Availability

Mounting a Database to an Instance

Opening a Closed Database

Opening a Database in Read-Only Mode

Restricting Access to an Open Database

Shutting Down a Database

Shutting Down with the NORMAL Option

Shutting Down with the IMMEDIATE Option

Shutting Down with the TRANSACTIONAL Option

Shutting Down with the ABORT Option

Suspending and Resuming a Database

Using Initialization Parameter Files

The Sample Initialization Parameter File

The Number of Initialization Parameter Files

The Location of the Initialization Parameter File in Distributed Environments

Server Processes

Dedicated Server Processes

Multi-Threaded Server Processes

Configuring Oracle for the Multi-Threaded Server

Initialization Parameters for MTS

MTS_DISPATCHERS: Setting the Initial Number of Dispatchers

MTS_SERVERS: Setting the Initial Number of Shared Servers

Modifying Dispatcher and Server Processes

Monitoring MTS

Tracking Oracle Background Processes

What are the Oracle Background Processes

Monitoring the Processes of an Oracle Instance

Trace Files, the Alert Log, and Background Processes

Managing Processes for the Parallel Query Option

Managing the Query Servers

Variations in the Number of Query Server Processes

Managing Processes for External Procedures

Setting up an Environment for Calling External Routines

Sample Entry in tnsnames.ora

Sample Entry in listener.ora

Terminating Sessions

Identifying Which Session to Terminate

Terminating an Active Session

Terminating an Inactive Session

What is a Control File?

Guidelines for Control Files

Name Control Files

Multiplex Control Files on Different Disks

Place Control Files Appropriately

Manage the Size of Control Files

Creating Control Files

Creating Initial Control Files

Creating Additional Control File Copies, and Renaming and Relocating Control Files

New Control Files

Creating New Control Files

Troubleshooting After Creating Control Files

Checking for Missing or Extra Files

Handling Errors During CREATE CONTROLFILE

Dropping Control Files

What Is the Online Redo Log?

Redo Threads

Online Redo Log Contents

How Oracle Writes to the Online Redo Log

Planning the Online Redo Log

Multiplexing Online Redo Log Files

Placing Online Redo Log Members on Different Disks

Setting the Size of Online Redo Log Members

Choosing the Number of Online Redo Log Files

Creating Online Redo Log Groups and Members

Creating Online Redo Log Groups

Creating Online Redo Log Members

Renaming and Relocating Online Redo Log Members

Dropping Online Redo Log Groups and Members

Dropping Log Groups

Dropping Online Redo Log Members

Forcing Log Switches

Verifying Blocks in Redo Log Files

Clearing an Online Redo Log File

Restrictions

Listing Information about the Online Redo Log

What Is the Archived Redo Log?

Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

Running a Database in NOARCHIVELOG Mode

Running a Database in ARCHIVELOG Mode

Controlling the Archiving Mode

Setting the Initial Database Archiving Mode

Changing the Database Archiving Mode

Enabling Automatic Archiving

Disabling Automatic Archiving

Performing Manual Archiving

Specifying the Archive Destination

Specifying Archive Destinations

Understanding Archive Destination States

Specifying the Mode of Log Transmission

Normal Transmission Mode

Standby Transmission Mode

Managing Archive Destination Failure

Specifying the Minimum Number of Successful Destinations

Re-Archiving to a Failed Destination

Tuning Archive Performance

Specifying Multiple ARCn Processes

Adjusting Archive Buffer Parameters

Displaying Archived Redo Log Information

Fixed Views

The ARCHIVE LOG LIST SQL Statement

Controlling Trace Output Generated by the Archivelog Process

Using LogMiner to Analyze Online and Archived Redo Logs

How Can You Use LogMiner?

Restrictions

Creating a Dictionary File

Specifying Redo Logs for Analysis

Using LogMiner

Using LogMiner: Scenarios

SNP Background Processes

Multiple SNP processes

Starting up SNP processes

Managing Job Queues

The DBMS_JOB Package

Submitting a Job to the Job Queue

How Jobs Execute

Removing a Job from the Job Queue

Altering a Job

Broken Jobs

Forcing a Job to Execute

Terminating a Job

Viewing Job Queue Information

Guidelines for Managing Tablespaces

Use Multiple Tablespaces

Specify Tablespace Storage Parameters

Assign Tablespace Quotas to Users

Creating Tablespaces

Dictionary-Managed Tablespaces

Locally Managed Tablespaces

Temporary Tablespaces

Managing Tablespace Allocation

Storage Parameters in Locally Managed Tablespaces

Storage Parameters for Dictionary-Managed Tablespaces

Coalescing Free Space in Dictionary-Managed Tablespaces

Altering Tablespace Availability

Taking Tablespaces Offline

Bringing Tablespaces Online

Read-Only Tablespaces

Making a Tablespace Read-Only

Making a Read-Only Tablespace Writable

Creating a Read-Only Tablespace on a WORM Device

Delaying the Opening of Datafiles in Read Only Tablespaces

Dropping Tablespaces

Using the DBMS_SPACE_ADMIN Package

Transporting Tablespaces Between Databases

Introduction to Transportable Tablespaces

Limitations

Procedure for Transporting Tablespaces Between Databases

Object Behaviors

Using Transportable Tablespaces

Viewing Information About Tablespaces

Guidelines for Managing Datafiles

Determine the Number of Datafiles

Set the Size of Datafiles

Place Datafiles Appropriately

Store Datafiles Separate From Redo Log Files

Creating and Adding Datafiles to a Tablespace

Changing a Datafile's Size

Enabling and Disabling Automatic Extension for a Datafile

Manually Resizing a Datafile

Altering Datafile Availability

Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode

Taking Datafiles Offline in NOARCHIVELOG Mode

Renaming and Relocating Datafiles

Renaming and Relocating Datafiles for a Single Tablespace

Renaming and Relocating Datafiles for Multiple Tablespaces

Verifying Data Blocks in Datafiles

Viewing Information About Datafiles

Guidelines for Managing Rollback Segments

Use Multiple Rollback Segments

Choose Between Public and Private Rollback Segments

Specify Rollback Segments to Acquire Automatically

Approximate Rollback Segment Sizes

Create Rollback Segments with Many Equally Sized Extents

Set an Optimal Number of Extents for Each Rollback Segment

Place Rollback Segments in a Separate Tablespace

Creating Rollback Segments

The CREATE ROLLBACK SEGMENT Statement

Bringing New Rollback Segments Online

Setting Storage Parameters When Creating a Rollback Segment

Altering Rollback Segments

Changing Rollback Segment Storage Parameters

Shrinking a Rollback Segment Manually

Changing the ONLINE/OFFLINE Status of Rollback Segments

Explicitly Assigning a Transaction to a Rollback Segment

Dropping Rollback Segments

Monitoring Rollback Segment Information

Displaying Rollback Segment Information

Rollback Segment Statistics

Displaying All Rollback Segments

Displaying Whether a Rollback Segment Has Gone Offline

Managing Space in Data Blocks

The PCTFREE Parameter

The PCTUSED Parameter

Selecting Associated PCTUSED and PCTFREE Values

Transaction Entry Settings (INITRANS and MAXTRANS)

Setting Storage Parameters

Identifying the Storage Parameters

Setting Default Storage Parameters for Segments in a Tablespace

Setting Storage Parameters for Data Segments

Setting Storage Parameters for Index Segments

Setting Storage Parameters for LOBs, Varrays, and Nested Tables

Changing Values for Storage Parameters

Understanding Precedence in Storage Parameters

Deallocating Space

Viewing the High Water Mark

Issuing Space Deallocation Statements

Understanding Space Use of Datatypes

Guidelines for Managing Tables

Design Tables Before Creating Them

Specify How Data Block Space Is to Be Used

Specify Transaction Entry Parameters

Specify the Location of Each Table

Parallelize Table Creation

Consider Creating UNRECOVERABLE Tables

Estimate Table Size and Set Storage Parameters

Plan for Large Tables

Table Restrictions

Creating Tables

Altering Tables

Moving a Table to a New Segment or Tablespace

Manually Allocating Storage for a Table

Dropping Columns

Dropping Tables

Index-Organized Tables

What are Index-Organized Tables

Creating Index-Organized Tables

Maintaining Index-Organized Tables

Analyzing Index-Organized Tables

Using the ORDER BY Clause with Index-Organized Tables

Converting Index-Organized Tables to Regular Tables

Guidelines for Managing Indexes

Create Indexes After Inserting Table Data

Limit the Number of Indexes per Table

Specify Transaction Entry Parameters

Specify Index Block Space Use

Estimate Index Size and Set Storage Parameters

Specify the Tablespace for Each Index

Parallelize Index Creation

Consider Creating Indexes with NOLOGGING

Consider Costs and Benefits of Coalescing or Rebuilding Indexes

Consider Cost Before Disabling or Dropping Constraints

Creating Indexes

Creating an Index Explicitly

Creating an Index Associated with a Constraint

Creating an Index Online

Creating a Function-Based Index

Rebuilding an Existing Index

Creating a Key-Compressed Index

Altering Indexes

Monitoring Space Use of Indexes

Dropping Indexes

What Are Partitioned Tables and Indexes?

Partitioning Methods

Using the Range Partitioning Method

Using the Hash Partitioning Method

Using the Composite Partitioning Method

Creating Partitions

Creating Range Partitions

Creating Hash Partitions

Creating Composite Partitions and Subpartitions

Maintaining Partitions

Adding Partitions

Coalescing Partitions

Dropping Partitions

Exchanging Partitions

Merging Partitions

Modifying Partition Default Attributes

Modifying Real Attributes of Partitions

Moving Partitions

Rebuilding Index Partitions

Renaming Partitions

Splitting Partitions

Truncating Partitions

Partitioned Tables and Indexes Examples

Moving the Time Window in a Historical Table

Converting a Partition View into a Partitioned Table

Guidelines for Managing Clusters

Choose Appropriate Tables for the Cluster

Choose Appropriate Columns for the Cluster Key

Specify Data Block Space Use

Specify the Space Required by an Average Cluster Key and Its Associated Rows

Specify the Location of Each Cluster and Cluster Index Rows

Estimate Cluster Size and Set Storage Parameters

Creating Clusters

Creating Clustered Tables

Creating Cluster Indexes

Altering Clusters

Altering Cluster Tables and Cluster Indexes

Dropping Clusters

Dropping Clustered Tables

Dropping Cluster Indexes

Should You Use Hash Clusters?

Advantages of Hashing

Disadvantages of Hashing

Creating Hash Clusters

Creating Single-Table Hash Clusters

Controlling Space Use Within a Hash Cluster

How to Estimate Size Required by Hash Clusters and Set Storage Parameters

Altering Hash Clusters

Dropping Hash Clusters

Managing Views

Managing Sequences

Creating Sequences

Altering Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Dropping Synonyms

Creating Multiple Tables and Views in a Single Operation

Renaming Schema Objects

Analyzing Tables, Indexes, and Clusters

Using Statistics for Tables, Indexes, and Clusters

Validating Tables, Indexes, and Clusters

Listing Chained Rows of Tables and Clusters

Truncating Tables and Clusters

Enabling and Disabling Triggers

Enabling Triggers

Disabling Triggers

Managing Integrity Constraints

Integrity Constraint States

Setting Integrity Constraints Upon Definition

Modifying Existing Integrity Constraints

Deferring Constraint Checks

Managing Constraints That Have Associated Indexes

Dropping Integrity Constraints

Reporting Constraint Exceptions

Managing Object Dependencies

Manually Recompiling Views

Manually Recompiling Procedures and Functions

Manually Recompiling Packages

Managing Object Name Resolution

Changing Storage Parameters for the Data Dictionary

Structures in the Data Dictionary

Errors that Require Changing Data Dictionary Storage

Displaying Information About Schema Objects

Example 1: Displaying Schema Objects By Type

Example 2: Displaying Column Information

Example 3: Displaying Dependencies of Views and Synonyms

Example 4: Displaying General Segment Information

Example 5: Displaying General Extent Information

Example 6: Displaying the Free Space (Extents) of a Database

Example 7: Displaying Segments that Cannot Allocate Additional Extents

Options for Repairing Data Block Corruption

About the DBMS_REPAIR Package

DBMS_REPAIR Procedures

Limitations and Restrictions

Using the DBMS_REPAIR Package

Stage 1: Detect and Report Corruptions

Stage 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR

Stage 3: Make Objects Usable

Stage 4: Repair Corruptions and Rebuild Lost Data

DBMS_REPAIR Examples

Using ADMIN_TABLES to Build a Repair Table or Orphan Key Table

Using the CHECK_OBJECT Procedure to Detect Corruption

Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure

Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEYS

Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure

Enabling or Disabling the Skipping of Corrupt Blocks: SKIP_CORRUPT_BLOCKS

System Security Policy

Database User Management

User Authentication

Operating System Security

Data Security Policy

User Security Policy

General User Security

End-User Security

Administrator Security

Application Developer Security

Application Administrator Security

Password Management Policy

Account Locking

Password Aging and Expiration

Password History

Password Complexity Verification

Auditing Policy

Session and User Licensing

Concurrent Usage Licensing

Named User Limits

Viewing Licensing Limits and Current Values

User Authentication

Database Authentication

External Authentication

Global Authentication and Authorization

Multi-Tier Authentication and Authorization

Oracle Users

Creating Users

Altering Users

Dropping Users

Managing Resources with Profiles

Enabling and Disabling Resource Limits

Creating Profiles

Assigning Profiles

Altering Profiles

Using Composite Limits

Dropping Profiles

Listing Information About Database Users and Profiles

Listing Information about Users and Profiles: Examples

Examples

Identifying User Privileges

System Privileges

Object Privileges

Managing User Roles

Granting User Privileges and Roles

Granting System Privileges and Roles

Granting Object Privileges and Roles

Granting Privileges on Columns

Revoking User Privileges and Roles

Revoking System Privileges and Roles

Revoking Object Privileges and Roles

Effects of Revoking Privileges

Granting to and Revoking from the User Group PUBLIC

When Do Grants and Revokes Take Effect?

The SET ROLE Statement

Specifying Default Roles

Restricting the Number of Roles that a User Can Enable

Granting Roles Using the Operating System or Network

Using Operating System Role Identification

Using Operating System Role Management

Granting and Revoking Roles When OS_ROLES=TRUE

Enabling and Disabling Roles When OS_ROLES=TRUE

Using Network Connections with Operating System Role Management

Listing Privilege and Role Information

Listing All System Privilege Grants

Listing All Role Grants

Listing Object Privileges Granted to a User

Listing the Current Privilege Domain of Your Session

Listing Roles of the Database

Listing Information About the Privilege Domains of Roles

Guidelines for Auditing

Audit via the Database or Operating System

Keep Audited Information Manageable

Creating and Deleting the Database Audit Trail Views

Creating the Audit Trail Views

Deleting the Audit Trail Views

Managing Audit Trail Information

Events Audited by Default

Setting Auditing Options

Enabling and Disabling Database Auditing

Controlling the Growth and Size of the Audit Trail

Protecting the Audit Trail

Viewing Database Audit Trail Information

Listing Active Statement Audit Options

Listing Active Privilege Audit Options

Listing Active Object Audit Options for Specific Objects

Listing Default Object Audit Options

Listing Audit Records

Listing Audit Records for the AUDIT SESSION Option

Auditing Through Database Triggers

What is the Database Resource Manager?

Administering the Database Resource Manager

Creating and Managing Resource Plans

Using the Pending Area for Creating Plan Schemas

Creating Resource Plans

Creating Resource Consumer Groups

Specifying Resource Plan Directives

Managing Resource Consumer Groups

Assigning an Initial Resource Consumer Group

Changing Resource Consumer Groups

Granting the Switch Privilege

Enabling the Database Resource Manager

Putting it All Together: Examples

A Multilevel Schema

An Oracle Supplied Plan

Database Resource Manager Views

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Basic Database Administration

1 The Oracle Database Administrator

2 Creating an Oracle Database

3 Starting Up and Shutting Down

Part II Oracle Server Configuration

4 Managing Oracle Processes

5 Managing Control Files

6 Managing the Online Redo Log

7 Managing Archived Redo Logs

8 Managing Job Queues

Part III Database Storage

9 Managing Tablespaces

10 Managing Datafiles

11 Managing Rollback Segments

Part IV Schema Objects

12 Guidelines for Managing Schema Objects

13 Managing Tables

14 Managing Indexes

15 Managing Partitioned Tables and Indexes

16 Managing Clusters

17 Managing Hash Clusters

18 Managing Views, Sequences and Synonyms

19 General Management of Schema Objects

20 Addressing Data Block Corruption

Part V Database Security

21 Establishing Security Policies

22 Managing Users and Resources

23 Managing User Privileges and Roles

24 Auditing Database Use

Part VI Database Resource Management

25 The Database Resource Manager

Index