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

DBWn process, 7-11

incremental, 7-9, 28-4

signal DBWn 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 (DBWn), 7-8

checkpoints signal, 7-8

defined, 7-8

least recently used algorithm (LRU), 7-8

media failure, 28-6

multiple DBWn 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

DBWn 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 (Dnnn)

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 (SNPn), 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

Dnnn 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 (SNPn), 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

LCKn 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 (LCKn), 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 (DBWn), 1-17, 7-8

dedicated server, 7-23

dispatcher (Dnnn), 1-18, 7-13

distributed transaction resolution, 7-12

during recovery, 28-14

failure in, 28-3

job queue (SNPn), 1-19, 7-13

message propagation, 16-8

snapshot refresh, 31-9, 31-10

listener, 7-14

shared servers and, 7-20

lock (LCKn), 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 (QMNn), 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

QMNn 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 (QMNn), 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 (SNPn), 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 (Snnn), 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 (SNPn), 1-19, 7-13

automatic snapshot refresh, 31-9

snapshot log and, 31-8

snapshot too old message, 23-5

Snnn background processes, 7-14

SNPn 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