Index

A B C D E F G H I J K L M N O P Q R S T U V W

A

abort response, 31-13

two-phase commit, 31-13

accounts

operating system

database administrator, 1-10

user

SYS and SYSTEM, 1-11

ADD LOGFILE MEMBER option

ALTER DATABASE statement, 7-13

ADD LOGFILE option

ALTER DATABASE statement, 7-13

ADD PARTITION clause, 17-27

ADD SUBPARTITION clause, 17-29, 17-30

ADMIN OPTION

about, 25-12

revoking roles/privileges, 25-16

ADMIN_TABLES procedure, 22-4

DBMS_REPAIR package

ADMIN_TABLES procedure, 22-3

examples

building orphan key table, 22-10

building repair table, 22-9

ADMINISTER_RESOURCE_MANAGER system privilege, 27-8

administration

distributed databases, 29-1

tools, 28-31

administrators

application, 1-4

AFTER SUSPEND system event, 14-21

AFTER SUSPEND trigger, 14-21

example of registering, 14-23

agent

Heterogeneous Services, definition of, 28-5

aggregate functions, 29-33

alert file

job failures and, 10-10

alert log

about, 5-15

location of, 5-16

size of, 5-16

using, 5-15

when written, 5-17

ALL_DB_LINKS view, 29-21

ALL_JOBS view

jobs in system, viewing, 10-15

allocation

extents, 15-12

minimizing extents for rollback segments, 13-24

ALTER CLUSTER statement

ALLOCATE EXTENT clause, 18-9

using for hash clusters, 19-9

using for index clusters, 18-9

ALTER DATABASE statement

ADD LOGFILE MEMBER option, 7-13

ADD LOGFILE option, 7-13

ARCHIVELOG option, 8-5

CLEAR LOGFILE option, 7-19

CLEAR UNARCHIVED LOGFILE option, 7-7

database partially available to users, 4-9

DATAFILE...OFFLINE DROP clause, 12-9

datafiles online or offline, 11-24, 12-10

default temporary tablespace, specifying, 2-24

DROP LOGFILE MEMBER option, 7-17

DROP LOGFILE option, 7-16

MOUNT clause, 4-9

NOARCHIVELOG option, 8-5

OPEN clause, 4-10

READ ONLY clause, 4-10

RENAME FILE clause, 12-13

tempfiles online or offline, 11-24, 12-10

UNRECOVERABLE DATAFILE option, 7-20

ALTER FUNCTION statement

COMPILE clause, 21-25

ALTER INDEX statement

COALESCE clause, 16-8

for maintaining partitioned indexes, 17-23 to 17-61

MONITORING USAGE clause, 16-21

ALTER PACKAGE statement

COMPILE clause, 21-25

ALTER PROCEDURE statement

COMPILE clause, 21-25

ALTER PROFILE statement

altering resource limits, 24-21

ALTER RESOURCE COST statement, 24-21

ALTER ROLE statement

changing authorization method, 25-7

ALTER ROLLBACK SEGMENT statement

bringing segments online, 13-22

changing storage parameters, 13-21

taking segment offline, 13-23

ALTER SEQUENCE statement, 20-13

ALTER SESSION statement

ADVISE clause, 32-10

CLOSE DATABASE LINK clause, 30-2

SET SQL_TRACE initialization parameter, 5-17

setting time zone, 2-28

system privilege, 30-2

ALTER SYSTEM statement

ARCHIVE LOG ALL option, 8-9

ARCHIVE LOG option, 8-8

DISABLE DISTRIBUTED RECOVERY clause, 32-25

ENABLE DISTRIBUTED RECOVERY clause, 32-25

ENABLE RESTRICTED SESSION clause, 4-11

QUIESCE RETRICTED, 4-15

RESUME clause, 4-17

SCOPE clause for SET, 2-47

SET RESOURCE_LIMIT option, 24-19

SET RESOURCE_MANAGER_PLAN, 27-25

SET SHARED_SERVERS initialization parameter, 5-10

setting initialization parameters, 2-47

SUSPEND clause, 4-17

SWITCH LOGFILE option, 7-18

to enable Database Resource Manager, 27-24

UNQUIESCE, 4-16

ALTER TABLE

MODIFY DEFAULT ATTRIBUTES FOR PARTITION clause, 17-43

ALTER TABLE statement

ADD (column) clause, 15-13

ALLOCATE EXTENT clause, 15-12

DEALLOCATE UNUSED clause, 15-13

DISABLE ALL TRIGGERS clause, 21-14

DISABLE integrity constraint clause, 21-18

DROP COLUMN clause, 15-14

DROP integrity constraint clause, 21-19

DROP UNUSED COLUMNS clause, 15-15

ENABLE ALL TRIGGERS clause, 21-13

ENABLE integrity constraint clause, 21-18

external tables, 15-38

for maintaining partitions, 17-23 to 17-61

MODIFY (column) clause, 15-13

MODIFY DEFAULT ATTRIBUTES clause, 17-43

modifying index-organized table attributes, 15-30

MOVE clause, 15-12, 15-30

reasons for use, 15-10

RENAME COLUMN clause, 15-14

SET UNUSED clause, 15-15

ALTER TABLESPACE statement

ADD DATAFILE parameter, 11-11

ONLINE option, example, 11-23

READ ONLY option, 11-25

READ WRITE option, 11-27

RENAME DATAFILE clause, 12-11

taking datafiles/tempfiles online/offline, 11-23, 12-9

ALTER TRIGGER statement

DISABLE clause, 21-14

ENABLE clause, 21-13

ALTER USER privilege, 24-6

ALTER USER statement

default roles, 25-21

GRANT CONNECT THROUGH clause, 24-16

REVOKE CONNECT THROUGH clause, 24-16

ALTER VIEW statement

COMPILE clause, 21-25

altering indexes, 16-19 to 16-21

altering storage parameters, 15-11

altering users, 24-6

ANALYZE statement, 21-5

CASCADE clause, 21-7

corruption reporting, 22-5

ESTIMATE STATISTICS SAMPLE clause, 21-5

LIST CHAINED ROWS clause, 21-7

listing chained rows, 21-7

VALIDATE STRUCTURE clause, 21-6

VALIDATE STRUCTURE ONLINE clause, 21-7

validating structure, 22-4

ANALYZE TABLE statement, 30-7

analyzing redo log files, 9-1

analyzing schema objects, 21-3 to 21-6

analyzing tables

cost-based optimization, 30-7

application administrators, 23-12

application context, 23-4

application developers

privileges for, 23-10

roles for, 23-11

application development

constraints, 30-3

database links

controlling connections, 30-2

distributed databases, 30-1

analyzing execution plan, 30-9

controlling connections, 30-2

handling errors, 30-3

handling RPC errors, 30-11

managing distribution of data, 30-2

managing referential integrity, 30-3

optimizing distributed queries, 28-47

overview, 28-44

remote procedure calls, 28-46

tuning distributed queries, 30-3

tuning using collocated inline views, 30-4

using cost-based optimization, 30-5

using hints to tune queries, 30-8

distributing data, 30-2

referential integrity, 30-3

remote connections

terminating, 30-2

security for, 23-10

applications

administrator, 1-4

errors

RAISE_APPLICATION_ERROR() procedure, 30-11

applications administrator, 1-4

AQ_ADMINISTRATOR_ROLE role, 25-6

AQ_USER_ROLE role, 25-6

ARCH process

specifying multiple processes, 8-19

architecture

Optimal Flexible Architecture (OFA), 2-7

ARCHIVE LOG option

ALTER SYSTEM statement, 8-8

archive processes, 5-12

ARCHIVE_LAG_TARGET initialization parameter, 7-11

archived redo logs, 8-2

archiving modes, 8-5

controlling destination availability state, 8-14

destination status, 8-12

destinations

mandatory, 8-16

minimum number of, 8-16

re-archiving to failed, 8-18

sample scenarios, 8-17

failed destinations and, 8-16

multiplexing, 8-10

normal transmission of, 8-14

specifying destinations for, 8-10

standby transmission of, 8-14

status information, 8-24

transmitting, 8-14

tuning, 8-19

archived redo mandatory destinations

for archived redo logs, 8-16

ARCHIVELOG mode, 8-3

advantages, 8-3

archiving, 8-2

automatic archiving in, 8-3

definition of, 8-3

distributed databases, 8-4

enabling, 8-5

manual archiving in, 8-3

running in, 8-3

switching to, 8-5

taking datafiles offline and online in, 12-9

archivelog process (ARCn)

tracing, 8-21

archiver, 5-12

archiving

advantages, 8-2

automatic

disabling, 8-8

disabling at instance startup, 8-8

enabling after instance startup, 8-7

enabling at instance startup, 8-7

changing archiving mode, 8-5

controlling destination availability state, 8-14

controlling number of processes, 8-7

destination status, 8-12

destinations

failure, 8-16

disabling, 8-5, 8-8

disadvantages, 8-2

enabling, 8-5, 8-7

manual, 8-9

multiple ARCH processes, 8-19

privileges

disabling, 8-8

enabling, 8-6

for manual archiving, 8-9

setting initial mode, 8-5

to failed destinations, 8-18

trace, controlling, 8-21

tuning, 8-19

viewing information on, 8-24

AUDIT statement

BY proxy clause, 26-13

schema objects, 26-12

statement auditing, 26-11

system privileges, 26-11

audit trail, 26-15

archiving, 26-17

auditing changes to, 26-18

controlling size of, 26-15

creating and deleting, 26-19

deleting views, 26-20

dropping, 26-19

interpreting, 26-20

maximum size of, 26-16

protecting integrity of, 26-18

purging records from, 26-16

recording changes to, 26-18

reducing size of, 26-17

table that holds, 26-2

views on, 26-19

AUDIT_FILE_DEST initialization parameter, 26-8

setting for OS auditing, 26-8

AUDIT_SYS_OPERATIONS initialization parameter, 26-8

auditing SYS, 26-6

AUDIT_TRAIL initialization parameter, 26-8

auditing SYS, 26-6

setting, 26-8

auditing, 26-2

audit option levels, 26-9

audit trail records, 26-4

database links, 28-31

default options, 26-13

disabling default options, 26-15

disabling options, 26-8, 26-13, 26-14

disabling options versus auditing, 26-14

enabling options, 26-8

privileges for, 26-8

enabling options versus auditing, 26-10

fine-grained, 26-18

guidelines, 26-2

historical information, 26-4

information stored in OS file, 26-5

keeping information manageable, 26-3

managing the audit trail, 26-19

multi-tier environments, 26-13

operating-system audit trails, 26-2

policies for, 23-20

privilege audit options, 26-11

privileges required for object, 26-12

privileges required for system, 26-12

schema objects, 26-12

session level, 26-11

statement, 26-11

statement level, 26-11

suspicious activity, 26-3

SYS, 26-6

system privileges, 26-11

to OS file, 26-8

using the database, 26-2

viewing

active object options, 26-22

active privilege options, 26-22

active statement options, 26-22

default object options, 26-23

views, 26-19

AUTHENTICATED BY clause

CREATE DATABASE LINK statement, 29-16

authentication

by database, 24-9

by SSL, 24-8, 24-14

database links, 28-25

directory service, 24-14

external, 24-11

global, 24-13

operating system, 1-17

password policy, 23-5

proxy, 24-16

selecting a method, 1-15

specifying when creating a user, 24-3

users, 23-2

using password file, 1-19

ways to authenticate users, 24-8

authorization

changing for roles, 25-7

global, 24-13

omitting for roles, 25-7

operating-system role management and, 25-9

roles, about, 25-8

automatic undo management, 2-24

B

background processes, 5-11 to 5-13

FMON, 12-17

BACKGROUND_DUMP_DEST initialization parameter, 5-16

backups

after creating new databases, 2-22

guidelines, 1-7

effects of archiving on, 8-3

BLANK_TRIMMING initialization parameter, 15-13

BLOCKSIZE clause

of CREATE TABLESPACE, 11-19

broken jobs

about, 10-12

running, 10-13

BUFFER_POOL storage parameter

description, 14-11

buffers

buffer cache in SGA, 2-40

C

calls

remote procedure, 28-46

CASCADE clause

when dropping unique or primary keys, 21-19

cascading revokes, 25-19

CATAUDIT.SQL script

running, 26-19

CATBLOCK.SQL script, 5-15

CATNOAUD.SQL script

running, 26-20

centralized user management

distributed systems, 28-27

chained rows

eliminating from table, procedure, 21-8

CHAINED_ROWS table

used by ANALYZE statement, 21-7

change vectors, 7-2

CHAR datatype

increasing column length, 15-13

character sets

multibyte characters in role names, 25-7

multibyte characters in role passwords, 25-8

specifying when creating a database, 2-3

CHECK_OBJECT procedure, 22-2, 22-4, 22-5

example, 22-10

checkpoint process, 5-12

checksums

for data blocks, 12-14

redo log blocks, 7-18

CJQ0 background process, 10-2

CLEAR LOGFILE option

ALTER DATABASE statement, 7-19

clearing redo log files, 7-7, 7-19

restrictions, 7-19

client/server architectures

distributed databases, 28-6

direct and indirect connections, 28-7

Globalization Support, 28-47

CLOSE DATABASE LINK clause

ALTER SESSION statement, 30-2

closing database links, 29-19

clustered tables. See clusters.

clusters

allocating extents, 18-9

altering, 18-8

analyzing, 21-3 to 21-6

cluster indexes, 18-10

altering, 18-9

creating, 18-8

dropping, 18-11

cluster keys

columns for, 18-4

definition, 18-2

SIZE parameter, 18-5

clustered tables, 18-2, 18-4, 18-7, 18-11

ALTER TABLE restrictions, 18-9

columns for cluster key, 18-4

creating, 18-6

deallocating extents, 18-9

dropping, 18-10

estimating space, 18-5, 18-6

guidelines for managing, 18-4 to 18-6

hash

contrasted with index, 19-2

hash clusters, 19-1 to 19-9

index

contrasted with hash, 19-2

location, 18-6

overview of, 18-2

privileges

for altering, 18-8

for creating, 18-6

for dropping, 18-11

selecting tables, 18-4

single-table hash clusters, 19-5

specifying PCTFREE for, 14-4

truncating, 21-9

validating structure, 21-6

COALESCE PARTITION clause, 17-32

coalescing indexes

costs, 16-8

collocated inline views

tuning distributed queries, 30-4

columns

adding, 15-13

displaying information about, 21-34

dropping, 15-14 to 15-16

granting privileges for selected, 25-15

granting privileges on, 25-15

increasing length, 15-13

INSERT privilege and, 25-15

listing users granted to, 25-28

modifying definition, 15-13

privileges, 25-15

renaming, 15-14

revoking privileges on, 25-18

COMMENT statement, 15-40

COMMIT COMMENT statement

used with distributed transactions, 32-2, 32-10

commit phase, 31-11, 31-23

two-phase commit, 31-14

commit point site, 31-7

commit point strength, 31-8, 32-2

determining, 31-9

distributed transactions, 31-6, 31-8

how Oracle determines, 31-8

commit point strength

definition, 31-8

specifying, 32-2

COMMIT statement

FORCE clause, 32-10, 32-11, 32-12

forcing, 32-9

two-phase commit and, 28-36

COMMIT_POINT_STRENGTH initialization parameter, 31-8, 32-2

committing transactions

distributed

commit point site, 31-7

composite limits

costs and, 24-22

composite partitioned tables

subpartition template, modifying, 17-48

composite partitioning

creating tables using, 17-14

default partition, 17-10

range-list, 17-8, 17-15

when to use, 17-7

CONNECT command

starting an instance, 4-3

CONNECT INTERNAL

desupported, 1-15

CONNECT role, 25-5

connected user database links, 29-12

advantages and disadvantages, 28-16

creating, 29-12

definition, 28-16

example, 28-19

REMOTE_OS_AUTHENT initialization parameter, 28-17

connection qualifiers

database links and, 29-13

connections

auditing, 26-11

remote

terminating, 30-2

constraints

See also integrity constraints

application development issues, 30-3

disabling at table creation, 21-17

dropping integrity constraints, 21-19

enable novalidate state, 21-16

enabling example, 21-17

enabling when violations exist, 21-16

exceptions, 21-16, 21-21

exceptions to integrity constraints, 21-21

integrity constraint states, 21-15

keeping index when disabling, 21-18

keeping index when dropping, 21-18

ORA-02055

constrain violation, 30-3

renaming, 21-19

setting at table creation, 21-17

when to disable, 21-15

control files

adding, 6-5

changing size, 6-5

conflicts with data dictionary, 6-9

creating

about, 6-2

additional control files, 6-5

initially, 6-4

new files, 6-6

creating as Oracle-managed files, 3-17

default name, 2-36, 6-5

dropping, 6-11

errors during creation, 6-10

guidelines for, 6-2 to 6-4

importance of multiplexed, 6-3

location of, 6-3

log sequence numbers, 7-5

mirrored, 6-3

mirroring, 2-36

moving, 6-5

multiplexed

importance of, 6-3

names, 6-2

number of, 6-3

overwriting existing, 2-36

relocating, 6-5

renaming, 6-5

requirement of one, 6-2

size of, 6-4

specifying names before database creation, 2-36

troubleshooting, 6-9

unavailable during startup, 4-6

CONTROL_FILES initialization parameter

overwriting existing control files, 2-36

setting

before database creation, 6-4

names for, 6-2

setting before database creation, 2-36

warning about setting, 2-36

corruption

data block

repairing, 22-2 to 22-15

cost-based optimization, 30-5

distributed databases, 28-47

hints, 30-8

using for distributed queries, 30-5

costs

resource limits and, 24-22

CREATE CLUSTER statement

creating clusters, 18-7

example, 18-7

for hash clusters, 19-4

HASH IS option, 19-4, 19-6

HASHKEYS option, 19-4, 19-7

SIZE option, 19-6

CREATE CONTROLFILE statement

about, 6-6

checking for inconsistencies, 6-9

NORESETLOGS option, 6-8

RESETLOGS option, 6-8

CREATE DATABASE LINK statement, 29-9

CREATE DATABASE statement

CONTROLFILE REUSE option, 6-5

DEFAULT TEMPORARY TABLESPACE clause, 2-24

EXTENT MANAGEMENT LOCAL clause, 2-26

MAXLOGFILES option, 7-10

MAXLOGMEMBERS parameter, 7-10

password for SYS, 2-23

password for SYSTEM, 2-23

setting time zone, 2-28

specifying FORCE LOGGING, 2-29

UNDO TABLESPACE clause, 2-24

used to create an undo tablespace, 13-6

using Oracle-managed files, 3-8

CREATE INDEX statement

NOLOGGING, 16-7

ON CLUSTER option, 18-8

partitioned indexes, 17-12 to 17-15

using, 16-10

with a constraint, 16-11

CREATE PROFILE statement

about, 24-20

CREATE ROLE statement

IDENTIFIED BY option, 25-8

IDENTIFIED EXTERNALLY option, 25-9

CREATE ROLLBACK SEGMENT statement

about, 13-19

CREATE SCHEMA statement

multiple tables and views, 21-2

CREATE SEQUENCE statement, 20-12

CREATE SPFILE statement, 2-46

CREATE SYNONYM statement, 20-14

CREATE TABLE statement

AS SELECT clause, 15-4, 15-8

CLUSTER option, 18-7

COMPRESS clause, 15-29

creating partitioned tables, 17-11 to 17-21

creating temporary table, 15-8

INCLUDING clause, 15-28

index-organized tables, 15-25

MONITORING clause, 15-9

NOLOGGING clause, 15-4

ORGANIZATION EXTERNAL clause, 15-35

OVERFLOW clause, 15-27

parallelizing, 15-8

PCTTHRESHOLD clause, 15-28

TABLESPACE clause, specifying, 15-3

use of, 15-7

CREATE TABLESPACE

BLOCKSIZE CLAUSE, using, 11-19

FORCE LOGGING clause, using, 11-20

Oracle-managed files, 3-14

CREATE TABLESPACE statement

example, 11-10

SEGMENT MANAGEMENT clause, 11-8

CREATE TEMPORARY TABLESPACE

Oracle-managed files, 3-16

CREATE TEMPORARY TABLESPACE statement, 11-13

CREATE UNDO TABLESPACE

Oracle-managed files, 3-14

CREATE UNDO TABLESPACE statement

using to create an undo tablespace, 13-6

CREATE UNIQUE INDEX statement

using, 16-11

CREATE USER statement

IDENTIFIED BY option, 24-3

IDENTIFIED EXTERNALLY option, 24-3

CREATE VIEW statement

about, 20-2

OR REPLACE option, 20-11

WITH CHECK OPTION, 20-3

CREATE_SIMPLE_PLAN procedure

Database Resource Manager, 27-10

creating a database

default temporary tablespace, specifying, 2-24

creating an audit trail, 26-19

creating connected user links

scenario, 29-35, 29-36

creating current user links

scenario, 29-37

creating database links, 29-8

connected user, 29-12

current user, 29-12

example, 28-19

fixed user, 29-11

private, 29-9

public, 29-10

service names within link names, 29-13

specifying types, 29-9

creating databases, 2-1, 8-5

backing up the new database, 2-22

executing CREATE DATABASE, 2-18

manually from a script, 2-5

preparing to, 2-2

prerequisites for, 2-4

problems encountered while, 2-31

UNDO MANAGEMENT clause, 2-24

upgrading to a new release, 2-5

using Database Configuration Assistant, 2-5

with locally managed tablespaces, 2-26

creating datafiles, 12-5

creating fixed user links

scenario, 29-34, 29-35

creating indexes

after inserting table data, 16-3

associated with integrity constraints, 16-11

NOLOGGING, 16-7

USING INDEX clause, 16-11

creating profiles, 24-20

creating sequences, 20-12

creating synonyms, 20-14

creating views, 20-2

current user database links, 29-12

advantages and disadvantages, 28-18

cannot access in shared schema, 28-28

creating, 29-12

definition, 28-16

example, 28-19

schema independence, 28-28

cursors

and closing database links, 30-2

D

data

loading using external tables, 15-35

security of, 23-3

data block corruption

repairing, 22-2 to 22-15

data blocks

altering size of, 2-37

managing space in, 14-2 to 14-7

non-standard block size, 2-37

PCTFREE in clusters, 18-5

shared in clusters, 18-2

specifying size of, 2-37

standard block size, 2-37

transaction entry settings, 14-8

verifying, 12-14

data dictionary

changing storage parameters, 21-30

changing storage parameters of, 21-27

conflicts with control files, 6-9

purging pending rows from, 32-13, 32-14

schema object views, 21-30

segments in the, 21-28

V$DBFILE view, 2-31

V$LOGFILE view, 2-31

data dictionary views

DBA_DB_LINKS, 29-21, 32-3, 32-5

USER, 32-3, 32-5

data encryption

distributed systems, 28-30

data manipulation language

statements allowed in distributed transactions, 28-33

database

granting privileges, 25-11

granting roles, 25-11

database administrators, 1-2

application administrator versus, 23-12

initial priorities, 1-4 to 1-8

operating system account, 1-10

password files for, 1-16

responsibilities of, 1-2

roles

about, 1-13

for security, 23-9

security and privileges of, 1-10

security for, 23-8

security officer versus, 1-3, 23-2

SYS and SYSTEM accounts, 1-11

utilities for, 1-26

database authentication, 24-9

Database Configuration Assistant

advantages, 2-6

configuring options, 2-9

creating databases, 2-7 to 2-9

defined, 2-5

deleting databases, 2-9

managing templates, 2-9

templates, using, 2-11

database links

advantages, 28-11

auditing, 28-31

authentication, 28-25

without passwords, 28-26

closing, 29-19, 30-2

connected user, 29-12, 29-35

advantages and disadvantages, 28-16

definition, 28-16

connections

controlling, 30-2

determining open, 29-24

creating, 29-8

connected user, 29-12, 29-35

connected user, shared, 29-36

current user, 29-12, 29-37

example, 28-19

fixed user, 29-11, 29-34

fixed user, shared, 29-35

obtaining necessary privileges, 29-8

private, 29-9

public, 29-10

scenarios, 29-34

shared, 29-14, 29-15

specifying types, 29-9

current user, 28-15, 29-12

advantages and disadvantages, 28-18

definition, 28-16

data dictionary views

ALL, 32-3, 32-5

DBA_DB_LINKS, 32-3, 32-5

USER, 29-21, 32-3, 32-5

definition, 28-8

distributed queries, 28-34

distributed transactions, 28-35

dropping, 29-19

enforcing global naming, 29-3

enterprise users and, 28-28

fixed user, 29-34

advantages and disadvantages, 28-17

definition, 28-16

global

definition, 28-15

global names, 28-12

global object names, 28-36

handling errors, 30-3

job queues and, 10-9

limiting number of connections, 29-20

listing, 29-21, 32-3, 32-5

managing, 29-18

minimizing network connections, 29-14

name resolution, 28-36

schema objects, 28-38

views, synonyms, and procedures, 28-42

when global database name is complete, 28-37

when global database name is partial, 28-37

when no global database name is specified, 28-37

names for, 28-14

passwords, viewing, 29-22

private

definition, 28-15

public

definition, 28-15

referential integrity in, 30-3

remote queries, 28-33

remote transactions, 28-33, 28-35

resolution, 28-36

restrictions, 28-22

roles on remote database, 28-23

schema objects, 28-20

name resolution, 28-22

synonyms for, 28-21

service names used within link names, 29-13

shared, 28-10

configuring, 29-16

creating, 29-14

creating links to dedicated servers, 29-16

creating links to shared servers, 29-17

determining whether to use, 29-14

shared SQL, 28-34

tuning distributed queries, 30-3

tuning queries with hints, 30-8

tuning using collocated inline views, 30-4

types of links, 28-15

types of users, 28-16

users

specifying, 29-11

using cost-based optimization, 30-5

viewing, 29-21

Database Resource Manager

active session pool with queuing, 27-6

administering system privilege, 27-8 to 27-10

automatic consumer group switching, 27-7

CREATE_SIMPLE_PLAN procedure, 27-10

description, 27-2

enabling, 27-24

execution time limit, 27-7

managing resource consumer groups, 27-20

changing resource consumer groups, 27-21

granting the switch privilege, 27-21, 27-22

revoking the switch privilege, 27-23

setting initial resource consumer group, 27-21

switching a session, 27-21

switching sessions for a user, 27-22

multiple level CPU resource allocation, 27-6

pending area, 27-12 to 27-14

resource allocation methods, 27-4

ACTIVE_SESS_POOL_MTH, 27-15

CPU resource, 27-14

EMPHASIS, 27-14

limiting degree of parallelism, 27-15

PARALLEL_DEGREE_LIMIT_ABSOLUTE, 27-15

PARALLEL_DEGREE_LIMIT_MTH, 27-15

QUEUEING_MTH, 27-15

ROUND-ROBIN, 27-16

resource consumer groups, 27-3

creating, 27-16 to 27-17

DEFAULT_CONSUMER_GROUP, 27-16, 27-17, 27-21, 27-23

deleting, 27-17

LOW_GROUP, 27-17, 27-29

managing, 27-20 to 27-23

OTHER_GROUPS, 27-6, 27-13, 27-16, 27-19, 27-28

parameters, 27-16

SYS_GROUP, 27-17, 27-28

updating, 27-17

resource plan directives, 27-4, 27-12

deleting, 27-19

specifying, 27-17 to 27-20

updating, 27-19

resource plans, 27-3

creating, 27-10 to 27-16

DELETE_PLAN_CASCADE, 27-16

deleting, 27-15

examples, 27-4, 27-25

parameters, 27-14

plan schemas, 27-6, 27-12, 27-16, 27-25, 27-32

subplans, 27-5, 27-6, 27-16

SYSTEM_PLAN, 27-15, 27-17, 27-28

top plan, 27-6, 27-13, 27-24

updating, 27-15

specifying a parallel degree limit, 27-7

undo pool, 27-7

used for quiescing a database, 4-15

validating plan schema changes, 27-12

views, 27-31

database users

enrolling, 1-7

database writer, 5-12

database writer process

calculating checksums for data blocks, 12-14

DATABASE_PROPERTIES view

name of default temporary tablespace, 2-25

databases

administering, 1-1

administration of distributed, 29-1

altering availability, 4-9 to 4-11

auditing, 26-1

backing up, 2-22

after creation of, 1-7

configuring options using DBCA, 2-9

control files of, 6-2

creating, 8-5

opening and, 1-6

creating manually, 2-14 to 2-22

creating using DBCA, 2-7

default temporary tablespace, specifying, 2-24

deleting using DBCA, 2-9

design of

implementing, 1-7

distributed

site autonomy of, 28-24

dropping, 2-31

global database name, about, 2-35

global database names in distributed systems, 2-36

hardware evaluation, 1-5

logical structure of, 1-5

mounting a database, 4-6

mounting to an instance, 4-9

names, about, 2-36

names, conflicts in, 2-36

opening a closed database, 4-10

password encryption, 23-5

physical structure, 1-6

physical structure of, 1-6

planning, 1-5

production, 23-10, 23-12

quiescing, 4-14

read-only, opening, 4-10

recovery, 4-8

renaming, 6-6, 6-8

restricting access, 4-11

resuming, 4-17

security. See also security.

shutting down, 4-11 to 4-14

specifying control files, 2-36

starting up, 4-3 to 4-9

structure of

distributed database, 1-6

suspending, 4-17

templates (DBCA), 2-9

test, 23-10

troubleshooting creation problems, 2-31

tuning

archiving large databases, 8-19

responsibilities for, 1-8

undo management, 2-24

upgrading, 2-5

user responsibilities, 1-4

viewing datafiles and redo log files, 2-31

with locally managed tablespaces, 2-26

datafiles

adding to a tablespace, 12-5

bringing online and offline, 12-8

checking associated tablespaces, 11-51

creating, 12-5

creating as Oracle-managed files, 3-14

database administrators access, 1-10

default directory, 12-5

definition, 12-2

deleting, 11-29

dropping, 12-9, 12-14

dropping Oracle-managed files, 3-21

file numbers, 12-2

fully specifying filenames, 12-5

guidelines for managing, 12-2 to 12-4

identifying filenames, 12-12

location, 12-4

mapping files to physical devices, 12-15 to 12-27

minimum number of, 12-2

MISSING, 6-9

monitoring using views, 12-28

online, 12-9

relocating, 12-10, 12-13

relocating, example, 12-12

renaming, 12-10, 12-13

renaming for single tables, 12-11

reusing, 12-5

size of, 12-4

statements to create, 12-5

storing separately from redo log files, 12-4

taking offline, 11-23

unavailable when database is opened, 4-6

V$DBFILE and V$LOGFILE views, 2-31

verifying data blocks, 12-14

DB_BLOCK_CHECKING initialization parameter, 22-4, 22-5

DB_BLOCK_CHECKSUM initialization parameter, 12-14

enabling redo block checking with, 7-18

DB_BLOCK_SIZE initialization parameter

setting, 2-37

DB_CACHE_SIZE initialization parameter

setting, 2-40

DB_CREATE_FILE_DEST initialization parameter

described, 3-5

DB_CREATE_ONLINE_LOG_DEST_n initialization parameter

described, 3-5

DB_DOMAIN initialization parameter

setting before database creation, 2-35, 2-36

DB_FILES initialization parameter, 12-3

DB_NAME initialization parameter

setting before database creation, 2-35

DB_nK_CACHE_SIZE initialization parameter

using with transportable tablespaces, 11-41

DB_nK_CACHE_SIZE initialization parameters

setting, 2-40

DB_VERIFY utility, 22-4, 22-5

DBA role, 1-13, 25-5

DBA. See database administrators.

DBA_2PC_NEIGHBORS view, 32-5

using to trace session tree, 32-6

DBA_2PC_PENDING view, 32-3, 32-13, 32-22

using to list in-doubt transactions, 32-3

DBA_DATA_FILES view, 11-50

DBA_DB_LINKS view, 29-21, 32-3, 32-5

DBA_JOBS view

jobs in system, viewing, 10-15

DBA_JOBS_RUNNING

running jobs, viewing, 10-15

DBA_RESUMABLE view, 14-21

DBA_ROLLBACK_SEGS view, 13-25, 13-26

DBA_SEGMENTS view, 11-50

DBA_TEMP_FILES view, 11-50

DBA_TS_QUOTAS view, 11-50

DBA_UNDO_EXTENTS view

undo tablespace extents, 13-12

DBA_USERS view, 11-50

DBCA. See Database Configuration Assistant

DBMS_FLASHBACK package

setting undo retention period for, 13-10

DBMS_JOB package, 10-3

DBMS_LOGMNR_D.BUILD procedure, 9-6

DBMS_METADATA package

GET_DDL function, 21-31

using for object definition, 21-31

DBMS_REDEFINITION package

redefining tables online, 15-17

DBMS_REPAIR package, 22-2 to 22-15

CHECK_OBJECT procedure, 22-2

DUMP_ORPHAN_KEYS procedure, 22-3

examples, 22-8 to 22-15

limitations, 22-3

procedures, 22-2

SEGMENT_FIX_STATUS procedure, 22-3

SKIP_CORRUPT_BLOCKS procedure, 22-3

using, 22-3 to 22-8

DBMS_REPAIR procedure

FIX_CORRUPT_BLOCKS procedure, 22-2

REBUILD_FREELISTS procedure, 22-3

DBMS_RESOURCE_MANAGER package, 27-4, 27-9, 27-20, 27-21

procedures (table of), 27-8

DBMS_RESOURCE_MANAGER_PRIVS package, 27-9, 27-20

procedures (table of), 27-9

DBMS_RESUMABLE package, 14-22

DBMS_SESSION package, 27-23

DBMS_SPACE package, 14-25

example for unused space, 21-32

FREE_BLOCK procedure, 21-31

SPACE_USAGE procedure, 21-31

UNUSED_SPACE procedure, 21-31

DBMS_SPACE_ADMIN package, 11-30 to 11-33

DBMS_STATS package, 21-4

MONITORING clause of CREATE TABLE, 15-9

DBMS_STORAGE_MAP package

invoking for file mapping, 12-22

DBMS_STORAGE_MAPPING package, 12-22, 12-23

DBMS_TRANSACTION package

PURGE_LOST_DB_ENTRY procedure, 32-13

DBMS_UTILITY package

ANALYZE_SCHEMA procedure

used for computing statistics, 21-6

DEALLOCATE UNUSED clause, 14-26

deallocating unused space, 14-25

DBMS_SPACE package, 14-25

DEALLOCATE UNUSED clause, 14-26

examples, 14-26

high water mark, 14-25

declarative referential integrity constraints, 30-3

dedicated server processes, 5-2

trace files for, 5-15

default

audit options, 26-13

disabling, 26-15

DEFAULT keyword

list partitioning, 17-13

default partitions, 17-7

default roles, 25-21

default subpartition, 17-10

DEFAULT_CONSUMER_GROUP for Database Resource Manager, 27-16, 27-17, 27-21, 27-23

defaults

profile, 24-20

role, 24-7

tablespace quota, 24-4

user tablespaces, 24-3

DELETE_CATALOG_ROLE role, 25-6

DELETE_CATALOG_ROLE roll, 25-4

dependencies

displaying, 21-34

destinations

archived redo logs

optional, 8-16

sample scenarios, 8-17

developers, application, 23-10

dictionary protection mechanism, 25-2

dictionary-managed tablespaces, 11-10 to 11-12

migrating SYSTEM to locally managed, 11-34

Digital's POLYCENTER Manager on NetView, 28-32

directory service

See also enterprise directory service.

DISABLE ROW MOVEMENT clause, 17-10

disabling audit options, 26-13, 26-14

disabling auditing, 26-8

disabling recoverer process

distributed transactions, 32-25

disabling resource limits, 24-19

disconnections

auditing, 26-11

dispatcher processes, 5-6, 5-10, 5-13

DISPATCHERS initialization parameter

setting initially, 5-6

distributed applications

distributing data, 30-2

distributed databases

administration

overview, 28-23

application development

analyzing execution plan, 30-9

controlling connections, 30-2

handling errors, 30-3

handling RPC errors, 30-11

managing distribution of data, 30-2

managing referential integrity, 30-3

tuning distributed queries, 30-3

tuning using collocated inline views, 30-4

using cost-based optimization, 30-5

using hints to tune queries, 30-8

client/server architectures, 28-6

commit point strength, 31-8

cost-based optimization, 28-47

distributed processing, 28-3

distributed queries, 28-34

distributed updates, 28-34

distributing an application's data, 30-2

global database names

how they are formed, 29-2

global object names, 28-22, 29-2

global users

schema-dependent, 28-27

schema-independent, 28-28

Globalization Support, 28-47

location transparency, 28-44

creating, 29-26

creating using procedures, 29-30

creating using synonyms, 29-28

creating using views, 29-26

restrictions, 29-33

management tools, 28-31

managing read consistency, 32-25

nodes of, 28-6

overview, 28-2

referential integrity

application development, 30-3

remote object security, 29-28

remote queries and updates, 28-33

replicated databases and, 28-4

resumable space allocation, 14-18

running in ARCHIVELOG mode, 8-4

running in NOARCHIVELOG mode, 8-4

scenarios, 29-34

security, 28-24

site autonomy, 28-24

SQL transparency, 28-45

starting a remote instance, 4-9

transaction processing, 28-33

transparency, 28-44

queries, 29-32

updates, 29-32

distributed processing

distributed databases, 28-3

distributed queries, 28-34

analyzing tables, 30-7

application development issues, 30-3

cost-based optimization, 30-5

optimizing, 28-47

distributed systems

data encryption, 28-30

distributed transactions, 28-35

case study, 31-19

commit point site, 31-7

commit point strength, 31-8

committing, 31-8

database server role, 31-5

defined, 31-2

DML and DDL, 31-3

failure during, 32-23

global coordinator, 31-6

local coordinator, 31-6

lock timeout interval, 32-23

locked resources, 32-23

locks for in-doubt, 32-24

management, 31-1, 32-1

manually overriding in-doubt, 32-9

naming, 32-2, 32-10

recovery in single-process systems, 32-25

session trees, 31-4

clients, 31-5

commit point site, 31-6, 31-8

database servers, 31-5

global coordinators, 31-6

local coordinators, 31-6

setting advice, 32-10

specifying

commit point strength, 32-2

tracing session tree, 32-5

transaction control statements, 31-4

transaction timeouts, 32-23

two-phase commit, 31-10

discovering problems, 32-8

example, 31-19

viewing information about, 32-3

distributed updates, 28-34

distributing I/O, 2-2

DML. See data manipulation language

DRIVING_SITE hint, 30-9

DROP CLUSTER statement

CASCADE CONSTRAINTS option, 18-10

dropping cluster, 18-10

dropping cluster index, 18-10

dropping hash cluster, 19-9

INCLUDING TABLES option, 18-10

DROP LOGFILE MEMBER option

ALTER DATABASE statement, 7-17

DROP LOGFILE option

ALTER DATABASE statement, 7-16

DROP PARTITION clause, 17-32

DROP PROFILE statement, 24-23

DROP ROLE statement, 25-10, 25-11

DROP ROLLBACK SEGMENT statement, 13-25

DROP SYNONYM statement, 20-15

DROP TABLE statement

about, 15-23

CASCADE CONSTRAINTS option, 15-23

for clustered tables, 18-11

DROP TABLESPACE statement, 11-30

DROP USER privilege, 24-8

DROP USER statement, 24-8

dropping an audit trail, 26-19

dropping columns from tables, 15-14

marking unused, 15-15

remove unused columns, 15-15

dropping database links, 29-19

dropping datafiles

Oracle managed, 3-21

dropping profiles, 24-23

dropping tables

CASCADE clause, 15-23

consequences of, 15-23

privileges, 15-23

dropping tempfiles

Oracle managed, 3-21

dropping users, 24-7

DUMP_ORPHAN_KEYS procedure, 22-3, 22-6, 22-7

example, 22-13

E

EMPHASIS resource allocation method, 27-14

ENABLE ROW MOVEMENT clause, 17-10, 17-11

enabling recoverer process

distributed transactions, 32-25

enabling resource limits, 24-19

encryption

database passwords, 23-5, 24-9

enterprise directory service, 23-8, 25-10

enterprise roles, 23-8, 24-14, 25-10

enterprise users, 23-8, 24-14, 25-10

definition, 28-28

errors

alert log and, 5-15

messages

trapping, 30-11

ORA-00028, 5-22

ORA-00900, 30-11

ORA-01090, 4-11

ORA-01173, 6-10

ORA-01176, 6-10

ORA-01177, 6-10

ORA-01578, 12-14

ORA-01591, 32-24

ORA-02015, 30-11

ORA-02049, 32-23

ORA-02050, 32-8

ORA-02051, 32-8

ORA-02054, 32-8

ORA-02055

integrity constrain violation, 30-3

ORA-02067

rollback required, 30-3

ORA-06510

PL/SQL error, 30-12

ORA-1215, 6-10

ORA-1216, 6-10

ORA-1547, 21-30

ORA-1628 through 1630, 21-30

remote procedures, 30-11

snapshot too old, 13-9, 13-18

trace files and, 5-15

when creating a database, 2-31

when creating control file, 6-10

while starting a database, 4-8

while starting an instance, 4-8

exception handler, 30-11

local, 30-12

EXCEPTION keyword, 30-11

exceptions

assigning names

PRAGMA_EXCEPTION_INIT, 30-12

integrity constraints, 21-21

user-defined

PL/SQL, 30-11

EXCHANGE PARTITION clause, 17-37

EXCHANGE SUBPARTITION clause, 17-37

EXECUTE_CATALOG_ROLE role, 25-6

EXECUTE_CATALOG_ROLE roll, 25-3

executing jobs

enabling processes for, 10-2

execution plans

analyzing for distributed queries, 30-9

EXP_FULL_DATABASE role, 25-5

Export utility

about, 1-26

restricted mode and, 4-7

exporting jobs, 10-6

EXTENT MANAGEMENT LOCAL clause

CREATE DATABASE, 2-26

extents

allocating

clusters, 18-9

allocating for tables, 15-12

data dictionary views for, 21-33

deallocating

clusters, 18-9

displaying free extents, 21-36

displaying information on, 21-35

external authentication

by network, 24-13

by operating system, 24-12

external procedures

managing processes for, 5-20

external tables

altering, 15-38

creating, 15-35

defined, 15-33

dropping, 15-39

object privileges, 15-39

object privileges for directory, 15-39

system privileges, 15-39

uploading data example, 15-35

F

failures

media

multiplexed online redo logs, 7-5

features, new, xlviii to lxi

file mapping

examples, 12-25

how it works, 12-16

how to use, 12-21

overview, 12-16

structures, 12-18

views, 12-23

file system

used for Oracle-managed files, 3-3

FILE_MAPPING initialization parameter, 12-22

filenames

Oracle-managed files, 3-7

files

Oracle-managed, 3-1 to 3-28

fine-grained access control, 23-4

fine-grained auditing, 26-18

FIX_CORRUPT_BLOCKS procedure, 22-2, 22-7

example, 22-12

fixed user database links

07_DICTIONARY_ACCESSIBILITY initialization parameter, 28-18

advantages and disadvantages, 28-17

creating, 29-11

definition, 28-16

example, 28-20

flashback query

setting retention period for, 13-10

FMON background process, 12-17

FMPUTL external process

used for file mapping, 12-18

FOR PARTITION clause, 17-44

FORCE clause

COMMIT statement, 32-10

ROLLBACK statement, 32-10

FORCE LOGGING clause

CREATE CONTROLFILE, 2-30

CREATE DATABASE, 2-29

CREATE TABLESPACE, 11-20

performance considerations, 2-30

forcing

COMMIT or ROLLBACK, 32-4, 32-9

forcing a log switch, 7-18

using ARCHIVE_LAG_TIME, 7-10

with the ALTER SYSTEM statement, 7-18

forget phase

two-phase commit, 31-15

free space

coalescing, 11-16

listing free extents, 21-36

tablespaces and, 11-51

FREELIST GROUPS storage parameter

description, 14-11

FREELISTS GROUPS parameter, 11-8, 11-9

FREELISTS parameter, 11-8, 11-9

FREELISTS storage parameter

description, 14-11

function-based indexes, 16-14 to 16-18

functions

recompiling, 21-25

G

generic connectivity

definition, 28-6

global authentication and authorization, 24-13

global cache service, 5-13

global coordinators, 31-6

distributed transactions, 31-6

global database consistency

distributed databases and, 31-14

global database links, 28-15

creating, 29-11

global database name, 2-35

global database names

changing the domain, 29-4

database links, 28-12

distributed databases

how they are formed, 29-2

enforcing for database links, 28-14

enforcing global naming, 29-3

impact of changing, 28-42

querying, 29-4

global object names

database links, 28-36

distributed databases, 29-2

global roles, 24-13, 25-10

global users, 24-13, 29-37

distributed systems

schema-dependent, 28-27

schema-independent, 28-28

GLOBAL_NAME view

using to determine global database name, 29-4

GLOBAL_NAMES initialization parameter, 28-14

Globalization Support

client/server architectures, 28-48

distributed databases

clients and servers may diverge, 28-47

heterogeneous systems, 28-49

homogeneous systems, 28-48

GRANT ANY OBJECT PRIVILEGE system privilege, 25-14, 25-17

GRANT CONNECT THROUGH clause

for proxy authorization, 24-16

GRANT statement, 25-11

ADMIN OPTION, 25-12

creating a new user, 25-12

object privileges, 25-12

SYSOPER/SYSDBA privileges, 1-24

system privileges and roles, 25-11

when takes effect, 25-20

WITH GRANT OPTION, 25-13

granting privileges and roles

listing grants, 25-26

SYSOPER/SYSDBA privileges, 1-24

GV$DBLINK view, 29-25

H

hardware

evaluating, 1-5

hash clusters

advantages and disadvantages, 19-2 to 19-3

altering, 19-9

choosing key, 19-5

contrasted with index clusters, 19-2

controlling space use of, 19-5

creating, 19-4

dropping, 19-9

estimating storage, 19-8

examples, 19-7

hash function, 19-2, 19-3, 19-4, 19-6

HASH IS option, 19-4, 19-6

HASHKEYS option, 19-4, 19-7

single-table, 19-5

SIZE option, 19-6

hash functions

for hash cluster, 19-2

hash partitioning

creating tables using, 17-12

index-organized tables, 17-21

when to use, 17-5

heterogeneous distributed systems

definition, 28-5

Heterogeneous Services

overview, 28-5

high water mark, 14-25

hints, 30-8

DRIVING_SITE, 30-9

NO_MERGE, 30-8

using to tune distributed queries, 30-8

historical tables

moving time window, 17-61

HP's OpenView, 28-32

HS_ADMIN_ROLE role, 25-6

I

IBM's NetView/6000, 28-32

IMP_FULL_DATABASE role, 25-5

implementing database design, 1-7

Import utility

about, 1-26

restricted mode and, 4-7

importing jobs, 10-6

index clusters. See clusters.

indexes

altering, 16-19 to 16-21

analyzing, 21-3 to 21-6

choosing columns to index, 16-4

cluster indexes, 18-8, 18-9, 18-10

coalescing, 16-8, 16-21

column order for performance, 16-5

creating, 16-9 to 16-19

disabling and dropping constraints cost, 16-9

dropping, 16-5, 16-22

estimating size, 16-6

explicitly creating a unique index, 16-11

function-based, 16-14 to 16-18

guidelines for managing, 16-2 to 16-9

keeping when disabling constraint, 21-18

keeping when dropping constraint, 21-18

key compression, 16-18

limiting per table, 16-5

monitoring space use of, 16-21

monitoring usage, 16-21

parallelizing index creation, 16-7

partitioned, 17-2

see also partitioned indexes

PCTFREE for, 16-5

PCTUSED for, 16-5

privileges

for altering, 16-19

for dropping, 16-22

rebuilding, 16-8, 16-20

rebuilding online, 16-20

separating from a table, 15-6

setting storage parameters for, 16-6

space used by, 16-21

specifying PCTFREE for, 14-4

statement for creating, 16-10

tablespace for, 16-6

temporary segments and, 16-3

updating global indexes, 17-26

validating structure, 21-6

when to create, 16-4

index-organized tables

analyzing, 15-32

AS subquery, 15-27

converting to heap, 15-33

creating, 15-25

described, 15-24

hash partitioning, 17-21

INCLUDING clause, 15-28

key compression, 15-29

maintaining, 15-30

ORDER BY clause, using, 15-33

overflow clause, 15-27

partitioning, 17-10, 17-19 to 17-21

partitioning secondary indexes, 17-20

range partitioning, 17-20

rebuilding with MOVE clause, 15-30

threshold value, 15-28

updating key column, 15-31

in-doubt transaction

manually committing, example, 32-15

in-doubt transactions, 31-14

after a system failure, 32-8

automatic resolution, 31-16

failure during commit phase, 31-17

failure during prepare phase, 31-16

deciding how to handle, 32-7

deciding whether to perform manual override, 32-9

manually committing, 32-11

manually overriding, 31-18, 32-10

scenario, 32-15

manually rolling back, 32-12

overriding manually, 32-9

overview, 31-15

pending transactions table, 32-22

purging rows from data dictionary, 32-13

deciding when necessary, 32-14

recoverer process, 32-24

rollback segments, 32-9

rolling back, 32-11, 32-12, 32-13

SCNs and, 31-19

simulating, 32-24

tracing session tree, 32-5

viewing information about, 32-3

INITIAL storage parameter

cannot alter, 14-13, 15-12

description, 14-10

rollback segments, 13-18, 13-20

when deallocating unused space, 14-26

initialization parameter file

creating, 2-15

creating for database creation, 2-15

editing before database creation, 2-35

individual parameter names, 2-35

server parameter file, 2-44 to 2-51, 4-4

initialization parameters

ARCHIVE_LAG_TARGET, 7-11

buffer cache, 2-40

DB_BLOCK_CHECKSUM, 7-18

DB_CREATE_FILE_DEST, 3-5

DB_CREATE_ONLINE_LOG_DEST_n, 3-5

FILE_MAPPING, 12-22

LOG_ARCHIVE_DEST_n, 8-10

LOG_ARCHIVE_DEST_STATE_n, 8-14

LOG_ARCHIVE_MAX_PROCESSES, 8-7, 8-19

LOG_ARCHIVE_MIN_SUCCEED_DEST, 8-16

LOG_ARCHIVE_START, 8-7, 8-8, 8-13

LOG_ARCHIVE_TRACE, 8-21

MAX_ROLLBACK_SEGMENTS, 13-15

RESOURCE_MANAGER_PLAN, 27-24

ROLLBACK_SEGMENTS, 13-15

shared server and, 5-5

SPFILE, 2-47

TRANSACTIONS, 13-15

TRANSACTIONS_PER_ROLLBACK_SEGMENT, 13-15

UNDO_MANAGEMENT, 2-24, 13-3

UNDO_RETENTION, 13-9

UNDO_SUPPRESS_ERROR, 13-4

UNDO_TABLESPACE, 13-3

INITRANS storage parameter

altering, 15-11

guidelines for setting, 14-8

INSERT privilege

granting, 25-15

revoking, 25-18

installation

Oracle9i, 1-5

instances

aborting, 4-13

shutting down immediately, 4-12

shutting down normally, 4-12

starting up, 4-2 to 4-9

transactional shutdown, 4-13

integrity constraints

See also constraints

cost of disabling, 16-9

cost of dropping, 16-9

creating indexes associated with, 16-11

dropping tablespaces and, 11-30

ORA-02055

constraint violation, 30-3

INTERNAL

security for, 23-8

INTERNAL username

connecting for shutdown, 4-11

I/O

distributing, 2-2

IOT. See index-organized tables.

J

Jnnn processes

managing job queues, 10-3 to 10-14

job queues

altering jobs, 10-11

broken jobs, 10-12

CJQ background process, 10-2

DBMS_JOB package, 10-3

executing jobs in, 10-9

Jnnn processes, 10-2

locks, 10-9

removing jobs from, 10-10

submitting jobs to, 10-4 to 10-9

terminating jobs, 10-14

viewing information, 10-15

JOB_QUEUE_PROCESSES initialization parameter, 10-2

jobs

altering, 10-11

broken, 10-12

database links and, 10-9

environment, recording when submitted, 10-6

executing, 10-9

exporting, 10-6

forcing to execute, 10-14

importing, 10-6

job definition, 10-7

job execution interval, 10-8

job number, 10-7

ownership of, 10-7

removing from job queue, 10-10

running broken jobs, 10-13

submitting to job queue, 10-4

terminating, 10-14

trace files for job failures, 10-10

troubleshooting, 10-10

join views

definition, 20-3

DELETE statements, 20-8

key-preserved tables in, 20-6

modifying, 20-5

rule for, 20-7

updating, 20-5

joins

distributed databases

managing statement transparency, 29-33

JQ locks, 10-9

K

key compression, 15-29

indexes, 16-18

key-preserved tables

in join views, 20-6

keys

cluster, 18-2, 18-4, 18-5

L

LIST CHAINED ROWS clause

of ANALYZE statement, 21-7

list partitioning

adding values to value list, 17-45

creating tables using, 17-13

DEFAULT keyword, 17-13

dropping values from value-list, 17-46

when to use, 17-5

listing database links, 29-21, 32-3, 32-5

loading data

using external tables, 15-35

LOBs

storage parameters for, 14-12

local coordinators, 31-6

distributed transactions, 31-6

locally managed tablespaces, 11-5 to 11-10

automatic segment space management, 11-8

DBMS_SPACE_ADMIN package, 11-30

detecting and repairing defects, 11-30

migrating SYSTEM from dictionary-managed, 11-34

tempfiles, 11-13

temporary, creating, 11-13

location transparency

distributed databases

creating using procedures, 29-30

creating using synonyms, 29-28

creating using views, 29-26

using procedures, 29-30, 29-31, 29-32

lock timeout interval

distributed transactions, 32-23

locks

in-doubt distributed transactions, 32-23, 32-24

job queue, 10-9

monitoring, 5-15

log sequence number

control files, 7-5

log switches

description, 7-5

forcing, 7-18

log sequence numbers, 7-5

multiplexed redo log files and, 7-7

privileges, 7-18

using ARCHIVE_LAG_TIME, 7-10

waiting for archiving to complete, 7-7

log writer process (LGWR), 5-12

multiplexed redo log files and, 7-6

online redo logs available for use, 7-3

trace file monitoring, 5-16

trace files and, 7-6

writing to online redo log files, 7-3

LOG_ARCHIVE_DEST initialization parameter

specifying destinations using, 8-10

LOG_ARCHIVE_DEST_n initialization parameter, 8-10

REOPEN option, 8-18

LOG_ARCHIVE_DEST_STATE_n initialization parameter, 8-14

LOG_ARCHIVE_DUPLEX_DEST initialization parameter

specifying destinations using, 8-10

LOG_ARCHIVE_MAX_PROCESSES initialization parameter, 8-7, 8-19

LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter, 8-16

LOG_ARCHIVE_START initialization parameter, 8-7, 8-13

setting, 8-8

LOG_ARCHIVE_TRACE initialization parameter, 8-21

LOGGING clause

CREATE TABLESPACE, 11-20

logical structure of a database, 1-5

logical volume manager

used for Oracle-managed files, 3-2

logical volume managers

mapping files to physical devices, 12-15 to 12-27

LogMiner

continuous mining, 9-25

formatting returned data, 9-17

LogMiner utility

analyzing output, 9-16

dbmslmd.sql script, 9-6

dictionary options, 9-5

ending a session, 9-28

executing reconstructed SQL, 9-17

extracting a dictionary file, 9-6

extracting data values from redo logs, 9-18

graphical user interface, 9-1

re-creating LogMiner tables in alternate tablespace, 9-11

redo log files, 9-4

specifying redo logs for analysis, 9-24

starting, 9-26

steps in a typical session, 9-23

supplemental logging, 9-19

identification keys, 9-20

log groups, 9-22

suppressing delimiters in SQL_REDO and SQL_UNDO, 9-17

tracking DDL statements, 9-9

using the online catalog, 9-8

using to analyze redo log files, 9-1

V$LOGMNR_CONTENTS view, 9-16

views, 9-15

LogMiner Viewer, 9-1

LOGON trigger

setting resumable mode, 14-20

LONG columns, 29-33

LONG RAW columns, 29-33

LOW_GROUP for Database Resource Manager, 27-17, 27-29

M

managing datafiles, 12-1 to 12-29

managing job queues, 10-3 to 10-14

managing roles, 25-6

managing sequences, 20-11 to 20-13

managing synonyms, 20-13 to 20-15

managing tables, 15-1 to 15-41

managing views, 20-2 to 20-11

manual archiving

in ARCHIVELOG mode, 8-9

manual overrides

in-doubt transactions, 32-10

MAX_DUMP_FILE_SIZE initialization parameter, 5-16

MAX_ENABLED_ROLES initialization parameter

enabling roles and, 25-22

MAX_ROLLBACK_SEGMENTS initialization parameter, 13-15

MAXDATAFILES parameter

changing, 6-6

MAXEXTENTS storage parameter

description, 14-10

rollback segments, 13-17, 13-20

setting for the data dictionary, 21-28

MAXINSTANCES parameter

changing, 6-6

MAXLOGFILES option

CREATE DATABASE statement, 7-10

MAXLOGFILES parameter

changing, 6-6

MAXLOGHISTORY parameter

changing, 6-6

MAXLOGMEMBERS parameter

changing, 6-6

CREATE DATABASE statement, 7-10

MAXTRANS storage parameter

altering, 15-11

guidelines for setting, 14-8

media recovery

effects of archiving on, 8-3

memory

viewing per user, 24-26

MERGE PARTITIONS clause, 17-38

messages

error

trapping, 30-11

migrated rows

eliminating from table, procedure, 21-8

MINEXTENTS storage parameter

cannot alter, 14-13, 15-12

deallocating unused space, 14-26

description, 14-10

rollback segments, 13-18, 13-20

mirrored control files, 6-3

mirrored files

online redo log, 7-6

location, 7-9

size, 7-9

mirroring

control files, 2-36

MISSING datafiles, 6-9

MODIFY DEFAULT ATTRIBUTES clause, 17-44

using for partitioned tables, 17-43

MODIFY DEFAULT ATTRIBUTES FOR PARTITION clause

of ALTER TABLE, 17-43

MODIFY PARTITION clause, 17-44, 17-48, 17-51

MODIFY SUBPARTITION clause, 17-45

MONITORING clause

CREATE TABLE, 15-9

monitoring datafiles, 12-28

MONITORING USAGE clause

of ALTER INDEX statement, 16-21

MOUNT option

STARTUP command, 4-7

mounting a database, 4-6

MOVE PARTITION clause, 17-44, 17-48

MOVE SUBPARTITION clause, 17-44, 17-49

moving control files, 6-5

multiplexed control files

importance of, 6-3

multiplexing

archived redo logs, 8-10

control files, 6-3

redo log files, 7-5

groups, 7-6

multi-tier environments

auditing clients, 26-13

N

name resolution

distributed databases, 28-22

impact of global name changes, 28-42

schema objects, 28-38

when global database name is complete, 28-37

when global database name is partial, 28-37

when no global database name is specified, 28-37

named user limits

setting initially, 2-43

nested tables

storage parameters for, 14-12

network

authentication, 24-13

network authentication, 24-13

network connections

minimizing, 29-14

networks

distributed databases use of, 28-2

new features, xlviii to lxi

NEXT storage parameter

altering, 14-13, 15-12

description, 14-10

rollback segments, 13-18, 13-20

setting for the data dictionary, 21-28

NO_DATA_FOUND keyword, 30-11

NO_MERGE hint, 30-8

NOARCHIVELOG MODE

dropping datafiles, 12-9

NOARCHIVELOG mode

archiving, 8-2

definition, 8-2

media failure, 8-3

no hot backups, 8-3

running in, 8-2

switching to, 8-5

taking datafiles offline in, 12-9

NOAUDIT statement

disabling audit options, 26-13

disabling default object audit options, 26-15

disabling object auditing, 26-14

disabling statement and privilege auditing, 26-14

NOLOGGING CLAUSE

CREATE TABLESPACE, 11-20

NOMOUNT option

STARTUP command, 4-6

normal transmission mode

definition, 8-14

Novell's NetWare Management System, 28-32

O

O7_DICTIONARY_ACCESSIBILITY initialization parameter, 25-3

object privileges

for external tables, 15-39

granting on behalf of the owner, 25-14

revoking, 25-16

revoking on behalf of owner, 25-17

objects

See also schema objects

referencing with synonyms, 29-28

See also schema objects

offline tablespaces

priorities, 11-21

rollback segments and, 13-22

taking offline, 11-21

online redefinition of tables

abort and cleanup, 15-20

example, 15-20

features of, 15-16

intermediate synchronization, 15-19

restrictions, 15-22

steps, 15-17

online redo log, 7-2

See also redo logs

creating

groups and members, 7-12

creating members, 7-13

dropping groups, 7-16

dropping members, 7-16

forcing a log switch, 7-18

guidelines for configuring, 7-5

INVALID members, 7-17

location of, 7-9

managing, 7-1

moving files, 7-14

number of files in the, 7-10

optimum configuration for the, 7-10

privileges

adding groups, 7-12

dropping groups, 7-16

dropping members, 7-17

forcing a log switch, 7-18

renaming files, 7-14

renaming members, 7-14

specifying ARCHIVE_LAG_TIME, 7-10

STALE members, 7-17

viewing information about, 7-20

online redo log files

creating as Oracle-managed files, 3-19

OPEN_LINKS initialization parameter, 29-20

opening a database

after creation, 1-6

operating systems

accounts, 25-24

authentication, 24-12, 25-22

database administrators requirements for, 1-10

enabling and disabling roles, 25-25

renaming and relocating files, 12-10

role identification, 25-23

roles and, 25-22

security in, 23-3

Optimal Flexible Architecture (OFA), 2-7

OPTIMAL storage parameter

description, 14-11

rollback segments, 13-17, 13-18, 13-20

optional destinations

for archived redo logs

destinations

archived redo logsmandatory, 8-16

ORA_TZFILE environment variable

specifying time zone file for database, 2-29

ORA-00900 error, 30-11

ORA-02015 error, 30-11

ORA-02055 error

integrity constraint violation, 30-3

ORA-02067 error

rollback required, 30-3

ORA-06510 error

PL/SQL error, 30-12

Oracle

installing, 1-5

release numbers, 1-8

Oracle Call Interface. See OCI

Oracle Enterprise Manager, 4-2

Oracle Managed Files feature

See also Oracle-managed files

Oracle Net

service names in, 8-15

transmitting archived logs via, 8-15

Oracle Universal Installer, 2-5

Oracle9i Real Application Clusters

allocating extents for cluster, 18-9

sequence numbers and, 20-13

threads of online redo log, 7-2

Oracle-managed files

behavior, 3-21

benefits, 3-3

CREATE DATABASE statement, 3-8

creating, 3-6 to 3-21

creating control files, 3-17

creating datafiles, 3-14

creating online redo log files, 3-19

creating tempfiles, 3-16

described, 3-2

dropping datafile, 3-21

dropping online redo log files, 3-22

dropping tempfile, 3-21

initialization parameters, 3-4

introduction, 2-25

naming, 3-7

renaming, 3-22

scenarios for using, 3-22 to 3-28

ORAPWD utility, 1-20

ORGANIZATION EXTERNAL clause

of CREATE TABLE, 15-35

OS authentication, 1-17

OS_ROLES parameter

operating-system authorization and, 25-9

REMOTE_OS_ROLES and, 25-25

using, 25-23

OSDBA group, 1-18

OSOPER group, 1-18

OTHER_GROUPS for Database Resource Manager, 27-6, 27-13, 27-16, 27-19, 27-28

P

packages

DBMS_JOB, 10-3

DBMS_METADATA, 21-31

DBMS_REDEFINITION, 15-17

DBMS_REPAIR, 22-2 to 22-15

DBMS_RESOURCE_MANAGER, 27-4, 27-8, 27-9, 27-20, 27-21

DBMS_RESOURCE_MANAGER_PRIVS, 27-9, 27-20

DBMS_RESUMABLE, 14-22

DBMS_SESSION, 27-23

DBMS_SPACE, 14-25, 21-31

DBMS_STATS, 15-9, 21-4

DBMS_STORAGE_MAPPING, 12-22, 12-23

DBMS_UTILITY

used for computing statistics, 21-6

privileges for recompiling, 21-25

recompiling, 21-25

parallel execution

managing, 5-18

parallel hints, 5-18

parallelizing index creation, 16-7

resumable space allocation, 14-18

parallel hints, 5-18

PARALLEL_DEGREE_LIMIT_ABSOLUTE resource allocation method, 27-15

parallelizing table creation, 15-4, 15-8

parameter files

See also initialization parameter file.

PARTITION BY HASH clause, 17-12

PARTITION BY LIST clause, 17-13

PARTITION BY RANGE clause, 17-11

for composite-partitioned tables, 17-14, 17-15

PARTITION clause

for composite-partitioned tables, 17-14, 17-15

for hash partitions, 17-12

for list partitions, 17-13

for range partitions, 17-11

partition views

converting to partitioned table, 17-62

partitioned indexes, 17-1 to 17-65

adding partitions, 17-31

creating local index on composite partitioned table, 17-15

creating local index on hash partitioned table, 17-13

creating range partitions, 17-12

description, 17-2

dropping partitions, 17-35

global, 17-3

local, 17-3

maintenance operations, 17-22 to 17-61

table of, 17-24

modifying partition default attributes, 17-43

modifying real attributes of partitions, 17-45

moving partitions, 17-49

rebuilding index partitions, 17-50

renaming index partitions/subpartitions, 17-52

secondary indexes on index-organized tables, 17-20

splitting partitions, 17-57

partitioned tables, 17-1 to 17-65

adding partitions, 17-27

adding subpartitions, 17-29, 17-30

coalescing partitions, 17-31

converting partition views, 17-62

creating composite partitions and subpartitions, 17-14

creating hash partitions, 17-12

creating list partitions, 17-13

creating range partitions, 17-11, 17-12

description, 17-2

DISABLE ROW MOVEMENT, 17-10

dropping partitions, 17-32

ENABLE ROW MOVEMENT, 17-10

exchanging partitions, 17-35

exchanging subpartitions, 17-37, 17-38

global indexes on, 17-3

index-organized tables, 17-10, 17-20, 17-21

local indexes on, 17-3

maintenance operations, 17-22 to 17-61

table of, 17-23

marking indexes UNUSABLE, 17-28, 17-30, 17-31, 17-33, 17-35, 17-36, 17-38, 17-44, 17-45, 17-48, 17-52, 17-59

merging partitions, 17-38

modifying default attributes, 17-43

modifying real attributes of partitions, 17-44

modifying real attributes of subpartitions, 17-45

moving partitions, 17-48

moving subpartitions, 17-49

rebuilding index partitions, 17-50

renaming partitions, 17-51

renaming subpartitions, 17-51

splitting partitions, 17-52

truncating partitions, 17-59

truncating subpartitions, 17-61

updating global indexes automatically, 17-26

partitioning

composite, 17-7

creating partitions, 17-10 to 17-21

default partition, 17-7

default subpartition, 17-10

hash, 17-5

indexes, 17-2

See also partitioned indexes

index-organized tables, 17-10, 17-20, 17-21

list, 17-5, 17-45, 17-46

maintaining partitions, 17-22 to 17-61

methods, 17-3

range, 17-4

range-list, 17-8, 17-15

subpartition templates, 17-17

tables, 17-2

See also partitioned tables

partitions

See also partitioned tables.

See also partitioned indexes.

PARTITIONS clause

for hash partitions, 17-12

password file authentication, 1-19

passwords

changing for roles, 25-7

default for SYS and SYSTEM, 1-11

encrypted

database, 23-5

encryption, 24-9

password file, 1-23

creating, 1-20

OS authentication, 1-16

removing, 1-25

state of, 1-25

privileges for changing for roles, 25-8

privileges to alter, 24-6

roles, 25-8

security policy for users, 23-5

setting REMOTE_LOGIN_PASSWORD parameter, 1-22

user authentication, 24-9

viewing for database links, 29-22

PCTFREE parameter

clustered tables, 14-4

clusters, used in, 18-5

guidelines for setting, 14-4

indexes, 14-4

non-clustered tables, 14-4

PCTUSED, use with, 14-7

usage, 14-2

PCTFREE storage parameter

altering, 15-11

table creation, 15-2

PCTINCREASE parameter

altering, 14-13

rollback segments, 13-18, 13-20

setting for the data dictionary, 21-28

PCTINCREASE storage parameter

altering, 15-12

description, 14-10

PCTUSED parameter, 11-8, 11-9

clusters, used in, 18-5

guidelines for setting, 14-6

PCTFREE, use with, 14-7

usage, 14-5

PCTUSED storage parameter

altering, 15-11

table creation, 15-2

pending area for Database Resource Manager plans, 27-12 to 27-14

validating plan schema changes, 27-12

pending transaction tables, 32-22

performance

index column order, 16-5

location of datafiles and, 12-4

tuning archiving, 8-19

physical structure of a database, 1-6

plan schemas for Database Resource Manager, 27-6, 27-12, 27-16, 27-25, 27-32

examples, 27-25

validating plan changes, 27-12

planning

database creation, 2-2

relational design, 1-6

the database, 1-5

PL/SQL

errors

ORA-06510, 30-12

program units

replaced views and, 20-11

user-defined exceptions, 30-11

PRAGMA_EXCEPTION_INIT procedure

assigning exception names, 30-12

predefined roles, 1-13

prepare phase, 31-11

recognizing read-only nodes, 31-12

two-phase commit, 31-11

prepare/commit phases

effects of failure, 32-23

failures during, 32-8

locked resources, 32-23

pending transaction table, 32-22

prepared response

two-phase commit, 31-12

prerequisites

for creating a database, 2-4

PRIMARY KEY constraints

associated indexes, 16-11

dropping associated indexes, 16-23

enabling on creation, 16-11

foreign key references when dropped, 21-19

indexes associated with, 16-11

private database links, 28-15

private rollback segments, 13-15, 13-19

taking offline, 13-24

private synonyms, 20-13

granting privileges and roles

specifying ALL, 25-4

revoking privileges and roles

specifying ALL, 25-4

privileges, 25-2

See also system privileges.

adding redo log groups, 7-12

altering

indexes, 16-19

passwords, 24-7

sequences, 20-12

users, 24-6

altering role authentication method, 25-8

altering tables, 15-10

application developers and, 23-10

audit object, 26-12

auditing system, 26-12

auditing use of, 26-11

cascading revokes, 25-19

closing a database link, 30-2

column, 25-15

creating

sequences, 20-12

synonyms, 20-14

views, 20-2

creating database links, 29-8

creating roles, 25-7

creating rollback segments, 13-19

creating tables, 15-6

creating tablespaces, 11-4

creating users, 24-2

database administrator, 1-10

disabling automatic archiving, 8-8

dropping

indexes, 16-22

online redo log members, 7-17

redo log groups, 7-16

sequences, 20-13

synonyms, 20-14

views, 20-10

dropping profiles, 24-23

dropping roles, 25-10

dropping rollback segments, 13-25

dropping tables, 15-23

enabling and disabling resource limits, 24-19

enabling and disabling triggers, 21-13

enabling automatic archiving, 8-6

for external tables, 15-39

forcing a log switch, 7-18

granting, 25-11

granting object privileges, 25-12

granting system privileges, 25-11

granting, about, 25-11

grouping with roles, 25-6

individual privilege names, 25-2

listing grants, 25-27

managing with procedures, 29-32

managing with synonyms, 29-30

managing with views, 29-28

manually archiving, 8-9

object, 25-4

on selected columns, 25-18

policies for managing, 23-6

recompiling packages, 21-25

recompiling procedures, 21-25

recompiling views, 21-25

renaming

objects, 21-3

redo log members, 7-14

replacing views, 20-10

RESTRICTED SESSION system privilege, 4-7

revoking, 25-16

revoking object, 25-16

revoking object privileges, 25-16, 25-19

revoking system privileges, 25-16

setting resource costs, 24-22

system, 25-2

taking tablespaces offline, 11-21

truncating, 21-11

See also system privileges.

procedures

external, 5-20

location transparency using, 29-30, 29-31, 29-32

recompiling, 21-25

remote calls, 28-46

process monitor, 5-12

processes

See also server processes

PROCESSES initialization parameter

setting before database creation, 2-41

PRODUCT_COMPONENT_VERSION view, 1-10

profiles, 24-18

altering, 24-21

assigning to users, 24-20

creating, 24-20

default, 24-20

disabling resource limits, 24-19

dropping, 24-23

enabling resource limits, 24-19

listing, 24-23

managing, 24-18

privileges for dropping, 24-23

privileges to alter, 24-21

privileges to set resource costs, 24-22

PUBLIC_DEFAULT, 24-20

setting a limit to null, 24-21

viewing, 24-25

program global area (PGA)

effect of MAX_ENABLED_ROLES on, 25-22

proxies

auditing clients of, 26-13

proxy authentication and authorization, 24-16

proxy authentication, 24-16

proxy authorization, 24-16

proxy servers

auditing clients, 26-13

PROXY_USERS view, 24-16

public database links

connected user, 29-35

fixed user, 29-34

public fixed user database links, 29-34

public rollback segments, 13-19

taking offline, 13-24

public synonyms, 20-13

PUBLIC user group

granting and revoking privileges to, 25-20

procedures and, 25-20

PUBLIC_DEFAULT profile

dropping profiles and, 24-23

using, 24-20

PURGE_LOST_DB_ENTRY procedure

DBMS_TRANSACTION package, 32-13

purging pending rows

from data dictionary, 32-13

when necessary, 32-14

Q

queries

distributed, 28-34

application development issues, 30-3

distributed or remote, 28-33

location transparency and, 28-45

post-processing, 30-4

remote, 30-4

transparency, 29-32

quiescing a database, 4-14

quotas

listing, 24-23

revoking from users, 24-4

setting to zero, 24-4

tablespace, 24-4

tablespace quotas, 11-3

temporary segments and, 24-4

unlimited, 24-4

viewing, 24-25

R

range partitioning

creating tables using, 17-11

index-organized tables, 17-20

when to use, 17-4

range-hash partitioning

subpartitioning template, 17-17

range-list partitioning, 17-8, 17-15

subpartitioning template, 17-19

read consistency

managing in distributed databases, 32-25

read-only database

opening, 4-10

read-only response

two-phase commit, 31-12

read-only tablespaces

datafiles, 12-8

read-only tablespaces, see tablespaces, read-only

REBUILD PARTITION clause, 17-49, 17-51

REBUILD SUBPARTITION clause, 17-51

REBUILD UNUSABLE LOCAL INDEXES clause, 17-51

REBUILD_FREELISTS procedure, 22-3, 22-6, 22-8

example, 22-13

rebuilding indexes, 16-20

costs, 16-8

online, 16-20

RECOVER option

STARTUP command, 4-8

recoverer process, 5-13

recoverer process (RECO)

disabling, 32-24, 32-25

distributed transaction recovery, 32-24

enabling, 32-24, 32-25

pending transaction table, 32-24

recovery

creating new control files, 6-6

Recovery Manager

starting a database, 4-2

starting an instance, 4-2

RECOVERY_CATALOG_OWNER role, 25-6

redefining tables

online, 15-16 to 15-22

redo log files

active (current), 7-4

analyzing, 9-1

archived

advantages of, 8-2

contents of, 8-2

log switches and, 7-5

archived redo log files, 8-5

archived redo logs, 8-2

available for use, 7-3

circular use of, 7-3

clearing, 7-7, 7-19

restrictions, 7-19

contents of, 7-2

creating

groups and members, 7-12

creating members, 7-13

distributed transaction information in, 7-3

groups, 7-6

creating, 7-12

dropping, 7-16

members, 7-6

threads, 7-2

how many in redo log, 7-10

inactive, 7-4

legal and illegal configurations, 7-7

LGWR and the, 7-3

log sequence numbers of, 7-5

log switches, 7-5

members, 7-6

creating, 7-12

dropping, 7-16

maximum number of, 7-10

mirrored

log switches and, 7-7

multiplexed

diagrammed, 7-6

if all inaccessible, 7-7

multiplexing, 7-5

groups, 7-6

if some members inaccessible, 7-7

online, 7-2

recovery use of, 7-2

requirement of two, 7-3

threads of, 7-2

online redo log, 7-1

planning the, 7-5, 7-10

privileges

adding groups and members, 7-12

redo entries, 7-2

requirements, 7-7

verifying blocks, 7-18

viewing, 2-31

redo logs

See also online redo log

storing separately from datafiles, 12-4

unavailable when database is opened, 4-6

redo records, 7-2

LOGGING and NOLOGGING, 11-20

REFERENCES privilege

CASCADE CONSTRAINTS option, 25-18

revoking, 25-18

referential integrity

distributed database systems

application development, 30-3

relational design

planning, 1-6

release number format, 1-8

releases, 1-8

checking the Oracle database release number, 1-10

relocating control files, 6-5

remote connections, 1-25

connecting as SYSOPER/SYSDBA, 1-13

password files, 1-20

remote data

querying, 29-33

updating, 29-33

remote procedure calls, 28-46

distributed databases and, 28-46

remote queries, 30-4

distributed databases and, 28-33

execution, 30-4

post-processing, 30-4

remote transactions, 28-35

defined, 28-35

REMOTE_LOGIN_PASSWORDFILE initialization parameter, 1-22

REMOTE_OS_AUTHENT initialization parameter, 28-17

setting, 24-12

REMOTE_OS_ROLES initialization parameter

setting, 25-9, 25-26

RENAME PARTITION clause, 17-51

RENAME statement, 21-3

RENAME SUBPARTITION clause, 17-51

renaming control files, 6-5

renaming files

Oracle-managed files, 3-22

REOPEN option

LOG_ARCHIVE_DEST_n initialization parameter, 8-18

repairing data block corruption

DBMS_REPAIR, 22-2 to 22-15

resource allocation methods, 27-4

active session pool, 27-15

CPU resource, 27-14

EMPHASIS, 27-14

limit on degree of parallelism, 27-15

limiting degree of parallelism, 27-15

PARALLEL_DEGREE_LIMIT_ABSOLUTE, 27-15

queueing resource allocation method, 27-15

ROUND-ROBIN, 27-16

resource consumer groups, 27-3

creating, 27-16 to 27-17

DEFAULT_CONSUMER_GROUP, 27-16, 27-17, 27-21, 27-23

deleting, 27-17

LOW_GROUP, 27-17, 27-29

managing, 27-20 to 27-23

OTHER_GROUPS, 27-6, 27-13, 27-16, 27-19, 27-28

parameters, 27-16

SYS_GROUP, 27-17, 27-28

updating, 27-17

resource limits

altering in profiles, 24-21

assigning with profiles, 24-20

costs and, 24-22

creating profiles and, 24-20

disabling, 24-19

enabling, 24-19

privileges to enable and disable, 24-19

privileges to set costs, 24-22

profiles, 24-18

PUBLIC_DEFAULT profile and, 24-20

setting to null, 24-21

resource plan directives, 27-4, 27-12

deleting, 27-19

specifying, 27-17 to 27-20

updating, 27-19

resource plans, 27-3

creating, 27-10 to 27-16

DELETE_PLAN_CASCADE, 27-16

deleting, 27-15

examples, 27-4, 27-25

parameters, 27-14

plan schemas, 27-6, 27-12, 27-16, 27-25, 27-32

subplans, 27-5, 27-6, 27-16

SYSTEM_PLAN, 27-15, 27-17, 27-28

top plan, 27-6, 27-13, 27-24

updating, 27-15

validating, 27-12

RESOURCE role, 25-5

RESOURCE_LIMIT initialization parameter

enabling and disabling limits, 24-19

RESOURCE_MANAGER_PLAN initialization parameter, 27-24

resources

profiles, 24-18

responsibilities

database administrator, 1-2

of database users, 1-4

RESTRICT OPTION

STARTUP command, 4-7

RESTRICTED SESSION system privilege

connecting to database, 4-7

connecting to database., 4-7

restricted mode and, 4-7

resumable space allocation

correctable errors, 14-17

detecting suspended statements, 14-21

disabling, 14-19

distributed databases, 14-18

enabling, 14-19

example, 14-23

how resumable statements work, 14-15

naming statements, 14-20

parallel execution and, 14-18

resumable operations, 14-16

setting as default for session, 14-20

timeout interval, 14-19, 14-20

REVOKE CONNECT THROUGH clause

revoking proxy authorization, 24-16

REVOKE statement, 25-16

when takes effect, 25-20

revoking privileges and roles

on selected columns, 25-18

REVOKE statement, 25-16

when using operating-system roles, 25-25

RMAN. See Recovery Manager.

role identification

operating system accounts, 25-24

roles

ADMIN OPTION and, 25-12

application developers and, 23-11

AQ_ADMINISTRATOR_ROLE, 25-6

AQ_USER_ROLE, 25-6

authorization, 25-8

authorized by enterprise directory service, 25-10

changing authorization for, 25-7

changing passwords, 25-7

CONNECT role, 25-5

database authorization, 25-8

DBA role, 1-13, 25-5

default, 24-7, 25-21

definition, 25-5

DELETE_CATALOG_ROLE, 25-6

disabling, 25-21

dropping, 25-10

enabling, 25-21

enterprise, 24-14, 25-10

EXECUTE_CATALOG_ROLE, 25-6

EXP_FULL_DATABASE, 25-5

global, 24-13, 25-10

global authorization, 25-10

GRANT statement, 25-25

granting, 25-11

granting, about, 25-11

HS_ADMIN_ROLE, 25-6

IMP_FULL_DATABASE, 25-5

listing, 25-30

listing grants, 25-28

listing privileges and roles in, 25-30

management using the operating system, 25-22

managing, 25-6

maximum, 25-22

multibyte characters in names, 25-7

multibyte characters in passwords, 25-8

network authorization, 25-9

obtained through database links, 28-23

operating system, 25-24

operating system granting of, 25-23, 25-25

operating-system authorization, 25-9

OS management and the shared server, 25-25

passwords for enabling, 25-8

predefined, 1-13, 25-5

privileges for creating, 25-7

privileges for dropping, 25-10

privileges, changing authorization method for, 25-8

privileges, changing passwords, 25-8

RECOVERY_CATALOG_OWNER, 25-6

RESOURCE role, 25-5

REVOKE statement, 25-25

revoking, 25-16

revoking ADMIN OPTION, 25-16

security and, 23-6

SELECT_CATALOG_ROLE, 25-6

SET ROLE statement, 25-25

SNMPAGENT, 25-6

unique names for, 25-7

WITH GRANT OPTION and, 25-13

without authorization, 25-7

rollback segments

acquiring automatically, 13-15, 13-23

acquiring on startup, 2-43

altering storage parameters, 13-21

AVAILABLE, 13-22

bringing online, 13-22

bringing online when new, 13-19

bringing PARTLY AVAILABLE segment online, 13-23

checking if offline, 13-23

creating, 13-19 to 13-21

displaying information about, 13-26

displaying names of all, 13-28

displaying PENDING OFFLINE segments, 13-29

dropping, 13-22, 13-25

equally sized extents, 13-17

explicitly assigning transactions to, 13-24

guidelines for managing, 13-13 to 13-18

in-doubt distributed transactions, 32-9

initial creation of SYSTEM, 13-14

INITIAL storage parameter, 13-18, 13-20

initialization parameters used with, 13-5

invalid status, 13-25

listing extents in, 21-35

location of, 13-18

making available for use, 13-22

maximum number of, 13-15

MINEXTENTS, 13-17, 13-18, 13-20

NEXT, 13-18, 13-20

OFFLINE, 13-22

OPTIMAL, 13-17, 13-18, 13-20

PARTLY AVAILABLE, 13-22

PCTINCREASE, 13-18, 13-20

PENDING OFFLINE, 13-24

private, 13-15, 13-19

privileges for dropping, 13-25

privileges required to create, 13-19

public, 13-19

public vs. private, 13-15

setting size of, 13-16

shrinking size of, 13-21

starting an instance using, 13-4

status for dropping, 13-25

status or state, 13-22

storage parameters, 13-20

taking offline, 13-23

taking tablespaces offline and, 11-23

using multiple, 13-14

ROLLBACK statement

FORCE clause, 32-10, 32-11, 32-12

forcing, 32-9

ROLLBACK_SEGMENTS initialization parameter, 13-15

adding rollback segments to, 13-19, 13-23

dropping rollback segments, 13-25

online at instance startup, 13-16

setting before database creation, 2-43

rollbacks

ORA-02067 error, 30-3

ROUND-ROBIN resource allocation method, 27-16

row movement clause for partitioned tables, 17-10

rows

chaining across blocks, 14-4

listing chained or migrated, 21-7

S

Sample Schemas

description, 2-33

savepoints

in-doubt transactions, 32-11, 32-13

schema objects

analyzing, 21-3 to 21-6

cascading effects on revoking, 25-19

creating multiple objects, 21-2

default audit options, 26-13

default tablespace for, 24-3

defining using DBMS_METADATA package, 21-31

dependencies between, 21-23

disabling audit options, 26-14

distributed database naming conventions for, 28-22

enabling audit options on, 26-12

global names, 28-22

granting privileges, 25-12

in a revoked tablespace, 24-4

listing by type, 21-33

name resolution in SQL statements, 21-25

obtaining metadata about, 21-31

owned by dropped users, 24-7

privileges to access, 25-4

privileges to rename, 21-3

privileges with, 25-4

renaming, 21-3

revoking privileges, 25-16

validating structure, 21-6

viewing information, 21-30

schema-independent users, 24-14

SCN. See system change number.

SCOPE clause

ALTER SYSTEM SET, 2-47

Secure Sockets Layer, 23-2, 24-8, 24-14

security

accessing a database, 23-2

administrator of, 23-2

application developers and, 23-10

auditing policies, 23-20

authentication of users, 23-2

data, 23-3

database security, 23-2

database users and, 23-2

distributed databases, 28-24

centralized user management, 28-27

establishing policies, 23-1

general users, 23-4

level of, 23-3

multibyte characters in role names, 25-7

multibyte characters in role passwords, 25-8

operating-system security and the database, 23-3

policies for database administrators, 23-8

privilege management policies, 23-6

privileges, 23-2

protecting the audit trail, 26-18

remote objects, 29-28

REMOTE_OS_ROLES parameter, 25-26

roles to force security, 23-6

security officer, 1-3

test databases, 23-10

using synonyms, 29-30

SEGMENT_FIX_STATUS procedure, 22-3

segments

available space, 21-31

data dictionary, 21-28

data dictionary views for, 21-33

deallocating unused space, 14-25

displaying information on, 21-35

monitoring rollback, 13-27

rollback. See rollback segments.

temporary

storage parameters, 14-13

SELECT statement

FOR UPDATE clause, 29-33

SELECT_CATALOG_ROLE role, 25-6

SELECT_CATALOG_ROLE roll, 25-3

sequences

altering, 20-13

creating, 20-12

dropping, 20-13

managing, 20-11

Oracle Real Applications Clusters and, 20-13

privileges for altering, 20-12

privileges for creating, 20-12

privileges for dropping, 20-13

SERVER parameter

net service name, 29-16

server parameter file

creating, 2-46

defined, 2-44

error recovery, 2-50

exporting, 2-49

migrating to, 2-45

RMAN backup, 2-50

setting initialization parameter values, 2-47

SPFILE initialization parameter, 2-47

STARTUP command behavior, 2-45, 4-3

viewing parameter settings, 2-51

server processes

archiver (ARCn), 5-12

background, 5-11 to 5-13

checkpoint (CKPT), 5-12

database writer (DBWn), 5-12

dedicated, 5-2

dispatcher (Dnnn), 5-13

dispatchers, 5-6 to 5-10

global cache service (LMS), 5-13

job queue coordinator process (CJQ0), 5-13, 10-2

log writer (LGWR), 5-12

monitoring, 5-13

monitoring locks, 5-15

process monitor (PMON), 5-12

recoverer (RECO), 5-13

shared server, 5-3 to 5-10

system monitor (SMON), 5-12

trace files for, 5-15

servers

role in two-phase commit, 31-5

service names

database links and, 29-13

session trees

distributed transactions, 31-4

clients, 31-5

commit point site, 31-6, 31-8

database servers, 31-5

global coordinators, 31-6

local coordinators, 31-6

tracing, 32-5

sessions

auditing connections and disconnections, 26-11

listing privilege domain of, 25-29

setting advice for transactions, 32-10

terminating, 5-21 to 5-23

viewing memory use, 24-26

sessions, user

active, 5-22

inactive, 5-23

marked to be terminated, 5-22

terminating, 5-21

viewing terminated sessions, 5-23

SET ROLE statement

how password is set, 25-8

used to enable/disable roles, 25-21

when using operating-system roles, 25-25

SET TIME_ ZONE clause

CREATE DATABASE, 2-28

SET TIME_ZONE clause

ALTER SESSION, 2-28

time zone files, 2-28

SET TRANSACTION statement

naming transactions, 32-2

USE ROLLBACK SEGMENT option, 13-24

SGA. See system global area.

SGA_MAX_SIZE initialization parameter, 2-38

setting size, 2-40

shared database links

configuring, 29-16

creating links, 29-14, 29-15

to dedicated servers, 29-16

to shared servers, 29-17

determining whether to use, 29-14

example, 28-20

SHARED keyword

CREATE DATABASE LINK statement, 29-15

shared server, 5-3

adjusting number of dispatchers, 5-8

enabling and disabling, 5-10

initialization parameters, 5-5

OS role management restrictions, 25-25

setting initial number of dispatchers, 5-6

setting initial number of servers, 5-7

setting minimum number of servers, 5-10

views, 5-10

shared server processes

trace files for, 5-15

shared SQL

for remote and distributed statements, 28-34

SHARED_SERVERS initialization parameter

initial setting, 5-7

SHUTDOWN command

ABORT option, 4-13

IMMEDIATE option, 4-12

NORMAL option, 4-12

TRANSACTIONAL option, 4-13

Simple Network Management Protocol (SNMP) support

database management, 28-32

single-process systems

enabling distributed recovery, 32-25

single-table hash clusters, 19-5

site autonomy

distributed databases, 28-24

SKIP_CORRUPT_BLOCKS procedure, 22-3, 22-7

example, 22-14

snapshot too old

OPTIMAL storage parameter and, 13-18

undo retention and, 13-9

SNMPAGENT role, 25-6

SORT_AREA_SIZE initialization parameter

index creation and, 16-3

space allocation

resumable, 14-14 to 14-25

space management

data blocks, 14-2 to 14-7

datatypes, space requirements, 14-29

deallocating unused space, 14-25

setting storage parameters, 14-9 to 14-13

SPACE_ERROR_INFO procedure, 14-21

specifying destinations

for archived redo logs, 8-10

specifying multiple ARCH processes, 8-19

SPFILE initialization parameter, 2-47

specifying from client machine, 4-5

SPLIT PARTITION clause, 17-27, 17-52

SQL errors

ORA-00900, 30-11

ORA-02015, 30-11

SQL statements

disabling audit options, 26-14

distributed databases and, 28-33

enabling audit options on, 26-11

SQL*Loader

about, 1-26

SQL*Plus

starting, 4-3

starting a database, 4-2

starting an instance, 4-2

SQL_TRACE initialization parameter

trace files and, 5-15

SSL. See Secure Sockets Layer.

STALE status

of redo log members, 7-17

standby transmission mode

definition of, 8-14

Oracle Net and, 8-15

RFS processes and, 8-15

starting a database

forcing, 4-8

Oracle Enterprise Manager, 4-2

recovery and, 4-8

Recovery Manage, 4-2

restricted mode, 4-7

SQL*Plus, 4-2

when control files unavailable, 4-6

when redo logs unavailable, 4-6

starting an instance

automatically at system startup, 4-9

database closed and mounted, 4-6

database name conflicts and, 2-36

enabling automatic archiving, 8-7

forcing, 4-8

mounting and opening the database, 4-6

normally, 4-6

Oracle Enterprise Manager, 4-2

recovery and, 4-8

Recovery Manager, 4-2

remote instance startup, 4-9

restricted mode, 4-7

SQL*Plus, 4-2

when control files unavailable, 4-6

when redo logs unavailable, 4-6

without mounting a database, 4-6

STARTUP command

default behavior, 2-45

MOUNT option, 4-7

NOMOUNT option, 2-17, 4-6

RECOVER option, 4-8

RESTRICT option, 4-7

starting a database, 4-2, 4-3

statistics

automatically collecting for tables, 15-9

storage

quotas and, 24-4

revoking tablespaces and, 24-4

unlimited quotas, 24-4

STORAGE clause

See also storage parameters

storage parameters

altering, 15-11

altering defaults for tablespaces, 11-11

applicable objects, 14-8

BUFFER POOL, 14-11

changing for data dictionary objects, 21-27

data dictionary, 21-27

default, 14-9

example, 14-14

for the data dictionary, 21-28

FREELIST GROUPS, 14-11

FREELISTS, 14-11

INITIAL, 14-10

INITIAL, cannot alter, 15-12

INITRANS, altering, 15-11

MAXEXTENTS, 14-10

MAXTRANS, altering, 15-11

MINEXTENTS, 14-10

MINEXTENTS, cannot alter, 15-12

NEXT, 14-10

NEXT, altering, 15-12

OPTIMAL, 14-11

OPTIMAL (in rollback segments), 13-18

PCTFREE, altering, 15-11

PCTFREE, specifying, 15-2

PCTINCREASE, 14-10

PCTINCREASE, altering, 15-12

PCTUSED, altering, 15-11

PCTUSED, specifying, 15-2

precedence of, 14-13

rollback segments, 13-20

setting, 14-9 to 14-13

SYSTEM rollback segment, 13-21

temporary segments, 14-13

storage subsystems

mapping files to physical devices, 12-15 to 12-27

STORE IN clause, 17-14

stored procedures

distributed query creation, 30-3

managing privileges, 29-32

privileges for recompiling, 21-25

remote object security, 29-32

using privileges granted to PUBLIC, 25-20

SUBPARTITION BY HASH clause

for composite-partitioned tables, 17-14

SUBPARTITION BY LIST clause

for composite-partitioned tables, 17-15

SUBPARTITION clause, 17-29, 17-30, 17-54

for composite-partitioned tables, 17-14, 17-15

subpartition template

modifying, 17-48

subpartition templates, 17-17

subpartitions, 17-2

SUBPARTITIONS clause, 17-29, 17-54

for composite-partitioned tables, 17-14

subqueries, 29-33

in remote updates, 28-34

SunSoft's SunNet Manager, 28-32

supplemental logging

for LogMiner utility, 9-19

LogMiner utility

identification keys, 9-20

log groups, 9-22

SWITCH LOGFILE option

ALTER SYSTEM statement, 7-18

synonyms

CREATE statement, 29-28

creating, 20-14

definition and creation, 29-28

displaying dependencies of, 21-34

dropping, 20-15

examples, 29-29

location transparency using, 29-28

managing, 20-13 to 20-15

managing privileges, 29-30

name resolution, 28-42

name resolution in distributed databases, 28-42

private, 20-13

privileges for creating, 20-14

privileges for dropping, 20-14

public, 20-13

remote object security, 29-30

SYS

specifying password for CREATE DATABASE statement, 2-23

SYS account

default password, 1-11

objects owned, 1-12

policies for protecting, 23-8

privileges, 1-12

user, 1-12

SYS_GROUP for Database Resource Manager, 27-17, 27-28

SYS.AUD$ table

audit trail, 26-2

creating and deleting, 26-19

SYSDBA system privilege

connecting to database, 1-14

SYSOPER system privilege

connecting to database, 1-14

SYSOPER/SYSDBA privileges

adding users to the password file, 1-23

connecting with, 1-13

determining who has privileges, 1-24

granting and revoking, 1-24

SYSTEM

specifying password for CREATE DATABASE, 2-23

SYSTEM account

default password, 1-11

objects owned, 1-12

policies for protecting, 23-8

system change number

using V$DATAFILE to view information about, 12-29

when assigned, 7-2

system change numbers (SCN)

coordination in a distributed database system, 31-14

in-doubt transactions, 32-12

system global area

initialization parameters affecting size, 2-38

specifying buffer cache sizes, 2-40

system monitor, 5-12

system privileges, 25-2

ADMINISTER_RESOURCE_MANAGER, 27-8

described, 25-2

for external tables, 15-39

GRANT ANY OBJECT PRIVILEGE, 25-14, 25-17

granting, 25-11

SYSTEM rollback segment

altering storage parameters of, 13-21

SYSTEM Tablespace

creating locally managed, 2-26

SYSTEM tablespace

cannot drop, 11-29

initial rollback segment, 13-14

restrictions on taking offline, 12-8

when created, 11-3

SYSTEM_PLAN for Database Resource Manager, 27-15, 27-17, 27-28

T

tables

adding columns, 15-13

allocating extents, 15-12

altering, 15-10

altering physical attributes, 15-11

analyzing, 21-3 to 21-6

clustered (hash). See hash clusters

clustered (index). See clusters.

creating, 15-7

creating in locally managed tablespaces, 15-3

data block space, specifying, 15-2

designing before creating, 15-2

dropping, 15-23

dropping columns, 15-14 to 15-16

estimating size, 15-4

external, 15-33 to 15-39

guidelines for managing, 15-2

hash clustered. See hash clusters

historical

moving time windows, 17-61

increasing column length, 15-13

index-organized, 15-24 to 15-33

partitioning, 17-19 to 17-21

key-preserved, 20-6

limiting indexes on, 16-5

managing, 15-1 to 15-41

modifying column definition, 15-13

moving, 15-12

parallelizing creation, 15-4, 15-8

partitioned, 17-2 to 17-65

see also partitioned tables

planning large tables, 15-5

privileges for creation, 15-6

privileges to alter, 15-10

redefining online, 15-16 to 15-22

renaming columns, 15-14

restrictions when creating, 15-6

separating from indexes, 15-6

setting storage parameters, 15-4

specifying location, 15-3

specifying PCTFREE for, 14-4

statistics collection, automatic, 15-9

temporary, 15-8

temporary space for creation, 15-6

truncating, 21-9

unrecoverable (NOLOGGING), 15-4

validating structure, 21-6

views, 15-40

tablespace set, 11-37

tablespaces

adding datafiles, 12-5

altering storage parameters, 11-11

assigning defaults for users, 24-3

assigning user quotas, 11-3

automatic segment space management, 11-8

checking default storage parameters, 11-51

coalescing free space, 11-16

creating a default temporary tablespace, 2-24

creating undo tablespace at database creation, 2-24

DBMS_SPACE_ADMIN package, 11-30

default quota, 24-4

detecting and repairing defects, 11-30

dictionary managed, 11-10 to 11-12

dropping, 11-29

guidelines for managing, 11-2

listing files of, 11-51

listing free space in, 11-51

locally managed, 11-5 to 11-10

locally managed SYSTEM, 2-26

locally managed temporary, 11-13

location, 12-4

making read-only, 11-25

making read-only writable, 11-27

migrating SYSTEM to locally managed, 11-34

multiple block sizes, 11-41

on a WORM device, 11-27

privileges for creating, 11-4

privileges to take offline, 11-21

quotas for users, 24-4

quotas, assigning, 11-3

revoking from users, 24-4

setting default storage parameters, 14-11

setting default storage parameters for, 11-3

specifying non-standard block sizes, 11-19

SYSTEM tablespace, 11-3

taking offline normal, 11-21

taking offline temporarily, 11-22

tempfiles in locally managed, 11-13

temporary

assigning to users, 24-5

for creating large indexes, 16-13

temporary, creating, 11-12

transportable, 11-35 to 11-49

undo, 13-2 to 13-13

unlimited quotas, 24-4

using multiple, 11-2

viewing quotas, 24-25

tempfiles, 11-13

creating as Oracle-managed files, 3-16

dropping Oracle-managed files, 3-21

taking offline, 11-23

templates

for databases (DBCA), 2-11

temporary segments

index creation and, 16-3

temporary tables

creating, 15-8

temporary tablespaces, see tablespaces, temporary

terminating user sessions

active sessions, 5-22

identifying sessions, 5-22

inactive session, example, 5-23

inactive sessions, 5-23

threads

online redo log, 7-2

time zone

files, 2-28

setting for database, 2-28

TNSNAMES.ORA file, 8-11

trace files

job failures and, 10-10

location of, 5-16

log writer, 5-16

log writer process and, 7-6

size of, 5-16

using, 5-15, 5-16

when written, 5-17

tracing

archivelog process, 8-21

transaction control statements

distributed transactions and, 31-4

transaction failures

simulating, 32-24

transaction management

overview, 31-10

transaction processing

distributed systems, 28-33

transactions

assigning to specific rollback segment, 13-24

closing database links, 30-2

distributed

two-phase commit and, 28-36

in-doubt, 31-14

after a system failure, 32-8

pending transactions table, 32-22

recoverer process (RECO) and, 32-24

manually overriding in-doubt, 32-9

naming distributed, 32-2, 32-10

remote, 28-35

rollback segments and, 13-24

TRANSACTIONS initialization parameter, 13-15

TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameter, 13-15

transmitting archived redo logs, 8-14

in normal transmission mode, 8-14

in standby transmission mode, 8-14

transparency

location

using procedures, 29-30, 29-31, 29-32

query, 29-32

update, 29-32

transportable tablespaces, 11-35 to 11-49

multiple block sizes, 11-41

transporting tablespaces between databases, 11-34 to 11-49

triggers

disabling, 21-13

distributed query creation, 30-3

enabling, 21-13

privileges for enabling and disabling, 21-13

TRUNCATE PARTITION clause, 17-59

TRUNCATE statement, 21-10

DROP STORAGE clause, 21-11

REUSE STORAGE clause, 21-11

vs. dropping table, 15-23

TRUNCATE SUBPARTITION clause, 17-61

tuning

analyzing tables, 30-7

archiving, 8-19

cost-based optimization, 30-5

databases, 1-8

two-phase commit

case study, 31-19

commit phase, 31-14, 31-23

steps in, 31-14

described, 28-35

distributed transactions, 31-10

tracing session tree, 32-5

viewing information about, 32-3

forget phase, 31-15

in-doubt transactions, 31-15

automatic resolution, 31-16

manual resolution, 31-18

SCNs and, 31-19

phases, 31-10

prepare phase, 31-11

abort response, 31-13

prepared response, 31-12

read-only response, 31-12

responses, 31-11

steps, 31-13

problems, 32-8

recognizing read-only nodes, 31-12

specifying commit point strength, 32-2

U

undo space management

automatic undo management mode, 13-3 to 13-13

described, 13-2

rollback segment undo mode, 13-13 to 13-29

specifying mode, 13-3

undo tablespaces

altering, 13-7

creating, 13-6

dropping, 13-7

estimating space requirements, 13-11

initialization parameters for, 13-3

monitoring, 13-12

PENDING OFFLINE status, 13-8

specifying at database creation, 2-24

specifying retention period, 13-9

starting an instance using, 13-3

statistics for, 13-12

switching, 13-8

used with flashback queries, 13-10

user quotas, 13-9

viewing information about, 13-11

UNDO_MANAGEMENT initialization parameter, 2-24

starting instance as AUTO, 13-3

UNDO_RETENTION initialization parameter

for undo tablespaces, 13-9

UNDO_SUPPRESS_ERROR initialization parameter

for undo tablespaces, 13-4

UNDO_TABLESPACE initialization parameter

starting an instance using, 13-3

UNIQUE key constraints

associated indexes, 16-11

dropping associated indexes, 16-23

enabling on creation, 16-11

foreign key references when dropped, 21-19

indexes associated with, 16-11

UNLIMITED TABLESPACE privilege, 24-5

UNRECOVERABLE DATAFILE option

ALTER DATABASE statement, 7-20

UPDATE GLOBAL INDEX clause

of ALTER TABLE, 17-26

UPDATE privilege

revoking, 25-18

updates

location transparency and, 28-45

transparency, 29-32

upgrading a database, 2-5

USER_DB_LINKS view, 29-21

USER_DUMP_DEST initialization parameter, 5-16

USER_JOBS view

jobs in system, viewing, 10-15

USER_RESUMABLE view, 14-21

USER_SEGMENTS view, 11-50

usernames

SYS and SYSTEM, 1-11

users

altering, 24-6

assigning profiles to, 24-20

assigning tablespace quotas, 11-3

assigning unlimited quotas for, 24-4

authentication

about, 23-2, 24-8

changing default roles, 24-7

database authentication, 24-9

default tablespaces, 24-3

dropping, 24-7

dropping profiles and, 24-23

dropping roles and, 25-10

end-user security policies, 23-6

enrolling, 1-7

enterprise, 24-14, 25-10

external authentication, 24-11

global, 24-13

in a newly created database, 2-32

limiting number of, 2-43

listing, 24-23

listing privileges granted to, 25-27

listing roles granted to, 25-28

managing, 24-2

network authentication, 24-13

objects after dropping, 24-7

operating system authentication, 24-12

password security, 23-5

policies for managing privileges, 23-6

privileges for changing passwords, 24-6

privileges for creating, 24-2

privileges for dropping, 24-8

proxy authentication and authorization, 24-16

PUBLIC group, 25-20

schema-independent, 24-14

security and, 23-2

security for general users, 23-4

session, terminating, 5-23

specifying user names, 24-3

tablespace quotas, 24-4

unique user names, 2-43

viewing information on, 24-25

viewing memory use, 24-26

viewing tablespace quotas, 24-25

utilities

Export, 1-26

for the database administrator, 1-26

Import, 1-26

SQL*Loader, 1-26

UTLCHAIN.SQL script

listing chained rows, 21-7

UTLCHN1.SQL script

listing chained rows, 21-7

UTLLOCKT.SQL script, 5-15

V

V$ARCHIVE view, 8-23

V$ARCHIVE_DEST view

obtaining destination status, 8-13

V$DATABASE view, 8-24

V$DATAFILE view, 11-50

V$DBFILE view, 2-31

V$DBLINK view, 29-25

V$DISPATCHER view

monitoring shared server dispatchers, 5-8

V$DISPATCHER_RATE view

monitoring shared server dispatchers, 5-8

V$INSTANCE view

for database quiesce state, 4-16

V$LOG view, 8-23

displaying archiving status, 8-23

online redo log, 7-20

viewing redo data with, 7-20

V$LOG_HISTORY view

viewing redo data, 7-20

V$LOGFILE view, 2-31

log file status, 7-17

viewing redo data, 7-20

V$LOGMNR_CONTENTS view, 9-16

V$OBJECT_USAGE view

for monitoring index usage, 16-21

V$PWFILE_USERS view, 1-24

V$QUEUE view

monitoring shared server dispatchers, 5-8

V$ROLLNAME view

finding PENDING OFFLINE segments, 13-29

V$ROLLSTAT view

finding PENDING OFFLINE segments, 13-29

undo segments, 13-12

V$SESSION view, 5-23

V$SORT SEGMENT view, 11-50

V$SORT_USER view, 11-50

V$TEMP_EXTENT_MAP view, 11-50

V$TEMP_EXTENT_POOL view, 11-50

V$TEMP_SPACE_HEADER view, 11-50

V$TEMPFILE view, 11-50

V$THREAD view, 7-20

V$TIMEZONE_NAMES view

time zone table information, 2-29

V$TRANSACTION view

undo tablespaces information, 13-12

V$UNDOSTAT view

statistics for undo tablespaces, 13-12

V$VERSION view, 1-10

varrays

storage parameters for, 14-12

verifying blocks

redo log files, 7-18

views

creating, 20-2

creating with errors, 20-4

Database Resource Manager, 27-31

DATABASE_PROPERTIES, 2-25

DBA_RESUMABLE, 14-21

displaying dependencies of, 21-34

dropping, 20-10

file mapping views, 12-23

for monitoring datafiles, 12-28

FOR UPDATE clause and, 20-3

join. See join views.

location transparency using, 29-26

managing, 20-2, 20-11

managing privileges with, 29-28

name resolution in distributed databases, 28-42

ORDER BY clause and, 20-3

privileges, 20-2

privileges for dropping, 20-10

privileges for recompiling, 21-25

privileges to replace, 20-10

recompiling, 21-25

remote object security, 29-28

tables, 15-40

USER_RESUMABLE, 14-21

V$ARCHIVE, 8-23

V$ARCHIVE_DEST, 8-13

V$DATABASE, 8-24

V$LOG, 7-20, 8-23

V$LOG_HISTORY, 7-20

V$LOGFILE, 7-17, 7-20

V$OBJECT_USAGE, 16-21

V$THREAD, 7-20

wildcards in, 20-4

WITH CHECK OPTION, 20-3

W

wildcards

in views, 20-4

Windows operating system

OS audit trail, 26-2, 26-9

WORM devices

and read-only tablespaces, 11-27