Oracle8 Administrator's Guide
Release 8.0
A58397-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Basic Database Administration

1 The Oracle Database Administrator

Types of Oracle Users

Database Administrators

Security Officers

Application Developers

Application Administrators

Network Administrators

Database Administrator Security and Privileges

The Database Administrator's Operating System Account

Database Administrator Usernames

Database Administrator Authentication

Selecting an Authentication Method

Using Operating System Authentication

OSOPER and OSDBA

Using an Authentication Password File

Password File Administration

Setting REMOTE_LOGIN_ PASSWORDFILE

Adding Users to a Password File

Connecting with Administrator Privileges

Maintaining a Password File

Database Administrator Utilities

Enterprise Manager

Export and Import

Initial 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 Oracle Software Releases

Release Number Format

Versions of Other Oracle Software

Checking Your Current Release Number

2 Creating an Oracle Database

Considerations Before Creating a Database

Creation Prerequisites

Using an Initial Database

Migrating an Older Version of the Database

Creating an Oracle Database

Steps for Creating an Oracle Database

Creating a Database: Example

Troubleshooting Database Creation

Dropping a Database

DB_NAME and DB_DOMAIN

DB_BLOCK_BUFFERS

ROLLBACK_SEGMENTS

License Parameters

LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING

LICENSE_MAX_USERS

Considerations After Creating a Database

Initial Tuning Guidelines

Allocating Rollback Segments

Choosing the Number of DB_BLOCK_LRU_LATCHES

Distributing I/O

3 Starting Up and Shutting Down

Startup Procedures

Preparing to Start an Instance

Starting an Instance: Scenarios

Altering Database Availability

Mounting a Database to an Instance

Opening a Closed Database

Restricting Access to an Open Database

Shutdown Procedures

Shutting Down a Database Under Normal Conditions

Shutting Down a Database Immediately

Shutdown Transactional

Aborting an Instance

Using Parameter Files

The Sample Parameter File

The Number of Parameter Files

The Location of the Parameter File in Distributed Environments

Part II Oracle Server Configuration

4 Managing Oracle Processes

Configuring Oracle for Dedicated Server Processes

When to Connect to a Dedicated Server Process

Configuring Oracle for Multi-Threaded Server Processes

SHARED_POOL_ SIZE: Allocating Additional Space in the Shared Pool for Shared Server

MTS_LISTENER_ ADDRESS: Setting the Listener Process Address

MTS_SERVICE: Specifying Service Names for Dispatchers

MTS_DISPATCHERS: Setting the Initial Number of Dispatchers

MTS_MAX_ DISPATCHERS: Setting the Maximum Number of Dispatchers

MTS_SERVERS: Setting the Initial Number of Shared Server Processes

MTS_MAX_SERVERS: Setting the Maximum Number of Shared Server Processes

Modifying Server Processes

Changing the Minimum Number of Shared Server Processes

Adding and Removing Dispatcher Processes

Tracking Oracle Processes

Monitoring the Processes of an Oracle Instance

Trace Files, the ALERT File, and Background Processes

Starting the Checkpoint Process

Managing Processes for the Parallel Query Option

Managing the Query Servers

Variations in the Number of Query Server Processes

Managing Processes for External Procedures

Terminating Sessions

Identifying Which Session to Terminate

Terminating an Active Session

Terminating an Inactive Session

5 Managing the Online Redo Log

Planning the Online Redo Log

Multiplex the Online Redo Log

Place Online Redo Log Members on Different Disks

Set the Size of Online Redo Log Members

Choose an Appropriate 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

Dropping Online Redo Log Members

Controlling Checkpoints and Log Switches

Setting Database Checkpoint Intervals

Forcing a Log Switch

Forcing a Fast Database Checkpoint Without a Log Switch

Verifying Blocks in Redo Log Files

Clearing an Online Redo Log File

Listing Information about the Online Redo Log

6 Managing Control Files

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 Copies of the Control File, 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

7 Managing Job Queues

SNP Background Processes

Multiple SNP processes

Starting up SNP processes

Managing Job Queues

DBMS_JOB Package

Submitting a Job to the Job Queue

How Jobs Execute

Removing a Job From the Job Queue

Forcing a Job to Execute

Terminating a Job

Viewing Job Queue Information

Part III Oracle Server Configuration

8 Managing Tablespaces

Guidelines for Managing Tablespaces

Using Multiple Tablespaces

Specifying Tablespace Storage Parameters

Assigning Tablespace Quotas to Users

Creating Tablespaces

Creating a Temporary Tablespace

Managing Tablespace Allocation

Altering Storage Settings for Tablespaces

Coalescing Free Space

Altering Tablespace Availability

Bringing Tablespaces Online

Taking Tablespaces Offline

Making a Tablespace Read-Only

Making a Read-Only Tablespace Writeable

Creating a Read-Only Tablespace on a WORM Device

Dropping Tablespaces

Viewing Information About Tablespaces

9 Managing Datafiles

Guidelines for Managing Datafiles

Number of Datafiles

Set the Size of Datafiles

Place Datafiles Appropriately

Store Datafiles Separately 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 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

10 Guidelines for Managing Schema Objects

Managing Space in Data Blocks

The PCTFREE Parameter

The PCTUSED Parameter

Selecting Associated PCTUSED and PCTFREE Values

Setting Storage Parameters

Storage Parameters You Can Specify

Setting INITRANS and MAXTRANS

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 LOB Segments

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

Summary of Oracle Datatypes

11 Managing Partitioned Tables and Indexes

What Are Partitioned Tables and Indexes?

Creating Partitions

Maintaining Partitions

Moving Partitions

Adding Partitions

Dropping Partitions

Truncating Partitions

Splitting Partitions

Merging Partitions

Exchanging Table Partitions

Rebuilding Index Partitions

Moving the Time Window in a Historical Table

Quiescing Applications During a Multi-Step Maintenance Operation

12 Managing Tables

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

Manually Allocating Storage for a Table

Dropping Tables

Index-Organized Tables

What Are Index-Organized Tables?

Creating Index-Organized Tables

Maintaining Index-Organized Tables

Scenario: Using the ORDER BY Clause with Index-Organized Tables

Scenario: Updating the Key Column

Converting Index-Organized Tables to Regular Tables

13 Managing Views, Sequences and Synonyms

Modifying a Join View

Replacing Views

Managing Sequences

Creating Sequences

Altering Sequences

Initialization Parameters Affecting Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Dropping Synonyms

14 Managing Indexes

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

Specify the Tablespace for Each Index

Parallelize Index Creation

Consider Creating UNRECOVERABLE Indexes

Estimate Index Size and Set Storage Parameters

Considerations Before Disabling or Dropping Constraints

Creating Indexes

Creating an Index Associated with a Constraint

Creating an Index Explicitly

Re-creating an Existing Index

Altering Indexes

Monitoring Space Use of Indexes

Dropping Indexes

15 Managing Clusters

Guidelines for Managing Clusters

Cluster Appropriate Tables

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

16 Managing Hash Clusters

Guidelines for Managing Hash Clusters

Advantages of Hashing

Disadvantages of Hashing

Estimate Size Required by Hash Clusters and Set Storage Parameters

Creating Hash Clusters

Controlling Space Use Within a Hash Cluster

Altering Hash Clusters

Dropping Hash Clusters

17 General Management of Schema Objects

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

Deferring Constraint Checks

Managing Constraints That Have Associated Indexes

Disabling, Enable Novalidating and Enabling Integrity Constraints Upon Definition

Enabling and Disabling Existing Integrity Constraints

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

Dictionary Storage Oracle Packages

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

Part IV Database Security

18 Managing Rollback Segments

Guidelines for Managing Rollback Segments

Use Multiple Rollback Segments

Choose Between Public and Private Rollback Segments

Specify Rollback Segments to Acquire Automatically

Set Rollback Segment Sizes Appropriately

Create Rollback Segments with Many Equally Sized Extents

Set an Optimal Number of Extents for Each Rollback Segment

Set the Storage Location for Rollback

Creating Rollback Segments

Bringing New Rollback Segments Online

Specifying Storage Parameters for Rollback Segments

Setting Storage Parameters When Creating a Rollback Segment

Changing Rollback Segment Storage Parameters

Altering Rollback Segment Format

Shrinking a Rollback Segment Manually

Taking Rollback Segments Online and Offline

Bringing Rollback Segments Online

Taking Rollback Segments Offline

Explicitly Assigning a Transaction to a Rollback Segment

Dropping Rollback Segments

Monitoring Rollback Segment Information

Displaying Rollback Segment Information

19 Establishing Security Policies

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

20 Managing Users and Resources

Session and User Licensing

Concurrent Usage Licensing

Connecting Privileges

Setting the Maximum Number of Sessions

Setting the Session Warning Limit

Changing Concurrent Usage Limits While the Database is Running

Named User Limits

Viewing Licensing Limits and Current Values

User Authentication

Database Authentication

External Authentication

Enterprise Authentication

Managing Resources with Profiles

Creating Profiles

Assigning Profiles

Altering Profiles

Using Composite Limits

Dropping Profiles

Enabling and Disabling Resource Limits

Listing Information About Database Users and Profiles

Listing Information about Users and Profiles: Examples

21 Managing User Privileges and Roles

Identifying User Privileges

System Privileges

Object Privileges

Managing User Roles

Creating a Role

Predefined Roles

Role Authorization

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

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 Privilege and Role Information: Examples

22 Auditing Database Use

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

23 Archiving Redo Information

Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

Running a Database in NOARCHIVELOG Mode

Running a Database in ARCHIVELOG Mode

Turning Archiving On and Off

Setting the Initial Database Archiving Mode

Changing the Database Archiving Mode

Enabling Automatic Archiving

Disabling Automatic Archiving

Performing Manual Archiving

Tuning Archiving

Minimizing the Impact on System Performance

Improving Archiving Speed

Displaying Archiving Status Information

Specifying the Archived Redo Log Filename Format and Destination

A Space Estimations for Schema Objects

Estimating Space Required by Non-Clustered Tables

Estimating Space for Indexes

Estimating Space Required by Clusters

Estimating Space Required by Hash Clusters

Copyright © 1997 Oracle Corporation.
All Rights Reserved.