Table of Contents

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