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 con