Oracle8i Administrator's Guide
Release 2 (8.1.6)







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 Your Oracle Database Software Release
Release Number Format
Checking Your Current Release Number

2 Creating an Oracle Database

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

3 Starting Up and Shutting Down

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

Part II Oracle Server Configuration

4 Managing Oracle Processes

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

5 Managing Control Files

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

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
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
Controlling Trace Output Generated by the Archivelog Process
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
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

Part III Database Storage

9 Managing Tablespaces

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
Scenario 1
Scenario 2
Scenario 3
Scenario 4
Scenario 5
Transporting Tablespaces Between Databases
Introduction to Transportable Tablespaces
Procedure for Transporting Tablespaces Between Databases
Object Behaviors
Using Transportable Tablespaces
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 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

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

Part IV Schema Objects

12 Guidelines for Managing Schema Objects

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

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

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

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

16 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

17 Managing Hash Clusters

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

18 Managing Views, Sequences and Synonyms

Managing Views
Creating Views
Updating a Join View
Altering Views
Dropping Views
Replacing Views
Managing Sequences
Creating Sequences
Altering Sequences
Dropping Sequences
Managing Synonyms
Creating Synonyms
Dropping Synonyms

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

20 Addressing Data Block Corruption

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

Part V Database Security

21 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

22 Managing Users and Resources

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

23 Managing User Privileges and Roles

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

24 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

Part VI Database Resource Management

25 The Database Resource Manager

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


Copyright © 1999 Oracle Corporation.

All Rights Reserved.