Oracle8i Concepts
Release 8.1.5

A67781-01

Library

Product

Contents

Prev

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 


A

aborting an instance, 5-10, 32-4
access control, 30-2
discretionary, 1-39
fine-grained access control, 30-22
password encryption, 29-7
privileges, 30-2
roles, 30-16
access methods, 23-34
cluster scans, 23-34
execution plans, 22-2
hash scans, 23-35
index scans, 23-35
table scans, 23-34
access paths
cluster join, 23-40
composite index, 23-42
defined, 22-4
hash cluster key, 23-41
indexed cluster key, 23-41
list of, 23-36
optimization, 23-33
single row by cluster join, 23-38
single row by hash cluster key (with unique key), 23-39
single row by rowid, 23-38
single row by unique or primary key, 23-40
ADMIN OPTION
roles, 30-19
system privileges, 30-3
with EXECUTE ANY TYPE, 14-14
administrator privileges, 5-3
connections audited, 31-5
OUTLN schema, 22-6
statement execution not audited, 31-5
ADT, See object type
Advanced Queuing (Oracle AQ), 19-1
exception handling, 19-11
exporting queue tables, 19-12
message queuing, 19-2
publish/subscribe support, 19-10
event publication, 20-18
queue monitor process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queue tables, 19-4
recipients, 19-5
rule-based subscriptions, 19-5, 19-6
subscription lists, 19-5
remote databases, 19-9
advanced replication
hybrid configurations, 34-13
multimaster configuration, 34-6
procedural replication, 34-15
synchronous propagation, 34-16
uses for, 34-6, 34-12
Advanced Security option, 33-18
affinity
parallel DML, 26-49
partitions, 26-48
AFTER triggers, 20-9
defined, 20-9
when fired, 20-21
agents for queuing, 19-4
ALERT files, 8-14
ARCn processes, 8-12
redo log, 8-9
aliases
qualifying column names, 14-8, 14-9
qualifying subqueries (inline views), 10-16
ALL, 23-6
ALL_ views, 2-6
ALL_ROWS hint, 23-33
ALL_UPDATABLE_COLUMNS view, 10-15
ALTER ANY TYPE privilege, 14-13
See also privileges
ALTER command, 16-4
auditing partitions, 11-63
ALTER DATABASE command
standby database, 5-7
ALTER INDEX command
no-logging mode for SPLIT PARTITION, 11-58, 25-7
partition attributes, 11-37
REBUILD PARTITION, 11-60
ALTER SESSION
FORCE PARALLEL DDL, 26-24, 26-27
create or rebuild index, 26-25, 26-27
create table as select, 26-26, 26-27
move or split partition, 26-25, 26-28
FORCE PARALLEL DML
insert, 26-23, 26-24, 26-27
update and delete, 26-22, 26-27
ALTER SESSION command, 16-5
dynamic parameters, 5-5
ENABLE PARALLEL DML, 26-38
HASH_JOIN_ENABLED, 24-7
OPTIMIZER_GOAL, 23-32
SET CONSTRAINTS DEFERRED, 28-21
transaction isolation level, 27-7, 27-31
ALTER SYSTEM command, 16-5
dynamic parameters, 5-5
LOG_ARCHIVE_MAX_PROCESSES, 8-12, 32-20
SWITCH LOGFILE option, 8-12
ALTER TABLE command
add or coalesce hash partition, 11-17
auditing, 31-7
CACHE clause, 7-4
DEALLOCATE UNUSED, 4-14
disable or enable constraints, 28-22
DROP COLUMN, 10-6
EXCHANGE PARTITION, 11-11
MERGE PARTITIONS, 11-16
MODIFY CONSTRAINT, 28-23
no-logging mode for SPLIT PARTITION, 11-58, 25-7
partition attributes, 11-27
triggers and, 20-6
UNUSED column, 10-6
validate or novalidate constraints, 28-22
ALTER TABLESPACE command
READ ONLY, 3-10
READ WRITE, 3-11
TEMPORARY or PERMANENT, 3-13
ALTER USER command
temporary segments and, 4-18
ALWAYS_ANTI_JOIN parameter, 24-13
ALWAYS_SEMI_JOIN parameter, 24-13
ANALYZE command, 16-4
creating histograms, 22-11
estimated statistics, 22-14
partition statistics, 11-14
shared pool and, 7-11
anonymous PL/SQL blocks, 16-15, 18-9
applications, 16-17
calling a stored procedure, 16-19
contrasted with stored procedures, 18-9
dynamic SQL, 16-19
performance, 18-9
ANSI SQL standard
datatypes of, 12-22
Oracle certification, 1-3
ANSI/ISO SQL standard, 1-3
composite foreign keys, 28-16
data concurrency, 27-2
isolation levels, 27-10
anti-joins, 24-13
ANY, 23-6
application context, 30-24
applications
application vs. database triggers, 20-3
can find constraint violations, 28-6
data dictionary references, 2-4
data warehousing, 10-32
star queries, 24-14
database access through, 8-2
decision support systems (DSS), 10-33
parallel SQL, 26-2, 26-31
dependencies of, 21-11
direct-load INSERT, 26-38
discrete transactions, 17-8
enhancing security with, 1-42, 28-5
index-organized tables, 10-40
information retrieval (IR), 10-40
network communication and, 6-5
object dependencies and, 21-13
online analytical processing (OLAP), 10-42
online transaction processing (OLTP)
reverse key indexes, 10-31
parallel DML, 26-37
processes, 8-4
program interface and, 8-25
roles and, 30-18
security
application context, 30-24
sharing code, 7-17
spatial applications, 10-42
transaction termination and, 17-5
AQ
exporting queue tables, 19-12
message queuing, 19-2
publish/subscribe support, 19-10
event publication, 20-18
queue monitor process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queue tables, 19-4
recipients, 19-5
rule-based subscriptions, 19-5, 19-6
subscription lists, 19-5
remote databases, 19-9
AQ_ADMINISTRATOR role, 19-7
AQ_TM_PROCESS parameter, 19-6, 19-7
architecture
client/server, 1-33
MPP, 26-49
of Oracle, 1-14
SMP, 26-49
archived redo log, 1-48
automatic archiving, 32-19
enabling, 32-18
manual archiving, 32-20
ARCHIVELOG mode
archiver process (ARCn) and, 1-19, 8-12, 32-18
defined, 32-18
overview, 1-48
partial database backups, 1-50, 32-24
whole database backups, 32-24
archiver process (ARCn)
automatic archiving, 32-19
described, 1-19, 8-12
example, 32-18
multiple processes, 1-19, 8-12
not used for manual archiving, 32-21
trace file, 32-20
ARCn background process, 1-19, 8-12
See also archiver process
array processing, 16-13
arrays
size of VARRAYs, 13-11
variable (VARRAYs), 13-11
asynchronous I/O
parallel recovery and, 32-11
asynchronous processing, 19-2
atomic nulls, 14-3
attributes
leaf-level, 14-18
leaf-level scalar, 14-18
attributes of object types, 13-2, 13-4
AUDIT command, 16-4
locks, 27-29
audit trail
deleting data in dictionary, 2-5
auditing, 1-44, 31-1
audit options, 31-3
audit records, 31-3
audit trails, 31-3
database, 31-3
operating system, 31-5, 31-6
by access, 31-11
mandated for, 31-12
by session, 31-10
prohibited with, 31-12
connect with administrator privileges, 31-5
data dictionary used for, 2-5
database and OS usernames, 29-4
DDL statements, 31-7
described, 1-44, 31-2
distributed databases and, 31-6
DML statements, 31-7
partitioned tables and indexes, 11-63
privilege use, 31-2, 31-7
range of focus, 31-3, 31-9
schema object, 31-2, 31-8
security and, 31-6
startup and shutdown, 31-5
statement, 31-2, 31-7
successful executions, 31-9
transaction independence, 31-4
types of, 31-2
unsuccessful executions, 31-9
user, 31-12
when options take effect, 31-6
authentication
database administrators, 29-12
described, 29-3
multi-tier, 29-9
network, 29-4
operating system, 29-4
Oracle, 29-7
public key infrastructure, 29-5
remote, 29-6
Automated Standby Database, 32-26

B

B*-tree indexes, 10-27
bitmap indexes vs., 10-32, 10-33
index-organized tables, 10-36
back-ends, 6-2
background processes, 1-18, 8-5
described, 8-5
diagrammed, 8-6
overview of, 1-18
trace files for, 8-14
See also processes
backups
control files, 32-25
datafiles, 32-25
for read-only tablespaces, 32-26
overview of, 1-45, 32-23
parallel, 32-16
partial, 1-50, 32-24
Recovery Manager, 1-51, 32-15
types of, 1-49
using Export to supplement, 32-25
whole database backup, 1-50, 32-23
base tables, 1-24
data dictionary, 2-2
See also views
basic replication
uses of, 34-11
BEFORE triggers, 20-9
defined, 20-9
when fired, 20-21
BETWEEN, 23-7
BFILE datatype, 12-14
binary data
BFILEs, 12-14
BLOBs, 12-13
RAW and LONG RAW, 12-14
bind variables
optimization, 23-52
user-defined types, 13-14
bitmap indexes, 10-32
cardinality, 10-33
nulls and, 10-8, 10-35
parallel query and DML, 10-33
partitioned tables, 11-14
scans of, 23-36
star transformation, 24-16
bitmap tablespace management, 3-8
temporary tablespaces, 3-13
BLOBs, 12-13
block
anonymous, 16-15, 18-9
block-level recovery, 32-14
database, 4-3
See also data blocks
block sampling, 22-14
block server process (BSP), 27-6
blocking transactions, 27-11
block-level recovery, 27-21, 32-14
BOOLEAN datatype, 12-2
branch blocks, 10-28
BSP background process, 27-6
buffer cache, 7-3, 8-8
extended buffer cache (32-bit), 7-14
multiple buffer pools, 7-5
buffer pools, 7-5
BUFFER_POOL_KEEP parameter, 7-5
BUFFER_POOL_RECYCLE parameter, 7-5
buffers
database buffer cache, 1-16, 7-3, 8-8
Fast-Start Checkpointing, 32-13
incremental checkpoint, 8-8
redo log buffer, 1-16, 7-6
BUILD_PART_INDEX procedure, 11-30
business rules
enforcing in application code, 28-5
enforcing using stored procedures, 28-5
enforcing with constraints, 1-58, 28-1
advantages of, 28-5
enforcing with triggers, 1-59

C

CACHE clause, 7-4
Cache Fusion
read consistency, 27-6
caches
buffer cache, 7-3
multiple buffer pools, 7-5
cache hit, 7-4
cache miss, 7-4
data dictionary, 2-4, 7-10
location of, 7-6
database buffer, 1-16
library cache, 7-6, 7-7, 7-10
object cache, 13-14, 14-16
object views, 15-4
private SQL area, 7-8
shared SQL area, 7-6, 7-8
writing of buffers, 8-8
calls
Oracle call interface, 8-25
remote procedure, 33-12
cannot serialize access, 27-11
capture avoidance rule, 14-8
cardinality, 10-33
Cartesian products, 23-3
CASCADE actions
DELETE statements and, 28-17
catalog, replication, 34-14
century, 12-12
certificate authority, 29-5
chaining of rows, 4-10, 10-5
CHAR datatype, 12-5
blank-padded comparison semantics, 12-5
character sets
CLOB and NCLOB datatypes, 12-13
column lengths, 12-6
for various languages, 5-5
NCHAR and NVARCHAR2, 12-6
CHARTOROWID function, 12-23
CHECK constraints, 28-17
checking mechanism, 28-20
defined, 28-17
multiple constraints on a column, 28-18
partially null foreign keys, 28-16
partition views, 11-11
subqueries prohibited in, 28-18
checkpoint process (CKPT), 1-19, 8-11
checkpoints
checkpoint process (CKPT), 1-19, 8-11
control files and, 32-22
DBWn process, 8-8, 8-11
Fast-Start Checkpointing, 32-13
incremental, 8-8
statistics on, 8-11
CHOOSE hint, 23-33
CKPT background process, 1-19, 8-11
client/server architectures, 6-2
clients, 1-33
diagrammed, 6-2
direct and indirect connections, 33-2
distributed databases and, 33-2
distributed processing in, 6-2
overview of, 1-33, 6-2
program interface, 8-25
CLOB datatype, 12-13
clone databases
mounting, 5-7
cluster joins, 24-5
cluster keys, 1-27, 10-48
clustered computer systems
Oracle Parallel Server, 5-3
clusters
cannot be partitioned, 11-2
choosing data to cluster, 10-48
defined, 1-27
dictionary locks and, 27-29
hash, 10-50
allocation of space for, 10-55
collision resolution, 10-53
contrasted with index, 10-51
root blocks, 10-55
scans of, 23-35, 23-39, 23-41
single-table, 10-57
storage of, 10-51
index, 10-50
contrasted with hash, 10-51
scans of, 23-41
indexes on, 10-21
cannot be partitioned, 11-2
joins and, 10-48, 23-38, 23-40, 24-5
keys, 1-27, 10-48, 10-49
affect indexing of nulls, 10-8
overview of, 10-46
performance considerations of, 10-48
rowids and, 10-7
scans of, 7-4, 23-34, 23-38
hash, 23-39, 23-41
joins, 23-40
setting parameters of, 10-49
storage format of, 10-49
storage parameters of, 10-4
coalescing extents, 4-15
coalescing free space
extents, 4-13
SMON process, 1-19, 8-11
within data blocks, 4-9
collections, 13-10
index-organized tables, 10-38
key compression, 10-30
nested tables, 13-12
variable arrays (VARRAYs), 13-11
columns
cardinality, 10-33
column names
qualifying in queries, 14-8, 14-9
column objects, 13-8
indexes, 14-6
default values for, 10-8
defined, 1-23
described, 10-3
dropping, 10-6
integrity constraints, 10-4, 10-8, 28-4, 28-7
maximum in concatenated indexes, 10-23
maximum in view or table, 10-12
nested tables, 10-9
order of, 10-7
prohibiting nulls in, 28-7
pseudocolumns
ROWID, 12-15
ROWNUM, 23-16, 23-25, 23-49
USER, 30-7
selectivity, 22-8
histograms, 22-8, 22-10
unused, 10-6
COMMENT command, 16-4
COMMIT command, 16-5
ending a transaction, 17-2, 17-4
fast commit, 8-10
implied by DDL, 17-2, 17-4
two-phase commit and, 17-7, 33-13
two-phase commit in parallel DML, 26-40
committing transactions
defined, 17-2
fast commit, 8-10
group commits, 8-10
implementation, 8-10
overview, 1-54
parallel DML, 26-40
communication protocols, 6-5
comparison methods, 13-6
compatibility, 1-3
compatibility levels
transportable tablespaces, 3-14
COMPATIBLE parameter
read-only tablespaces, 3-11
compilation of object types, 14-17
compiled PL/SQL, 18-17
advantages of, 18-8
procedures, 18-9
pseudocode, 18-18, 20-25
recompiling, 18-19
shared pool, 16-16
triggers, 20-25
compiled triggers, 20-25
complex view merging, 23-17
composite indexes, 10-22
compression of free space in data blocks, 4-9
compression, index key, 10-29
concatenated indexes, 10-22
concurrency
defined, 1-30
described, 27-2
direct-load INSERT, 25-11
enforced with locks, 1-32
limits on
per database, 29-20
per user, 29-17
partition maintenance, 11-50
restrictions on, 1-43, 25-11
transactions and, 27-15
configuration of a database
parameter file, 5-4
process structure, 8-2
conflicts
procedural replication, 34-16
CONNECT BY clause
optimizing view queries, 23-16
CONNECT INTERNAL, 5-3
CONNECT role, 30-22
user-defined types, 14-13, 14-14
connectibility, 1-2
connections
defined, 8-4
embedded SQL, 16-6
listener process and, 6-6, 8-14
restricting, 5-6
sessions contrasted with, 8-4
usernames, 29-2
with administrator privileges, 5-3
audit records, 31-5
consistency of data, 1-55
multiversion consistency model, 1-31
See also read consistency
constants
comparisons and, 23-5
evaluation of expressions, 23-5
in stored procedures, 16-17
when computed, 23-5
constraints, 1-58
alternatives to, 28-5
applications can find violations, 28-6
CHECK, 28-17
default values and, 28-20
defined, 10-4
disabling temporarily, 28-7
effect on performance, 28-6
ENABLE or DISABLE, 28-22
enforced with indexes, 10-23
PRIMARY KEY, 28-12
UNIQUE, 28-10
FOREIGN KEY, 1-59, 28-13
mechanisms of enforcement, 28-18
modifying, 28-23
NOT NULL, 28-7, 28-11
object tables, 14-5
overview, 1-58
parallel create table, 26-25
PRIMARY KEY, 1-59, 28-11
prohibited in views, 10-12
referential
effect of updates, 28-16
self-referencing, 28-15
triggers cannot violate, 20-21
triggers contrasted with, 20-5
types listed, 1-58, 28-1
UNIQUE key, 1-59, 28-8
partially null, 28-11
VALIDATE or NOVALIDATE, 28-22
what happens when violated, 28-5
when evaluated, 10-8
constructor methods, 1-57, 13-6, 14-18
literal invocation of, 14-4
contention
for data
deadlocks, 8-19, 27-17
lock escalation does not occur, 27-17
for rollback segments, 4-21
control files, 1-13, 32-21
backing up, 32-25
changes recorded, 32-22
checkpoints and, 32-22
contents, 32-21
how specified, 5-4
multiplexed, 1-49, 32-22
overview, 1-13, 32-21
physical database structure, 1-5
recovery and, 1-49
used in mounting database, 5-6
converting data
ANSI datatypes, 12-22
program interface, 8-25
SQL/DS and DB2 datatypes, 12-22
correlation names
inline views, 10-16
cost based optimization, 33-10
cost-based optimization, 22-7
extensible optimization, 22-16
histograms, 22-8
query rewrite, 10-17
selectivity of predicates, 22-8
histograms, 22-8, 22-10
user-defined, 22-17
star queries, 24-14
statistics, 22-8, 23-32
user-defined, 22-17
user-defined costs, 22-17
CPU time limit, 29-17
crash recovery, 32-4, 32-13
instance failure, 1-46, 5-10, 32-4
opening a database, 5-8
read-only tablespaces, 32-6
required after aborting instance, 5-10
SMON process, 1-19, 8-11
CREATE ANY TYPE privilege, 14-13
See also privileges
CREATE CLUSTER command
HASHKEYS clause, 10-53, 10-57
SINGLE TABLE HASHKEYS, 10-57
storage parameters, 4-17
CREATE command, 16-4
CREATE FUNCTION command, 18-17
CREATE INDEX command
no-logging mode, 11-58, 25-7
object types, 14-6
partition attributes, 11-37
rules of parallelism, 26-25
storage parameters, 4-17
temporary segments and, 4-18
CREATE OUTLINE statement, 22-6
CREATE PACKAGE BODY command, 18-12, 18-17
CREATE PACKAGE command
examples, 18-12, 20-10
locks, 27-29
package name, 18-17
CREATE PROCEDURE command
example, 18-6
locks, 27-29
procedure name, 18-17
CREATE SYNONYM command
locks, 27-29
CREATE TABLE AS SELECT
rules of parallelism
index-organized tables, 26-31
CREATE TABLE command
AS SELECT
decision support systems, 26-31
direct-load INSERT vs., 25-2
no-logging mode, 11-58, 25-7
rules of parallelism, 26-25
space fragmentation, 26-33
temporary storage space, 26-33
auditing, 31-7, 31-10
CACHE clause, 7-4
enable or disable constraints, 28-22
examples
column objects, 13-5, 14-8
nested tables, 13-12
object tables, 13-8, 13-12, 14-5, 14-8
locks, 27-29
parallelism, 26-31
index-organized tables, 26-31
partition attributes, 11-27
storage parameters, 4-17
triggers and, 20-6
CREATE TABLESPACE command
TEMPORARY clause, 3-13
CREATE TEMPORARY TABLE command, 10-10
CREATE TEMPORARY TABLESPACE command, 3-13
CREATE TRIGGER command
compiled and stored, 20-25
examples, 20-11, 20-14, 20-24
object tables, 14-7
locks, 27-29
CREATE TYPE command
incomplete types, 14-16
nested tables, 13-4, 13-12, 14-4
object types, 13-4, 14-3, 14-4, 14-8
object views, 15-3
VARRAYs, 13-11
CREATE TYPE privilege, 14-13
See also privileges
CREATE USER command
temporary segments and, 4-18
CREATE VIEW command
examples, 20-14
object views, 15-3
locks, 27-29
CREATE_STORED_OUTLINES session parameter, 22-6
cross joins, 23-3
current user, 18-10
cursors
creating, 16-11
defined, 16-6
embedded SQL, 16-5
maximum number of, 16-7
object dependencies and, 21-10
opening, 7-9, 16-7
overview of, 1-17
private SQL areas and, 7-9, 16-6
recursive, 16-7
recursive SQL and, 16-7
stored procedures and, 16-18

D

dangling REFs, 13-9, 13-10
data
access to, 1-51
control of, 29-2
fine-grained access control, 30-22
message queues, 19-7
security domains, 29-2
concurrent access to, 27-2
consistency of
defined, 1-55
examples of lock behavior, 27-32
locks, 27-3
manual locking, 27-31
read consistency, 1-31
repeatable reads, 27-6
transaction level, 27-6
underlying principles, 27-15
distributed manipulation of, 1-35
how stored in tables, 10-4
integrity of, 1-30, 10-4, 28-2
CHECK constraints, 28-17
enforcing, 28-4, 28-5
overview, 1-57
parallel DML restrictions, 26-45
referential, 28-3
two-phase commit, 1-35
types, 28-3
locks on, 27-20
replicating, 1-36
data blocks, 1-10, 4-2
allocating for extents, 4-13
block-level recovery, 32-14
cached in memory, 8-8
clustered, 10-49
coalescing extents, 4-13
coalescing free space in blocks, 4-9
controlling free space in, 4-5
format, 4-3
free lists and, 4-9
hash keys and, 10-55
how rows stored in, 10-5
overview, 4-2
read-only transactions and, 27-32
row directory, 10-5
shared in clusters, 10-46
shown in rowids, 12-17, 12-18
space available for inserted rows, 4-9
stored in the buffer cache, 7-3
writing to disk, 8-8
data conversion
ANSI datatypes, 12-22
program interface, 8-25
SQL/DS and DB2 datatypes, 12-22
Data Definition Language (DDL)
auditing, 31-7
commit implied by, 17-4
defined, 1-52
described, 16-4
embedding in PL/SQL, 16-19
locks, 27-28
parallel DDL, 26-3
parsing with DBMS_SQL, 16-19
processing statements, 16-14
roles and privileges, 30-20
data dictionary
access to, 2-2
adding objects to, 2-4
ALL prefixed views, 2-6
audit trail (SYS.AUD$), 2-5
backups, 32-25
cache, 7-10
location of, 7-6
content of, 2-2, 7-10
procedures, 18-18
datafile 1, 3-6, 32-25
DBA prefixed views, 2-6
defined, 1-30, 2-2
dependencies tracked by, 21-3
dictionary-managed tablespaces, 3-7
DUAL table, 2-7
dynamic performance tables, 2-7
locks, 27-28
owner of, 2-3
prefixes to views of, 2-5
public synonyms for, 2-4
row cache and, 7-10
statistics in, 22-14, 23-32
partition statistics, 11-14
structure of, 2-2
SYSTEM tablespace, 2-2, 2-5, 3-6
updates of, 2-5
USER prefixed views, 2-6
uses of, 2-3
table and column definitions, 16-11
validity of procedures, 18-18
views used in optimization, 22-14
data locks
conversion, 27-16
duration of, 27-15
escalation, 27-16
Data Manipulation Language (DML)
auditing, 31-7
defined, 1-52
described, 16-3
distributed transactions, 33-11
locks acquired by, 27-25
parallel DML, 26-3, 26-35
partition locks, 11-45
privileges controlling, 30-5
processing statements, 16-10
serializable isolation for subqueries, 27-14
transaction model for parallel DML, 26-39
triggers and, 20-3, 20-23
data models, 1-22
data object number
extended rowid, 12-17
data segments, 1-11, 4-16, 10-4
data warehousing
basic replication for, 34-12
bitmap indexes, 10-32
dimension schema objects, 1-29, 10-18
dimensions, 24-14
hierarchies, 1-29, 10-18
materialized views, 10-17
refreshing table data, 26-37
star queries, 24-14
summaries, 10-17
Database, 5-3
database administrators (DBAs)
authentication, 29-12
data dictionary views, 2-6
DBA role, 14-13, 30-22
password files, 29-13
responsible for backup and recovery, 32-2
database buffers
after committing transactions, 17-6
buffer cache, 7-3, 8-8
clean, 8-8
committing transactions, 8-10
defined, 1-16, 7-3
dirty, 7-3, 8-8
free, 7-3
multiple buffer pools, 7-5
pinned, 7-3
size of cache, 7-5
writing of, 8-8
database links, 1-29
defined, 1-29
overview of, 33-6
partition-extended table names, 11-65
database management system (DBMS), 1-2
object-relational DBMS, 13-2
Oracle server, 1-4
principles, 1-22
database structures
control files, 1-13, 32-21
data blocks, 1-10, 4-2, 4-3
data dictionary, 1-30, 2-1
datafiles, 1-11, 3-1, 3-16
extents, 1-10, 4-2, 4-11
logical, 1-5, 1-9
memory, 1-14, 7-1
physical, 1-5
processes, 1-14, 1-17, 8-1
redo log files, 1-12, 32-7
revealing with rowids, 12-18
schema objects, 1-10, 10-2
segments, 1-10, 4-2, 4-16
tablespaces, 1-9, 3-1, 3-6
database triggers, 1-59, 20-1
See also triggers
database writer process (DBWn), 8-8
checkpoints, 8-8
defined, 8-8
least recently used algorithm (LRU), 8-8
media failure, 32-6
multiple DBWn processes, 8-8
overview of, 1-18
trace file, 32-6
when active, 8-8
write-ahead, 8-9
writing to disk at checkpoints, 8-11
databases
access control
overview, 1-51
password encryption, 29-7
security domains, 29-2
backing up, 1-50, 32-23
clone database, 5-7
closing, 5-10
aborting the instance, 5-10, 32-4
configuring, 5-4
contain schemas, 29-2
defined, 1-8, 1-9
dismounting, 5-10
distributed, 1-35, 33-1
changing global database name, 7-11
nodes of, 1-35
overview of, 1-33, 1-34, 33-1
site autonomy of, 33-16
statement optimization on, 23-30
table replication, 1-36
two-phase commit, 1-35
global database names, 33-4
limitations on usage, 29-15
managing
Enterprise Manager, 33-19
modes of archiving, 32-18
mounting, 5-6
name stored in control file, 32-21
open and closed, 5-2
opening, 5-7
acquiring rollback segments, 4-26
opening read-only, 5-9
recovery of, 1-45, 32-2
scalability, 6-4, 26-2, 26-37
shutting down, 5-9
size of
how determined, 3-4
standby, 5-7, 32-26
starting up, 5-2
forced, 5-10
structures
control files, 1-13, 32-21
data blocks, 1-10, 4-2, 4-3
data dictionary, 1-30, 2-1
datafiles, 1-11, 3-1, 3-16
extents, 1-10, 4-2, 4-11
logical, 1-5, 1-9, 4-1
memory, 1-14, 7-1
physical, 1-5, 1-11
processes, 1-14, 1-17, 8-1
redo log files, 1-12, 32-7
revealing with rowids, 12-18
schema objects, 1-10, 10-2
segments, 1-10, 4-2, 4-16
tablespaces, 1-9, 3-1, 3-6
datafile 1, 3-16
backup, 32-25
data dictionary, 3-6, 32-25
SYSTEM tablespace, 3-6, 3-16
datafiles
backing up, 32-25
contents of, 3-16
datafile 1, 3-6, 3-16
backup, 32-25
SYSTEM tablespace, 3-6, 3-16
in online or offline tablespaces, 3-17
named in control files, 32-21
overview of, 1-9, 1-12, 3-16
parallel recovery, 32-11
physical database structure, 1-5
read-only, 3-10
recovery, 32-6
read-only tablespaces and, 3-12
relationship to tablespaces, 3-2
shown in rowids, 12-17, 12-18
taking offline, 3-17
temporary, 3-17
unrecoverable, 32-17
datatypes, 12-2, 12-3
ANSI, 12-22
array types, 13-11
BOOLEAN, 12-2
CHAR, 12-5
character, 12-5, 12-13
collections, 13-10
conversions of
by program interface, 8-25
non-Oracle types, 12-22
Oracle to another Oracle type, 12-23
DATE, 12-10
DB2, 12-22
how they relate to tables, 10-3
in PL/SQL, 12-2
list of available, 12-2
LOB datatypes, 12-12
BFILE, 12-14
BLOB, 12-13
CLOB and NCLOB, 12-13
default logging mode, 25-7
LONG, 12-7
storage of, 10-7
multimedia, 13-3
NCHAR and NVARCHAR2, 12-6
nested tables, 10-9, 13-12
NUMBER, 12-8
object types, 1-22, 13-4
of columns, 1-23
RAW and LONG RAW, 12-14
ROWID, 12-15, 12-16
SQL/DS, 12-22
summary, 12-3
user-defined, 13-1, 13-3
statistics, 22-17
VARCHAR, 12-6
VARCHAR2, 12-5
DATE datatype, 12-10
arithmetic with, 12-11
changing default format of, 12-10
Julian dates, 12-11
midnight, 12-11
partition pruning, 11-22
partitioning, 11-14, 11-21
DB_BLOCK_BUFFERS parameter
buffer cache and, 7-5
system global area size and, 7-13
DB_BLOCK_LRU_LATCHES parameter, 8-8
DB_BLOCK_SIZE parameter
buffer cache and, 7-5
system global area size and, 7-13
DB_FILE_MULTIBLOCK_READ_COUNT parameter, 23-51
cost-based optimization, 24-9
DB_FILES parameter, 7-15
DB_NAME parameter, 32-22
DB_WRITER_PROCESSES parameter, 1-18, 8-8
DBA role, 30-22
user-defined types, 14-13
DBA_ views, 2-6
DBA_QUEUE_SCHEDULES view, 19-10
DBA_SYNONYMS.SQL script
using, 2-7
DBA_UPDATABLE_COLUMNS view, 10-15
DBMS, 1-2
general requirements, 1-51
object-relational DBMS, 13-2
DBMS_AQ package, 19-4
DBMS_AQADM package, 19-4, 19-7
DBMS_JOB package, 8-13
Oracle supplied packages, 18-16
DBMS_LOCK package, 27-39
Oracle supplied packages, 18-16
DBMS_PCLXUTIL package, 11-30
DBMS_RLS package
security policies, 30-23
uses definer rights, 30-8
DBMS_SQL package, 16-19
Oracle supplied packages, 18-16
parsing DDL statements, 16-19
DBMS_STATS package, 22-12
creating histograms, 22-11
estimated statistics, 22-14
partition statistics, 11-14
DBWn background process, 8-8
See also database writer process
DDL, 1-52, 16-4
See also Data Definition Language
dead transactions, 32-4
block-level recovery, 32-14
deadlocks
artificial, 8-19
avoiding, 27-19
defined, 27-17
detection of, 27-18
distributed transactions and, 27-18
deallocating extents, 4-14
decision support application
basic replication for, 34-11
decision support systems (DSS), 11-6
bitmap indexes, 10-33
disk striping, 26-48
materialized views, 10-17
parallel DML, 26-37
parallel SQL, 26-2, 26-31, 26-37
partitions, 11-6
performance, 11-9, 26-37
scoring tables, 26-38
dedicated servers, 8-22
defined, 1-18
examples of use, 8-24
multi-threaded servers vs., 8-16
default values, 10-8
constraints effect on, 10-8, 28-20
user-defined types, 14-4
deferred constraints
deferrable or nondeferrable, 28-21
initially deferred or immediate, 28-21
define phase of query processing, 16-12
definer rights, 18-9
name resolution, 18-19
procedure security, 30-7
degree of parallelism, 26-20, 26-23
between query operations, 26-13
parallel SQL, 26-7, 26-16
delete cascade constraint, 28-17
DELETE command, 16-4
foreign key references and, 28-16
freeing space in data blocks, 4-9
no-logging mode, 25-7
LOBs, 25-7
parallel DELETE, 26-21
triggers and, 20-2, 20-6
delete no action constraint, 28-16
DELETE privilege for object tables, 14-15, 14-16
denormalized tables, 1-29, 10-19
denormalized views
star schemas, 24-15
dependencies, 21-1
between schema objects, 21-2
function-based indexes, 10-26, 21-7
local, 21-10
managing, 21-1
non-existent referenced objects and, 21-8
object type definitions, 14-16, 14-17
on non-existence of other objects, 21-8
Oracle Forms triggers and, 21-13
privileges and, 21-6
remote objects and, 21-10
shared pool and, 21-10
dereferencing, 13-10
implicit, 13-10
describe phase of query processing, 16-12
DETERMINISTIC functions, 23-9
function-based indexes, 21-8
deterministic functions, 23-9
dictionary
See data dictionary
dictionary cache locks, 27-30
dictionary-managed tablespaces, 3-7
different-row writers block writers, 27-10
Digital's POLYCENTER Manager on Net View, 33-20
dimensions, 1-29, 10-18
attributes, 1-29, 10-19
hierarchies, 1-29, 10-18
join key, 1-29, 10-19
normalized or denormalized tables, 1-29, 10-19
star joins, 24-14
star queries, 24-14
direct-load INSERT, 25-2
logging mode, 25-5
parallel INSERT, 25-3
parallel load vs. parallel INSERT, 25-2
restrictions, 25-11, 26-43
serial INSERT, 25-3
space management, 25-8
Directory-enabled Oracle Security Manager, 29-5
dirty buffer, 7-3
Fast-Start Checkpointing, 32-13
incremental checkpoint, 8-8
dirty read, 27-2, 27-10
dirty write, 27-10
DISABLE constraints, 28-22
DISABLED indexes, 21-8
disaster recovery, 32-26
disconnected environments
as in advanced replication, 34-12
discrete transaction management
summary, 17-8
discretionary access control, 1-39, 29-2
disk affinity
parallel DML, 26-49
partitions, 26-48
disk failures, 1-46, 32-5
disk space
controlling allocation for tables, 10-4
datafiles used to allocate, 3-16
disk striping
affinity, 26-48
partitions, 11-9
dispatcher processes (Dnnn)
defined, 1-20
described, 8-14
limiting SGA space per session, 29-18
listener process and, 8-14
network protocols and, 8-14
prevent startup and shutdown, 8-20
response queue and, 8-17
user processes connect via Net8, 8-14, 8-16
DISTINCT operator
optimizing views, 23-17
distributed databases, 33-1
auditing and, 31-6
client/server architectures and, 6-2
database links, 33-6
deadlocks and, 27-18
dependent schema objects and, 21-10
diagrammed, 33-2
different Oracle versions, 33-7
distributed queries, 33-11
distributed updates, 33-11
global object names, 33-6
heterogeneous, 33-8
job queue processes (SNPn), 1-20, 8-13
management tools, 33-19
message propagation, 19-9
nodes of, 33-2
overview of, 1-34, 33-2
recoverer process (RECO) and, 8-12
remote dependencies, 21-11
remote queries and updates, 33-11
server can also be client in, 6-2
site autonomy of, 33-16
statement optimization on, 23-30
table replication, 1-36
transparency of, 33-14
two-phase commit, 1-35, 33-13
distributed processing environment
client/server architecture in, 1-33, 6-2
data manipulation statements, 16-10
described, 1-33, 6-2
distributed databases vs., 33-7
materialized views (snapshots), 10-17
distributed query optimization, 33-10
distributed transactions
defined, 33-12
distributed statements, 23-4
optimizing, 23-30
parallel DDL restrictions, 26-28
parallel DML restrictions, 26-28, 26-46
routing statements to nodes, 16-11
sample table scan not supported, 23-34
two-phase commit and, 1-35, 17-7
DISTRIBUTED_TRANSACTIONS parameter, 8-12
distributing application loads
as in advanced replication, 34-7
DML, 1-52, 16-3
See also Data Manipulation Language
DML subpartition locks, 11-46
Dnnn background processes, 8-14
See also dispatcher processes
domain index, 10-43
domain indexes
extensible optimization, 22-16
user-defined statistics, 22-17
drivers, 8-26
DROP ANY TYPE privilege, 14-13
See also privileges
DROP COLUMN clause, 10-6
DROP command, 16-4
DROP TABLE command
auditing, 31-7
triggers and, 20-6
DROP TYPE command
dependencies and, 14-17
FORCE option, 14-17
DSS database
disk striping, 26-48
parallel DML, 26-37
partitioning indexes, 11-37
partitions, 11-6
performance, 11-9
scoring tables, 26-38
DUAL table, 2-7
dump files
Export and Import, 14-20
dynamic partitioning, 26-6
dynamic performance tables (V$ tables), 2-7
dynamic predicates
in security policies, 30-23
dynamic SQL
DBMS_SQL package, 16-19
embedded, 16-19
name resolution, 18-20

E

embedded SQL statements, 1-53, 16-5
dynamic SQL in PL/SQL, 16-19
ENABLE constraints, 28-22
Enterprise Manager, 33-19
ALERT file, 8-15
checkpoint statistics, 8-11
distributed databases, 33-19
executing a package, 18-6
executing a procedure, 18-4
granting roles, 30-18
granting system privileges, 30-3
lock and latch monitors, 27-29
parallel recovery, 32-11
PL/SQL, 16-17, 16-19
schema object privileges, 30-4
showing size of SGA, 7-13
shutdown, 5-9, 5-10
SQL statements, 16-2
startup, 5-5
statistics monitor, 29-19
equijoins
cluster joins, 24-5
defined, 23-3
hash joins, 24-7
sort-merge, 24-4
equipartitioning, 11-24
examples, 11-25, 11-30, 11-32
LOB columns, 11-38
local indexes, 11-29
on one dimension, 11-24
overflow of index-organized tables, 11-42, 11-44
range partitioning, 11-24
errors
in embedded SQL, 16-6
tracked in trace files, 8-14
exceptions
during trigger execution, 20-23
raising, 16-18
stored procedures and, 16-18
EXCHANGE PARTITION, 11-11
exclusive locks
row locks (TX), 27-20
RX locks, 27-23
table locks (TM), 27-21
exclusive mode, 4-27
EXECUTE ANY TYPE privilege, 14-13, 14-14
See also privileges
EXECUTE privilege
user-defined types, 14-14
verifying user access, 18-18
See also privileges
EXECUTE user-defined type, 14-13
execution plan
accessing views, 23-19, 23-22, 23-23
complex statements, 23-14
compound queries, 23-27, 23-28, 23-29
joining views, 23-25
joins, 24-2, 24-8
OR operators, 23-12
star transformation, 24-18
execution plans
examples, 23-14
execution sequence of, 22-5
EXPLAIN PLAN, 16-4
location of, 7-8
overview of, 22-2
parsing SQL, 16-11
partitions and partition views, 11-11, 11-14
plan stability, 22-6
viewing, 22-4
EXP_FULL_DATABASE role, 30-22
EXPLAIN PLAN command, 16-4
access paths, 23-38, 23-39, 23-40, 23-41, 23-42, 23-43, 23-44, 23-45, 23-46, 23-47, 23-48, 23-49, 23-50
partition pruning, 11-22
star query, 24-17
star transformation, 24-18
explicit locking, 27-31
Export utility, 1-5
copying statistics, 22-8
partition maintenance operations, 11-48
use in backups, 32-25
user-defined types, 14-20
extended rowid format, 12-17
extensible optimization, 22-16
user-defined costs, 22-17
user-defined selectivity, 22-17
user-defined statistics, 22-17
extents
allocating, 4-12
allocating data blocks for, 4-13
allocation to rollback segments
after segment creation, 4-24
at segment creation, 4-22
allocation, how performed, 4-13
as collections of data blocks, 4-11
coalescing, 4-15
deallocation
from rollback segments, 4-25
when performed, 4-14
defined, 4-2
dictionary managed, 3-7
dropping rollback segments and, 4-25
in rollback segments
changing current, 4-22
incremental, 4-11
locally managed, 3-8
managing, 4-12
materialized views, 4-15
overview of, 4-11
parallel DDL, 26-33
parallel INSERT
storage parameters, 25-8
external procedures, 16-20, 18-11
external reference, 18-10
name resolution, 18-19

F

fact tables
star joins, 24-14
star queries, 24-14
failover sites
as in advanced replication, 34-6
failures, 32-2
archiving redo log files, 32-20
database buffers and, 32-8
described, 1-45, 32-2
instance, 1-46, 32-4
recovery from, 5-8, 5-10, 32-4
internal errors
tracked in trace files, 8-14
media, 1-46, 32-5
network, 32-3
safeguards provided, 32-6
statement and process, 1-45, 8-11, 32-2
survivability, 32-26
user error, 1-45, 32-2
See also recovery
fast commit, 8-10
fast full index scans, 23-36
fast refresh, 10-18
FAST_START_IO_TARGET parameter, 32-13
Fast-Start Checkpointing, 32-13
Fast-Start On-Demand Rollback, 32-10
Fast-Start Parallel Rollback, 32-14
Fast-Start Recovery, 32-13
fetching rows in a query, 16-14
embedded SQL, 16-6
file management locks, 27-30
files
ALERT and trace files, 8-9, 8-14
Export and Import dump file, 14-20
initialization parameter, 5-4, 5-5
LISTENER.ORA, 6-6
operating system, 1-5
Oracle database, 1-9, 1-11, 32-6
password, 29-13
administrator privileges, 5-3
See also control files, datafiles, redo log files
fine-grained access control, 30-22
FIPS standard, 16-6
FIRST_ROWS hint, 23-33
fixed views, 2-7
flagging of nonstandard features, 16-6
FORCE option
object type dependencies, 14-17
FORCE PARALLEL DDL option, 26-24, 26-27
create or rebuild index, 26-25, 26-27
create table as select, 26-26, 26-27
move or split partition, 26-25, 26-28
FORCE PARALLEL DML option
insert, 26-23, 26-24, 26-27
update and delete, 26-22, 26-27
FOREIGN KEY constraints
changes in parent key values, 28-16
constraint checking, 28-20
deleting parent table rows and, 28-17
maximum number of columns in, 28-13
nulls and, 28-15
updating parent key tables, 28-16
foreign keys, 1-58
defined, 1-59
partially null, 28-16
privilege to use parent key, 30-5
fragmentation
parallel DDL, 26-33
free lists, 4-9
free space
coalescing extents, 4-13
SMON process, 1-19, 8-11
coalescing within data blocks, 4-9
free lists, 4-9
parameters for data blocks, 4-5
section of data blocks, 4-5
front-ends, 6-2
full index scans, 23-35
full table scans, 23-34, 23-48
LRU algorithm and, 7-4
multiblock reads, 23-51
parallel execution, 26-5, 26-6
rule-based optimizer, 23-54
selectivity and, 23-51
function-based indexes, 10-24
dependencies, 10-26, 21-7
DISABLED, 21-8
privileges, 10-26, 21-8
UNUSABLE, 21-8
functions
function-based indexes, 10-24
hash functions, 10-54
Java
parallel execution, 26-46
PL/SQL, 18-2, 18-6
contrasted with procedures, 1-56, 18-2
DETERMINISTIC, 21-8, 23-9
deterministic, 23-9
parallel execution, 26-46
privileges for, 30-7
roles, 30-20
See also procedures
SQL, 16-2
COUNT, 10-35
default column values, 10-8
in CHECK constraints, 28-18
in views, 10-14
NVL, 10-8
optimizing view queries, 23-23
user-defined
extensible optimization, 22-16
fuzzy reads, 27-3

G

gateways, 33-8
global database names
shared pool and, 7-11
global indexes
partitioning, 11-32
managing partitions, 11-33, 11-60
summary of index types, 11-34
global schema object names, 1-29, 33-6
global user, 18-21, 33-18
current user links, 18-21
GRANT ANY PRIVILEGE system privilege, 30-3
GRANT command, 16-4
locks, 27-29
GRANT option for EXECUTE privilege, 14-14
granting
execute user-defined type, 14-14
privileges and roles, 30-3
GROUP BY clause
optimizing views, 23-17
temporary tablespaces, 3-12
group commits, 8-10
groups, instance, 26-19
guesses in logical rowids, 12-20
staleness, 12-21
statistics for, 12-21

H

handles for SQL statements, 1-17, 7-9
hash clusters, 1-29, 10-50
allocation of space for, 10-55
collision resolution, 10-53
contrasted with index, 10-51
overview of, 1-29
root blocks, 10-55
scans of, 23-35, 23-39, 23-41
single-table hash clusters, 10-57
storage of, 10-51
hash join, 24-7
HASH_AREA_SIZE parameter, 24-8
HASH_MULTIBLOCK_IO_COUNT parameter, 24-8
index join, 23-36, 23-49
HASH_AJ hint, 24-13
HASH_AREA_SIZE parameter, 24-8
HASH_JOIN_ENABLED parameter, 24-7
HASH_MULTIBLOCK_IO_COUNT parameter, 24-8
HASH_SJ hint, 24-13
HASHKEYS parameter, 10-53, 10-57
headers
of data blocks, 4-4
of row pieces, 10-5
heterogeneous distributed databases, 33-8
Heterogeneous Services, 33-8
agents, 33-9
HEXTORAW function, 12-23
HI_SHARED_MEMORY_ADDRESS parameter, 7-13
hierarchies, 1-29, 10-18
join key, 1-29, 10-19
levels, 1-29, 10-18
high water mark
direct-load INSERT, 25-3
HIGH_VALUE statistics, 23-52
hints
cannot override sample access path, 23-50
extensible optimization, 22-16
INDEX, 24-15
INDEX_FFS, 23-36
INDEX_JOIN, 23-36
MERGE, 23-17
MERGE_AJ and HASH_AJ, 24-13
MERGE_SJ and HASH_SJ, 24-13
ORDERED, 24-9, 24-15
overriding optimizer choice, 23-50
overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 23-33
PARALLEL, 26-17
PARALLEL_INDEX, 26-17
PUSH_JOIN_PRED, 24-12
STAR, 24-15
USE_HASH, 24-7
histograms, 22-8
historical database
maintenance operations, 11-48
partitions, 11-6
HP's OpenView, 33-20
hybrid configurations
advanced replication, 34-13

I

IBM's NetView/6000, 33-20
ILMS, 16-20
immediate constraints, 28-20
IMP_FULL_DATABASE role, 30-22
implicit dereferencing, 13-10
Import utility, 1-6
copying statistics, 22-8
partition maintenance operations, 11-48
use in recovery, 32-25
user-defined types, 14-20
in basic replication, 34-12
IN operator, 23-5
merging views, 23-18
IN subquery, 23-17
incomplete object types, 14-17
incremental checkpoint, 8-8
incremental refresh, 10-18
index joins, 23-36, 23-49
index segments, 1-11, 4-17
INDEX_FFS hint, 23-36
INDEX_JOIN hint, 23-36
indexes, 1-26, 10-21
auditing partitions, 11-63
B*-tree structure of, 10-27
bitmap indexes, 10-32, 10-36
nulls and, 10-8
parallel query and DML, 10-33
branch blocks, 10-28
building
using an existing index, 10-22
cardinality, 10-33
cluster, 10-50
cannot be partitioned, 11-2
contrasted with table, 10-50
dropping, 10-50
scans of, 23-41
composite, 10-22
scans of, 23-42
concatenated, 10-22
described, 1-26, 10-21
domain, 10-42
domain indexes
extensible optimization, 22-16
user-defined statistics, 22-17
enforcing integrity constraints, 28-10, 28-12
extensible, 10-42
fast full scans of, 23-36
function-based, 10-24
dependencies, 10-26, 21-7, 21-9
DETERMINISTIC functions, 21-8
DISABLED, 21-8
optimization with, 10-25
privileges, 10-26, 21-8
global partitioned indexes, 11-32
managing partitions, 11-33, 11-60
index joins, 23-36, 23-49
index unusable (IU), 11-61
index-organized tables, 10-36
logical rowids, 10-39, 12-20
secondary indexes, 10-39
internal structure of, 10-27
key compression, 10-29
indexes (continued),
keys and, 10-23
primary key constraints, 28-12
unique key constraints, 28-10
leaf blocks, 10-28
local indexes, 11-29, 11-59
building partitions in parallel, 11-30
location of, 10-26
LONG RAW datatypes prohibit, 12-15
managing partitions, 11-59
no-logging mode, 25-7
nonunique, 10-22
nulls and, 10-8, 10-24, 10-35
on attribute of object column, 14-6
on complex data types, 10-42
on REFs, 14-6
optimization and, 23-10
overview of, 1-26, 10-21
parallel DDL storage, 26-33
parallel index scans, 26-5
partition pruning, 11-4
partitioned tables, 10-36
partitioning guidelines, 11-36
partitions, 11-2, 11-29
performance and, 10-22
privileges for partitions, 11-62
range scans, 23-35
rebuild partition, 11-60
rebuilt after direct-load INSERT, 25-8
reverse key indexes, 10-31
rowids and, 10-28
scans of, 23-35
bounded range, 23-44
cluster key, 23-41
composite, 23-42
MAX or MIN, 23-46
ORDER BY, 23-47
restrictions, 23-48
single-column, 23-42
unbounded range, 23-45
statement conversion and, 23-10
storage format of, 10-26
unique, 10-22
unique scans, 23-35
user-defined types, 14-6
when used with views, 10-14
index-organized tables, 10-36
applications, 10-40
benefits, 10-38
key compression in, 10-30, 10-38
logical rowids, 10-39, 12-20
parallel CREATE, 26-31
parallel queries, 26-29
partition of, 11-42
partitioned secondary indexes on, 11-45
queue tables, 19-12
rebuild of, 10-39
row overflow area, 10-38
secondary indexes on, 10-39
indextype, 10-43
in-doubt transactions, 4-24, 5-8
information
distribution
in basic replication, 34-11
off-loading
in basic replication, 34-11
transport, 34-12
information distribution
basic replication for, 34-11
information retrieval (IR) applications
index-organized tables, 10-40
initialization parameters
ALWAYS_ANTI_JOIN, 24-13
ALWAYS_SEMI_JOIN, 24-13
AQ_TM_PROCESS, 19-6, 19-7
BUFFER_POOL_KEEP, 7-5
BUFFER_POOL_RECYCLE, 7-5
COMPATIBLE, 3-11
DB_BLOCK_BUFFERS, 7-5, 7-13
DB_BLOCK_LRU_LATCHES, 8-8
DB_BLOCK_SIZE, 7-5, 7-13
DB_FILE_MULTIBLOCK_READ_COUNT, 23-51, 24-9
DB_FILES, 7-15
DB_NAME, 32-22
DB_WRITER_PROCESSES, 1-18, 8-8
DISTRIBUTED_TRANSACTIONS, 8-12
FAST_START_IO_TARGET, 32-13
HASH_AREA_SIZE, 24-8
HASH_JOIN_ENABLED, 24-7
HASH_MULTIBLOCK_IO_COUNT, 24-8
HI_SHARED_MEMORY_ADDRESS, 7-13
JOB_QUEUE_PROCESSES, 19-10
LICENSE_MAX_SESSIONS, 29-20
LICENSE_SESSIONS_WARNING, 29-20
LOCK_SGA, 7-13, 7-17
LOG_ARCHIVE_MAX_PROCESSES, 1-19, 8-12, 32-19
LOG_ARCHIVE_START, 32-19
LOG_BUFFER, 7-6, 7-13
LOG_CHECKPOINT_INTERVAL, 32-13
LOG_CHECKPOINT_TIMEOUT, 32-13
MTS_MAX_SERVERS, 8-19, 8-20
MTS_SERVERS, 8-19
NLS_LANGUAGE, 11-20
NLS_NUMERIC_CHARACTERS, 12-9
NLS_SORT, 11-20
OPEN_CURSORS, 7-9, 16-7
OPEN_LINKS, 7-15
OPTIMIZER_FEATURES_ENABLE, 23-17, 23-36, 24-12
OPTIMIZER_MODE, 23-31
OPTIMIZER_PERCENT_PARALLEL, 22-8
PARALLEL_MAX_SERVERS, 26-8
PARALLEL_MIN_PERCENT, 26-18
PARALLEL_MIN_SERVERS, 26-7, 26-8
PARALLEL_SERVER, 5-6
REMOTE_DEPENDENCIES_MODE, 21-11
ROLLBACK_SEGMENTS, 4-27
SERVICE_NAMES, 6-6
SHARED_MEMORY_ADDRESS, 7-13
SHARED_POOL_SIZE, 7-6, 7-13
SKIP_UNUSABLE_INDEXES, 21-8
SORT_AREA_RETAINED_SIZE, 7-16
SORT_AREA_SIZE, 4-18, 7-16, 24-9
SQL_TRACE, 8-15
STAR_TRANSFORMATION_ENABLED, 24-19
TRANSACTIONS, 4-27
TRANSACTIONS_PER_ROLLBACK_SEGMENT, 4-27
USE_INDIRECT_DATA_BUFFERS, 7-14
initially deferred constraints, 28-21
initially immediate constraints, 28-21
INIT.ORA files, 5-4, 5-5
inline views, 10-16
example, 10-16
inner capture, 14-8
INSERT command, 16-3
direct-load INSERT, 25-2
no-logging mode, 11-58, 25-5, 25-7
free lists and, 4-9
parallelizing INSERT ... SELECT, 26-23
storage for parallel INSERT, 25-8
triggers and, 20-2, 20-6
BEFORE triggers, 20-9
INSERT privilege for object tables, 14-15, 14-16
instance groups for parallel operations, 26-19
instance recovery, 32-4
instance failure, 1-46, 32-4
read-only tablespaces, 32-6
SMON process, 1-19, 8-11, 26-41
See also crash recovery
instances, 1-6
aborting, 5-10, 32-4
acquire rollback segments, 4-26
associating with databases, 5-2, 5-6
defined, 1-16
described, 5-2
diagrammed, 8-6
failure in, 1-46, 32-4
instance groups, 26-19
memory structures of, 7-2
multiple-process, 8-2
overview of, 1-6
process structure, 8-2
recovery of, 5-10, 32-4
Fast-Start Checkpointing, 32-13
opening a database, 5-8
SMON process, 8-11
restricted mode, 5-6
service names, 6-6
sharing databases, 1-8
shutting down, 5-9, 5-10
audit record, 31-5
starting, 5-5
audit record, 31-5
system identifiers (SIDs), 6-6
virtual memory, 7-17
INSTEAD OF triggers, 20-12
nested tables, 15-5
object views, 15-5
integrity constraints, 28-2
default column values and, 10-8
See also constraints
integrity rules, 1-22
parallel DML restrictions, 26-45
Inter-Language Method Services (ILMS), 16-20
INTERNAL connection, 5-3
statement execution not audited, 31-5
internal errors tracked in trace files, 8-14
inter-operator parallelism, 26-13
INTERSECT operator
compound queries, 23-4
example, 23-29
optimizing view queries, 23-16
intra-operator parallelism, 26-13
INVALID status, 21-2
invoker rights, 18-9
name resolution, 18-19
procedure security, 30-8
supplied packages, 30-8
IS NULL predicate, 10-8
ISO SQL standard, 1-3, 12-22
composite foreign keys, 28-16
isolation levels
choosing, 27-12
read committed, 27-8
setting, 27-7, 27-31

J

Java
triggers, 20-1, 20-7
job queue processes (SNPn), 1-20, 8-13
message propagation, 19-10
JOB_QUEUE_PROCESSES parameter, 19-10
jobs, 8-2
join views, 10-15
joins
anti-joins, 24-13
Cartesian products, 23-3
cluster, 10-48, 23-38, 24-5
searches on, 23-40
convert to subqueries, 23-13
cross, 23-3
defined, 23-3
encapsulated in views, 1-24, 10-13
equijoins, 23-3
execution plans and, 24-2
hash joins, 24-7
index joins, 23-36, 23-49
join order
execution plans, 22-2
selectivity of predicates, 22-8, 22-17
nested loops, 24-2
cost-based optimization, 24-8
nonequijoins, 23-3
optimization of, 24-9
outer, 23-3
non-null values for nulls, 24-11
partition-wise, 11-5
sample table scan not supported, 23-34
select-project-join views, 23-15
semi-joins, 24-13
sort-merge, 24-4
cost-based optimization, 24-9
example, 23-46
star joins, 24-14
star queries, 24-14
views, 1-25, 10-15

K

key compression, 10-29
keys
cluster, 1-27, 10-48
defined, 28-9
foreign, 28-13
hash, 10-53, 10-57
in constraints, 1-58
indexes and, 10-23
compression, 10-29
PRIMARY KEY constraints, 28-12
reverse key, 10-31
UNIQUE constraints, 28-10
key values, 1-59
maximum storage for values, 10-23
parent, 28-13, 28-15
primary, 28-11
referenced, 1-59, 28-13
reverse key indexes, 10-31
searches, 23-39
unique, 28-8
composite, 28-9, 28-11

L

large pool, 7-12
overview of, 1-16
latches
described, 27-30
LRU, 8-8
LCK0 background process, 1-20, 8-13
leaf blocks, 10-28
leaf-level attributes, 14-18
leaf-level scalar attributes, 14-18
least recently used (LRU) algorithm
database buffers and, 7-3
dictionary cache, 2-4
full table scans and, 7-4
latches, 8-8
shared SQL pool, 7-8, 7-10
LGWR background process, 8-9
See also log writer process
library cache, 7-6, 7-7, 7-10
LICENSE_MAX_SESSIONS parameter, 29-20
LICENSE_SESSIONS_WARNING parameter, 29-20
licensing
concurrent usage, 29-20
named user, 29-21
viewing current limits, 29-20
LIKE, 23-5
links, 33-6
listener processes, 6-6, 8-14
service names, 6-6
LISTENER.ORA file, 6-6
literal invocation
constructor methods, 14-4
LOB datatypes, 12-12
BFILE, 12-14
BLOBs, 12-13
CLOBs and NCLOBs, 12-13
default logging mode, 25-7
NOLOGGING mode, 25-7
restrictions
parallel DDL, 26-31
parallel DML, 26-44
local databases, 1-35
local indexes, 11-29, 11-34
bitmap indexes
on partitioned tables, 10-36
parallel query and DML, 10-33
building partitions in parallel, 11-30
equipartitioning, 11-29
managing partitions, 11-59
locally-managed tablespaces, 3-8
temporary tablespaces, 3-13
location transparency, 1-35
lock process (LCK0), 1-20, 8-13
LOCK TABLE command, 16-4
LOCK_SGA parameter, 7-13, 7-17
locks, 1-32, 27-3
after committing transactions, 17-6
automatic, 1-32, 27-15, 27-19
conversion, 27-16
data, 27-20
duration of, 27-15
deadlocks, 27-17, 27-18
avoiding, 27-19
dictionary, 27-28
clusters and, 27-29
duration of, 27-29
dictionary cache, 27-30
DML acquired, 27-27
diagrammed, 27-25
DML partition locks, 11-45
escalation does not occur, 27-17
exclusive table locks (X), 27-25
file management locks, 27-30
how Oracle uses, 27-15
internal, 27-29
latches and, 27-30
log management locks, 27-30
manual, 1-33, 27-31
examples of behavior, 27-32
object level locking, 13-15
Oracle Lock Management Services, 27-39
overview of, 1-32, 27-3
parallel cache management (PCM), 27-20
parallel DML, 26-42
parse, 16-11, 27-29
rollback segment, 27-30
row (TX), 27-20
block-level recovery, 32-14
row exclusive locks (RX), 27-23
row share table locks (RS), 27-23
share row exclusive locks (SRX), 27-25
share table locks (S), 27-24
share-subexclusive locks (SSX), 27-25
subexclusive table locks (SX), 27-23
subshare table locks (SS), 27-23
table (TM), 27-21
table lock modes, 27-22
tablespace, 27-30
types of, 27-19
log entries, 1-12, 32-9
See also redo log files, 1-12
log management locks, 27-30
log sequence numbers, 1-48
log switch
ALTER SYSTEM SWITCH LOGFILE, 8-12
archiver process, 1-19, 8-12
log writer process (LGWR), 1-19, 8-9
archiving modes, 32-18
group commits, 8-10
manual archiving and, 32-20
redo log buffers and, 7-6
starting new ARCn processes, 8-12
system change numbers, 17-6
write-ahead, 8-9
LOG_ARCHIVE_MAX_PROCESSES parameter, 1-19, 8-12
automatic archiving, 32-19
LOG_ARCHIVE_START parameter, 32-19
LOG_BUFFER parameter, 7-6
system global area size and, 7-13
LOG_CHECKPOINT_INTERVAL parameter, 32-13
LOG_CHECKPOINT_TIMEOUT parameter, 32-13
logging mode
direct-load INSERT, 25-5
NOARCHIVELOG mode and, 25-5
parallel DDL, 26-31, 26-32
partitions, 11-58
SQL operations affected by, 25-7
logical blocks, 4-2
logical database structures, 1-5, 1-9
tablespaces, 3-6
logical reads limit, 29-17
logical rowids, 12-20
index on index-organized table, 10-39
physical guesses, 10-39, 12-20
staleness of guesses, 12-21
statistics for guesses, 12-21
LONG datatype
automatically the last column, 10-7
defined, 12-7
partitioning restriction, 11-14
storage of, 10-7
LONG RAW datatype, 12-14
indexing prohibited on, 12-15
partitioning restriction, 11-14
similarity to LONG datatype, 12-15
lookup tables
star queries, 24-14
LOW_VALUE statistics, 23-52
LRU, 7-3, 7-4, 8-8
dictionary cache, 2-4
latches, 8-8
shared SQL pool, 7-8, 7-10

M

manual locking, 1-33, 27-31
map methods, 1-57, 13-7
massively parallel processing (MPP)
affinity, 26-6, 26-48, 26-49
multiple Oracle instances, 5-3
parallel SQL execution, 26-2
master definition sites, 34-5
master groups, 34-5
master sites, 34-5
matching foreign keys
full, partial, or none, 28-16
materialized view logs, 10-18
materialized views, 10-17
deallocating extents, 4-15
materialized view logs, 10-18
overview, 1-25
partitioned, 10-18, 11-2
refreshing, 10-18
same as snapshots, 1-25, 34-3
MAXEXTENTS UNLIMITED storage parameter, 26-40
MAXVALUE
partitioned tables and indexes, 11-20
media failure, 1-46, 32-5
memory
allocation for SQL statements, 7-11
content of, 7-2
cursors (statement handles), 1-17
extended buffer cache (32-bit), 7-14
overview of structures in, 1-14
processes use of, 8-2
shared SQL areas, 7-8
software code areas, 7-17
sort areas, 7-16
stored procedures, 18-8, 18-17
structures in, 7-2
system global area (SGA)
allocation in, 7-2
initialization parameters, 7-12, 7-13
locking into physical memory, 7-13, 7-17
SGA size, 7-12
starting address, 7-13
virtual, 7-17
See also system global area
MERGE hint, 23-17
MERGE_AJ hint, 24-13
MERGE_SJ hint, 24-13
merging complex views, 23-17
merging partitions, 11-16
merging views into statements, 23-15
message queuing, 19-2
exporting queue tables, 19-12
messages, 19-4
publish/subscribe support, 19-10
event publication, 20-18
queue monitor process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queue tables, 19-4
recipients, 19-5
rule-based subscriptions, 19-5, 19-6
subscription lists, 19-5
remote databases, 19-9
methods
comparison methods, 13-6
constructor methods, 13-6
literal invocation, 14-4
privileges on, 30-11
methods of collections
constructor methods, 1-57
methods of object types, 1-57, 13-4
constructor methods, 1-57, 14-18
execution privilege for, 14-13
map methods, 1-57, 13-7
order methods, 1-57, 13-7
PL/SQL, 13-14
purchase order example, 13-2, 13-5
selfish style of invocation, 13-6
use of empty parentheses with, 14-9
MINIMUM EXTENT
parallel DML, 25-9, 25-10
MINIMUM EXTENT parameter, 26-33
MINUS operator
compound queries, 23-4
optimizing view queries, 23-16
mobile computing environment
materialized views, 10-17
modes
archive log, 32-18
table lock, 27-22
two-task, 8-3
MODIFY CONSTRAINT option, 28-23
monitoring user actions, 1-44, 31-2
MOVE PARTITION command
no-logging mode, 11-58, 25-7
rules of parallelism, 26-25
MPP
See massively parallel processing
MTS_MAX_SERVERS parameter, 8-19
artificial deadlocks and, 8-20
MTS_SERVERS parameter, 8-19
multiblock writes, 8-8
multimaster replication, 34-6
multimedia datatypes, 13-3
multiple-process systems (multiuser systems), 8-2
multiplexing
control files, 1-49, 32-22
recovery and, 32-5
redo log files, 1-48
multi-threaded server, 8-16
artificial deadlocks in, 8-19
dedicated server contrasted with, 8-16
described, 8-3, 8-16
dispatcher processes, 1-20, 8-14
example of use, 8-20
limiting private SQL areas, 29-18
Net8 or SQL*Net V2 requirement, 8-14, 8-16
parallel SQL execution, 26-8
private SQL areas, 7-9
sort areas, 7-16
processes needed for, 8-16
restricted operations in, 8-20
server processes, 1-18, 8-14, 8-19
session information, 7-14
session memory in the large pool, 7-12
shared server processes, 8-14, 8-19
multiuser environments, 1-2, 8-2
multiverison consistency model, 1-31
multiversion concurrency control, 27-5
mutating errors and triggers, 20-22

N

name resolution for procedures, 18-19
name resolution in distributed databases, 33-6
named user licensing, 29-21
National Language Support (NLS)
character sets for, 12-6
CHECK constraints and, 28-18
clients and servers may diverge, 33-20
DATE datatype and partitions, 11-14, 11-21
NCHAR and NVARCHAR2 datatypes, 12-6
NCLOB datatype, 12-13
parameters, 5-5
views and, 10-14
NCHAR datatype, 12-6
NCLOB datatype, 12-13
nested loops joins, 24-2
cost-based optimization, 24-8
nested tables, 10-9, 13-12
indexes, 14-6
index-organized tables, 10-38
key compression, 10-30
INSTEAD OF triggers, 15-5
restrictions, 26-30
updating in views, 15-5
Net8, 1-7, 1-37, 6-5, 33-4
Advanced Security option, 33-18
applications and, 6-5
client/server systems use of, 6-5
multi-threaded server requirement, 8-14, 8-16
overview of, 6-5
network listener process, 6-6
connection requests, 8-14, 8-16
dedicated server example, 8-24
multi-threaded server example, 8-20
service names, 6-6
networks
client/server architecture use of, 6-2
communication protocols, 6-5, 8-26
dispatcher processes and, 8-14, 8-16
distributed databases, 33-4
distributed databases use of', 33-2
drivers, 8-26
failures of, 32-3
listener processes of, 6-6, 8-14
Net8, 6-5, 33-4
network authentication service, 29-4
Oracle Names, 33-4
two-task mode and, 8-23
using Oracle on, 1-7, 1-37
NEXT storage parameter
parallel direct-load INSERT, 25-9
calculating value, 25-9
NLS
See National Language Support
NLS_DATE_FORMAT parameter, 12-10
NLS_LANG environment variable, 11-20
NLS_LANGUAGE parameter, 11-20
NLS_NUMERIC_CHARACTERS parameter, 12-9
NLS_SORT parameter
no effect on partitioning keys, 11-20
ORDER BY access path, 23-47
NOARCHIVELOG mode, 32-18
database backups for recovery, 32-24
defined, 32-18
LOGGING mode and, 25-5
overview, 1-48
NOAUDIT command, 16-4
locks, 27-29
nodes
disk affinity in a Parallel Server, 26-48
of distributed databases, 1-35
NOLOGGING mode
direct-load INSERT, 25-5
parallel DDL, 26-31, 26-32
partitions, 11-58
SQL operations affected by, 25-7
nonequijoins
defined, 23-3
non-persistent queues, 19-10
nonprefixed indexes, 11-31, 11-35
global partitioned indexes, 11-33
nonrepeatable reads, 27-3, 27-10
nonunique indexes, 10-22
NOREVERSE option for indexes, 10-31
normalized tables, 1-29, 10-19
star schemas, 24-15
NOT, 23-7
NOT IN subquery, 24-13
NOT NULL constraints
constraint checking, 28-20
defined, 28-7
implied by PRIMARY KEY, 28-12
UNIQUE keys and, 28-11
NOVALIDATE constraints, 28-22
Novell's NetWare Management System, 33-20
nulls
as default values, 10-8
atomic, 14-3
column order and, 10-7
converting to values, 10-8
optimization, 24-11
defined, 10-7
foreign keys and, 28-15, 28-16
how stored, 10-7
indexes and, 10-8, 10-24, 10-35
inequality in UNIQUE key, 28-11
non-null values for, 10-8, 24-11
object types, 14-3
partitioned tables and indexes, 11-21
prohibited in primary keys, 28-11
prohibiting, 28-7
UNIQUE key constraints and, 28-11
unknown in comparisons, 10-8
NUM_DISTINCT column
USER_TAB_COLUMNS view, 23-52
NUM_ROWS column
USER_TABLES view, 23-52
NUMBER datatype, 12-8
internal format of, 12-9
rounding, 12-9
NVARCHAR2 datatype, 12-6
NVL function, 10-8

O

object cache
object views, 15-4
OCI, 13-14
privileges, 14-16
Pro*C, 13-14
object identifiers, 15-3
for object types, 14-18
for object views, 15-3, 15-4
WITH OBJECT OID clause, 15-3, 15-4
object privileges, 30-3
See also schema object privileges
object tables, 13-3, 13-7
constraints, 14-5
indexes, 14-6
row objects, 13-8
triggers, 14-6
virtual object tables, 15-2
object types, 1-22, 13-2, 13-4
attributes of, 13-2, 13-4
column objects, 13-8
indexes, 14-6
comparison methods for, 13-6
constructor methods for, 1-57, 13-6, 14-18
incomplete, 14-17
locking in cache, 13-15
message queuing, 19-6
methods of, 1-57, 13-4
method calls, 14-9
PL/SQL, 13-14
purchase order example, 13-2, 13-5
mutually dependent, 14-16
object views, 10-16
Oracle type translator, 13-15
parallel query, 26-29
restrictions, 26-30
purchase order example, 13-2, 13-4
restrictions
parallel DDL, 26-31
parallel DML, 26-44
row objects, 13-8
use of table aliases, 14-8
object views, 10-16, 15-1
advantages of, 15-2
defining, 15-3
modifiability, 20-12
nested tables, 15-5
object identifiers for, 15-3, 15-4
updating, 15-5
use of INSTEAD OF triggers with, 15-5
object-relational DBMS (ORDBMS), 1-22, 13-2
objects
privileges on, 30-11
objects in a database schema, 1-5
See also schema objects
OCI, 8-25
anonymous blocks, 16-17
bind variables, 16-13
object cache, 13-14
OCIObjectFlush, 15-4
OCIObjectPin, 15-4
stored procedures, 16-18
ODCIIndex, 10-43
offline backups
whole database backup, 32-23
offline redo log files, 1-48, 32-7
OIDs, 14-18, 15-3, 15-4
collections
key compression, 10-30, 10-38
WITH OBJECT OID clause, 15-3, 15-4
OLTP database, 11-5
batch jobs, 26-38
parallel DML, 26-37
partitioning indexes, 11-36
partitions, 11-6
online analytical processing (OLAP)
index-organized tables, 10-42
online redo log, 1-47, 32-7
archiving, 32-18, 32-19
checkpoints, 32-22
media failure, 32-5
multiplexed, 32-5
recorded in control file, 32-21
online transaction processing (OLTP), 11-5
reverse key indexes, 10-31
OPEN_CURSORS parameter, 16-7
managing private SQL areas, 7-9
OPEN_LINKS parameter, 7-15
operating systems
authentication by, 29-4
block size, 4-3
communications software, 8-26
privileges for administrator, 5-3
roles and, 30-22
operations in a relational database, 1-22
OPTIMAL storage parameter, 4-25
optimization, 22-2
choosing the approach, 23-31
conversion of expressions and predicates, 23-4
cost-based, 22-7, 24-8
choosing an access path, 23-50
examples of, 23-51
histograms, 22-8
remote databases and, 23-30
star queries, 24-14
user-defined costs, 22-17
described, 22-2
DISTINCT, 23-17
distributed SQL statements, 23-30
execution plan for partitions, 11-11, 11-14
extensible optimizer, 22-16
function-based indexes, 10-25
GROUP BY views, 23-17
hints, 23-33, 23-36
index build, 10-22
manual, 23-33
merging complex views, 23-17
merging views into statements, 23-15
non-null values for nulls, 24-11
operations performed, 23-2
parallel SQL, 26-10
partition pruning, 11-4
indexes, 11-36
partition pruning (elimination), 11-4
partitioned indexes, 11-35
partition-wise joins, 11-5
plan stability, 22-6
PL/SQL, 23-33
query rewrite, 10-17
in security policies, 30-23
rule-based, 22-18, 24-9
choosing an access path, 23-54
examples of, 23-54
selectivity of predicates, 22-8
histograms, 22-8, 22-10
user-defined, 22-17
selectivity of queries and, 23-51
select-project-join views, 23-15
semi-joins, 24-13
statistics, 22-8, 23-32
user-defined, 22-17
transitivity and, 23-8
types of SQL statements, 23-3
without merging, 23-25
OPTIMIZER_FEATURES_ENABLE parameter, 23-17, 23-36, 24-12
OPTIMIZER_GOAL option, 23-32
OPTIMIZER_MODE, 23-31
hints affecting, 23-33
OPTIMIZER_PERCENT_PARALLEL parameter, 22-8
Oracle
adherence to standards, 1-3
integrity constraints, 28-5
architecture, 1-8, 1-14
client/server architecture of, 6-2
compatibility, 1-3
compatibility levels, 3-14
configurations of, 8-2
multiple-process Oracle, 8-2
connectibility, 1-2
different Oracle versions, 33-7
data access, 1-51
examples of operations, 1-21
dedicated server, 8-24
multi-threaded server, 8-20
features, 1-2
instances, 1-6, 1-16, 5-2
licensing of, 29-19
Oracle server, 1-4
Parallel Server option, 1-8
See also Parallel Server
portability, 1-3
processes of, 1-17, 8-5
scalability of, 6-4
SQL processing, 16-8
using on networks, 1-2, 1-37
Oracle AQ, 19-1
exporting queue tables, 19-12
message queuing, 19-2
publish/subscribe support, 19-10
event publication, 20-18
queue monitor process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queue tables, 19-4
recipients, 19-5
rule-based subscriptions, 19-5, 19-6
subscription lists, 19-5
remote databases, 19-9
Oracle blocks, 1-10, 4-2
See also data blocks
Oracle Call Interface (OCI), 8-25
anonymous blocks, 16-17
bind variables, 16-13
object cache, 13-14
OCIObjectFlush, 15-4
OCIObjectPin, 15-4
stored procedures, 16-18
Oracle Certificate Authority, 29-5
Oracle code, 8-2, 8-25
Oracle Data Cartridge Interface, 10-43
Oracle Enterprise Manager
See Enterprise Manager
Oracle Forms
object dependencies and, 21-13
PL/SQL, 16-16
Oracle Names
global directory service, 33-4
Oracle Open Gateways, 33-8
Oracle Parallel Server, 1-8
See also Parallel Server
Oracle precompilers
anonymous blocks, 16-17
bind variables, 16-13
cursors, 16-11
embedded SQL, 16-5
FIPS flagger, 16-6
stored procedures, 16-18
Oracle program interface (OPI), 8-25
Oracle Replication Manager, 34-15
Oracle Security Manager, 29-5, 33-18
Oracle server, 1-4
See also Oracle
Oracle type translator (OTT), 13-15
Oracle Wallet Manager, 29-5
Oracle wallets, 29-5
Oracle XA
session memory in the large pool, 7-12
Oracle Internet Directory, 29-5
ORDBMS, 1-22, 13-2
order methods, 1-57, 13-7
ORDERED hint, 24-9
OTT, 13-15
outer joins
defined, 23-3
non-null values for nulls, 24-11
OUTLN schema
DBA privileges, 22-6

P

P code, 18-18
packages, 18-4, 18-11
advantages of, 18-15
as program units, 1-56
auditing, 31-8
dynamic SQL, 16-19
examples of, 18-12, 30-9, 30-10
executing, 16-16, 18-18
for locking, 27-39
OUTLN_PKG, 22-6
overview of, 1-26
private, 18-15
privileges
divided by construct, 30-9
executing, 30-7, 30-9
public, 18-15
queuing, 19-4
session state and, 21-6
shared SQL areas and, 7-10
storing, 18-17
supplied packages, 18-16
invoker or definer rights, 30-8
validity of, 18-18
pages, 4-2
parallel backup operations, 32-16
PARALLEL clause
parallelization rules, 26-20
parallel DDL, 26-30
extent allocation, 26-33
functions, 26-46
parallelism types, 26-3
parallelization rules, 26-20
partitioned tables and indexes, 26-31
building local indexes, 11-30
restrictions
LOBs, 26-31
object types, 26-30, 26-31
parallel DELETE, 26-21
parallel DML, 26-35
applications, 26-37
bitmap indexes, 10-33
degree of parallelism, 26-20, 26-23
enabling PARALLEL DML, 26-38
functions, 26-46
lock and enqueue resources, 26-42
parallelism types, 26-3
parallelization rules, 26-20
recovery, 26-40
restrictions, 26-43
object types, 26-30, 26-44
remote transactions, 26-46
rollback segments, 26-40
transaction model, 26-39
parallel execution, 26-2
coordinator, 25-3, 26-6
full table scans, 26-5
inter-operator parallelism, 26-13
intra-operator parallelism, 26-13
partitioned tables and indexes, 26-4
server, 25-3, 26-6
index maintenance, 25-8
NEXT extent size, 25-9
temporary segments, 25-8
See also parallel SQL
parallel execution coordinator, 26-6
direct-load INSERT, 25-3
parallel execution servers, 26-6
direct-load INSERT, 25-3
index maintenance, 25-8
NEXT extent size, 25-9
temporary segments, 25-8
PARALLEL hint, 26-17
parallelization rules, 26-20
UPDATE and DELETE, 26-21
parallel query, 26-28
bitmap indexes, 10-33
functions, 26-46
index-organized tables, 26-29
object types, 26-29
restrictions, 26-30
parallelization rules, 26-20
parallel recovery, 32-10, 32-16
Parallel Server, 1-8
concurrency limits and, 29-21
databases and instances, 5-3
disk affinity, 26-48
distributed locks, 27-20
DML locks and performance, 11-47
exclusive mode
rollback segments and, 4-27
file and log management locks, 27-30
instance groups, 26-19
isolation levels, 27-11
lock processes, 1-20, 8-13
mounting a database using, 5-6
named user licensing and, 29-21
parallel SQL, 26-1
PCM locks, 27-20
read consistency, 27-6
reverse key indexes, 10-31
shared mode
rollback segments and, 4-27
system change numbers, 8-10
system monitor process and, 8-11, 26-41
temporary tablespaces, 3-12
PARALLEL SERVER parameter, 5-6
parallel SQL, 26-2
allocating rows to parallel execution servers, 26-11
coordinator process, 26-6
direct-load INSERT, 25-3
degree of parallelism, 26-16
instance groups, 26-19
multi-threaded server, 26-8
number of parallel execution servers, 26-7
operations in execution plan, 26-10
optimizer, 26-10
Parallel Server and, 26-1
parallelization rules, 26-20
server processes, 26-6
direct-load INSERT, 25-3, 25-8
NEXT extent size, 25-9
summary or rollup tables, 26-31
See also parallel execution
parallel UPDATE, 26-21
PARALLEL_INDEX hint, 26-17
PARALLEL_MAX_SERVERS parameter, 26-8
PARALLEL_MIN_PERCENT parameter, 26-18
PARALLEL_MIN_SERVERS parameter, 26-7, 26-8
parameter files, 5-4
example of, 5-4
used at startup, 5-5
parameters
initialization, 5-4
locking behavior, 27-19
See also initialization parameters
National Language Support, 5-5
storage, 4-5, 4-11
parentheses, use of in method calls, 14-9
parse trees, 18-17
construction of, 16-7
in shared SQL area, 7-8
stored in database, 18-18
parsing, 16-11
DBMS_SQL package, 16-19
embedded SQL, 16-6
parse calls, 16-8
parse locks, 16-11, 27-29
performed, 16-8
SQL statements, 16-11, 16-19
partial backups, 32-24
partition elimination, 11-4
PARTITION option, 11-63
partition views, 11-11
PARTITION_VIEW_ENABLED parameter, 11-12
partitioning
LOBs
DML locks, 11-46
maintenance operations, 11-57
tables with LOB columns, 11-38
partitioning columns, 11-15
partitioning keys, 11-15, 11-19
multi-column keys, 11-22
partitions, 11-2, 11-13
advantages of, 11-5, 11-7
affinity, 26-48
basic partitioning model, 11-13
bitmap indexes, 10-36
concurrent maintenance operations, 11-50
DATE datatype, 11-14, 11-21
DML partition locks, 11-45
dynamic partitioning, 26-6
equipartitioning, 11-24
examples, 11-25, 11-30, 11-32
LOB columns, 11-38
local indexes, 11-29
on one dimension, 11-24
overflow of index-organized tables, 11-42, 11-44
range partitioning, 11-24
EXCHANGE PARTITION, 11-11
execution plan, 11-11, 11-14
global indexes, 11-32, 11-60
hash partitioning, 11-16
local indexes, 11-29, 11-59
building in parallel, 11-30
LONG and LONG RAW restriction, 11-14
maintenance operations, 11-48
materialized views, 10-18, 11-2
merging, 11-16
no-logging mode, 25-7
nonprefixed indexes, 11-31, 11-35
OLTP databases, 11-6
parallel DDL, 26-31
parallel queries, 26-4
partition bounds, 11-20
partition elimination, 11-4
partition names, 11-18
partition pruning, 11-4
DATE datatype, 11-22
disk striping and, 26-48
indexes, 11-36
parallelizing by block range, 26-4
partition transparency, 11-10
partition-extended table names, 11-63
partitioning indexes, 11-29, 11-36
partitioning keys, 11-15, 11-19
partitioning tables, 11-27
partition-wise joins, 11-5
physical attributes, 11-27, 11-37
prefixed indexes, 11-30
range partitioning, 11-15
disk striping and, 26-48
rebuild partition, 11-60
referencing a partition, 11-19
restrictions
bitmap indexes, 11-14
datatypes, 11-14, 11-21
partition-extended table names, 11-64
rules of parallelism, 26-25, 26-27
segments, 4-17
statistics, 11-14, 22-11
VLDB, 11-5
partition-wise joins, 11-5
passwords
account locking, 29-7
administrator privileges, 5-3
complexity verification, 29-8
connecting with, 8-4
connecting without, 29-4
database user authentication, 29-7
encryption, 29-7
expiration, 29-8
password files, 29-13
password reuse, 29-8
used in roles, 1-42
PCTFREE storage parameter
how it works, 4-6
PCTUSED and, 4-8
PCTINCREASE storage parameter
parallel DML, 25-9, 25-10
PCTUSED storage parameter
how it works, 4-6
PCTFREE and, 4-8
performance
clusters and, 10-48
constraint effects on, 28-6
DSS database, 11-9, 26-37
dynamic performance tables (V$), 2-7
group commits, 8-10
index build, 10-22
I/O, 11-9
Oracle Parallel Server and DML locks, 11-47
packages, 18-16
parallel recovery and, 32-11
partitions, 11-9
prefixed and nonprefixed indexes, 11-35
recovery, 32-13
resource limits and, 29-16
SGA size and, 7-12
sort operations, 3-12
structures that improve, 1-26, 1-27
viewing execution plans, 22-4
persistent areas, 7-8
persistent queuing, 19-2
PGA, 1-17, 7-14
multi-threaded server, 8-19
phantom reads, 27-3, 27-10
physical database structures, 1-5, 1-11
control files, 1-13, 32-21
datafiles, 1-12, 3-16
redo log files, 1-12, 32-7
physical guesses in logical rowids, 12-20
staleness, 12-21
statistics for, 12-21
PKI, 29-5
plan
accessing views, 23-19, 23-22, 23-23
complex statements, 23-14
compound queries, 23-27, 23-28, 23-29
joining views, 23-25
joins, 24-2, 24-8
OR operators, 23-12
SQL execution, 16-4, 16-11
star transformation, 24-18
plan stability for optimization, 22-6
PL/SQL, 16-15
anonymous blocks, 16-15, 18-9
auditing of statements within, 31-4
bind variables
user-defined types, 13-14
database triggers, 1-59, 20-1
datatypes, 12-2
deterministic functions, 23-9
dynamic SQL, 16-19
exception handling, 16-18
executing, 16-15, 18-18, 18-19
external procedures, 16-20, 18-11
language constructs, 16-17
object views, 15-4
optimizer goal, 23-33
overview of, 1-55, 16-15
packages, 18-4, 18-11
parse locks, 27-29
parsing DDL statements, 16-19
partition-extended table names, 11-65
PL/SQL engine, 16-15, 18-2
compiler, 18-17
executing a procedure, 18-19
products containing, 16-16
program units, 1-25, 7-10, 16-15, 18-2
compiled, 16-16, 18-9, 18-17
shared SQL areas and, 7-10
roles in procedures, 30-20
stored procedures, 1-25, 16-15, 18-2, 18-6
user locks, 27-39
user-defined datatypes, 13-13
PMON background process, 8-11
See also process monitor process
point-in-time recovery
clone database, 5-7
portability, 1-3
precompilers
anonymous blocks, 16-17
bind variables, 16-13
cursors, 16-11
embedded SQL, 16-5
FIPS flagger, 16-6
stored procedures, 16-18
predicates
dynamic
in security policies, 30-23
optimizing view queries, 23-15
partition pruning, 11-4
indexes, 11-36
pushing into a view, 23-18, 23-23
examples, 23-19, 23-21
selectivity, 22-8
histograms, 22-8, 22-10
user-defined, 22-17
prefixed indexes, 11-30, 11-34
prefixes of data dictionary views, 2-5
PRIMARY KEY constraints, 28-11
constraint checking, 28-20
described, 28-11
indexes used to enforce, 28-12
name of, 28-12
maximum number of columns, 28-12
NOT NULL constraints implied by, 28-12
primary keys, 1-59, 28-11
advantages of, 28-11
defined, 28-3
optimization, 23-14
searches, 23-40
private rollback segments, 4-26
private SQL areas
cursors and, 7-9
described, 7-8
how managed, 7-9
persistent areas, 7-8
runtime areas, 7-8
privileges
administrator, 5-3
connections audited, 31-5
OUTLN schema, 22-6
statement execution not audited, 31-5
auditing use of, 1-44, 31-7
checked when parsing, 16-11
function-based indexes, 10-26, 21-8
granting, 1-41, 30-3, 30-4
examples of, 30-9, 30-10
grouping into roles, 1-41
overview of, 1-41, 30-2
partitioned tables and indexes, 11-62
procedures, 30-7
creating and altering, 30-9
executing, 18-18, 30-7
in packages, 30-9
RESTRICTED SESSION, 29-20
revoked
object dependencies and, 21-6
revoking, 30-3, 30-4
roles, 30-16
restrictions on, 30-20
schema object, 30-3
DML and DDL operations, 30-5
granting and revoking, 30-4
overview of, 1-41
packages, 30-9
procedures, 30-7
system, 30-2
granting and revoking, 30-3
overview of, 1-41
user-defined types, 14-13
to start up or shut down a database, 5-3
trigger privileges, 30-8
user-defined types
acquired by role, 14-13
ALTER ANY TYPE, 14-13
checked when pinning, 14-16
column level for object tables, 14-16
CREATE ANY TYPE, 14-13
CREATE TYPE, 14-13
DELETE, 14-15, 14-16
DROP ANY TYPE, 14-13
EXECUTE, 14-13, 14-14
EXECUTE ANY TYPE, 14-13, 14-14
EXECUTE ANY TYPE with ADMIN OPTION, 14-14
EXECUTE with GRANT option, 14-14
INSERT, 14-15, 14-16
SELECT, 14-15, 14-16
system privileges, 14-13
UPDATE, 14-15, 14-16
using, 14-13, 14-17
views, 30-6
creating, 30-6
using, 30-6
Pro*C/C++
processing SQL statements, 16-10
user-defined datatypes, 13-14
procedural replication, 34-15
detecting conflicts, 34-16
wrapper, 34-16
procedure calls
remote, 33-12
procedures, 16-15, 18-1, 18-6, 21-8
advantages of, 18-7
auditing, 31-8
contrasted with anonymous blocks, 18-9
contrasted with functions, 1-56, 18-2
current user, 18-10
cursors and, 16-18
definer rights, 18-9, 30-7
roles disabled, 30-20
dependency tracking in, 21-6
deterministic functions, 23-9
examples of, 18-6, 30-9, 30-10
executing, 16-16, 18-18
external procedures, 16-20, 18-11
external reference in, 18-10, 18-19
INVALID status, 21-2, 21-6
invoker rights, 18-9, 30-8
roles used, 30-20
supplied packages, 30-8
prerequisites for compilation of, 21-5
privileges
create or alter, 30-9
executing, 30-7
executing in packages, 30-9
remote procedure calls, 33-12
security enhanced by, 18-7, 30-8
shared SQL areas and, 7-10
stored procedures, 16-15, 16-18, 18-2
storing, 18-17
supplied packages, 18-16
invoker or definer rights, 30-8
triggers, 20-2
validity of, 18-18
process global area (PGA), 7-14
See also program global area
process monitor process (PMON)
cleans up timed-out sessions, 29-18
described, 1-19, 8-11
network failure, 32-3
parallel DML process recovery, 26-41
process failure, 32-3
processes, 8-2
archiver (ARCn), 1-19, 8-12, 32-19
background, 1-18, 8-5
diagrammed, 8-6
block server (BSP), 27-6
checkpoint (CKPT), 1-19, 8-11
checkpoints and, 8-8
database writer (DBWn), 1-18, 8-8
dedicated server, 8-19
dispatcher (Dnnn), 1-20, 8-14
distributed transaction resolution, 8-12
during recovery, 32-12
failure in, 32-3
job queue (SNPn), 1-20, 8-13
message propagation, 19-10
listener, 6-6, 8-14
shared servers and, 8-16
lock (LCK0), 1-20, 8-13
log writer (LGWR), 1-19, 8-9
multiple-process Oracle, 8-2
multi-threaded server, 8-16
artificial deadlocks and, 8-19
client requests and, 8-17
Oracle, 1-17, 8-5
overview of, 1-17
parallel execution coordinator, 26-6
direct-load INSERT, 25-3
parallel execution servers, 26-6
direct-load INSERT, 25-3, 25-8
NEXT extent size, 25-9
process monitor (PMON), 1-19, 8-11
queue monitor (QMNn), 1-20, 8-13, 19-6
recoverer (RECO), 1-20, 8-12
and in-doubt transactions, 1-36
server, 1-18, 1-34, 8-5
dedicated, 8-22
shared, 8-14, 8-19
shadow, 8-22
structure, 8-2
system monitor (SMON), 1-19, 8-11
trace files for, 8-14
user, 1-17, 8-4
allocate PGAs, 7-14
manual archiving by, 32-21
recovery from failure of, 8-11
sharing server processes, 8-14
processing
DDL statements, 16-14
distributed, 1-33
DML statements, 16-10
overview, 16-8
parallel SQL, 26-2
queries, 16-12
profiles
overview of, 1-43
password management, 29-7
when to use, 29-18
program global area (PGA), 1-17, 7-14
allocation of, 7-14
contents of, 7-14
multi-threaded servers, 8-19
nonshared and writable, 7-14
size of, 7-15
program interface, 8-25
Oracle side (OPI), 8-25
overview of, 1-20
structure of, 8-25
two-task mode in, 8-23
user side (UPI), 8-25
program units, 1-25, 16-15, 18-2
prerequisites for compilation of, 21-5
shared pool and, 7-10
propagation scheduling capabilities, 19-10
pruning partitions, 11-4, 26-4, 26-48
DATE datatype, 11-22
EXPLAIN PLAN, 11-22
index partitions, 11-4
indexes, 11-36
pseudocode, 18-18
triggers, 20-25
pseudocolumns
CHECK constraints prohibit
LEVEL and ROWNUM, 28-18
modifying views, 20-13
ROWID, 12-15
ROWNUM
cannot use indexes, 23-49
optimizing view queries, 23-16, 23-25
USER, 30-7
public key infrastructure, 29-5
public rollback segments, 4-26
PUBLIC user group, 29-14, 30-19
validity of procedures, 18-19
publication
DDL statements, 20-20
DML statements, 20-20
logon/logoff events, 20-19
system events
server errors, 20-19
startup/shutdown, 20-19
using triggers, 20-18
publish/subscribe support, 19-10
asynchronous notification, 19-11
event publication, 20-18
listen capability, 19-11
message propagation, 19-9
rule-based subscriber, 19-6
triggers, 20-18
purchase order example
object types, 13-2, 13-4
PUSH_JOIN_PRED hint, 24-12

Q

QMNn background process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queries
ad hoc, 26-31
composite indexes, 10-22
compound
defined, 23-4
optimization of, 23-27
ORs converted to, 23-10
default locking of, 27-26
define phase, 16-12
defined, 23-3
describe phase, 16-12
distributed or remote, 33-11
fetching rows, 16-12
in DML, 16-3
index scans parallelized by partition, 26-5
inline views, 10-16
location transparency and, 33-15
merged with view queries, 10-14
optimizing IN subquery, 23-17
optimizing view queries, 23-15
parallel processing, 26-2
phases of, 27-5
processing, 16-12
read consistency of, 1-32, 27-5
SAMPLE clause
cost-based optimization, 22-16
selectivity of, 23-51
star queries, 24-14
stored as views, 1-24, 10-11
table scans parallelized by rowid, 26-4
temporary segments and, 4-18, 16-12
triggers use of, 20-23
query rewrite, 10-17
dynamic predicates in security policies, 30-23
queue monitor process (QMNn), 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queuing, 19-2
exception handling, 19-11
exporting queue tables, 19-12
instance affinity, 19-10
publish/subscribe support, 19-10
event publication, 20-18
queue level access control, 19-9
queue monitor process, 1-20, 8-13, 19-6
interval statistics, 19-11
window of execution, 19-7
queue tables, 19-4, 19-12
recipients, 19-5
rule-based subscriptions, 19-5, 19-6
subscription lists, 19-5
remote databases, 19-9
quotas
revoking tablespace access and, 29-14
setting to zero, 29-14
SYS user not subject to, 29-14
tablespace, 1-43, 29-13
temporary segments ignore, 29-14

R

RADIUS, 29-6
range partitioning, 11-15
and primary key columns, 11-42
equipartitioning, 11-24
key comparison, 11-20, 11-22
partition bounds, 11-20
RAW datatype, 12-14
RAWTOHEX function, 12-23
RDBMS, 1-22
object-relational DBMS, 1-22, 13-2
See also Oracle
read committed isolation, 27-6, 27-8
read consistency, 27-2, 27-4
Cache Fusion, 27-6
defined, 1-31
dirty read, 27-2, 27-10
multiversion consistency model, 1-31, 27-4
nonrepeatable read, 27-3, 27-10
Oracle Parallel Server, 27-6
phantom read, 27-3, 27-10
queries, 16-12, 27-4
rollback segments and, 4-20
snapshot too old message, 27-5
statement level, 27-5
subqueries in DML, 27-14
transactions, 1-31, 27-4, 27-6
triggers and, 20-21, 20-23
READ ONLY option
ALTER TABLESPACE, 3-10
read snapshot time, 27-10
read uncommitted, 27-3
READ WRITE option
ALTER TABLESPACE, 3-11
readers block writers, 27-10
read-only databases
opening, 5-9
read-only replication
uses of, 34-11
read-only snapshot, 34-8
read-only tablespaces
backing up, 32-26
described, 3-10
restrictions on, 3-12
transition read-only mode, 3-11
read-only transactions, 1-32
reads
data block
limits on, 29-17
dirty, 27-2
repeatable, 27-6
real-time
data replication, 34-16
replication, 34-16
REBUILD INDEX command
no-logging mode, 11-58, 25-7
rules of parallelism, 26-25
REBUILD INDEX PARTITION command, 11-60
no-logging mode, 25-7
rules of parallelism, 26-25
recipients, 19-5
subscription lists, 19-5
recoverer process (RECO), 1-20, 8-12
in-doubt transactions, 1-36, 5-8, 17-8
recovery
basic steps, 1-50, 32-9
block-level recovery, 27-21, 32-14
crash recovery, 1-46, 32-4, 32-13
instance failure, 5-10
opening a database, 5-8
read-only tablespaces, 32-6
required after aborting instance, 5-10
SMON process, 1-19, 8-11
database buffers and, 32-8
dead transactions, 32-4
diagrammed, 32-13
disaster recovery, 32-26
distributed processing in, 8-12
instance recovery, 32-4
Fast-Start Checkpointing, 32-13
instance failure, 1-46, 32-4
parallel DML, 26-41
read-only tablespaces, 32-6
SMON process, 1-19, 8-11, 26-41
media recovery
dispatcher processes, 8-20
enabled or disabled, 32-18
of distributed transactions, 5-8
overview of, 1-45, 32-8
parallel DML, 26-40
parallel recovery, 32-10
parallel restore, 32-16
point-in-time
clone database, 5-7
process recovery, 8-11, 32-3
recommendations for, 32-13
Recovery Manager, 1-51, 32-15
rolling back transactions, 32-9
rolling forward, 32-9
standby database, 32-26
statement failure, 32-3
structures used in, 1-47, 32-6
whole database backups, 32-24
Recovery Manager, 1-51, 32-15
generating reports, 32-17
operating without a catalog, 32-16
parallel operations, 32-16
parallel recovery, 32-11
recovery catalog, 32-15
recursive SQL
cursors and, 16-7
redo entries, 1-12, 32-9
redo log, 1-12, 32-9
archiving modes, 32-18
rolling forward, 32-8, 32-9
instance failure, 32-4
redo log buffers, 1-16, 7-6
circularity, 8-9
committing a transaction, 8-10
log writer process and, 7-6
size of, 7-6
writing, 8-9
redo log entries
committed data, 32-8, 32-9
uncommitted data, 32-9
redo log files, 1-12, 32-7
archived, 1-48, 32-18
automatically, 32-19
errors in archiving, 32-20
manually, 32-20
archiver process (ARCn), 1-19, 8-12
buffer management, 8-9
files named in control file, 32-21
log sequence numbers, 1-48
recorded in control file, 32-22
log switch
ALTER SYSTEM SWITCH LOGFILE, 8-12
archiver process, 1-19, 8-12
log writer process, 8-9
mode of, 1-48
multiplexed, 1-48
purpose of, 1-12
online or offline, 1-47, 1-48, 32-7
overview of, 1-12, 1-47
parallel recovery, 32-10
physical database structure, 1-5
recovery and, 32-7
redo entries, 1-12, 32-9
rolling forward and, 32-9
when temporary segments in, 4-19
written before transaction commit, 8-10
redo records, 1-12
referenced
keys, 1-59, 28-13
objects
dependencies, 21-2
external reference, 18-10, 18-19
partitions, 11-19
REFERENCES privilege
when granted through a role, 30-21
referential integrity, 27-11, 28-13
cascade rule, 28-3
examples of, 28-18
partially null foreign keys, 28-16
PRIMARY KEY constraints, 28-11
restrict rule, 28-3
self-referential constraints, 28-15, 28-18
set to default rule, 28-3
set to null rule, 28-3
refresh
incremental, 10-18
job queue processes (SNPn), 1-20, 8-13
materialized views, 10-18
REFs
constructing from object identifiers, 14-18, 14-19
dangling, 13-9, 13-10
dereferencing of, 13-10
for rows of object views, 15-3
implicit dereferencing of, 13-10
indexes on, 14-6
mutually dependent types, 14-16
pinning, 14-16, 15-4
scoped, 13-9, 14-19
size of, 14-19
use of table aliases, 14-8
REFTOHEX function, 12-23
relational DBMS (RDBMS)
object-relational DBMS, 13-2
principles, 1-22
SQL and, 16-2
See also Oracle
relations, 1-23
remote databases, 1-35
database links, 33-6
remote dependencies, 21-11
remote procedure calls, 33-12
remote procedure calls (RPCs), 33-12
remote transactions, 33-12
parallel DML and DDL restrictions, 26-28
REMOTE_DEPENDENCIES_MODE parameter, 21-11
RENAME command, 16-4
repeatable reads, 27-3
replication
advanced, uses for, 34-6
catalog, 34-14
conflicts
procedural replication, 34-16
definition, 34-2
distributed databases vs., 33-7
group, 34-2
materialized views (snapshots), 10-17
objects, 34-2
procedural, 34-15
real-time, 34-16
restrictions
direct-load INSERT, 25-12
parallel DML, 26-44
sites, 34-5
uses of read-only, 34-11
replication management API, 34-14
administration requests, 34-14
Replication Manager, 34-15
reserved words, 16-3
resource limits
call level, 29-16
connect time per session, 29-18
CPU time limit, 29-17
determining values for, 29-19
idle time per session, 29-17
logical reads limit, 29-17
overview of, 1-43
private SGA space per session, 29-18
session level, 29-16
sessions per user, 29-17
RESOURCE role, 30-22
user-defined types, 14-13, 14-14
response queues, 8-17
response time, 22-7
cost-based approach, 23-31
restricted mode
starting instances in, 5-6
restricted rowid format, 12-18
RESTRICTED SESSION privilege, 29-20
restrictions
direct-load INSERT, 25-11, 26-43
nested tables, 26-30
parallel DDL, 26-31
remote transactions, 26-28
parallel DML, 26-43
remote transactions, 26-28, 26-46
parallel execution of functions, 26-46
partition views, 11-12
partitions
bitmap indexes, 11-14
datatypes, 11-14, 11-21
partition-extended table names, 11-64
reverse key indexes, 10-31
REVERSE option for indexes, 10-31
REVOKE command, 16-4
FORCE option, 14-17
locks, 27-29
object types and dependencies, 14-17
rewrite
predicates in security policies, 30-23
using materialized views, 10-17
roles, 1-41, 30-16
application, 30-18
CONNECT role, 14-13, 14-14, 30-22
DBA role, 14-13, 30-22
DDL statements and, 30-20
definer-rights procedures disable, 30-20
dependency management in, 30-20
distributed database applications, 33-17
enabled or disabled, 30-18
EXP_FULL_DATABASE role, 30-22
functionality, 30-2
global authentication service, 33-17
granting, 30-3, 30-18
IMP_FULL_DATABASE role, 30-22
in applications, 1-42
invoker-rights procedures use, 30-20
managing via operating system, 30-22
naming, 30-19
overview of, 1-41
predefined, 30-22
queue administrator, 19-7
RESOURCE role, 14-13, 14-14, 30-22
restrictions on privileges of, 30-20
revoking, 30-18
schemas do not contain, 30-19
security domains of, 30-19
setting in PL/SQL blocks, 30-20
use of passwords with, 1-42
user, 30-18
users capable of granting, 30-19
uses of, 30-17
rollback, 4-20, 17-6
defined, 1-53
described, 17-6
during recovery, 1-51, 32-9
ending a transaction, 17-2, 17-4, 17-6
statement-level, 17-4
to a savepoint, 17-6
ROLLBACK command, 16-5
rollback entries, 4-20
rollback segments, 1-11, 4-19
access to, 4-20
acquired during startup, 5-8
allocation of extents for, 4-22
new extents, 4-24
clashes when acquiring, 4-27
committing transactions and, 4-21
contention for, 4-21
deallocating extents from, 4-25
deferred, 4-30
defined, 1-11
dropping, 4-25
restrictions on, 4-30
how transactions write to, 4-22
in-doubt distributed transactions, 4-24
invalid, 4-28
locks on, 27-30
MAXEXTENTS UNLIMITED, 26-40
moving to the next extent of, 4-22
number of transactions per, 4-21
offline, 4-28, 4-30
offline tablespaces and, 4-30
online, 4-28, 4-30
OPTIMAL, 26-40
overview of, 4-19, 32-8
parallel DML, 26-40
parallel recovery, 32-10
partly available, 4-28, 32-4
private, 4-26
public, 4-26
read consistency and, 1-31, 4-20, 27-4
recovery needed for, 4-28
states of, 4-28
SYSTEM rollback segment, 4-26
transactions and, 4-20
use of in recovery, 1-49, 32-9
when acquired, 4-26
when used, 4-20
written circularly, 4-21
rolling back during recovery, 32-9
rolling back transactions, 1-54, 17-2, 17-6, 32-4
rolling forward during recovery, 1-50, 32-9
root blocks, 10-55
row cache, 7-10
row data (section of data block), 4-5
row directories, 4-4
row locking, 27-11, 27-20
block-level recovery, 27-21, 32-14
serializable transactions and, 27-8
row objects, 13-8
row pieces, 10-5
headers, 10-5
how identified, 10-7
row sampling, 22-14
row sources, 22-4
row triggers, 20-8
when fired, 20-21
See also triggers
ROWID datatype, 12-15, 12-16
extended rowid format, 12-17
restricted rowid format, 12-18
rowids, 10-7
accessing, 12-15
changes in, 12-16
in non-Oracle databases, 12-22
internal use of, 12-16, 12-19
logical, 12-15
logical rowids, 12-20
index on index-organized table, 10-39
physical guesses, 10-39, 12-20
staleness of guesses, 12-21
statistics for guesses, 12-21
of clustered rows, 10-7
physical, 12-15
row migration, 4-10
sorting indexes by, 10-28
table access by, 23-34
universal, 12-15
ROWIDTOCHAR function, 12-23
row-level locking, 27-10, 27-20
ROWNUM pseudocolumn
cannot use indexes, 23-49
optimizing view queries, 23-16, 23-25
rows, 1-23, 10-3
addresses of, 10-7
chaining across blocks, 4-10, 10-5
clustered, 10-6
rowids of, 10-7
defined, 1-23
described, 10-3
fetched, 16-12
format of in data blocks, 4-4
headers, 10-5
locking, 27-11, 27-20
locks on, 11-45, 27-20, 27-23
logical rowids, 12-20
index-organized tables, 10-39
migrating to new block, 4-10
pieces of, 10-5
row objects, 13-8
row overflow in index-organized tables, 10-38
row sources, 22-4
rowids used to locate, 23-34, 23-38
row-level security, 30-22
shown in rowids, 12-17, 12-18
size of, 10-5
storage format of, 10-5
triggers on, 20-8
when rowid changes, 12-16
RPC, 33-12
RULE hint
OPTIMIZER_MODE and, 23-33
rule-based optimization, 22-18
rule-based subscriptions, 19-5, 19-6
runtime areas, 7-8

S

same-row writers block writers, 27-10
SAMPLE BLOCK option, 23-34
access path, 23-49
hints cannot override, 23-50
SAMPLE clause
cost-based optimization, 22-16
SAMPLE option, 23-34
access path, 23-49
hints cannot override, 23-50
sample table scans, 23-34, 23-49
hints cannot override, 23-50
SAVEPOINT command, 16-5
savepoints, 1-55, 17-7
described, 17-7
implicit, 17-4
overview of, 1-55
rolling back to, 17-6
scalability
batch jobs, 26-38
client/server architecture, 6-4
parallel DML, 26-37
parallel SQL execution, 26-2
scans, 23-34
cluster, 23-38, 23-39, 23-40, 23-41
indexed, 23-41
fast full index scan, 23-36
full table, 23-34, 23-48
LRU algorithm, 7-4
multiblock reads, 23-51
parallel query, 26-5
rule-based optimizer, 23-54
hash cluster, 23-39, 23-41
index, 23-35
bitmap, 23-36
bounded range, 23-44
cluster key, 23-41
composite, 23-42
MAX or MIN, 23-46
ORDER BY, 23-47
restrictions, 23-48
selectivity and, 23-51
single-column, 23-42
unbounded range, 23-45
index joins, 23-36, 23-49
range, 23-35, 23-42
bounded, 23-44
MAX or MIN, 23-46
ORDER BY, 23-47
unbounded, 23-45
sample table, 23-34, 23-49
hints cannot override, 23-50
table scan and CACHE clause, 7-4
unique, 23-35, 23-40, 23-41
schema names
in distributed databases, 33-6
qualifying column names, 14-9
unique within a database, 33-6
schema object privileges, 30-3
DML and DDL operations, 30-5
granting and revoking, 30-4
overview of, 1-41
views, 30-6
schema objects, 10-1
auditing, 1-44, 31-8
creating
tablespace quota required, 29-14
default tablespace for, 29-13
defined, 1-5
dependencies of, 21-2
and distributed databases, 21-12
and views, 10-15
on non-existence of other objects, 21-8
triggers manage, 20-21
dependent on lost privileges, 21-6
dimensions, 10-18
distributed database naming conventions for, 33-6
domain index, 10-43
global names, 33-6
in a revoked tablespace, 29-14
indextype, 10-43
information in data dictionary, 2-2
INVALID status, 21-2
materialized views, 10-17
names in distributed databases, 33-6
overview of, 1-10, 1-23, 10-2
privileges on, 30-3
relationship to datafiles, 3-16, 10-2
trigger dependencies on, 20-25
user-defined operator, 10-44
user-defined types, 13-3
schemas, 29-2
associated with users, 1-38, 10-2
contents of, 10-2
contrasted with tablespaces, 10-2
defined, 29-2
objects in, 10-2
OUTLN, 22-6
star schemas, 24-14, 24-15
normalized tables, 24-15
user-defined datatypes, 13-14
SCN, 17-5
See also system change numbers
scoped REFs, 13-9, 14-19
security, 1-41, 29-2
administrator privileges, 5-3
application enforcement of, 1-42
auditing, 31-2, 31-6
auditing user actions, 1-44
data, 1-38
definer rights, 18-9, 18-19
deleting audit data, 2-5
described, 1-38
discretionary access control, 1-39, 29-2
distributed databases, 33-17
domains, 1-40, 29-2
dynamic predicates, 30-23
enforcement mechanisms, 1-39
fine-grained access control, 30-22
invoker rights, 18-9, 18-19
message queues, 19-7
passwords, 29-7
policies
implementing, 30-24
procedures enhance, 30-8
program interface enforcement of, 8-25
security policies, 30-22
system, 1-38, 2-3
views and, 10-13
views enhance, 30-6
security domains, 1-40, 29-2
enabled roles and, 30-18
tablespace quotas, 29-13
segments, 1-11, 4-16
data, 4-16
deallocating extents from, 4-14
defined, 4-3
header block, 4-11
index, 4-17
overview of, 1-11, 4-16
rollback, 4-19
table
high water mark, 25-3
temporary, 1-11, 4-17, 10-10
allocating, 4-17
cleaned up by SMON, 8-11
dropping, 4-16
ignore quotas, 29-14
operations that require, 4-18
parallel INSERT, 25-8
tablespace containing, 4-16, 4-18
SELECT command, 16-3
composite indexes, 10-22
SAMPLE clause
cost-based optimization, 22-16
SAMPLE option, 23-34
access path, 23-49, 23-50
subqueries, 16-12
See also queries
SELECT privilege for object tables, 14-15, 14-16
selectivity of predicates, 22-8
histograms, 22-8, 22-10
user-defined selectivity, 22-17
selectivity of queries, 23-51
select-project-join views, 23-15
selfish style of method invocation, 13-6
semi-joins, 24-13
sequences, 1-25, 10-19
auditing, 31-8
CHECK constraints prohibit, 28-18
independence from tables, 10-20
length of numbers, 10-19
number generation, 10-19
Server Manager
PL/SQL, 16-17, 16-19
SQL statements, 16-2
server processes, 1-18, 8-5
listener process and, 6-6
servers, 1-33
client/server architecture, 6-2
dedicated, 1-18, 8-22
multi-threaded contrasted with, 8-16
dedicated server architecture, 8-3
defined, 1-34
multi-threaded, 1-18
architecture, 8-3, 8-16
dedicated contrasted with, 8-16
processes of, 8-14, 8-16, 8-19
processes of, 1-18
shared, 1-18
service names, 6-6
SERVICE_NAMES parameter, 6-6
Services
Heterogeneous, 33-8
session control statements, 1-53, 16-5
SESSION_ROLES view
queried from PL/SQL block, 30-20
sessions
auditing by, 31-10
connections contrasted with, 8-4
current user, 18-10
defined, 8-4, 31-10
enabling PARALLEL DML, 26-38
limit on concurrent, 1-43
by license, 29-20
limits per user, 29-17
memory allocation in the large pool, 7-12
package state and, 21-6
resource limits and, 29-16
stack space in PGA, 7-14
time limits on, 29-17
transaction isolation level, 27-31
when auditing options take effect, 31-6
where information is stored, 7-14
SET CONSTRAINTS command
DEFERRABLE or IMMEDIATE, 28-21
SET ROLE command, 16-5
SET TRANSACTION command, 16-5
ISOLATION LEVEL, 27-7, 27-31
READ ONLY, 4-21
SET UNUSED option for columns, 10-6
SGA
See system global area
shadow processes, 8-22
share locks
share table locks (S), 27-24
shared global area (SGA), 7-2
See also system global area
shared mode
rollback segments, 4-27
shared pool, 7-6
allocation of, 7-10
ANALYZE command and, 7-11
dependency management and, 7-11
described, 7-6
flushing, 7-11
object dependencies and, 21-10
overview of, 1-16
procedures and packages, 18-17
row cache and, 7-10
size of, 7-6
shared server processes (Snnn), 8-14, 8-19
described, 8-19
shared servers, 1-18
cannot connect with administrator privileges, 5-3
shared SQL areas, 7-8, 16-7
ANALYZE command and, 7-11
dependency management and, 7-11
described, 7-8
loading SQL into, 16-11
overview of, 1-16, 16-7
parse locks and, 27-29
procedures, packages, triggers and, 7-10
size of, 7-8
SHARED_MEMORY_ADDRESS parameter, 7-13
SHARED_POOL_SIZE parameter, 7-6
system global area size and, 7-13
shutdown, 5-9, 5-10
abnormal, 5-6, 5-10
audit record, 31-5
deallocation of the SGA, 7-2
prohibited by dispatcher processes, 8-20
steps, 5-9
SHUTDOWN ABORT command, 5-10
crash recovery required, 32-4
SIDs in LISTENER.ORA file, 6-6
signature checking, 21-11
Simple Network Management Protocol (SNMP)
database management, 33-20
Simple Network Management Protocol (SNMP) support
database management, 33-20
SINGLE TABLE HASHKEYS, 10-57
single-table hash clusters, 10-57
site autonomy, 1-35, 33-16
skewing parallel DML workload, 26-19
SKIP_UNUSABLE_INDEXES parameter, 21-8
SMON background process, 8-11
See also system monitor process
SMP architecture
disk affinity, 26-49
snapshot
read-only, 34-8
refresh, 8-13
same as materialized view, 1-25, 34-3
updateable, 34-9
snapshot refresh
job queue processes (SNPn), 1-20, 8-13
snapshot too old message, 27-5
snapshots
group, 34-6
site, 34-5
SNMP support
database management, 33-20
Snnn background processes, 8-14
SNPn background processes, 1-20, 8-13
message propagation, 19-10
software code areas, 7-17
shared by programs and utilities, 7-17
SOME, 23-6
sort areas, 7-16
sort operations, 3-12
sort segments, 3-12
SORT_AREA_RETAINED_SIZE parameter, 7-16
SORT_AREA_SIZE parameter, 4-18, 7-16
cost-based optimization and, 24-9
sort-merge joins, 24-4
access path, 23-46
cost-based optimization, 24-9
example, 23-46
space management
compression of free space in blocks, 4-9
data blocks, 4-5
direct-load INSERT, 25-8
extents, 4-11
MINIMUM EXTENT parameter, 26-33
parallel DDL, 26-33
PCTFREE, 4-6
PCTUSED, 4-6
row chaining, 4-10
segments, 4-16
spatial applications
index-organized tables, 10-42
SPLIT PARTITION command
no-logging mode, 11-58, 25-7
rules of parallelism, 26-25
SQL, 16-2
cursors used in, 16-6
Data Definition Language (DDL), 16-4
Data Manipulation Language (DML), 16-3
dynamic SQL, 16-19
embedded, 1-53, 16-5
user-defined datatypes, 13-14
extension
partition or subpartition name, 11-63
functions, 16-2
column default values, 10-8
COUNT, 10-35
in CHECK constraints, 28-18
NVL, 10-8
optimizing view queries, 23-23
memory allocation for, 7-11
overview of, 1-52, 16-2
parallel execution, 26-2
parsing of, 16-7
PL/SQL and, 1-55, 16-15
recursive, 16-6
cursors and, 16-7
reserved words, 16-3
session control statements, 16-5
shared SQL, 16-7
statement-level rollback, 17-4
system control statements, 16-5
transaction control statements, 16-5
transactions and, 1-53, 17-2, 17-5
types of statements in, 1-52, 16-3
optimizing, 23-3
user-defined datatypes, 13-13, 14-8
embedded SQL, 13-14
OCI, 13-15
SQL areas
private, 7-8
persistent, 7-8
runtime, 7-8
shared, 1-16, 7-8, 16-7
SQL statements, 1-52, 16-3, 16-8
array processing, 16-13
auditing, 31-7, 31-9
overview, 1-44
when records generated, 31-4
complex, 23-3, 23-13
optimizing, 23-13
converting
examples of, 23-10
creating cursors, 16-11
dictionary cache locks and, 27-30
distributed
defined, 23-4, 33-11
optimization of, 23-30
routing to nodes, 16-11
embedded, 16-5
execution, 16-8, 16-13
execution plans of, 22-2
failure in, 32-2
handles, 1-17
number of triggers fired by single, 20-21
optimization
complex statements, 23-13
types of statements, 23-3
overview, 1-52
parallel execution, 26-2
parallelizing, 26-2, 26-10
parse locks, 27-29
parsing, 16-11
privileges required for, 30-3
recursive
OPTIMIZER_GOAL does not affect, 23-32
referencing dependent objects, 21-4
remote
defined, 23-4, 33-11
resource limits and, 29-16
simple, 23-3
successful execution, 17-3
transactions, 16-14
triggers on, 20-2, 20-8
triggering events, 20-6
types of, 1-52, 16-3, 23-3
SQL*Loader, 1-6
direct load
NOLOGGING mode, 11-58, 25-7
parallel direct load, 25-2
similar to direct-load INSERT, 25-2
partition operations, 11-48, 11-50
SQL*Menu
PL/SQL, 16-16
SQL*Module
FIPS flagger, 16-6
stored procedures, 16-18
SQL*Net
See Net8
SQL*Plus
ALERT file, 8-15
anonymous blocks, 16-17
connecting with, 29-4
executing a package, 18-6
executing a procedure, 18-4
lock and latch monitors, 27-29
parallel recovery, 32-11
session variables, 16-17
showing size of SGA, 7-13
SQL statements, 16-2
statistics monitor, 29-19
stored procedures, 16-18
SQL_TRACE parameter, 8-15
SQL92, 27-2
stack space, 7-14
standards, 1-3
ANSI/ISO, 1-3, 28-5, 28-16
isolation levels, 27-2, 27-10
FIPS, 16-6
integrity constraints, 28-5, 28-16
Oracle adherence, 1-3
standby database
mounting, 5-7
survivability, 32-26
STAR hint, 24-15
star joins, 24-14
star query, 24-14
denormalized views, 24-15
extended star schemas, 24-15
hints, 24-15
indexes, 24-15
star transformation, 24-16
tuning, 24-15
star schemas
denormalized views, 24-15
star transformation, 24-16
example, 24-16
restrictions, 24-20
STAR_TRANSFORMATION hint, 24-19
STAR_TRANSFORMATION_ENABLED parameter, 24-19
startup, 5-2, 5-5
allocation of the SGA, 7-2
starting address, 7-13
audit record, 31-5
forcing, 5-6
prohibited by dispatcher processes, 8-20
recovery during, 32-4
restricted mode, 5-6
steps, 5-5
STARTUP FORCE command
crash recovery required, 32-4
statement triggers, 20-8
described, 20-8
when fired, 20-21
See also triggers
statement-level read consistency, 27-5
statements
See SQL statements
statistics
checkpoint, 8-11
estimated, 22-14
block sampling, 22-14
row sampling, 22-14
exporting and importing, 22-8
extensible optimization, 22-16
from ANALYZE, 22-13
from B*-tree or bitmap index, 22-12
generating and managing with DBMS_STATS, 22-12
HIGH_VALUE and LOW_VALUE, 23-52
optimizer goal, 23-32
optimizer mode, 23-31
optimizer use of, 22-7, 22-8, 23-32
partitioned tables and indexes, 11-14
partitions and subpartitions, 22-11
queuing, 19-11
selectivity of predicates, 22-8
histograms, 22-8, 22-10
user-defined, 22-17
user-defined statistics, 22-17
storage
clusters, 10-49
datafiles, 3-16
fragmentation in parallel DDL, 26-33
hash clusters, 10-51
index partitions, 11-37
indexes, 10-26
logical structures, 3-6, 10-2
nested tables, 14-19
nulls, 10-7
object tables, 14-18
parallel INSERT, 25-8
REFs, 14-19
restricting for users, 29-14
revoking tablespaces and, 29-14
table partitions, 11-27
tablespace quotas and, 29-14
triggers, 20-2, 20-25
user quotas on, 1-42
view definitions, 10-14
STORAGE clause
parallel execution, 26-33
using, 4-11
storage parameters
MAXEXTENTS UNLIMITED, 26-40
NEXT, 25-8
calculating, 25-9
OPTIMAL (in rollback segments), 4-25, 26-40
parallel direct-load INSERT, 25-8
PCTINCREASE, 25-8, 25-10
setting, 4-11
stored functions, 1-26, 18-2, 18-6
stored procedures, 1-26, 16-15, 18-2, 18-6
calling, 16-18
contrasted with anonymous blocks, 18-9
triggers contrasted with, 20-2
variables and constants, 16-17
See also procedures
Structured Query Language (SQL), 1-52, 16-2
See also SQL
structures
data blocks
shown in rowids, 12-18
data dictionary, 1-30, 2-1
datafiles
shown in rowids, 12-18
locking, 27-28
logical, 1-5, 1-9, 4-1
data blocks, 1-10, 4-2, 4-3
extents, 1-10, 4-2, 4-11
schema objects, 1-10, 10-2
segments, 1-10, 4-2, 4-16
tablespaces, 1-9, 3-1, 3-6
memory, 1-14, 7-1
physical, 1-5, 1-11
control files, 1-13, 32-21
datafiles, 1-11, 3-1, 3-16
redo log files, 1-12, 32-7
processes, 1-14, 1-17, 8-1
subpartition locks
DML, 11-46
SUBPARTITION option, 11-63
subpartitions
statistics, 22-11
subqueries, 16-12
CHECK constraints prohibit, 28-18
converting to joins, 23-13
in DDL statements, 26-31
in DML statements
serializable isolation, 27-14
in remote updates, 33-11
inline views, 10-16
NOT IN, 24-13
optimizing IN subquery, 23-17
query processing, 16-12
See also queries
subscriptions
rule-based, 19-5, 19-6
summaries, 10-17
SunSoft's SunNet Manager, 33-20
supplied packages, 18-16
invoker or definer rights, 30-8
survivability, 32-26
synchronous data propagation, 34-16
synonyms, 21-8
constraints indirectly affect, 28-5
described, 10-20
for data dictionary views, 2-4
inherit privileges from object, 30-3
overview of, 1-26
partition-extended table names, 11-65
private, 10-20
public, 10-20
uses of, 10-20
SYS username
data dictionary tables owned by, 2-3
security domain of, 29-3
statement execution not audited, 31-5
temporary schema objects owned by, 29-14
V$ views, 2-7
SYS.AUD$ view
purging, 2-5
SYSDBA privilege, 5-3
SYSOPER privilege, 5-3
system change numbers (SCN)
committed transactions, 17-5
defined, 17-5
read consistency and, 27-5
redo logs, 8-10
when determined, 27-5
system control statements, 1-53, 16-5
system global area (SGA), 7-2
allocating, 5-5
contents of, 7-3
data dictionary cache, 2-4, 7-10
database buffer cache, 7-3
diagram, 5-2
fixed, 7-3
large pool, 7-12
limiting private SQL areas, 29-18
overview of, 1-16, 7-2
redo log buffer, 7-6, 17-5
rollback segments and, 17-5
shared and writable, 7-3
shared pool, 7-6
size of, 7-12
variable parameters, 5-4
when allocated, 7-2
system monitor process (SMON), 8-11
defined, 1-19, 8-11
instance recovery, 32-4
parallel DML instance recovery, 26-41
parallel DML system recovery, 26-41
Parallel Server and, 8-11, 26-41
rolling back transactions, 32-10
temporary segment cleanup, 8-11
system privileges, 30-2
ADMIN OPTION, 14-14, 30-3
described, 30-2
granting and revoking, 30-3
user-defined types, 14-13
See also privileges
SYSTEM rollback segment, 4-26
SYSTEM tablespace, 3-6
data dictionary stored in, 2-2, 2-5, 3-6
datafile 1, 3-16
media failure, 32-6
online requirement of, 3-9
procedures stored in, 3-6, 18-18
SYSTEM username
security domain of, 29-3

T

table directories, 4-4
tables
affect dependent views, 21-5
auditing, 11-63, 31-8
base, 1-24
data dictionary use of, 2-2
relationship to views, 10-12
clustered, 10-46
contain integrity constraints, 1-58
contained in tablespaces, 10-5
controlling space allocation for, 10-4, 25-8
dimensions
star queries, 24-14
DUAL, 2-7
dynamic partitioning, 26-6
enable or disable constraints, 28-22
fact tables
star queries, 24-14
full table scan and buffer cache, 7-4
hash, 10-55
historical, 26-38
how data is stored in, 10-4
indexes and, 10-21
index-organized
key compression in, 10-30, 10-38
index-organized tables, 10-36
logical rowids, 10-39, 12-20
integrity constraints, 28-2, 28-5
locks on, 11-45, 27-21, 27-23, 27-25
lookup tables, 24-14
maximum number of columns in, 10-12
nested tables, 10-9, 13-12
indexes, 14-6
no-logging mode, 25-7
normalization
star schemas, 24-15
normalized or denormalized, 1-29, 10-19
object tables, 13-3, 13-7
constraints, 14-5
indexes, 14-6
triggers, 14-6
virtual, 15-2
overview of, 1-23, 10-3
parallel creation, 26-31
parallel DDL storage, 26-33
parallel table scans, 26-4
PARTITION option, 11-63
partition-extended table names, 11-63
partitions, 11-2, 11-27
presented in views, 10-11
privileges for partitions, 11-62
privileges on, 30-5
queue tables, 19-4, 19-12
refreshing in data warehouse, 26-37
replicating, 1-36
single-table hash clusters, 10-57
specifying tablespaces for, 10-5
STORAGE clause with parallel execution, 26-33
SUBPARTITION option, 11-63
summary or rollup, 26-31
table aliases, 14-8, 14-9
table names
qualifying column names, 14-8, 14-9
temporary, 10-10
segments in, 4-18
triggers used in, 20-2
validate or novalidate constraints, 28-22
virtual or viewed, 1-24
with LOB columns
partitioning, 11-38
tablespace point-in-time recovery
clone database, 5-7
tablespaces, 3-6
contrasted with schemas, 10-2
default for object creation, 1-42, 29-13
described, 3-6
dictionary-managed, 3-7
how specified for tables, 10-5
locally-managed, 3-8
temporary tablespaces, 3-13
locks on, 27-30
MINIMUM EXTENT
parallel DML, 25-10
moving or copying to another database, 3-14
no-logging mode, 25-7
offline, 1-10, 3-9, 3-17
and index data, 3-10
cannot be read-only, 3-11
remain offline on remount, 3-9
online, 1-10, 3-9, 3-17
overview of, 1-9, 3-6
quotas on, 1-42, 1-43, 29-13, 29-14
limited and unlimited, 29-14
no default, 29-14
read-only, 3-10
dropping objects from, 3-12
transition mode, 3-11
relationship to datafiles, 3-2
revoking access from users, 29-14
size of, 3-4
space allocation, 3-7
temporary, 1-43, 3-12
default for user, 29-13
transition read only mode, 3-11
transportable, 3-13
used for temporary segments, 4-16, 4-18
See also SYSTEM tablespace
TAF, 32-14
tasks, 8-2
tempfiles, 3-17
temporary segments, 4-16, 4-18, 10-10
allocating, 4-18
allocation for queries, 4-18
deallocating extents from, 4-16
dropping, 4-16
ignore quotas, 29-14
operations that require, 4-18
parallel DDL, 26-33
parallel INSERT, 25-8
tablespace containing, 4-16, 4-18
when not in redo log, 4-19
temporary tables, 10-10
temporary tablespaces, 3-12
threads
multi-threaded server, 8-14, 8-16
three-valued logic (true, false, unknown)
produced by nulls, 10-8
throughput, 22-7
cost-based approach, 23-31
timestamp checking, 21-11
TO_CHAR function
data conversion, 12-23
Julian dates, 12-11
NLS default in CHECK constraints, 28-18
NLS default in views, 10-14
TO_DATE function, 12-10
data conversion, 12-23
Julian dates, 12-11
NLS default in CHECK constraints, 28-18
NLS default in views, 10-14
partitions, 11-14, 11-21
TO_NUMBER function, 12-9
data conversion, 12-23
Julian dates, 12-11
NLS default in CHECK constraints, 28-18
NLS default in views, 10-14
trace files, 8-14
ARCn trace file, 32-20
DBWn trace file, 32-6
LGWR trace file, 8-9
transaction control statements, 1-53, 16-5
in autonomous PL/SQL blocks, 17-10
transaction set consistency, 27-10
transaction tables, 4-20
reset at recovery, 8-11
transactions, 1-53, 17-1
advanced queuing, 19-3
assigning system change numbers, 17-5
assigning to rollback segments, 4-21
asynchronous processing, 19-2
autonomous, 17-9
within a PL/SQL block, 17-9
block-level recovery, 27-21, 32-14
committing, 1-54, 8-10, 17-3, 17-5
group commits, 8-10
use of rollback segments, 4-21
concurrency and, 27-15
controlling transactions, 16-14
dead, 32-4
deadlocks and, 17-4, 27-17
defining and controlling, 16-14
described, 17-2
discrete transactions, 16-14, 17-8
distributed, 1-32
deadlocks and, 27-18
parallel DDL restrictions, 26-28
parallel DML restrictions, 26-28, 26-46
resolving automatically, 8-12
two-phase commit, 1-35, 17-7, 33-13
distribution among rollback segments of, 4-21
end of, 17-4
consistent data, 16-14
in-doubt
limit rollback segment access, 4-30
resolving automatically, 1-36, 5-8, 17-8
resolving manually, 1-36
rollback segments and, 4-24
use partly available segments, 4-30
manual locking of, 27-31
overview of, 1-53
read consistency of, 1-31, 27-6
read-only, 1-32, 27-6
not assigned to rollback segments, 4-20
recovery, 32-4
redo log files written before commit, 8-10
rollback segments and, 4-20
rolling back, 1-54, 17-6
and offline tablespaces, 4-30
partially, 17-6
use of rollback segments, 4-20
savepoints in, 1-55, 17-7
serializable, 27-7
space used in data blocks for, 4-5
start of, 17-4
statement level rollback and, 17-4
system change numbers, 8-10
terminating the application and, 17-5
transaction control statements, 16-5
triggers and, 20-23
two-phase commit in parallel DML, 26-40
writing to rollback segments, 4-22
TRANSACTIONS parameter, 4-27
TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter, 4-27
transition read only tablespaces, 3-11
Transparent Application Failover, 32-14
transportable tablespaces, 3-13
triggers, 1-59, 20-1, 21-8
action, 20-7
timing of, 20-9
AFTER triggers, 20-9
as program units, 1-56
auditing, 31-8
BEFORE triggers, 20-9
cascading, 20-4
constraints apply to, 20-21
constraints contrasted with, 20-5
data access and, 20-23
dependency management of, 20-25, 21-6
enabled triggers, 20-21
enabled or disabled, 20-21
enforcing data integrity with, 28-4
events, 20-6
examples of, 20-10, 20-13, 20-23
firing (executing), 20-2, 20-25
privileges required, 20-25
steps involved, 20-21
timing of, 20-21
INSTEAD OF triggers, 20-12
object views and, 15-5
INVALID status, 21-2, 21-6
Java, 20-7
maintain data integrity, 1-59
Oracle Forms triggers vs., 20-3
overview of, 1-59, 20-2
parts of, 20-5
privileges for executing, 30-8
roles, 30-20
procedures contrasted with, 20-2
prohibited in views, 10-12
publish/subscribe support, 20-18
restrictions, 20-7, 26-46
direct-load INSERT, 25-12
parallel DML, 26-44
row, 20-8
schema object dependencies, 20-21, 20-25
sequence for firing multiple, 20-21
shared SQL areas and, 7-10
statement, 20-8
storage of, 20-25
types of, 20-8
UNKNOWN does not fire, 20-7
user-defined types, 14-6
uses of, 20-3
TRUNCATE command, 16-4
two-phase commit
described, 1-35, 33-13
manual override of, 1-36
parallel DML, 26-40
transaction management, 17-7
triggers, 20-21
two-task mode, 8-3
listener process and, 8-14
network communication and, 8-23
program interface in, 8-23
types
privileges on, 30-11
See datatypes, object types

U

undo, 1-11
See also rollback
UNION ALL operator
examples, 23-11, 23-13, 23-27
optimizing view queries, 23-16
transforming OR into, 23-10
UNION ALL views, 11-11
UNION operator
compound queries, 23-4
examples, 23-18, 23-28
optimizing view queries, 23-16
unique indexes, 10-22
UNIQUE key constraints, 28-8
composite keys, 28-9, 28-11
constraint checking, 28-20
indexes used to enforce, 28-10
maximum number of columns, 28-10
NOT NULL constraints and, 28-11
nulls and, 28-11
size limit of, 28-10
unique keys, 1-58, 1-59, 28-9
composite, 28-9, 28-11
optimization, 23-14
searches, 23-40
UNLIMITED extents, 26-40
UNUSABLE indexes
function-based, 21-8
UNUSED column, 10-6
UPDATE command, 16-4
foreign key references and, 28-16
freeing space in data blocks, 4-9
no-logging mode, 25-7
LOBs, 25-7
parallel UPDATE, 26-21
triggers and, 20-2, 20-6
BEFORE triggers, 20-9
update no action constraint, 28-16
UPDATE privilege for object tables, 14-15, 14-16
updateable snapshot, 34-9
updates
distributed, 33-11
location transparency and, 33-15
object views, 15-5
updatability of object views, 15-5
updatability of views, 10-15, 20-12, 20-13
updatable join views, 10-15
update intensive environments, 27-8
UROWID datatype, 12-15
USE_INDIRECT_DATA_BUFFERS parameter, 7-14
USE_STORED_OUTLINES session parameter, 22-6
user locks, 27-39
user processes
allocate PGAs, 7-14
connections and, 8-4
dedicated server processes and, 8-22
manual archiving by, 32-21
sessions and, 8-4
shared server processes and, 8-19
user program interface (UPI), 8-25
USER pseudocolumn, 30-7
USER_ views, 2-6
USER_TAB_COL_STATISTICS view, 23-52
USER_TAB_COLUMNS view, 23-52
USER_TABLES view, 23-52
USER_UPDATABLE_COLUMNS view, 10-15
user-defined costs, 22-17
user-defined datatypes, 13-1, 13-3, 14-1
collections, 13-10
nested tables, 13-12
variable arrays (VARRAYs), 13-11
Export and Import, 14-20
incomplete types, 14-16
object types, 13-2, 13-4
use of table aliases, 14-8
object-relational model, 1-22
privileges, 14-13
storage, 14-18
user-defined operator, 10-44
users, 29-2
access rights, 29-2
associated with schemas, 10-2
auditing, 31-12
authentication of, 29-3
coordinating concurrent actions of, 1-30
current user, 18-10
dedicated servers and, 8-22
default tablespaces of, 29-13
distributed databases, 33-17
licensing by number of, 29-21
licensing of, 29-19
listed in data dictionary, 2-2
multiuser environments, 1-2, 8-2
password encryption, 29-7
privileges of, 1-41
processes of, 1-17, 8-4
profiles of, 1-43, 29-18
PUBLIC user group, 29-14, 30-19
resource limits of, 29-16
restrictions on resource use of, 1-42
roles and, 30-16
for types of users, 30-18
schemas of, 1-38, 29-2
security domains of, 1-40, 29-2, 30-19
tablespace quotas of, 1-43, 29-13
tablespaces of, 1-42
temporary tablespaces of, 1-43, 4-18, 29-13
usernames, 1-40, 29-2
sessions and connections, 8-4

V

V_$ and V$ views, 2-7
V$LICENSE, 29-20
VALIDATE constraints, 28-22
VALUES LESS THAN clause, 11-20
DATE datatype, 11-21
examples, 11-16, 11-18
MAXVALUE, 11-21, 11-23
multicolumn keys, 11-23
VARCHAR datatype, 12-6
VARCHAR2 datatype, 12-5
non-padded comparison semantics, 12-6
similarity to RAW datatype, 12-15
variables
bind variables
optimization, 23-52
user-defined types, 13-14
embedded SQL, 16-6
in stored procedures, 16-17
object variables, 15-4
VARRAYs, 13-11
index-organized tables, 10-38
key compression, 10-30
very large database (VLDB), 11-5
parallel SQL, 26-2
partitions, 11-5
views, 1-24, 10-11
altering base tables and, 21-5
auditing, 31-8
base tables, 1-24
complex view merging, 23-17
constraints and triggers prohibited in, 10-12
constraints indirectly affect, 28-5
containing expressions, 20-13
data dictionary
updatable columns, 10-15
user-accessible views, 2-3
definition expanded, 21-5
denormalized
star schemas, 24-15
dependency status of, 21-5
fixed views, 2-7
histograms, 22-11
how stored, 10-12
indexes and, 10-14
inherently modifiable, 20-13
inline views, 10-16
INSTEAD OF triggers, 20-12
INVALID status, 21-2
materialized views, 1-25, 10-17
same as snapshots, 1-25, 34-3
maximum number of columns in, 10-12
modifiable, 20-13
modifying, 20-12
NLS parameters in, 10-14
non-null values for nulls, 24-11
object views, 10-16, 15-1
updatability, 15-5
optimization, 23-15
overview of, 1-24, 10-11
partition statistics, 11-14
partition views, 11-11
prerequisites for compilation of, 21-5
privileges for, 30-6
pseudocolumns, 20-13
schema object dependencies, 10-15, 21-4, 21-8
security applications of, 30-6
select-project-join views, 23-15
SQL functions in, 10-14
statistics, 22-14
updatability, 10-15, 15-5, 20-13
uses of, 10-13
virtual memory, 7-17
virtual tables, 1-24
VLDB
parallel SQL, 26-2
partitions, 11-5

W

waits for blocking transaction, 27-10
Wallet Manager, 29-5
wallets, 29-5
warehouse
materialized views, 10-17
refreshing table data, 26-37
See also data warehousing
whole database backups, 1-50, 32-23
WITH OBJECT OID clause, 15-3, 15-4
workload skewing, 26-19
wrapper
procedural replication, 34-16
write-ahead, 8-9
writers block readers, 27-10

X

X.509 certificates, 29-5
XA
session memory in the large pool, 7-12

Y

year 2000, 12-12


Prev
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents