Oracle8 Administrator's Guide
Release 8.0







Title and Copyright Information

Send Us Your Comments


Part I Basic Database Administration

1 The Oracle Database Administrator

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
Using an Authentication Password File
Password File Administration
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
License Parameters
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
Altering a Job
Broken Jobs
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 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

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

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

21 Managing User Privileges and Roles

Identifying User Privileges
System Privileges
Object Privileges
Managing User Roles
Creating a Role
Predefined Roles
Role Authorization
Dropping 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

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

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.