Expert Oracle 9i Database Administration

by Alapati, Sam R.

• ISBN13: 9781590590225
• ISBN10: 1590590228
• Format: Paperback
• Copyright: 2003-04-01
• Publisher: Springer-Verlag New York Inc

Summary

This is the only real Oracle9idatabase administration book in the market! This 1,000+ page book enables a beginner or intermediate level Oracle DBA or Oracle developer/manager to master the art of building and managing complex Oracle9idatabases.Expert Oracle9iDatabase Administrationis thorough in covering all aspects of the 9idatabase, and includes a UNIX primer as well as an introduction to SQL and PL/SQL. Author Sam Alapati covers the entire spectrum of the new Oracle9iRDBMS software and clearly explains how to use all of its powerful features.Currently there is no one book that includes the necessary UNIX administration, Windows NT management, and SQL backgrounds and the necessary database administration principles. This book fills that gap well, by providing all the necessary material in one comprehensive volume.It takes several courses as well as mastery of several Oracle manuals to become a proficient DBA. Readers will be able to become expert Oracle DBAs by using this book.Expert Oracle9iDatabase Administrationis designed to be the most complete Oracle9idatabase text available today.

Table of Contents

About the Author	xxxi
About the Technical Reviewer	xxxii
Acknowledgments	xxxiii
Introduction	xxiv
Part One Database Basics, Data Modeling, and UNIX/Linux	1	(104)
Chapter 1 The Oracle DBA's World	3	(22)
The Oracle DBA's Role	3	(7)
The DBA's Security Role	4	(2)
The DBA's System Management Role	6	(2)
The DBA's Database Design Role	8	(2)
Different DBA Job Classifications	10	(1)
Types of Databases	10	(2)
Online Transaction Processing and
Decision Support System Databases	11	(1)
Development, Test, and Production Databases	11	(1)
Background and Training	12	(9)
Background and Training for an Oracle DBA	12	(3)
Certification	15	(1)
Resources and Organizations for Oracle DBAs	16	(2)
Managing High-Performance Databases	18	(1)
Improving Your Troubleshooting Skills	19	(2)
The Daily Routine of a Typical Oracle DBA	21	(1)
Some General Advice	21	(2)
Know When You Need Help	22	(1)
Remember You Are Not Alone	22	(1)
Think Outside the Box	22	(1)
Summary	23	(2)
Chapter 2 Relational Database Modeling and Database Design	25	(30)
Relational Databases: A Brief Introduction	26	(1)
The Relational Database Model	26	(4)
Database schemas	27	(1)
Data Definition and Data Manipulation Languages	27	(1)
Relational Algebra	28	(1)
Relational Calculus	29	(1)
SQL and Relational Theory	29	(1)
Relational Database Life Cycle	30	(1)
Requirements Gathering and Analysis	30	(1)
Logical Database Design	31	(14)
Entity-Relationship Modeling	32	(6)
Normalization	38	(2)
The Normal Forms	40	(4)
ERM Tools: The Oracle Designer	44	(1)
Physical Database Design	45	(3)
Transformation of Entities and Relationships	46	(1)
Designing Different Types of Tables	47	(1)
Table Structures and Naming Conventions	47	(1)
Column Specifications and Choosing Data Types	47	(1)
Business Rules and Data Integrity	47	(1)
Implementation of the Physical Design	48	(1)
Database Sizing and Database Storage	48	(1)
Implementing Database Security	48	(1)
Moving to the New System	49	(1)
Reverse Engineering a Database	49	(1)
Object-Relational and Object Databases	49	(4)
The Relational Model	50	(1)
The Object Model	50	(1)
The Object-Relational Model	51	(2)
Summary	53	(2)
Chapter 3 Essential UNIX (and Linux) for the Oracle DBA	55	(50)
Overview of the UNIX Operating System	55	(2)
Understanding the UNIX Shell(s)	57	(3)
Accessing the UNIX System	58	(1)
Logging Into and Logging Out of UNIX	58	(2)
Overview of Basic UNIX Commands	60	(3)
Introducing the UNIX Environment	63	(2)
Displaying the Environment	64	(1)
Working with Shell Variables	65	(1)
Modifying the Environment	65	(1)
Input and Output in UNIX	65	(1)
Navigating Files and Directories in UNIX	66	(8)
Files in the UNIX System	67	(1)
Types of Files	67	(1)
Linking Files	67	(1)
Managing Files	68	(1)
Permissions: Reading from or Writing to Files in UNIX	69	(3)
UNIX Directory Structure	72	(1)
Directory Management	72	(1)
Important UNIX Directories	73	(1)
Writing Files with the vi Editor	74	(2)
Creating and Modifying Files Using vi	74	(1)
Using the Head and Tail Commands	75	(1)
Shell Scripting	76	(3)
Creating a Simple UNIX Shell Program	76	(1)
Using Shell Variables	76	(3)
Control Flow Structures in Korn Shell Programming	79	(3)
Looping	79	(2)
Conditional Branching	81	(1)
Dealing with UNIX Processes	82	(2)
Running Programs in the Background with Nohup	83	(1)
Terminating Processes with the Kill Command	83	(1)
UNIX System Administration and the Oracle DBA	84	(8)
UNIX Backup and Restore Utilities	84	(1)
The Crontab and Automation of Scripts	85	(1)
Using Telnet	86	(1)
Remote Login and Remote Copy	87	(1)
Using ssh, the Secure Shell	87	(1)
Using FTP to Send and Receive Files	87	(1)
UNIX System Performance Monitoring Tools	88	(4)
Disks and Storage in UNIX	92	(3)
Disk Storage Configuration Choices	92	(1)
Monitoring Disk Usage	93	(1)
Disk Storage, Performance, and Availability	94	(1)
RAID Systems	95	(6)
RAID Levels	96	(2)
Choosing the Ideal Disk Configuration	98	(2)
Redundant Disk Controllers	100	(1)
RAID and Backups	100	(1)
RAID and Oracle	101	(1)
Storage Technologies	101	(3)
Storage Area Networks	102	(1)
Networked Attached Storage	102	(1)
InfiniBand	103	(1)
Oracle and Storage System Compatibility	103	(1)
Summary	104	(1)
Part Two The Oracle	105	(156)
Chapter 4 Installing the Oracle9i RDBMS	107	(32)
Installing Oracle on UNIX Systems	107	(3)
Reviewing the Documentation	108	(1)
Determining Disk and Memory Requirements	109	(1)
Following the Optimal Flexible Architecture	110	(5)
Mount Points	112	(1)
Directory and File Naming Conventions	112	(3)
Performing Preinstallation Tasks	115	(7)
UNIX System Administrator's Tasks	116	(4)
Oracle Owner's Tasks	120	(2)
A Final Checklist for the Installation	122	(1)
Installing the Oracle Software	123	(11)
After the Installation	134	(2)
UNIX Post-Installation Tasks	134	(1)
User Oracle Post-Installation Tasks	135	(1)
Uninstalling Oracle	136	(1)
Summary	137	(2)
Chapter 5 Introduction to the Oracle9i Architecture	139	(58)
Oracle Database Structures	139	(20)
The Logical Structures	140	(6)
Physical Database Structures	146	(6)
Trace Files	152	(1)
Data Files and Tablespaces	152	(5)
Automatic Undo Management	157	(2)
Oracle Managed Files	159	(1)
Oracle Processes	159	(7)
Interaction Between the User and Oracle Processes	160	(1)
The Server Process	160	(1)
The Background Processes	161	(5)
Oracle Memory Structures	166	(12)
Oracle System Global Area (SGA)	167	(8)
The Program Global Area (PGA)	175	(3)
Monitoring Memory Allocation	178	(7)
Dynamically Modifying the SGA	181	(2)
Using Oracle's SGA and PGA Advisories	183	(2)
Managing Memory Parameters with Oracle Enterprise Manager	185	(2)
A Simple Oracle Database Transaction	187	(2)
Data Consistency and Data Concurrency	189	(3)
The Database Writer and the Write Ahead Protocol	190	(1)
The System Change Number	190	(1)
The Central Role of the Oracle Control File	191	(1)
The Oracle Data Dictionary and the Dynamic Performance Views	192	(3)
The Oracle Data Dictionary	192	(1)
The Dynamic Performance (V$) Views	193	(1)
The Oracle Optimizer	194	(1)
Oracle Enterprise Manager	195	(1)
Summary	195	(2)
Chapter 6 Using SQL*Plus and iSQL*Plus	197	(40)
SQL*Plus for the DBA	197	(12)
Using SQL*Plus in Interactive and Noninteractive Modes	198	(1)
SQL*Plus Commands and SQL Commands	198	(1)
SQL*Plus Security	199	(3)
Setting the SQL*Plus Environment with the Set Command	202	(1)
Setting Common SQL*Plus Variables	203	(3)
SQL*Plus Command Line Arguments	206	(1)
Removing Settings with the Clear Command	207	(1)
The Store Command	208	(1)
The Show Command	208	(1)
Exiting SQL*Plus	208	(1)
Key SOL*Plus "Performing" Commands	209	(9)
The Sqlprompt Command	209	(1)
The Describe Command	209	(1)
The Host Command	210	(1)
The Spool Command	210	(1)
The Accept and Prompt Commands	211	(1)
The Execute Command	211	(1)
The Pause Command	211	(1)
Creating Command Files in SQL*Plus	212	(1)
Saving SQL Commands to a File	212	(1)
Executing SQL*Plus Scripts	213	(1)
Executing a SQL*Plus Command Script	213	(2)
Creating a Windows Batch Script	215	(1)
The Define and Undefine Commands	216	(1)
Using Comments in SQL*Plus	217	(1)
Listing Your SQL Commands	217	(1)
Editing Within SQL*plus	218	(5)
Inserting and Deleting Lines in SQL*Plus	219	(2)
Adding to Text	221	(1)
Incorporating Comments with the Remark Command	221	(1)
Copying Tables with the Copy Command	221	(1)
Making DML Changes Permanent with SQL*Plus	222	(1)
Creating Web Pages Using SQL*Plus	222	(1)
Key SOL*Plus Database Administration Commands	223	(1)
The Recover Command	223	(1)
The Start and Shutdown Commands	223	(1)
The Archivelog Command	223	(1)
Commands for Formatting SQL*Plus Output and Creating Reports	224	(2)
The Break Command	224	(1)
The Column Command	224	(1)
The Compute Command	225	(1)
The Repfooter Command	225	(1)
The Repheader Command	225	(1)
The Btitle and Ttitle Commands	225	(1)
Using SQL to Generate SOL	226	(1)
Introducing the Oraclegi iSQL*Plus Interface	227	(9)
Installing the iSQL*Plus Software	228	(1)
Configuring iSQL*Plus	228	(2)
Using the iSQL*Plus Interface	230	(3)
Authentication Levels	233	(2)
Executing Statements	235	(1)
A Security Warning for iSQL*Plus	236	(1)
Summary	236	(1)
Chapter 7 Schema Management in Oracle9i	237	(72)
Types of SQL Statements in Oracle	237	(4)
System Control Statements	238	(1)
Session Control Statements	239	(1)
Data Manipulation Statements	239	(1)
Transaction Control Statements	240	(1)
Data Definition Statements	240	(1)
Oracle Schema Management	241	(1)
Creating and Managing Tablespaces	242	(19)
Locally and Dictionary Managed Tablespaces	243	(1)
Creating Locally Managed Tablespaces	244	(12)
Creating a Simple Table	256	(5)
Special Oracle Tables	261	(16)
Temporary Tables	261	(2)
Index-Organized Tables	263	(2)
External Tables	265	(2)
Partitioned Tables	267	(7)
Data Dictionary Views for Managing Tables	274	(3)
Clusters	277	(1)
Hash Clusters	277	(1)
Oracle Indexes	277	(8)
Guidelines for Creating Indexes	279	(1)
Oracle Index Schemes	280	(1)
Creating a Simple Index	281	(1)
Bitmap Indexes	281	(1)
Reverse Key Indexes	282	(1)
Function-Based Indexes	282	(1)
Partitioned Indexes	282	(2)
Monitoring Index Usage	284	(1)
Index Maintenance	285	(1)
Database Integrity Constraint Management	285	(6)
Primary Key Constraints	286	(1)
Not Null Constraints	287	(1)
Check Constraints	287	(1)
Unique Constraints	287	(1)
Referential Integrity Constraints	288	(1)
Integrity Constraint States	288	(1)
Disabling Integrity Constraints	289	(1)
Rely Constraints	290	(1)
Deferrable and Immediate Constraints	290	(1)
Using Synonyms	291	(3)
Creating a Public Synonym	292	(1)
Creating a Private Synonym	293	(1)
Viewing Information on Synonyms	294	(1)
Using Sequences	294	(2)
Using Triggers	296	(1)
Using Views	296	(1)
Using Materialized Views	297	(10)
Using the Summary Advisor	298	(5)
Creating Materialized Views	303	(4)
Summary	307	(2)
Chapter 8 Oracle Transaction Management	309
Transactions	310	(1)
Anatomy of a SQL Transaction	311	(1)
Parsing	311	(1)
Binding	312	(1)
Execution	312	(1)
Transaction Properties	312	(1)
Transaction Concurrency Control	313	(5)
Concurrency Problems	314	(1)
Schedules and Serializability	315	(1)
The ISO Transaction Standard	315	(1)
Oracle Transaction Management	316	(2)
Oracle's Isolation Levels	318	(4)
Changing the Default Isolation Level	319	(1)
The Read-Only Isolation Level	320	(1)
The Serializable Isolation Level	320	(1)
The Read Committed vs. the Serializable Level of Isolation	320	(2)
Implementing Oracle's Concurrency Control	322	(10)
Oracle Locking Methods	323	(1)
Oracle Lock Types	324	(1)
Oracle Lock Modes	325	(3)
Explicit Locking in Oracle	328	(2)
Managing Oracle Locks	330	(2)
Using Undo Records to Provide Read Consistency	332	(8)
Automatic Undo Management	333	(7)
Querying Old Data with Oracle Flashback	340	(6)
Implementing Flashback Query	342	(4)
Discrete Transactions	346	(1)
Autonomous Transactions	346	(3)
Resumable Storage Allocation	349	(3)
Resumable Operations	349	(1)
Common Resumable Errors	350	(1)
Using the Resumable Space Allocation Feature	350	(1)
Notification of Suspended Operations	351	(1)
Monitoring Resumable Space Allocation	352	(1)
Managing Long Transactions	352	(8)
Benefits of Using the Workspace Manager	353	(1)
Table Versioning and Workspaces	353	(6)
Managing the Workspace Manager	359	(1)
Summary	360
Part Three Database Creation, Connectivity,and User Management	261	(270)
Chapter 9 Creating an Oracle Database	363	(62)
Getting Ready to Create the Database	364	(27)
Installing the Software	364	(1)
Creating the File System for the Database	365	(2)
Ensuring Enough Memory Is Allocated	367	(1)
Getting Necessary Authorizations	367	(1)
Setting the Operating System Environment Variables	367	(1)
Creating the Initialization File	368	(2)
Important Oracle9i Initialization Parameters	370	(13)
Rollback Segment Parameters	383	(8)
Undocumented Initialization Parameters	391	(1)
Viewing the Current Initialization Parameter Values	391	(2)
Reading the Init.ora File (or the SPFILE)	392	(1)
The V$PARAMETER Table	392	(1)
The Show Parameter Command	392	(1)
Creating the Database	393	(16)
Setting Operating System Variables	394	(1)
EnsuringYou Have the Privileges to Create Databases	394	(1)
Creating the Init.ora File	395	(1)
Starting the Oracle Instance	396	(3)
The Create Database Script	399	(4)
Running Oracle Scripts to Create Data Dictionary Objects	403	(1)
Creating Additional Tablespaces	404	(2)
Changing the Passwords for the Default Users	406	(1)
Changing the Archive Logging Mode	406	(2)
Running the Pupbld.sql File	408	(1)
What Next?	409	(1)
The Server Parameter File (SPFILE)	409	(5)
Creating a Server Parameter File	410	(3)
Scope of Dynamic Parameter Changes	413	(1)
Starting and Shutting Down the Database	414	(10)
Starting the Database	414	(3)
Restricting Database Access	417	(1)
Shutting Down the Database	418	(3)
Quiescing a Database	421	(2)
Suspending a Database	423	(1)
Using V$VIEWS to Monitor Database Status	423	(1)
Summary	424	(1)
Chapter 10 Connectivity and Networking	425	(44)
Oracle Networking and Database Connectivity	426	(1)
Networking Concepts: How Oracle Networking Works	427	(4)
Database Instance Name	428	(1)
Database Service Name	428	(1)
Connect Descriptors	429	(1)
Connect Identifiers	429	(1)
Connect Strings	430	(1)
The Listener and Connectivity	431	(4)
Automatic Service Registration	432	(1)
Listener Commands	433	(1)
Listener Management	434	(1)
Naming and Connectivity	435	(26)
Local Naming	436	(3)
Host Naming Method	439	(1)
External Naming Method	439	(1)
Oracle Names	440	(1)
The Directory Naming Method	440	(21)
Oracle and Java Database Connectivity	461	(7)
Establishing Database Connectivity	461	(1)
Working with the Database	462	(3)
A Complete Program	465	(1)
Connectivity Troubleshooting	466	(2)
Summary	468	(1)
Chapter 11 User Management and Database Security	469	(62)
Managing Users	470	(7)
Preliminary Tasks Before Creating Users	470	(1)
Creating a New User	471	(1)
User Profiles and Resource Management	472	(1)
Profile Parameters and Limits	473	(4)
Using the Database Resource Manager	477	(9)
Using the Database Resource Manager	478	(5)
Creating Resource Plans and Plan Directives	483	(2)
Enabling the Database Resource Manager	485	(1)
Managing Access to Data	486	(7)
Privileges in an Oracle Database	486	(4)
Roles and Privileges	490	(3)
Using Views and Stored Procedures to Manage Privileges	493	(1)
Fine-grained Data Security	493	(8)
Using Virtual Private Databases	494	(6)
Label-Based Access Control	500	(1)
Auditing Database Usage	501	(10)
Customizing Database Auditing with the Help of Triggers	501	(4)
Enabling Auditing	505	(1)
Oracle Default Auditing	506	(2)
Audit Levels	508	(1)
Turning Auditing On	508	(1)
Turning Auditing Off	509	(1)
Using Fine-grained Auditing	509	(2)
Managing the Audit Trail	511	(1)
User Authentication	511	(6)
Database Authentication of Users	511	(5)
External Authentication	516	(1)
Proxy Authentication	517	(1)
Centralized User Authorization	517	(1)
Enterprise User Security	517	(2)
Shared Schemas	518	(1)
Single Sign-On	518	(1)
Data Encryption	518	(1)
Using Oracle Internet Directory for Security Management	518	(1)
Database Security Do's and Don'ts	519	(5)
User Accounts	519	(1)
Passwords	519	(1)
Operating System Authentication	520	(1)
Audit Your Database	520	(1)
Grant Privileges Appropriately	520	(1)
Set Appropriate Permissions	521	(1)
Safeguard the Network and the Listener	522	(1)
Keep Up-to-Date	523	(1)
Use Oracle's Advanced Security Feature	523	(1)
Take Care of Application Security	524	(1)
Useful Techniques for Managing Users	524	(5)
How to Alter a Profile	524	(1)
How to List User Information	525	(1)
How to Find Out the Memory Use for Each Active Session	525	(1)
What SQL Is a User Currently Executing?	526	(1)
How to Log In As a Different User	526	(1)
Useful DBA Views to Monitor User Roles and Privileges	527	(1)
How to Kill a User's Session	527	(2)
Summary	529	(2)
Part Four Data Loading, Backup, and Recovery	531	(190)
Chapter 12 Loading and Transforming Data	533	(46)
Introduction to Data Loading and Transformation	533	(3)
Loading Data with ETL	534	(1)
Loading Data with the Oracle9iWarehouse Builder	535	(1)
The SOL*Loader Utility	536	(19)
Different Methods of Data Loading	536	(1)
The SQL*Loader Control File	537	(10)
Generating Data During the Load	547	(1)
Invoking SQL*Loader	548	(7)
Using External Tables to Load Data	555	(7)
Using External Tables for Data Loading	556	(2)
Important Access Parameters for External Tables	558	(1)
Using SQL*Loader to Generate External Table Creation Statements	559	(3)
Transforming Data	562	(11)
Deriving the Data from Existing Tables	563	(1)
Using PL/SQL to Transform Data	564	(1)
Using SQL to Transform Data	564	(9)
Some Useful SOL*Loader Data-Loading Techniques	573	(4)
Using the When Clause During Loads	573	(1)
Loading the Username into a Table	574	(1)
Loading Large Data Fields into a Table	574	(1)
Loading a Sequence Number into a Table	575	(1)
Loading Data from a Table into an ASCII File	575	(1)
Identifying and Removing Duplicate Data	575	(1)
Dropping Indexes Before Bulk Data Loads	576	(1)
Loading into Multiple Tables	576	(1)
Trapping Error Codes from SQL*Loader	576	(1)
Loading XML Data into an Oracle XML Database	577	(1)
Summary	577	(2)
Chapter 13 Using the Export and Import Utilities	579	(32)
Exporting and Importing Data	579	(1)
Using the Export Utility	580	(10)
Export Parameters	581	(6)
Invoking the Export Utility	587	(3)
Using the Import Utility	590	(9)
The Order of Importing	590	(1)
Import Parameters	590	(8)
Invoking the Import Utility	598	(1)
Transportable Tables paces	599	(5)
Uses of Transportable Tablespaces	600	(1)
Transporting a Tablespace	600	(4)
Some Export and Import Techniques	604	(6)
Avoiding Common Export/Import Errors	605	(1)
Using the Statistics Parameter Appropriately	605	(1)
Understanding the Limitations of the Export and Import Utilities	606	(1)
Performing an Efficient Database Migration	606	(1)
Restoring a Database Using the Export and Import Utilities	607	(1)
Exporting and Importing Large Databases	607	(1)
UNIX Shell Script to Automate Exports	608	(1)
Ensuring the Validity of the Exports	608	(1)
Optimizing Exports and Imports	609	(1)
Summary	610	(1)
Chapter 14 Backing Up Databases	611	(62)
Backing Up Oracle Databases	611	(8)
Important Backup Terms	612	(2)
Backup Strategies	614	(5)
Backing Up the "Other" Files	619	(1)
A Summary of Backup Methods	619	(1)
Oracle Recovery Manager	619	(28)
Benefits of RMAN	620	(1)
RMAN Architecture	621	(1)
Connecting to RMAN	622	(2)
Scripting with RMAN	624	(1)
Important RMAN Terms	625	(2)
RMAN Commands	627	(4)
Configuring RMAN	631	(6)
Creating the Recovery Catalog	637	(5)
Examples of Various Backups Using RMAN	642	(3)
Performing Incremental Backups	645	(1)
Using RMAN to Perform Online Backups	646	(1)
Using a Media Management Layer with RMAN	647	(8)
Using Legato Single Server Version with RMAN	648	(1)
The LSSV Architecture	649	(1)
Installing LSSV	650	(1)
Using LSSV	651	(1)
LSSV and RMAN	652	(3)
Creating Backups with Operating System Commands	655	(7)
Making Whole Database Backups	656	(5)
Partial Database Backups	661	(1)
Monitoring User-Managed Online Backups	662	(1)
Enhanced Data Protection for Disaster Recovery	662	(4)
Oracle Data Guard and Standby Databases	663	(1)
Physical and Logical Standby Databases	664	(1)
Protection Modes	665	(1)
Database Corruption and Testing Backups	666	(4)
Oracle Data Block Corruption	666	(1)
Detecting Data Block Corruption	667	(2)
Verification of RMAN Backups	669	(1)
Oracle's HARD Initiative	670	(1)
Techniques and Troubleshooting	670	(1)
Monitoring RMAN	670	(1)
The ORA_00257 Error	671	(1)
Summary	671	(2)
Chapter 15 Database Recovery	673	(48)
Types of Database Failures	673	(4)
System Failure	673	(1)
Fast-Start Fault Recovery	674	(1)
Data Center Disasters	675	(1)
Human Error	675	(1)
Media Failures	676	(1)
Extent of Database Failure and Types of Recovery	677	(2)
Restoring vs. Recovering a Database	677	(1)
Time Needed for Recovery	678	(1)
Complete and Incomplete Recovery	679	(1)
Open and Closed Recovery	679	(1)
Reducing Your Vulnerability	679	(1)
Performing Recovery with RMAN	680	(3)
Why RMAN Is Best for Recovery	680	(2)
User-Managed Recovery of a Database	682	(1)
Typical Media Recovery Scenarios	683	(15)
Complete Recovery of a Whole Database	683	(3)
Recovering a Tablespace	686	(2)
Recovering a Data File	688	(2)
Incomplete Recovery	690	(3)
Recovering from the Loss of Control Files	693	(4)
Recovering a Data File Without a Backup	697	(1)
Cloning a Database	698	(5)
Using RMAN	698	(4)
Cloning a Database Manually	702	(1)
Techniques for Granular Recovery	703	(11)
Tablespace Point-in-Time Recovery	704	(1)
Using RMAN for TSPITR	704	(1)
Using LogMiner for Precision Recovery	705	(6)
Using Flashback Query for Recovery	711	(3)
Repairing Data Corruption and Trial Recovery	714	(2)
Block Media Recovery	714	(1)
Trial Recovery	715	(1)
Troubleshooting Recovery	716	(3)
The ORA-01194 Error	716	(1)
The ORA-01152 Error	717	(1)
The ORA-00376 Error	718	(1)
Summary	719	(2)
Part Five Managing the Operational Oracle Database	721	(98)
Chapter 16 Managing the Operational Database	723	(48)
Oracle's Automatic Management Features	723	(13)
Fully Locally Managed Databases	724	(2)
Automatic Undo Management	726	(4)
Resumable Space Allocation	730	(4)
Automating and Monitoring Recovery Time	734	(2)
Easy File Management with OMF	736	(6)
Benefits of Using OMF	737	(1)
Creating Oracle Managed Files	737	(5)
Online Capabilities of Oracle9i	742	(18)
Online Data Reorganization	742	(3)
Online Table Redefinition	745	(6)
Dynamic Resource Management	751	(2)
Online Database Block Changes	753	(2)
Dynamic Memory Reconfiguration	755	(4)
Using Database Quiescing for Online Maintenance	759	(1)
Suspending the Database	760	(1)
Managing the Online Redo Logs	760	(6)
Managing the Archived Logs	764	(1)
Managing a Database Link	764	(2)
Mapping Oracle Files to Physical Devices	766	(2)
Architecture of File Mapping	766	(1)
Setting Up File Mapping	767	(1)
Tips and Troubleshooting	768	(1)
Problems During Quiescing a Database	768	(1)
Failed Online Table Redefinitions	768	(1)
Problems During Online Index Rebuilds	769	(1)
Summary	769	(2)
Chapter 17 Using Oracle9i Enterprise Manager	771	(48)
Oracle Enterprise Manager	771	(7)
Benefits of Using OEM to Manage Databases	773	(2)
The Various Systems of OEM	775	(1)
The OEM Architecture and Components	776	(2)
Configuring OEM	778	(14)
Configuring the Intelligent Agent	778	(2)
Configuring the Management Server	780	(5)
Configuring the OEM Console	785	(3)
Configuring Reporting	788	(3)
Running the Console from a Web Browser	791	(1)
Using the OEM Console	792	(1)
Discovering Services	792	(1)
The Services.ora File	792	(1)
Database Management Through OEM	793	(6)
Database Instance Management	794	(1)
Database Schema Management	795	(1)
Database Security Management	796	(1)
Database Storage Management	796	(1)
Distributed Database Management	797	(2)
Using the OEM Wizards	799	(4)
The Export and Import Wizards	800	(1)
The Analyze Wizard	801	(1)
The Backup Wizard	802	(1)
Using the OEM Reporting Features	803	(3)
Prebuilt and Custom Reports	804	(1)
Accessing the Reporting Web Site	804	(2)
Managing Jobs with OEM	806	(4)
The Job Detail View	807	(1)
Creating a Job	807	(3)
Managing Events with OEM	810	(2)
Creating Events	810	(1)
Specifying the Test Conditions	810	(1)
Selecting the Test Criteria	811	(1)
Scheduling the Event	812	(1)
Configuring E-mail and Paging Notification	812	(2)
Configuring Paging Notification	812	(2)
Configuring E-mail Notification	814	(1)
Oracle Management Packs	814	(3)
Oracle Diagnostics Pack	814	(3)
Oracle Tuning Pack	817	(1)
Oracle Change Management Pack	817	(1)
Troubleshooting OEM	817	(1)
Summary	818	(1)
Part Six Performance Tuning and Troubleshooting the Production Database	819
Chapter 18 Improving Database Performance: SOL Query Optimization	821	(66)
An Approach to Oracle Performance Tuning	821	(3)
A Systematic Approach to Performance Tuning	822	(1)
Reactive Performance Tuning	822	(1)
SQL Coding Practices	823	(1)
Optimizing Oracle Query Processing	824	(4)
Parsing	824	(1)
Optimization	824	(4)
Query Execution	828	(1)
Query Optimization and the Oracle Cost-Based Optimizer	828	(11)
Choosing Your Optimization Mode	829	(1)
Providing Statistics About the Objects to the Optimizer	829	(1)
Setting the Optimizer Mode	830	(1)
Setting the Optimizer Level	831	(1)
What Does the Optimizer Do?	831	(2)
Drawbacks of the CBO	833	(1)
Providing Statistics to the CBO	834	(5)
Writing Efficient SQL	839	(14)
Efficient Where Clauses	839	(3)
Using Hints to Influence the Execution Plan	842	(1)
Selecting the Best Join Method	843	(1)
Using Bitmap Join Indexes	843	(2)
Selecting the Best Join Order	845	(1)
Indexing Strategy	845	(4)
Using the OEM Index Tuning Wizard	849	(1)
Monitoring Index Utilization	850	(1)
Removing Unnecessary Indexes	851	(1)
Using Similar SQL Statements	851	(1)
Reducing SQL Overhead via "Inline" Functions	851	(1)
Using Bind Variables	852	(1)
Avoiding Improper Use of Views	853	(1)
Avoiding Unnecessary Full Table Scans	853	(1)
How the DBA Can Help Improve SQL Processing	853	(7)
Using Partitioned Tables	854	(1)
Using Compression Techniques	854	(1)
Using Materialized Views	855	(1)
Using Stored Outlines to Stabilize the CBO	855	(4)
Using Parallel Execution	859	(1)
Other DBA Tasks	859	(1)
SQL Performance Tuning Tools	860	(24)
Using the Explain Plan	861	(4)
Using Autotrace	865	(5)
Using SQL Trace and TKPROF	870	(7)
Using V$SQLAREA to Find Inefficient SQL	877	(2)
Using GUI Tools in Tuning	879	(5)
A Simple Approach to Tuning SQL Statements	884	(2)
Identify Problem Statements	884	(1)
Locate the Source of the Inefficiency	885	(1)
Tune the Statement	885	(1)
Compare Performance	885	(1)
Summary	886	(1)
Chapter 19 Performance Tuning: Tuning the Instance	887	(86)
An Introduction to Instance Tuning	887	(3)
Tuning Oracle Memory	890	(22)
Tuning the Shared Pool	892	(14)
Tuning the Buffer Cache	906	(5)
Tuning the Large Pool	911	(1)
Tuning PGA Memory	911	(1)
Automatic PGA Memory Management	911	(1)
Evaluating System Performance	912	(8)
CPU Performance	912	(7)
Disk I/O	919	(1)
Measuring I/0 Performance	920	(2)
Is the I/O Optimally Distributed?	921	(1)
Reducing Disk Contention	922	(1)
The Oracle SAME Guidelines for Optimal Disk Usage	922	(1)
Collecting Instance Performance Statistics with Statspack	922	(14)
Installing Statspack	923	(2)
Using Statspack	925	(11)
Measuring Instance Performance	936	(18)
Database Hit Ratios	937	(1)
Database Wait Statistics	938	(14)
Know Your Application	952	(2)
Operating System Memory Management	954	(1)
When a Database Hangs	954	(9)
Handling a Stuck Archiver Process	954	(2)
System Utilization Problems	956	(1)
Excessive Contention for Resources	957	(1)
Locking Issues	957	(1)
Abnormal Increase in Process Size	958	(2)
Delays Due to Shared Pool Problems	960	(1)
Problems Due to Bad Statistics	960	(1)
Collecting Information During a Database Hang	960	(3)
A Simple Approach to Instance Tuning	963	(9)
What's Happening in the Database?	963	(1)
Are There Any Long-Running Transactions?	964	(1)
Is Oracle the Problem?	965	(1)
Is the Network Okay?	965	(1)
Is the System CPU Bound?	966	(2)
Is the System I/O Bound?	968	(1)
Checking Memory-Related Issues	969	(1)
Is the System Wait Bound?	969	(1)
Eliminating the Contention	970	(2)
Summary	972	(1)
Chapter 20 The Oracle Data Dictionary and the Dynamic Performance Views	973	(56)
The Oracle Data Dictionary	973	(2)
How Is the Data Dictionary Created?	974	(1)
Using the Data Dictionary Static Views	975	(28)
General Views	975	(4)
User Management-Related Views	979	(8)
Audit-Related Views	987	(2)
Storage-Related Views	989	(4)
Views for Monitoring Transactions	993	(2)
Constraint- and Index-Related Views	995	(4)
Views for Managing Database Objects	999	(1)
Views for Managing Tables and Views	999	(4)
Using the Dynamic Performance Tables	1003	(24)
Memory-Related Views	1004	(4)
Backup-Related Views	1008	(2)
Session- and User-Related Views	1010	(2)
Redo Log- and Archive Log-Related Views	1012	(3)
Recovery-Related Views	1015	(1)
Performance Monitoring Views	1016	(2)
SQL-Related Views	1018	(1)
Operational Performance-Related Views	1018	(4)
General Views	1022	(4)
Storage-and File-Related Views	1026	(1)
Summary	1027	(2)
Chapter 21 Using Oracle PL/SOL Packages	1029	(48)
Overview of the Oracle-Supplied PL/SQL Packages	1029	(2)
Who Creates the Oracle Packages?	1029	(1)
How Do You Use the Oracle Packages?	1030	(1)
DBMS_JOB	1031	(2)
Using the DBMSJOB Package	1032	(1)
Other DBMSJOB Procedures	1033	(1)
DBMS_APPLICATION INFO	1033	(2)
DBMS_SESSION	1035	(2)
Set_Role	1036	(1)
Set_Sql_Trace	1036	(1)
Switch_Current_Consumer_Group	1037	(1)
DBMS_SYSTEM	1037	(3)
Set_Sql_Trace_In_Session	1039	(1)
Set_Int_Param_In_Session	1039	(1)
Set_Bool_Param_In_Session	1039	(1)
Set_Ev	1040	(1)
DBMS_OUTPUT	1040	(2)
DBMS_REPAIR	1042	(3)
Using the DBMS_REPAIR Package	1043	(1)
Fixing Block Corruption	1044	(1)
DBMS_OUTLN and DBMS_OUTLN_EDIT	1045	(2)
Using the DBMS_OUTLN Package to Manage Stored Outlines	1045	(1)
Tracking the Outlines in the Database	1046	(1)
DBMS_SPACE	1047	(2)
DBMS_SPACE ADMIN	1049	(1)
DBMS_ PROFILER	1050	(3)
A Useful Package for Developers	1050	(1)
Installing the DBMS PROFILER Package	1051	(1)
Using the DBMS PROFILER Package	1051	(2)
UTL_FILE	1053	(4)
Using the UTL_FILE Package	1054	(2)
A Simple Example Using the UTL_FILE Package	1056	(1)
UTL_SMTP	1057	(3)
Procedures in the UTL_SMTP Package	1057	(3)
Using the UTL_SMTP Package	1060	(1)
DBMS_SHARED_POOL	1060	(2)
DBMS_LOGSTDBY	1062	(1)
Oracle Packages in Earlier Chapters	1063	(13)
The DBMS_STATS Package	1063	(1)
DBMS_RLS	1064	(1)
DBMS_FGA	1065	(1)
DBMS_RESOURCE_MANAGER	1065	(1)
DBMS_RESUMABLE	1066	(1)
DBMS_OLAP	1066	(2)
DBMS_MVIEW	1068	(1)
DBMS_METADATA	1069	(1)
DBMS_REDEFINITION	1069	(1)
DBMS_FLASHBACK	1070	(1)
DBMS_WM	1070	(1)
DBMS_LOGMNR	1071	(1)
DBMS_REFRESH	1072	(1)
DBMS_TTS	1073	(1)
DBMS_UTILITY	1074	(1)
DBMS_STORAGE_MAP	1075	(1)
Summary	1076	(1)
Chapter 22 Managing Oracle Databases on Windows and Linux Systems	1077	(28)
Oracle9i and Windows	1077	(3)
Database Access Methods	1078	(1)
Oracle Support for.NET	1078	(1)
Integration with Windows Services	1079	(1)
Essential Differences in Managing Oracle on Windows and UNIX	1080	(8)
Processes vs. Threads	1080	(3)
Oracle Service Threads	1083	(1)
Services and Daemons	1084	(1)
Tuning Memory Resources	1085	(1)
CPU and Memory Considerations	1086	(1)
Automatic Start-up and Shutdown	1086	(1)
Users and Groups	1086	(1)
The File Systems	1087	(1)
The Windows Copy Utilities	1087	(1)
Differences in the Use of the GUI	1087	(1)
Automating Jobs	1087	(1)
Diagnostic Tools	1088	(1)
Installing Oracle9i on a Windows System	1088	(1)
The Windows Registry	1089	(1)
Using the Registry	1089	(1)
Managing Oracle on Windows Systems	1090	(11)
Oracle Services	1090	(4)
Starting Up and Shutting Down the Oracle Database	1094	(3)
Using the Oracle Administration Assistant for NT	1097	(3)
Uninstalling Oracle on Windows	1100	(1)
Oracle9i and Linux	1101	(2)
Oracle's Commitment to Linux	1102	(1)
Ensuring You Have a Stable Version of Linux	1102	(1)
Managing Oracle on Linux	1103	(1)
Summary	1103	(2)
Appendix A Brief Oracle9i SQL and PL/SQL Primer	1105
The Oracle9i Sample Schemas	1106	(1)
Basic SQL	1107	(4)
The Select Statement	1107	(2)
The Insert Statement	1109	(1)
The Delete Statement	1109	(1)
The Update Statement	1110	(1)
Filtering Data	1111	(1)
Sorting the Results of a Query	1111	(1)
The Order By Clause	1111	(1)
Sorting Order	1111	(1)
Sorting by Multiple Columns	1111	(1)
Operators	1112	(1)
Comparison Operators	1112	(1)
Logical Operators	1112	(1)
The Set Operators	1113	(1)
SQL Functions	1113	(5)
Single-Row Functions	1113	(1)
Aggregate Functions	1114	(1)
Number and Date Functions	1114	(1)
General Functions and Conditional Expressions	1115	(1)
SQL Analytical Functions	1116	(1)
Hierarchical Retrieval of Data	1117	(1)
Selecting Data from Multiple Tables	1118	(2)
The Cartesian Product	1118	(1)
The Four Types of Oracle joins	1119	(1)
Grouping Operations	1120	(3)
Nesting Group Functions	1121	(1)
The Group By Clause with a Rollup Operator	1121	(1)
The Group By Clause with a Cube Operator	1122	(1)
The Group By Clause with a Grouping Operator	1122	(1)
The Group By Clause with a Grouping Sets Operator	1122	(1)
The Having Clause	1123	(1)
Writing Subqueries	1123	(2)
Top N Analysis	1123	(1)
Single-Row Subqueries	1124	(1)
Multiple-Row Subqueries	1124	(1)
Multiple-Column Subqueries	1124	(1)
Advanced Subqueries	1124	(1)
The Exists and Not Exists Operators	1124	(1)
Abstract Data Types	1125	(2)
The Create Type Command	1125	(1)
Object Tables	1125	(1)
Collections	1126	(1)
Type Inheritance	1126	(1)
The Cast Operator	1127	(1)
Using PL/SQL	1127	(3)
The Basic PL/SQL Block	1128	(1)
Declaring Variables	1128	(1)
Writing Executable Statements	1129	(1)
Handling Errors	1129	(1)
PL/SQL Control Structures	1130	(2)
Conditional Control	1130	(1)
PL/SQL Looping Constructs	1130	(2)
Cursors	1132	(3)
Implicit Cursors	1132	(1)
Explicit Cursors	1132	(1)
Cursor Attributes	1133	(1)
Cursor For Loops	1134	(1)
PL/SQL Records	1134	(1)
Cursor Variables	1134	(1)
Functions	1135	(1)
Packages	1135	(1)
Oracle XML DB	1136	(5)
Storing XML in Oracle XML DB	1137	(2)
The Oracle XML DB Repository	1139	(1)
Setting Up an XML Schema	1140	(1)
Creating a Relational View from an XML Document	1140	(1)
Oracle9i and Java	1141	(2)
Using Java with Oracle	1141	(2)
Summary	1143	(2)
Index	1145