Index

disable or enable constraints, 28-22

CACHE clause, 7-4

DEALLOCATE UNUSED, 4-14

DROP COLUMN, 10-6

EXCHANGE PARTITION, 11-11

MERGE PARTITIONS, 11-16

MODIFY CONSTRAINT, 28-23

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

partition attributes, 11-27

triggers and, 20-6

UNUSED column, 10-6

validate or novalidate constraints, 28-22

ALTER TABLESPACE command

READ ONLY, 3-10

READ WRITE, 3-11

TEMPORARY or PERMANENT, 3-13

ALTER USER command

temporary segments and, 4-18

ALWAYS_ANTI_JOIN parameter, 24-13

ALWAYS_SEMI_JOIN parameter, 24-13

ANALYZE command, 16-4

creating histograms, 22-11

estimated statistics, 22-14

partition statistics, 11-14

shared pool and, 7-11

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

applications, 16-17

calling a stored procedure, 16-19

contrasted with stored procedures, 18-9

dynamic SQL, 16-19

performance, 18-9

ANSI SQL standard

datatypes of, 12-22

Oracle certification, 1-3

ANSI/ISO SQL standard, 1-3

composite foreign keys, 28-16

data concurrency, 27-2

isolation levels, 27-10

anti-joins, 24-13

ANY, 23-6

application context, 30-24

applications

application vs. database triggers, 20-3

can find constraint violations, 28-6

data dictionary references, 2-4

data warehousing, 10-32

database access through, 8-2

decision support systems (DSS), 10-33

parallel SQL, 26-2, 26-31

dependencies of, 21-11

direct-load INSERT, 26-38

discrete transactions, 17-8

enhancing security with, 1-42, 28-5

index-organized tables, 10-40

information retrieval (IR), 10-40

network communication and, 6-5

object dependencies and, 21-13

online analytical processing (OLAP), 10-42

online transaction processing (OLTP)

reverse key indexes, 10-31

parallel DML, 26-37

processes, 8-4

program interface and, 8-25

roles and, 30-18

security

application context, 30-24

sharing code, 7-17

spatial applications, 10-42

transaction termination and, 17-5

exporting queue tables, 19-12

message queuing, 19-2

publish/subscribe support, 19-10

event publication, 20-18

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

interval statistics, 19-11

window of execution, 19-7

queue tables, 19-4

recipients, 19-5

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

subscription lists, 19-5

remote databases, 19-9

AQ_ADMINISTRATOR role, 19-7

AQ_TM_PROCESS parameter, 19-6, 19-7

architecture

client/server, 1-33

MPP, 26-49

of Oracle, 1-14

SMP, 26-49

archived redo log, 1-48

automatic archiving, 32-19

enabling, 32-18

manual archiving, 32-20

ARCHIVELOG mode

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

defined, 32-18

overview, 1-48

partial database backups, 1-50, 32-24

whole database backups, 32-24

archiver process (ARCn)

automatic archiving, 32-19

described, 1-19, 8-12

example, 32-18

multiple processes, 1-19, 8-12

not used for manual archiving, 32-21

trace file, 32-20

ARCn background process, 1-19, 8-12

See also archiver process

array processing, 16-13

arrays

size of VARRAYs, 13-11

variable (VARRAYs), 13-11

asynchronous I/O

parallel recovery and, 32-11

asynchronous processing, 19-2

atomic nulls, 14-3

attributes

leaf-level, 14-18

leaf-level scalar, 14-18

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

AUDIT command, 16-4

deleting data in dictionary, 2-5

audit trail

auditing, 1-44, 31-1

authentication

database administrators, 29-12

described, 29-3

multi-tier, 29-9

network, 29-4

operating system, 29-4

Oracle, 29-7

public key infrastructure, 29-5

remote, 29-6

Automated Standby Database, 32-26

B

B*-tree indexes, 10-27

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

index-organized tables, 10-36

back-ends, 6-2

background processes, 1-18, 8-5

described, 8-5

diagrammed, 8-6

overview of, 1-18

trace files for, 8-14

See also processes

backups

control files, 32-25

datafiles, 32-25

for read-only tablespaces, 32-26

overview of, 1-45, 32-23

parallel, 32-16

partial, 1-50, 32-24

Recovery Manager, 1-51, 32-15

types of, 1-49

using Export to supplement, 32-25

whole database backup, 1-50, 32-23

base tables, 1-24

data dictionary, 2-2

See also views

basic replication

uses of, 34-11

BEFORE triggers, 20-9

defined, 20-9

when fired, 20-21

BETWEEN, 23-7

BFILE datatype, 12-14

binary data

BFILEs, 12-14

BLOBs, 12-13

RAW and LONG RAW, 12-14

bind variables

optimization, 23-52

user-defined types, 13-14

bitmap indexes, 10-32

cardinality, 10-33

nulls and, 10-8, 10-35

parallel query and DML, 10-33

partitioned tables, 11-14

scans of, 23-36

star transformation, 24-16

bitmap tablespace management, 3-8

temporary tablespaces, 3-13

BLOBs, 12-13

block

anonymous, 16-15, 18-9

block-level recovery, 32-14

database, 4-3

See also data blocks

block sampling, 22-14

block server process (BSP), 27-6

blocking transactions, 27-11

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

BOOLEAN datatype, 12-2

branch blocks, 10-28

BSP background process, 27-6

buffer cache, 7-3, 8-8

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

multiple buffer pools, 7-5

buffer pools, 7-5

BUFFER_POOL_KEEP parameter, 7-5

BUFFER_POOL_RECYCLE parameter, 7-5

buffers

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

Fast-Start Checkpointing, 32-13

incremental checkpoint, 8-8

redo log buffer, 1-16, 7-6

BUILD_PART_INDEX procedure, 11-30

business rules

enforcing in application code, 28-5

enforcing using stored procedures, 28-5

enforcing with constraints, 1-58, 28-1

advantages of, 28-5

enforcing with triggers, 1-59

C

CACHE clause, 7-4

Cache Fusion

read consistency, 27-6

caches

buffer cache, 7-3

multiple buffer pools, 7-5

cache hit, 7-4

cache miss, 7-4

data dictionary, 2-4, 7-10

location of, 7-6

database buffer, 1-16

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

object cache, 13-14, 14-16

object views, 15-4

private SQL area, 7-8

shared SQL area, 7-6, 7-8

writing of buffers, 8-8

calls

Oracle call interface, 8-25

remote procedure, 33-12

cannot serialize access, 27-11

capture avoidance rule, 14-8

cardinality, 10-33

Cartesian products, 23-3

CASCADE actions

DELETE statements and, 28-17

catalog, replication, 34-14

century, 12-12

certificate authority, 29-5

chaining of rows, 4-10, 10-5

CHAR datatype, 12-5

blank-padded comparison semantics, 12-5

character sets

CLOB and NCLOB datatypes, 12-13

column lengths, 12-6

for various languages, 5-5

NCHAR and NVARCHAR2, 12-6

CHARTOROWID function, 12-23

CHECK constraints, 28-17

checking mechanism, 28-20

defined, 28-17

multiple constraints on a column, 28-18

partially null foreign keys, 28-16

partition views, 11-11

subqueries prohibited in, 28-18

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

checkpoints

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

control files and, 32-22

DBWn process, 8-8, 8-11

Fast-Start Checkpointing, 32-13

incremental, 8-8

statistics on, 8-11

CHOOSE hint, 23-33

CKPT background process, 1-19, 8-11

client/server architectures, 6-2

clients, 1-33

diagrammed, 6-2

direct and indirect connections, 33-2

distributed databases and, 33-2

distributed processing in, 6-2

overview of, 1-33, 6-2

program interface, 8-25

CLOB datatype, 12-13

clone databases

mounting, 5-7

cluster joins, 24-5

cluster keys, 1-27, 10-48

clustered computer systems

Oracle Parallel Server, 5-3

clusters

coalescing extents, 4-15

coalescing free space

extents, 4-13

SMON process, 1-19, 8-11

within data blocks, 4-9

collections, 13-10

index-organized tables, 10-38

key compression, 10-30

nested tables, 13-12

variable arrays (VARRAYs), 13-11

columns

COMMENT command, 16-4

COMMIT command, 16-5

ending a transaction, 17-2, 17-4

fast commit, 8-10

implied by DDL, 17-2, 17-4

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

two-phase commit in parallel DML, 26-40

committing transactions

communication protocols, 6-5

comparison methods, 13-6

compatibility, 1-3

compatibility levels

transportable tablespaces, 3-14

COMPATIBLE parameter

read-only tablespaces, 3-11

compilation of object types, 14-17

compiled PL/SQL, 18-17

advantages of, 18-8

procedures, 18-9

pseudocode, 18-18, 20-25

recompiling, 18-19

shared pool, 16-16

triggers, 20-25

compiled triggers, 20-25

complex view merging, 23-17

composite indexes, 10-22

compression of free space in data blocks, 4-9

compression, index key, 10-29

concatenated indexes, 10-22

concurrency

defined, 1-30

described, 27-2

direct-load INSERT, 25-11

enforced with locks, 1-32

limits on

per database, 29-20

per user, 29-17

partition maintenance, 11-50

restrictions on, 1-43, 25-11

transactions and, 27-15

configuration of a database

parameter file, 5-4

process structure, 8-2

conflicts

procedural replication, 34-16

CONNECT BY clause

optimizing view queries, 23-16

CONNECT INTERNAL, 5-3

CONNECT role, 30-22

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

connectibility, 1-2

connections

defined, 8-4

embedded SQL, 16-6

listener process and, 6-6, 8-14

restricting, 5-6

sessions contrasted with, 8-4

usernames, 29-2

with administrator privileges, 5-3

audit records, 31-5

consistency of data, 1-55

multiversion consistency model, 1-31

See also read consistency

constants

comparisons and, 23-5

evaluation of expressions, 23-5

in stored procedures, 16-17

when computed, 23-5

constraints, 1-58

alternatives to, 28-5

applications can find violations, 28-6

CHECK, 28-17

default values and, 28-20

defined, 10-4

disabling temporarily, 28-7

effect on performance, 28-6

ENABLE or DISABLE, 28-22

enforced with indexes, 10-23

PRIMARY KEY, 28-12

UNIQUE, 28-10

FOREIGN KEY, 1-59, 28-13

mechanisms of enforcement, 28-18

modifying, 28-23

NOT NULL, 28-7, 28-11

object tables, 14-5

overview, 1-58

parallel create table, 26-25

PRIMARY KEY, 1-59, 28-11

prohibited in views, 10-12

referential

effect of updates, 28-16

self-referencing, 28-15

triggers cannot violate, 20-21

triggers contrasted with, 20-5

types listed, 1-58, 28-1

UNIQUE key, 1-59, 28-8

partially null, 28-11

VALIDATE or NOVALIDATE, 28-22

what happens when violated, 28-5

when evaluated, 10-8

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

literal invocation of, 14-4

contention

for data

deadlocks, 8-19, 27-17

lock escalation does not occur, 27-17

for rollback segments, 4-21

control files, 1-13, 32-21

backing up, 32-25

changes recorded, 32-22

checkpoints and, 32-22

contents, 32-21

how specified, 5-4

multiplexed, 1-49, 32-22

overview, 1-13, 32-21

physical database structure, 1-5

recovery and, 1-49

used in mounting database, 5-6

converting data

ANSI datatypes, 12-22

program interface, 8-25

SQL/DS and DB2 datatypes, 12-22

correlation names

inline views, 10-16

cost based optimization, 33-10

cost-based optimization, 22-7

extensible optimization, 22-16

histograms, 22-8

query rewrite, 10-17

selectivity of predicates, 22-8

histograms, 22-8, 22-10

user-defined, 22-17

user-defined costs, 22-17

statistics, 22-8, 23-32

user-defined, 22-17

CPU time limit, 29-17

crash recovery, 32-4, 32-13

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

opening a database, 5-8

read-only tablespaces, 32-6

required after aborting instance, 5-10

SMON process, 1-19, 8-11

CREATE ANY TYPE privilege, 14-13

See also privileges

CREATE CLUSTER command

HASHKEYS clause, 10-53, 10-57

SINGLE TABLE HASHKEYS, 10-57

storage parameters, 4-17

CREATE command, 16-4

CREATE FUNCTION command, 18-17

CREATE INDEX command

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

object types, 14-6

partition attributes, 11-37

rules of parallelism, 26-25

storage parameters, 4-17

temporary segments and, 4-18

CREATE OUTLINE statement, 22-6

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

CREATE PACKAGE command

examples, 18-12, 20-10

package name, 18-17

CREATE PROCEDURE command

example, 18-6

procedure name, 18-17

CREATE SYNONYM command

index-organized tables, 26-31

CREATE TABLE AS SELECT

rules of parallelism

CREATE TABLE command

CREATE TABLESPACE command

TEMPORARY clause, 3-13

CREATE TEMPORARY TABLE command, 10-10

CREATE TEMPORARY TABLESPACE command, 3-13

CREATE TRIGGER command

compiled and stored, 20-25

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

object tables, 14-7

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

CREATE TYPE command

incomplete types, 14-16

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

object views, 15-3

VARRAYs, 13-11

CREATE TYPE privilege, 14-13

See also privileges

CREATE USER command

temporary segments and, 4-18

CREATE VIEW command

examples, 20-14

object views, 15-3

CREATE_STORED_OUTLINES session parameter, 22-6

cursors

maximum number of, 16-7

object dependencies and, 21-10

opening, 7-9, 16-7

overview of, 1-17

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

recursive, 16-7

recursive SQL and, 16-7

stored procedures and, 16-18

D

dangling REFs, 13-9, 13-10

data

data blocks, 1-10, 4-2

data conversion

ANSI datatypes, 12-22

program interface, 8-25

SQL/DS and DB2 datatypes, 12-22

Data Definition Language (DDL)

embedding in PL/SQL, 16-19

commit implied by, 17-4

defined, 1-52

described, 16-4

locks, 27-28

parallel DDL, 26-3

parsing with DBMS_SQL, 16-19

processing statements, 16-14

roles and privileges, 30-20

data dictionary

data locks

conversion, 27-16

duration of, 27-15

escalation, 27-16

Data Manipulation Language (DML)

distributed transactions, 33-11

defined, 1-52

described, 16-3

locks acquired by, 27-25

parallel DML, 26-3, 26-35

partition locks, 11-45

privileges controlling, 30-5

processing statements, 16-10

serializable isolation for subqueries, 27-14

transaction model for parallel DML, 26-39

triggers and, 20-3, 20-23

data models, 1-22

data object number

extended rowid, 12-17

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

data warehousing

basic replication for, 34-12

bitmap indexes, 10-32

dimension schema objects, 1-29, 10-18

dimensions, 24-14

hierarchies, 1-29, 10-18

materialized views, 10-17

refreshing table data, 26-37

data dictionary views, 2-6

summaries, 10-17

Database, 5-3

database administrators (DBAs)

authentication, 29-12

DBA role, 14-13, 30-22

password files, 29-13

responsible for backup and recovery, 32-2

database buffers

after committing transactions, 17-6

buffer cache, 7-3, 8-8

clean, 8-8

committing transactions, 8-10

defined, 1-16, 7-3

dirty, 7-3, 8-8

free, 7-3

multiple buffer pools, 7-5

partition-extended table names, 11-65

database management system (DBMS), 1-2

object-relational DBMS, 13-2

Oracle server, 1-4

principles, 1-22

database structures

database triggers, 1-59, 20-1

See also triggers

database writer process (DBWn), 8-8

checkpoints, 8-8

defined, 8-8

least recently used algorithm (LRU), 8-8

media failure, 32-6

multiple DBWn processes, 8-8

writing to disk at checkpoints, 8-11

databases

access control

overview, 1-51

password encryption, 29-7

security domains, 29-2

backing up, 1-50, 32-23

clone database, 5-7

closing, 5-10

aborting the instance, 5-10, 32-4

configuring, 5-4

contain schemas, 29-2

defined, 1-8, 1-9

dismounting, 5-10

distributed, 1-35, 33-1

changing global database name, 7-11

nodes of, 1-35

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

site autonomy of, 33-16

statement optimization on, 23-30

table replication, 1-36

two-phase commit, 1-35

global database names, 33-4

limitations on usage, 29-15

managing

Enterprise Manager, 33-19

modes of archiving, 32-18

mounting, 5-6

name stored in control file, 32-21

open and closed, 5-2

opening, 5-7

acquiring rollback segments, 4-26

opening read-only, 5-9

recovery of, 1-45, 32-2

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

size of

structures

data dictionary, 3-6, 32-25

SYSTEM tablespace, 3-6, 3-16

datafiles

datatypes, 12-2, 12-3

DATE datatype, 12-10

arithmetic with, 12-11

changing default format of, 12-10

Julian dates, 12-11

midnight, 12-11

partition pruning, 11-22

partitioning, 11-14, 11-21

DB_BLOCK_BUFFERS parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_BLOCK_LRU_LATCHES parameter, 8-8

DB_BLOCK_SIZE parameter

buffer cache and, 7-5

system global area size and, 7-13

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 23-51

cost-based optimization, 24-9

DB_FILES parameter, 7-15

DB_NAME parameter, 32-22

DB_WRITER_PROCESSES parameter, 1-18, 8-8

DBA role, 30-22

user-defined types, 14-13

DBA_ views, 2-6

DBA_QUEUE_SCHEDULES view, 19-10

DBA_SYNONYMS.SQL script

using, 2-7

DBA_UPDATABLE_COLUMNS view, 10-15

DBMS, 1-2

general requirements, 1-51

object-relational DBMS, 13-2

DBMS_AQ package, 19-4

DBMS_AQADM package, 19-4, 19-7

DBMS_JOB package, 8-13

Oracle supplied packages, 18-16

DBMS_LOCK package, 27-39

Oracle supplied packages, 18-16

DBMS_PCLXUTIL package, 11-30

DBMS_RLS package

security policies, 30-23

uses definer rights, 30-8

DBMS_SQL package, 16-19

Oracle supplied packages, 18-16

parsing DDL statements, 16-19

DBMS_STATS package, 22-12

creating histograms, 22-11

estimated statistics, 22-14

partition statistics, 11-14

DBWn background process, 8-8

See also database writer process

DDL, 1-52, 16-4

See also Data Definition Language

dead transactions, 32-4

block-level recovery, 32-14

deadlocks

distributed transactions and, 27-18

deallocating extents, 4-14

decision support application

basic replication for, 34-11

decision support systems (DSS), 11-6

bitmap indexes, 10-33

disk striping, 26-48

materialized views, 10-17

parallel DML, 26-37

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

partitions, 11-6

performance, 11-9, 26-37

scoring tables, 26-38

dedicated servers, 8-22

defined, 1-18

examples of use, 8-24

multi-threaded servers vs., 8-16

default values, 10-8

constraints effect on, 10-8, 28-20

user-defined types, 14-4

deferred constraints

deferrable or nondeferrable, 28-21

initially deferred or immediate, 28-21

define phase of query processing, 16-12

definer rights, 18-9

name resolution, 18-19

procedure security, 30-7

degree of parallelism, 26-20, 26-23

between query operations, 26-13

parallel SQL, 26-7, 26-16

delete cascade constraint, 28-17

DELETE command, 16-4

foreign key references and, 28-16

freeing space in data blocks, 4-9

no-logging mode, 25-7

LOBs, 25-7

parallel DELETE, 26-21

triggers and, 20-2, 20-6

delete no action constraint, 28-16

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

denormalized tables, 1-29, 10-19

denormalized views

star schemas, 24-15

dependencies, 21-1

between schema objects, 21-2

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

local, 21-10

managing, 21-1

non-existent referenced objects and, 21-8

object type definitions, 14-16, 14-17

on non-existence of other objects, 21-8

Oracle Forms triggers and, 21-13

privileges and, 21-6

remote objects and, 21-10

shared pool and, 21-10

dereferencing, 13-10

implicit, 13-10

describe phase of query processing, 16-12

DETERMINISTIC functions, 23-9

function-based indexes, 21-8

deterministic functions, 23-9

dictionary

See data dictionary

dictionary cache locks, 27-30

dictionary-managed tablespaces, 3-7

different-row writers block writers, 27-10

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

dimensions, 1-29, 10-18

attributes, 1-29, 10-19

hierarchies, 1-29, 10-18

join key, 1-29, 10-19

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

star joins, 24-14

parallel load vs. parallel INSERT, 25-2

direct-load INSERT, 25-2

logging mode, 25-5

parallel INSERT, 25-3

restrictions, 25-11, 26-43

serial INSERT, 25-3

space management, 25-8

Directory-enabled Oracle Security Manager, 29-5

dirty buffer, 7-3

Fast-Start Checkpointing, 32-13

incremental checkpoint, 8-8

dirty read, 27-2, 27-10

dirty write, 27-10

DISABLE constraints, 28-22

DISABLED indexes, 21-8

disaster recovery, 32-26

disconnected environments

as in advanced replication, 34-12

discrete transaction management

summary, 17-8

discretionary access control, 1-39, 29-2

disk affinity

parallel DML, 26-49

partitions, 26-48

disk failures, 1-46, 32-5

disk space

controlling allocation for tables, 10-4

datafiles used to allocate, 3-16

disk striping

affinity, 26-48

partitions, 11-9

dispatcher processes (Dnnn)

defined, 1-20

described, 8-14

limiting SGA space per session, 29-18

listener process and, 8-14

network protocols and, 8-14

prevent startup and shutdown, 8-20

response queue and, 8-17

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

DISTINCT operator

optimizing views, 23-17

distributed databases, 33-1

distributed processing environment

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

data manipulation statements, 16-10

described, 1-33, 6-2

distributed databases vs., 33-7

materialized views (snapshots), 10-17

distributed query optimization, 33-10

distributed transactions

defined, 33-12

distributed statements, 23-4

optimizing, 23-30

parallel DDL restrictions, 26-28

parallel DML restrictions, 26-28, 26-46

routing statements to nodes, 16-11

sample table scan not supported, 23-34

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

DISTRIBUTED_TRANSACTIONS parameter, 8-12

distributing application loads

as in advanced replication, 34-7

DML, 1-52, 16-3

See also Data Manipulation Language

DML subpartition locks, 11-46

Dnnn background processes, 8-14

See also dispatcher processes

domain index, 10-43

domain indexes

extensible optimization, 22-16

user-defined statistics, 22-17

drivers, 8-26

DROP ANY TYPE privilege, 14-13

See also privileges

DROP COLUMN clause, 10-6

DROP command, 16-4

DROP TABLE command