Oracle8 Concepts
Release 8.0
A58227-01
Library
Product
Contents
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
access control, 26-2
discretionary, 1-29
mandatory, 1-34
password encryption, 25-4
privileges, 26-2
roles, 26-10
access paths
cluster join, 20-50
composite index, 20-53
defined, 20-4
hash cluster key, 20-51
indexed cluster key, 20-51
list of, 20-46
optimization, 20-43
single row by cluster join, 20-48
single row by hash cluster key (with unique key), 20-49
single row by ROWID, 20-48
single row by unique or primary key, 20-49
ADMIN OPTION
roles, 26-14
system privileges, 26-3
with EXECUTE ANY TYPE, 12-11
administration request, 31-18
administrator privileges
not audited, 27-5
ADT,
See
object type
Advanced Networking Option, 30-17
data encryption, 30-17
Advanced Queuing (Oracle AQ), 16-1
exporting queue tables, 16-8
message queuing, 16-2
queue monitor process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queue tables, 16-4
remote databases, 16-7
advanced replication, 31-3
asynchronous propagation, 31-19
deferred transactions and, 31-20
hybrid configurations, 31-15
job queues and, 31-20
multi-master configuration, 31-13
overview, 31-11
procedural replication, 31-26
row-level replication, 31-19
RPCs and, 31-19
sequences and, 31-24
synchronous propagation, 31-27
updatable snapshots, 31-14
uses for, 31-12
affinity
parallel DML, 22-41
partitions, 22-40
AFTER triggers, 18-9
defined, 18-9
when fired, 18-14
agents for queuing, 16-4
ALERT files, 7-14
redo log, 7-10
aliases
qualifying column names, 12-2,
12-3
ALL, 20-16
ALL_ views, 4-6
ALL_ROWS hint, 20-43
ALL_UPDATABLE_COLUMNS view, 8-13
ALTER ANY TYPE privilege, 12-10
See also
privileges
ALTER command, 14-4
auditing partitions, 9-42
ALTER INDEX command
REBUILD PARTITION, 9-39
ALTER SESSION command, 14-5
dynamic parameters, 5-5
ENABLE PARALLEL DML, 22-32
HASH_JOIN_ENABLED, 20-73
OPTIMIZER_GOAL, 20-42
SET CONSTRAINTS DEFERRED, 24-20
transaction isolation level, 23-7,
23-30
ALTER SYSTEM command, 14-5
dynamic parameters, 5-5
ALTER TABLE command
auditing, 27-7
CACHE clause, 6-4
DEALLOCATE UNUSED, 2-13
disable or enable constraints, 24-21
EXCHANGE PARTITION, 9-10
no-logging mode for SPLIT PARTITION, 21-7
novalidate constraints, 24-21
ALTER USER command
temporary segments and, 2-16
alternate key
detecting conflicts and, 31-25
ALWAYS_ANTI_JOIN parameter, 20-79
ALWAYS_SEMI_JOIN parameter, 20-79
ANALYZE command, 14-4
COMPUTE STATISTICS clause, 20-42
creating histograms, 20-8
ESTIMATE STATISTICS clause, 20-42
partition statistics, 9-12
shared pool and, 6-11
anonymous PL/SQL blocks, 14-16,
17-9
applications, 14-18
calling a stored procedure, 14-19
contrasted with stored procedures, 17-9
dynamic SQL, 14-20
performance, 17-9
ANSI SQL standard
datatypes of, 10-19
Oracle certification, 1-3
ANSI/ISO SQL standard, 1-3
composite foreign keys, 24-15
data concurrency, 23-2
isolation levels, 23-10
anti-joins, 20-79
ANY, 20-15
applications
application vs. database triggers, 18-4
can find constraint violations, 24-6
data dictionary references, 4-4
data warehousing, 8-23,
20-80
database access through, 7-2
decision support systems (DSS), 8-24
parallel SQL, 22-2,
22-26
dependencies of, 19-9
direct-load INSERT, 22-32
discrete transactions, 15-8
enhancing security with, 1-31,
24-5
index-organized tables, 8-30
information retrieval (IR), 8-30
network communication and, 29-5
object dependencies and, 19-11
online analytical processing (OLAP), 8-31
online transaction processing (OLTP)
reverse key indexes, 8-22
parallel DML, 22-31
processes, 7-2,
7-4
program interface and, 7-27
roles and, 26-12
sharing code, 6-17
spatial applications, 8-31
transaction termination and, 15-5
AQ
exporting queue tables, 16-8
message queuing, 16-2
queue monitor process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queue tables, 16-4
remote databases, 16-7
AQ_ADMINISTRATOR role, 16-5
AQ_TM_PROCESS parameter, 16-4,
16-5
ARCH background process,
7-12
See also
archiver process
architecture
client/server, 1-23
MPP, 22-41
of Oracle, 1-13
SMP, 22-41
archived redo log, 1-38
automatic archiving, 28-17
enabling, 28-16
manual archiving, 28-18
ARCHIVELOG mode
archiver process (ARCH) and, 7-12,
28-16
defined, 28-16
overview, 1-39
partial database backups, 1-40,
28-22
whole database backups, 28-21
archiver process (ARCH)
automatic archiving, 28-17
defined, 1-18
described, 7-12
example, 28-16
array processing, 14-13
arrays
size of VARRAYs, 11-10
variable (VARRAYs), 11-10
asynchronous I/O
parallel recovery and, 28-14
asynchronous processing, 16-2
asynchronous replication, 31-19
atomic nulls, 12-6
attributes of object types, 11-2,
11-4
AUDIT command, 14-4
locks, 23-28
audit trail
deleting data in dictionary, 4-5
auditing, 1-33,
27-1
audit options, 27-3
audit records, 27-3
audit trails, 27-3
database, 27-3
operating system, 27-5,
27-6
by access, 27-11
mandated for, 27-11
by session, 27-10
prohibited with, 27-11
data dictionary used for, 4-5
database and OS usernames, 25-4
DDL statements, 27-7
described, 1-33,
27-2
distributed databases and, 27-6
DML statements, 27-7
partitioned tables and indexes, 9-42
privilege use, 27-2,
27-7
range of focus, 27-3,
27-9
schema object, 27-2,
27-8
security and, 27-6
statement, 27-2,
27-7
successful executions, 27-9
transaction independence, 27-5
types of, 27-2
unsuccessful executions, 27-9
user, 27-12
when options take effect, 27-6
authentication
described, 25-3
network, 25-4
operating system, 25-3
Oracle, 25-4
automatic refresh
refresh group, 31-9
refresh interval, 31-9
B
B*-tree indexes, 8-20
bitmap indexes vs., 8-23,
8-24
index-organized tables, 8-28
back-ends, 29-2
background processes, 1-17,
7-6
described, 7-6
diagrammed, 7-6
overview of, 1-17
trace files for, 7-14
See also
processes
backups
control files, 28-22
datafiles, 28-22
for read-only tablespaces, 28-23
overview of, 1-34,
28-21
parallel, 28-12
partial, 1-40,
28-22
Recovery Manager, 1-41,
28-10
types of, 1-39
using Export to supplement, 28-23
whole database backup, 1-40,
28-21
base tables, 1-43
data dictionary, 4-2
See also
views
basic replication, 31-2,
31-4
uses of, 31-4
BEFORE triggers, 18-8
defined, 18-8
when fired, 18-14
BETWEEN, 20-17
BFILE datatype, 10-11
binary data
BFILEs, 10-11
BLOBs, 10-10
RAW and LONG RAW, 10-11
bind variables
optimization, 20-64
user-defined types, 11-12
bitmap indexes, 8-23
nulls and, 8-8,
8-27
parallel query and DML, 8-24
partitioned tables, 9-12
scans of, 20-46
star transformation, 20-82
BLOBs, 10-10
blocking transactions, 23-10
blocks
anonymous, 14-16,
17-9
database, 2-3
See also
data blocks
BOOLEAN datatype, 10-2
branch blocks, 8-21
buffer cache, 6-3,
7-8
extended buffer cache (32-bit), 6-13
multiple buffer pools, 6-5
buffer pools, 6-5
BUFFER_POOL_KEEP parameter, 6-5
BUFFER_POOL_RECYCLE parameter, 6-5
buffers
database buffer cache, 1-15,
6-3,
7-8
incremental checkpoint, 7-9,
28-4
redo log buffer, 1-15,
6-6
business rules
enforcing in application code, 24-5
enforcing using stored procedures, 24-5
enforcing with constraints, 1-55,
24-1
advantages of, 24-5
enforcing with triggers, 1-56
C
CACHE clause, 6-4
caches
buffer cache, 6-3
multiple buffer pools, 6-5
cache hit, 6-4
cache miss, 6-4
data dictionary, 4-4,
6-10
location of, 6-6
database buffer, 1-15
library cache, 6-6
object cache, 11-13,
12-13
object views, 13-4
private SQL area, 6-8
shared SQL area, 6-6,
6-8
writing of buffers, 7-8
calls
Oracle call interface, 7-27
remote procedure, 30-11
cannot serialize access, 23-10
capture avoidance rule, 12-2
Cartesian products, 20-13
CASCADE actions
DELETE statements and, 24-16
catalog, replication, 31-18
century, 10-9
chaining of rows, 2-10,
8-5
CHAR datatype, 10-3
blank-padded comparison semantics, 10-3
character sets
CLOB and NCLOB datatyes, 10-10
column lengths, 10-4
for various languages, 5-4
NCHAR and NVARCHAR2, 10-4
CHECK constraints, 24-16
checking mechanism, 24-19
defined, 24-16
multiple constraints on a column, 24-17
partially null foreign keys, 24-15
partition views, 9-10
subqueries prohibited in, 24-16
checkpoint process (CKPT), 1-18,
7-11
checkpoints
checkpoint process (CKPT), 1-18,
7-11
control files and, 28-20
DBW
n
process, 7-11
incremental, 7-9,
28-4
signal DBW
n
process, 7-8
statistics on, 7-11
CHOOSE hint, 20-43
CKPT background process, 1-18,
7-11
CLEANUP_ROLLBACK_ENTRIES parameter, 22-34
client/server architectures, 29-2
clients, 1-24
diagrammed, 29-3
direct and indirect connections, 30-2
distributed databases and, 30-2
distributed processing in, 29-3
overview of, 1-23,
29-2
program interface, 7-27
CLOB datatype, 10-10
cluster joins, 20-71
cluster keys, 1-46,
8-32
clustered computer systems
Oracle Parallel Server, 5-2
clusters
choosing data to cluster, 8-34
defined, 1-46
dictionary locks and, 23-28
hash, 8-36
allocation of space for, 8-41
collision resolution, 8-39
contrasted with index, 8-36
root blocks, 8-41
scans of, 20-44,
20-49,
20-51
storage of, 8-37
index, 8-35
contrasted with hash, 8-36
scans of, 20-51
indexes and, 8-17
joins and, 8-34,
20-48,
20-50,
20-71
keys, 1-46,
8-32,
8-35
affect indexing of nulls, 8-8
overview of, 8-32
performance considerations of, 8-34
ROWIDs and, 8-7
scans of, 6-4,
20-44,
20-48
hash, 20-49,
20-51
joins, 20-50
setting parameters of, 8-34
storage format of, 8-34
storage parameters of, 8-4
collections, 11-10
nested tables, 11-11
variable arrays (VARRAYs), 11-10
column group, 31-25
shadow, 31-25
columns
column names
qualifying in queries, 12-2,
12-3
column objects, 11-8
indexes, 12-9
default values for, 8-8
defined, 1-43
described, 8-3
integrity constraints, 8-4,
8-8,
24-4,
24-7
maximum in concatenated indexes, 8-18
maximum in view or table, 8-10
nested tables, 8-9
order of, 8-7
prohibiting nulls in, 24-7
pseudocolumns
ROWID, 10-12
ROWNUM, 20-26,
20-35,
20-62
USER, 26-7
COMMENT command, 14-4
COMMIT command, 14-5
ending a transaction, 15-2,
15-4
fast commit, 7-10
implied by DDL, 15-2,
15-4
two-phase commit and, 15-7,
30-12
two-phase commit in parallel DML, 22-34
committing transactions
defined, 15-2
fast commit, 7-10
group commits, 7-10
implementation, 7-10
overview, 1-52
parallel DML, 22-34
communication protocols, 29-5
comparison methods, 11-6
compatibility, 1-4
compilation of object types, 12-14
compiled PL/SQL, 17-15
advantages of, 17-8
procedures, 17-9
pseudocode, 17-16,
18-17
recompiling, 17-17
shared pool, 14-17
triggers, 18-17
compiled triggers, 18-17
complete refresh, 31-8
complex snapshot, 31-10
complex view merging, 20-27
COMPLEX_VIEW_MERGING parameter, 20-27
composite indexes, 8-18
compression of free space in data blocks, 2-9
COMPUTE STATISTICS clause, 20-42
concatenated indexes, 8-18
concurrency
defined, 1-20
described, 23-2
direct-load INSERT, 21-9
enforced with locks, 1-22
limits on
per database, 25-14
per user, 25-12
partition maintenance, 9-33
restrictions on, 1-32,
21-9
transactions and, 23-15
configuration of a database
parameter file, 5-4
process structure, 7-2,
7-16
conflict resolution, 31-25
conflicts, 31-22
column groups and, 31-25
data models and, 31-23
delete, 31-23
detecting, 31-24
procedural replication, 31-26
replication, 31-22
resolving, 31-25
row-level replication, 31-24
uniqueness, 31-23
update, 31-23
CONNECT BY clause
optimizing view queries, 20-26
CONNECT INTERNAL, 5-3
CONNECT role, 26-16
user-defined types, 12-10,
12-11
connectibility, 1-4
connections
defined, 7-4
embedded SQL, 14-6
listener process and, 7-14
restricting, 5-5
sessions contrasted with, 7-4
usernames, 25-2
with administrator privileges, 5-3
consistency of data, 1-52
multiversion consistency model, 1-21
See also
read consistency
constants
comparisons and, 20-14
in stored procedures, 14-18
when computed, 20-14
constraints, 1-55
alternatives to, 24-5
applications can find violations, 24-6
CHECK, 24-16
default values and, 24-19
defined, 8-4
disabling temporarily, 24-6
effect on performance, 24-6
enable or disable constraints, 24-21
enforced with indexes, 8-19
PRIMARY KEY, 24-11
UNIQUE, 24-9
FOREIGN KEY, 1-56,
24-12
mechanisms of enforcement, 24-17
NOT NULL, 24-7,
24-10
novalidate constraints, 24-21
object tables, 12-8
overview, 1-55
parallel create table, 22-22
PRIMARY KEY, 1-56,
24-10
prohibited in views, 8-11
referential
effect of updates, 24-15
self-referencing, 24-14
triggers cannot violate, 18-14
triggers contrasted with, 18-5
types listed, 1-55,
24-1
UNIQUE key, 1-56,
24-8
partially null, 24-10
what happens when violated, 24-5
when evaluated, 8-8
constructor methods, 1-54,
11-6,
12-4
literal invocation of, 12-7
contention
for data
deadlocks, 7-23,
23-16
lock escalation does not occur, 23-16
for rollback segments, 2-19
control files, 1-12,
28-19
backing up, 28-22
changes recorded, 28-20
checkpoints and, 28-20
contents, 28-19
how specified, 5-4
multiplexed, 1-39,
28-20
overview, 1-12,
28-19
physical database structure, 1-5
recovery and, 1-39
used in mounting database, 5-6
converting data
ANSI datatypes, 10-19
program interface, 7-27
SQL/DS and DB2 datatypes, 10-19
coordinated sequence generation, 31-24
cost-based optimization, 20-6
histograms, 20-8
statistics, 20-42
CPU time limit, 25-11
CREATE ANY TYPE privilege, 12-10
See also
privileges
CREATE command, 14-4
CREATE FUNCTION command, 17-15
CREATE INDEX command
no-logging mode, 21-7
object types, 12-9
rules of parallelism, 22-21
temporary segments and, 2-16
CREATE PACKAGE BODY command, 17-10,
17-15
CREATE PACKAGE command
examples, 17-10,
18-10
locks, 23-28
package name, 17-15
CREATE PROCEDURE command
example, 17-6
locks, 23-28
procedure name, 17-15
CREATE SYNONYM command
locks, 23-28
CREATE TABLE AS SELECT
direct-load INSERT vs., 21-2
no-logging mode, 21-7
rules of parallelism, 22-22
space fragmentation, 22-27
CREATE TABLE command
auditing, 27-7,
27-9
CACHE clause, 6-4
enable or disable constraints, 24-21
examples
column objects, 11-5,
12-2
nested tables, 11-11
object tables, 11-7,
11-11,
12-2,
12-8
locks, 23-28
parallelism, 22-26
CREATE TRIGGER command
compiled and stored, 18-17
examples, 18-10,
18-13,
18-17
object tables, 12-9
locks, 23-28
CREATE TYPE command
incomplete types, 12-13
nested tables, 11-4,
11-11,
12-7
object types, 11-4,
12-2,
12-6,
12-7
object views, 13-3
VARRAYs, 11-10
CREATE TYPE privilege, 12-10
See also
privileges
CREATE USER command
temporary segments and, 2-16
CREATE VIEW command
examples, 18-12
object views, 13-3
locks, 23-28
cross joins, 20-13
cursors
creating, 14-10
defined, 14-6
embedded SQL, 14-6
maximum number of, 14-7
object dependencies and, 19-8
opening, 6-9,
14-7
overview of, 1-15
private SQL areas and, 6-9,
14-6
recursive, 14-7
recursive SQL and, 14-7
stored procedures and, 14-18
D
dangling REFs, 11-9
data
access to, 1-49
control of, 25-2
message queues, 16-5
security domains, 25-2
concurrent access to, 23-2
consistency of
defined, 1-52
examples of lock behavior, 23-30
locks, 23-3
manual locking, 23-29
read consistency, 1-21
repeatable reads, 23-6
transaction level, 23-6
underlying principles, 23-14
distributed manipulation of, 1-25
how stored in tables, 8-4
integrity of, 1-20,
8-4,
24-2
CHECK constraints, 24-16
enforcing, 24-4,
24-5
overview, 1-55
parallel DML restrictions, 22-38
referential, 24-3
two-phase commit, 1-25
types, 24-2
locks on, 23-19
replicating, 1-26,
31-2
data blocks, 1-10,
2-2
allocating for extents, 2-11
cached in memory, 7-8
clustered, 8-34
coalescing free, 2-12
controlling free space in, 2-5
format, 2-3
free lists and, 2-9
hash keys and, 8-41
how rows stored in, 8-5
overview, 2-2
read-only transactions and, 23-30
row directory, 8-6
shared in clusters, 8-32
shown in ROWIDs, 10-13,
10-14
space available for inserted rows, 2-9
stored in the buffer cache, 6-3
writing to disk, 7-8
data conversion
ANSI datatypes, 10-19
program interface, 7-27
SQL/DS and DB2 datatypes, 10-19
Data Definition Language (DDL)
auditing, 27-7
commit implied by, 15-4
defined, 1-50
described, 14-4
locks, 23-27
parallel DDL, 22-3
parsing with DBMS_SQL, 14-20
processing statements, 14-14
roles and privileges, 26-14
data dictionary
access to, 4-2
adding objects to, 4-4
ALL prefixed views, 4-6
audit trail (SYS.AUD$), 4-5
backups, 28-23
cache, 6-10
location of, 6-6
content of, 4-2,
6-10
procedures, 17-16
DBA prefixed views, 4-6
defined, 1-48,
4-2
dependencies tracked by, 19-3
DUAL table, 4-7
dynamic performance tables, 4-7
locks, 23-27
owner of, 4-3
prefixes to views of, 4-5
public synonyms for, 4-4
row cache and, 6-10
statistics in, 20-42
partition statistics, 9-12
structure of, 4-2
updates of, 4-5
USER prefixed views, 4-6
uses of, 4-3
table and column definitions, 14-11
validity of procedures, 17-16
views used in optimization, 20-7
data locks
conversion, 23-16
duration of, 23-15
escalation, 23-16
Data Manipulation Language (DML)
auditing, 27-7
defined, 1-50
described, 14-3
distributed transactions, 30-10
locks acquired by, 23-24
parallel DML, 22-3,
22-29
partition locks, 9-30
privileges controlling, 26-5
processing statements, 14-10
serializable isolation for subqueries, 23-13
transaction model for parallel DML, 22-33
triggers and, 18-3,
18-16
data models, 1-41
data object number
extended ROWID, 10-13
data ownership models, 31-23
dynamic ownership, 31-23
primary ownership, 31-23
shared ownership, 31-24
static ownership, 31-23
data segments, 1-11,
2-15,
8-4
data warehousing, 20-80
bitmap indexes, 8-23
refreshing table data, 22-31
star queries, 20-80
database administrators (DBAs)
authentication, 25-6
data dictionary views, 4-6
DBA role, 12-10,
26-16
password files, 25-7
responsible for backup and recovery, 28-2
database buffers
after committing transactions, 15-6
buffer cache, 6-3,
7-8
clean, 7-8
committing transactions, 7-10
defined, 1-15,
6-3
dirty, 6-3,
7-8
free, 6-3
multiple buffer pools, 6-5
pinned, 6-3
size of cache, 6-5
writing of, 7-8
database links, 1-48
defined, 1-48
overview of, 30-6
database management system (DBMS), 1-2
object-relational DBMS, 11-2
Oracle server, 1-4
principles, 1-41
database triggers, 1-56,
18-1
See also
triggers
database writer process (DBW
n
), 7-8
checkpoints signal, 7-8
defined, 7-8
least recently used algorithm (LRU), 7-8
media failure, 28-6
multiple DBW
n
processes, 7-8
multiple I/O processes, 7-9
overview of, 1-17
trace file, 28-6
when active, 7-8
write-ahead, 7-10
writing to disk at checkpoints, 7-11
databases
access control
overview, 1-49
password encryption, 25-4
security domains, 25-2
backing up, 1-40,
28-21
closing, 5-8
aborting the instance, 5-8
configuring, 5-4
contain schemas, 25-2
defined, 1-8
dismounting, 5-8
distributed, 1-24,
30-1
changing global database name, 6-11
nodes of, 1-24,
30-2
overview of, 1-23,
1-24,
30-1
site autonomy of, 30-15
statement optimization on, 20-40
table replication, 1-26
two-phase commit, 1-25
global database names, 30-4
limitations on usage, 25-10
logical structure of, 1-6
logical structures (objects) in, 1-8
modes of archiving, 28-16
mounting, 5-6
name stored in control file, 28-19
open and closed, 5-2
opening, 5-7
acquiring rollback segments, 2-23
physical structure, 1-5,
1-11,
2-2
revealing with ROWIDs, 10-14
recovery of, 1-34,
28-2
scalability, 22-2,
22-31,
29-4
shutting down, 5-8
size of
how determined, 3-6
standby, 28-24
starting up, 5-2
forced, 5-9
datafiles
backing up, 28-22
contents of, 3-12
dictionary in datafile 1, 28-23
in online or offline tablespaces, 3-12
named in control files, 28-19
overview of, 1-9,
1-11,
3-11
parallel recovery, 28-13
physical database structure, 1-5
read-only, 3-9
recovery, 28-5
read-only tablespaces and, 3-10
relationship to tablespaces, 3-2
shown in ROWIDs, 10-13,
10-14
taking offline, 3-12
unrecoverable, 28-13
datatypes, 10-2,
10-17
ANSI, 10-19
array types, 11-10
BOOLEAN, 10-2
CHAR, 10-3
character, 10-2,
10-10
collections, 11-10
conversions of
by program interface, 7-27
non-Oracle types, 10-19
Oracle to another Oracle type, 10-20
DATE, 10-7
DB2, 10-19
how they relate to tables, 8-3
in PL/SQL, 10-2
list of available, 10-2
LOB datatypes, 10-9
BFILE, 10-11
BLOB, 10-10
CLOB and NCLOB, 10-10
default logging mode, 21-7
LONG, 10-5
storage of, 8-7
MLSLABEL, 10-16
multimedia, 11-3
NCHAR and NVARCHAR2, 10-4
nested tables, 8-9,
11-11
NUMBER, 10-5
object types, 1-42,
11-4
of columns, 1-43
RAW and LONG RAW, 10-11
REF, 11-8
ROWID, 10-12
SQL/DS, 10-19
summary, 10-17
user-defined, 11-1,
11-3
VARCHAR, 10-3
VARCHAR2, 10-3
DATE datatype, 10-7
arithmetic with, 10-9
changing default format of, 10-7
Julian dates, 10-8
partitioning, 9-12,
9-16
DB_BLOCK_BUFFERS parameter
buffer cache and, 6-5
system global area size and, 6-12
DB_BLOCK_LRU_LATCHES parameter, 7-8
DB_BLOCK_MAX_DIRTY_TARGET parameter, 7-9,
28-5
DB_BLOCK_SIZE parameter
buffer cache and, 6-5
system global area size and, 6-12
DB_FILE_MULTIBLOCK_READ_COUNT parameter, 20-63
cost-based optimization, 20-75
DB_FILES parameter, 6-15
DB_NAME parameter, 28-20
DB_WRITER_PROCESSES parameter, 1-17,
7-8
DBA role, 26-16
user-defined types, 12-10
DBA_ views, 4-6
DBA_QUEUE_SCHEDULES view, 16-8
DBA_SYNONYMS.SQL script
using, 4-7
DBA_UPDATABLE_COLUMNS view, 8-13
DBMS, 1-2
general requirements, 1-49
object-relational DBMS, 11-2
DBMS_AQ package, 16-4
DBMS_AQADM package, 16-4,
16-5
DBMS_JOB package, 7-13
DBMS_LOCK package, 23-40
DBMS_SQL package, 14-20
parsing DDL statements, 14-20
DBW
n
background process, 7-8
See also
database writer process
DBWR_IO_SLAVES parameter, 7-9
DDL, 1-50,
14-4
See also
Data Definition Language
deadlocks
artificial, 7-23
avoiding, 23-18
defined, 23-16
detection of, 23-17
distributed transactions and, 23-17
deallocating extents, 2-13
decision support systems (DSS), 9-4
bitmap indexes, 8-24
disk striping, 22-41
parallel DML, 22-31
parallel SQL, 22-2,
22-26,
22-31
partitions, 9-4
performance, 9-7,
22-31
scoring tables, 22-32
dedicated servers, 7-18
defined, 1-17
examples of use, 7-25
multithreaded servers vs., 7-20
default values, 8-8
constraints effect on, 8-8,
24-19
user-defined types, 12-7
deferred constraints
deferrable or nondeferrable, 24-20
initially deferred or immediate, 24-20
deferred transactions, 31-20
DefError view
conflicts and, 31-25
define phase of query processing, 14-12
defining query of a snapshot, 31-7
degree of parallelism, 22-17,
22-19
between query operations, 22-11
parallel SQL, 22-7,
22-13
delete cascade constraint, 24-16
DELETE command, 14-4
foreign key references and, 24-15
freeing space in data blocks, 2-9
parallel DELETE, 22-18
triggers and, 18-2,
18-6
INSTEAD OF triggers, 18-11
delete conflict, 31-23
delete no action constraint, 24-15
DELETE privilege for object tables, 12-12,
12-13
dependencies
between schema objects, 19-2
local, 19-9
non-existent referenced objects and, 19-7
object type definitions, 12-13,
12-15
on non-existence of other objects, 19-7
Oracle Forms triggers and, 19-11
privileges and, 19-6
remote objects and, 19-9
shared pool and, 19-8
dereferencing, 11-9
implicit, 11-9
describe phase of query processing, 14-12
dictionary
See
data dictionary
dictionary cache locks, 23-29
different row-writers block writers, 23-10
Digital POLYCENTER Manager on NetView, 30-19
direct-load INSERT, 21-2
logging mode, 21-5
parallel INSERT, 21-3
parallel load vs. parallel INSERT, 21-2
restrictions, 21-9,
22-37
serial INSERT, 21-3
space management, 21-8
dirty buffer, 6-3
incremental checkpoint, 7-9,
28-4
dirty read, 23-2,
23-10
dirty write, 23-10
disable constraints, 24-21
disaster recovery, 28-24
discrete transaction management
summary, 15-8
discretionary access control, 1-29,
25-2
disk affinity
parallel DML, 22-41
partitions, 22-40
disk failures, 1-37,
28-5
disk space
controlling allocation for tables, 8-4
datafiles used to allocate, 3-11
disk striping
affinity, 22-40
partitions, 9-8
dispatcher processes (D
nnn
)
defined, 1-18
described, 7-13
limiting SGA space per session, 25-12
listener process and, 7-14
network protocols and, 7-14
prevent startup and shutdown, 7-24
response queue and, 7-21
user processes connect via Net8, 7-14,
7-20
DISTINCT operator
optimizing views, 20-27
distributed databases, 30-1
auditing and, 27-6
client/server architectures and, 29-2
database links, 30-6
deadlocks and, 23-17
dependent schema objects and, 19-9
diagrammed, 30-3
different Oracle versions, 30-7
distributed queries, 30-11
distributed updates, 30-11
global schema object names, 30-6
heterogeneous, 30-8
job queue processes (SNP
n
), 1-19,
7-13
management tools, 30-18
message propagation, 16-7
nodes of, 30-2
overview of, 1-24,
30-2
recoverer process (RECO) and, 7-12
remote dependencies, 19-9
remote queries and updates, 30-10
server can also be client in, 29-2
site autonomy of, 30-15
statement optimization on, 20-40
table replication, 1-26,
31-2,
31-3
for read and update, 31-11
for read only, 31-2,
31-4
transparency of, 30-13
two-phase commit, 1-25,
30-12
distributed processing environment
client/server architecture in, 1-23,
29-3
data manipulation statements, 14-10
described, 1-23,
29-2
distributed databases vs., 30-7
distributed transactions
defined, 30-11
optimizing, 20-40
parallel DML restrictions, 22-40
routing statements to nodes, 14-11
two-phase commit and, 1-25,
15-7
DISTRIBUTED_TRANSACTIONS parameter, 7-12
DML, 1-50,
14-3
See also
Data Manipulation Language
D
nnn
background processes, 7-13
See also
dispatcher processes
drivers, 7-27
DROP ANY TYPE privilege, 12-10
See also
privileges
DROP command, 14-4
DROP TABLE command
auditing, 27-7
DROP TYPE command
dependencies and, 12-15
FORCE option, 12-15
DSS database
disk striping, 22-41
parallel DML, 22-31
partitioning indexes, 9-29
partitions, 9-5
performance, 9-7
scoring tables, 22-32
DUAL table, 4-7
dump files
Export and Import, 12-15
dynamic ownership, 31-23
dynamic partitioning, 22-6
dynamic performance tables (V$ tables), 4-7
dynamic SQL
DBMS_SQL package, 14-20
E
embedded SQL statements, 1-50,
14-5
enable constraints, 24-21
encryption, 30-17
Enterprise Manager
advanced queuing, 16-9
ALERT file, 7-15
checkpoint statistics, 7-11
distributed databases, 30-18
executing a package, 17-6
executing a procedure, 17-4
granting roles, 26-13
granting system privileges, 26-3
lock and latch monitors, 23-28
parallel recovery, 28-13
PL/SQL, 14-18,
14-19
schema object privileges, 26-4
showing size of SGA, 6-12
shutdown, 5-8,
5-9
SQL statements, 14-2
startup, 5-5
statistics monitor, 25-13
equijoins
cluster joins, 20-71
defined, 20-13
hash joins, 20-73
sort-merge, 20-70
equipartitioning, 9-18
errors
in embedded SQL, 14-6
tracked in trace files, 7-14
ESTIMATE STATISTICS clause, 20-42
exceptions
during trigger execution, 18-15
raising, 14-19
stored procedures and, 14-19
EXCHANGE PARTITION, 9-10
exclusive locks
row locks (TX), 23-19
RX locks, 23-22
table locks (TM), 23-20
exclusive mode, 2-25,
5-6
EXECUTE ANY TYPE privilege, 12-10,
12-11
See also
privileges
EXECUTE privilege
user-defined types, 12-11,
12-12,
12-13
verifying user access, 17-16
See also
privileges
EXECUTE user-defined type, 12-10
execution plan
accessing views, 20-29,
20-32,
20-33
complex statements, 20-24
compound queries, 20-37,
20-38,
20-39
joining views, 20-35
joins, 20-67,
20-74
OR operators, 20-21
star transformation, 20-83
execution plans
examples, 20-24
execution sequence of, 20-5
EXPLAIN PLAN, 14-4
location of, 6-8
overview of, 20-2
parsing SQL, 14-11
partitions and partition views, 9-10,
9-12
viewing, 20-4
EXP_FULL_DATABASE role, 26-16
EXPLAIN PLAN command, 14-4
access paths, 20-48,
20-49,
20-50,
20-51,
20-52,
20-53,
20-54,
20-55,
20-57,
20-58,
20-59,
20-61,
20-62
star query, 20-82
star transformation, 20-83
explicit locking, 23-29
Export utility
partition maintenance operations, 9-31
use in backups, 28-23
user-defined types, 12-15
extended ROWID format, 10-12
extents
allocating data blocks for, 2-11
allocation to rollback segments
after segment creation, 2-21
at segment creation, 2-19
allocation, how performed, 2-11
as collections of data blocks, 2-10
deallocation
from rollback segments, 2-22
when performed, 2-13
defined, 2-3
dropping rollback segments and, 2-22
in rollback segments
changing current, 2-20
incremental, 2-11
overview of, 2-10
parallel DDL, 22-27
external procedures, 14-20,
17-9
F
failover database, 31-12
failures, 28-2
archiving redo log files, 28-18
database buffers and, 28-8
described, 1-35,
28-2
instance, 1-37,
28-4
recovery from, 28-4
internal errors
tracked in trace files, 7-14
media, 1-37,
28-5
network, 28-3
safeguards provided, 28-7
statement and process, 1-36,
7-12,
28-2
survivability, 28-24
user error, 1-35,
28-2
See also
recovery
fast commit, 7-10
fast full index scans, 20-45
fast refresh, 31-8
fast transaction rollback, 28-10
fast warmstart, 28-4
FAST_FULL_SCAN_ENABLED parameter, 20-45
fetching rows in a query, 14-14
embedded SQL, 14-6
file management locks, 23-29
files
ALERT and trace files, 7-10,
7-14
Export and Import dump file, 12-15
initialization parameter, 5-4,
5-5
operating system, 1-5
Oracle database, 1-9,
1-11,
28-7
password, 25-7
administrator privileges, 5-3
See also
control files, datafiles, redo log files
FIPS standard, 1-3,
14-6
FIRST_ROWS hint, 20-43
flagging of nonstandard features, 1-3,
14-6
FORCE option
object type dependencies, 12-15
FOREIGN KEY constraints
changes in parent key values, 24-15
constraint checking, 24-19
deleting parent table rows and, 24-16
maximum number of columns in, 24-12
nulls and, 24-14
updating parent key tables, 24-15
foreign keys, 1-55
defined, 1-56
partially null, 24-15
privilege to use parent key, 26-5
fragmentation
parallel DDL, 22-28
free lists, 2-9
free space (section of data blocks), 2-5
front-ends, 29-2
full index scans, 20-45
full table scans, 20-44,
20-61
LRU algorithm and, 6-4
multiblock reads, 20-63
parallel query, 22-5,
22-6
rule-based optimizer, 20-66
selectivity and, 20-63
functions
hash functions, 8-40
PL/SQL, 17-2,
17-6
contrasted with procedures, 1-53,
17-2
parallel DML restrictions, 22-39
privileges for, 26-7
roles disabled in, 26-14
See also
procedures
SQL, 14-2
COUNT, 8-27
default column values, 8-8
in CHECK constraints, 24-16
in views, 8-12
NVL, 8-7
optimizing view queries, 20-26,
20-33
fuzzy reads, 23-3
G
gateways, 30-8
global database names
shared pool and, 6-11
global indexes, 9-25,
9-27
managing partitions, 9-39
global schema object names, 1-48,
30-6
GRANT ANY PRIVILEGE system privilege, 26-3
GRANT command, 14-4
locks, 23-28
GRANT option for EXECUTE privilege, 12-11
granting
execute user-defined type, 12-11
privileges and roles, 26-3
GROUP BY clause
optimizing views, 20-27
group commits, 7-10
groups, instance, 22-16
H
handles for SQL statements, 1-15,
6-9
hash clusters, 1-48,
8-36
overview of, 1-48
scans of, 20-44,
20-49,
20-51
hash join, 20-73
HASH_AREA_SIZE parameter, 20-74
HASH_MULTIBLOCK_IO_COUNT parameter, 20-74
HASH_AJ hint, 20-79
HASH_AREA_SIZE parameter, 20-74
HASH_JOIN_ENABLED parameter, 20-73
HASH_MULTIBLOCK_IO_COUNT parameter, 20-74
HASH_SJ hint, 20-79
HASHKEYS parameter, 8-39
headers
of data blocks, 2-4
of row pieces, 8-5
heterogeneous distributed databases, 30-8
Heterogeneous Services, 30-10
HI_SHARED_MEMORY_ADDRESS parameter, 6-13
HIGH_VALUE column
of USER_TAB_COLUMNS view, 20-64
hints
INDEX, 20-81
INDEX_FFS, 20-45
MERGE, 20-27
MERGE_AJ and HASH_AJ, 20-79
MERGE_SJ and HASH_SJ, 20-79
ORDERED, 20-75,
20-81
overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 20-43
PARALLEL, 22-13
PARALLEL_INDEX, 22-13
PUSH_JOIN_PRED, 20-78
STAR, 20-81
USE_HASH, 20-73
histograms, 20-8
historical database
maintenance operations, 9-32
partitions, 9-5
HP OpenView, 30-19
hybrid configurations
advanced replication, 31-15
I
IBM NetView/6000, 30-19
identity column
detecting conflicts and, 31-25
immediate constraints, 24-19
IMP_FULL_DATABASE role, 26-16
implicit dereferencing, 11-9
Import utility
partition maintenance operations, 9-31
use in recovery, 28-23
user-defined types, 12-15
IN operator, 20-15
merging views, 20-28
IN subquery, 20-27
incomplete object types, 12-14
incremental checkpoint, 7-9,
28-4
index segments, 1-10,
2-15
INDEX_FFS hint, 20-45
indexes, 1-46,
8-17
auditing partitions, 9-42
B*-tree structure of, 8-20
bitmap indexes, 8-23,
8-28
nulls and, 8-8
parallel query and DML, 8-24
branch blocks, 8-21
building
using an existing index, 8-17
cluster, 8-35
contrasted with table, 8-36
dropping, 8-36
scans of, 20-51
composite, 8-18
scans of, 20-53
concatenated, 8-18
described, 1-46,
8-17
enforcing integrity constraints, 24-9,
24-11
fast full scans of, 20-45
global indexes, 9-25,
9-39
index unusable (IU), 9-39
index-organized tables, 8-28
internal structure of, 8-20
keys and, 8-19
primary key constraints, 24-11
unique key constraints, 24-9
leaf blocks, 8-21
local indexes, 9-23,
9-38
location of, 8-19
LONG RAW datatypes prohibit, 10-11
managing partitions, 9-38
no-logging mode, 21-7
non-unique, 8-17
nulls and, 8-8,
8-27
on attribute of object column, 12-9
on object identifiers, 12-5
on REFs, 12-9
optimization and, 20-20
overview of, 1-46,
8-17
parallel DDL storage, 22-27
parallel index scans, 22-5
partition pruning, 9-4
partitioned tables, 8-28
partitioning guidelines, 9-28
partitions, 9-2,
9-22
performance and, 8-17
privileges for partitions, 9-41
range scans, 20-45
rebuild partition, 9-39
rebuilt after direct-load INSERT, 21-8
reverse key indexes, 8-22
ROWIDs and, 8-21
scans of, 20-44
bounded range, 20-55
cluster key, 20-51
composite, 20-53
MAX or MIN, 20-59
ORDER BY, 20-59
restrictions, 20-61
single-column, 20-53
unbounded range, 20-57
statement conversion and, 20-20
storage format of, 8-19
unique, 8-17
unique scans, 20-45
user-defined types, 12-9
when used with views, 8-12
index-organized tables, 8-28
applications, 8-30
benefits, 8-29
queue tables, 16-8
row overflow area, 8-29
in-doubt transactions, 2-21,
5-7
information consolidation
advanced replication and, 31-14
Information Retrieval (IR) applications
index-organized tables, 8-30
initialization parameters
ALWAYS_ANTI_JOIN, 20-79
ALWAYS_SEMI_JOIN, 20-79
AQ_TM_PROCESS, 16-4,
16-5
BUFFER_POOL_KEEP, 6-5
BUFFER_POOL_RECYCLE, 6-5
CLEANUP_ROLLBACK_ENTRIES, 22-34
COMPLEX_VIEW_MERGING, 20-27
DB_BLOCK_BUFFERS, 6-5,
6-12
DB_BLOCK_LRU_LATCHES, 7-8
DB_BLOCK_MAX_DIRTY_TARGET, 7-9,
28-5
DB_BLOCK_SIZE, 6-5,
6-12
DB_FILE_MULTIBLOCK_READ_COUNT, 20-63,
20-75
DB_FILES, 6-15
DB_NAME, 28-20
DB_WRITER_PROCESSES, 1-17,
7-8
DBWR_IO_SLAVES, 7-9
DISTRIBUTED_TRANSACTIONS, 7-12
FAST_FULL_SCAN_ENABLED, 20-45
HASH_AREA_SIZE, 20-74
HASH_JOIN_ENABLED, 20-73
HASH_MULTIBLOCK_IO_COUNT, 20-74
HI_SHARED_MEMORY_ADDRESS, 6-13
JOB_QUEUE_PROCESSES, 16-8
LGWR_IO_SLAVES, 7-11
LICENSE_MAX_SESSIONS, 25-14
LICENSE_SESSIONS_WARNING, 25-15
LOCK_SGA, 6-12,
6-16
LOCK_SGA_AREAS, 6-12,
6-16
LOG_ARCHIVE_START, 28-17
LOG_BUFFER, 6-6,
6-12
LOG_CHECKPOINT_INTERVAL, 7-8
LOG_CHECKPOINT_TIMEOUT, 7-8
LOG_FILES, 6-15
MTS_MAX_SERVERS, 7-23,
7-24
MTS_SERVERS, 7-23
NLS_LANGUAGE, 9-15
NLS_NUMERIC_CHARACTERS, 10-6
NLS_SORT, 9-15
OPEN_CURSORS, 6-9,
14-7
OPEN_LINKS, 6-15
OPTIMIZER_MODE, 20-41
PARALLEL_DEFAULT_MAX_SCANS (obsolete), 22-15
PARALLEL_DEFAULT_SCANSIZE (obsolete), 22-15
PARALLEL_MAX_SERVERS, 22-8
PARALLEL_MIN_PERCENT, 22-15
PARALLEL_MIN_SERVERS, 22-7,
22-8
PARALLEL_SERVERS_IDLE_TIME, 22-8
PUSH_JOIN_PREDICATE, 20-78
REMOTE_DEPENDENCIES_MODE, 19-9
ROLLBACK_SEGMENTS, 2-24
SHARED_MEMORY_ADDRESS, 6-13
SHARED_POOL_SIZE, 6-6,
6-12
SORT_AREA_RETAINED_SIZE, 6-15
SORT_AREA_SIZE, 2-16,
6-15,
20-75
SORT_DIRECT_WRITES, 6-16
SQL_TRACE, 7-15
STAR_TRANSFORMATION_ENABLED, 20-85
TRANSACTIONS, 2-24
TRANSACTIONS_PER_ROLLBACK_SEGMENT, 2-24
USE_INDIRECT_DATA_BUFFERS, 6-13
initially deferred constraints, 24-20
initially immediate constraints, 24-20
INIT.ORA files, 5-4,
5-5
inner capture, 12-2
INSERT command, 14-4
direct-load INSERT, 21-2
free lists and, 2-9
parallelizing INSERT ... SELECT, 22-20
storage for parallel INSERT, 21-8
triggers and, 18-2,
18-6
BEFORE triggers, 18-9
INSTEAD OF triggers, 18-11,
18-13
INSERT privilege for object tables, 12-12,
12-13
instance groups for parallel operations, 22-16
instances, 1-6
acquire rollback segments, 2-24
associating with databases, 5-2,
5-6
defined, 1-15
described, 5-2
diagrammed, 7-6
failure in, 1-37,
28-4
instance groups, 22-16
memory structures of, 6-2
multiple-process, 7-3,
7-16
overview of, 1-6
process structure, 7-2
recovery of, 28-4
incremental checkpoints, 28-4
opening a database, 5-7
SMON process, 7-11
restricted mode, 5-5
sharing databases, 1-8
shutting down, 5-8,
5-9
single-process, 7-2
starting, 5-5
virtual memory, 6-16
INSTEAD OF triggers, 18-11
object views, 13-5
integrity constraints, 24-2
default column values and, 8-8
See also
constraints
integrity rules, 1-42
parallel DML restrictions, 22-38
INTERNAL connection, 5-3
audit records not generated by, 27-5
internal errors tracked in trace files, 7-14
inter-operator parallelism, 22-11
INTERSECT operator
compound queries, 20-14
example, 20-39
optimizing view queries, 20-26
intra-operator parallelism, 22-11
INVALID status, 19-3
IS NULL predicate, 8-7
ISO SQL standard, 1-3,
10-19
composite foreign keys, 24-15
isolation levels
choosing, 23-12
read committed, 23-7
setting, 23-7,
23-30
J
job queue processes (SNP
n
), 1-19,
7-13
automatic snapshot refresh, 31-9,
31-10
message propagation, 16-8
job queues, 31-20
snapshot refresh, 31-10
JOB_QUEUE_PROCESSES parameter, 16-8
jobs, 7-2
join views, 8-13
joins
anti-joins, 20-79
Cartesian products, 20-13
cluster, 8-34,
20-48,
20-71
searches on, 20-50
convert to subqueries, 20-23
cross, 20-13
defined, 20-13
encapsulated in views, 1-44,
8-11
equijoins, 20-13
execution plans and, 20-67
hash joins, 20-73
nested loops, 20-68
cost-based optimization, 20-74
nonequijoins, 20-13
optimization of, 20-75
outer, 20-13
non-null values for nulls, 20-77
select-project-join views, 20-25
semi-joins, 20-79
sort-merge, 20-70
cost-based optimization, 20-75
example, 20-58
views, 1-44,
8-13
K
keys
cluster, 1-46,
8-32
defined, 24-8
foreign, 24-12
hash, 8-39
in constraints, 1-56
indexes and, 8-19,
8-22,
24-9,
24-11
key values, 1-56
maximum storage for values, 8-18
parent, 24-12,
24-14
primary, 24-10
referenced, 1-56,
24-12
reverse key indexes, 8-22
searches, 20-49
unique, 24-8
composite, 24-8,
24-10
L
labels
MLSLABEL datatype, 10-16
latches
described, 23-28
LRU, 7-8
LCK
n
background processes, 7-13
See also
lock processes
leaf blocks, 8-21
least recently used algorithm (LRU)
database buffers and, 6-3
dictionary cache, 4-4
full table scans and, 6-4
latches, 7-8
shared SQL pool, 6-8,
6-10
LGWR background process,
7-9
See also
log writer process
LGWR_IO_SLAVES parameter, 7-11
LICENSE_MAX_SESSIONS parameter, 25-14
LICENSE_SESSIONS_WARNING parameter, 25-15
licensing
concurrent usage, 25-14
named user, 25-15
viewing current limits, 25-15
LIKE, 20-15
links, 30-6
listener processes, 7-14
literal invocation
constructor methods, 12-7
LOB datatypes, 10-9
BFILE, 10-11
BLOBs, 10-10
CLOBs and NCLOBs, 10-10
default logging mode, 21-7
local databases, 1-24
local indexes, 9-23,
9-27
bitmap indexes
on partitioned tables, 8-28
parallel query and DML, 8-24
managing partitions, 9-38
location transparency, 1-24
lock processes (LCK
n
), 1-19,
7-13
LOCK TABLE command, 14-4
LOCK_SGA parameter, 6-12,
6-16
LOCK_SGA_AREAS parameter, 6-12,
6-16
locks, 1-22,
23-3
after committing transactions, 15-6
automatic, 1-23,
23-14,
23-18
conversion, 23-16
data, 23-19
duration of, 23-15
deadlocks, 23-16,
23-17
avoiding, 23-18
dictionary, 23-27
clusters and, 23-28
duration of, 23-28
dictionary cache, 23-29
DML acquired, 23-26
diagrammed, 23-24
DML partition locks, 9-30
escalation does not occur, 23-16
exclusive table locks (X), 23-24
file management locks, 23-29
how Oracle uses, 23-14
internal, 23-28
latches and, 23-28
log management locks, 23-29
manual, 1-23,
23-29
examples of behavior, 23-30
object level locking, 11-14
Oracle Lock Management Services, 23-40
overview of, 1-22,
23-3
parallel cache management (PCM), 23-19
parallel DML, 22-36
parse, 14-11,
23-28
rollback segment, 23-29
row (TX), 23-19
row exclusive locks (RX), 23-22
row share table locks (RS), 23-22
share row exclusive locks (SRX), 23-24
share table locks (S), 23-23
share-sub-exclusive locks (SSX), 23-24
sub-exclusive table locks (SX), 23-22
sub-share table locks (SS), 23-22
table (TM), 23-20
table lock modes, 23-21
tablespace, 23-29
types of, 23-18
log management locks, 23-29
log sequence numbers, 1-38
log writer process (LGWR), 1-18,
7-9
archiving modes, 28-16
group commits, 7-10
manual archiving and, 28-18
multiple I/O processes, 7-11
redo log buffers and, 6-6
system change numbers, 15-5
write-ahead, 7-10
LOG_ARCHIVE_START parameter, 28-17
LOG_BUFFER parameter, 6-6
system global area size and, 6-12
LOG_CHECKPOINT_INTERVAL parameter, 7-8
LOG_CHECKPOINT_TIMEOUT parameter, 7-8
LOG_FILES parameter, 6-15
logging mode
direct-load INSERT, 21-5
NOARCHIVELOG mode and, 21-5
parallel DDL, 22-25,
22-27
partitions, 9-37
SQL operations affected by, 21-7
logical blocks, 2-2
logical database structure, 1-6
logical reads limit, 25-11
logical structures, 1-8
LONG datatype
automatically the last column, 8-7
defined, 10-5
partitioning restriction, 9-12
storage of, 8-7
LONG RAW datatype, 10-11
indexing prohibited on, 10-11
partitioning restriction, 9-12
similarity to LONG datatype, 10-11
LOW_VALUE column
of USER_TAB_COLUMNS view, 20-64
LRU, 6-3,
6-4,
7-8
dictionary cache, 4-4
latches, 7-8
shared SQL pool, 6-8,
6-10
M
MAC, 1-34
mandatory access control, 1-34
manual locking, 1-23,
23-29
manual refresh, 31-10
map methods, 1-54,
11-6
massively parallel processing (MPP)
affinity, 22-6,
22-40,
22-41
multiple Oracle instances, 5-2
parallel SQL execution, 22-2
master definition site, 31-18
master group, 31-17
master site, 31-17
master table
snapshot log, 31-8
matching foreign keys
full, partial, or none, 24-15
MAXVALUE
partitioned tables and indexes, 9-15
media failure, 1-37,
28-5
memory
allocation for SQL statements, 6-10
content of, 6-2
cursors (statement handles), 1-15
extended buffer cache (32-bit), 6-13
overview of structures in, 1-13
processes use of, 7-2
shared SQL areas, 6-8
software code areas, 6-16
sort areas, 6-15
stored procedures, 17-8,
17-15
structures in, 6-2
system global area (SGA)
allocation in, 6-2
initialization parameters, 6-12
locking into physical memory, 6-12,
6-16
SGA size, 6-11
starting address, 6-13
virtual, 6-16
MERGE hint, 20-27
MERGE_AJ hint, 20-79
MERGE_SJ hint, 20-79
merging complex views, 20-27
merging views into statements, 20-25
message queuing, 16-2
exporting queue tables, 16-8
messages, 16-3
queue monitor process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queue tables, 16-4
remote databases, 16-7
methods
comparison methods, 11-6
constructor methods, 11-6
literal invocation, 12-7
methods of collections
constructor methods, 1-54
methods of object types, 1-54,
11-4
constructor methods, 1-54,
12-4
execution privilege for, 12-10
map methods, 1-54,
11-6
order methods, 1-54,
11-6
PL/SQL, 11-13
purchase order example, 11-2,
11-5
selfish style of invocaton, 11-5
use of empty parentheses with, 12-3
MINIMUM EXTENT parameter, 22-28
MINUS operator
compound queries, 20-14
optimizing view queries, 20-26
MLSLABEL datatype, 10-16
modes
archive log, 28-16
exclusive, 5-6
shared, 5-6
single-task, 7-16
table lock, 23-21
two-task, 7-16,
7-18
monitoring user actions, 1-33,
27-2
MOVE PARTITION command
no-logging mode, 21-7
rules of parallelism, 22-21
MPP
See
massively parallel processing
MTS_MAX_SERVERS parameter, 7-23
artificial deadlocks and, 7-24
MTS_SERVERS parameter, 7-23
multiblock writes, 7-8
multi-master replication, 31-13
multimedia datatypes, 11-3
multiple-process systems (multiuser systems), 7-3
multiplexing
control files, 1-39,
28-20
recovery and, 28-6
redo log files, 1-38
multithreaded server, 7-20
artificial deadlocks in, 7-23
dedicated server contrasted with, 7-20
described, 7-16,
7-20
dispatcher processes, 1-18,
7-13
example of use, 7-26
Net8 or SQL*Net V2 requirement, 7-14,
7-20
processes needed for, 7-20
restricted operations in, 7-24
server processes, 1-17,
7-14,
7-23
shared server processes, 7-14,
7-23
multiuser environments, 1-2,
7-3
multiverison consistency model, 1-21
multiversion concurrency control, 23-5
N
name resolution in distributed databases, 30-6
named user licensing, 25-15
National Language Support (NLS)
character sets for, 10-4
CHECK constraints and, 24-17
clients and servers may diverge, 30-19
NCHAR and NVARCHAR2 datatypes, 10-4
NCLOB datatype, 10-10
parameters, 5-4
views and, 8-12
NCHAR datatype, 10-4
NCLOB datatype, 10-10
nested loops joins, 20-68
cost-based optimization, 20-74
nested tables, 8-9,
11-11
indexes, 12-9
Net8, 1-7,
1-26,
29-5,
30-4
Advanced Networking Option, 30-17
applications and, 29-5
client/server systems use of, 29-5
multithreaded server requirement, 7-14,
7-20
overview of, 29-5
networks
client/server architecture use of, 29-2
communication protocols, 7-27,
7-28,
29-5
dispatcher processes and, 7-14,
7-20
distributed databases, 30-2,
30-4
drivers, 7-27
failures of, 28-3
listener processes of, 7-14
Net8, 29-5,
30-4
network authentication service, 25-4
Oracle Names, 30-4
two-task mode and, 7-19
using Oracle on, 1-7,
1-26
NEXT storage parameter
parallel DML, 21-8
NLS
See
National Language Support
NLS_DATE_FORMAT parameter, 10-7
NLS_LANG environment variable, 9-15
NLS_LANGUAGE parameter, 9-15
NLS_NUMERIC_CHARACTERS parameter, 10-6
NLS_SORT parameter
no effect on partitioning keys, 9-15
ORDER BY access path, 20-60
NOARCHIVELOG mode, 28-16
database backups for recovery, 28-21
defined, 28-16
LOGGING mode and, 21-5
overview, 1-39
NOAUDIT command, 14-4
locks, 23-28
nodes
disk affinity in a Parallel Server, 22-40
of distributed databases, 1-24
NOLOGGING mode
direct-load INSERT, 21-5
parallel DDL, 22-25,
22-27
partitions, 9-37
SQL operations affected by, 21-7
nonequijoins
defined, 20-13
non-prefixed indexes, 9-27
non-repeatable reads, 23-3,
23-10
non-unique indexes, 8-17
NOREVERSE option for indexes, 8-22
NOT, 20-17
NOT IN subquery, 20-79
NOT NULL constraints
constraint checking, 24-19
defined, 24-7
implied by PRIMARY KEY, 24-11
UNIQUE keys and, 24-10
novalidate constraints, 24-21
Novell NetWare Management System, 30-19
nulls
as default values, 8-8
atomic, 12-6
column order and, 8-7
converting to values, 8-7
optimization, 20-77
defined, 8-7
foreign keys and, 24-14,
24-15
how stored, 8-7
indexes and, 8-8,
8-27
inequality in UNIQUE key, 24-10
non-null values for, 8-7,
20-77
object types, 12-6
partitioned tables and indexes, 9-16
prohibited in primary keys, 24-10
prohibiting, 24-7
UNIQUE key constraints and, 24-10
unknown in comparisons, 8-7
NUM_DISTINCT column
USER_TAB_COLUMNS view, 20-64
NUM_ROWS column
USER_TABLES view, 20-64
NUMBER datatype, 10-5
internal format of, 10-7
rounding, 10-6
NVARCHAR2 datatype, 10-4
NVL function, 8-7
O
object cache
object views, 13-4
OCI, 11-13
privileges, 12-13
Pro*C, 11-13
object identifiers, 11-8,
13-3
for object types, 12-4
index on, 12-5
for object views, 13-3,
13-4
for row objects, 11-8
WITH OBJECT OID clause, 13-3,
13-4
object privileges, 26-3
See also
schema object privileges
object tables, 11-3,
11-7
constraints, 12-8
indexes, 12-9
row objects, 11-8
triggers, 12-9
virtual object tables, 13-2
object types, 1-42,
11-2,
11-4
attributes of, 11-2,
11-4
column objects, 11-8
indexes, 12-9
comparison methods for, 11-6
constructor methods for, 1-54,
11-6,
12-4
incomplete, 12-14
locking in cache, 11-14
message queuing, 16-5
methods of, 1-54,
11-4
method calls, 12-3
PL/SQL, 11-13
purchase order example, 11-2,
11-5
mutually dependent, 12-13
object views, 8-14
Oracle type translator, 11-14
purchase order example, 11-2,
11-4
row objects, 11-8
use of table aliases, 12-2
object views, 8-14,
13-1
advantages of, 13-2
defining, 13-2
modifiability, 18-12
object identifiers for, 13-3,
13-4
row objects, 11-8
updating, 13-4
use of INSTEAD OF triggers with, 13-5
object-relational DBMS (ORDBMS), 1-42,
11-2
objects in a database schema, 1-6
See also
schema objects
OCI, 7-27
anonymous blocks, 14-18
bind variables, 14-13
object cache, 11-13
OCIObjectFlush, 13-4
OCIObjectPin, 13-4
stored procedures, 14-19
offline backups
whole database backup, 28-21
offline redo log files, 1-38,
28-7
OIDs, 11-8,
12-4,
13-3,
13-4
WITH OBJECT OID clause, 13-3,
13-4
OLTP database, 9-4
batch jobs, 22-32
parallel DML, 22-31
partitioning indexes, 9-28
partitions, 9-5
online analytical processing (OLAP)
index-organized tables, 8-31
online redo log, 1-38,
28-7
archiving, 28-16,
28-17
checkpoints, 28-20
media failure, 28-6
multiplexed, 28-6
recorded in control file, 28-19
online transaction processing (OLTP), 9-4
reverse key indexes, 8-22
OPEN_CURSORS parameter, 14-7
managing private SQL areas, 6-9
OPEN_LINKS parameter, 6-15
operating systems
authentication by, 25-3
block size, 2-3
communications software, 7-28
privileges for administrator, 5-3
roles and, 26-16
operations in a relational database, 1-42
OPTIMAL storage parameter, 2-22
optimization, 20-2
choosing the approach, 20-41
conversion of expressions and predicates, 20-14
cost-based, 20-6,
20-74
choosing an access path, 20-62
examples of, 20-63
histograms, 20-8
remote databases and, 20-40
described, 20-2
DISTINCT, 20-27
distributed SQL statements, 20-40
execution plan for partitions, 9-10,
9-12
GROUP BY views, 20-27
hints, 20-43,
20-45
index build, 8-17
manual, 20-43
merging complex views, 20-27
merging views into statements, 20-25
non-null values for nulls, 20-77
operations performed, 20-12
parallel SQL, 22-9
partition pruning, 9-3
indexes, 9-28
partitioned indexes, 9-28
PL/SQL, 20-43
rule-based, 20-11,
20-75
choosing an access path, 20-66
examples of, 20-66
selectivity of queries and, 20-63
select-project-join views, 20-25
semi-joins, 20-79
statistics, 20-42
transitivity and, 20-17
types of SQL statements, 20-13
without merging, 20-35
OPTIMIZER_GOAL option, 20-42
OPTIMIZER_MODE, 20-41
hints affecting, 20-43
Oracle
adherence to standards, 1-3
integrity constraints, 24-5
architecture, 1-8,
1-13
client/server architecture of, 29-2
compatibility, 1-4
configurations of, 7-2,
7-16
multiple-process Oracle, 7-3,
7-16
single-process Oracle, 7-2
connectibility, 1-4
different Oracle versions, 30-7
data access, 1-49
examples of operations, 1-19,
7-24
features, 1-2
instances, 1-6,
1-15,
5-2
licensing of, 25-14
Oracle server, 1-4
Parallel Server option, 1-8
See also
Parallel Server
portability, 1-4
processes of, 1-16,
7-5
scalability of, 29-4
SQL processing, 14-8
Trusted Oracle, 1-34
using on networks, 1-4,
1-26
Oracle AQ, 16-1
exporting queue tables, 16-8
message queuing, 16-2
queue monitor process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queue tables, 16-4
remote databases, 16-7
Oracle blocks, 1-10,
2-2
See also
data blocks
Oracle Call Interface (OCI), 7-27
anonymous blocks, 14-18
bind variables, 14-13
object cache, 11-13
OCIObjectFlush, 13-4
OCIObjectPin, 13-4
stored procedures, 14-19
Oracle code, 7-2,
7-27
Oracle Enterprise Manager
See
Enterprise Manager
Oracle Forms
object dependencies and, 19-11
PL/SQL, 14-17
Oracle Names
global directory service, 30-4
Oracle Open Gateways, 30-8
Oracle Parallel Server, 1-8
See also
Parallel Server
Oracle precompilers
anonymous blocks, 14-18
bind variables, 14-13
cursors, 14-10
embedded SQL, 14-5
FIPS flagger, 14-6
stored procedures, 14-19
Oracle program interface (OPI), 7-27
Oracle Replication Manager, 31-17
Oracle Security Server, 30-17
Oracle server, 1-4
See also
Oracle
Oracle type translator (OTT), 11-14
ORDBMS, 1-42,
11-2
order methods, 1-54,
11-6
ORDERED hint, 20-75
OTT, 11-14
outer joins
defined, 20-13
non-null values for nulls, 20-77
P
P code, 17-16
packages, 17-4,
17-10
advantages of, 17-13
as program units, 1-53
auditing, 27-8
dynamic SQL, 14-20
examples of, 17-10,
26-8,
26-9
executing, 14-17,
17-16
for locking, 23-40
overview of, 1-45
private, 17-14
privileges
divided by construct, 26-8
executing, 26-7,
26-8
public, 17-14
queuing, 16-4
session state and, 19-6
shared SQL areas and, 6-9
storing, 17-15
validity of, 17-16
pages, 2-2
parallel backup operations, 28-12
PARALLEL clause
parallelization rules, 22-17
parallel coordinator process, 22-6
parallel DDL
extent allocation, 22-27
parallelism types, 22-3
parallelization rules, 22-17
partitioned tables and indexes, 22-25
parallel DELETE, 22-18
parallel DML, 22-29
applications, 22-31
bitmap indexes, 8-24
degree of parallelism, 22-17,
22-19
enabling PARALLEL DML, 22-32
lock and enqueue resources, 22-36
parallelism types, 22-3
parallelization rules, 22-17
recovery, 22-34
restrictions, 22-37
transaction model, 22-33
PARALLEL hint, 22-13
parallelization rules, 22-17
UPDATE and DELETE, 22-18
parallel mode, 5-6
parallel propagation, 31-21
parallel query, 22-2
bitmap indexes, 8-24
full table scans, 22-5
inter-operator parallelism, 22-11
intra-operator parallelism, 22-11
parallelization rules, 22-17
partitioned tables and indexes, 22-4
parallel recovery, 28-12,
28-13
Parallel Server, 1-8
concurrency limits and, 25-15
databases and instances, 5-2
disk affinity, 22-40
distributed locks, 23-19
DML locks and performance, 9-31
exclusive mode, 5-6
rollback segments and, 2-25
file and log management locks, 23-29
instance groups, 22-16
isolation levels, 23-11
lock processes, 1-19,
7-13
mounting a database using, 5-6
named user licensing and, 25-16
parallel SQL, 22-1
PCM locks, 23-19
reverse key indexes, 8-22
shared mode, 5-6
rollback segments and, 2-25
system change numbers, 7-10
system monitor process and, 7-11
parallel server process, 22-6
parallel SQL
allocating rows to parallel server processes, 22-9
coordinator process, 22-6
degree of parallelism, 22-13
instance groups, 22-16
multithreaded server, 22-8
number of parallel server processes, 22-7
operations in execution plan, 22-9
optimizer, 22-9
Parallel Server and, 22-1
parallelization rules, 22-17
summary or rollup tables, 22-26
parallel UPDATE, 22-18
PARALLEL_DEFAULT_MAX_SCANS parameter (obsolete), 22-15
PARALLEL_DEFAULT_SCANSIZE parameter (obsolete), 22-15
PARALLEL_INDEX hint, 22-13
PARALLEL_MAX_SERVERS parameter, 22-8
PARALLEL_MIN_PERCENT parameter, 22-15
PARALLEL_MIN_SERVERS parameter, 22-7,
22-8
PARALLEL_SERVER_IDLE_TIME parameter, 22-8
parameter files, 5-4
example of, 5-4
used at startup, 5-5
parameters
initialization, 5-4
locking behavior, 23-18
See also
initialization parameters
National Language Support, 5-4
storage, 2-5,
2-11
parentheses, use of in method calls, 12-3
parse trees, 17-16
construction of, 14-7
in shared SQL area, 6-8
stored in database, 17-16
parsing, 14-11
DBMS_SQL package, 14-20
embedded SQL, 14-6
parse calls, 14-8
parse locks, 14-11,
23-28
performed, 14-8
SQL statements, 14-11,
14-20
partial backups, 28-22
partition views, 9-10
partitioning columns, 9-12
partitioning keys, 9-13,
9-15
multi-column keys, 9-17
partitions, 9-2,
9-11
advantages of, 9-4,
9-6
affinity, 22-40
basic partitioning model, 9-11
bitmap indexes, 8-28
concurrent maintenance operations, 9-33
DML partition locks, 9-30
dynamic partitioning, 22-6
equipartitioning, 9-18
EXCHANGE PARTITION, 9-10
execution plan, 9-10,
9-12
global indexes, 9-25,
9-39
local indexes, 9-23,
9-38
LONG and LONG RAW restriction, 9-12
maintenance operations, 9-31
no-logging mode, 21-7
OLTP databases, 9-5
parallel DDL, 22-25
parallel queries, 22-4
partition bounds, 9-14
partition names, 9-14
partition pruning, 9-3
disk striping and, 22-41
indexes, 9-28
parallelizing by block range, 22-4
TO_DATE format mask, 9-12,
9-16
partition transparency, 9-9
partition-extended table names, 9-42
partitioning indexes, 9-22,
9-28
partitioning keys, 9-13,
9-15
partitioning tables, 9-21
physical attributes, 9-21,
9-29
prefixed indexes, 9-24
range partitioning, 9-12
disk striping and, 22-41
rebuild partition, 9-39
referencing a partition, 9-14
restrictions
bitmap indexes, 9-12
datatypes, 9-12,
9-16
partition-extended table names, 9-42
rules of parallelism, 22-21,
22-23
statistics, 9-12
VLDB, 9-4
passwords
account locking, 25-5
administrator privileges, 5-3
complexity verification, 25-6
connecting with, 7-5
connecting without, 25-3
database user authentication, 25-4
encryption, 25-4
expiration, 25-5
password files, 25-7
password reuse, 25-5
used in roles, 1-31
PCTFREE storage parameter
how it works, 2-6
PCTUSED and, 2-8
PCTINCREASE storage parameter
parallel DML, 21-9
PCTUSED storage parameter
how it works, 2-7
PCTFREE and, 2-8
performance
clusters and, 8-34
constraint effects on, 24-6
DSS database, 9-7,
22-31
dynamic performance tables (V$), 4-7
group commits, 7-10
I/O, 9-8
index build, 8-17
Oracle Parallel Server and DML locks, 9-31
packages, 17-14
parallel recovery and, 28-14
partitions, 9-7
prefixed and non-prefixed indexes, 9-28
recovery, 28-5
resource limits and, 25-10
SGA size and, 6-11
structures that improve, 1-46
viewing execution plans, 20-4
persistent areas, 6-8
persistent queuing, 16-2
PGA, 1-16,
6-13
multithreaded server, 7-23
phantom reads, 23-3,
23-10
physical database structure, 1-5
PL/SQL, 14-16
anonymous blocks, 14-16,
17-9
auditing of statements within, 27-4
bind variables
user-defined types, 11-12
database triggers, 1-56,
18-1
datatypes, 10-2
dynamic SQL, 14-20
exception handling, 14-19
executing, 14-16,
17-16,
17-17
external procedures, 14-20,
17-9
language constructs, 14-18
object views, 13-4
optimizer goal, 20-43
overview of, 1-53,
14-16
packages, 17-4,
17-10
parse locks, 23-28
parsing DDL statements, 14-20
PL/SQL engine, 14-16,
17-2
compiler, 17-15
executing a procedure, 17-17
products containing, 14-17
program units, 1-45,
6-9,
14-16,
17-2
compiled, 14-17,
17-9,
17-15
shared SQL areas and, 6-9
roles disabled in named PL/SQL blocks, 26-14
stored procedures, 1-45,
14-16,
17-2,
17-6
user locks, 23-40
user-defined datatypes, 11-12
plan
accessing views, 20-29,
20-32,
20-33
complex statements, 20-24
compound queries, 20-37,
20-38,
20-39
joining views, 20-35
joins, 20-67,
20-74
OR operators, 20-21
SQL execution, 14-4,
14-11
star transformation, 20-83
PMON background process,
7-12
See also
process monitor process
portability, 1-4
precompilers
anonymous blocks, 14-18
bind variables, 14-13
cursors, 14-10
embedded SQL, 14-5
FIPS flagger, 14-6
stored procedures, 14-19
predicates
optimizing view queries, 20-25
partition pruning, 9-3
indexes, 9-28
pushing into a view, 20-28,
20-33
examples, 20-29,
20-31
prefixed indexes, 9-24,
9-27
prefixes of data dictionary views, 4-5
PRIMARY KEY constraints, 24-10
constraint checking, 24-19
described, 24-10
indexes used to enforce, 24-11
name of, 24-11
maximum number of columns, 24-11
NOT NULL constraints implied by, 24-11
primary key snapshot, 31-11
primary keys, 1-56,
24-10
advantages of, 24-10
defined, 24-3
optimization, 20-24
searches, 20-49
primary ownership, 31-23
private rollback segments, 2-23
private SQL areas
cursors and, 6-9
described, 6-8
how managed, 6-9
persistent areas, 6-8
runtime areas, 6-8
privileges
administrator
not audited, 27-5
auditing use of, 1-33,
27-7
checked when parsing, 14-11
granting, 1-30,
26-3,
26-4
examples of, 26-8,
26-9
grouping into roles, 1-31
overview of, 1-30,
26-2
partitioned tables and indexes, 9-41
procedures, 26-7
creating and altering, 26-8
executing, 17-16,
26-7
in packages, 26-8
RESTRICTED SESSION, 25-15
revoked
object dependencies and, 19-6
revoking, 26-3,
26-4
roles, 26-10
restrictions on, 26-15
schema object, 26-3
DML and DDL operations, 26-4
granting and revoking, 26-4
overview of, 1-30
packages, 26-8
procedures, 26-7
system, 26-2
granting and revoking, 26-3
overview of, 1-30
user-defined types, 12-10
to start up or shut down a database, 5-3
trigger privileges, 26-8
user-defined types
acquired by role, 12-10
ALTER ANY TYPE, 12-10
checked when pinning, 12-13
column level for object tables, 12-13
CREATE ANY TYPE, 12-10
CREATE TYPE, 12-10
DELETE, 12-12,
12-13
DROP ANY TYPE, 12-10
EXECUTE, 12-10,
12-11,
12-12,
12-13
EXECUTE ANY TYPE, 12-10,
12-11
EXECUTE ANY TYPE with ADMIN OPTION, 12-11
EXECUTE with GRANT option, 12-11
INSERT, 12-12,
12-13
SELECT, 12-12,
12-13
system privileges, 12-10
UPDATE, 12-12,
12-13
using, 12-11,
12-15
views, 26-6
creating, 26-6
using, 26-6
Pro*C/C++
processing SQL statements, 14-10
user-defined datatypes, 11-13
procedural replication, 31-26
detecting conflicts, 31-26
wrapper, 31-26
procedures, 14-16,
17-1,
17-6,
19-7
advantages of, 17-7
auditing, 27-8
contrasted with anonymous blocks, 17-9
contrasted with functions, 1-53,
17-2
cursors and, 14-18
dependency tracking in, 19-6
examples of, 17-6,
26-8,
26-9
executing, 14-17,
17-16
external procedures, 14-20,
17-9
INVALID status, 19-3,
19-6
prerequisites for compilation of, 19-5
privileges
create or alter, 26-8
executing, 26-7
executing in packages, 26-8
remote procedure calls, 30-11
roles disabled in, 26-14
security enhanced by, 17-7,
26-7
shared SQL areas and, 6-9
stored procedures, 14-16,
14-19,
17-2
storing, 17-15
triggers, 18-2
validity of, 17-16
process global area (PGA), 6-13
See also
program global area
process monitor process (PMON)
cleans up timed-out sessions, 25-12
described, 1-18,
7-12
network failure, 28-3
parallel DML process recovery, 22-34
process failure, 28-3
processes, 7-2
archiver (ARCH), 1-18,
7-12
background, 1-17,
7-6
diagrammed, 7-6
checkpoint (CKPT), 1-18,
7-11
checkpoints and, 7-8
database writer (DBW
n
), 1-17,
7-8
dedicated server, 7-23
dispatcher (D
nnn
), 1-18,
7-13
distributed transaction resolution, 7-12
during recovery, 28-14
failure in, 28-3
job queue (SNP
n
), 1-19,
7-13
message propagation, 16-8
snapshot refresh, 31-9,
31-10
listener, 7-14
shared servers and, 7-20
lock (LCK
n
), 1-19,
7-13
log writer (LGWR), 1-18,
7-9
multiple-process Oracle, 7-3
multithreaded server, 7-20
artificial deadlocks and, 7-23
client requests and, 7-21
Oracle, 1-16,
7-5
single-process Oracle, 7-2
overview of, 1-16
parallel coordinator, 22-6
parallel server processes, 22-6
process monitor (PMON), 1-18,
7-12
queue monitor (QMN
n
), 1-19,
7-13,
16-4
recoverer (RECO), 1-18,
7-12
and in-doubt transactions, 1-26
server, 1-16,
1-24,
7-5
dedicated, 7-18
shared, 7-13,
7-14,
7-23
shadow, 7-18
structure, 7-2
system monitor (SMON), 1-18,
7-11
trace files for, 7-14
user, 1-16,
7-4
allocate PGAs, 6-13
recovery from failure of, 7-12
sharing server processes, 7-13,
7-14
processing
DDL statements, 14-14
distributed, 1-23
DML statements, 14-10
overview, 14-8
parallel SQL, 22-2
queries, 14-11
profiles
overview of, 1-32
password management, 25-5
when to use, 25-13
program global area (PGA), 1-16,
6-13
allocation of, 6-13
contents of, 6-13
multithreaded servers, 7-23
nonshared and writable, 6-13
size of, 6-14
program interface, 7-27
Oracle side (OPI), 7-27
overview of, 1-19
single-task mode in, 7-17
structure of, 7-27
two-task mode in, 7-19
user side (UPI), 7-27
program units, 1-45,
14-16,
17-2
prerequisites for compilation of, 19-5
shared pool and, 6-9
propagation
parallel, 31-21
serial, 31-21
propagator of replicated data, 31-22
pruning partitions, 9-3,
22-4,
22-41
index partitions, 9-4
indexes, 9-28
TO_DATE format mask, 9-12,
9-16
pseudocode, 17-16
triggers, 18-17
pseudocolumns
CHECK constraints prohibit
LEVEL and ROWNUM, 24-16
modifying views, 18-12
ROWID, 10-12
ROWNUM
cannot use indexes, 20-62
optimizing view queries, 20-26,
20-35
USER, 26-7
public rollback segments, 2-23
PUBLIC user group, 25-9,
26-14
validity of procedures, 17-17
purchase order example
object types, 11-2,
11-4
PUSH_JOIN_PRED hint, 20-78
PUSH_JOIN_PREDICATE parameter, 20-78
Q
QMN
n
background process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queries
ad hoc, 22-26
compound
defined, 20-14
optimization of, 20-37
ORs converted to, 20-20
default locking of, 23-25
define phase, 14-12
defined, 20-13
describe phase, 14-12
distributed or remote, 30-10
fetching rows, 14-11
in DML, 14-3
index scans parallelized by partition, 22-5
location transparency and, 30-14
merged with view queries, 8-12
optimizing IN subquery, 20-27
optimizing view queries, 20-25
parallel processing, 22-2
phases of, 23-5
processing, 14-11
read consistency of, 1-22,
23-5
selectivity of, 20-63
star queries, 20-80
stored as views, 1-43,
8-10
table scans parallelized by rowid, 22-4
temporary segments and, 2-16,
14-12
triggers use of, 18-16
queue monitor process (QMN
n
), 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queuing, 16-2
exporting queue tables, 16-8
queue monitor process, 1-19,
7-13,
16-4
interval statistics, 16-8
window of execution, 16-5
queue tables, 16-4,
16-8
remote databases, 16-7
quotas
revoking tablespace access and, 25-9
setting to zero, 25-9
tablespace, 1-32,
25-8
temporary segments ignore, 25-9
R
range partitioning, 9-12
RAW datatype, 10-11
RDBMS, 1-41
object-relational DBMS, 1-42,
11-2
See also
Oracle
read committed isolation, 23-6,
23-7
read consistency
defined, 1-21
multiversion consistency model, 1-21
queries, 14-12
rollback segments and, 2-18
snapshot too old message, 23-5
subqueries in DML, 23-13
transactions, 1-21,
23-6
triggers and, 18-14,
18-16
read snapshot time, 23-10
read uncommitted, 23-3
readers block writers, 23-10
read-only replication, 31-2,
31-4
uses of, 31-4
read-only snapshot, 31-6
refresh types, 31-9
read-only tablespaces
backing up, 28-23
described, 3-9
restrictions on, 3-10
read-only transactions, 1-22
reads
data block
limits on, 25-11
dirty, 23-2
repeatable, 23-6
real-time replication, 31-27
REBUILD INDEX command
no-logging mode, 21-7
rules of parallelism, 22-21
REBUILD INDEX PARTITION command, 9-39
no-logging mode, 21-7
rules of parallelism, 22-21
receiver of replicated data, 31-22
recoverer process (RECO), 1-18,
7-12
in-doubt transactions, 1-26,
5-7,
15-8
recovery
basic steps, 1-40,
28-9
database buffers and, 28-8
diagrammed, 28-15
disaster recovery, 28-24
distributed processing in, 7-12
instance
SMON process, 7-11
instance recovery, 28-4
incremental checkpoint, 28-5
opening a database, 5-7
parallel DML, 22-35
required after abort, 5-8
media recovery
dispatcher processes, 7-24
enabled or disabled, 28-16
of distributed transactions, 5-7
overview of, 1-34,
28-8
parallel DML, 22-34
parallel recovery, 28-13
parallel restore, 28-12
process recovery, 7-12,
28-3
recommendations for, 28-15
Recovery Manager, 1-41,
28-10
rolling back during, 28-9
rolling forward and, 28-9
standby database, 28-24
statement failure, 28-3
structures used in, 1-38,
28-7
whole database backups, 28-21
Recovery Manager, 1-41,
28-10
generating reports, 28-12
operating without a catalog, 28-11
parallel operations, 28-12
recovery catalog, 28-10
recursive SQL
cursors and, 14-7
redo log buffers, 1-15,
6-6
circularity, 7-9
committing a transaction, 7-10
log writer process and, 6-6
size of, 6-6
writing, 7-9
redo log files, 1-12,
28-7
archived, 1-38,
28-16
automatically, 28-17
errors in archiving, 28-18
manually, 28-18
archiver process (ARCH), 7-12
buffer management, 7-9
files named in control file, 28-19
log sequence numbers, 1-38
recorded in control file, 28-19
log writer process, 7-9
mode of, 1-39
multiplexed, 1-38
purpose of, 1-12
online or offline, 1-38,
28-7
overview of, 1-12,
1-38
parallel recovery, 28-13
physical database structure, 1-5
recovery and, 28-7
rolling forward and, 28-9
when temporary segments in, 2-17
written before transaction commit, 7-10
REF targets, 12-14
See also
REFs
referenced
keys, 1-56,
24-12
objects, 19-2
partitions, 9-14
REFERENCES privilege
when granted through a role, 26-15
referential integrity, 23-11,
24-12
cascade rule, 24-3
examples of, 24-17
partially null foreign keys, 24-15
PRIMARY KEY constraints, 24-10
restrict rule, 24-3
self-referential constraints, 24-14,
24-17
set to default rule, 24-3
set to null rule, 24-3
refresh
job queue processes (SNP
n
), 1-19,
7-13
refresh group, 31-9
automatic refresh, 31-9
manual refresh, 31-10
refresh interval
snapshot refresh group, 31-9
refresh types
read-only snapshots, 31-9
REFs, 11-8
constructing from object identifiers, 12-4,
12-5
dangling, 11-9
dereferencing of, 11-9
for rows of object views, 13-3
implicit dereferencing of, 11-9
indexes on, 12-9
mutually dependent types, 12-13
pinning, 12-13,
13-4
potential REF targets
REF targets, 12-14
scoped, 11-8,
12-5
size of, 12-5
use of table aliases, 12-2
relational DBMS (RDBMS)
object-relational DBMS, 11-2
principles, 1-41
SQL and, 14-2
See also
Oracle
relations, 1-43
remote databases, 1-24,
30-2
database links, 30-6
See also
distributed databases
remote dependencies, 19-9
remote procedure calls, 30-11,
31-19
remote transactions, 30-11
REMOTE_DEPENDENCIES_MODE parameter, 19-9
RENAME command, 14-4
repeatable reads, 23-3
replication, 31-1
administrator, 31-22
basic, 31-2,
31-4
catalog, 31-18
conflicts, 31-22
column groups, 31-25
data models and, 31-23
detecting, 31-24
procedural replication, 31-26
resolution methods, 31-25
resolving, 31-25
row-level replication, 31-24
definition, 31-2
distributed databases vs., 30-7
group, 31-17
object, 31-17
procedural, 31-26
propagator, 31-22
real-time, 31-27
receiver, 31-22
restrictions
direct-load INSERT, 21-10
parallel DML, 22-38
site, 31-17
symmetric, 31-11,
31-12
uses of read-only, 31-4
uses of symmetric, 31-12
replication management API, 31-18
Replication Manager, 31-17
reserved words, 14-3
resource limits
call level, 25-11
connect time per session, 25-12
CPU time limit, 25-11
determining values for, 25-13
idle time per session, 25-12
logical reads limit, 25-11
overview of, 1-32
private SGA space per session, 25-12
session level, 25-10
sessions per user, 25-12
RESOURCE role, 26-16
user-defined types, 12-10,
12-11
response queues, 7-21
response time, 20-7
cost-based approach, 20-41
restricted mode
starting instances in, 5-5
restricted ROWID format, 10-14
RESTRICTED SESSION privilege, 25-15
restrictions
direct-load INSERT, 21-9
parallel DML and direct-load INSERT, 22-37
partition views, 9-11
partitions
bitmap indexes, 9-12
datatypes, 9-12,
9-16
partition-extended table names, 9-42
reverse key indexes, 8-22
REVERSE option for indexes, 8-22
REVOKE command, 14-4
FORCE option, 12-15
locks, 23-28
object types and dependencies, 12-15
roles, 1-31,
26-10
application, 26-12
CONNECT role, 12-10,
12-11,
26-16
DBA role, 12-10,
26-16
DDL statements and, 26-14
dependency management in, 26-15
disabled in named PL/SQL blocks, 26-14
distributed database applications, 30-16
enabled or disabled, 26-13
EXP_FULL_DATABASE role, 26-16
functionality, 26-2
global authentication service, 30-17
granting, 26-3,
26-13
IMP_FULL_DATABASE role, 26-16
in applications, 1-31
managing via operating system, 26-16
naming, 26-14
overview of, 1-31
predefined, 26-16
queue administrator, 16-5
RESOURCE role, 12-10,
12-11,
26-16
restrictions on privileges of, 26-15
revoking, 26-13
schemas do not contain, 26-14
security domains of, 26-14
use of passwords with, 1-31
user, 26-12
users capable of granting, 26-13
uses of, 26-11
rollback, 2-17,
15-6
defined, 1-51
described, 15-6
during recovery, 1-41,
28-9
ending a transaction, 15-2,
15-4,
15-6
fast transaction rollback, 28-10
statement-level, 15-4
to a savepoint, 15-6
ROLLBACK command, 14-5
rollback entries, 2-17
rollback segments, 1-11,
2-17
access to, 2-17
acquired during startup, 5-7
allocation of extents for, 2-19
new extents, 2-21
clashes when acquiring, 2-24
committing transactions and, 2-19
contention for, 2-19
deallocating extents from, 2-22
deferred, 2-27
defined, 1-11
dropping, 2-22
restrictions on, 2-27
how transactions write to, 2-19
in-doubt distributed transactions, 2-21
invalid, 2-25
locks on, 23-29
moving to the next extent of, 2-20
number of transactions per, 2-19
offline, 2-25,
2-27
offline tablespaces and, 2-27
online, 2-25,
2-27
overview of, 2-17,
28-8
parallel recovery, 28-10
partly available, 2-25,
28-4
private, 2-23
public, 2-23
read consistency and, 1-21,
2-18,
23-4
recovery needed for, 2-25
states of, 2-25
SYSTEM rollback segment, 2-23
transactions and, 2-18
use of in recovery, 1-39,
28-9
when acquired, 2-23
when used, 2-18
written circularly, 2-19
rolling back during recovery, 28-9
rolling back transactions, 1-52,
15-2,
15-6
fast warmstart, 28-4
rolling forward during recovery, 1-40,
28-9
root blocks, 8-41
row cache, 6-10
row data (section of data block), 2-5
row directories, 2-4
row locking, 23-10,
23-19
serializable transactions and, 23-8
row objects, 11-8
row pieces, 8-5
headers, 8-5
how identified, 8-7
row sources, 20-3
row triggers, 18-7,
18-8
when fired, 18-14
See also
triggers
ROWID datatype, 10-12
extended ROWID format, 10-12
restricted ROWID format, 10-14
ROWID snapshot, 31-11
ROWIDs, 8-7
accessing, 10-12
changes in, 10-12
in non-Oracle databases, 10-15
in REFs, 12-5
internal use of, 10-16
of clustered rows, 8-7
sorting indexes by, 8-21
table access by, 20-44
ROWLABEL column, 10-16
row-level locking, 23-10,
23-19
row-level replication, 31-19
detecting conflicts, 31-24
ROWNUM pseudocolumn
cannot use indexes, 20-62
optimizing view queries, 20-26,
20-35
rows, 1-43,
8-3
addresses of, 8-7
chaining across blocks, 2-10,
8-5
clustered, 8-6
ROWIDs of, 8-7
defined, 1-43
described, 8-3
fetched, 14-11
format of in data blocks, 2-4
headers, 8-5
locking, 23-10,
23-19
locks on, 9-30,
23-19,
23-22
pieces of, 8-5
row objects, 11-8
row overflow in index-organized tables, 8-29
row sources, 20-3
ROWIDs used to locate, 20-44,
20-48
shown in ROWIDs, 10-13,
10-14
size of, 8-5
storage format of, 8-5
triggers on, 18-8
when ROWID changes, 10-12
RPC, 30-11,
31-19
RULE hint
OPTIMIZER_MODE and, 20-43
rule-based optimization, 20-11
runtime areas, 6-8
S
same-row writers block writers, 23-10
SAVEPOINT command, 14-5
savepoints, 1-52,
15-7
described, 15-7
implicit, 15-4
overview of, 1-52
rolling back to, 15-6
scalability
batch jobs, 22-32
client/server architecture, 29-4
parallel DML, 22-31
parallel SQL execution, 22-2
scans, 20-44
cluster, 20-48,
20-49,
20-50,
20-51
indexed, 20-51
fast full index scan, 20-45
full table, 20-44,
20-61
LRU algorithm, 6-4
multiblock reads, 20-63
parallel query, 22-5
rule-based optimizer, 20-66
hash cluster, 20-49,
20-51
index, 20-44
bitmap, 20-46
bounded range, 20-55
cluster key, 20-51
composite, 20-53
MAX or MIN, 20-59
ORDER BY, 20-59
restrictions, 20-61
selectivity and, 20-63
single-column, 20-53
unbounded range, 20-57
range, 20-45,
20-53
bounded, 20-55
MAX or MIN, 20-59
ORDER BY, 20-59
unbounded, 20-57
table scan and CACHE clause, 6-4
unique, 20-45,
20-49,
20-51
schema names
in distributed databases, 30-6
qualifying column names, 12-3
unique within a database, 30-6
schema object privileges, 26-3
DML and DDL operations, 26-4
granting and revoking, 26-4
overview of, 1-30
views, 26-6
schema objects, 8-1
auditing, 1-33,
27-8
creating
tablespace quota required, 25-8
default tablespace for, 25-8
defined, 1-6
dependencies of, 19-2
and distributed databases, 19-11
and views, 8-13
on non-existence of other objects, 19-7
triggers manage, 18-14
dependent on lost privileges, 19-6
global names, 30-6
in a revoked tablespace, 25-9
information about, 4-2
INVALID status, 19-3
names in distributed databases, 30-6
overview of, 1-10,
1-43,
8-2
privileges on, 26-3
relationship to datafiles, 3-12,
8-2
trigger dependencies on, 18-18
user-defined types, 11-3
schemas, 25-2
associated with users, 1-28,
8-2
contents of, 8-2
contrasted with tablespaces, 8-2
defined, 25-2
objects in, 8-2
user-defined datatypes, 11-13
SCN, 15-5
See also
system change numbers
scoped REFs, 11-8,
12-5
security, 1-31,
25-2
administrator privileges, 5-3
application enforcement of, 1-31
auditing, 27-2,
27-6
auditing user actions, 1-33
data, 1-28
data encryption, 30-17
deleting audit data, 4-5
described, 1-28
discretionary access control, 1-29,
25-2
distributed databases, 30-16
domains, 1-30,
25-2
enforcement mechanisms, 1-29
message queues, 16-5
passwords, 25-4
procedures enhance, 26-7
program interface enforcement of, 7-27
system, 1-28,
4-3
views and, 8-11
views enhance, 26-6
security domains, 1-30,
25-2
enabled roles and, 26-13
tablespace quotas, 25-8
segments, 1-10,
2-15
data, 2-15
deallocating extents from, 2-13
defined, 2-3
header block, 2-11
index, 2-15
overview of, 1-10,
2-15
rollback, 2-17
temporary, 1-11,
2-16
allocating, 2-16
cleaned up by SMON, 7-11
dropping, 2-14
ignore quotas, 25-9
operations that require, 2-16
parallel INSERT, 21-8
tablespace containing, 2-14,
2-16
SELECT command, 14-3
subqueries, 14-12
See also
queries
SELECT privilege for object tables, 12-12,
12-13
selectivity of queries, 20-63
select-project-join views, 20-25
selfish style of method invocation, 11-5
semi-joins, 20-79
sequences, 1-44,
8-15
auditing, 27-8
CHECK constraints prohibit, 24-16
coordinated generation, 31-24
independence from tables, 8-15
length of numbers, 8-15
number generation, 8-14
serial propagation, 31-21
Server Manager
ALERT file, 7-15
executing a package, 17-6
executing a procedure, 17-4
lock and latch monitors, 23-28
PL/SQL, 14-18,
14-19
session variables, 14-18
showing size of SGA, 6-12
SQL statements, 14-2
statistics monitor, 25-13
server processes, 1-16,
7-5
servers, 1-23
client/server architecture, 29-2
dedicated, 1-17,
7-18
multithreaded contrasted with, 7-20
dedicated server architecture, 7-16
defined, 1-24
multithreaded, 1-17
architecture, 7-16,
7-20
dedicated contrasted with, 7-20
processes of, 7-13,
7-14,
7-20,
7-23
processes of, 1-16
shared, 1-17
session control statements, 1-50,
14-5
SESSION_ROLES view
queried from PL/SQL block, 26-14
sessions
auditing by, 27-10
connections contrasted with, 7-4
defined, 7-5,
27-10
enabling PARALLEL DML, 22-32
limit on concurrent, 1-32
by license, 25-14
limits per user, 25-12
package state and, 19-6
resource limits and, 25-10
stack space in PGA, 6-13
time limits on, 25-12
transaction isolation level, 23-30
when auditing options take effect, 27-6
where information is stored, 6-13
SET CONSTRAINTS command
DEFERRABLE or IMMEDIATE, 24-20
SET ROLE command, 14-5
SET TRANSACTION command, 14-5
ISOLATION LEVEL, 23-7,
23-30
READ ONLY, 2-18
SGA
See
system global area
shadow column group, 31-25
shadow processes, 7-18
share locks
share table locks (S), 23-23
shared global area (SGA), 6-2
See also
system global area
shared mode, 5-6
rollback segments, 2-25
shared ownership, 31-24
shared pool, 6-6
allocation of, 6-10
ANALYZE command and, 6-11
dependency management and, 6-11
described, 6-6
flushing, 6-11
object dependencies and, 19-8
overview of, 1-15
procedures and packages, 17-15
row cache and, 6-10
size of, 6-6
shared server processes (S
nnn
), 7-14,
7-23
described, 7-23
shared servers, 1-17
cannot connect with administrator privileges, 5-3
shared SQL areas, 6-8,
14-7
ANALYZE command and, 6-11
dependency management and, 6-11
described, 6-8
loading SQL into, 14-11
overview of, 1-15,
14-7
parse locks and, 23-28
procedures, packages, triggers and, 6-9
size of, 6-8
SHARED_MEMORY_ADDRESS parameter, 6-13
SHARED_POOL_SIZE parameter, 6-6
system global area size and, 6-12
shutdown, 5-8,
5-9
abnormal, 5-6,
5-9
deallocation of the SGA, 6-2
prohibited by dispatcher processes, 7-24
steps, 5-8
SHUTDOWN ABORT command, 5-9
signature checking, 19-9
Simple Network Management Protocol (SNMP) support
database management, 30-19
simple snapshot
structure, 31-7
single-process systems (single-user systems), 7-2
single-task mode, 7-16
site autonomy, 1-25,
30-15
skewing parallel DML workload, 22-16
SMON background process,
7-11
See also
system monitor process
SMP architecture
disk affinity, 22-41
snapshot
complex, 31-10
defining query, 31-7
group, 31-18
log, 31-8
primary key, 31-11
read-only, 31-6
refresh, 7-13,
31-8,
31-10
rowid, 31-11
simple
structure, 31-7
site, 31-18
subquery, 31-7
updatable, 31-14
updating, 31-8
snapshot refresh
complete, 31-8
fast, 31-8
group
refresh interval, 31-9
groups, 31-9
job queue processes (SNP
n
), 1-19,
7-13
automatic snapshot refresh, 31-9
snapshot log and, 31-8
snapshot too old message, 23-5
S
nnn
background processes, 7-14
SNP
n
background processes, 1-19,
7-13
for automatic snapshot refresh, 31-9,
31-10
message propagation, 16-8
software code areas, 6-16
shared by programs and utilities, 6-17
SOME, 20-15
sort areas, 6-15
sort direct writes feature, 6-16
SORT_AREA_RETAINED_SIZE parameter, 6-15
SORT_AREA_SIZE parameter, 2-16,
6-15
cost-based optimization and, 20-75
SORT_DIRECT_WRITES parameter, 6-16
sort-merge joins, 20-70
access path, 20-58
cost-based optimization, 20-75
example, 20-58
space management
compression of free space, 2-9
direct-load INSERT, 21-8
MINIMUM EXTENT parameter, 22-28
parallel DDL, 22-27
PCTFREE, 2-6
PCTUSED, 2-7
row chaining, 2-10
segments, 2-15
spatial applications
index-organized tables, 8-31
SPLIT PARTITION command
no-logging mode, 21-7
rules of parallelism, 22-21
SQL, 14-2
cursors used in, 14-6
Data Definition Language (DDL), 14-4
Data Manipulation Language (DML), 14-3
dynamic SQL, 14-20
embedded, 1-50,
14-5
user-defined datatypes, 11-13
functions, 14-2
column default values, 8-8
COUNT, 8-27
in CHECK constraints, 24-16
NVL, 8-7
optimizing view queries, 20-26,
20-33
memory allocation for, 6-10
overview of, 1-49,
14-2
parallel execution, 22-2
parsing of, 14-7
PL/SQL and, 1-53,
14-16
recursive, 14-6
cursors and, 14-7
reserved words, 14-3
session control statements, 14-5
shared SQL, 14-7
statement-level rollback, 15-4
system control statements, 14-5
transaction control statements, 14-5
transactions and, 1-50,
15-2,
15-5
types of statements in, 1-49,
14-3
optimizing, 20-13
user-defined datatypes, 11-12,
12-2
embedded SQL, 11-13
OCI, 11-14
SQL areas
private, 6-8
persistent, 6-8
runtime, 6-8
shared, 1-15,
6-8,
14-7
SQL statements, 1-49,
14-3,
14-8
array processing, 14-13
auditing, 27-7,
27-9
overview, 1-33
when records generated, 27-4
complex, 20-13,
20-23
optimizing, 20-23
converting
examples of, 20-20
creating cursors, 14-10
dictionary cache locks and, 23-29
distributed
defined, 20-14
optimization of, 20-40
routing to nodes, 14-11
distributed databases and, 30-10
embedded, 14-5
execution, 14-8,
14-13
execution plans of, 20-2
failure in, 28-2
handles, 1-15
number of triggers fired by single, 18-14
optimization
complex statements, 20-23
types of statements, 20-13
overview, 1-49
parallel query, 22-2
parallelizing, 22-2,
22-9
parse locks, 23-28
parsing, 14-11
privileges required for, 26-3
recursive
OPTIMIZER_GOAL does not affect, 20-42
referencing dependent objects, 19-4
resource limits and, 25-11
simple, 20-13
successful execution, 15-3
transactions, 14-14
triggers on, 18-2,
18-8
triggering events, 18-6
types of, 1-49,
14-3,
20-13
SQL*Loader
Direct Loader, 21-2
partition operations, 9-31,
9-33
SQL*Menu
PL/SQL, 14-17
SQL*Module
FIPS flagger, 14-6
stored procedures, 14-19
SQL*Net
See
Net8
SQL*Plus
anonymous blocks, 14-18
connecting with, 25-3
SQL statements, 14-2
stored procedures, 14-19
SQL_TRACE parameter, 7-15
SQL92, 23-2
stack space, 6-13
standards, 1-3
ANSI/ISO, 1-3,
24-5,
24-15
isolation levels, 23-2,
23-10
FIPS, 1-3,
14-6
integrity constraints, 24-5,
24-15
Oracle adherence, 1-3
standby databases, 28-24
STAR hint, 20-81
star query, 20-82
extended star schemas, 20-81
hints, 20-81
indexes, 20-81
tuning, 20-80
star transformation, 20-82
example, 20-82
restrictions, 20-85
STAR_TRANSFORMATION hint, 20-85
STAR_TRANSFORMATION_ENABLED parameter, 20-85
startup, 5-2,
5-5
allocation of the SGA, 6-2
starting address, 6-13
exclusive mode, 5-6
fast warmstart, 28-4
forcing, 5-6
prohibited by dispatcher processes, 7-24
recovery during, 28-4
restricted mode, 5-5
shared mode, 5-6
steps, 5-5
statement triggers, 18-7
described, 18-8
when fired, 18-14
See also
triggers
statement-level read consistency, 23-5
statements
See
SQL statements
static ownership, 31-23
statistics
ANALYZE command, 20-42
checkpoint, 7-11
optimizer use of, 20-6,
20-7
partitioned tables and indexes, 9-12
queuing, 16-8
storage
datafiles, 3-11
for parallel INSERT, 21-8
fragmentation in parallel DDL, 22-28
logical structures, 3-3,
8-2
nested tables, 12-5
object tables, 12-4
of hash clusters, 8-37
of index clusters, 8-34
of index partitions, 9-29
of indexes, 8-19
of nulls, 8-7
of table partitions, 9-21
of views, 8-12
REFs, 12-5
restricting for users, 25-8
revoking tablespaces and, 25-9
tablespace quotas and, 25-8
triggers, 18-2,
18-17
user quotas on, 1-32
STORAGE clause
parallel query, 22-27
using, 2-11
storage parameters
NEXT, 21-8
OPTIMAL (in rollback segments), 2-22
PCTINCREASE, 21-8
setting, 2-11
store-and-forward replication, 31-19
stored functions, 1-45,
17-2,
17-6
stored procedures, 1-45,
14-16,
17-2,
17-6
calling, 14-19
contrasted with anonymous blocks, 17-9
triggers contrasted with, 18-2
variables and constants, 14-18
See also
procedures
Structured Query Language (SQL), 1-49,
14-2
See also
SQL
structures
locking, 23-27
logical, 1-6,
1-8
tablespaces, 3-3
physical, 1-5,
1-11
datafiles, 3-11
schema objects, 8-2
subqueries, 14-12
CHECK constraints prohibit, 24-16
converting to joins, 20-23
in DML statements
serializable isolation, 23-13
in remote updates, 30-10
NOT IN, 20-79
optimizing IN subquery, 20-27
See also
queries
subquery snapshot, 31-7
SunSoft
SunNet Manager, 30-19
survivability, 28-24
symmetric replication, 31-3,
31-12
overview, 31-11
uses for, 31-12
synchronous data propagation, 31-27
synonyms, 19-7
constraints indirectly affect, 24-5
described, 8-15
for data dictionary views, 4-4
inherit privileges from object, 26-3
overview of, 1-45
private, 8-16
public, 8-16
uses of, 8-16
SYS username
audit records not generated by, 27-5
data dictionary tables owned by, 4-3
security domain of, 25-3
SYS.AUD$ view
purging, 4-5
SYSDBA privilege, 5-3
SYSOPER privilege, 5-3
system change numbers (SCN)
committed transactions, 15-5
defined, 15-5
read consistency and, 23-5
redo logs, 7-10
when determined, 23-5
system control statements, 1-50,
14-5
system global area (SGA), 6-2
allocating, 5-5
contents of, 6-3
data dictionary cache, 4-4
database buffer cache, 6-3
diagram, 5-2
fixed, 6-3
limiting use of in multithreaded server, 25-12
overview of, 1-15,
6-2
redo log buffer, 6-6,
15-5
rollback segments and, 15-5
shared and writable, 6-3
shared pool, 6-6
size of, 6-11
variable parameters, 5-4
when allocated, 6-2
system monitor process (SMON), 7-11
defined, 1-18,
7-11
instance recovery, 28-4
parallel DML instance recovery, 22-35
parallel DML system recovery, 22-35
Parallel Server and, 7-11,
22-35
rolling back transactions, 28-10
temporary segment cleanup, 7-11
system privileges, 26-2
ADMIN OPTION, 12-11,
26-3
described, 26-2
granting and revoking, 26-3
user-defined types, 12-10
See also
privileges
SYSTEM rollback segment, 2-23
SYSTEM tablespace, 3-4
data dictionary stored in, 4-2,
4-5
media failure, 28-6
online requirement of, 3-7
procedures stored in, 17-16
SYSTEM username
security domain of, 25-3
T
table directories, 2-4
tables
affect dependent views, 19-5
auditing, 9-42,
27-8
base, 1-43
data dictionary use of, 4-2
relationship to views, 8-11
clustered, 8-32
contain integrity constraints, 1-55
contained in tablespaces, 8-5
controlling space allocation for, 8-4,
21-8
DUAL, 4-7
dynamic partitioning, 22-6
enable or disable constraints, 24-21
full table scan and buffer cache, 6-4
hash, 8-41
historical, 22-32
how data is stored in, 8-4
indexes and, 8-17
index-organized tables, 8-28
integrity constraints, 24-2,
24-5
locks on, 9-30,
23-20,
23-22,
23-24
maximum number of columns in, 8-10
nested tables, 8-9,
11-11
indexes, 12-9
no-logging mode, 21-7
novalidate constraints, 24-21
object tables, 11-3,
11-7
constraints, 12-8
indexes, 12-9
triggers, 12-9
virtual, 13-2
overview of, 1-43,
8-3
parallel creation, 22-26
parallel DDL storage, 22-27
parallel table scans, 22-4
partition-extended table names, 9-42
partitions, 9-2,
9-21
presented in views, 8-10
privileges for partitions, 9-41
privileges on, 26-4
queue tables, 16-4,
16-8
refreshing in data warehouse, 22-31
replicating, 1-26,
31-2,
31-3
specifying tablespaces for, 8-5
STORAGE clause with parallel query, 22-27
summary or rollup, 22-26
table aliases, 12-2,
12-3
table names
qualifying column names, 12-2,
12-3
triggers used in, 18-2
virtual or viewed, 1-43
tablespaces, 3-3
contrasted with schemas, 8-2
default for object creation, 1-32,
25-8
described, 3-3
how specified for tables, 8-5
locks on, 23-29
no-logging mode, 21-7
offline, 1-9,
3-7,
3-12
and index data, 3-9
cannot be read-only, 3-10
remain offline on remount, 3-8
online, 1-9,
3-7,
3-12
overview of, 1-9,
3-3
quotas on, 1-32,
25-8
limited and unlimited, 25-8
no default, 25-8
read-only, 3-9
dropping objects from, 3-10
relationship to datafiles, 3-2
revoking access from users, 25-9
size of, 3-6
temporary, 1-32,
3-11
default for user, 25-8
used for temporary segments, 2-14,
2-16
tasks, 7-2
temporary segments, 2-14,
2-16
allocating, 2-16
deallocating extents from, 2-14
dropping, 2-14
ignore quotas, 25-9
operations that require, 2-16
parallel DDL, 22-28
parallel INSERT, 21-8
tablespace containing, 2-14,
2-16
when not in redo log, 2-17
temporary tablespaces, 3-11
threads
multithreaded server, 7-13,
7-20
three-valued logic (true, false, unknown)
produced by nulls, 8-7
throughput, 20-7
cost-based approach, 20-41
timestamp checking, 19-9
TO_DATE function, 10-7
partition pruning, 9-12,
9-16
trace files, 7-14
DBW
n
, 28-6
LGWR trace file, 7-10
transaction control statements, 1-50,
14-5
transaction set consistency, 23-9,
23-10
transaction tables, 2-18
reset at recovery, 7-12
transactions, 1-50,
15-1
advanced queuing, 16-3
assigning system change numbers, 15-5
assigning to rollback segments, 2-18
asynchronous processing, 16-2
committing, 1-52,
7-10,
15-3,
15-5
group commits, 7-10
use of rollback segments, 2-19
concurrency and, 23-15
controlling transactions, 14-14
deadlocks and, 15-4,
23-16
defining and controlling, 14-14
described, 15-2
discrete transactions, 14-14,
15-8
distributed, 1-22
deadlocks and, 23-17
parallel DML restrictions, 22-40
resolving automatically, 7-12
two-phase commit, 1-25,
15-7,
30-12
distribution among rollback segments of, 2-19
end of, 15-4
consistent data, 14-14
in-doubt
limit rollback segment access, 2-27
resolving automatically, 1-26,
5-7,
15-8
resolving manually, 1-26
rollback segments and, 2-21
use partly available segments, 2-27
manual locking of, 23-30
overview of, 1-50
read consistency of, 1-21,
23-6
read-only, 1-22,
23-6
not assigned to rollback segments, 2-18
redo log files written before commit, 7-10
rollback segments and, 2-18
rolling back, 1-52,
15-6
and offline tablespaces, 2-27
partially, 15-6
use of rollback segments, 2-18
savepoints in, 1-52,
15-7
serializable, 23-6
space used in data blocks for, 2-5
start of, 15-4
statement level rollback and, 15-4
system change numbers, 7-10
terminating the application and, 15-5
transaction control statements, 14-5
triggers and, 18-16
two-phase commit in parallel DML, 22-34
writing to rollback segments, 2-19
TRANSACTIONS parameter, 2-24
TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter, 2-24
triggers, 1-56,
18-1,
19-7
action, 18-7
timing of, 18-8
AFTER triggers, 18-9
as program units, 1-54
auditing, 27-8
BEFORE triggers, 18-8
cascading, 18-3
constraints apply to, 18-14
constraints contrasted with, 18-5
data access and, 18-16
dependency management of, 18-18,
19-6
enabled triggers, 18-14
enabled or disabled, 18-14
enforcing data integrity with, 24-4
events, 18-6
examples of, 18-10,
18-12,
18-16
firing (executing), 18-2,
18-18
privileges required, 18-18
steps involved, 18-14
timing of, 18-14
INSTEAD OF triggers, 18-11
object views and, 13-5
INVALID status, 19-3,
19-6
maintain data integrity, 1-56
Oracle Forms triggers vs., 18-4
overview of, 1-56,
18-2
parts of, 18-5
privileges for executing, 26-8
procedures contrasted with, 18-2
prohibited in views, 8-11
restrictions, 18-7,
22-39
direct-load INSERT, 21-10
parallel DML, 22-38
roles disabled in, 26-14
row, 18-8
schema object dependencies, 18-14,
18-18
sequence for firing multiple, 18-14
shared SQL areas and, 6-9
statement, 18-8
storage of, 18-17
types of, 18-7
UNKNOWN does not fire, 18-7
user-defined types, 12-9
uses of, 18-3
TRUNCATE command, 14-4
Trusted Oracle
described, 1-34
mandatory access control, 1-34
MLSLABEL datatype, 10-16
mounting multiple databases in, 5-3
two-phase commit
described, 1-25,
30-12
manual override of, 1-26
parallel DML, 22-34
transaction management, 15-7
triggers, 18-14
two-task mode, 7-16,
7-18
described, 7-18
listener process and, 7-14
network communication and, 7-19
program interface in, 7-19
types
See
datatypes, object types
U
undo, 1-11
See also
rollback
UNION ALL operator
examples, 20-21,
20-23,
20-37
optimizing view queries, 20-26
transforming OR into, 20-20
UNION ALL views, 9-10
UNION operator
compound queries, 20-14
examples, 20-28,
20-38
optimizing view queries, 20-26
unique indexes, 8-17
UNIQUE key constraints, 24-8
composite keys, 24-8,
24-10
constraint checking, 24-19
indexes used to enforce, 24-9
maximum number of columns, 24-9
NOT NULL constraints and, 24-10
nulls and, 24-10
size limit of, 24-9
unique keys, 1-55,
1-56,
24-8
composite, 24-8,
24-10
optimization, 20-24
searches, 20-49
uniqueness conflict, 31-23
updatable snapshot, 31-14
UPDATE command, 14-4
foreign key references and, 24-15
freeing space in data blocks, 2-9
parallel UPDATE, 22-18
triggers and, 18-2,
18-6
BEFORE triggers, 18-9
INSTEAD OF triggers, 18-11
update no action constraint, 24-15
UPDATE privilege for object tables, 12-12,
12-13
updates
conflict in replicated data, 31-23
distributed, 30-11
location transparency and, 30-14
object views, 13-4
remote, 30-10
updatability of object views, 13-4
updatability of views, 8-13,
18-11,
18-12
updatable join views, 8-13
update intensive environments, 23-8
USE_INDIRECT_DATA_BUFFERS parameter, 6-13
user locks, 23-40
user processes
allocate PGAs, 6-13
connections and, 7-4
dedicated server processes and, 7-18
sessions and, 7-5
shared server processes and, 7-23
user program interface (UPI), 7-27
USER pseudocolumn, 26-7
USER_ views, 4-6
USER_TAB_COLUMNS view, 20-64
USER_TABLES view, 20-64
USER_UPDATABLE_COLUMNS view, 8-14
user-defined datatypes, 11-1,
11-3,
12-1
collections, 11-10
nested tables, 11-11
variable arrays (VARRAYs), 11-10
Export and Import, 12-15
incomplete types, 12-13
object types, 11-2,
11-4
use of table aliases, 12-2
object-relational model, 1-42
privileges, 12-10
storage, 12-4
users, 25-2
access rights, 25-2
associated with schemas, 8-2
auditing, 27-12
authentication of, 25-3
coordinating concurrent actions of, 1-20
dedicated servers and, 7-18
default tablespaces of, 25-8
distributed databases, 30-16
licensing by number of, 25-15
licensing of, 25-14
listed in data dictionary, 4-2
multiuser environments, 1-2,
7-3
password encryption, 25-4
privileges of, 1-30
processes of, 1-16,
7-4
profiles of, 1-32,
25-13
PUBLIC user group, 25-9,
26-14
resource limits of, 25-10
restrictions on resource use of, 1-32
roles and, 26-10
for types of users, 26-12
schemas of, 1-28,
25-2
security domains of, 1-30,
25-2,
26-14
single-user Oracle, 7-2
tablespace quotas of, 1-32,
25-8
tablespaces of, 1-32
temporary tablespaces of, 1-32,
2-16,
25-8
usernames, 1-30,
25-2
sessions and connections, 7-5
V
V_$ and V$ views, 4-7
V$LICENSE, 25-15
VARCHAR datatype, 10-3
VARCHAR2 datatype, 10-3
non-padded comparison semantics, 10-3
similarity to RAW datatype, 10-11
variables
bind variables
optimization, 20-64
user-defined types, 11-12
embedded SQL, 14-6
in stored procedures, 14-18
object variables, 13-4
VARRAYs, 11-10
very large database (VLDB), 9-4
parallel SQL, 22-2
partitions, 9-4
views, 1-43,
8-10
altering base tables and, 19-5
auditing, 27-8
base tables, 1-43
complex view merging, 20-27
constraints and triggers prohibited in, 8-11
constraints indirectly affect, 24-5
containing expressions, 18-12
data dictionary
updatable columns, 8-13
user-accessible views, 4-3
definition expanded, 19-5
dependency status of, 19-5
histograms, 20-10
how stored, 8-11
indexes and, 8-12
inherently modifiable, 18-12
INVALID status, 19-3
maximum number of columns in, 8-10
modifiable, 18-12
modifying, 18-11
NLS parameters in, 8-12
non-null values for nulls, 20-77
object views, 8-14,
13-1
row objects, 11-8
updatability, 13-4
optimization, 20-25
overview of, 1-43,
8-10
partition statistics, 9-12
partition views, 9-10
prerequisites for compilation of, 19-5
privileges for, 26-6
pseudocolumns, 18-12
schema object dependencies, 8-13,
19-4,
19-7
security applications of, 26-6
select-project-join views, 20-25
SQL functions in, 8-12
updatability, 8-13,
13-4,
18-12
uses of, 8-11
virtual memory, 6-16
virtual tables, 1-43
VLDB
parallel SQL, 22-2
partitions, 9-4
W
waits for blocking transaction, 23-10
warehouse
refreshing table data, 22-31
See also
data warehousing
whole database backups, 1-40,
28-21
WITH OBJECT OID clause, 13-3,
13-4
workload skewing, 22-16
wrapper
procedural replication, 31-26
write-ahead, 7-10
writers block readers, 23-10
Y
year 2000, 10-9
Prev
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Library
Product
Contents
