Manning Oracle8i Database Administration

Oracle8i Database Administration Noel Yuhanna 1999 \| 543 pages ISBN: 1884777783
	$42.95	Softbound print book

Contents

preface xi
acknowledgments xii
about this book xiii
Who should read this book? xiii
Why this book must be read xiii
Using the DBA scripts xiv
Conventions used in this book xiv
Sample layout xiv
Author online xv
Source code xv

1 Getting started 1

1.1 How do I install Oracle8 on Windows NT? 2
1.2 How can I automatically start up my Oracle database when the computer is restarted? 6
1.3 How do I create a database from scratch? 8
1.4 How do I find out what database options have been installed? 14
1.5 How can I recreate the control file? 16
1.6 How can I change my database name (DBNAME)? 19
1.7 How can I see the current initialization parameters of my database? 20
1.8 How do I run multiple instances of Oracle on a server? 22
1.9 How can I generate a complete report of the database? 23
1.10 How do I install Oracle8i on UNIX? 32
1.11 How do I install Oracle8i on Windows NT? 38

2 Table & index administration 45

2.1 How do I create a large object (LOB) in Oracle8? 47

2.2 How do I implement deferred constraints? 50

2.3 How do I create a bitmapped index? 54

2.4 Are bitmapped indexes really small and fast? 56

2.5 What is an index-organized table? 60

2.6 How do I create a reverse-key index? 62

2.7 How can I create a clustered index? 64

2.8 How do I create a hash cluster? 68

2.9 How can I estimate the amount of space needed by a table? 70

2.10 How can I estimate the amount of space required by an index? 72

2.11 How can I view and eliminate migrated and chained rows? 74

2.12 How do I create a test table with dummy data? 78

2.13 How do I create a function-based index? 80

2.14 How do I create a single-table hash cluster? 82

2.15 How do I drop a column without recreating the table? 83

2.16 How can I create an index online? 86

2.17 How can I move a nonpartitioned table? 88

3 Tablespace administration 90

3.1 How do I make a tablespace read-only? 91

3.2 How can I coalesce free space in a tablespace? 94

3.3 How can I manually stripe a tablespace in Oracle to improve performance? 97

3.4 How can I move tables from one tablespace to another? 102

3.5 How can I decrease the tablespace size? 106

3.6 How can I set the tablespace quota for users? 108

3.7 Can Oracle automatically extend a tablespace? 110

3.8 How can I create a tablespace on a raw device? 112

3.9 Can I move a file system datafile to a raw device and vice-versa? 117

3.10 I would like to know how many rows there are in a datafile for a given table? 119

3.11 How can I move a datafile to another location? 121

3.12 How can I determine the tablespace usage by a user? 123

3.13 What you need to know about unlimited extents 125

3.14 How do I create a transportable tablespace? 128

3.15 How do I distribute my Oracle data on CD? 134

3.16 How do I create a locally managed tablespace? 137

4 Triggers, stored procedure & PL/SQL administration 140

4.1 What are the new features available in PL/SQL8? 141

4.2 How can I create a database trigger in Oracle8? 143

4.3 How can I generate a report on triggers? 148

4.4 How can I implement INSTEAD OF triggers’ functionality? 150

4.5 What is a stored procedure and how do I create it? 155

4.6 How can I generate a report on stored procedures? 162

4.7 How can I view the source code of all of the stored procedures in my database? 164

4.8 How can I create a file containing the source code of my stored procedure? 166

4.9 How can I hide my PL/SQL code? 168

4.10 How can I generate sequence numbers for my table? 170

4.11 How can I generate a report on the sequence numbers in the database? 173

4.12 Is it possible to execute dynamic SQL using PL/SQL? 174

4.13 How can I read and write an external file using PL/SQL procedure? 178

4.14 How can I make an external procedure call in Oracle8? 181

4.15 How do I create a user-defined function? 186

4.16 How can I use the DBMS_ALERT package? 188

4.17 How do I submit a job to the job queue? 191

4.18 How do I monitor jobs in the job queue? 195

4.19 How do I manage my jobs? 199

4.20 How do I implement the autonomous PL/SQL block feature? 201

4.21 How can I write dynamic SQL in PL/SQL? 205

4.22 How can I implement bulk binds using PL/SQL? 207

5 Partitioning administration 211

5.1 How do I create a range-partitioned table? 212

5.2 How can I convert an existing table to a range partitioned table? 219

5.3 How can I create a partition view? 223

5.4 How can I convert a partition view to a partitioned table? 225

5.5 How can I manage a range-partitioned table? 230

5.6 How can I monitor the partitioned tables in Oracle8? 234

5.7 What do I need to know about partition pruning? 237

5.8 How can I create a local partitioned index in Oracle8? 239

5.9 How do I create a global partitioned index in Oracle8? 243

5.10 How do I monitor a partitioned index in Oracle8? 247

5.11 How do I create a hash-partitioned table? 250

5.12 How do I create a composite-partitioned table? 254

5.13 How do I merge partitions? 256

5.14 How do I enable row movement between partitions? 259

6 Backup & recovery 261

6.1 What are the various backup options available in Oracle8? 262

6.2 How do I use the Oracle RMAN to do backup and recovery? 265

6.3 How do I take hot database backups in Windows NT using the OS backup method? 270

6.4 How do I take a hot database backup in UNIX using OS backup method? 274

6.5 How do I take a full database backup in Windows NT using OS backup? 277

6.6 How do I take full database backup in UNIX using OS backup? 280

6.7 How do I schedule a database backup using the OS backup method? 283

6.8 How do I recover the database to a point in time? 286

6.9 How do I back up my database using the Export/Import utility? 288

6.10 How can I back up the database in parallel using the Recovery Manager? 291

6.11 I have lost one of my datafiles and cannot start up Oracle. What can I do? 293

6.12 How can I ensure that the blocks being read from the datafiles are not corrupted? 295

6.13 How can I check the datafile for physical data structure integrity? 296

6.14 How can I resume/suspend the database? 297

6.15 How can I increase the number of archive processes? 299

7 Performance & tuning 301

7.1 What Oracle block size should I use for my database? 302

7.2 How do I create a tablespace on a raw device? 304

7.3 What is the multiple buffer pool feature and how can I use it? 308

7.4 What is the new ROWID format in Oracle8? 311

7.5 What RAID level should I choose for my database? 314

7.6 How can I tune my Oracle8 database on UNIX? 318

7.7 How can I tune my Oracle8 database on Windows NT? 323

7.8 How can I balance my database I/O across controllers/disks? 325

7.9 How do I improve query performance? 328

7.10 How can I view log entries using the Log Miner utility? 331

7.11 How can I speed up my instance recovery process? 337

7.12 What sort improvements are in Oracle8i? 337

7.13 Got scripts? 338

How can I generate a report on the database I/O? 338
How can I generate a report on the commands executed on the instance? 338
How can I generate a report on the SQL statements that are being executed on the instance? 338
How can I generate a report on the I/O calls for each SQL statement being executed on the instance? 339
How can I generate a report on the hit ratio? 339
How can I generate a report on the locks held by users? 339
How can I generate a report on the active processes? 339
How can I generate a report on tables and indexes that have been analyzed? 339
How can I generate a report on the space consumed by each user? 340
How can I generate a detailed report on a user? 339
What are the various V$ tables in Oracle8? 339
How can I generate a tablespace freespace report? 339
How can I generate a report on rollback segments? 340
How can I generate a report on the active transactions? 340
How can I generate a report on the extents allocation? 340
How can I generate a report on the number of objects held by users? 340
How can I generate a report on the redo logs? 340
How can I generate a report on waits? 340
How can I generate a report on the system statistics? 340
How can I generate a detailed report on all of the statistics? 340
How can I view the CPU usage for various sessions? 340
How can I generate a report on the session hit ratio? 340
How can I generate a report on tables that have no indexes? 340
How can I generate a report on the table access method for the instance? 341
How can I generate a report on the undocumented Oracle parameters? 341

8 Parallel execution 342

8.1 What is Oracle’s parallel execution and how do I configure it? 343

8.2 How can I set up my database to do parallel load? 347

8.3 How can I create an index in parallel? 352

8.4 How do I run a query using the Oracle parallel execution feature? 356

8.5 How can I copy selected data from one table into a new table using the parallel execution feature? 357

8.6 What is the new Oracle8 parallel DML feature and how do I configure it? 358

8.7 How do I configure for the parallel update feature? 360

8.8 How do I configure for the parallel delete feature? 362

8.9 How do I configure for the parallel insert feature? 364

8.10 How can I automate parallel query tuning? 366

9 Standby database 370

9.1 What are the high-availability solutions available in Oracle? 371

9.2 How do I implement a standby database solution? 374

9.3 How do I convert my old primary database to a standby database? 388

9.4 How can I implement a standby database solution with recovery up to the last committed transaction? 393

9.5 How can I automate my archive logs transfer and recovery on the standby database server? 398

9.6 What are the precautions to ensure that the standby database will activate properly and be consistent? 403

9.7 How do I place the standby database in managed recovery mode? 405

9.8 How can I place the standby database in read-only mode? 427

10 Advanced database replication 431

10.1 What different forms of replication are available in Oracle8? 432

10.2 How do I implement a basic read-only snapshot? 435

10.3 How do I implement bidirectional asynchronous replication for some of my tables? 443

10.4 What is procedural replication and how do I implement it? 459

10.5 How do I resolve conflicts in my replicated environment? 466

10.6 How can I compare tables across two remote databases? 474

10.7 How can I rectify tables that are not identical? 477

10.8 How do I implement synchronous bidirectional replication? 479

10.9 How can I implement advanced replication in Oracle8i? 488

11 Oracle8 on Linux 495

11.1 What are the basic requirements for running Oracle8 on LINUX? 496

11.2 How do I install Oracle8 on LINUX? 497

11.3 How do I configure Oracle Net8 on LINUX? 505

11.4 How can I monitor performance on LINUX? 507

11.5 How to I compile my PRO*C programs in LINUX? 509

11.6 How can I migrate my Oracle database from another UNIX platform to LINUX? 511

11.7 How can I migrate my Oracle database from Windows NT to LINUX? 513

index 517

DESCRIPTION

Databases are growing larger, and the use of distributed databases is on the rise. Oracle8i Database Administration addresses some of the most common yet complex issues that are faced by DBAs around the world.

Oracle has the largest market share in Database Management software and is the world's second largest software company. This book is designed for for Oracle Database administrators and others who are involved in planning, programming, administration, and implementation of Oracle8 Databases.

Although this book targets intermediate and advanced database administrators, it can also be an invaluable resource to entry-level DBAs, designers and developers, project managers, system administrators, data warehousing professionals or anyone interested in Oracle.

Oracle8i Database Administration uses a Q&A approach that provides in-depth technical solutions. The questions in this book have been compiled from many sources including Oracle forums, the Internet, and the author's personal experiences. The book also examines features that are new in Oracle8.

Translation rights for Oracle8i Database Administration have been granted for Brazil, Italy, and India. If you are interested in learning where to buy this book in a language other than English, please inquire at your local bookseller.

WHAT THE EXPERTS SAY ABOUT THIS BOOK...

"...it is much better than any Oracle Press book I've ever read. The answers are great at being very detailed...most of them go step-by-step through how to do something...unlike my experiences with the Oracle Press book. I highly recommend this to anyone who deals with Oracle on a regular basis."
--Billy Barron, Delphi Consultants, for JavaMUG

WHAT THE READERS SAY ABOUT THIS BOOK...

"The author has taken a radical new approach with this book, in that the solutions offered are taken from actual questions/problems faced by Oracle DBAs. The scripts included in the book make for compelling reading as well, and show practical solutions to some of the most difficult problems facing the Oracle DBA. Not just another techni-cal manual, but a real hands-on wealth of solutions. Great book!"
--Adrian Booth, Amazon.com Customer

"This book is outstanding! It provides factual information—posing a typical problem a DBA runs into and most importantly shows you how to solve this problem. I would highly recommend this book."
--Veronica Veroulis, Amazon.com Customer

ABOUT THE AUTHOR...

Noel Yuhanna is a Senior Database Consultant in a large multinational Corporation. He has over 15 years of experience in Oracle, Informix and Sybase databases. He has worked on very large, terabyte-sized databases and has helped several companies in database installation, capacity planning, performance, and tuning.

Inside the book

Manning Blog

Author Blogs

Author Calendar

Catalog

About...

Table of Contents

DESCRIPTION

WHAT THE EXPERTS SAY ABOUT THIS BOOK...

WHAT THE READERS SAY ABOUT THIS BOOK...

ABOUT THE AUTHOR...