Oracle9i Database Concepts -- Index

access control, 25-2

discretionary, definition, 2-20

fine-grained access control, 25-24

password encryption, 24-8

privileges, 25-2

roles, 25-17

roles, definition, 2-22

ADMIN OPTION

roles, 25-20

system privileges, 25-3

administrator privileges, 6-3

connections audited, 26-5

statement execution not audited, 26-5

Advanced Queuing

event publication, 18-17

publish-subscribe support, 18-17

queue monitor process, 9-14

uses for, 2-8

AFTER triggers, 18-10

defined, 18-10

when fired, 18-20

aggregate functions

user-defined, 14-14

alert file, 9-14

ARCn processes, 9-13

redo logs, 9-9

alias

qualifying subqueries (inline views), 11-21

ALL_ views, 5-6

ALL_UPDATABLE_COLUMNS view, 11-20

allocation of resources, 10-1

ALTER DATABASE statement, 6-8

ALTER SESSION statement, 16-5

SET CONSTRAINTS DEFERRED clause, 23-25

transaction isolation level, 22-7, 22-32

ALTER statement, 16-4

ALTER SYSTEM statement, 16-5

dynamic parameters

LOG_ARCHIVE_MAX_PROCESSES, 9-14

ALTER TABLE statement

auditing, 26-7

CACHE clause, 8-7

DEALLOCATE UNUSED clause, 3-16

disable or enable constraints, 23-26

MODIFY CONSTRAINT clause, 23-27

triggers, 18-7

validate or novalidate constraints, 23-26

ALTER USER statement

temporary segments, 3-21

ANALYZE statement

shared pool, 8-13

anonymous PL/SQL blocks, 16-18, 16-30

applications, 16-20

calling a stored procedure, 16-21

contrasted with stored procedures, 16-30

dynamic SQL, 16-22

performance, 16-31

ANSI SQL standard

datatypes of, 13-24

Oracle certification, 1-3

ANSI/ISO SQL standard, 1-3

data concurrency, 22-2

isolation levels, 22-10

application

application triggers compared with database triggers, 18-3

applications

can find constraint violations, 23-6

context, 25-26

data dictionary references, 5-4

data warehousing, 11-47

database access through, 9-2

dependencies of, 19-12

discrete transactions, 17-11

enhancing security with, 23-6, 25-18

object dependencies and, 19-14

online transaction processing (OLTP)

reverse key indexes, 11-46

processes, 9-4

program interface and, 9-22

roles and, 25-19

security

application context, 25-26

sharing code, 8-21

transaction termination and, 17-6

architecture

client/server, definition, 2-2

of Data Guard, 2-39

overview, 1-12

ARCHIVE_LAG_TARGET parameter, 2-42

archived redo logs

definition, 2-28

ARCHIVELOG mode

archiver process (ARCn) and, 9-13

definition, 2-28

archiver (ARCn) process

definition, 1-18

archiver process (ARCn)

described, 9-13

multiple processes, 9-14

ARCn background process, 9-13

array processing, 16-15

arrays

size of VARRAYs, 14-11

variable (VARRAYs), 14-11

asynchronous communication

in message queuing, definition, 2-9

attributes

definition, 16-35

object types, 14-2, 14-4

attributes of object types, 14-4

AUDIT statement, 16-4

locks, 22-30

auditing, 26-1

audit options, 26-3

audit records, 26-3

audit trails, 26-3

database, 26-3

operating system, 26-5, 26-6

by access, 26-13

mandated for, 26-14

by session, 26-12

prohibited with, 26-14

connect with administrator privileges, 26-5

database and operating-system usernames, 24-4

DDL statements, 26-7

described, 26-2

distributed databases and, 26-6

DML statements, 26-7

fine-grained, 26-9

levels of, listed, 2-24

privilege use, 26-2, 26-7

range of focus, 26-3, 26-11

schema object, 26-2, 26-3, 26-8

security and, 26-7

startup and shutdown, 26-5

statement, 26-2, 26-7

successful executions, 26-11

transaction independence, 26-4

types of, 26-2

unsuccessful executions, 26-11

user, 26-14

when options take effect, 26-6

Aurora (Oracle8i JVM), 16-37

authentication

database administrators, 24-14

described, 24-3

multitier, 24-10

network, 24-4

operating system, 24-4

Oracle, 24-8

public key infrastructure, 24-5

remote, 24-7

automatic segment space management, 3-6

automatic undo management, 3-22

definition, 2-29

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

background processes, 9-5

definition, 1-16

described, 9-5

diagrammed, 9-6

trace files for, 9-14

backup

general overview, 2-25

backups

partial database backups, definition, 2-30

types listed, 2-30

whole database backup, definition, 2-30

bandwidth, 20-3

base tables

data dictionary, 5-3

definition, 1-24

BEFORE triggers, 18-10

defined, 18-10

when fired, 18-20

BFILE datatype, 13-16

binary data

BFILEs, 13-16

BLOBs, 13-15

RAW and LONG RAW, 13-16

bind variables

user-defined types, 14-17

bitmap indexes, 11-47

cardinality, 11-48

nulls and, 11-9, 11-51

parallel query and DML, 11-48

bitmap tablespace management, 4-12

bitmaps

to manage free space, 3-6

BLOBs (binary large objects), 13-15

block size

non-standard

in tablespaces, 4-13

blocking transactions, 22-11

block-level recovery, 22-22

blocks

anonymous, 16-18, 16-30

database, 3-3

BOOLEAN datatype, 13-2

branch blocks, 11-35

B-tree indexes, 11-34

compared with bitmap indexes, 11-47, 11-48

index-organized tables, 11-57

buffer caches, 8-6, 9-8

database, 8-6, 9-8

definition, 1-14

extended buffer cache (32-bit), 8-16

multiple buffer pools, 8-9

buffer pools, 8-9

BUFFER_POOL_KEEP initialization parameter, 8-9

BUFFER_POOL_RECYCLE initialization parameter, 8-9

buffers

database buffer cache

incremental checkpoint, 9-8

redo log, 8-10

redo log, definition, 1-14

business rules

enforcing in application code, 23-5

enforcing using stored procedures, 23-5

enforcing with constraints, 23-1

advantages of, 23-5

bytecode

defined, 16-37

CACHE clause, 8-7

Cache Fusion, 22-6

caches

buffer, 8-6

multiple buffer pools, 8-9

cache hit, 8-6

cache miss, 8-6

data dictionary, 5-4, 8-12

location of, 8-10

database buffer, definition, 1-14

library cache, 8-10, 8-11, 8-12

object cache, 14-18, 14-19

object views, 15-4

private SQL area, 8-11

shared SQL area, 8-10, 8-11

writing of buffers, 9-8

calls

Oracle call interface, 9-23

cannot serialize access, 22-11

cardinality, 11-48

CASCADE actions

DELETE statements and, 23-16

century, 13-12

certificate authority, 24-6

chaining of rows, 1-10, 3-7, 11-6

CHAR datatype, 13-3

blank-padded comparison semantics, 13-4

character sets

CLOB and NCLOB datatypes, 13-15

column lengths, 13-4

for various languages, 6-5

NCHAR and NVARCHAR2, 13-7

CHARTOROWID function

data conversion, 13-27

check constraints, 23-21

checking mechanism, 23-23

defined, 23-21

multiple constraints on a column, 23-21

subqueries prohibited in, 23-21

checkpoint (CKPT) process

definition, 1-17

checkpoint process (CKPT), 9-11

checkpoints

checkpoint process (CKPT), 9-11

control files and, 4-21

DBWn process, 9-8, 9-11

incremental, 9-8

redo log size, 2-33

statistics on, 9-11

CKPT background process, 9-11

class

attributes, 16-35, 16-36

definition, 16-35

hierarchy, 16-36

inheritance, 16-36

methods, 16-35, 16-36

client processes. See user processes

clients

in client/server architecture, definition, 2-2

client/server architectures, 7-2

definition, 2-2

diagrammed, 7-4

distributed processing in, 7-4

overview of, 7-2

program interface, 9-22

CLOB datatype, 13-15

clone databases

mounting, 6-8

cluster keys, 11-64

definition, 1-27

CLUSTER_DATABASE parameter, 6-7

clustered computer systems

Real Application Clusters, 6-3

clusters

cannot be partitioned, 12-1

definition, 1-27

dictionary locks and, 22-30

hash, 11-64

contrasted with index, 11-64

hash, definition, 1-30

index

contrasted with hash, 11-64

indexes on, 11-27

cannot be partitioned, 12-1

keys, 11-64

affect indexing of nulls, 11-9

overview of, 11-62

rowids and, 11-8

scans of, 8-7

storage parameters of, 11-6

coalescing extents, 3-17

coalescing free space

extents, 3-15

SMON process, 9-11

SMON process, definition, 1-18

within data blocks, 3-7

collections, 14-11

index-organized tables, 11-58

key compression, 11-46

nested tables, 14-12

variable arrays (VARRAYs), 14-11

columns

cardinality, 11-48

column objects, 14-8

default values for, 11-9

definition, 1-24

described, 11-4

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

maximum in concatenated indexes, 11-30

maximum in view or table, 11-16

nested tables, 11-12

order of, 11-8

prohibiting nulls in, 23-7

pseudocolumns

ROWID, 13-17

USER, 25-7

COMMENT statement, 16-4

COMMIT comment

deprecation of, 17-10

COMMIT statement, 16-5

ending a transaction, 17-2, 17-5

fast commit, 9-10

implied by DDL, 17-2, 17-6

two-phase commit, 17-10

committing transactions

defined, 17-2

fast commit, 9-10

group commits, 9-10

implementation, 9-10

comparison methods, 14-7

definition, 2-17

compiled PL/SQL

advantages of, 16-29

procedures, 16-30

pseudocode, 18-24

shared pool, 16-19

triggers, 18-24

composite indexes, 11-29

compression of free space in data blocks, 3-6

compression, index key, 11-44

concatenated indexes, 11-29

concurrency

data, definition, 1-31

described, 22-2

limits on

for each database, 24-22

for each user, 24-20

transactions and, 22-17

configuration of a database

process structure, 9-2

configuring

parameter file, 6-4

process structure, 9-2

CONNECT role, 25-23

connection pooling, 24-10

connections

defined, 9-4

embedded SQL, 16-6

listener process and, 7-9, 9-19

restricting, 6-6

sessions contrasted with, 9-4

with administrator privileges, 6-3

audit records, 26-5

consistency

read consistency, definition, 1-32

consistency of data

See also read consistency

constants

in stored procedures, 16-20

constraints

allowed in views, 11-16

alternatives to, 23-5

applications can find violations, 23-6

CHECK, 23-21

default values and, 23-24

defined, 11-5

disabling temporarily, 23-7

effect on performance, 23-6

ENABLE or DISABLE, 23-26

enforced with indexes, 11-30

PRIMARY KEY, 23-12

UNIQUE, 23-10

FOREIGN KEY, 23-13

FOREIGN KEY, definition, 2-19

integrity

types listed, 2-18

integrity, definition, 2-18

mechanisms of enforcement, 23-21

modifying, 23-27

NOT NULL, 23-7, 23-11

PRIMARY KEY, 23-11

PRIMARY KEY, definition, 2-19

referential

effect of updates, 23-16

self-referencing, 23-14

triggers cannot violate, 18-20

triggers contrasted with, 18-5

types listed, 23-1

UNIQUE key, 23-8

partially null, 23-11

UNIQUE key, definition, 2-19

VALIDATE or NOVALIDATE, 23-26

what happens when violated, 23-5

when evaluated, 11-10

constructor methods, 14-6

definition, 2-17

contention

for data

deadlocks, 22-18

lock escalation does not occur, 22-18

for rollback segments, 3-26

control files, 4-20

changes recorded, 4-21

checkpoints and, 4-21

contents, 4-20

definition, 1-10

how specified, 6-4

multiplexed, 4-22

overview, 4-20

used in mounting database, 6-7

converting data

ANSI datatypes, 13-24

program interface, 9-23

SQL/DS and DB2 datatypes, 13-24

correlation names

inline views, 11-21

cost-based optimization

query rewrite, 11-21

CPU

utilization, 20-3

CPU allocation

rules, 10-15

CPU resources

allocation, 10-5

CPU time limit, 24-19

crash recovery

instance failure, 6-11

opening a database, 6-9

required after aborting instance, 6-11

SMON process, 9-11

SMON process, definition, 1-17

CREATE CLUSTER statement

storage parameters, 3-19

CREATE INDEX statement

storage parameters, 3-20

temporary segments, 3-20

CREATE PACKAGE statement

examples, 18-12

locks, 22-30

CREATE PROCEDURE statement

locks, 22-30

CREATE statement, 16-4

CREATE SYNONYM statement

locks, 22-30

CREATE TABLE AS SELECT

rules of parallelism

index-organized tables, 20-12, 20-13

CREATE TABLE statement

AS SELECT

compared with direct-path INSERT, 21-2

auditing, 26-7, 26-11

CACHE clause, 8-7

enable or disable constraints, 23-26

examples

column objects, 14-5

nested tables, 14-12

object tables, 14-8, 14-12

locks, 22-30

parallelism

index-organized tables, 20-12, 20-13

storage parameters, 3-19

triggers, 18-7

CREATE TEMPORARY TABLE statement, 11-12

CREATE TRIGGER statement

compiled and stored, 18-24

examples, 18-12, 18-15, 18-23

locks, 22-30

CREATE TYPE statement

nested tables, 14-4, 14-12

object types, 14-4

object views, 15-3

VARRAYs, 14-11

CREATE USER statement

temporary segments, 3-21

CREATE VIEW statement

examples, 18-15

object views, 15-3

locks, 22-30

cursors

creating, 16-12

defined, 16-6

definition, 1-15

embedded SQL, 16-6

maximum number of, 16-7

object dependencies and, 19-11

opening, 8-17, 16-7

private SQL areas and, 8-18, 16-6

recursive, 16-7

recursive SQL and, 16-7

scrollable, 16-7

stored procedures and, 16-20

dangling REFs, 14-10

data

access to

concurrent, 22-2

control of, 24-2

fine-grained access control, 25-24

security domains, 24-2

concurrency, definition, 1-31

consistency of

examples of lock behavior, 22-32

locks, 22-3

manual locking, 22-31

read consistency, definition, 1-32

repeatable reads, 22-6

transaction level, 22-6

underlying principles, 22-16

how stored in tables, 11-5

integrity of, 11-5, 23-2

CHECK constraints, 23-21

enforcing, 23-4, 23-5

introduction, 2-17

referential, 23-3

types, 23-3

locks on, 22-21

data blocks, 3-2

allocating for extents, 3-15

cached in memory, 9-8

coalescing extents, 3-15

coalescing free space in blocks, 3-7

controlling free space in, 3-8

definition, 1-7

format, 3-4

free lists and, 3-12

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

overview, 3-2

read-only transactions and, 22-32

row directory, 11-8

shared in clusters, 11-62

shown in rowids, 13-19, 13-20

space available for inserted rows, 3-11

stored in the buffer cache, 8-6

writing to disk, 9-8

data conversion

ANSI datatypes, 13-24

CHARTOROWID function, 13-27

HEXTORAW function, 13-27

program interface, 9-23

RAWTOHEX function, 13-27

RAWTONHEX function, 13-27

REFTOHEX function, 13-27

ROWIDTOCHAR function, 13-27

ROWIDTONCHAR function, 13-27

SQL/DS and DB2 datatypes, 13-24

TO_CHAR function, 13-27

TO_CLOB function, 13-27

TO_DATE function, 13-27

TO_NCHAR function, 13-27

TO_NCLOB function, 13-27

TO_NUMBER function, 13-27

data definition language

auditing, 26-7

commit implied by, 17-6

described, 16-4

embedding in PL/SQL, 16-22

locks, 22-29

parsing with DBMS_SQL, 16-22

processing statements, 16-15

roles and privileges, 25-22

data definition language (DDL)

definition, 2-12

data dictionary

access to, 5-3

ALL prefixed views, 5-6

cache, 8-12

location of, 8-10

content of, 5-2, 8-12

datafiles, 4-7

DBA prefixed views, 5-6

defined, 5-2

definition, 1-31

dependencies tracked by, 19-3

dictionary-managed tablespaces, 4-11

DUAL table, 5-6

dynamic performance tables, 5-7

locks, 22-29

owner of, 5-3

prefixes to views of, 5-5

public synonyms for, 5-4

row cache and, 8-12

structure of, 5-3

SYSTEM tablespace, 4-7, 5-2, 5-5

USER prefixed views, 5-5

uses of, 5-3

table and column definitions, 16-12

Data Guard

general overview, 2-34

overview of features, 2-37

Data Guard architecture, 2-39

Data Guard Broker

definition, 2-38

Data Guard Manager

definition, 2-38

data loading

with external tables, 11-14

data locks

conversion, 22-18

duration of, 22-17

escalation, 22-18

data loss

limiting, 2-42

data manipulation language

auditing, 26-7

described, 16-3

locks acquired by, 22-26

parallel DML, 20-13

privileges controlling, 25-5

processing statements, 16-11

serializable isolation for subqueries, 22-14

triggers and, 18-4, 18-22

data manipulation language (DML)

definition, 2-12

data models

object-relational principles, 1-21, 1-22

data object number

extended rowid, 13-19

data security

definition, 2-20

data segments, 3-19, 11-5

definition, 1-7

data warehousing

bitmap indexes, 11-47

dimension schema objects, 11-24

features, 2-10

hierarchies, 11-24

invalidated views and packages, 19-7

materialized views, 11-21

summaries, 11-21

database

links, definition, 1-30

database administrators (DBAs)

authentication, 24-14

data dictionary views, 5-6

DBA role, 25-23

password files, 24-15

database buffers

after committing transactions, 17-7

buffer cache, 8-6, 9-8

clean, 9-8

committing transactions, 9-10

defined, 8-6

definition, 1-14

dirty, 8-6, 9-8

free, 8-6

multiple buffer pools, 8-9

pinned, 8-6

size of cache, 8-7

writing of, 9-8

database management system (DBMS)

object-relational DBMS, 14-2

principles, 1-21

database object metadata, 5-7

Database Resource Manager, 10-1

active session pool with queuing, 10-12

and operating system control, 10-17

and performance, 10-7

automatic consumer group switching, 10-12

execution time limit, 10-13

introduction, 10-2

multiple level CPU resource allocation, 10-11

resource plans

plan schemas, 10-11

specifying a parallel degree limit, 10-12

terminology, 10-3

undo pool, 10-13

database security

general overview, 2-19

database structures

control files, 4-20

data blocks, 3-2, 3-3

data dictionary, 5-1

datafiles, 4-1, 4-18

extents, 3-2, 3-13

memory, 8-1

processes, 9-1

revealing with rowids, 13-20

schema objects, 11-3

segments, 3-2, 3-18

tablespaces, 4-1, 4-7

database triggers, 18-1

See also triggers

database writer (DBWn) process

definition, 1-17

database writer process (DBWn), 9-8

checkpoints, 9-8

defined, 9-8

least recently used algorithm (LRU), 9-8

multiple DBWn processes, 9-8

when active, 9-8

write-ahead, 9-9

writing to disk at checkpoints, 9-11

databases

access control

password encryption, 24-8

security domains, 24-2

clone database, 6-8

closing, 6-11

aborting the instance, 6-11

configuring, 6-4

contain schemas, 24-2

definition, 1-5

dismounting, 6-11

distributed

changing global database name, 8-13

nodes of, definition, 2-3

distributed, definition, 2-3

limitations on usage, 24-18

mounting, 6-7

name stored in control file, 4-21

open and closed, 6-3

opening, 6-9

acquiring rollback segments, 3-31

opening read-only, 6-10

scalability, 7-7, 20-2

shutting down, 6-11

standby, 6-8

starting up, 6-2

forced, 6-12

structures

control files, 4-20

data blocks, 3-2, 3-3

data dictionary, 5-1

datafiles, 4-1, 4-18

extents, 3-2, 3-13

logical, 3-1

memory, 8-1

processes, 9-1

revealing with rowids, 13-20

schema objects, 11-3

segments, 3-2, 3-18

tablespaces, 4-1, 4-7

datafiles

contents of, 4-19

data dictionary, 4-7

datafile 1, 4-7

SYSTEM tablespace, 4-7

definition, 1-8

in online or offline tablespaces, 4-19

named in control files, 4-21

overview of, 4-18

read-only, 4-15

relationship to tablespaces, 4-2

shown in rowids, 13-19, 13-20

SYSTEM tablespace, 4-7

taking offline, 4-19

temporary, 4-20

datatypes, 13-2, 13-3

ANSI, 13-24

array types, 14-11

BOOLEAN, 13-2

CHAR, 13-3

character, 13-3, 13-15

collections, 14-11

conversions of

by program interface, 9-23

non-Oracle types, 13-24

Oracle to another Oracle type, 13-27

DATE, 13-10

DB2, 13-24

definition, 1-24

how they relate to tables, 11-4

in PL/SQL, 13-2

list of available, 13-2

LOB datatypes, 13-14

BFILE, 13-16

BLOB, 13-15

CLOB and NCLOB, 13-15

LONG, 13-8

storage of, 11-8

multimedia, 14-3

NCHAR and NVARCHAR2, 13-7

nested tables, 11-12, 14-12

NUMBER, 13-8

object types, 14-4

RAW and LONG RAW, 13-16

ROWID, 13-17, 13-18

SQL/DS, 13-24

summary, 13-3

TIMESTAMP, 13-13

TIMESTAMP WITH LOCAL TIME ZONE, 13-13

TIMESTAMP WITH TIME ZONE, 13-13

user-defined, 14-1, 14-3

VARCHAR, 13-4

VARCHAR2, 13-4

XML, 13-25

DATE datatype, 13-10

arithmetic with, 13-12

changing default format of, 13-10

Julian dates, 13-11

midnight, 13-11

DATETIME datatypes, 13-12

daylight savings support, 13-12

DB_BLOCK_SIZE parameter

buffer cache and, 8-7

DB_CACHE_SIZE

parameter

buffer cache and, 8-7

DB_CACHE_SIZE parameter

system global area size and, 8-5

DB_NAME parameter, 4-21

DBA role, 25-23

DBA_ views, 5-6

DBA_SYNONYMS.SQL script

using, 5-6

DBA_UPDATABLE_COLUMNS view, 11-20

DBMS

object-relational DBMS, 14-2

DBMS. See database management system (DBMS)

DBMS_JAVA package, 16-38

delete_permission method, 16-40

disable_permission method, 16-40

dropjava method, 16-39

enable_permission method, 16-40

get_compiler_option method, 16-38

grant_permission method, 16-39

grant_policy_permission method, 16-39

loadjava method, 16-39

longname method, 16-38

reset_compiler_option method, 16-38

restart_debugging method, 16-40

restrict_permission method, 16-39

revoke_permission method, 16-40

set_compiler_option method, 16-38

set_output method, 16-39

shortname method, 16-38

start_debugging method, 16-40

stop_debugging method, 16-40

DBMS_LOCK package, 22-40

DBMS_OUTPUT package, 16-39

DBMS_RLS package

security policies, 25-24

uses definer rights, 25-9

DBMS_SQL package, 16-22

parsing DDL statements, 16-22

DbmsJava class

See DBMS_JAVA package

DBWn background process, 9-8

data definition language

See also Data Definition Language

DDL, 16-4

DDL. See data definition language (DDL)

deadlocks

avoiding, 22-20

defined, 22-18

detection of, 22-19

distributed transactions and, 22-20

deallocating extents, 3-16

debugging, 16-40

decision support systems (DSS)

materialized views, 11-21

dedicated servers, 9-21

compared with shared servers, 9-15

default access driver

for external tables, 11-14

default tablespace

definition, 2-23

default temporary tablespace, 4-9

specifying, 4-10

default values, 11-9

constraints effect on, 11-10, 23-24

deferred constraints

deferrable or nondeferrable, 23-24

initially deferred or immediate, 23-24

Define Constraints on Views, 11-22

define phase of query processing, 16-13

definer rights

procedure security, 25-8

degree of parallelism, 20-8

parallel SQL, 20-5

delete cascade constraint, 23-16

DELETE statement, 16-4

foreign key references, 23-16

freeing space in data blocks, 3-6

triggers, 18-2, 18-7

delete_permission method, 16-40

denormalized tables, 11-24

dependencies, 19-1

between schema objects, 19-2

function-based indexes, 11-32, 19-8

local, 19-11

managing, 19-1

non-existent referenced objects and, 19-9

on non-existence of other objects, 19-10

Oracle Forms triggers and, 19-14

privileges and, 19-7

remote objects and, 19-11

shared pool and, 19-11

dereferencing, 14-10

implicit, 14-10

describe phase of query processing, 16-13

DETERMINISTIC functions

function-based indexes, 19-8

dictionary

See data dictionary

dictionary cache locks, 22-31

dictionary-managed tablespaces, 4-11

different-row writers block writers, 22-10

dimensions, 11-24

attributes, 11-24

hierarchies, 11-24

join key, 11-24

in hierarchical relationships, definition, 1-30

normalized or denormalized tables, 11-24

direct-path INSERT, 21-2

index maintenance, 21-5

logging mode, 21-4

parallel INSERT, 21-3

parallel load compared with parallel INSERT, 21-2, 21-3

serial INSERT, 21-3

dirty buffer, 8-6

incremental checkpoint, 9-8

dirty read, 22-3, 22-10

dirty write, 22-10

DISABLE constraints, 23-26

disable_permission method, 16-40

DISABLED indexes, 19-9

disaster protection

Data Guard, 2-36

Disaster Recovery Server, 2-41

discrete transaction management

summary, 17-11

discretionary access control, 24-2

definition, 2-20

disk affinities

disabling with MPP, 12-2, 12-6, 12-11, 12-19

disk failure. See media failure

disk space

controlling allocation for tables, 11-5

datafiles used to allocate, 4-18

dispatcher (Dnnn) processes

definition, 1-18

dispatcher processes (Dnnn)

described, 9-19

limiting SGA space for each session, 24-21

listener process and, 9-19

network protocols and, 9-19

prevent startup and shutdown, 9-20

response queue and, 9-17

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

distributed databases

auditing and, 26-6

client/server architectures and, 7-4

deadlocks and, 22-20

definition, 2-3

dependent schema objects and, 19-11

job queue processes, 9-12

recoverer process (RECO) and, 9-12

remote dependencies, 19-12

server can also be client in, 7-4

distributed processing

definition, 2-2

distributed processing environment

client/server architecture in, 7-4

data manipulation statements, 16-11

described, 7-4

materialized views (snapshots), 11-21

distributed transactions

naming, 17-9

parallel DDL restrictions, 20-12

parallel DML restrictions, 20-12

routing statements to nodes, 16-12

two-phase commit and, 17-10

DISTRIBUTED_TRANSACTIONS parameter, 9-12

data manipulation language

See also Data Manipulation Language

DML, 16-3

DML. See data manipulation language (DML)

Dnnn background processes, 9-19

See also dispatcher processes

drivers, 9-23

DRMON, 2-41

DROP statement, 16-4

DROP TABLE statement

auditing, 26-7

triggers, 18-7

dropjava method, 16-39

DUAL table, 5-6

dynamic partitioning, 20-4

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

dynamic predicates

in security policies, 25-24

dynamic SQL

DBMS_SQL package, 16-22

embedded, 16-22

editing stored outlines, 16-17

embedded SQL, 16-5

dynamic SQL in PL/SQL, 16-22

ENABLE constraints, 23-26

enable_permission method, 16-40

enterprise users, 24-2

errors

in embedded SQL, 16-6

tracked in trace files, 9-14

exceptions

during trigger execution, 18-21

raising, 16-21

stored procedures and, 16-21

exclusive locks

row locks (TX), 22-21

RX locks, 22-24

table locks (TM), 22-22

exclusive mode, 3-32

execution plans, 16-17

EXPLAIN PLAN, 16-4

location of, 8-11

parsing SQL, 16-12

EXP_FULL_DATABASE role, 25-23

EXPLAIN PLAN statement, 16-4

explicit locking, 22-31

Export utility

definition, 1-11

extended rowid format, 13-18

extents

allocating, 3-14

allocating data blocks for, 3-15

allocation to rollback segments

after segment creation, 3-29

at segment creation, 3-27

allocation, how performed, 3-15

as collections of data blocks, 3-13

coalescing, 3-17

deallocation

from rollback segments, 3-30

when performed, 3-16

defined, 3-2

definition, 1-7

dictionary managed, 4-11

dropping rollback segments and, 3-30

in rollback segments

changing current, 3-27

incremental, 3-13

locally managed, 4-12

managing, 3-14

materialized views, 3-17

overview of, 3-13

external procedures, 16-31

external tables

parallel access, 11-15

failures

instance

recovery from, 6-9, 6-11

internal errors

tracked in trace files, 9-14

statement and process, 9-12

types listed, 2-25

fast commit, 9-10

fast refresh, 11-23

fetching rows in a query, 16-15

embedded SQL, 16-6

file management locks, 22-31

files

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

initialization parameter, 6-4, 6-6

password, 24-15

administrator privileges, 6-3

See also control files, datafiles, redo log files

FINAL and NOT FINAL types, 14-13

fine-grained access control, 25-24

fine-grained auditing, 26-9

FIPS standard, 16-6

fixed views, 5-7

flagging of nonstandard features, 16-6

Flashback Query

benefits, 22-41

overview, 22-40

uses, 22-42

foreign key constraints

changes in parent key values, 23-16

constraint checking, 23-23

deleting parent table rows and, 23-16

maximum number of columns in, 23-13

nulls and, 23-15

share locks, 23-17

updating parent key tables, 23-16

updating tables, 23-17, 23-19

foreign keys

definition, 2-19

privilege to use parent key, 25-5

fractional seconds, 13-13

free lists, 3-12

free space

automatic segment space management, 3-6

coalescing extents, 3-15

SMON process, 9-11

coalescing within data blocks, 3-7

free lists, 3-12

parameters for data blocks, 3-8

section of data blocks, 3-5

free space management

in-segment, 3-6

front-ends, 7-2

full table scans

LRU algorithm and, 8-7

parallel execution, 20-3, 20-4

function-based indexes, 11-31

dependencies, 11-32, 19-8

DISABLED, 19-9

privileges, 11-32, 19-9

UNUSABLE, 19-9

functions

definition, 2-16

function-based indexes, 11-31

PL/SQL, 16-24, 16-28

contrasted with procedures, 16-24

DETERMINISTIC, 19-8

privileges for, 25-7

roles, 25-21

See also procedures

SQL, 16-2

COUNT, 11-51

in CHECK constraints, 23-21

in views, 11-18

NVL, 11-9

fuzzy reads, 22-3

get_compiler_option method, 16-38

Global Cache Service process (LMS), 9-14

global database names

shared pool and, 8-13

global partitioned indexes

maintenance, 12-13

Globalization Support

character sets for, 13-4

CHECK constraints and, 23-21

NCHAR and NVARCHAR2 datatypes, 13-7

NCLOB datatype, 13-15

parameters, 6-5

views and, 11-18

GRANT ANY PRIVILEGE system privilege, 25-3

GRANT statement, 16-4

locks, 22-30

grant_permission method, 16-39

grant_policy_permission method, 16-39

granted privileges

definition, 2-22

granting

privileges and roles, 25-3

GROUP BY clause

temporary tablespaces, 4-16

group commits, 9-10

guesses in logical rowids, 13-22

staleness, 13-23

statistics for, 13-23

handles for SQL statements, 8-17

definition, 1-15

hash clusters, 11-64

contrasted with index, 11-64

definition, 1-30

headers

of data blocks, 3-4

of row pieces, 11-6

Heterogeneous Services

general overview, 2-7

HEXTORAW function

data conversion, 13-27

HI_SHARED_MEMORY_ADDRESS parameter, 8-15

hierarchical materialized views. See multitier materialized views

hierarchies, 11-24

join key, 11-24

levels, 11-24

high availability

types listed, 2-35

high water mark, 3-36

definition, 3-3, 3-36

direct-path INSERT, 21-4

immediate constraints, 23-24

IMP_FULL_DATABASE role, 25-23

implicit dereferencing, 14-10

Import utility

definition, 1-11

incremental checkpoint, 9-8

incremental refresh, 11-23

index segments, 3-20

definition, 1-7

indexes, 11-27

bitmap indexes, 11-47, 11-52

nulls and, 11-9

parallel query and DML, 11-48

branch blocks, 11-35

B-tree structure of, 11-34

building

using an existing index, 11-28

cardinality, 11-48

cluster

cannot be partitioned, 12-1

composite, 11-29

concatenated, 11-29

definition, 1-27

described, 11-27

domain, 11-62

enforcing integrity constraints, 23-10, 23-12

extensible, 11-62

function-based, 11-31

dependencies, 11-32, 19-8, 19-10

DETERMINISTIC functions, 19-8

DISABLED, 19-9

optimization with, 11-32

privileges, 11-32, 19-9

index-organized tables, 11-57

logical rowids, 11-60, 13-22

secondary indexes, 11-60

internal structure of, 11-34

key compression, 11-44

keys and, 11-30

primary key constraints, 23-12

unique key constraints, 23-10

leaf blocks, 11-35

location of, 11-33

LONG RAW datatypes prohibit, 13-17

nonunique, 11-29

nulls and, 11-9, 11-30, 11-51

on complex data types, 11-62

overview of, 11-27

partitioned tables, 11-52

partitions, 12-2

performance and, 11-28

rebuilt after direct-path INSERT, 21-5

reverse key indexes, 11-46

rowids and, 11-35

storage format of, 11-33

unique, 11-29

when used with views, 11-18

index-organized tables, 11-57

benefits, 11-58

key compression in, 11-46, 11-58

logical rowids, 11-60, 13-22

parallel CREATE, 20-12, 20-13

secondary indexes on, 11-60

in-doubt transactions, 3-29, 6-10

inheritance, 16-36

initialization parameter file, 6-4, 6-6

example, 6-4

startup, 6-6

initialization parameters

ARCHIVE_LAG_TARGET, 2-42

BUFFER_POOL_KEEP, 8-9

BUFFER_POOL_RECYCLE, 8-9

CLUSTER_DATABASE, 6-7

DB_BLOCK_SIZE, 8-7

DB_CACHE_SIZE, 8-5, 8-7

DB_NAME, 4-21

DISTRIBUTED_TRANSACTIONS, 9-12

HI_SHARED_MEMORY_ADDRESS, 8-15

LICENSE_MAX_SESSIONS, 24-23

LICENSE_SESSIONS_WARNING, 24-23

LOCK_SGA, 8-15

LOG_ARCHIVE_MAX_PROCESSES, 9-14

LOG_BUFFER, 8-5, 8-10

MAX_SHARED_SERVERS, 9-20

NLS_NUMERIC_CHARACTERS, 13-9

OPEN_CURSORS, 8-17, 16-7

REMOTE_DEPENDENCIES_MODE, 19-12

ROLLBACK_SEGMENTS, 3-32

SERVICE_NAMES, 7-10

SHARED_MEMORY_ADDRESS, 8-15

SHARED_POOL_SIZE, 8-5, 8-10

SHARED_SERVERS, 9-20

SKIP_UNUSABLE_INDEXES, 19-9

SORT_AREA_SIZE, 3-20

SQL_TRACE, 9-15

TRANSACTIONS, 3-32

TRANSACTIONS_PER_ROLLBACK_SEGMENT, 3-32

UNDO_MANAGEMENT, 6-9

USE_INDIRECT_DATA_BUFFERS, 8-16

initially deferred constraints, 23-24

initially immediate constraints, 23-24

initjvm.sql, 16-38

INIT.ORA. See initialization parameter file.

inline views, 11-21

example, 11-21

INSERT statement, 16-3

direct-path INSERT, 21-2

no-logging mode, 21-4

free lists, 3-12

triggers, 18-2, 18-7

BEFORE triggers, 18-10

instance

aborting, 6-11

definition, 1-4

instance failure

definition, 2-26

instance recovery

SMON process, 9-11

SMON process, definition, 1-17

tuning methods listed, 2-32

See also crash recovery

instances

acquire rollback segments, 3-32

associating with databases, 6-3, 6-7

described, 6-2

diagrammed, 9-6

memory structures of, 8-2

multiple-process, 9-2

process structure, 9-2

recovery of, 6-11

opening a database, 6-9

SMON process, 9-11

restricted mode, 6-6

service names, 7-9

shutting down, 6-11, 6-12

audit record, 26-5

starting, 6-6

audit record, 26-5

INSTEAD OF triggers, 18-13

nested tables, 15-5

object views, 15-5

integrity constraints, 23-2

default column values and, 11-10

definition, 2-18

FOREIGN KEY, definition, 2-19

PRIMARY KEY, definition, 2-19

types listed, 2-18

UNIQUE key, definition, 2-19

See also constraints

integrity rules

definition, 1-21

interfaces

defined, 16-36

INTERNAL connection

statement execution not audited, 26-5

internal errors tracked in trace files, 9-14

intra-block chaining, 11-6

INVALID status, 19-3

invoker rights

procedure security, 25-9

supplied packages, 25-9

I/O

parallel execution, 20-3

IS NULL predicate, 11-9

ISO SQL standard, 1-3, 13-24

isolation levels

choosing, 22-12

read committed, 22-8

setting, 22-7, 22-32

Java

attributes, 16-35

types of, 16-35

class, 16-35

methods, 16-35, 16-36

object-oriented programming terminology, 16-35

overview, 16-34

permissions, 16-40

polymorphism, 16-36

triggers, 18-1, 18-8

Java Compatibility Kit, 16-37

Java language specification, 16-37

Java object types, 14-21

Java virtual machine, 16-37

JCK, 16-37

See Java Compatibility Kit

JLS

See Java language specification

specification, 16-37

job queue processes, 9-12

definition, 1-18

jobs, 9-2

join views, 11-20

joins

encapsulated in views, 11-17

views, 11-20

JVM

bytecodes, 16-37

defined, 16-37

security, 16-40

See Java virtual machine

specification, 16-37

key compression, 11-44

keys

cluster, 11-64

cluster, definition, 1-27

defined, 23-9

foreign, 23-13

in constraints, definition, 2-18

indexes and, 11-30

compression, 11-44

PRIMARY KEY constraints, 23-12

reverse key, 11-46

UNIQUE constraints, 23-10

maximum storage for values, 11-30

parent, 23-13, 23-14

primary, 23-11

referenced, 23-13

reverse key indexes, 11-46

unique, 23-8

composite, 23-9, 23-11

large pool, 8-14

definition, 1-15

latches

described, 22-31

LDAP, 7-10

leaf blocks, 11-35

least recently used (LRU) algorithm

database buffers and, 8-6

dictionary cache, 5-4

full table scans and, 8-7

latches, 9-8

shared SQL pool, 8-11, 8-12

LGWR background process, 9-9

library cache, 8-10, 8-11, 8-12

LICENSE_MAX_SESSIONS parameter, 24-23

LICENSE_SESSIONS_WARNING parameter, 24-23

licensing

concurrent usage, 24-22

named user, 24-24

viewing current limits, 24-23

lightweight sessions, 24-10

listener process, 7-9

service names, 7-9

listeners, 7-9, 9-19

service names, 7-9

LMS background process, 9-14

loader access driver, 11-14

loadjava method, 16-39

LOB datatypes, 13-14

BFILE, 13-16

BLOBs, 13-15

CLOBs and NCLOBs, 13-15

restrictions

parallel DDL, 20-12

local indexes

bitmap indexes

on partitioned tables, 11-52

parallel query and DML, 11-48

locally-managed tablespaces, 4-12

LOCK TABLE statement, 16-4

LOCK_SGA parameter, 8-15

locking

indexed foreign keys and, 23-19

unindexed foreign keys and, 23-17

locks, 22-3

after committing transactions, 17-7

automatic, 22-17, 22-20

conversion, 22-18

data, 22-21

duration of, 22-17

deadlocks, 22-18, 22-19

avoiding, 22-20

dictionary, 22-29

clusters and, 22-30

duration of, 22-30

dictionary cache, 22-31

DML acquired, 22-28

diagrammed, 22-26

escalation does not occur, 22-18

exclusive table locks (X), 22-26

file management locks, 22-31

how Oracle uses, 22-16

internal, 22-30

latches and, 22-31

log management locks, 22-31

manual, 22-31

examples of behavior, 22-32

object level locking, 14-19

Oracle Lock Management Services, 22-40

overview of, 22-3

parse, 16-12, 22-30

rollback segment, 22-31

row (TX), 22-21

row exclusive locks (RX), 22-24

row share table locks (RS), 22-24

share row exclusive locks (SRX), 22-25

share table locks (S), 22-25

share-subexclusive locks (SSX), 22-25

subexclusive table locks (SX), 22-24

subshare table locks (SS), 22-24

table (TM), 22-22

table lock modes, 22-22

tablespace, 22-31

types of, 22-20

uses for, 1-33

log entries, 1-9

See also redo log files, 1-9

log management locks, 22-31

log switch

archiver process, 9-13

archiver process, definition, 1-18

log transport services

definition, 2-37

log writer (LGWR) process

definition, 1-17

log writer process (LGWR), 9-9

group commits, 9-10

redo log buffers and, 8-10

starting new ARCn processes, 9-13

system change numbers, 17-7

write-ahead, 9-9

LOG_ARCHIVE_MAX_PROCESSES parameter, 9-14

LOG_BUFFER parameter, 8-10

system global area size and, 8-5

logging mode

direct-path INSERT, 21-4

NOARCHIVELOG mode and, 21-5

parallel DDL, 20-13

logical blocks, 3-2

logical database structures

definition, 1-5

tablespaces, 4-7

logical reads limit, 24-20

logical rowids, 13-22

index on index-organized table, 11-60

physical guesses, 11-60, 13-22

staleness of guesses, 13-23

statistics for guesses, 13-23

logical standby database

definition, 2-37

logical structures

definition, 1-7

LogMiner, 2-38

LogMiner utility

general overview, 2-43

LogMiner Viewer, 2-43

LONG datatype

automatically the last column, 11-9

defined, 13-8

storage of, 11-8

LONG RAW datatype, 13-16

indexing prohibited on, 13-17

similarity to LONG datatype, 13-16

longname method, 16-38

LRU, 8-6, 8-7, 9-8

dictionary cache, 5-4

shared SQL pool, 8-11, 8-12

managing free space, 3-6

manual locking, 22-31

map methods, 14-7

definition, 2-17

massively parallel processing (MPP)

multiple Oracle instances, 6-3

massively parallel systems, 20-3

materialized view logs, 11-23

materialized views, 11-21

deallocating extents, 3-17

definition, 1-25

materialized view logs, 11-23

multitier, definition, 2-5

partitioned, 11-22, 12-1

refresh

job queue processes, 9-12

refreshing, 11-23

MAX_SHARED_SERVERS parameter, 9-20

media failure

definition, 2-27

memory

allocation for SQL statements, 8-12

content of, 8-2

cursors (statement handles), definition, 1-15

extended buffer cache (32-bit), 8-16

overview of structures, 1-12

processes use of, 9-2

shared SQL areas, 8-11

software code areas, 8-21

stored procedures, 16-29

structures in, 8-2

system global area (SGA)

allocation in, 8-3

initialization parameters, 8-5, 8-15

locking into physical memory, 8-15

SGA size, 8-4

starting address, 8-15

See also system global area

MERGE statement, 16-3

message queuing

publish-subscribe support

event publication, 18-17

queue monitor process, 9-14

metadata

viewing, 5-7

methods, 16-35

comparison methods, 14-7

constructor methods, 14-6

of object types, definition, 2-17

privileges on, 25-12

methods of object types, 14-4

comparison methods, definition, 2-17

constructor methods, definition, 2-17

map methods, 14-7

map methods, definition, 2-17

order methods, 14-7

order methods, definition, 2-17

PL/SQL, 14-17

purchase order example, 14-2, 14-5

selfish style of invocation, 14-6

mobile computing environment

materialized views, 11-22

modes

table lock, 22-22

two-task, 9-3

monitoring user actions, 26-2

MPP

disk affinity, 12-2, 12-6, 12-11, 12-19

MPP. See massively parallel processing

MTS. See shared server

multiblock writes, 9-8

multimedia datatypes, 14-3

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

multiplexed online redo logs

definition, 2-28

multiplexing

control files, 4-22

multithreaded server. See shared server

multitier materialized views

definition, 2-5

multiuser environments, 9-2

multiversion concurrency control, 22-5

mutating errors and triggers, 18-21

named user licensing, 24-24

NCHAR datatype, 13-7

NCLOB datatype, 13-15

nested tables, 11-12, 14-12

index-organized tables, 11-58

key compression, 11-46

INSTEAD OF triggers, 15-5

updating in views, 15-5

network listener process

connection requests, 9-16, 9-19

networks

client/server architecture use of, 7-2

communication protocols, 9-23, 9-24

dispatcher processes and, 9-16, 9-19

drivers, 9-23

listener processes of, 7-9, 9-19

network authentication service, 24-4

Oracle Net Services, 7-8

two-task mode and, 9-22

NLS.See Globalization Support

NLS_DATE_FORMAT parameter, 13-10

NLS_NUMERIC_CHARACTERS parameter, 13-9

NOARCHIVELOG mode

definition, 2-28

LOGGING mode and, 21-5

NOAUDIT statement, 16-4

locks, 22-30

nodes

of distributed databases, definition, 2-3

NOLOGGING mode

direct-path INSERT, 21-4

parallel DDL, 20-13

nonprefixed indexes, 12-12

nonrepeatable reads, 22-3, 22-10

nonunique indexes, 11-29

NOREVERSE clause for indexes, 11-46

normalized tables, 11-24

NOT INSTANTIABLE types and methods, 14-14

NOT NULL constraints

constraint checking, 23-23

defined, 23-7

implied by PRIMARY KEY, 23-12

UNIQUE keys and, 23-11

NOVALIDATE constraints, 23-26

NOWAIT parameter

with savepoints, 17-9

nulls

as default values, 11-10

column order and, 11-9

converting to values, 11-9

defined, 11-9

foreign keys and, 23-15

how stored, 11-9

indexes and, 11-9, 11-30, 11-51

inequality in UNIQUE key, 23-11

non-null values for, 11-9

prohibited in primary keys, 23-11

prohibiting, 23-7

UNIQUE key constraints and, 23-11

unknown in comparisons, 11-9

NUMBER datatype, 13-8

internal format of, 13-9

rounding, 13-9

NVARCHAR2 datatype, 13-7

NVL function, 11-9

object cache

object views, 15-4

OCI, 14-19

Pro*C, 14-18

object identifiers, 15-3, 15-4

collections

key compression, 11-46, 11-58

for object views, 15-3, 15-4

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

object privileges, 25-3

See also schema object privileges

object tables, 14-3, 14-8

row objects, 14-8

virtual object tables, 15-2

Object Type Translator (OTT), 14-20

object types, 14-2, 14-4

attributes of, 14-2, 14-4

column objects, 14-8

comparison methods for, 14-7

constructor methods for, 14-6

locking in cache, 14-19

methods of, 14-4

PL/SQL, 14-17

purchase order example, 14-2, 14-5

methods of, definition, 2-17

object views, 11-20

Oracle type translator, 14-20

purchase order example, 14-2, 14-4

restrictions

parallel DDL, 20-12

row objects, 14-8

SQLJ, 14-21

object views, 11-20, 15-1

advantages of, 15-2

defining, 15-3

modifiability, 18-13

nested tables, 15-5

object identifiers for, 15-3, 15-4

updating, 15-5

use of INSTEAD OF triggers with, 15-5

object-relational database management system (ORDBMS)

definition, 1-22

principles, 1-21

object-relational DBMS (ORDBMS), 14-2

objects

privileges on, 25-12

OCI, 9-23

anonymous blocks, 16-20

bind variables, 16-14

object cache, 14-19

OCIObjectFlush, 15-4

OCIObjectPin, 15-4

stored procedures, 16-21

offline redo logs

definition, 2-28

OiD (see Oracle Internet Directory), 7-10

OLTP. See Online Transaction Processing (OLTP)

online redo log

checkpoints, 4-21

recorded in control file, 4-21

online redo logs

definition, 2-28

Online Transaction Processing (OLTP), 2-10

online transaction processing (OLTP)

reverse key indexes, 11-46

OPEN_CURSORS parameter, 16-7

managing private SQL areas, 8-17

operating systems

authentication by, 24-4

block size, 3-3

communications software, 9-24

privileges for administrator, 6-3

roles and, 25-23

OPTIMAL storage parameter, 3-30

optimization

function-based indexes, 11-32

index build, 11-28

parallel SQL, 20-5

query rewrite, 11-21

in security policies, 25-24

Optimizer, 16-16

Oracle, 16-2

adherence to standards

integrity constraints, 23-5

architecture, overview, 1-12

client/server architecture of, 7-2

configurations of, 9-2

multiple-process Oracle, 9-2

instances, 6-2

licensing of, 24-22

processes of, 9-5

scalability of, 7-7

SQL processing, 16-8

Oracle Advanced Queuing

uses for, 2-8

Oracle blocks, 3-2

Oracle Call Interface. See OCI

Oracle Certificate Authority, 24-6

Oracle code, 9-2, 9-22

Oracle Enterprise Login Assistant, 24-6

Oracle Enterprise Manager

ALERT file, 9-15

checkpoint statistics, 9-11

executing a package, 16-27

executing a procedure, 16-26

granting roles, 25-20

granting system privileges, 25-3

lock and latch monitors, 22-30

PL/SQL, 16-20, 16-21

schema object privileges, 25-4

showing size of SGA, 8-5

shutdown, 6-11, 6-12

SQL statements, 16-2

startup, 6-6

statistics monitor, 24-22

Oracle Enterprise Security Manager, 24-6

Oracle Forms

object dependencies and, 19-14

PL/SQL, 16-19

Oracle Internet Directory, 7-10, 24-6

Oracle Net Services, 7-8

client/server systems use of, 7-8

overview, 7-8

shared server requirement, 9-16, 9-19

Oracle processes

definition, 1-16

Oracle program interface (OPI), 9-23

Oracle server

definition, 1-4

Oracle type translator (OTT), 14-20

Oracle Wallet Manager, 24-5

Oracle wallets, 24-5

Oracle XA

session memory in the large pool, 8-14

ORDBMS, 14-2

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

order methods, 14-7

definition, 2-17

OTT. See Object Type Translator (OTT)

packages, 16-26, 16-33

advantages of, 16-33

as program units, definition, 2-16

auditing, 26-8

dynamic SQL, 16-22

examples of, 25-10, 25-11

executing, 16-19

for locking, 22-40

private, 16-34

privileges

divided by construct, 25-10

executing, 25-7, 25-10

public, 16-34

session state and, 19-7

shared SQL areas and, 8-11

supplied packages

invoker or definer rights, 25-9

pages, 3-2

parallel access

to external tables, 11-15

parallel DDL

restrictions

LOBs, 20-12

object types, 20-12

parallel DML, 20-13

bitmap indexes, 11-48

parallel execution, 20-2

coordinator, 20-4, 21-4

direct-path INSERT, 21-4

full table scans, 20-3

introduction, 20-3

of table functions, 16-32

process classification, 12-2, 12-6, 12-11, 12-13, 12-19

server, 20-4, 21-4

index maintenance, 21-5

servers, 20-4

tuning, 20-2

See also parallel SQL

parallel execution servers

direct-path INSERT, 21-4

parallel query, 20-12

bitmap indexes, 11-48

parallel SQL, 20-2

coordinator process, 20-4

direct-path INSERT, 21-4

optimizer, 20-5

Real Application Clusters and, 20-1

server processes, 20-4

direct-path INSERT, 21-4, 21-5

See also parallel execution

parallelism

degree, 20-8

parameters

Globalization Support, 6-5

initialization, 6-4

locking behavior, 22-20

See also initialization parameters

storage, 3-8, 3-13

parse trees

construction of, 16-8

in shared SQL area, 8-11

parsing, 16-12

DBMS_SQL package, 16-22

embedded SQL, 16-6

parse calls, 16-8

parse locks, 16-12, 22-30

performed, 16-8

SQL statements, 16-12, 16-22

partial database backups

definition, 2-30

partitions, 12-2

bitmap indexes, 11-52

dynamic partitioning, 20-4

hash partitioning, 12-9

materialized views, 11-22, 12-1

nonprefixed indexes, 12-12

segments, 3-19, 3-20

passwords

account locking, 24-8

administrator privileges, 6-3

complexity verification, 24-9

connecting with, 9-4

connecting without, 24-4

database user authentication, 24-8

encryption, 24-8

expiration, 24-9

password files, 24-15

password reuse, 24-9

used in roles, 25-18

PCTFREE storage parameter

how it works, 3-8

PCTUSED and, 3-10

PCTUSED storage parameter

how it works, 3-9

PCTFREE and, 3-10

performance

constraint effects on, 23-6

dynamic performance tables (V$), 5-7

group commits, 9-10

index build, 11-28

packages, 16-34

resource limits and, 24-18

SGA size and, 8-4

sort operations, 4-16

permissions, 16-40

PGA, 8-16

PGA (Program Global Area)

shared server, 9-20

PGA. See program global area (PGA)

phantom reads, 22-3, 22-10

physical database structures

control files, 4-20

datafiles, 4-18

definition, 1-8

physical guesses in logical rowids, 13-22

staleness, 13-23

statistics for, 13-23

physical standby database

definition, 2-37

pipelined table functions, 16-32

PKI, 24-5

plan

SQL execution, 16-4, 16-12

plan schemas for Database Resource Manager, 10-11

PL/SQL, 16-18

anonymous blocks, 16-18, 16-31

auditing of statements within, 26-4

bind variables

user-defined types, 14-17

database triggers, 18-1

datatypes, 13-2

dynamic SQL, 16-22

exception handling, 16-21

executing, 16-18

external procedures, 16-31

gateway, 16-23

language constructs, 16-20

object views, 15-4

overview of, 16-18

packages, 16-26, 16-33

parse locks, 22-30

parsing DDL statements, 16-22

PL/SQL engine, 16-18, 16-24

products containing, 16-19

program units, 8-11, 16-18, 16-24

compiled, 16-19, 16-30

shared SQL areas and, 8-11

roles in procedures, 25-21

stored procedures, 16-18, 16-24, 16-28

user locks, 22-40

user-defined datatypes, 14-17

PL/SQL Server Pages, 16-22

PMON background process, 9-12

See also process monitor process

PMON process, 7-10

point-in-time recovery

clone database, 6-8

polymorphism, 16-36

precompilers

anonymous blocks, 16-20

bind variables, 16-14

cursors, 16-12

embedded SQL, 16-5

FIPS flagger, 16-6

stored procedures, 16-21

predicates

dynamic

in security policies, 25-24

prefixes of data dictionary views, 5-5

PRIMARY KEY constraints, 23-11

constraint checking, 23-23

described, 23-11

indexes used to enforce, 23-12

name of, 23-13

maximum number of columns, 23-13

NOT NULL constraints implied by, 23-12

primary keys, 23-12

advantages of, 23-12

defined, 23-3

definition, 2-19

private rollback segments, 3-31

private SQL areas

cursors and, 8-17

described, 8-11

how managed, 8-17

privileges

administrator, 6-3

connections audited, 26-5

statement execution not audited, 26-5

auditing use of, 26-7

auditing, definition, 2-24

checked when parsing, 16-12

definition, 2-21

function-based indexes, 11-32, 19-9

granted, definition, 2-22

granting, 25-3, 25-4

examples of, 25-10, 25-11

overview of, 25-2

procedures, 25-7

creating and altering, 25-9

executing, 25-7

in packages, 25-10

RESTRICTED SESSION, 24-23

revoked

object dependencies and, 19-7

revoking, 25-3, 25-4

roles, 25-17

restrictions on, 25-22

schema object, 25-3

DML and DDL operations, 25-5

granting and revoking, 25-4

packages, 25-10

procedures, 25-7

schema object, definition, 2-22

system, 25-2

granting and revoking, 25-3

system, definition, 2-22

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

trigger privileges, 25-8

views, 25-6

creating, 25-6

using, 25-7

Pro*C/C++

processing SQL statements, 16-11

user-defined datatypes, 14-17

procedures, 16-18, 16-23, 16-28, 19-9

advantages of, 16-28

auditing, 26-8

contrasted with anonymous blocks, 16-30

contrasted with functions, 16-24

cursors and, 16-20

definer rights, 25-8

roles disabled, 25-21

definition, 2-16

dependency tracking in, 19-6

examples of, 25-10, 25-11

executing, 16-19

external procedures, 16-31

INVALID status, 19-3, 19-6

invoker rights, 25-9

roles used, 25-21

supplied packages, 25-9

prerequisites for compilation of, 19-5

privileges

create or alter, 25-9

executing, 25-7

executing in packages, 25-10

security enhanced by, 16-28, 25-8

shared SQL areas and, 8-11

stored procedures, 16-18, 16-21, 16-24

supplied packages

invoker or definer rights, 25-9

triggers, 18-2

process failure

definition, 2-26

process global area (PGA), 8-16

See also program global area

process monitor (PMON) process

definition, 1-18

process monitor process (PMON)

cleans up timed-out sessions, 24-20

described, 9-12

processes, 9-2

archiver (ARCn), 9-13

background, 9-5

diagrammed, 9-6

checkpoint (CKPT), 9-11

checkpoints and, 9-8

classes of parallel execution, 12-2, 12-6, 12-11, 12-13, 12-19

database writer (DBWn), 9-8

dedicated server, 9-19

definition, 1-15

dispatcher (Dnnn), 9-19

distributed transaction resolution, 9-12

Global Cache Service (LMS), 9-14

job queue, 9-12

listener, 7-9, 9-19

shared servers and, 9-16

log writer (LGWR), 9-9

multiple-process Oracle, 9-2

Oracle, 9-5

Oracle, definition, 1-16

parallel execution coordinator, 20-4

direct-path INSERT, 21-4

parallel execution servers, 20-4

direct-path INSERT, 21-4, 21-5

process monitor (PMON), 9-12

queue monitor (QMNn), 9-14

recoverer (RECO), 9-12

server, 9-5

dedicated, 9-21

shared, 9-19

shadow, 9-21

shared server, 9-16

client requests and, 9-17

structure, 9-2

system monitor (SMON), 9-11

trace files for, 9-14

user, 9-4

recovery from failure of, 9-12

sharing server processes, 9-19

processing

DDL statements, 16-15

distributed, definition, 2-2

DML statements, 16-11

overview, 16-8

parallel SQL, 20-2

queries, 16-13

profiles

password management, 24-8

user, definition, 2-23

when to use, 24-21

program global area (PGA), 8-16

definition, 1-15

shared servers, 9-20

program interface, 9-22

definition, 1-19

Oracle side (OPI), 9-23

structure of, 9-23

two-task mode in, 9-22

user side (UPI), 9-23

program units, 16-18, 16-24

definition, 1-26

prerequisites for compilation of, 19-5

shared pool and, 8-11

proxies, 24-10

pseudocode

triggers, 18-24

pseudocolumns

CHECK constraints prohibit

LEVEL and ROWNUM, 23-21

modifying views, 18-14

ROWID, 13-17

USER, 25-7

PSP. See PL/SQL Server Pages

public key infrastructure, 24-5

public rollback segments, 3-31

PUBLIC user group, 24-17, 25-21

publication

DDL statements, 18-18

DML statements, 18-19

logon/logoff events, 18-18

system events

server errors, 18-18

startup/shutdown, 18-18

using triggers, 18-16

publish-subscribe support

event publication, 18-17

triggers, 18-16

purchase order example

object types, 14-2, 14-4

QMNn background process, 9-14

queries

composite indexes, 11-29

default locking of, 22-27

define phase, 16-13

describe phase, 16-13

fetching rows, 16-13

in DML, 16-3

inline views, 11-21

merged with view queries, 11-18

parallel processing, 20-2

phases of, 22-5

processing, 16-13

read consistency of, 22-5

stored as views, 11-15

temporary segments and, 3-20, 16-13

triggers use of, 18-22

query rewrite, 11-21

dynamic predicates in security policies, 25-24

queue monitor (QMNn) process

definition, 1-19

queue monitor process (QMNn), 9-14

queuing

publish-subscribe support

event publication, 18-17

queue monitor process, 9-14

Quiesce Database, 22-15

uses for, 1-34

quotas

revoking tablespace access and, 24-17

setting to zero, 24-17

SYS user not subject to, 24-17

tablespace, 24-16

temporary segments ignore, 24-17

tablespace, definition, 2-23

RADIUS, 24-7

RAW datatype, 13-16

RAWTOHEX function

data conversion, 13-27

RAWTONHEX function

data conversion, 13-27

RDBMS

object-relational DBMS, 14-2

RDBMS. See relational database management system (RDBMS)

read committed isolation, 22-7, 22-8

read consistency, 22-2, 22-4

Cache Fusion, 22-6

definition, 1-32

dirty read, 22-3, 22-10

multiversion consistency model, 22-4

nonrepeatable read, 22-3, 22-10

phantom read, 22-3, 22-10

queries, 16-13, 22-4

Real Application Clusters, 22-6

rollback segments and, 3-26

statement level, 22-5

subqueries in DML, 22-14

transactions, 22-4, 22-6

triggers and, 18-20, 18-22

read snapshot time, 22-10

read uncommitted, 22-3

readers block writers, 22-10

read-only

databases

opening, 6-10

tablespaces, 4-15

transactions, definition, 1-33

reads

data block

limits on, 24-20

dirty, 22-3

repeatable, 22-6

Real Application Clusters

concurrency limits and, 24-23

databases and instances, 6-3

exclusive mode

rollback segments and, 3-32

isolation levels, 22-11

lock processes, 9-14

mounting a database using, 6-7

named user licensing and, 24-24

parallel SQL, 20-1

read consistency, 22-6

reverse key indexes, 11-46

shared mode

rollback segments and, 3-32

system change numbers, 9-10

system monitor process and, 9-11

temporary tablespaces, 4-16

recoverer (RECO) process

definition, 1-18

recoverer process (RECO), 9-12

in-doubt transactions, 6-10, 17-10

recovery

basic steps, 2-30

block-level recovery, 22-22

crash recovery

instance failure, 6-11

opening a database, 6-9

required after aborting instance, 6-11

SMON process, 9-11

SMON process, definition, 1-17

Disaster Recovery Server, 2-41

distributed processing in, 9-12

general overview, 2-25

instance recovery

SMON process, 9-11

SMON process, definition, 1-17

media recovery

dispatcher processes, 9-20

of distributed transactions, 6-10

point-in-time

clone database, 6-8

process recovery, 9-12

Recovery Manager (RMAN)

definition, 2-32

recursive SQL

cursors and, 16-7

redo log buffers

definition, 1-14

redo logs

archived, definition, 2-28

archiver (ARCn) process, definition, 1-18

archiver process (ARCn), 9-13

buffer management, 9-9

buffers, 8-10

circular buffer, 9-9

committing a transaction, 9-10

definition, 2-28

files named in control file, 4-21

log sequence numbers

recorded in control file, 4-21

log switch

archiver process, 9-13

archiver process, definition, 1-18

log writer process, 8-10, 9-9

modes, definition, 2-28

multiplexed, definition, 1-9, 2-28

offline, definition, 2-28

online, definition, 2-28

overview, 1-9

size of buffers, 8-10

when temporary segments in, 3-21

writing buffers, 9-9

written before transaction commit, 9-10

referenced

keys, 23-13

objects

dependencies, 19-2

referenced keys

definition, 2-19

REFERENCES privilege

when granted through a role, 25-22

referential integrity, 22-11, 23-13

cascade rule, 23-3

examples of, 23-21

PRIMARY KEY constraints, 23-11

restrict rule, 23-3

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

set to default rule, 23-3

set to null rule, 23-3

refresh

incremental, 11-23

job queue processes, 9-12

materialized views, 11-23

REFs

dangling, 14-10

dereferencing of, 14-10

for rows of object views, 15-3

implicit dereferencing of, 14-10

pinning, 15-4

scoped, 14-9

REFTOHEX function

data conversion, 13-27

relational database management system (RDBMS)

definition, 1-21

relational DBMS (RDBMS)

object-relational DBMS, 14-2

SQL and, 16-2

remote dependencies, 19-12

remote transactions

parallel DML and DDL restrictions, 20-12

REMOTE_DEPENDENCIES_MODE parameter, 19-12

RENAME statement, 16-4

repeatable reads, 22-3

replication

definition, 2-4

materialized views (snapshots), 11-21

reserved words, 16-3

reset_compiler_option method, 16-38

resource allocation, 10-1, 10-2

CPU time, 10-13

directives, 10-11

levels and priorities, 10-16

multilevel plans, 10-13

plan-level methods, 10-11

resource allocation methods

definition, 10-3

resource consumer groups

definition, 10-3

resource consumer-group methods, 10-11

resource consumers

grouping, 10-7

resource limits

call level, 24-19

connect time for each session, 24-20

CPU time limit, 24-19

determining values for, 24-21

idle time in each session, 24-20

logical reads limit, 24-20

number of sessions for each user, 24-20

private SGA space for each session, 24-21

session level, 24-19

resource plan

definition, 10-3

resource plan directives

definition, 10-3

resource plans

activating, 10-8

dynamic, 10-8

grouping, 10-8

hierarchical, 10-10

levels, 10-10

performance, 10-10

persistent, 10-8

plan schemas, 10-11

RESOURCE role, 25-23

response queues, 9-17

restart_debugging method, 16-40

restrict_permission method, 16-39

restricted mode

starting instances in, 6-6

restricted rowid format, 13-19

RESTRICTED SESSION privilege, 24-23

restrictions

parallel DDL, 20-12

remote transactions, 20-12

parallel DML

remote transactions, 20-12

resumable space allocation

overview, 17-5

resumable statements. See resumable space allocation

REVERSE clause for indexes, 11-46

reverse key indexes, 11-46

REVOKE statement, 16-4

locks, 22-30

revoke_permission method, 16-39

rewrite

predicates in security policies, 25-24

using materialized views, 11-21

RMAN. See Recovery Manager (RMAN)

roles, 25-17

application, 25-19

CONNECT role, 25-23

DBA role, 25-23

DDL statements and, 25-22

definer-rights procedures disable, 25-21

definition, 2-22

dependency management in, 25-22

enabled or disabled, 25-20

EXP_FULL_DATABASE role, 25-23

functionality, 25-2

granting, 25-3, 25-20

IMP_FULL_DATABASE role, 25-23

in applications, 25-18

invoker-rights procedures use, 25-21

managing through operating system, 25-23

naming, 25-21

predefined, 25-23

RESOURCE role, 25-23

restrictions on privileges of, 25-22

revoking, 25-20

schemas do not contain, 25-21

secure application roles, 25-27

security domains of, 25-21

setting in PL/SQL blocks, 25-21

use of passwords with, 25-18

user, 25-19

users capable of granting, 25-20

uses of, 25-18

rollback, 3-25, 17-7

definition, 2-13

described, 17-7

ending a transaction, 17-2, 17-5, 17-7

statement-level, 17-4

to a savepoint, 17-8

rollback entries, 3-25

Rollback Segment Undo

definition, 2-29

rollback segments, 3-24

access to, 3-25

acquired during startup, 6-9

allocation of extents for, 3-27

new extents, 3-29

clashes when acquiring, 3-32

committing transactions and, 3-26

contention for, 3-26

deallocating extents from, 3-30

deferred, 3-35

definition, 1-8

dropping, 3-30

restrictions on, 3-35

how transactions write to, 3-27

in-doubt distributed transactions, 3-29

invalid, 3-33

locks on, 22-31

moving to the next extent of, 3-27

number of transactions per, 3-26

offline, 3-33, 3-35

offline tablespaces and, 3-35

online, 3-33, 3-35

overview of, 3-24

partly available, 3-33

private, 3-31

public, 3-31

read consistency and, 3-26, 22-4

recovery needed for, 3-33

states of, 3-33

SYSTEM rollback segment, 3-31

transactions and, 3-26

when acquired, 3-31

when used, 3-25

written circularly, 3-26

ROLLBACK statement, 16-5

rolling back

definition, 2-31

rolling back transactions, 17-2, 17-7

rolling forward

definition, 2-31

row cache, 8-12

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

row directories, 3-5

row locking, 22-11, 22-21

block-level recovery, 22-22

serializable transactions and, 22-8

row objects, 14-8

row pieces, 1-10, 11-6

headers, 11-7

how identified, 11-8

row triggers, 18-9

when fired, 18-20

See also triggers

ROWID datatype, 13-17, 13-18

extended rowid format, 13-18

restricted rowid format, 13-19

rowids, 11-8

accessing, 13-17

changes in, 13-18

in non-Oracle databases, 13-24

internal use of, 13-17, 13-21

logical, 13-17

logical rowids, 13-22

index on index-organized table, 11-60

physical guesses, 11-60, 13-22

staleness of guesses, 13-23

statistics for guesses, 13-23

of clustered rows, 11-8

physical, 13-17

row migration, 3-7

sorting indexes by, 11-35

universal, 13-17

ROWIDTOCHAR function

data conversion, 13-27

ROWIDTONCHAR function

data conversion, 13-27

row-level locking, 22-10, 22-21

rows, 11-4

addresses of, 11-8

chaining across blocks, 1-10, 3-7, 11-6

clustered, 11-8

rowids of, 11-8

definition, 1-24

described, 11-4

fetched, 16-13

format of in data blocks, 3-5

headers, 11-6

locking, 22-11, 22-21

locks on, 22-21, 22-24

logical rowids, 13-22

index-organized tables, 11-60

migrating to new block, 3-7

pieces of, 11-6

row objects, 14-8

row-level security, 25-24

shown in rowids, 13-19, 13-20

size of, 11-6

storage format of, 11-6

triggers on, 18-9

when rowid changes, 13-18

same-row writers block writers, 22-10

SAVEPOINT statement, 16-5

savepoints, 17-8

described, 17-8

implicit, 17-4

in transactions, definition, 2-15

rolling back to, 17-8

scalability

client/server architecture, 7-7

parallel SQL execution, 20-2

scans

full table

LRU algorithm, 8-7

parallel query, 20-3

table scan and CACHE clause, 8-7

schema object privileges, 25-3

definition, 2-22

DML and DDL operations, 25-5

granting and revoking, 25-4

views, 25-6

schema objects, 11-1

auditing, 26-8

auditing, definition, 2-24

creating

tablespace quota required, 24-16

default tablespace for, 24-16

definition, 1-6, 1-23

dependencies of, 19-2

and distributed databases, 19-13

and views, 11-19

on non-existence of other objects, 19-10

triggers manage, 18-20

dependent on lost privileges, 19-7

dimensions, 11-24

in a revoked tablespace, 24-17

information in data dictionary, 5-2

INVALID status, 19-3

list of, 11-2

materialized views, 11-21

privileges on, 25-3

relationship to datafiles, 4-19, 11-3

trigger dependencies on, 18-24

user-defined types, 14-3

schemas, 24-2

contents of, 11-3

contrasted with tablespaces, 11-3

defined, 24-2

definition of, 11-2

user-defined datatypes, 14-17

SCN. See system change numbers

scoped REFs, 14-9

secure application roles, 25-27

security, 24-2

administrator privileges, 6-3

application enforcement of, 25-18

auditing, 26-2, 26-7

data, definition, 2-20

discretionary access control, 24-2

discretionary access control, definition, 2-20

domains, 24-2

domains, definition, 2-21

dynamic predicates, 25-24

enforcement mechanisms listed, 2-20

fine-grained access control, 25-24

general overview, 2-19

JVM, 16-40

passwords, 24-8

policies

implementing, 25-26

procedures enhance, 25-8

program interface enforcement of, 9-22

security policies, 25-24

system, 5-3

system, definition, 2-20

views and, 11-17

views enhance, 25-7

security domains, 24-2

definition, 2-21

enabled roles and, 25-20

tablespace quotas, 24-16

segment space management, automatic, 3-6

segments, 3-18

data, 3-19

data, definition, 1-7

deallocating extents from, 3-16

defined, 3-3

definition, 1-7

header block, 3-13

index, 3-20

index, definition, 1-7

overview of, 3-18

rollback, 3-24

rollback, definition, 1-8

table

high water mark, 21-4

temporary, 3-20, 11-13

allocating, 3-20

cleaned up by SMON, 9-11

dropping, 3-18

ignore quotas, 24-17

operations that require, 3-20

tablespace containing, 3-21

temporary, definition, 1-8

SELECT statement, 16-3

composite indexes, 11-29

subqueries, 16-13

See also queries

selfish style of method invocation, 14-6

sequences, 11-25

auditing, 26-8

CHECK constraints prohibit, 23-21

definition, 1-26

independence from tables, 11-25

length of numbers, 11-25

number generation, 11-25

server processes, 9-5

listener process and, 7-9

servers

client/server architecture, 7-2

dedicated, 9-21

shared servers contrasted with, 9-15

dedicated server architecture, 9-3

in client/server architecture, definition, 2-2

shared

architecture, 9-3, 9-16

dedicated servers contrasted with, 9-15

processes of, 9-16, 9-19

server-side scripts, 16-23

service names, 7-9

SERVICE_NAMES parameter, 7-10

session control statements, 16-5

SESSION_ROLES view

queried from PL/SQL block, 25-21

sessions

auditing by, 26-12

connections contrasted with, 9-4

defined, 9-4, 26-12

lightweight, 24-10

limit on concurrent

by license, 24-22

limits for each user, 24-20

memory allocation in the large pool, 8-14

package state and, 19-7

resource limits and, 24-19

time limits on, 24-20

transaction isolation level, 22-32

when auditing options take effect, 26-6

SET CONSTRAINTS statement

DEFERRABLE or IMMEDIATE, 23-25

SET ROLE statement, 16-5

SET TRANSACTION statement, 16-5

ISOLATION LEVEL, 22-7, 22-32

READ ONLY clause, 3-26

set_compiler_option method, 16-38

set_output method, 16-39

SGA. See system global area

shadow processes, 9-21

share locks

on foreign keys, 23-17

share table locks (S), 22-25

shared global area (SGA), 8-3

shared mode

rollback segments, 3-32

shared pool, 8-10

allocation of, 8-12

ANALYZE statement, 8-13

definition, 1-14

dependency management and, 8-13

described, 8-10

flushing, 8-13

object dependencies and, 19-11

row cache and, 8-12

size of, 8-10

shared server, 9-15

dedicated server contrasted with, 9-15

described, 9-3, 9-15

dispatcher processes, 9-19

limiting private SQL areas, 24-21

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

private SQL areas, 8-17

processes, 9-19

processes needed for, 9-16

restricted operations in, 9-20

session memory in the large pool, 8-14

shared server processes (Snnn), 9-19

described, 9-19

shared SQL areas, 8-11, 16-7

ANALYZE statement, 8-13

definition, 1-14

dependency management and, 8-13

described, 8-11

loading SQL into, 16-12

overview of, 16-7

parse locks and, 22-30

procedures, packages, triggers and, 8-11

size of, 8-11

SHARED_MEMORY_ADDRESS parameter, 8-15

SHARED_POOL_SIZE parameter, 8-10

system global area size and, 8-5

SHARED_SERVERS parameter, 9-20

shortname method, 16-38

shutdown, 6-11, 6-12

abnormal, 6-6, 6-12

audit record, 26-5

deallocation of the SGA, 8-3

prohibited by dispatcher processes, 9-20

steps, 6-11

SHUTDOWN ABORT statement, 6-12

signature checking, 19-12

SKIP_UNUSABLE_INDEXES parameter, 19-9

SMON background process, 9-11

See also system monitor process

software code areas, 8-21

shared by programs and utilities, 8-21

sort operations, 4-16

sort segments, 4-16

SORT_AREA_SIZE parameter, 3-20

space management

compression of free space in blocks, 3-7

data blocks, 3-8

extents, 3-13

PCTFREE, 3-8

PCTUSED, 3-9

row chaining, 3-7

segments, 3-18

SQL, 16-2

cursors used in, 16-6

Data Definition Language (DDL), 16-4

Data Manipulation Language (DML), 16-3

dynamic SQL, 16-22

embedded, 16-5

user-defined datatypes, 14-18

functions, 16-2

COUNT, 11-51

in CHECK constraints, 23-21

NVL, 11-9

memory allocation for, 8-12

overview of, 16-2

parallel execution, 20-2

parsing of, 16-8

PL/SQL and, 16-18

recursive, 16-6

cursors and, 16-7

reserved words, 16-3

session control statements, 16-5

shared SQL, 16-7

statement-level rollback, 17-4

system control statements, 16-5

transaction control statements, 16-5

transactions and, 17-2, 17-6

types of statements in, 16-3

user-defined datatypes, 14-17

embedded SQL, 14-18

OCI, 14-19

SQL areas

private, 8-11

shared, 8-11, 16-7

shared, definition, 1-14

SQL statements, 16-3, 16-9

array processing, 16-15

auditing, 26-7, 26-11

when records generated, 26-4

auditing, definition, 2-24

creating cursors, 16-12

dictionary cache locks and, 22-31

distributed

routing to nodes, 16-12

embedded, 16-5

execution, 16-9, 16-14

handles, definition, 1-15

number of triggers fired by single, 18-20

parallel execution, 20-2

parallelizing, 20-5

parse locks, 22-30

parsing, 16-12

privileges required for, 25-3

referencing dependent objects, 19-4

resource limits and, 24-19

successful execution, 17-3

transactions, 16-15

triggers on, 18-2, 18-9

triggering events, 18-7

types of, 16-3

SQL*Loader

direct load

similar to direct-path INSERT, 21-2

SQL*Loader utility

definition, 1-12

SQL*Menu

PL/SQL, 16-19

SQL*Module

FIPS flagger, 16-6

stored procedures, 16-21

SQL*Net

See Oracle Net Services

SQL*Plus

ALERT file, 9-15

anonymous blocks, 16-20

connecting with, 24-4

executing a package, 16-27

executing a procedure, 16-26

lock and latch monitors, 22-30

session variables, 16-20

showing size of SGA, 8-5

SQL statements, 16-2

statistics monitor, 24-22

stored procedures, 16-21

SQL_TRACE parameter, 9-15

SQL92, 22-2

SQL-based log analyzer (LogMiner)

general overview, 2-43

SQLJ object types, 14-21

standards

ANSI/ISO, 1-3, 23-5

isolation levels, 22-2, 22-10

FIPS, 16-6

integrity constraints, 23-5

Oracle adherence, 1-3

standby database

logical, definition, 2-37

mounting, 6-8

physical, definition, 2-37

start_debugging method, 16-40

startup, 6-2, 6-6

allocation of the SGA, 8-3

starting address, 8-15

audit record, 26-5

forcing, 6-6

prohibited by dispatcher processes, 9-20

restricted mode, 6-6

steps, 6-6

statement auditing

definition, 2-24

statement failure

definition, 2-26

statement triggers, 18-9

described, 18-9

when fired, 18-20

See also triggers

statement-level read consistency, 22-5

statements

resumable, overview, 17-5

statistics

checkpoint, 9-11

stop_debugging method, 16-40

storage

datafiles, 4-18

indexes, 11-33

logical structures, 4-7, 11-3

nulls, 11-9

restricting for users, 24-16

revoking tablespaces and, 24-17

tablespace quotas and, 24-17

triggers, 18-2, 18-24

view definitions, 11-18

STORAGE clause

using, 3-13

storage parameters

OPTIMAL (in rollback segments), 3-30

setting, 3-13

stored functions, 16-24, 16-28

stored outlines, 16-17

editing, 16-17

stored procedures, 16-18, 16-24, 16-28

calling, 16-21

contrasted with anonymous blocks, 16-30

triggers contrasted with, 18-2

variables and constants, 16-20

See also procedures

Structured Query Language (SQL), 16-2

See also SQL

structures

data blocks

shown in rowids, 13-20

data dictionary, 5-1

datafiles

shown in rowids, 13-20

locking, 22-29

logical, 3-1

data blocks, 3-2, 3-3

extents, 3-2, 3-13

schema objects, 11-3

segments, 3-2, 3-18

tablespaces, 4-1, 4-7

memory, 8-1

physical

control files, 4-20

datafiles, 4-1, 4-18

processes, 9-1

subqueries, 16-13

CHECK constraints prohibit, 23-21

in DML statements

serializable isolation, 22-14

inline views, 11-21

query processing, 16-13

See also queries

summaries, 11-21

supplied packages

invoker or definer rights, 25-9

symmetric multiprocessors, 20-3

synchronous communication

in message queuing, definition, 2-8

synonyms, 19-9

constraints indirectly affect, 23-5

definition, 1-26

described, 11-26

for data dictionary views, 5-4

inherit privileges from object, 25-4

private, 11-26

public, 11-26

uses of, 11-26

SYS username

data dictionary tables owned by, 5-3

security domain of, 24-3

statement execution not audited, 26-5

temporary schema objects owned by, 24-17

V$ views, 5-7

SYSDBA privilege, 6-3

SYSOPER privilege, 6-3

system change numbers (SCN)

committed transactions, 17-7

defined, 17-7

read consistency and, 22-5

redo logs, 9-10

when determined, 22-5

system control statements, 16-5

system global area (SGA), 8-3

allocating, 6-6

contents of, 8-4

data dictionary cache, 5-4, 8-12

database buffer cache, 8-6

definition, 1-14

diagram, 6-2

fixed, 8-4

large pool, 8-14

limiting private SQL areas, 24-21

overview of, 8-3

redo log buffer, 8-10, 17-6

rollback segments and, 17-6

shared and writable, 8-4

shared pool, 8-10

size of, 8-4

variable parameters, 6-4

when allocated, 8-3

system monitor (SMON) process

definition, 1-17

system monitor process (SMON), 9-11

defined, 9-11

Real Application Clusters and, 9-11

temporary segment cleanup, 9-11

system privileges, 25-2

ADMIN OPTION, 25-3

definition, 2-22

described, 25-2

granting and revoking, 25-3

SYSTEM rollback segment, 3-31

system security

definition, 2-20

SYSTEM tablespace, 4-7

data dictionary stored in, 4-7, 5-2, 5-5

online requirement of, 4-14

procedures stored in, 4-8

SYSTEM username

security domain of, 24-3

table functions, 16-32

parallel execution, 16-32

pipelined, 16-32

tables

affect dependent views, 19-5

auditing, 26-8

base

data dictionary use of, 5-3

relationship to views, 11-17

clustered, 11-62

clustered, definition, 1-27

contained in tablespaces, 11-6

controlling space allocation for, 11-5

definition, 1-23

directories, 3-5

DUAL, 5-6

dynamic partitioning, 20-4

enable or disable constraints, 23-26

external, 11-14

full table scan and buffer cache, 8-7

how data is stored in, 11-5

indexes and, 11-27

index-organized

key compression in, 11-46, 11-58

index-organized tables, 11-57

logical rowids, 11-60, 13-22

integrity constraints, 23-2, 23-5

locks on, 22-22, 22-24, 22-25

maximum number of columns in, 11-16

nested tables, 11-12, 14-12

normalized or denormalized, 11-24

object tables, 14-3, 14-8

virtual, 15-2

overview of, 11-4

partitions, 12-2

presented in views, 11-15

privileges on, 25-5

specifying tablespaces for, 11-6

temporary, 11-12

segments in, 3-21

triggers used in, 18-2

validate or novalidate constraints, 23-26

virtual or viewed, 1-24

tablespace point-in-time recovery

clone database, 6-8

tablespaces, 4-7

contrasted with schemas, 11-3

default for object creation, 24-16

default for object creation, definition, 2-23

definition, 1-5

described, 4-7

dictionary-managed, 4-11

how specified for tables, 11-6

locally-managed, 4-12

locks on, 22-31

moving or copying to another database, 4-18

offline, 4-14, 4-19

and index data, 4-15

remain offline on remount, 4-14

online, 4-14, 4-19

online and offline distinguished, 1-6

overview of, 4-7

quotas on, 24-16

limited and unlimited, 24-17

no default, 24-16

quotas, definition, 2-23

read-only, 4-15

relationship to datafiles, 4-2

revoking access from users, 24-17

size of, 4-5

space allocation, 4-11

temporary, 4-16

default for user, 24-16

temporary, definition, 2-23

transportable, 4-17

used for temporary segments, 3-21

See also SYSTEM tablespace

TAF. See Transparent Application Failover (TAF)

tasks, 9-2

tempfiles, 4-20

temporary segments, 3-21, 11-13

allocating, 3-21

allocation for queries, 3-21

deallocating extents from, 3-18

definition, 1-8

dropping, 3-18

ignore quotas, 24-17

operations that require, 3-20

tablespace containing, 3-21

when not in redo log, 3-21

temporary tables, 11-12

temporary tablespace

default, 4-9

temporary tablespaces, 4-16

definition, 2-23

threads

shared server, 9-15, 9-19

three-valued logic (true, false, unknown)

produced by nulls, 11-9

time stamp checking, 19-12

time zones

in date/time columns, 13-13

TIMESTAMP datatype, 13-13

TIMESTAMP WITH LOCAL TIME ZONE datatype, 13-13

TIMESTAMP WITH TIME ZONE datatype, 13-13

TO_CHAR function

data conversion, 13-27

Globalization Support default in CHECK constraints, 23-21

Globalization Support default in views, 11-18

Julian dates, 13-11

TO_CLOB function

data conversion, 13-27

TO_DATE function, 13-10

data conversion, 13-27

Globalization Support default in CHECK constraints, 23-21

Globalization Support default in views, 11-18

Julian dates, 13-11

TO_NCHAR function

data conversion, 13-27

TO_NCLOB function

data conversion, 13-27

TO_NUMBER function, 13-9

data conversion, 13-27

Globalization Support default in CHECK constraints, 23-21

Globalization Support default in views, 11-18

Julian dates, 13-11

trace files, 9-14

LGWR trace file, 9-9

transaction control statements, 16-5

in autonomous PL/SQL blocks, 17-13

transaction set consistency, 22-10

transaction tables, 3-25

reset at recovery, 9-12

transactions, 17-1

assigning system change numbers, 17-7

assigning to rollback segments, 3-26

autonomous, 17-12

within a PL/SQL block, 17-12

block-level recovery, 22-22

committing, 9-10, 17-4, 17-6

group commits, 9-10

use of rollback segments, 3-26

committing, definition, 2-14

concurrency and, 22-17

controlling transactions, 16-15

deadlocks and, 17-4, 22-18

defining and controlling, 16-15

definition, 2-13

described, 17-2

discrete transactions, 16-16, 17-11

distributed

deadlocks and, 22-20

parallel DDL restrictions, 20-12

parallel DML restrictions, 20-12

resolving automatically, 9-12

two-phase commit, 17-10

distribution among rollback segments of, 3-26

end of, 17-5

consistent data, 16-15

in-doubt

limit rollback segment access, 3-35

resolving automatically, 6-10, 17-10

rollback segments and, 3-29

use partly available segments, 3-35

manual locking of, 22-32

naming, 17-9

read consistency of, 22-6

read consistency, definition, 1-32

read-only, 22-7

not assigned to rollback segments, 3-26

read-only, definition, 1-33

redo log files written before commit, 9-10

rollback segments and, 3-26

rolling back, 17-7

and offline tablespaces, 3-35

partially, 17-8

use of rollback segments, 3-25

rolling back, definition, 2-14

savepoints in, 17-8

savepoints, definition, 2-15

serializable, 22-7

space used in data blocks for, 3-5

start of, 17-5

statement level rollback and, 17-4

system change numbers, 9-10

terminating the application and, 17-6

transaction control statements, 16-5

triggers and, 18-22

writing to rollback segments, 3-27

TRANSACTIONS parameter, 3-32

TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter, 3-32

transient type descriptions, 14-18

Transparent Application Failover (TAF)

general overview, 2-31

transportable tablespaces, 4-17

triggers, 18-1, 19-9

action, 18-8

timing of, 18-10

AFTER triggers, 18-10

as program units, definition, 2-16

auditing, 26-8

BEFORE triggers, 18-10

cascading, 18-4

compared with Oracle Forms triggers, 18-3

constraints apply to, 18-20

constraints contrasted with, 18-5

data access and, 18-22

dependency management of, 18-24, 19-6

enabled triggers, 18-20

enabled or disabled, 18-19

enforcing data integrity with, 23-5

events, 18-7

examples of, 18-12, 18-14, 18-22

firing (executing), 18-2, 18-24

privileges required, 18-24

steps involved, 18-20

timing of, 18-20

INSTEAD OF triggers, 18-13

object views and, 15-5

INVALID status, 19-3, 19-6

Java, 18-8

overview of, 18-2

parts of, 18-6

privileges for executing, 25-8

roles, 25-21

procedures contrasted with, 18-2

prohibited in views, 11-16

publish-subscribe support, 18-16

restrictions, 18-8

row, 18-9

schema object dependencies, 18-20, 18-24

sequence for firing multiple, 18-20

shared SQL areas and, 8-11

statement, 18-9

storage of, 18-24

types of, 18-9

UNKNOWN does not fire, 18-8

uses of, 18-4

TRUNCATE statement, 16-4

two-phase commit

transaction management, 17-10

triggers, 18-20

two-task mode, 9-3

listener process and, 9-19

network communication and, 9-22

program interface in, 9-22

type descriptions

dynamic creation and access, 14-18

transient, 14-18

type inheritance, 14-13

definition, 1-22

types

privileges on, 25-12

See datatypes, object types

UDAG (User-Defined Aggregate Functions), 14-14

UDAGs (User-Defined Aggregate Functions)

creation and use of, 14-15

undo, 1-8

See also rollback

undo management, automatic, 3-22

undo tablespaces, 4-8

unique indexes, 11-29

UNIQUE key constraints, 23-8

composite keys, 23-9, 23-11

constraint checking, 23-23

indexes used to enforce, 23-10

maximum number of columns, 23-10

NOT NULL constraints and, 23-11

nulls and, 23-11

size limit of, 23-10

unique keys, 23-9

composite, 23-9, 23-11

definition, 2-19

UNUSABLE indexes

function-based, 19-9

update no action constraint, 23-16

UPDATE statement, 16-3

foreign key references, 23-16

freeing space in data blocks, 3-6

triggers, 18-2, 18-7

BEFORE triggers, 18-10

updates

object views, 15-5

updatability of object views, 15-5

updatability of views, 11-20, 18-13, 18-14

updatable join views, 11-20

update intensive environments, 22-8

updating tables

with parent keys, 23-17, 23-19

UROWID datatype, 13-17

USE_INDIRECT_DATA_BUFFERS parameter, 8-16

user error failure

definition, 2-25

user processes

connections and, 9-4

dedicated server processes and, 9-21

definition, 1-15

sessions and, 9-4

shared server processes and, 9-19

user profiles

definition, 2-23

user program interface (UPI), 9-23

USER pseudocolumn, 25-7

USER_ views, 5-5

USER_OBJECTS view, 16-38

USER_UPDATABLE_COLUMNS view, 11-20

User-Defined Aggregate Functions (UDAGs), 14-14

creation and use of, 14-15

user-defined datatypes, 14-1, 14-3

collections, 14-11

nested tables, 14-12

variable arrays (VARRAYs), 14-11

object types, 14-2, 14-4

users, 24-2

access rights, 24-2

auditing, 26-14

authentication of, 24-3

dedicated servers and, 9-21

default tablespaces of, 24-16

enterprise, 24-2

licensing by number of, 24-24

licensing of, 24-22

listed in data dictionary, 5-2

locks, 22-40

multiuser environments, 9-2

password encryption, 24-8

processes of, 9-4

profiles of, 24-21

PUBLIC user group, 24-17, 25-21

resource limits of, 24-19

roles and, 25-17

for types of users, 25-19

schemas of, 24-2

security domains of, 24-2, 25-21

tablespace quotas of, 24-16

temporary tablespaces of, 3-21, 24-16

usernames, 24-2

sessions and connections, 9-4

V_$ and V$ views, 5-7

V$LICENSE, 24-23

VALIDATE constraints, 23-26

VARCHAR datatype, 13-4

VARCHAR2 datatype, 13-4

non-padded comparison semantics, 13-4

similarity to RAW datatype, 13-16

variables

bind variables

user-defined types, 14-17

embedded SQL, 16-6

in stored procedures, 16-20

object variables, 15-4

varrays, 14-11

index-organized tables, 11-58

key compression, 11-46

view hierarchies, 15-6

views, 11-15

altering base tables and, 19-5

auditing, 26-8

constraints indirectly affect, 23-5

containing expressions, 18-14

data dictionary

updatable columns, 11-20

user-accessible views, 5-3

definition, 1-24

definition expanded, 19-5

dependency status of, 19-5

fixed views, 5-7

Globalization Support parameters in, 11-18

how stored, 11-17

indexes and, 11-18

inherently modifiable, 18-14

inline views, 11-21

INSTEAD OF triggers, 18-13

INVALID status, 19-3

materialized views, 11-21

materialized views, definition, 1-25

maximum number of columns in, 11-16

modifiable, 18-14

modifying, 18-13

object views, 11-20, 15-1

updatability, 15-5

overview of, 11-15

prerequisites for compilation of, 19-5

privileges for, 25-6

pseudocolumns, 18-14

schema object dependencies, 11-19, 19-4, 19-9

security applications of, 25-7

SQL functions in, 11-18

triggers prohibited in, 11-16

updatability, 11-20, 15-5, 18-14

uses of, 11-17

virtual tables. See views

waits for blocking transaction, 22-10

Wallet Manager, 24-5

wallets, 24-5

warehouse

materialized views, 11-21

web page scripting, 16-23

whole database backups

definition, 2-30

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

write-ahead, 9-9

writers block readers, 22-10

X.509 certificates, 24-5

XA

session memory in the large pool, 8-14

XML datatypes, 13-25

year 2000, 13-12