Oracle9i Database Concepts -- Index

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

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

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

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

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

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

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

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

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

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

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

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

manual locking, 20-32

map methods, 13-7

massively parallel processing

disk affinity, 11-2, 11-14, 11-22

multiple Oracle instances, 5-3

massively parallel systems, 18-3

materialized view logs, 10-24

materialized views, 10-22

deallocating extents, 2-11

definition, 1-58

materialized view logs, 10-24

multitier, definition, 1-35

partitioned, 10-23, 11-1

refresh

job queue processes, 8-12

refreshing, 10-24

MAX_SHARED_SERVERS parameter, 8-20

maximize availability, 1-63

maximize data protection, 1-63

maximize performance, 1-63

media failure

definition, 1-51

memory

allocation for SQL statements, 7-13

content of, 7-2

cursors (statement handles), definition, 1-26

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

overview of structures, 1-25

processes use of, 8-2

shared SQL areas, 7-12

software code areas, 7-22

stored procedures, 14-24

structures in, 7-2

system global area (SGA)

allocation in, 7-3

initialization parameters, 7-5, 7-16

locking into physical memory, 7-16

SGA size, 7-4

starting address, 7-16

See also system global area

MERGE statement, 14-3

message queuing

publish-subscribe support

event publication, 17-14

queue monitor process, 8-14

metadata

viewing, 4-7

methods

comparison methods, 13-7

constructor methods, 13-6

privileges on, 23-12

methods of object types, 13-4

map methods, 13-7

order methods, 13-7

PL/SQL, 13-18

purchase order example, 13-2, 13-5

selfish style of invocation, 13-6

mobile computing environment

materialized views, 10-22

modes

table lock, 20-23

monitoring user actions, 24-2

MPP. See massively parallel processing

multiblock writes, 8-8

multimedia datatypes, 13-3

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

multiplexing

control files, 3-22

multithreaded server. See shared server

multitier materialized views

definition, 1-35

multiuser environments, 8-2

multiversion concurrency control, 20-5

mutating errors and triggers, 17-19

NATIONAL CHAR datatype (ANSI), 12-24

NATIONAL CHAR VARYING datatype (ANSI), 12-24

NATIONAL CHARACTER datatype (ANSI), 12-24

NATIONAL CHARACTER VARYING datatype (ANSI), 12-24

NCHAR datatype, 12-6

ANSI, 12-24

NCHAR VARYING datatype (ANSI), 12-24

NCLOB datatype, 12-15

nested tables, 10-13, 13-12

index-organized tables, 10-59

key compression, 10-47

INSTEAD OF triggers, 13-26

updating in views, 13-26

network listener process

connection requests, 8-16, 8-19

networks

client/server architecture use of, 6-2

communication protocols, 8-23, 8-24

dispatcher processes and, 8-16, 8-19

drivers, 8-23

listener processes of, 6-9, 8-19

network authentication service, 22-4

Oracle Net Services, 6-7

two-task mode and, 8-22

NLS_DATE_FORMAT parameter, 12-10

NLS_NUMERIC_CHARACTERS parameter, 12-9

NOARCHIVELOG mode

LOGGING mode and, 19-5

NOAUDIT statement, 14-4

locks, 20-31

nodes

of distributed databases, definition, 1-33

NOLOGGING mode

direct-path INSERT, 19-4

parallel DDL, 18-12

nonprefixed indexes, 11-14

nonrepeatable reads, 20-11

nonunique indexes, 10-30

NOREVERSE clause for indexes, 10-47

normalized tables, 10-25

NOT INSTANTIABLE types and methods, 13-14

NOT NULL constraints

constraint checking, 21-23

defined, 21-7

implied by PRIMARY KEY, 21-12

UNIQUE keys and, 21-11

NOVALIDATE constraints, 21-26

NOWAIT parameter

with savepoints, 16-9

nulls

as default values, 10-11

column order and, 10-10

converting to values, 10-10

defined, 10-10

foreign keys and, 21-15

how stored, 10-10

indexes and, 10-10, 10-31, 10-52

inequality in UNIQUE key, 21-11

non-null values for, 10-10

prohibited in primary keys, 21-11

prohibiting, 21-7

UNIQUE key constraints and, 21-11

unknown in comparisons, 10-10

NUMBER datatype, 12-8

internal format of, 12-9

rounding, 12-9

NUMERIC datatype (ANSI), 12-24

NVARCHAR2 datatype, 12-6

NVL function, 10-10

object cache

object views, 13-25

OCI, 13-20

Pro*C, 13-18

object identifiers, 13-24, 13-25

collections

key compression, 10-47, 10-59

for object views, 13-24, 13-25

WITH OBJECT OID clause, 13-25

object privileges, 23-3

See also schema object privileges

object tables, 13-3, 13-8

row objects, 13-8

virtual object tables, 13-23

Object Type Translator (OTT), 13-20

object types, 13-2, 13-4

attributes of, 13-2, 13-4

column objects, 13-8

comparison methods for, 13-7

constructor methods for, 13-6

locking in cache, 13-20

methods of, 13-4

PL/SQL, 13-18

purchase order example, 13-2, 13-5

object views, 10-21

Oracle type translator, 13-20

purchase order example, 13-2, 13-4

restrictions

parallel DDL, 18-12

row objects, 13-8

SQLJ, 13-21

object views, 10-21

advantages of, 13-23

defining, 13-24

modifiability, 17-12

nested tables, 13-26

object identifiers for, 13-24, 13-25

updating, 13-26

use of INSTEAD OF triggers with, 13-26

object-relational database management system, 13-2

object-relational database management system (ORDBMS)

definition, 1-40

principles, 1-32

object-relational DBMS, 13-2

objects

privileges on, 23-12

OID (see Oracle Internet Directory), 22-14

online redo logs

checkpoints, 3-21

recorded in control file, 3-21

online transaction processing (OLTP)

reverse key indexes, 10-47

OPEN_CURSORS parameter, 14-6

managing private SQL areas, 7-18

operating systems

authentication by, 22-4

block size, 2-3

communications software, 8-24

privileges for administrator, 5-3

roles and, 23-23

OPTIMAL storage parameter, B-9

optimization

function-based indexes, 10-33

index build, 10-29

parallel SQL, 18-5

query rewrite, 10-22

in security policies, 23-25

optimizer, 14-14

Oracle

adherence to standards

integrity constraints, 21-5

architecture, overview, 1-21

client/server architecture of, 6-2

configurations of, 8-2

multiple-process Oracle, 8-2

instances, 5-2

processes of, 8-5

scalability of, 6-4

SQL processing, 14-8

Oracle blocks, 2-2

Oracle Call Interface. See OCI

Oracle Certificate Authority, 22-6

Oracle code, 8-2, 8-22

Oracle eLocation, 1-67

Oracle Enterprise Login Assistant, 22-6

Oracle Enterprise Manager. See Enterprise Manager

Oracle Enterprise Security Manager, 22-6

Oracle Forms

object dependencies and, 15-13

PL/SQL, 14-18

Oracle Internet Directory, 6-10, 22-6

Oracle Net Services, 6-7

client/server systems use of, 6-7

overview, 6-7

shared server requirement, 8-16, 8-19

Oracle processes

definition, 1-27

Oracle program interface (OPI), 8-23

Oracle Streams, 1-36

overview, 1-36

Oracle type translator (OTT), 13-20

Oracle Wallet Manager, 22-5

Oracle wallets, 22-5

Oracle XA

session memory in the large pool, 7-15

ORDBMS. See object-relational database management system (ORDBMS)

order methods, 13-7

OTT. See Object Type Translator (OTT)

packages, 14-27

advantages of, 14-29

as program units, definition, 1-14

auditing, 24-8

dynamic SQL, 14-20

examples of, 23-10, 23-11

executing, 14-18

for locking, 20-41

private, 14-29

privileges

divided by construct, 23-10

executing, 23-8, 23-10

public, 14-29

session state and, 15-7

shared SQL areas and, 7-12

supplied packages

invoker or definer rights, 23-9

pages, 2-2

parallel access

to external tables, 10-16

parallel DDL

restrictions

LOBs, 18-12

object types, 18-12

parallel DML, 18-13

bitmap indexes, 10-49

parallel execution, 18-2

coordinator, 18-4

full table scans, 18-3

introduction, 18-3

of table functions, 14-26

process classification, 11-2, 11-14, 11-16, 11-22

server, 18-4

index maintenance, 19-5

servers, 18-4

tuning, 18-2

See also parallel SQL

parallel query, 18-12

bitmap indexes, 10-49

parallel SQL, 18-2

coordinator process, 18-4

optimizer, 18-5

Real Application Clusters and, 18-1

server processes, 18-4

direct-path INSERT, 19-5

See also parallel execution

parallelism

degree, 18-8

parameters

initialization, 5-4

locking behavior, 20-21

See also initialization parameters

storage, 2-7, 2-8, B-15

parse trees

construction of, 14-7

in shared SQL area, 7-12

parsing, 14-11

DBMS_SQL package, 14-20

embedded SQL, 14-5

parse calls, 14-8

parse locks, 14-11, 20-31

performed, 14-8

SQL statements, 14-11, 14-20

partitions, 11-2

bitmap indexes, 10-53

dynamic partitioning, 18-4

hash partitioning, 11-9

materialized views, 10-23, 11-1

nonprefixed indexes, 11-14

segments, 2-12, 2-13

passwords

account locking, 22-8

administrator privileges, 5-3

complexity verification, 22-9

connecting with, 8-4

connecting without, 22-4

database user authentication, 22-8

encryption, 22-8

expiration, 22-9

password files, 22-14

password reuse, 22-9

used in roles, 23-18

PCTFREE storage parameter

how it works, B-16

PCTUSED and, B-18

PCTUSED storage parameter

how it works, B-17

PCTFREE and, B-18

performance

constraint effects on, 21-6

dynamic performance tables (V$), 4-7

group commits, 8-10

index build, 10-29

packages, 14-29

resource limits and, 22-17

SGA size and, 7-4

sort operations, 3-16

PGA. See program global area (PGA)

PGA_AGGREGATE_TARGET initialization parameter, 7-20

phantom reads, 20-11

physical database structures

control files, 3-20

datafiles, 3-18

definition, 1-6

physical guesses in logical rowids, 12-21

staleness, 12-22

statistics for, 12-23

physical standby databases, 1-64

pipelined table functions, 14-26

PKI, 22-5

plan

SQL execution, 14-3, 14-11

plan schemas for Database Resource Manager, 9-12

PL/SQL, 14-16

anonymous blocks, 14-16, 14-25

auditing of statements within, 24-4

bind variables

user-defined types, 13-18

database triggers, 17-1

datatypes, 12-2

dynamic SQL, 14-20

exception handling, 14-20

executing, 14-17

external procedures, 14-26

gateway, 14-31

language constructs, 14-19

native execution, 14-17

object views, 13-25

overview of, 14-16

packages, 14-27

parse locks, 20-31

parsing DDL statements, 14-20

PL/SQL engine, 14-17

products containing, 14-18

program units, 7-12, 14-16, 14-21

compiled, 14-18, 14-25

shared SQL areas and, 7-12

roles in procedures, 23-21

stored procedures, 14-16, 14-21

user locks, 20-41

user-defined datatypes, 13-18

PL/SQL Server Pages, 14-30

PMON background process, 6-9, 8-12

point-in-time recovery

clone database, 5-8

precompilers

anonymous blocks, 14-19

bind variables, 14-13

cursors, 14-10

embedded SQL, 14-5

FIPS flagger, 14-6

predicates

dynamic

in security policies, 23-25

prefixes of data dictionary views, 4-5

PRIMARY KEY constraints, 21-11

constraint checking, 21-23

described, 21-11

indexes used to enforce, 21-12

name of, 21-13

maximum number of columns, 21-13

NOT NULL constraints implied by, 21-12

primary keys, 21-12

advantages of, 21-12

defined, 21-3

private rollback segments, B-10

private SQL areas

cursors and, 7-18

described, 7-12

how managed, 7-18

privileges

administrator, 5-3

statement execution audited, 24-5

auditing use of, 24-7

checked when parsing, 14-11

definition, 1-45

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

granted, definition, 1-46

granting, 23-3, 23-4

examples of, 23-10, 23-11

overview of, 23-2

procedures, 23-8

creating and altering, 23-10

executing, 23-8

in packages, 23-10

revoked

object dependencies and, 15-7

revoking, 23-3, 23-4

roles, 23-17

restrictions on, 23-22

schema object, 23-3

DML and DDL operations, 23-5

granting and revoking, 23-4

packages, 23-10

procedures, 23-8

schema object, definition, 1-46

system, 23-2

granting and revoking, 23-3

system, definition, 1-46

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

trigger privileges, 23-9

views, 23-6

creating, 23-6

using, 23-7

Pro*C/C++

processing SQL statements, 14-10

user-defined datatypes, 13-18

procedures, 14-16, 14-21

advantages of, 14-23

auditing, 24-8

contrasted with anonymous blocks, 14-25

contrasted with functions, 14-21

cursors and, 14-19

definer rights, 23-8

roles disabled, 23-21

definition, 1-13

dependency tracking in, 15-6

examples of, 23-10, 23-11

executing, 14-18

external procedures, 14-26

INVALID status, 15-6

invoker rights, 23-9

roles used, 23-21

supplied packages, 23-9

prerequisites for compilation of, 15-5

privileges

create or alter, 23-10

executing, 23-8

executing in packages, 23-10

security enhanced by, 14-23, 23-8

shared SQL areas and, 7-12

stored procedures, 14-16, 14-17, 14-21

supplied packages

invoker or definer rights, 23-9

triggers, 17-2

process global area (PGA)

See also program global area (PGA)

process monitor process (PMON)

cleans up timed-out sessions, 22-19

described, 8-12

processes, 8-2

archiver (ARCn), 8-13

background, 8-5

diagrammed, 8-6

checkpoint (CKPT), 8-11

checkpoints and, 8-8

classes of parallel execution, 11-2, 11-14, 11-16, 11-22

database writer (DBWn), 8-8

dedicated server, 8-19

definition, 1-26

dispatcher (Dnnn), 8-19

distributed transaction resolution, 8-12

Global Cache Service (LMS), 8-14

job queue, 8-12

listener, 6-9, 8-19

shared servers and, 8-16

log writer (LGWR), 8-9

multiple-process Oracle, 8-2

Oracle, 8-5

Oracle, definition, 1-27

parallel execution coordinator, 18-4

parallel execution servers, 18-4

direct-path INSERT, 19-5

process monitor (PMON), 8-12

queue monitor (QMNn), 8-14

recoverer (RECO), 8-12

server, 8-5

dedicated, 8-21

shared, 8-19

shadow, 8-21

shared server, 8-15

client requests and, 8-16

structure, 8-2

system monitor (SMON), 8-11

trace files for, 8-14

user, 8-4

recovery from failure of, 8-12

sharing server processes, 8-19

processing

DDL statements, 14-14

distributed, definition, 1-32

DML statements, 14-10

overview, 14-8

parallel SQL, 18-2

queries, 14-11

profiles

password management, 22-8

user, definition, 1-47

when to use, 22-20

program global area (PGA), 7-17

definition, 1-26

shared server, 8-20

shared servers, 8-20

program interface, 8-22

definition, 1-30

Oracle side (OPI), 8-23

structure of, 8-23

two-task mode in, 8-22

user side (UPI), 8-23

program units, 14-16, 14-21

prerequisites for compilation of, 15-5

shared pool and, 7-12

protection modes, 1-63

proxies, 22-10

pseudocode

triggers, 17-21

pseudocolumns

CHECK constraints prohibit

LEVEL and ROWNUM, 21-21

modifying views, 17-13

ROWID, 12-17

USER, 23-8

PSP. See PL/SQL Server Pages

public key infrastructure, 22-5

public rollback segments, B-10

PUBLIC user group, 22-16, 23-21

publication

DDL statements, 17-16

DML statements, 17-16

logon/logoff events, 17-15

system events

server errors, 17-15

startup/shutdown, 17-15

using triggers, 17-14

publish-subscribe support

event publication, 17-14

triggers, 17-14

purchase order example

object types, 13-2, 13-4

QMNn background process, 8-14

queries

composite indexes, 10-30

default locking of, 20-28

define phase, 14-12

describe phase, 14-12

fetching rows, 14-11

in DML, 14-3

inline views, 10-21

merged with view queries, 10-19

parallel processing, 18-2

phases of, 20-5

processing, 14-11

read consistency of, 20-5

stored as views, 10-16

temporary segments and, 2-14, 14-12

triggers use of, 17-20

query rewrite, 10-22

dynamic predicates in security policies, 23-25

queue monitor process (QMNn), 8-14

definition, 1-30

queuing

publish-subscribe support

event publication, 17-14

queue monitor process, 8-14

Quiesce Database, 20-15

quiesce database

uses for, 1-42

quotas

revoking tablespace access and, 22-16

setting to zero, 22-16

SYS user not subject to, 22-16

tablespace, 22-14

temporary segments ignore, 22-15

tablespace, definition, 1-47

RADIUS, 22-7

RAW datatype, 12-15

RAWTOHEX function

data conversion, 12-27

RAWTONHEX function

data conversion, 12-27

read committed isolation, 20-8

read consistency, 20-2, 20-4

Cache Fusion, 20-6

definition, 1-40

dirty read, 20-3, 20-11

multiversion consistency model, 20-4

nonrepeatable read, 20-11

phantom read, 20-11

queries, 14-12, 20-4

Real Application Clusters, 20-6

rollback segments and, B-5

statement level, 20-5

subqueries in DML, 20-14

transactions, 20-4, 20-6

triggers and, 17-17, 17-20

read snapshot time, 20-11

read uncommitted, 20-3

readers block writers, 20-11

read-only

databases

opening, 5-9

tablespaces, 3-15

transactions, definition, 1-41

reads

data block

limits on, 22-18

dirty, 20-3

repeatable, 20-6

Real Application Clusters

databases and instances, 5-3

exclusive mode

rollback segments and, B-11

isolation levels, 20-12

lock processes, 8-14

mounting a database using, 5-7

parallel SQL, 18-1

read consistency, 20-6

reverse key indexes, 10-47

shared mode

rollback segments and, B-11

system change numbers, 8-10

system monitor process and, 8-11

temporary tablespaces, 3-16

REAL datatype (ANSI), 12-24

recoverer process (RECO), 8-12

definition, 1-29

in-doubt transactions, 5-9, 16-10

recovery

block-level recovery, 20-23

distributed processing in, 8-12

general overview, 1-50

instance failure, 5-10

instance recovery

SMON process, 1-28, 8-11

media recovery

dispatcher processes, 8-20

of distributed transactions, 5-9

opening a database, 5-8

point-in-time

clone database, 5-8

process recovery, 8-12

required after terminating instance, 5-10

SMON process, 1-28, 8-11

recursive SQL

cursors and, 14-7

redo log buffers

definition, 1-25

redo logs

archiver process (ARCn), 8-13

buffer management, 8-9

buffers, 7-11

circular buffer, 8-9

committing a transaction, 8-10

definition, 1-52

files named in control file, 3-21

log sequence numbers

recorded in control file, 3-21

log switch

archiver process, 8-13

log writer process, 7-11, 8-9

multiplexed, definition, 1-7

overview, 1-7

size of buffers, 7-11

when temporary segments in, 2-15

writing buffers, 8-9

written before transaction commit, 8-10

referenced

keys, 21-13

objects

dependencies, 15-2

REFERENCES privilege

when granted through a role, 23-22

referential integrity, 20-12, 21-13

cascade rule, 21-3

examples of, 21-21

PRIMARY KEY constraints, 21-11

restrict rule, 21-3

self-referential constraints, 21-14, 21-21

set to default rule, 21-3

set to null rule, 21-3

refresh

incremental, 10-24

job queue processes, 8-12

materialized views, 10-24

REFs

dangling, 13-10

dereferencing of, 13-10

for rows of object views, 13-24

implicit dereferencing of, 13-10

pinning, 13-26

scoped, 13-10

REFTOHEX function

data conversion, 12-27

relational database management system (RDBMS), 13-2

SQL, 14-2

remote dependencies, 15-11

remote transactions

parallel DML and DDL restrictions, 18-12

REMOTE_DEPENDENCIES_MODE parameter, 15-11

RENAME statement, 14-4

repeatable reads, 20-3

replication

definition, 1-35

materialized views (snapshots), 10-22

reserved words, 14-2

resource allocation, 9-1, 9-2

CPU time, 9-13

directives, 9-11

levels and priorities, 9-16

methods, 9-3

multilevel plans, 9-13

plan-level methods, 9-11

resource consumer groups

definition, 9-3

resource consumer-group methods, 9-11

resource consumers

grouping, 9-7

resource limits

call level, 22-18

connect time for each session, 22-19

CPU time limit, 22-18

determining values for, 22-20

idle time in each session, 22-19

logical reads limit, 22-18

number of sessions for each user, 22-19

private SGA space for each session, 22-19

resource plan directives

definition, 9-3

resource plans

activating, 9-8

definition, 9-3

dynamic, 9-8

grouping, 9-8

hierarchical, 9-10

levels, 9-10

performance, 9-10

persistent, 9-8

plan schemas, 9-12

RESOURCE role, 23-23

response queues, 8-16

restricted mode

starting instances in, 5-6

restricted rowid format, 12-19

restrictions

parallel DDL, 18-12

remote transactions, 18-12

parallel DML

remote transactions, 18-12

resumable space allocation

overview, 16-5

resumable statements. See resumable space allocation

REVERSE clause for indexes, 10-47

reverse key indexes, 10-47

REVOKE statement, 14-4

locks, 20-31

rewrite

predicates in security policies, 23-25

using materialized views, 10-22

roles, 23-17

application, 23-19

CONNECT role, 23-23

DBA role, 23-23

DDL statements and, 23-22

definer-rights procedures disable, 23-21

definition, 1-46

dependency management in, 23-22

enabled or disabled, 23-20

EXP_FULL_DATABASE role, 23-23

functionality, 23-2

granting, 23-3, 23-20

IMP_FULL_DATABASE role, 23-23

in applications, 23-18

invoker-rights procedures use, 23-21

managing through operating system, 23-23

naming, 23-21

predefined, 23-23

RESOURCE role, 23-23

restrictions on privileges of, 23-22

revoking, 23-20

schemas do not contain, 23-21

secure application roles, 23-26

security domains of, 23-21

setting in PL/SQL blocks, 23-21

use of passwords with, 23-18

user, 23-19

users capable of granting, 23-20

uses of, 23-18

rollback, 16-7, B-4

definition, 1-17

described, 16-7

ending a transaction, 16-2, 16-7

statement-level, 16-4

to a savepoint, 16-8

rollback entries, B-4

rollback segments, B-4

access to, B-4

acquired during startup, 5-8

allocation of extents for, B-6

new extents, B-8

clashes when acquiring, B-11

committing transactions and, B-5

contention for, B-6

deallocating extents from, B-9

deferred, B-14

dropping, B-9

restrictions on, B-14

how transactions write to, B-6

in-doubt distributed transactions, B-8

invalid, B-12

locks on, 20-32

moving to the next extent of, B-6

number of transactions per, B-6

offline, B-12, B-14

offline tablespaces and, B-14

online, B-12, B-14

overview of, B-4

partly available, B-12

private, B-10

public, B-10

read consistency and, 20-4, B-5

recovery needed for, B-12

states of, B-12

SYSTEM rollback segment, B-10

transactions and, B-5

when acquired, B-10

when used, B-5

written circularly, B-5

ROLLBACK statement, 14-5

rolling back, 16-2, 16-7

row cache, 7-13

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

row directories, 2-5

row locking, 20-11, 20-22

block-level recovery, 20-23

serializable transactions and, 20-8

row objects, 13-8

row pieces, 1-2, 10-6

headers, 10-8

how identified, 10-9

row triggers, 17-9

when fired, 17-18

See also triggers

ROWID datatype, 12-16, 12-17

extended rowid format, 12-18

restricted rowid format, 12-19

rowids, 10-9

accessing, 12-17

changes in, 12-17

in non-Oracle databases, 12-23

internal use of, 12-17, 12-21

logical, 12-16

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

of clustered rows, 10-9

physical, 12-16

row migration, 2-7

sorting indexes by, 10-36

universal, 12-16

ROWIDTOCHAR function

data conversion, 12-27

ROWIDTONCHAR function

data conversion, 12-27

row-level locking, 20-11, 20-22

rows, 10-5

addresses of, 10-9

chaining across blocks, 1-2, 2-7, 10-6

clustered, 10-9

rowids of, 10-9

described, 10-5

fetched, 14-11

format of in data blocks, 2-5

headers, 10-7

locking, 20-11, 20-22

locks on, 20-22, 20-24

logical rowids, 12-21

index-organized tables, 10-60

migrating to new block, 2-7

pieces of, 10-7

row objects, 13-8

row-level security, 23-24

shown in rowids, 12-18, 12-19

size of, 10-6

storage format of, 10-6

triggers on, 17-9

when rowid changes, 12-17

same-row writers block writers, 20-11

SAVEPOINT statement, 14-5

savepoints, 16-8

described, 16-8

implicit, 16-4

rolling back to, 16-8

scalability

client/server architecture, 6-4

parallel SQL execution, 18-2

scans

full table

LRU algorithm, 7-8

parallel query, 18-3

table scan and CACHE clause, 7-8

schema object privileges, 23-3

definition, 1-46

DML and DDL operations, 23-5

granting and revoking, 23-4

views, 23-6

schema objects, 10-1

auditing, 24-8

creating

tablespace quota required, 22-15

default tablespace for, 22-15

definition, 1-2, 1-32

dependencies of, 15-2

and distributed databases, 15-13

and views, 10-20

on non-existence of other objects, 15-10

triggers manage, 17-17

dependent on lost privileges, 15-7

dimensions, 10-25

in a revoked tablespace, 22-16

information in data dictionary, 4-2

list of, 10-2

materialized views, 10-22

privileges on, 23-3

relationship to datafiles, 3-19, 10-3

trigger dependencies on, 17-21

user-defined types, 13-3

schemas, 22-2

contents of, 10-3

contrasted with tablespaces, 10-3

defined, 22-2

definition of, 10-2

user-defined datatypes, 13-18

SCN. See system change numbers

scoped REFs, 13-10

secure application roles, 23-26

security, 22-2

administrator privileges, 5-3

application enforcement of, 23-18

auditing, 24-2, 24-7

data, definition, 1-43

discretionary access control, 22-2

discretionary access control, definition, 1-44

domains, 22-2

domains, definition, 1-45

dynamic predicates, 23-25

enforcement mechanisms listed, 1-44

fine-grained access control, 23-24

overview, 1-43

passwords, 22-8

policies

implementing, 23-25

procedures enhance, 23-8

program interface enforcement of, 8-22

security policies, 23-24

system, 4-3

system, definition, 1-43

views and, 10-18

views enhance, 23-7

security domains, 22-2

definition, 1-45

enabled roles and, 23-20

tablespace quotas, 22-14

segment space management, automatic, 2-6

segments, 2-12

data, 2-12

data, definition, 1-4

deallocating extents from, 2-10

defined, 2-3

definition, 1-4

header block, 2-8

index, 2-13

overview of, 2-12

rollback, B-4

table

high water mark, 19-4

temporary, 2-13, 10-14

allocating, 2-13

cleaned up by SMON, 8-11

dropping, 2-11

ignore quotas, 22-16

operations that require, 2-14

tablespace containing, 2-14

SELECT statement, 14-3

composite indexes, 10-30

subqueries, 14-12

See also queries

selfish style of method invocation, 13-6

sequences, 10-26

auditing, 24-8

CHECK constraints prohibit, 21-21

independence from tables, 10-26

length of numbers, 10-26

number generation, 10-25

server processes, 8-5

listener process and, 6-9

servers

client/server architecture, 6-2

dedicated, 8-21

shared servers contrasted with, 8-15

in client/server architecture, definition, 1-32

shared

architecture, 8-3, 8-15

dedicated servers contrasted with, 8-15

processes of, 8-15, 8-19

server-side scripts, 14-31

service names, 6-9

SERVICE_NAMES parameter, 6-9

session control statements, 14-5

SESSION_ROLES view

queried from PL/SQL block, 23-21

sessions

auditing by, 24-11

connections contrasted with, 8-4

defined, 8-4, 24-11

lightweight, 22-10

limits for each user, 22-19

memory allocation in the large pool, 7-15

package state and, 15-7

time limits on, 22-19

when auditing options take effect, 24-6

SET CONSTRAINTS statement

DEFERRABLE or IMMEDIATE, 21-25

SET ROLE statement, 14-5

SET TRANSACTION statement, 14-5

ISOLATION LEVEL, 20-7, 20-33

READ ONLY clause, B-5

SGA. See system global area

SGA_MAX_SIZE initialization parameter, 7-4, 7-16

shadow processes, 8-21

share locks

on foreign keys, 21-17

share table locks (S), 20-26

shared global area (SGA), 7-3

shared mode

rollback segments, B-11

shared pool, 7-11

allocation of, 7-13

ANALYZE statement, 7-14

definition, 1-25

dependency management and, 7-14

described, 7-11

flushing, 7-14

object dependencies and, 15-10

row cache and, 7-13

size of, 7-12

shared server, 8-15

dedicated server contrasted with, 8-15

described, 8-3, 8-15

dispatcher processes, 8-19

limiting private SQL areas, 22-19

Oracle Net Services or SQL*Net V2 requirement, 8-16, 8-19

private SQL areas, 7-18

processes, 8-19

processes needed for, 8-15

restricted operations in, 8-20

session memory in the large pool, 7-15

shared server processes (Snnn), 8-19

described, 8-19

shared SQL areas, 7-12, 14-7

ANALYZE statement, 7-14

definition, 1-25

dependency management and, 7-14

described, 7-12

loading SQL into, 14-11

overview of, 14-7

parse locks and, 20-31

procedures, packages, triggers and, 7-12

size of, 7-12

SHARED_MEMORY_ADDRESS parameter, 7-16

SHARED_POOL_SIZE initialization parameter, 7-5

SHARED_POOL_SIZE parameter, 7-12

system global area size and, 7-5

SHARED_SERVERS parameter, 8-20

shutdown, 5-10, 5-11

abnormal, 5-6, 5-11

deallocation of the SGA, 7-3

prohibited by dispatcher processes, 8-20

steps, 5-10

SHUTDOWN ABORT statement, 5-11

signature checking, 15-11

SKIP_UNUSABLE_INDEXES parameter, 15-9

SMALLINT datatype

ANSI, 12-24

DB2, 12-25

SQL/DS, 12-25

SMON background process, 8-11

See also system monitor process

SMON process, 8-11

software code areas, 7-22

shared by programs and utilities, 7-22

sort operations, 3-16

sort segments, 3-16

SORT_AREA_SIZE parameter, 2-14

space management

compression of free space in blocks, 2-6

data blocks, 2-7, B-15

extents, 2-8

PCTFREE, B-16

PCTUSED, B-17

row chaining, 2-7

segments, 2-12

SQL, 14-2

cursors used in, 14-6

data definition language (DDL), 14-4

data manipulation language (DML), 14-3

dynamic SQL, 14-20

embedded, 14-5

user-defined datatypes, 13-18

functions, 14-2

COUNT, 10-52

in CHECK constraints, 21-21

NVL, 10-10

memory allocation for, 7-13

overview of, 14-2

parallel execution, 18-2

parsing of, 14-7

PL/SQL and, 14-16

recursive, 14-6

cursors and, 14-7

reserved words, 14-2

session control statements, 14-5

shared SQL, 14-7

statement-level rollback, 16-4

system control statements, 14-5

transaction control statements, 14-5

transactions and, 16-2, 16-6

types of statements in, 14-3

user-defined datatypes, 13-17

embedded SQL, 13-18

OCI, 13-20

SQL areas

private, 7-12

shared, 7-12, 14-7

shared, definition, 1-25

SQL statements, 14-2, 14-8

array processing, 14-13

auditing, 24-7, 24-10

when records generated, 24-4

creating cursors, 14-10

dictionary cache locks and, 20-32

distributed

routing to nodes, 14-11

embedded, 14-5

execution, 14-8, 14-13

handles, definition, 1-26

number of triggers fired by single, 17-18

parallel execution, 18-2

parallelizing, 18-5

parse locks, 20-31

parsing, 14-11

privileges required for, 23-3

referencing dependent objects, 15-4

resource limits and, 22-18

successful execution, 16-3

transactions, 14-14

triggers on, 17-2, 17-9

triggering events, 17-7

types of, 14-3

SQL*Loader

definition, 1-9

direct load

similar to direct-path INSERT, 19-2

SQL*Menu

PL/SQL, 14-18

SQL*Module

FIPS flagger, 14-6

SQL*Net

See Oracle Net Services

SQL*Plus, 1-21

ALERT file, 8-15

anonymous blocks, 14-19

connecting with, 22-4

executing a package, 14-28

executing a procedure, 14-23

lock and latch monitors, 20-31

session variables, 14-19

showing size of SGA, 7-5

SQL statements, 14-2

statistics monitor, 22-20

SQL_TRACE parameter, 8-14

SQL92, 20-2

SQL-99 extensions, 1-59

SQL/DS datatypes

conversion to Oracle datatypes, 12-25

implicit conversion, 12-25

restrictions on, 12-25

SQLJ object types, 13-21

standards

ANSI/ISO, 21-5

isolation levels, 20-2, 20-11

FIPS, 14-6

integrity constraints, 21-5

standby database

mounting, 5-7

startup, 5-2, 5-5

allocation of the SGA, 7-3

starting address, 7-16

forcing, 5-6

prohibited by dispatcher processes, 8-20

restricted mode, 5-6

steps, 5-5

statement failure

definition, 1-50

statement triggers, 17-9

described, 17-9

when fired, 17-18

See also triggers

statement-level read consistency, 20-5

statements

resumable, overview, 16-5

statistics

checkpoint, 8-11

storage

datafiles, 3-18

indexes, 10-34

logical structures, 3-7, 10-3

nulls, 10-10

restricting for users, 22-15

revoking tablespaces and, 22-16

tablespace quotas and, 22-15

triggers, 17-2, 17-21

view definitions, 10-19

STORAGE clause

using, 2-8

storage parameters

OPTIMAL (in rollback segments), B-9

setting, 2-8

stored functions, 14-21

stored outlines, 14-15

editing, 14-16

stored procedures, 14-16, 14-21

calling, 14-21

contrasted with anonymous blocks, 14-25

triggers contrasted with, 17-2

variables and constants, 14-19

See also procedures

Streams. See Oracle Streams

Structured Query Language (SQL), 14-2

See also SQL

structures

data blocks

shown in rowids, 12-19

data dictionary, 4-1

datafiles

shown in rowids, 12-19

locking, 20-30

logical, 2-1

data blocks, 2-2, 2-3

extents, 2-2, 2-8

schema objects, 10-3

segments, 2-2, 2-12

tablespaces, 3-1, 3-7

memory, 7-1

physical

control files, 3-20

datafiles, 3-1, 3-18

processes, 8-1

subqueries, 14-12

CHECK constraints prohibit, 21-21

in DML statements

serializable isolation, 20-14

inline views, 10-21

query processing, 14-12

See also queries

summaries, 10-22

supplied packages

invoker or definer rights, 23-9

symmetric multiprocessors, 18-3

synchronous communication

in message queuing, definition, 1-38

synonyms

constraints indirectly affect, 21-5

described, 10-27

for data dictionary views, 4-4

inherit privileges from object, 23-4

private, 10-27

public, 10-27

uses of, 10-27

SYS username

data dictionary tables owned by, 4-3

security domain of, 22-3

statement execution audited, 24-5

temporary schema objects owned by, 22-16

V$ views, 4-7

SYSDBA privilege, 5-3

SYSOPER privilege, 5-3

system change numbers (SCN)

committed transactions, 16-7

defined, 16-7

read consistency and, 20-5

redo logs, 8-10

when determined, 20-5

system control statements, 14-5

system global area (SGA), 7-3

allocating, 5-5

contents of, 7-4

data dictionary cache, 4-4, 7-13

database buffer cache, 7-7

definition, 1-25

diagram, 5-2

fixed, 7-4

large pool, 7-15

limiting private SQL areas, 22-19

overview of, 7-3

redo log buffer, 7-11, 16-6

rollback segments and, 16-6

shared and writable, 7-4

shared pool, 7-11

size of, 7-4

variable parameters, 5-4

when allocated, 7-3

system monitor process (SMON), 8-11

defined, 8-11

definition, 1-28

Real Application Clusters and, 8-11

temporary segment cleanup, 8-11

system privileges, 23-2

ADMIN OPTION, 23-3

definition, 1-46

described, 23-2

granting and revoking, 23-3

SYSTEM rollback segment, B-10

system security

definition, 1-43

SYSTEM tablespace, 3-7

data dictionary stored in, 3-8, 4-2, 4-5

locally managed, 3-7

online requirement of, 3-13

procedures stored in, 3-8

SYSTEM username

security domain of, 22-3

table functions, 14-26

parallel execution, 14-26

pipelined, 14-26

tables

affect dependent views, 15-5

auditing, 24-8

base

relationship to views, 10-17

clustered, 10-63

clustered, definition, 1-3

contained in tablespaces, 10-6

controlling space allocation for, 10-6

directories, 2-5

DUAL, 4-6

dynamic partitioning, 18-4

enable or disable constraints, 21-26

external, 10-14

full table scan and buffer cache, 7-8

how data is stored in, 10-6

indexes and, 10-28

index-organized

key compression in, 10-47, 10-59

index-organized tables, 10-57

logical rowids, 10-60, 12-21

integrity constraints, 21-2, 21-5

locks on, 20-23, 20-24, 20-26

maximum number of columns in, 10-17

nested tables, 10-13, 13-12

normalized or denormalized, 10-25

object tables, 13-3, 13-8

virtual, 13-23

overview of, 10-5

partitions, 11-2

presented in views, 10-16

privileges on, 23-5

specifying tablespaces for, 10-6

temporary, 10-13

segments in, 2-14

triggers used in, 17-2

validate or novalidate constraints, 21-26

virtual or viewed, 1-3

tablespace point-in-time recovery

clone database, 5-8

tablespaces, 3-7

contrasted with schemas, 10-3

default for object creation, 22-15

default for object creation, definition, 1-47

definition, 1-5

described, 3-7

dictionary managed, 3-13

how specified for tables, 10-6

locally managed, 3-11

locks on, 20-32

moving or copying to another database, 3-17

offline, 3-13, 3-19

and index data, 3-15

remain offline on remount, 3-14

online, 3-13, 3-19

online and offline distinguished, 1-6

overview of, 3-7

quotas on, 22-14, 22-15

limited and unlimited, 22-15

no default, 22-15

quotas, definition, 1-47

read-only, 3-15

relationship to datafiles, 3-2

revoking access from users, 22-16

size of, 3-3

space allocation, 3-11

temporary, 3-16

default for user, 22-15

temporary, definition, 1-47

used for temporary segments, 2-14

See also SYSTEM tablespace

tasks, 8-2

tempfiles, 3-19

temporary segments, 2-14, 10-14

allocating, 2-14

allocation for queries, 2-14

deallocating extents from, 2-11

dropping, 2-11

ignore quotas, 22-16

operations that require, 2-14

tablespace containing, 2-14

when not in redo log, 2-15

temporary tables, 10-13

temporary tablespaces, 3-16

default, 3-10

definition, 1-47

threads

shared server, 8-15, 8-19

three-valued logic (true, false, unknown)

produced by nulls, 10-10

TIME datatype

DB2, 12-25

SQL/DS, 12-25

time stamp checking, 15-11

time zones

in date/time columns, 12-12

TIMESTAMP datatype, 12-12

DB2, 12-25

SQL/DS, 12-25

TIMESTAMP WITH LOCAL TIME ZONE datatype, 12-12

TIMESTAMP WITH TIME ZONE datatype, 12-12

TO_CHAR function

data conversion, 12-26

globalization support default in CHECK constraints, 21-21

globalization support default in views, 10-19

Julian dates, 12-11

TO_CLOB function

data conversion, 12-27

TO_DATE function, 12-10

data conversion, 12-26

globalization support default in CHECK constraints, 21-21

globalization support default in views, 10-19

Julian dates, 12-11

TO_NCHAR function

data conversion, 12-26

TO_NCLOB function

data conversion, 12-27

TO_NUMBER function, 12-9

data conversion, 12-26

globalization support default in CHECK constraints, 21-21

globalization support default in views, 10-19

Julian dates, 12-11

trace files, 8-14

LGWR trace file, 8-9

transaction control statements, 14-5

in autonomous PL/SQL blocks, 16-13

transaction set consistency, 20-10, 20-11

transaction tables, B-5

reset at recovery, 8-12

transactions, 16-1

assigning system change numbers, 16-7

assigning to rollback segments, B-5

autonomous, 16-12

within a PL/SQL block, 16-12

block-level recovery, 20-23

committing, 8-10, 16-4, 16-6

group commits, 8-10

use of rollback segments, B-5

committing, definition, 1-18

concurrency and, 20-17

controlling transactions, 14-14

deadlocks and, 16-4, 20-19

defining and controlling, 14-14

definition, 1-13

described, 16-2

discrete transactions, 14-14, 16-11

distributed

deadlocks and, 20-20

parallel DDL restrictions, 18-12

parallel DML restrictions, 18-12

resolving automatically, 8-12

two-phase commit, 16-10

distribution among rollback segments of, B-6

end of, 16-5

consistent data, 14-14

in-doubt

limit rollback segment access, B-14

resolving automatically, 5-9, 16-10

rollback segments and, B-8

use partly available segments, B-14

naming, 16-9

read consistency of, 20-6

read consistency, definition, 1-41

read-only

not assigned to rollback segments, B-5

read-only, definition, 1-41

redo log files written before commit, 8-10

rollback segments and, B-5

rolling back, 16-7

and offline tablespaces, B-14

partially, 16-8

use of rollback segments, B-5

rolling back, definition, 1-19

savepoints in, 16-8

serializable, 20-7

space used in data blocks for, 2-5

start of, 16-5

statement level rollback and, 16-4

system change numbers, 8-10

terminating the application and, 16-6

transaction control statements, 14-5

triggers and, 17-20

writing to rollback segments, B-6

TRANSACTIONS parameter, B-11

TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter, B-11

transient type descriptions, 13-19

triggers, 17-1

action, 17-8

timing of, 17-10

AFTER triggers, 17-10

as program units, definition, 1-14

auditing, 24-8

BEFORE triggers, 17-10

cascading, 17-4

compared with Oracle Forms triggers, 17-3

constraints apply to, 17-17

constraints contrasted with, 17-5

data access and, 17-20

dependency management of, 15-6, 17-21

enabled triggers, 17-17

enabled or disabled, 17-17

enforcing data integrity with, 21-5

events, 17-7

examples of, 17-20

firing (executing), 17-2, 17-21

privileges required, 17-21

steps involved, 17-17

timing of, 17-18

INSTEAD OF triggers, 17-12

object views and, 13-26

INVALID status, 15-6

Java, 17-8

overview of, 17-2

parts of, 17-6

privileges for executing, 23-9

roles, 23-21

procedures contrasted with, 17-2

prohibited in views, 10-17

publish-subscribe support, 17-14

restrictions, 17-8

row, 17-9

schema object dependencies, 17-17, 17-21

sequence for firing multiple, 17-18

shared SQL areas and, 7-12

statement, 17-9

storage of, 17-21

types of, 17-9

UNKNOWN does not fire, 17-8

uses of, 17-4

TRUNCATE statement, 14-4

two-phase commit

transaction management, 16-10

triggers, 17-17

two-task mode

listener process and, 8-19

network communication and, 8-22

program interface in, 8-22

type descriptions

dynamic creation and access, 13-19

transient, 13-19

type inheritance, 13-13

types

privileges on, 23-12

See datatypes, object types

UDAG (User-Defined Aggregate Functions), 13-15

UDAGs (User-Defined Aggregate Functions)

creation and use of, 13-15

Ultra Search, 1-67

undo, 1-5

See also rollback

undo management, automatic, 2-16

undo tablespaces, 3-8

Unicode, 12-3, 12-5, 12-6, 12-15

unique indexes, 10-30

UNIQUE key constraints, 21-8

composite keys, 21-9, 21-11

constraint checking, 21-23

indexes used to enforce, 21-10

maximum number of columns, 21-10

NOT NULL constraints and, 21-11

nulls and, 21-11

size limit of, 21-10

unique keys, 21-9

composite, 21-9, 21-11

UNUSABLE indexes

function-based, 15-9

update no action constraint, 21-16

UPDATE statement, 14-3

foreign key references, 21-16

freeing space in data blocks, 2-6

triggers, 17-2, 17-7

BEFORE triggers, 17-10

updates

object views, 13-26

updatability of object views, 13-26

updatability of views, 10-20, 17-12, 17-13

updatable join views, 10-20

update intensive environments, 20-9

updating tables

with parent keys, 21-17, 21-19

UROWID datatype, 12-16

USE_INDIRECT_DATA_BUFFERS parameter, 7-17

user processes

connections and, 8-4

dedicated server processes and, 8-21

definition, 1-26

sessions and, 8-4

shared server processes and, 8-19

user profiles

definition, 1-47

user program interface (UPI), 8-23

USER pseudocolumn, 23-8

USER_ views, 4-5

USER_UPDATABLE_COLUMNS view, 10-21

User-Defined Aggregate Functions (UDAGs)

creation and use of, 13-15

user-defined aggregate functions (UDAGs), 13-15

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

users, 22-2

access rights, 22-2

auditing, 24-13

authentication of, 22-3

dedicated servers and, 8-21

default tablespaces of, 22-15

enterprise, 22-2

listed in data dictionary, 4-2

locks, 20-41

multiuser environments, 8-2

password encryption, 22-8

processes of, 8-4

profiles of, 22-20

PUBLIC user group, 22-16, 23-21

roles and, 23-17

for types of users, 23-19

schemas of, 22-2

security domains of, 22-2, 23-21

tablespace quotas of, 22-14

temporary tablespaces of, 2-14, 22-15

usernames, 22-2

sessions and connections, 8-4

V$BUFFER_POOL view, 7-10

V_$ and V$ views, 4-7

VALIDATE constraints, 21-26

VARCHAR datatype, 12-4

DB2, 12-25

SQL/DS, 12-25

VARCHAR2 datatype, 12-4

non-padded comparison semantics, 12-4

similarity to RAW datatype, 12-16

VARGRAPHIC datatype

DB2, 12-25

SQL/DS, 12-25

variables

bind variables

user-defined types, 13-18

embedded SQL, 14-5

in stored procedures, 14-19

object variables, 13-25

varrays, 13-11

index-organized tables, 10-59

key compression, 10-47

view hierarchies, 13-27

views, 10-16

altering base tables and, 15-5

auditing, 24-8

constraints indirectly affect, 21-5

containing expressions, 17-13

data dictionary

updatable columns, 10-20

definition expanded, 15-5

dependency status of, 15-5

fixed views, 4-7

globalization support parameters in, 10-19

how stored, 10-17

indexes and, 10-19

inherently modifiable, 17-13

inline views, 10-21

INSTEAD OF triggers, 17-12

materialized views, 10-22

materialized views, definition, 1-58

maximum number of columns in, 10-17

modifiable, 17-13

modifying, 17-12

object views, 10-21

updatability, 13-26

overview of, 10-16

prerequisites for compilation of, 15-5

privileges for, 23-6

pseudocolumns, 17-13

schema object dependencies, 10-20, 15-4

security applications of, 23-7

SQL functions in, 10-19

triggers prohibited in, 10-17

updatability, 10-20, 13-26, 17-13

uses of, 10-18

waits for blocking transaction, 20-11

Wallet Manager, 22-5

wallets, 22-5

warehouse

materialized views, 10-22

Web page scripting, 14-31

WITH OBJECT OID clause, 13-25

write-ahead, 8-9

writers block readers, 20-11

X.509 certificates, 22-5

XA

session memory in the large pool, 7-15

XDK, 1-16

XML datatypes, 12-25

XML DB, 1-15

XMLType datatype, 1-16, 12-25

year 2000, 12-12