Oracle8i Concepts Release 8.1.5 A67781-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

aborting an instance, 5-10, 32-4

access control, 30-2

discretionary, 1-39

fine-grained access control, 30-22

password encryption, 29-7

privileges, 30-2

roles, 30-16

access methods, 23-34

cluster scans, 23-34

execution plans, 22-2

hash scans, 23-35

index scans, 23-35

table scans, 23-34

access paths

cluster join, 23-40

composite index, 23-42

defined, 22-4

hash cluster key, 23-41

indexed cluster key, 23-41

list of, 23-36

optimization, 23-33

single row by cluster join, 23-38

single row by hash cluster key (with unique key), 23-39

single row by rowid, 23-38

single row by unique or primary key, 23-40

ADMIN OPTION

roles, 30-19

system privileges, 30-3

with EXECUTE ANY TYPE, 14-14

administrator privileges, 5-3

connections audited, 31-5

OUTLN schema, 22-6

statement execution not audited, 31-5

ADT, See object type

Advanced Queuing (Oracle AQ), 19-1

exception handling, 19-11

exporting queue tables, 19-12

message queuing, 19-2

publish/subscribe support, 19-10

event publication, 20-18

queue monitor process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4

recipients, 19-5

rule-based subscriptions, 19-5, 19-6

subscription lists, 19-5

remote databases, 19-9

advanced replication

hybrid configurations, 34-13

multimaster configuration, 34-6

procedural replication, 34-15

synchronous propagation, 34-16

uses for, 34-6, 34-12

Advanced Security option, 33-18

affinity

parallel DML, 26-49

partitions, 26-48

AFTER triggers, 20-9

defined, 20-9

when fired, 20-21

agents for queuing, 19-4

ALERT files, 8-14

ARCn processes, 8-12

redo log, 8-9

aliases

qualifying column names, 14-8, 14-9

qualifying subqueries (inline views), 10-16

ALL, 23-6

ALL_ views, 2-6

ALL_ROWS hint, 23-33

ALL_UPDATABLE_COLUMNS view, 10-15

ALTER ANY TYPE privilege, 14-13

See also privileges

ALTER command, 16-4

auditing partitions, 11-63

ALTER DATABASE command

standby database, 5-7

ALTER INDEX command

no-logging mode for SPLIT PARTITION, 11-58, 25-7

partition attributes, 11-37

REBUILD PARTITION, 11-60

ALTER SESSION

FORCE PARALLEL DDL, 26-24, 26-27

create or rebuild index, 26-25, 26-27

create table as select, 26-26, 26-27

move or split partition, 26-25, 26-28

FORCE PARALLEL DML

insert, 26-23, 26-24, 26-27

update and delete, 26-22, 26-27

ALTER SESSION command, 16-5

dynamic parameters, 5-5

ENABLE PARALLEL DML, 26-38

HASH_JOIN_ENABLED, 24-7

OPTIMIZER_GOAL, 23-32

SET CONSTRAINTS DEFERRED, 28-21

transaction isolation level, 27-7, 27-31

ALTER SYSTEM command, 16-5

dynamic parameters, 5-5

LOG_ARCHIVE_MAX_PROCESSES, 8-12, 32-20

SWITCH LOGFILE option, 8-12

ALTER TABLE command

add or coalesce hash partition, 11-17

auditing, 31-7

CACHE clause, 7-4

DEALLOCATE UNUSED, 4-14

disable or enable constraints, 28-22

DROP COLUMN, 10-6

EXCHANGE PARTITION, 11-11

MERGE PARTITIONS, 11-16

MODIFY CONSTRAINT, 28-23

no-logging mode for SPLIT PARTITION, 11-58, 25-7

partition attributes, 11-27

triggers and, 20-6

UNUSED column, 10-6

validate or novalidate constraints, 28-22

ALTER TABLESPACE command

READ ONLY, 3-10

READ WRITE, 3-11

TEMPORARY or PERMANENT, 3-13

ALTER USER command

temporary segments and, 4-18

ALWAYS_ANTI_JOIN parameter, 24-13

ALWAYS_SEMI_JOIN parameter, 24-13

ANALYZE command, 16-4

creating histograms, 22-11

estimated statistics, 22-14

partition statistics, 11-14

shared pool and, 7-11

anonymous PL/SQL blocks, 16-15, 18-9

applications, 16-17

calling a stored procedure, 16-19

contrasted with stored procedures, 18-9

dynamic SQL, 16-19

performance, 18-9

ANSI SQL standard

datatypes of, 12-22

Oracle certification, 1-3

ANSI/ISO SQL standard, 1-3

composite foreign keys, 28-16

data concurrency, 27-2

isolation levels, 27-10

anti-joins, 24-13

ANY, 23-6

application context, 30-24

applications

application vs. database triggers, 20-3

can find constraint violations, 28-6

data dictionary references, 2-4

data warehousing, 10-32

star queries, 24-14

database access through, 8-2

decision support systems (DSS), 10-33

parallel SQL, 26-2, 26-31

dependencies of, 21-11

direct-load INSERT, 26-38

discrete transactions, 17-8

enhancing security with, 1-42, 28-5

index-organized tables, 10-40

information retrieval (IR), 10-40

network communication and, 6-5

object dependencies and, 21-13

online analytical processing (OLAP), 10-42

online transaction processing (OLTP)

reverse key indexes, 10-31

parallel DML, 26-37

processes, 8-4

program interface and, 8-25

roles and, 30-18

security

application context, 30-24

sharing code, 7-17

spatial applications, 10-42

transaction termination and, 17-5

AQ

exporting queue tables, 19-12

message queuing, 19-2

publish/subscribe support, 19-10

event publication, 20-18

queue monitor process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4

recipients, 19-5

rule-based subscriptions, 19-5, 19-6

subscription lists, 19-5

remote databases, 19-9

AQ_ADMINISTRATOR role, 19-7

AQ_TM_PROCESS parameter, 19-6, 19-7

architecture

client/server, 1-33

MPP, 26-49

of Oracle, 1-14

SMP, 26-49

archived redo log, 1-48

automatic archiving, 32-19

enabling, 32-18

manual archiving, 32-20

ARCHIVELOG mode

archiver process (ARCn) and, 1-19, 8-12, 32-18

defined, 32-18

overview, 1-48

partial database backups, 1-50, 32-24

whole database backups, 32-24

archiver process (ARCn)

automatic archiving, 32-19

described, 1-19, 8-12

example, 32-18

multiple processes, 1-19, 8-12

not used for manual archiving, 32-21

trace file, 32-20

ARCn background process, 1-19, 8-12

See also archiver process

array processing, 16-13

arrays

size of VARRAYs, 13-11

variable (VARRAYs), 13-11

asynchronous I/O

parallel recovery and, 32-11

asynchronous processing, 19-2

atomic nulls, 14-3

attributes

leaf-level, 14-18

leaf-level scalar, 14-18

attributes of object types, 13-2, 13-4

AUDIT command, 16-4

locks, 27-29

audit trail

deleting data in dictionary, 2-5

auditing, 1-44, 31-1

audit options, 31-3

audit records, 31-3

audit trails, 31-3

database, 31-3

operating system, 31-5, 31-6

by access, 31-11

mandated for, 31-12

by session, 31-10

prohibited with, 31-12

connect with administrator privileges, 31-5

data dictionary used for, 2-5

database and OS usernames, 29-4

DDL statements, 31-7

described, 1-44, 31-2

distributed databases and, 31-6

DML statements, 31-7

partitioned tables and indexes, 11-63

privilege use, 31-2, 31-7

range of focus, 31-3, 31-9

schema object, 31-2, 31-8

security and, 31-6

startup and shutdown, 31-5

statement, 31-2, 31-7

successful executions, 31-9

transaction independence, 31-4

types of, 31-2

unsuccessful executions, 31-9

user, 31-12

when options take effect, 31-6

authentication

database administrators, 29-12

described, 29-3

multi-tier, 29-9

network, 29-4

operating system, 29-4

Oracle, 29-7

public key infrastructure, 29-5

remote, 29-6

Automated Standby Database, 32-26

B

B*-tree indexes, 10-27

bitmap indexes vs., 10-32, 10-33

index-organized tables, 10-36

back-ends, 6-2

background processes, 1-18, 8-5

described, 8-5

diagrammed, 8-6

overview of, 1-18

trace files for, 8-14

See also processes

backups

control files, 32-25

datafiles, 32-25

for read-only tablespaces, 32-26

overview of, 1-45, 32-23

parallel, 32-16

partial, 1-50, 32-24

Recovery Manager, 1-51, 32-15

types of, 1-49

using Export to supplement, 32-25

whole database backup, 1-50, 32-23

base tables, 1-24

data dictionary, 2-2

See also views

basic replication

uses of, 34-11

BEFORE triggers, 20-9

defined, 20-9

when fired, 20-21

BETWEEN, 23-7

BFILE datatype, 12-14

binary data

BFILEs, 12-14

BLOBs, 12-13

RAW and LONG RAW, 12-14

bind variables

optimization, 23-52

user-defined types, 13-14

bitmap indexes, 10-32

cardinality, 10-33

nulls and, 10-8, 10-35

parallel query and DML, 10-33

partitioned tables, 11-14

scans of, 23-36

star transformation, 24-16

bitmap tablespace management, 3-8

temporary tablespaces, 3-13

BLOBs, 12-13

block

anonymous, 16-15, 18-9

block-level recovery, 32-14

database, 4-3

See also data blocks

block sampling, 22-14

block server process (BSP), 27-6

blocking transactions, 27-11

block-level recovery, 27-21, 32-14

BOOLEAN datatype, 12-2

branch blocks, 10-28

BSP background process, 27-6

buffer cache, 7-3, 8-8

extended buffer cache (32-bit), 7-14

multiple buffer pools, 7-5

buffer pools, 7-5

BUFFER_POOL_KEEP parameter, 7-5

BUFFER_POOL_RECYCLE parameter, 7-5

buffers

database buffer cache, 1-16, 7-3, 8-8

Fast-Start Checkpointing, 32-13

incremental checkpoint, 8-8

redo log buffer, 1-16, 7-6

BUILD_PART_INDEX procedure, 11-30

business rules

enforcing in application code, 28-5

enforcing using stored procedures, 28-5

enforcing with constraints, 1-58, 28-1

advantages of, 28-5

enforcing with triggers, 1-59

C

CACHE clause, 7-4

Cache Fusion

read consistency, 27-6

caches

buffer cache, 7-3

multiple buffer pools, 7-5

cache hit, 7-4

cache miss, 7-4

data dictionary, 2-4, 7-10

location of, 7-6

database buffer, 1-16

library cache, 7-6, 7-7, 7-10

object cache, 13-14, 14-16

object views, 15-4

private SQL area, 7-8

shared SQL area, 7-6, 7-8

writing of buffers, 8-8

calls

Oracle call interface, 8-25

remote procedure, 33-12

cannot serialize access, 27-11

capture avoidance rule, 14-8

cardinality, 10-33

Cartesian products, 23-3

CASCADE actions

DELETE statements and, 28-17

catalog, replication, 34-14

century, 12-12

certificate authority, 29-5

chaining of rows, 4-10, 10-5

CHAR datatype, 12-5

blank-padded comparison semantics, 12-5

character sets

CLOB and NCLOB datatypes, 12-13

column lengths, 12-6

for various languages, 5-5

NCHAR and NVARCHAR2, 12-6

CHARTOROWID function, 12-23

CHECK constraints, 28-17

checking mechanism, 28-20

defined, 28-17

multiple constraints on a column, 28-18

partially null foreign keys, 28-16

partition views, 11-11

subqueries prohibited in, 28-18

checkpoint process (CKPT), 1-19, 8-11

checkpoints

checkpoint process (CKPT), 1-19, 8-11

control files and, 32-22

DBWn process, 8-8, 8-11

Fast-Start Checkpointing, 32-13

incremental, 8-8

statistics on, 8-11

CHOOSE hint, 23-33

CKPT background process, 1-19, 8-11

client/server architectures, 6-2

clients, 1-33

diagrammed, 6-2

direct and indirect connections, 33-2

distributed databases and, 33-2

distributed processing in, 6-2

overview of, 1-33, 6-2

program interface, 8-25

CLOB datatype, 12-13

clone databases

mounting, 5-7

cluster joins, 24-5

cluster keys, 1-27, 10-48

clustered computer systems

Oracle Parallel Server, 5-3

clusters

cannot be partitioned, 11-2

choosing data to cluster, 10-48

defined, 1-27

dictionary locks and, 27-29

hash, 10-50

allocation of space for, 10-55

collision resolution, 10-53

contrasted with index, 10-51

root blocks, 10-55

scans of, 23-35, 23-39, 23-41

single-table, 10-57

storage of, 10-51

index, 10-50

contrasted with hash, 10-51

scans of, 23-41

indexes on, 10-21

cannot be partitioned, 11-2

joins and, 10-48, 23-38, 23-40, 24-5

keys, 1-27, 10-48, 10-49

affect indexing of nulls, 10-8

overview of, 10-46

performance considerations of, 10-48

rowids and, 10-7

scans of, 7-4, 23-34, 23-38

hash, 23-39, 23-41

joins, 23-40

setting parameters of, 10-49

storage format of, 10-49

storage parameters of, 10-4

coalescing extents, 4-15

coalescing free space

extents, 4-13

SMON process, 1-19, 8-11

within data blocks, 4-9

collections, 13-10

index-organized tables, 10-38

key compression, 10-30

nested tables, 13-12

variable arrays (VARRAYs), 13-11

columns

cardinality, 10-33

column names

qualifying in queries, 14-8, 14-9

column objects, 13-8

indexes, 14-6

default values for, 10-8

defined, 1-23

described, 10-3

dropping, 10-6

integrity constraints, 10-4, 10-8, 28-4, 28-7

maximum in concatenated indexes, 10-23

maximum in view or table, 10-12

nested tables, 10-9

order of, 10-7

prohibiting nulls in, 28-7

pseudocolumns

ROWID, 12-15

ROWNUM, 23-16, 23-25, 23-49

USER, 30-7

selectivity, 22-8

histograms, 22-8, 22-10

unused, 10-6

COMMENT command, 16-4

COMMIT command, 16-5

ending a transaction, 17-2, 17-4

fast commit, 8-10

implied by DDL, 17-2, 17-4

two-phase commit and, 17-7, 33-13

two-phase commit in parallel DML, 26-40

committing transactions

defined, 17-2

fast commit, 8-10

group commits, 8-10

implementation, 8-10

overview, 1-54

parallel DML, 26-40

communication protocols, 6-5

comparison methods, 13-6

compatibility, 1-3

compatibility levels

transportable tablespaces, 3-14

COMPATIBLE parameter

read-only tablespaces, 3-11

compilation of object types, 14-17

compiled PL/SQL, 18-17

advantages of, 18-8

procedures, 18-9

pseudocode, 18-18, 20-25

recompiling, 18-19

shared pool, 16-16

triggers, 20-25

compiled triggers, 20-25

complex view merging, 23-17

composite indexes, 10-22

compression of free space in data blocks, 4-9

compression, index key, 10-29

concatenated indexes, 10-22

concurrency

defined, 1-30

described, 27-2

direct-load INSERT, 25-11

enforced with locks, 1-32

limits on

per database, 29-20

per user, 29-17

partition maintenance, 11-50

restrictions on, 1-43, 25-11

transactions and, 27-15

configuration of a database

parameter file, 5-4

process structure, 8-2

conflicts

procedural replication, 34-16

CONNECT BY clause

optimizing view queries, 23-16

CONNECT INTERNAL, 5-3

CONNECT role, 30-22

user-defined types, 14-13, 14-14

connectibility, 1-2

connections

defined, 8-4

embedded SQL, 16-6

listener process and, 6-6, 8-14

restricting, 5-6

sessions contrasted with, 8-4

usernames, 29-2

with administrator privileges, 5-3

audit records, 31-5

consistency of data, 1-55

multiversion consistency model, 1-31

See also read consistency

constants

comparisons and, 23-5

evaluation of expressions, 23-5

in stored procedures, 16-17

when computed, 23-5

constraints, 1-58

alternatives to, 28-5

applications can find violations, 28-6

CHECK, 28-17

default values and, 28-20

defined, 10-4

disabling temporarily, 28-7

effect on performance, 28-6

ENABLE or DISABLE, 28-22

enforced with indexes, 10-23

PRIMARY KEY, 28-12

UNIQUE, 28-10

FOREIGN KEY, 1-59, 28-13

mechanisms of enforcement, 28-18

modifying, 28-23

NOT NULL, 28-7, 28-11

object tables, 14-5

overview, 1-58

parallel create table, 26-25

PRIMARY KEY, 1-59, 28-11

prohibited in views, 10-12

referential

effect of updates, 28-16

self-referencing, 28-15

triggers cannot violate, 20-21

triggers contrasted with, 20-5

types listed, 1-58, 28-1

UNIQUE key, 1-59, 28-8

partially null, 28-11

VALIDATE or NOVALIDATE, 28-22

what happens when violated, 28-5

when evaluated, 10-8

constructor methods, 1-57, 13-6, 14-18

literal invocation of, 14-4

contention

for data

deadlocks, 8-19, 27-17

lock escalation does not occur, 27-17

for rollback segments, 4-21

control files, 1-13, 32-21

backing up, 32-25

changes recorded, 32-22

checkpoints and, 32-22

contents, 32-21

how specified, 5-4

multiplexed, 1-49, 32-22

overview, 1-13, 32-21

physical database structure, 1-5

recovery and, 1-49

used in mounting database, 5-6

converting data

ANSI datatypes, 12-22

program interface, 8-25

SQL/DS and DB2 datatypes, 12-22

correlation names

inline views, 10-16

cost based optimization, 33-10

cost-based optimization, 22-7

extensible optimization, 22-16

histograms, 22-8

query rewrite, 10-17

selectivity of predicates, 22-8

histograms, 22-8, 22-10

user-defined, 22-17

star queries, 24-14

statistics, 22-8, 23-32

user-defined, 22-17

user-defined costs, 22-17

CPU time limit, 29-17

crash recovery, 32-4, 32-13

instance failure, 1-46, 5-10, 32-4

opening a database, 5-8

read-only tablespaces, 32-6

required after aborting instance, 5-10

SMON process, 1-19, 8-11

CREATE ANY TYPE privilege, 14-13

See also privileges

CREATE CLUSTER command

HASHKEYS clause, 10-53, 10-57

SINGLE TABLE HASHKEYS, 10-57

storage parameters, 4-17

CREATE command, 16-4

CREATE FUNCTION command, 18-17

CREATE INDEX command

no-logging mode, 11-58, 25-7

object types, 14-6

partition attributes, 11-37

rules of parallelism, 26-25

storage parameters, 4-17

temporary segments and, 4-18

CREATE OUTLINE statement, 22-6

CREATE PACKAGE BODY command, 18-12, 18-17

CREATE PACKAGE command

examples, 18-12, 20-10

locks, 27-29

package name, 18-17

CREATE PROCEDURE command

example, 18-6

locks, 27-29

procedure name, 18-17

CREATE SYNONYM command

locks, 27-29

CREATE TABLE AS SELECT

rules of parallelism

index-organized tables, 26-31

CREATE TABLE command

AS SELECT

decision support systems, 26-31

direct-load INSERT vs., 25-2

no-logging mode, 11-58, 25-7

rules of parallelism, 26-25

space fragmentation, 26-33

temporary storage space, 26-33

auditing, 31-7, 31-10

CACHE clause, 7-4

enable or disable constraints, 28-22

examples

column objects, 13-5, 14-8

nested tables, 13-12

object tables, 13-8, 13-12, 14-5, 14-8

locks, 27-29

parallelism, 26-31

index-organized tables, 26-31

partition attributes, 11-27

storage parameters, 4-17

triggers and, 20-6

CREATE TABLESPACE command

TEMPORARY clause, 3-13

CREATE TEMPORARY TABLE command, 10-10

CREATE TEMPORARY TABLESPACE command, 3-13

CREATE TRIGGER command

compiled and stored, 20-25

examples, 20-11, 20-14, 20-24

object tables, 14-7

locks, 27-29

CREATE TYPE command

incomplete types, 14-16

nested tables, 13-4, 13-12, 14-4

object types, 13-4, 14-3, 14-4, 14-8

object views, 15-3

VARRAYs, 13-11

CREATE TYPE privilege, 14-13

See also privileges

CREATE USER command

temporary segments and, 4-18

CREATE VIEW command

examples, 20-14

object views, 15-3

locks, 27-29

CREATE_STORED_OUTLINES session parameter, 22-6

cross joins, 23-3

current user, 18-10

cursors

creating, 16-11

defined, 16-6

embedded SQL, 16-5

maximum number of, 16-7

object dependencies and, 21-10

opening, 7-9, 16-7

overview of, 1-17

private SQL areas and, 7-9, 16-6

recursive, 16-7

recursive SQL and, 16-7

stored procedures and, 16-18

D

dangling REFs, 13-9, 13-10

data

access to, 1-51

control of, 29-2

fine-grained access control, 30-22

message queues, 19-7

security domains, 29-2

concurrent access to, 27-2

consistency of

defined, 1-55

examples of lock behavior, 27-32

locks, 27-3

manual locking, 27-31

read consistency, 1-31

repeatable reads, 27-6

transaction level, 27-6

underlying principles, 27-15

distributed manipulation of, 1-35

how stored in tables, 10-4

integrity of, 1-30, 10-4, 28-2

CHECK constraints, 28-17

enforcing, 28-4, 28-5

overview, 1-57

parallel DML restrictions, 26-45

referential, 28-3

two-phase commit, 1-35

types, 28-3

locks on, 27-20

replicating, 1-36

data blocks, 1-10, 4-2

allocating for extents, 4-13

block-level recovery, 32-14

cached in memory, 8-8

clustered, 10-49

coalescing extents, 4-13

coalescing free space in blocks, 4-9

controlling free space in, 4-5

format, 4-3

free lists and, 4-9

hash keys and, 10-55

how rows stored in, 10-5

overview, 4-2

read-only transactions and, 27-32

row directory, 10-5

shared in clusters, 10-46

shown in rowids, 12-17, 12-18

space available for inserted rows, 4-9

stored in the buffer cache, 7-3

writing to disk, 8-8

data conversion

ANSI datatypes, 12-22

program interface, 8-25

SQL/DS and DB2 datatypes, 12-22

Data Definition Language (DDL)

auditing, 31-7

commit implied by, 17-4

defined, 1-52

described, 16-4

embedding in PL/SQL, 16-19

locks, 27-28

parallel DDL, 26-3

parsing with DBMS_SQL, 16-19

processing statements, 16-14

roles and privileges, 30-20

data dictionary

access to, 2-2

adding objects to, 2-4

ALL prefixed views, 2-6

audit trail (SYS.AUD$), 2-5

backups, 32-25

cache, 7-10

location of, 7-6

content of, 2-2, 7-10

procedures, 18-18

datafile 1, 3-6, 32-25

DBA prefixed views, 2-6

defined, 1-30, 2-2

dependencies tracked by, 21-3

dictionary-managed tablespaces, 3-7

DUAL table, 2-7

dynamic performance tables, 2-7

locks, 27-28

owner of, 2-3

prefixes to views of, 2-5

public synonyms for, 2-4

row cache and, 7-10

statistics in, 22-14, 23-32

partition statistics, 11-14

structure of, 2-2

SYSTEM tablespace, 2-2, 2-5, 3-6

updates of, 2-5

USER prefixed views, 2-6

uses of, 2-3

table and column definitions, 16-11

validity of procedures, 18-18

views used in optimization, 22-14

data locks

conversion, 27-16

duration of, 27-15

escalation, 27-16

Data Manipulation Language (DML)

auditing, 31-7

defined, 1-52

described, 16-3

distributed transactions, 33-11

locks acquired by, 27-25

parallel DML, 26-3, 26-35

partition locks, 11-45

privileges controlling, 30-5

processing statements, 16-10

serializable isolation for subqueries, 27-14

transaction model for parallel DML, 26-39

triggers and, 20-3, 20-23

data models, 1-22

data object number

extended rowid, 12-17

data segments, 1-11, 4-16, 10-4

data warehousing

basic replication for, 34-12

bitmap indexes, 10-32

dimension schema objects, 1-29, 10-18

dimensions, 24-14

hierarchies, 1-29, 10-18

materialized views, 10-17

refreshing table data, 26-37

star queries, 24-14

summaries, 10-17

Database, 5-3

database administrators (DBAs)

authentication, 29-12

data dictionary views, 2-6

DBA role, 14-13, 30-22

password files, 29-13

responsible for backup and recovery, 32-2

database buffers

after committing transactions, 17-6

buffer cache, 7-3, 8-8

clean, 8-8

committing transactions, 8-10

defined, 1-16, 7-3

dirty, 7-3, 8-8

free, 7-3

multiple buffer pools, 7-5

pinned, 7-3

size of cache, 7-5

writing of, 8-8

database links, 1-29

defined, 1-29

overview of, 33-6

partition-extended table names, 11-65

database management system (DBMS), 1-2

object-relational DBMS, 13-2

Oracle server, 1-4

principles, 1-22

database structures

control files, 1-13, 32-21

data blocks, 1-10, 4-2, 4-3

data dictionary, 1-30, 2-1

datafiles, 1-11, 3-1, 3-16

extents, 1-10, 4-2, 4-11

logical, 1-5, 1-9

memory, 1-14, 7-1

physical, 1-5

processes, 1-14, 1-17, 8-1

redo log files, 1-12, 32-7

revealing with rowids, 12-18

schema objects, 1-10, 10-2

segments, 1-10, 4-2, 4-16

tablespaces, 1-9, 3-1, 3-6

database triggers, 1-59, 20-1

See also triggers

database writer process (DBWn), 8-8

checkpoints, 8-8

defined, 8-8

least recently used algorithm (LRU), 8-8

media failure, 32-6

multiple DBWn processes, 8-8

overview of, 1-18

trace file, 32-6

when active, 8-8

write-ahead, 8-9

writing to disk at checkpoints, 8-11

databases

access control

overview, 1-51

password encryption, 29-7

security domains, 29-2

backing up, 1-50, 32-23

clone database, 5-7

closing, 5-10

aborting the instance, 5-10, 32-4

configuring, 5-4

contain schemas, 29-2

defined, 1-8, 1-9

dismounting, 5-10

distributed, 1-35, 33-1

changing global database name, 7-11

nodes of, 1-35

overview of, 1-33, 1-34, 33-1

site autonomy of, 33-16

statement optimization on, 23-30

table replication, 1-36

two-phase commit, 1-35

global database names, 33-4

limitations on usage, 29-15

managing

Enterprise Manager, 33-19

modes of archiving, 32-18

mounting, 5-6

name stored in control file, 32-21

open and closed, 5-2

opening, 5-7

acquiring rollback segments, 4-26

opening read-only, 5-9

recovery of, 1-45, 32-2

scalability, 6-4, 26-2, 26-37

shutting down, 5-9

size of

how determined, 3-4

standby, 5-7, 32-26

starting up, 5-2

forced, 5-10

structures

control files, 1-13, 32-21

data blocks, 1-10, 4-2, 4-3

data dictionary, 1-30, 2-1

datafiles, 1-11, 3-1, 3-16

extents, 1-10, 4-2, 4-11

logical, 1-5, 1-9, 4-1

memory, 1-14, 7-1

physical, 1-5, 1-11

processes, 1-14, 1-17, 8-1

redo log files, 1-12, 32-7

revealing with rowids, 12-18

schema objects, 1-10, 10-2

segments, 1-10, 4-2, 4-16

tablespaces, 1-9, 3-1, 3-6

datafile 1, 3-16

backup, 32-25

data dictionary, 3-6, 32-25

SYSTEM tablespace, 3-6, 3-16

datafiles

backing up, 32-25

contents of, 3-16

datafile 1, 3-6, 3-16

backup, 32-25

SYSTEM tablespace, 3-6, 3-16

in online or offline tablespaces, 3-17

named in control files, 32-21

overview of, 1-9, 1-12, 3-16

parallel recovery, 32-11

physical database structure, 1-5

read-only, 3-10

recovery, 32-6

read-only tablespaces and, 3-12

relationship to tablespaces, 3-2

shown in rowids, 12-17, 12-18

taking offline, 3-17

temporary, 3-17

unrecoverable, 32-17

datatypes, 12-2, 12-3

ANSI, 12-22

array types, 13-11

BOOLEAN, 12-2

CHAR, 12-5

character, 12-5, 12-13

collections, 13-10

conversions of

by program interface, 8-25

non-Oracle types, 12-22

Oracle to another Oracle type, 12-23

DATE, 12-10

DB2, 12-22

how they relate to tables, 10-3

in PL/SQL, 12-2

list of available, 12-2

LOB datatypes, 12-12

BFILE, 12-14

BLOB, 12-13

CLOB and NCLOB, 12-13

default logging mode, 25-7

LONG, 12-7

storage of, 10-7

multimedia, 13-3

NCHAR and NVARCHAR2, 12-6

nested tables, 10-9, 13-12

NUMBER, 12-8

object types, 1-22, 13-4

of columns, 1-23

RAW and LONG RAW, 12-14

ROWID, 12-15, 12-16

SQL/DS, 12-22

summary, 12-3

user-defined, 13-1, 13-3

statistics, 22-17

VARCHAR, 12-6

VARCHAR2, 12-5

DATE datatype, 12-10

arithmetic with, 12-11

changing default format of, 12-10

Julian dates, 12-11

midnight, 12-11

partition pruning, 11-22

partitioning, 11-14, 11-21

DB_BLOCK_BUFFERS parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_BLOCK_LRU_LATCHES parameter, 8-8

DB_BLOCK_SIZE parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 23-51

cost-based optimization, 24-9

DB_FILES parameter, 7-15

DB_NAME parameter, 32-22

DB_WRITER_PROCESSES parameter, 1-18, 8-8

DBA role, 30-22

user-defined types, 14-13

DBA_ views, 2-6

DBA_QUEUE_SCHEDULES view, 19-10

DBA_SYNONYMS.SQL script

using, 2-7

DBA_UPDATABLE_COLUMNS view, 10-15

DBMS, 1-2

general requirements, 1-51

object-relational DBMS, 13-2

DBMS_AQ package, 19-4

DBMS_AQADM package, 19-4, 19-7

DBMS_JOB package, 8-13

Oracle supplied packages, 18-16

DBMS_LOCK package, 27-39

Oracle supplied packages, 18-16

DBMS_PCLXUTIL package, 11-30

DBMS_RLS package

security policies, 30-23

uses definer rights, 30-8

DBMS_SQL package, 16-19

Oracle supplied packages, 18-16

parsing DDL statements, 16-19

DBMS_STATS package, 22-12

creating histograms, 22-11

estimated statistics, 22-14

partition statistics, 11-14

DBWn background process, 8-8

See also database writer process

DDL, 1-52, 16-4

See also Data Definition Language

dead transactions, 32-4

block-level recovery, 32-14

deadlocks

artificial, 8-19

avoiding, 27-19

defined, 27-17

detection of, 27-18

distributed transactions and, 27-18

deallocating extents, 4-14

decision support application

basic replication for, 34-11

decision support systems (DSS), 11-6

bitmap indexes, 10-33

disk striping, 26-48

materialized views, 10-17

parallel DML, 26-37

parallel SQL, 26-2, 26-31, 26-37

partitions, 11-6

performance, 11-9, 26-37

scoring tables, 26-38

dedicated servers, 8-22

defined, 1-18

examples of use, 8-24

multi-threaded servers vs., 8-16

default values, 10-8

constraints effect on, 10-8, 28-20

user-defined types, 14-4

deferred constraints

deferrable or nondeferrable, 28-21

initially deferred or immediate, 28-21

define phase of query processing, 16-12

definer rights, 18-9

name resolution, 18-19

procedure security, 30-7

degree of parallelism, 26-20, 26-23

between query operations, 26-13

parallel SQL, 26-7, 26-16

delete cascade constraint, 28-17

DELETE command, 16-4

foreign key references and, 28-16

freeing space in data blocks, 4-9

no-logging mode, 25-7

LOBs, 25-7

parallel DELETE, 26-21

triggers and, 20-2, 20-6

delete no action constraint, 28-16

DELETE privilege for object tables, 14-15, 14-16

denormalized tables, 1-29, 10-19

denormalized views

star schemas, 24-15

dependencies, 21-1

between schema objects, 21-2

function-based indexes, 10-26, 21-7

local, 21-10

managing, 21-1

non-existent referenced objects and, 21-8

object type definitions, 14-16, 14-17

on non-existence of other objects, 21-8

Oracle Forms triggers and, 21-13

privileges and, 21-6

remote objects and, 21-10

shared pool and, 21-10

dereferencing, 13-10

implicit, 13-10

describe phase of query processing, 16-12

DETERMINISTIC functions, 23-9

function-based indexes, 21-8

deterministic functions, 23-9

dictionary

See data dictionary

dictionary cache locks, 27-30

dictionary-managed tablespaces, 3-7

different-row writers block writers, 27-10

Digital's POLYCENTER Manager on Net View, 33-20

dimensions, 1-29, 10-18

attributes, 1-29, 10-19

hierarchies, 1-29, 10-18

join key, 1-29, 10-19

normalized or denormalized tables, 1-29, 10-19

star joins, 24-14

star queries, 24-14

direct-load INSERT, 25-2

logging mode, 25-5

parallel INSERT, 25-3

parallel load vs. parallel INSERT, 25-2

restrictions, 25-11, 26-43

serial INSERT, 25-3

space management, 25-8

Directory-enabled Oracle Security Manager, 29-5

dirty buffer, 7-3

Fast-Start Checkpointing, 32-13

incremental checkpoint, 8-8

dirty read, 27-2, 27-10

dirty write, 27-10

DISABLE constraints, 28-22

DISABLED indexes, 21-8

disaster recovery, 32-26

disconnected environments

as in advanced replication, 34-12

discrete transaction management

summary, 17-8

discretionary access control, 1-39, 29-2

disk affinity

parallel DML, 26-49

partitions, 26-48

disk failures, 1-46, 32-5

disk space

controlling allocation for tables, 10-4

datafiles used to allocate, 3-16

disk striping

affinity, 26-48

partitions, 11-9

dispatcher processes (Dnnn)

defined, 1-20

described, 8-14

limiting SGA space per session, 29-18

listener process and, 8-14

network protocols and, 8-14

prevent startup and shutdown, 8-20

response queue and, 8-17

user processes connect via Net8, 8-14, 8-16

DISTINCT operator

optimizing views, 23-17

distributed databases, 33-1

auditing and, 31-6

client/server architectures and, 6-2

database links, 33-6

deadlocks and, 27-18

dependent schema objects and, 21-10

diagrammed, 33-2

different Oracle versions, 33-7

distributed queries, 33-11

distributed updates, 33-11

global object names, 33-6

heterogeneous, 33-8

job queue processes (SNPn), 1-20, 8-13

management tools, 33-19

message propagation, 19-9

nodes of, 33-2

overview of, 1-34, 33-2

recoverer process (RECO) and, 8-12

remote dependencies, 21-11

remote queries and updates, 33-11

server can also be client in, 6-2

site autonomy of, 33-16

statement optimization on, 23-30

table replication, 1-36

transparency of, 33-14

two-phase commit, 1-35, 33-13

distributed processing environment

client/server architecture in, 1-33, 6-2

data manipulation statements, 16-10

described, 1-33, 6-2

distributed databases vs., 33-7

materialized views (snapshots), 10-17

distributed query optimization, 33-10

distributed transactions

defined, 33-12

distributed statements, 23-4

optimizing, 23-30

parallel DDL restrictions, 26-28

parallel DML restrictions, 26-28, 26-46

routing statements to nodes, 16-11

sample table scan not supported, 23-34

two-phase commit and, 1-35, 17-7

DISTRIBUTED_TRANSACTIONS parameter, 8-12

distributing application loads

as in advanced replication, 34-7

DML, 1-52, 16-3

See also Data Manipulation Language

DML subpartition locks, 11-46

Dnnn background processes, 8-14

See also dispatcher processes

domain index, 10-43

domain indexes

extensible optimization, 22-16

user-defined statistics, 22-17

drivers, 8-26

DROP ANY TYPE privilege, 14-13

See also privileges

DROP COLUMN clause, 10-6

DROP command, 16-4

DROP TABLE command

auditing, 31-7

triggers and, 20-6

DROP TYPE command

dependencies and, 14-17

FORCE option, 14-17

DSS database

disk striping, 26-48

parallel DML, 26-37

partitioning indexes, 11-37

partitions, 11-6

performance, 11-9

scoring tables, 26-38

DUAL table, 2-7

dump files

Export and Import, 14-20

dynamic partitioning, 26-6

dynamic performance tables (V$ tables), 2-7

dynamic predicates

in security policies, 30-23

dynamic SQL

DBMS_SQL package, 16-19

embedded, 16-19

name resolution, 18-20

E

embedded SQL statements, 1-53, 16-5

dynamic SQL in PL/SQL, 16-19

ENABLE constraints, 28-22

Enterprise Manager, 33-19

ALERT file, 8-15

checkpoint statistics, 8-11

distributed databases, 33-19

executing a package, 18-6

executing a procedure, 18-4

granting roles, 30-18

granting system privileges, 30-3

lock and latch monitors, 27-29

parallel recovery, 32-11

PL/SQL, 16-17, 16-19

schema object privileges, 30-4

showing size of SGA, 7-13

shutdown, 5-9, 5-10

SQL statements, 16-2

startup, 5-5

statistics monitor, 29-19

equijoins

cluster joins, 24-5

defined, 23-3

hash joins, 24-7

sort-merge, 24-4

equipartitioning, 11-24

examples, 11-25, 11-30, 11-32

LOB columns, 11-38

local indexes, 11-29

on one dimension, 11-24

overflow of index-organized tables, 11-42, 11-44

range partitioning, 11-24

errors

in embedded SQL, 16-6

tracked in trace files, 8-14

exceptions

during trigger execution, 20-23

raising, 16-18

stored procedures and, 16-18

EXCHANGE PARTITION, 11-11

exclusive locks

row locks (TX), 27-20

RX locks, 27-23

table locks (TM), 27-21

exclusive mode, 4-27

EXECUTE ANY TYPE privilege, 14-13, 14-14

See also privileges

EXECUTE privilege

user-defined types, 14-14

verifying user access, 18-18

See also privileges

EXECUTE user-defined type, 14-13

execution plan

accessing views, 23-19, 23-22, 23-23

complex statements, 23-14

compound queries, 23-27, 23-28, 23-29

joining views, 23-25

joins, 24-2, 24-8

OR operators, 23-12

star transformation, 24-18

execution plans

examples, 23-14

execution sequence of, 22-5

EXPLAIN PLAN, 16-4

location of, 7-8

overview of, 22-2

parsing SQL, 16-11

partitions and partition views, 11-11, 11-14

plan stability, 22-6

viewing, 22-4

EXP_FULL_DATABASE role, 30-22

EXPLAIN PLAN command, 16-4

access paths, 23-38, 23-39, 23-40, 23-41, 23-42, 23-43, 23-44, 23-45, 23-46, 23-47, 23-48, 23-49, 23-50

partition pruning, 11-22

star query, 24-17

star transformation, 24-18

explicit locking, 27-31

Export utility, 1-5

copying statistics, 22-8

partition maintenance operations, 11-48

use in backups, 32-25

user-defined types, 14-20

extended rowid format, 12-17

extensible optimization, 22-16

user-defined costs, 22-17

user-defined selectivity, 22-17

user-defined statistics, 22-17

extents

allocating, 4-12

allocating data blocks for, 4-13

allocation to rollback segments

after segment creation, 4-24

at segment creation, 4-22

allocation, how performed, 4-13

as collections of data blocks, 4-11

coalescing, 4-15

deallocation

from rollback segments, 4-25

when performed, 4-14

defined, 4-2

dictionary managed, 3-7

dropping rollback segments and, 4-25

in rollback segments

changing current, 4-22

incremental, 4-11

locally managed, 3-8

managing, 4-12

materialized views, 4-15

overview of, 4-11

parallel DDL, 26-33

parallel INSERT

storage parameters, 25-8

external procedures, 16-20, 18-11

external reference, 18-10

name resolution, 18-19

F

fact tables

star joins, 24-14

star queries, 24-14

failover sites

as in advanced replication, 34-6

failures, 32-2

archiving redo log files, 32-20

database buffers and, 32-8

described, 1-45, 32-2

instance, 1-46, 32-4

recovery from, 5-8, 5-10, 32-4

internal errors

tracked in trace files, 8-14

media, 1-46, 32-5

network, 32-3

safeguards provided, 32-6

statement and process, 1-45, 8-11, 32-2

survivability, 32-26

user error, 1-45, 32-2

See also recovery

fast commit, 8-10

fast full index scans, 23-36

fast refresh, 10-18

FAST_START_IO_TARGET parameter, 32-13

Fast-Start Checkpointing, 32-13

Fast-Start On-Demand Rollback, 32-10

Fast-Start Parallel Rollback, 32-14

Fast-Start Recovery, 32-13

fetching rows in a query, 16-14

embedded SQL, 16-6

file management locks, 27-30

files

ALERT and trace files, 8-9, 8-14

Export and Import dump file, 14-20

initialization parameter, 5-4, 5-5

LISTENER.ORA, 6-6

operating system, 1-5

Oracle database, 1-9, 1-11, 32-6

password, 29-13

administrator privileges, 5-3

See also control files, datafiles, redo log files

fine-grained access control, 30-22

FIPS standard, 16-6

FIRST_ROWS hint, 23-33

fixed views, 2-7

flagging of nonstandard features, 16-6

FORCE option

object type dependencies, 14-17

FORCE PARALLEL DDL option, 26-24, 26-27

create or rebuild index, 26-25, 26-27

create table as select, 26-26, 26-27

move or split partition, 26-25, 26-28

FORCE PARALLEL DML option

insert, 26-23, 26-24, 26-27

update and delete, 26-22, 26-27

FOREIGN KEY constraints

changes in parent key values, 28-16

constraint checking, 28-20

deleting parent table rows and, 28-17

maximum number of columns in, 28-13

nulls and, 28-15

updating parent key tables, 28-16

foreign keys, 1-58

defined, 1-59

partially null, 28-16

privilege to use parent key, 30-5

fragmentation

parallel DDL, 26-33

free lists, 4-9

free space

coalescing extents, 4-13

SMON process, 1-19, 8-11

coalescing within data blocks, 4-9

free lists, 4-9

parameters for data blocks, 4-5

section of data blocks, 4-5

front-ends, 6-2

full index scans, 23-35

full table scans, 23-34, 23-48

LRU algorithm and, 7-4

multiblock reads, 23-51

parallel execution, 26-5, 26-6

rule-based optimizer, 23-54

selectivity and, 23-51

function-based indexes, 10-24

dependencies, 10-26, 21-7

DISABLED, 21-8

privileges, 10-26, 21-8

UNUSABLE, 21-8

functions

function-based indexes, 10-24

hash functions, 10-54

Java

parallel execution, 26-46

PL/SQL, 18-2, 18-6

contrasted with procedures, 1-56, 18-2

DETERMINISTIC, 21-8, 23-9

deterministic, 23-9

parallel execution, 26-46

privileges for, 30-7

roles, 30-20

See also procedures

SQL, 16-2

COUNT, 10-35

default column values, 10-8

in CHECK constraints, 28-18

in views, 10-14

NVL, 10-8

optimizing view queries, 23-23

user-defined

extensible optimization, 22-16

fuzzy reads, 27-3

G

gateways, 33-8

global database names

shared pool and, 7-11

global indexes

partitioning, 11-32

managing partitions, 11-33, 11-60

summary of index types, 11-34

global schema object names, 1-29, 33-6

global user, 18-21, 33-18

current user links, 18-21

GRANT ANY PRIVILEGE system privilege, 30-3

GRANT command, 16-4

locks, 27-29

GRANT option for EXECUTE privilege, 14-14

granting

execute user-defined type, 14-14

privileges and roles, 30-3

GROUP BY clause

optimizing views, 23-17

temporary tablespaces, 3-12

group commits, 8-10

groups, instance, 26-19

guesses in logical rowids, 12-20

staleness, 12-21

statistics for, 12-21

H

handles for SQL statements, 1-17, 7-9

hash clusters, 1-29, 10-50

allocation of space for, 10-55

collision resolution, 10-53

contrasted with index, 10-51

overview of, 1-29

root blocks, 10-55

scans of, 23-35, 23-39, 23-41

single-table hash clusters, 10-57

storage of, 10-51

hash join, 24-7

HASH_AREA_SIZE parameter, 24-8

HASH_MULTIBLOCK_IO_COUNT parameter, 24-8

index join, 23-36, 23-49

HASH_AJ hint, 24-13

HASH_AREA_SIZE parameter, 24-8

HASH_JOIN_ENABLED parameter, 24-7

HASH_MULTIBLOCK_IO_COUNT parameter, 24-8

HASH_SJ hint, 24-13

HASHKEYS parameter, 10-53, 10-57

headers

of data blocks, 4-4

of row pieces, 10-5

heterogeneous distributed databases, 33-8

Heterogeneous Services, 33-8

agents, 33-9

HEXTORAW function, 12-23

HI_SHARED_MEMORY_ADDRESS parameter, 7-13

hierarchies, 1-29, 10-18

join key, 1-29, 10-19

levels, 1-29, 10-18

high water mark

direct-load INSERT, 25-3

HIGH_VALUE statistics, 23-52

hints

cannot override sample access path, 23-50

extensible optimization, 22-16

INDEX, 24-15

INDEX_FFS, 23-36

INDEX_JOIN, 23-36

MERGE, 23-17

MERGE_AJ and HASH_AJ, 24-13

MERGE_SJ and HASH_SJ, 24-13

ORDERED, 24-9, 24-15

overriding optimizer choice, 23-50

overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 23-33

PARALLEL, 26-17

PARALLEL_INDEX, 26-17

PUSH_JOIN_PRED, 24-12

STAR, 24-15

USE_HASH, 24-7

histograms, 22-8

historical database

maintenance operations, 11-48

partitions, 11-6

HP's OpenView, 33-20

hybrid configurations

advanced replication, 34-13

I

IBM's NetView/6000, 33-20

ILMS, 16-20

immediate constraints, 28-20

IMP_FULL_DATABASE role, 30-22

implicit dereferencing, 13-10

Import utility, 1-6

copying statistics, 22-8

partition maintenance operations, 11-48

use in recovery, 32-25

user-defined types, 14-20

in basic replication, 34-12

IN operator, 23-5

merging views, 23-18

IN subquery, 23-17

incomplete object types, 14-17

incremental checkpoint, 8-8

incremental refresh, 10-18

index joins, 23-36, 23-49

index segments, 1-11, 4-17

INDEX_FFS hint, 23-36

INDEX_JOIN hint, 23-36

indexes, 1-26, 10-21

auditing partitions, 11-63

B*-tree structure of, 10-27

bitmap indexes, 10-32, 10-36

nulls and, 10-8

parallel query and DML, 10-33

branch blocks, 10-28

building

using an existing index, 10-22

cardinality, 10-33

cluster, 10-50

cannot be partitioned, 11-2

contrasted with table, 10-50

dropping, 10-50

scans of, 23-41

composite, 10-22

scans of, 23-42

concatenated, 10-22

described, 1-26, 10-21

domain, 10-42

domain indexes

extensible optimization, 22-16

user-defined statistics, 22-17

enforcing integrity constraints, 28-10, 28-12

extensible, 10-42

fast full scans of, 23-36

function-based, 10-24

dependencies, 10-26, 21-7, 21-9

DETERMINISTIC functions, 21-8

DISABLED, 21-8

optimization with, 10-25

privileges, 10-26, 21-8

global partitioned indexes, 11-32

managing partitions, 11-33, 11-60

index joins, 23-36, 23-49

index unusable (IU), 11-61

index-organized tables, 10-36

logical rowids, 10-39, 12-20

secondary indexes, 10-39

internal structure of, 10-27

key compression, 10-29

indexes (continued),

keys and, 10-23

primary key constraints, 28-12

unique key constraints, 28-10

leaf blocks, 10-28

local indexes, 11-29, 11-59

building partitions in parallel, 11-30

location of, 10-26

LONG RAW datatypes prohibit, 12-15

managing partitions, 11-59

no-logging mode, 25-7

nonunique, 10-22

nulls and, 10-8, 10-24, 10-35

on attribute of object column, 14-6

on complex data types, 10-42

on REFs, 14-6

optimization and, 23-10

overview of, 1-26, 10-21

parallel DDL storage, 26-33

parallel index scans, 26-5

partition pruning, 11-4

partitioned tables, 10-36

partitioning guidelines, 11-36

partitions, 11-2, 11-29

performance and, 10-22

privileges for partitions, 11-62

range scans, 23-35

rebuild partition, 11-60

rebuilt after direct-load INSERT, 25-8

reverse key indexes, 10-31

rowids and, 10-28

scans of, 23-35

bounded range, 23-44

cluster key, 23-41

composite, 23-42

MAX or MIN, 23-46

ORDER BY, 23-47

restrictions, 23-48

single-column, 23-42

unbounded range, 23-45

statement conversion and, 23-10

storage format of, 10-26

unique, 10-22

unique scans, 23-35

user-defined types, 14-6

when used with views, 10-14

index-organized tables, 10-36

applications, 10-40

benefits, 10-38

key compression in, 10-30, 10-38

logical rowids, 10-39, 12-20

parallel CREATE, 26-31

parallel queries, 26-29

partition of, 11-42

partitioned secondary indexes on, 11-45

queue tables, 19-12

rebuild of, 10-39

row overflow area, 10-38

secondary indexes on, 10-39

indextype, 10-43

in-doubt transactions, 4-24, 5-8

information

distribution

in basic replication, 34-11

off-loading

in basic replication, 34-11

transport, 34-12

information distribution

basic replication for, 34-11

information retrieval (IR) applications

index-organized tables, 10-40

initialization parameters

ALWAYS_ANTI_JOIN, 24-13

ALWAYS_SEMI_JOIN, 24-13

AQ_TM_PROCESS, 19-6, 19-7

BUFFER_POOL_KEEP, 7-5

BUFFER_POOL_RECYCLE, 7-5

COMPATIBLE, 3-11

DB_BLOCK_BUFFERS, 7-5, 7-13

DB_BLOCK_LRU_LATCHES, 8-8

DB_BLOCK_SIZE, 7-5, 7-13

DB_FILE_MULTIBLOCK_READ_COUNT, 23-51, 24-9

DB_FILES, 7-15

DB_NAME, 32-22

DB_WRITER_PROCESSES, 1-18, 8-8

DISTRIBUTED_TRANSACTIONS, 8-12

FAST_START_IO_TARGET, 32-13

HASH_AREA_SIZE, 24-8

HASH_JOIN_ENABLED, 24-7

HASH_MULTIBLOCK_IO_COUNT, 24-8

HI_SHARED_MEMORY_ADDRESS, 7-13

JOB_QUEUE_PROCESSES, 19-10

LICENSE_MAX_SESSIONS, 29-20

LICENSE_SESSIONS_WARNING, 29-20

LOCK_SGA, 7-13, 7-17

LOG_ARCHIVE_MAX_PROCESSES, 1-19, 8-12, 32-19

LOG_ARCHIVE_START, 32-19

LOG_BUFFER, 7-6, 7-13

LOG_CHECKPOINT_INTERVAL, 32-13

LOG_CHECKPOINT_TIMEOUT, 32-13

MTS_MAX_SERVERS, 8-19, 8-20

MTS_SERVERS, 8-19

NLS_LANGUAGE, 11-20

NLS_NUMERIC_CHARACTERS, 12-9

NLS_SORT, 11-20

OPEN_CURSORS, 7-9, 16-7

OPEN_LINKS, 7-15

OPTIMIZER_FEATURES_ENABLE, 23-17, 23-36, 24-12

OPTIMIZER_MODE, 23-31

OPTIMIZER_PERCENT_PARALLEL, 22-8

PARALLEL_MAX_SERVERS, 26-8

PARALLEL_MIN_PERCENT, 26-18

PARALLEL_MIN_SERVERS, 26-7, 26-8

PARALLEL_SERVER, 5-6

REMOTE_DEPENDENCIES_MODE, 21-11

ROLLBACK_SEGMENTS, 4-27

SERVICE_NAMES, 6-6

SHARED_MEMORY_ADDRESS, 7-13

SHARED_POOL_SIZE, 7-6, 7-13

SKIP_UNUSABLE_INDEXES, 21-8

SORT_AREA_RETAINED_SIZE, 7-16

SORT_AREA_SIZE, 4-18, 7-16, 24-9

SQL_TRACE, 8-15

STAR_TRANSFORMATION_ENABLED, 24-19

TRANSACTIONS, 4-27

TRANSACTIONS_PER_ROLLBACK_SEGMENT, 4-27

USE_INDIRECT_DATA_BUFFERS, 7-14

initially deferred constraints, 28-21

initially immediate constraints, 28-21

INIT.ORA files, 5-4, 5-5

inline views, 10-16

example, 10-16

inner capture, 14-8

INSERT command, 16-3

direct-load INSERT, 25-2

no-logging mode, 11-58, 25-5, 25-7

free lists and, 4-9

parallelizing INSERT ... SELECT, 26-23

storage for parallel INSERT, 25-8

triggers and, 20-2, 20-6

BEFORE triggers, 20-9

INSERT privilege for object tables, 14-15, 14-16

instance groups for parallel operations, 26-19

instance recovery, 32-4

instance failure, 1-46, 32-4

read-only tablespaces, 32-6

SMON process, 1-19, 8-11, 26-41

See also crash recovery

instances, 1-6

aborting, 5-10, 32-4

acquire rollback segments, 4-26

associating with databases, 5-2, 5-6

defined, 1-16

described, 5-2

diagrammed, 8-6

failure in, 1-46, 32-4

instance groups, 26-19

memory structures of, 7-2

multiple-process, 8-2

overview of, 1-6

process structure, 8-2

recovery of, 5-10, 32-4

Fast-Start Checkpointing, 32-13

opening a database, 5-8

SMON process, 8-11

restricted mode, 5-6

service names, 6-6

sharing databases, 1-8

shutting down, 5-9, 5-10

audit record, 31-5

starting, 5-5

audit record, 31-5

system identifiers (SIDs), 6-6

virtual memory, 7-17

INSTEAD OF triggers, 20-12

nested tables, 15-5

object views, 15-5

integrity constraints, 28-2

default column values and, 10-8

See also constraints

integrity rules, 1-22

parallel DML restrictions, 26-45

Inter-Language Method Services (ILMS), 16-20

INTERNAL connection, 5-3

statement execution not audited, 31-5

internal errors tracked in trace files, 8-14

inter-operator parallelism, 26-13

INTERSECT operator

compound queries, 23-4

example, 23-29

optimizing view queries, 23-16

intra-operator parallelism, 26-13

INVALID status, 21-2

invoker rights, 18-9

name resolution, 18-19

procedure security, 30-8

supplied packages, 30-8

IS NULL predicate, 10-8

ISO SQL standard, 1-3, 12-22

composite foreign keys, 28-16

isolation levels

choosing, 27-12

read committed, 27-8

setting, 27-7, 27-31

J

Java

triggers, 20-1, 20-7

job queue processes (SNPn), 1-20, 8-13

message propagation, 19-10

JOB_QUEUE_PROCESSES parameter, 19-10

jobs, 8-2

join views, 10-15

joins

anti-joins, 24-13

Cartesian products, 23-3

cluster, 10-48, 23-38, 24-5

searches on, 23-40

convert to subqueries, 23-13

cross, 23-3

defined, 23-3

encapsulated in views, 1-24, 10-13

equijoins, 23-3

execution plans and, 24-2

hash joins, 24-7

index joins, 23-36, 23-49

join order

execution plans, 22-2

selectivity of predicates, 22-8, 22-17

nested loops, 24-2

cost-based optimization, 24-8

nonequijoins, 23-3

optimization of, 24-9

outer, 23-3

non-null values for nulls, 24-11

partition-wise, 11-5

sample table scan not supported, 23-34

select-project-join views, 23-15

semi-joins, 24-13

sort-merge, 24-4

cost-based optimization, 24-9

example, 23-46

star joins, 24-14

star queries, 24-14

views, 1-25, 10-15

K

key compression, 10-29

keys

cluster, 1-27, 10-48

defined, 28-9

foreign, 28-13

hash, 10-53, 10-57

in constraints, 1-58

indexes and, 10-23

compression, 10-29

PRIMARY KEY constraints, 28-12

reverse key, 10-31

UNIQUE constraints, 28-10

key values, 1-59

maximum storage for values, 10-23

parent, 28-13, 28-15

primary, 28-11

referenced, 1-59, 28-13

reverse key indexes, 10-31

searches, 23-39

unique, 28-8

composite, 28-9, 28-11

L

large pool, 7-12

overview of, 1-16

latches

described, 27-30

LRU, 8-8

LCK0 background process, 1-20, 8-13

leaf blocks, 10-28

leaf-level attributes, 14-18

leaf-level scalar attributes, 14-18

least recently used (LRU) algorithm

database buffers and, 7-3

dictionary cache, 2-4

full table scans and, 7-4

latches, 8-8

shared SQL pool, 7-8, 7-10

LGWR background process, 8-9

See also log writer process

library cache, 7-6, 7-7, 7-10

LICENSE_MAX_SESSIONS parameter, 29-20

LICENSE_SESSIONS_WARNING parameter, 29-20

licensing

concurrent usage, 29-20

named user, 29-21

viewing current limits, 29-20

LIKE, 23-5

links, 33-6

listener processes, 6-6, 8-14

service names, 6-6

LISTENER.ORA file, 6-6

literal invocation

constructor methods, 14-4

LOB datatypes, 12-12

BFILE, 12-14

BLOBs, 12-13

CLOBs and NCLOBs, 12-13

default logging mode, 25-7

NOLOGGING mode, 25-7

restrictions

parallel DDL, 26-31

parallel DML, 26-44

local databases, 1-35

local indexes, 11-29, 11-34

bitmap indexes

on partitioned tables, 10-36

parallel query and DML, 10-33

building partitions in parallel, 11-30

equipartitioning, 11-29

managing partitions, 11-59

locally-managed tablespaces, 3-8

temporary tablespaces, 3-13

location transparency, 1-35

lock process (LCK0), 1-20, 8-13

LOCK TABLE command, 16-4

LOCK_SGA parameter, 7-13, 7-17

locks, 1-32, 27-3

after committing transactions, 17-6

automatic, 1-32, 27-15, 27-19

conversion, 27-16

data, 27-20

duration of, 27-15

deadlocks, 27-17, 27-18

avoiding, 27-19

dictionary, 27-28

clusters and, 27-29

duration of, 27-29

dictionary cache, 27-30

DML acquired, 27-27

diagrammed, 27-25

DML partition locks, 11-45

escalation does not occur, 27-17

exclusive table locks (X), 27-25

file management locks, 27-30

how Oracle uses, 27-15

internal, 27-29

latches and, 27-30

log management locks, 27-30

manual, 1-33, 27-31

examples of behavior, 27-32

object level locking, 13-15

Oracle Lock Management Services, 27-39

overview of, 1-32, 27-3

parallel cache management (PCM), 27-20

parallel DML, 26-42

parse, 16-11, 27-29

rollback segment, 27-30

row (TX), 27-20

block-level recovery, 32-14

row exclusive locks (RX), 27-23

row share table locks (RS), 27-23

share row exclusive locks (SRX), 27-25

share table locks (S), 27-24

share-subexclusive locks (SSX), 27-25

subexclusive table locks (SX), 27-23

subshare table locks (SS), 27-23

table (TM), 27-21

table lock modes, 27-22

tablespace, 27-30

types of, 27-19

log entries, 1-12, 32-9

See also redo log files, 1-12

log management locks, 27-30

log sequence numbers, 1-48

log switch

ALTER SYSTEM SWITCH LOGFILE, 8-12

archiver process, 1-19, 8-12

log writer process (LGWR), 1-19, 8-9

archiving modes, 32-18

group commits, 8-10

manual archiving and, 32-20

redo log buffers and, 7-6

starting new ARCn processes, 8-12

system change numbers, 17-6

write-ahead, 8-9

LOG_ARCHIVE_MAX_PROCESSES parameter, 1-19, 8-12

automatic archiving, 32-19

LOG_ARCHIVE_START parameter, 32-19

LOG_BUFFER parameter, 7-6

system global area size and, 7-13

LOG_CHECKPOINT_INTERVAL parameter, 32-13

LOG_CHECKPOINT_TIMEOUT parameter, 32-13

logging mode

direct-load INSERT, 25-5

NOARCHIVELOG mode and, 25-5

parallel DDL, 26-31, 26-32

partitions, 11-58

SQL operations affected by, 25-7

logical blocks, 4-2

logical database structures, 1-5, 1-9

tablespaces, 3-6

logical reads limit, 29-17

logical rowids, 12-20

index on index-organized table, 10-39

physical guesses, 10-39, 12-20

staleness of guesses, 12-21

statistics for guesses, 12-21

LONG datatype

automatically the last column, 10-7

defined, 12-7

partitioning restriction, 11-14

storage of, 10-7

LONG RAW datatype, 12-14

indexing prohibited on, 12-15

partitioning restriction, 11-14

similarity to LONG datatype, 12-15

lookup tables

star queries, 24-14

LOW_VALUE statistics, 23-52

LRU, 7-3, 7-4, 8-8

dictionary cache, 2-4

latches, 8-8

shared SQL pool, 7-8, 7-10

M

manual locking, 1-33, 27-31

map methods, 1-57, 13-7

massively parallel processing (MPP)

affinity, 26-6, 26-48, 26-49

multiple Oracle instances, 5-3

parallel SQL execution, 26-2

master definition sites, 34-5

master groups, 34-5

master sites, 34-5

matching foreign keys

full, partial, or none, 28-16

materialized view logs, 10-18

materialized views, 10-17

deallocating extents, 4-15

materialized view logs, 10-18

overview, 1-25

partitioned, 10-18, 11-2

refreshing, 10-18

same as snapshots, 1-25, 34-3

MAXEXTENTS UNLIMITED storage parameter, 26-40

MAXVALUE

partitioned tables and indexes, 11-20

media failure, 1-46, 32-5

memory

allocation for SQL statements, 7-11

content of, 7-2

cursors (statement handles), 1-17

extended buffer cache (32-bit), 7-14

overview of structures in, 1-14

processes use of, 8-2

shared SQL areas, 7-8

software code areas, 7-17

sort areas, 7-16

stored procedures, 18-8, 18-17

structures in, 7-2

system global area (SGA)

allocation in, 7-2

initialization parameters, 7-12, 7-13

locking into physical memory, 7-13, 7-17

SGA size, 7-12

starting address, 7-13

virtual, 7-17

See also system global area

MERGE hint, 23-17

MERGE_AJ hint, 24-13

MERGE_SJ hint, 24-13

merging complex views, 23-17

merging partitions, 11-16

merging views into statements, 23-15

message queuing, 19-2

exporting queue tables, 19-12

messages, 19-4

publish/subscribe support, 19-10

event publication, 20-18

queue monitor process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4

recipients, 19-5

rule-based subscriptions, 19-5, 19-6

subscription lists, 19-5

remote databases, 19-9

methods

comparison methods, 13-6

constructor methods, 13-6

literal invocation, 14-4

privileges on, 30-11

methods of collections

constructor methods, 1-57

methods of object types, 1-57, 13-4

constructor methods, 1-57, 14-18

execution privilege for, 14-13

map methods, 1-57, 13-7

order methods, 1-57, 13-7

PL/SQL, 13-14

purchase order example, 13-2, 13-5

selfish style of invocation, 13-6

use of empty parentheses with, 14-9

MINIMUM EXTENT

parallel DML, 25-9, 25-10

MINIMUM EXTENT parameter, 26-33

MINUS operator

compound queries, 23-4

optimizing view queries, 23-16

mobile computing environment

materialized views, 10-17

modes

archive log, 32-18

table lock, 27-22

two-task, 8-3

MODIFY CONSTRAINT option, 28-23

monitoring user actions, 1-44, 31-2

MOVE PARTITION command

no-logging mode, 11-58, 25-7

rules of parallelism, 26-25

MPP

See massively parallel processing

MTS_MAX_SERVERS parameter, 8-19

artificial deadlocks and, 8-20

MTS_SERVERS parameter, 8-19

multiblock writes, 8-8

multimaster replication, 34-6

multimedia datatypes, 13-3

multiple-process systems (multiuser systems), 8-2

multiplexing

control files, 1-49, 32-22

recovery and, 32-5

redo log files, 1-48

multi-threaded server, 8-16

artificial deadlocks in, 8-19

dedicated server contrasted with, 8-16

described, 8-3, 8-16

dispatcher processes, 1-20, 8-14

example of use, 8-20

limiting private SQL areas, 29-18

Net8 or SQL*Net V2 requirement, 8-14, 8-16

parallel SQL execution, 26-8

private SQL areas, 7-9

sort areas, 7-16

processes needed for, 8-16

restricted operations in, 8-20

server processes, 1-18, 8-14, 8-19

session information, 7-14

session memory in the large pool, 7-12

shared server processes, 8-14, 8-19

multiuser environments, 1-2, 8-2

multiverison consistency model, 1-31

multiversion concurrency control, 27-5

mutating errors and triggers, 20-22

N

name resolution for procedures, 18-19

name resolution in distributed databases, 33-6

named user licensing, 29-21

National Language Support (NLS)

character sets for, 12-6

CHECK constraints and, 28-18

clients and servers may diverge, 33-20

DATE datatype and partitions, 11-14, 11-21

NCHAR and NVARCHAR2 datatypes, 12-6

NCLOB datatype, 12-13

parameters, 5-5

views and, 10-14

NCHAR datatype, 12-6

NCLOB datatype, 12-13

nested loops joins, 24-2

cost-based optimization, 24-8

nested tables, 10-9, 13-12

indexes, 14-6

index-organized tables, 10-38

key compression, 10-30

INSTEAD OF triggers, 15-5

restrictions, 26-30

updating in views, 15-5

Net8, 1-7, 1-37, 6-5, 33-4

Advanced Security option, 33-18

applications and, 6-5

client/server systems use of, 6-5

multi-threaded server requirement, 8-14, 8-16

overview of, 6-5

network listener process, 6-6

connection requests, 8-14, 8-16

dedicated server example, 8-24

multi-threaded server example, 8-20

service names, 6-6

networks

client/server architecture use of, 6-2

communication protocols, 6-5, 8-26

dispatcher processes and, 8-14, 8-16

distributed databases, 33-4

distributed databases use of', 33-2

drivers, 8-26

failures of, 32-3

listener processes of, 6-6, 8-14

Net8, 6-5, 33-4

network authentication service, 29-4

Oracle Names, 33-4

two-task mode and, 8-23

using Oracle on, 1-7, 1-37

NEXT storage parameter

parallel direct-load INSERT, 25-9

calculating value, 25-9

NLS

See National Language Support

NLS_DATE_FORMAT parameter, 12-10

NLS_LANG environment variable, 11-20

NLS_LANGUAGE parameter, 11-20

NLS_NUMERIC_CHARACTERS parameter, 12-9

NLS_SORT parameter

no effect on partitioning keys, 11-20

ORDER BY access path, 23-47

NOARCHIVELOG mode, 32-18

database backups for recovery, 32-24

defined, 32-18

LOGGING mode and, 25-5

overview, 1-48

NOAUDIT command, 16-4

locks, 27-29

nodes

disk affinity in a Parallel Server, 26-48

of distributed databases, 1-35

NOLOGGING mode

direct-load INSERT, 25-5

parallel DDL, 26-31, 26-32

partitions, 11-58

SQL operations affected by, 25-7

nonequijoins

defined, 23-3

non-persistent queues, 19-10

nonprefixed indexes, 11-31, 11-35

global partitioned indexes, 11-33

nonrepeatable reads, 27-3, 27-10

nonunique indexes, 10-22

NOREVERSE option for indexes, 10-31

normalized tables, 1-29, 10-19

star schemas, 24-15

NOT, 23-7

NOT IN subquery, 24-13

NOT NULL constraints

constraint checking, 28-20

defined, 28-7

implied by PRIMARY KEY, 28-12

UNIQUE keys and, 28-11

NOVALIDATE constraints, 28-22

Novell's NetWare Management System, 33-20

nulls

as default values, 10-8

atomic, 14-3

column order and, 10-7

converting to values, 10-8

optimization, 24-11

defined, 10-7

foreign keys and, 28-15, 28-16

how stored, 10-7

indexes and, 10-8, 10-24, 10-35

inequality in UNIQUE key, 28-11

non-null values for, 10-8, 24-11

object types, 14-3

partitioned tables and indexes, 11-21

prohibited in primary keys, 28-11

prohibiting, 28-7

UNIQUE key constraints and, 28-11

unknown in comparisons, 10-8

NUM_DISTINCT column

USER_TAB_COLUMNS view, 23-52

NUM_ROWS column

USER_TABLES view, 23-52

NUMBER datatype, 12-8

internal format of, 12-9

rounding, 12-9

NVARCHAR2 datatype, 12-6

NVL function, 10-8

O

object cache

object views, 15-4

OCI, 13-14

privileges, 14-16

Pro*C, 13-14

object identifiers, 15-3

for object types, 14-18

for object views, 15-3, 15-4

WITH OBJECT OID clause, 15-3, 15-4

object privileges, 30-3

See also schema object privileges

object tables, 13-3, 13-7

constraints, 14-5

indexes, 14-6

row objects, 13-8

triggers, 14-6

virtual object tables, 15-2

object types, 1-22, 13-2, 13-4

attributes of, 13-2, 13-4

column objects, 13-8

indexes, 14-6

comparison methods for, 13-6

constructor methods for, 1-57, 13-6, 14-18

incomplete, 14-17

locking in cache, 13-15

message queuing, 19-6

methods of, 1-57, 13-4

method calls, 14-9

PL/SQL, 13-14

purchase order example, 13-2, 13-5

mutually dependent, 14-16

object views, 10-16

Oracle type translator, 13-15

parallel query, 26-29

restrictions, 26-30

purchase order example, 13-2, 13-4

restrictions

parallel DDL, 26-31

parallel DML, 26-44

row objects, 13-8

use of table aliases, 14-8

object views, 10-16, 15-1

advantages of, 15-2

defining, 15-3

modifiability, 20-12

nested tables, 15-5

object identifiers for, 15-3, 15-4

updating, 15-5

use of INSTEAD OF triggers with, 15-5

object-relational DBMS (ORDBMS), 1-22, 13-2

objects

privileges on, 30-11

objects in a database schema, 1-5

See also schema objects

OCI, 8-25

anonymous blocks, 16-17

bind variables, 16-13

object cache, 13-14

OCIObjectFlush, 15-4

OCIObjectPin, 15-4

stored procedures, 16-18

ODCIIndex, 10-43

offline backups

whole database backup, 32-23

offline redo log files, 1-48, 32-7

OIDs, 14-18, 15-3, 15-4

collections

key compression, 10-30, 10-38

WITH OBJECT OID clause, 15-3, 15-4

OLTP database, 11-5

batch jobs, 26-38

parallel DML, 26-37

partitioning indexes, 11-36

partitions, 11-6

online analytical processing (OLAP)

index-organized tables, 10-42

online redo log, 1-47, 32-7

archiving, 32-18, 32-19

checkpoints, 32-22

media failure, 32-5

multiplexed, 32-5

recorded in control file, 32-21

online transaction processing (OLTP), 11-5

reverse key indexes, 10-31

OPEN_CURSORS parameter, 16-7

managing private SQL areas, 7-9

OPEN_LINKS parameter, 7-15

operating systems

authentication by, 29-4

block size, 4-3

communications software, 8-26

privileges for administrator, 5-3

roles and, 30-22

operations in a relational database, 1-22

OPTIMAL storage parameter, 4-25

optimization, 22-2

choosing the approach, 23-31

conversion of expressions and predicates, 23-4

cost-based, 22-7, 24-8

choosing an access path, 23-50

examples of, 23-51

histograms, 22-8

remote databases and, 23-30

star queries, 24-14

user-defined costs, 22-17

described, 22-2

DISTINCT, 23-17

distributed SQL statements, 23-30

execution plan for partitions, 11-11, 11-14

extensible optimizer, 22-16

function-based indexes, 10-25

GROUP BY views, 23-17

hints, 23-33, 23-36

index build, 10-22

manual, 23-33

merging complex views, 23-17

merging views into statements, 23-15

non-null values for nulls, 24-11

operations performed, 23-2

parallel SQL, 26-10

partition pruning, 11-4

indexes, 11-36

partition pruning (elimination), 11-4

partitioned indexes, 11-35

partition-wise joins, 11-5

plan stability, 22-6

PL/SQL, 23-33

query rewrite, 10-17

in security policies, 30-23

rule-based, 22-18, 24-9

choosing an access path, 23-54

examples of, 23-54

selectivity of predicates, 22-8

histograms, 22-8, 22-10

user-defined, 22-17

selectivity of queries and, 23-51

select-project-join views, 23-15

semi-joins, 24-13

statistics, 22-8, 23-32

user-defined, 22-17

transitivity and, 23-8

types of SQL statements, 23-3

without merging, 23-25

OPTIMIZER_FEATURES_ENABLE parameter, 23-17, 23-36, 24-12

OPTIMIZER_GOAL option, 23-32

OPTIMIZER_MODE, 23-31

hints affecting, 23-33

OPTIMIZER_PERCENT_PARALLEL parameter, 22-8

Oracle

adherence to standards, 1-3

integrity constraints, 28-5

architecture, 1-8, 1-14

client/server architecture of, 6-2

compatibility, 1-3

compatibility levels, 3-14

configurations of, 8-2

multiple-process Oracle, 8-2

connectibility, 1-2

different Oracle versions, 33-7

data access, 1-51

examples of operations, 1-21

dedicated server, 8-24

multi-threaded server, 8-20

features, 1-2

instances, 1-6, 1-16, 5-2

licensing of, 29-19

Oracle server, 1-4

Parallel Server option, 1-8

See also Parallel Server

portability, 1-3

processes of, 1-17, 8-5

scalability of, 6-4

SQL processing, 16-8

using on networks, 1-2, 1-37

Oracle AQ, 19-1

exporting queue tables, 19-12

message queuing, 19-2

publish/subscribe support, 19-10

event publication, 20-18

queue monitor process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4

recipients, 19-5

rule-based subscriptions, 19-5, 19-6

subscription lists, 19-5

remote databases, 19-9

Oracle blocks, 1-10, 4-2

See also data blocks

Oracle Call Interface (OCI), 8-25

anonymous blocks, 16-17

bind variables, 16-13

object cache, 13-14

OCIObjectFlush, 15-4

OCIObjectPin, 15-4

stored procedures, 16-18

Oracle Certificate Authority, 29-5

Oracle code, 8-2, 8-25

Oracle Data Cartridge Interface, 10-43

Oracle Enterprise Manager

See Enterprise Manager

Oracle Forms

object dependencies and, 21-13

PL/SQL, 16-16

Oracle Names

global directory service, 33-4

Oracle Open Gateways, 33-8

Oracle Parallel Server, 1-8

See also Parallel Server

Oracle precompilers

anonymous blocks, 16-17

bind variables, 16-13

cursors, 16-11

embedded SQL, 16-5

FIPS flagger, 16-6

stored procedures, 16-18

Oracle program interface (OPI), 8-25

Oracle Replication Manager, 34-15

Oracle Security Manager, 29-5, 33-18

Oracle server, 1-4

See also Oracle

Oracle type translator (OTT), 13-15

Oracle Wallet Manager, 29-5

Oracle wallets, 29-5

Oracle XA

session memory in the large pool, 7-12

Oracle Internet Directory, 29-5

ORDBMS, 1-22, 13-2

order methods, 1-57, 13-7

ORDERED hint, 24-9

OTT, 13-15

outer joins

defined, 23-3

non-null values for nulls, 24-11

OUTLN schema

DBA privileges, 22-6

P

P code, 18-18

packages, 18-4, 18-11

advantages of, 18-15

as program units, 1-56

auditing, 31-8

dynamic SQL, 16-19

examples of, 18-12, 30-9, 30-10

executing, 16-16, 18-18

for locking, 27-39

OUTLN_PKG, 22-6

overview of, 1-26

private, 18-15

privileges

divided by construct, 30-9

executing, 30-7, 30-9

public, 18-15

queuing, 19-4

session state and, 21-6

shared SQL areas and, 7-10

storing, 18-17

supplied packages, 18-16

invoker or definer rights, 30-8

validity of, 18-18

pages, 4-2

parallel backup operations, 32-16

PARALLEL clause

parallelization rules, 26-20

parallel DDL, 26-30

extent allocation, 26-33

functions, 26-46

parallelism types, 26-3

parallelization rules, 26-20

partitioned tables and indexes, 26-31

building local indexes, 11-30

restrictions

LOBs, 26-31

object types, 26-30, 26-31

parallel DELETE, 26-21

parallel DML, 26-35

applications, 26-37

bitmap indexes, 10-33

degree of parallelism, 26-20, 26-23

enabling PARALLEL DML, 26-38

functions, 26-46

lock and enqueue resources, 26-42

parallelism types, 26-3

parallelization rules, 26-20

recovery, 26-40

restrictions, 26-43

object types, 26-30, 26-44

remote transactions, 26-46

rollback segments, 26-40

transaction model, 26-39

parallel execution, 26-2

coordinator, 25-3, 26-6

full table scans, 26-5

inter-operator parallelism, 26-13

intra-operator parallelism, 26-13

partitioned tables and indexes, 26-4

server, 25-3, 26-6

index maintenance, 25-8

NEXT extent size, 25-9

temporary segments, 25-8

See also parallel SQL

parallel execution coordinator, 26-6

direct-load INSERT, 25-3

parallel execution servers, 26-6

direct-load INSERT, 25-3

index maintenance, 25-8

NEXT extent size, 25-9

temporary segments, 25-8

PARALLEL hint, 26-17

parallelization rules, 26-20

UPDATE and DELETE, 26-21

parallel query, 26-28

bitmap indexes, 10-33

functions, 26-46

index-organized tables, 26-29

object types, 26-29

restrictions, 26-30

parallelization rules, 26-20

parallel recovery, 32-10, 32-16

Parallel Server, 1-8

concurrency limits and, 29-21

databases and instances, 5-3

disk affinity, 26-48

distributed locks, 27-20

DML locks and performance, 11-47

exclusive mode

rollback segments and, 4-27

file and log management locks, 27-30

instance groups, 26-19

isolation levels, 27-11

lock processes, 1-20, 8-13

mounting a database using, 5-6

named user licensing and, 29-21

parallel SQL, 26-1

PCM locks, 27-20

read consistency, 27-6

reverse key indexes, 10-31

shared mode

rollback segments and, 4-27

system change numbers, 8-10

system monitor process and, 8-11, 26-41

temporary tablespaces, 3-12

PARALLEL SERVER parameter, 5-6

parallel SQL, 26-2

allocating rows to parallel execution servers, 26-11

coordinator process, 26-6

direct-load INSERT, 25-3

degree of parallelism, 26-16

instance groups, 26-19

multi-threaded server, 26-8

number of parallel execution servers, 26-7

operations in execution plan, 26-10

optimizer, 26-10

Parallel Server and, 26-1

parallelization rules, 26-20

server processes, 26-6

direct-load INSERT, 25-3, 25-8

NEXT extent size, 25-9

summary or rollup tables, 26-31

See also parallel execution

parallel UPDATE, 26-21

PARALLEL_INDEX hint, 26-17

PARALLEL_MAX_SERVERS parameter, 26-8

PARALLEL_MIN_PERCENT parameter, 26-18

PARALLEL_MIN_SERVERS parameter, 26-7, 26-8

parameter files, 5-4

example of, 5-4

used at startup, 5-5

parameters

initialization, 5-4

locking behavior, 27-19

See also initialization parameters

National Language Support, 5-5

storage, 4-5, 4-11

parentheses, use of in method calls, 14-9

parse trees, 18-17

construction of, 16-7

in shared SQL area, 7-8

stored in database, 18-18

parsing, 16-11

DBMS_SQL package, 16-19

embedded SQL, 16-6

parse calls, 16-8

parse locks, 16-11, 27-29

performed, 16-8

SQL statements, 16-11, 16-19

partial backups, 32-24

partition elimination, 11-4

PARTITION option, 11-63

partition views, 11-11

PARTITION_VIEW_ENABLED parameter, 11-12

partitioning

LOBs

DML locks, 11-46

maintenance operations, 11-57

tables with LOB columns, 11-38

partitioning columns, 11-15

partitioning keys, 11-15, 11-19

multi-column keys, 11-22

partitions, 11-2, 11-13

advantages of, 11-5, 11-7

affinity, 26-48

basic partitioning model, 11-13

bitmap indexes, 10-36

concurrent maintenance operations, 11-50

DATE datatype, 11-14, 11-21

DML partition locks, 11-45

dynamic partitioning, 26-6

equipartitioning, 11-24

examples, 11-25, 11-30, 11-32

LOB columns, 11-38

local indexes, 11-29

on one dimension, 11-24

overflow of index-organized tables, 11-42, 11-44

range partitioning, 11-24

EXCHANGE PARTITION, 11-11

execution plan, 11-11, 11-14

global indexes, 11-32, 11-60

hash partitioning, 11-16

local indexes, 11-29, 11-59

building in parallel, 11-30

LONG and LONG RAW restriction, 11-14

maintenance operations, 11-48

materialized views, 10-18, 11-2

merging, 11-16

no-logging mode, 25-7

nonprefixed indexes, 11-31, 11-35

OLTP databases, 11-6

parallel DDL, 26-31

parallel queries, 26-4

partition bounds, 11-20

partition elimination, 11-4

partition names, 11-18

partition pruning, 11-4

DATE datatype, 11-22

disk striping and, 26-48

indexes, 11-36

parallelizing by block range, 26-4

partition transparency, 11-10

partition-extended table names, 11-63

partitioning indexes, 11-29, 11-36

partitioning keys, 11-15, 11-19

partitioning tables, 11-27

partition-wise joins, 11-5

physical attributes, 11-27, 11-37

prefixed indexes, 11-30

range partitioning, 11-15

disk striping and, 26-48

rebuild partition, 11-60

referencing a partition, 11-19

restrictions

bitmap indexes, 11-14

datatypes, 11-14, 11-21

partition-extended table names, 11-64

rules of parallelism, 26-25, 26-27

segments, 4-17

statistics, 11-14, 22-11

VLDB, 11-5

partition-wise joins, 11-5

passwords

account locking, 29-7

administrator privileges, 5-3

complexity verification, 29-8

connecting with, 8-4

connecting without, 29-4

database user authentication, 29-7

encryption, 29-7

expiration, 29-8

password files, 29-13

password reuse, 29-8

used in roles, 1-42

PCTFREE storage parameter

how it works, 4-6

PCTUSED and, 4-8

PCTINCREASE storage parameter

parallel DML, 25-9, 25-10

PCTUSED storage parameter

how it works, 4-6

PCTFREE and, 4-8

performance

clusters and, 10-48

constraint effects on, 28-6

DSS database, 11-9, 26-37

dynamic performance tables (V$), 2-7

group commits, 8-10

index build, 10-22

I/O, 11-9

Oracle Parallel Server and DML locks, 11-47

packages, 18-16

parallel recovery and, 32-11

partitions, 11-9

prefixed and nonprefixed indexes, 11-35

recovery, 32-13

resource limits and, 29-16

SGA size and, 7-12

sort operations, 3-12

structures that improve, 1-26, 1-27

viewing execution plans, 22-4

persistent areas, 7-8

persistent queuing, 19-2

PGA, 1-17, 7-14

multi-threaded server, 8-19

phantom reads, 27-3, 27-10

physical database structures, 1-5, 1-11

control files, 1-13, 32-21

datafiles, 1-12, 3-16

redo log files, 1-12, 32-7

physical guesses in logical rowids, 12-20

staleness, 12-21

statistics for, 12-21

PKI, 29-5

plan

accessing views, 23-19, 23-22, 23-23

complex statements, 23-14

compound queries, 23-27, 23-28, 23-29

joining views, 23-25

joins, 24-2, 24-8

OR operators, 23-12

SQL execution, 16-4, 16-11

star transformation, 24-18

plan stability for optimization, 22-6

PL/SQL, 16-15

anonymous blocks, 16-15, 18-9

auditing of statements within, 31-4

bind variables

user-defined types, 13-14

database triggers, 1-59, 20-1

datatypes, 12-2

deterministic functions, 23-9

dynamic SQL, 16-19

exception handling, 16-18

executing, 16-15, 18-18, 18-19

external procedures, 16-20, 18-11

language constructs, 16-17

object views, 15-4

optimizer goal, 23-33

overview of, 1-55, 16-15

packages, 18-4, 18-11

parse locks, 27-29

parsing DDL statements, 16-19

partition-extended table names, 11-65

PL/SQL engine, 16-15, 18-2

compiler, 18-17

executing a procedure, 18-19

products containing, 16-16

program units, 1-25, 7-10, 16-15, 18-2

compiled, 16-16, 18-9, 18-17

shared SQL areas and, 7-10

roles in procedures, 30-20

stored procedures, 1-25, 16-15, 18-2, 18-6

user locks, 27-39

user-defined datatypes, 13-13

PMON background process, 8-11

See also process monitor process

point-in-time recovery

clone database, 5-7

portability, 1-3

precompilers

anonymous blocks, 16-17

bind variables, 16-13

cursors, 16-11

embedded SQL, 16-5

FIPS flagger, 16-6

stored procedures, 16-18

predicates

dynamic

in security policies, 30-23

optimizing view queries, 23-15

partition pruning, 11-4

indexes, 11-36

pushing into a view, 23-18, 23-23

examples, 23-19, 23-21

selectivity, 22-8

histograms, 22-8, 22-10

user-defined, 22-17

prefixed indexes, 11-30, 11-34

prefixes of data dictionary views, 2-5

PRIMARY KEY constraints, 28-11

constraint checking, 28-20

described, 28-11

indexes used to enforce, 28-12

name of, 28-12

maximum number of columns, 28-12

NOT NULL constraints implied by, 28-12

primary keys, 1-59, 28-11

advantages of, 28-11

defined, 28-3

optimization, 23-14

searches, 23-40

private rollback segments, 4-26

private SQL areas

cursors and, 7-9

described, 7-8

how managed, 7-9

persistent areas, 7-8

runtime areas, 7-8

privileges

administrator, 5-3

connections audited, 31-5

OUTLN schema, 22-6

statement execution not audited, 31-5

auditing use of, 1-44, 31-7

checked when parsing, 16-11

function-based indexes, 10-26, 21-8

granting, 1-41, 30-3, 30-4

examples of, 30-9, 30-10

grouping into roles, 1-41

overview of, 1-41, 30-2

partitioned tables and indexes, 11-62

procedures, 30-7

creating and altering, 30-9

executing, 18-18, 30-7

in packages, 30-9

RESTRICTED SESSION, 29-20

revoked

object dependencies and, 21-6

revoking, 30-3, 30-4

roles, 30-16

restrictions on, 30-20

schema object, 30-3

DML and DDL operations, 30-5

granting and revoking, 30-4

overview of, 1-41

packages, 30-9

procedures, 30-7

system, 30-2

granting and revoking, 30-3

overview of, 1-41

user-defined types, 14-13

to start up or shut down a database, 5-3

trigger privileges, 30-8

user-defined types

acquired by role, 14-13

ALTER ANY TYPE, 14-13

checked when pinning, 14-16

column level for object tables, 14-16

CREATE ANY TYPE, 14-13

CREATE TYPE, 14-13

DELETE, 14-15, 14-16

DROP ANY TYPE, 14-13

EXECUTE, 14-13, 14-14

EXECUTE ANY TYPE, 14-13, 14-14

EXECUTE ANY TYPE with ADMIN OPTION, 14-14

EXECUTE with GRANT option, 14-14

INSERT, 14-15, 14-16

SELECT, 14-15, 14-16

system privileges, 14-13

UPDATE, 14-15, 14-16

using, 14-13, 14-17

views, 30-6

creating, 30-6

using, 30-6

Pro*C/C++

processing SQL statements, 16-10

user-defined datatypes, 13-14

procedural replication, 34-15

detecting conflicts, 34-16

wrapper, 34-16

procedure calls

remote, 33-12

procedures, 16-15, 18-1, 18-6, 21-8

advantages of, 18-7

auditing, 31-8

contrasted with anonymous blocks, 18-9

contrasted with functions, 1-56, 18-2

current user, 18-10

cursors and, 16-18

definer rights, 18-9, 30-7

roles disabled, 30-20

dependency tracking in, 21-6

deterministic functions, 23-9

examples of, 18-6, 30-9, 30-10

executing, 16-16, 18-18

external procedures, 16-20, 18-11

external reference in, 18-10, 18-19

INVALID status, 21-2, 21-6

invoker rights, 18-9, 30-8

roles used, 30-20

supplied packages, 30-8

prerequisites for compilation of, 21-5

privileges

create or alter, 30-9

executing, 30-7

executing in packages, 30-9

remote procedure calls, 33-12

security enhanced by, 18-7, 30-8

shared SQL areas and, 7-10

stored procedures, 16-15, 16-18, 18-2

storing, 18-17

supplied packages, 18-16

invoker or definer rights, 30-8

triggers, 20-2

validity of, 18-18

process global area (PGA), 7-14

See also program global area

process monitor process (PMON)

cleans up timed-out sessions, 29-18

described, 1-19, 8-11

network failure, 32-3

parallel DML process recovery, 26-41

process failure, 32-3

processes, 8-2

archiver (ARCn), 1-19, 8-12, 32-19

background, 1-18, 8-5

diagrammed, 8-6

block server (BSP), 27-6

checkpoint (CKPT), 1-19, 8-11

checkpoints and, 8-8

database writer (DBWn), 1-18, 8-8

dedicated server, 8-19

dispatcher (Dnnn), 1-20, 8-14

distributed transaction resolution, 8-12

during recovery, 32-12

failure in, 32-3

job queue (SNPn), 1-20, 8-13

message propagation, 19-10

listener, 6-6, 8-14

shared servers and, 8-16

lock (LCK0), 1-20, 8-13

log writer (LGWR), 1-19, 8-9

multiple-process Oracle, 8-2

multi-threaded server, 8-16

artificial deadlocks and, 8-19

client requests and, 8-17

Oracle, 1-17, 8-5

overview of, 1-17

parallel execution coordinator, 26-6

direct-load INSERT, 25-3

parallel execution servers, 26-6

direct-load INSERT, 25-3, 25-8

NEXT extent size, 25-9

process monitor (PMON), 1-19, 8-11

queue monitor (QMNn), 1-20, 8-13, 19-6

recoverer (RECO), 1-20, 8-12

and in-doubt transactions, 1-36

server, 1-18, 1-34, 8-5

dedicated, 8-22

shared, 8-14, 8-19

shadow, 8-22

structure, 8-2

system monitor (SMON), 1-19, 8-11

trace files for, 8-14

user, 1-17, 8-4

allocate PGAs, 7-14

manual archiving by, 32-21

recovery from failure of, 8-11

sharing server processes, 8-14

processing

DDL statements, 16-14

distributed, 1-33

DML statements, 16-10

overview, 16-8

parallel SQL, 26-2

queries, 16-12

profiles

overview of, 1-43

password management, 29-7

when to use, 29-18

program global area (PGA), 1-17, 7-14

allocation of, 7-14

contents of, 7-14

multi-threaded servers, 8-19

nonshared and writable, 7-14

size of, 7-15

program interface, 8-25

Oracle side (OPI), 8-25

overview of, 1-20

structure of, 8-25

two-task mode in, 8-23

user side (UPI), 8-25

program units, 1-25, 16-15, 18-2

prerequisites for compilation of, 21-5

shared pool and, 7-10

propagation scheduling capabilities, 19-10

pruning partitions, 11-4, 26-4, 26-48

DATE datatype, 11-22

EXPLAIN PLAN, 11-22

index partitions, 11-4

indexes, 11-36

pseudocode, 18-18

triggers, 20-25

pseudocolumns

CHECK constraints prohibit

LEVEL and ROWNUM, 28-18

modifying views, 20-13

ROWID, 12-15

ROWNUM

cannot use indexes, 23-49

optimizing view queries, 23-16, 23-25

USER, 30-7

public key infrastructure, 29-5

public rollback segments, 4-26

PUBLIC user group, 29-14, 30-19

validity of procedures, 18-19

publication

DDL statements, 20-20

DML statements, 20-20

logon/logoff events, 20-19

system events

server errors, 20-19

startup/shutdown, 20-19

using triggers, 20-18

publish/subscribe support, 19-10

asynchronous notification, 19-11

event publication, 20-18

listen capability, 19-11

message propagation, 19-9

rule-based subscriber, 19-6

triggers, 20-18

purchase order example

object types, 13-2, 13-4

PUSH_JOIN_PRED hint, 24-12

Q

QMNn background process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queries

ad hoc, 26-31

composite indexes, 10-22

compound

defined, 23-4

optimization of, 23-27

ORs converted to, 23-10

default locking of, 27-26

define phase, 16-12

defined, 23-3

describe phase, 16-12

distributed or remote, 33-11

fetching rows, 16-12

in DML, 16-3

index scans parallelized by partition, 26-5

inline views, 10-16

location transparency and, 33-15

merged with view queries, 10-14

optimizing IN subquery, 23-17

optimizing view queries, 23-15

parallel processing, 26-2

phases of, 27-5

processing, 16-12

read consistency of, 1-32, 27-5

SAMPLE clause

cost-based optimization, 22-16

selectivity of, 23-51

star queries, 24-14

stored as views, 1-24, 10-11

table scans parallelized by rowid, 26-4

temporary segments and, 4-18, 16-12

triggers use of, 20-23

query rewrite, 10-17

dynamic predicates in security policies, 30-23

queue monitor process (QMNn), 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queuing, 19-2

exception handling, 19-11

exporting queue tables, 19-12

instance affinity, 19-10

publish/subscribe support, 19-10

event publication, 20-18

queue level access control, 19-9

queue monitor process, 1-20, 8-13, 19-6

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4, 19-12

recipients, 19-5

rule-based subscriptions, 19-5, 19-6

subscription lists, 19-5

remote databases, 19-9

quotas

revoking tablespace access and, 29-14

setting to zero, 29-14

SYS user not subject to, 29-14

tablespace, 1-43, 29-13

temporary segments ignore, 29-14

R

RADIUS, 29-6

range partitioning, 11-15

and primary key columns, 11-42

equipartitioning, 11-24

key comparison, 11-20, 11-22

partition bounds, 11-20

RAW datatype, 12-14

RAWTOHEX function, 12-23

RDBMS, 1-22

object-relational DBMS, 1-22, 13-2

See also Oracle

read committed isolation, 27-6, 27-8

read consistency, 27-2, 27-4

Cache Fusion, 27-6

defined, 1-31

dirty read, 27-2, 27-10

multiversion consistency model, 1-31, 27-4

nonrepeatable read, 27-3, 27-10

Oracle Parallel Server, 27-6

phantom read, 27-3, 27-10

queries, 16-12, 27-4

rollback segments and, 4-20

snapshot too old message, 27-5

statement level, 27-5

subqueries in DML, 27-14

transactions, 1-31, 27-4, 27-6

triggers and, 20-21, 20-23

READ ONLY option

ALTER TABLESPACE, 3-10

read snapshot time, 27-10

read uncommitted, 27-3

READ WRITE option

ALTER TABLESPACE, 3-11

readers block writers, 27-10

read-only databases

opening, 5-9

read-only replication

uses of, 34-11

read-only snapshot, 34-8

read-only tablespaces

backing up, 32-26

described, 3-10

restrictions on, 3-12

transition read-only mode, 3-11

read-only transactions, 1-32

reads

data block

limits on, 29-17

dirty, 27-2

repeatable, 27-6

real-time

data replication, 34-16

replication, 34-16

REBUILD INDEX command

no-logging mode, 11-58, 25-7

rules of parallelism, 26-25

REBUILD INDEX PARTITION command, 11-60

no-logging mode, 25-7

rules of parallelism, 26-25

recipients, 19-5

subscription lists, 19-5

recoverer process (RECO), 1-20, 8-12

in-doubt transactions, 1-36, 5-8, 17-8

recovery

basic steps, 1-50, 32-9

block-level recovery, 27-21, 32-14

crash recovery, 1-46, 32-4, 32-13

instance failure, 5-10

opening a database, 5-8

read-only tablespaces, 32-6

required after aborting instance, 5-10

SMON process, 1-19, 8-11

database buffers and, 32-8

dead transactions, 32-4

diagrammed, 32-13

disaster recovery, 32-26

distributed processing in, 8-12

instance recovery, 32-4

Fast-Start Checkpointing, 32-13

instance failure, 1-46, 32-4

parallel DML, 26-41

read-only tablespaces, 32-6

SMON process, 1-19, 8-11, 26-41

media recovery

dispatcher processes, 8-20

enabled or disabled, 32-18

of distributed transactions, 5-8

overview of, 1-45, 32-8

parallel DML, 26-40

parallel recovery, 32-10

parallel restore, 32-16

point-in-time

clone database, 5-7

process recovery, 8-11, 32-3

recommendations for, 32-13

Recovery Manager, 1-51, 32-15

rolling back transactions, 32-9

rolling forward, 32-9

standby database, 32-26

statement failure, 32-3

structures used in, 1-47, 32-6

whole database backups, 32-24

Recovery Manager, 1-51, 32-15

generating reports, 32-17

operating without a catalog, 32-16

parallel operations, 32-16

parallel recovery, 32-11

recovery catalog, 32-15

recursive SQL

cursors and, 16-7

redo entries, 1-12, 32-9

redo log, 1-12, 32-9

archiving modes, 32-18

rolling forward, 32-8, 32-9

instance failure, 32-4

redo log buffers, 1-16, 7-6

circularity, 8-9

committing a transaction, 8-10

log writer process and, 7-6

size of, 7-6

writing, 8-9

redo log entries

committed data, 32-8, 32-9

uncommitted data, 32-9

redo log files, 1-12, 32-7

archived, 1-48, 32-18

automatically, 32-19

errors in archiving, 32-20

manually, 32-20

archiver process (ARCn), 1-19, 8-12

buffer management, 8-9

files named in control file, 32-21

log sequence numbers, 1-48

recorded in control file, 32-22

log switch

ALTER SYSTEM SWITCH LOGFILE, 8-12

archiver process, 1-19, 8-12

log writer process, 8-9

mode of, 1-48

multiplexed, 1-48

purpose of, 1-12

online or offline, 1-47, 1-48, 32-7

overview of, 1-12, 1-47

parallel recovery, 32-10

physical database structure, 1-5

recovery and, 32-7

redo entries, 1-12, 32-9

rolling forward and, 32-9

when temporary segments in, 4-19

written before transaction commit, 8-10

redo records, 1-12

referenced

keys, 1-59, 28-13

objects

dependencies, 21-2

external reference, 18-10, 18-19

partitions, 11-19

REFERENCES privilege

when granted through a role, 30-21

referential integrity, 27-11, 28-13

cascade rule, 28-3

examples of, 28-18

partially null foreign keys, 28-16

PRIMARY KEY constraints, 28-11

restrict rule, 28-3

self-referential constraints, 28-15, 28-18

set to default rule, 28-3

set to null rule, 28-3

refresh

incremental, 10-18

job queue processes (SNPn), 1-20, 8-13

materialized views, 10-18

REFs

constructing from object identifiers, 14-18, 14-19

dangling, 13-9, 13-10

dereferencing of, 13-10

for rows of object views, 15-3

implicit dereferencing of, 13-10

indexes on, 14-6

mutually dependent types, 14-16

pinning, 14-16, 15-4

scoped, 13-9, 14-19

size of, 14-19

use of table aliases, 14-8

REFTOHEX function, 12-23

relational DBMS (RDBMS)

object-relational DBMS, 13-2

principles, 1-22

SQL and, 16-2

See also Oracle

relations, 1-23

remote databases, 1-35

database links, 33-6

remote dependencies, 21-11

remote procedure calls, 33-12

remote procedure calls (RPCs), 33-12

remote transactions, 33-12

parallel DML and DDL restrictions, 26-28

REMOTE_DEPENDENCIES_MODE parameter, 21-11

RENAME command, 16-4

repeatable reads, 27-3

replication

advanced, uses for, 34-6

catalog, 34-14

conflicts

procedural replication, 34-16

definition, 34-2

distributed databases vs., 33-7

group, 34-2

materialized views (snapshots), 10-17

objects, 34-2

procedural, 34-15

real-time, 34-16

restrictions

direct-load INSERT, 25-12

parallel DML, 26-44

sites, 34-5

uses of read-only, 34-11

replication management API, 34-14

administration requests, 34-14

Replication Manager, 34-15

reserved words, 16-3

resource limits

call level, 29-16

connect time per session, 29-18

CPU time limit, 29-17

determining values for, 29-19

idle time per session, 29-17

logical reads limit, 29-17

overview of, 1-43

private SGA space per session, 29-18

session level, 29-16

sessions per user, 29-17

RESOURCE role, 30-22

user-defined types, 14-13, 14-14

response queues, 8-17

response time, 22-7

cost-based approach, 23-31

restricted mode

starting instances in, 5-6

restricted rowid format, 12-18

RESTRICTED SESSION privilege, 29-20

restrictions

direct-load INSERT, 25-11, 26-43

nested tables, 26-30

parallel DDL, 26-31

remote transactions, 26-28

parallel DML, 26-43

remote transactions, 26-28, 26-46

parallel execution of functions, 26-46

partition views, 11-12

partitions

bitmap indexes, 11-14

datatypes, 11-14, 11-21

partition-extended table names, 11-64

reverse key indexes, 10-31

REVERSE option for indexes, 10-31

REVOKE command, 16-4

FORCE option, 14-17

locks, 27-29

object types and dependencies, 14-17

rewrite

predicates in security policies, 30-23

using materialized views, 10-17

roles, 1-41, 30-16

application, 30-18

CONNECT role, 14-13, 14-14, 30-22

DBA role, 14-13, 30-22

DDL statements and, 30-20

definer-rights procedures disable, 30-20

dependency management in, 30-20

distributed database applications, 33-17

enabled or disabled, 30-18

EXP_FULL_DATABASE role, 30-22

functionality, 30-2

global authentication service, 33-17

granting, 30-3, 30-18

IMP_FULL_DATABASE role, 30-22

in applications, 1-42

invoker-rights procedures use, 30-20

managing via operating system, 30-22

naming, 30-19

overview of, 1-41

predefined, 30-22

queue administrator, 19-7

RESOURCE role, 14-13, 14-14, 30-22

restrictions on privileges of, 30-20

revoking, 30-18

schemas do not contain, 30-19

security domains of, 30-19

setting in PL/SQL blocks, 30-20

use of passwords with, 1-42

user, 30-18

users capable of granting, 30-19

uses of, 30-17

rollback, 4-20, 17-6

defined, 1-53

described, 17-6

during recovery, 1-51, 32-9

ending a transaction, 17-2, 17-4, 17-6

statement-level, 17-4

to a savepoint, 17-6

ROLLBACK command, 16-5

rollback entries, 4-20

rollback segments, 1-11, 4-19

access to, 4-20

acquired during startup, 5-8

allocation of extents for, 4-22

new extents, 4-24

clashes when acquiring, 4-27

committing transactions and, 4-21

contention for, 4-21

deallocating extents from, 4-25

deferred, 4-30

defined, 1-11

dropping, 4-25

restrictions on, 4-30

how transactions write to, 4-22

in-doubt distributed transactions, 4-24

invalid, 4-28

locks on, 27-30

MAXEXTENTS UNLIMITED, 26-40

moving to the next extent of, 4-22

number of transactions per, 4-21

offline, 4-28, 4-30

offline tablespaces and, 4-30

online, 4-28, 4-30

OPTIMAL, 26-40

overview of, 4-19, 32-8

parallel DML, 26-40

parallel recovery, 32-10

partly available, 4-28, 32-4

private, 4-26

public, 4-26

read consistency and, 1-31, 4-20, 27-4

recovery needed for, 4-28

states of, 4-28

SYSTEM rollback segment, 4-26

transactions and, 4-20

use of in recovery, 1-49, 32-9

when acquired, 4-26

when used, 4-20

written circularly, 4-21

rolling back during recovery, 32-9

rolling back transactions, 1-54, 17-2, 17-6, 32-4

rolling forward during recovery, 1-50, 32-9

root blocks, 10-55

row cache, 7-10

row data (section of data block), 4-5

row directories, 4-4

row locking, 27-11, 27-20

block-level recovery, 27-21, 32-14

serializable transactions and, 27-8

row objects, 13-8

row pieces, 10-5

headers, 10-5

how identified, 10-7

row sampling, 22-14

row sources, 22-4

row triggers, 20-8

when fired, 20-21

See also triggers

ROWID datatype, 12-15, 12-16

extended rowid format, 12-17

restricted rowid format, 12-18

rowids, 10-7

accessing, 12-15

changes in, 12-16

in non-Oracle databases, 12-22

internal use of, 12-16, 12-19

logical, 12-15

logical rowids, 12-20

index on index-organized table, 10-39

physical guesses, 10-39, 12-20

staleness of guesses, 12-21

statistics for guesses, 12-21

of clustered rows, 10-7

physical, 12-15

row migration, 4-10

sorting indexes by, 10-28

table access by, 23-34

universal, 12-15

ROWIDTOCHAR function, 12-23

row-level locking, 27-10, 27-20

ROWNUM pseudocolumn

cannot use indexes, 23-49

optimizing view queries, 23-16, 23-25

rows, 1-23, 10-3

addresses of, 10-7

chaining across blocks, 4-10, 10-5

clustered, 10-6

rowids of, 10-7

defined, 1-23

described, 10-3

fetched, 16-12

format of in data blocks, 4-4

headers, 10-5

locking, 27-11, 27-20

locks on, 11-45, 27-20, 27-23

logical rowids, 12-20

index-organized tables, 10-39

migrating to new block, 4-10

pieces of, 10-5

row objects, 13-8

row overflow in index-organized tables, 10-38

row sources, 22-4

rowids used to locate, 23-34, 23-38

row-level security, 30-22

shown in rowids, 12-17, 12-18

size of, 10-5

storage format of, 10-5

triggers on, 20-8

when rowid changes, 12-16

RPC, 33-12

RULE hint

OPTIMIZER_MODE and, 23-33

rule-based optimization, 22-18

rule-based subscriptions, 19-5, 19-6

runtime areas, 7-8