Index

ALTER DATABASE statement, 6-12

ADD LOGFILE MEMBER option

ADD LOGFILE option

ALTER DATABASE statement, 6-11

ADD PARTITION clause, 15-14

ADD SUBPARTITION clause, 15-15

ADMIN OPTION

about, 23-11

revoking roles/privileges, 23-13

ADMIN_TABLES procedure, 20-3, 20-4

examples

building orphan key table, 20-10

building repair table, 20-9

ADMINISTER_RESOURCE_MANAGER system privilege, 25-3

administrators

AFTER triggers

alert log

session high water mark in, 22-7

size of, 4-16

using, 4-15

when written, 4-16

ALL_INDEXES view

ALL_TAB_COLUMNS view

ALL_TABLES view

minimizing extents for rollback segments, 11-12

allocation

extents, 13-13

temporary space, 13-6

ALTER CLUSTER statement

ALLOCATE EXTENT option, 16-9

using for hash clusters, 17-9

using for index clusters, 16-9

ALTER DATABASE statement

ADD LOGFILE MEMBER option, 6-12

ADD LOGFILE option, 6-11

ARCHIVELOG option, 7-6

CLEAR LOGFILE option, 6-17

CLEAR UNARCHIVED LOGFILE option, 6-7

database partially available to users, 3-8

DATAFILE...OFFLINE DROP option, 10-8

DROP LOGFILE MEMBER option, 6-16

DROP LOGFILE option, 6-15

MOUNT option, 3-8

NOARCHIVELOG option, 7-6

OPEN option, 3-8

READ ONLY option, 3-9

RENAME FILE option

datafiles for multiple tablespaces, 10-11

UNRECOVERABLE DATAFILE option, 6-18

ALTER FUNCTION statement

COMPILE option, 19-25

ALTER INDEX COALESCE, 14-7

ALTER INDEX statement, 14-15

for maintaining partitioned indexes, 15-12 to 15-32

ALTER PACKAGE statement

COMPILE option, 19-25

ALTER PROCEDURE statement

COMPILE option, 19-25

ALTER PROFILE statement

altering resource limits, 22-23

ALTER RESOURCE COST statement, 22-24

ALTER ROLE statement

changing authorization method, 23-7

ALTER ROLLBACK SEGMENT statement

changing storage parameters, 11-9

OFFLINE option, 11-11

ONLINE option, 11-10, 11-11

STORAGE clause, 11-9

ALTER SEQUENCE statement, 18-13

ALTER SESSION statement

SET SQL_TRACE initialization parameter, 4-17

ALTER SYSTEM statement

ARCHIVE LOG ALL option, 7-9

ARCHIVE LOG option, 7-9

ENABLE RESTRICTED SESSION option, 3-9

RESUME clause, 3-14

SET LICENSE_MAX_SESSIONS option, 22-4

SET LICENSE_MAX_USERS option, 22-6

SET LICENSE_SESSIONS_WARNING option, 22-4

SET MTS_SERVERS option, 4-8

SET RESOURCE_LIMIT option, 22-22

SET RESOURCE_MANAGER_PLAN, 25-18

SUSPEND clause, 3-14

SWITCH LOGFILE option, 6-16

ALTER TABLE statement

ALLOCATE EXTENT option, 13-14

DISABLE ALL TRIGGERS option, 19-14

DISABLE integrity constraint option, 19-18

DROP integrity constraint option, 19-20

ENABLE ALL TRIGGERS option, 19-14

ENABLE integrity constraint option, 19-18, 19-19

example, 13-12

for maintaining partitions, 15-12 to 15-32

ALTER TABLESPACE statement

ADD DATAFILE parameter, 10-5

ONLINE option

example, 9-17

READ ONLY option, 9-18

READ WRITE option, 9-20

RENAME DATA FILE option, 10-10

ALTER TRIGGER statement

DISABLE option, 19-14

ENABLE option, 19-13

ALTER USER privilege, 22-18

ALTER USER statement

default roles, 23-18

GRANT CONNECT THROUGH clause, 22-14

REVOKE CONNECT THROUGH clause, 22-14

ALTER VIEW statement

COMPILE option, 19-24

altering storage parameters, 13-12

altering users, 22-19

ANALYZE statement

CASCADE option, 19-9

COMPUTE STATISTICS option, 19-5

corruption reporting, 20-5

ESTIMATE STATISTICS SAMPLE option, 19-5

LIST CHAINED ROWS option, 19-9

shared SQL and, 19-8

STATISTICS option, 19-4

VALIDATE STRUCTURE option, 19-9

validating structure, 20-4

analyzing archived redo logs, 7-26

analyzing objects

about, 19-3

privileges, 19-4

application administrators, 21-11

application context, 21-4

application developers

privileges for, 21-10

roles for, 21-11

application development

security for, 21-10

applications

administrator, 1-3

applications administrator, 1-3

ARCH process

specifying multiple processes, 7-20

architecture

Optimal Flexible Architecture (OFA), 2-5

archive buffer parameters, 7-21

ARCHIVE LOG option

ALTER SYSTEM statement, 7-9

ARCHIVE LOG statement

LIST option, 6-15

archive processes, 4-12

archived redo logs, 7-2

analyzing, 7-26

archiving modes, 7-6

destination states, 7-12

active/inactive, 7-13

bad param, 7-14

deferred, 7-13

enabled/disabled, 7-13

valid/invalid, 7-13

destinations

re-archiving to failed, 7-19

sample scenarios, 7-17

enabling automatic archiving, 7-7

failed destinations and, 7-16

multiplexing, 7-10

normal transmission of, 7-14

specifying destinations for, 7-10

standby transmission of, 7-14

status information, 7-23

automatic archiving in, 7-5

definition of, 7-4

distributed databases, 7-5

enabling, 7-6

manual archiving in, 7-5

running in, 7-4

switching to, 7-6

taking datafiles offline and online in, 10-8

archivelog process (ARCn)

tracing, 7-25

archiver, 4-12

archiving

AUDIT statement, 24-9

BY proxy clause, 24-11

schema objects, 24-10

statement auditing, 24-10

system privileges, 24-10

audit trail, 24-14

archiving, 24-15

auditing changes to, 24-17

controlling size of, 24-14

creating and deleting, 24-4

deleting views, 24-5

dropping, 24-4

interpreting, 24-17

maximum size of, 24-14

protecting integrity of, 24-16

purging records from, 24-15

recording changes to, 24-17

records in, 24-7

reducing size of, 24-16

table that holds, 24-2

views on, 24-4

AUDIT_TRAIL initialization parameter

setting, 24-13

auditing, 24-2

audit option levels, 24-8

AUDIT statement, 24-9

audit trail records, 24-5

default options, 24-10

disabling default options, 24-13

disabling options, 24-11, 24-12, 24-13

disabling options versus auditing, 24-12

enabling options, 24-9, 24-13

about, 24-9

privileges for, 24-13

enabling options versus auditing, 24-9

guidelines, 24-2

historical information, 24-4

keeping information manageable, 24-2

managing the audit trail, 24-4

multi-tier environments, 24-11

operating-system audit trails, 24-7

policies for, 21-19

privilege audit options, 24-8

privileges required for object, 24-11

privileges required for system, 24-10

schema objects, 24-10

session level, 24-8

statement, 24-10

statement level, 24-8

suspicious activity, 24-3

system privileges, 24-10

triggers and, 24-20, 24-21

using the database, 24-2

viewing

active object options, 24-19

active privilege options, 24-19

active statement options, 24-18

default object options, 24-19

views, 24-4

authentication

by database, 22-8

by SSL, 22-7, 22-12

directory service, 22-12

external, 22-9

global, 22-11

multi-tier, 22-13

operating system, 1-7

password file, 1-9

password policy, 21-5

specifying when creating a user, 22-15

users, 21-2

ways to authenticate users, 22-7

authorization

changing for roles, 23-7

global, 22-11

omitting for roles, 23-7

operating-system role management and, 23-9

roles

about, 23-8

multi-threaded server and, 23-9

background processes, 4-11 to 4-13

SNP, 8-2

BACKGROUND_DUMP_DEST initialization parameter, 4-16

backups

after creating new databases

full backups, 2-11

guidelines, 1-20

effects of archiving on, 7-4

bad param destination state, 7-14

broken jobs

about, 8-12

running, 8-13

BUFFER_POOL parameter

description, 12-11

buffers

buffer cache in SGA, 2-22

CASCADE option

when dropping unique or primary keys, 19-19

cascading revokes, 23-15

CATAUDIT.SQL script

running, 24-4

CATBLOCK.SQL script, 4-15

CATNOAUD.SQL script

running, 24-5

change vectors, 6-2

CHAR datatype

increasing column length, 13-11

character sets

multi-byte characters

in role names, 23-7

in role passwords, 23-8

parameter file and, 3-15

specifying when creating a database, 2-2

CHECK_OBJECT procedure, 20-2, 20-4, 20-5

example, 20-10

checkpoint process, 4-12

checksums

for data blocks, 10-12

redo log blocks, 6-17

CLEAR LOGFILE option

ALTER DATABASE statement, 6-17

clearing redo log files, 6-7, 6-17

restrictions, 6-17

clustered tables. See clusters.

clusters

allocating extents, 16-9

altering, 16-9

analyzing statistics, 19-3

cluster indexes, 16-10

cluster keys

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

ALTER TABLE restrictions, 16-10

columns for cluster key, 16-4

creating, 16-6

deallocating extents, 16-9

dropped tables and, 13-17

dropping, 16-10

estimating space, 16-5, 16-6

guidelines for managing, 16-4 to 16-6

hash

contrasted with index, 17-2

hash clusters, 17-1 to 17-9

index

contrasted with hash, 17-2

privileges

selecting tables, 16-4

single-table hash clusters, 17-5

specifying PCTFREE for, 12-4

truncating, 19-10

validating structure, 19-9

COALESCE PARTITION clause, 15-16

columns

displaying information about, 19-31

granting privileges for selected, 23-11

granting privileges on, 23-12

increasing length, 13-11

INSERT privilege and, 23-12

listing users granted to, 23-24

privileges, 23-12

revoking privileges on, 23-14

composite limits

costs and, 22-24

composite partitioning, 15-6

COMPUTE STATISTICS option, 19-5

CONNECT command

starting an instance, 3-2

CONNECT role, 23-5

connections

auditing, 24-8

constraints

See also integrity constraints

disabling at table creation, 19-17

dropping integrity constraints, 19-20

enable novalidate state, 19-16

enabling example, 19-18

enabling when violations exist, 19-16

exceptions, 19-16, 19-21

exceptions to integrity constraints, 19-21

integrity constraint states, 19-15

setting at table creation, 19-17

when to disable, 19-16

control files

CONTROL_FILES initialization parameter

overwriting existing control files, 2-21

setting

before database creation, 2-21, 5-4

names for, 5-2

warning about setting, 2-21

corruption

data block

repairing, 20-2 to 20-14

costs

resource limits and, 22-24

CREATE CLUSTER statement

creating clusters, 16-7

example, 16-7

for hash clusters, 17-4

HASH IS option, 17-4, 17-6

HASHKEYS option, 17-4, 17-7

SIZE option, 17-6

CREATE CONTROLFILE statement

about, 5-5

checking for inconsistencies, 5-8

NORESETLOGS option, 5-7

RESETLOGS option, 5-7

CREATE DATABASE statement

CONTROLFILE REUSE option, 5-4

MAXLOGFILES option, 6-10

MAXLOGMEMBERS parameter, 6-10

CREATE INDEX statement

explicitly, 14-9

ON CLUSTER option, 16-8

partitioned indexes, 15-9 to 15-11

UNRECOVERABLE, 14-6

with a constraint, 14-10

CREATE PROFILE statement

about, 22-22

CREATE ROLE statement

IDENTIFIED BY option, 23-8

IDENTIFIED EXTERNALLY option, 23-8

CREATE ROLLBACK SEGMENT statement

about, 11-6

tuning guidelines, 2-26

CREATE SCHEMA statement

multiple tables and views, 19-2

CREATE SEQUENCE statement, 18-12

CREATE SYNONYM statement, 18-14

CREATE TABLE statement

about, 13-9

CLUSTER option, 16-7

creating partitioned tables, 15-8 to 15-11

UNRECOVERABLE, 13-4

CREATE TABLESPACE statement

datafile names in, 9-5

example, 9-5

CREATE TEMPORARY TABLESPACE statement, 9-9

CREATE USER statement

IDENTIFIED BY option, 22-15

IDENTIFIED EXTERNALLY option, 22-15

CREATE VIEW statement

about, 18-2

OR REPLACE option, 18-11

WITH CHECK OPTION, 18-3

creating a parameter file, 2-9

creating an audit trail, 24-4

creating databases, 2-1, 7-6

backing up the new database, 2-11

executing CREATE DATABASE, 2-10

manually from a script, 2-4

migration from different versions, 2-4

preparing to, 2-2

prerequisites for, 2-3

problems encountered while, 2-16

using DBCA, 2-4

creating datafiles, 10-5

creating profiles, 22-22

creating sequences, 18-12

creating synonyms, 18-14

creating views, 18-2

custom database

overview, 2-6

data

security of, 21-3

data block corruption

repairing, 20-2 to 20-14

data blocks

altering size of, 2-22

managing space in, 12-2 to 12-6

operating system blocks versus, 2-22

PCTFREE in clusters, 16-5

shared in clusters, 16-2

size of, 2-22

transaction entry settings, 12-7

verifying, 10-12

data dictionary

changing storage parameters, 19-28

conflicts with control files, 5-8

schema object views, 19-29

segments in the, 19-27

setting storage parameters of, 19-26

V$DBFILE view, 2-16

V$LOGFILE view, 2-16

data warehousing

Database Resource Manager, 25-2

database administrators, 1-2

application administrator versus, 21-11

initial priorities, 1-17 to 1-21

operating system account, 1-4

password files for, 1-7

responsibilities of, 1-2

roles

about, 1-6

for security, 21-8

security and privileges of, 1-4

security for, 21-8

security officer versus, 1-3, 21-2

user accounts, 1-4

utilities for, 1-17

database authentication, 22-8

database links

job queues and, 8-8

Database Resource Manager, 25-1 to 25-22

administering system privilege, 25-3 to 25-5

enabling, 25-18

ALTER SYSTEM statement, 25-18

RESOURCE_MANAGER_PLAN initialization parameter, 25-18

managing resource consumer groups, 25-14

changing resource consumer groups, 25-15

granting the switch privilege, 25-15, 25-16

revoking the switch privilege, 25-16

setting initial resource consumer group, 25-14

switching a session, 25-15

switching sessions for a user, 25-15

multiple level CPU resource allocation, 25-13

pending area, 25-7 to 25-10

resource allocation methods, 25-3

CPU resource, 25-10

EMPHASIS, 25-8, 25-10

limiting degree of parallelism, 25-10

PARALLEL_DEGREE_LIMIT_ABSOLUTE, 25-10

ROUND-ROBIN, 25-11

resource consumer groups, 25-2

resource plan directives, 25-3, 25-8

CPU_Pn, 25-12

deleting, 25-14

PARALLEL_DEGREE_LIMIT_P1, 25-8, 25-13

parameters, 25-12

specifying, 25-12 to 25-14

updating, 25-14

resource plans, 25-3

validating plan schema changes, 25-8

views, 25-21

database users

enrolling, 1-20

database writer, 4-11, 10-13

databases

administering, 1-1

altering availability, 3-8 to 3-9

auditing, 24-1

available installation types, 2-6

backing up

after creation of, 1-20

full backups, 2-11

control files of, 5-2

creating, 2-1 to 2-26, 7-6

opening and, 1-20

troubleshooting problems, 2-16

design of

implementing, 1-20

dropping, 2-16

global database name

about, 2-20

global database names

in a distributed system, 2-20

hardware evaluation, 1-18

logical structure of, 1-19

migration of, 2-4

mounting a database, 3-5

mounting to an instance, 3-8

names

about, 2-20

conflicts in, 2-20

opening

a closed database, 3-8

password encryption, 21-5

physical structure, 1-19

physical structure of, 1-19

planning, 1-19

production, 21-10, 21-11

read-only, opening, 3-9

renaming, 2-20, 5-5, 5-7

restricting access, 3-9

security. See also security.

shutting down, 3-10 to 3-13

specifying control files, 2-21

starting up, 3-2 to 3-7

structure of

distributed database, 1-19

suspending and resuming, 3-13

test, 21-10

tuning

archiving large databases, 7-19

responsibilities for, 1-21

types of, 2-6

user responsibilities, 1-3

viewing datafiles and redo log files, 2-16

datafiles

adding to a tablespace, 10-5

bringing online and offline, 10-7

checking associated tablespaces, 9-41

creating, 10-5

database administrators access, 1-4

default directory, 10-5

dropping, 9-22

NOARCHIVELOG mode, 10-8

fully specifying filenames, 10-5

guidelines for managing, 10-2 to 10-4

identifying filenames, 10-10

location, 10-4

minimum number of, 10-2

MISSING, 5-8

monitoring, 10-13

online, 10-8

privileges to rename, 10-9

privileges to take offline, 10-8

relocating, 10-9, 10-11

relocating, example, 10-10

renaming, 10-9, 10-11

renaming for single tables, 10-9

reusing, 10-5

size of, 10-4

storing separately from redo log files, 10-4

unavailable when database is opened, 3-4

verifying data blocks, 10-12

viewing

general status of, 10-13

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

DB_BLOCK_BUFFERS initialization parameter

setting before database creation, 2-22

DB_BLOCK_CHECKING initialization parameter, 20-4, 20-5

DB_BLOCK_CHECKSUM initialization parameter, 10-12

DB_BLOCK_SIZE initialization parameter

database buffer cache size and, 2-22

setting before creation, 2-22

DB_DOMAIN initialization parameter

setting before database creation, 2-20

DB_NAME initialization parameter

setting before database creation, 2-20

DB_VERIFY utility, 20-4, 20-5

DBA role, 1-6, 23-5

DBA. See database administrators.

DBA_DATA_FILES view, 9-40

DBA_INDEXES view

jobs in system, viewing, 8-14

DBA_JOBS view

DBA_JOBS_RUNNING

running jobs, viewing, 8-14

DBA_ROLLBACK_SEGS view, 11-13, 11-14

DBA_SEGMENTS view, 9-39

DBA_TAB_COLUMNS view

DBA_TABLES view

DBA_TABLESPACES view, 9-23

DBA_TEMP_FILES view, 9-40

DBA_TS_QUOTAS view, 9-40

DBA_USERS view, 9-40

DBCA. See Oracle Database Configuration Assistant

DBMS_DDL package

ANALYZE_OBJECT procedure

used for computing statistics, 19-9

DBMS_JOB package, 8-3

DBMS_LOGMNR package

ADD_LOGFILE procedure, 7-30

START_LOGMNR procedure, 7-31

DBMS_LOGMNR_D package

BUILD procedure, 7-28, 7-29

DBMS_REPAIR package, 20-2 to 20-14

examples, 20-8 to 20-14

limitations, 20-3

procedures, 20-2

using, 20-3 to 20-8

DBMS_RESOURCE_MANAGER package, 25-5, 25-6, 25-14, 25-15

procedures (table of), 25-3

DBMS_RESOURCE_MANAGER_PRIVS package, 25-5, 25-14

procedures (table of), 25-4

DBMS_SESSION package, 25-17

DBMS_SPACE package, 12-15

DBMS_SPACE_ADMIN package, 9-23 to 9-26

DBMS_STATS package

used for computing statistics, 19-8

DBMS_UTILITY package

ANALYZE_SCHEMA procedure

used for computing statistics, 19-9

DEALLOCATE UNUSED clause, 12-15

deallocating unused space, 12-14

DBMS_SPACE package, 12-15

DEALLOCATE UNUSED clause, 12-15

examples, 12-16

high water mark, 12-15

Decision Support Systems. See DSS

dedicated server processes, 4-2

trace files for, 4-15

default

audit options, 24-10

disabling, 24-13

default roles, 23-18

DEFAULT_CONSUMER_GROUP for Database Resource Manager, 25-11, 25-12, 25-15, 25-17

defaults

profile, 22-22

role, 22-20

tablespace quota, 22-17

user tablespaces, 22-16

deferred destination state, 7-13

DELETE_CATALOG_ROLE roll, 23-3

deleting table statistics, 19-4

dependencies

displaying, 19-31

destination states for archived redo logs, 7-12

destinations

archived redo logs

sample scenarios, 7-17

developers, application, 21-10

dictionary files

LogMiner and the, 7-28

dictionary protection mechanism, 23-2

dictionary-managed tablespaces, 9-5 to 9-6

directory service

See also enterprise directory service.

DISABLE ROW MOVEMENT clause, 15-8

disabled destination state

disabling audit options, 24-11, 24-12

disabling auditing, 24-13

disabling resource limits, 22-21

disconnections

auditing, 24-8

dispatcher processes, 4-6, 4-10, 4-13

distributed databases

parameter file location in, 3-16

running in ARCHIVELOG mode, 7-5

running in NOARCHIVELOG mode, 7-5

starting a remote instance, 3-7

distributing I/O, 2-26

DROP CLUSTER statement

CASCADE CONSTRAINTS option, 16-11

dropping cluster, 16-10

dropping cluster index, 16-10

dropping hash cluster, 17-9

INCLUDING TABLES option, 16-10

DROP COLUMN clause, 13-14

DROP LOGFILE MEMBER option

ALTER DATABASE statement, 6-16

DROP LOGFILE option

ALTER DATABASE statement, 6-15

DROP PARTITION clause, 15-16

DROP PROFILE statement, 22-25

DROP ROLE statement, 23-10

DROP ROLLBACK SEGMENT statement, 11-13

DROP SYNONYM statement, 18-15

DROP TABLE statement

about, 13-16

CASCADE CONSTRAINTS option, 13-16

for clustered tables, 16-11

DROP TABLESPACE statement, 9-23

DROP UNUSED COLUMNS clause, 13-15

DROP USER privilege, 22-20

DROP USER statement, 22-20

dropping an audit trail, 24-4

dropping columns from tables, 13-14 to 13-15

dropping profiles, 22-25

dropping users, 22-20

DUMP_ORPHAN_KEYS procedure, 20-3, 20-6, 20-7

example, 20-12

EMPHASIS resource allocation method, 25-8, 25-10

ENABLE ROW MOVEMENT clause, 15-8

enabled destination state

enabling resource limits, 22-21

encryption

database passwords, 21-5, 22-8

enterprise directory service, 21-7, 23-9

enterprise roles, 21-7, 22-11, 23-9

enterprise users, 21-7, 22-11, 23-9

errors

ORA-1628 through 1630, 19-28

snapshot too old, 11-6

trace files and, 4-15

when creating a database, 2-16

when creating control file, 5-9

while starting a database, 3-6

while starting an instance, 3-6

ESTIMATE STATISTICS option, 19-5

estimating size of tables, 13-4

estimating sizes

of tables, 13-4

exceptions

integrity constraints, 19-21

EXCHANGE PARTITION clause, 15-19

EXCHANGE SUBPARTITION clause, 15-20

EXECUTE_CATALOG_ROLE roll, 23-3

EXP_FULL_DATABASE role, 23-5

Export utility

about, 1-17

restricted mode and, 3-6

exporting jobs, 8-6

extents

allocating

clusters, 16-9

index creation, 14-5

tables, 13-13

data dictionary views for, 19-30

deallocating

clusters, 16-9

displaying free extents, 19-32

displaying information on, 19-32

by operating system, 22-10

external authentication

by network, 22-11

failures

media

multiplexed online redo logs, 6-5

fine-grained access control, 21-4

FIX_CORRUPT_BLOCKS procedure, 20-2, 20-7

example, 20-11

FOR PARTITION clause, 15-24

forcing a log switch, 6-16

with the ALTER SYSTEM statement, 6-16

free space

coalescing, 9-12

listing free extents, 19-32

tablespaces and, 9-41

FREELIST GROUPS parameter

description, 12-10

FREELISTS parameter

description, 12-11

function-based indexes, 14-11 to 14-13

functions

recompiling, 19-25

global authentication and authorization, 22-11

global database name, 2-20

global roles, 22-11, 23-9

global users, 22-11

GRANT CONNECT THROUGH clause

for multi-tier authorization, 22-14

GRANT OPTION

GRANT statement

object privileges, 23-11

SYSOPER/SYSDBA privileges, 1-13

system privileges and roles, 23-10

when takes effect, 23-17

granting privileges and roles

listing grants, 23-22

SYSOPER/SYSDBA privileges, 1-13

groups

redo log files

LOG_FILES initialization parameter, 6-10

hardware

evaluating, 1-18

hash clusters

hash functions

for hash cluster, 17-2

hash partitioning, 15-5

high water mark, 12-15

for a session, 22-3

historical tables

moving time window, 15-33

hybrid database environment

creating, 2-6

I/O

distributing, 2-26

IMP_FULL_DATABASE role, 23-5

implementing database design, 1-20

Import utility

about, 1-17

restricted mode and, 3-6

importing jobs, 8-6

inactive destination state

analyzing statistics, 19-3

INCLUDING clause, 13-21

index clusters. See clusters.

indexes

cluster indexes, 16-8, 16-10

correct tables and columns, 14-9

creating

after inserting table data, 14-3

explicitly, 14-9

unrecoverable, 14-6

disabling and dropping constraints and, 14-8

extent allocation for, 14-5

dropping, 14-16

estimating size, 14-5

explicitly creating, 14-9

function-based, 14-11 to 14-13

guidelines for managing, 14-2

INITRANS for, 14-4

limiting per table, 14-4

MAXTRANS for, 14-4

monitoring space use of, 14-16

parallelizing index creation, 14-6

partitioned, 15-2

see also partitioned indexes

privileges

separating from a table, 13-5

setting storage parameters for, 14-5

space used by, 14-16

specifying PCTFREE for, 12-4

SQL*Loader and, 14-4

tablespace for, 14-5

temporary segments and, 14-3

validating structure, 19-9

index-organized tables, 13-17 to 13-26

AS subquery, 13-20

creating, 13-19

description, 13-17

INCLUDING clause, 13-21

key compression, 13-22

maintaining, 13-23

overflow clause, 13-20

partitioning, 15-9

partitioning secondary indexes, 15-9

rebuilding with MOVE clause, 13-23

threshold value, 13-21

updating key column, 13-24

INITIAL storage parameter

cannot alter, 12-13

description, 12-9

when deallocating unused space, 12-15

initialization parameter files

character set of, 3-15

creating, 2-9

creating for database creation, 2-9

distributed databases, 3-16

editing before database creation, 2-19

individual parameter names, 2-20

initialization parameters

LOG_ARCHIVE_BUFFER_SIZE, 7-21, 7-22

LOG_ARCHIVE_BUFFERS, 7-21, 7-22

LOG_ARCHIVE_DEST_n, 7-10

LOG_ARCHIVE_DEST_STATE_n, 7-12

LOG_ARCHIVE_MAX_PROCESSES, 7-20

LOG_ARCHIVE_MIN_SUCCEED_DEST, 7-16

LOG_ARCHIVE_START, 7-8, 7-14

LOG_ARCHIVE_TRACE, 7-25

LOG_BLOCK_CHECKSUM, 6-17

LOG_FILES, 6-10

multi-threaded server and, 4-5

RESOURCE_MANAGER_PLAN, 25-18

INITRANS storage parameter

guidelines for setting, 12-7

INSERT privilege

granting, 23-12

revoking, 23-14

installation

Oracle8i, 1-18

tuning recommendations for, 2-25

instances

aborting, 3-13

shutting down immediately, 3-12

starting up, 3-2 to 3-7

integrity constraints

See also constraints

disabling

effects on indexes, 14-8

dropping

effects on indexes, 14-8

dropping and disabling, 14-8

dropping tablespaces and, 9-23

INTERNAL

alternatives to, 1-8

OSOPER and OSDBA, 1-8

security for, 21-8

INTERNAL date function

executing jobs and, 8-8

INTERNAL username

connecting for shutdown, 3-10

invalid destination state

submitting jobs to, 8-4 to 8-8

IOT. See index organized tables.

job queues

altering jobs, 8-11

broken jobs, 8-12

DBMS_JOB package, 8-3

executing jobs in, 8-9

locks, 8-9

removing jobs from, 8-10

SNP processes, 8-2

terminating jobs, 8-14

viewing information, 8-14

JOB_QUEUE_INTERVAL initialization parameter
, 8-3

JOB_QUEUE_PROCESSES initialization parameter, 8-3

jobs

altering, 8-11

broken, 8-12

database links and, 8-8

environment, recording when submitted, 8-5

executing, 8-9

exporting, 8-6

forcing to execute, 8-13

importing, 8-6

INTERNAL date function and, 8-8

job definition, 8-7

job number, 8-7

ownership of, 8-7

removing from job queue, 8-10

running broken jobs, 8-13

submitting to job queue, 8-4

terminating, 8-14

trace files for job failures, 8-9

troubleshooting, 8-9

join views

definition, 18-3

DELETE statements, 18-8

key-preserved tables in, 18-6

key compression, 13-22

key-preserved tables

in join views, 18-6

keys

cluster, 16-2, 16-4, 16-5

LICENSE_MAX_SESSIONS initialization parameter

changing while instance runs, 22-4

setting, 22-4

changing while database runs, 22-6

LICENSE_MAX_USERS initialization parameter

setting, 22-6

LICENSE_SESSION_WARNING initialization parameter

changing while instance runs, 22-4

LICENSE_SESSIONS_WARNING initialization parameter

setting, 22-4

licensing

complying with license agreement, 2-23, 22-2

concurrent usage, 22-3

named user, 22-2, 22-5

number of concurrent sessions, 2-23

privileges for changing named user limits, 22-6

privileges for changing session limits, 22-5

session-based, 22-2

viewing limits, 22-6

limits

concurrent usage, 22-3

session, high water mark, 22-3

LIST CHAINED ROWS option, 19-9

LOBS

storage parameters for, 12-12

locally managed tablespaces, 9-6 to 9-8

DBMS_SPACE_ADMIN package, 9-23

detecting and repairing defects, 9-23

temporary

locks

log sequence number

log switches

log sequence numbers, 6-5

multiplexed redo log files and, 6-7

privileges, 6-16

waiting for archiving to complete, 6-7

log writer process (LGWR), 4-11

multiplexed redo log files and, 6-6

online redo logs available for use, 6-3

trace file monitoring, 4-16

trace files and, 6-6

writing to online redo log files, 6-3

LOG_ARCHIVE_BUFFER_SIZE initialization parameter, 7-22

LOG_ARCHIVE_BUFFERS initialization parameter, 7-22

setting, 7-22

LOG_ARCHIVE_DEST initialization parameter

specifying destinations using, 7-10

LOG_ARCHIVE_DEST_n initialization parameter, 7-10

REOPEN option, 7-19

LOG_ARCHIVE_DUPLEX_DEST initialization parameter

specifying destinations using, 7-10

LOG_ARCHIVE_MAX_PROCESSES initialization parameter, 7-20

LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter, 7-16

LOG_ARCHIVE_START initialization parameter, 7-8

bad param destination state, 7-14

setting, 7-8

LOG_ARCHIVE_TRACE initialization parameter, 7-25

LOG_BLOCK_CHECKSUM initialization parameter

enabling redo block checking with, 6-17

LOG_FILES initialization parameter

number of log files, 6-10

logical structure of a database, 1-19

LogMiner utility, 7-26 to 7-35

dictionary file, 7-28

using the, 7-30

using to analyze archived redo logs, 7-26

LOW_GROUP for Database Resource Manager, 25-12, 25-20

managing datafiles, 10-1 to 10-14

managing job queues, 8-3 to 8-14

managing roles, 23-4

managing sequences, 18-12 to 18-13

managing synonyms, 18-14 to 18-15

managing tables, 13-1 to 13-26

managing views, 18-2 to 18-12

manual archiving

in ARCHIVELOG mode, 7-9

MAX_DUMP_FILE_SIZE initialization parameter, 4-16

MAX_ENABLED_ROLES initialization parameter

enabling roles and, 23-18

MAXDATAFILES parameter

MAXEXTENTS storage parameter

description, 12-10

setting for the data dictionary, 19-27

MAXINSTANCES parameter

CREATE DATABASE statement, 6-10

MAXLOGFILES option

MAXLOGFILES parameter

MAXLOGHISTORY parameter

MAXLOGMEMBERS parameter

CREATE DATABASE statement, 6-10

MAXTRANS storage parameter

guidelines for setting, 12-7

media recovery

effects of archiving on, 7-4

memory

viewing per user, 22-29

MERGE PARTITIONS clause, 15-21

migration

database migration, 2-4

MINEXTENTS storage parameter

deallocating unused space, 12-15

cannot alter, 12-13

description, 12-10

mirrored control files, 5-3

mirrored files

mirroring

MISSING datafiles, 5-8

MODIFY DEFAULT ATTRIBUTES clause, 15-23, 15-24

MODIFY DEFAULT ATTRIBUTES FOR PARTITION clause, 15-23

MODIFY PARTITION clause, 15-24, 15-26, 15-28

MODIFY SUBPARTITION clause, 15-25

monitoring datafiles, 10-13

monitoring tablespaces, 10-13

MOUNT option

STARTUP statement, 3-5

mounting a database, 3-5

MOVE PARTITION clause, 15-24, 15-25

MOVE SUBPARTITION clause, 15-24, 15-26

moving control files, 5-5

MTS. See multi-threaded server.

MTS_DISPATCHERS initialization parameter

setting initially, 4-6

MTS_SERVERS initialization parameter

initial setting, 4-7

multiplexed control files

importance of, 5-3

multiplexing

archived redo logs, 7-10

control files, 5-3

redo log files, 6-5

groups, 6-6

multi-threaded server, 4-3

adjusting number of dispatchers, 4-8

enabling and disabling, 4-8

initialization parameters, 4-5

OS role management restrictions, 23-22

restrictions on OS role authorization, 23-9

setting initial number of dispatchers, 4-6

setting initial number of servers, 4-7

setting minimum number of servers, 4-8

views, 4-10

multi-tier authentication, 22-13

OCI interface, 22-14

multi-tier authorization, 22-13

multi-tier environments

auditing clients, 24-11

named user limits, 22-5

setting initially, 2-24

nested tables

storage parameters for, 12-12

Net8

service names in, 7-15

transmitting archived logs via, 7-15

network

authentication, 22-11

network authentication, 22-11

NEXT storage parameter

altering, 12-13

description, 12-9

setting for the data dictionary, 19-27

NOARCHIVELOG mode

taking datafiles offline in, 10-8

NOAUDIT statement

disabling audit options, 24-11

disabling default object audit options, 24-13

disabling object auditing, 24-12

disabling statement and privilege auditing, 24-12

NOMOUNT option

STARTUP statement, 3-4

normal transmission mode

definition, 7-14

O7_DICTIONARY_ACCESSIBILITY initialization parameter, 23-3

objects

See also schema objects

offline rollback segments

bringing online, 11-10

when to use, 11-10

offline tablespaces

priorities, 9-15

rollback segments and, 11-10

taking offline, 9-15

OLTP

database environment, 2-6

online redo log, 6-2

online rollback segments

bringing rollback segments online, 11-10

taking offline, 11-11

Online Transaction Processing. See OLTP

opening a database

after creation, 1-20

operating systems

accounts, 23-20

auditing with, 24-2

authentication, 22-10, 23-18

database administrators requirements for, 1-4

deleting datafiles, 9-23

enabling and disabling roles, 23-21

renaming and relocating files, 10-9

role identification, 23-20

roles and, 23-18

security in, 21-3

Optimal Flexible Architecture (OFA), 2-5

OPTIMAL storage parameter, 12-11

rollback segments, 11-5, 11-6, 11-8

Oracle

installing, 1-18

release numbers, 1-21

Oracle blocks, 2-22

Oracle Call Interface

multi-tier authentication, 22-14

Oracle database

available types, 2-6

creating for a hybrid environment, 2-6

creating for an OLTP environment, 2-6

custom database overview, 2-6

Custom installation type, 2-6

Minimal installation type, 2-6

starter database overview, 2-6

Typical installation type, 2-6

Oracle Database Configuration Assistant

advantages, 2-5

creating a hybrid database environment, 2-6

creating an OLTP database environment, 2-6

defined, 2-4

methods of creation, 2-6

modes for creation, 2-5

Oracle Enterprise Manager, 3-4

Oracle Parallel Server

allocating extents for cluster, 16-9

datafile upper bound for instances, 10-3

licensed session limit and, 2-24

limits on named users and, 22-6

named users and, 2-24

rollback segments, 11-3

sequence numbers and, 18-13

session and warning limits, 22-3

specifying thread for archiving, 7-10

threads of online redo log, 6-2

V$THREAD view, 6-18

Oracle server

complying with license agreement, 22-2

Oracle Universal Installer, 2-4, 2-5

ORAPWD utility, 1-9

OS authentication, 1-7

OS_ROLES parameter

operating-system authorization and, 23-9

REMOTE_OS_ROLES and, 23-22

using, 23-20

OTHER_GROUPS for Database Resource Manager, 25-7, 25-9, 25-11, 25-14, 25-20

packages

parallel query option

number of server processes, 4-17

parallelizing index creation, 14-6

parallelizing table creation, 13-4

query servers, 4-17

PARALLEL_DEGREE_LIMIT_ABSOLUTE resource allocation method, 25-10

PARALLEL_MAX_SERVERS initialization parameter, 4-18

PARALLEL_MIN_SERVERS initialization parameter, 4-18

PARALLEL_SERVER_IDLE_TIME initialization parameter, 4-18

parameter files

See also initialization parameter files.

PARTITION BY HASH clause, 15-10

PARTITION BY RANGE clause, 15-8, 15-11

PARTITION clause, 15-8, 15-10, 15-11

partition views

converting to partitioned table, 15-34

partitioned indexes, 15-1 to 15-35

adding partitions, 15-15

creating local index on composite partitioned table, 15-11

creating local index on hash partitioned table, 15-10

creating range partitions, 15-9

description, 15-2

dropping partitions, 15-18

global, 15-3

local, 15-3

maintenance operations, 15-12 to 15-32

table of, 15-12

modifying partition default attributes, 15-23

modifying real attributes of partitions, 15-25

moving partitions, 15-26

rebuilding index partitions, 15-27

renaming index partitions/subpartitions, 15-28

secondary indexes on index-organized tables, 15-9

splitting partitions, 15-30

partitioned tables, 15-1 to 15-35

adding partitions, 15-14

adding subpartitions, 15-15

coalescing partitions, 15-16

converting partition views, 15-34

creating composite partitions and subpartitions, 15-11

creating hash partitions, 15-10

creating range partitions, 15-8, 15-9

description, 15-2

DISABLE ROW MOVEMENT, 15-8

dropping partitions, 15-16

ENABLE ROW MOVEMENT, 15-8

exchanging partitions, 15-19

exchanging subpartitions, 15-20

global indexes on, 15-3

index-organized tables, 15-9

local indexes on, 15-3

maintenance operations, 15-12 to 15-32

table of, 15-12

marking indexes UNUSABLE, 15-14, 15-16, 15-17, 15-19, 15-21, 15-24, 15-25, 15-26, 15-29, 15-31

merging partitions, 15-21

modifying partition default attributes, 15-23

modifying real attributes of partitions, 15-24

modifying real attributes of subpartitions, 15-25

modifying subpartition default attributes, 15-23

moving partitions, 15-25

moving subpartitions, 15-26

rebuilding index partitions, 15-27

renaming partitions, 15-28

renaming subpartitions, 15-28

splitting partitions, 15-29

truncating partitions, 15-30

truncating subpartitions, 15-32

partitioning

composite, 15-6

creating partitions, 15-7 to 15-11

hash, 15-5

indexes, 15-2

See also partitioned indexes

index-organized tables, 15-9

maintaining partitions, 15-12 to 15-32

methods, 15-3

range, 15-3

tables, 15-2

See also partitioned tables

partitions

See also partitioned tables.

See also partitioned indexes.

PARTITIONS clause, 15-10

passwords

authentication file for, 1-9

changing for roles, 23-7

encrypted

database, 21-5

encryption, 22-8

initial for SYS and SYSTEM, 1-5

password file, 1-12

creating, 1-9

OS authentication, 1-7

removing, 1-16

state of, 1-16

privileges for changing for roles, 23-7

privileges to alter, 22-18

roles, 23-8

security policy for users, 21-5

setting REMOTE_LOGIN_PASSWORD parameter, 1-11

user authentication, 22-8

PCTFREE parameter

guidelines for setting, 12-3

block overhead and, 12-6

clustered tables, 12-4

clusters, used in, 16-5

examples of use, 12-6

indexes, 12-4

non-clustered tables, 12-4

PCTUSED, use with, 12-6

usage, 12-2

PCTINCREASE storage parameter

altering, 12-13

setting for the data dictionary, 19-27

PCTINCREASEstorage parameter

description, 12-9

PCTUSED parameter

guidelines for setting, 12-5

block overhead and, 12-6

clusters, used in, 16-5

examples of use, 12-6

PCTFREE, use with, 12-6

usage, 12-4

pending area for Database Resource Manager plans, 25-7 to 25-10

validating plan schema changes, 25-8

performance

location of datafiles and, 10-4

tuning archiving, 7-19

PFILE option

physical structure of a database, 1-19

PL/SQL

program units

replaced views and, 18-12

plan schemas for Database Resource Manager, 25-7, 25-11, 25-13, 25-18, 25-22

examples, 25-18

validating plan changes, 25-8

planning

database creation, 2-2

relational design, 1-19

the database, 1-19

predefined roles, 1-6

prerequisites

for creating a database, 2-3

PRIMARY KEY constraint

dropping associated indexes, 14-17

enabling on creation, 14-9

foreign key references when dropped, 19-19

indexes associated with, 14-9

storage of associated indexes, 14-10

private rollback segments, 11-3, 11-7

taking offline, 11-12

private synonyms, 18-14

granting privileges and roles

specifying ALL, 23-4

revoking privileges and roles

specifying ALL, 23-4

privileges, 23-2

See also system privileges.

adding datafiles to a tablespace, 10-5

adding redo log groups, 6-11

altering

indexes, 14-15

named user limit, 22-6

passwords, 22-19

role authentication, 23-7

sequences, 18-12

tables, 13-11

users, 22-18

analyzing objects, 19-4

application developers and, 21-10

audit object, 24-11

auditing system, 24-10

auditing use of, 24-8

bringing datafiles offline and online, 10-8

cascading revokes, 23-15

creating

creating rollback segments, 11-6

creating users, 22-15

database administrator, 1-4

disabling automatic archiving, 7-8

dropping

indexes, 14-16

online redo log members, 6-15

redo log groups, 6-14

dropping profiles, 22-25

dropping rollback segments, 11-13

enabling and disabling resource limits, 22-21

enabling and disabling triggers, 19-13

enabling automatic archiving, 7-7

for changing session limits, 22-5

forcing a log switch, 6-16

granting

about, 23-10

object privileges, 23-11

required privileges, 23-11

system privileges, 23-10

grouping with roles, 23-4

individual privilege names, 23-2

listing grants, 23-23

manually archiving, 7-9

object, 23-4

on selected columns, 23-14

policies for managing, 21-6

recompiling packages, 19-25

recompiling procedures, 19-25

recompiling views, 19-24

renaming

datafiles of a tablespace, 10-9

datafiles of several tablespaces, 10-12

objects, 19-3

redo log members, 6-12

replacing views, 18-11

RESTRICTED SESSION system privilege, 3-6

revoking, 23-14

GRANT OPTION, 23-14

object privileges, 23-16

system privileges, 23-13

revoking object, 23-14

revoking object privileges, 23-14

setting resource costs, 22-24

system, 23-2

taking tablespaces offline, 9-15

truncating, 19-11

See also system privileges.

procedures

recompiling, 19-25

process monitor, 4-12

processes

See also server processes

SNP background processes, 8-2

PROCESSES initialization parameter

PRODUCT_COMPONENT_VERSION view, 1-22

profiles, 22-21

altering, 22-23

assigning to users, 22-23

creating, 22-22

default, 22-22

disabling resource limits, 22-21

dropping, 22-25

enabling resource limits, 22-21

listing, 22-25

managing, 22-21

privileges for dropping, 22-25

privileges to alter, 22-23

privileges to set resource costs, 22-24

PUBLIC_DEFAULT, 22-22

setting a limit to null, 22-23

viewing, 22-28

program global area (PGA)

effect of MAX_ENABLED_ROLES on, 23-18

proxies

auditing clients of, 24-11

multi-tier authentication and authorization, 22-13

proxy servers

auditing clients, 24-11

PROXY_USERS view, 22-14

public rollback segments, 11-3, 11-7

taking offline, 11-12

public synonyms, 18-14

PUBLIC user group

granting and revoking privileges to, 23-16

procedures and, 23-17

PUBLIC_DEFAULT profile

dropping profiles and, 22-25

using, 22-22

query server process

about, 4-17

queue monitor processes, 4-13

quotas

listing, 22-25

revoking from users, 22-17

setting to zero, 22-17

tablespace, 22-17

tablespace quotas, 9-3

temporary segments and, 22-17

unlimited, 22-17

viewing, 22-27

range partitioning, 15-3

read-only database

opening, 3-9

read-only tablespaces

datafiles, 10-8

read-only tablespaces, see tablespaces, read-only

REBUILD PARTITION clause, 15-26, 15-27

REBUILD SUBPARTITION clause, 15-27

REBUILD UNUSABLE LOCAL INDEXES clause, 15-28

REBUILD_FREELISTS procedure, 20-3, 20-6, 20-8

example, 20-13

RECOVER option

STARTUP statement, 3-6

recoverer process, 4-12

recovery

creating new control files, 5-5

database startup with automatic, 3-6

instance startup with automatic, 3-6

Recovery Manager

starting an instance, 3-3

redo log files

active (current), 6-4

archived

advantages of, 7-2

contents of, 7-2

log switches and, 6-5

archived redo log files, 7-6

archived redo logs, 7-3

available for use, 6-3

creating

groups and members, 6-11

creating members, 6-11

distributed transaction information in, 6-3

groups, 6-6

creating, 6-11

decreasing number, 6-10

dropping, 6-14

LOG_FILES initialization parameter, 6-10

members, 6-6

threads, 6-2

how many in redo log, 6-9

inactive, 6-4

legal and illegal configurations, 6-7

LGWR and the, 6-3

log sequence numbers of, 6-5

maximum number of, 6-10

mirrored

log switches and, 6-7

multiplexed

diagrammed, 6-6

if all inaccessible, 6-7

multiplexing, 6-5

groups, 6-6

if some members inaccessible, 6-7

online, 6-2

recovery use of, 6-2

requirement of two, 6-3

threads of, 6-2

online redo log, 6-1

planning the, 6-5, 6-10

privileges

adding groups and members, 6-11

redo entries, 6-2

requirements, 6-7

verifying blocks, 6-17

viewing, 2-16

redo logs

See also online redo log

do not back up, 7-2

storing separately from datafiles, 10-4

unavailable when database is opened, 3-4

redo records, 6-2

REFERENCES privilege

CASCADE CONSTRAINTS option, 23-15

revoking, 23-14, 23-15

relational design

planning, 1-19

release number format, 1-21

releases, 1-21

checking the Oracle database release number, 1-22

relocating control files, 5-5

remote connections, 1-16

connecting as SYSOPER/SYSDBA, 1-14

password files, 1-9

REMOTE_LOGIN_PASSWORDFILE initialization parameter, 1-11

REMOTE_OS_AUTHENT initialization parameter

setting, 22-10

REMOTE_OS_ROLES initialization parameter

setting, 23-9, 23-22

RENAME PARTITION clause, 15-28

RENAME statement, 19-3

RENAME SUBPARTITION clause, 15-28

renaming control files, 5-5

REOPEN option

LOG_ARCHIVE_DEST_n initialization parameter, 7-19

repairing data block corruption

DBMS_REPAIR, 20-2 to 20-14

resource allocation methods, 25-3

CPU resource, 25-10

EMPHASIS, 25-8, 25-10

limiting degree of parallelism, 25-10

PARALLEL_DEGREE_LIMIT_ABSOLUTE, 25-10

ROUND-ROBIN, 25-11

resource consumer groups, 25-2

resource limits

altering in profiles, 22-23

assigning with profiles, 22-23

costs and, 22-24

creating profiles and, 22-22

disabling, 22-21

enabling, 22-21

privileges to enable and disable, 22-21

privileges to set costs, 22-24

profiles, 22-21

PUBLIC_DEFAULT profile and, 22-22

setting to null, 22-23

resource plan directives, 25-3, 25-8

CPU_Pn, 25-12

deleting, 25-14

PARALLEL_DEGREE_LIMIT_P1, 25-8, 25-13

parameters, 25-12

specifying, 25-12 to 25-14

updating, 25-14

resource plans, 25-3

RESOURCE role, 23-5

RESOURCE_LIMIT initialization parameter

enabling and disabling limits, 22-21

RESOURCE_MANAGER_PLAN initialization parameter, 25-18

resources

profiles, 22-21

responsibilities

of a database administrator, 1-2

of database users, 1-3

RESTRICT OPTION

STARTUP statement, 3-6

RESTRICTED SESSION system privilege

connecting to database, 3-6

connecting to database., 3-6

restricted mode and, 3-6

session limits and, 22-3

resuming a database, 3-13

REVOKE CONNECT THROUGH clause

revoking multi-tier authorization, 22-14

REVOKE statement, 23-13

when takes effect, 23-17

revoking privileges and roles

on selected columns, 23-14

REVOKE statement, 23-13

when using operating-system roles, 23-21

RMAN. See Recovery Manager.

roles

ADMIN OPTION and, 23-11

application developers and, 21-11

authorization, 23-8

authorized by enterprise directory service, 23-9

backward compatibility, 23-5

changing authorization for, 23-7

changing passwords, 23-7

CONNECT role, 23-5

database authorization, 23-8

DBA role, 1-6, 23-5

default, 22-20, 23-18

disabling, 23-17

dropping, 23-10

enabling, 23-17

enterprise, 22-11, 23-9

EXP_FULL_DATABASE, 23-5

global, 22-11, 23-9

global authorization, 23-9

GRANT OPTION and, 23-12

GRANT statement, 23-21

granting

about, 23-10

grouping with roles, 23-4

IMP_FULL_DATABASE, 23-5

listing, 23-25

listing grants, 23-24

listing privileges and roles in, 23-26

management using the operating system, 23-18

managing, 23-4

maximum, 23-18

multi-byte characters

in names, 23-7

multi-byte characters in passwords, 23-8

multi-threaded server and, 23-9

network authorization, 23-9

operating system granting of, 23-20, 23-21

operating-system authorization, 23-8

OS management and the multi-threaded server, 23-22

passwords for enabling, 23-8

predefined, 1-6, 23-5

privileges

changing authorization method, 23-7

changing passwords, 23-7

for creating, 23-6

for dropping, 23-10

granting system privileges or roles, 23-10

RESOURCE role, 23-5

REVOKE statement, 23-21

revoking, 23-13

revoking ADMIN OPTION, 23-14

security and, 21-6

SET ROLE statement, 23-21

unique names for, 23-7

without authorization, 23-7

rollback segments

acquiring automatically, 11-3, 11-11

acquiring on startup, 2-23

allocating, 2-25

altering storage parameters, 11-9

AVAILABLE, 11-10

bringing

online, 11-10

online automatically, 11-11

PARTLY AVAILABLE segment online, 11-10

bringing online when new, 11-7

checking if offline, 11-11

choosing how many, 2-25

choosing size for, 2-25

creating, 11-6 to 11-8

creating public and private, 11-3

displaying

information on, 11-14

PENDING OFFLINE segments, 11-16

displaying names of all, 11-15

dropping, 11-10, 11-12

equally sized extents, 11-5

explicitly assigning transactions to, 11-12

guidelines for managing, 11-2 to 11-6

INITIAL, 11-5, 11-8

initial, 11-2

invalid status, 11-13

listing extents in, 19-32

location of, 11-6

making available for use, 11-9

MINEXTENTS, 11-5, 11-8

NEXT, 11-5, 11-8

OFFLINE, 11-10

offline, 11-10

offline status, 11-11

online status, 11-11

OPTIMAL, 11-5, 11-6, 11-8

PARTLY AVAILABLE, 11-10

PCTINCREASE, 11-5, 11-8

PENDING OFFLINE, 11-12

private, 11-3, 11-7

privileges

for dropping, 11-13

required to create, 11-6

public, 11-3, 11-7

setting size of, 11-4

shrinking size of, 11-9

status for dropping, 11-13

status or state, 11-10

storage parameters, 11-7

taking offline, 11-11

taking tablespaces offline and, 9-17

using multiple, 11-2

ROLLBACK_SEGMENTS initialization parameter

adding rollback segments to, 11-7, 11-11

dropping rollback segments, 11-13

online at instance startup, 11-3, 11-4

ROUND-ROBIN resource allocation method, 25-11

row movement clause for partitioned tables, 15-8

rows

chaining across blocks, 12-4, 19-9

schema objects

cascading effects on revoking, 23-16

creating multiple objects, 19-2

default audit options, 24-10

default tablespace for, 22-16

dependencies between, 19-23

disabling audit options, 24-12

enabling audit options on, 24-10

granting privileges, 23-11

in a revoked tablespace, 22-17

listing by type, 19-30

listing information, 19-29

owned by dropped users, 22-20

privileges to access, 23-4

privileges to rename, 19-3

privileges with, 23-4

renaming, 19-3

revoking privileges, 23-14

schema-independent users, 22-12

SCN. See system change number.

SCN, see system change number

Secure Sockets Layer, 21-2, 22-7, 22-12

security

segments

data dictionary, 19-27

deallocating unused space, 12-14

displaying information on, 19-32

monitoring rollback, 11-14

rollback. See rollback segments.

temporary

storage parameters, 12-13

SELECT_CATALOG_ROLE roll, 23-3

sequences

Parallel Server and, 18-13

privileges for altering, 18-12

privileges for creating, 18-12

privileges for dropping, 18-13

server processes

session limits, license

setting initially, 2-23

sessions

auditing connections and disconnections, 24-8

limits per instance, 22-3

listing privilege domain of, 23-24

number of concurrent sessions, 2-23

Parallel Server session limits, 2-24

setting maximum for instance, 22-4

setting warning limit for instance, 22-4

terminating, 4-20 to 4-23

viewing current number and high water mark, 22-6

viewing memory use, 22-29

sessions, user

active, 4-22

inactive, 4-22

marked to be terminated, 4-22

terminating, 4-20

viewing terminated sessions, 4-22

SET ROLE statement

how password is set, 23-8

used to enable/disable roles, 23-17

when using operating-system roles, 23-21

SET TRANSACTION statement

USE ROLLBACK SEGMENT option, 11-12

setting archive buffer parameters, 7-21

SGA. See System Global Area., 2-22

shared pool

ANALYZE statement and, 19-8

shared server processes, 4-2

trace files for, 4-15

shared SQL areas

ANALYZE statement and, 19-8

SHUTDOWN statement

ABORT option, 3-13

IMMEDIATE option, 3-12

NORMAL option, 3-11

shutting down an instance

aborting the instance, 3-13

connecting and, 3-10

connecting as INTERNAL, 3-10

example of, 3-12

immediately, 3-12

normally, 3-11

single-table hash clusters, 17-5

sizes

estimating for tables, 13-4

SKIP_CORRUPT_BLOCKS procedure, 20-3, 20-7

example, 20-14

snapshots

too old

OPTIMAL storage parameter and, 11-6

SNP background processes

about, 8-2

managing job queues, 8-3 to 8-14

starting, 8-3

viewing job queue information, 8-14

SORT_AREA_SIZE initialization parameter

index creation and, 14-3

space management

data blocks, 12-2 to 12-6

datatypes, space requirements, 12-18

deallocating unused space, 12-14

setting storage parameters, 12-8 to 12-13

specifying destinations

for archived redo logs, 7-10

specifying multiple ARCH processes, 7-20

SPLIT PARTITION clause, 15-14, 15-29

SQL statements

disabling audit options, 24-12

enabling audit options on, 24-10

SQL*Loader

about, 1-17

indexes and, 14-4

SQL*Plus

starting, 3-2

starting an instance, 3-3

SQL_TRACE initialization parameter

trace files and, 4-15

SSL. See Secure Sockets Layer.

STALE status

of redo log members, 6-15

standby transmission mode

definition of, 7-14

Net8 and, 7-15

RFS processes and, 7-15

starter database

overview, 2-6

starting a database

exclusive mode, 3-7

forcing, 3-6

Oracle Enterprise Manager, 3-4

when control files unavailable, 3-4

when redo logs unavailable, 3-4

starting an instance

automatically at system startup, 3-7

database closed and mounted, 3-5

database name conflicts and, 2-20

enabling automatic archiving, 7-8

examples of, 3-7

exclusive mode, 3-7

forcing, 3-6

mounting and opening the database, 3-5

normally, 3-5

Oracle Enterprise Manager, 3-4

parallel mode, 3-7

recovery and, 3-6

Recovery Manager, 3-3

remote instance startup, 3-7

restricted mode, 3-5

SQL*Plus, 3-3

when control files unavailable, 3-4

when redo logs unavailable, 3-4

without mounting a database, 3-4

STARTUP statement

MOUNT option, 3-5

NOMOUNT option, 2-10, 3-4

RECOVER option, 3-6

RESTRICT option, 3-6

specifying database name, 3-3

altering tablespaces, 9-12

statistics

deleting, 19-4

updating, 19-4

storage

quotas and, 22-17

revoking tablespaces and, 22-17

unlimited quotas, 22-17

STORAGE clause

See also storage parameters

storage parameters

for the data dictionary, 19-27

applicable objects, 12-8

data dictionary, 19-26

default, 12-8

example, 12-13

OPTIMAL (in rollback segments), 11-6

PCTFREE, 13-12

PCTUSED, 13-12

precedence of, 12-13

rollback segments, 11-7

setting, 12-8 to 12-13

SYSTEM rollback segment, 11-9

temporary segments, 12-13

STORE IN clause, 15-11

stored procedures

privileges for recompiling, 19-25

using privileges granted to PUBLIC, 23-17

stream

tape drive, 7-22

SUBPARTITION BY HASH clause, 15-11

SUBPARTITION clause, 15-11, 15-15, 15-30

subpartitions, 15-2

SUBPARTITIONS clause, 15-11, 15-15, 15-30

suspending a database, 3-13

SWITCH LOGFILE option

ALTER SYSTEM statement, 6-16

synonyms

creating, 18-14

displaying dependencies of, 19-31

privileges for creating, 18-14

dropping, 18-15

managing, 18-14 to 18-15

private, 18-14

privileges for dropping, 18-14

public, 18-14

SYS account

initial password, 1-5

objects owned, 1-5

policies for protecting, 21-8

privileges, 1-5

user, 1-5

SYS_GROUP for Database Resource Manager, 25-12, 25-20

SYS.AUD$ table

audit trail, 24-2

creating and deleting, 24-4

SYSOPER/SYSDBA privileges

adding users to the password file, 1-12

connecting with, 1-14

determining who has privileges, 1-14

granting and revoking, 1-13

SYSTEM account

initial password, 1-5

objects owned, 1-5

policies for protecting, 21-8

system change number

using V$DATAFILE to view information about, 10-14

when assigned, 6-2

System Global Area, 2-22

determining buffers in cache, 2-22

system monitor, 4-12

system privileges, 23-2

ADMINISTER_RESOURCE_MANAGER, 25-3

SYSTEM rollback segment

altering storage parameters of, 11-9

SYSTEM tablespace

cannot drop, 9-22

initial rollback segment, 11-2

non-data dictionary tables and, 13-3

restrictions on taking offline, 10-7

when created, 9-4

SYSTEM_PLAN for Database Resource Manager, 25-10, 25-12, 25-20

tables

allocating extents, 13-13

altering, 13-11, 13-12

analyzing statistics, 19-3

clustered (hash). See hash clusters

clustered (index). See clusters.

creating, 13-9

designing before creating, 13-2

dropping, 13-16

dropping columns, 13-14

marking unused, 13-14

remove unused columns, 13-15

removing, 13-14

estimating size, 13-4

guidelines for managing, 13-1, 13-6, 13-26

hash clustered. See hash clusters

historical

moving time windows, 15-33

increasing column length, 13-11

index-organized, 13-17 to 13-26

key-preserved, 18-6

limiting indexes on, 14-4

location of, 13-3

managing, 13-1

parallelizing creation of, 13-4

partitioned, 15-2 to 15-35

see also partitioned tables

privileges for creation, 13-9

privileges for dropping, 13-15

privileges to alter, 13-11

separating from indexes, 13-5

specifying PCTFREE for, 12-4

specifying tablespace, 13-3

SYSTEM tablespace and, 13-3

temporary, 13-10

temporary space and, 13-6

transaction parameters, 13-3

truncating, 19-10

UNRECOVERABLE, 13-4

validating structure, 19-9

tablespace set, 9-29

tablespaces

adding datafiles, 10-5

altering storage settings, 9-12

assigning defaults for users, 22-16

assigning user quotas, 9-3

checking default storage parameters, 9-40

coalescing free space, 9-12

default quota, 22-17

dictionary managed, 9-5 to 9-6

dropping, 9-22

guidelines for managing, 9-2

listing files of, 9-41

listing free space in, 9-41

locally managed, 9-6 to 9-8

DBMS_SPACE_ADMIN package, 9-23

detecting and repairing defects, 9-23

privileges for creating, 9-4

privileges to take offline, 9-15

quotas

assigning, 9-3

quotas for users, 22-17

read-only

making read-only, 9-18

making writable, 9-20

on a WORM device, 9-21

revoking from users, 22-17

setting default storage parameters, 12-11

setting default storage parameters for, 9-3

SYSTEM tablespace, 9-4

taking offline normal, 9-15

taking offline temporarily, 9-16

temporary

assigning to users, 22-16

creating, 9-8

for creating large indexes, 14-3

for sort segments, 13-6

transportable, 9-27 to 9-39

unlimited quotas, 22-17

using multiple, 9-2

viewing quotas, 22-27

tape drives

streaming for archiving, 7-22

tempfiles, 9-9

temporary segments

index creation and, 14-3

temporary space

allocating, 13-6

temporary tables, 13-10

temporary tablespaces, see tablespaces, temporary

terminating user sessions

active sessions, 4-22

identifying sessions, 4-21

inactive session, example, 4-22

inactive sessions, 4-22

threads

online redo log, 6-2

TNSNAMES.ORA file, 7-11

trace files

job failures and, 8-9

location of, 4-16

log writer, 4-16

log writer process and, 6-6

size of, 4-16

using, 4-15, 4-16

when written, 4-16

tracing

archivelog process, 7-25

transactions

assigning to specific rollback segment, 11-12

rollback segments and, 11-12

TRANSACTIONS initialization parameter

using, 11-2

TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameter

using, 11-2

transmitting archived redo logs, 7-14

in normal transmission mode, 7-14

in standby transmission mode, 7-14

transportable tablespaces, 9-27 to 9-39

transporting tablespaces between databases, 9-26 to 9-39

triggers

auditing, 24-20, 24-21

disabling, 19-14

privileges for enabling and disabling, 19-13

enabling, 19-13

examples, 24-21

TRUNCATE PARTITION clause, 15-30

TRUNCATE statement, 19-10

DROP STORAGE option, 19-12

REUSE STORAGE option, 19-12

TRUNCATE SUBPARTITION clause, 15-32

tuning

archiving, 7-19

databases, 1-21

initially, 2-25

UNIQUE key constraints

dropping associated indexes, 14-17

enabling on creation, 14-9

foreign key references when dropped, 19-19

indexes associated with, 14-9

storage of associated indexes, 14-10

UNLIMITED TABLESPACE privilege, 22-18

UNRECOVERABLE DATAFILE option

ALTER DATABASE statement, 6-18

unrecoverable indexes

indexes, 14-6

UPDATE privilege

revoking, 23-14

USER_DUMP_DEST initialization parameter, 4-16

USER_INDEXES view

jobs in system, viewing, 8-14

USER_JOBS view

USER_SEGMENTS view, 9-39

USER_TAB_COLUMNS view

USER_TABLES view

assigning profiles to, 22-23

usernames

SYS and SYSTEM, 1-4

users

altering, 22-19

assigning tablespace quotas, 9-3

assigning unlimited quotas for, 22-17

authentication

about, 21-2, 22-7

changing default roles, 22-20

database authentication, 22-8

default tablespaces, 22-16

dropping, 22-20

dropping profiles and, 22-25

dropping roles and, 23-10

end-user security policies, 21-6

enrolling, 1-20

enterprise, 22-11, 23-9

external authentication, 22-9

global, 22-11

in a newly created database, 2-25

limiting number of, 2-24

listing, 22-25

listing privileges granted to, 23-23

listing roles granted to, 23-24

managing, 22-14

multi-tier authentication and authorization, 22-13

network authentication, 22-11

objects after dropping, 22-20

operating system authentication, 22-10

password security, 21-5

policies for managing privileges, 21-6

privileges for changing passwords, 22-18

privileges for creating, 22-15

privileges for dropping, 22-20

PUBLIC group, 23-16

schema-independent, 22-12

security and, 21-2

security for general users, 21-4

session, terminating, 4-22

specifying user names, 22-15

tablespace quotas, 22-17

unique user names, 2-24, 22-6

viewing information on, 22-27

viewing memory use, 22-29

viewing tablespace quotas, 22-27

utilities

Export, 1-17

for the database administrator, 1-17

Import, 1-17

SQL*Loader, 1-17

UTLCHAIN.SQL script, 19-9

UTLLOCKT.SQL script, 4-15

V$ARCHIVE view, 7-22

V$ARCHIVE_DEST view

obtaining destination status, 7-13

V$DATABASE view, 7-23

V$DATAFILE view, 9-39

V$DBFILE view, 2-16

V$DISPATCHER view

monitoring MTS dispatchers, 4-8

V$DISPATCHER_RATE view

monitoring MTS dispatchers, 4-8

V$LICENSE view, 22-6

V$LOG view, 7-22

displaying archiving status, 7-22

online redo log, 6-18

viewing redo data with, 6-18

V$LOGFILE view, 2-16

logfile status, 6-15

viewing redo data with, 6-18

V$LOGMNR_CONTENTS view, 7-32

using to analyze archived redo logs, 7-26

V$PWFILE_USERS view, 1-14

V$QUEUE view

monitoring MTS dispatchers, 4-8

V$ROLLNAME view

finding PENDING OFFLINE segments, 11-16

V$ROLLSTAT view

finding PENDING OFFLINE segments, 11-16

V$SESSION view, 4-22

V$SORT SEGMENT view, 9-40

V$SORT_USER view, 9-40

V$TEMP_EXTENT_MAP view, 9-40

V$TEMP_EXTENT_POOL view, 9-40

V$TEMP_SPACE_HEADER view, 9-40

V$TEMPFILE view, 9-40

V$THREAD view, 6-18

viewing redo data with, 6-18

V$VERSION view, 1-23

valid destination state