Oracle8i Administrator's Guide
Release 8.1.5







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

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

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

Part II Oracle Server Configuration

4 Managing Oracle Processes

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

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

6 Managing the Online Redo Log

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
Listing Information about the Online Redo Log

7 Managing Archived Redo Logs

What Is the Archived Redo Log?
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?
Creating a Dictionary File
Specifying Redo Logs for Analysis
Using LogMiner
Using LogMiner: Scenarios

8 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 Database Storage

9 Managing Tablespaces

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
Making a Read-Only Tablespace Writeable
Creating a Read-Only Tablespace on a WORM Device
Dropping Tablespaces
Using the DBMS_SPACE_ADMIN Package
Scenario 1
Scenario 2
Scenario 3
Scenario 4
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

10 Managing Datafiles

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

11 Using the Database Resource Manager

Using Database Resource Manager Packages
Using the DBMS_SESSION Package to Change a User's Resource Consumer Groups
Database Resource Manager Views

12 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

13 Managing Partitioned Tables and 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
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

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

15 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

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

17 Managing Clusters

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

18 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

19 Detecting and Repairing Data
Block Corruption

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

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

21 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

Part IV Database Security

22 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

23 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

24 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

25 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


Copyright © 1999 Oracle Corporation.

All Rights Reserved.