Oracle8: Database Administration

Volume 2 • Instructor Guide
30020GC10
Production 1.0
March 1998
M06292
Copyright  Oracle Corporation, 1998. All rights reserved.
This documentation contains proprietary information of Oracle Corporation. It is
provided under a license agreement containing restrictions on use and disclosure
and is also protected by copyright law. Reverse engineering of the software is
prohibited. If this documentation is delivered to a U.S. Government Agency of the
Department of Defense, then it is delivered with Restricted Rights and the
following legend is applicable:
Restricted Rights Legend
Use, duplication or disclosure by the Government is subject to restrictions for
commercial computer software and shall be deemed to be Restricted Rights
software under Federal law, as set forth in subparagraph (c) (1) (ii) of DFARS
252.227-7013, Rights in Technical Data and Computer Software (October 1988).
This material or any portion of it may not be copied in any form or by any means
without the express prior written permission of the Worldwide Education Services
group of Oracle Corporation. Any other copying is a violation of copyright law and
may result in civil and/or criminal penalties.
If this documentation is delivered to a U.S. Government Agency not within the
Department of Defense, then it is delivered with “Restricted Right,” as defined in
FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).
The information in this document is subject to change without notice. If you find
any problems in the documentation, please report them in writing to Education
Products, Oracle Corporation, 500 Oracle Parkway, Box 659806, Redwood
Shores, CA 94065. Oracle Corporation does not warrant that this document is
error-free.

Tigger Newman
Howard Ostrow
Hans Proetzl
Gary Purcell
Shankar Raman
Donalyn Selinsky
Roger Simon
Jim Spiller
Ramonito Te
Sabine Teuber
Jean-Francois Verrier
Norbert Wittje
Publishers
Stephanie Jones
Kelly Lee
Renee Voss
Oracle8: Database Administration iii.
Contents
Preface
Profile xii
Related Publications xiii
Typographic Conventions xiv
Curriculum Map
Oracle8 Database Administrator Curriculum xviii
Advanced DBA Curriculum xix
Introduction
Course Objectives I-3

Summary 3-43
iv Oracle8: Database Administration.
Contents
Lesson 4: Creating a Database
Objectives 4-3
Overview 4-4
Preparing the Operating System 4-5
Creating a Database 4-12
Lesson 5: Creating Data Dictionary Views and Standard Packages
Objectives 5-3
Data Dictionary Structure 5-4
Constructing the Data Dictionary 5-11
Using Administrative Scripts 5-13
Administering Stored Procedures and Packages 5-15
Obtaining Information 5-23
Troubleshooting 5-26
Summary 5-27
Lesson 6: Maintaining the Control File
Objectives 6-3
Using the Control File 6-4
The Contents of the Control File 6-5
Obtaining Information About the Control File 6-7
Multiplexing the Control File 6-9
Summary 6-10
Lesson 7: Maintaining Redo Log Files
Objectives 7-3
Overview 7-4

Considerations in Creating Tablespaces 8-37
Lesson 9: Storage Structure and Relationships
Objectives 9-3
Overview 9-4
Types of Segments 9-5
Using Block Space Utilization Parameters 9-16
Obtaining Information About Storage Structures 9-21
Planning the Location of Segments 9-25
Summary 9-27
Lesson 10: Managing Rollback Segments
Objectives 10-3
Overview 10-4
Rollback Segments 10-5
Using Rollback Segments with Transactions 10-8
Planning Rollback Segments 10-13
Creating Rollback Segments 10-15
Maintaining Rollback Segments 10-21
Obtaining Rollback Segment Information 10-26
Troubleshooting Rollback Segment Problems 10-32
Summary 10-39
Lesson 11: Managing Temporary Segments
Objectives 11-3
Overview 11-4
Types of Temporary Segments 11-6
Allocating Space for Temporary Segments 11-9
Obtaining Temporary Segment Information 11-11
Summary 11-14
vi Oracle8: Database Administration
Creating Clusters 15-9
Maintaining Clusters 15-17
Retrieving Information About Clusters 15-21
Index-Organized Tables 15-24
Using Index-Organized Tables 15-26
Retrieving Information About Index-Organized Tables 15-31
Summary 15-32
Lesson 16: Loading and Reorganizing Data
Objectives 16-3
Overview 16-4
Loading Data Using Direct-Load Insert 16-6
Loading Data Using SQL*Loader 16-9
Reorganizing Data Using Export and Import 16-29
Summary 16-48
Oracle8: Database Administration vii.
Contents
Lesson 17: Managing Users
Objectives 17-3
Overview 17-4
Creating New Database Users 17-7
Altering and Dropping Database Users 17-14
Dropping Users 17-18
Monitoring Information About Users 17-19
Summary 17-21
Lesson 18: Managing Profiles
Objectives 18-3
Overview 18-4

.
Contents
Lesson 21: Auditing
Objectives 21-3
Overview 21-4
Using Database Auditing 21-7
Viewing Auditing Results 21-18
Auditing Guidelines 21-20
Summary 21-22
Lesson 22: Using National Language Support
Objectives 22-3
Overview 22-4
Choosing a Database and a National Character Set 22-6
Specifying Language-Dependent Behavior 22-12
NLS Parameters and SQL-Functions 22-20
NLS Parameters in SQL-Functions 22-22
Importing and Loading Data Using NLS 22-26
Obtaining Information About NLS Settings 22-27
Summary 22-32
Appendix A: Practices
Environment A-2
Exercise 1—Oracle Architectural Components A-3
Lab 2—Using Administration Tools A-5
Lab 3—Managing an Oracle Instance A-6
Lab 4—Creating a Database A-8
Lab 5—Creating Data Dictionary Views and Standard Packages A-9
Lab 6—Maintaining the Control File A-10
Lab 7—Maintaining Redo Log Files A-11
Lab 8—Managing Tablespaces and Data Files A-12
Lab 9—Storage Structure and Relationships A-13

Lab 12—Managing Tables B-20
Lab 13—Managing Indexes B-22
Lab 14—Maintaining Data Integrity B-24
Lab 15—Using Clusters and Index-Organized Tables B-26
Lab 16—Loading and Reorganizing Data B-28
Lab 17—Managing Users B-30
Lab 18—Managing Profiles B-31
Lab 19—Managing Privileges B-33
Lab 20—Managing Roles B-34
Lab 21—Auditing B-35
Lab 22—Using National Language Support B-36
Appendix C: Server Manager Solutions
Exercise 1—Oracle Architectural Components C-2
Lab 2—Using Administration Tools C-4
Lab 3—Managing an Oracle Instance C-8
Lab 4—Creating a Database C-16
Lab 5—Creating Data Dictionary Views and Standard Packages C-20
Lab 6—Maintaining the Control File C-25
Lab 7—Maintaining Redo Log Files C-29
Lab 8—Managing Tablespaces and Data Files C-35
Lab 9—Storage Structure and Relationships C-40
Lab 10—Managing Rollback Segments C-47
Lab 11—Managing Temporary Segments C-56
Lab 12—Managing Tables C-60
Lab 13—Managing Indexes C-70
Lab 14—Maintaining Data Integrity C-78
Lab 15—Using Clusters and Index-Organized Tables C-87
Lab 16—Loading and Reorganizing Data C-92
Lab 17—Managing Users C-102
Lab 18—Managing Profiles C-105

Lab 21—Auditing D-118
Lab 22—Using National Language Support D-119
Appendix E: Certification Test: Sample Questions
Oracle Certified Professional (OCP) Program:
Oracle Certified Database Administrator Track E-2
Oracle Database Administration: Sample Test E-3
Oracle Backup and Recovery: Sample Test E-5
Answers E-8
Registering for an OCP Test E-9

13
Managing Indexes
13-2 Oracle8: Database Administration.
Lesson 13: Managing Indexes
Instructor Note
Topic Timing
Lecture 30 minutes
Practice 45 minutes
Total 75 minutes
Oracle8: Database Administration 13-3.
Objectives
Objectives
13-2
Copyright  Oracle Corporation, 1998. All rights reserved.

the combined size of all the columns cannot exceed roughly one-third of the
data block size.
13-3
Copyright  Oracle Corporation, 1998. All rights reserved.
Classification of Indexes
Logical
• Single column or concatenated
• Unique or nonunique
Physical
• Partitioned or nonpartitioned
• B-tree or bitmap
– Normal or reverse key (B-tree only)
Oracle8: Database Administration 13-5.
Types of Indexes
Unique and Nonunique Indexes
A unique index guarantees that no two rows of a table have duplicate values
in the column that defines the index. An index key in a unique index can
only point to one row in the table.
In a nonunique index, a single key can have multiple rows associated with it.
Partitioned and Nonpartitioned Indexes
Partitioned indexes are used for large tables to store index entries
corresponding to an index in several segments. Partitioning allows an index
to be spread across many tablespaces, decreasing contention for index
lookup and increasing manageability. Partitioned indexes are often used with
partitioned tables to improve scalability and manageability. An index
partition can be created for each table partition.
This lesson discusses the creation and maintenance of nonpartitioned B-tree

Key column length
Key column value
ROWID
Root
Branch
Leaf
Index entry
Oracle8: Database Administration 13-7.
Types of Indexes
Index Leaf Entry Characteristics
In a B-tree index on a nonpartitioned table:
• Key values are repeated if there are multiple rows that have the same key
value.
• There is no index entry corresponding to a row that has all key columns
that are NULL.
• Restricted ROWID is used to point to the rows of the table, since all rows
belong to the same segment.
Effect of DML Operations on an Index
The Oracle server maintains all the indexes when DML operations are
carried out on the table. Here is an explanation of the effect of a DML
command on an index:
• Insert operations result in the insertion of an index entry in the
appropriate block.
• Deleting a row results only in a logical deletion of the index entry. The
space used by the deleted row is not available for new entries until all the
entries in the block are deleted.
• Updates to the key columns result in a logical delete and an insert to the

13-5
Copyright  Oracle Corporation, 1998. All rights reserved.
KEY ROWID
EMPNO (BLOCK# ROW# FILE#)

1257 0000000F.0002.0001
2877 0000000F.0006.0001
4567 0000000F.0004.0001
6657 0000000F.0003.0001
8967 0000000F.0005.0001
9637 0000000F.0001.0001
9947 0000000F.0000.0001Reverse Key Index
Index on EMP (EMPNO)
EMP table
EMPNO ENAME JOB

7499 ALLEN SALESMAN
7369 SMITH CLERK
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGEROracle8: Database Administration 13-9


Table
Index
Block 10
Block 11
Block 12
File 3
13-10 Oracle8: Database Administration.
Lesson 13: Managing Indexes
As shown in the diagram, the leaf node of a bitmap index contains the
following:
•Anentry header, containing number of columns and lock information.
• Key values consisting of length and value pairs for each key column (In
the example, the key consists of only one column, and the first entry has
a key value of Blue.)
• Start ROWID, which in the example contains a file number 3, a block
number 10, and a row number 0
• End ROWID, which in the example includes a block number 12 and a
row number 8
•Abitmap segment consisting of a string of bits (The bit is set when the
corresponding row contains the key value and is unset when the row
does not contain the key value. The Oracle server uses a patented
compression technique to store bitmap segments.)
The start ROWID is the ROWID of the first row pointed to by the bitmap
segment of the bitmap
—that is, the first bit of the bitmap corresponds to that
ROWID, the second bit of the bitmap corresponds to the next row in the
block, and the end ROWID is a pointer to the last row in the table covered by

Copyright  Oracle Corporation, 1998. All rights reserved.
Comparing B-Tree and
Bitmap Indexes
B-tree
Suitable for high-cardinality
columns
Updates on keys relatively
inexpensive
Inefficient for queries
using OR predicates
Useful for OLTP
Bitmap
Suitable for low-cardinality
columns
Updates to key columns very
expensive
Efficient for queries
using OR predicates
Useful for DSS
13-12 Oracle8: Database Administration.
Lesson 13: Managing Indexes
Creating Indexes
An index can be created either in the account of the user who owns the table
or in a different account, although it is generally created in the same account
as the table.
Syntax
Use the following command to create a B-tree index:

column is the name of the column
ASC/ DESC isprovidedfor syntacticcompatibilitywith
other databases
TABLESPACE identifies the tablespace where the index
will be created
PCTFREE is the amount of space reserved in each
block (in percentage of total space minus
the blockheader) atthe timeof creationfor
accommodating new index entries
INITRANS specifies the number of transaction entries
preallocated in each block
(The default and the minimum value is 2.)
MAXTRANS limitsthenumberof transaction entriesthat
can be allocated to each block
(The default is 255.)
STORAGE identifiesthestorageclausethatdetermines
how extents are allocated to the index
LOGGING specifies that the creation of the index and
subsequent operations on the index are
logged in the redo log file
(This is the default.)
NOLOGGING specifies that the creation and certain types
of data loads are not logged in the redo log
file
NOSORT specifies that the rows are stored in the
database in ascending order, and therefore,
the Oracle server does not have to sort the
rows while creating the index
13-14 Oracle8: Database Administration


tool automatically define the storage and block utilization parameters based
on an estimate of the initial volume, the growth rate, the insert activity on the
table, and the order in which rows are inserted.
Oracle8: Database Administration 13-15.
Creating Indexes
Consider the following while creating an index:
• Indexes speed up query performance and slow down DML operations.
Always minimize the number of indexes needed on volatile tables.
• Place indexes in a separate tablespace
, not in a tablespace that has
rollback segments, temporary segments, and tables.
• To minimize fragmentation, use a few standard extent sizes that are
multiples of 5*DB_BLOCK_SIZE.
• There could be significant performance gain for large indexes by
avoiding redo generation. Consider using the NOLOGGING clause for
creating large indexes.
• Because index entries are smaller compared to the rows they index,
index blocks tend to have more entries per block. For this reason,
INITRANS should generally be higher on indexes than on the
corresponding tables.
13-9
Copyright  Oracle Corporation, 1998. All rights reserved.
Creating Indexes: Guidelines
• Balance query and DML needs
• Place in separate tablespace
• Use uniform extent sizes: multiples of
five blocks or MINIMUM EXTENT size