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

access control, 23-2

discretionary, definition, 1-44

fine-grained access control, 23-24

password encryption, 22-8

privileges, 23-2

roles, 23-17

roles, definition, 1-46

ADMIN OPTION

roles, 23-20

system privileges, 23-3

administrator privileges, 5-3

statement execution audited, 24-5

Advanced Queuing, 1-16

event publication, 17-14

publish-subscribe support, 17-14

queue monitor process, 8-14

AFTER triggers, 17-10

defined, 17-10

when fired, 17-18

aggregate functions

user-defined, 13-15

alert file, 8-14

ARCn processes, 8-13

redo logs, 8-9

alias

qualifying subqueries (inline views), 10-21

ALL_ views, 4-6

ALL_UPDATABLE_COLUMNS view, 10-21

allocation of resources, 9-1

ALTER DATABASE statement, 5-7

ALTER SESSION statement, 14-5

SET CONSTRAINTS DEFERRED clause, 21-25

transaction isolation level, 20-8

ALTER statement, 14-4

ALTER SYSTEM statement, 14-5

dynamic parameters

LOG_ARCHIVE_MAX_PROCESSES, 8-13

ALTER TABLE statement

auditing, 24-7

CACHE clause, 7-8

DEALLOCATE UNUSED clause, 2-10

disable or enable constraints, 21-26

MODIFY CONSTRAINT clause, 21-27

triggers, 17-7

validate or novalidate constraints, 21-26

ALTER USER statement

temporary segments, 2-14

American National Standards Institute (ANSI)

datatypes

conversion to Oracle datatypes, 12-23

datatypes, implicit conversion, 12-24

ANALYZE statement

shared pool, 7-14

anonymous PL/SQL blocks, 14-16, 14-25

applications, 14-19

contrasted with stored procedures, 14-25

dynamic SQL, 14-20

performance, 14-25

ANSI SQL standard

datatypes of, 12-23

ANSI/ISO SQL standard

data concurrency, 20-2

isolation levels, 20-11

applications

application triggers compared with database triggers, 17-3

can find constraint violations, 21-6

context, 23-25

data dictionary references, 4-4

data warehousing, 10-48

database access through, 8-2

dependencies of, 15-11

discrete transactions, 16-11

enhancing security with, 21-6, 23-18

object dependencies and, 15-13

online transaction processing (OLTP)

reverse key indexes, 10-47

processes, 8-4

program interface and, 8-22

roles and, 23-19

security

application context, 23-25

sharing code, 7-22

transaction termination and, 16-6

architecture

client/server, definition, 1-32

overview, 1-21

ARCHIVELOG mode

archiver process (ARCn) and, 8-13

archiver process (ARCn)

described, 8-13

multiple processes, 8-13

ARCn background process, 8-13

array processing, 14-13

arrays

size of VARRAYs, 13-11

variable (VARRAYs), 13-11

asynchronous communication

in message queuing, definition, 1-38

attributes

object types, 13-2, 13-4

attributes of object types, 13-4

AUDIT statement, 14-4

locks, 20-31

auditing, 24-1

audit options, 24-3

audit records, 24-3

audit trails, 24-3

database, 24-3

operating system, 24-5, 24-6

by access, 24-12

mandated for, 24-12

by session, 24-11

prohibited with, 24-12

database and operating-system usernames, 22-4

DDL statements, 24-7

described, 24-2

distributed databases and, 24-6

DML statements, 24-7

fine-grained, 24-9

levels of, listed, 1-48

privilege use, 24-2, 24-7

range of focus, 24-3, 24-10

schema object, 24-2, 24-3, 24-8

security and, 24-7

statement, 24-2, 24-7

successful executions, 24-10

transaction independence, 24-4

types of, 24-2

unsuccessful executions, 24-10

user, 24-13

when options take effect, 24-6

authentication

database administrators, 22-13

described, 22-3

multitier, 22-10

network, 22-4

operating system, 22-4

Oracle, 22-8

public key infrastructure, 22-5

remote, 22-7

automatic segment space management, 2-6

automatic undo management, 2-16

B

back-end of client/server architecture, 6-2

background processes, 8-5

definition, 1-27

described, 8-5

diagrammed, 8-6

trace files for, 8-14

backups

overview, 1-50

types listed, 1-53

bandwidth, 18-3

base tables

definition, 1-3

BEFORE triggers, 17-10

defined, 17-10

when fired, 17-18

BFILE datatype, 12-15

binary data

BFILEs, 12-15

BLOBs, 12-14

RAW and LONG RAW, 12-15

bind variables

user-defined types, 13-18

bitmap indexes, 10-48

cardinality, 10-49

nulls and, 10-10, 10-52

parallel query and DML, 10-49

bitmap tablespace management, 3-11

bitmaps

to manage free space, 2-6

BLOBs (binary large objects), 12-14

blocking transactions, 20-11

block-level recovery, 20-23

blocks

anonymous, 14-16, 14-25

database, 2-3

BOOLEAN datatype, 12-2

branch blocks, 10-36

broker, 1-64

B-tree indexes, 10-35

compared with bitmap indexes, 10-48, 10-49

index-organized tables, 10-57

buffer caches, 7-7, 8-8

database, 7-7, 8-8

definition, 1-25

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

multiple buffer pools, 7-10

buffer pools, 7-10

BUFFER_POOL_KEEP initialization parameter, 7-10

BUFFER_POOL_RECYCLE initialization parameter, 7-10

buffers

database buffer cache

incremental checkpoint, 8-8

redo log, 7-11

redo log, definition, 1-25

business rules

enforcing in application code, 21-5

enforcing using stored procedures, 21-5

enforcing with constraints, 21-1

advantages of, 21-5

byte semantics, 12-5

C

CACHE clause, 7-8

Cache Fusion, 20-6

caches

buffer, 7-7

multiple buffer pools, 7-10

cache hit, 7-7

cache miss, 7-7

data dictionary, 4-4, 7-13

location of, 7-11

database buffer, definition, 1-25

library cache, 7-11, 7-12, 7-13

object cache, 13-18, 13-20

object views, 13-25

private SQL area, 7-12

shared SQL area, 7-11, 7-12

writing of buffers, 8-8

calls

Oracle call interface, 8-23

cannot serialize access, 20-11

cardinality, 10-49

CASCADE actions

DELETE statements and, 21-16

century, 12-12

certificate authority, 22-6

chaining of rows, 1-2, 2-7, 10-6

change data capture, 1-60

CHAR datatype, 12-3

ANSI, 12-24

blank-padded comparison semantics, 12-4

CHAR VARYING datatype, ANSI, 12-24

CHARACTER datatype

ANSI, 12-24

DB2, 12-25

SQL/DS, 12-25

character semantics, 12-5

character sets

CLOB and NCLOB datatypes, 12-15

column lengths, 12-4

NCHAR and NVARCHAR2, 12-6

CHARACTER VARYING datatype

ANSI, 12-24

CHARTOROWID function

data conversion, 12-27

check constraints, 21-20

checking mechanism, 21-23

defined, 21-20

multiple constraints on a column, 21-21

subqueries prohibited in, 21-21

checkpoint process (CKPT), 8-11

definition, 1-28

checkpoints

checkpoint process (CKPT), 8-11

control files and, 3-21

DBWn process, 8-8, 8-11

incremental, 8-8

statistics on, 8-11

CKPT background process, 8-11

client processes. See user processes

clients

in client/server architecture, definition, 1-32

client/server architectures, 6-2

definition, 1-32

diagrammed, 6-2

distributed processing in, 6-2

overview of, 6-2

program interface, 8-22

CLOB datatype, 12-15

clone databases

mounting, 5-8

cluster keys, 10-65

CLUSTER_DATABASE parameter, 5-7

clustered computer systems

Real Application Clusters, 5-3

clusters

cannot be partitioned, 11-1

definition, 1-3

dictionary locks and, 20-31

hash, 10-65

contrasted with index, 10-65

index

contrasted with hash, 10-65

indexes on, 10-28

cannot be partitioned, 11-1

keys, 10-65

affect indexing of nulls, 10-10

overview of, 10-63

rowids and, 10-9

scans of, 7-8

storage parameters of, 10-6

coalescing extents, 2-11

coalescing free space

extents, B-3

SMON process, 1-28, 8-11

within data blocks, 2-6

collections, 13-11

index-organized tables, 10-59

key compression, 10-47

nested tables, 13-12

variable arrays (VARRAYs), 13-11

columns

cardinality, 10-49

column objects, 13-8

default values for, 10-10

described, 10-5

integrity constraints, 10-5, 10-11, 21-4, 21-7

maximum in concatenated indexes, 10-31

maximum in view or table, 10-17

nested tables, 10-13

order of, 10-9

prohibiting nulls in, 21-7

pseudocolumns

ROWID, 12-17

USER, 23-8

COMMENT statement, 14-4

COMMIT comment

deprecation of, 16-9

COMMIT statement, 14-5

ending a transaction, 16-2

fast commit, 8-10

implied by DDL, 16-2

two-phase commit, 16-10

committing transactions

defined, 16-2

fast commit, 8-10

group commits, 8-10

implementation, 8-10

comparison methods, 13-7

compiled PL/SQL

advantages of, 14-24

procedures, 14-25

pseudocode, 17-21

shared pool, 14-18

triggers, 17-21

components

Data Guard, 1-63

composite indexes, 10-30

compression of free space in data blocks, 2-6

compression, index key, 10-45

concatenated indexes, 10-30

concurrency

data, definition, 1-40

described, 20-2

limits on

for each user, 22-19

transactions and, 20-17

configuration of a database

process structure, 8-2

configurations

Data Guard, 1-63

configuring

parameter file, 5-4

process structure, 8-2

CONNECT role, 23-23

connection pooling, 22-10

connections

defined, 8-4

embedded SQL, 14-5

listener process and, 6-9, 8-19

restricting, 5-6

sessions contrasted with, 8-4

with administrator privileges, 5-3

consistency

read consistency, definition, 1-40

consistency of data

See also read consistency

constants

in stored procedures, 14-19

constraints

allowed in views, 10-17

alternatives to, 21-5

applications can find violations, 21-6

CHECK, 21-20

default values and, 21-24

defined, 10-5

disabling temporarily, 21-7

effect on performance, 21-6

ENABLE or DISABLE, 21-26

enforced with indexes, 10-31

PRIMARY KEY, 21-12

UNIQUE, 21-10

FOREIGN KEY, 21-13

integrity

types listed, 1-20

integrity, definition, 1-20

mechanisms of enforcement, 21-21

modifying, 21-27

NOT NULL, 21-7, 21-11

on views, 10-23

PRIMARY KEY, 21-11

referential

effect of updates, 21-16

self-referencing, 21-14

triggers cannot violate, 17-17

triggers contrasted with, 17-5

types listed, 21-1

UNIQUE key, 21-8

partially null, 21-11

VALIDATE or NOVALIDATE, 21-26

what happens when violated, 21-5

when evaluated, 10-11

constructor methods, 13-6

content management, 1-67

contention

for data

deadlocks, 20-19

lock escalation does not occur, 20-19

for rollback segments, B-6

control files, 3-20

changes recorded, 3-21

checkpoints and, 3-21

contents, 3-20

definition, 1-8

how specified, 5-4

multiplexed, 3-22

overview, 3-20

used in mounting database, 5-6

converting data

program interface, 8-23

correlation names

inline views, 10-21

cost-based optimization

query rewrite, 10-22

CPU

utilization, 18-3

CPU allocation

rules, 9-15

CPU resources

allocation, 9-5

CPU time limit, 22-18

CPU_COUNT, 9-18

CREATE CLUSTER statement

storage parameters, 2-13

CREATE INDEX statement

storage parameters, 2-13

temporary segments, 2-14

CREATE PACKAGE statement

locks, 20-31

CREATE PROCEDURE statement

locks, 20-31

CREATE statement, 14-4

CREATE SYNONYM statement

locks, 20-31

CREATE TABLE AS SELECT

rules of parallelism

index-organized tables, 18-12, 18-13

CREATE TABLE statement

AS SELECT

compared with direct-path INSERT, 19-2

auditing, 24-7, 24-10

CACHE clause, 7-8

enable or disable constraints, 21-26

examples

column objects, 13-5

nested tables, 13-12

object tables, 13-8, 13-12

locks, 20-31

parallelism

index-organized tables, 18-12, 18-13

storage parameters, 2-13

triggers, 17-7

CREATE TEMPORARY TABLE statement, 10-13

CREATE TRIGGER statement

compiled and stored, 17-21

examples, 17-20

locks, 20-31

CREATE TYPE statement

nested tables, 13-4, 13-12

object types, 13-4

object views, 13-25

VARRAYs, 13-11

CREATE USER statement

temporary segments, 2-14

CREATE VIEW statement

examples

object views, 13-25

locks, 20-31

cursors

creating, 14-10

defined, 14-6

definition, 1-26

embedded SQL, 14-5

maximum number of, 14-6

object dependencies and, 15-10

opening, 7-18, 14-6

private SQL areas and, 7-19, 14-6

recursive, 14-7

recursive SQL and, 14-7

scrollable, 14-7

stored procedures and, 14-19

D

dangling REFs, 13-10

data

access to

concurrent, 20-2

control of, 22-2

fine-grained access control, 23-24

security domains, 22-2

concurrency, definition, 1-40

consistency of

examples of lock behavior, 20-33

locks, 20-3

manual locking, 20-32

read consistency, definition, 1-40

repeatable reads, 20-6

transaction level, 20-6

underlying principles, 20-17

how stored in tables, 10-6

integrity of, 10-5, 21-2

CHECK constraints, 21-20

enforcing, 21-4, 21-5

introduction, 1-19

referential, 21-3

types, 21-3

locks on, 20-22

data blocks, 2-2

allocating for extents, B-2

cached in memory, 8-8

coalescing extents, B-3

coalescing free space in blocks, 2-6

controlling free space in, 2-7, B-15

definition, 1-4

format, 2-4

free lists and, B-20

how rows stored in, 1-2, 10-6

overview, 2-2

read-only transactions and, 20-33

row directory, 10-9

shared in clusters, 10-63

shown in rowids, 12-18, 12-19

space available for inserted rows, B-19

stored in the buffer cache, 7-7

writing to disk, 8-8

data conversion

CHARTOROWID function, 12-27

HEXTORAW function, 12-27

program interface, 8-23

RAWTOHEX function, 12-27

RAWTONHEX function, 12-27

REFTOHEX function, 12-27

ROWIDTOCHAR function, 12-27

ROWIDTONCHAR function, 12-27

TO_CHAR function, 12-26

TO_CLOB function, 12-27

TO_DATE function, 12-26

TO_NCHAR function, 12-26

TO_NCLOB function, 12-27

TO_NUMBER function, 12-26

data definition language

auditing, 24-7

definition, 1-11

described, 14-4

embedding in PL/SQL, 14-20

locks, 20-30

parsing with DBMS_SQL, 14-20

processing statements, 14-14

roles and privileges, 23-22

data dictionary

access to, 4-2

ALL prefixed views, 4-6

cache, 7-13

location of, 7-11

content of, 4-2, 7-13

datafiles, 3-8

DBA prefixed views, 4-6

defined, 4-2

definition, 1-32

dependencies tracked by, 15-3

dictionary managed tablespaces, 3-13

DUAL table, 4-6

dynamic performance tables, 4-7

locks, 20-30

owner of, 4-3

prefixes to views of, 4-5

public synonyms for, 4-4

row cache and, 7-13

structure of, 4-2

SYSTEM tablespace, 3-8, 4-2, 4-5

USER prefixed views, 4-5

uses of, 4-3

table and column definitions, 14-11

Data Guard

broker, 1-64

components, 1-63

configurations, 1-63

log apply services

log apply services, 1-63

log transport services

log transport services, 1-63

logical standby databases, 1-64

overview, 1-63

physical standby databases, 1-64

data loading

with external tables, 10-15

data locks

conversion, 20-18

duration of, 20-17

escalation, 20-18

data manipulation language

auditing, 24-7

definition, 1-11

described, 14-3

locks acquired by, 20-27

parallel DML, 18-13

privileges controlling, 23-5

processing statements, 14-10

serializable isolation for subqueries, 20-14

triggers and, 17-4, 17-20

data models

object-relational principles, 1-32, 1-40

data object number

extended rowid, 12-18

data protection, 1-63

modes, 1-63

data security

definition, 1-43

data segments, 2-12, 10-6

definition, 1-4

data warehousing

architecture, 1-55

bitmap indexes, 10-48

dimension schema objects, 10-25

ETL, 1-54

features, 1-53

hierarchies, 10-25

invalidated views and packages, 15-7

materialized views, 1-58, 10-22

OLAP, 1-54

summaries, 10-22

database administrators (DBAs)

authentication, 22-13

data dictionary views, 4-6

DBA role, 23-23

password files, 22-14

database buffers

after committing transactions, 16-7

buffer cache, 7-7, 8-8

clean, 8-8

committing transactions, 8-10

defined, 7-7

definition, 1-25

dirty, 7-7, 8-8

free, 7-7

multiple buffer pools, 7-10

pinned, 7-7

size of cache, 7-8

writing of, 8-8

database management system (DBMS)

object-relational DBMS, 13-2

principles, 1-32

database object metadata, 4-7

Database Resource Manager, 9-1

active session pool with queuing, 9-12

and operating system control, 9-17

and performance, 9-7

automatic consumer group switching, 9-12

execution time limit, 9-13

introduction, 9-2

multiple level CPU resource allocation, 9-12

resource plans

plan schemas, 9-12

specifying a parallel degree limit, 9-12

terminology, 9-3

undo pool, 9-13

database security

overview, 1-43

database structures

control files, 3-20

data blocks, 2-2, 2-3

data dictionary, 4-1

datafiles, 3-1, 3-18

extents, 2-2, 2-8

memory, 7-1

processes, 8-1

revealing with rowids, 12-19

schema objects, 10-3

segments, 2-2, 2-12

tablespaces, 3-1, 3-7

database triggers, 17-1

and information management, 1-14

See also triggers

database writer process (DBWn), 8-8

checkpoints, 8-8

defined, 8-8

definition, 1-28

least recently used algorithm (LRU), 8-8

multiple DBWn processes, 8-8

when active, 8-8

write-ahead, 8-9

writing to disk at checkpoints, 8-11

databases

access control

password encryption, 22-8

security domains, 22-2

clone database, 5-8

closing, 5-10

terminating the instance, 5-10

configuring, 5-4

contain schemas, 22-2

distributed

changing global database name, 7-14

nodes of, definition, 1-33

distributed, definition, 1-33

limitations on usage, 22-17

links, definition, 1-3

mounting, 5-6

name stored in control file, 3-20

open and closed, 5-3

opening, 5-8

acquiring rollback segments, B-10

opening read-only, 5-9

scalability, 6-4, 18-2

shutting down, 5-10

standby, 5-7

starting up, 5-2

forced, 5-11

structures

control files, 3-20

data blocks, 2-2, 2-3

data dictionary, 4-1

datafiles, 3-1, 3-18

extents, 2-2, 2-8

logical, 2-1

memory, 7-1

processes, 8-1

revealing with rowids, 12-19

schema objects, 10-3

segments, 2-2, 2-12

tablespaces, 3-1, 3-7

datafiles

contents of, 3-18

data dictionary, 3-8

datafile 1, 3-8

SYSTEM tablespace, 3-8

definition, 1-7

in online or offline tablespaces, 3-19

named in control files, 3-21

overview of, 3-18

read-only, 3-15

relationship to tablespaces, 3-2

shown in rowids, 12-18, 12-19

SYSTEM tablespace, 3-8

taking offline, 3-19

temporary, 3-19

datatypes, 12-2, 12-3

ANSI, 12-23

array types, 13-11

BOOLEAN, 12-2

CHAR, 12-3

character, 12-3, 12-15

collections, 13-11

conversions of

by program interface, 8-23

non-Oracle types, 12-23

Oracle to another Oracle type, 12-26

DATE, 12-10

DB2, 12-23

how they relate to tables, 10-5

in PL/SQL, 12-2

list of available, 12-2

LOB datatypes, 12-13

BFILE, 12-15

BLOB, 12-14

CLOB and NCLOB, 12-15

LONG, 12-7

storage of, 10-9

multimedia, 13-3

NCHAR and NVARCHAR2, 12-6

nested tables, 10-13, 13-12

NUMBER, 12-8

object types, 13-4

RAW and LONG RAW, 12-15

ROWID, 12-16, 12-17

SQL/DS, 12-23

summary, 12-3

TIMESTAMP, 12-12

TIMESTAMP WITH LOCAL TIME ZONE, 12-12

TIMESTAMP WITH TIME ZONE, 12-12

URI, 12-26

user-defined, 13-1, 13-3

VARCHAR, 12-4

VARCHAR2, 12-4

XML, 12-25

DATE datatype, 12-10

arithmetic with, 12-11

changing default format of, 12-10

Julian dates, 12-11

midnight, 12-10

DATETIME datatypes, 12-12

daylight savings support, 12-12

DB_BLOCK_SIZE initialization parameter, 7-8

DB_BLOCK_SIZE parameter

buffer cache, 7-8

DB_CACHE_SIZE initialization parameter, 7-5, 7-6, 7-8, 7-9

DB_CACHE_SIZE parameter

buffer cache, 7-8

system global area size and, 7-5

DB_KEEP_CACHE_SIZE initialization parameter, 7-8, 7-10

DB_NAME parameter, 3-21

DB_nK_CACHE_SIZE initialization parameter, 7-9

DB_RECYCLY_CACHE_SIZE initialization parameter, 7-8, 7-10

DB2 datatypes

conversion to Oracle datatypes, 12-25

implicit conversion, 12-25

restrictions on, 12-25

DBA role, 23-23

DBA_ views, 4-6

DBA_UPDATABLE_COLUMNS view, 10-21

DBMS

object-relational DBMS, 13-2

DBMS. See database management system (DBMS)

DBMS_LOCK package, 20-41

DBMS_RLS package

security policies, 23-24

uses definer rights, 23-9

DBMS_SQL package, 14-20

parsing DDL statements, 14-20

DBWn background process, 8-8

DDL. See data definition language (DDL)

deadlocks

avoiding, 20-21

defined, 20-19

detection of, 20-20

distributed transactions and, 20-20

deallocating extents, 2-10

DECIMAL datatype

ANSI, 12-24

DB2, 12-25

SQL/DS, 12-25

decision support systems (DSS)

materialized views, 10-22

dedicated servers, 8-21

compared with shared servers, 8-15

default access driver

for external tables, 10-15

default tablespace

definition, 1-47

default temporary tablespaces, 3-10

specifying, 3-10

default values, 10-10

constraints effect on, 10-11, 21-24

deferred constraints

deferrable or nondeferrable, 21-24

initially deferred or immediate, 21-24

define phase of query processing, 14-12

definer rights

procedure security, 23-8

degree of parallelism, 18-8

parallel SQL, 18-5

delete cascade constraint, 21-16

DELETE statement, 14-3

foreign key references, 21-16

freeing space in data blocks, 2-6

triggers, 17-2, 17-7

denormalized tables, 10-25

dependencies, 15-1

between schema objects, 15-2

function-based indexes, 10-33, 15-8

local, 15-10

managing, 15-1

on non-existence of other objects, 15-10

Oracle Forms triggers and, 15-13

privileges and, 15-7

remote objects and, 15-10

shared pool and, 15-10

dereferencing, 13-10

implicit, 13-10

describe phase of query processing, 14-12

DETERMINISTIC functions

function-based indexes, 15-8

dictionary

See data dictionary

dictionary cache locks, 20-32

dictionary managed tablespaces, 3-13

different-row writers block writers, 20-11

dimensions, 10-25

attributes, 10-25

hierarchies, 10-25

join key, 10-25

normalized or denormalized tables, 10-25

direct-path INSERT, 19-2

index maintenance, 19-5

logging mode, 19-4

parallel INSERT, 19-3

parallel load compared with parallel INSERT, 19-3

serial INSERT, 19-3

dirty buffer, 7-7

incremental checkpoint, 8-8

dirty read, 20-3, 20-11

dirty write, 20-11

DISABLE constraints, 21-26

DISABLED indexes, 15-8, 15-9

disaster recovery, 1-63

discrete transaction management

summary, 16-11

discretionary access control, 22-2

definition, 1-44

disk affinities

disabling with massively parallel processing, 11-2, 11-14, 11-22

disk failure. See media failure

disk space

controlling allocation for tables, 10-6

datafiles used to allocate, 3-18

dispatcher processes (Dnnn)

definition, 1-29

described, 8-19

limiting SGA space for each session, 22-19

listener process and, 8-19

network protocols and, 8-19

prevent startup and shutdown, 8-20

response queue and, 8-16

user processes connect through Oracle Net Services, 8-16, 8-19

distributed databases

auditing and, 24-6

client/server architectures and, 6-2

deadlocks and, 20-20

definition, 1-33

dependent schema objects and, 15-10

job queue processes, 8-12

recoverer process (RECO) and, 8-12

remote dependencies, 15-11

server can also be client in, 6-2

distributed processing environment

client/server architecture in, 6-2

data manipulation statements, 14-10

definition, 1-32

described, 6-2

materialized views (snapshots), 10-22

distributed transactions

naming, 16-9

parallel DDL restrictions, 18-12

parallel DML restrictions, 18-12

routing statements to nodes, 14-11

two-phase commit and, 16-10

DML. See data manipulation language (DML)

Dnnn background processes, 8-19

See also dispatcher processes

DOUBLE PRECISION datatype (ANSI), 12-24

drivers, 8-23

DROP statement, 14-4

DROP TABLE statement

auditing, 24-7

triggers, 17-7

DUAL table, 4-6

dynamic partitioning, 18-4

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

dynamic predicates

in security policies, 23-25

dynamic SQL

DBMS_SQL package, 14-20

embedded, 14-20

E

editing stored outlines, 14-16

embedded SQL, 14-5

dynamic SQL in PL/SQL, 14-20

ENABLE constraints, 21-26

Enterprise Manager

ALERT file, 8-15

checkpoint statistics, 8-11

executing a package, 14-28

executing a procedure, 14-23

granting roles, 23-20

lock and latch monitors, 20-31

PL/SQL, 14-19

schema object privileges, 23-4

showing size of SGA, 7-5

shutdown, 5-10, 5-11

SQL statements, 14-2

startup, 5-5

statistics monitor, 22-20

enterprise users, 22-2

errors

in embedded SQL, 14-5

tracked in trace files, 8-14

exceptions

during trigger execution, 17-19

raising, 14-20

stored procedures and, 14-20

exclusive locks

row locks (TX), 20-22

RX locks, 20-25

table locks (TM), 20-23

exclusive mode, B-11

execution plans, 14-15

EXPLAIN PLAN, 14-3

location of, 7-12

parsing SQL, 14-11

EXP_FULL_DATABASE role, 23-23

EXPLAIN PLAN statement, 14-3

explicit locking, 20-32

Export utility

definition, 1-8

extended rowid format, 12-18

extents

allocating, 2-9

allocating data blocks for, B-2

allocation to rollback segments

after segment creation, B-8

at segment creation, B-6

as collections of data blocks, 2-8

coalescing, 2-11

deallocation

from rollback segments, B-9

when performed, 2-10

defined, 2-2

definition, 1-4

dictionary managed, 3-13

dropping rollback segments and, B-9

in rollback segments

changing current, B-6

incremental, 2-8

locally managed, 3-11

materialized views, 2-11

overview of, 2-8

external procedures, 14-26

external tables

parallel access, 10-16

F

failures

instance

recovery from, 5-8, 5-10

internal errors

tracked in trace files, 8-14

statement and process, 8-12

types listed, 1-50

fast commit, 8-10

fast refresh, 10-24

fetching rows in a query, 14-13

embedded SQL, 14-6

file management locks, 20-32

files

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

initialization parameter, 5-4, 5-5

password, 22-14

administrator privileges, 5-3

See also control files, datafiles, redo log files

FINAL and NOT FINAL types, 13-13

fine-grained access control, 23-24

fine-grained auditing, 1-49, 24-9

FIPS standard, 14-6

fixed views, 4-7

flagging of nonstandard features, 14-6

flashback query

overview, 20-41

uses, 20-43

FLOAT datatype

DB2, 12-25

SQL/DS, 12-25

FLOAT datatype (ANSI), 12-24

FORCE LOGGING mode, 19-5

foreign key constraints

changes in parent key values, 21-16

constraint checking, 21-23

deleting parent table rows and, 21-16

maximum number of columns in, 21-13

nulls and, 21-15

share locks, 21-17

updating parent key tables, 21-16

updating tables, 21-17, 21-19

foreign keys

privilege to use parent key, 23-6

fractional seconds, 12-12

free lists, B-20

free space

automatic segment space management, 2-6

coalescing extents, B-3

SMON process, 8-11

coalescing within data blocks, 2-6

free lists, B-20

managing, 2-6

parameters for data blocks, 2-7, B-15

section of data blocks, 2-5

free space management

in-segment, 2-6

front-ends, 6-2

full table scans

LRU algorithm and, 7-8

parallel execution, 18-3, 18-4

function-based indexes, 10-32

dependencies, 10-33, 15-8

DISABLED, 15-8, 15-9

privileges, 10-33, 15-8

UNUSABLE, 15-9

functions

definition, 1-13

function-based indexes, 10-32

PL/SQL, 14-21

contrasted with procedures, 14-21

DETERMINISTIC, 15-8

privileges for, 23-8

roles, 23-21

See also procedures

SQL, 14-2

COUNT, 10-52

in CHECK constraints, 21-21

in views, 10-19

NVL, 10-10

G

Global Cache Service process (LMS), 8-14

global database names

shared pool and, 7-14

global partitioned indexes

maintenance, 11-16

globalization support

character sets for, 12-4

CHECK constraints and, 21-21

NCHAR and NVARCHAR2 datatypes, 12-6

NCLOB datatype, 12-15

views and, 10-19

GRANT ANY PRIVILEGE system privilege, 23-3

GRANT statement, 14-4

locks, 20-31

granted privileges

definition, 1-46

granting

privileges and roles, 23-3

granules, 7-5

GRAPHIC datatype

DB2, 12-25

SQL/DS, 12-25

GROUP BY clause

temporary tablespaces, 3-16

group commits, 8-10

guesses in logical rowids, 12-21

staleness, 12-22

statistics for, 12-23

H

handles for SQL statements, 7-18

definition, 1-26

hash clusters, 10-65

contrasted with index, 10-65

headers

of data blocks, 2-4

of row pieces, 10-7

Heterogeneous Services

overview, 1-38

HEXTORAW function

data conversion, 12-27

HI_SHARED_MEMORY_ADDRESS parameter, 7-16

hierarchical materialized views. See multitier materialized views

hierarchies, 10-25

join key, 10-25

levels, 10-25

high water mark, B-15

definition, 2-3, B-15

direct-path INSERT, 19-4

I

immediate constraints, 21-24

IMP_FULL_DATABASE role, 23-23

implicit dereferencing, 13-10

Import utility

definition, 1-9

incremental checkpoint, 8-8

incremental refresh, 10-24

index segments, 2-13

indexes, 10-28

bitmap indexes, 10-48, 10-53

nulls and, 10-10

parallel query and DML, 10-49

branch blocks, 10-36

B-tree structure of, 10-35

building

using an existing index, 10-29

cardinality, 10-49

cluster

cannot be partitioned, 11-1

composite, 10-30

concatenated, 10-30

definition, 1-3

described, 10-28

domain, 10-62

enforcing integrity constraints, 21-10, 21-12

extensible, 10-62

function-based, 10-32

dependencies, 10-33, 15-8

DETERMINISTIC functions, 15-8

DISABLED, 15-9

optimization with, 10-33

privileges, 10-33, 15-8

index-organized tables, 10-57

logical rowids, 10-60, 12-21

secondary indexes, 10-60

internal structure of, 10-35

key compression, 10-45

keys and, 10-31

primary key constraints, 21-12

unique key constraints, 21-10

leaf blocks, 10-36

location of, 10-34

LONG RAW datatypes prohibit, 12-16

nonunique, 10-30

nulls and, 10-10, 10-31, 10-52

on complex data types, 10-62

overview of, 10-28

partitioned tables, 10-53

partitions, 11-2

performance and, 10-29

rebuilt after direct-path INSERT, 19-5

reverse key indexes, 10-47

rowids and, 10-36

storage format of, 10-34

unique, 10-30

when used with views, 10-19

index-organized tables, 10-57

benefits, 10-58

key compression in, 10-47, 10-59

logical rowids, 10-60, 12-21

parallel CREATE, 18-12, 18-13

secondary indexes on, 10-60

in-doubt transactions, 5-9, B-8

initialization parameter file, 5-4, 5-5

startup, 5-5

initialization parameters

BUFFER_POOL_KEEP, 7-10

BUFFER_POOL_RECYCLE, 7-10

CLUSTER_DATABASE, 5-7

DB_BLOCK_SIZE, 7-8

DB_CACHE_SIZE, 7-5, 7-8

DB_NAME, 3-21

HI_SHARED_MEMORY_ADDRESS, 7-16

LOCK_SGA, 7-16

LOG_ARCHIVE_MAX_PROCESSES, 8-13

LOG_BUFFER, 7-5, 7-11

MAX_SHARED_SERVERS, 8-20

NLS_NUMERIC_CHARACTERS, 12-9

OPEN_CURSORS, 7-18, 14-6

REMOTE_DEPENDENCIES_MODE, 15-11

ROLLBACK_SEGMENTS, B-11

SERVICE_NAMES, 6-9

SHARED_MEMORY_ADDRESS, 7-16

SHARED_POOL_SIZE, 7-5, 7-12

SHARED_SERVERS, 8-20

SKIP_UNUSABLE_INDEXES, 15-9

SORT_AREA_SIZE, 2-14

SQL_TRACE, 8-14

TRANSACTIONS, B-11

TRANSACTIONS_PER_ROLLBACK_SEGMENT, B-11

UNDO_MANAGEMENT, 5-9

USE_INDIRECT_DATA_BUFFERS, 7-17

initially deferred constraints, 21-24

initially immediate constraints, 21-24

INIT.ORA. See initialization parameter file.

inline views, 10-21

example, 10-21

INSERT statement, 14-3

direct-path INSERT, 19-2

no-logging mode, 19-4

free lists, B-20

triggers, 17-2, 17-7

BEFORE triggers, 17-10

instance failure

definition, 1-51

instance recovery

SMON process, 1-28, 8-11

instances

acquire rollback segments, B-10

associating with databases, 5-3, 5-6

definition, 1-24

described, 5-2

diagrammed, 8-6

memory structures of, 7-2

multiple-process, 8-2

process structure, 8-2

recovery of, 5-10

opening a database, 5-8

SMON process, 8-11

restricted mode, 5-6

service names, 6-9

shutting down, 5-10, 5-11

starting, 5-5

terminating, 5-10

INSTEAD OF triggers, 17-12

nested tables, 13-26

object views, 13-26

INT datatype (ANSI), 12-24

INTEGER datatype

ANSI, 12-24

DB2, 12-25

SQL/DS, 12-25

integrity constraints, 21-2

default column values and, 10-11

definition, 1-20

types listed, 1-20

See also constraints

interMedia, 1-67

internal errors tracked in trace files, 8-14

Internet File System, 1-68

intrablock chaining, 10-7

invoker rights

procedure security, 23-9

supplied packages, 23-9

I/O

parallel execution, 18-3

IS NULL predicate, 10-10

ISO SQL standard, 12-23

isolation levels

choosing, 20-13

read committed, 20-8

setting, 20-7, 20-33

J

Java

attributes, 14-33

class hierarchy, 14-34

classes, 14-32

interfaces, 14-35

methods, 14-33

overview, 14-31

polymorphism, 14-36

triggers, 17-1, 17-8

Java object types, 13-21

Java Virtual Machine, 14-37

job queue processes, 8-12

definition, 1-29

jobs, 8-2

join views, 10-20

joins

encapsulated in views, 10-18

views, 10-20

K

key compression, 10-45

keys

cluster, 10-65

defined, 21-9

foreign, 21-13

in constraints, definition, 1-20

indexes and, 10-31

compression, 10-45

PRIMARY KEY constraints, 21-12

reverse key, 10-47

UNIQUE constraints, 21-10

maximum storage for values, 10-31

parent, 21-13, 21-14

primary, 21-11

referenced, 21-13

reverse key indexes, 10-47

unique, 21-8

composite, 21-9, 21-11

L

large pool, 7-15

definition, 1-26

LARGE_POOL_SIZE initialization parameter, 7-5

latches

described, 20-31

LDAP, 22-14

leaf blocks, 10-36

least recently used (LRU) algorithm

database buffers and, 7-7

dictionary cache, 4-4

full table scans and, 7-8

latches, 8-8

shared SQL pool, 7-12, 7-13

LGWR background process, 8-9

library cache, 7-11, 7-12, 7-13

lightweight sessions, 22-10

listener process, 6-9

service names, 6-9

listeners, 6-9, 8-19

service names, 6-9

LMS background process, 8-14

LNOCI, 8-23

anonymous blocks, 14-19

bind variables, 14-13

object cache, 13-20

OCIObjectFlush, 13-26

OCIObjectPin, 13-26

loader access driver, 10-15

LOB datatypes, 12-13

BFILE, 12-15

BLOBs, 12-14

CLOBs and NCLOBs, 12-15

restrictions

parallel DDL, 18-12

local indexes

bitmap indexes

on partitioned tables, 10-53

parallel query and DML, 10-49

locally managed tablespaces, 3-11

LOCK TABLE statement, 14-3

LOCK_SGA parameter, 7-16

locking

indexed foreign keys and, 21-19

unindexed foreign keys and, 21-17

locks, 20-3

after committing transactions, 16-7

automatic, 20-17, 20-21

conversion, 20-18

data, 20-22

duration of, 20-17

deadlocks, 20-19, 20-20

avoiding, 20-21

dictionary, 20-30

clusters and, 20-31

duration of, 20-31

dictionary cache, 20-32

DML acquired, 20-29

diagrammed, 20-27

escalation does not occur, 20-19

exclusive table locks (X), 20-27

file management locks, 20-32

how Oracle uses, 20-17

internal, 20-31

latches and, 20-31

log management locks, 20-32

manual, 20-32

examples of behavior, 20-33

object level locking, 13-20

Oracle Lock Management Services, 20-41

overview of, 20-3

parse, 14-11, 20-31

rollback segment, 20-32

row (TX), 20-22

row exclusive locks (RX), 20-25

row share table locks (RS), 20-24

share row exclusive locks (SRX), 20-26

share table locks (S), 20-26

share-subexclusive locks (SSX), 20-26

subexclusive table locks (SX), 20-25

subshare table locks (SS), 20-24

table (TM), 20-23

table lock modes, 20-23

tablespace, 20-32

types of, 20-21

uses for, 1-42

log entries, 1-7

See also redo log files, 1-7

log management locks, 20-32

log switch

archiver process, 8-13

log writer process (LGWR), 8-9

definition, 1-28

group commits, 8-10

redo log buffers and, 7-11

starting new ARCn processes, 8-13

system change numbers, 16-7

write-ahead, 8-9

LOG_ARCHIVE_MAX_PROCESSES parameter, 8-13

LOG_BUFFER initialization parameter, 7-5

LOG_BUFFER parameter, 7-11

system global area size and, 7-5

logging mode

direct-path INSERT, 19-4

NOARCHIVELOG mode and, 19-5

parallel DDL, 18-12

logical blocks, 2-2

logical database structures

definition, 1-2, 1-3

tablespaces, 3-7

logical reads limit, 22-18

logical rowids, 12-21

index on index-organized table, 10-60

physical guesses, 10-60, 12-21

staleness of guesses, 12-22

statistics for guesses, 12-23

logical standby databases, 1-64

LogMiner, 1-65

LONG datatype

automatically the last column, 10-10

defined, 12-7

storage of, 10-9

LONG RAW datatype, 12-15

indexing prohibited on, 12-16

similarity to LONG datatype, 12-16

LONG VARCHAR datatype

DB2, 12-25

SQL/DS, 12-25

LONG VARGRAPHIC datatype

DB2, 12-25

SQL/DS, 12-25

LRU, 7-7, 7-8, 8-8

dictionary cache, 4-4

shared SQL pool, 7-12, 7-13