Index

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

Symbols

: (colon)

datetime format element, 2-67

, (comma)

datetime format element, 2-67

number format element, 2-62

- (dash)

datetime format element, 2-67

$ (dollar sign)

number format element, 2-62

% (percent) used with LIKE operator, 5-13

. (period)

datetime format element, 2-67

number format element, 2-62

; (semicolon)

datetime format element, 2-67

/ (slash)

datetime format element, 2-67

Numerics

0 (zero)

number format element, 2-62

20th century, 2-71

specifying, 2-71

21st century, 2-71

specifying, 2-71

9 (nine)

number format element, 2-62

A

ABS function, 6-15

ACCESSED GLOBALLY clause

of CREATE CONTEXT, 12-12

ACCOUNT LOCK clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-33

ACCOUNT UNLOCK clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-33

ACOS function, 6-15

ACTIVATE STANDBY DATABASE clause

of ALTER DATABASE, 8-34

ACTIVE_INSTANCE_COUNT initialization parameter

setting with ALTER SYSTEM, 9-33

A.D. datetime format element, 2-67, 2-70

AD datetime format element, 2-67, 2-70

ADD clause

of ALTER DIMENSION, 8-46

of ALTER INDEXTYPE, 8-73

of ALTER TABLE, 10-53

of ALTER VIEW, 11-29

ADD DATAFILE clause

of ALTER TABLESPACE, 10-85

ADD LOG GROUP clause

of ALTER TABLE, 10-28

ADD LOGFILE clause

of ALTER DATABASE, 8-15

ADD LOGFILE GROUP clause

of ALTER DATABASE, 8-30

ADD LOGFILE MEMBER clause

of ALTER DATABASE, 8-15, 8-30

ADD LOGFILE THREAD clause

of ALTER DATABASE, 8-30

ADD OVERFLOW clause

of ALTER TABLE, 10-34

ADD PARTITION clause

of ALTER TABLE, 10-43, 10-44, 10-45

ADD PRIMARY KEY clause

of ALTER MATERIALIZED VIEW LOG, 8-97

ADD ROWID clause

of ALTER MATERIALIZED VIEW, 8-97

of ALTER MATERIALIZED VIEW LOG, 8-97

ADD SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 8-31

ADD TEMPFILE clause

of ALTER TABLESPACE, 10-85

ADD VALUES clause

of ALTER TABLE ... MODIFY PARTITION, 10-38

ADD_MONTHS function, 6-16

ADMINISTER DATABASE TRIGGER system privilege, 16-44

ADVISE clause

of ALTER SESSION, 9-3

AFTER clause

of CREATE TRIGGER, 14-86

AFTER triggers, 14-86

AGENT clause

of CREATE LIBRARY, 13-3

aggregate functions, 6-6

user-defined, creating, 12-56

alias

for a column, 7-3

for an expressions in a view query, 15-40

specifying in queries and subqueries, 17-16

ALL clause

of SELECT, 17-11

of SET CONSTRAINTS, 17-40

of SET ROLE, 17-43

ALL EXCEPT clause

of SET ROLE, 17-43

ALL operator, 5-5

ALL PRIVILEGES clause

of GRANT, 16-37

of REVOKE, 16-90

ALL PRIVILEGES shortcut

of AUDIT, 11-53

ALL shortcut

of AUDIT, 11-53

ALL_COL_COMMENTS data dictionary view, 11-67

ALL_ROWS hint, 2-89

ALL_TAB_COMMENTS data dictionary view, 11-67

ALLOCATE EXTENT clause

of ALTER CLUSTER, 8-4, 8-5

of ALTER INDEX, 8-53, 8-58

of ALTER MATERIALIZED VIEW, 8-80

of ALTER TABLE, 10-28

ALLOW CORRUPTION clause

of ALTER DATABASE ... RECOVER, 8-22

ALTER ANY CLUSTER system privilege, 16-39

ALTER ANY DIMENSION system privilege, 16-39

ALTER ANY INDEX system privilege, 16-40

ALTER ANY INDEXTYPE system privilege, 16-40

ALTER ANY MATERIALIZED VIEW system privilege, 16-41

ALTER ANY OUTLINE system privilege, 16-41

ALTER ANY PROCEDURE system privilege, 16-41

ALTER ANY ROLE system privilege, 16-42

ALTER ANY SEQUENCE system privilege, 16-42

ALTER ANY TABLE system privilege, 16-43

ALTER ANY TRIGGER system privilege, 16-44

ALTER ANY TYPE system privilege, 16-44

ALTER CLUSTER statement, 8-3

ALTER DATABASE statement, 8-9

ALTER DATABASE system privilege, 16-39

ALTER DIMENSION statement, 8-44

ALTER FUNCTION statement, 8-48

ALTER INDEX statement, 8-51

ALTER INDEXTYPE statement, 8-72

ALTER JAVA CLASS statement, 8-74

ALTER JAVA SOURCE statement, 8-74

ALTER MATERIALIZED VIEW LOG statement, 8-93

ALTER MATERIALIZED VIEW statement, 8-77

ALTER object privilege, 16-48

on a sequence, 16-49

on a table, 16-49

ALTER OUTLINE statement, 8-100

ALTER PACKAGE statement, 8-102

ALTER PROCEDURE statement, 8-106

ALTER PROFILE statement, 8-109

ALTER PROFILE system privilege, 16-42

ALTER RESOURCE COST statement, 8-113

ALTER RESOURCE COST system privilege, 16-42

ALTER ROLE statement, 8-116

ALTER ROLLBACK SEGMENT statement, 8-118

ALTER ROLLBACK SEGMENT system privilege, 16-42

ALTER SEQUENCE statement, 8-122

ALTER SESSION statement, 9-2

ALTER SESSION system privilege, 16-42

ALTER SNAPSHOT LOG. See ALTER MATERIALIZED VIEW LOG

ALTER SNAPSHOT. See ALTER MATERIALIZED VIEW

ALTER statements

triggers on, 14-89

ALTER SYSTEM statement, 9-20

ALTER SYSTEM system privilege, 16-39

ALTER TABLE statement, 10-2

ALTER TABLESPACE statement, 10-82

ALTER TABLESPACE system privilege, 16-43

ALTER TRIGGER statement, 11-2

ALTER TYPE statement, 11-6

ALTER USER statement, 11-20

ALTER USER system privilege, 16-44

ALTER VIEW statement, 11-28

alter_column_properties clause

of ALTER TABLE, 10-18

alter_constraint_clauses

of ALTER TABLE, 10-17

alter_external_table_clause

of ALTER TABLE, 10-22

A.M. datetime format element, 2-67, 2-70

AM datetime format element, 2-67, 2-70

American National Standards Institute (ANSI), B-1

datatypes, 2-34

conversion to Oracle datatypes, 2-34

datatypes, implicit conversion, 2-34

standards, xvii, 1-2, B-2

supported datatypes, 2-5

analytic functions, 6-8

AVG, 6-20

CORR, 6-34

COUNT, 6-37

COVAR_POP, 6-39

COVAR_SAMP, 6-41

CUME_DIST, 6-43

DENSE_RANK, 6-50

FIRST, 6-59

FIRST_VALUE, 6-61

inverse distribution, 6-109, 6-112

LAG, 6-72

LAST, 6-73

LAST_VALUE, 6-76

LEAD, 6-78

linear regression, 6-120

MAX, 6-86

MIN, 6-89

NTILE, 6-101

OVER clause, 6-8, 6-10

PERCENT_CONT, 6-109

PERCENT_DISC, 6-112

PERCENT_RANK, 6-107

RANK, 6-114

RATIO_TO_REPORT, 6-116

ROW_NUMBER, 6-131

STDDEV, 6-139

STDDEV_POP, 6-140

STDDEV_SAMP, 6-142

SUM, 6-145

syntax, 6-8

user-defined, 6-10, 12-56

VAR_POP, 6-192

VAR_SAMP, 6-194

VARIANCE, 6-195

ANALYZE ANY system privilege, 16-45

ANALYZE CLUSTER statement, 11-31

ANALYZE INDEX statement, 11-31

ANALYZE TABLE statement, 11-31

ANCILLARY TO clause

of CREATE OPERATOR, 13-39

AND condition, 5-8

AND DATAFILES clause

of DROP TABLESPACE, 16-12

AND_EQUAL hint, 2-89

ANSI. See American National Standards Institute (ANSI)

ANY operator, 5-5

APPEND hint, 2-90

application servers

allowing connection as user, 11-24

applications

allowing connection as user, 11-24

securing, 12-11

validating, 12-11

AQ_ADMINISTRATOR_ROLE role, 16-46

AQ_TM_PROCESSES initialization parameter

setting with ALTER SYSTEM, 9-33

AQ_USER_ROLE role, 16-46

ARCHIVE LOG clause

of ALTER SYSTEM, 9-23

archive logs

applying to standby database, 8-24

archive mode

specifying, 12-26

ARCHIVE_LAG_TARGET initialization parameter

setting with ALTER SYSTEM, 9-34

archived redo logs

location, 8-20

storage locations, 9-65

ARCHIVELOG clause

of ALTER DATABASE, 8-15, 8-29

of CREATE CONTROLFILE, 12-18

of CREATE DATABASE, 12-26

arguments

of operators, 3-1

arithmetic

operators, 3-3

with DATE values, 2-19

AS 'filespec' clause

of CREATE LIBRARY, 13-3

AS clause

of CREATE JAVA, 12-95

AS EXTERNAL clause

of CREATE FUNCTION, 13-62

of CREATE TYPE BODY, 15-27

AS OBJECT clause

of CREATE TYPE, 15-9

AS subquery clause

of CREATE MATERIALIZED VIEW, 13-11, 13-22

of CREATE TABLE, 14-53

of CREATE VIEW, 15-42

AS TABLE clause

of CREATE TYPE, 15-17

AS VARRAY clause

of CREATE TYPE, 15-17

ASC clause

of CREATE INDEX, 12-71

ASCII

character set, 2-44

ASCII function, 6-16

ASCIISTR function, 6-17

ASIN function, 6-18

ASSOCIATE STATISTICS statement, 11-46

ATAN function, 6-19

ATAN2 function, 6-19

ATTRIBUTE clause

of ALTER DIMENSION, 8-45

of CREATE DIMENSION, 12-40, 12-43

attributes

adding to a dimension, 8-46

dropping from a dimension, 8-46

maximum number of in object type, 14-21

of dimensions, defining, 12-43

of user-defined types

mapping to Java fields, 15-11

AUDIT ANY system privilege, 16-45

AUDIT SYSTEM system privilege, 16-39

AUDIT_FILE_DEST initialization parameter

setting with ALTER SYSTEM, 9-34

AUDIT_TRAIL initialization parameter

setting with ALTER SYSTEM, 9-34

auditing

options

for database objects, 11-56

for SQL statements, 11-58

policies

value-based, 11-50

SQL statements, 11-56

SQL statements, stopping, 16-79

auditing_by_clause

of AUDIT, 11-51

auditing_on_clause

of AUDIT, 11-52

AUTHENTICATED BY clause

of CREATE DATABASE LINK, 12-36

AUTHENTICATED clause

of ALTER USER, 11-24

AUTHID CURRENT_USER clause

of ALTER JAVA, 8-75

of CREATE FUNCTION, 12-53

of CREATE JAVA, 12-91, 12-93

of CREATE PACKAGE, 13-47

of CREATE PROCEDURE, 13-61

of CREATE TYPE, 11-13, 15-9

AUTHID DEFINER clause

of ALTER JAVA, 8-75

of CREATE FUNCTION, 12-53

of CREATE JAVA, 12-91, 12-93

of CREATE PACKAGE, 13-47

of CREATE PROCEDURE, 13-61

of CREATE TYPE, 11-13, 15-9

AUTOALLOCATE clause

of CREATE TABLESPACE, 14-75

AUTOEXTEND clause

of ALTER DATABASE, 8-14

of ALTER TABLESPACE, 10-84, 10-86

of CREATE DATABASE, 12-23

of CREATE TABLESPACE, 14-70, 14-72

of CREATE TEMPORARY TABLESPACE, 14-80, 14-81

automatic segment-space management, 2-15, 14-27, 14-76

Automatic Undo Management mode, 8-118, 12-29

AVG function, 6-20

B

number format element, 2-62

BACKGROUND_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 9-35

BACKGROUND_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 9-35

BACKUP ANY TABLE system privilege, 16-43

BACKUP CONTROLFILE clause

of ALTER DATABASE, 8-16, 8-33

BACKUP_TAPE_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 9-35

B.C. datetime format element, 2-67, 2-70

BC datetime format element, 2-67, 2-70

BECOME USER system privilege, 16-44

BEFORE clause

of CREATE TRIGGER, 14-86

BEFORE triggers, 14-86

BEGIN BACKUP clause

of ALTER TABLESPACE, 10-88

BFILE

datatype, 2-30

locators, 2-30

BFILENAME function, 6-21

BIN_TO_NUM function, 6-22

binary large objects. See BLOB

binary operators, 3-2

BINDING clause

of CREATE OPERATOR, 13-37, 13-39

bit vectors

converting to numbers, 6-22

BITAND function, 6-23

BITMAP clause

of CREATE INDEX, 12-67

bitmap indexes, 12-67

bitmap_join_index_clause

of CREATE INDEX, 12-64

BITMAP_MERGE_AREA_SIZE initialization parameter

setting with ALTER SYSTEM, 9-36

blank padding

specifying in format models, 2-73

suppressing, 2-73

BLANK_TRIMMING initialization parameter

setting with ALTER SYSTEM, 9-36

blank-padded comparison semantics, 2-43

BLOB datatype, 2-31

transactional support, 2-31

BLOCKSIZE clause

of CREATE TABLESPACE, 14-73

BODY clause

of ALTER PACKAGE, 8-103

BUFFER_POOL parameter

of STORAGE clause, 17-55

BUFFER_POOL_KEEP initialization parameter

setting with ALTER SYSTEM, 9-36

BUFFER_POOL_RECYCLE initialization parameter

setting with ALTER SYSTEM, 9-37

BUILD DEFERRED clause

of CREATE MATERIALIZED VIEW, 13-15

BUILD IMMEDIATE clause

of CREATE MATERIALIZED VIEW, 13-15

BY ACCESS clause

of AUDIT, 11-55

BY proxy clause

of AUDIT, 11-54

BY SESSION clause

of AUDIT, 11-55

BY user clause

of AUDIT, 11-54

BYTE character semantics, 2-10, 2-11

BYTE length semantics, 10-56

C

number format element, 2-62

C clause

of CREATE TYPE, 15-14

of CREATE TYPE BODY, 15-26

C method

mapping to an object type, 15-14

CACHE clause

of ALTER MATERIALIZED VIEW, 8-85

of ALTER MATERIALIZED VIEW LOG, 8-97

of ALTER TABLE, 10-30, 14-45

of CREATE CLUSTER, 12-9

of CREATE MATERIALIZED VIEW, 13-14

of CREATE MATERIALIZED VIEW LOG, 13-32

CACHE hint, 2-90

CACHE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 8-122

of CREATE SEQUENCE, 13-84

CACHE READS clause

of ALTER TABLE, 10-65

of CREATE TABLE, 14-45

cached cursors

execution plan for, 16-24

CALL clause

of CREATE TRIGGER, 14-94

CALL procedure statement

of CREATE TRIGGER, 14-94

call spec. See call specifications

call specifications

in procedures, 13-57

of CREATE PROCEDURE, 13-62

of CREATE TYPE, 15-14

of CREATE TYPE BODY, 15-26

CALL statement, 11-64

calls

limiting CPU time for, 13-67

limiting data blocks read, 13-67

Cartesian products, 7-10

CASCADE clause

of CREATE TABLE, 14-53

of DROP PROFILE, 15-87

of DROP USER, 16-20

CASCADE CONSTRAINTS clause

of DROP CLUSTER, 15-61

of DROP TABLE, 16-8

of DROP TABLESPACE, 16-12

of DROP VIEW, 16-23

of REVOKE, 16-91

CASE expressions, 4-5

searched, 4-6

simple, 4-6

case sensitivity

schema object names, 2-109

CAST function, 6-24

MULTISET parameter, 6-25

CATSEARCH condition, 5-2

CC datetime format element, 2-67

CEIL function, 6-27

chained rows

listing, 11-42

of clusters, 11-36

CHANGE CATEGORY clause

of ALTER OUTLINE, 8-101

CHAR character semantics, 2-10, 2-11

CHAR datatype, 2-10

ANSI, 2-34

converting to VARCHAR2, 2-61

CHAR length semantics, 10-56

CHAR VARYING datatype, ANSI, 2-34

CHARACTER datatype

ANSI, 2-34

DB2, 2-35

SQL/DS, 2-35

character functions, 6-4, 6-5

character large objects. See CLOB

character length semantics, 10-56

character literal. See text

CHARACTER SET parameter

of ALTER DATABASE, 8-35

of CREATE CONTROLFILE, 12-18

of CREATE DATABASE, 12-27

character sets

changing, 8-35

common, 2-44

database, specifying, 12-27

multibyte characters, 2-107

specifying for database, 12-27

character strings

comparison rules, 2-42

exact matching, 2-73

fixed-length, 2-10

national character set, 2-10

variable length, 2-11

variable-length, 2-14

zero-length, 2-10

CHARACTER VARYING datatype

ANSI, 2-34

characters

single, comparison rules, 2-43

CHARTOROWID function, 6-27

CHECK clause

of constraint_clause, 11-84

of CREATE TABLE, 14-23

check constraints, 11-84

CHECK DATAFILES clause

of ALTER SYSTEM, 9-26

CHECKPOINT clause

of ALTER SYSTEM, 9-25

checkpoints

forcing, 9-25

CHOOSE hint, 2-90

CHR function, 6-28

CHUNK clause

of ALTER TABLE, 10-66

of CREATE TABLE, 14-36

CIRCUITS initialization parameter

setting with ALTER SYSTEM, 9-38

CLEAR LOGFILE clause

of ALTER DATABASE, 8-15, 8-32

CLOB datatype, 2-31

transactional support, 2-31

clone databases

mounting, 8-17

CLOSE DATABASE LINK clause

of ALTER SESSION, 9-3

CLUSTER clause

of ANALYZE, 11-36

of CREATE INDEX, 12-62, 12-68

of CREATE TABLE, 14-33

of TRUNCATE, 17-61

CLUSTER hint, 2-90

CLUSTER_DATABASE initialization parameter

setting with ALTER SYSTEM, 9-38

CLUSTER_DATABASE_INSTANCES initialization parameter

setting with ALTER SYSTEM, 9-39

cluster_index_clause

of CREATE INDEX, 12-62

CLUSTER_INTERCONNECTS initialization parameter

setting with ALTER SYSTEM, 9-39

clusters

assigning tables to, 14-33

caching retrieved blocks, 12-9

cluster indexes, 12-68

collecting statistics on, 11-36

creating, 12-2

data block, allocating, 12-5

deallocating unused extents, 8-4

degree of parallelism

changing, 8-4, 8-6

when creating, 12-8

dropping tables, 15-61

extents, allocating, 8-4, 8-5

granting system privileges on, 16-39

hash, 12-6

single-table, 12-7

indexed, 12-6

key values

allocating space for, 12-6

modifying space for, 8-5

migrated and chained rows in, 11-36, 11-42

modifying, 8-3

physical attributes

specifying, 12-5

releasing unused space, 8-6

removing from the database, 15-60

SQL examples, 15-61

storage attributes

changing, 8-4

specifying, 12-5, 17-49

storage characteristics, changing, 8-5

tablespace in which created, 12-6

validating structure, 11-40

COALESCE clause

for partitions, 10-45

of ALTER INDEX, 8-65

of ALTER TABLE, 10-35, 10-38

of ALTER TABLESPACE, 10-90

COALESCE function, 6-29

as a variety of CASE expression, 6-29

COALESCE SUBPARTITION clause

of ALTER TABLE, 10-38

collections

inserting rows into, 16-60

modifying, 10-61

nested tables, 2-38

treating as a table, 15-54, 16-60, 17-15, 17-66, 17-67

unnesting, 17-15

examples, 17-35

varrays, 2-37

collection-typed values

converting to datatypes, 6-24

column constraints, 11-74, 11-79

of ALTER TABLE, 10-55

of CREATE TABLE, 14-23

restrictions on, 10-57

column REF constraints, 11-75, 11-84

of ALTER TABLE, 10-54

of CREATE TABLE, 14-23

column_properties clause

of ALTER TABLE, 10-18, 10-63

columns

adding, 10-53

aliases for, 7-3

altering storage, 10-63

associating statistics with, 11-48

basing an index on, 12-69

collecting statistics on, 11-37

creating comments about, 11-67

defining, 14-6

dropping from a table, 10-57

LOB

storage attributes, 10-65, 13-11

maximum number of, 14-21

modifying existing, 10-55

nested table

storage attributes, 13-11

object type

storing in a materialized view, 13-11

parent-child relationships between, 12-39

prohibiting nulls in, 11-81

qualifying names of, 7-2

REF

describing, 11-84

restricting values for, 11-73

specifying as foreign key, 11-83

specifying as primary key, 11-81

specifying constraints on, 14-23

specifying default values, 14-22

storage properties, 14-34

substitutable, identifying type, 6-156

unique values in, 11-80

varray

storage attributes, 13-11

COLUMNS clause

of ASSOCIATE STATISTICS, 11-46, 11-48

COMMENT ANY TABLE system privilege, 16-45

COMMENT clause

of COMMIT, 11-71

COMMENT statement, 11-67

comments, 2-85

adding to objects, 11-67

associating with a transaction, 11-71

dropping from objects, 11-67

in SQL statements, 2-85

on indextypes, 11-69

on operators, 11-69

on schema objects, 2-87

removing from the data dictionary, 11-67

specifying, 2-86

viewing, 11-67

commit

automatic, 11-70

COMMIT IN PROCEDURE clause

of ALTER SESSION, 9-3

COMMIT statement, 11-70

COMMIT_POINT_STRENGTH initialization parameter

setting with ALTER SYSTEM, 9-39

comparison conditions, 5-4

comparison functions

MAP, 15-26

ORDER, 15-26, 15-27

comparison semantics

blank-padded, 2-43

nonpadded, 2-43

of character strings, 2-42

COMPATIBLE initialization parameter

setting with ALTER SYSTEM, 9-40

COMPILE clause

of ALTER DIMENSION, 8-46

of ALTER FUNCTION, 8-49

of ALTER JAVA SOURCE, 8-75

of ALTER MATERIALIZED VIEW, 8-90

of ALTER PACKAGE, 8-103

of ALTER PROCEDURE, 8-107

of ALTER TRIGGER, 11-3

of ALTER TYPE, 11-9

of ALTER VIEW, 11-30

of CREATE JAVA, 12-92

compiler switches

dropping and preserving, 8-49, 8-104, 8-107, 11-4, 11-10

COMPOSE function, 6-31

composite foreign keys, 11-82

composite partitioning clause

of CREATE TABLE, 14-15, 14-43

composite primary keys, 11-81

composite unique constraints, 11-80

COMPOSITE_LIMIT parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-67

compound conditions, 5-17

compound expressions, 4-4

COMPRESS clause

of ALTER INDEX, 8-54

of ALTER INDEX ... REBUILD, 8-62

of ALTER TABLE, 10-69

of CREATE TABLE, 14-30

COMPUTE STATISTICS clause

of ALTER INDEX... REBUILD, 8-63

of ANALYZE, 11-36

of CREATE INDEX, 12-74

CONCAT function, 6-31

concatenation operator, 3-4

conditional_insert_clause

of INSERT, 16-64

conditions

comparison, 5-4

compound, 5-17

EXISTS, 5-11

group comparison, 5-6

in SQL syntax, 5-1

IS OF type, 5-16

LIKE, 5-12

logical, 5-7

membership, 5-9

null, 5-11

range, 5-11

simple comparison, 5-5

CONNECT BY clause

of queries and subqueries, 17-18

of SELECT, 7-5, 17-18

CONNECT clause

of SELECT and subqueries, 17-8

CONNECT role, 16-46

CONNECT THROUGH clause

of ALTER USER, 11-24

CONNECT TO clause

of CREATE DATABASE LINK, 12-35

CONNECT_TIME parameter

of ALTER PROFILE, 8-110

of ALTER RESOURCE COST, 8-114

CONSIDER FRESH clause

of ALTER MATERIALIZED VIEW, 8-90

constant values. See literals

CONSTRAINT(S) session parameter, 9-11

constraints

adding, 10-53

check, 11-84

checking

at end of transaction, 11-86

at start of transaction, 11-87

at the end of each DML statement, 11-86

column REF, 11-84

composite unique, 11-80

deferrable, 11-86, 17-40

enforcing, 9-11

defining, 11-73, 14-6

on a column, 14-23

on a table, 14-23

disabling, 10-70, 14-47

cascading, 14-53

dropping, 10-62, 16-12

enabling, 10-70, 14-47, 14-50

foreign key, 11-83

modifying existing, 10-61

not null, 11-81

on columns, 11-79

on tables, 11-78

on views, 11-78

dropping, 11-30, 16-23

modifying, 11-30

primary key, 11-81

attributes of index, 11-88

enabling, 14-50

referential integrity, 11-82, 11-83

restrictions, 11-79

scope, 11-85

setting state for a transaction, 17-40

storing rows in violation, 10-51

table REF, 11-84

unique, 11-80

attributes of index, 11-88

composite, 11-80

enabling, 14-49

constructor methods

and object types, 15-3

CONTAINS condition, 5-2

context namespaces

accessible to instance, 12-13

associating with package, 12-11

initializing using OCI, 12-12

initializing using the LDAP directory, 12-12

removing from the database, 15-62

contexts

creating namespaces for, 12-11

granting system privileges on, 16-39

control files

allowing reuse, 12-16, 12-24

backing up, 8-33

re-creating, 12-14

CONTROL_FILE_RECORD_KEEP_TIME initialization parameter

setting with ALTER SYSTEM, 9-40

CONTROL_FILES initialization parameter

setting with ALTER SYSTEM, 9-40

controlfile clauses

of ALTER DATABASE, 8-16

CONTROLFILE REUSE clause

of CREATE DATABASE, 12-24

controlfiles

standby, creating, 8-33

conversion

rules, string to date, 2-75

conversion functions, 6-5

CONVERT clause

of ALTER DATABASE, 8-38

CONVERT function, 6-32

CORE_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 9-41

CORR function, 6-34

correlated subqueries, 7-12

correlation names

for base tables of indexes, 12-69

in DELETE, 15-54

in SELECT, 17-16

COS function, 6-36

COSH function, 6-36

COUNT function, 6-37

COVAR_POP function, 6-39

COVAR_SAMP function, 6-41

CPU_COUNT initialization parameter

setting with ALTER SYSTEM, 9-41

CPU_PER_CALL parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-67

CPU_PER_SESSION parameter

of ALTER PROFILE, 8-110

of ALTER RESOURCE COST, 8-113

of CREATE PROFILE, 13-67

CREATE ANY CLUSTER system privilege, 16-39

CREATE ANY CONTEXT system privilege, 16-39

CREATE ANY DIMENSION system privilege, 16-39

CREATE ANY DIRECTORY system privilege, 16-40

CREATE ANY INDEX system privilege, 16-40

CREATE ANY INDEXTYPE system privilege, 16-40

CREATE ANY LIBRARY system privilege, 16-40

CREATE ANY MATERIALIZED VIEW system privilege, 16-40

CREATE ANY OPERATOR system privilege, 16-41

CREATE ANY OUTLINE system privilege, 16-41

CREATE ANY PROCEDURE system privilege, 16-41

CREATE ANY SEQUENCE system privilege, 16-42

CREATE ANY SYNONYM system privilege, 16-43

CREATE ANY TABLE system privilege, 16-43

CREATE ANY TRIGGER system privilege, 16-44

CREATE ANY TYPE system privilege, 16-44

CREATE ANY VIEW system privilege, 16-45

CREATE CLUSTER statement, 12-2

CREATE CLUSTER system privilege, 16-39

CREATE CONTEXT statement, 12-11

CREATE CONTROLFILE statement, 12-14

CREATE DATABASE LINK statement, 12-33

CREATE DATABASE LINK system privilege, 16-39

CREATE DATABASE statement, 12-20

CREATE DATAFILE clause

of ALTER DATABASE, 8-13, 8-26

CREATE DIMENSION

system privilege, 16-39

CREATE DIMENSION statement, 12-39

CREATE DIRECTORY statement, 12-44

CREATE FUNCTION statement, 12-47

CREATE INDEX

statement, 12-60

CREATE INDEXTYPE

statement, 12-87

CREATE INDEXTYPE system privilege, 16-40

CREATE JAVA statement, 12-90

CREATE LIBRARY statement, 13-2

CREATE LIBRARY system privilege, 16-40

CREATE MATERIALIZED VIEW LOG statement, 13-28

CREATE MATERIALIZED VIEW statement, 13-5

CREATE MATERIALIZED VIEW system privilege, 16-40

CREATE OPERATOR statement, 13-37

CREATE OPERATOR system privilege, 16-41

CREATE OUTLINE statement, 13-41

CREATE PACKAGE BODY statement, 13-50

CREATE PACKAGE statement, 13-45

CREATE PFILE statement, 13-55

CREATE PROCEDURE statement, 13-57

CREATE PROCEDURE system privilege, 16-41

CREATE PROFILE statement, 13-64

CREATE PROFILE system privilege, 16-42

CREATE PUBLIC DATABASE LINK system privilege, 16-39

CREATE PUBLIC SYNONYM system privilege, 16-43

CREATE ROLE statement, 13-71

CREATE ROLE system privilege, 16-42

CREATE ROLLBACK SEGMENT statement, 13-74

CREATE ROLLBACK SEGMENT system privilege, 16-42

CREATE SCHEMA statement, 13-78

CREATE SEQUENCE statement, 13-81

CREATE SEQUENCE system privilege, 16-42

CREATE SESSION system privilege, 16-42

CREATE SPFILE statement, 13-86

CREATE STANDBY CONTROLFILE clause

of ALTER DATABASE, 8-16, 8-33

CREATE statements

triggers on, 14-89

CREATE SYNONYM statement, 14-2

CREATE SYNONYM system privilege, 16-43

CREATE TABLE statement, 14-6

CREATE TABLE system privilege, 16-43

CREATE TABLESPACE statement, 14-67

CREATE TABLESPACE system privilege, 16-43

CREATE TEMPORARY TABLESPACE statement, 14-79

CREATE TRIGGER statement, 14-83

CREATE TRIGGER system privilege, 16-44

CREATE TYPE BODY statement, 15-23

CREATE TYPE statement, 15-3

CREATE TYPE system privilege, 16-44

CREATE USER statement, 15-29

CREATE USER system privilege, 16-44

CREATE VIEW statement, 15-36

CREATE VIEW system privilege, 16-45

CREATE_BITMAP_AREA_SIZE initialization parameter

setting with ALTER SYSTEM, 9-42

CREATE_STORED_OUTLINES initialization parameter

setting with ALTER SYSTEM, 9-42

CREATE_STORED_OUTLINES session parameter, 9-11

cross joins, 17-17

CUBE clause

of SELECT statements, 17-20

CUME_DIST function, 6-43

cumulative distributions, 6-43

currency symbol

ISO, 2-62

local, 2-63

setting for a session, 9-8

union, 2-64

CURRENT_DATE function, 6-45

CURRENT_SCHEMA session parameter, 9-12

CURRENT_TIMESTAMP function, 6-46

CURRENT_USER clause

of CREATE DATABASE LINKI, 12-35

CURRVAL pseudocolumn, 2-79, 13-81

CURSOR expressions, 4-7

CURSOR_SHARING initialization parameter

setting with ALTER SESSION, 9-7, 9-43

CURSOR_SPACE_FOR_TIME initialization parameter

setting with ALTER SYSTEM, 9-43

cursors

cached, 16-24

shared pool, 9-102

CustomDatum Java storage format, 15-11

CYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 8-122

of CREATE SEQUENCE, 13-84

D

number format element, 2-62

data

aggregation

composite columns of GROUP BY, 17-20

concatenated grouping sets of GROUP BY, 17-20

grouping sets, 17-20

caching frequently used, 10-30, 14-45

independence, 14-2

integrity checking on input, 2-13

retrieving, 7-2

specifying as temporary, 14-20

undo

storing, 13-74

data conversion, 2-46

between character datatypes, 2-48

implicit

disadvantages, 2-46

implicit versus explicit, 2-46

when performed implicitly, 2-46, 2-48

when specified explicitly, 2-49

data definition language (DDL)

events and triggers, 14-89

statements, 7-16

and implicit commit, 7-16

causing recompilation, 7-16

PL/SQL support, 7-16

statements requiring exclusive access, 7-16

data dictionary

adding comments to, 11-67

data manipulation language (DML)

allowing during indexing, 8-60

operations

and triggers, 14-88

during index creation, 12-74

during index rebuild, 10-68

parallelizing, 14-46

restricting operations, 9-29

retrieving affected rows, 15-54, 16-63, 17-71

retrieving rows affected by, 15-54, 16-63, 17-71

statements, 7-18

PL/SQL support, 7-18

triggers

and LOB columns and attributes, 2-29

data object number

in extended rowids, 2-32

database events

and triggers, 14-90

auditing, 14-90

transparent logging of, 14-90

database links, 7-15

closing, 9-3

creating, 2-114, 12-33

creating synonyms with, 14-4

current user, 12-35

granting system privileges on, 16-39

naming, 2-114

public, 12-34

dropping, 15-63

referring to, 2-115

removing from the database, 15-63

shared, 12-34

syntax, 2-114

username and password, 2-115

database objects

dropping, 16-20

nonschema, 2-103

schema, 2-102

database triggers. See triggers

databases

accounts

creating, 15-29

allowing generation of redo logs, 8-18

allowing reuse of control files, 12-24

allowing unlimited resources to users, 13-66

archive mode

specifying, 12-26

blocks

specifying size, 14-73

cache

buffers in, 9-44

cancel-based recovery, 8-21

terminating, 8-23

change-based recovery, 8-21

changing character set, 8-35

changing characteristics, 12-14

changing global name, 8-38

changing name, 12-14, 12-16

character set, specifying, 12-27

character sets

changing, 8-35

specifying, 12-27

connect strings, 2-115

converting from Oracle7 data dictionary, 8-38

create script for, 8-33

creating, 12-20

datafiles

modifying, 8-26

specifying, 12-27

designing media recovery, 8-19

enabling automatic extension, 12-27

ending backup of, 8-25

erasing all data from, 12-20

granting system privileges on, 16-39

instances of, 12-26

limiting resources for users, 13-64

log files

modifying, 8-29

specifying, 12-25

managed recovery, 8-12

modifying, 8-9

mounting, 8-17, 12-21

moving a subset to a different database, 10-49

naming, 8-17

national character set

specifying, 12-27

no data loss mode, 12-30

no-data-loss mode, 8-34

online

adding log files, 8-29

opening, 8-18, 12-21

after media recovery, 8-18

prepare to re-create, 8-33

preparing for standby status, 8-35

quiesced state, 9-29

read-only, 8-18

read/write, 8-18

reconstructing damaged, 8-19

recovering, 8-19, 8-20

recovery

allowing corrupt blocks, 8-22

testing, 8-22

with backup control file, 8-21

re-creating control file for, 12-14

remote

accessing, 7-15

authenticating users to, 12-36

connecting to, 12-35

inserting into, 16-59

service name of, 12-36

table locks on, 16-73

resetting

current log sequence, 8-18

to an earlier version, 8-37

restricting users to read-only transactions, 8-18

resuming activity, 9-28

standby

adding log files, 8-29

PROTECTED mode, 8-34

specifying, 12-30

UNPROTECTED MODE, 8-34

suspending activity, 9-28

tempfiles

modifying, 8-26

time zone

determining, 6-47

setting, 8-36, 12-31

time-based recovery, 8-21

upgrading, 8-38

DATAFILE clause

of CREATE DATABASE, 12-27

DATAFILE clauses

of ALTER DATABASE, 8-13, 8-27

DATAFILE END BACKUP clause

of ALTER DATABASE, 8-25

DATAFILE OFFLINE clause

of ALTER DATABASE, 8-27

DATAFILE ONLINE clause

of ALTER DATABASE, 8-27

DATAFILE RESIZE clause

of ALTER DATABASE, 8-27

datafiles

bringing online, 8-27

changing size of, 8-27

creating new, 8-26

designing media recovery, 8-19

dropping, 16-12

enabling automatic extension, 14-72

end online backup of, 8-27, 10-88

online backup of, 10-88

online, updating information on, 9-26

putting online, 8-27

recover damaged, 8-19

recovering, 8-21

re-creating lost or damaged, 8-26

renaming, 8-29

resizing, 8-27

reusing, 16-30

size of, 16-29

specifying, 16-28

for a tablespace, 14-72

specifying for database, 12-27

system generated, 8-26

taking offline, 8-27

datatypes, 2-2

"Any" types, 2-38

ANSI-supported, 2-5

associating statistics with, 11-48

BFILE, 2-9, 2-30

BLOB, 2-9, 2-31

built-in, 2-7

CHAR, 2-8, 2-10

character, 2-9

CLOB, 2-9, 2-31

comparison rules, 2-42

converting to collection-typed values, 6-24

converting to other datatypes, 6-24

DATE, 2-7, 2-17

datetime, 2-16

interval, 2-16

INTERVAL DAY TO SECOND, 2-22

INTERVAL YEAR TO MONTH, 2-22

length semantics, 2-10, 2-11

LONG, 2-7, 2-14

LONG RAW, 2-8, 2-25

media types, 2-42

NCHAR, 2-9, 2-10

NCLOB, 2-9, 2-31

NUMBER, 2-12

NUMER, 2-7

NVARCHAR2, 2-7, 2-11

Oracle-supplied types, 2-38

RAW, 2-8, 2-25

ROWID, 2-8, 2-31

spatial type, 2-41

TIMESTAMP, 2-20

TIMESTAMP WITH LOCAL TIME ZONE, 2-22

TIMESTAMP WITH TIME ZONE, 2-21

UROWID, 2-8, 2-33

user-defined, 2-36

VARCHAR, 2-12

VARCHAR2, 2-7, 2-11

XML types, 2-39

DATE columns

converting to datetime columns, 10-55

DATE datatype, 2-17

julian, 2-20

date format models, 2-65

punctuation in, 2-66

text in, 2-66

date functions, 6-5

dates

arithmetic, 2-19

comparison rules, 2-42

datetime arithmetic

boundary cases, 9-12

calculating daylight savings time, 2-24

datetime columns

creating from DATE columns, 10-55

datetime datatypes, 2-16

daylight savings time, 2-24

datetime expressions, 4-9

datetime field

extracting from a datetime or interval value, 6-57

datetime format elements, 2-66

and Globalization Support, 2-70

capitalization, 2-66

ISO standard, 2-71

RR, 2-71

suffixes, 2-72

datetime functions, 6-5

DAY datetime format element, 2-70

daylight savings time, 2-24

DB_BLOCK_BUFFERS initialization parameter

setting with ALTER SYSTEM, 9-44

DB_BLOCK_CHECKING initialization parameter

setting with ALTER SESSION, 9-7

setting with ALTER SYSTEM, 9-44

DB_BLOCK_CHECKSUM initialization parameter

setting with ALTER SYSTEM, 9-45

DB_BLOCK_SIZE initialization parameter

setting with ALTER SYSTEM, 9-45

DB_CACHE_ADVICE initialization parameter

setting with ALTER SYSTEM, 9-46

DB_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-46, 9-69, 9-82

DB_CACHE_SIZE parameter

of ALTER SYSTEM, 9-69, 9-82

DB_CREATE_FILE_DEST initialization parameter

setting with ALTER SESSION, 9-7

setting with ALTER SYSTEM, 9-46

DB_CREATE_ONLINE_LOG_DEST_n initialization parameter

setting with ALTER SESSION, 9-7

setting with ALTER SYSTEM, 9-47

DB_DOMAIN initialization parameter

setting with ALTER SYSTEM, 9-47

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter

setting with ALTER SESSION, 9-7

setting with ALTER SYSTEM, 9-47

DB_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 9-48

DB_FILES initialization parameter

setting with ALTER SYSTEM, 9-48

DB_KEEP_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-49

DB_NAME initialization parameter

setting with ALTER SYSTEM, 9-49

DB_nK_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-43

DB_RECYCLE_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-50

DB_WRITER_PROCESSES initialization parameter

setting with ALTER SYSTEM, 9-50

DB2 datatypes, 2-34

conversion to Oracle datatypes, 2-35

implicit conversion, 2-35

restrictions on, 2-35

DBA role, 16-46

DBA_2PC_PENDING data dictionary view, 9-3

DBA_COL_COMMENTS data dictionary view, 11-67

DBA_ROLLBACK_SEGS data dictionary view, 15-90

DBA_TAB_COMMENTS data dictionary view, 11-67

DBLINK_ENCRYPT_LOGIN initialization parameter

setting with ALTER SYSTEM, 9-50

DBMS_OUTPUT package, 11-3

DBMS_ROWID package

and extended rowids, 2-32

DBMSSTDX.SQL script, 12-48, 13-45, 13-50, 13-57

and triggers, 14-83

DBTIMEZONE function, 6-47

DBWR_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 9-51

DD datetime format element, 2-67

DDAY datetime format element, 2-67

DDD datetime format element, 2-67

DDL. See data definition language (DDL)

DEALLOCATE UNUSED clause

of ALTER CLUSTER, 8-4, 8-6

of ALTER INDEX, 8-53

of ALTER TABLE, 10-29

DEBUG clause

of ALTER FUNCTION, 8-49

of ALTER PACKAGE, 8-104

of ALTER PROCEDURE, 8-107

of ALTER TRIGGER, 11-4

of ALTER TYPE, 11-9

decimal characters, 2-54

reset for session, 9-8

specifying, 2-62

DECIMAL datatype

ANSI, 2-34

DB2, 2-35

SQL/DS, 2-35

DECODE function, 6-47

DECOMPOSE function, 6-49

DEFAULT clause

of ALTER TABLE, 10-54

of CREATE TABLE, 14-22

DEFAULT COST clause

of ASSOCIATE STATISTICS, 11-47, 11-49

default index, suppressing, 13-17

DEFAULT profile

assigning to users, 15-87

DEFAULT ROLE clause

of ALTER USER, 11-23

DEFAULT SELECTIVITY clause

of ASSOCIATE STATISTICS, 11-47, 11-49

DEFAULT storage clause

of ALTER TABLESPACE, 10-87

of CREATE TABLESPACE, 14-74

DEFAULT TABLESPACE clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-32

DEFAULT TEMPORARY TABLESPACE clause

of ALTER DATABASE, 8-37

of CREATE DATABASE, 12-23

default_cost_clause

of ASSOCIATE STATISTICS, 11-47

default_selectivity_clause

of ASSOCIATE STATISTICS, 11-47

default_temporary_tablespace_clause

of CREATE DATABASE, 12-23

DEFERRABLE clause

of constraint_clause, 11-86

deferrable constraints, 17-40

DEFERRED clause

of SET CONSTRAINTS, 17-40

definer-rights functions, 12-53

DELETE ANY TABLE system privilege, 16-43

DELETE object privilege, 16-48

on a table, 16-49

on a view, 16-49

DELETE statement, 15-49

triggers on, 14-88

DELETE STATISTICS clause

of ANALYZE, 11-43

DELETE_CATALOG_ROLE role, 16-46

DENSE_RANK function, 6-50

DEREF function, 6-52

DESC clause

of CREATE INDEX, 12-71

DETERMINISTIC clause

of CREATE FUNCTION, 12-54

dimensions

attributes

adding, 8-46

changing, 8-44

defining, 12-43

dropping, 8-46

compiling invalidated, 8-46

creating, 12-39

examples, 12-43

granting system privileges on, 16-39

hierarchies

adding, 8-46

changing, 8-44

defining, 12-41

dropping, 8-46

levels

adding, 8-46

defining, 12-41

dropping, 8-46

removing from the database, 15-64

directories. See directory objects

directory objects

as aliases for OS directories, 12-44

auditing, 11-55

creating, 12-44

granting system privileges on, 16-40

redefining, 12-45

removing from the database, 15-66

direct-path INSERT, 16-54

DISABLE [constraint] clause

of CREATE TABLE, 11-91, 14-49

DISABLE ALL TRIGGERS clause

of ALTER TABLE, 10-71

DISABLE clause

of ALTER INDEX, 8-65

of ALTER TRIGGER, 11-3

of CREATE TABLE, 14-47

DISABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 9-27

DISABLE NOVALIDATE constraint state, 11-92, 14-49

DISABLE PARALLEL DML clause

of ALTER SESSION, 9-4

DISABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 8-89

of CREATE MATERIALIZED VIEW, 13-21

DISABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 9-27

DISABLE RESUMABLE clause

of ALTER SESSION, 9-6

DISABLE ROW MOVEMENT clause

of ALTER TABLE, 10-15, 10-53

of CREATE TABLE, 14-13, 14-44

DISABLE STORAGE IN ROW clause

of ALTER TABLE, 10-65

of CREATE TABLE, 14-36

DISABLE TABLE LOCK clause

of ALTER TABLE, 10-71

DISABLE THREAD clause

of ALTER DATABASE, 8-38

DISABLE VALIDATE constraint state, 11-91, 14-49

DISASSOCIATE STATISTICS statement, 15-57

DISCONNECT SESSION clause

of ALTER SYSTEM, 9-26

DISK_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 9-51

dispatcher processes

creating additional, 9-103

terminating, 9-103

DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 9-51

DISTINCT clause

of SELECT, 17-11

distinct queries, 17-11

distributed queries, 7-15

restrictions on, 7-15

DISTRIBUTED_TRANSACTIONS initialization parameter

setting with ALTER SYSTEM, 9-52

distribution

hints for, 2-99

DML. See data manipulation language (DML)

dml_event_clause

of CREATE TRIGGER, 14-85

domain indexes, 12-60, 12-78, 12-87

and LONG columns, 10-56

associating statistics with, 11-48

determining user-defined CPU and I/O costs, 16-24

invoking drop routines for, 16-7

modifying, 8-64

rebuilding, 8-60

removing from the database, 15-69

specifying alter string for, 8-64

domain_index_clause

of CREATE INDEX, 12-63

DOUBLE PRECISION datatype (ANSI), 2-34

DROP ANY CLUSTER system privilege, 16-39

DROP ANY CONTEXT system privilege, 16-39

DROP ANY DIMENSION system privilege, 16-39

DROP ANY DIRECTORY system privilege, 16-40

DROP ANY INDEX system privilege, 16-40

DROP ANY INDEXTYPE system privilege, 16-40

DROP ANY LIBRARY system privilege, 16-40

DROP ANY MATERIALIZED VIEW system privilege, 16-41

DROP ANY OPERATOR system privilege, 16-41

DROP ANY OUTLINE system privilege, 16-41

DROP ANY PROCEDURE system privilege, 16-41

DROP ANY ROLE system privilege, 16-42

DROP ANY SEQUENCE system privilege, 16-42

DROP ANY SYNONYM system privilege, 16-43

DROP ANY TABLE system privilege, 16-43

DROP ANY TRIGGER system privilege, 16-44

DROP ANY TYPE system privilege, 16-44

DROP ANY VIEW system privilege, 16-45

DROP clause

of ALTER DIMENSION, 8-46

of ALTER INDEXTYPE, 8-73

DROP CLUSTER statement, 15-60

DROP COLUMN clause

of ALTER TABLE, 10-57

DROP CONSTRAINT clause

of ALTER TABLE, 10-62

DROP constraint clause

of ALTER VIEW, 11-30

DROP CONTEXT statement, 15-62

DROP DATABASE LINK statement, 15-63

DROP DIMENSION statement, 15-64

DROP DIRECTORY statement, 15-66

DROP FUNCTION statement, 15-67

DROP INDEX statement, 15-69

DROP INDEXTYPE statement, 15-71

DROP JAVA statement, 15-73

DROP LIBRARY statement, 15-75

DROP LIBRARY system privilege, 16-40

DROP LOG GROUP clause

of ALTER TABLE, 10-28

DROP LOGFILE clause

of ALTER DATABASE, 8-15, 8-31

DROP LOGFILE MEMBER clause

of ALTER DATABASE, 8-15, 8-31

DROP MATERIALIZED VIEW LOG statement, 15-78

DROP MATERIALIZED VIEW statement, 15-76

DROP OPERATOR statement, 15-80

DROP OUTLINE statement, 15-82

DROP PACKAGE BODY statement, 15-83

DROP PACKAGE statement, 15-83

DROP PARTITION clause

of ALTER INDEX, 8-56, 8-68

of ALTER TABLE, 10-45

DROP PRIMARY constraint clause

of ALTER TABLE, 10-62

DROP PROCEDURE statement, 15-85

DROP PROFILE statement, 15-87

DROP PROFILE system privilege, 16-42

DROP PUBLIC DATABASE LINK system privilege, 16-39

DROP PUBLIC SYNONYM system privilege, 16-43

DROP ROLE statement, 15-89

DROP ROLLBACK SEGMENT statement, 15-90

DROP ROLLBACK SEGMENT system privilege, 16-42

DROP SEQUENCE statement, 16-2

DROP statements

triggers on, 14-89

DROP SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 8-32

DROP SYNONYM statement, 16-4

DROP TABLE statement, 16-6

DROP TABLESPACE statement, 16-10

DROP TABLESPACE system privilege, 16-43

DROP TRIGGER statement, 16-13

DROP TYPE BODY statement, 16-18

DROP TYPE statement, 16-15

DROP UNIQUE constraint clause

of ALTER TABLE, 10-62

DROP USER statement, 16-20

DROP USER system privilege, 16-45

DROP VALUES clause

of ALTER TABLE ... MODIFY PARTITION, 10-38

DROP VIEW statement, 16-22

drop_constraint_clause

of ALTER TABLE, 10-17

drop_index_partition_clause

of ALTER INDEX, 8-56

DRS_START initialization parameter

setting with ALTER SYSTEM, 9-53

DUAL dummy table, 2-107, 7-14

dump file

limiting size of, 9-8

DUMP function, 6-53

DY datetime format element, 2-67, 2-70

E

number format element, 2-62

E datetime format element, 2-67

EBCDIC character set, 2-44

EE datetime format element, 2-67

embedded SQL, 1-4, 7-19

precompiler support, 7-19

EMPTY_BLOB function, 6-55

EMPTY_CLOB function, 6-55

ENABLE ALL TRIGGERS clause

of ALTER TABLE, 10-71

ENABLE clause

of ALTER INDEX, 8-64, 8-65

of ALTER TRIGGER, 11-3

of CREATE TABLE, 14-47

ENABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 9-27

ENABLE NOVALIDATE constraint state, 11-90, 14-48

ENABLE PARALLEL DML clause

of ALTER SESSION, 9-4

ENABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 8-89

of CREATE MATERIALIZED VIEW, 13-21

ENABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 9-27

ENABLE RESUMABLE clause

of ALTER SESSION, 9-6

ENABLE ROW MOVEMENT clause

of ALTER TABLE, 10-15, 10-53

of CREATE TABLE, 14-13, 14-44

ENABLE STORAGE IN ROW clause

of ALTER TABLE, 10-65

of CREATE TABLE, 14-36

ENABLE TABLE LOCK clause

of ALTER TABLE, 10-70

ENABLE THREAD clause

of ALTER DATABASE, 8-38

ENABLE VALIDATE constraint state, 11-90, 14-48

enable_disable_clause

of ALTER TABLE, 10-70

of CREATE TABLE, 14-18

END BACKUP clause

of ALTER DATABASE ... DATAFILE, 8-27

of ALTER TABLESPACE, 10-88

equality test, 5-4

equijoins, 7-10

defining for a dimension, 12-42

equivalency tests, 5-10

error messages

setting language of, 9-8

ERROR_ON_OVERLAP_TIME session parameter, 9-12

ESTIMATE STATISTICS clause

of ANALYZE, 11-39

EVENTS initialization parameter

setting with ALTER SYSTEM, 9-53

EXCEPTIONS INTO clause

of ALTER TABLE, 10-51

restrictions, 10-51

EXCHANGE PARTITION clause

of ALTER TABLE, 10-15, 10-49

EXCHANGE SUBPARTITION clause

of ALTER TABLE, 10-15, 10-49

exchange_table_partition clause

of ALTER TABLE, 10-15

exchange_table_subpartition clause

of ALTER TABLE, 10-15

exchanging partitions

restrictions on, 10-52

EXCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 8-98

of CREATE MATERIALIZED VIEW LOG, 13-34

EXCLUSIVE lock mode, 16-73

EXECUTE ANY INDEXTYPE system privilege, 16-40

EXECUTE ANY OPERATOR system privilege, 16-41

EXECUTE ANY PROCEDURE system privilege, 16-41

EXECUTE ANY TYPE system privilege, 16-44

EXECUTE object privilege, 16-48

on a function, procedure, or package, 16-50

on an indextype, 16-51

on an object type, 16-50

on an operator, 16-51

EXECUTE_CATALOG_ROLE role, 16-46

execution plans

determining, 16-24

dropping outlines for, 15-82

saving, 13-41

EXEMPT ACCESS POLICY system privilege, 16-45

EXISTS condition, 5-11, 5-12

EXISTSNODE function, 6-55

EXP function, 6-56

EXP_FULL_DATABASE role, 16-46

EXPLAIN PLAN statement, 16-24

explicit data conversion, 2-46, 2-49

expressions

CASE, 4-5

changing declared type of, 6-182

comparing, 6-47

compound, 4-4

computing with the DUAL table, 7-14

CURSOR, 4-7

datetime, 4-9

in SQL syntax, 4-2

interval, 4-11

list of, 4-15

object access, 4-12

scalar subqueries as, 4-13

simple, 4-3

type constructor, 4-13

variable, 4-15

extended rowids, 2-32

base 64, 2-32

not directly available, 2-32

EXTENT MANAGEMENT clause

for temporary tablespaces, 14-81

of CREATE DATABASE, 12-23

of CREATE TABLESPACE, 14-70, 14-75

of CREATE TEMPORARY TABLESPACE, 14-80

EXTENT MANAGEMENT DICTIONARY clause

of CREATE TABLESPACE, 14-75

EXTENT MANAGEMENT LOCAL clause

of CREATE TABLESPACE, 14-75

of CREATE TEMPORARY TABLESPACE, 14-82

extents

allocating for partitions, 10-28

allocating for subpartitions, 10-28

allocating for tables, 10-28

restricting access by instances, 8-58

specifying maximum number for an object, 17-53

specifying number allocated upon object creation, 17-53

specifying the first for an object, 17-51

specifying the percentage of size increase, 17-52

specifying the second for an object, 17-52

external functions, 12-47, 13-57

external LOBs, 2-25

external procedures, 13-55, 13-57

running from remote database, 13-3

external tables, 14-29

altering, 10-67

creating, 14-32

restrictions on, 14-32

external users, 13-72, 15-31

EXTRACT (datetime) function, 6-57

EXTRACT (XML) function, 6-58

F

FAILED_LOGIN_ATTEMPTS parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

FAL_CLIENT initialization parameter

setting with ALTER SYSTEM, 9-53

FAL_SERVER initialization parameter

setting with ALTER SYSTEM, 9-53

FAST_START_IO_TARGET initialization parameter

setting with ALTER SESSION, 9-54

FAST_START_MTTR_TARGET initialization parameter

setting with ALTER SYSTEM, 9-54

FAST_START_PARALLEL_ROLLBACK initialization parameter

setting with ALTER SYSTEM, 9-55

features

new, xxvii

FF datetime format element, 2-67

files

specifying as a redo log file group, 16-28

specifying as datafiles, 16-28

specifying as tempfiles, 16-28

filespec clause, 16-28

of CREATE CONTROLFILE, 12-16

of CREATE DATABASE, 12-24

of CREATE LIBRARY, 13-2

of CREATE TABLESPACE, 14-69

of CREATE TEMPORARY TABLESPACE, 14-79

FINAL clause

of CREATE TYPE, 15-12, 15-13

FIPS

compliance, B-9

flagging, 9-12

FIRST function, 6-59

FIRST_ROWS(n) hint, 2-91

FIRST_VALUE function, 6-61

FIXED_DATE initialization parameter

setting with ALTER SYSTEM, 9-55

FLAGGER session parameter, 9-12

FLOAT datatype, 2-14

DB2, 2-35

SQL/DS, 2-35

FLOAT datatype (ANSI), 2-34

floating-point numbers, 2-12, 2-14

FLOOR function, 6-63

FLUSH SHARED POOL clause

of ALTER SYSTEM, 9-28

FM format model modifier, 2-73

FM number format element, 2-62

FOR clause

of ANALYZE ... COMPUTE STATISTICS, 11-37

of ANALYZE ... ESTIMATE STATISTICS, 11-37

of CREATE INDEXTYPE, 12-88

of EXPLAIN PLAN, 16-26

FOR EACH ROW clause

of CREATE TRIGGER, 14-92

FOR UPDATE clause

of CREATE MATERIALIZED VIEW, 13-21

of SELECT, 17-10, 17-23

FORCE ANY TRANSACTION system privilege, 16-45

FORCE clause

of COMMIT, 11-71

of CREATE VIEW, 15-39

of DISASSOCIATE STATISTICS, 15-59

of DROP INDEX, 15-70

of DROP INDEXTYPE, 15-72

of DROP OPERATOR, 15-81

of DROP TYPE, 16-16

of REVOKE, 16-91

of ROLLBACK, 16-98

FORCE PARALLEL DML clause

of ALTER SESSION, 9-4

FORCE TRANSACTION system privilege, 16-45

FOREIGN KEY clause

of constraint_clause, 11-83

foreign key constraints, 11-83

foreign tables

rowids of, 2-33

format models, 2-59

changing the return format, 2-60

date, 2-65

changing, 2-66

default format, 2-66

format elements, 2-66

maximum length, 2-66

modifiers, 2-73

number, 2-61

number, elements of, 2-62

specifying, 2-60

XML, 2-76

formats

for dates and numbers. See format models

of return values from the database, 2-59

of values stored in the database, 2-59

free lists

specifying for a table, partition, cluster, or index, 17-54

FREELIST GROUPS parameter

of STORAGE clause, 17-54

FREELISTS parameter

of STORAGE clause, 17-54

FROM clause

of queries, 7-10

FROM COLUMNS clause

of DISASSOCIATE STATISTICS, 15-58

FROM FUNCTIONS clause

of DISASSOCIATE STATISTICS, 15-58

FROM INDEXES clause

of DISASSOCIATE STATISTICS, 15-58

FROM INDEXTYPES clause

of DISASSOCIATE STATISTICS, 15-58

FROM PACKAGES clause

of DISASSOCIATE STATISTICS, 15-58

FROM TYPES clause

of DISASSOCIATE STATISTICS, 15-58

FROM_TZ function, 6-63

FULL hint, 2-92

full outer joins, 17-16

function expressions

built-in, 4-11

user-defined, 4-11

function-based indexes, 12-60

and query rewrite, 9-9

creating, 12-69

disabling, 9-94

enabling, 8-60, 8-64, 8-65, 9-94

enabling and disabling, 8-60

functions

See also SQL functions

3GL, calling, 13-2

analytic

user-defined, 12-56

associating statistics with, 11-48

avoiding run-time compilation, 8-48

built_in

as expressions, 4-11

calling, 11-64

changing the declaration of, 12-51

changing the definition of, 12-51

datatype of return value, 12-53

datetime, 6-5

DECODE, 6-47

defining an index on, 12-69

examples, 12-57

executing, 11-64

from parallel query processes, 12-54

external, 12-47, 13-57

inverse distribution, 6-109, 6-112

issuing COMMIT or ROLLBACK statements, 9-3

linear regression, 6-120

naming rules, 2-109

partitioning

among parallel query processes, 12-54

privileges executed with, 11-13, 15-9

recompiling explicitly, 8-49

recompiling invalid, 8-48

re-creating, 12-51, 12-92

removing from the database, 15-67

returning collections, 12-55

returning results iteratively, 12-55

schema executed in, 11-13, 15-9

specifying schema and user privileges for, 12-53

stored, 12-47

storing return value of, 11-65

synonyms for, 14-2

table, 12-55

user_defined

as expressions, 4-11

user-defined, 6-201

aggregate, 12-56

using a saved copy, 12-54

FUNCTIONS clause

of ASSOCIATE STATISTICS, 11-47, 11-48

FX format model modifier, 2-73

G

G number format element, 2-62

GC_FILES_TO_LOCKS initialization parameter

setting with ALTER SYSTEM, 9-55

general recovery clause

of ALTER DATABASE, 8-11, 8-19

global database names

enforcing resolution, 9-56

global indexes. See indexes, globally partitioned

GLOBAL PARTITION BY RANGE clause

of CREATE INDEX, 11-89, 12-64, 12-75, 14-51

GLOBAL QUERY REWRITE system privilege, 16-40, 16-41

GLOBAL TEMPORARY clause

of CREATE TABLE, 14-20

global users, 13-72, 15-31

GLOBAL_CONTEXT_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 9-56

GLOBAL_NAMES initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-56

global_partitioned_index_clause

of CREATE INDEX, 12-64

Globalization Support

change session settings, 9-8

globally partitioned indexes, 11-89, 12-75, 12-77, 14-51

GRANT ANY PRIVILEGE system privilege, 16-45

GRANT ANY ROLE system privilege, 16-42

GRANT clause

of ALTER USER, 11-24

GRANT CONNECT THROUGH clause

of ALTER USER, 11-22, 11-24

grantee_clause

of REVOKE, 16-87

GRAPHIC datatype

DB2, 2-35

SQL/DS, 2-35

greater than or equal to tests, 5-5

greater than tests, 5-5

GREATEST function, 6-64

GROUP BY clause

CUBE extension, 17-20

identifying duplicate groupings, 6-65

of SELECT and subqueries, 17-9, 17-19

ROLLUP extension of, 17-20

group comparison conditions, 5-6

GROUP_ID function, 6-65

GROUPING function, 6-66

grouping sets, 17-20

GROUPING SETS clause

of SELECT and subqueries, 17-20

GROUPING_ID function, 6-67

groupings

filtering out duplicate, 6-65

H

hash clusters

creating, 12-6

single-table, creating, 12-7

specifying hash function for, 12-7

HASH hint, 2-92

HASH IS clause

of CREATE CLUSTER, 12-7

hash joins

allocating memory for, 9-8

enabling and disabling, 9-8

hash partitioning clause

of CREATE TABLE, 14-17, 14-42

hash partitions

adding, 10-44

coalescing, 10-38

HASH_AJ hint, 2-92

HASH_AREA_SIZE initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-57

HASH_JOIN_ENABLED initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-57

HASHKEYS clause

of CREATE CLUSTER, 12-6

HAVING condition

of GROUP BY clause, 17-21

heap-organized tables

creating, 14-6

hexadecimal value

returning, 2-64

HEXTORAW function, 6-69

HH datetime format element, 2-67

HH12 datetime format element, 2-67

HH24 datetime format element, 2-67

HI_SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 9-58

hierarchical queries, 2-82, 7-3, 17-18

child rows, 2-82, 7-4

illustrated, 2-83

leaf rows, 2-82

parent rows, 2-82, 7-4

retrieving root and node values, 6-147

hierarchical query clause

of SELECT and subqueries, 17-8

hierarchies

adding to a dimension, 8-46

dropping from a dimension, 8-46

of dimensions, defining, 12-41

HIERARCHY clause

of CREATE DIMENSION, 12-40, 12-41

high water mark

of clusters, 8-6

of indexes, 8-57

of tables, 10-29, 11-35

hints, 7-3

ALL_ROWS hint, 2-89

AND_EQUAL hint, 2-89

CACHE hint, 2-90

CLUSTER hint, 2-90

FIRST_ROWS hint, 2-91

FULL hint, 2-92

HASH hint, 2-92

in SQL statements, 2-87

INDEX hint, 2-92

INDEX_ASC hint, 2-93

INDEX_DESC hint, 2-93

NO_EXPAND hint, 2-95

NO_MERGE hint, 2-96

NO_PUSH_PRED hint, 2-96

NOCACHE hint, 2-95

NOPARALLEL hint, 2-96

NOREWRITE hint, 2-97

ORDERED hint, 2-97

PARALLEL hint, 2-98

passing to the optimizer, 17-63

PQ_DISTRIBUTE hint, 2-99

PUSH_PRED hint, 2-99

PUSH_SUBQ hint, 2-100

REWRITE hint, 2-100

ROWID hint, 2-100

RULE hint, 2-100

syntax, 2-87

USE_CONCAT hint, 2-101

USE_MERGE hint, 2-102

USE_NL hint, 2-102

histograms

creating equiwidth, 6-198

HS_ADMIN_ROLE role, 16-47

HS_AUTOREGISTER initialization parameter

setting with ALTER SYSTEM, 9-58

I

I datetime format element, 2-67

IDENTIFIED BY clause

of ALTER ROLE. See CREATE ROLE

of CREATE DATABASE LINK, 12-36

of SET ROLE, 17-43

IDENTIFIED EXTERNALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER. See CREATE USER

of CREATE ROLE, 13-72

of CREATE USER, 15-31

IDENTIFIED GLOBALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER, 11-23

of CREATE ROLE, 13-72

of CREATE USER, 15-31

IDLE_TIME parameter

of ALTER PROFILE, 8-110

IFILE initialization parameter

setting with ALTER SYSTEM, 9-58

IMMEDIATE clause

of SET CONSTRAINTS, 17-40

IMP_FULL_DATABASE role, 16-46

implementation_clause

of CREATE OPERATOR, 13-38

implicit data conversion, 2-46, 2-48

IN OUT parameter

of CREATE FUNCTION, 12-52

of CREATE PROCEDURE, 13-60

IN parameter

of CREATE function, 12-52

of CREATE PROCEDURE, 13-60

INCLUDING CONTENTS clause

of DROP TABLESPACE, 16-11

INCLUDING DATAFILES clause

of ALTER DATABASE TEMPFILE DROP clause, 8-28

INCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 8-98

of CREATE MATERIALIZED VIEW LOG, 13-34

INCLUDING TABLES clause

of DROP CLUSTER, 15-61

incomplete object types, 15-3

creating, 15-3, 15-5

INCREMENT BY clause

of ALTER SEQUENCE. See CREATE SEQUENCE

INCREMENT BY parameter

of CREATE SEQUENCE, 13-83

INDEX clause

of ANALYZE, 11-33

of CREATE CLUSTER, 12-6

INDEX hint, 2-92

INDEX object privilege, 16-48

on a table, 16-49

INDEX_ASC hint, 2-93

INDEX_DESC hint, 2-93

index_subpartition_clause

of CREATE INDEX, 12-66

indexed clusters

creating, 12-6

indexes, 8-59

access path, optimizing for, 9-9

allocating new extents for, 8-58

application-specific, 12-87

ascending, 12-71

based on indextypes, 12-78

bitmap, 12-67

bitmap join, 12-79

B-tree, 12-60

changing attributes, 8-59

changing parallelism of, 8-58

collecting statistics on, 11-33

on composite-partitioned tables, 12-77

creating, 12-60

deallocating unused space from, 8-57

descending, 12-71

and query rewrite, 12-71

as function-based indexes, 12-71

direct-path inserts, logging, 8-59

disassociating statistics types from, 15-70

domain, 12-60, 12-78, 12-87

dropping index partitions, 15-70

examples, 12-80

function-based, 12-60

creating, 12-69

globally partitioned, 11-89, 12-75, 12-77, 14-51

updating, 10-39

granting system privileges on, 16-40

on hash-partitioned tables, 12-77

join, bitmap, 12-79

key compression of, 8-62

key compression, enabling, 8-60

keys, eliminating repetition, 8-60

locally partitioned, 12-77

logging rebuild operations, 8-60

logging rebuild operations on, 8-64

marking as UNUSABLE, 8-65

merging block contents, 8-60

merging contents of index blocks, 8-65

modifying attributes, 8-60

moving, 8-60

on clusters, 12-68

on index-organized tables, 12-68

on nested table storage tables, 12-68

on partitioned tables, 12-68

on scalar typed object attributes, 12-68

on table columns, 12-68

online, 12-74

parallelizing creation of, 12-75

partitioned, 2-104, 12-60

user-defined, 11-89, 12-75, 14-51

partitioning, 12-75

partitions, 12-75

adding new, 8-68

changing default attributes, 8-67

changing physical attributes, 8-59

changing storage characteristics, 8-67

deallocating unused space from, 8-57

dropping, 8-68

marking UNUSABLE, 8-68, 10-38

modifying the real characteristics, 8-67

preventing use of, 8-65

rebuilding, 8-60

rebuilding unusable, 10-38

re-creating, 8-60

removing, 8-67

renaming, 8-68

specifying tablespace, 8-60

specifying tablespace for, 8-62

splitting, 8-67, 8-68

physical attributes, 12-72

preventing use of, 8-65

on range-partitioned tables, 12-77

rebuilding, 8-60

rebuilding while online, 8-63

re-creating, 8-60

removing from the database, 15-69

renaming, 8-60, 8-65

reverse, 8-60, 8-62, 12-73

specifying tablespace for, 8-60, 8-62

statistics on, 12-74

statistics on rebuild, 8-63

statistics on usage, 8-66

storage attributes, 12-72, 17-49

subpartitions

allocating extents for, 8-69

changing default attributes, 8-67

changing physical attributes, 8-59

changing storage characteristics, 8-67

deallocating unused space from, 8-57, 8-69

marking UNUSABLE, 8-69

modifying, 8-60

moving, 8-60

preventing use of, 8-65

rebuilding, 8-60

re-creating, 8-60

renaming, 8-68

specifying tablespace, 8-60

specifying tablespace for, 8-62

tablespace containing, 12-72

unique, 12-67

unsorted, 12-73

used to enforce constraints, 10-63, 14-50

validating structure, 11-40

INDEXES clause

of ASSOCIATE STATISTICS, 11-47, 11-48

index-organized tables

bitmap indexes on, creating, 14-30

creating, 14-6

mapping tables, 10-69

moving, 10-42

mapping tables, creating, 14-30

modifying, 10-33

moving, 10-68

overflow segments

specifying storage, 10-34, 14-42

partitioned, updating secondary indexes, 8-68

PCT_ACCESS_DIRECT statistics, 11-35

primary key indexes

coalescing, 10-35

updating, 10-35

rebuilding, 10-68

rowids of, 2-33

secondary indexes, updating, 8-66

INDEXTYPE clause

of COMMENT, 11-69

of CREATE INDEX, 12-63, 12-78

indextypes

adding operators, 8-72

altering, 8-72

associating statistics with, 11-48

changing implementation type, 8-72

comments on, 11-69

creating, 12-87

disassociating from statistics types, 15-71

drop routines, invoking, 15-70

granting system privileges on, 16-40

indexes based on, 12-78

instances, 12-60

removing from the database, 15-71

INDEXTYPES clause

of ASSOCIATE STATISTICS, 11-47, 11-48

in-doubt transactions

forcing, 11-71

forcing commit of, 11-71

forcing rollback, 16-98

rolling back, 16-96

inequality test, 5-4

INITCAP function, 6-69

INITIAL parameter

of STORAGE clause, 17-51

initialization parameters

changing session settings, 9-6

CIRCUITS, 9-38

INITIALIZED EXTERNALLY clause

of CREATE CONTEXT, 12-12

INITIALIZED GLOBALLY clause

of CREATE CONTEXT, 12-12

INITIALLY DEFERRED clause

of constraint_clause, 11-87

INITIALLY IMMEDIATE clause

of constraint_clause, 11-87

INITRANS parameter

of ALTER CLUSTER, 8-5

of ALTER INDEX, 8-53, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER TABLE, 10-26

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE

of CREATE TABLE, 14-26

inline views, 7-12

IN-lists, 2-101

inner joins, 7-11, 17-16

INSERT ANY TABLE system privilege, 16-43

INSERT clause

of MERGE, 16-76

INSERT object privilege, 16-48

on a table, 16-49

on a view, 16-49

INSERT statement, 16-54

append, 2-90

triggers on, 14-88

inserts

and simultaneous update, 16-75

conditional multitable, 16-64

conventional, 16-54

direct-path, 16-54

multitable, 16-63

single-table, 16-58

instance recovery

continue after interruption, 8-19

INSTANCE session parameter, 9-13

INSTANCE_GROUPS initialization parameter

setting with ALTER SYSTEM, 9-59

INSTANCE_NAME initialization parameter

setting with ALTER SYSTEM, 9-59

INSTANCE_NUMBER initialization parameter

setting with ALTER SYSTEM, 9-60

instances

global name resolution for, 9-56

making index extents available to, 8-58

memory requirements of, 9-44

setting parameters for, 9-30

INSTANTIABLE clause

of CREATE TYPE, 15-12

INSTEAD OF clause

of CREATE TRIGGER, 14-87

INSTEAD OF triggers, 14-87

INSTR function, 6-70

INSTR2 function, 6-70

INSTR4 function, 6-70

INSTRB function, 6-70

INSTRC function, 6-70

INT datatype (ANSI), 2-34

INTEGER datatype

ANSI, 2-34

DB2, 2-35

SQL/DS, 2-35

integers

generating unique, 13-81

in SQL syntax, 2-53

precision of, 2-53

specifying, 2-12

syntax of, 2-53

integrity constraints. See constraints

internal LOBs, 2-25

International Standards Organization (ISO), B-1

standards, xvii, 1-2, B-2

INTERSECT set operator, 3-6, 17-21

interval datatypes, 2-16

INTERVAL DAY TO SECOND datatype, 2-22

INTERVAL expressions, 4-11

INTERVAL YEAR TO MONTH datatype, 2-22

INTO clause

of EXPLAIN PLAN, 16-25

of INSERT, 16-58

INVALIDATE GLOBAL INDEXES clause

of ALTER TABLE, 10-39

inverse distribution functions, 6-109, 6-112

invoker_rights_clause

of ALTER JAVA, 8-75

of CREATE FUNCTION, 12-53

of CREATE JAVA, 12-91, 12-93

of CREATE PACKAGE, 13-46

of CREATE PROCEDURE, 13-58

of CREATE TYPE, 11-13, 15-9

invoker-rights functions

defining, 12-53

IS NOT NULL operator, 5-11

IS NULL operator, 5-11

IS OF type condition, 5-16

ISO. See International Standards Organization (ISO)

ISOLATION_LEVEL session parameter, 9-13

IW datetime format element, 2-67

IY datetime format element, 2-67

IYY datetime format element, 2-67

IYYY datetime format element, 2-67

J

J datetime format element, 2-67

Java

class

creating, 12-90, 12-92

dropping, 15-73

resolving, 8-74, 12-92

Java source schema object

creating, 12-92

methods

return type of, 15-13

resource

creating, 12-90, 12-93

dropping, 15-73

schema object

name resolution of, 12-94

source

compiling, 8-74, 12-92

creating, 12-90

dropping, 15-73

storage formats

CustomDatum, 15-11

SQLData, 15-11

JAVA clause

of CREATE TYPE, 15-14

of CREATE TYPE BODY, 15-26

Java methods

mapping to an object type, 15-14

JAVA_MAX_SESSIONSPACE_LIMIT initialization parameter

setting with ALTER SYSTEM, 9-61

JAVA_MAX_SESSIONSPACE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-60

JAVA_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 9-60

JOB_QUEUE_PROCESSES initialization parameter

setting with ALTER SYSTEM, 9-61

JOIN clause

of CREATE DIMENSION, 12-40

JOIN KEY clause

of ALTER DIMENSION, 8-45

of CREATE DIMENSION, 12-42

join views

example, 15-46

making updatable, 15-43

modifying, 15-53, 16-60, 17-68

joins, 7-9

conditions

defining, 7-9

cross, 17-17

equijoins, 7-10

full outer, 17-16

inner, 7-11, 17-16

left outer, 17-16

natural, 17-17

nested loop, optimizing for, 9-9

outer, 7-11

restrictions, 7-11

parallel, and PQ_DISTRIBUTE hint, 2-99

right outer, 17-16

self, 7-10

without join conditions, 7-10

julian dates, 2-20

K

key compression, 14-30

definition, 8-62

disabling, 8-62, 12-72

enabling, 8-60

of index rebuild, 10-69

of indexes

disabling, 8-62

of index-organized tables, 14-30

key-preserved tables, 15-43

keywords, 2-108

optional, A-4

required, A-4

KILL SESSION clause

of ALTER SYSTEM, 9-27

L

L number format element, 2-62

LAG function, 6-72

LANGUAGE clause

of CREATE PROCEDURE, 13-62

of CREATE TYPE, 15-14

of CREATE TYPE BODY, 15-26

large objects. See LOB datatypes

LARGE_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 9-61

LAST function, 6-73

LAST_DAY function, 6-75

LAST_VALUE function, 6-76

LEAD function, 6-78

LEAST function, 6-80

left outer joins, 17-16

LENGTH function, 6-80

LENGTH2 function, 6-80

LENGTH4 function, 6-80

LENGTHB function, 6-80

LENGTHC function, 6-80

less than tests, 5-5

LEVEL clause

of ALTER DIMENSION, 8-45

of CREATE DIMENSION, 12-40, 12-41

LEVEL pseudocolumn, 2-82, 17-18

and hierarchical queries, 2-82

level_clause

of CREATE DIMENSION, 12-40

levels

adding to a dimension, 8-46

dropping from a dimension, 8-46

of dimensions, defining, 12-41

libraries

creating, 13-2

granting system privileges on, 16-40

re-creating, 13-3

removing from the database, 15-75

library units. See Java schema objects

LICENSE_MAX_SESSIONS initialization parameter

setting with ALTER SYSTEM, 9-62

LICENSE_MAX_USERS initialization parameter

setting with ALTER SYSTEM, 9-62

LICENSE_SESSIONS_WARNING initialization parameter

setting with ALTER SYSTEM, 9-63

licenses

changing limits, 9-62, 9-63

licensing

changing limits, 9-62

LIKE conditions, 5-12

linear regression functions, 6-120

LIST CHAINED ROWS clause

of ANALYZE, 11-42

list partitioning

adding partitions, 10-45

adding values, 10-38

creating partitions, 14-41

dropping values, 10-38

listeners

registering, 9-30

literals

in SQL statements and functions, 2-51

in SQL syntax, 2-51

LN function, 6-81

LOB columns

adding, 10-53

creating from LONG columns, 2-15, 10-56

modifying, 10-55

modifying storage, 10-65

restricted in joins, 7-10

storage characteristics of materialized views, 8-83

LOB datatypes, 2-25

LOB index clause

of ALTER TABLE, 10-66

of CREATE TABLE, 14-36

LOB storage clause

for partitions, 10-66

of ALTER MATERIALIZED VIEW, 8-80, 8-83

of ALTER TABLE, 10-20, 10-65

of CREATE MATERIALIZED VIEW, 13-11, 13-13

of CREATE TABLE, 14-12, 14-35

LOBs

attributes, initializing, 2-26

CACHE READS setting, 2-29

columns

difference from LONG and LONG RAW, 2-26

populating, 2-26

external, 2-25

indexes for, 14-36

internal, 2-25

locators, 2-25

logging attribute, 14-27

modifying physical attributes, 10-67

number of bytes manipulated in, 14-36

saving values in a cache, 10-65, 14-45

specifying directories for, 12-44

storage

attributes, 14-35

characteristics, 14-26

in-line, 14-36

tablespace for

defining, 14-27

LOCAL clause

of CREATE INDEX, 12-64, 12-77

local users, 13-72, 15-31

LOCAL_LISTENER initialization parameter

setting with ALTER SYSTEM, 9-63

local_partitioned_index_clause

of CREATE INDEX, 12-64

locally managed tablespaces

altering, 10-85

storage attributes, 17-49

locally partitioned indexes, 12-77

LOCALTIMESTAMP function, 6-82

location transparency, 14-2

LOCK ANY TABLE system privilege, 16-43

LOCK TABLE statement, 16-71

LOCK_NAME_SPACE initialization parameter

setting with ALTER SYSTEM, 9-64

LOCK_SGA initialization parameter

setting with ALTER SYSTEM, 9-64

locking

automatic

overriding, 16-71

locks. See table locks

log data

collection during update operations, 8-31

supplemental

dropping, 8-32

log file clauses

of ALTER DATABASE, 8-15

log files

adding, 8-29

dropping, 8-29

modifying, 8-29

registering, 8-35

renaming, 8-29

setting session path for, 9-8

specifying for the database, 12-25

LOG function, 6-83

log groups

dropping, 10-28

LOG_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 9-64

LOG_ARCHIVE_DEST_n initialization parameter

overriding DELAY setting, 8-24

setting with ALTER SESSION, 9-8, 9-65

LOG_ARCHIVE_DEST_STATE_n initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-67

LOG_ARCHIVE_DUPLEX_DEST initialization parameter

setting with ALTER SYSTEM, 9-67

LOG_ARCHIVE_FORMAT initialization parameter

setting with ALTER SYSTEM, 9-67

LOG_ARCHIVE_MAX_PROCESSES initialization parameter

setting with ALTER SYSTEM, 9-68

LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-68

LOG_ARCHIVE_START parameter

of ALTER SYSTEM, 9-69

LOG_ARCHIVE_TRACE initialization parameter

setting with ALTER SYSTEM, 9-69

LOG_BUFFER initialization parameter

setting with ALTER SYSTEM, 9-69

LOG_CHECKPOINT_INTERVAL initialization parameter

setting with ALTER SYSTEM, 9-70

LOG_CHECKPOINT_TIMEOUT initialization parameter

setting with ALTER SYSTEM, 9-70

LOG_CHECKPOINTS_TO_ALERT initialization parameter

setting with ALTER SYSTEM, 9-70

LOG_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 9-71

LOGFILE clause

OF CREATE DATABASE, 12-25

LOGFILE GROUP clause

of CREATE CONTROLFILE, 12-16

logging

and redo log size, 14-28

specifying minimal, 14-28

LOGGING clause

of ALTER INDEX, 8-59

of ALTER INDEX ... REBUILD, 8-64

of ALTER MATERIALIZED VIEW, 8-84

of ALTER MATERIALIZED VIEW LOG, 8-97

of ALTER TABLE, 10-27

of ALTER TABLESPACE, 10-90

of CREATE MATERIALIZED VIEW, 13-14

of CREATE MATERIALIZED VIEW LOG, 13-32

of CREATE TABLE, 14-27

of CREATE TABLESPACE, 14-73

logical conditions, 5-7

LOGICAL_READS_PER_CALL parameter

of ALTER PROFILE, 8-110

LOGICAL_READS_PER_SESSION parameter

of ALTER PROFILE, 8-110

of ALTER RESOURCE COST, 8-114

LOGMNR_MAX_PERSISTENT_SESSIONS initialization parameter

setting with ALTER SYSTEM, 9-71

LOGOFF database event

triggers on, 14-91

LOGON database event

triggers on, 14-90

LONG columns

and domain indexes, 10-56

converting to LOB, 2-15, 10-56

restrictions on, 2-15

to store text strings, 2-14

to store view definitions, 2-14

where referenced from, 2-15

LONG datatype, 2-14

in triggers, 2-16

LONG RAW datatype, 2-25

converting from CHAR data, 2-25

LONG VARCHAR datatype

DB2, 2-35

SQL/DS, 2-35

LONG VARGRAPHIC datatype

DB2, 2-35

SQL/DS, 2-35

LOWER function, 6-83

LPAD function, 6-84

LTRIM function, 6-85

M

MAKE_REF function, 6-85

MANAGE TABLESPACE system privilege, 16-43

managed recovery

of database, 8-12

wait period of, 8-24

managed standby recovery

as background process, 8-25

overriding delays, 8-25

returning control during, 8-25

terminating, 8-24

MANAGED STANDBY RECOVERY clause

of ALTER DATABASE, 8-23

MAP MEMBER clause

of ALTER TYPE, 11-11

of CREATE TYPE, 15-26

MAP methods

specifying, 11-11

MAPPING TABLE clause

of ALTER TABLE, 10-42, 10-69

mapping tables

of index-organized tables, 10-69

master databases, 13-5

master tables, 13-5

MATCHES condition, 5-2

materialized join views, 13-28

materialized view logs, 13-28

adding columns, 8-97

creating, 13-28

excluding new values from, 8-98

logging changes to, 8-97

object ID based, 8-98

parallelizing creation, 13-32

partition attributes, changing, 8-96

partitioned, 13-33

physical attributes

specifying, 13-31

physical attributes, changing, 8-96

removing from the database, 15-78

required for fast refresh, 13-28

rowid based, 8-98

saving new values in, 8-98

saving old values in, 13-34

storage attributes

specifying, 13-31

materialized views, 8-86, 13-17

allowing update of, 13-21

changing from rowid-based to primary-key-based, 8-88

changing to primary-key-based, 8-98

complete refresh, 8-87, 13-18

constraints on, 11-87

creating, 13-5

creating comments about, 11-67

for data warehousing, 13-5

degree of parallelism, 8-84, 8-96

during creation, 13-15

enabling and disabling query rewrite, 13-21

examples, 13-23, 13-35

fast refresh, 8-86, 13-17, 13-18

forced refresh, 8-87

granting system privileges on, 16-40

index characteristics

changing, 8-84

indexes that maintain, 13-16

join, 13-28

LOB storage attributes, 8-83

logging changes to, 8-84

master table, dropping, 15-77

object type, creating, 13-12

partitions, 8-83

physical attributes, 13-13

changing, 8-83

primary key, 13-19

recording values in master table, 8-97

query rewrite

eligibility for, 11-87

enabling and disabling, 8-89

re-creating during refresh, 8-87

refresh mode

changing, 8-86

refresh time

changing, 8-86

refreshing after DML on master table, 8-88, 13-18

refreshing on next COMMIT, 8-87, 13-18

removing from the database, 15-76

for replication, 13-5

restricting scope of, 13-12

retrieving data from, 17-4

revalidating, 8-90

rowid, 13-19

rowid values

recording in master table, 8-97

saving blocks in a cache, 8-85

storage attributes, 13-13

changing, 8-83

subquery, 13-22

suppressing creation of default index, 13-17

synonyms for, 14-2

when to populate, 13-15

MAX function, 6-86

MAX_COMMIT_PROPAGATION_DELAY initialization parameter

setting with ALTER SYSTEM, 9-72

MAX_DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 9-72

MAX_DUMP_FILE_SIZE initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-73

MAX_ENABLED_ROLES initialization parameter

setting with ALTER SYSTEM, 9-73

MAX_SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 9-73

MAXDATAFILES parameter

of CREATE CONTROLFILE, 12-18

of CREATE DATABASE, 12-26

MAXEXTENTS parameter

of STORAGE clause, 17-53

MAXINSTANCES parameter

of CREATE CONTROLFILE, 12-18

OF CREATE DATABASE, 12-26

MAXLOGFILES parameter

of CREATE CONTROLFILE, 12-17

of CREATE DATABASE, 12-25

MAXLOGHISTORY parameter

of CREATE CONTROLFILE, 12-17

of CREATE DATABASE, 12-26

MAXLOGMEMBERS parameter

of CREATE CONTROLFILE, 12-17

of CREATE DATABASE, 12-25

MAXSIZE clause

of ALTER DATABASE, 8-14

of CREATE DATABASE, 12-23

of CREATE TABLESPACE, 14-70

of CREATE TEMPORARY TABLESPACE, 14-80

MAXTRANS parameter

of ALTER CLUSTER, 8-5

of ALTER INDEX, 8-53, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER TABLE, 10-26

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE

of CREATE TABLE, 14-26

MAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 13-83

media recovery

avoid on startup, 8-27

designing, 8-19

disabling, 8-25

from specified redo logs, 8-19

of database, 8-19

of datafiles, 8-19

of standby database, 8-19

of tablespaces, 8-19

parallelizing, 8-22

performing ongoing, 8-23

preparing for, 8-29

restrictions, 8-19

sustained standby recovery, 8-23

median values, 6-112

MEMBER clause

of ALTER TYPE, 11-11

of CREATE TYPE, 15-12

of CREATE TYPE BODY, 15-25

membership conditions, 5-9

MERGE hint, 2-94

MERGE PARTITIONS clause

of ALTER TABLE, 10-49

MERGE statement, 16-75

MERGE_AJ hint, 2-92

merge_insert_clause

of MERGE, 16-76

merge_update_clause

of MERGE, 16-75, 16-76

methods

overriding a method a supertype, 15-13

preventing overriding in subtypes, 15-13

static, 15-12

without implementation, 15-13

MI datetime format element, 2-67

MI number format element, 2-62

migrated rows

listing, 11-42

of clusters, 11-36

MIN function, 6-89

MINEXTENTS parameter

of STORAGE clause, 17-53

MINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 10-32

MINIMUM EXTENT clause

of ALTER TABLESPACE, 10-87

of CREATE TABLESPACE, 14-73

MINUS set operator, 3-6, 17-21

MINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 13-84

MM datetime format element, 2-67

MOD function, 6-90

MODE clause

of LOCK TABLE, 16-73

MODIFY clause

of ALTER TABLE, 10-55

MODIFY CONSTRAINT clause

of ALTER TABLE, 10-17, 10-61

of ALTER VIEW, 11-30

MODIFY DEFAULT ATTRIBUTES clause

of ALTER INDEX, 8-55, 8-67

of ALTER TABLE, 10-36

MODIFY LOB clause

of ALTER TABLE, 10-67

MODIFY LOB storage clause

of ALTER MATERIALIZED VIEW, 8-80, 8-83

of ALTER TABLE, 10-67

MODIFY NESTED TABLE clause

of ALTER TABLE, 10-17, 10-61

MODIFY PARTITION clause

of ALTER INDEX, 8-55, 8-67

of ALTER MATERIALIZED VIEW, 8-84

of ALTER TABLE, 10-37

MODIFY scoped_table_ref_constraint clause

of ALTER MATERIALIZED VIEW, 8-86

MODIFY SUBPARTITION clause

of ALTER INDEX, 8-56, 8-69

of ALTER TABLE, 10-40

MODIFY VARRAY clause

of ALTER TABLE, 10-22, 10-67

modify_collection_retrieval_clause

of ALTER TABLE, 10-17

modify_varray_storage_clause

of ALTER TABLE, 10-22

MON datetime format element, 2-67, 2-70

MONITORING clause

of ALTER TABLE, 10-31

of CREATE TABLE, 14-46

MONITORING USAGE clause

of ALTER INDEX, 8-66

MONTH datetime format element, 2-67, 2-70

MONTHS_BETWEEN function, 6-91

MOUNT clause

of ALTER DATABASE, 8-17

MOVE clause

of ALTER TABLE, 10-23, 10-68

MOVE ONLINE clause

of ALTER TABLE, 10-68

MOVE PARTITION clause

of ALTER TABLE, 10-41

MOVE SUBPARTITION clause

of ALTER TABLE, 10-42

move_table_clause

of ALTER TABLE, 10-23

MTS. See shared server

MULTISET parameter

of CAST function, 6-25

multitable inserts, 16-63

conditional, 16-63

unconditional, 16-63

multi-threaded server. See shared server

N

NAME clause

of SET TRANSACTION, 17-47

NAMED clause

of CREATE JAVA, 12-93

namespaces

and object naming rules, 2-108

for nonschema objects, 2-109

for schema objects, 2-108

NATIONAL CHAR datatype (ANSI), 2-34

NATIONAL CHAR VARYING datatype (ANSI), 2-34

NATIONAL CHARACTER datatype (ANSI), 2-34

national character set

fixed vs. variable width, 2-11

multibyte character data, 2-31

multibyte character sets, 2-10, 2-11

variable-length strings, 2-11

NATIONAL CHARACTER SET parameter

of ALTER DATABASE, 8-35

of CREATE DATABASE, 12-27

national character sets

changing, 8-35

NATIONAL CHARACTER VARYING datatype (ANSI), 2-34

natural joins, 17-17

NCHAR datatype, 2-10

ANSI, 2-34

NCHAR VARYING datatype (ANSI), 2-34

NCHR function, 6-92

NCLOB datatype, 2-31

transactional support of, 2-31

negative scale, 2-13

nested loop joins

optimizing for, 9-9

nested subqueries, 7-12

NESTED TABLE clause

of ALTER TABLE, 10-19, 10-64

of CREATE TABLE, 14-11, 14-38

of CREATE TRIGGER, 14-92

nested tables, 2-38

changing returned value, 10-61

compared with varrays, 2-45

comparison rules, 2-45

creating, 15-3, 15-8

defining as index-organized tables, 10-64

dropping the body of, 16-18

dropping the specification of, 16-15

indexing columns of, 12-69

modifying, 10-61

storage characteristics of, 10-64, 14-38

update in a view, 14-87

nested_table_col_properties clause

of ALTER TABLE, 10-19

of CREATE MATERIALIZED VIEW, 13-11

new features, xxvii

NEW_TIME function, 6-93

NEXT clause

of ALTER MATERIALIZED VIEW...REFRESH, 8-88

NEXT parameter

of STORAGE clause, 17-52

NEXT_DAY function, 6-94

NEXTVAL pseudocolumn, 2-79, 13-81

NL_SJ hint, 2-92

NLS_CALENDAR initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-74

NLS_CHARSET_DECL_LEN function, 6-94

NLS_CHARSET_ID function, 6-95

NLS_CHARSET_NAME function, 6-96

NLS_COMP initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-74

NLS_CURRENCY initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-74

NLS_DATE_FORMAT initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-75

NLS_DATE_LANGUAGE initialization parameter, 2-70

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-75

NLS_DUAL_CURRENCY initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-76

NLS_INITCAP function, 6-96

NLS_ISO_CURRENCY initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-76

NLS_LANGUAGE initialization parameter, 2-70, 7-9

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-76

NLS_LENGTH_SEMANTICS initialization parameter

overriding, 2-10

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-77

NLS_LOWER function, 6-98

NLS_NCHAR_CONV_EXCP initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-77

NLS_NUMERIC_CHARACTERS initialization parameter

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-77

NLS_SORT initialization parameter, 7-9

setting with ALTER SESSION, 9-8

setting with ALTER SYSTEM, 9-78

NLS_TERRITORY initialization parameter, 2-70

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-78

NLS_TIMESTAMP_FORMAT initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-78

NLS_TIMESTAMP_TZ_FORMAT initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-79

NLS_UPPER function, 6-100

NLSSORT function, 6-99

no data loss mode

setting, 12-30

NO_EXPAND hint, 2-95

NO_INDEX hint, 2-96

NO_MERGE hint, 2-96

NO_PUSH_PRED hint, 2-96

NOAPPEND hint, 2-95

NOARCHIVELOG clause

of ALTER DATABASE, 8-15, 8-29

of CREATE CONTROLFILE, 12-18

OF CREATE DATABASE, 8-19, 12-26

NOAUDIT statement, 16-79

NOCACHE clause

of ALTER CLUSTER, 8-6

of ALTER MATERIALIZED VIEW, 8-85

of ALTER MATERIALIZED VIEW LOG, 8-97

of ALTER SEQUENCE. See CREATE SEQUENCE

of ALTER TABLE, 10-30, 14-45

of CREATE CLUSTER, 12-9

of CREATE MATERIALIZED VIEW, 13-14

of CREATE MATERIALIZED VIEW LOG, 13-32

of CREATE SEQUENCE, 13-84

NOCACHE hint, 2-95

NOCOMPRESS clause

of ALTER INDEX ... REBUILD, 8-62

of ALTER TABLE, 10-69

of CREATE INDEX, 12-72

of CREATE TABLE, 14-30

NOCOPY clause

of CREATE FUNCTION, 12-52

of CREATE PROCEDURE, 13-60

NOCYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 8-122

of CREATE SEQUENCE, 13-84

NOFORCE clause

of CREATE JAVA, 12-92

of CREATE VIEW, 15-40

NOLOGGING clause

of ALTER INDEX, 8-59

of ALTER INDEX ...REBUILD, 8-64

of ALTER MATERIALIZED VIEW, 8-84

of ALTER MATERIALIZED VIEW LOG, 8-97

of ALTER TABLE, 10-27

of ALTER TABLESPACE, 10-90

of CREATE MATERIALIZED VIEW, 13-14

of CREATE MATERIALIZED VIEW LOG, 13-32

of CREATE TABLE, 14-27

of CREATE TABLESPACE, 14-73

NOMAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 13-83

NOMINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 10-32

NOMINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 8-122

of CREATE SEQUENCE, 13-84

NOMONITORING clause

of ALTER TABLE, 10-31

of CREATE TABLE, 14-46

NOMONITORING USAGE clause

of ALTER INDEX, 8-66

NONE clause

of SET ROLE, 17-43

nonequivilancy tests, 5-10

nonpadded comparison semantics, 2-43

nonschema objects

list of, 2-103

namespaces, 2-109

NOORDER parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 8-122

of CREATE SEQUENCE, 13-85

NOPARALLEL clause

of CREATE INDEX, 8-7, 8-23, 8-58, 8-84, 8-96, 10-39, 12-8, 12-75, 13-15, 13-32, 14-47

NOPARALLEL hint, 2-96

NOPARALLEL_INDEX hint, 2-96

NORELY clause

of constraint_clause, 11-87

NORESETLOGS clause

of CREATE CONTROLFILE, 12-17

NOREVERSE parameter

of ALTER INDEX ... REBUILD, 8-62

NOREWRITE hint, 2-97

NOROWDEPENDENCIES clause

of CREATE CLUSTER, 12-8

of CREATE TABLE, 14-46

NOSORT clause

of ALTER INDEX, 12-73

of constraint_clause, 11-90

NOT condition, 5-8

NOT DEFERRABLE clause

of constraint_clause, 11-86

NOT FINAL clause

of CREATE TYPE, 15-12

NOT IDENTIFIED clause

of ALTER ROLE. See CREATE ROLE

of CREATE ROLE, 13-72

NOT INSTANTIABLE clause

of CREATE TYPE, 15-12, 15-13

NOT NULL clause

of constraint_clause, 11-81

of CREATE TABLE, 14-23

NOT NULL constraints, 11-81

NOWAIT clause

of LOCK TABLE, 16-73

NTILE function, 6-101

null, 2-77

difference from zero, 2-77

in conditions, 2-78

table of, 2-79

in functions, 2-77

with comparison conditions, 2-78

NULL clause

of constraint_clause, 11-81

null conditions, 5-11

NULLIF function, 6-102

as a form of CASE expression, 6-102

NUMBER datatype, 2-12

converting to VARCHAR2, 2-61

precision, 2-12

scale, 2-12

number format models, 2-61

number functions, 6-3

numbers

comparison rules, 2-42

floating-point, 2-12, 2-14

in SQL syntax, 2-53

precision of, 2-54

rounding, 2-13

spelling out, 2-73

syntax of, 2-53

NUMERIC datatype (ANSI), 2-34

NUMTODSINTERVAL function, 6-103

NUMTOYMINTERVAL function, 6-104

NVARCHAR2 datatype, 2-11

NVL function, 6-105

NVL2 function, 6-106

O

O7_DICTIONARY_ACCESSIBILITY initialization parameter

setting with ALTER SYSTEM, 9-79

object access expressions, 4-12

object cache, 9-9, 9-80

OBJECT IDENTIFIER clause

of CREATE TABLE, 14-24

object identifiers

contained in REFs, 2-37

of object views, 15-41

primary key, 14-24

specifying, 14-24

specifying an index on, 14-24

system-generated, 14-24

object instances

types of, 5-16

object privileges

granting, 13-71

multiple, 13-78

on specific columns, 16-37

on a database object

revoking, 16-91

ON COMMIT REFRESH, 16-48

QUERY REWRITE, 16-48

revoking

from a role, 16-86, 16-90

from a user, 16-86, 16-90

from PUBLIC, 16-90

UNDER, 16-48

object reference functions, 6-14

object tables

adding rows to, 16-54

as part of hierarchy, 14-21

creating, 14-8, 14-21

updating to latest version, 10-31

upgrading, 10-31

object type columns

in a type hierarchy, 14-34

membership in hierarchy, 10-63

substitutability, 10-63

object type materialized views

creating, 13-12

object types, 2-36

adding methods to, 11-14

adding new member subprograms, 11-10

allowing object instances of, 15-12

allowing subtypes, 15-12

and subtypes, 11-10

and supertypes, 11-10

attributes, 2-116

in a type hierarchy, 14-34

membership in hierarchy, 10-63

substitutability, 10-63

bodies

creating, 15-23

re-creating, 15-25

SQL examples, 15-27

comparison rules, 2-45

MAP function, 2-45

ORDER function, 2-45

compiling the specification and body, 11-9

components of, 2-36

creating, 15-3, 15-5

defining member methods of, 15-23

disassociating statistics types from, 16-15

dropping methods from, 11-14

dropping the body of, 16-18

dropping the specification of, 16-15

evolved, rebuilding references to, 8-86

function subprogram

declaring, 15-27

function subprograms, 11-11, 15-12, 15-25

granting system privileges on, 16-44

handling dependent types, 11-16

incomplete, 15-3, 15-5

inheritance, 15-13

invalidating dependent types, 11-16

methods, 2-116

nested table, 15-8

privileges on subtypes, 16-38

procedure subprogram

declaring, 15-27

procedure subprograms, 11-11, 15-12, 15-25

references to. See REFs

root, specifying, 15-10

SQL examples, 15-18

static methods of, 15-12

statistics types, 11-46

subtypes, specifying, 15-10

top-level, 15-10

user-defined

creating, 15-9

values

comparing, 15-26, 15-27

varrays, 15-8

object views

base tables

adding rows, 16-54

creating, 15-41

creating subviews, 15-42

defining, 15-36

OBJECT_CACHE_MAX_SIZE_PERCENT initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-79

OBJECT_CACHE_OPTIMAL_SIZE initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-80

object_type_col_properties clause

of ALTER TABLE, 10-18, 10-63

of CREATE MATERIALIZED VIEW, 13-11

objects. See object types or database objects

OF clause

of CREATE VIEW, 15-41

OF object_type clause

of CREATE TABLE, 14-21

OFFLINE clause

of ALTER ROLLBACK SEGMENT, 8-119

of ALTER TABLESPACE, 10-87

of CREATE TABLESPACE, 14-74

OIDINDEX clause

of CREATE TABLE, 14-24

OIDs. See object identifiers

ON clause

of CREATE OUTLINE, 13-43

ON COMMIT clause

of CREATE TABLE, 14-24

ON COMMIT REFRESH object privilege, 16-48

on a materialized view, 16-50

ON COMMIT REFRESH system privilege, 16-41

ON DATABASE clause

of CREATE TRIGGER, 14-91

ON DEFAULT clause

of AUDIT, 11-55

of NOAUDIT, 16-82

ON DELETE CASCADE clause

of constraint_clause, 11-83

ON DELETE SET NULL clause

of constraint_clause, 11-83

ON DIRECTORY clause

of AUDIT, 11-55

of NOAUDIT, 16-82

ON NESTED TABLE clause

of CREATE TRIGGER, 14-91

ON object clause

of NOAUDIT, 16-82

of REVOKE, 16-91

ON PREBUILT TABLE clause

of CREATE MATERIALIZED VIEW, 13-16

ON SCHEMA clause

of CREATE TRIGGER, 14-91

on_composite_partitioned_table_clause

of CREATE INDEX, 12-66

on_hash_partitioned_table_clause

of CREATE INDEX, 12-65

on_list_partitioned_table_clause

of CREATE INDEX, 12-65

on_object_clause

of REVOKE, 16-88

on_range_partitioned_table_clause

of CREATE INDEX, 12-65

online backup

of tablespaces, ending, 10-88

ONLINE clause

of ALTER ROLLBACK SEGMENT, 8-119

of ALTER TABLESPACE, 10-87

of CREATE INDEX, 12-74

of CREATE TABLESPACE, 14-74

online indexes, 12-74

rebuilding, 10-68

ONLINE parameter

of ALTER INDEX ... REBUILD, 8-63

online redo logs

reinitializing, 8-32

OPEN clause

of ALTER DATABASE, 8-18

OPEN NORESETLOGS clause

of ALTER DATABASE, 8-18

OPEN READ ONLY clause

of ALTER DATABASE, 8-18

OPEN READ WRITE clause

of ALTER DATABASE, 8-18

OPEN RESETLOGS clause

of ALTER DATABASE, 8-18

OPEN_CURSORS initialization parameter

setting with ALTER SYSTEM, 9-80

OPEN_LINKS initialization parameter

setting with ALTER SYSTEM, 9-80

OPEN_LINKS_PER_INSTANCE initialization parameter

setting with ALTER SYSTEM, 9-81

operands, 3-1

operating system files

dropping, 16-12

removing, 8-28

OPERATOR clause

of COMMENT, 11-69

operators, 3-1

adding to indextypes, 8-73

arithmetic, 3-3

binary, 3-2

comments on, 11-69

concatenation, 3-4

dropping from indextypes, 8-73

granting

system privileges on, 16-41

precedence, 3-2

set, 3-6, 17-21

unary, 3-2

user-defined, 3-6

binding to a function, 13-39

creating, 13-37

dropping, 15-80

function providing implementation, 13-39

how bindings are implemented, 13-39

implementation type, 13-39

return type of binding, 13-39

OPTIMAL parameter

of STORAGE clause, 17-55

OPTIMIZER_FEATURES_ENABLE initialization parameter

setting with ALTER SYSTEM, 9-81

OPTIMIZER_INDEX_CACCHING initialization parameter

setting with ALTER SYSTEM, 9-81

OPTIMIZER_INDEX_CACHING initialization parameter

setting with ALTER SESSION, 9-9

OPTIMIZER_INDEX_COST_ADJ initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-82

OPTIMIZER_MAX_PERMUTATIONS initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-82

OPTIMIZER_MODE initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-82

OR condition, 5-8, 5-9

OR REPLACE clause

of CREATE CONTEXT, 12-12

of CREATE DIRECTORY, 12-45

of CREATE FUNCTION, 12-51, 12-92

of CREATE LIBRARY, 13-3

of CREATE OUTLINE, 13-42

of CREATE PACKAGE, 13-46

of CREATE PACKAGE BODY, 13-51

of CREATE PROCEDURE, 13-59

of CREATE TRIGGER, 14-85

of CREATE TYPE, 15-8

of CREATE TYPE BODY, 15-25

of CREATE VIEW, 15-39

Oracle reserved words, C-1

Oracle Tools

support of SQL, 1-5

ORACLE_TRACE_COLLECTION_NAME initialization parameter

setting with ALTER SYSTEM, 9-83

ORACLE_TRACE_COLLECTION_PATH initialization parameter

setting with ALTER SYSTEM, 9-83

ORACLE_TRACE_COLLECTION_SIZE initialization parameter

setting with ALTER SYSTEM, 9-84

ORACLE_TRACE_ENABLE initialization parameter

setting with ALTER SYSTEM, 9-84

ORACLE_TRACE_FACILITY_NAME initialization parameter

setting with ALTER SYSTEM, 9-84

ORACLE_TRACE_FACILITY_PATH initialization parameter

setting with ALTER SYSTEM, 9-85

Oracle9i Text

built-in conditions, 5-2

CATSEARCH, 5-2

CONTAINS, 5-2

creating domain indexes, 12-79

MATCHES, 5-2

SCORE operator, 3-2

ORDER BY clause

of queries, 7-8

of SELECT, 7-9, 17-10, 17-22

with ROWNUM, 2-84

ORDER clause

of ALTER SEQUENCE. See CREATE SEQUENCE

ORDER MEMBER clause

of ALTER TYPE, 11-11

of CREATE TYPE BODY, 15-27

ORDER methods

specifying, 11-11

ORDER parameter

of CREATE SEQUENCE, 13-84

ORDER SIBLINGS BY clause

of SELECT, 17-22

ORDERED hint, 2-97

ORDERED_PREDICATES hint, 2-97

ordinal numbers

specifying, 2-73

spelling out, 2-73

ORGANIZATION EXTERNAL clause

of CREATE TABLE, 14-29, 14-32

ORGANIZATION HEAP clause

of CREATE TABLE, 14-29

ORGANIZATION INDEX clause

of CREATE TABLE, 14-29

OS_AUTHENT_PREFIX initialization parameter

setting with ALTER SYSTEM, 9-85

OS_ROLES initialization parameter

setting with ALTER SYSTEM, 9-85

OUT parameter

of CREATE FUNCTION, 12-52

of CREATE PROCEDURE, 13-60

outer joins, 7-11

restrictions, 7-11

outlines

assign to a different category, 8-101

assigning to a different category, 8-100, 8-102

automatically creating and storing, 9-42

copying, 13-43

creating, 13-41

creating on statements, 13-43

dropping from the database, 15-82

enabling and disabling dynamically, 13-41

for use by current session, 13-42

for use by PUBLIC, 13-42

granting

system privileges on, 16-41

private, use by the optimizer, 9-15

rebuilding, 8-100, 8-102

recompiling, 8-101

renaming, 8-100, 8-101, 8-102

replacing, 13-42

storing during the session, 9-11

storing groups of, 13-43

use by the optimizer, 9-112

use to generate execution plans, 9-16

used to generate execution plans, 13-41

OVER clause

of analytic functions, 6-8, 6-10

OVERFLOW clause

of ALTER INDEX, 8-56

of ALTER TABLE, 10-34

of CREATE TABLE, 14-31

OVERRIDING clause

of CREATE TYPE, 15-13

P

package bodies

creating, 13-50

re-creating, 13-51

removing from the database, 15-83

packaged procedures

dropping, 15-85

packages

associating statistics with, 11-48

avoiding run-time compilation, 8-103

creating, 13-45

disassociating statistics types from, 15-84

invoker rights, 13-47

recompiling explicitly, 8-103

redefining, 13-46

removing from the database, 15-83

specifying schema and privileges of, 13-47

synonyms for, 14-2

PACKAGES clause

of ASSOCIATE STATISTICS, 11-47, 11-48

PARALLEL clause

of ALTER CLUSTER, 8-4, 8-6

of ALTER DATABASE, 8-22

of ALTER INDEX, 8-53, 8-58

of ALTER MATERIALIZED VIEW, 8-80, 8-84

of ALTER MATERIALIZED VIEW LOG, 8-95, 8-96

of ALTER TABLE, 10-39

of CREATE CLUSTER, 12-8

of CREATE INDEX, 12-75

of CREATE MATERIALIZED VIEW, 13-11, 13-15

of CREATE MATERIALIZED VIEW LOG, 13-31, 13-32

of CREATE TABLE, 14-18, 14-46

parallel execution

hints, 2-98

of DDL statements, 9-4

of DML statements, 9-4

PARALLEL hint, 2-98

parallel joins

and PQ_DISTRIBUTE hint, 2-99

PARALLEL_ADAPTIVE_MULTI_USER initialization parameter

setting with ALTER SYSTEM, 9-86

PARALLEL_AUTOMATIC_TUNING initialization parameter

setting with ALTER SYSTEM, 9-86

PARALLEL_BROADCAST_ENABLED initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-86

PARALLEL_ENABLE clause

of CREATE FUNCTION, 12-54

PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter

setting with ALTER SYSTEM, 9-87

PARALLEL_INSTANCE_GROUP initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-87

PARALLEL_MAX_SERVERS initialization parameter

setting with ALTER SYSTEM, 9-88

PARALLEL_MIN_PERCENT initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-88

PARALLEL_MIN_SERVERS initialization parameter

setting with ALTER SYSTEM, 9-89

PARALLEL_THREADS_PER_CPU initialization parameter

setting with ALTER SYSTEM, 9-89

parameter files

creating, 13-55

parameters

in syntax

optional, A-4

required, A-4

PARAMETERS clause

of ALTER INDEX ... REBUILD, 8-64

of CREATE INDEX, 12-79

PARTITION ... LOB storage clause

of ALTER TABLE, 10-66

PARTITION BY HASH clause

of CREATE TABLE, 14-42

PARTITION BY LIST clause

of CREATE TABLE, 14-41

PARTITION BY RANGE clause

of CREATE TABLE, 14-15, 14-40

PARTITION clause

of ANALYZE, 11-36

of CREATE INDEX, 11-90, 12-76, 14-52

of CREATE TABLE, 14-40

of DELETE, 15-52

of INSERT, 16-59

of LOCK TABLE, 16-72

of UPDATE, 17-66

partition_storage_clause

of ALTER TABLE, 10-21

PARTITION_VIEW_ENABLED initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-90

partitioned indexes, 2-104, 12-60, 12-77

user-defined, 11-89, 12-75, 14-51

partitioned index-organized tables

secondary indexes, updating, 8-68

partitioned tables, 2-104

partition-extended table names, 2-105

in DML statements, 2-105

restrictions on, 2-106

syntax, 2-105

partitioning

by range, 14-15

clauses

of ALTER INDEX, 8-54

of ALTER MATERIALIZED VIEW, 8-80, 8-83

of ALTER MATERIALIZED VIEW LOG, 8-95, 8-96

of ALTER TABLE, 10-35

of CREATE MATERIALIZED VIEW, 13-12, 13-14

of CREATE MATERIALIZED VIEW LOG, 13-31, 13-33

partitions

adding, 10-35

adding rows to, 16-54

allocating extents for, 10-28

based on literal values, 14-41

composite, 2-104

specifying, 14-43

converting into nonpartitioned tables, 10-49

deallocating unused space from, 10-29

dropping, 10-45

extents

allocating for an index, 8-58

hash, 2-104

adding, 10-44

coalescing, 10-45

specifying, 14-42

index, 12-75

inserting rows into, 16-59

list, adding, 10-45

LOB storage characteristics of, 10-66

locking, 16-71

logging attribute, 14-27

logging insert operations, 10-27

merging, 10-49

modifying, 10-35, 10-37

moving to a different segment, 10-41

physical attributes

changing, 10-26

range, 2-104

adding, 10-43

specifying, 14-40

removing rows from, 10-46, 15-52

renaming, 10-40

revising values in, 17-66

splitting, 10-47

storage characteristics, 14-26

tablespace for

defining, 14-27

PASSWORD EXPIRE clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-33

PASSWORD_GRACE_TIME parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

PASSWORD_LIFE_TIME parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

PASSWORD_LOCK_TIME parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

PASSWORD_REUSE_MAX parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

PASSWORD_REUSE_TIME parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

PASSWORD_VERIFY_FUNCTION parameter

of ALTER PROFILE, 8-110

of CREATE PROFILE, 13-68

passwords

expiration of, 15-33

grace period, 13-68

guaranteeing complexity, 13-68

limiting use and reuse, 13-68

locking, 13-68

making unavailable, 13-68

parameters

of ALTER PROFILE, 13-69

of CREATE PROFILE, 13-65

special characters in, 13-68

PCT_ACCESS_DIRECT statistics

for index-organized tables, 11-35

PCTFREE parameter

of ALTER CLUSTER, 8-5

of ALTER INDEX, 8-53, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER TABLE, 10-26

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE TABLE, 14-25

PCTINCREASE parameter

of STORAGE clause, 17-52

PCTTHRESHOLD parameter

of CREATE TABLE, 10-33, 14-30

PCTUSED parameter

of ALTER CLUSTER, 8-5

of ALTER INDEX, 8-53, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER TABLE, 10-26

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE TABLE, 14-25

PCTVERSION parameter

of CREATE TABLE, 14-36

of LOB storage clause, 10-66

PERCENT_RANK function, 6-107

PERCENTILE_CONT function, 6-109

PERCENTILE_DISC function, 6-112

performance

optimize for nested loop joins, 9-9

optimizing for index access path, 9-9

session optimizer approach, 9-9

shared pool, 9-102

PERMANENT clause

of ALTER TABLESPACE, 10-89

of CREATE TABLESPACE, 14-74

PGA_AGGREGATE_TARGET initialization parameter

setting with ALTER SYSTEM, 9-90

physical attributes clause

of a constraint, 11-78

of ALTER INDEX, 8-53, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER TABLE, 10-26

of CREATE CLUSTER, 12-3

of CREATE MATERIALIZED VIEW, 13-10

of CREATE MATERIALIZED VIEW LOG, 13-31

of CREATE TABLE, 14-13, 14-25

PIPELINED clause

of CREATE FUNCTION, 12-55

plan stability, 13-41

PLAN_TABLE sample table, 16-24

PL/SQL

compatibility with earlier releases, 9-93

program body

of CREATE FUNCTION, 12-56

PLSQL_COMPILER_FLAGS initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-90

PLSQL_DEBUG session parameter, 9-14

PLSQL_NATIVE_C_COMPILER initialization parameter

setting with ALTER SYSTEM, 9-91

PLSQL_NATIVE_LIBRARY_DIR initialization parameter

setting with ALTER SYSTEM, 9-91

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter

setting with ALTER SYSTEM, 9-91

PLSQL_NATIVE_LINKER initialization parameter

setting with ALTER SYSTEM, 9-92

PLSQL_NATIVE_MAKE_FILE_NAME initialization parameter

setting with ALTER SYSTEM, 9-92

PLSQL_NATIVE_MAKE_UTILITY initialization parameter

setting with ALTER SYSTEM, 9-92

PLSQL_V2_COMPATIBILITY initialization parameter

setting with ALTER SYSTEM, 9-93

P.M. datetime format element, 2-67, 2-70

PM datetime format element, 2-67, 2-70

POWER function, 6-113

PQ_DISTRIBUTE hint, 2-99

PR number format element, 2-62

PRAGMA clause

of ALTER TYPE, 11-11

of CREATE TYPE, 15-7, 15-14

PRAGMA RESTRICT_REFERENCES, 11-11

PRE_PAGE_SGA initialization parameter

setting with ALTER SYSTEM, 9-93

precedence

of conditions, 5-3

of operators, 3-2

precision

number of digits of, 2-54

of NUMBER datatype, 2-12

precompilers

Oracle, 1-4

PREPARE TO SWITCHOVER clause

of ALTER DATABASE, 8-35

PRIMARY KEY clause

of constraint_clause, 11-81

of CREATE TABLE, 14-23

primary key constraints, 11-81

enabling, 14-50

index on, 14-50

primary keys

generating values for, 13-81

PRIVATE clause

of CREATE OUTLINE, 13-42

private outlines

use by the optimizer, 9-15

PRIVATE_SGA parameter

of ALTER PROFILE, 8-110

of ALTER RESOURCE COST, 8-114

privileges

on subtypes of object types, 16-38

See also system privileges or object privileges

procedures

3GL, calling, 13-2

avoid run-time compilation, 8-107

calling, 11-64

compile explicitly, 8-107

creating, 13-55, 13-57

declaring

as a Java method, 13-62

as C functions, 13-62

executing, 11-64

external, 13-55, 13-57

running from remote database, 13-3

granting

system privileges on, 16-41

invalidating local objects dependent on, 15-85

issuing COMMIT or ROLLBACK statements, 9-3

naming rules, 2-109

privileges executed with, 11-13, 15-9

recompiling, 8-106

re-creating, 13-59

removing from the database, 15-85

schema executed in, 11-13, 15-9

shared pool, 9-102

specifying schema and privileges for, 13-61

synonyms for, 14-2

PROCESSES initialization parameter

setting with ALTER SYSTEM, 9-93

PROFILE clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-33

profiles

adding resource limits, 8-109

assigning to a user, 15-33

changing resource limits, 8-109

creating, 13-64

examples, 13-69

deassigning from users, 15-87

dropping resource limits, 8-109

granting

system privileges on, 16-42

modifying, examples, 8-111

removing from the database, 15-87

proxy clause

of ALTER USER, 11-22, 11-24

pseudocolumns, 2-79

CURRVAL, 2-79

LEVEL, 2-82

NEXTVAL, 2-79

ROWID, 2-83

ROWNUM, 2-84

uses for, 2-85

PUBLIC clause

of CREATE OUTLINE, 13-42

of CREATE ROLLBACK SEGMENT, 13-75

of CREATE SYNONYM, 14-3

of DROP DATABASE LINK, 15-63

public database links

dropping, 15-63

public rollback segments, 13-75

public synonyms, 14-3

dropping, 16-4

PUSH_PRED hint, 2-99

Q

Q datetime format element, 2-67

queries, 7-2, 17-4

comments in, 7-3

compound, 7-8

correlated

left correlation, 17-15

defined, 7-2

distributed, 7-15

grouping returned rows on a value, 17-19

hierarchical. See hierarchical queries

hierarchical, ordering, 17-22

hints in, 7-3

join, 7-9, 17-16

locking rows during, 17-23

ordering returned rows, 17-22

outer joins in, 17-16

referencing multiple tables, 7-9

select lists of, 7-2

selecting from a random sample of rows, 17-14

sorting results, 7-8

syntax, 7-2

top-level, 7-2

top-N, 2-84

query rewrite

and dimensions, 12-39

and function-based indexes, 9-9

and rule-based optimization, 9-9

defined, 17-4

disabling, 9-94

enabling, 9-94

enabling and disabling, 9-9

QUERY REWRITE object privilege, 16-48

on a materialized view, 16-50

QUERY REWRITE system privilege, 16-40, 16-41

QUERY_REWRITE_ENABLED initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-94

QUERY_REWRITE_INTEGRITY initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-94

QUIESCE RESTRICTED clause

of ALTER SYSTEM, 9-29

QUOTA clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-33

quotation marks

use with database object names, 2-109

R

range conditions, 5-11

range partitions

adding, 10-43

creating, 14-40

RANK function, 6-114

RATIO_TO_REPORT function, 6-116

RAW datatype, 2-25

converting from CHAR data, 2-25

RAWTOHEX function, 6-117

RAWTONHEX function, 6-118

RDBMS_SERVER_DN initialization parameter

setting with ALTER SYSTEM, 9-95

READ object privilege, 16-48

on a materialized directory, 16-50

READ ONLY clause

of ALTER TABLESPACE, 10-89

READ WRITE clause

of ALTER TABLESPACE, 10-89

READ_ONLY_OPEN_DELAYED initialization parameter

setting with ALTER SYSTEM, 9-95

REAL datatype (ANSI), 2-34

REBUILD clause

of ALTER INDEX, 8-54, 8-60

of ALTER MATERIALIZED VIEW, 8-86

of ALTER OUTLINE, 8-101

REBUILD PARTITION clause

of ALTER INDEX, 8-61

REBUILD SUBPARTITION clause

of ALTER INDEX, 8-61

REBUILD UNUSABLE LOCAL INDEXES clause

of ALTER TABLE, 10-38

rebuilding, 8-86

RECOVER AUTOMATIC clause

of ALTER DATABASE, 8-20

RECOVER CANCEL clause

of ALTER DATABASE, 8-11, 8-23

RECOVER clause

of ALTER DATABASE, 8-19

RECOVER CONTINUE clause

of ALTER DATABASE, 8-11, 8-23

RECOVER DATABASE clause

of ALTER DATABASE, 8-11, 8-20

RECOVER DATAFILE clause

of ALTER DATABASE, 8-11, 8-21

RECOVER LOGFILE clause

of ALTER DATABASE, 8-11, 8-22

RECOVER MANAGED STANDBY DATABASE clause

of ALTER DATABASE, 8-12

RECOVER STANDBY DATAFILE clause

of ALTER DATABASE, 8-21

RECOVER STANDBY TABLESPACE clause

of ALTER DATABASE, 8-21

RECOVER TABLESPACE clause

of ALTER DATABASE, 8-11, 8-21

RECOVERABLE, 8-60, 14-28

See also LOGGING clause

recovery

discarding data, 8-18

distributed, enabling, 9-27

instance, continue after interruption, 8-19

media, designing, 8-19

media, performing ongoing, 8-23

of database, 8-11

parallelizing, 8-22

RECOVERY_CATALOG_OWNER role, 16-47

recovery_clauses

of ALTER DATABASE, 8-10

RECOVERY_PARALLELISM initialization parameter

setting with ALTER SYSTEM, 9-95

redo log groups

supplemental, adding, 10-28

supplemental, dropping, 10-28

redo logs, 8-18

adding, 8-29

archive location, 9-25

automatic archiving, 9-23

starting, 9-25

stopping, 9-25

automatic name generation, 8-19, 8-20

clearing, 8-29

disabling specified threads in a cluster database, 8-38

dropping, 8-29, 8-31

enabling and disabling thread, 8-29

enabling specified threads in a cluster database, 8-38

manual archiving, 9-23

manually archiving

all, 9-25

by filename, 9-24

by group number, 9-24

by SCN, 9-23

current, 9-24

next, 9-24

with sequence numbers, 9-23

members

adding to existing groups, 8-30

dropping, 8-31

renaming, 8-29

remove changes from, 8-18

reusing, 16-30

size of, 16-29

specifying, 12-25, 16-28

for media recovery, 8-22

specifying archive mode, 12-26

switching groups, 9-28

threads, 9-23

REF columns

rescoping, 8-86

specifying, 14-23

specifying from table or column level, 14-23

REF function, 6-118

REFERENCES clause

of constraint_clause, 11-83

of CREATE TABLE, 14-23

REFERENCES object privilege, 16-48

on a table, 16-49

on a view, 16-49

REFERENCING clause

of CREATE TRIGGER, 14-85, 14-92

referential integrity constraints, 11-82, 11-83

REFRESH clause

of ALTER MATERIALIZED VIEW, 8-82, 8-86

of CREATE MATERIALIZED VIEW, 13-10

REFRESH COMPLETE clause

of ALTER MATERIALIZED VIEW, 8-87

of CREATE MATERIALIZED VIEW, 13-17

REFRESH FAST clause

of ALTER MATERIALIZED VIEW, 8-86

of CREATE MATERIALIZED VIEW, 13-17

REFRESH FORCE clause

of ALTER MATERIALIZED VIEW, 8-87

of CREATE MATERIALIZED VIEW, 13-17

REFRESH ON COMMIT clause

of ALTER MATERIALIZED VIEW, 8-87

of CREATE MATERIALIZED VIEW, 13-17

REFRESH ON DEMAND clause

of ALTER MATERIALIZED VIEW, 8-88

of CREATE MATERIALIZED VIEW, 13-17

REFs, 2-37, 11-84

as containers for OIDs, 2-37

dangling, 11-40

updating, 11-40

validating, 11-40

REFTOHEX function, 6-119

of ALTER SYSTEM, 9-30

of ALTER DATABASE, 8-35

REGR_AVGX function, 6-120

REGR_AVGY function, 6-120

REGR_COUNT function, 6-120

REGR_INTERCEPT function, 6-120

REGR_R2 function, 6-120

REGR_SLOPE function, 6-120

REGR_SXX function, 6-120

REGR_SXY function, 6-120

REGR_SYY function, 6-120

relational tables

creating, 14-8

RELY clause

of constraint_clause, 11-87

REMOTE_ARCHIVE_ENABLE initialization parameter

setting with ALTER SYSTEM, 9-95

REMOTE_DEPENDENCIES_MODE initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-96

REMOTE_LISTENER initialization parameter

setting with ALTER SYSTEM, 9-96

REMOTE_LOGIN_PASSWORDFILE initialization parameter

and control files, 12-14

and databases, 12-21

setting with ALTER SYSTEM, 9-96

REMOTE_OS_AUTHENT initialization parameter

setting with ALTER SYSTEM, 9-97

REMOTE_OS_ROLES initialization parameter

setting with ALTER SYSTEM, 9-97

RENAME clause

of ALTER INDEX, 8-65

of ALTER OUTLINE, 8-101

of ALTER TABLE, 10-32

of ALTER TRIGGER, 11-3

RENAME DATAFILE clause

of ALTER TABLESPACE, 10-85

RENAME FILE clause

of ALTER DATABASE, 8-9, 8-29

RENAME GLOBAL_NAME clause

of ALTER DATABASE, 8-38

RENAME PARTITION clause

of ALTER INDEX, 8-55, 8-68

of ALTER TABLE, 10-40

RENAME statement, 16-84

RENAME SUBPARTITION clause

of ALTER INDEX, 8-55, 8-68

of ALTER TABLE, 10-40

REPLACE AS OBJECT clause

of ALTER TYPE, 11-10

REPLACE function, 6-128

replication

row-level dependency tracking, 12-8, 14-46

REPLICATION_DEPENDENCY_TRACKING initialization parameter

setting with ALTER SYSTEM, 9-97

reserved words, 2-107, C-1

RESET COMPATIBILITY clause

of ALTER DATABASE, 8-37

reset sequence of, 8-18

RESETLOGS parameter

of CREATE CONTROLFILE, 12-16

RESOLVE clause

of ALTER JAVA CLASS, 8-75

of CREATE JAVA, 12-92

RESOLVER clause

of ALTER JAVA CLASS, 8-75

of ALTER JAVA SOURCE, 8-75

of CREATE JAVA, 12-94

Resource Manager, 9-29

resource parameters

of CREATE PROFILE, 13-65

RESOURCE role, 16-46

RESOURCE_LIMIT initialization parameter

setting with ALTER SYSTEM, 9-98

RESOURCE_MANAGER_PLAN initialization parameter

setting with ALTER SYSTEM, 9-98

response time

optimizing, 2-91

RESTRICT_REFERENCES pragma

of ALTER TYPE, 11-11

restricted rowids, 2-32

compatibility and migration of, 2-33

RESTRICTED SESSION system privilege, 16-42

resumable space allocation, 9-6

RESUMABLE system privilege, 16-45

RESUME clause

of ALTER SYSTEM, 9-28

RETURN clause

of CREATE FUNCTION, 12-53

of CREATE OPERATOR, 13-39

of CREATE TYPE, 15-13

of CREATE TYPE BODY, 15-27

RETURNING clause

of DELETE, 15-54

of INSERT, 16-56, 16-63

of UPDATE, 17-65, 17-71

REUSE clause

of CREATE CONTROLFILE, 12-16

of filespec clause, 16-30

REUSE SETTINGS clause

of ALTER FUNCTION, 8-49

of ALTER PACKAGE, 8-104

of ALTER PROCEDURE, 8-107

of ALTER TRIGGER, 11-4

of ALTER TYPE, 11-10

REVERSE clause

of CREATE INDEX, 12-73

reverse indexes, 12-73

REVERSE parameter

of ALTER INDEX ... REBUILD, 8-62

REVOKE clause

of ALTER USER, 11-24

REVOKE CONNECT THROUGH clause

of ALTER USER, 11-22, 11-24

REVOKE statement, 16-86

REWRITE hint, 2-100

right outer joins, 17-16

RM datetime format element, 2-67

RN number format element, 2-62

RNDS attribute

of PRAGMA RESTRICT_REFERENCES, 15-15

RNPS attribute

of PRAGMA RESTRICT_REFERENCES, 15-15

roles

application, 8-117

AQ_ADMINISTRATOR_ROLE, 16-46

AQ_USER_ROLE, 16-46

authorization

by a password, 13-72

by an external service, 13-72

by the database, 13-72

by the enterprise directory service, 13-72

changing, 8-116

CONNECT, 16-46

creating, 13-71

DBA, 16-46

DELETE_CATALOG_ROLE, 16-46

disabling

for the current session, 17-42, 17-43

effect on user sessions, 8-117

enabling

for the current session, 17-42, 17-43

EXECUTE_CATALOG_ROLE, 16-46

EXP_FULL_DATABASE, 16-46

granting, 16-32

system privileges on, 16-42

to a user, 16-35

to another role, 16-35

to PUBLIC, 16-35

HS_ADMIN_ROLE, 16-47

identifying by password, 13-72

identifying externally, 13-72

identifying through enterprise directory service, 13-72

identifying using a package, 13-72

IMP_FULL_DATABASE, 16-46

RECOVERY_CATALOG_OWNER, 16-47

removing from the database, 15-89

RESOURCE, 16-46

revoking, 16-86

from another role, 15-89, 16-89

from PUBLIC, 16-89

from users, 15-89, 16-89

SELECT_CATALOG_ROLE, 16-46

SNMPAGENT, 16-47

rollback segments

bringing online, 8-118, 8-119

changing storage characteristics, 8-118, 8-120

creating, 13-74

granting

system privileges on, 16-42

public, 13-75

reducing size, 8-118, 8-120

removing from the database, 15-90

specifying optimal size of, 17-55

specifying tablespaces for, 13-75

SQL examples, 13-76

storage attributes, 17-49

storage characteristics, 13-76

system-generated, 13-74

taking offline, 8-118, 8-119

ROLLBACK statement, 16-96

rollback undo, 8-118, 12-29

ROLLBACK_SEGMENTS initialization parameter

setting with ALTER SYSTEM, 9-98

ROLLUP clause

of SELECT statements, 17-20

ROUND function

date function, 6-130

format models, 6-199

number function, 6-129

routines

calling, 11-64

executing, 11-64

ROW EXCLUSIVE lock mode, 16-73

ROW SHARE lock mode, 16-73

ROW_LOCKING initialization parameter

setting with ALTER SYSTEM, 9-99

ROW_NUMBER function, 6-131

ROWDEPENDENCIES clause

of CREATE CLUSTER, 12-8

of CREATE TABLE, 14-46

ROWID datatype, 2-31

ROWID hint, 2-100

ROWID pseudocolumn, 2-31, 2-33, 2-83

rowids

block portion of, 2-32

description of, 2-31

extended, 2-32

base 64, 2-32

not directly available, 2-32

file portion of, 2-32

nonphysical, 2-33

of foreign tables, 2-33

of index-organized tables, 2-33

restricted, 2-32

compatibility and migration of, 2-33

row portion of, 2-32

uses for, 2-83

ROWIDTOCHAR function, 6-132

ROWIDTONCHAR function, 6-133

row-level dependency tracking, 12-8, 14-46

ROWNUM pseudocolumn, 2-84

uses for, 2-85

rows

adding to a table, 16-54

allowing movement of between partitions, 14-13

inserting

into partitions, 16-59

into remote databases, 16-59

into subpartitions, 16-59

movement between partitions, 14-44

removing

from a cluster, 17-58

from a table, 17-58

from partitions and subpartitions, 15-52

from tables and views, 15-49

selecting in hierarchical order, 7-3

specifying constraints on, 11-84

stored in ascending order, 11-90

storing if in violation of constraints, 10-51

RPAD function, 6-133

RR datetime format element, 2-67, 2-71

interpreting, 2-71

RRRR datetime format element, 2-67

RTRIM function, 6-134

RULE hint, 2-100

run-time compilation

avoiding, 8-106, 11-28

S

S number format element, 2-62

SAMPLE clause

of SELECT, 17-14

of SELECT and subqueries, 17-7

SAVEPOINT statement, 17-2

savepoints

erasing, 11-70

rolling back to, 16-97

specifying, 17-2

scalar subqueries, 4-13

scalar subquery expressions, 4-13

scale

greater than precision, 2-13

negative, 2-13

of NUMBER datatype, 2-12

SCC datetime format element, 2-67

SCHEMA clause

of CREATE JAVA, 12-93

schema objects, 2-102

auditing

options, 11-60

defining default buffer pool for, 17-55

dropping, 16-20

in other schemas, 2-113

list of, 2-102

name resolution, 2-112

namespaces, 2-108

naming, 2-106

examples, 2-110

guidelines, 2-111

rules, 2-106

object types, 2-36

on remote databases, 2-114

partitioned indexes, 2-104

partitioned tables, 2-104

parts of, 2-104

protecting location, 14-2

protecting owner, 14-2

providing alternate names for, 14-2

reauthorizing, 7-16

recompiling, 7-16

referring to, 2-111, 9-12

remote, accessing, 12-33

validating structure, 11-40

schemas

changing for a session, 9-12

creating, 13-78

definition of, 2-102

scientific notation, 2-63

SCOPE clause

of column ref constraints, 11-85

scope constraints, 11-85

SCOPE FOR clause

of ALTER MATERIALIZED VIEW, 8-81

of CREATE MATERIALIZED VIEW, 13-12

scoped_table_ref_constraint clause

of ALTER MATERIALIZED VIEW, 8-81

SCORE operator, 3-2

security

enforcing, 14-83

segment attributes clause

of CREATE TABLE, 14-13

SEGMENT MANAGEMENT FREELISTS clause

of CREATE TABLESPACE, 14-76

SEGMENT MANAGEMENT PAGETABLE clause

of CREATE TABLESPACE, 14-76

segment_management_clause

of CREATE TABLESPACE, 14-76

segments

space management

automatic, 14-76

manual, 14-76

using bitmaps, 14-76

using free lists, 14-76

SELECT ANY DICTIONARY system privilege, 16-45

SELECT ANY OUTLINE system privilege, 16-41

SELECT ANY SEQUENCE system privilege, 16-42

SELECT ANY TABLE system privilege, 16-43

select lists, 7-2

ordering, 7-8

SELECT object privilege, 16-48

on a materialized view, 16-50

on a sequence, 16-50

on a table, 16-49

on a view, 16-49

SELECT statement, 7-2, 17-4

SELECT_CATALOG_ROLE role, 16-46

self joins, 7-10

sequences, 2-79, 13-81

accessing values of, 13-81

changing

the increment value, 8-122

creating, 13-81

creating without limit, 13-83

granting

system privileges on, 16-42

guarantee consecutive values, 13-84

how to use, 2-81

increment value, setting, 13-83

incrementing, 13-81

initial value, setting, 13-83

maximum value

eliminating, 8-122

setting, 13-83

setting or changing, 8-122

minimum value

eliminating, 8-122

setting, 13-84

setting or changing, 8-122

number of cached values, changing, 8-122

ordering values, 8-122

preallocating values, 13-84

recycling values, 8-122

removing from the database, 16-2

renaming, 16-84

restarting, 16-2

at a different number, 8-123

at a predefined limit, 13-83

values, 13-84

reusing, 13-81

stopping at a predefined limit, 13-83

synonyms for, 14-2

where to use, 2-80

SERIAL_REUSE initialization parameter

setting with ALTER SYSTEM, 9-99

server parameter files

creating, 13-86

SERVERERROR event

triggers on, 14-90

service name

of remote database, 12-36

SERVICE_NAMES initialization parameter

setting with ALTER SYSTEM, 9-100

session control statements, 7-19

PL/SQL support of, 7-19

session locks

releasing, 9-27

session parameters

changing settings, 9-11

INSTANCE, 9-13

PLSQL_DEBUG, 9-14

SESSION_CACHED_CURSORS initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-100

SESSION_MAX_OPEN_FILES initialization parameter

setting with ALTER SYSTEM, 9-100

SESSION_ROLES view, 17-42

sessions

affecting with roles, 8-117

calculating resource cost limits, 8-113

changing resource cost limits, 8-113

disconnecting, 9-26

global name resolution for, 9-8

granting

system privileges on, 16-42

limiting CPU time, 8-113

limiting data block reads, 8-114

limiting inactive periods, 8-109

limiting private SGA space, 8-114

limiting resource costs, 8-113

limiting total elapsed time, 8-114

limiting total resources, 8-109

modifying characteristics of, 9-6

number of concurrent, 9-62

object cache, 9-9

restricting, 9-29

restricting to privileged users, 9-27

switching to a different instance, 9-13

terminating, 9-27

time zone setting, 9-14

SESSIONS initialization parameter

setting with ALTER SYSTEM, 9-101

SESSIONS_PER_USER parameter

of ALTER PROFILE, 8-110

SESSIONTIMEZONE function, 6-135

SET clause

of ALTER SESSION, 9-6

of ALTER SYSTEM, 9-30

of UPDATE, 17-68

SET CONSTRAINT(S) statement, 17-40

SET DANGLING TO NULL clause

of ANALYZE, 11-40

SET DATABASE clause

of CREATE CONTROLFILE, 12-16

set operators, 3-6, 17-21

INTERSECT, 3-6

MINUS, 3-6

UNION, 3-6

UNION ALL, 3-6

SET ROLE statement, 17-42

SET STANDBY DATABASE clause

of ALTER DATABASE, 8-34

SET STATEMENT_ID clause

of EXPLAIN PLAN, 16-25

SET TIME_ZONE clause

of ALTER DATABASE, 8-17, 8-36

of ALTER SESSION, 9-14

of CREATE DATABASE, 12-24

SET TRANSACTION statement, 17-45

SET UNUSED clause

of ALTER TABLE, 10-57

SGA. See system global area (SGA)

SGA_MAX_SIZE initialization parameter

setting with ALTER SYSTEM, 9-101

SHADOW_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 9-101

SHARE ROW EXCLUSIVE lock mode, 16-73

SHARE UPDATE lock mode, 16-73

SHARED clause

of CREATE DATABASE LINK, 12-34

shared pool

flushing, 9-28

shared server

parameters

DISPATCHERS, 9-51

processes

creating additional, 9-103

terminating, 9-103

system parameters, 9-103

SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 9-102

SHARED_POOL_RESERVED_SIZE initialization parameter

setting with ALTER SYSTEM, 9-102

SHARED_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 9-102

SHARED_SERVER_SESSIONS initialization parameter

setting with ALTER SYSTEM, 9-104

SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 9-103

SHRINK clause

of ALTER ROLLBACK SEGMENT, 8-120

SHUTDOWN clause

of ALTER SYSTEM, 9-30

SHUTDOWN event

triggers on, 14-90

SIGN function, 6-135

simple comparison conditions, 5-5

simple expressions, 4-3

SIN function, 6-136

SINGLE TABLE clause

of CREATE CLUSTER, 12-7

single-row functions, 6-3

miscellaneous, 6-6

single-table insert, 16-58

SINH function, 6-136

SIZE clause

of ALTER CLUSTER, 8-5

of CREATE CLUSTER, 12-6

of filespec clause, 16-29

SKIP_UNUSABLE_INDEXES session parameter, 9-14

SMALLINT datatype

ANSI, 2-34

DB2, 2-35

SQL/DS, 2-35

snapshot logs. See materialized view logs

snapshots. See materialized views

SNMPAGENT role, 16-47

SOME operator, 5-5

sort operations

changing linguistic sequence, 9-8

SORT_AREA_RETAINED_SIZE initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-104

SORT_AREA_SIZE initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-105

SOUNDEX function, 6-137

SP datetime format element suffix, 2-73

special characters

in passwords, 13-68

SPECIFICATION clause

of ALTER PACKAGE, 8-103

spelled numbers

specifying, 2-73

SPFILE initialization parameter

setting with ALTER SYSTEM, 9-105

SPLIT PARTITION clause

of ALTER INDEX, 8-56, 8-68

of ALTER TABLE, 10-47

SPTH datetime format element suffix, 2-73

SQL functions

ABS, 6-15

ACOS, 6-15

ADD_MONTHS, 6-16

aggregate, 6-6

analytic, 6-8

applied to LOB columns, 6-2

ASCII, 6-16

ASCIISTR, 6-17

ASIN, 6-18

ATAN, 6-19

ATAN2, 6-19

AVG, 6-20

BFILENAME, 6-21

BIN_TO_NUM, 6-22

BITAND, 6-23

CAST, 6-24

CEIL, 6-27

character

returning character values, 6-4

returning number values, 6-5

CHARTOROWID, 6-27

CHR, 6-28

COALESCE, 6-29

COMPOSE, 6-31

CONCAT, 6-31

conversion, 6-5

CONVERT, 6-32

CORR, 6-34

COS, 6-36

COSH, 6-36

COUNT, 6-37

COVAR_POP, 6-39

COVAR_SAMP, 6-41

CUME_DIST, 6-43

CURRRENT_DATE, 6-45

CURRRENT_TIMESTAMP, 6-46

date, 6-5

DBTIMEZONE, 6-47

DECOMPOSE, 6-49

DENSE_RANK, 6-50

DEREF, 6-52

DUMP, 6-53

EMPTY_BLOB, 6-55

EMPTY_CLOB, 6-55

EXISTSNODE, 6-55

EXP, 6-56

EXTRACT (datetime), 6-57

EXTRACT (XML), 6-58

FIRST, 6-59

FIRST_VALUE, 6-61

FLOOR, 6-63

FROM_TZ, 6-63

GREATEST, 6-64

GROUP_ID, 6-65

GROUPING, 6-66

GROUPING_ID, 6-67

HEXTORAW, 6-69

INITCAP, 6-69

INSTR, 6-70

INSTR2, 6-70

INSTR4, 6-70

INSTRB, 6-70

INSTRC, 6-70

LAG, 6-72

LAST, 6-73

LAST_DAY, 6-75

LAST_VALUE, 6-76

LEAD, 6-78

LEAST, 6-80

LENGTH, 6-80

LENGTH2, 6-80

LENGTH4, 6-80

LENGTHB, 6-80

LENGTHC, 6-80

linear regression, 6-120

LN, 6-81

LOCALTIMESTAMP, 6-82

LOG, 6-83

LOWER, 6-83

LPAD, 6-84

LTRIM, 6-85

MAKE_REF, 6-85

MAX, 6-86

MIN, 6-89

MOD, 6-90

MONTHS_BETWEEN, 6-91

NCHR, 6-92

NEW_TIME, 6-93

NEXT_DAY, 6-94

NLS_CHARSET_DECL_LEN, 6-94

NLS_CHARSET_ID, 6-95

NLS_CHARSET_NAME, 6-96

NLS_INITCAP, 6-96

NLS_LOWER, 6-98

NLS_UPPER, 6-100

NLSSORT, 6-99

NLV2, 6-106

NTILE, 6-101

NULLIF, 6-102

number, 6-3

NUMTODSINTERVAL, 6-103

NUMTOYMINTERVAL, 6-104

NVL, 6-105

object reference, 6-14

PERCENT_RANK, 6-107

PERCENTILE_CONT, 6-109

PERCENTILE_DISC, 6-112

POWER, 6-113

RANK, 6-114

RATIO_TO_REPORT, 6-116

RAWTOHEX, 6-117

RAWTONHEX, 6-118

REF, 6-118

REFTOHEX, 6-119

REGR_AVGX, 6-120

REGR_AVGY, 6-120

REGR_COUNT, 6-120

REGR_INTERCEPT, 6-120

REGR_R2, 6-120

REGR_SLOPE, 6-120

REGR_SXX, 6-120

REGR_SXY, 6-120

REGR_SYY, 6-120

REPLACE, 6-128

ROUND (date), 6-130

ROUND (number), 6-129

ROW_NUMBER, 6-131

ROWIDTOCHAR, 6-132

ROWIDTONCHAR, 6-133

RPAD, 6-133

RTRIM, 6-134

SESSIONTIMEZONE, 6-135

SIGN, 6-135

SIN, 6-136

single-row, 6-3

miscellaneous, 6-6

SINH, 6-136

SOUNDEX, 6-137

SQRT, 6-138

STDDEV, 6-139

STDDEV_POP, 6-140

STDDEV_SAMP, 6-142

SUBSTR, 6-144

SUBSTR2, 6-144

SUBSTR4, 6-144

SUBSTRB, 6-144

SUBSTRC, 6-144

SUM, 6-145

SYS_CONNECT_BY_PATH, 6-147

SYS_CONTEXT, 6-148

SYS_DBURIGEN, 6-153

SYS_EXTRACT_UTC, 6-154

SYS_GUID, 6-155

SYS_TYPEID, 6-156

SYS_XMLAGG, 6-157

SYS_XMLGEN, 6-158

SYSDATE, 6-159

SYSTIMESTAMP, 6-160

TAN, 6-161

TANH, 6-161

TO_CHAR (character), 6-162

TO_CHAR (datetime), 6-163

TO_CHAR (number), 6-165

TO_CLOB, 6-167

TO_DATE, 6-167

TO_DSINTERVAL, 6-168

TO_LOB, 6-169

TO_MULTI_BYTE, 6-170

TO_NCHAR (character), 6-171

TO_NCHAR (datetime), 6-172

TO_NCHAR (number), 6-173

TO_NCLOB, 6-174

TO_NUMBER, 6-174

TO_SINGLE_BYTE, 6-175

TO_TIMESTAMP, 6-176

TO_YMINTERVAL, 6-178

TRANSLATE, 6-179

TRANSLATE...USING, 6-180

TREAT, 6-182

TRIM, 6-183

TRUNC (date), 6-185

TRUNC (number), 6-184

TZ_OFFSET, 6-186

UID, 6-187

UNISTR, 6-187

UPPER, 6-188

USER, 6-189

USERENV, 6-189

VALUE, 6-191

VAR_POP, 6-192

VAR_SAMP, 6-194

VARIANCE, 6-195

VSIZE, 6-197

WIDTH_BUCKET, 6-198

SQL statements

auditing

by access, 11-55

by proxy, 11-54

by session, 11-55

by user, 11-54

stopping, 16-79

successful, 11-55

DDL, 7-16

determining the execution plan for, 16-24

DML, 7-18

rolling back, 16-96

session control, 7-19

space allocation, resumable, 9-6

suspending and completing, 9-6

system control, 7-19

tracking the occurrence in a session, 11-50

transaction control, 7-18

undoing, 16-96

SQL*Loader inserts, logging, 8-59

SQL:99 standards, 1-2

SQL_TRACE initialization parameter

setting with ALTER SYSTEM, 9-106

SQL_TRACE session parameter, 9-14

SQL92_SECURITY initialization parameter

setting with ALTER SYSTEM, 9-106

SQLData Java storage format, 15-11

SQL/DS datatypes, 2-34

conversion to Oracle datatypes, 2-35

implicit conversion, 2-35

restrictions on, 2-35

SQLJ object types

creating, 15-10

sqlj_object_type_attr clause

of CREATE TYPE, 15-11

SQRT function, 6-138

SS datetime format element, 2-67

SSSSS datetime format element, 2-67

standalone procedures

dropping, 15-85

standard SQL, B-1

Oracle extensions to, B-10

standby database

recovering, 8-21

standby databases

activating, 8-34

applying archive logs, 8-24

designing media recovery, 8-19

mounting, 8-17

preparing for primary status, 8-35

recovering, 8-20, 8-21

specifying, 12-30

STANDBY_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 9-106

STANDBY_FILE_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 9-107

star transformation, 2-101

STAR_TRANSFORMATION hint, 2-101

STAR_TRANSFORMATION_ENABLED initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-107

START WITH clause

of ALTER MATERIALIZED VIEW...REFRESH, 8-88

of queries and subqueries, 17-18

of SELECT and subqueries, 17-8

START WITH parameter

of CREATE SEQUENCE, 13-83

STARTUP event

triggers on, 14-90

startup_clauses

of ALTER DATABASE, 8-10

STATIC clause

of ALTER TYPE, 11-11

of CREATE TYPE, 15-12

of CREATE TYPE BODY, 15-25

statistics

collection during index rebuild, 8-60

computing exactly, 11-36

deleting from the data dictionary, 11-43

estimating, 11-39

forcing disassociation, 15-59

on index usage, 8-66

on indexes, 12-74

on scalar object attributes

collecting, 11-31

on schema objects

collecting, 11-31

deleting, 11-31

user-defined

dropping, 15-70, 15-71, 15-84, 16-7, 16-15

statistics types

associating

with columns, 11-48

with datatypes, 11-48

with domain indexes, 11-48

with functions, 11-48

with indextypes, 11-48

with packages, 11-48

disassociating

from columns, 15-57

from domain indexes, 15-57

from functions, 15-57

from indextypes, 15-57

from packages, 15-57

from types, 15-57

STDDEV function, 6-139

STDDEV_POP function, 6-140

STDDEV_SAMP function, 6-142

STORAGE clause, 17-49

of ALTER CLUSTER, 8-5

of ALTER INDEX, 8-53, 8-54, 8-59

of ALTER MATERIALIZED VIEW, 8-82

of ALTER MATERIALIZED VIEW LOG, 8-95

of ALTER ROLLBACK SEGMENT, 8-119, 8-120

of CREATE CLUSTER, 12-5

of CREATE MATERIALIZED VIEW LOG, 13-31

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE ROLLBACK SEGMENTS, 13-76

of CREATE TABLE, 14-13, 14-26

of CREATE TABLESPACE, 14-70

STORAGE IN ROW clause

of ALTER TABLE, 10-65

storage parameters

default, changing, 10-87

resetting, 17-58

STORE IN clause

of ALTER TABLE, 10-34, 14-42

STORE IN DEFAULT clause

of CREATE INDEX, 12-78

STORE IN tablespace clause

of CREATE INDEX, 12-78

stored functions, 12-47

strings

converting to ASCII values, 6-17

converting to unicode, 6-31

Structured Query Language (SQL)

description, 1-2

embedded, 1-4

functions, 6-2

keywords, A-4

Oracle Tools support of, 1-5

parameters, A-4

standards, 1-2, B-1

statements

auditing, 11-56

determining the cost of, 16-24

syntax, 8-1, A-2

SUBPARTITION BY HASH clause

of CREATE TABLE, 14-16, 14-43

SUBPARTITION clause

of ANALYZE, 11-36

of CREATE INDEX, 12-78

of CREATE TABLE, 14-44

of DELETE, 15-52

of INSERT, 16-59

of LOCK TABLE, 16-72

of UPDATE, 17-66

subpartition-extended table names, 2-105

in DML statements, 2-105

restrictions on, 2-106

syntax, 2-105

subpartitions

adding, 10-37

adding rows to, 16-54

allocating extents for, 10-28, 10-40

coalescing, 10-38

converting into nonpartitioned tables, 10-49

creating, 14-16, 14-44

deallocating unused space from, 10-29, 10-40

inserting rows into, 16-59

locking, 16-71

logging insert operations, 10-27

moving to a different segment, 10-42

physical attributes

changing, 10-26

removing rows from, 10-46, 15-52

renaming, 10-40

revising values in, 17-66

specifying, 14-43

subqueries, 7-2, 7-12, 17-4

assigning names to, 17-10

containing subqueries, 7-12

correlated, 7-12

defined, 7-2

extended subquery unnesting, 7-14

factoring of, 17-10

inline views, 7-12

nested, 7-12

restricting, 17-15

scalar, 4-13

used as expressions, 4-13

to insert table data, 14-53

unnesting, 7-14

using in place of expressions, 4-13

SUBSTR function, 6-144

SUBSTR2 function, 6-144

SUBSTR4 function, 6-144

SUBSTRB function, 6-144

SUBSTRC function, 6-144

subtotal values

deriving, 17-20

subtypes, 11-10

dropping safely, 16-16

SUM function, 6-145

supertypes, 11-10

SUSPEND clause

of ALTER SYSTEM, 9-28

sustained standby recovery mode, 8-23

SWITCH LOGFILE clause

of ALTER SYSTEM, 9-28

SYEAR datetime format element, 2-67

synonyms

changing the definition of, 16-4

creating, 14-2

granting

system privileges on, 16-42

local, 14-4

private, dropping, 16-4

public, 14-3

dropping, 16-4

remote, 14-4

removing from the database, 16-4

renaming, 16-84

synonyms for, 14-2

syntax diagrams, A-2

loops, A-5

multipart diagrams, A-5

SYS schema

database triggers stored in, 14-93

functions stored in, 14-93

SYS_CONNECT_BY_PATH function, 6-147

SYS_CONTEXT function, 6-148

SYS_DBURIGEN function, 6-153

SYS_EXTRACT_UTC function, 6-154

SYS_GUID function, 6-155

SYS_TYPEID function, 6-156

SYS_XMLAGG function, 6-157

SYS_XMLGEN function, 6-158

SYSDATE function, 6-159

SYSDBA system privilege, 16-46

SYSOPER system privilege, 16-46

system control statements, 7-19

PL/SQL support of, 7-19

system date

altering, 9-55

system events

attributes of, 14-93

triggers on, 14-90

system global area

flushing, 9-28

updating, 9-26

system privileges

ADMINISTER DATABASE TRIGGER, 16-44

ALTER ANY CLUSTER, 16-39

ALTER ANY DIMENSION, 16-39

ALTER ANY INDEX, 16-40

ALTER ANY INDEXTYPE, 16-40

ALTER ANY MATERIALIZED VIEW, 16-41

ALTER ANY OUTLINE, 16-41

ALTER ANY PROCEDURE, 16-41

ALTER ANY ROLE, 16-42

ALTER ANY SEQUENCE, 16-42

ALTER ANY TABLE, 16-43

ALTER ANY TRIGGER, 16-44

ALTER ANY TYPE, 16-44

ALTER DATABASE, 16-39

ALTER PROFILE, 16-42

ALTER RESOURCE COST, 16-42

ALTER ROLLBACK SEGMENT, 16-42

ALTER SESSION, 16-42

ALTER SYSTEM, 16-39

ALTER TABLESPACE, 16-43

ALTER USER, 16-44

ANALYZE ANY, 16-45

AUDIT ANY, 16-45

AUDIT SYSTEM, 16-39

BACKUP ANY TABLE, 16-43

BECOME USER, 16-44

COMMENT ANY TABLE, 16-45

CREATE ANY CLUSTER, 16-39

CREATE ANY CONTEXT, 16-39

CREATE ANY DIMENSION, 16-39

CREATE ANY DIRECTORY, 16-40

CREATE ANY INDEX, 16-40

CREATE ANY INDEXTYPE, 16-40

CREATE ANY LIBRARY, 16-40

CREATE ANY MATERIALIZED VIEW, 16-40

CREATE ANY OPERATOR, 16-41

CREATE ANY OUTLINE, 16-41

CREATE ANY PROCEDURE, 16-41

CREATE ANY SEQUENCE, 16-42

CREATE ANY SYNONYM, 16-43

CREATE ANY TABLE, 16-43

CREATE ANY TRIGGER, 16-44

CREATE ANY TYPE, 16-44

CREATE ANY VIEW, 16-45

CREATE CLUSTER, 16-39

CREATE DATABASE LINK, 16-39

CREATE DIMENSION, 16-39

CREATE INDEXTYPE, 16-40

CREATE LIBRARY, 16-40

CREATE MATERIALIZED VIEW, 16-40

CREATE OPERATOR, 16-41

CREATE PROCEDURE, 16-41

CREATE PROFILE, 16-42

CREATE PUBLIC DATABASE LINK, 16-39

CREATE PUBLIC SYNONYM, 16-43

CREATE ROLE, 16-42

CREATE ROLLBACK SEGMENT, 16-42

CREATE SEQUENCE, 16-42

CREATE SESSION, 16-42

CREATE SYNONYM, 16-43

CREATE TABLE, 16-43

CREATE TABLESPACE, 16-43

CREATE TRIGGER, 16-44

CREATE TYPE, 16-44

CREATE USER, 16-44

CREATE VIEW, 16-45

DELETE ANY TABLE, 16-43

DROP ANY CLUSTER, 16-39

DROP ANY CONTEXT, 16-39

DROP ANY DIMENSION, 16-39

DROP ANY DIRECTORY, 16-40

DROP ANY INDEX, 16-40

DROP ANY INDEXTYPE, 16-40

DROP ANY LIBRARY, 16-40

DROP ANY MATERIALIZED VEIW, 16-41

DROP ANY OPERATOR, 16-41

DROP ANY OUTLINE, 16-41

DROP ANY PROCEDURE, 16-41

DROP ANY ROLE, 16-42

DROP ANY SEQUENCE, 16-42

DROP ANY SYNONYM, 16-43

DROP ANY TABLE, 16-43

DROP ANY TRIGGER, 16-44

DROP ANY TYPE, 16-44

DROP ANY VIEW, 16-45

DROP LIBRARY, 16-40

DROP PROFILE, 16-42

DROP PUBLIC DATABASE LINK, 16-39

DROP PUBLIC SYNONYM, 16-43

DROP ROLLBACK SEGMENT, 16-42

DROP TABLESPACE, 16-43

DROP USER, 16-45

EXECUTE ANY INDEXTYPE, 16-40

EXECUTE ANY OPERATOR, 16-41

EXECUTE ANY PROCEDURE, 16-41

EXECUTE ANY TYPE, 16-44

EXEMPT ACCESS POLICY, 16-45

FORCE ANY TRANSACTION, 16-45

FORCE TRANSACTION, 16-45

GLOBAL QUERY REWRITE, 16-40, 16-41

GRANT ANY PRIVILEGE, 16-45

GRANT ANY ROLE, 16-42

granting, 13-71, 16-32

to a role, 16-35

to a user, 16-35

to PUBLIC, 16-35

INSERT ANY TABLE, 16-43

list of, 16-39

LOCK ANY TABLE, 16-43

MANAGE TABLESPACE, 16-43

ON COMMIT REFRESH, 16-41

QUERY REWRITE, 16-40, 16-41

RESTRICTED SESSION, 16-42

RESUMABLE, 16-45

revoking, 16-86

from a role, 16-88

from a user, 16-88

from PUBLIC, 16-88

SELECT ANY DICTIONARY, 16-45

SELECT ANY OUTLINE, 16-41

SELECT ANY SEQUENCE, 16-42

SELECT ANY TABLE, 16-43

SYSDBA, 16-46

SYSOPER, 16-46

UNDER ANY TYPE, 16-44

UNDER ANY VIEW, 16-45

UNLIMITED TABLESPACE, 16-43

UPDATE ANY TABLE, 16-43

system resources

enabling and disabling, 9-98

SYSTIMESTAMP function, 6-160

SYYYY datetime format element, 2-67

T

TABLE clause

of ANALYZE, 11-34

of DELETE, 15-54

of INSERT, 16-60

of SELECT, 17-15

of TRUNCATE, 17-59

of UPDATE, 17-66, 17-67, 17-68

table constraints, 11-78

defined, 11-74

of ALTER TABLE, 10-55

of CREATE TABLE, 14-23

table functions

creating, 12-55

table locks

disabling, 10-71

duration of, 16-71

enabling, 10-70

EXCLUSIVE, 16-72, 16-73

modes of, 16-73

on partitions, 16-72

on remote database, 16-73

on subpartitions, 16-72

and queries, 16-71

ROW EXCLUSIVE, 16-72, 16-73

ROW SHARE, 16-72, 16-73

SHARE, 16-72

SHARE ROW EXCLUSIVE, 16-73

SHARE UPDATE, 16-73

table REF constraints, 11-75, 11-84

of ALTER TABLE, 10-54

of CREATE TABLE, 14-23

table_index_clause

of CREATE INDEX, 12-62

tables

adding rows to, 16-54

aliases, 2-116

in CREATE INDEX, 12-69

in DELETE, 15-54

allocating extents for, 10-28

assigning to a cluster, 14-33

changing degree of parallelism on, 10-39

changing existing values in, 17-63

collecting statistics on, 10-31, 11-34

constraints on, 11-78

creating, 14-6

multiple, 13-78

creating comments about, 11-67

data stored outside database, 14-32

deallocating unused space from, 10-29

default physical attributes

changing, 10-26

degree of parallelism

specifying, 14-6

disassociating statistics types from, 16-7

dropping

along with cluster, 15-61

along with owner, 16-20

indexes of, 16-7

partitions of, 16-7

external, 14-29

creating, 14-32

restrictions on, 14-32

externally organized, 14-29

granting

system privileges on, 16-43

heap organized, 14-29

index-organized, 14-29

overflow segment for, 14-31

space in index block, 10-33, 14-30

inserting rows with a subquery, 14-53

inserting using the direct-path method, 16-54

joining in a query, 17-16

LOB storage of, 14-26

locking, 16-71

logging

insert operations, 10-27

table creation, 14-27

migrated and chained rows in, 11-42

moving to a new segment, 10-68

moving, index-organized, 10-68

nested

creating, 15-17

storage characteristics, 14-38

object

creating, 14-8

organization, defining, 14-29

parallel creation of, 14-46

parallelism

setting default degree, 14-46

partition attributes of, 10-36

partitioning, 2-104, 14-6, 14-39

allowing rows to move between partitions, 10-53

default attributes of, 10-36

physical attributes

changing, 10-26

relational

creating, 14-8

remote, accessing, 12-33

removing from the database, 16-6

removing rows from, 15-49

renaming, 10-32, 16-84

restricting

records in a block, 10-32

references to, 11-85

retrieving data from, 17-4

saving blocks in a cache, 10-30, 14-45

SQL examples, 14-55

storage attributes, 17-49

defining, 14-6

storage characteristics

defining, 14-26

storage properties, 14-34

storage properties of, 14-25

subpartition attributes of, 10-36

synonyms for, 14-2

tablespace for

defining, 14-6, 14-27

temporary

duration of data, 14-24

session-specific, 14-20

transaction specific, 14-20

unclustering, 15-60

updating through views, 15-43

validating structure, 11-40

with unusable indexes, 9-14

TABLESPACE clause

of ALTER INDEX ... REBUILD, 8-62

of CREATE CLUSTER, 12-6

of CREATE INDEX, 12-72

of CREATE MATERIALIZED VIEW, 13-13

of CREATE MATERIALIZED VIEW LOG, 13-31

of CREATE ROLLBACK SEGMENTS, 13-75

of CREATE TABLE, 14-27

tablespaces, 10-87

allocating space for users, 15-33

allowing write operations on, 10-89

automatic segment-space management, 2-15, 14-27, 14-76

backing up datafiles, 10-88

bringing online, 10-87, 14-74

coalescing free extents, 10-90

converting

from permanent to temporary, 10-89

from temporary to permanent, 10-89

creating, 14-67

datafiles

adding, 10-85

renaming, 10-85

default storage attributes, 17-49

default temporary, 8-37

learning name of, 8-37

designing media recovery, 8-19

dropping contents, 16-11

enable autoextension of, 10-86

ending online backup, 10-88

extent management, 14-81

extent size, 14-73

granting system privileges on, 16-43

locally managed, 17-49

altering, 10-85

temporary, 14-81

logging attribute, 10-90, 14-73

managing extents of, 14-75

of session duration, 14-79

permanent objects in, 14-74

read only, 10-89

reconstructing lost or damaged, 8-19, 8-26

recovering, 8-19, 8-21

removing from the database, 16-10

size of free extents in, 10-87

specifying

datafiles for, 14-72

for a table, 14-25

for a user, 15-32

for index rebuild, 10-69

taking offline, 10-87, 14-74

tempfiles

adding, 10-85

temporary

creating, 14-79

specifying for a user, 15-32

temporary objects in, 14-74

undo

altering, 10-85

creating, 12-29, 14-71

dropping, 16-11

TAN function, 6-161

TANH function, 6-161

TAPE_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 9-107

temp_tablespace_extent_clause

of CREAT TEMPORARY TABLESPACE, 14-80

of CREATE DATABASE, 12-23

TEMPFILE clause

of ALTER DATABASE, 8-13, 8-27

of CREATE TEMPORARY TABLESPACE, 14-80

tempfiles

automatic extension of, 14-81

bringing online, 8-27

disabling automatic extension, 8-27

dropping, 8-27

enabling automatic extension, 8-27

renaming, 8-29

resizing, 8-27

reusing, 16-30

size of, 16-29

specifying, 14-80, 16-28

taking offline, 8-27

TEMPORARY clause

of ALTER TABLESPACE, 10-89

of CREATE TABLESPACE, 14-74

temporary tables

creating, 14-6, 14-20

session-specific, 14-20

transaction-specific, 14-20

TEMPORARY TABLESPACE clause

of ALTER USER. See CREATE USER

of CREATE USER, 15-32

temporary tablespaces

creating, 14-79

default, 8-37

specifying for a user, 15-32

SQL examples, 14-82

TEST clause

of ALTER DATABASE ... RECOVER, 8-22

text

date and number formats, 2-59

in SQL syntax, 2-51

properties of CHAR and VARCHAR2 datatypes, 2-52

syntax of, 2-52

TH datetime format element suffix, 2-73

THREAD initialization parameter

setting with ALTER SYSTEM, 9-108

throughput

optimizing, 2-89

THSP datetime format element suffix, 2-73

TIME datatype

DB2, 2-35

SQL/DS, 2-35

time zone

determining for session, 6-135

setting for the database, 12-31

TIME_ZONE session parameter, 9-14

TIMED_OS_STATISTICS initialization parameter

setting with ALTER SESSION, 9-9

setting with ALTER SYSTEM, 9-108

TIMED_STATISTICS initialization parameter

setting with ALTER SESSION, 9-10

setting with ALTER SYSTEM, 9-108

TIMESTAMP datatype, 2-20

DB2, 2-35

SQL/DS, 2-35

TIMESTAMP WITH LOCAL TIME ZONE datatype, 2-22

TIMESTAMP WITH TIME ZONE datatype, 2-21

TM number format element, 2-62

TO SAVEPOINT clause

of ROLLBACK, 16-97

TO_CHAR

datetime conversion function, 6-163

number conversion function, 6-165

TO_CHAR (character) function, 6-162

TO_CHAR function, 2-61, 2-65, 2-73

TO_CLOB function, 6-167

TO_DATE function, 2-65, 2-71, 2-73, 6-167

TO_DSINTERVAL function, 6-168

TO_LOB function, 6-169

TO_MULTI_BYTE function, 6-170

TO_NCHAR (character) function, 6-171

TO_NCHAR (datetime) function, 6-172

TO_NCHAR (number) function, 6-173

TO_NCLOB function, 6-174

TO_NUMBER function, 2-61, 6-174

TO_SINGLE_BYTE function, 6-175

TO_TIMESTAMP function, 6-176

TO_TIMESTAMP_TZ function

SQL functions

TO_TIMESTAMP_TZ, 6-177

TO_YMINTERVAL function, 6-178

top-N queries, 2-84

TRACE_ENABLED initialization parameter

setting with ALTER SYSTEM, 9-109

TRACEFILE_IDENTIFIER initialization parameter

setting with ALTER SESSION, 9-10

setting with ALTER SYSTEM, 9-109

transaction control statements, 7-18

PL/SQL support of, 7-18

TRANSACTION_AUDITING initialization parameter

setting with ALTER SYSTEM, 9-109

transactions

allowing to complete, 9-26

assigning

rollback segment to, 17-45

automatically committing, 11-70

changes, making permanent, 11-70

commenting on, 11-71

distributed, forcing, 9-3

ending, 11-70

implicit commit of, 7-16, 7-18, 7-19

in-doubt

committing, 11-70

forcing, 11-71

resolving, 17-47

isolation level, 17-45

locks, releasing, 11-70

naming, 17-47

read-only, 17-45

read/write, 17-45

rolling back, 9-27, 13-74, 16-96

to a savepoint, 16-97

savepoints for, 17-2

TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameter

setting with ALTER SYSTEM, 9-110

TRANSLATE ... USING function, 6-180

TRANSLATE function, 6-179

TREAT function, 6-182

triggers

AFTER, 14-86

BEFORE, 14-86

compiling, 11-2, 11-3

creating, 14-83

multiple, 14-87

database

altering, 11-3

dropping, 16-13, 16-20

disabling, 10-71, 11-2, 11-3

enabling, 10-71, 11-2, 11-3, 14-83

executing

with a PL/SQL block, 14-93

with an external procedure, 14-94

granting

system privileges on, 16-44

INSTEAD OF, 14-87

dropping, 15-39

on database events, 14-90

on DDL events, 14-89

on DML operations, 14-88

on views, 14-87

order of firing, 14-87

re-creating, 14-85

removing from the database, 16-13

renaming, 11-3

restrictions on, 14-93

row values

old and new, 14-92

row, specifying, 14-92

SQL examples, 14-94

statement, 14-92

TRIM function, 6-183

TRUNC function

date function, 6-185

format models, 6-199

number function, 6-184

TRUNCATE PARTITION clause

of ALTER TABLE, 10-46

TRUNCATE statement, 17-58

TRUNCATE SUBPARTITION clause

of ALTER TABLE, 10-46

TRUST attribute

of PRAGMA RESTRICT_REFERENCES, 15-15

type constructor expressions, 4-13

type methods

return type of, 15-13

TYPES clause

of ASSOCIATE STATISTICS, 11-47, 11-48

types. See object types or datatypes

TZ_OFFSET function, 6-186

TZD datetime format element, 2-67

TZH datetime format element, 2-67

TZM datetime format element, 2-67

TZR datetime format element, 2-67

U

U number format element, 2-62

UID function, 6-187

unary operators, 3-2

UNDER ANY TABLE system privilege, 16-44

UNDER ANY VIEW system privilege, 16-45

UNDER clause

of CREATE VIEW, 15-42

UNDER object privilege, 16-48

on a type, 16-51

on a view, 16-49

undo

rollback, 8-118, 12-29

system managed, 8-118, 12-29

UNDO tablespace clause

of CREATE DATABASE, 12-29

of CREATE TABLESPACE, 14-71

undo tablespaces

creating, 12-29, 14-71

dropping, 16-11

modifying, 10-85

UNDO_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 9-110

UNDO_RETENTION initialization parameter

setting with ALTER SYSTEM, 9-110

UNDO_SUPPRESS_ERRORS initialization parameter

setting with ALTER SESSION, 9-10

setting with ALTER SYSTEM, 9-111

UNDO_TABLESPACE initialization parameter

setting with ALTER SYSTEM, 9-111

UNIFORM clause

of CREATE TABLESPACE, 14-75

UNION ALL set operator, 3-6, 17-21

UNION set operator, 3-6, 17-21

UNIQUE clause

of constraint_clause, 11-80

of CREATE INDEX, 12-67

of CREATE TABLE, 14-23

of SELECT, 17-11

unique constraints

enabling, 14-49

index on, 14-50

unique indexes, 12-67

unique queries, 17-11

UNISTR function, 6-187

universal rowids. See urowids

UNLIMITED TABLESPACE system privilege, 16-43

unnesting collections, 17-15

examples, 17-35

unnesting subqueries, 7-14

UNQUIESCE clause

of ALTER SYSTEM, 9-29

UNRECOVERABLE, 8-60, 14-28

See also NOLOGGING clause

unsorted indexes, 12-73

UNUSABLE clause

of ALTER INDEX, 8-65

UNUSABLE LOCAL INDEXES clause

of ALTER MATERIALIZED VIEW, 8-84

of ALTER TABLE, 10-38

UPDATE ANY TABLE system privilege, 16-43

UPDATE BLOCK REFERENCES clause

of ALTER INDEX, 8-66, 8-68

of ALTER TABLE, 10-35

UPDATE GLOBAL INDEXES clause

of ALTER TABLE, 10-39

UPDATE object privilege, 16-48

on a table, 16-49

on a view, 16-49

update operations

collecting supplemental log data for, 8-31

UPDATE SET clause

of MERGE, 16-75

UPDATE statement, 17-63

triggers on, 14-88

updates

and simultaneous insert, 16-75

UPGRADE clause

of ALTER TABLE, 10-31

UPPER function, 6-188

URLs

generating, 6-153

UROWID datatype, 2-33

urowids

and foreign tables, 2-33

and index-organized tables, 2-33

description of, 2-33

USE_CONCAT hint, 2-101

USE_MERGE hint, 2-102

USE_NL hint, 2-102

USE_PRIVATE_OUTLINES session parameter, 9-15

USE_STORED_OUTLINES initialization parameter

setting with ALTER SESSION, 9-112

USE_STORED_OUTLINES session parameter, 9-16, 9-112

USER function, 6-189

USER_COL_COMMENTS data dictionary view, 11-67

USER_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 9-112

USER_TAB_COMMENTS data dictionary view, 11-67

user-defined aggregate functions, 12-56

user-defined functions, 6-201

name precedence of, 6-202

naming conventions, 6-203

restrictions on, 12-51

user-defined operators, 3-6

user-defined statistics

dropping, 15-70, 15-71, 15-84, 16-7, 16-15

user-defined types, 2-36

defining, 15-9

mapping to Java classes, 15-10

USERENV function, 6-189

users

allocating space for, 15-33

and database links, 12-35

assigning

default roles, 11-23

profiles, 15-33

authenticating to a remote server, 12-36

changing authentication, 11-24

changing global authentication, 11-23

creating, 15-29

default tablespaces, 15-32

denying access to tables and views, 16-71

external, 13-72, 15-31

global, 13-72, 15-31

granting

system privileges on, 16-44

local, 13-72, 15-31

locking accounts, 15-33

maximum concurrent, 9-62

password expiration of, 15-33

removing from the database, 16-20

SQL examples, 15-33

temporary tablespaces for, 15-32

USING BFILE clause

of CREATE JAVA, 12-94

USING BLOB clause

of CREATE JAVA, 12-94

USING clause

of ALTER INDEXTYPE, 8-73

of ASSOCIATE STATISTICS, 11-47, 11-48

of CREATE DATABASE LINK, 12-36

of CREATE INDEXTYPE, 12-88

of CREATE OPERATOR, 13-39

USING CLOB clause

of CREATE JAVA, 12-94

USING INDEX clause

of ALTER MATERIALIZED VIEW, 8-85

of ALTER TABLE, 10-24

of constraint_clause, 11-88

of CREATE MATERIALIZED VIEW, 13-16

of CREATE TABLE, 14-19, 14-50

USING NO INDEX clause

of CREATE MATERIALIZED VIEW, 13-17

USING ROLLBACK SEGMENT clause

of ALTER MATERIALIZED VIEW...REFRESH, 8-89

of CREATE MATERIALIZED VIEW, 13-20

UTC

extracting from a datetime value, 6-154

UTC offset

replacing with time zone region, 2-21

UTL_FILE_DIR initialization parameter

setting with ALTER SYSTEM, 9-112

UTLCHN.SQL script, 11-42

UTLEXPT1.SQL script, 10-51

UTLXPLAN.SQL script, 16-24

V

V number format element, 2-62

VALIDATE clause

of DROP TYPE, 16-16

VALIDATE REF UPDATE clause

of ANALYZE, 11-40

VALIDATE STRUCTURE clause

of ANALYZE, 11-40

validation

of clusters, 11-40

of database objects

offline, 11-41

of database objects, online, 11-41

of indexes, 11-40

of tables, 11-40

VALUE function, 6-191

VALUES clause

of CREATE INDEX, 11-90, 12-76, 14-52

of INSERT, 16-61

VALUES LESS THAN clause

of CREATE TABLE, 14-40

VAR_POP function, 6-192

VAR_SAMP function, 6-194

VARCHAR datatype, 2-12

DB2, 2-35

SQL/DS, 2-35

VARCHAR2 datatype, 2-11

converting to NUMBER, 2-61

VARGRAPHIC datatype

DB2, 2-35

SQL/DS, 2-35

variable expressions, 4-15

VARIANCE function, 6-195

VARRAY clause

of ALTER TABLE, 10-20

VARRAY column properties

of ALTER TABLE, 10-20, 10-64

of CREATE MATERIALIZED VIEW, 13-11

of CREATE TABLE, 14-11, 14-37

varrays, 2-37

changing returned value, 10-61

compared with nested tables, 2-45

comparison rules, 2-45

creating, 15-3, 15-8, 15-17

dropping the body of, 16-18

dropping the specification of, 16-15

storage characteristics, 10-64, 10-67, 14-37

storing out of line, 2-37

varying arrays. See varrays

view

join

and key-preserved tables, 15-43

view constraints, 11-78

dropping, 16-23

views

base tables

adding rows, 16-54

changing

definition, 16-22

values in base tables, 17-63

constraints on, 11-78

creating

before base tables, 15-39

comments about, 11-67

multiple, 13-78

creating object subviews, 15-42

defining, 15-36

dropping constraints on, 11-30

granting

system privileges on, 16-45

modifying constraints on, 11-30

object, creating, 15-41

recompiling, 11-28

re-creating, 15-39

remote, accessing, 12-33

removing

from the database, 16-22

rows from the base table of, 15-49

renaming, 16-84

retrieving data from, 17-4

subquery of, 15-42

restricting, 15-44

synonyms for, 14-2

updatable, 15-43

with joins, making updatable, 15-43

VSIZE function, 6-197

W

W datetime format element, 2-67

WHEN clause

of CREATE TRIGGER, 14-93

WHEN MATCHED clause

of MERGE, 16-76

WHEN NOT MATCHED clause

of MERGE, 16-76

WHENEVER NOT SUCCESSFUL clause

of NOAUDIT, 16-82

WHENEVER SUCCESSFUL clause

of AUDIT sql_statements, 11-55

of NOAUDIT, 16-82

WHERE clause

of DELETE, 15-54

of SELECT, 7-4

of UPDATE, 17-70

WIDTH_BUCKET function, 6-198

WITH ADMIN OPTION clause

of GRANT, 16-36

WITH CHECK OPTION clause

of CREATE VIEW, 15-39, 15-44

of DELETE, 15-52

of INSERT, 16-61

of SELECT, 17-7, 17-15

of UPDATE, 17-67

WITH GRANT OPTION clause

of GRANT, 16-37

WITH HIERARCHY OPTION

of GRANT, 16-38

WITH INDEX CONTEXT clause

of CREATE OPERATOR, 13-39

WITH OBJECT ID clause

of CREATE MATERIALIZED VIEW LOG, 13-33

WITH OBJECT IDENTIFIER clause

of CREATE VIEW, 15-41

WITH OBJECT OID. See WITH OBJECT IDENTIFIER.

WITH PRIMARY KEY clause

of ALTER MATERIALIZED VIEW, 8-88

of CREATE MATERIALIZED VIEW LOG, 13-33

of CREATE MATERIALIZED VIEW...REFRESH, 13-17

WITH query_name clause

of SELECT, 17-10

WITH READ ONLY clause

of CREATE VIEW, 15-39, 15-44

of DELETE, 15-52

of INSERT, 16-61

of SELECT, 17-7, 17-15

of UPDATE, 17-67

WITH ROWID clause

of column ref constraints, 11-86

of CREATE MATERIALIZED VIEW LOG, 13-33

of CREATE MATERIALIZED VIEW...REFRESH, 13-17

WITH SEQUENCE clause

of CREATE MATERIALIZED VIEW LOG, 13-33

WNDS attribute

of PRAGMA RESTRICT_REFERENCES, 15-15

WNPS attribute

of PRAGMA RESTRICT_REFERENCES, 15-15

WORKAREA_SIZE_POLICY initialization parameter

setting with ALTER SESSION, 9-10

setting with ALTER SYSTEM, 9-113

WRITE object privilege

on a directory, 16-50

WW datetime format element, 2-67

X

X datetime format element, 2-67

X number format element, 2-62

XML data

storage of, 14-39

XML documents

producing from XML fragments, 6-157

retrieving from the database, 6-153

XML format models, 2-76

XML fragments, 6-58

XMLGenFormatType object, 2-76

XMLType storage clause

of CREATE TABLE, 14-39

Y

Y datetime format element, 2-67

Y,YYY datetime format element, 2-67

YEAR datetime format element, 2-67

YY datetime format element, 2-67

YYY datetime format element, 2-67

YYYY datetime format element, 2-67