Oracle 8i Admin Guide

PDF Directory......Page 0
Password File Administration 19......Page 3
Troubleshooting Database Creation 216......Page 4
The Location of the Initialization Parameter File in Distributed Environments 316......Page 5
Creating Initial Control Files 54......Page 6
Running a Database in NOARCHIVELOG Mode 73......Page 7
Starting up SNP processes 83......Page 8
Dropping Tablespaces 922......Page 9
Guidelines for Managing Rollback Segments 112......Page 10
Setting Storage Parameters for Data Segments 1212......Page 11
Converting Index-Organized Tables to Regular Tables 1326......Page 12
Adding Partitions 1514......Page 13
Advantages of Hashing 172......Page 14
Managing Integrity Constraints 1914......Page 15
DBMS_REPAIR Examples 208......Page 16
User Authentication 227......Page 17
Revoking Object Privileges and Roles 2314......Page 18
Listing Active Statement Audit Options 2418......Page 19
Database Resource Manager Views 2521......Page 20
Send Us Your Comments......Page 21
Preface......Page 23
Part I Basic Database Administration......Page 31
1 The Oracle Database Administrator......Page 33
Database Administrators......Page 34
Database Users......Page 35
Database Administrator Usernames......Page 36
SYSTEM......Page 37
Selecting an Authentication Method......Page 38
Using Operating System Authentication......Page 39
OSOPER and OSDBA......Page 40
Password File Administration......Page 41
Using ORAPWD......Page 42
Setting REMOTE_LOGIN_ PASSWORDFILE......Page 43
Adding Users to a Password File......Page 44
Granting and Revoking SYSOPER and SYSDBA Privileges......Page 45
Connecting with Administrator Privileges: Example......Page 46
To Replace a Password File......Page 47
Changing the Password File State......Page 48
Priorities of a Database Administrator......Page 49
Step 2: Evaluate the Database Server Hardware......Page 50
Step 3: Plan the Database......Page 51
Step 7: Enroll System Users......Page 52
Release Number Format......Page 53
Checking Your Current Release Number......Page 54
2 Creating an Oracle Database......Page 57
Planning for Database Creation......Page 58
Deciding How to Create an Oracle Database......Page 59
The Oracle Database Configuration Assistant (DBCA)......Page 60
DBCA Modes for Database Creation......Page 61
Selecting the Database Creation Method......Page 62
Steps for Creating an Oracle Database......Page 65
The Database Creation Script......Page 67
The CREATE DATABASE Statement......Page 69
Creating a Tablespace for Rollback Segments......Page 70
Creating Rollback Segments......Page 71
Installation Parameters......Page 72
A Sample Initialization File......Page 73
Editing the Initialization Parameter File......Page 75
DB_NAME and DB_DOMAIN......Page 76
CONTROL_FILES......Page 77
DB_BLOCK_BUFFERS......Page 78
LICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING......Page 79
Considerations After Creating a Database......Page 80
Allocating Rollback Segments......Page 81
Distributing I/O......Page 82
3 Starting Up and Shutting Down......Page 83
Options for Starting Up a Database......Page 84
Using Recovery Manager......Page 85
Starting an Instance Without Mounting a Database......Page 86
Restricting Access to a Database at Startup......Page 87
Starting an Instance, Mounting a Database, and Starting Complete Media Recovery......Page 88
Starting Remote Instances......Page 89
Opening a Closed Database......Page 90
Restricting Access to an Open Database......Page 91
Shutting Down a Database......Page 92
Shutting Down with the NORMAL Option......Page 93
Shutting Down with the TRANSACTIONAL Option......Page 94
Suspending and Resuming a Database......Page 95
The Sample Initialization Parameter File......Page 97
The Location of the Initialization Parameter File in Distributed Environments......Page 98
Part II Oracle Server Configuration......Page 99
4 Managing Oracle Processes......Page 101
Dedicated Server Processes......Page 102
Multi-Threaded Server Processes......Page 103
Initialization Parameters for MTS......Page 105
MTS_DISPATCHERS: Setting the Initial Number of Dispatchers......Page 106
MTS_SERVERS: Setting the Initial Number of Shared Servers......Page 107
Adding and Removing Dispatcher Processes......Page 108
Shutting Down Specific Dispatcher Processes......Page 109
Monitoring MTS......Page 110
What are the Oracle Background Processes......Page 111
Process and Session Views......Page 114
Trace Files, the Alert Log, and Background Processes......Page 115
Controlling When Oracle Writes to Trace Files......Page 116
Managing the Query Servers......Page 117
Managing Processes for External Procedures......Page 118
Sample Entry in tnsnames.ora......Page 119
Terminating Sessions......Page 120
Identifying Which Session to Terminate......Page 121
Terminating an Inactive Session......Page 122
5 Managing Control Files......Page 125
Name Control Files......Page 126
Manage the Size of Control Files......Page 127
Creating Initial Control Files......Page 128
New Control Files......Page 129
Creating New Control Files......Page 130
Checking for Missing or Extra Files......Page 132
Dropping Control Files......Page 133
6 Managing the Online Redo Log......Page 135
Online Redo Log Contents......Page 136
How Oracle Writes to the Online Redo Log......Page 137
Active (Current) and Inactive Online Redo Log Files......Page 138
Multiplexing Online Redo Log Files......Page 139
Responding to Online Redo Log Failure......Page 140
Legal and Illegal Configurations......Page 141
Choosing the Number of Online Redo Log Files......Page 143
Creating Online Redo Log Members......Page 145
Renaming and Relocating Online Redo Log Members......Page 146
To Rename Online Redo Log Members......Page 147
Dropping Log Groups......Page 148
Dropping Online Redo Log Members......Page 149
Forcing Log Switches......Page 150
Restrictions......Page 151
Listing Information about the Online Redo Log......Page 152
7 Managing Archived Redo Logs......Page 155
What Is the Archived Redo Log?......Page 156
Running a Database in NOARCHIVELOG Mode......Page 157
Running a Database in ARCHIVELOG Mode......Page 158
Changing the Database Archiving Mode......Page 160
Enabling Automatic Archiving......Page 161
Disabling Automatic Archiving at Instance Startup......Page 162
Performing Manual Archiving......Page 163
Method1: Using the LOG_ARCHIVE_DEST_n Parameter......Page 164
Understanding Archive Destination States......Page 166
Standby Transmission Mode......Page 168
Specifying Mandatory and Optional Destinations......Page 170
Scenario 1......Page 171
Scenario 2......Page 172
Tuning Archive Performance......Page 173
Specifying Multiple ARCn Processes......Page 174
Adjusting Archive Buffer Parameters......Page 175
Displaying Archived Redo Log Information......Page 176
Fixed Views......Page 177
The ARCHIVE LOG LIST SQL Statement......Page 178
Controlling Trace Output Generated by the Archivelog Process......Page 179
How Can You Use LogMiner?......Page 180
Restrictions......Page 181
To Create a Dictionary File on an Oracle8i Database:......Page 182
To Create a Dictionary File on an Oracle8 Database:......Page 183
Using LogMiner......Page 184
To Use LogMiner:......Page 185
Using LogMiner: Scenarios......Page 186
Step 2: Adding Logs and Limiting the Search Range......Page 187
Step 3: Starting LogMiner and Analyzing the Data......Page 188
Calculating Table Access Statistics......Page 189
8 Managing Job Queues......Page 191
Multiple SNP processes......Page 192
The DBMS_JOB Package......Page 193
Submitting a Job to the Job Queue......Page 194
Job Environment......Page 195
Jobs and Import/Export......Page 196
Job Definitions......Page 197
Database Links and Jobs......Page 198
Job Execution Errors......Page 199
Removing a Job from the Job Queue......Page 200
WHAT......Page 201
How a Job Becomes Broken......Page 202
Forcing a Job to Execute......Page 203
Viewing Job Queue Information......Page 204
Part III Database Storage......Page 207
9 Managing Tablespaces......Page 209
Use Multiple Tablespaces......Page 210
Assign Tablespace Quotas to Users......Page 211
Creating Tablespaces......Page 212
Creating a Dictionary-Managed Tablespace......Page 213
Locally Managed Tablespaces......Page 214
Creating a Locally Managed Tablespace......Page 215
Creating a Dictionary-Managed Temporary Tablespace......Page 216
Creating a Locally Managed Temporary Tablespace......Page 217
Managing Tablespace Allocation......Page 218
Specifying Default Storage Parameters......Page 219
Coalescing Free Space in Dictionary-Managed Tablespaces......Page 220
Manually Coalescing Free Space......Page 221
Monitoring Free Space......Page 222
Taking Tablespaces Offline......Page 223
Read-Only Tablespaces......Page 225
Making a Tablespace Read-Only......Page 226
Making a Read-Only Tablespace Writable......Page 228
Delaying the Opening of Datafiles in Read Only Tablespaces......Page 229
Dropping Tablespaces......Page 230
Using the DBMS_SPACE_ADMIN Package......Page 231
Scenario 3......Page 233
Transporting Tablespaces Between Databases......Page 234
Limitations......Page 235
Procedure for Transporting Tablespaces Between Databases......Page 236
Step 1: Pick a Self-Contained Set of Tablespaces......Page 237
Step 2: Generate a Transportable Tablespace Set......Page 238
Step 4: Plug In the Tablespace Set......Page 239
REFs......Page 241
Objects......Page 242
Transporting and Attaching Partitions for Data Warehousing......Page 243
Publishing Structured Data on CDs......Page 245
Mounting the Same Tablespace Read-only on Multiple Databases......Page 246
Viewing Information About Tablespaces......Page 247
Listing Tablespaces and Default Storage Parameters: Example......Page 248
Statistics for Free Space (Extents) of Each Tablespace: Example......Page 249
10 Managing Datafiles......Page 251
Determine the Number of Datafiles......Page 252
Store Datafiles Separate From Redo Log Files......Page 254
Enabling and Disabling Automatic Extension for a Datafile......Page 255
Manually Resizing a Datafile......Page 256
Altering Datafile Availability......Page 257
Taking Datafiles Offline in NOARCHIVELOG Mode......Page 258
Renaming Datafiles in a Single Tablespace......Page 259
Relocating and Renaming Datafiles in a Single Tablespace......Page 260
Renaming and Relocating Datafiles for Multiple Tablespaces......Page 261
Verifying Data Blocks in Datafiles......Page 262
Viewing Information About Datafiles......Page 263
11 Managing Rollback Segments......Page 265
Use Multiple Rollback Segments......Page 266
Specify Rollback Segments to Acquire Automatically......Page 267
Approximate Rollback Segment Sizes......Page 268
Create Rollback Segments with Many Equally Sized Extents......Page 269
Creating Rollback Segments......Page 270
Setting Storage Parameters When Creating a Rollback Segment......Page 271
Changing the ONLINE/OFFLINE Status of Rollback Segments......Page 273
Bringing a PARTLY AVAILABLE Rollback Segment Online......Page 274
Taking Rollback Segments Offline......Page 275
Dropping Rollback Segments......Page 276
Monitoring Rollback Segment Information......Page 277
Rollback Segment Statistics......Page 278
Displaying All Rollback Segments......Page 279
Displaying Whether a Rollback Segment Has Gone Offline......Page 280
Part IV Schema Objects......Page 281
12 Guidelines for Managing Schema Objects......Page 283
The PCTFREE Parameter......Page 284
Specifying PCTFREE......Page 285
The PCTUSED Parameter......Page 286
Specifying PCTUSED......Page 287
Examples of Choosing PCTFREE and PCTUSED Values......Page 288
MAXTRANS......Page 289
Identifying the Storage Parameters......Page 290
PCTINCREASE......Page 291
FREELIST GROUPS......Page 292
Setting Default Storage Parameters for Segments in a Tablespace......Page 293
Changing Values for Storage Parameters......Page 294
Storage Parameter Example......Page 295
Deallocating Space......Page 296
Issuing Space Deallocation Statements......Page 297
Deallocating Space Examples......Page 298
Understanding Space Use of Datatypes......Page 300
13 Managing Tables......Page 301
Specify How Data Block Space Is to Be Used......Page 302
Specify the Location of Each Table......Page 303
Estimate Table Size and Set Storage Parameters......Page 304
Plan for Large Tables......Page 305
Table Restrictions......Page 306
Creating Tables......Page 309
Altering Tables......Page 311
Manually Allocating Storage for a Table......Page 313
Marking Columns Unused......Page 314
Dropping Tables......Page 315
Differences Between Index Organized and Regular Tables......Page 317
Creating Index-Organized Tables......Page 319
Using the Overflow Clause......Page 320
Using the INCLUDING clause......Page 321
Using Key Compression......Page 322
Moving (Rebuilding) Index-Organized Tables......Page 323
Analyzing Index-Organized Tables......Page 324
Using the ORDER BY Clause with Index-Organized Tables......Page 325
Converting Index-Organized Tables to Regular Tables......Page 326
14 Managing Indexes......Page 327
Guidelines for Managing Indexes......Page 328
Create Indexes After Inserting Table Data......Page 329
Specify Index Block Space Use......Page 330
Specify the Tablespace for Each Index......Page 331
Consider Creating Indexes with NOLOGGING......Page 332
Consider Costs and Benefits of Coalescing or Rebuilding Indexes......Page 333
Creating Indexes......Page 334
Creating an Index Associated with a Constraint......Page 335
Creating an Index Online......Page 336
Creating a Function-Based Index......Page 337
Example 1......Page 338
Example 4......Page 339
Creating a Key-Compressed Index......Page 340
Altering Indexes......Page 341
Dropping Indexes......Page 342
15 Managing Partitioned Tables and Indexes......Page 345
What Are Partitioned Tables and Indexes?......Page 346
Using the Range Partitioning Method......Page 347
Using the Hash Partitioning Method......Page 349
Using the Composite Partitioning Method......Page 350
Creating Partitions......Page 351
Creating Range Partitions......Page 352
Creating Hash Partitions......Page 354
Creating Composite Partitions and Subpartitions......Page 355
Maintaining Partitions......Page 356
Adding Partitions to a Composite-Partitioned Table......Page 358
Adding Index Partitions......Page 359
Dropping a Table Partition......Page 360
Dropping a Partition from a Table Containing Data and Global Indexes......Page 361
Dropping Index Partitions......Page 362
Exchanging a Hash or Range Partition......Page 363
Exchanging a Subpartition of a Composite-Partitioned Table......Page 364
Merging Range Partitions......Page 365
Merging Range Composite Partitions......Page 366
Modifying Default Attributes of Index Partitions......Page 367
Modifying Real Attributes for a Range Partition......Page 368
Moving Partitions......Page 369
Moving Index Partitions......Page 370
Using Alter Index to Rebuild a Partition......Page 371
Renaming a Index Partition......Page 372
Splitting a Range Table Partition......Page 373
Truncating Partitions......Page 374
Truncating a Partition Containing Data and Referential Integrity Constraints......Page 375
Partitioned Tables and Indexes Examples......Page 376
Moving the Time Window in a Historical Table......Page 377
Converting a Partition View into a Partitioned Table......Page 378
16 Managing Clusters......Page 381
Guidelines for Managing Clusters......Page 382
Choose Appropriate Columns for the Cluster Key......Page 384
Specify the Space Required by an Average Cluster Key and Its Associated Rows......Page 385
Creating Clusters......Page 386
Creating Clustered Tables......Page 387
Creating Cluster Indexes......Page 388
Altering Clusters......Page 389
Dropping Clusters......Page 390
Dropping Cluster Indexes......Page 391
17 Managing Hash Clusters......Page 393
Advantages of Hashing......Page 394
Disadvantages of Hashing......Page 395
Creating Hash Clusters......Page 396
Choosing the Key......Page 397
Setting SIZE......Page 398
Example 2......Page 399
How to Estimate Size Required by Hash Clusters and Set Storage Parameters......Page 400
Dropping Hash Clusters......Page 401
18 Managing Views, Sequences and Synonyms......Page 403
Creating Views......Page 404
Join Views......Page 405
Creating Views with Errors......Page 406
Updating a Join View......Page 407
Key-Preserved Tables......Page 408
UPDATE Statements......Page 409
DELETE Statements......Page 410
Using the UPDATABLE_ COLUMNS Views......Page 411
Altering Views......Page 412
Replacing Views......Page 413
Creating Sequences......Page 414
Dropping Sequences......Page 415
Dropping Synonyms......Page 416
19 General Management of Schema Objects......Page 419
Creating Multiple Tables and Views in a Single Operation......Page 420
Analyzing Tables, Indexes, and Clusters......Page 421
Using Statistics for Tables, Indexes, and Clusters......Page 422
Computing Statistics Using the ANALYZE Statement......Page 423
Index Statistics......Page 424
Viewing Object Statistics......Page 425
DBMS_STATS......Page 426
Listing Chained Rows of Tables and Clusters......Page 427
Truncating Tables and Clusters......Page 428
Using TRUNCATE......Page 429
Enabling and Disabling Triggers......Page 430
Enabling Triggers......Page 431
Managing Integrity Constraints......Page 432
Integrity Constraint States......Page 433
Enable Novalidate Constraint State......Page 434
Disabling Constraints Upon Definition......Page 435
Disabling Enabled Constraints......Page 436
Set All Constraints Deferred......Page 437
Dropping Integrity Constraints......Page 438
Reporting Constraint Exceptions......Page 439
Managing Object Dependencies......Page 441
Manually Recompiling Views......Page 442
Managing Object Name Resolution......Page 443
Changing Storage Parameters for the Data Dictionary......Page 444
Structures in the Data Dictionary......Page 445
Errors that Require Changing Data Dictionary Storage......Page 446
Displaying Information About Schema Objects......Page 447
Example 1: Displaying Schema Objects By Type......Page 448
Example 3: Displaying Dependencies of Views and Synonyms......Page 449
Example 6: Displaying the Free Space (Extents) of a Database......Page 450
Example 7: Displaying Segments that Cannot Allocate Additional Extents......Page 451
20 Addressing Data Block Corruption......Page 455
DBMS_REPAIR Procedures......Page 456
Using the DBMS_REPAIR Package......Page 457
DBMS_REPAIR: Using the CHECK_OBJECT and ADMIN_TABLES Procedures......Page 458
Stage 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR......Page 459
Recover Data Using the DUMP_ORPHAN_KEYS Procedures......Page 461
Using ADMIN_TABLES to Build a Repair Table or Orphan Key Table......Page 462
Creating a Repair Table......Page 463
Using the CHECK_OBJECT Procedure to Detect Corruption......Page 464
Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure......Page 465
Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEYS......Page 466
Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure......Page 467
Enabling or Disabling the Skipping of Corrupt Blocks: SKIP_CORRUPT_BLOCKS......Page 468
Part V Database Security......Page 469
21 Establishing Security Policies......Page 471
User Authentication......Page 472
Data Security Policy......Page 473
General User Security......Page 474
Password Security......Page 475
Using Roles for End-User Privilege Management......Page 476
Using a Directory Service for End-User Privilege Management......Page 477
Using Roles for Administrator Privilege Management......Page 478
Application Developer Security......Page 479
Free Versus Controlled Application Development......Page 480
Application Administrator Security......Page 481
Account Locking......Page 482
Password Aging and Expiration......Page 483
Password History......Page 484
Password Verification Routine Formatting Guidelines......Page 485
Auditing Policy......Page 489
22 Managing Users and Resources......Page 491
Session and User Licensing......Page 492
Connecting Privileges......Page 493
Changing Concurrent Usage Limits While the Database is Running......Page 494
Named User Limits......Page 495
Viewing Licensing Limits and Current Values......Page 496
User Authentication......Page 497
Advantages of Database Authentication......Page 498
External Authentication......Page 499
Operating System Authentication......Page 500
Global Authentication and Authorization......Page 501
Creating a Schema-Independent User......Page 502
Multi-Tier Authentication and Authorization......Page 503
Oracle Users......Page 504
Setting a User’s Authentication......Page 505
Assigning a Temporary Tablespace......Page 506
UNLIMITED TABLESPACE System Privilege......Page 507
Altering Users......Page 508
Changing a User’s Authentication Mechanism......Page 509
Dropping Users......Page 510
Enabling and Disabling Resource Limits Before Startup......Page 511
Creating Profiles......Page 512
Altering Profiles......Page 513
Setting Resource Costs......Page 514
Listing Information About Database Users and Profiles......Page 515
Listing All Tablespace Quotas......Page 517
Listing All Profiles and Assigned Limits......Page 518
Viewing Memory Use Per User Session......Page 519
Examples......Page 520
23 Managing User Privileges and Roles......Page 523
System Privilege Restrictions......Page 524
Accessing Frequently Used Dictionary Objects......Page 525
Managing User Roles......Page 526
Predefined Roles......Page 527
Creating a Role......Page 528
Role Authorization by the Operating System......Page 530
Role Authorization by an Enterprise Directory Service......Page 531
Granting System Privileges and Roles......Page 532
Granting Object Privileges and Roles......Page 533
Granting Privileges on Columns......Page 534
Revoking System Privileges and Roles......Page 535
Revoking Column-Selective Object Privileges......Page 536
System Privileges......Page 537
Granting to and Revoking from the User Group PUBLIC......Page 538
The SET ROLE Statement......Page 539
Granting Roles Using the Operating System or Network......Page 540
Using Operating System Role Identification......Page 542
Enabling and Disabling Roles When OS_ROLES=TRUE......Page 543
Listing Privilege and Role Information......Page 544
Listing All System Privilege Grants......Page 545
Listing the Current Privilege Domain of Your Session......Page 546
Listing Roles of the Database......Page 547
Listing Information About the Privilege Domains of Roles......Page 548
24 Auditing Database Use......Page 551
Keep Audited Information Manageable......Page 552
Auditing Suspicious Database Activity......Page 553
Creating the Audit Trail Views......Page 554
Managing Audit Trail Information......Page 555
Setting Auditing Options......Page 557
Privilege Audit Options......Page 558
Enabling Audit Options......Page 559
Enabling Object Auditing......Page 560
Disabling Audit Options......Page 561
Disabling Object Auditing......Page 562
Enabling and Disabling Database Auditing......Page 563
Controlling the Growth and Size of the Audit Trail......Page 564
Purging Audit Records from the Audit Trail......Page 565
Protecting the Audit Trail......Page 566
Viewing Database Audit Trail Information......Page 567
Listing Active Statement Audit Options......Page 568
Listing Default Object Audit Options......Page 569
Auditing Through Database Triggers......Page 570
Part VI Database Resource Management......Page 575
25 The Database Resource Manager......Page 577
What is the Database Resource Manager?......Page 578
Administering the Database Resource Manager......Page 579
Creating and Managing Resource Plans......Page 582
Using the Pending Area for Creating Plan Schemas......Page 583
Validating Changes......Page 584
Clearing the Pending Area......Page 585
Updating a Plan......Page 586
Creating Resource Consumer Groups......Page 587
Specifying Resource Plan Directives......Page 588
Creating a Resource Plan Directive......Page 589
Assigning an Initial Resource Consumer Group......Page 590
Switching Sessions for a User......Page 591
Revoking Switch Privileges......Page 592
Using the DBMS_SESSION Package to Switch Consumer Group......Page 593
A Multilevel Schema......Page 594
An Oracle Supplied Plan......Page 596
Database Resource Manager Views......Page 597
Index......Page 599