Oracle8i Administrator's Guide

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

Release Number Format

Versions of Other Oracle Software

Checking Your Current Release Number

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

Parameters

DB_NAME and DB_DOMAIN

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

Starting Up a Database

Preparing to Start an Instance

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 Parameter Files

The Sample Parameter File

The Number of Parameter Files

The Location of the Parameter File in Distributed Environments

Setting Up Server Processes

When to Connect to a Dedicated Server Process

Configuring Oracle for Multi-Threaded Server Architecture

MTS_DISPATCHERS: Setting the Initial Number of Dispatchers (Required)

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

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

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

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

Setting Archive Buffer Parameters

Displaying Archived Redo Log Information

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

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

Using Multiple Tablespaces

Specifying Tablespace Storage Parameters

Assigning Tablespace Quotas to Users

Creating Tablespaces

Creating Locally Managed 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

Prerequisites

Making a Read-Only Tablespace Writeable

Creating a Read-Only Tablespace on a WORM Device

Dropping Tablespaces

Using the DBMS_SPACE_ADMIN Package

Transporting Tablespaces Between Databases

Introduction to Transportable Tablespaces

Current Limitations

Step 1: Pick a Self-contained Set of Tablespaces

Step 2: Generate a Transportable Tablespace Set

Step 3: Transport the Tablespace Set

Step 4: Plug In the Tablespace Set

Object Behaviors

Transporting and Attaching Partitions for Data Warehousing: Example

Publishing Structured Data on CDs

Mounting the Same Tablespace Read-only on Multiple Databases

Archive Historical Data via Transportable Tablespaces

Using Transportable Tablespaces to Perform TSPITR

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

Introduction

Using Database Resource Manager Packages

Using the DBMS_RESOURCE_MANAGER Package

The DBMS_RESOURCE_MANAGER_PRIVS Package

Using the DBMS_SESSION Package to Change a User's Resource Consumer Groups

Database Resource Manager Views

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

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

Maintaining Partitions

Moving Partitions

Adding Partitions

Dropping Partitions

Coalescing Partitions

Modifying Partition Default Attributes

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

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

Dropping Columns

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

Managing Views

Creating Views

Modifying a Join View

Replacing Views

Dropping Views

Managing Sequences

Creating Sequences

Altering Sequences

Initialization Parameters Affecting Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Dropping Synonyms

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 Indexes with NOLOGGING

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

Creating an Index Online

Creating a Function-Based Index

Re-creating an Existing Index

Creating a Key-Compressed Index

Altering Indexes

Monitoring Space Use of Indexes

Dropping Indexes

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

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

DBMS_REPAIR Package Contents

Step 1: Detect and Report Corruptions

DBMS_REPAIR: Using the check_object and admin_tables Procedures

DB_VERIFY: Performing an Offline Database Check

ANALYZE: Corruption Reporting

DB_BLOCK_CHECKING (Block Checking Initialization Parameter)

Step 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR

Step 3: Make Objects Usable

Corruption Repair: Using the fix_corrupt_blocks and skip_corrupt_blocks Procedures

Implications when Skipping Corrupt Blocks

Step 4: Repair Corruptions and Rebuild Lost Data

Recover Data Using the dump_orphan_keys Procedures

Repair Freelists Using the rebuild_freelists Procedure

Limitations and Restrictions

DBMS_REPAIR Procedures

DBMS_REPAIR Exceptions

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

Setting Integrity Constraints Upon Definition

Modifying 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

Oracle Dictionary Storage 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

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

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

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

Oracle Users

Creating Users

Altering Users

Dropping Users

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

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

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

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

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 Using the Database Resource Manager

12 Guidelines for Managing Schema Objects

13 Managing Partitioned Tables and Indexes

14 Managing Tables

15 Managing Views, Sequences and Synonyms

16 Managing Indexes

17 Managing Clusters

18 Managing Hash Clusters

19 Detecting and Repairing Data
Block Corruption

20 General Management of Schema Objects

21 Managing Rollback Segments

Part IV Database Security

22 Establishing Security Policies

23 Managing Users and Resources

24 Managing User Privileges and Roles

25 Auditing Database Use

Index