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 

---

A

aborting an instance, 5-10, 29-4

access control, 27-2

discretionary, 1-38

fine-grained access control, 27-23

password encryption, 26-7

privileges, 27-2

roles, 27-17

access methods

execution plans, 21-2

access paths

defined, 21-5

ADMIN OPTION

roles, 27-20

system privileges, 27-3

administrator privileges, 5-3

connections audited, 28-5

OUTLN schema, 21-7

statement execution not audited, 28-4

Advanced Queuing, 18-1

agents, 18-6

DEQUEUE request, 18-10

event publication, 19-19

exception handling, 18-14

exception queues, 18-6

exporting queue tables, 18-15

features, 18-9

message queuing, 18-2

messages, 18-6

point-to-point model, 18-4

publish-subscribe model, 18-5

publish-subscribe support, 18-13, 19-19

queue monitor, 18-8

queue monitor process, 1-19, 8-14, 18-8

interval statistics, 18-14

window of execution, 18-9

queue tables, 18-6

recipients, 18-7

rule-based subscriptions, 18-8

subscription lists, 18-7

remote databases, 18-12

rules, 18-8

subscribers, 18-7

user queues, 18-6

affinity

parallel DML, 23-50

partitions, 23-49

AFTER triggers, 19-10

defined, 19-10

when fired, 19-22

agents for queuing, 18-6

alert files, 8-15

ARCn processes, 8-13

redo logs, 8-10

aliases

qualifying subqueries (inline views), 10-17

ALL_ views, 2-6

ALL_UPDATABLE_COLUMNS view, 10-16

ALTER DATABASE statement, 5-7

ALTER INDEX statement

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

partition attributes, 11-36

REBUILD PARTITION clause, 11-60

ALTER SESSION statement, 15-6

dynamic parameters, 5-5

ENABLE PARALLEL DML clause, 23-39

FORCE PARALLEL DDL clause, 23-25, 23-28

create or rebuild index, 23-25, 23-28

create table as select, 23-26, 23-28

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

FORCE PARALLEL DML clause

insert, 23-24, 23-27

update and delete, 23-22, 23-27

SET CONSTRAINTS DEFERRED clause, 25-25

transaction isolation level, 24-8, 24-32

ALTER statement, 15-4

auditing partitions, 11-63

ALTER SYSTEM statement, 15-6

dynamic parameters, 5-5

LOG_ARCHIVE_MAX_PROCESSES, 8-13, 29-21

SWITCH LOGFILE clause, 8-12

ALTER TABLE statement

add or coalesce hash partition, 11-16

auditing, 28-7

CACHE clause, 7-4

DEALLOCATE UNUSED clause, 4-15

disable or enable constraints, 25-26

DROP COLUMN clause, 10-7

EXCHANGE PARTITION, 11-11

MERGE PARTITIONS clause, 11-14

MODIFY CONSTRAINT clause, 25-27

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

partition attributes, 11-26

triggers, 19-7

UNUSED column, 10-7

validate or novalidate constraints, 25-26

ALTER TABLESPACE statement

READ ONLY clause, 3-11

READ WRITE clause, 3-11

TEMPORARY or PERMANENT, 3-13

ALTER USER statement

temporary segments, 4-20

ANALYZE statement, 15-5

creating histograms, 21-12

estimated statistics, 21-15

partition statistics, 11-13

shared pool, 7-11

anonymous PL/SQL blocks, 15-16, 17-11

applications, 15-18

calling a stored procedure, 15-20

contrasted with stored procedures, 17-11

dynamic SQL, 15-20

performance, 17-11

ANSI SQL standard

datatypes of, 12-23

Oracle certification, 1-3

ANSI/ISO SQL standard, 1-3

composite foreign keys, 25-16

data concurrency, 24-2

isolation levels, 24-11

applications

application vs. database triggers, 19-3

can find constraint violations, 25-6

context, 27-25

data dictionary references, 2-4

data warehousing, 10-34

database access through, 8-2

decision support systems (DSS), 10-35

parallel SQL, 23-2, 23-32

dependencies of, 20-11

direct-load INSERT, 23-39

discrete transactions, 16-9

enhancing security with, 1-41, 25-6

index-organized tables, 10-43

information retrieval (IR), 10-44

network communication and, 6-5

object dependencies and, 20-13

online analytical processing (OLAP), 10-45

online transaction processing (OLTP)

reverse key indexes, 10-33

parallel DML, 23-38

processes, 8-4

program interface and, 8-27

roles and, 27-18

security

application context, 27-25

sharing code, 7-19

spatial applications, 10-45

transaction termination and, 16-5

AQ_ADMINISTRATOR role, 18-9

AQ_TM_PROCESS parameter, 18-8, 18-9

architecture

client/server, 1-32

MPP, 23-50

of Oracle, 1-12

SMP, 23-50

archived redo logs, 1-47

automatic archiving, 29-20

enabling, 29-19

manual archiving, 29-21

ARCHIVELOG mode

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

defined, 29-19

overview, 1-47

partial database backups, 1-49, 29-26

whole database backups, 29-25

archiver process (ARCn)

automatic archiving, 29-20

described, 1-18, 8-12

example, 29-19

multiple processes, 1-18, 8-13

not used for manual archiving, 29-22

trace file, 29-21

ARCn background process, 1-18, 8-12

See also archiver process

array processing, 15-15

arrays

size of VARRAYs, 13-11

variable (VARRAYs), 13-11

asynchronous I/O

parallel recovery and, 29-12

asynchronous processing, 18-2

attributes

object types, 13-2, 13-4

attributes of object types, 13-4

AUDIT statement, 15-5

locks, 24-30

auditing, 1-43, 28-1

audit options, 28-3

audit records, 28-3

audit trails, 28-3

database, 28-3

operating system, 28-5, 28-6

by access, 28-11

mandated for, 28-12

by session, 28-10

prohibited with, 28-12

connect with administrator privileges, 28-5

data dictionary used for, 2-5

database and OS usernames, 26-4

DDL statements, 28-7

deleting data in dictionary, 2-5

described, 1-43, 28-2

distributed databases and, 28-6

DML statements, 28-7

partitioned tables and indexes, 11-63

privilege use, 28-2, 28-7

range of focus, 28-3, 28-9

schema object, 28-2, 28-8

security and, 28-6

startup and shutdown, 28-5

statement, 28-2, 28-7

successful executions, 28-9

transaction independence, 28-4

types of, 28-2

unsuccessful executions, 28-9

user, 28-12

when options take effect, 28-5

authentication

database administrators, 26-13

described, 26-3

multi-tier, 26-9

network, 26-4

operating system, 26-4

Oracle, 26-7

public key infrastructure, 26-5

remote, 26-7

---

B

back-ends, 6-2

background processes, 1-16, 8-5

described, 8-5

diagrammed, 8-6

overview of, 1-16

trace files for, 8-15

See also processes

backups

control files, 29-26

datafiles, 29-26

for read-only tablespaces, 29-27

overview of, 1-44, 29-24

parallel, 29-17

partial, 1-49, 29-26

Recovery Manager, 1-50, 29-16

types of, 1-49

using Export to supplement, 29-27

whole database backup, 1-49, 29-24

base tables, 1-23

data dictionary, 2-3

See also views

BEFORE triggers, 19-10

defined, 19-10

when fired, 19-22

BFILE datatype, 12-14

binary data

BFILEs, 12-14

BLOBs, 12-13

RAW and LONG RAW, 12-15

bind variables

user-defined types, 13-14

bitmap indexes, 10-34

cardinality, 10-35

nulls and, 10-9, 10-38

parallel query and DML, 10-35

partitioned tables, 11-13

bitmap tablespace management, 3-8

temporary tablespaces, 3-13

BLOBs, 12-13

block server process (BSP), 24-7

blocking transactions, 24-11

block-level recovery, 24-21, 29-15

blocks

anonymous, 15-16, 17-11

block-level recovery, 29-15

database, 4-3

sampling, 21-16

BOOLEAN datatype, 12-2

branch blocks, 10-30

BSP. See block server process

B-tree indexes, 10-29

bitmap indexes vs., 10-34, 10-35

index-organized tables, 10-40

buffer caches, 7-3, 8-8

database, 1-14, 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-6

BUFFER_POOL_RECYCLE parameter, 7-6

buffers

database buffer cache

fast-start checkpointing, 29-14

incremental checkpoint, 8-8

redo log, 1-14, 7-6

BUILD_PART_INDEX procedure, 11-29

business rules

enforcing in application code, 25-5

enforcing using stored procedures, 25-5

enforcing with constraints, 1-57, 25-1

advantages of, 25-5

---

C

CACHE clause, 7-4

Cache Fusion, 24-7

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

database buffer, 1-14

library cache, 7-7, 7-8, 7-10

object cache, 13-14

object views, 14-4

private SQL area, 7-8

shared SQL area, 7-7, 7-8

writing of buffers, 8-8

calls

Oracle call interface, 8-27

remote procedure, 30-46

cannot serialize access, 24-11

cardinality, 10-35

CASCADE actions

DELETE statements and, 25-17

century, 12-12

certificate authority, 26-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-14

column lengths, 12-6

for various languages, 5-5

NCHAR and NVARCHAR2, 12-7

CHARTOROWID function, 12-24

check constraints, 25-21

checking mechanism, 25-23

defined, 25-21

multiple constraints on a column, 25-21

partially null foreign keys, 25-16

subqueries prohibited in, 25-21

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

checkpoints

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

control files and, 29-23

DBWn process, 8-8, 8-11

fast-start checkpointing, 29-14

incremental, 8-8

statistics on, 8-11

CKPT background process, 1-17, 8-11

client/server architectures, 6-2

clients, 1-32

diagrammed, 6-2

direct and indirect connections, 30-9

distributed databases and, 30-6

distributed processing in, 6-2

overview of, 1-32, 6-2

program interface, 8-27

CLOB datatype, 12-14

clone databases

mounting, 5-8

cluster keys, 1-26, 10-52

clustered computer systems

Oracle Parallel Server, 5-3

clusters

cannot be partitioned, 11-2

choosing data to cluster, 10-52

defined, 1-26

dictionary locks and, 24-30

hash, 10-54

allocation of space for, 10-59

collision resolution, 10-57

contrasted with index, 10-55

root blocks, 10-59

single-table, 10-61

storage of, 10-55

index, 10-54

contrasted with hash, 10-55

indexes on, 10-23

cannot be partitioned, 11-2

joins and, 10-52

keys, 1-26, 10-52, 10-53

affect indexing of nulls, 10-9

overview of, 10-49

performance considerations of, 10-52

rowids and, 10-8

scans of, 7-4

setting parameters of, 10-53

storage format of, 10-53

storage parameters of, 10-5

coalescing extents, 4-16

coalescing free space

extents, 4-14

SMON process, 1-18, 8-11

within data blocks, 4-10

collections, 13-11

index-organized tables, 10-41

key compression, 10-33

nested tables, 13-12

variable arrays (VARRAYs), 13-11

columns

cardinality, 10-35

column objects, 13-8

default values for, 10-9

defined, 1-22

described, 10-3

dropping, 10-7

integrity constraints, 10-4, 10-9, 25-4, 25-7

maximum in concatenated indexes, 10-25

maximum in view or table, 10-13

nested tables, 10-10

order of, 10-8

prohibiting nulls in, 25-7

pseudocolumns

ROWID, 12-16

USER, 27-7

selectivity, 21-9

histograms, 21-10, 21-11

unused, 10-7

COMMENT statement, 15-5

COMMIT statement, 15-5

ending a transaction, 16-2, 16-5

fast commit, 8-10

implied by DDL, 16-2, 16-5

two-phase commit, 16-8, 30-36

two-phase commit in parallel DML, 23-41

committing transactions

defined, 16-2

fast commit, 8-10

group commits, 8-10

implementation, 8-10

overview, 1-54

parallel DML, 23-41

communication protocols, 6-5

comparison methods, 13-7

compatibility, 1-3

compatibility levels

transportable tablespaces, 3-15

COMPATIBLE parameter

read-only tablespaces, 3-11

compiled PL/SQL, 17-17

advantages of, 17-9

procedures, 17-11

pseudocode, 17-18, 19-26

recompiling, 17-20

shared pool, 15-17

triggers, 19-26

composite indexes, 10-24

compression of free space in data blocks, 4-10

compression, index key, 10-31

concatenated indexes, 10-24

concurrency

defined, 1-29

described, 24-2

direct-load INSERT, 22-10

enforced with locks, 1-31

limits on

per database, 26-21

per user, 26-19

partition maintenance, 11-49

restrictions on, 1-42, 22-10

transactions and, 24-16

configuration of a database

process structure, 8-2

configuring

parameter file, 5-4

process structure, 8-2

conflicts

procedural replication, 31-17

CONNECT INTERNAL, 5-3

CONNECT role, 27-22

connectibility, 1-2

connection pooling, 26-9

connections

defined, 8-4

embedded SQL, 15-6

listener process and, 6-6, 8-14

restricting, 5-6

sessions contrasted with, 8-4

with administrator privileges, 5-3

audit records, 28-5

consistency of data, 1-54

multiversion consistency model, 1-30

See also read consistency

constants

in stored procedures, 15-18

constraints, 1-57

alternatives to, 25-5

applications can find violations, 25-6

CHECK, 25-21

default values and, 25-24

defined, 10-4

disabling temporarily, 25-7

effect on performance, 25-7

ENABLE or DISABLE, 25-26

enforced with indexes, 10-25

PRIMARY KEY, 25-12

UNIQUE, 25-10

FOREIGN KEY, 1-58, 25-13

mechanisms of enforcement, 25-21

modifying, 25-27

NOT NULL, 25-7, 25-11

overview, 1-57

parallel create table, 23-25

PRIMARY KEY, 1-58, 25-11

prohibited in views, 10-13

referential

effect of updates, 25-16

self-referencing, 25-15

triggers cannot violate, 19-22

triggers contrasted with, 19-5

types listed, 1-57, 25-1

UNIQUE key, 1-58, 25-8

partially null, 25-11

VALIDATE or NOVALIDATE, 25-26

what happens when violated, 25-5

when evaluated, 10-9

constructor methods, 1-56, 13-6

contention

for data

deadlocks, 8-20, 24-17

lock escalation does not occur, 24-17

for rollback segments, 4-23

control files, 1-10, 29-22

backing up, 29-26

changes recorded, 29-23

checkpoints and, 29-23

contents, 29-22

how specified, 5-4

multiplexed, 1-48, 29-23

overview, 1-10, 29-22

recovery and, 1-48

used in mounting database, 5-6

converting data

ANSI datatypes, 12-23

program interface, 8-27

SQL/DS and DB2 datatypes, 12-23

correlation names

inline views, 10-17

cost-based optimization, 21-8, 30-47

extensible optimization, 21-18

histograms, 21-9

query rewrite, 10-18

selectivity of predicates, 21-9

histograms, 21-9, 21-11

user-defined, 21-19

statistics, 21-9

user-defined, 21-19

user-defined costs, 21-19

CPU time limit, 26-18

crash recovery, 29-4, 29-14

instance failure, 1-45, 5-11, 29-4

opening a database, 5-9

read-only tablespaces, 29-6

required after aborting instance, 5-10

SMON process, 1-17, 8-11

CREATE CLUSTER statement

HASHKEYS clause, 10-57, 10-61

SINGLE TABLE HASHKEYS clause, 10-61

storage parameters, 4-18

CREATE FUNCTION statement, 17-18

CREATE INDEX statement

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

partition attributes, 11-36

rules of parallelism, 23-25

storage parameters, 4-19

temporary segments, 4-19

CREATE OUTLINE statement, 21-7

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

CREATE PACKAGE statement

examples, 17-12, 19-12

locks, 24-30

package name, 17-18

CREATE PROCEDURE statement

example, 17-6

locks, 24-30

procedure name, 17-18

CREATE statement, 15-4

CREATE SYNONYM statement

locks, 24-30

CREATE TABLE AS SELECT

rules of parallelism

index-organized tables, 23-31

CREATE TABLE statement

AS SELECT

comparison with direct-load INSERT, 22-2

decision support systems, 23-32

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

rules of parallelism, 23-25

space fragmentation, 23-34

temporary storage space, 23-34

auditing, 28-7, 28-10

CACHE clause, 7-4

enable or disable constraints, 25-26

examples

column objects, 13-5

nested tables, 13-12

object tables, 13-8, 13-12

locks, 24-30

parallelism, 23-32

index-organized tables, 23-31

partition attributes, 11-26

storage parameters, 4-18

triggers, 19-7

CREATE TABLESPACE statement

TEMPORARY clause, 3-13

CREATE TEMPORARY TABLE statement, 10-11

CREATE TEMPORARY TABLESPACE statement, 3-13

CREATE TRIGGER statement

compiled and stored, 19-26

examples, 19-12, 19-15, 19-25

locks, 24-30

CREATE TYPE statement

nested tables, 13-4, 13-12

object types, 13-4

object views, 14-3

VARRAYs, 13-11

CREATE USER statement

temporary segments, 4-20

CREATE VIEW statement

examples, 19-15

object views, 14-3

locks, 24-30

CREATE_STORED_OUTLINES session parameter, 21-7

cursors

creating, 15-12

defined, 15-7

embedded SQL, 15-6

maximum number of, 15-7

object dependencies and, 20-10

opening, 7-9, 15-7

overview of, 1-15

private SQL areas and, 7-9, 15-7

recursive, 15-7

recursive SQL and, 15-7

stored procedures and, 15-19

---

D

dangling REFs, 13-10

data

access to, 1-50

concurrent, 24-2

control of, 26-2

fine-grained access control, 27-23

message queues, 18-9

security domains, 26-2

consistency of

defined, 1-54

examples of lock behavior, 24-33

locks, 24-3

manual locking, 24-32

read consistency, 1-30

repeatable reads, 24-6

transaction level, 24-6

underlying principles, 24-15

distributed manipulation of, 1-34

how stored in tables, 10-4

integrity of, 1-29, 10-4, 25-2

CHECK constraints, 25-21

enforcing, 25-4, 25-5

overview, 1-57

parallel DML restrictions, 23-46

referential, 25-3

two-phase commit, 1-35

types, 25-3

locks on, 24-20

replicating, 1-36

data blocks, 1-7, 4-2

allocating for extents, 4-14

block-level recovery, 29-15

cached in memory, 8-8

clustered, 10-53

coalescing extents, 4-14

coalescing free space in blocks, 4-10

controlling free space in, 4-5

format, 4-3

free lists and, 4-10

hash keys and, 10-59

how rows stored in, 10-5

overview, 4-2

read-only transactions and, 24-33

row directory, 10-7

shared in clusters, 10-49

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-23

program interface, 8-27

SQL/DS and DB2 datatypes, 12-23

data definition language, 1-52

auditing, 28-7

commit implied by, 16-5

defined, 1-52

described, 15-4

embedding in PL/SQL, 15-20

locks, 24-29

parallel DDL, 23-3

parsing with DBMS_SQL, 15-20

processing statements, 15-15

roles and privileges, 27-21

data dictionary

access to, 2-3

adding objects to, 2-5

ALL prefixed views, 2-6

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

backups, 29-26

cache, 7-10

location of, 7-7

content of, 2-2, 7-10

procedures, 17-19

datafiles, 3-6, 29-26

DBA prefixed views, 2-6

defined, 1-29, 2-2

dependencies tracked by, 20-3

dictionary-managed tablespaces, 3-8

DUAL table, 2-7

dynamic performance tables, 2-7

locks, 24-29

owner of, 2-3

prefixes to views of, 2-5

public synonyms for, 2-4

replication, 31-14

row cache and, 7-10

statistics in, 21-16

partition statistics, 11-13

structure of, 2-3

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, 15-12

validity of procedures, 17-19

views used in optimization, 21-16

data locks

conversion, 24-17

duration of, 24-16

escalation, 24-17

data manipulation language

auditing, 28-7

defined, 1-52

described, 15-4

locks acquired by, 24-26

parallel DML, 23-3, 23-36

partition locks, 11-45

privileges controlling, 27-5

processing statements, 15-11

serializable isolation for subqueries, 24-14

statements allowed in distributed transactions, 30-34

transaction model for parallel DML, 23-40

triggers and, 19-3, 19-24

data models, 1-20

data object number

extended rowid, 12-17

data segments, 1-7, 4-18, 10-4

data warehousing

bitmap indexes, 10-34

dimension schema objects, 1-28, 10-20

hierarchies, 1-28, 10-20

materialized views, 10-18

refreshing table data, 23-38

summaries, 10-18

database administrators (DBAs)

authentication, 26-13

data dictionary views, 2-6

DBA role, 27-22

password files, 26-14

responsible for backup and recovery, 29-2

database buffers

after committing transactions, 16-6

buffer cache, 7-3, 8-8

clean, 8-8

committing transactions, 8-10

defined, 1-14, 7-3

dirty, 7-4, 8-8

free, 7-4

multiple buffer pools, 7-5

pinned, 7-4

size of cache, 7-5

writing of, 8-8

database links, 1-28

defined, 1-28

partition-extended table names, 11-65

resolution, 30-36

roles on remote database, 30-23

database management system (DBMS), 1-2

object-relational DBMS, 13-2

Oracle server, 1-4

principles, 1-20

database structures

control files, 1-10, 29-22

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

data dictionary, 1-29, 2-1

datafiles, 1-8, 3-1, 3-16

extents, 1-7, 4-2, 4-12

logical, 1-5

memory, 1-12, 7-1

processes, 1-12, 1-15, 8-1

redo log files, 1-9, 29-7

revealing with rowids, 12-18

schema objects, 1-6, 10-2

segments, 1-7, 4-2, 4-18

tablespaces, 1-5, 3-1, 3-6

database triggers, 19-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, 29-6

multiple DBWn processes, 8-8

overview of, 1-17

trace file, 29-6

when active, 8-8

write-ahead, 8-9

writing to disk at checkpoints, 8-11

databases

access control

overview, 1-50

password encryption, 26-7

security domains, 26-2

backing up, 1-49, 29-24

clone database, 5-8

closing, 5-10

aborting the instance, 5-10, 29-4

configuring, 5-4

contain schemas, 26-2

defined, 1-5

dismounting, 5-11

distributed, 1-34

changing global database name, 7-12

nodes of, 1-34

overview of, 1-32, 1-34

site autonomy of, 30-24

table replication, 1-36

two-phase commit, 1-35

limitations on usage, 26-17

modes of archiving, 29-18

mounting, 5-6

name stored in control file, 29-22

open and closed, 5-3

opening, 5-8

acquiring rollback segments, 4-27

opening read-only, 5-9

recovery of, 1-44, 29-2

scalability, 6-4, 23-2, 23-38

shutting down, 5-10

standby, 5-7, 29-27

starting up, 5-2

forced, 5-11

structures

control files, 1-10, 29-22

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

data dictionary, 1-29, 2-1

datafiles, 1-8, 3-1, 3-16

extents, 1-7, 4-2, 4-12

logical, 1-5, 4-1

memory, 1-12, 7-1

physical, 1-8

processes, 1-12, 1-15, 8-1

redo log files, 1-9, 29-7

revealing with rowids, 12-18

schema objects, 1-6, 10-2

segments, 1-7, 4-2, 4-18

tablespaces, 1-5, 3-1, 3-6

datafiles

backing up, 29-26

backup, 29-26

contents of, 3-17

data dictionary, 3-6, 29-26

datafile 1, 3-6

backup, 29-26

SYSTEM tablespace, 3-6

in online or offline tablespaces, 3-17

named in control files, 29-22

overview of, 1-5, 1-8, 3-16

parallel recovery, 29-11

read-only, 3-11

recovery, 29-6

read-only tablespaces and, 3-12

relationship to tablespaces, 3-2

shown in rowids, 12-17, 12-18

SYSTEM tablespace, 3-6

taking offline, 3-17

temporary, 3-18

unrecoverable, 29-18

datatypes, 12-2, 12-3

ANSI, 12-23

array types, 13-11

BOOLEAN, 12-2

CHAR, 12-5

character, 12-5, 12-14

collections, 13-11

conversions of

by program interface, 8-27

non-Oracle types, 12-23

Oracle to another Oracle type, 12-24

DATE, 12-10

DB2, 12-23

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-14

default logging mode, 22-8

LONG, 12-7

storage of, 10-8

multimedia, 13-3

NCHAR and NVARCHAR2, 12-7

nested tables, 10-10, 13-12

NUMBER, 12-8

object types, 1-21, 13-4

of columns, 1-22

RAW and LONG RAW, 12-15

ROWID, 12-15, 12-16

SQL/DS, 12-23

summary, 12-3

user-defined, 13-1, 13-3

statistics, 21-19

VARCHAR, 12-6

VARCHAR2, 12-6

DATE datatype, 12-10

arithmetic with, 12-11

changing default format of, 12-10

Julian dates, 12-11

midnight, 12-11

partition pruning, 11-21

partitioning, 11-12, 11-21

DB_BLOCK_BUFFERS parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_BLOCK_SIZE parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_FILES parameter, 7-17

DB_NAME parameter, 29-23

DB_WRITER_PROCESSES parameter, 1-17

DBA role, 27-22

DBA_ views, 2-6

DBA_QUEUE_SCHEDULES view, 18-12

DBA_SYNONYMS.SQL script

using, 2-7

DBA_UPDATABLE_COLUMNS view, 10-16

DBMS, 1-2

general requirements, 1-50

object-relational DBMS, 13-2

DBMS_AQ package, 18-6

DBMS_AQADM package, 18-6, 18-9

DBMS_JOB package, 8-14

Oracle supplied packages, 17-17

DBMS_LOCK package, 24-40

Oracle supplied packages, 17-17

DBMS_PCLXUTIL package, 11-29

DBMS_RLS package

security policies, 27-23

uses definer rights, 27-9

DBMS_SQL package, 15-20

Oracle supplied packages, 17-17

parsing DDL statements, 15-20

DBMS_STATS package, 21-13

creating histograms, 21-12

estimated statistics, 21-15

partition statistics, 11-13

DBWn background process, 8-8

See also database writer process

data definition language

See also Data Definition Language

DDL, 15-4

DDL. See data definition language

dead transactions, 29-4

block-level recovery, 29-15

deadlocks

artificial, 8-20

avoiding, 24-19

defined, 24-17

detection of, 24-18

distributed transactions and, 24-19

deallocating extents, 4-15

decision support systems (DSS), 11-6

bitmap indexes, 10-35

disk striping, 23-49

materialized views, 10-18

parallel DML, 23-38

parallel SQL, 23-2, 23-32, 23-38

partitions, 11-6

performance, 11-9, 23-38

scoring tables, 23-39

dedicated servers, 8-23

defined, 1-16

examples of use, 8-25

multi-threaded servers versus, 8-16

default values, 10-9

constraints effect on, 10-9, 25-24

deferred constraints

deferrable or nondeferrable, 25-24

initially deferred or immediate, 25-24

define phase of query processing, 15-13

definer rights, 17-7

name resolution, 17-20

procedure security, 27-8

degree of parallelism, 23-15, 23-20, 23-23

between query operations, 23-13

parallel SQL, 23-7, 23-16

delete cascade constraint, 25-17

DELETE statement, 15-4

foreign key references, 25-16

freeing space in data blocks, 4-10

no-logging mode, 22-8

LOBs, 22-7

parallel DELETE, 23-22

triggers, 19-2, 19-7

denormalized tables, 1-28, 10-20

dependencies, 20-1

between schema objects, 20-2

function-based indexes, 10-28, 20-7

local, 20-10

managing, 20-1

non-existent referenced objects and, 20-8

on non-existence of other objects, 20-9

Oracle Forms triggers and, 20-13

privileges and, 20-6

remote objects and, 20-10

shared pool and, 20-10

deployment templates, 31-11

instantiation, 31-11

DEQUEUE request, 18-10

dereferencing, 13-10

implicit, 13-10

describe phase of query processing, 15-13

DETERMINISTIC functions

function-based indexes, 20-8

dictionary

See data dictionary

dictionary cache locks, 24-31

dictionary-managed tablespaces, 3-8

different-row writers block writers, 24-11

Digital's POLYCENTER Manager on NetView, 30-33

dimensions, 1-28, 10-20

attributes, 1-28, 10-20

hierarchies, 1-28, 10-20

join key, 1-28, 10-20

normalized or denormalized tables, 1-28, 10-20

direct-load INSERT, 22-2

logging mode, 22-5

parallel INSERT, 22-3

parallel load vs. parallel INSERT, 22-3

restrictions, 22-10, 23-44

serial INSERT, 22-3

space management, 22-9

dirty buffer, 7-4

fast-start checkpointing, 29-14

incremental checkpoint, 8-8

dirty read, 24-3, 24-11

dirty write, 24-11

DISABLE constraints, 25-26

DISABLED indexes, 20-8

disaster recovery, 29-27, 29-28

discrete transaction management

summary, 16-9

discretionary access control, 1-38, 26-2

disk affinity

parallel DML, 23-50

partitions, 23-49

disk failures, 1-45, 29-5

disk space

controlling allocation for tables, 10-4

datafiles used to allocate, 3-16

disk striping

affinity, 23-49

partitions, 11-9

dispatcher processes (Dnnn)

defined, 1-18

described, 8-14

limiting SGA space per session, 26-19

listener process and, 8-14

network protocols and, 8-14

prevent startup and shutdown, 8-21

response queue and, 8-17

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

distributed databases

auditing and, 28-6

client/server architectures and, 6-2

deadlocks and, 24-19

dependent schema objects and, 20-10

distributed queries, 30-34

distributed updates, 30-34

global object names, 30-22

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

management tools, 30-31

message propagation, 18-12

nodes of, 30-6

overview of, 1-34, 30-2

recoverer process (RECO) and, 8-12

remote dependencies, 20-11

remote queries and updates, 30-34

server can also be client in, 6-2

site autonomy of, 30-24

table replication, 1-36

transparency of, 30-44

two-phase commit, 1-35

distributed processing environment

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

data manipulation statements, 15-11

described, 1-32, 6-2

materialized views (snapshots), 10-18

distributed query optimization, 30-47

distributed schema management

replication

distributed schema management, 31-14

distributed transactions

defined, 30-35

parallel DDL restrictions, 23-28

parallel DML restrictions, 23-28, 23-47

routing statements to nodes, 15-12

two-phase commit and, 1-35, 16-8

DISTRIBUTED_TRANSACTIONS parameter, 8-12

data manipulation language

See also Data Manipulation Language

DML, 15-4

DML statements

allowed in distributed transactions, 30-34

DML subpartition locks, 11-46

Dnnn background processes, 8-14

See also dispatcher processes

domain index, 10-47

domain indexes

extensible optimization, 21-18

user-defined statistics, 21-19

drivers, 8-28

DROP COLUMN clause, 10-7

DROP statement, 15-4

DROP TABLE statement

auditing, 28-7

triggers, 19-7

DSS database

disk striping, 23-49

parallel DML, 23-38

partitioning indexes, 11-36

partitions, 11-6

performance, 11-9

scoring tables, 23-39

DUAL table, 2-7

dynamic partitioning, 23-6

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

dynamic predicates

in security policies, 27-24

dynamic SQL

DBMS_SQL package, 15-20

embedded, 15-20

name resolution, 17-21

---

E

embedded SQL, 1-52, 15-6

dynamic SQL in PL/SQL, 15-20

ENABLE constraints, 25-26

enterprise users, 26-2

equipartitioning, 11-23

examples, 11-24, 11-30, 11-31

LOB columns, 11-37

local indexes, 11-29

on one dimension, 11-23

overflow of index-organized tables, 11-41, 11-43

range partitioning, 11-24

errors

in embedded SQL, 15-6

tracked in trace files, 8-15

exceptions

during trigger execution, 19-24

raising, 15-19

stored procedures and, 15-19

EXCHANGE PARTITION, 11-11

exclusive locks

row locks (TX), 24-21

RX locks, 24-24

table locks (TM), 24-22

exclusive mode, 4-28

EXECUTE privilege

verifying user access, 17-19

execution plans

execution sequence of, 21-6

EXPLAIN PLAN, 15-4

location of, 7-8

overview of, 21-2

parsing SQL, 15-12

partitions and partition views, 11-13

plan stability, 21-7

viewing, 21-5

EXP_FULL_DATABASE role, 27-22

EXPLAIN PLAN statement, 15-4

partition pruning, 11-22

explicit locking, 24-32

Export utility, 1-11

copying statistics, 21-9

partition maintenance operations, 11-47

use in backups, 29-27

extended rowid format, 12-17

extensible optimization, 21-18

user-defined costs, 21-19

user-defined selectivity, 21-19

user-defined statistics, 21-19

extents

allocating, 4-14

allocating data blocks for, 4-14

allocation to rollback segments

after segment creation, 4-25

at segment creation, 4-23

allocation, how performed, 4-14

as collections of data blocks, 4-12

coalescing, 4-16

deallocation

from rollback segments, 4-26

when performed, 4-15

defined, 4-2

dictionary managed, 3-8

dropping rollback segments and, 4-26

in rollback segments

changing current, 4-24

incremental, 4-12

locally managed, 3-8

managing, 4-13

materialized views, 4-16

overview of, 4-12

parallel DDL, 23-34

parallel INSERT

storage parameters, 22-9

external procedures, 15-21, 17-12

external reference, 17-8

name resolution, 17-20

---

F

failures, 29-2

archiving redo log files, 29-21

database buffers and, 29-8

described, 1-44, 29-2

instance, 1-45, 29-4

recovery from, 5-9, 5-11, 29-4

internal errors

tracked in trace files, 8-15

media, 1-45, 29-5

network, 29-3

safeguards provided, 29-6

statement and process, 1-44, 8-12, 29-3

survivability, 29-27

user error, 1-44, 29-2

See also recovery

fast commit, 8-10

fast refresh, 10-19

FAST_START_IO_TARGET parameter, 29-14

fast-start

checkpointing, 29-14

fault recovery, 29-14

parallel rollback, 29-15

rollback on demand, 29-10

fetching rows in a query, 15-15

embedded SQL, 15-6

file management locks, 24-31

files

ALERT and trace files, 8-10, 8-15

initialization parameter, 5-4, 5-6

LISTENER.ORA, 6-6

Oracle database, 1-5, 1-8, 29-6

password, 26-14

administrator privileges, 5-3

See also control files, datafiles, redo log files

fine-grained access control, 27-23

FIPS standard, 15-6

fixed views, 2-7

flagging of nonstandard features, 15-6

foreign key constraints

changes in parent key values, 25-16

constraint checking, 25-23

deleting parent table rows and, 25-17

maximum number of columns in, 25-13

nulls and, 25-15

updating parent key tables, 25-16

updating tables, 25-18, 25-19

foreign keys, 1-57

defined, 1-58

partially null, 25-16

privilege to use parent key, 27-5

fragmentation

parallel DDL, 23-34

free lists, 4-10

free space

coalescing extents, 4-14

SMON process, 1-18, 8-11

coalescing within data blocks, 4-10

free lists, 4-10

parameters for data blocks, 4-5

section of data blocks, 4-5

front-ends, 6-2

full table scans

LRU algorithm and, 7-4

parallel execution, 23-5, 23-6

function-based indexes, 10-26

dependencies, 10-28, 20-7

DISABLED, 20-8

privileges, 10-28, 20-8

UNUSABLE, 20-8

functions

function-based indexes, 10-26

hash functions, 10-58

Java

parallel execution, 23-47

PL/SQL, 17-2, 17-6

contrasted with procedures, 1-55, 17-2

DETERMINISTIC, 20-8

parallel execution, 23-47

privileges for, 27-7

roles, 27-20

See also procedures

SQL, 15-3

COUNT, 10-38

default column values, 10-9

in CHECK constraints, 25-21

in views, 10-15

NVL, 10-9

user-defined

extensible optimization, 21-18

fuzzy reads, 24-3

---

G

global database names

shared pool and, 7-12

global indexes

partitioning, 11-31

managing partitions, 11-32, 11-60

summary of index types, 11-33

global naming, 30-15

global schema object names, 1-28

global user

current user links, 17-22

GLOBAL_NAMES initialization parameter, 30-15

GRANT ANY PRIVILEGE system privilege, 27-3

GRANT statement, 15-5

locks, 24-30

granting

privileges and roles, 27-3

GROUP BY clause

temporary tablespaces, 3-12

group commits, 8-10

groups, instance, 23-19

guesses in logical rowids, 12-21

staleness, 12-22

statistics for, 12-22

---

H

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

hash clusters, 1-28, 10-54

allocation of space for, 10-59

collision resolution, 10-57

contrasted with index, 10-55

overview of, 1-28

root blocks, 10-59

single-table hash clusters, 10-61

storage of, 10-55

HASHKEYS parameter, 10-57, 10-61

headers

of data blocks, 4-4

of row pieces, 10-5

heterogeneous services, 30-5

HEXTORAW function, 12-24

HI_SHARED_MEMORY_ADDRESS parameter, 7-14

hierarchies, 1-28, 10-20

join key, 1-28, 10-20

levels, 1-28, 10-20

high water mark

direct-load INSERT, 22-3

hints

extensible optimization, 21-18

PARALLEL, 23-17

PARALLEL_INDEX, 23-17

histograms, 21-9

historical database

maintenance operations, 11-47

partitions, 11-6

HP's OpenView, 30-33

---

I

IBM's NetView/6000, 30-33

ILMS, 15-21

immediate constraints, 25-24

IMP_FULL_DATABASE role, 27-22

implicit dereferencing, 13-10

Import utility, 1-11

copying statistics, 21-9

partition maintenance operations, 11-47

use in recovery, 29-27

incremental checkpoint, 8-8

incremental refresh, 10-19

index segments, 1-7, 4-19

indexes, 1-25, 10-23

auditing partitions, 11-63

bitmap indexes, 10-34, 10-39

nulls and, 10-9

parallel query and DML, 10-35

branch blocks, 10-30

B-tree structure of, 10-29

building

using an existing index, 10-24

cardinality, 10-35

cluster, 10-54

cannot be partitioned, 11-2

contrasted with table, 10-54

dropping, 10-54

composite, 10-24

concatenated, 10-24

described, 1-25, 10-23

domain, 10-45

domain indexes

extensible optimization, 21-18

user-defined statistics, 21-19

enforcing integrity constraints, 25-10, 25-12

extensible, 10-45

function-based, 10-26

dependencies, 10-28, 20-7, 20-9

DETERMINISTIC functions, 20-8

DISABLED, 20-8

optimization with, 10-27

privileges, 10-28, 20-8

global partitioned indexes, 11-31

managing partitions, 11-32, 11-60

index unusable (IU), 11-61

index-organized tables, 10-39

logical rowids, 10-42, 12-20

secondary indexes, 10-42

internal structure of, 10-29

key compression, 10-31

keys and, 10-25

primary key constraints, 25-12

unique key constraints, 25-10

leaf blocks, 10-30

local indexes, 11-29, 11-59

building partitions in parallel, 11-29

location of, 10-28

LONG RAW datatypes prohibit, 12-15

managing partitions, 11-59

no-logging mode, 22-7

nonunique, 10-24

nulls and, 10-9, 10-25, 10-38

on complex data types, 10-45

overview of, 1-25, 10-23

parallel DDL storage, 23-34

parallel index scans, 23-5

partition pruning, 11-4

partitioned tables, 10-39

partitioning guidelines, 11-35

partitions, 11-2, 11-28

performance and, 10-24

privileges for partitions, 11-62

rebuild partition, 11-60

rebuilt after direct-load INSERT, 22-8

reverse key indexes, 10-33

rowids and, 10-30

storage format of, 10-29

unique, 10-24

when used with views, 10-15

index-organized tables, 10-39

applications, 10-43

benefits, 10-41

key compression in, 10-33, 10-41

logical rowids, 10-42, 12-20

parallel CREATE, 23-31

parallel queries, 23-29

partition of, 11-41

partitioned secondary indexes on, 11-44

queue tables, 18-15

rebuild of, 10-43

row overflow area, 10-41

secondary indexes on, 10-42

indextype, 10-46

in-doubt transactions, 4-25, 5-9

information retrieval (IR) applications

index-organized tables, 10-44

initialization parameter file, 5-4, 5-6

example, 5-4

startup, 5-6

initialization parameters

AQ_TM_PROCESS, 18-8, 18-9

BUFFER_POOL_KEEP, 7-6

BUFFER_POOL_RECYCLE, 7-6

COMPATIBLE, 3-11

DB_BLOCK_BUFFERS, 7-5, 7-13

DB_BLOCK_SIZE, 7-5, 7-13

DB_FILES, 7-17

DB_NAME, 29-23

DB_WRITER_PROCESSES, 1-17

DISTRIBUTED_TRANSACTIONS, 8-12

FAST_START_IO_TARGET, 29-14

HI_SHARED_MEMORY_ADDRESS, 7-14

JOB_QUEUE_PROCESSES, 18-12

LICENSE_MAX_SESSIONS, 26-21

LICENSE_SESSIONS_WARNING, 26-21

LOCK_SGA, 7-14, 7-18

LOG_ARCHIVE_MAX_PROCESSES, 1-18, 8-13, 29-20

LOG_ARCHIVE_START, 29-20

LOG_BUFFER, 7-6, 7-13

LOG_CHECKPOINT_INTERVAL, 29-14

LOG_CHECKPOINT_TIMEOUT, 29-14

MTS_MAX_SERVERS, 8-20, 8-21

MTS_SERVERS, 8-20

NLS_LANGUAGE, 11-20

NLS_NUMERIC_CHARACTERS, 12-9

NLS_SORT, 11-20

OPEN_CURSORS, 7-9, 15-7

OPEN_LINKS, 7-17

OPTIMIZER_PERCENT_PARALLEL, 21-9

PARALLEL_MAX_SERVERS, 23-8

PARALLEL_MIN_PERCENT, 23-19

PARALLEL_MIN_SERVERS, 23-7, 23-9

PARALLEL_SERVER, 5-7

REMOTE_DEPENDENCIES_MODE, 20-11

ROLLBACK_SEGMENTS, 4-28

SERVICE_NAMES, 6-6

SHARED_MEMORY_ADDRESS, 7-14

SHARED_POOL_SIZE, 7-7, 7-13

SKIP_UNUSABLE_INDEXES, 20-8

SORT_AREA_RETAINED_SIZE, 7-17

SORT_AREA_SIZE, 4-20, 7-17

SQL_TRACE, 8-15

TRANSACTIONS, 4-28

TRANSACTIONS_PER_ROLLBACK_SEGMENT, 4-28

USE_INDIRECT_DATA_BUFFERS, 7-14

initially deferred constraints, 25-24

initially immediate constraints, 25-24

INIT.ORA. See initialization parameter file.

inline views, 10-17

example, 10-17

INSERT statement, 15-4

direct-load INSERT, 22-2

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

free lists, 4-10

parallelizing INSERT ... SELECT, 23-23

storage for parallel INSERT, 22-9

triggers, 19-2, 19-7

BEFORE triggers, 19-10

instance groups for parallel operations, 23-19

instance recovery, 29-4

instance failure, 1-45, 29-4

read-only tablespaces, 29-6

SMON process, 1-17, 8-11, 23-42

See also crash recovery

instances, 1-4

aborting, 5-10, 29-4

acquire rollback segments, 4-28

associating with databases, 5-3, 5-6

defined, 1-14

described, 5-2

diagrammed, 8-6

failure in, 1-45, 29-4

instance groups, 23-19

memory structures of, 7-2

multiple-process, 8-2

overview of, 1-4

process structure, 8-2

recovery of, 5-11, 29-4

fast-start checkpointing, 29-14

opening a database, 5-9

SMON process, 8-11

restricted mode, 5-6

service names, 6-6

sharing databases, 1-4

shutting down, 5-10, 5-11

audit record, 28-5

starting, 5-5

audit record, 28-5

system identifiers (SIDs), 6-6

virtual memory, 7-18

instantiation, 31-11

INSTEAD OF triggers, 19-13

nested tables, 14-5

object views, 14-5

integrity constraints, 25-2

default column values and, 10-9

See also constraints

integrity rules, 1-21

parallel DML restrictions, 23-46

Inter-Language Method Services (ILMS), 15-21

INTERNAL connection, 5-3

statement execution not audited, 28-4

internal errors tracked in trace files, 8-15

inter-operator parallelism, 23-13

intra-operator parallelism, 23-13

INVALID status, 20-3

invoker rights, 17-7

name resolution, 17-20

procedure security, 27-8

supplied packages, 27-9

IS NULL predicate, 10-9

ISO SQL standard, 1-3, 12-23

composite foreign keys, 25-16

isolation levels

choosing, 24-13

read committed, 24-8

setting, 24-8, 24-32

---

J

Java

triggers, 19-1, 19-8

Java Messaging Service, 18-4, 18-14

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

message propagation, 18-12

JOB_QUEUE_PROCESSES parameter, 18-12

jobs, 8-2

join views, 10-16

joins

cluster, 10-52

encapsulated in views, 1-23, 10-14

join order

execution plans, 21-2

selectivity of predicates, 21-9, 21-10, 21-19

partition-wise, 11-5

views, 1-23, 10-16

---

K

key compression, 10-31

keys

cluster, 1-26, 10-52

defined, 25-9

foreign, 25-13

hash, 10-57, 10-61

in constraints, 1-58

indexes and, 10-25

compression, 10-31

PRIMARY KEY constraints, 25-12

reverse key, 10-33

UNIQUE constraints, 25-10

key values, 1-58

maximum storage for values, 10-25

parent, 25-13, 25-15

primary, 25-11

referenced, 1-58, 25-13

reverse key indexes, 10-33

unique, 25-8

composite, 25-9, 25-11

---

L

large pool, 7-12

overview of, 1-15

latches

described, 24-31

LCK0 background process, 1-18, 8-13

leaf blocks, 10-30

least recently used (LRU) algorithm

database buffers and, 7-4

dictionary cache, 2-4

full table scans and, 7-4

latches, 8-8

shared SQL pool, 7-8, 7-11

LGWR background process, 8-9

See also log writer process

library cache, 7-7, 7-8, 7-10

LICENSE_MAX_SESSIONS parameter, 26-21

LICENSE_SESSIONS_WARNING parameter, 26-21

licensing

concurrent usage, 26-21

named user, 26-22

viewing current limits, 26-22

lightweight sessions, 26-9

LISTENER.ORA file, 6-6

listeners, 6-6, 8-14

service names, 6-6

LOB datatypes, 12-12

BFILE, 12-14

BLOBs, 12-13

CLOBs and NCLOBs, 12-14

default logging mode, 22-8

NOLOGGING mode, 22-7

restrictions

parallel DDL, 23-32

parallel DML, 23-45

local databases, 1-34

local indexes, 11-29, 11-33

bitmap indexes

on partitioned tables, 10-39

parallel query and DML, 10-35

building partitions in parallel, 11-29

equipartitioning, 11-29

managing partitions, 11-59

locally-managed tablespaces, 3-8

temporary tablespaces, 3-13

location transparency, 1-34

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

LOCK TABLE statement, 15-4

LOCK_SGA parameter, 7-14, 7-18

locking

indexed foreign keys and, 25-19

unindexed foreign keys and, 25-18

locks, 1-31, 24-3

after committing transactions, 16-6

automatic, 1-32, 24-16, 24-19

conversion, 24-17

data, 24-20

duration of, 24-16

deadlocks, 24-17, 24-18

avoiding, 24-19

dictionary, 24-29

clusters and, 24-30

duration of, 24-30

dictionary cache, 24-31

DML acquired, 24-28

diagrammed, 24-26

DML partition locks, 11-45

escalation does not occur, 24-17

exclusive table locks (X), 24-26

file management locks, 24-31

how Oracle uses, 24-15

internal, 24-30

latches and, 24-31

log management locks, 24-31

manual, 1-32, 24-32

examples of behavior, 24-33

object level locking, 13-15

Oracle Lock Management Services, 24-40

overview of, 1-31, 24-3

parallel cache management (PCM), 24-20

parallel DML, 23-43

parse, 15-12, 24-30

rollback segment, 24-31

row (TX), 24-21

block-level recovery, 29-15

row exclusive locks (RX), 24-24

row share table locks (RS), 24-24

share row exclusive locks (SRX), 24-25

share table locks (S), 24-25

share-subexclusive locks (SSX), 24-25

subexclusive table locks (SX), 24-24

subshare table locks (SS), 24-24

table (TM), 24-22

table lock modes, 24-22

tablespace, 24-31

types of, 24-19

log entries, 1-9, 29-9

See also redo log files, 1-9

log management locks, 24-31

log sequence numbers, 1-47

log switch

ALTER SYSTEM SWITCH LOGFILE, 8-12

archiver process, 1-18, 8-12

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

archiving modes, 29-19

group commits, 8-10

manual archiving and, 29-21

redo log buffers and, 7-6

starting new ARCn processes, 8-13

system change numbers, 16-6

write-ahead, 8-9

LOG_ARCHIVE_MAX_PROCESSES parameter, 1-18, 8-13

automatic archiving, 29-20

LOG_ARCHIVE_START parameter, 29-20

LOG_BUFFER parameter, 7-6

system global area size and, 7-13

LOG_CHECKPOINT_INTERVAL parameter, 29-14

LOG_CHECKPOINT_TIMEOUT parameter, 29-14

logging mode

direct-load INSERT, 22-5

NOARCHIVELOG mode and, 22-6

parallel DDL, 23-31, 23-33

partitions, 11-58

SQL operations affected by, 22-7

logical blocks, 4-2

logical database structures, 1-5

tablespaces, 3-6

logical reads limit, 26-18

logical rowids, 12-20

index on index-organized table, 10-42

physical guesses, 10-42, 12-21

staleness of guesses, 12-22

statistics for guesses, 12-22

LONG datatype

automatically the last column, 10-8

defined, 12-7

partitioning restriction, 11-12

storage of, 10-8

LONG RAW datatype, 12-15

indexing prohibited on, 12-15

partitioning restriction, 11-12

similarity to LONG datatype, 12-15

LRU, 7-4, 8-8

dictionary cache, 2-4

shared SQL pool, 7-8, 7-11

---

M

managed standby database, 29-28

manual locking, 1-32, 24-32

map methods, 1-56, 13-7

massively parallel processing (MPP)

affinity, 23-6, 23-49, 23-50

multiple Oracle instances, 5-3

parallel SQL execution, 23-2

master definition site, 31-5

master groups, 31-5

master sites, 31-5

matching foreign keys

full, partial, or none, 25-16

materialized view logs, 10-19

materialized views, 10-18

deallocating extents, 4-16

materialized view logs, 10-19

overview, 1-23

partitioned, 10-19, 11-2

refreshing, 10-19

same as snapshots, 1-24

MAXEXTENTS UNLIMITED storage parameter, 23-41

MAXVALUE

partitioned tables and indexes, 11-20

media failure, 1-45, 29-5

memory

allocation for SQL statements, 7-11

content of, 7-2

cursors (statement handles), 1-15

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

overview of structures in, 1-12

processes use of, 8-2

shared SQL areas, 7-8

software code areas, 7-18

sort areas, 7-17

stored procedures, 17-9, 17-18

structures in, 7-2

system global area (SGA)

allocation in, 7-2

initialization parameters, 7-13, 7-14

locking into physical memory, 7-14, 7-18

SGA size, 7-13

starting address, 7-14

virtual, 7-18

See also system global area

merging partitions, 11-14

message queuing, 18-2

exporting queue tables, 18-15

messages, 18-6

publish-subscribe support

event publication, 19-19

publish/subscribe support, 18-13

queue monitor process, 1-19, 8-14, 18-8

interval statistics, 18-14

window of execution, 18-9

queue tables, 18-6

recipients, 18-7

rule-based subscriptions, 18-8

subscription lists, 18-7

remote databases, 18-12

methods

comparison methods, 13-7

constructor methods, 13-6

privileges on, 27-12

methods of collections

constructor methods, 1-56

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

constructor methods, 1-56

map methods, 1-56, 13-7

order methods, 1-56, 13-7

PL/SQL, 13-14

purchase order example, 13-2, 13-5

selfish style of invocation, 13-6

MINIMUM EXTENT parameter, 23-34

mobile computing environment

materialized views, 10-18

modes

archive log, 29-18

table lock, 24-22

two-task, 8-3

monitoring user actions, 1-43, 28-2

MOVE PARTITION statement

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

rules of parallelism, 23-25

MPP. See massively parallel processing

MTS. See multi-threaded server

MTS_MAX_SERVERS parameter, 8-20

artificial deadlocks and, 8-21

MTS_SERVERS parameter, 8-20

multiblock writes, 8-8

multimaster replication, 31-5

multimedia datatypes, 13-3

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

multiplexing

control files, 1-48, 29-23

recovery and, 29-5

redo log files, 1-47

multi-threaded server, 8-16

artificial deadlocks in, 8-20

dedicated server contrasted with, 8-16

described, 8-3, 8-16

dispatcher processes, 1-18, 8-14

example of use, 8-21

limiting private SQL areas, 26-19

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

parallel SQL execution, 23-8

private SQL areas, 7-9

sort areas, 7-17

processes needed for, 8-16

restricted operations in, 8-21

server processes, 1-16, 8-20

session information, 7-15

session memory in the large pool, 7-12

shared server processes, 8-15, 8-20

multiuser environments, 1-2, 8-2

multiversion concurrency control, 24-6

multiversion consistency model, 1-30

mutating errors and triggers, 19-24

---

N

name resolution

in distributed databases, 30-22

name resolution for procedures, 17-20

named user licensing, 26-22

National Language Support (NLS)

character sets for, 12-6

CHECK constraints and, 25-21

clients and servers may diverge, 30-47

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

NCHAR and NVARCHAR2 datatypes, 12-7

NCLOB datatype, 12-14

parameters, 5-5

views and, 10-15

NCHAR datatype, 12-7

NCLOB datatype, 12-14

nested tables, 10-10, 13-12

index-organized tables, 10-41

key compression, 10-33

INSTEAD OF triggers, 14-5

restrictions, 23-30

updating in views, 14-5

Net8, 1-19, 1-36, 6-5

applications and, 6-5

client/server systems use of, 6-5

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

overview of, 6-5

NET81, 30-13

network listener process, 6-6

connection requests, 8-14, 8-16

dedicated server example, 8-26

multi-threaded server example, 8-22

service names, 6-6

networks

client/server architecture use of, 6-2

communication protocols, 6-5, 8-28

dispatcher processes and, 8-14, 8-16

distributed databases use of, 30-2

drivers, 8-28

failures of, 29-3

listener processes of, 6-6, 8-14

Net8, 6-5

network authentication service, 26-4

two-task mode and, 8-24, 8-25

using Oracle on, 1-19, 1-36

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

NOARCHIVELOG mode, 29-18

database backups for recovery, 29-25

defined, 29-18

LOGGING mode and, 22-6

overview, 1-47

NOAUDIT statement, 15-5

locks, 24-30

nodes

disk affinity in a Parallel Server, 23-49

of distributed databases, 1-34

NOLOGGING mode

direct-load INSERT, 22-5

parallel DDL, 23-31, 23-33

partitions, 11-58

SQL operations affected by, 22-7

non-persistent queues, 18-12

nonprefixed indexes, 11-30, 11-34

global partitioned indexes, 11-32

nonrepeatable reads, 24-3, 24-11

nonunique indexes, 10-24

NOREVERSE clause for indexes, 10-33

normalized tables, 1-28, 10-20

NOT NULL constraints

constraint checking, 25-23

defined, 25-7

implied by PRIMARY KEY, 25-12

UNIQUE keys and, 25-11

NOVALIDATE constraints, 25-26

Novell's NetWare Management System, 30-33

nulls

as default values, 10-9

column order and, 10-8

converting to values, 10-9

defined, 10-8

foreign keys and, 25-15, 25-16

how stored, 10-8

indexes and, 10-9, 10-25, 10-38

inequality in UNIQUE key, 25-11

non-null values for, 10-9

partitioned tables and indexes, 11-20

prohibited in primary keys, 25-11

prohibiting, 25-7

UNIQUE key constraints and, 25-11

unknown in comparisons, 10-9

NUMBER datatype, 12-8

internal format of, 12-9

rounding, 12-9

NVARCHAR2 datatype, 12-7

NVL function, 10-9

n-way replication. See multimaster replication

---

O

object cache

object views, 14-4

OCI, 13-14

Pro*C, 13-14

object identifiers, 14-3, 14-4

collections

key compression, 10-33, 10-41

for object views, 14-3, 14-4

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

object privileges, 27-3

See also schema object privileges

object tables, 13-3, 13-8

row objects, 13-8

virtual object tables, 14-2

Object Type Translator (OTT), 13-15

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

attributes of, 13-2, 13-4

column objects, 13-8

comparison methods for, 13-7

constructor methods for, 1-56, 13-6

locking in cache, 13-15

message queuing, 18-9

methods of, 1-56, 13-4

PL/SQL, 13-14

purchase order example, 13-2, 13-5

object views, 10-17

Oracle type translator, 13-15

parallel query, 23-30

restrictions, 23-30

purchase order example, 13-2, 13-4

restrictions

parallel DDL, 23-32

parallel DML, 23-45

row objects, 13-8

object views, 10-17, 14-1

advantages of, 14-2

defining, 14-3

modifiability, 19-13

nested tables, 14-5

object identifiers for, 14-3, 14-4

updating, 14-5

use of INSTEAD OF triggers with, 14-5

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

objects

privileges on, 27-12

OCI, 8-27

anonymous blocks, 15-18

bind variables, 15-14

object cache, 13-14

OCIObjectFlush, 14-4

OCIObjectPin, 14-4

stored procedures, 15-19

ODCIIndex, 10-46

offline backups

whole database backup, 29-24

offline redo log files, 1-47, 29-7

OLTP database, 11-6

batch jobs, 23-39

parallel DML, 23-38

partitioning indexes, 11-35

partitions, 11-7

online analytical processing (OLAP)

index-organized tables, 10-45

online redo log, 1-46, 29-7

archiving, 29-19, 29-20

checkpoints, 29-23

media failure, 29-5

multiplexed, 29-5

recorded in control file, 29-22

online transaction processing (OLTP), 11-6

reverse key indexes, 10-33

OPEN_CURSORS parameter, 15-7

managing private SQL areas, 7-9

OPEN_LINKS parameter, 7-17

operating systems

authentication by, 26-4

block size, 4-3

communications software, 8-28

privileges for administrator, 5-3

roles and, 27-23

operations in a relational database, 1-21

OPS. See Oracle Parallel Server

OPTIMAL storage parameter, 4-26

optimization, 21-2

cost-based, 21-8

histograms, 21-9

user-defined costs, 21-19

described, 21-2

execution plan for partitions, 11-13

extensible optimizer, 21-18

function-based indexes, 10-27

index build, 10-24

parallel SQL, 23-10

partition pruning, 11-4

indexes, 11-35

partition pruning (elimination), 11-4

partitioned indexes, 11-34

partition-wise joins, 11-5

plan stability, 21-7

query rewrite, 10-18

in security policies, 27-24

rule-based, 21-20

selectivity of predicates, 21-9

histograms, 21-9, 21-11

user-defined, 21-19

statistics, 21-9

user-defined, 21-19

OPTIMIZER_PERCENT_PARALLEL parameter, 21-9

Oracle

adherence to standards, 1-3

integrity constraints, 25-5

architecture, 1-12

client/server architecture of, 6-2

compatibility, 1-3

compatibility levels, 3-15

configurations of, 8-2

multiple-process Oracle, 8-2

connectibility, 1-2

data access, 1-50

examples of operations, 1-19

dedicated server, 8-25

multi-threaded server, 8-21

features, 1-2

instances, 1-4, 1-14, 5-2

licensing of, 26-20

Oracle server, 1-4

Parallel Server option, 1-4

See also Parallel Server

portability, 1-3

processes of, 1-16, 8-5

scalability of, 6-4

SQL processing, 15-9

using on networks, 1-2, 1-36

Oracle Advanced Queuing. See Advanced Queuing

Oracle blocks, 1-7, 4-2

See also data blocks

Oracle Call Interface. See OCI

Oracle Certificate Authority, 26-5

Oracle code, 8-2, 8-27

Oracle Data Cartridge Interface, 10-46

Oracle Enterprise Login Assistant, 26-6

Oracle Enterprise Manager

ALERT file, 8-15

checkpoint statistics, 8-11

executing a package, 17-6

executing a procedure, 17-4

granting roles, 27-19

granting system privileges, 27-3

lock and latch monitors, 24-30

parallel recovery, 29-11

PL/SQL, 15-18, 15-19

schema object privileges, 27-4

showing size of SGA, 7-13

shutdown, 5-10, 5-11

SQL statements, 15-2

startup, 5-6

statistics monitor, 26-20

Oracle Enterprise Security Manager, 26-5

Oracle Forms

object dependencies and, 20-13

PL/SQL, 15-17

Oracle Internet Directory, 26-5

Oracle Parallel Server, 1-4

concurrency limits and, 26-22

databases and instances, 5-3

disk affinity, 23-49

distributed locks, 24-20

DML locks and performance, 11-46

exclusive mode

rollback segments and, 4-28

file and log management locks, 24-31

instance groups, 23-19

isolation levels, 24-12

lock processes, 1-18, 8-13

mounting a database using, 5-7

named user licensing and, 26-23

parallel SQL, 23-1

PCM locks, 24-20

read consistency, 24-7

reverse key indexes, 10-33

shared mode

rollback segments and, 4-28

system change numbers, 8-10

system monitor process and, 8-11, 23-42

temporary tablespaces, 3-12

Oracle program interface (OPI), 8-28

Oracle Replication Manager, 31-13

Oracle server, 1-4

See also Oracle

Oracle type translator (OTT), 13-15

Oracle Wallet Manager, 26-5

Oracle wallets, 26-5

Oracle XA

session memory in the large pool, 7-12

ORDBMS, 1-21, 13-2

order methods, 1-56, 13-7

OTT. See Object Type Translator (OTT)

OUTLN schema

DBA privileges, 21-7

---

P

P code, 17-18

packages, 17-4, 17-12

advantages of, 17-16

as program units, 1-55

auditing, 28-8

dynamic SQL, 15-20

examples of, 17-12, 27-10, 27-11

executing, 15-17, 17-19

for locking, 24-40

OUTLN_PKG, 21-7

private, 17-16

privileges

divided by construct, 27-10

executing, 27-7, 27-10

public, 17-16

queuing, 18-6

session state and, 20-6

shared SQL areas and, 7-10

storing, 17-17

supplied packages, 17-17

invoker or definer rights, 27-9

validity of, 17-19

pages, 4-2

parallel backup operations, 29-17

PARALLEL clause

parallelization rules, 23-20

parallel DDL, 23-31

extent allocation, 23-34

functions, 23-47

parallelism types, 23-3

parallelization rules, 23-20

partitioned tables and indexes, 23-31

building local indexes, 11-29

restrictions

LOBs, 23-32

object types, 23-30, 23-32

parallel DELETE, 23-21, 23-22

parallel DML, 23-36

applications, 23-38

bitmap indexes, 10-35

degree of parallelism, 23-20, 23-23

enabling PARALLEL DML, 23-39

functions, 23-47

lock and enqueue resources, 23-43

parallelism types, 23-3

parallelization rules, 23-20

recovery, 23-41

restrictions, 23-44

object types, 23-30, 23-45

remote transactions, 23-47

rollback segments, 23-41

transaction model, 23-40

parallel execution, 23-2

coordinator, 22-3, 23-6

direct-load INSERT, 22-3

full table scans, 23-5

inter-operator parallelism, 23-13

intra-operator parallelism, 23-13

partitioned tables and indexes, 23-4

server, 22-3, 23-6

index maintenance, 22-8

temporary segments, 22-9

servers, 23-6

direct-load INSERT

index maintenance, 22-8temporary segments, 22-9

See also parallel SQL

parallel execution servers

direct-load INSERT, 22-3

PARALLEL hint, 23-17

parallelization rules, 23-20

UPDATE and DELETE, 23-22

parallel query, 23-28

bitmap indexes, 10-35

functions, 23-47

index-organized tables, 23-29

object types, 23-30

restrictions, 23-30

parallelization rules, 23-20

parallel recovery, 29-11, 29-17

PARALLEL SERVER parameter, 5-7

Parallel Server. See Oracle Parallel Server

parallel SQL, 23-2

allocating rows to parallel execution servers, 23-11

coordinator process, 23-6

direct-load INSERT, 22-3

degree of parallelism, 23-16

instance groups, 23-19

multi-threaded server, 23-8

number of parallel execution servers, 23-7

optimizer, 23-10

Parallel Server and, 23-1

parallelization rules, 23-20

server processes, 23-6

direct-load INSERT, 22-3, 22-8, 22-9

summary or rollup tables, 23-32

See also parallel execution

parallel UPDATE, 23-21, 23-22

PARALLEL_INDEX hint, 23-17

PARALLEL_MAX_SERVERS parameter, 23-8

PARALLEL_MIN_PERCENT parameter, 23-19

PARALLEL_MIN_SERVERS parameter, 23-7, 23-9

parallelism

degree, 23-15

parameters

initialization, 5-4

locking behavior, 24-19

See also initialization parameters

National Language Support, 5-5

storage, 4-5, 4-12

parse trees, 17-18

construction of, 15-8

in shared SQL area, 7-8

stored in database, 17-18

parsing, 15-12

DBMS_SQL package, 15-20

embedded SQL, 15-6

parse calls, 15-8

parse locks, 15-12, 24-30

performed, 15-8

SQL statements, 15-12, 15-20

partial backups, 29-26

PARTITION clause, 11-63

partition views, 11-11

partitioning

columns, 11-13

LOBs

DML locks, 11-45

maintenance operations, 11-56

tables with LOB columns, 11-37

partitions, 11-2, 11-11

advantages of, 11-5, 11-7

affinity, 23-49

basic partitioning model, 11-11

bitmap indexes, 10-39

concurrent maintenance operations, 11-49

DATE datatype, 11-12, 11-21

DML partition locks, 11-45

dynamic partitioning, 23-6

equipartitioning, 11-23

examples, 11-24, 11-30, 11-31

LOB columns, 11-37

local indexes, 11-29

on one dimension, 11-23

overflow of index-organized tables, 11-41, 11-43

range partitioning, 11-24

EXCHANGE PARTITION, 11-11

execution plan, 11-13

global indexes, 11-31, 11-60

hash partitioning, 11-15

local indexes, 11-29, 11-59

building in parallel, 11-29

LONG and LONG RAW restriction, 11-12

maintenance operations, 11-47

materialized views, 10-19, 11-2

merging, 11-14

multi-column keys, 11-22

no-logging mode, 22-7

nonprefixed indexes, 11-30, 11-34

OLTP databases, 11-7

parallel DDL, 23-31

parallel queries, 23-4

partition bounds, 11-19

partition elimination, 11-4

partition names, 11-18

partition pruning, 11-4

DATE datatype, 11-21

disk striping and, 23-49

indexes, 11-35

parallelizing by block range, 23-4

partition transparency, 11-11

partition-extended table names, 11-63

partitioning indexes, 11-28, 11-35

partitioning keys, 11-13, 11-18

partitioning tables, 11-26

partition-wise joins, 11-5

physical attributes, 11-26, 11-36

prefixed indexes, 11-30

range partitioning, 11-13

disk striping and, 23-49

rebuild partition, 11-60

referencing a partition, 11-18

restrictions

bitmap indexes, 11-13

datatypes, 11-12, 11-21

partition-extended table names, 11-64

rules of parallelism, 23-25, 23-27

segments, 4-18, 4-19

statistics, 11-13, 21-12

VLDB, 11-5

passwords

account locking, 26-7

administrator privileges, 5-3

complexity verification, 26-8

connecting with, 8-4

connecting without, 26-4

database user authentication, 26-7

encryption, 26-7

expiration, 26-8

password files, 26-14

password reuse, 26-8

used in roles, 1-41

PCTFREE storage parameter

how it works, 4-6

PCTUSED and, 4-8

PCTUSED storage parameter

how it works, 4-7

PCTFREE and, 4-8

peer-to-peer replication. See multimaster replication

performance

clusters and, 10-52

constraint effects on, 25-7

DSS database, 11-9, 23-38

dynamic performance tables (V$), 2-7

group commits, 8-10

index build, 10-24

I/O, 11-9

Oracle Parallel Server and DML locks, 11-46

packages, 17-16

parallel recovery and, 29-11

partitions, 11-9

prefixed and nonprefixed indexes, 11-34

recovery, 29-14

resource limits and, 26-17

SGA size and, 7-13

sort operations, 3-12

structures that improve, 1-25, 1-26

viewing execution plans, 21-5

persistent areas, 7-8

persistent queuing, 18-3

PGA, 1-15, 7-15

multi-threaded server, 8-20

phantom reads, 24-3, 24-11

physical database structures, 1-8

control files, 1-10, 29-22

datafiles, 1-8, 3-16

redo log files, 1-9, 29-7

physical guesses in logical rowids, 12-21

staleness, 12-22

statistics for, 12-22

PKI, 26-5

plan

SQL execution, 15-4, 15-12

plan stability for optimization, 21-7

PL/SQL, 15-16

anonymous blocks, 15-16, 17-11

auditing of statements within, 28-4

bind variables

user-defined types, 13-14

database triggers, 19-1

datatypes, 12-2

dynamic SQL, 15-20

exception handling, 15-19

executing, 15-16, 17-19, 17-20

external procedures, 15-21, 17-12

gateway, 15-21

language constructs, 15-18

object views, 14-4

overview of, 1-55, 15-16

packages, 17-4, 17-12

parse locks, 24-30

parsing DDL statements, 15-20

partition-extended table names, 11-65

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

compiler, 17-17

executing a procedure, 17-20

products containing, 15-17

program units, 1-24, 7-10, 15-16, 17-2

compiled, 15-17, 17-11, 17-17

shared SQL areas and, 7-10

roles in procedures, 27-20

stored procedures, 1-24, 15-16, 17-2, 17-6

user locks, 24-40

user-defined datatypes, 13-13

PL/SQL Server Pages, 15-21

PMON background process, 8-12

See also process monitor process

point-in-time recovery

clone database, 5-8

point-to-point model, 18-4

portability, 1-3

precompilers

anonymous blocks, 15-18

bind variables, 15-14

cursors, 15-12

embedded SQL, 15-6

FIPS flagger, 15-6

stored procedures, 15-19

predicates

dynamic

in security policies, 27-24

partition pruning, 11-4

indexes, 11-35

selectivity, 21-9

histograms, 21-10, 21-11

user-defined, 21-19

prefixed indexes, 11-30, 11-33

prefixes of data dictionary views, 2-5

pre-spawned dedicated processes, 8-27

PRIMARY KEY constraints, 25-11

constraint checking, 25-23

described, 25-11

indexes used to enforce, 25-12

name of, 25-12

maximum number of columns, 25-12

NOT NULL constraints implied by, 25-12

primary keys, 1-58, 25-11

advantages of, 25-11

defined, 25-3

private rollback segments, 4-27

private SQL areas

cursors and, 7-9

described, 7-8

how managed, 7-9

persistent areas, 7-8

runtime areas, 7-9

privileges

administrator, 5-3

connections audited, 28-5

OUTLN schema, 21-7

statement execution not audited, 28-4

auditing use of, 1-43, 28-7

checked when parsing, 15-12

function-based indexes, 10-28, 20-8

granting, 1-40, 27-3, 27-4

examples of, 27-10, 27-11

grouping into roles, 1-40

overview of, 1-40, 27-2

partitioned tables and indexes, 11-62

procedures, 27-7

creating and altering, 27-9

executing, 17-19, 27-7

in packages, 27-10

RESTRICTED SESSION, 26-22

revoked

object dependencies and, 20-6

revoking, 27-3, 27-4

roles, 27-17

restrictions on, 27-21

schema object, 27-3

DML and DDL operations, 27-5

granting and revoking, 27-4

overview of, 1-40

packages, 27-10

procedures, 27-7

system, 27-2

granting and revoking, 27-3

overview of, 1-40

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

trigger privileges, 27-8

views, 27-6

creating, 27-6

using, 27-6

Pro*C/C++

processing SQL statements, 15-11

user-defined datatypes, 13-14

procedural replication, 31-16

detecting conflicts, 31-17

wrapper, 31-16

procedures, 15-16, 17-1, 17-6, 20-8

advantages of, 17-8

auditing, 28-8

contrasted with anonymous blocks, 17-11

contrasted with functions, 1-55, 17-2

cursors and, 15-19

definer rights, 17-7, 27-8

roles disabled, 27-20

dependency tracking in, 20-6

examples of, 17-6, 27-10, 27-11

executing, 15-17, 17-19

external procedures, 15-21, 17-12

external reference in, 17-8, 17-20

INVALID status, 20-3, 20-6

invoker rights, 17-7, 27-8

roles used, 27-21

supplied packages, 27-9

prerequisites for compilation of, 20-5

privileges

create or alter, 27-9

executing, 27-7

executing in packages, 27-10

remote calls, 30-46

security enhanced by, 17-8, 27-8

shared SQL areas and, 7-10

stored procedures, 15-16, 15-19, 17-2

storing, 17-17

supplied packages, 17-17

invoker or definer rights, 27-9

triggers, 19-2

validity of, 17-19

process global area (PGA), 7-15

See also program global area

process monitor process (PMON)

cleans up timed-out sessions, 26-19

described, 1-18, 8-12

network failure, 29-3

parallel DML process recovery, 23-42

process failure, 29-3

processes, 8-2

archiver (ARCn), 1-18, 8-12, 29-20

background, 1-16, 8-5

diagrammed, 8-6

block server (BSP), 24-7

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

checkpoints and, 8-8

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

dedicated server, 8-20

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

distributed transaction resolution, 8-12

during recovery, 29-12

failure in, 29-3

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

message propagation, 18-12

listener, 6-6, 8-14

shared servers and, 8-16

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

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

multiple-process Oracle, 8-2

multi-threaded server, 8-16

artificial deadlocks and, 8-20

client requests and, 8-17

Oracle, 1-16, 8-5

overview of, 1-15

parallel execution coordinator, 23-6

direct-load INSERT, 22-3

parallel execution servers, 23-6

direct-load INSERT, 22-3, 22-8, 22-9

pre-spawned, 8-27

process monitor (PMON), 1-18, 8-12

queue monitor (QMNn), 1-19, 8-14, 18-8

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

and in-doubt transactions, 1-35

server, 1-16, 1-33, 8-5

dedicated, 8-23

shared, 8-14, 8-15, 8-20

shadow, 8-23

structure, 8-2

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

trace files for, 8-15

user, 1-15, 8-4

allocate PGAs, 7-15

manual archiving by, 29-22

recovery from failure of, 8-12

sharing server processes, 8-14, 8-15

processing

DDL statements, 15-15

distributed, 1-32

DML statements, 15-11

overview, 15-9

parallel SQL, 23-2

queries, 15-13

profiles

overview of, 1-42

password management, 26-8

when to use, 26-20

program global area (PGA), 1-15, 7-15

allocation of, 7-15

contents of, 7-15

multi-threaded servers, 8-20

nonshared and writable, 7-15

size of, 7-16

program interface, 8-27

Oracle side (OPI), 8-28

overview of, 1-19

structure of, 8-27

two-task mode in, 8-25

user side (UPI), 8-27

program units, 1-24, 15-16, 17-2

prerequisites for compilation of, 20-5

shared pool and, 7-10

propagation scheduling capabilities, 18-12

proxies, 26-9

pruning partitions, 11-4, 23-4, 23-49

DATE datatype, 11-21

EXPLAIN PLAN, 11-22

index partitions, 11-4

indexes, 11-35

pseudocode, 17-18

triggers, 19-26

pseudocolumns

CHECK constraints prohibit

LEVEL and ROWNUM, 25-21

modifying views, 19-14

ROWID, 12-16

USER, 27-7

PSP. See PL/SQL Server Pages

public key infrastructure, 26-5

public rollback segments, 4-27

PUBLIC user group, 26-16, 27-20

validity of procedures, 17-19

publication

DDL statements, 19-21

DML statements, 19-21

logon/logoff events, 19-20

system events

server errors, 19-20

startup/shutdown, 19-20

using triggers, 19-19

publish-subscribe model, 18-5

publish-subscribe support, 18-13

event publication, 19-19

listen capability, 18-14

message propagation, 18-12

rule-based subscriber, 18-8

triggers, 19-19

purchase order example

object types, 13-2, 13-4

---

Q

QMNn background process, 1-19, 8-14, 18-8

interval statistics, 18-14

window of execution, 18-9

queries

ad hoc, 23-32

composite indexes, 10-24

default locking of, 24-27

define phase, 15-13

describe phase, 15-13

distributed, 30-34

distributed or remote, 30-34

fetching rows, 15-13

in DML, 15-4

index scans parallelized by partition, 23-5

inline views, 10-17

location transparency and, 30-45

merged with view queries, 10-15

parallel processing, 23-2

phases of, 24-5

processing, 15-13

read consistency of, 1-31, 24-6

SAMPLE clause

cost-based optimization, 21-17

stored as views, 1-22, 10-12

table scans parallelized by rowid, 23-4

temporary segments and, 4-20, 15-13

triggers use of, 19-24

query rewrite, 10-18

dynamic predicates in security policies, 27-24

queue monitor process (QMNn), 1-19, 8-14, 18-8

interval statistics, 18-14

window of execution, 18-9

queues

multi-consumer, 18-6

single consumer, 18-6

queuing, 18-2

exception handling, 18-14

exporting queue tables, 18-15

instance affinity, 18-13

publish-subscribe support, 18-13

event publication, 19-19

queue level access control, 18-12

queue monitor process, 1-19, 8-14, 18-8

interval statistics, 18-14

window of execution, 18-9

queue tables, 18-6, 18-15

recipients, 18-7

rule-based subscriptions, 18-8

subscription lists, 18-7

remote databases, 18-12

quotas

revoking tablespace access and, 26-15

setting to zero, 26-15

SYS user not subject to, 26-15

tablespace, 1-42, 26-14

temporary segments ignore, 26-15

---

R

RADIUS, 26-7

range partitioning, 11-13

and primary key columns, 11-41

equipartitioning, 11-24

key comparison, 11-19, 11-22

partition bounds, 11-19

RAW datatype, 12-15

RAWTOHEX function, 12-24

RDBMS, 1-21

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

See also Oracle

read committed isolation, 24-7, 24-8

read consistency, 24-2, 24-4

Cache Fusion, 24-7

defined, 1-30

dirty read, 24-3, 24-11

multiversion consistency model, 1-30, 24-4

nonrepeatable read, 24-3, 24-11

Oracle Parallel Server, 24-7

phantom read, 24-3, 24-11

queries, 15-13, 24-4

rollback segments and, 4-22

snapshot too old message, 24-5

statement level, 24-6

subqueries in DML, 24-14

transactions, 1-30, 24-4, 24-6

triggers and, 19-22, 19-24

READ ONLY clause

ALTER TABLESPACE, 3-11

read snapshot time, 24-11

read uncommitted, 24-3

READ WRITE clause

ALTER TABLESPACE, 3-11

readers block writers, 24-11

read-only

databases

opening, 5-9

tablespaces, 3-11

backing up, 29-27

restrictions on, 3-12

transition read-only mode, 3-11

transactions, 1-31

read-only snapshots, 31-7

reads

data block

limits on, 26-18

dirty, 24-3

repeatable, 24-6

REBUILD INDEX PARTITION statement, 11-60

no-logging mode, 22-7

rules of parallelism, 23-25

REBUILD INDEX statement

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

rules of parallelism, 23-25

recipients, 18-7

subscription lists, 18-7

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

in-doubt transactions, 1-35, 5-9, 16-8

recovery

basic steps, 1-49, 29-10

block-level recovery, 24-21, 29-15

crash recovery, 1-45, 29-4, 29-14

instance failure, 5-11

opening a database, 5-9

read-only tablespaces, 29-6

required after aborting instance, 5-10

SMON process, 1-17, 8-11

database buffers and, 29-8

dead transactions, 29-4

diagrammed, 29-13

disaster recovery, 29-28

distributed processing in, 8-12

instance recovery, 29-4

fast-start checkpointing, 29-14

instance failure, 1-45, 29-4

parallel DML, 23-42

read-only tablespaces, 29-6

SMON process, 1-17, 8-11, 23-42

media recovery

dispatcher processes, 8-21

enabled or disabled, 29-18

of distributed transactions, 5-9

overview of, 1-44, 29-8

parallel DML, 23-41

parallel recovery, 29-11

parallel restore, 29-17

point-in-time

clone database, 5-8

process recovery, 8-12, 29-3

recommendations for, 29-13

Recovery Manager, 1-50, 29-16

rolling back transactions, 29-9

rolling forward, 29-9

standby database, 29-28

statement failure, 29-3

structures used in, 1-46, 29-6

whole database backups, 29-25

Recovery Manager, 1-50, 29-16

generating reports, 29-18

operating without a catalog, 29-17

parallel operations, 29-17

parallel recovery, 29-11

recovery catalog, 29-16

recursive SQL

cursors and, 15-7

redo logs, 1-9, 29-7, 29-9

archived, 1-47, 29-19

automatically, 29-20

errors in archiving, 29-21

manually, 29-21

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

archiving modes, 29-18

buffer management, 8-9

buffers, 1-14, 7-6

circular buffer, 8-9

committed data, 29-9

committing a transaction, 8-10

entries, 1-9, 29-9

files named in control file, 29-22

log sequence numbers, 1-47

recorded in control file, 29-23

log switch

ALTER SYSTEM SWITCH LOGFILE, 8-12

archiver process, 1-18, 8-12

log writer process, 7-6, 8-9

mode of, 1-47

multiplexed, 1-47

purpose of, 1-9

online or offline, 1-46, 1-47, 29-7

overview of, 1-9, 1-46

parallel recovery, 29-11

recovery and, 29-7

rolling forward, 29-9

instance failure, 29-4

rolling forward and, 29-9

size of buffers, 7-6

uncommitted data, 29-9

when temporary segments in, 4-20

writing buffers, 8-9

written before transaction commit, 8-10

referenced

keys, 1-58, 25-13

objects

dependencies, 20-2

external reference, 17-8, 17-20

partitions, 11-18

REFERENCES privilege

when granted through a role, 27-21

referential integrity, 24-12, 25-13

cascade rule, 25-3

examples of, 25-21

partially null foreign keys, 25-16

PRIMARY KEY constraints, 25-11

restrict rule, 25-3

self-referential constraints, 25-15, 25-21

set to default rule, 25-3

set to null rule, 25-3

refresh

incremental, 10-19

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

materialized views, 10-19

snapshots, 31-10

REFs

dangling, 13-10

dereferencing of, 13-10

for rows of object views, 14-3

implicit dereferencing of, 13-10

pinning, 14-4

scoped, 13-9

REFTOHEX function, 12-24

relational DBMS (RDBMS)

object-relational DBMS, 13-2

principles, 1-20

SQL and, 15-2

See also Oracle

relations, 1-22

remote databases, 1-34

remote dependencies, 20-11

remote procedure calls, 30-46

remote transactions, 30-35

parallel DML and DDL restrictions, 23-28

REMOTE_DEPENDENCIES_MODE parameter, 20-11

RENAME statement, 15-4

repeatable reads, 24-3

replication

administration, 31-12

applications that use, 31-3

availability, 31-2

conflict resolution, 31-15

conflicts

procedural replication, 31-17

definition, 31-2

deployment templates, 31-11

disconnected computing, 31-2

groups, 31-4

hybrid configurations, 31-11

introduction, 31-2

mass deployment, 31-3

master definition site, 31-5

master groups, 31-5

master sites, 31-5

materialized views (snapshots), 10-18

multimaster, 31-5

network load reduction, 31-2

performance, 31-2

procedural, 31-16

replication management API, 31-14

restrictions

direct-load INSERT, 22-10

parallel DML, 23-45

sites, 31-4

snapshot groups, 31-5

snapshots, 31-7

synchronous, 31-16

uses of, 31-2

replication catalog, 31-14

replication management API, 31-14

replication objects, 31-4

reserved words, 15-3

resource limits

call level, 26-18

connect time per session, 26-19

CPU time limit, 26-18

determining values for, 26-20

idle time per session, 26-19

logical reads limit, 26-18

overview of, 1-42

private SGA space per session, 26-19

session level, 26-17

sessions per user, 26-19

RESOURCE role, 27-22

response queues, 8-17

response time, 21-9

restricted mode

starting instances in, 5-6

restricted rowid format, 12-18

RESTRICTED SESSION privilege, 26-22

restrictions

direct-load INSERT, 22-10, 23-44

nested tables, 23-30

parallel DDL, 23-32

remote transactions, 23-28

parallel DML, 23-44

remote transactions, 23-28, 23-47

parallel execution of functions, 23-47

partitions

bitmap indexes, 11-13

datatypes, 11-12, 11-21

partition-extended table names, 11-64

REVERSE clause for indexes, 10-33

reverse key indexes, 10-33

REVOKE statement, 15-5

locks, 24-30

rewrite

predicates in security policies, 27-24

using materialized views, 10-18

roles, 1-40, 27-17

application, 27-18

CONNECT role, 27-22

DBA role, 27-22

DDL statements and, 27-21

definer-rights procedures disable, 27-20

dependency management in, 27-21

enabled or disabled, 27-19

EXP_FULL_DATABASE role, 27-22

functionality, 27-2

granting, 27-3, 27-19

IMP_FULL_DATABASE role, 27-22

in applications, 1-41

invoker-rights procedures use, 27-21

managing via operating system, 27-23

naming, 27-20

obtained through database links, 30-23

overview of, 1-40

predefined, 27-22

queue administrator, 18-9

RESOURCE role, 27-22

restrictions on privileges of, 27-21

revoking, 27-19

schemas do not contain, 27-20

security domains of, 27-20

setting in PL/SQL blocks, 27-21

use of passwords with, 1-41

user, 27-19

users capable of granting, 27-20

uses of, 27-18

rollback, 4-21, 16-6

defined, 1-53

described, 16-6

during recovery, 1-50, 29-9

ending a transaction, 16-2, 16-5, 16-6

statement-level, 16-4

to a savepoint, 16-8

rollback entries, 4-21

rollback segments, 1-7, 4-21

access to, 4-21

acquired during startup, 5-9

allocation of extents for, 4-23

new extents, 4-25

clashes when acquiring, 4-28

committing transactions and, 4-23

contention for, 4-23

deallocating extents from, 4-26

deferred, 4-31

defined, 1-7

dropping, 4-26

restrictions on, 4-31

how transactions write to, 4-23

in-doubt distributed transactions, 4-25

invalid, 4-29

locks on, 24-31

MAXEXTENTS UNLIMITED, 23-41

moving to the next extent of, 4-24

number of transactions per, 4-23

offline, 4-29, 4-31

offline tablespaces and, 4-31

online, 4-29, 4-31

OPTIMAL, 23-41

overview of, 4-21, 29-8

parallel DML, 23-41

parallel recovery, 29-10

partly available, 4-29, 29-4

private, 4-27

public, 4-27

read consistency and, 1-30, 4-22, 24-4

recovery needed for, 4-29

states of, 4-29

SYSTEM rollback segment, 4-27

transactions and, 4-22

use of in recovery, 1-48, 29-9

when acquired, 4-27

when used, 4-22

written circularly, 4-23

ROLLBACK statement, 15-5

rolling back transactions, 1-54, 16-2, 16-6, 29-4

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

root blocks, 10-59

row cache, 7-10

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

row directories, 4-4

row locking, 24-11, 24-21

block-level recovery, 24-21, 29-15

serializable transactions and, 24-9

row objects, 13-8

row pieces, 10-5

headers, 10-6

how identified, 10-8

row sampling, 21-16

row sources, 21-4

row triggers, 19-9

when fired, 19-22

See also triggers

ROWID datatype, 12-15, 12-16

extended rowid format, 12-17

restricted rowid format, 12-18

rowids, 10-8

accessing, 12-16

changes in, 12-17

in non-Oracle databases, 12-23

internal use of, 12-16, 12-20

logical, 12-15

logical rowids, 12-20

index on index-organized table, 10-42

physical guesses, 10-42, 12-21

staleness of guesses, 12-22

statistics for guesses, 12-22

of clustered rows, 10-8

physical, 12-15

row migration, 4-11

sorting indexes by, 10-30

universal, 12-16

ROWIDTOCHAR function, 12-24

row-level locking, 24-11, 24-21

rows, 1-22, 10-3

addresses of, 10-8

chaining across blocks, 4-10, 10-5

clustered, 10-7

rowids of, 10-8

defined, 1-22

described, 10-3

fetched, 15-13

format of in data blocks, 4-4

headers, 10-5

locking, 24-11, 24-21

locks on, 11-45, 24-21, 24-24

logical rowids, 12-20

index-organized tables, 10-42

migrating to new block, 4-11

pieces of, 10-5

row objects, 13-8

row overflow in index-organized tables, 10-41

row sources, 21-4

row-level security, 27-23

shown in rowids, 12-17, 12-18

size of, 10-5

storage format of, 10-5

triggers on, 19-9

when rowid changes, 12-17

rule-based optimization, 21-20

rule-based subscriptions, 18-8

runtime areas, 7-9

---

S

same-row writers block writers, 24-11

SAMPLE clause

cost-based optimization, 21-17

SAVEPOINT statement, 15-5

savepoints, 1-54, 16-7

described, 16-7

implicit, 16-4

overview of, 1-54

rolling back to, 16-8

scalability

batch jobs, 23-39

client/server architecture, 6-4

parallel DML, 23-38

parallel SQL execution, 23-2

scans

full table

LRU algorithm, 7-4

parallel query, 23-5

table scan and CACHE clause, 7-4

schema object privileges, 27-3

DML and DDL operations, 27-5

granting and revoking, 27-4

overview of, 1-40

views, 27-6

schema objects, 10-1

auditing, 1-43, 28-8

creating

tablespace quota required, 26-15

default tablespace for, 26-14

dependencies of, 20-2

and distributed databases, 20-12

and views, 10-16

on non-existence of other objects, 20-9

triggers manage, 19-22

dependent on lost privileges, 20-6

dimensions, 10-20

distributed database naming conventions for, 30-22

domain index, 10-47

global names, 30-22

in a revoked tablespace, 26-15

indextype, 10-46

information in data dictionary, 2-2

INVALID status, 20-3

materialized views, 10-18

overview of, 1-6, 1-22, 10-2

privileges on, 27-3

relationship to datafiles, 3-17, 10-2

trigger dependencies on, 19-26

user-defined operator, 10-48

user-defined types, 13-3

schemas, 26-2

associated with users, 1-37, 10-2

contents of, 10-2

contrasted with tablespaces, 10-2

defined, 26-2

objects in, 10-2

OUTLN, 21-7

user-defined datatypes, 13-14

SCN. See system change numbers

scoped REFs, 13-9

security, 1-40, 26-2

administrator privileges, 5-3

application enforcement of, 1-41

auditing, 28-2, 28-6

auditing user actions, 1-43

data, 1-38

definer rights, 17-7, 17-20

deleting audit data, 2-5

described, 1-37

discretionary access control, 1-38, 26-2

domains, 1-39, 26-2

dynamic predicates, 27-24

enforcement mechanisms, 1-38

fine-grained access control, 27-23

invoker rights, 17-7, 17-20

message queues, 18-9

passwords, 26-7

policies

implementing, 27-25

procedures enhance, 27-8

program interface enforcement of, 8-27

security policies, 27-23

system, 1-38, 2-3

views and, 10-14

views enhance, 27-6

security domains, 1-39, 26-2

enabled roles and, 27-19

tablespace quotas, 26-14

segments, 1-7, 4-18

data, 4-18

deallocating extents from, 4-15

defined, 4-3

header block, 4-12

index, 4-19

overview of, 1-7, 4-18

rollback, 4-21

table

high water mark, 22-3

temporary, 1-8, 4-19, 10-11

allocating, 4-19

cleaned up by SMON, 8-11

dropping, 4-17

ignore quotas, 26-15

operations that require, 4-19

parallel INSERT, 22-9

tablespace containing, 4-17, 4-20

SELECT statement, 15-4

composite indexes, 10-24

SAMPLE clause

cost-based optimization, 21-17

subqueries, 15-13

See also queries

selectivity of predicates, 21-9

histograms, 21-9, 21-11

user-defined selectivity, 21-19

selfish style of method invocation, 13-6

sequences, 1-24, 10-21

auditing, 28-8

CHECK constraints prohibit, 25-21

independence from tables, 10-21

length of numbers, 10-21

number generation, 10-21

Server Manager

PL/SQL, 15-18, 15-19

SQL statements, 15-2

server processes, 1-16, 8-5

listener process and, 6-6

servers, 1-32

client/server architecture, 6-2

dedicated, 1-16, 8-23

multi-threaded contrasted with, 8-16

dedicated server architecture, 8-3

defined, 1-33

multi-threaded, 1-16

architecture, 8-3, 8-16

dedicated contrasted with, 8-16

processes of, 8-14, 8-15, 8-16, 8-20

processes of, 1-16

shared, 1-16

server-side scripts, 15-21

service names, 6-6

SERVICE_NAMES parameter, 6-6

session control statements, 1-52, 15-6

SESSION_ROLES view

queried from PL/SQL block, 27-21

sessions

auditing by, 28-10

connections contrasted with, 8-4

defined, 8-4, 28-10

enabling PARALLEL DML, 23-39

lightweight, 26-9

limit on concurrent, 1-42

by license, 26-21

limits per user, 26-19

memory allocation in the large pool, 7-12

package state and, 20-6

resource limits and, 26-17

stack space in PGA, 7-15

time limits on, 26-19

transaction isolation level, 24-32

when auditing options take effect, 28-5

where information is stored, 7-15

SET CONSTRAINTS statement

DEFERRABLE or IMMEDIATE, 25-25

SET ROLE statement, 15-6

SET TRANSACTION statement, 15-5

ISOLATION LEVEL, 24-8, 24-32

READ ONLY clause, 4-22

SET UNUSED clause for columns, 10-7

SGA. See system global area

shadow processes, 8-23

share locks

share table locks (S), 24-25

shared global area (SGA), 7-2

shared mode

rollback segments, 4-28

shared pool, 7-7

allocation of, 7-11

ANALYZE statement, 7-11

dependency management and, 7-11

described, 7-7

flushing, 7-12

object dependencies and, 20-10

overview of, 1-14

procedures and packages, 17-18

row cache and, 7-10

size of, 7-7

shared server processes (Snnn), 8-15, 8-20

described, 8-20

shared servers, 1-16

shared SQL areas, 7-8, 15-7

ANALYZE statement, 7-11

dependency management and, 7-11

described, 7-8

loading SQL into, 15-12

overview of, 1-14, 15-7

parse locks and, 24-30

procedures, packages, triggers and, 7-10

size of, 7-8

shared SQL for remote and distributed statements, 30-35

SHARED_MEMORY_ADDRESS parameter, 7-14

SHARED_POOL_SIZE parameter, 7-7

system global area size and, 7-13

shutdown, 5-10, 5-11

abnormal, 5-6, 5-11

audit record, 28-5

deallocation of the SGA, 7-2

prohibited by dispatcher processes, 8-21

steps, 5-10

SHUTDOWN ABORT statement, 5-11

crash recovery required, 29-4

SIDs in LISTENER.ORA file, 6-6

signature checking, 20-11

Simple Network Management Protocol (SNMP) support

database management, 30-33

SINGLE TABLE HASHKEYS, 10-61

single-table hash clusters, 10-61

site autonomy, 1-34, 30-24

skewing parallel DML workload, 23-19

SKIP_UNUSABLE_INDEXES parameter, 20-8

SMON background process, 8-11

See also system monitor process

SMP architecture

disk affinity, 23-50

snaphsots, 31-7

snapshot groups, 31-5

snapshot logs, 31-10

snapshot too old message, 24-5

snapshots

deployment templates, 31-11

read-only, 31-7

refresh, 8-13, 31-10

job queue processes, 1-19, 8-13

same as materialized view, 1-24

snapshot logs, 31-10

updateable, 31-8

Snnn background processes, 8-15

SNPn background processes, 1-19, 8-13

message propagation, 18-12

software code areas, 7-18

shared by programs and utilities, 7-19

sort areas, 7-17

sort operations, 3-12

sort segments, 3-13

SORT_AREA_RETAINED_SIZE parameter, 7-17

SORT_AREA_SIZE parameter, 4-20, 7-17

space management

compression of free space in blocks, 4-10

data blocks, 4-5

direct-load INSERT, 22-9

extents, 4-12

MINIMUM EXTENT parameter, 23-34

parallel DDL, 23-34

PCTFREE, 4-6

PCTUSED, 4-7

row chaining, 4-10

segments, 4-18

spatial applications

index-organized tables, 10-45

SPLIT PARTITION statement

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

rules of parallelism, 23-25

SQL, 15-2

cursors used in, 15-7

Data Definition Language (DDL), 15-4

Data Manipulation Language (DML), 15-4

dynamic SQL, 15-20

embedded, 1-52, 15-6

user-defined datatypes, 13-14

extension

partition or subpartition name, 11-63

functions, 15-2

column default values, 10-9

COUNT, 10-38

in CHECK constraints, 25-21

NVL, 10-9

memory allocation for, 7-11

overview of, 1-51, 15-2

parallel execution, 23-2

parsing of, 15-8

PL/SQL and, 1-55, 15-16

recursive, 15-7

cursors and, 15-7

reserved words, 15-3

session control statements, 15-6

shared SQL, 15-7

statement-level rollback, 16-4

system control statements, 15-6

transaction control statements, 15-5

transactions and, 1-52, 16-2, 16-5

types of statements in, 1-51, 15-3

user-defined datatypes, 13-13

embedded SQL, 13-14

OCI, 13-15

SQL areas

private, 7-8

persistent, 7-8

runtime, 7-9

shared, 1-14, 7-8, 15-7

SQL statements, 1-51, 15-3, 15-9

array processing, 15-15

auditing, 28-7, 28-9

overview, 1-43

when records generated, 28-4

creating cursors, 15-12

dictionary cache locks and, 24-31

distributed

routing to nodes, 15-12

distributed databases and, 30-34

embedded, 15-6

execution, 15-9, 15-14

execution plans of, 21-2

failure in, 29-3

handles, 1-15

number of triggers fired by single, 19-22

overview, 1-51

parallel execution, 23-2

parallelizing, 23-2, 23-10

parse locks, 24-30

parsing, 15-12

privileges required for, 27-3

referencing dependent objects, 20-4

resource limits and, 26-18

successful execution, 16-3

transactions, 15-15

triggers on, 19-2, 19-9

triggering events, 19-7

types of, 1-51, 15-3

SQL*Loader, 1-11

direct load

NOLOGGING mode, 11-58, 22-7

parallel direct load, 22-3

similar to direct-load INSERT, 22-2

partition operations, 11-47, 11-49

SQL*Menu

PL/SQL, 15-17

SQL*Module

FIPS flagger, 15-6

stored procedures, 15-19

SQL*Net

See Net8

SQL*Plus

ALERT file, 8-15

anonymous blocks, 15-18

connecting with, 26-4

executing a package, 17-6

executing a procedure, 17-4

lock and latch monitors, 24-30

parallel recovery, 29-11

session variables, 15-18

showing size of SGA, 7-13

SQL statements, 15-2

statistics monitor, 26-20

stored procedures, 15-19

SQL_TRACE parameter, 8-15

SQL92, 24-2

stack space, 7-15

standards, 1-3

ANSI/ISO, 1-3, 25-5, 25-16

isolation levels, 24-2, 24-11

FIPS, 15-6

integrity constraints, 25-5, 25-16

Oracle adherence, 1-3

standby database

mounting, 5-7

survivability, 29-27

startup, 5-2, 5-5

allocation of the SGA, 7-2

starting address, 7-14

audit record, 28-5

forcing, 5-6

prohibited by dispatcher processes, 8-21

recovery during, 29-4

restricted mode, 5-6

steps, 5-5

STARTUP FORCE statement

crash recovery required, 29-4

statement triggers, 19-9

described, 19-9

when fired, 19-22

See also triggers

statement-level read consistency, 24-6

statements

See SQL statements

statistics

checkpoint, 8-11

estimated, 21-15

block sampling, 21-16

row sampling, 21-16

exporting and importing, 21-9

extensible optimization, 21-18

from ANALYZE, 21-15

from B*-tree or bitmap index, 21-14

generating and managing with DBMS_STATS, 21-13

optimizer use of, 21-8, 21-9

partitioned tables and indexes, 11-13

partitions and subpartitions, 21-12

queuing, 18-13

selectivity of predicates, 21-9

histograms, 21-9, 21-11

user-defined, 21-19

user-defined statistics, 21-19

storage

clusters, 10-53

datafiles, 3-16

fragmentation in parallel DDL, 23-34

hash clusters, 10-55

index partitions, 11-36

indexes, 10-28

logical structures, 3-6, 10-2

nulls, 10-8

parallel INSERT, 22-9

restricting for users, 26-15

revoking tablespaces and, 26-15

table partitions, 11-26

tablespace quotas and, 26-15

triggers, 19-2, 19-26

user quotas on, 1-41

view definitions, 10-15

STORAGE clause

parallel execution, 23-34

using, 4-12

storage parameters

MAXEXTENTS UNLIMITED, 23-41

NEXT, 22-9

OPTIMAL (in rollback segments), 4-26, 23-41

parallel direct-load INSERT, 22-9

PCTINCREASE, 22-9

setting, 4-12

stored functions, 17-2, 17-6

stored procedures, 15-16, 17-2, 17-6

calling, 15-19

contrasted with anonymous blocks, 17-11

triggers contrasted with, 19-2

variables and constants, 15-18

See also procedures

Structured Query Language (SQL), 1-51, 15-2

See also SQL

structures

data blocks

shown in rowids, 12-18

data dictionary, 1-29, 2-1

datafiles

shown in rowids, 12-18

locking, 24-29

logical, 1-5, 4-1

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

extents, 1-7, 4-2, 4-12

schema objects, 1-6, 10-2

segments, 1-7, 4-2, 4-18

tablespaces, 1-5, 3-1, 3-6

memory, 1-12, 7-1

physical, 1-8

control files, 1-10, 29-22

datafiles, 1-8, 3-1, 3-16

redo log files, 1-9, 29-7

processes, 1-12, 1-15, 8-1

SUBPARTITION clause, 11-63

subpartition locks

DML, 11-46

subpartitions

statistics, 21-12

subqueries, 15-13

CHECK constraints prohibit, 25-21

in DDL statements, 23-32

in DML statements

serializable isolation, 24-14

in remote updates, 30-34

inline views, 10-17

query processing, 15-13

See also queries

subscriptions

rule-based, 18-8

summaries, 10-18

SunSoft's SunNet Manager, 30-33

supplied packages, 17-17

invoker or definer rights, 27-9

survivability, 29-27

synchronous replication, 31-16

synonyms, 20-8

constraints indirectly affect, 25-5

described, 10-22

for data dictionary views, 2-4

inherit privileges from object, 27-4

name resolution, 30-42

overview of, 1-25

partition-extended table names, 11-65

private, 10-22

public, 10-22

uses of, 10-22

SYS username

data dictionary tables owned by, 2-3

security domain of, 26-3

statement execution not audited, 28-4

temporary schema objects owned by, 26-15

V$ views, 2-7

SYS.AUD$ view

purging, 2-5

SYSDBA privilege, 5-3

SYSOPER privilege, 5-3

system change numbers (SCN)

committed transactions, 16-6

defined, 16-6

read consistency and, 24-5, 24-6

redo logs, 8-10

when determined, 24-5

system control statements, 1-52, 15-6

system global area (SGA), 7-2

allocating, 5-6

contents of, 7-3

data dictionary cache, 2-4, 7-10

database buffer cache, 7-3

diagram, 5-2

fixed, 7-3

large pool, 7-12

limiting private SQL areas, 26-19

overview of, 1-14, 7-2

redo log buffer, 7-6, 16-5

rollback segments and, 16-5

shared and writable, 7-3

shared pool, 7-7

size of, 7-13

variable parameters, 5-4

when allocated, 7-2

system monitor process (SMON), 8-11

defined, 1-17, 8-11

instance recovery, 29-4

parallel DML instance recovery, 23-42

parallel DML system recovery, 23-42

Parallel Server and, 8-11, 23-42

rolling back transactions, 29-10

temporary segment cleanup, 8-11

system privileges, 27-2

ADMIN OPTION, 27-3

described, 27-2

granting and revoking, 27-3

SYSTEM rollback segment, 4-27

SYSTEM tablespace, 3-6

data dictionary stored in, 2-2, 2-5, 3-6

media failure, 29-6

online requirement of, 3-9

procedures stored in, 3-6, 17-19

SYSTEM username

security domain of, 26-3

---

T

tables

affect dependent views, 20-5

auditing, 11-63, 28-8

base, 1-23

data dictionary use of, 2-3

relationship to views, 10-13

clustered, 10-49

contain integrity constraints, 1-57

contained in tablespaces, 10-5

controlling space allocation for, 10-4, 22-9

directories, 4-4

DUAL, 2-7

dynamic partitioning, 23-6

enable or disable constraints, 25-26

full table scan and buffer cache, 7-4

hash, 10-59

historical, 23-39

how data is stored in, 10-4

indexes and, 10-23

index-organized

key compression in, 10-33, 10-41

index-organized tables, 10-39

logical rowids, 10-42, 12-20

integrity constraints, 25-2, 25-5

locks on, 11-45, 24-21, 24-24, 24-25

maximum number of columns in, 10-13

nested tables, 10-10, 13-12

no-logging mode, 22-7

normalized or denormalized, 1-28, 10-20

object tables, 13-3, 13-8

virtual, 14-2

overview of, 1-22, 10-3

parallel creation, 23-32

parallel DDL storage, 23-34

parallel table scans, 23-4

PARTITION clause, 11-63

partition-extended table names, 11-63

partitions, 11-2, 11-26

presented in views, 10-12

privileges for partitions, 11-62

privileges on, 27-5

queue tables, 18-6, 18-15

refreshing in data warehouse, 23-38

replicating, 1-36

single-table hash clusters, 10-61

specifying tablespaces for, 10-5

STORAGE clause with parallel execution, 23-34

SUBPARTITION clause, 11-63

summary or rollup, 23-32

temporary, 10-11

segments in, 4-20

triggers used in, 19-2

validate or novalidate constraints, 25-26

virtual or viewed, 1-23

with LOB columns

partitioning, 11-37

tablespace point-in-time recovery

clone database, 5-8

tablespaces, 3-6

contrasted with schemas, 10-2

default for object creation, 1-41, 26-14

described, 3-6

dictionary-managed, 3-8

how specified for tables, 10-5

locally-managed, 3-8

temporary tablespaces, 3-13

locks on, 24-31

moving or copying to another database, 3-14

no-logging mode, 22-7

offline, 1-6, 3-9, 3-17

and index data, 3-10

cannot be read-only, 3-11

remain offline on remount, 3-10

online, 1-6, 3-9, 3-17

overview of, 1-5, 3-6

quotas on, 1-41, 1-42, 26-14, 26-15

limited and unlimited, 26-15

no default, 26-15

read-only, 3-11

dropping objects from, 3-12

transition mode, 3-11

relationship to datafiles, 3-2

revoking access from users, 26-15

size of, 3-4

space allocation, 3-7

temporary, 1-42, 3-12

default for user, 26-15

transition read only mode, 3-11

transportable, 3-14

used for temporary segments, 4-17, 4-20

See also SYSTEM tablespace

TAF, 29-15

tasks, 8-2

tempfiles, 3-18

temporary segments, 4-17, 4-20, 10-11

allocating, 4-20

allocation for queries, 4-20

deallocating extents from, 4-17

dropping, 4-17

ignore quotas, 26-15

operations that require, 4-19

parallel DDL, 23-34

parallel INSERT, 22-9

tablespace containing, 4-17, 4-20

when not in redo log, 4-20

temporary tables, 10-11

temporary tablespaces, 3-12

threads

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

three-valued logic (true, false, unknown)

produced by nulls, 10-9

throughput, 21-8

timestamp checking, 20-11

TO_CHAR function

data conversion, 12-24

Julian dates, 12-11

NLS default in CHECK constraints, 25-21

NLS default in views, 10-15

TO_DATE function, 12-10

data conversion, 12-24

Julian dates, 12-11

NLS default in CHECK constraints, 25-21

NLS default in views, 10-15

partitions, 11-12, 11-21

TO_NUMBER function, 12-9

data conversion, 12-24

Julian dates, 12-11

NLS default in CHECK constraints, 25-21

NLS default in views, 10-15

trace files, 8-15

ARCn trace file, 29-21

DBWn trace file, 29-6

LGWR trace file, 8-10

transaction control statements, 1-52, 15-5

in autonomous PL/SQL blocks, 16-11

transaction set consistency, 24-10, 24-11

transaction tables, 4-22

reset at recovery, 8-12

transactions, 1-52, 16-1

advanced queuing, 18-3

assigning system change numbers, 16-6

assigning to rollback segments, 4-22

asynchronous processing, 18-2

autonomous, 16-10

within a PL/SQL block, 16-10

block-level recovery, 24-21, 29-15

committing, 1-54, 8-10, 16-4, 16-5

group commits, 8-10

use of rollback segments, 4-23

concurrency and, 24-16

controlling transactions, 15-15

dead, 29-4

deadlocks and, 16-4, 24-17

defining and controlling, 15-15

described, 16-2

discrete transactions, 15-16, 16-9

distributed, 1-31

deadlocks and, 24-19

parallel DDL restrictions, 23-28

parallel DML restrictions, 23-28, 23-47

resolving automatically, 8-12

two-phase commit, 1-35, 16-8

two-phase commit and, 30-36

distribution among rollback segments of, 4-23

end of, 16-5

consistent data, 15-15

in-doubt

limit rollback segment access, 4-31

resolving automatically, 1-35, 5-9, 16-8

resolving manually, 1-35

rollback segments and, 4-25

use partly available segments, 4-31

manual locking of, 24-32

overview of, 1-52

read consistency of, 1-30, 24-6

read-only, 1-31, 24-7

not assigned to rollback segments, 4-22

recovery, 29-4

redo log files written before commit, 8-10

rollback segments and, 4-22

rolling back, 1-54, 16-6

and offline tablespaces, 4-31

partially, 16-8

use of rollback segments, 4-22

savepoints in, 1-54, 16-7

serializable, 24-7

space used in data blocks for, 4-5

start of, 16-5

statement level rollback and, 16-4

system change numbers, 8-10

terminating the application and, 16-5

transaction control statements, 15-5

triggers and, 19-24

two-phase commit in parallel DML, 23-41

writing to rollback segments, 4-23

TRANSACTIONS parameter, 4-28

TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter, 4-28

transition read only tablespaces, 3-11

Transparent Application Failover, 29-15

transportable tablespaces, 3-14

triggers, 19-1, 20-8

action, 19-8

timing of, 19-10

AFTER triggers, 19-10

as program units, 1-56

auditing, 28-8

BEFORE triggers, 19-10

cascading, 19-4

constraints apply to, 19-22

constraints contrasted with, 19-5

data access and, 19-24

dependency management of, 19-26, 20-6

enabled triggers, 19-22

enabled or disabled, 19-22

enforcing data integrity with, 25-5

events, 19-7

examples of, 19-12, 19-14, 19-25

firing (executing), 19-2, 19-26

privileges required, 19-26

steps involved, 19-22

timing of, 19-22

INSTEAD OF triggers, 19-13

object views and, 14-5

INVALID status, 20-3, 20-6

Java, 19-8

Oracle Forms triggers versus, 19-3

overview of, 19-2

parts of, 19-6

privileges for executing, 27-8

roles, 27-20

procedures contrasted with, 19-2

prohibited in views, 10-13

publish-subscribe support, 19-19

restrictions, 19-8, 23-47

direct-load INSERT, 22-10

parallel DML, 23-45

row, 19-9

schema object dependencies, 19-22, 19-26

sequence for firing multiple, 19-22

shared SQL areas and, 7-10

statement, 19-9

storage of, 19-26

types of, 19-9

UNKNOWN does not fire, 19-8

uses of, 19-3

TRUNCATE statement, 15-4

two-phase commit

described, 1-35, 30-36

manual override of, 1-35

parallel DML, 23-41

transaction management, 16-8

triggers, 19-22

two-task mode, 8-3

listener process and, 8-14

network communication and, 8-24

program interface in, 8-25

types

privileges on, 27-12

See datatypes, object types

---

U

undo, 1-7

See also rollback

unique indexes, 10-24

UNIQUE key constraints, 25-8

composite keys, 25-9, 25-11

constraint checking, 25-23

indexes used to enforce, 25-10

maximum number of columns, 25-10

NOT NULL constraints and, 25-11

nulls and, 25-11

size limit of, 25-10

unique keys, 1-57, 1-58, 25-9

composite, 25-9, 25-11

UNLIMITED extents, 23-41

UNUSABLE indexes

function-based, 20-8

UNUSED column, 10-7

update no action constraint, 25-16

UPDATE statement, 15-4

foreign key references, 25-16

freeing space in data blocks, 4-10

no-logging mode, 22-8

LOBs, 22-7

parallel UPDATE, 23-22

triggers, 19-2, 19-7

BEFORE triggers, 19-10

updateable snapshots, 31-8

updates

location transparency and, 30-45

object views, 14-5

updatability of object views, 14-5

updatability of views, 10-16, 19-13, 19-14

updatable join views, 10-16

update intensive environments, 24-9

updating tables

with parent keys, 25-18, 25-19

UROWID datatype, 12-16

USE_INDIRECT_DATA_BUFFERS parameter, 7-14

USE_STORED_OUTLINES session parameter, 21-7

user processes

allocate PGAs, 7-15

connections and, 8-4

dedicated server processes and, 8-23

manual archiving by, 29-22

sessions and, 8-4

shared server processes and, 8-20

user program interface (UPI), 8-27

USER pseudocolumn, 27-7

USER_ views, 2-6

USER_UPDATABLE_COLUMNS view, 10-17

user-defined costs, 21-19

user-defined datatypes, 13-1, 13-3

collections, 13-11

nested tables, 13-12

variable arrays (VARRAYs), 13-11

object types, 13-2, 13-4

object-relational model, 1-21

user-defined operator, 10-48

users, 26-2

access rights, 26-2

associated with schemas, 10-2

auditing, 28-12

authentication of, 26-3

coordinating concurrent actions of, 1-29

dedicated servers and, 8-23

default tablespaces of, 26-14

enterprise, 26-2

licensing by number of, 26-22

licensing of, 26-20

listed in data dictionary, 2-2

locks, 24-40

multiuser environments, 1-2, 8-2

password encryption, 26-7

privileges of, 1-40

processes of, 1-15, 8-4

profiles of, 1-42, 26-20

PUBLIC user group, 26-16, 27-20

resource limits of, 26-17

restrictions on resource use of, 1-41

roles and, 27-17

for types of users, 27-19

schemas of, 1-37, 26-2

security domains of, 1-39, 26-2, 27-20

tablespace quotas of, 1-42, 26-14

tablespaces of, 1-41

temporary tablespaces of, 1-42, 4-20, 26-15

usernames, 1-39, 26-2

sessions and connections, 8-4

---

V

V_$ and V$ views, 2-7

V$LICENSE, 26-22

VALIDATE constraints, 25-26

VALUES LESS THAN clause, 11-19

DATE datatype, 11-21

examples, 11-14, 11-17

MAXVALUE, 11-20, 11-23

multicolumn keys, 11-23

VARCHAR datatype, 12-6

VARCHAR2 datatype, 12-6

non-padded comparison semantics, 12-6

similarity to RAW datatype, 12-15

variables

bind variables

user-defined types, 13-14

embedded SQL, 15-6

in stored procedures, 15-18

object variables, 14-4

varrays, 13-11

index-organized tables, 10-41

key compression, 10-33

very large database (VLDB), 11-5

parallel SQL, 23-2

partitions, 11-5

views, 1-22, 10-12

altering base tables and, 20-5

auditing, 28-8

base tables, 1-23

constraints and triggers prohibited in, 10-13

constraints indirectly affect, 25-5

containing expressions, 19-14

data dictionary

updatable columns, 10-16

user-accessible views, 2-3

definition expanded, 20-5

dependency status of, 20-5

fixed views, 2-7

histograms, 21-12

how stored, 10-13

indexes and, 10-15

inherently modifiable, 19-14

inline views, 10-17

INSTEAD OF triggers, 19-13

INVALID status, 20-3

materialized views, 1-23, 10-18

same as snapshots, 1-24

maximum number of columns in, 10-13

modifiable, 19-14

modifying, 19-13

name resolution, 30-42

NLS parameters in, 10-15

object views, 10-17, 14-1

updatability, 14-5

overview of, 1-22, 10-12

partition statistics, 11-13

partition views, 11-11

prerequisites for compilation of, 20-5

privileges for, 27-6

pseudocolumns, 19-14

schema object dependencies, 10-16, 20-4, 20-8

security applications of, 27-6

SQL functions in, 10-15

statistics, 21-16

updatability, 10-16, 14-5, 19-14

uses of, 10-14

virtual memory, 7-18

virtual tables, 1-22

VLDB

parallel SQL, 23-2

partitions, 11-5

---

W

waits for blocking transaction, 24-11

Wallet Manager, 26-5

wallets, 26-5

warehouse

materialized views, 10-18

refreshing table data, 23-38

See also data warehousing

web page scripting, 15-21

whole database backups, 1-49, 29-24

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

workload skewing, 23-19

wrapper

procedural replication, 31-16

write-ahead, 8-9

writers block readers, 24-11

---

X

X.509 certificates, 26-5

XA

session memory in the large pool, 7-12

---

Y

year 2000, 12-12