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

$ (dollar sign)

number format element, 2-65

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

, (comma)

datetime format element, 2-70

number format element, 2-65

: (colon)

datetime format element, 2-70

- (dash)

datetime format element, 2-70

. (period)

datetime format element, 2-70

number format element, 2-65

; (semicolon)

datetime format element, 2-70

/ (slash)

datetime format element, 2-70

---

Numerics

0 (zero)

number format element, 2-65

20th century, 2-74

21st century, 2-74

7.3.4 release

upgrading to Oracle9i release 2, 9-26

9 (nine)

number format element, 2-65

---

A

ABORT LOGICAL STANDBY clause

of ALTER DATABASE, 9-48

ABS function, 6-15

ACCESSED GLOBALLY clause

of CREATE CONTEXT, 13-13

ACCOUNT LOCK clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-37

ACCOUNT UNLOCK clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-37

ACOS function, 6-16

ACTIVATE STANDBY DATABASE clause

of ALTER DATABASE, 9-45

ACTIVE_INSTANCE_COUNT initialization parameter

setting with ALTER SYSTEM, 10-36

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

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

ADD clause

of ALTER DIMENSION, 9-60

of ALTER INDEXTYPE, 9-88

of ALTER TABLE, 11-41

of ALTER VIEW, 12-31

ADD DATAFILE clause

of ALTER TABLESPACE, 11-103

ADD LOG GROUP clause

of ALTER TABLE, 11-34

ADD LOGFILE clause

of ALTER DATABASE, 9-21

ADD LOGFILE GROUP clause

of ALTER DATABASE, 9-40

ADD LOGFILE MEMBER clause

of ALTER DATABASE, 9-21, 9-41

ADD LOGFILE THREAD clause

of ALTER DATABASE, 9-40

ADD OVERFLOW clause

of ALTER TABLE, 11-40

ADD PARTITION clause

of ALTER TABLE, 11-68, 11-70, 11-71

ADD PRIMARY KEY clause

of ALTER MATERIALIZED VIEW LOG, 9-116

ADD ROWID clause

of ALTER MATERIALIZED VIEW, 9-116

of ALTER MATERIALIZED VIEW LOG, 9-116

ADD SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 9-42

ADD TEMPFILE clause

of ALTER TABLESPACE, 11-103

ADD VALUES clause

of ALTER TABLE ... MODIFY PARTITION, 11-64

ADD_MONTHS function, 6-16

adding a constraint, 11-57

ADMINISTER DATABASE TRIGGER system privilege, 17-42

ADVISE clause

of ALTER SESSION, 10-3

AFTER clause

of CREATE TRIGGER, 15-99

AFTER triggers, 15-99

AGENT clause

of CREATE LIBRARY, 14-3

aggregate functions, 6-7

user-defined, creating, 13-58

alias

for a column, 8-3

for an expressions in a view query, 16-43

specifying in queries and subqueries, 18-17

ALL clause

of SELECT, 18-11

of SET CONSTRAINTS, 18-45

of SET ROLE, 18-48

ALL EXCEPT clause

of SET ROLE, 18-48

ALL operator, 5-5

ALL PRIVILEGES clause

of GRANT, 17-34

of REVOKE, 17-94

ALL PRIVILEGES shortcut

of AUDIT, 12-55

ALL shortcut

of AUDIT, 12-55

ALL_COL_COMMENTS data dictionary view, 12-69

ALL_ROWS hint, 2-94

ALL_TAB_COMMENTS data dictionary view, 12-69

ALLOCATE EXTENT clause

of ALTER CLUSTER, 9-8, 9-9

of ALTER INDEX, 9-66, 9-72

of ALTER MATERIALIZED VIEW, 9-98

of ALTER TABLE, 11-35

ALLOW CORRUPTION clause

of ALTER DATABASE ... RECOVER, 9-30

ALTER ANY CLUSTER system privilege, 17-37

ALTER ANY DIMENSION system privilege, 17-37

ALTER ANY INDEX system privilege, 17-38

ALTER ANY INDEXTYPE system privilege, 17-38

ALTER ANY MATERIALIZED VIEW system privilege, 17-39

ALTER ANY OUTLINE system privilege, 17-39

ALTER ANY PROCEDURE system privilege, 17-40

ALTER ANY ROLE system privilege, 17-40

ALTER ANY SEQUENCE system privilege, 17-41

ALTER ANY TABLE system privilege, 17-41

ALTER ANY TRIGGER system privilege, 17-42

ALTER ANY TYPE system privilege, 17-43

ALTER CLUSTER statement, 9-7

ALTER DATABASE statement, 9-13

ALTER DATABASE system privilege, 17-37

ALTER DIMENSION statement, 9-58

ALTER FUNCTION statement, 9-61

ALTER INDEX statement, 9-64

ALTER INDEXTYPE statement, 9-87

ALTER JAVA CLASS statement, 9-89

ALTER JAVA SOURCE statement, 9-89

ALTER MATERIALIZED VIEW LOG statement, 9-112

ALTER MATERIALIZED VIEW statement, 9-92

ALTER object privilege, 17-47

on a sequence, 17-49

on a table, 17-48

ALTER OPERATOR statement, 9-119

ALTER OUTLINE statement, 9-120

ALTER PACKAGE statement, 9-122

ALTER PROCEDURE statement, 9-126

ALTER PROFILE statement, 9-129

ALTER PROFILE system privilege, 17-40

ALTER RESOURCE COST statement, 9-133

ALTER RESOURCE COST system privilege, 17-41

ALTER ROLE statement, 9-136

ALTER ROLLBACK SEGMENT statement, 9-138

ALTER ROLLBACK SEGMENT system privilege, 17-40

ALTER SEQUENCE statement, 9-142

ALTER SESSION statement, 10-2

ALTER SESSION system privilege, 17-41

ALTER SNAPSHOT LOG. See ALTER MATERIALIZED VIEW LOG

ALTER SNAPSHOT. See ALTER MATERIALIZED VIEW

ALTER statements

triggers on, 15-102

ALTER SYSTEM statement, 10-22

ALTER SYSTEM system privilege, 17-37

ALTER TABLE statement, 11-2

ALTER TABLESPACE statement, 11-101

ALTER TABLESPACE system privilege, 17-42

ALTER TRIGGER statement, 12-2

ALTER TYPE statement, 12-6

ALTER USER statement, 12-21

ALTER USER system privilege, 17-43

ALTER VIEW statement, 12-30

alter_external_table_clause

of ALTER TABLE, 11-16

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

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

American National Standards Institute (ANSI), B-1

datatypes, 2-36

conversion to Oracle datatypes, 2-36

datatypes, implicit conversion, 2-36

standards, xvii, 1-2, B-2

supported datatypes, 2-5

analytic functions, 6-9

AVG, 6-20

CORR, 6-35

COUNT, 6-38

COVAR_POP, 6-40

COVAR_SAMP, 6-42

CUME_DIST, 6-44

DENSE_RANK, 6-52

FIRST, 6-63

FIRST_VALUE, 6-65

inverse distribution, 6-114, 6-117

LAG, 6-76

LAST, 6-77

LAST_VALUE, 6-80

LEAD, 6-82

linear regression, 6-125

MAX, 6-91

MIN, 6-93

NTILE, 6-105

OVER clause, 6-9, 6-11

PERCENT_CONT, 6-114

PERCENT_DISC, 6-117

PERCENT_RANK, 6-112

RANK, 6-119

RATIO_TO_REPORT, 6-121

ROW_NUMBER, 6-135

STDDEV, 6-144

STDDEV_POP, 6-145

STDDEV_SAMP, 6-147

SUM, 6-150

syntax, 6-9

user-defined, 6-11, 13-58

VAR_POP, 6-199

VAR_SAMP, 6-201

VARIANCE, 6-203

ANALYZE ANY system privilege, 17-44

ANALYZE CLUSTER statement, 12-33

ANALYZE INDEX statement, 12-33

ANALYZE TABLE statement, 12-33

ANCILLARY TO clause

of CREATE OPERATOR, 14-44

AND condition, 5-8

AND DATAFILES clause

of DROP TABLESPACE, 17-12

AND_EQUAL hint, 2-95

ANSI. See American National Standards Institute (ANSI)

ANY operator, 5-5

APPEND hint, 2-95

application servers

allowing connection as user, 12-25

applications

allowing connection as user, 12-25

securing, 13-12

validating, 13-12

AQ_ADMINISTRATOR_ROLE role, 17-46

AQ_TM_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-36

AQ_USER_ROLE role, 17-46

ARCHIVE LOG clause

of ALTER SYSTEM, 10-25

archive logs

applying to standby database, 9-32

archive mode

specifying, 13-28

ARCHIVE_LAG_TARGET initialization parameter

setting with ALTER SYSTEM, 10-37

archived redo logs

location, 9-28

storage locations, 10-71

ARCHIVELOG clause

of ALTER DATABASE, 9-21, 9-39

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-28

arguments

of operators, 3-1

arithmetic

operators, 3-3

with DATE values, 2-20

AS clause

of CREATE JAVA, 13-99

AS EXTERNAL clause

of CREATE FUNCTION, 14-67

of CREATE TYPE BODY, 16-30

AS OBJECT clause

of CREATE TYPE, 16-9

AS subquery clause

of CREATE MATERIALIZED VIEW, 14-8, 14-25

of CREATE TABLE, 15-62

of CREATE VIEW, 16-46

AS TABLE clause

of CREATE TYPE, 16-19

AS VARRAY clause

of CREATE TYPE, 16-18

ASC clause

of CREATE INDEX, 13-74

ASCII

character set, 2-46

ASCII function, 6-17

ASCIISTR function, 6-18

ASIN function, 6-19

ASSOCIATE STATISTICS statement, 12-48

ATAN function, 6-19

ATAN2 function, 6-20

ATTRIBUTE clause

of ALTER DIMENSION, 9-59

of CREATE DIMENSION, 13-42, 13-45

attributes

adding to a dimension, 9-60

dropping from a dimension, 9-60

maximum number of in object type, 15-25

of dimensions, defining, 13-45

of user-defined types

mapping to Java fields, 16-12

AUDIT ANY system privilege, 17-44

AUDIT SYSTEM system privilege, 17-37

AUDIT_FILE_DEST initialization parameter

setting with ALTER SYSTEM, 10-37

AUDIT_SYS_OPERATIONS initialization parameter

setting with ALTER SYSTEM, 10-37

AUDIT_TRAIL initialization parameter

setting with ALTER SYSTEM, 10-38

auditing

options

for database objects, 12-58

for SQL statements, 12-60

policies

value-based, 12-52

SQL statements, 12-53, 12-58

by a proxy, 12-53

by a user, 12-53

SQL statements, on a directory, 12-54

SQL statements, on a schema, 12-54

SQL statements, stopping, 17-82

system privileges, 12-53

users connected to SYS schema, 10-37

AUTHENTICATED BY clause

of CREATE DATABASE LINK, 13-38

AUTHENTICATED clause

of ALTER USER, 12-26

AUTHID CURRENT_USER clause

of ALTER JAVA, 9-90

of CREATE FUNCTION, 13-55

of CREATE JAVA, 13-95, 13-97

of CREATE PACKAGE, 14-52

of CREATE PROCEDURE, 14-66

of CREATE TYPE, 12-13, 16-10

AUTHID DEFINER clause

of ALTER JAVA, 9-90

of CREATE FUNCTION, 13-55

of CREATE JAVA, 13-95, 13-97

of CREATE PACKAGE, 14-52

of CREATE PROCEDURE, 14-66

of CREATE TYPE, 12-13, 16-10

AUTOALLOCATE clause

of CREATE TABLESPACE, 15-88

AUTOEXTEND clause

of ALTER DATABASE, 9-20

of CREATE DATABASE, 13-25

of CREATE TEMPORARY TABLESPACE, 15-94

automatic segment-space management, 2-16, 15-89

Automatic Undo Management mode, 9-138, 13-32

AVG function, 6-20

---

B

B

number format element, 2-65

BACKGROUND_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 10-38

BACKGROUND_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-38

BACKUP ANY TABLE system privilege, 17-41

BACKUP CONTROLFILE clause

of ALTER DATABASE, 9-22, 9-44

BACKUP_TAPE_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 10-38

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

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

BECOME USER system privilege, 17-43

BEFORE clause

of CREATE TRIGGER, 15-99

BEFORE triggers, 15-99

BEGIN BACKUP clause

of ALTER TABLESPACE, 11-106

BFILE

datatype, 2-32

locators, 2-32

BFILENAME function, 6-22

BIN_TO_NUM function, 6-23

binary large objects. See BLOB

binary operators, 3-2

BINDING clause

of CREATE OPERATOR, 14-42, 14-44

bit vectors

converting to numbers, 6-23

BITAND function, 6-24

BITMAP clause

of CREATE INDEX, 13-69

bitmap indexes, 13-69

creating join indexes, 13-64

BITMAP_MERGE_AREA_SIZE initialization parameter

setting with ALTER SYSTEM, 10-39

blank padding

specifying in format models, 2-76

suppressing, 2-76

BLANK_TRIMMING initialization parameter

setting with ALTER SYSTEM, 10-39

blank-padded comparison semantics, 2-46

BLOB datatype, 2-33

transactional support, 2-33

BLOCKSIZE clause

of CREATE TABLESPACE, 15-85

BODY clause

of ALTER PACKAGE, 9-123

BUFFER_POOL parameter

of STORAGE clause, 7-63

BUFFER_POOL_KEEP initialization parameter

setting with ALTER SYSTEM, 10-40

BUFFER_POOL_RECYCLE initialization parameter

setting with ALTER SYSTEM, 10-40

BUILD DEFERRED clause

of CREATE MATERIALIZED VIEW, 14-18

BUILD IMMEDIATE clause

of CREATE MATERIALIZED VIEW, 14-18

BY ACCESS clause

of AUDIT, 12-57

BY proxy clause

of AUDIT, 12-56

BY SESSION clause

of AUDIT, 12-57

BY user clause

of AUDIT, 12-55

BYTE character semantics, 2-9, 2-11

BYTE length semantics, 11-50

---

C

C

number format element, 2-65

C clause

of CREATE TYPE, 16-15

of CREATE TYPE BODY, 16-29

C method

mapping to an object type, 16-15

CACHE clause

of ALTER MATERIALIZED VIEW, 9-103

of ALTER MATERIALIZED VIEW LOG, 9-116

of ALTER TABLE, 11-35, 15-54

of CREATE CLUSTER, 13-9

of CREATE MATERIALIZED VIEW, 14-17

of CREATE MATERIALIZED VIEW LOG, 14-36

CACHE hint, 2-95

CACHE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-142

of CREATE SEQUENCE, 14-90

CACHE READS clause

of ALTER TABLE, 11-45

of CREATE TABLE, 15-55

cached cursors

execution plan for, 17-24

CALL clause

of CREATE TRIGGER, 15-107

CALL procedure statement

of CREATE TRIGGER, 15-107

call spec. See call specifications

call specifications

in procedures, 14-62

of CREATE PROCEDURE, 14-66

of CREATE TYPE, 16-15

of CREATE TYPE BODY, 16-29

CALL statement, 12-66

calls

limiting CPU time for, 14-72

limiting data blocks read, 14-72

Cartesian products, 8-11

CASCADE clause

of CREATE TABLE, 15-62

of DROP PROFILE, 16-94

of DROP USER, 17-20

CASCADE CONSTRAINTS clause

of DROP CLUSTER, 16-68

of DROP TABLE, 17-8

of DROP TABLESPACE, 17-12

of DROP VIEW, 17-23

of REVOKE, 17-94

CASE expressions, 4-6

searched, 4-6

simple, 4-6

CAST function, 6-25

MULTISET parameter, 6-25

CATSEARCH condition, 5-2

CC datetime format element, 2-70

CEIL function, 6-28

chained rows

listing, 12-44

of clusters, 12-38

CHANGE CATEGORY clause

of ALTER OUTLINE, 9-121

CHAR character semantics, 2-9, 2-11

CHAR datatype, 2-9

ANSI, 2-36

converting to VARCHAR2, 2-64

CHAR length semantics, 11-50

CHAR VARYING datatype, ANSI, 2-36

CHARACTER datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

character functions, 6-4, 6-5

character large objects. See CLOB

character length semantics, 11-50

character literal. See text

CHARACTER SET parameter

of ALTER DATABASE, 9-49

of CREATE CONTROLFILE, 13-20

of CREATE DATABASE, 13-29

character sets

changing, 9-49

common, 2-46

database, specifying, 13-29

multibyte characters, 2-112

specifying for database, 13-29

character strings

comparison rules, 2-45

exact matching, 2-77

fixed-length, 2-9

national character set, 2-10

variable length, 2-11

variable-length, 2-14

zero-length, 2-10

CHARACTER VARYING datatype

ANSI, 2-36

characters

single, comparison rules, 2-46

CHARTOROWID function, 6-28

CHECK clause

of constraints, 7-15

of CREATE TABLE, 15-27

check constraints, 7-15

CHECK DATAFILES clause

of ALTER SYSTEM, 10-28

CHECKPOINT clause

of ALTER SYSTEM, 10-27

checkpoints

forcing, 10-27

CHOOSE hint, 2-95

CHR function, 6-29

CHUNK clause

of ALTER TABLE, 11-46

of CREATE TABLE, 15-38

CIRCUITS initialization parameter

setting with ALTER SYSTEM, 10-41

CLEAR LOGFILE clause

of ALTER DATABASE, 9-21, 9-43

CLOB datatype, 2-33

transactional support, 2-33

clone databases

mounting, 9-25

CLOSE DATABASE LINK clause

of ALTER SESSION, 10-3

CLUSTER clause

of ANALYZE, 12-38

of CREATE INDEX, 13-70

of CREATE TABLE, 15-35

of TRUNCATE, 18-57

CLUSTER hint, 2-96

CLUSTER_DATABASE initialization parameter

setting with ALTER SYSTEM, 10-41

CLUSTER_DATABASE_INSTANCES initialization parameter

setting with ALTER SYSTEM, 10-42

CLUSTER_INTERCONNECTS initialization parameter

setting with ALTER SYSTEM, 10-42

clusters

assigning tables to, 15-35

caching retrieved blocks, 13-9

cluster indexes, 13-70

collecting statistics on, 12-38

creating, 13-2

deallocating unused extents, 9-8

degree of parallelism

changing, 9-8, 9-10

when creating, 13-8

dropping tables, 16-68

extents, allocating, 9-8, 9-9

granting system privileges on, 17-36

hash, 13-6

single-table, 13-7

indexed, 13-6

key values

allocating space for, 13-5

modifying space for, 9-9

migrated and chained rows in, 12-38, 12-44

modifying, 9-7

physical attributes

changing, 9-8

specifying, 13-5

releasing unused space, 9-10

removing from the database, 16-67

SQL examples, 16-68

storage attributes

changing, 9-8

storage characteristics, changing, 9-9

tablespace in which created, 13-6

validating structure, 12-42

COALESCE clause

for partitions, 11-71

of ALTER INDEX, 9-80

of ALTER TABLE, 11-41, 11-63

of ALTER TABLESPACE, 11-108

COALESCE function, 6-31

as a variety of CASE expression, 6-31

COALESCE SUBPARTITION clause

of ALTER TABLE, 11-63

collection types

multilevel, 15-41

collections

inserting rows into, 17-60

modifying, 11-56

modifying retrieval method, 11-11

nested tables, 2-39

treating as a table, 16-60, 17-60, 18-16, 18-62, 18-63

unnesting, 18-16

examples, 18-39

varrays, 2-39

collection-typed values

converting to datatypes, 6-25

column constraints

restrictions on, 11-50

column REF constraints, 7-16

of CREATE TABLE, 15-26

columns

adding, 11-41

aliases for, 8-3

altering storage, 11-43

associating statistics with, 12-50

basing an index on, 13-72

collecting statistics on, 12-39

comments on, 12-70

creating comments about, 12-69

defining, 15-7

dropping from a table, 11-51

LOB

storage attributes, 11-45

maximum number of, 15-25

modifying existing, 11-48

parent-child relationships between, 13-41

properties, altering, 11-11, 11-43

qualifying names of, 8-2

REF

describing, 7-16

renaming, 11-55

restricting values for, 7-5

specifying as primary key, 7-12

specifying constraints on, 15-27

specifying default values, 15-26

storage properties, 15-36

substitutable, identifying type, 6-160

COLUMNS clause

of ASSOCIATE STATISTICS, 12-48, 12-50

COMMENT ANY TABLE system privilege, 17-44

COMMENT clause

of COMMIT, 12-73

COMMENT statement, 12-69

comments, 2-90

adding to objects, 12-69

associating with a transaction, 12-73

dropping from objects, 12-69

in SQL statements, 2-91

on indextypes, 12-71

on operators, 12-70

on schema objects, 2-92

on table columns, 12-70

on tables, 12-70

removing from the data dictionary, 12-69

specifying, 2-91

viewing, 12-69

commit

automatic, 12-72

COMMIT IN PROCEDURE clause

of ALTER SESSION, 10-3

COMMIT statement, 12-72

COMMIT TO SWITCHOVER clause

of ALTER DATABASE, 9-47

COMMIT_POINT_STRENGTH initialization parameter

setting with ALTER SYSTEM, 10-42

comparison conditions, 5-4

comparison functions

MAP, 16-29

ORDER, 16-29

comparison semantics

blank-padded, 2-46

nonpadded, 2-45

of character strings, 2-45

COMPATIBLE initialization parameter

setting with ALTER SYSTEM, 10-43

COMPILE clause

of ALTER DIMENSION, 9-60

of ALTER FUNCTION, 9-62

of ALTER JAVA SOURCE, 9-90

of ALTER MATERIALIZED VIEW, 9-108

of ALTER PACKAGE, 9-123

of ALTER PROCEDURE, 9-127

of ALTER TRIGGER, 12-3

of ALTER TYPE, 12-9

of ALTER VIEW, 12-32

of CREATE JAVA, 13-96

compiler switches

dropping and preserving, 9-62, 9-124, 9-127, 12-4, 12-10

COMPOSE function, 6-32

composite foreign keys, 7-13

composite partitioning

range-list, 11-62, 15-50

when creating a table, 15-20, 15-49

composite primary keys, 7-12

COMPOSITE_LIMIT parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

compound conditions, 5-21

compound expressions, 4-5

COMPRESS clause

of ALTER INDEX ... REBUILD, 9-76

of CREATE TABLE, 15-32

compression

of index keys, 9-67

COMPUTE STATISTICS clause

of ALTER INDEX... REBUILD, 9-77

of ANALYZE, 12-38

of CREATE INDEX, 13-77

CONCAT function, 6-33

concatenation operator, 3-4

conditions

comparison, 5-4

compound, 5-21

EXISTS, 5-13, 5-14

group comparison, 5-7

in SQL syntax, 5-1

IS OF type, 5-19

LIKE, 5-15

logical, 5-8

membership, 5-9

null, 5-13

range, 5-12

simple comparison, 5-5

UNDER_PATH, 5-20

CONNECT BY clause

of queries and subqueries, 18-21

of SELECT, 8-5, 18-20

CONNECT clause

of SELECT and subqueries, 18-8

CONNECT role, 17-46

CONNECT THROUGH clause

of ALTER USER, 12-26

CONNECT TO clause

of CREATE DATABASE LINK, 13-37

CONNECT_TIME parameter

of ALTER PROFILE, 9-130

of ALTER RESOURCE COST, 9-134

CONSIDER FRESH clause

of ALTER MATERIALIZED VIEW, 9-108

constant values. See literals

CONSTRAINT(S) session parameter, 10-12

constraints

adding to a table, 11-57

altering, 11-11

check, 7-15

checking

at end of transaction, 7-18

at start of transaction, 7-19

at the end of each DML statement, 7-18

column REF, 7-16

deferrable, 7-18, 18-45

enforcing, 10-12

defining, 7-5, 15-7

for a table, 15-27

on a column, 15-27

disabling, 15-57

cascading, 15-62

disabling after table creation, 11-87

disabling during table creation, 15-23

dropping, 11-11, 11-58, 17-12

enabling, 15-57, 15-59

enabling after table creation, 11-87

enabling during table creation, 15-23

foreign key, 7-13

modifying existing, 11-57

on views

dropping, 12-32, 17-23

modifying, 12-32

primary key, 7-12

attributes of index, 7-22

enabling, 15-59

referential integrity, 7-13

renaming, 11-58

restrictions, 7-10

setting state for a transaction, 18-45

storing rows in violation, 11-81

table REF, 7-16

unique

attributes of index, 7-22

enabling, 15-59

constructor methods

and object types, 16-3

constructors

defining for an object type, 16-16

user-defined, 16-16

CONTAINS condition, 5-2

context namespaces

accessible to instance, 13-14

associating with package, 13-12

initializing using OCI, 13-13

initializing using the LDAP directory, 13-13

removing from the database, 16-69

contexts

creating namespaces for, 13-12

granting system privileges on, 17-37

control files

allowing reuse, 13-17, 13-26

backing up, 9-44

force logging mode, 13-20

re-creating, 13-15

CONTROL_FILE_RECORD_KEEP_TIME initialization parameter

setting with ALTER SYSTEM, 10-43

CONTROL_FILES initialization parameter

setting with ALTER SYSTEM, 10-44

controlfile clauses

of ALTER DATABASE, 9-22

CONTROLFILE REUSE clause

of CREATE DATABASE, 13-26

controlfiles

standby, creating, 9-44

conversion

rules, string to date, 2-79

conversion functions, 6-5

CONVERT clause

of ALTER DATABASE, 9-51

CONVERT function, 6-34

CORE_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-44

CORR function, 6-35

correlated subqueries, 8-13

correlation names

for base tables of indexes, 13-71

in DELETE, 16-61

in SELECT, 18-17

COS function, 6-37

COSH function, 6-38

COUNT function, 6-38

COVAR_POP function, 6-40

COVAR_SAMP function, 6-42

CPU_COUNT initialization parameter

setting with ALTER SYSTEM, 10-44

CPU_PER_CALL parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-72

CPU_PER_SESSION parameter

of ALTER PROFILE, 9-130

of ALTER RESOURCE COST, 9-133

of CREATE PROFILE, 14-72

CREATE ANY CLUSTER system privilege, 17-37

CREATE ANY CONTEXT system privilege, 17-37

CREATE ANY DIMENSION system privilege, 17-37

CREATE ANY DIRECTORY system privilege, 17-38

CREATE ANY INDEX system privilege, 17-38

CREATE ANY INDEXTYPE system privilege, 17-38

CREATE ANY LIBRARY system privilege, 17-38

CREATE ANY MATERIALIZED VIEW system privilege, 17-39

CREATE ANY OPERATOR system privilege, 17-39

CREATE ANY OUTLINE system privilege, 17-39

CREATE ANY PROCEDURE system privilege, 17-40

CREATE ANY SEQUENCE system privilege, 17-40

CREATE ANY SYNONYM system privilege, 17-41

CREATE ANY TABLE system privilege, 17-41

CREATE ANY TRIGGER system privilege, 17-42

CREATE ANY TYPE system privilege, 17-43

CREATE ANY VIEW system privilege, 17-43

CREATE CLUSTER statement, 13-2

CREATE CLUSTER system privilege, 17-36

CREATE CONTEXT statement, 13-12

CREATE CONTROLFILE statement, 13-15

CREATE DATABASE LINK statement, 13-35

CREATE DATABASE LINK system privilege, 17-37

CREATE DATABASE statement, 13-22

CREATE DATAFILE clause

of ALTER DATABASE, 9-19, 9-36

CREATE DIMENSION

system privilege, 17-37

CREATE DIMENSION statement, 13-41

CREATE DIRECTORY statement, 13-46

CREATE FUNCTION statement, 13-49

CREATE INDEX

statement, 13-62

CREATE INDEXTYPE

statement, 13-91

CREATE INDEXTYPE system privilege, 17-38

CREATE JAVA statement, 13-94

CREATE LIBRARY statement, 14-2

CREATE LIBRARY system privilege, 17-38

CREATE MATERIALIZED VIEW LOG statement, 14-32

CREATE MATERIALIZED VIEW statement, 14-5

CREATE MATERIALIZED VIEW system privilege, 17-39

CREATE OPERATOR statement, 14-42

CREATE OPERATOR system privilege, 17-39

CREATE OUTLINE statement, 14-46

CREATE PACKAGE BODY statement, 14-55

CREATE PACKAGE statement, 14-50

CREATE PFILE statement, 14-60

CREATE PROCEDURE statement, 14-62

CREATE PROCEDURE system privilege, 17-40

CREATE PROFILE statement, 14-69

CREATE PROFILE system privilege, 17-40

CREATE PUBLIC DATABASE LINK system privilege, 17-37

CREATE PUBLIC SYNONYM system privilege, 17-41

CREATE ROLE statement, 14-77

CREATE ROLE system privilege, 17-40

CREATE ROLLBACK SEGMENT statement, 14-80

CREATE ROLLBACK SEGMENT system privilege, 17-40

CREATE SCHEMA statement, 14-84

CREATE SEQUENCE statement, 14-87

CREATE SEQUENCE system privilege, 17-40

CREATE SESSION system privilege, 17-41

CREATE SPFILE statement, 14-92

CREATE STANDBY CONTROLFILE clause

of ALTER DATABASE, 9-22, 9-44

CREATE statements

triggers on, 15-102

CREATE SYNONYM statement, 15-2

CREATE SYNONYM system privilege, 17-41

CREATE TABLE statement, 15-7

CREATE TABLE system privilege, 17-41

CREATE TABLESPACE statement, 15-80

CREATE TABLESPACE system privilege, 17-42

CREATE TEMPORARY TABLESPACE statement, 15-93

CREATE TRIGGER statement, 15-96

CREATE TRIGGER system privilege, 17-42

CREATE TYPE BODY statement, 16-25

CREATE TYPE statement, 16-3

CREATE TYPE system privilege, 17-43

CREATE USER statement, 16-32

CREATE USER system privilege, 17-43

CREATE VIEW statement, 16-39

CREATE VIEW system privilege, 17-43

CREATE_BITMAP_AREA_SIZE initialization parameter

setting with ALTER SYSTEM, 10-45

CREATE_STORED_OUTLINES initialization parameter

setting with ALTER SYSTEM, 10-45

CREATE_STORED_OUTLINES session parameter, 10-12

cross joins, 18-19

CUBE clause

of SELECT statements, 18-22

CUME_DIST function, 6-44

cumulative distributions, 6-44

currency symbol

ISO, 2-65

local, 2-66

setting for a session, 10-9

union, 2-67

CURRENT_DATE function, 6-46

CURRENT_SCHEMA session parameter, 10-13

CURRENT_TIMESTAMP function, 6-47

CURRENT_USER clause

of CREATE DATABASE LINKI, 13-37

CURRVAL pseudocolumn, 2-83, 14-87

CURSOR expressions, 4-7

CURSOR_SHARING initialization parameter

setting with ALTER SESSION, 10-8, 10-46

CURSOR_SPACE_FOR_TIME initialization parameter

setting with ALTER SYSTEM, 10-46

cursors

cached, 17-24

CustomDatum Java storage format, 16-11

CYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-142

of CREATE SEQUENCE, 14-90

---

D

D

number format element, 2-65

data

aggregation

composite columns of GROUP BY, 18-23

concatenated grouping sets of GROUP BY, 18-23

grouping sets, 18-23

caching frequently used, 11-35, 15-54

independence, 15-2

integrity checking on input, 2-12

retrieving, 8-2

specifying as temporary, 15-24

undo

storing, 14-80

data conversion, 2-48

between character datatypes, 2-50

implicit

disadvantages, 2-48

implicit versus explicit, 2-48

when performed implicitly, 2-49, 2-51

when specified explicitly, 2-52

data definition language (DDL)

events and triggers, 15-102

statements, 9-2

and implicit commit, 9-2

causing recompilation, 9-2

PL/SQL support, 9-2

statements requiring exclusive access, 9-2

data dictionary

adding comments to, 12-69

data manipulation language (DML)

allowing during indexing, 9-74

operations

and triggers, 15-100

during index creation, 13-77

during index rebuild, 11-86

parallelizing, 15-56

restricting operations, 10-31

retrieving affected rows, 16-61, 17-63, 18-67

retrieving rows affected by, 16-61, 17-63, 18-67

statements, 9-4

PL/SQL support, 9-4

triggers

and LOB columns and attributes, 2-31

data object number

in extended rowids, 2-34

data segment compression, 9-101, 11-33, 14-16, 15-29

database

preventing changes to, 9-53

system user passwords, 13-26

database events

and triggers, 15-103

auditing, 15-103

transparent logging of, 15-103

database links, 8-15

closing, 10-3

creating, 2-118, 13-35

creating synonyms with, 15-5

current user, 13-37

granting system privileges on, 17-37

naming, 2-119

public, 13-36

dropping, 16-70

referring to, 2-120

removing from the database, 16-70

shared, 13-36

syntax, 2-119

username and password, 2-119

database objects

dropping, 17-20

nonschema, 2-108

schema, 2-107

database triggers. See triggers

databases

accounts

creating, 16-32

allowing generation of redo logs, 9-25

allowing reuse of control files, 13-26

allowing unlimited resources to users, 14-71

archive mode

specifying, 13-28

blocks

specifying size, 15-85

cache

buffers in, 10-47

cancel-based recovery, 9-28

terminating, 9-31

change-based recovery, 9-29

changing character set, 9-49

changing characteristics, 13-15

changing global name, 9-52

changing name, 13-15, 13-17

character set, specifying, 13-29

character sets

changing, 9-49

specifying, 13-29

committing to standby status, 9-47

connect strings, 2-120

controlling, 9-53

controlling use, 9-53

converting from Oracle7 data dictionary, 9-51

create script for, 9-44

creating, 13-22

datafiles

modifying, 9-36

specifying, 13-29

designing media recovery, 9-27

ending backup of, 9-35

erasing all data from, 13-22

granting system privileges on, 17-37

in FORCE LOGGING mode, 9-39, 13-20, 13-28

instances of, 13-28

limiting resources for users, 14-69

log files

modifying, 9-39

specifying, 13-26

managed recovery, 9-17

modifying, 9-13

mounting, 9-25, 13-22

moving a subset to a different database, 11-80

naming, 9-25

national character set

specifying, 13-29

no-data-loss mode, 9-46

online

adding log files, 9-40

opening, 9-25, 13-22

after media recovery, 9-26

prepare to re-create, 9-44

protection mode of, 9-46

quiesced state, 10-31

read-only, 9-25

read/write, 9-25

reconstructing damaged, 9-27

recovering, 9-27, 9-28

recovery

allowing corrupt blocks, 9-30

testing, 9-30

with backup control file, 9-29

re-creating control file for, 13-15

remote

accessing, 8-15

authenticating users to, 13-38

connecting to, 13-37

inserting into, 17-59

service name of, 13-38

table locks on, 17-76

resetting

current log sequence, 9-26

to an earlier version, 9-51

restricting users to read-only transactions, 9-26

resuming activity, 10-31

standby

adding log files, 9-40

suspending activity, 10-31

tempfiles

modifying, 9-36

time zone

determining, 6-48

setting, valid values for, 9-50, 13-33

time-based recovery, 9-29

upgrading, 9-51

datafile

defining for the database, 13-25

DATAFILE clause

of CREATE DATABASE, 13-29

DATAFILE clauses

of ALTER DATABASE, 9-19, 9-37

DATAFILE END BACKUP clause

of ALTER DATABASE, 9-35

DATAFILE OFFLINE clause

of ALTER DATABASE, 9-37

DATAFILE ONLINE clause

of ALTER DATABASE, 9-37

DATAFILE RESIZE clause

of ALTER DATABASE, 9-37

datafiles

bringing online, 9-37

changing size of, 9-37

creating new, 9-36

defining for a tablespace, 15-82

defining for a temporary tablespace, 15-93

designing media recovery, 9-27

dropping, 17-12

enabling autoextend, 7-42

end online backup of, 9-37, 11-106

extending automatically, 7-42

mapping to logical volumes and physical devices, 10-61

online backup of, 11-106

online, updating information on, 10-28

putting online, 9-37

recover damaged, 9-27

recovering, 9-29

re-creating lost or damaged, 9-36

renaming, 9-39

resizing, 9-37, 9-38

reusing, 7-41

size of, 7-41

specifying, 7-39

for a tablespace, 15-84

specifying for database, 13-29

system generated, 9-36

taking offline, 9-37

datatypes, 2-2

"Any" types, 2-40

ANSI-supported, 2-5

associating statistics with, 12-49, 12-50

BFILE, 2-9, 2-32

BLOB, 2-9, 2-33

built-in, 2-6

CHAR, 2-8, 2-9

character, 2-9

CLOB, 2-8, 2-33

comparison rules, 2-45

converting to collection-typed values, 6-25

converting to other datatypes, 6-25

DATE, 2-7, 2-18

datetime, 2-16

interval, 2-16

INTERVAL DAY TO SECOND, 2-24

INTERVAL YEAR TO MONTH, 2-23

length semantics, 2-9, 2-11

LONG, 2-7, 2-14

LONG RAW, 2-8, 2-27

media types, 2-44

NCHAR, 2-8, 2-10

NCLOB, 2-9, 2-33

NUMBER, 2-12

NUMER, 2-7

NVARCHAR2, 2-6, 2-11

Oracle-supplied types, 2-40

RAW, 2-8, 2-27

ROWID, 2-8, 2-33

spatial type, 2-44

TIMESTAMP, 2-21

TIMESTAMP WITH LOCAL TIME ZONE, 2-22

TIMESTAMP WITH TIME ZONE, 2-21

UROWID, 2-8, 2-35

user-defined, 2-38

VARCHAR, 2-12

VARCHAR2, 2-6, 2-11

XML types, 2-41

DATE columns

converting to datetime columns, 11-49

DATE datatype, 2-18

julian, 2-20

date format models, 2-68

punctuation in, 2-69

text in, 2-69

date functions, 6-5

dates

arithmetic, 2-20

comparison rules, 2-45

datetime arithmetic

boundary cases, 10-13

calculating daylight savings time, 2-25

datetime columns

creating from DATE columns, 11-49

datetime datatypes, 2-16

daylight savings time, 2-25

datetime expressions, 4-9

datetime field

extracting from a datetime or interval value, 6-60

datetime format elements, 2-69

and Globalization Support, 2-73

capitalization, 2-69

ISO standard, 2-74

RR, 2-74

suffixes, 2-76

datetime functions, 6-5

DAY datetime format element, 2-73

daylight savings time, 2-25

boundary cases, 2-25

going into or coming out of effect, 2-25

DB_BLOCK_BUFFERS initialization parameter

setting with ALTER SYSTEM, 10-47

DB_BLOCK_CHECKING initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-47

DB_BLOCK_CHECKSUM initialization parameter

setting with ALTER SYSTEM, 10-48

DB_BLOCK_SIZE initialization parameter

setting with ALTER SYSTEM, 10-48

DB_CACHE_ADVICE initialization parameter

setting with ALTER SYSTEM, 10-49

DB_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-49, 10-74, 10-89

DB_CACHE_SIZE parameter

of ALTER SYSTEM, 10-74, 10-89

DB_CREATE_FILE_DEST initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-49

DB_CREATE_ONLINE_LOG_DEST_n initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-50

DB_DOMAIN initialization parameter

setting with ALTER SYSTEM, 10-50

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-50

DB_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 10-51

DB_FILES initialization parameter

setting with ALTER SYSTEM, 10-52

DB_KEEP_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-52

DB_NAME initialization parameter

setting with ALTER SYSTEM, 10-52

DB_nK_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-46

DB_RECYCLE_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-53

DB_WRITER_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-53

DB2 datatypes, 2-36

conversion to Oracle datatypes, 2-37

implicit conversion, 2-37

restrictions on, 2-37

DBA role, 17-46

DBA_2PC_PENDING data dictionary view, 10-3

DBA_COL_COMMENTS data dictionary view, 12-69

DBA_ROLLBACK_SEGS data dictionary view, 16-97

DBA_TAB_COMMENTS data dictionary view, 12-69

DBLINK_ENCRYPT_LOGIN initialization parameter

setting with ALTER SYSTEM, 10-54

DBMS_OUTPUT package, 12-3

DBMS_ROWID package

and extended rowids, 2-34

DBMSSTDX.SQL script, 13-50, 14-50, 14-55, 14-62

and triggers, 15-96

DBTIMEZONE function, 6-48

DBWR_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 10-54

DD datetime format element, 2-70

DDAY datetime format element, 2-70

DDD datetime format element, 2-70

DDL. See data definition language (DDL)

DEALLOCATE UNUSED clause

of ALTER CLUSTER, 9-8, 9-10

of ALTER INDEX, 9-66

of ALTER TABLE, 11-35

DEBUG ANY PROCEDURE system privilege, 17-37

DEBUG clause

of ALTER FUNCTION, 9-62

of ALTER PACKAGE, 9-124

of ALTER PROCEDURE, 9-127

of ALTER TRIGGER, 12-4

of ALTER TYPE, 12-10

DEBUG object privilege, 17-47

on a function, procedure, or package, 17-49

on a table, 17-48

on a view, 17-48

on an object type, 17-50

debugging

granting system privileges for, 17-37

decimal characters, 2-57

reset for session, 10-9

specifying, 2-65

DECIMAL datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

DECODE function, 6-49

DECOMPOSE function, 6-50

DEFAULT clause

of ALTER TABLE, 11-42

of CREATE TABLE, 15-26

DEFAULT COST clause

of ASSOCIATE STATISTICS, 12-49, 12-51

default index, suppressing, 14-20

DEFAULT profile

assigning to users, 16-94

DEFAULT ROLE clause

of ALTER USER, 12-25

DEFAULT SELECTIVITY clause

of ASSOCIATE STATISTICS, 12-49, 12-51

DEFAULT storage clause

of ALTER TABLESPACE, 11-105

of CREATE TABLESPACE, 15-86

DEFAULT TABLESPACE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

DEFAULT TEMPORARY TABLESPACE clause

of ALTER DATABASE, 9-50

of CREATE DATABASE, 13-25

DEFERRABLE clause

of constraints, 7-18

deferrable constraints, 18-45

DEFERRED clause

of SET CONSTRAINTS, 18-45

definer-rights functions, 13-55

DELETE ANY TABLE system privilege, 17-42

DELETE object privilege, 17-47

on a table, 17-48

on a view, 17-48

DELETE statement, 16-55

triggers on, 15-100

DELETE STATISTICS clause

of ANALYZE, 12-45

DELETE_CATALOG_ROLE role, 17-46

DENSE_RANK function, 6-52

DEREF function, 6-55

DESC clause

of CREATE INDEX, 13-74

DETERMINISTIC clause

of CREATE FUNCTION, 13-56

DG_BROKER_CONFIG_FILEn initialization parameter

setting with ALTER SYSTEM, 10-54

DG_BROKER_START initialization parameter

setting with ALTER SYSTEM, 10-55

dimensions

attributes

adding, 9-60

changing, 9-58

defining, 13-45

dropping, 9-60

compiling invalidated, 9-60

creating, 13-41

defining levels, 13-42

examples, 13-45

granting system privileges on, 17-37

hierarchies

adding, 9-60

changing, 9-58

defining, 13-43

dropping, 9-60

levels

adding, 9-60

defining, 13-43

dropping, 9-60

removing from the database, 16-71

directories. See directory objects

directory objects

as aliases for operating system directories, 13-46

auditing, 12-57

creating, 13-46

granting system privileges on, 17-38

redefining, 13-47

removing from the database, 16-73

direct-path INSERT, 17-54

DISABLE ALL TRIGGERS clause

of ALTER TABLE, 11-88

DISABLE clause

of ALTER INDEX, 9-79

of ALTER TRIGGER, 12-3

of CREATE TABLE, 15-57

DISABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 10-29

DISABLE NOVALIDATE constraint state, 7-21, 15-59

DISABLE PARALLEL DML clause

of ALTER SESSION, 10-4

DISABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 9-107

of CREATE MATERIALIZED VIEW, 14-24

DISABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 10-30

DISABLE RESUMABLE clause

of ALTER SESSION, 10-6

DISABLE ROW MOVEMENT clause

of ALTER TABLE, 11-6, 11-38

of CREATE TABLE, 15-16, 15-54

DISABLE STORAGE IN ROW clause

of ALTER TABLE, 11-45

of CREATE TABLE, 15-38

DISABLE TABLE LOCK clause

of ALTER TABLE, 11-88

DISABLE THREAD clause

of ALTER DATABASE, 9-52

DISABLE VALIDATE constraint state, 7-21, 15-59

DISASSOCIATE STATISTICS statement, 16-64

DISCONNECT SESSION clause

of ALTER SYSTEM, 10-28

DISK_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 10-55

dispatcher processes

creating additional, 10-110

terminating, 10-110

DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 10-55

DISTINCT clause

of SELECT, 18-11

distinct queries, 18-11

distributed queries, 8-15

restrictions on, 8-16

distribution

hints for, 2-104

DML. See data manipulation language (DML)

DML_LOCKS initialization parameter

setting with ALTER SYSTEM, 10-57

domain indexes, 13-62, 13-81, 13-91

and LONG columns, 11-49

associating statistics with, 12-49, 12-50

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

example, D-2

invoking drop routines for, 17-7

modifying, 9-78

parallelizing creation of, 13-82

rebuilding, 9-74

removing from the database, 16-76

specifying alter string for, 9-78

domain_index_clause

of CREATE INDEX, 13-66

DOUBLE PRECISION datatype (ANSI), 2-36

DROP ANY CLUSTER system privilege, 17-37

DROP ANY CONTEXT system privilege, 17-37

DROP ANY DIMENSION system privilege, 17-38

DROP ANY DIRECTORY system privilege, 17-38

DROP ANY INDEX system privilege, 17-38

DROP ANY INDEXTYPE system privilege, 17-38

DROP ANY LIBRARY system privilege, 17-38

DROP ANY MATERIALIZED VIEW system privilege, 17-39

DROP ANY OPERATOR system privilege, 17-39

DROP ANY OUTLINE system privilege, 17-39

DROP ANY PROCEDURE system privilege, 17-40

DROP ANY ROLE system privilege, 17-40

DROP ANY SEQUENCE system privilege, 17-41

DROP ANY SYNONYM system privilege, 17-41

DROP ANY TABLE system privilege, 17-42

DROP ANY TRIGGER system privilege, 17-42

DROP ANY TYPE system privilege, 17-43

DROP ANY VIEW system privilege, 17-44

DROP clause

of ALTER DIMENSION, 9-60

of ALTER INDEXTYPE, 9-88

DROP CLUSTER statement, 16-67

DROP COLUMN clause

of ALTER TABLE, 11-51

DROP CONSTRAINT clause

of ALTER TABLE, 11-58

DROP constraint clause

of ALTER VIEW, 12-32

DROP CONTEXT statement, 16-69

DROP DATABASE LINK statement, 16-70

DROP DIMENSION statement, 16-71

DROP DIRECTORY statement, 16-73

DROP FUNCTION statement, 16-74

DROP INDEX statement, 16-76

DROP INDEXTYPE statement, 16-78

DROP JAVA statement, 16-80

DROP LIBRARY statement, 16-82

DROP LOG GROUP clause

of ALTER TABLE, 11-34

DROP LOGFILE clause

of ALTER DATABASE, 9-21, 9-41

DROP LOGFILE MEMBER clause

of ALTER DATABASE, 9-21, 9-42

DROP MATERIALIZED VIEW LOG statement, 16-85

DROP MATERIALIZED VIEW statement, 16-83

DROP OPERATOR statement, 16-87

DROP OUTLINE statement, 16-89

DROP PACKAGE BODY statement, 16-90

DROP PACKAGE statement, 16-90

DROP PARTITION clause

of ALTER INDEX, 9-69, 9-83

of ALTER TABLE, 11-72

DROP PRIMARY constraint clause

of ALTER TABLE, 11-58

DROP PROCEDURE statement, 16-92

DROP PROFILE statement, 16-94

DROP PROFILE system privilege, 17-40

DROP PUBLIC DATABASE LINK system privilege, 17-37

DROP PUBLIC SYNONYM system privilege, 17-41

DROP ROLE statement, 16-96

DROP ROLLBACK SEGMENT statement, 16-97

DROP ROLLBACK SEGMENT system privilege, 17-40

DROP SEQUENCE statement, 17-2

DROP statements

triggers on, 15-102

DROP SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 9-43

DROP SYNONYM statement, 17-4

DROP TABLE statement, 17-6

DROP TABLESPACE statement, 17-10

DROP TABLESPACE system privilege, 17-42

DROP TRIGGER statement, 17-13

DROP TYPE BODY statement, 17-18

DROP TYPE statement, 17-15

DROP UNIQUE constraint clause

of ALTER TABLE, 11-58

DROP USER statement, 17-20

DROP USER system privilege, 17-43

DROP VALUES clause

of ALTER TABLE ... MODIFY PARTITION, 11-64

DROP VIEW statement, 17-22

DRS_START initialization parameter

setting with ALTER SYSTEM, 10-57

DUAL dummy table, 2-112, 8-15

dump file

limiting size of, 10-9

DUMP function, 6-56

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

DYNAMIC_SAMPLING hint, 2-96

---

E

E

number format element, 2-65

E datetime format element, 2-70

EBCDIC character set, 2-46

EE datetime format element, 2-70

embedded SQL, 1-4, 9-5

precompiler support, 9-5

EMPTY_BLOB function, 6-58

EMPTY_CLOB function, 6-58

ENABLE ALL TRIGGERS clause

of ALTER TABLE, 11-88

ENABLE clause

of ALTER INDEX, 9-79

of ALTER TRIGGER, 12-3

of CREATE TABLE, 15-57

ENABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 10-29

ENABLE NOVALIDATE constraint state, 7-21, 15-58

ENABLE PARALLEL DML clause

of ALTER SESSION, 10-4

ENABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 9-107

of CREATE MATERIALIZED VIEW, 14-24

ENABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 10-30

ENABLE RESUMABLE clause

of ALTER SESSION, 10-6

ENABLE ROW MOVEMENT clause

of ALTER TABLE, 11-6, 11-38

of CREATE TABLE, 15-16, 15-54

ENABLE STORAGE IN ROW clause

of ALTER TABLE, 11-45

of CREATE TABLE, 15-38

ENABLE TABLE LOCK clause

of ALTER TABLE, 11-88

ENABLE THREAD clause

of ALTER DATABASE, 9-52

ENABLE VALIDATE constraint state, 7-20, 15-58

END BACKUP clause

of ALTER DATABASE ... DATAFILE, 9-38

of ALTER TABLESPACE, 11-106, 11-107

ENQUEUE_RESOURCES initialization parameter

setting with ALTER SYSTEM, 10-58

equality test, 5-4

equijoins, 8-10

defining for a dimension, 13-44

equivalency tests, 5-11

error messages

setting language of, 10-9

ERROR_ON_OVERLAP_TIME session parameter, 10-13

ESTIMATE STATISTICS clause

of ANALYZE, 12-41

EVENTS initialization parameter

setting with ALTER SYSTEM, 10-58

EXCEPTIONS INTO clause

of ALTER TABLE, 11-81

restrictions, 11-82

EXCHANGE PARTITION clause

of ALTER TABLE, 11-24, 11-80

EXCHANGE SUBPARTITION clause

of ALTER TABLE, 11-24, 11-80

exchanging partitions

restrictions on, 11-82

EXCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 9-117

of CREATE MATERIALIZED VIEW LOG, 14-39

EXCLUSIVE lock mode, 17-76

EXECUTE ANY INDEXTYPE system privilege, 17-38

EXECUTE ANY OPERATOR system privilege, 17-39

EXECUTE ANY PROCEDURE system privilege, 17-40

EXECUTE ANY TYPE system privilege, 17-43

EXECUTE object privilege, 17-47

on a function, procedure, or package, 17-49

on a library, 17-50

on an indextype, 17-50

on an object type, 17-50

on an operator, 17-50

EXECUTE_CATALOG_ROLE role, 17-46

execution plans

determining, 17-24

dropping outlines for, 16-89

saving, 14-46

EXEMPT ACCESS POLICY system privilege, 17-44

EXISTS condition, 5-14, 5-15

EXISTS conditions, 5-13

EXISTSNODE function, 6-58

EXP function, 6-59

EXP_FULL_DATABASE role, 17-46

EXPLAIN PLAN statement, 17-24

explicit data conversion, 2-48, 2-52

expressions

CASE, 4-6

changing declared type of, 6-188

comparing, 6-49

compound, 4-5

computing with the DUAL table, 8-15

CURSOR, 4-7

datetime, 4-9

in SQL syntax, 4-2

interval, 4-12

lists of, 4-16

object access, 4-12

scalar subqueries as, 4-13

simple, 4-3

type constructor, 4-13

variable, 4-15

extended rowids, 2-34

base 64, 2-34

not directly available, 2-34

extensible indexing

example, D-2

EXTENT MANAGEMENT clause

for temporary tablespaces, 15-94

of CREATE DATABASE, 13-25

of CREATE TABLESPACE, 15-83, 15-87

of CREATE TEMPORARY TABLESPACE, 15-94

EXTENT MANAGEMENT DICTIONARY clause

of CREATE TABLESPACE, 15-88

EXTENT MANAGEMENT LOCAL clause

of CREATE DATABASE, 13-30

of CREATE TABLESPACE, 15-88

of CREATE TEMPORARY TABLESPACE, 15-95

extents

allocating for partitions, 11-35

allocating for subpartitions, 11-35

allocating for tables, 11-35

restricting access by instances, 9-72

specifying maximum number for an object, 7-61

specifying number allocated upon object creation, 7-60

specifying the first for an object, 7-59

specifying the percentage of size increase, 7-60

specifying the second for an object, 7-59

external functions, 13-49, 14-62

external LOBs, 2-27

external procedures, 14-60, 14-62

running from remote database, 14-3

external tables, 15-30

altering, 11-59

creating, 15-33

restrictions on, 15-34

external users, 14-78, 16-34

EXTRACT (datetime) function, 6-60

EXTRACT (XML) function, 6-61

EXTRACTVALUE function, 6-62

---

F

FAILED_LOGIN_ATTEMPTS parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

FAL_CLIENT initialization parameter

setting with ALTER SYSTEM, 10-59

FAL_SERVER initialization parameter

setting with ALTER SYSTEM, 10-59

FAST_START_IO_TARGET initialization parameter

setting with ALTER SESSION, 10-59

FAST_START_MTTR_TARGET initialization parameter

setting with ALTER SYSTEM, 10-60

FAST_START_PARALLEL_ROLLBACK initialization parameter

setting with ALTER SYSTEM, 10-60

FF datetime format element, 2-70

FILE_MAPPING initialization parameter

setting with ALTER SYSTEM, 10-61

files

specifying as a redo log file group, 7-39

specifying as datafiles, 7-39

specifying as tempfiles, 7-39

FILESYSTEMIO_OPTIONS initialization parameter

using with ALTER SYSTEM, 10-61

FINAL clause

of CREATE TYPE, 16-13, 16-14

FIPS

compliance, B-10

flagging, 10-13

FIRST function, 6-63

FIRST_ROWS(n) hint, 2-97

FIRST_VALUE function, 6-65

FIXED_DATE initialization parameter

setting with ALTER SYSTEM, 10-61

FLAGGER session parameter, 10-13

FLASHBACK ANY TABLE system privilege, 17-39, 17-42, 17-44

FLASHBACK object privilege, 17-47

flashback queries, 18-14

using with inserts, 17-58, 18-65

FLOAT datatype, 2-14

DB2, 2-37

SQL/DS, 2-37

FLOAT datatype (ANSI), 2-36

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

FLOOR function, 6-67

FLUSH SHARED POOL clause

of ALTER SYSTEM, 10-30

FM format model modifier, 2-76

FM number format element, 2-65

FOR clause

of ANALYZE ... COMPUTE STATISTICS, 12-39

of ANALYZE ... ESTIMATE STATISTICS, 12-39

of CREATE INDEXTYPE, 13-92

of EXPLAIN PLAN, 17-26

FOR EACH ROW clause

of CREATE TRIGGER, 15-105

FOR UPDATE clause

of CREATE MATERIALIZED VIEW, 14-24

of SELECT, 18-10, 18-26

FORCE ANY TRANSACTION system privilege, 17-44

FORCE clause

of COMMIT, 12-73

of CREATE VIEW, 16-43

of DISASSOCIATE STATISTICS, 16-66

of DROP INDEX, 16-77

of DROP INDEXTYPE, 16-79

of DROP OPERATOR, 16-88

of DROP TYPE, 17-16

of REVOKE, 17-95

of ROLLBACK, 17-102

FORCE LOGGING clause

of ALTER DATABASE, 9-39

of ALTER TABLESPACE, 11-108

of CREATE CONTROLFILE, 13-20

of CREATE DATABASE, 13-28

of CREATE TABLESPACE, 15-86

FORCE PARALLEL DML clause

of ALTER SESSION, 10-4

FORCE TRANSACTION system privilege, 17-44

FORCE_UNION_REWRITE hint, 2-96

foreign key constraints, 7-13

foreign tables

rowids of, 2-35

format models, 2-62

changing the return format, 2-63

date, 2-68

changing, 2-69

default format, 2-69

format elements, 2-69

maximum length, 2-69

modifiers, 2-76

number, 2-64

number, elements of, 2-64

specifying, 2-63

XML, 2-79

formats

for dates and numbers. See format models

of return values from the database, 2-62

of values stored in the database, 2-62

free lists

specifying for a table, partition, cluster, or index, 7-61

specifying for LOBs, 15-39

FREELIST GROUPS parameter

of STORAGE clause, 7-61

FREELISTS parameter

of STORAGE clause, 7-62

FREEPOOLS parameter

of LOB storage, 15-39

FROM clause

of queries, 8-10

FROM COLUMNS clause

of DISASSOCIATE STATISTICS, 16-65

FROM FUNCTIONS clause

of DISASSOCIATE STATISTICS, 16-65

FROM INDEXES clause

of DISASSOCIATE STATISTICS, 16-65

FROM INDEXTYPES clause

of DISASSOCIATE STATISTICS, 16-65

FROM PACKAGES clause

of DISASSOCIATE STATISTICS, 16-65

FROM TYPES clause

of DISASSOCIATE STATISTICS, 16-65

FROM_TZ function, 6-67

FULL hint, 2-97

full outer joins, 18-18

function expressions

built-in, 4-11

user-defined, 4-11

function-based indexes, 13-62

and query rewrite, 10-10

creating, 13-72

disabling, 10-101

enabling, 9-74, 9-79, 10-101

enabling and disabling, 9-74

refreshing, 9-48

functions

See also SQL functions

3GL, calling, 14-2

analytic

user-defined, 13-58

associating statistics with, 12-49, 12-50

avoiding run-time compilation, 9-61

built_in

as expressions, 4-11

calling, 12-66

changing the declaration of, 13-52

changing the definition of, 13-52

datatype of return value, 13-54

datetime, 6-5

DECODE, 6-49

defining an index on, 13-72

examples, 13-59

executing, 12-66

from parallel query processes, 13-57

external, 13-49, 14-62

inverse distribution, 6-114, 6-117

issuing COMMIT or ROLLBACK statements, 10-3

linear regression, 6-125

naming rules, 2-114

partitioning

among parallel query processes, 13-57

privileges executed with, 12-13, 16-10

recompiling explicitly, 9-62

recompiling invalid, 9-61

re-creating, 13-52, 13-95

removing from the database, 16-74

returning collections, 13-57

returning results iteratively, 13-57

schema executed in, 12-13, 16-10

specifying schema and user privileges for, 13-55

statistics, assigning default cost, 12-49

statistics, defining default selectivity, 12-49

stored, 13-49

storing return value of, 12-67

synonyms for, 15-2

table, 13-57

user_defined

as expressions, 4-11

user-defined, 6-219

aggregate, 13-58

using a saved copy, 13-56

FX format model modifier, 2-77

---

G

G number format element, 2-65

GC_FILES_TO_LOCKS initialization parameter

setting with ALTER SYSTEM, 10-61

general recovery clause

of ALTER DATABASE, 9-15, 9-27

global database names

enforcing resolution, 10-62

global indexes. See indexes, globally partitioned

GLOBAL PARTITION BY RANGE clause

of CREATE INDEX, 7-24, 13-66, 13-79, 15-61

GLOBAL QUERY REWRITE system privilege, 17-38, 17-39

GLOBAL TEMPORARY clause

of CREATE TABLE, 15-24

global users, 14-78, 16-35

GLOBAL_CONTEXT_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-62

GLOBAL_NAMES initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-62

Globalization Support

change session settings, 10-9

globally partitioned indexes, 7-24, 13-79, 13-80, 15-61

GRANT ANY OBJECT PRIVILEGE system privilege, 17-44

GRANT ANY PRIVILEGE system privilege, 17-44

GRANT ANY ROLE system privilege, 17-40

GRANT clause

of ALTER USER, 12-26

GRANT CONNECT THROUGH clause

of ALTER USER, 12-23, 12-25

GRAPHIC datatype

DB2, 2-37

SQL/DS, 2-37

greater than or equal to tests, 5-5

greater than tests, 5-5

GREATEST function, 6-68

GROUP BY clause

CUBE extension, 18-22

identifying duplicate groupings, 6-68

of SELECT and subqueries, 18-8, 18-21

ROLLUP extension of, 18-22

group comparison conditions, 5-7

GROUP_ID function, 6-68

GROUPING function, 6-70

grouping sets, 18-23

GROUPING SETS clause

of SELECT and subqueries, 18-23

GROUPING_ID function, 6-71

groupings

filtering out duplicate, 6-68

GUARD ALL clause

of ALTER DATABASE, 9-53

GUARD clause

of ALTER DATABASE, 9-53

GUARD NONE clause

of ALTER DATABASE, 9-53

GUARD STANDBY clause

of ALTER DATABASE, 9-53

---

H

hash clusters

creating, 13-6

single-table, creating, 13-7

specifying hash function for, 13-7

HASH hint, 2-97

HASH IS clause

of CREATE CLUSTER, 13-7

hash joins

allocating memory for, 10-9

enabling and disabling, 10-9

hash partitioning clause

of CREATE TABLE, 15-22, 15-46

hash partitions

adding, 11-70

coalescing, 11-63

HASH_AJ hint, 2-97, 2-98

HASH_AREA_SIZE initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-63

HASH_JOIN_ENABLED initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-63

HASHKEYS clause

of CREATE CLUSTER, 13-6

HAVING condition

of GROUP BY clause, 18-23

heap-organized tables

creating, 15-7

hexadecimal value

returning, 2-67

HEXTORAW function, 6-73

HH datetime format element, 2-70

HH12 datetime format element, 2-70

HH24 datetime format element, 2-70

HI_SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 10-64

hierarchical queries, 2-86, 8-3, 18-20

child rows, 2-86, 8-4

illustrated, 2-87

leaf rows, 2-86

ordering, 18-25

parent rows, 2-86, 8-4

retrieving root and node values, 6-151

hierarchical query clause

of SELECT and subqueries, 18-8

hierarchies

adding to a dimension, 9-60

dropping from a dimension, 9-60

of dimensions, defining, 13-43

HIERARCHY clause

of CREATE DIMENSION, 13-42, 13-43

high water mark

of clusters, 9-10

of indexes, 9-71

of tables, 11-35, 12-37

hints, 8-3

ALL_ROWS hint, 2-94

AND_EQUAL hint, 2-95

CACHE hint, 2-95

CLUSTER hint, 2-96

FIRST_ROWS hint, 2-97

FULL hint, 2-97

HASH hint, 2-97

in SQL statements, 2-92

INDEX hint, 2-98

INDEX_ASC hint, 2-98

INDEX_DESC hint, 2-98

NO_EXPAND hint, 2-100

NO_MERGE hint, 2-101

NO_PUSH_PRED hint, 2-101

NOCACHE hint, 2-100

NOPARALLEL hint, 2-101

NOREWRITE hint, 2-102

ORDERED hint, 2-102

PARALLEL hint, 2-103

passing to the optimizer, 18-59

PQ_DISTRIBUTE hint, 2-104

PUSH_PRED hint, 2-104

PUSH_SUBQ hint, 2-104

REWRITE hint, 2-105

ROWID hint, 2-105

RULE hint, 2-105

syntax, 2-93

USE_CONCAT hint, 2-106

USE_MERGE hint, 2-107

USE_NL hint, 2-107

histograms

creating equiwidth, 6-205

HS_ADMIN_ROLE role, 17-46

HS_AUTOREGISTER initialization parameter

setting with ALTER SYSTEM, 10-64

---

I

I datetime format element, 2-70

IDENTIFIED BY clause

of ALTER ROLE. See CREATE ROLE

of CREATE DATABASE LINK, 13-38

of SET ROLE, 18-48

IDENTIFIED EXTERNALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER. See CREATE USER

of CREATE ROLE, 14-78

of CREATE USER, 16-34

IDENTIFIED GLOBALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER, 12-24

of CREATE ROLE, 14-78

of CREATE USER, 16-35

IDLE_TIME parameter

of ALTER PROFILE, 9-130

IFILE initialization parameter

setting with ALTER SYSTEM, 10-64

IMMEDIATE clause

of SET CONSTRAINTS, 18-45

IMP_FULL_DATABASE role, 17-46

implicit data conversion, 2-48, 2-49, 2-51

IN OUT parameter

of CREATE FUNCTION, 13-53

of CREATE PROCEDURE, 14-65

IN parameter

of CREATE function, 13-53

of CREATE PROCEDURE, 14-65

INCLUDING CONTENTS clause

of DROP TABLESPACE, 17-11

INCLUDING DATAFILES clause

of ALTER DATABASE TEMPFILE DROP clause, 9-38

INCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 9-117

of CREATE MATERIALIZED VIEW LOG, 14-39

INCLUDING TABLES clause

of DROP CLUSTER, 16-68

incomplete object types, 16-3

creating, 16-3, 16-5

INCREMENT BY clause

of ALTER SEQUENCE. See CREATE SEQUENCE

INCREMENT BY parameter

of CREATE SEQUENCE, 14-89

INDEX clause

of ANALYZE, 12-35

of CREATE CLUSTER, 13-6

INDEX hint, 2-98

index keys

compression, 9-67

INDEX object privilege, 17-47

on a table, 17-48

index partitions

creating subpartitions, 13-68

dropping, 9-69

index subpartitions, 13-68

INDEX_ASC hint, 2-98

INDEX_DESC hint, 2-98

indexed clusters

creating, 13-6

indexes, 9-73

access path, optimizing for, 10-10

allocating new extents for, 9-72

application-specific, 13-91

ascending, 13-74

based on indextypes, 13-81

bitmap, 13-69

bitmap join, 13-83

B-tree, 13-62

changing attributes, 9-73

changing parallelism of, 9-72

collecting statistics on, 12-35

on composite-partitioned tables, 13-80

creating, 13-62

creating on a cluster, 13-64

creating on a table, 13-64

deallocating unused space from, 9-71

descending, 13-74

and query rewrite, 13-74

as function-based indexes, 13-74

direct-path inserts, logging, 9-73

disassociating statistics types from, 16-77

domain, 13-62, 13-81, 13-91

domain, example, D-2

dropping index partitions, 16-77

examples, 13-84

function-based, 13-62

creating, 13-72

global partitioned, creating, 13-66

globally partitioned, 7-24, 13-79, 13-80, 15-61

updating, 11-84

granting system privileges on, 17-38

on hash-partitioned tables, 13-80

join, bitmap, 13-83

key compression of, 9-76

key compression, enabling, 9-74

keys, eliminating repetition, 9-74

locally partitioned, 13-80

logging rebuild operations, 9-74

logging rebuild operations on, 9-78

marking as UNUSABLE, 9-79

merging block contents, 9-74

merging contents of index blocks, 9-80

modifying attributes, 9-74

moving, 9-74

on clusters, 13-70

on composite-partitioned tables, creating, 13-68

on hash-partitioned tables

creating, 13-68

on index-organized tables, 13-71

on list-partitioned tables

creating, 13-67

on nested table storage tables, 13-71

on partitioned tables, 13-71

on range-partitioned tables, creating, 13-67

on scalar typed object attributes, 13-71

on table columns, 13-71

on XMLType tables, 13-85

online, 13-77

parallelizing creation of, 13-78

partitioned, 2-109, 13-62

user-defined, 7-24, 13-79, 15-61

partitioning, 13-78

partitions, 13-78

adding new, 9-83

changing default attributes, 9-81

changing physical attributes, 9-73

changing storage characteristics, 9-81

deallocating unused space from, 9-71

dropping, 9-83

marking UNUSABLE, 9-83, 11-83

modifying the real characteristics, 9-82

preventing use of, 9-79

rebuilding, 9-74

rebuilding unusable, 11-83

re-creating, 9-74

removing, 9-81

renaming, 9-83

specifying tablespace, 9-74

specifying tablespace for, 9-76

splitting, 9-81, 9-83

physical attributes, 13-74

preventing use of, 9-79

on range-partitioned tables, 13-80

rebuilding, 9-74

rebuilding while online, 9-77

re-creating, 9-74

removing from the database, 16-76

renaming, 9-74, 9-79

reverse, 9-74, 9-76, 13-76

specifying tablespace for, 9-74, 9-76

statistics on, 13-77

statistics on rebuild, 9-77

statistics on usage, 9-80

storage attributes, 13-74

subpartitions

allocating extents for, 9-83

changing default attributes, 9-81

changing physical attributes, 9-73

changing storage characteristics, 9-81

deallocating unused space from, 9-71, 9-83

marking UNUSABLE, 9-83

modifying, 9-74

moving, 9-74

preventing use of, 9-79

rebuilding, 9-74

re-creating, 9-74

renaming, 9-83

specifying tablespace, 9-74

specifying tablespace for, 9-76

tablespace containing, 13-75

unique, 13-69

unsorted, 13-75

used to enforce constraints, 11-59, 15-60

validating structure, 12-42

index-organized tables

bitmap indexes on, creating, 15-32

creating, 15-7

mapping tables, 11-86

moving, 11-67

mapping tables, creating, 15-32

modifying, 11-38

moving, 11-85

overflow segments

specifying storage, 11-40, 15-47

partitioned, updating secondary indexes, 9-82

PCT_ACCESS_DIRECT statistics, 12-37

primary key indexes

coalescing, 11-41

updating, 11-40

rebuilding, 11-85

rowids of, 2-35

secondary indexes, updating, 9-81

INDEXTYPE clause

of CREATE INDEX, 13-66, 13-81

indextypes

adding operators, 9-87

altering, 9-87

associating statistics with, 12-49, 12-50

changing implementation type, 9-87

comments on, 12-71

creating, 13-91

disassociating from statistics types, 16-78

drop routines, invoking, 16-77

granting system privileges on, 17-38

indexes based on, 13-81

instances, 13-62

removing from the database, 16-78

in-doubt transactions

forcing, 12-73

forcing commit of, 12-73

forcing rollback, 17-102

rolling back, 17-100

inequality test, 5-4

INITCAP function, 6-73

INITIAL parameter

of STORAGE clause, 7-59

initialization parameters

changing session settings, 10-6

CIRCUITS, 10-41

INITIALIZED EXTERNALLY clause

of CREATE CONTEXT, 13-13

INITIALIZED GLOBALLY clause

of CREATE CONTEXT, 13-13

INITIALLY DEFERRED clause

of constraints, 7-20

INITIALLY IMMEDIATE clause

of constraints, 7-19

INITRANS parameter

of ALTER CLUSTER, 9-9

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-32

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE

of CREATE TABLE, 7-54

inline constraints

of ALTER TABLE, 11-43

of CREATE TABLE, 15-27

inline views, 8-13

IN-lists, 2-106

inner joins, 8-11, 18-18

INSERT ANY TABLE system privilege, 17-42

INSERT clause

of MERGE, 17-79

INSERT object privilege, 17-47

on a table, 17-48

on a view, 17-48

INSERT statement, 17-54

append, 2-95

triggers on, 15-100

inserts

and simultaneous update, 17-78

conditional, 17-65

conventional, 17-54

direct-path, 17-54

multitable, 17-64, 17-65

multitable, examples, 17-70

single-table, 17-58

using MERGE, 17-79

instance recovery

continue after interruption, 9-27

INSTANCE session parameter, 10-14

INSTANCE_GROUPS initialization parameter

setting with ALTER SYSTEM, 10-65

INSTANCE_NAME initialization parameter

setting with ALTER SYSTEM, 10-65

INSTANCE_NUMBER initialization parameter

setting with ALTER SYSTEM, 10-65

instances

global name resolution for, 10-62

making index extents available to, 9-72

memory requirements of, 10-47

setting parameters for, 10-33

INSTANTIABLE clause

of CREATE TYPE, 16-13

INSTEAD OF clause

of CREATE TRIGGER, 15-100

INSTEAD OF triggers, 15-100

INSTR function, 6-74

INSTR2 function, 6-74

INSTR4 function, 6-74

INSTRB function, 6-74

INSTRC function, 6-74

INT datatype (ANSI), 2-36

INTEGER datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

integers

generating unique, 14-87

in SQL syntax, 2-55

precision of, 2-56

specifying, 2-12

syntax of, 2-55

integrity constraints. See constraints

internal LOBs, 2-27

International Standards Organization (ISO), B-1

standards, xvii, 1-2, B-2

INTERSECT set operator, 3-6, 18-24

interval datatypes, 2-16

INTERVAL DAY TO SECOND datatype, 2-24

INTERVAL expressions, 4-12

INTERVAL YEAR TO MONTH datatype, 2-23

INTO clause

of EXPLAIN PLAN, 17-26

of INSERT, 17-58

INVALIDATE GLOBAL INDEXES clause

of ALTER TABLE, 11-84

inverse distribution functions, 6-114, 6-117

invoker rights

altering for a Java class, 9-90

altering for an object type, 12-13

defining for a function, 13-55

defining for a Java class, 13-95, 13-97

defining for a package, 14-51

defining for a procedure, 14-63

defining for an object type, 16-10

invoker-rights functions

defining, 13-55

IS NOT NULL operator, 5-13

IS NULL operator, 5-13

IS OF type condition, 5-19

ISO. See International Standards Organization (ISO)

ISOLATION_LEVEL session parameter, 10-14

IW datetime format element, 2-70

IY datetime format element, 2-70

IYY datetime format element, 2-70

IYYY datetime format element, 2-70

---

J

J datetime format element, 2-70

Java

class

creating, 13-94, 13-96

dropping, 16-80

resolving, 9-89, 13-96

Java source schema object

creating, 13-96

methods

return type of, 16-14

resource

creating, 13-94, 13-96

dropping, 16-80

schema object

name resolution of, 13-98

source

compiling, 9-89, 13-96

creating, 13-94

dropping, 16-80

storage formats

CustomDatum, 16-11

SQLData, 16-11

JAVA clause

of CREATE TYPE, 16-15

of CREATE TYPE BODY, 16-29

Java methods

mapping to an object type, 16-15

JAVA_MAX_SESSIONSPACE_LIMIT initialization parameter

setting with ALTER SYSTEM, 10-67

JAVA_MAX_SESSIONSPACE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-66

JAVA_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-66

JOB_QUEUE_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-67

JOIN clause

of CREATE DIMENSION, 13-42

JOIN KEY clause

of ALTER DIMENSION, 9-59

of CREATE DIMENSION, 13-44

join views

example, 16-51

making updatable, 16-48

modifying, 16-60, 17-61, 18-64

joins, 8-9

conditions

defining, 8-9

cross, 18-19

equijoins, 8-10

full outer, 18-18

inner, 8-11, 18-18

left outer, 18-18

natural, 18-19

nested loop, optimizing for, 10-10

outer, 8-11

restrictions, 8-11

parallel, and PQ_DISTRIBUTE hint, 2-104

right outer, 18-18

self, 8-10

without join conditions, 8-11

Julian dates, 2-20

---

K

key compression, 15-32

definition, 9-76

disabling, 9-76, 13-75

enabling, 9-74

of index rebuild, 11-86

of indexes

disabling, 9-76

of index-organized tables, 15-32

key-preserved tables, 16-48

keywords, 2-112

in object names, 2-112

optional, A-5

required, A-4

KILL SESSION clause

of ALTER SYSTEM, 10-29

---

L

L number format element, 2-65

LAG function, 6-76

LANGUAGE clause

of CREATE PROCEDURE, 14-66

of CREATE TYPE, 16-15

of CREATE TYPE BODY, 16-29

large objects. See LOB datatypes

LARGE_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-68

LAST function, 6-77

LAST_DAY function, 6-79

LAST_VALUE function, 6-80

LEAD function, 6-82

LEAST function, 6-83

left outer joins, 18-18

LENGTH function, 6-84

LENGTH2 function, 6-84

LENGTH4 function, 6-84

LENGTHB function, 6-84

LENGTHC function, 6-84

less than tests, 5-5

LEVEL clause

of ALTER DIMENSION, 9-59

of CREATE DIMENSION, 13-42, 13-43

LEVEL pseudocolumn, 2-86, 18-20

and hierarchical queries, 2-86

levels

adding to a dimension, 9-60

dropping from a dimension, 9-60

of dimensions, defining, 13-43

libraries

creating, 14-2

granting system privileges on, 17-38

re-creating, 14-2

removing from the database, 16-82

library units. See Java schema objects

LICENSE_MAX_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-68

LICENSE_MAX_USERS initialization parameter

setting with ALTER SYSTEM, 10-69

LICENSE_SESSIONS_WARNING initialization parameter

setting with ALTER SYSTEM, 10-69

licenses

changing limits, 10-68, 10-69

licensing

changing limits, 10-69

LIKE conditions, 5-15

linear regression functions, 6-125

LIST CHAINED ROWS clause

of ANALYZE, 12-44

list partitioning

adding default partition, 11-71

adding partitions, 11-64, 11-71

adding values, 11-64

creating a default partition, 15-48

creating partitions, 15-48

default partition

adding, 11-64

dropping, 11-64

dropping values, 11-64

merging default with nondefault partitions, 11-78

splitting default partition, 11-74

list subpartitions

adding, 11-62

listeners

registering, 10-33

literals

in SQL statements and functions, 2-54

in SQL syntax, 2-54

LN function, 6-85

LOB columns

adding, 11-41

creating from LONG columns, 2-15, 11-49

defining properties

for materialized views, 14-11

modifying, 11-48

modifying storage, 11-45

restricted in joins, 8-10

restrictions on, 2-28

storage characteristics of materialized views, 9-101

LOB datatypes, 2-27

LOB index clause

of ALTER TABLE, 11-47

of CREATE TABLE, 15-39

LOB storage clause

for partitions, 11-47

of ALTER MATERIALIZED VIEW, 9-101

of ALTER TABLE, 11-14, 11-45

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

of CREATE TABLE, 15-14, 15-37

LOB_storage_clause

of ALTER MATERIALIZED VIEW, 9-95

LOBs

attributes, initializing, 2-28

CACHE READS setting, 2-31

columns

difference from LONG and LONG RAW, 2-28

populating, 2-28

external, 2-27

indexes for, 15-39

internal, 2-27

locators, 2-27

logging attribute, 15-29

modifying physical attributes, 11-56

number of bytes manipulated in, 15-38

saving old versions, 15-38, 15-39

saving values in a cache, 11-45, 15-55

specifying directories for, 13-46

storage

attributes, 15-37

characteristics, 7-55

in-line, 15-37

tablespace for

defining, 15-28

LOCAL clause

of CREATE INDEX, 13-66, 13-80

local users, 14-78, 16-34

LOCAL_LISTENER initialization parameter

setting with ALTER SYSTEM, 10-70

locally managed tablespaces

altering, 11-103

storage attributes, 7-59

locally partitioned indexes, 13-80

LOCALTIMESTAMP function, 6-86

location transparency, 15-2

LOCK ANY TABLE system privilege, 17-42

LOCK TABLE statement, 17-74

LOCK_NAME_SPACE initialization parameter

setting with ALTER SYSTEM, 10-70

LOCK_SGA initialization parameter

setting with ALTER SYSTEM, 10-70

locking

automatic

overriding, 17-74

locks. See table locks

log data

collection during update operations, 9-42

log file clauses

of ALTER DATABASE, 9-21

log files

adding, 9-39

dropping, 9-39

modifying, 9-39

registering, 9-47

renaming, 9-39

setting session path for, 10-9

specifying for the database, 13-26

LOG function, 6-87

log groups

dropping, 11-34

LOG_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 10-71

LOG_ARCHIVE_DEST_n initialization parameter

overriding DELAY setting, 9-32

setting with ALTER SESSION, 10-9, 10-71

LOG_ARCHIVE_DEST_STATE_n initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-72

LOG_ARCHIVE_DUPLEX_DEST initialization parameter

setting with ALTER SYSTEM, 10-73

LOG_ARCHIVE_FORMAT initialization parameter

setting with ALTER SYSTEM, 10-73

LOG_ARCHIVE_MAX_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-74

LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-74

LOG_ARCHIVE_START parameter

of ALTER SYSTEM, 10-74

LOG_ARCHIVE_TRACE initialization parameter

setting with ALTER SYSTEM, 10-75

LOG_BUFFER initialization parameter

setting with ALTER SYSTEM, 10-75

LOG_CHECKPOINT_INTERVAL initialization parameter

setting with ALTER SYSTEM, 10-75

LOG_CHECKPOINT_TIMEOUT initialization parameter

setting with ALTER SYSTEM, 10-76

LOG_CHECKPOINTS_TO_ALERT initialization parameter

setting with ALTER SYSTEM, 10-76

LOG_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 10-76

LOG_PARALLELISM initialization parameter

setting with ALTER SYSTEM, 10-77

LOGFILE clause

OF CREATE DATABASE, 13-26

LOGFILE GROUP clause

of CREATE CONTROLFILE, 13-17

logging, 9-73, 15-86

and redo log size, 7-46

specifying minimal, 7-46

supplemental

dropping, 9-43

supplemental, adding log groups, 11-34

supplemental, dropping log groups, 11-34

LOGGING clause

of ALTER INDEX, 9-73

of ALTER INDEX ... REBUILD, 9-78

of ALTER MATERIALIZED VIEW, 9-102

of ALTER MATERIALIZED VIEW LOG, 9-116

of ALTER TABLE, 11-34

of ALTER TABLESPACE, 11-108

of CREATE MATERIALIZED VIEW, 14-17

of CREATE MATERIALIZED VIEW LOG, 14-36

of CREATE TABLE, 15-29

of CREATE TABLESPACE, 15-86

logical conditions, 5-8

logical standby database

aborting, 9-48

activating, 9-45

stopping, 9-48

LOGICAL_READS_PER_CALL parameter

of ALTER PROFILE, 9-130

LOGICAL_READS_PER_SESSION parameter

of ALTER PROFILE, 9-130

of ALTER RESOURCE COST, 9-134

LOGMNR_MAX_PERSISTENT_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-77

LOGOFF database event

triggers on, 15-104

LOGON database event

triggers on, 15-104

LONG columns

and domain indexes, 11-49

converting to LOB, 2-15, 11-49

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-27

converting from CHAR data, 2-27

LONG VARCHAR datatype

DB2, 2-37

SQL/DS, 2-37

LONG VARGRAPHIC datatype

DB2, 2-37

SQL/DS, 2-37

LOWER function, 6-87

LPAD function, 6-88

LTRIM function, 6-89

---

M

MAKE_REF function, 6-90

MANAGE TABLESPACE system privilege, 17-42

managed recovery

of database, 9-17

wait period of, 9-32

managed standby recovery

as background process, 9-32

overriding delays, 9-32

returning control during, 9-35

terminating automatically, 9-33

terminating existing, 9-34

MANAGED STANDBY RECOVERY clause

of ALTER DATABASE, 9-31

MAP MEMBER clause

of ALTER TYPE, 12-12

of CREATE TYPE, 16-29

MAP methods

specifying, 12-12

map methods

defining for a type, 16-17

MAPPING TABLE clause

of ALTER TABLE, 11-67, 11-86

mapping tables

of index-organized tables, 11-86, 15-32

modifying, 11-40

master databases, 14-5

master tables, 14-5

MATCHES condition, 5-2

materialized join views, 14-32

materialized view logs, 14-32

adding columns, 9-116

creating, 14-32

excluding new values from, 9-117

logging changes to, 9-116

object ID based, 9-117

parallelizing creation, 14-36

partition attributes, changing, 9-115

partitioned, 14-37

physical attributes

specifying, 14-35

physical attributes, changing, 9-115

removing from the database, 16-85

required for fast refresh, 14-32

rowid based, 9-117

saving new values in, 9-117

saving old values in, 14-39

storage attributes

specifying, 14-35

materialized view partition segments

compression of, 9-101, 14-16

materialized view segments

data compression of, 9-101, 14-16

materialized views, 9-104, 14-20

allowing update of, 14-24

changing from rowid-based to primary-key-based, 9-106

changing to primary-key-based, 9-117

complete refresh, 9-105, 14-21

constraints on, 7-22

creating, 14-5

creating comments about, 12-69

for data warehousing, 14-5

degree of parallelism, 9-102, 9-115

during creation, 14-18

enabling and disabling query rewrite, 14-24

examples, 14-27, 14-39

fast refresh, 9-104, 14-20, 14-21

forced refresh, 9-105

index characteristics

changing, 9-102

indexes that maintain, 14-20

join, 14-32

LOB storage attributes, 9-101

logging changes to, 9-102

master table, dropping, 16-84

object type, creating, 14-14

partitions, 9-101

physical attributes, 14-16

changing, 9-100

primary key, 14-22

recording values in master table, 9-116

query rewrite

eligibility for, 7-22

enabling and disabling, 9-107

re-creating during refresh, 9-105

refresh mode

changing, 9-104

refresh time

changing, 9-104

refreshing, 9-48

refreshing after DML on master table, 9-106, 14-22

refreshing on next COMMIT, 9-105, 14-21

removing from the database, 16-83

for replication, 14-5

restricting scope of, 14-15

retrieving data from, 18-4

revalidating, 9-108

rowid, 14-23

rowid values

recording in master table, 9-116

saving blocks in a cache, 9-103

storage attributes, 14-16

changing, 9-100

subquery, 14-25

suppressing creation of default index, 14-20

synonyms for, 15-2

when to populate, 14-18

MAX function, 6-91

MAX_COMMIT_PROPAGATION_DELAY initialization parameter

setting with ALTER SYSTEM, 10-78

MAX_DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 10-78

MAX_DUMP_FILE_SIZE initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-79

MAX_ENABLED_ROLES initialization parameter

setting with ALTER SYSTEM, 10-79

MAX_ROLLBACK_SEGMENTS initialization parameter

setting with ALTER SYSTEM, 10-79

MAX_SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-80

MAXDATAFILES parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-28

MAXEXTENTS parameter

of STORAGE clause, 7-61

MAXINSTANCES parameter

of CREATE CONTROLFILE, 13-19

OF CREATE DATABASE, 13-28

MAXLOGFILES parameter

of CREATE CONTROLFILE, 13-18

of CREATE DATABASE, 13-27

MAXLOGHISTORY parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-27

MAXLOGMEMBERS parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-27

MAXSIZE clause

of ALTER DATABASE, 9-20

MAXTRANS parameter

of ALTER CLUSTER, 9-9

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-32

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE

of CREATE TABLE, 7-55

MAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 14-89

media recovery

avoid on startup, 9-37

designing, 9-27

disabling, 9-35

from specified redo logs, 9-27

of database, 9-27

of datafiles, 9-27

of standby database, 9-27

of tablespaces, 9-27

parallelizing, 9-30

performing ongoing, 9-31

preparing for, 9-39

restrictions, 9-27

sustained standby recovery, 9-31

media types

ORDSYS.ORDAudio, 2-44

ORDSYS.ORDDoc, 2-44

ORDSYS.ORDImage, 2-44

ORDSYS.ORDVideo, 2-44

median values, 6-117

MEMBER clause

of ALTER TYPE, 12-11

of CREATE TYPE, 16-12

of CREATE TYPE BODY, 16-28

membership conditions, 5-9

MERGE hint, 2-99

MERGE PARTITIONS clause

of ALTER TABLE, 11-78

MERGE statement, 17-78

MERGE_AJ hint, 2-97, 2-98

merge_insert_clause

of MERGE, 17-79

methods

overriding a method a supertype, 16-14

preventing overriding in subtypes, 16-14

static, 16-13

without implementation, 16-14

MI datetime format element, 2-70

MI number format element, 2-65

MIGRATE clause

of ALTER DATABASE, 9-26

migrated rows

listing, 12-44

of clusters, 12-38

MIN function, 6-93

MINEXTENTS parameter

of STORAGE clause, 7-60

MINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 11-37

MINIMUM EXTENT clause

of ALTER TABLESPACE, 11-105

of CREATE TABLESPACE, 15-85

MINUS set operator, 3-6, 18-24

MINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 14-90

MM datetime format element, 2-70

MOD function, 6-94

MODE clause

of LOCK TABLE, 17-76

MODIFY clause

of ALTER TABLE, 11-48

MODIFY CONSTRAINT clause

of ALTER TABLE, 11-11, 11-57

of ALTER VIEW, 12-32

MODIFY DEFAULT ATTRIBUTES clause

of ALTER INDEX, 9-68, 9-81

of ALTER TABLE, 11-60

MODIFY LOB clause

of ALTER TABLE, 11-56

MODIFY LOB storage clause

of ALTER MATERIALIZED VIEW, 9-96, 9-101

of ALTER TABLE, 11-56

MODIFY NESTED TABLE clause

of ALTER TABLE, 11-11, 11-56

MODIFY PARTITION clause

of ALTER INDEX, 9-69, 9-82

of ALTER MATERIALIZED VIEW, 9-102

of ALTER TABLE, 11-61

MODIFY scoped_table_ref_constraint clause

of ALTER MATERIALIZED VIEW, 9-104

MODIFY SUBPARTITION clause

of ALTER INDEX, 9-70, 9-83

of ALTER TABLE, 11-64

MODIFY VARRAY clause

of ALTER TABLE, 11-15, 11-57

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

MONITORING clause

of ALTER TABLE, 11-36

of CREATE TABLE, 15-55

MONITORING USAGE clause

of ALTER INDEX, 9-80

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

MONTHS_BETWEEN function, 6-95

MOUNT clause

of ALTER DATABASE, 9-25

MOVE clause

of ALTER TABLE, 11-29, 11-85

MOVE ONLINE clause

of ALTER TABLE, 11-86

MOVE PARTITION clause

of ALTER TABLE, 11-66

MOVE SUBPARTITION clause

of ALTER TABLE, 11-67

MTS. See shared server

multilevel collections, 15-41

MULTISET parameter

of CAST function, 6-25

multitable inserts, 17-64

conditional, 17-64

examples, 17-70

unconditional, 17-64

multi-threaded server. See shared server

---

N

NAME clause

of SET TRANSACTION, 18-52

NAMED clause

of CREATE JAVA, 13-96

namespaces

and object naming rules, 2-113

for nonschema objects, 2-114

for schema objects, 2-113

NATIONAL CHAR datatype (ANSI), 2-36

NATIONAL CHAR VARYING datatype (ANSI), 2-36

NATIONAL CHARACTER datatype (ANSI), 2-36

national character set

fixed versus variable width, 2-11

multibyte character data, 2-33

multibyte character sets, 2-10, 2-11

variable-length strings, 2-11

NATIONAL CHARACTER SET parameter

of ALTER DATABASE, 9-49

of CREATE DATABASE, 13-29

national character sets

changing, 9-49

NATIONAL CHARACTER VARYING datatype (ANSI), 2-36

natural joins, 18-19

NCHAR datatype, 2-10

ANSI, 2-36

NCHAR VARYING datatype (ANSI), 2-36

NCHR function, 6-96

NCLOB datatype, 2-33

transactional support of, 2-33

negative scale, 2-13

nested loop joins

optimizing for, 10-10

nested subqueries, 8-13

NESTED TABLE clause

of ALTER TABLE, 11-12, 11-44

of CREATE TABLE, 15-13, 15-41

of CREATE TRIGGER, 15-105

nested table columns

defining properties

for materialized views, 14-11, 14-12

modifying properties, 11-12

nested tables, 2-39

changing returned value, 11-56

compared with varrays, 2-48

comparison rules, 2-48

creating, 16-3, 16-9

defining as index-organized tables, 11-44

dropping the body of, 17-18

dropping the specification of, 17-15

indexing columns of, 13-72

modifying, 11-56

multilevel, 15-41

storage characteristics of, 11-44, 15-41

update in a view, 15-100

NEW_TIME function, 6-96

NEXT clause

of ALTER MATERIALIZED VIEW...REFRESH, 9-106

NEXT parameter

of STORAGE clause, 7-59

NEXT_DAY function, 6-98

NEXTVAL pseudocolumn, 2-83, 14-87

NL_SJ hint, 2-97, 2-98

NLS_CALENDAR initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-80

NLS_CHARSET_DECL_LEN function, 6-98

NLS_CHARSET_ID function, 6-99

NLS_CHARSET_NAME function, 6-100

NLS_COMP initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_CURRENCY initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_DATE_FORMAT initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_DATE_LANGUAGE initialization parameter, 2-74

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_DUAL_CURRENCY initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_INITCAP function, 6-100

NLS_ISO_CURRENCY initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_LANGUAGE initialization parameter, 2-74, 8-9

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-83

NLS_LENGTH_SEMANTICS initialization parameter

overriding, 2-10

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-83

NLS_LOWER function, 6-102

NLS_NCHAR_CONV_EXCP initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-83

NLS_NUMERIC_CHARACTERS initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-84

NLS_SORT initialization parameter, 8-9

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-84

NLS_TERRITORY initialization parameter, 2-74

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-84

NLS_TIMESTAMP_FORMAT initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-85

NLS_TIMESTAMP_TZ_FORMAT initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-85

NLS_UPPER function, 6-104

NLSSORT function, 6-103

NO FORCE LOGGING clause

of ALTER DATABASE, 9-39

of ALTER TABLESPACE, 11-108

NO_EXPAND hint, 2-100

NO_INDEX hint, 2-101

NO_MERGE hint, 2-101

NO_PUSH_PRED hint, 2-101

NOAPPEND hint, 2-100

NOARCHIVELOG clause

of ALTER DATABASE, 9-21, 9-39

of CREATE CONTROLFILE, 13-19

OF CREATE DATABASE, 9-27, 13-28

NOAUDIT statement, 17-82

NOCACHE clause

of ALTER CLUSTER, 9-10

of ALTER MATERIALIZED VIEW, 9-103

of ALTER MATERIALIZED VIEW LOG, 9-116

of ALTER SEQUENCE. See CREATE SEQUENCE

of ALTER TABLE, 11-35, 15-55

of CREATE CLUSTER, 13-9

of CREATE MATERIALIZED VIEW, 14-17

of CREATE MATERIALIZED VIEW LOG, 14-36

of CREATE SEQUENCE, 14-90

NOCACHE hint, 2-100

NOCOMPRESS clause

of ALTER INDEX ... REBUILD, 9-76

of CREATE INDEX, 13-75

of CREATE TABLE, 15-32

NOCOPY clause

of CREATE FUNCTION, 13-53

of CREATE PROCEDURE, 14-65

NOCYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-142

of CREATE SEQUENCE, 14-90

NOFORCE clause

of CREATE JAVA, 13-96

of CREATE VIEW, 16-43

NOLOGGING mode

and force logging mode, 7-46

for nonpartitioned objects, 7-46

for partitioned objects, 7-46

NOMAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 14-89

NOMINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 11-37

NOMINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-142

of CREATE SEQUENCE, 14-90

NOMONITORING clause

of ALTER TABLE, 11-36

of CREATE TABLE, 15-56

NOMONITORING USAGE clause

of ALTER INDEX, 9-80

NONE clause

of SET ROLE, 18-48

nonequivalency tests, 5-11

nonpadded comparison semantics, 2-45

nonschema objects

list of, 2-108

namespaces, 2-114

NOORDER parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-142

of CREATE SEQUENCE, 14-91

NOPARALLEL clause

of CREATE INDEX, 7-50, 9-11, 9-31, 9-72, 9-102, 9-115, 11-84, 13-8, 13-78, 14-18, 14-37, 15-56

NOPARALLEL hint, 2-101

NOPARALLEL_INDEX hint, 2-101

NORELY clause

of constraints, 7-22

NORESETLOGS clause

of CREATE CONTROLFILE, 13-18

NOREVERSE parameter

of ALTER INDEX ... REBUILD, 9-76

NOREWRITE hint, 2-102

NOROWDEPENDENCIES clause

of CREATE CLUSTER, 13-9

of CREATE TABLE, 15-55

NOSORT clause

of ALTER INDEX, 13-75

NOT condition, 5-8

NOT DEFERRABLE clause

of constraints, 7-18

NOT FINAL clause

of CREATE TYPE, 16-13

NOT IDENTIFIED clause

of ALTER ROLE. See CREATE ROLE

of CREATE ROLE, 14-78

NOT INSTANTIABLE clause

of CREATE TYPE, 16-13, 16-14

NOT NULL clause

of CREATE TABLE, 15-27

NOWAIT clause

of LOCK TABLE, 17-76

NTILE function, 6-105

null, 2-81

difference from zero, 2-81

in conditions, 2-82

table of, 2-82

in functions, 2-81

with comparison conditions, 2-81

null conditions, 5-13

NULLIF function, 6-106

as a form of CASE expression, 6-106

NUMBER datatype, 2-12

converting to VARCHAR2, 2-64

precision, 2-12

scale, 2-12

number format models, 2-64

number functions, 6-3

numbers

comparison rules, 2-45

floating-point, 2-12, 2-14

in SQL syntax, 2-56

precision of, 2-57

rounding, 2-13

spelling out, 2-76

syntax of, 2-56

NUMERIC datatype (ANSI), 2-36

NUMTODSINTERVAL function, 6-107

NUMTOYMINTERVAL function, 6-108

NVARCHAR2 datatype, 2-11

NVL function, 6-109

NVL2 function, 6-110

---

O

O7_DICTIONARY_ACCESSIBILITY initialization parameter

setting with ALTER SYSTEM, 10-85

object access expressions, 4-12

object cache, 10-10, 10-86

OBJECT IDENTIFIER clause

of CREATE TABLE, 15-53

object identifiers

contained in REFs, 2-38

of object views, 16-45

primary key, 15-53

specifying, 15-53

specifying an index on, 15-54

system-generated, 15-53

object instances

types of, 5-19

object privileges

DEBUG, 17-47

FLASHBACK, 17-47

granting, 14-77

multiple, 14-84

on specific columns, 17-34

on a database object

revoking, 17-95

ON COMMIT REFRESH, 17-47

QUERY REWRITE, 17-47

revoking, 17-91

from a role, 17-89, 17-94

from a user, 17-89, 17-93

from PUBLIC, 17-94

UNDER, 17-47

object reference functions, 6-15

object tables

adding rows to, 17-54

as part of hierarchy, 15-52

creating, 15-9, 15-52

querying, 15-52

system-generated column name, 15-52, 15-64, 16-45, 16-49

updating to latest version, 11-36

upgrading, 11-36

object type columns

defining properties

for materialized views, 14-11, 14-12

in a type hierarchy, 15-36

membership in hierarchy, 11-43

modifying properties

for tables, 11-12, 11-43

substitutability, 11-43

object type materialized views

creating, 14-14

object types, 2-38

adding methods to, 12-14

adding new member subprograms, 12-10

allowing object instances of, 16-13

allowing subtypes, 16-13

and subtypes, 12-11

and supertypes, 12-11

attributes, 2-121

in a type hierarchy, 15-36

membership in hierarchy, 11-43

substitutability, 11-43

bodies

creating, 16-25

re-creating, 16-27

SQL examples, 16-30

comparison rules, 2-48

MAP function, 2-48

ORDER function, 2-48

compiling the specification and body, 12-9

components of, 2-38

creating, 16-3, 16-5

defining member methods of, 16-25

disassociating statistics types from, 17-15

dropping methods from, 12-14

dropping the body of, 17-18

dropping the specification of, 17-15

evolved, rebuilding references to, 9-104

function subprogram

declaring, 16-30

function subprograms, 12-11, 16-12, 16-28

granting system privileges on, 17-43

handling dependent types, 12-17

incomplete, 16-3, 16-5

inheritance, 16-14

invalidating dependent types, 12-17

methods, 2-121

nested table, 16-9

order methods, 16-17

privileges on subtypes, 17-35

procedure subprogram

declaring, 16-30

procedure subprograms, 12-11, 16-12, 16-28

references to. See REFs

root, specifying, 16-10

SQL examples, 16-19

static methods of, 16-13

statistics types, 12-48

subtypes, specifying, 16-11

top-level, 16-10

user-defined

creating, 16-9

values

comparing, 16-29

varrays, 16-8

object views, 16-45

base tables

adding rows, 17-54

creating, 16-45

creating subviews, 16-46

defining, 16-39

querying, 16-45

OBJECT_CACHE_MAX_SIZE_PERCENT initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-86

OBJECT_CACHE_OPTIMAL_SIZE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-86

objects. See object types or database objects

OF clause

of CREATE VIEW, 16-45

OFFLINE clause

of ALTER ROLLBACK SEGMENT, 9-139

of ALTER TABLESPACE, 11-105

of CREATE TABLESPACE, 15-87

OIDINDEX clause

of CREATE TABLE, 15-54

OIDs. See object identifiers

OLAP_PAGE_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-86

ON clause

of CREATE OUTLINE, 14-48

ON COMMIT clause

of CREATE TABLE, 15-53

ON COMMIT REFRESH object privilege, 17-47

on a materialized view, 17-49

ON COMMIT REFRESH system privilege, 17-39

ON DATABASE clause

of CREATE TRIGGER, 15-104

ON DEFAULT clause

of AUDIT, 12-57

of NOAUDIT, 17-85

ON DELETE CASCADE clause

of constraints, 7-14

ON DELETE SET NULL clause

of constraints, 7-14

ON DIRECTORY clause

of AUDIT, 12-57

of NOAUDIT, 17-85

ON NESTED TABLE clause

of CREATE TRIGGER, 15-104

ON object clause

of NOAUDIT, 17-85

of REVOKE, 17-95

ON PREBUILT TABLE clause

of CREATE MATERIALIZED VIEW, 14-19

ON SCHEMA clause

of CREATE TRIGGER, 15-104

online backup

of tablespaces, ending, 11-107

ONLINE clause

of ALTER ROLLBACK SEGMENT, 9-139

of ALTER TABLESPACE, 11-105

of CREATE INDEX, 13-77

of CREATE TABLESPACE, 15-87

online indexes, 13-77

rebuilding, 11-86

ONLINE parameter

of ALTER INDEX ... REBUILD, 9-77

online redo logs

reinitializing, 9-43

OPEN clause

of ALTER DATABASE, 9-25

OPEN NORESETLOGS clause

of ALTER DATABASE, 9-26

OPEN READ ONLY clause

of ALTER DATABASE, 9-26

OPEN READ WRITE clause

of ALTER DATABASE, 9-25

OPEN RESETLOGS clause

of ALTER DATABASE, 9-26

OPEN_CURSORS initialization parameter

setting with ALTER SYSTEM, 10-87

OPEN_LINKS initialization parameter

setting with ALTER SYSTEM, 10-87

OPEN_LINKS_PER_INSTANCE initialization parameter

setting with ALTER SYSTEM, 10-87

operands, 3-1

operating system files

dropping, 17-12

removing, 9-38

operators, 3-1

adding to indextypes, 9-88

altering, 9-119

arithmetic, 3-3

binary, 3-2

comments on, 12-70

concatenation, 3-4

dropping from indextypes, 9-88

granting

system privileges on, 17-39

precedence, 3-2

set, 3-6, 18-24

specifying implementation of, 14-43

unary, 3-2

user-defined, 3-6

binding to a function, 14-44

creating, 14-42

dropping, 16-87

function providing implementation, 14-44

how bindings are implemented, 14-44

implementation type, 14-44

return type of binding, 14-44

user-defined, compiling, 9-119

OPTIMAL parameter

of STORAGE clause, 7-63

OPTIMIZER_DYNAMIC_SAMPLING initialization parameter

setting with ALTER SYSTEM, 10-88

OPTIMIZER_FEATURES_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-88

OPTIMIZER_INDEX_CACCHING initialization parameter

setting with ALTER SYSTEM, 10-88

OPTIMIZER_INDEX_CACHING initialization parameter

setting with ALTER SESSION, 10-10

OPTIMIZER_INDEX_COST_ADJ initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-89

OPTIMIZER_MAX_PERMUTATIONS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-89

OPTIMIZER_MODE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-90

OR condition, 5-8, 5-9

OR REPLACE clause

of CREATE CONTEXT, 13-13

of CREATE DIRECTORY, 13-47

of CREATE FUNCTION, 13-52, 13-95

of CREATE LIBRARY, 14-2

of CREATE OUTLINE, 14-47

of CREATE PACKAGE, 14-51

of CREATE PACKAGE BODY, 14-56

of CREATE PROCEDURE, 14-64

of CREATE TRIGGER, 15-98

of CREATE TYPE, 16-9

of CREATE TYPE BODY, 16-27

of CREATE VIEW, 16-42

Oracle reserved words, C-1

Oracle Tools

support of SQL, 1-5

ORACLE_TRACE_COLLECTION_NAME initialization parameter

setting with ALTER SYSTEM, 10-90

ORACLE_TRACE_COLLECTION_PATH initialization parameter

setting with ALTER SYSTEM, 10-90

ORACLE_TRACE_COLLECTION_SIZE initialization parameter

setting with ALTER SYSTEM, 10-91

ORACLE_TRACE_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-91

ORACLE_TRACE_FACILITY_NAME initialization parameter

setting with ALTER SYSTEM, 10-91

ORACLE_TRACE_FACILITY_PATH initialization parameter

setting with ALTER SYSTEM, 10-92

Oracle9i Text

built-in conditions, 5-2

CATSEARCH, 5-2

CONTAINS, 5-2

creating domain indexes, 13-82

MATCHES, 5-2

SCORE operator, 3-2

ORDER BY clause

of queries, 8-9

of SELECT, 8-9, 18-9, 18-25

with ROWNUM, 2-89

ORDER clause

of ALTER SEQUENCE. See CREATE SEQUENCE

ORDER MEMBER clause

of ALTER TYPE, 12-12

of CREATE TYPE BODY, 16-29

ORDER methods

specifying, 12-12

order methods

defining for a type

object types

map methods, 16-17

ORDER parameter

of CREATE SEQUENCE, 14-90

ORDER SIBLINGS BY clause

of SELECT, 18-25

ORDERED hint, 2-102

ORDERED_PREDICATES hint, 2-102

ordinal numbers

specifying, 2-76

spelling out, 2-76

ORDSYS.ORDAudio media type, 2-44

ORDSYS.ORDDoc media type, 2-44

ORDSYS.ORDImage media type, 2-44

ORDSYS.ORDVideo media type, 2-44

ORGANIZATION EXTERNAL clause

of CREATE TABLE, 15-30, 15-33

ORGANIZATION HEAP clause

of CREATE TABLE, 15-30

ORGANIZATION INDEX clause

of CREATE TABLE, 15-30

OS_AUTHENT_PREFIX initialization parameter

setting with ALTER SYSTEM, 10-92

OS_ROLES initialization parameter

setting with ALTER SYSTEM, 10-92

OUT parameter

of CREATE FUNCTION, 13-53

of CREATE PROCEDURE, 14-65

outer joins, 8-11

restrictions, 8-11

outlines

assign to a different category, 9-121

assigning to a different category, 9-120, 9-122

automatically creating and storing, 10-45

copying, 14-48

creating, 14-46

creating on statements, 14-48

dropping from the database, 16-89

enabling and disabling dynamically, 14-46

for use by current session, 14-47

for use by PUBLIC, 14-47

granting

system privileges on, 17-39

private, use by the optimizer, 10-17

rebuilding, 9-120, 9-122

recompiling, 9-120

renaming, 9-120, 9-121, 9-122

replacing, 14-47

storing during the session, 10-12

storing groups of, 14-48

use by the optimizer, 10-120

use to generate execution plans, 10-17

used to generate execution plans, 14-46

out-of-line constraints

of CREATE TABLE, 15-27

OVER clause

of analytic functions, 6-9, 6-11

OVERFLOW clause

of ALTER INDEX, 9-70

of ALTER TABLE, 11-39

of CREATE TABLE, 15-32

OVERRIDING clause

of ALTER TYPE, 12-11

of CREATE TYPE, 16-14

---

P

package bodies

creating, 14-55

re-creating, 14-56

removing from the database, 16-90

packaged procedures

dropping, 16-92

packages

associating statistics with, 12-49, 12-50

avoiding run-time compilation, 9-123

creating, 14-50

disassociating statistics types from, 16-91

invoker rights, 14-52

recompiling explicitly, 9-123

redefining, 14-51

removing from the database, 16-90

specifying schema and privileges of, 14-52

synonyms for, 15-2

PARALLEL clause

of ALTER CLUSTER, 9-8, 9-10

of ALTER DATABASE, 9-30

of ALTER INDEX, 9-66, 9-72

of ALTER MATERIALIZED VIEW, 9-97, 9-102

of ALTER MATERIALIZED VIEW LOG, 9-114, 9-115

of ALTER TABLE, 11-84

of CREATE CLUSTER, 13-8

of CREATE INDEX, 13-78

of CREATE MATERIALIZED VIEW, 14-14, 14-18

of CREATE MATERIALIZED VIEW LOG, 14-35, 14-36

of CREATE TABLE, 15-22, 15-56

parallel execution

hints, 2-103

of DDL statements, 10-4

of DML statements, 10-4

PARALLEL hint, 2-103

parallel joins

and PQ_DISTRIBUTE hint, 2-104

PARALLEL_ADAPTIVE_MULTI_USER initialization parameter

setting with ALTER SYSTEM, 10-93

PARALLEL_AUTOMATIC_TUNING initialization parameter

setting with ALTER SYSTEM, 10-93

PARALLEL_ENABLE clause

of CREATE FUNCTION, 13-57

PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-94

PARALLEL_INSTANCE_GROUP initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-94

PARALLEL_MAX_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-94

PARALLEL_MIN_PERCENT initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-95

PARALLEL_MIN_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-95

PARALLEL_THREADS_PER_CPU initialization parameter

setting with ALTER SYSTEM, 10-96

parameter files

creating, 14-60

parameters

in syntax

optional, A-5

required, A-4

PARAMETERS clause

of ALTER INDEX ... REBUILD, 9-78

of CREATE INDEX, 13-82

PARTITION ... LOB storage clause

of ALTER TABLE, 11-47

PARTITION BY HASH clause

of CREATE TABLE, 15-46

PARTITION BY LIST clause

of CREATE TABLE, 15-48

PARTITION BY RANGE clause

of CREATE TABLE, 15-18, 15-44

PARTITION clause

of ANALYZE, 12-38

of CREATE INDEX, 7-24, 13-79, 15-62

of CREATE TABLE, 15-45

of DELETE, 16-58

of INSERT, 17-59

of LOCK TABLE, 17-75

of UPDATE, 18-62

partition_storage_clause

of ALTER TABLE, 11-14

PARTITION_VIEW_ENABLED initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-96

partitioned indexes, 2-109, 13-62, 13-80

local, creating, 13-66

user-defined, 7-24, 13-79, 15-61

partitioned index-organized tables

secondary indexes, updating, 9-82

partitioned tables, 2-109

partition-extended table names, 2-109

in DML statements, 2-109

restrictions on, 2-110

syntax, 2-110

partitioning

by range, 15-18

clauses

of ALTER INDEX, 9-68

of ALTER TABLE, 11-59

of materialized view logs, 9-115, 14-37

of materialized views, 9-101, 14-8, 14-17

partitions

adding, 11-59

adding rows to, 17-54

allocating extents for, 11-35

based on literal values, 15-48

composite, 2-109

specifying, 15-49

converting into nonpartitioned tables, 11-80

deallocating unused space from, 11-35

dropping, 11-72

exchanging with tables, 11-24

extents

allocating for an index, 9-72

hash, 2-109

adding, 11-70

coalescing, 11-71

specifying, 15-46

index, 13-78

inserting rows into, 17-59

list, adding, 11-71

LOB storage characteristics of, 11-47

locking, 17-74

logging attribute, 15-29

logging insert operations, 11-34

merging, 11-78

modifying, 11-59, 11-61

moving to a different segment, 11-66

physical attributes

changing, 11-32

range, 2-109

adding, 11-68

specifying, 15-44

removing rows from, 11-73, 16-58

renaming, 11-73

revising values in, 18-62

splitting, 11-74

storage characteristics, 7-55

tablespace for

defining, 15-28

PASSWORD EXPIRE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-37

PASSWORD_GRACE_TIME parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-74

PASSWORD_LIFE_TIME parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

PASSWORD_LOCK_TIME parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

PASSWORD_REUSE_MAX parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

PASSWORD_REUSE_TIME parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-73

PASSWORD_VERIFY_FUNCTION parameter

of ALTER PROFILE, 9-130

of CREATE PROFILE, 14-74

passwords

expiration of, 16-37

grace period, 14-73

guaranteeing complexity, 14-73

limiting use and reuse, 14-73

locking, 14-73

making unavailable, 14-73

parameters

of ALTER PROFILE, 14-74

of CREATE PROFILE, 14-70

special characters in, 14-74

PATH_VIEW, 5-13, 5-20

PCT_ACCESS_DIRECT statistics

for index-organized tables, 12-37

PCTFREE parameter

of ALTER CLUSTER, 9-9

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-32

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE TABLE, 7-53

PCTINCREASE parameter

of STORAGE clause, 7-60

PCTTHRESHOLD parameter

of CREATE TABLE, 11-39, 15-31

PCTUSED parameter

of ALTER CLUSTER, 9-9

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-32

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE TABLE, 7-54

PCTVERSION parameter

of LOB storage, 15-38

of LOB storage clause, 11-46

PERCENT_RANK function, 6-112

PERCENTILE_CONT function, 6-114

PERCENTILE_DISC function, 6-117

performance

optimize for nested loop joins, 10-10

optimizing for index access path, 10-10

session optimizer approach, 10-10

PERMANENT clause

of ALTER TABLESPACE, 11-108

of CREATE TABLESPACE, 15-87

PGA_AGGREGATE_TARGET initialization parameter

setting with ALTER SYSTEM, 10-97

physical attributes clause

of ALTER CLUSTER, 9-8

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-32

of CREATE CLUSTER, 13-3

of CREATE MATERIALIZED VIEW, 14-10

of CREATE TABLE, 15-16, 15-27

physical standby database

activating, 9-45

PIPELINED clause

of CREATE FUNCTION, 13-57

plan stability, 14-46

PLAN_TABLE sample table, 17-24

PL/SQL

compatibility with earlier releases, 10-100

program body

of CREATE FUNCTION, 13-59

PLSQL_COMPILER_FLAGS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-97

PLSQL_DEBUG session parameter, 10-15

PLSQL_NATIVE_C_COMPILER initialization parameter

setting with ALTER SYSTEM, 10-98

PLSQL_NATIVE_LIBRARY_DIR initialization parameter

setting with ALTER SYSTEM, 10-98

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter

setting with ALTER SYSTEM, 10-98

PLSQL_NATIVE_LINKER initialization parameter

setting with ALTER SYSTEM, 10-99

PLSQL_NATIVE_MAKE_FILE_NAME initialization parameter

setting with ALTER SYSTEM, 10-99

PLSQL_NATIVE_MAKE_UTILITY initialization parameter

setting with ALTER SYSTEM, 10-99

PLSQL_V2_COMPATIBILITY initialization parameter

setting with ALTER SYSTEM, 10-100

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

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

POWER function, 6-118

PQ_DISTRIBUTE hint, 2-104

PR number format element, 2-65

PRAGMA clause

of ALTER TYPE, 12-12

of CREATE TYPE, 16-8, 16-16

PRAGMA RESTRICT_REFERENCES, 12-12

PRE_PAGE_SGA initialization parameter

setting with ALTER SYSTEM, 10-100

precedence

of conditions, 5-3

of operators, 3-2

precision

number of digits of, 2-57

of NUMBER datatype, 2-12

precompilers

Oracle, 1-4

PRIMARY KEY clause

of constraints, 7-12

of CREATE TABLE, 15-27

primary key constraints, 7-12

enabling, 15-59

index on, 15-60

primary keys

generating values for, 14-87

PRIOR clause

of hierarchical queries, 8-3

PRIVATE clause

of CREATE OUTLINE, 14-47

private outlines

use by the optimizer, 10-17

PRIVATE_SGA parameter

of ALTER PROFILE, 9-130

of ALTER RESOURCE COST, 9-134

privileges

on subtypes of object types, 17-35

revoking from a grantee, 17-91

See also system privileges or object privileges

procedures

3GL, calling, 14-2

avoid run-time compilation, 9-127

calling, 12-66

compile explicitly, 9-127

creating, 14-60, 14-62

declaring

as a Java method, 14-66

as C functions, 14-66

executing, 12-66

external, 14-60, 14-62

running from remote database, 14-3

granting

system privileges on, 17-40

invalidating local objects dependent on, 16-92

issuing COMMIT or ROLLBACK statements, 10-3

naming rules, 2-114

privileges executed with, 12-13, 16-10

recompiling, 9-126

re-creating, 14-64

removing from the database, 16-92

schema executed in, 12-13, 16-10

specifying schema and privileges for, 14-66

synonyms for, 15-2

PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-100

PROFILE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

profiles

adding resource limits, 9-129

assigning to a user, 16-36

changing resource limits, 9-129

creating, 14-69

examples, 14-74

deassigning from users, 16-94

dropping resource limits, 9-129

granting

system privileges on, 17-40

modifying, examples, 9-131

removing from the database, 16-94

proxy clause

of ALTER USER, 12-23, 12-25

pseudocolumns, 2-83

CURRVAL, 2-83

LEVEL, 2-86

NEXTVAL, 2-83

ROWID, 2-88

ROWNUM, 2-89

uses for, 2-90

SYS_NC_ROWINFO$, 15-52, 15-64, 16-45, 16-49

XMLDATA, 2-90

PUBLIC clause

of CREATE OUTLINE, 14-47

of CREATE ROLLBACK SEGMENT, 14-81

of CREATE SYNONYM, 15-3

of DROP DATABASE LINK, 16-70

public database links

dropping, 16-70

public rollback segments, 14-81

public synonyms, 15-3

dropping, 17-4

PUSH_PRED hint, 2-104

---

Q

Q datetime format element, 2-70

queries, 8-2, 18-4

comments in, 8-3

compound, 8-9

correlated

left correlation, 18-16

defined, 8-2

distributed, 8-15

grouping returned rows on a value, 18-21

hierarchical. See hierarchical queries

hierarchical, ordering, 18-25

hints in, 8-3

join, 8-9, 18-18

locking rows during, 18-26

of past data, 18-14

ordering returned rows, 18-25

outer joins in, 18-17

referencing multiple tables, 8-9

select lists of, 8-2

selecting from a random sample of rows, 18-15

sorting results, 8-9

syntax, 8-2

top-level, 8-2

top-N, 2-89

query rewrite

and dimensions, 13-41

and function-based indexes, 10-10

and rule-based optimization, 10-10

defined, 18-4

disabling, 10-101

enabling, 10-101

enabling and disabling, 10-10

QUERY REWRITE object privilege, 17-47

on a materialized view, 17-49

QUERY REWRITE system privilege, 17-38, 17-39

QUERY_REWRITE_ENABLED initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-101

QUERY_REWRITE_INTEGRITY initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-101

QUIESCE RESTRICTED clause

of ALTER SYSTEM, 10-31

QUOTA clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

---

R

range conditions, 5-12

range partitions

adding, 11-68

creating, 15-44

values of, 15-45

RANK function, 6-119

RATIO_TO_REPORT function, 6-121

RAW datatype, 2-27

converting from CHAR data, 2-27

RAWTOHEX function, 6-122

RAWTONHEX function, 6-122

RDBMS_SERVER_DN initialization parameter

setting with ALTER SYSTEM, 10-101

READ object privilege, 17-47

on a materialized directory, 17-50

READ ONLY clause

of ALTER TABLESPACE, 11-107

READ WRITE clause

of ALTER TABLESPACE, 11-107

READ_ONLY_OPEN_DELAYED initialization parameter

setting with ALTER SYSTEM, 10-102

REAL datatype (ANSI), 2-36

REBUILD clause

of ALTER INDEX, 9-67, 9-74

of ALTER MATERIALIZED VIEW, 9-104

of ALTER OUTLINE, 9-120

REBUILD PARTITION clause

of ALTER INDEX, 9-75

REBUILD SUBPARTITION clause

of ALTER INDEX, 9-75

REBUILD UNUSABLE LOCAL INDEXES clause

of ALTER TABLE, 11-83

rebuilding, 9-104

RECOVER AUTOMATIC clause

of ALTER DATABASE, 9-27

RECOVER CANCEL clause

of ALTER DATABASE, 9-15, 9-31

RECOVER clause

of ALTER DATABASE, 9-27

RECOVER CONTINUE clause

of ALTER DATABASE, 9-15, 9-31

RECOVER DATABASE clause

of ALTER DATABASE, 9-15, 9-28

RECOVER DATAFILE clause

of ALTER DATABASE, 9-15, 9-29

RECOVER LOGFILE clause

of ALTER DATABASE, 9-15, 9-30

RECOVER MANAGED STANDBY DATABASE clause

of ALTER DATABASE, 9-17

RECOVER STANDBY DATAFILE clause

of ALTER DATABASE, 9-29

RECOVER STANDBY TABLESPACE clause

of ALTER DATABASE, 9-29

RECOVER TABLESPACE clause

of ALTER DATABASE, 9-15, 9-29

RECOVERABLE, 9-74, 15-30

See also LOGGING clause

recovery

discarding data, 9-25

distributed, enabling, 10-29

instance, continue after interruption, 9-27

media, designing, 9-27

media, performing ongoing, 9-31

of database, 9-15

parallelizing, 9-30

recovery clauses

of ALTER DATABASE, 9-15

RECOVERY_CATALOG_OWNER role, 17-46

RECOVERY_PARALLELISM initialization parameter

setting with ALTER SYSTEM, 10-102

redo allocation latch

avoiding high contention, 10-77

redo log files

specifying, 7-39

specifying for a controlfile, 13-17

redo logs, 9-25

adding, 9-39, 9-40

applying to logical standby database, 9-48

archive location, 10-27

automatic archiving, 10-25

starting, 10-27

stopping, 10-27

automatic name generation, 9-27

clearing, 9-39

disabling specified threads in a cluster database, 9-52

dropping, 9-39, 9-41

enabling and disabling thread, 9-39

enabling specified threads in a cluster database, 9-52

manual archiving, 10-25

manually archiving

all, 10-27

by filename, 10-26

by group number, 10-26

by SCN, 10-25

current, 10-26

next, 10-27

with sequence numbers, 10-25

members

adding to existing groups, 9-41

dropping, 9-42

renaming, 9-39

remove changes from, 9-25

reusing, 7-41

size of, 7-41

specifying, 7-39, 13-26

for media recovery, 9-30

specifying archive mode, 13-28

switching groups, 10-31

threads, 10-25

REF columns

rescoping, 9-104

specifying, 15-26

specifying from table or column level, 15-26

REF constraints

defining scope, for materialized views, 9-99

of ALTER TABLE, 11-43

REF function, 6-123

REFERENCES clause

of CREATE TABLE, 15-27

REFERENCES object privilege, 17-47

on a table, 17-48

on a view, 17-48

REFERENCING clause

of CREATE TRIGGER, 15-98, 15-105

referential integrity constraints, 7-13

REFRESH clause

of ALTER MATERIALIZED VIEW, 9-100, 9-104

of CREATE MATERIALIZED VIEW, 14-10

REFRESH COMPLETE clause

of ALTER MATERIALIZED VIEW, 9-105

of CREATE MATERIALIZED VIEW, 14-20

REFRESH FAST clause

of ALTER MATERIALIZED VIEW, 9-104

of CREATE MATERIALIZED VIEW, 14-20

REFRESH FORCE clause

of ALTER MATERIALIZED VIEW, 9-105

of CREATE MATERIALIZED VIEW, 14-20

REFRESH ON COMMIT clause

of ALTER MATERIALIZED VIEW, 9-105

of CREATE MATERIALIZED VIEW, 14-20

REFRESH ON DEMAND clause

of ALTER MATERIALIZED VIEW, 9-106

of CREATE MATERIALIZED VIEW, 14-20

REFs, 2-38, 7-16

as containers for OIDs, 2-38

dangling, 12-42

updating, 12-42

validating, 12-42

REFTOHEX function, 6-124

REGISTER clause

of ALTER SYSTEM, 10-33

REGISTER LOGFILE clause

of ALTER DATABASE, 9-47

REGR_AVGX function, 6-125

REGR_AVGY function, 6-125

REGR_COUNT function, 6-125

REGR_INTERCEPT function, 6-125

REGR_R2 function, 6-125

REGR_SLOPE function, 6-125

REGR_SXX function, 6-125

REGR_SXY function, 6-125

REGR_SYY function, 6-125

relational tables

creating, 15-9, 15-24

RELY clause

of constraints, 7-22

REMOTE_ARCHIVE_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-102

REMOTE_DEPENDENCIES_MODE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-102

REMOTE_LISTENER initialization parameter

setting with ALTER SYSTEM, 10-103

REMOTE_LOGIN_PASSWORDFILE initialization parameter

and control files, 13-16

and databases, 13-23

setting with ALTER SYSTEM, 10-103

REMOTE_OS_AUTHENT initialization parameter

setting with ALTER SYSTEM, 10-103

REMOTE_OS_ROLES initialization parameter

setting with ALTER SYSTEM, 10-104

RENAME clause

of ALTER INDEX, 9-79

of ALTER OUTLINE, 9-121

of ALTER TABLE, 11-37

of ALTER TRIGGER, 12-3

RENAME CONSTRAINT clause

of ALTER TABLE, 11-58

RENAME DATAFILE clause

of ALTER TABLESPACE, 11-103

RENAME FILE clause

of ALTER DATABASE, 9-14, 9-39

RENAME GLOBAL_NAME clause

of ALTER DATABASE, 9-52

RENAME PARTITION clause

of ALTER INDEX, 9-69, 9-83

of ALTER TABLE, 11-73

RENAME statement, 17-87

RENAME SUBPARTITION clause

of ALTER INDEX, 9-69, 9-83

of ALTER TABLE, 11-73

REPLACE AS OBJECT clause

of ALTER TYPE, 12-10

REPLACE function, 6-133

replication

row-level dependency tracking, 13-9, 15-55

REPLICATION_DEPENDENCY_TRACKING initialization parameter

setting with ALTER SYSTEM, 10-104

reserved words, 2-111, C-1

RESET COMPATIBILITY clause

of ALTER DATABASE, 9-51

reset sequence of, 9-25

RESETLOGS parameter

of CREATE CONTROLFILE, 13-18

RESOLVE clause

of ALTER JAVA CLASS, 9-90

of CREATE JAVA, 13-96

RESOLVER clause

of ALTER JAVA CLASS, 9-90

of ALTER JAVA SOURCE, 9-90

of CREATE JAVA, 13-98

Resource Manager, 10-31

resource parameters

of CREATE PROFILE, 14-70

RESOURCE role, 17-46

RESOURCE_LIMIT initialization parameter

setting with ALTER SYSTEM, 10-104

RESOURCE_MANAGER_PLAN initialization parameter

setting with ALTER SYSTEM, 10-105

RESOURCE_VIEW, 5-13, 5-20

response time

optimizing, 2-97

RESTRICT_REFERENCES pragma

of ALTER TYPE, 12-12

restricted rowids, 2-34

compatibility and migration of, 2-35

RESTRICTED SESSION system privilege, 17-37, 17-41

resumable space allocation, 10-6

RESUMABLE system privilege, 17-44

RESUME clause

of ALTER SYSTEM, 10-31

RETENTION parameter

of LOB storage, 15-39

RETURN clause

of CREATE FUNCTION, 13-54

of CREATE OPERATOR, 14-44

of CREATE TYPE, 16-14

of CREATE TYPE BODY, 16-30

RETURNING clause

of DELETE, 16-61

of INSERT, 17-56, 17-63

of UPDATE, 18-61, 18-67

REUSE clause

of CREATE CONTROLFILE, 13-17

of file specifications, 7-41

REUSE SETTINGS clause

of ALTER FUNCTION, 9-62

of ALTER PACKAGE, 9-124

of ALTER PROCEDURE, 9-127

of ALTER TRIGGER, 12-4

of ALTER TYPE, 12-10

REVERSE clause

of CREATE INDEX, 13-76

reverse indexes, 13-76

REVERSE parameter

of ALTER INDEX ... REBUILD, 9-76

REVOKE clause

of ALTER USER, 12-26

REVOKE CONNECT THROUGH clause

of ALTER USER, 12-23, 12-25

REVOKE statement, 17-89

REWRITE hint, 2-105

right outer joins, 18-18

RM datetime format element, 2-70

RN number format element, 2-65

RNDS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

RNPS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

roles

application, 9-137

AQ_ADMINISTRATOR_ROLE, 17-46

AQ_USER_ROLE, 17-46

authorization

by a password, 14-78

by an external service, 14-78

by the database, 14-78

by the enterprise directory service, 14-78

changing, 9-136

CONNECT, 17-46

creating, 14-77

DBA, 17-46

DELETE_CATALOG_ROLE, 17-46

disabling

for the current session, 18-47, 18-48

effect on user sessions, 9-137

enabling

for the current session, 18-47, 18-48

EXECUTE_CATALOG_ROLE, 17-46

EXP_FULL_DATABASE, 17-46

granting, 17-29

system privileges on, 17-40

to a user, 17-32

to another role, 17-32

to PUBLIC, 17-32

HS_ADMIN_ROLE, 17-46

identifying by password, 14-78

identifying externally, 14-78

identifying through enterprise directory service, 14-78

identifying using a package, 14-78

IMP_FULL_DATABASE, 17-46

RECOVERY_CATALOG_OWNER, 17-46

removing from the database, 16-96

RESOURCE, 17-46

revoking, 17-89

from another role, 16-96, 17-92

from PUBLIC, 17-92

from users, 16-96, 17-92

SELECT_CATALOG_ROLE, 17-46

SNMPAGENT, 17-46

rollback segments

bringing online, 9-138, 9-139

changing storage characteristics, 9-138, 9-139

creating, 14-80

granting

system privileges on, 17-40

public, 14-81

reducing size, 9-138, 9-140

removing from the database, 16-97

specifying optimal size of, 7-63

specifying tablespaces for, 14-82

SQL examples, 14-83

storage characteristics, 14-82

system-generated, 14-80

taking offline, 9-138, 9-139

ROLLBACK statement, 17-100

rollback undo, 9-138, 13-32

ROLLBACK_SEGMENTS initialization parameter

setting with ALTER SYSTEM, 10-105

ROLLUP clause

of SELECT statements, 18-22

ROUND function

date function, 6-135

format models, 6-218

number function, 6-134

routines

calling, 12-66

executing, 12-66

ROW EXCLUSIVE lock mode, 17-76

ROW SHARE lock mode, 17-76

ROW_LOCKING initialization parameter

setting with ALTER SYSTEM, 10-106

ROW_NUMBER function, 6-135

ROWDEPENDENCIES clause

of CREATE CLUSTER, 13-9

of CREATE TABLE, 15-55

ROWID datatype, 2-33

ROWID hint, 2-105

ROWID pseudocolumn, 2-33, 2-35, 2-88

rowids

block portion of, 2-34

description of, 2-33

extended, 2-34

base 64, 2-34

not directly available, 2-34

file portion of, 2-34

nonphysical, 2-35

of foreign tables, 2-35

of index-organized tables, 2-35

restricted, 2-34

compatibility and migration of, 2-35

row portion of, 2-34

uses for, 2-88

ROWIDTOCHAR function, 6-137

ROWIDTONCHAR function, 6-137

row-level dependency tracking, 13-9, 15-55

ROWNUM pseudocolumn, 2-89

uses for, 2-90

rows

adding to a table, 17-54

allowing movement of between partitions, 15-16

inserting

into partitions, 17-59

into remote databases, 17-59

into subpartitions, 17-59

movement between partitions, 15-54

removing

from a cluster, 18-54

from a table, 18-54

from partitions and subpartitions, 16-58

from tables and views, 16-55

selecting in hierarchical order, 8-3

specifying constraints on, 7-15

storing if in violation of constraints, 11-81

RPAD function, 6-138

RR datetime format element, 2-70, 2-74

RRRR datetime format element, 2-70

RTRIM function, 6-139

RULE hint, 2-105

run-time compilation

avoiding, 9-126, 12-30

---

S

S number format element, 2-65

SAMPLE clause

of SELECT, 18-15

of SELECT and subqueries, 18-7

SAVEPOINT statement, 18-2

savepoints

erasing, 12-72

rolling back to, 17-101

specifying, 18-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-70

SCHEMA clause

of CREATE JAVA, 13-97

schema objects, 2-107

auditing

options, 12-62

defining default buffer pool for, 7-63

dropping, 17-20

in other schemas, 2-118

list of, 2-107

name resolution, 2-117

namespaces, 2-113

naming

examples, 2-115

guidelines, 2-115

rules, 2-111

object types, 2-38

on remote databases, 2-118

partitioned indexes, 2-109

partitioned tables, 2-109

parts of, 2-108

protecting location, 15-2

protecting owner, 15-2

providing alternate names for, 15-2

reauthorizing, 9-2

recompiling, 9-2

referring to, 2-116, 10-13

remote, accessing, 13-35

validating structure, 12-42

schemas

changing for a session, 10-13

creating, 14-84

definition of, 2-107

scientific notation, 2-65

SCOPE FOR clause

of ALTER MATERIALIZED VIEW, 9-99

of CREATE MATERIALIZED VIEW, 14-15

SCORE operator, 3-2

security

enforcing, 15-96

segment attributes clause

of CREATE TABLE, 15-16

SEGMENT MANAGEMENT FREELISTS clause

of CREATE TABLESPACE, 15-89

SEGMENT MANAGEMENT PAGETABLE clause

of CREATE TABLESPACE, 15-89

segments

space management

automatic, 15-89

manual, 15-89

using bitmaps, 15-89

using free lists, 15-89

SELECT ANY DICTIONARY system privilege, 17-45

SELECT ANY OUTLINE system privilege, 17-40

SELECT ANY SEQUENCE system privilege, 17-41

SELECT ANY TABLE system privilege, 17-42

select lists, 8-2

ordering, 8-9

SELECT object privilege, 17-47

on a materialized view, 17-49

on a sequence, 17-49

on a table, 17-48

on a view, 17-48

SELECT statement, 8-2, 18-4

SELECT_CATALOG_ROLE role, 17-46

self joins, 8-10

sequences, 2-83, 14-87

accessing values of, 14-87

changing

the increment value, 9-142

creating, 14-87

creating without limit, 14-89

granting

system privileges on, 17-40

guarantee consecutive values, 14-90

how to use, 2-84

increment value, setting, 14-89

incrementing, 14-87

initial value, setting, 14-89

maximum value

eliminating, 9-142

setting, 14-89

setting or changing, 9-142

minimum value

eliminating, 9-142

setting, 14-90

setting or changing, 9-142

number of cached values, changing, 9-142

ordering values, 9-142

preallocating values, 14-90

recycling values, 9-142

removing from the database, 17-2

renaming, 17-87

restarting, 17-2

at a different number, 9-143

at a predefined limit, 14-89

values, 14-90

reusing, 14-87

stopping at a predefined limit, 14-89

synonyms for, 15-2

where to use, 2-84

SERIAL_REUSE initialization parameter

setting with ALTER SYSTEM, 10-106

server parameter files

creating, 14-92

SERVERERROR event

triggers on, 15-103

service name

of remote database, 13-38

SERVICE_NAMES initialization parameter

setting with ALTER SYSTEM, 10-106

session control statements, 9-4

PL/SQL support of, 9-4

session locks

releasing, 10-29

session parameters

changing settings, 10-12

INSTANCE, 10-14

PLSQL_DEBUG, 10-15

SESSION_CACHED_CURSORS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-107

SESSION_MAX_OPEN_FILES initialization parameter

setting with ALTER SYSTEM, 10-107

SESSION_ROLES view, 18-47

sessions

affecting with roles, 9-137

calculating resource cost limits, 9-133

changing resource cost limits, 9-133

disconnecting, 10-28

global name resolution for, 10-9

limiting CPU time, 9-133

limiting data block reads, 9-134

limiting inactive periods, 9-129

limiting private SGA space, 9-134

limiting resource costs, 9-133

limiting total elapsed time, 9-134

limiting total resources, 9-129

modifying characteristics of, 10-6

number of concurrent, 10-68

object cache, 10-10

restricting, 10-31

restricting to privileged users, 10-30

switching to a different instance, 10-14

terminating, 10-29

time zone setting, 10-16

SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-107

SESSIONS_PER_USER parameter

of ALTER PROFILE, 9-130

SESSIONTIMEZONE function, 6-139

SET clause

of ALTER SESSION, 10-6

of ALTER SYSTEM, 10-33

of UPDATE, 18-65

SET CONSTRAINT(S) statement, 18-45

SET DANGLING TO NULL clause

of ANALYZE, 12-42

SET DATABASE clause

of CREATE CONTROLFILE, 13-17

set operators, 3-6, 18-24

INTERSECT, 3-6

MINUS, 3-6

UNION, 3-6

UNION ALL, 3-6

SET ROLE statement, 18-47

SET STANDBY DATABASE clause

of ALTER DATABASE, 9-46

SET STATEMENT_ID clause

of EXPLAIN PLAN, 17-26

SET TIME_ZONE clause

of ALTER DATABASE, 9-24, 9-50

of ALTER SESSION, 10-16

of CREATE DATABASE, 13-25

SET TRANSACTION statement, 18-50

SET UNUSED clause

of ALTER TABLE, 11-51

SGA. See system global area (SGA)

SGA_MAX_SIZE initialization parameter

setting with ALTER SYSTEM, 10-108

SHADOW_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 10-108

SHARE ROW EXCLUSIVE lock mode, 17-76

SHARE UPDATE lock mode, 17-76

SHARED clause

of CREATE DATABASE LINK, 13-36

shared pool

flushing, 10-30

shared server

parameters

DISPATCHERS, 10-55

processes

creating additional, 10-110

terminating, 10-110

system parameters, 10-110

SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 10-108

SHARED_POOL_RESERVED_SIZE initialization parameter

setting with ALTER SYSTEM, 10-109

SHARED_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-109

SHARED_SERVER_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-111

SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-110

SHRINK clause

of ALTER ROLLBACK SEGMENT, 9-140

SHUTDOWN clause

of ALTER SYSTEM, 10-32

SHUTDOWN event

triggers on, 15-103

siblings

ordering in a hierarchical query, 18-25

SIGN function, 6-140

simple comparison conditions, 5-5

simple expressions, 4-3

SIN function, 6-141

SINGLE TABLE clause

of CREATE CLUSTER, 13-7

single-row functions, 6-3

miscellaneous, 6-6

single-table insert, 17-58

SINH function, 6-141

SIZE clause

of ALTER CLUSTER, 9-9

of CREATE CLUSTER, 13-5

of file specifications, 7-41

SKIP_UNUSABLE_INDEXES session parameter, 10-15

SMALLINT datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

SNMPAGENT role, 17-46

SOME operator, 5-5

sort operations

changing linguistic sequence, 10-10

SORT_AREA_RETAINED_SIZE initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-111

SORT_AREA_SIZE initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-112

SOUNDEX function, 6-142

SP datetime format element suffix, 2-76

special characters

in passwords, 14-74

SPECIFICATION clause

of ALTER PACKAGE, 9-123

spelled numbers

specifying, 2-76

SPFILE initialization parameter

setting with ALTER SYSTEM, 10-112

SPLIT PARTITION clause

of ALTER INDEX, 9-69, 9-83

of ALTER TABLE, 11-74

SPTH datetime format element suffix, 2-76

SQL functions

ABS, 6-15

ACOS, 6-16

ADD_MONTHS, 6-16

aggregate, 6-7

analytic, 6-9

applied to LOB columns, 6-2

ASCII, 6-17

ASCIISTR, 6-18

ASIN, 6-19

ATAN, 6-19

ATAN2, 6-20

AVG, 6-20

BFILENAME, 6-22

BIN_TO_NUM, 6-23

BITAND, 6-24

CAST, 6-25

CEIL, 6-28

character

returning character values, 6-4

returning number values, 6-5

CHARTOROWID, 6-28

CHR, 6-29

COALESCE, 6-31

COMPOSE, 6-32

CONCAT, 6-33

conversion, 6-5

CONVERT, 6-34

CORR, 6-35

COS, 6-37

COSH, 6-38

COUNT, 6-38

COVAR_POP, 6-40

COVAR_SAMP, 6-42

CUME_DIST, 6-44

CURRRENT_DATE, 6-46

CURRRENT_TIMESTAMP, 6-47

date, 6-5

DBTIMEZONE, 6-48

DECOMPOSE, 6-50

DENSE_RANK, 6-52

DEREF, 6-55

DUMP, 6-56

EMPTY_BLOB, 6-58

EMPTY_CLOB, 6-58

EXISTSNODE, 6-58

EXP, 6-59

EXTRACT (datetime), 6-60

EXTRACT (XML), 6-61

EXTRACTXML, 6-62

FIRST, 6-63

FIRST_VALUE, 6-65

FLOOR, 6-67

FROM_TZ, 6-67

GREATEST, 6-68

GROUP_ID, 6-68

GROUPING, 6-70

GROUPING_ID, 6-71

HEXTORAW, 6-73

INITCAP, 6-73

INSTR, 6-74

INSTR2, 6-74

INSTR4, 6-74

INSTRB, 6-74

INSTRC, 6-74

LAG, 6-76

LAST, 6-77

LAST_DAY, 6-79

LAST_VALUE, 6-80

LEAD, 6-82

LEAST, 6-83

LENGTH, 6-84

LENGTH2, 6-84

LENGTH4, 6-84

LENGTHB, 6-84

LENGTHC, 6-84

linear regression, 6-125

LN, 6-85

LOCALTIMESTAMP, 6-86

LOG, 6-87

LOWER, 6-87

LPAD, 6-88

LTRIM, 6-89

MAKE_REF, 6-90

MAX, 6-91

MIN, 6-93

MOD, 6-94

MONTHS_BETWEEN, 6-95

NCHR, 6-96

NEW_TIME, 6-96

NEXT_DAY, 6-98

NLS_CHARSET_DECL_LEN, 6-98

NLS_CHARSET_ID, 6-99

NLS_CHARSET_NAME, 6-100

NLS_INITCAP, 6-100

NLS_LOWER, 6-102

NLS_UPPER, 6-104

NLSSORT, 6-103

NLV2, 6-110

NTILE, 6-105

NULLIF, 6-106

number, 6-3

NUMTODSINTERVAL, 6-107

NUMTOYMINTERVAL, 6-108

NVL, 6-109

object reference, 6-15

PERCENT_RANK, 6-112

PERCENTILE_CONT, 6-114

PERCENTILE_DISC, 6-117

POWER, 6-118

RANK, 6-119

RATIO_TO_REPORT, 6-121

RAWTOHEX, 6-122

RAWTONHEX, 6-122

REF, 6-123

REFTOHEX, 6-124

REGR_AVGX, 6-125

REGR_AVGY, 6-125

REGR_COUNT, 6-125

REGR_INTERCEPT, 6-125

REGR_R2, 6-125

REGR_SLOPE, 6-125

REGR_SXX, 6-125

REGR_SXY, 6-125

REGR_SYY, 6-125

REPLACE, 6-133

ROUND (date), 6-135

ROUND (number), 6-134

ROW_NUMBER, 6-135

ROWIDTOCHAR, 6-137

ROWIDTONCHAR, 6-137

RPAD, 6-138

RTRIM, 6-139

SESSIONTIMEZONE, 6-139

SIGN, 6-140

SIN, 6-141

single-row, 6-3

miscellaneous, 6-6

SINH, 6-141

SOUNDEX, 6-142

SQRT, 6-143

STDDEV, 6-144

STDDEV_POP, 6-145

STDDEV_SAMP, 6-147

SUBSTR, 6-148

SUBSTR2, 6-148

SUBSTR4, 6-148

SUBSTRB, 6-148

SUBSTRC, 6-148

SUM, 6-150

SYS_CONNECT_BY_PATH, 6-151

SYS_CONTEXT, 6-152

SYS_DBURIGEN, 6-157

SYS_EXTRACT_UTC, 6-159

SYS_GUID, 6-159

SYS_TYPEID, 6-160

SYS_XMLAGG, 6-161

SYS_XMLGEN, 6-163

SYSDATE, 6-164

SYSTIMESTAMP, 6-165

TAN, 6-165

TANH, 6-166

TO_CHAR (character), 6-167

TO_CHAR (datetime), 6-168

TO_CHAR (number), 6-170

TO_CLOB, 6-172

TO_DATE, 6-172

TO_DSINTERVAL, 6-174

TO_LOB, 6-175

TO_MULTI_BYTE, 6-176

TO_NCHAR (character), 6-177

TO_NCHAR (datetime), 6-178

TO_NCHAR (number), 6-179

TO_NCLOB, 6-180

TO_NUMBER, 6-180

TO_SINGLE_BYTE, 6-181

TO_TIMESTAMP, 6-182

TO_YMINTERVAL, 6-185

TRANSLATE, 6-185

TRANSLATE...USING, 6-187

TREAT, 6-188

TRIM, 6-190

TRUNC (date), 6-192

TRUNC (number), 6-191

TZ_OFFSET, 6-192

UID, 6-193

UNISTR, 6-194

UPDATEXML, 6-194

UPPER, 6-196

USER, 6-196

USERENV, 6-197

VALUE, 6-199

VAR_POP, 6-199

VAR_SAMP, 6-201

VARIANCE, 6-203

VSIZE, 6-204

WIDTH_BUCKET, 6-205

SQL statements

auditing

by access, 12-57

by proxy, 12-56

by session, 12-57

by user, 12-55

stopping, 17-82

successful, 12-58

DDL, 9-2

determining the execution plan for, 17-24

DML, 9-4

organization of, 9-5

rolling back, 17-100

session control, 9-4

space allocation, resumable, 10-6

suspending and completing, 10-6

system control, 9-5

tracking the occurrence in a session, 12-52

transaction control, 9-4

type of, 9-2

undoing, 17-100

SQL*Loader inserts, logging, 9-73

SQL:99 standards, 1-2

SQL_TRACE initialization parameter

setting with ALTER SYSTEM, 10-113

SQL_TRACE session parameter, 10-15

SQL92_SECURITY initialization parameter

setting with ALTER SYSTEM, 10-113

SQLData Java storage format, 16-11

SQL/DS datatypes, 2-36

conversion to Oracle datatypes, 2-37

implicit conversion, 2-37

restrictions on, 2-37

SQLJ object types

creating, 16-11

mapping a Java class to, 16-12

SQRT function, 6-143

SS datetime format element, 2-70

SSSSS datetime format element, 2-70

standalone procedures

dropping, 16-92

standard SQL, B-1

Oracle extensions to, B-11

standby database

recovering, 9-29

standby databases

activating, 9-45

applying archive logs, 9-32

committing to primary status, 9-47

controlling use, 9-53

designing media recovery, 9-27

mounting, 9-25

recovering, 9-28, 9-29

STANDBY_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 10-113

STANDBY_FILE_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 10-114

star transformation, 2-106

STAR_TRANSFORMATION hint, 2-106

STAR_TRANSFORMATION_ENABLED initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-114

START LOGICAL STANDBY APPLY clause

of ALTER DATABASE, 9-48

START WITH clause

of ALTER MATERIALIZED VIEW...REFRESH, 9-106

of queries and subqueries, 18-20

of SELECT and subqueries, 18-8

START WITH parameter

of CREATE SEQUENCE, 14-89

STARTUP event

triggers on, 15-103

startup_clauses

of ALTER DATABASE, 9-15

STATIC clause

of ALTER TYPE, 12-11

of CREATE TYPE, 16-13

of CREATE TYPE BODY, 16-28

statistics

collection during index rebuild, 9-74

computing exactly, 12-38

deleting from the data dictionary, 12-45

estimating, 12-41

forcing disassociation, 16-66

on index usage, 9-80

on indexes, 13-77

on scalar object attributes

collecting, 12-33

on schema objects

collecting, 12-33

deleting, 12-33

user-defined

dropping, 16-77, 16-78, 16-91, 17-7, 17-15

statistics types

associating

with columns, 12-50

associating with datatypes, 12-49, 12-50

associating with domain indexes, 12-49, 12-50

associating with functions, 12-49, 12-50

associating with indextypes, 12-49, 12-50

associating with packages, 12-49, 12-50

disassociating

from columns, 16-64

from domain indexes, 16-64

from functions, 16-64

from indextypes, 16-64

from packages, 16-64

from types, 16-64

STATISTICS_LEVEL initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-114

STDDEV function, 6-144

STDDEV_POP function, 6-145

STDDEV_SAMP function, 6-147

STOP LOGICAL STANDBY clause

of ALTER DATABASE, 9-48

STORAGE clause

of ALTER CLUSTER, 9-9

of ALTER INDEX, 9-66, 9-73

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER ROLLBACK SEGMENT, 9-139

of CREATE MATERIALIZED VIEW LOG, 14-35

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE ROLLBACK SEGMENTS, 14-82

of CREATE TABLE, 7-55, 15-12

of CREATE TABLESPACE, 15-83

STORAGE IN ROW clause

of ALTER TABLE, 11-45

storage parameters

default, changing, 11-105

resetting, 18-54

STORE IN clause

of ALTER TABLE, 11-40, 15-47

STORE IN DEFAULT clause

of CREATE INDEX, 13-81

STORE IN tablespace clause

of CREATE INDEX, 13-81

stored functions, 13-49

strings

converting to ASCII values, 6-18

converting to unicode, 6-32

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, 12-58

determining the cost of, 17-24

syntax, 9-5, A-2

SUBPARTITION BY HASH clause

of CREATE TABLE, 15-20, 15-50

SUBPARTITION BY LIST clause

of CREATE TABLE, 15-50

SUBPARTITION clause

of ANALYZE, 12-38

of CREATE INDEX, 13-81

of DELETE, 16-58

of INSERT, 17-59

of LOCK TABLE, 17-75

of UPDATE, 18-62

subpartition template

creating, 11-61

replacing, 11-61

subpartition-extended table names, 2-109

in DML statements, 2-109

restrictions on, 2-110

syntax, 2-110

subpartitions

adding, 11-62

adding rows to, 17-54

allocating extents for, 11-35, 11-64

coalescing, 11-63

converting into nonpartitioned tables, 11-80

creating, 15-20

creating a template for, 11-61, 15-49

deallocating unused space from, 11-35, 11-64

exchanging with tables, 11-24

hash, 15-50

inserting rows into, 17-59

list, 15-50

list, adding, 11-62

locking, 17-74

logging insert operations, 11-34

moving to a different segment, 11-67

physical attributes

changing, 11-32

removing rows from, 11-73, 16-58

renaming, 11-73

revising values in, 18-62

specifying, 15-49

template, creating, 15-49

template, dropping, 11-61

template, replacing, 11-61

subqueries, 8-2, 8-13, 18-4

assigning names to, 18-10

containing subqueries, 8-13

correlated, 8-13

defined, 8-2

extended subquery unnesting, 8-15

factoring of, 18-10

inline views, 8-13

nested, 8-13

of past data, 18-14

scalar, 4-13

used as expressions, 4-13

to insert table data, 15-62

unnesting, 8-14

using in place of expressions, 4-13

SUBSTR function, 6-148

SUBSTR2 function, 6-148

SUBSTR4 function, 6-148

SUBSTRB function, 6-148

SUBSTRC function, 6-148

subtotal values

deriving, 18-22

subtypes, 12-11

dropping safely, 17-16

SUM function, 6-150

supertypes, 12-11

supplemental logging

identification key (full), 9-42

minimal, 9-42

SUSPEND clause

of ALTER SYSTEM, 10-31

sustained standby recovery mode, 9-31

SWITCH LOGFILE clause

of ALTER SYSTEM, 10-31

SYEAR datetime format element, 2-70

synonyms

changing the definition of, 17-4

creating, 15-2

granting

system privileges on, 17-41

local, 15-5

private, dropping, 17-4

public, 15-3

dropping, 17-4

remote, 15-5

removing from the database, 17-4

renaming, 17-87, 17-88

synonyms for, 15-2

syntax diagrams, A-2

loops, A-5

multipart diagrams, A-5

SYS schema

auditing, 10-37

database triggers stored in, 15-106

functions stored in, 15-106

SYS user

assigning password for, 13-26

SYS_CONNECT_BY_PATH function, 6-151

SYS_CONTEXT function, 6-152

SYS_DBURIGEN function, 6-157

SYS_EXTRACT_UTC function, 6-159

SYS_GUID function, 6-159

SYS_NC_ROWINFO$ column, 15-52, 15-64, 16-45, 16-48

SYS_NC_ROWINFO$ pseudocolumn, 15-52, 15-64, 16-45, 16-49

SYS_TYPEID function, 6-160

SYS_XMLAGG function, 6-161

SYS_XMLGEN function, 6-163

SYSDATE function, 6-164

SYSDBA system privilege, 17-45

SYSOPER system privilege, 17-45

system control statements, 9-5

PL/SQL support of, 9-5

system date

altering, 10-61

system events

attributes of, 15-106

triggers on, 15-103

system global area

flushing, 10-30

updating, 10-28

system privileges

ADMINISTER DATABASE TRIGGER, 17-42

ALTER ANY CLUSTER, 17-37

ALTER ANY DIMENSION, 17-37

ALTER ANY INDEX, 17-38

ALTER ANY INDEXTYPE, 17-38

ALTER ANY MATERIALIZED VIEW, 17-39

ALTER ANY OUTLINE, 17-39

ALTER ANY PROCEDURE, 17-40

ALTER ANY ROLE, 17-40

ALTER ANY SEQUENCE, 17-41

ALTER ANY TABLE, 17-41

ALTER ANY TRIGGER, 17-42

ALTER ANY TYPE, 17-43

ALTER DATABASE, 17-37

ALTER PROFILE, 17-40

ALTER RESOURCE COST, 17-41

ALTER ROLLBACK SEGMENT, 17-40

ALTER SESSION, 17-41

ALTER SYSTEM, 17-37

ALTER TABLESPACE, 17-42

ALTER USER, 17-43

ANALYZE ANY, 17-44

AUDIT ANY, 17-44

AUDIT SYSTEM, 17-37

BACKUP ANY TABLE, 17-41

BECOME USER, 17-43

COMMENT ANY TABLE, 17-44

CREATE ANY CLUSTER, 17-37

CREATE ANY CONTEXT, 17-37

CREATE ANY DIMENSION, 17-37

CREATE ANY DIRECTORY, 17-38

CREATE ANY INDEX, 17-38

CREATE ANY INDEXTYPE, 17-38

CREATE ANY LIBRARY, 17-38

CREATE ANY MATERIALIZED VIEW, 17-39

CREATE ANY OPERATOR, 17-39

CREATE ANY OUTLINE, 17-39

CREATE ANY PROCEDURE, 17-40

CREATE ANY SEQUENCE, 17-40

CREATE ANY SYNONYM, 17-41

CREATE ANY TABLE, 17-41

CREATE ANY TRIGGER, 17-42

CREATE ANY TYPE, 17-43

CREATE ANY VIEW, 17-43

CREATE CLUSTER, 17-36

CREATE DATABASE LINK, 17-37

CREATE DIMENSION, 17-37

CREATE INDEXTYPE, 17-38

CREATE LIBRARY, 17-38

CREATE MATERIALIZED VIEW, 17-39

CREATE OPERATOR, 17-39

CREATE PROCEDURE, 17-40

CREATE PROFILE, 17-40

CREATE PUBLIC DATABASE LINK, 17-37

CREATE PUBLIC SYNONYM, 17-41

CREATE ROLE, 17-40

CREATE ROLLBACK SEGMENT, 17-40

CREATE SEQUENCE, 17-40

CREATE SESSION, 17-41

CREATE SYNONYM, 17-41

CREATE TABLE, 17-41

CREATE TABLESPACE, 17-42

CREATE TRIGGER, 17-42

CREATE TYPE, 17-43

CREATE USER, 17-43

CREATE VIEW, 17-43

DEBUG ANY PROCEDURE, 17-37

DELETE ANY TABLE, 17-42

DROP ANY CLUSTER, 17-37

DROP ANY CONTEXT, 17-37

DROP ANY DIMENSION, 17-38

DROP ANY DIRECTORY, 17-38

DROP ANY INDEX, 17-38

DROP ANY INDEXTYPE, 17-38

DROP ANY LIBRARY, 17-38

DROP ANY MATERIALIZED VEIW, 17-39

DROP ANY OPERATOR, 17-39

DROP ANY OUTLINE, 17-39

DROP ANY PROCEDURE, 17-40

DROP ANY ROLE, 17-40

DROP ANY SEQUENCE, 17-41

DROP ANY SYNONYM, 17-41

DROP ANY TABLE, 17-42

DROP ANY TRIGGER, 17-42

DROP ANY TYPE, 17-43

DROP ANY VIEW, 17-44

DROP PROFILE, 17-40

DROP PUBLIC DATABASE LINK, 17-37

DROP PUBLIC SYNONYM, 17-41

DROP ROLLBACK SEGMENT, 17-40

DROP TABLESPACE, 17-42

DROP USER, 17-43

EXECUTE ANY INDEXTYPE, 17-38

EXECUTE ANY OPERATOR, 17-39

EXECUTE ANY PROCEDURE, 17-40

EXECUTE ANY TYPE, 17-43

EXEMPT ACCESS POLICY, 17-44

FLASHBACK ANY TABLE, 17-39, 17-42, 17-44

FORCE ANY TRANSACTION, 17-44

FORCE TRANSACTION, 17-44

GLOBAL QUERY REWRITE, 17-38, 17-39

GRANT ANY OBJECT PRIVILEGE, 17-44

GRANT ANY PRIVILEGE, 17-44

GRANT ANY ROLE, 17-40

granting, 14-77, 17-29

to a role, 17-32

to a user, 17-31

to PUBLIC, 17-32

INSERT ANY TABLE, 17-42

list of, 17-36

LOCK ANY TABLE, 17-42

MANAGE TABLESPACE, 17-42

ON COMMIT REFRESH, 17-39

QUERY REWRITE, 17-38, 17-39

RESTRICTED SESSION, 17-37, 17-41

RESUMABLE, 17-44

revoking, 17-89

from a role, 17-91

from a user, 17-91

from PUBLIC, 17-92

SELECT ANY DICTIONARY, 17-45

SELECT ANY OUTLINE, 17-40

SELECT ANY SEQUENCE, 17-41

SELECT ANY TABLE, 17-42

SYSDBA, 17-45

SYSOPER, 17-45

UNDER ANY TYPE, 17-43

UNDER ANY VIEW, 17-44

UNLIMITED TABLESPACE, 17-42

UPDATE ANY TABLE, 17-42

system resources

enabling and disabling, 10-104

SYSTEM tablespace

locally managed, 13-30

SYSTEM user

assigning password for, 13-26

SYSTIMESTAMP function, 6-165

SYYYY datetime format element, 2-70

---

T

table

XMLType, querying, 15-64

TABLE clause

of ANALYZE, 12-36

of DELETE, 16-60

of INSERT, 17-60

of SELECT, 18-16

of TRUNCATE, 18-55

of UPDATE, 18-62, 18-63, 18-65

table functions

creating, 13-57

table locks

disabling, 11-88

duration of, 17-74

enabling, 11-88

EXCLUSIVE, 17-75, 17-76

modes of, 17-76

on partitions, 17-75

on remote database, 17-76

on subpartitions, 17-75

and queries, 17-74

ROW EXCLUSIVE, 17-75, 17-76

ROW SHARE, 17-75, 17-76

SHARE, 17-75

SHARE ROW EXCLUSIVE, 17-76

SHARE UPDATE, 17-76

table partition segments

compression of, 11-33, 15-29

table REF constraints, 7-16

of CREATE TABLE, 15-26

table segments

data compression of, 11-33, 15-29

tables

adding rows to, 17-54

aliases, 2-121

in CREATE INDEX, 13-71

in DELETE, 16-61

allocating extents for, 11-35

assigning to a cluster, 15-35

changing degree of parallelism on, 11-84

changing existing values in, 18-59

collecting statistics on, 11-36, 12-36

comments on, 12-70

creating, 15-7

multiple, 14-84

creating comments about, 12-69

data stored outside database, 15-33

deallocating unused space from, 11-35

default physical attributes

changing, 11-32

degree of parallelism

specifying, 15-7

disassociating statistics types from, 17-7

dropping

along with cluster, 16-68

along with owner, 17-20

indexes of, 17-7

partitions of, 17-7

external, 15-30

creating, 15-33

restrictions on, 15-34

externally organized, 15-30

granting

system privileges on, 17-41

heap organized, 15-30

index-organized, 15-30

overflow segment for, 15-32

space in index block, 11-39, 15-31

inserting rows with a subquery, 15-62

inserting using the direct-path method, 17-54

joining in a query, 18-18

LOB storage of, 7-55

locking, 17-74

logging

insert operations, 11-34

table creation, 15-29

migrated and chained rows in, 12-44

moving, 11-29

moving to a new segment, 11-85

moving, index-organized, 11-85

nested

creating, 16-19

storage characteristics, 15-41

object

creating, 15-9

object, querying, 15-52

of XMLType, creating, 15-64

organization, defining, 15-30

parallel creation of, 15-56

parallelism

setting default degree, 15-56

partition attributes of, 11-60

partitioning, 2-109, 15-7, 15-44

allowing rows to move between partitions, 11-38

default attributes of, 11-60

physical attributes

changing, 11-32

relational

creating, 15-9

remote, accessing, 13-35

removing from the database, 17-6

removing rows from, 16-55

renaming, 11-37, 17-87

restricting

records in a block, 11-37

retrieving data from, 18-4

saving blocks in a cache, 11-35, 15-54

SQL examples, 15-65

storage attributes

defining, 15-7

storage characteristics

defining, 7-55

storage properties, 15-36

storage properties of, 15-27

subpartition attributes of, 11-60

synonyms for, 15-2

tablespace for

defining, 15-7, 15-28

temporary

duration of data, 15-53

session-specific, 15-24

transaction specific, 15-24

unclustering, 16-67

updating through views, 16-47

validating structure, 12-42

with unusable indexes, 10-15

TABLESPACE clause

of ALTER INDEX ... REBUILD, 9-76

of CREATE CLUSTER, 13-6

of CREATE INDEX, 13-75

of CREATE MATERIALIZED VIEW, 14-16

of CREATE MATERIALIZED VIEW LOG, 14-36

of CREATE ROLLBACK SEGMENTS, 14-82

of CREATE TABLE, 15-28

tablespaces, 11-105

allocating space for users, 16-36

allowing write operations on, 11-107

automatic segment-space management, 2-16, 15-89

backing up datafiles, 11-106

bringing online, 11-105, 15-87

coalescing free extents, 11-108

converting

from permanent to temporary, 11-108

from temporary to permanent, 11-108

creating, 15-80

datafiles

adding, 11-103

renaming, 11-103

default temporary, 9-50

learning name of, 9-50

designing media recovery, 9-27

dropping contents, 17-11

ending online backup, 11-107

extent management, 15-94

extent size, 15-85

granting system privileges on, 17-42

in FORCE LOGGING mode, 11-108, 15-86

locally managed, 7-59

altering, 11-103

temporary, 15-94

logging attribute, 11-108, 15-86

managing extents of, 15-87

of session duration, 15-93

permanent objects in, 15-87

read only, 11-107

reconstructing lost or damaged, 9-27, 9-36

recovering, 9-27, 9-29

removing from the database, 17-10

size of free extents in, 11-105

specifying

datafiles for, 15-84

for a table, 15-27

for a user, 16-36

for index rebuild, 11-86

taking offline, 11-105, 15-87

tempfiles

adding, 11-103

temporary

creating, 15-93

specifying for a user, 16-36

temporary objects in, 15-87

temporary, defining for the database, 13-25

undo

altering, 11-103

creating, 13-32, 15-83

dropping, 17-11

TAN function, 6-165

TANH function, 6-166

TAPE_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 10-114

TEMPFILE clause

of ALTER DATABASE, 9-19, 9-38

of CREATE TEMPORARY TABLESPACE, 15-94

tempfiles

bringing online, 9-38

defining for a tablespace, 15-82

defining for a temporary tablespace, 15-93

defining for the database, 13-25

disabling autoextend, 9-38

dropping, 9-38

enabling autoextend, 7-42, 9-38

extending automatically, 7-42

renaming, 9-39

resizing, 9-38

reusing, 7-41

size of, 7-41

specifying, 7-39, 15-94

taking offline, 9-38

TEMPORARY clause

of ALTER TABLESPACE, 11-108

of CREATE TABLESPACE, 15-87

temporary tables

creating, 15-7, 15-24

session-specific, 15-24

transaction-specific, 15-24

TEMPORARY TABLESPACE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

temporary tablespaces

creating, 15-93

default, 9-50

specifying extent management during database creation, 13-25

specifying extent management individually, 15-94

specifying for a user, 16-36

SQL examples, 15-95

TEST clause

of ALTER DATABASE ... RECOVER, 9-30

text

date and number formats, 2-62

in SQL syntax, 2-54

properties of CHAR and VARCHAR2 datatypes, 2-55

syntax of, 2-54

TH datetime format element suffix, 2-76

THREAD initialization parameter

setting with ALTER SYSTEM, 10-115

throughput

optimizing, 2-94

THSP datetime format element suffix, 2-76

TIME datatype

DB2, 2-37

SQL/DS, 2-37

time zone

determining for session, 6-139

formatting, 2-72

setting for the database, 13-33

time zones

converting data to particular, 4-9

TIME_ZONE session parameter, 10-16

TIMED_OS_STATISTICS initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-115

TIMED_STATISTICS initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-116

timestamp

converting to local time zone, 4-9

TIMESTAMP datatype, 2-21

DB2, 2-37

SQL/DS, 2-37

TIMESTAMP WITH LOCAL TIME ZONE datatype, 2-22

TIMESTAMP WITH TIME ZONE datatype, 2-21

TM number format element, 2-65

TO SAVEPOINT clause

of ROLLBACK, 17-101

TO_CHAR

datetime conversion function, 6-168

number conversion function, 6-170

TO_CHAR (character) function, 6-167

TO_CHAR function, 2-64, 2-68, 2-76

TO_CLOB function, 6-172

TO_DATE function, 2-68, 2-74, 2-77, 6-172

TO_DSINTERVAL function, 6-174

TO_LOB function, 6-175

TO_MULTI_BYTE function, 6-176

TO_NCHAR (character) function, 6-177

TO_NCHAR (datetime) function, 6-178

TO_NCHAR (number) function, 6-179

TO_NCLOB function, 6-180

TO_NUMBER function, 2-64, 6-180

TO_SINGLE_BYTE function, 6-181

TO_TIMESTAMP function, 6-182

TO_TIMESTAMP_TZ function

SQL functions

TO_TIMESTAMP_TZ, 6-183

TO_YMINTERVAL function, 6-185

top-N queries, 2-89

TRACE_ENABLED initialization parameter

setting with ALTER SYSTEM, 10-116

TRACEFILE_IDENTIFIER initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-116

transaction control statements, 9-4

PL/SQL support of, 9-4

TRANSACTION_AUDITING initialization parameter

setting with ALTER SYSTEM, 10-117

transactions

allowing to complete, 10-28

assigning

rollback segment to, 18-50

automatically committing, 12-72

changes, making permanent, 12-72

commenting on, 12-73

distributed, forcing, 10-3

ending, 12-72

implicit commit of, 9-2, 9-4, 9-5

in-doubt

committing, 12-72

forcing, 12-73

resolving, 18-52

isolation level, 18-50

locks, releasing, 12-72

naming, 18-52

read-only, 18-50

read/write, 18-50

rolling back, 10-29, 14-80, 17-100

to a savepoint, 17-101

savepoints for, 18-2

TRANSACTIONS initialization parameter

setting with ALTER SYSTEM, 10-117

TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameter

setting with ALTER SYSTEM, 10-117

TRANSLATE ... USING function, 6-187

TRANSLATE function, 6-185

TREAT function, 6-188

triggers

AFTER, 15-99

BEFORE, 15-99

compiling, 12-2, 12-3

creating, 15-96

multiple, 15-100

database

altering, 12-3

dropping, 17-13, 17-20

disabling, 11-88, 12-2, 12-3

enabling, 11-88, 12-2, 12-3, 15-96

executing

with a PL/SQL block, 15-106

with an external procedure, 15-107

granting

system privileges on, 17-42

INSTEAD OF, 15-100

dropping, 16-42

on database events, 15-103

on DDL events, 15-102

on DML operations, 15-98, 15-100

on views, 15-100

order of firing, 15-100

re-creating, 15-98

removing from the database, 17-13

renaming, 12-3

restrictions on, 15-106

row values

old and new, 15-105

row, specifying, 15-105

SQL examples, 15-107

statement, 15-105

TRIM function, 6-190

TRUNC function

date function, 6-192

format models, 6-218

number function, 6-191

TRUNCATE PARTITION clause

of ALTER TABLE, 11-73

TRUNCATE statement, 18-54

TRUNCATE SUBPARTITION clause

of ALTER TABLE, 11-73

TRUST attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

type constructor expressions, 4-13

type methods

return type of, 16-14

types. See object types or datatypes

TZ_OFFSET function, 6-192

TZD datetime format element, 2-70

TZH datetime format element, 2-70

TZM datetime format element, 2-70

TZR datetime format element, 2-70

---

U

U number format element, 2-65

UID function, 6-193

unary operators, 3-2

UNDER ANY TABLE system privilege, 17-43

UNDER ANY VIEW system privilege, 17-44

UNDER clause

of CREATE VIEW, 16-46

UNDER object privilege, 17-47

on a type, 17-50

on a view, 17-49

UNDER_PATH condition, 5-20

undo

rollback, 9-138, 13-32

system managed, 9-138, 13-32

UNDO tablespace clause

of CREATE DATABASE, 13-32

of CREATE TABLESPACE, 15-83

undo tablespaces

creating, 13-32, 15-83

dropping, 17-11

modifying, 11-103

UNDO_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 10-118

UNDO_RETENTION initialization parameter

setting with ALTER SYSTEM, 10-118

UNDO_SUPPRESS_ERRORS initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-119

UNDO_TABLESPACE initialization parameter

setting with ALTER SYSTEM, 10-119

UNIFORM clause

of CREATE TABLESPACE, 15-88

UNION ALL set operator, 3-6, 18-24

UNION set operator, 3-6, 18-24

UNIQUE clause

of CREATE INDEX, 13-69

of CREATE TABLE, 15-27

of SELECT, 18-11

unique constraints

enabling, 15-59

index on, 15-60

unique indexes, 13-69

unique queries, 18-11

UNISTR function, 6-194

universal rowids. See urowids

UNLIMITED TABLESPACE system privilege, 17-42

unnesting collections, 18-16

examples, 18-39

unnesting subqueries, 8-14

UNQUIESCE clause

of ALTER SYSTEM, 10-31

UNRECOVERABLE, 9-74, 15-30

See also NOLOGGING clause

unsorted indexes, 13-75

UNUSABLE clause

of ALTER INDEX, 9-79

UNUSABLE LOCAL INDEXES clause

of ALTER MATERIALIZED VIEW, 9-102

of ALTER TABLE, 11-83

UPDATE ANY TABLE system privilege, 17-42

UPDATE BLOCK REFERENCES clause

of ALTER INDEX, 9-81, 9-82

of ALTER TABLE, 11-40

UPDATE GLOBAL INDEXES clause

of ALTER TABLE, 11-84

UPDATE object privilege, 17-47

on a table, 17-48

on a view, 17-49

update operations

collecting supplemental log data for, 9-42

UPDATE SET clause

of MERGE, 17-78

UPDATE statement, 18-59

triggers on, 15-100

updates

and simultaneous insert, 17-78

using MERGE, 17-78, 17-79

UPDATEXML function, 6-194

UPGRADE clause

of ALTER TABLE, 11-36

upgrading

from release 7.3.4 to Oracle9i release 2, 9-26

UPPER function, 6-196

URLs

generating, 6-157

UROWID datatype, 2-35

urowids

and foreign tables, 2-35

and index-organized tables, 2-35

description of, 2-35

USE_CONCAT hint, 2-106

USE_INDIRECT_DATA_BUFFERS initialization parameter

setting with ALTER SYSTEM, 10-119

USE_MERGE hint, 2-107

USE_NL hint, 2-107

USE_PRIVATE_OUTLINES session parameter, 10-17

USE_STORED_OUTLINES initialization parameter

setting with ALTER SESSION, 10-120

USE_STORED_OUTLINES session parameter, 10-17, 10-120

USER function, 6-196

USER SYS clause

of CREATE DATABASE, 13-26

USER SYSTEM clause

of CREATE DATABASE, 13-26

USER_COL_COMMENTS data dictionary view, 12-69

USER_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-120

USER_TAB_COMMENTS data dictionary view, 12-69

user-defined aggregate functions, 13-58

user-defined functions, 6-219

name precedence of, 6-221

naming conventions, 6-221

restrictions on, 13-53

user-defined operators, 3-6

user-defined statistics

dropping, 16-77, 16-78, 16-91, 17-7, 17-15

user-defined types, 2-38

defining, 16-9

mapping to Java classes, 16-11

USERENV function, 6-197

users

allocating space for, 16-36

and database links, 13-37

assigning

default roles, 12-25

profiles, 16-36

authenticating to a remote server, 13-38

changing authentication, 12-26

changing global authentication, 12-24

creating, 16-32

default tablespaces, 16-36

denying access to tables and views, 17-74

external, 14-78, 16-34

global, 14-78, 16-35

granting

system privileges on, 17-43

local, 14-78, 16-34

locking accounts, 16-37

maximum concurrent, 10-69

password expiration of, 16-37

removing from the database, 17-20

SQL examples, 16-37

temporary tablespaces for, 16-36

USING BFILE clause

of CREATE JAVA, 13-98

USING BLOB clause

of CREATE JAVA, 13-98

USING clause

of ALTER INDEXTYPE, 9-88

of ASSOCIATE STATISTICS, 12-49, 12-50

of CREATE DATABASE LINK, 13-38

of CREATE INDEXTYPE, 13-92

of CREATE OPERATOR, 14-44

USING CLOB clause

of CREATE JAVA, 13-98

USING INDEX clause

of ALTER MATERIALIZED VIEW, 9-103

of ALTER TABLE, 11-30

of constraints, 7-22

of CREATE MATERIALIZED VIEW, 14-20

of CREATE TABLE, 15-60

USING NO INDEX clause

of CREATE MATERIALIZED VIEW, 14-20

USING ROLLBACK SEGMENT clause

of ALTER MATERIALIZED VIEW...REFRESH, 9-107

of CREATE MATERIALIZED VIEW, 14-23

UTC

extracting from a datetime value, 6-159

UTC offset

replacing with time zone region, 2-22

UTL_FILE_DIR initialization parameter

setting with ALTER SYSTEM, 10-120

UTLCHN.SQL script, 12-44

UTLEXPT1.SQL script, 11-81

UTLXPLAN.SQL script, 17-24

---

V

V number format element, 2-65

VALIDATE clause

of DROP TYPE, 17-16

VALIDATE REF UPDATE clause

of ANALYZE, 12-42

VALIDATE STRUCTURE clause

of ANALYZE, 12-42

validation

of clusters, 12-42

of database objects

offline, 12-44

of database objects, online, 12-44

of indexes, 12-42

of tables, 12-42

VALUE function, 6-199

VALUES clause

of CREATE INDEX, 7-24, 13-79, 15-62

of INSERT, 17-62

VALUES LESS THAN clause

of CREATE TABLE, 15-45

VAR_POP function, 6-199

VAR_SAMP function, 6-201

VARCHAR datatype, 2-12

DB2, 2-37

SQL/DS, 2-37

VARCHAR2 datatype, 2-11

converting to NUMBER, 2-64

VARGRAPHIC datatype

DB2, 2-37

SQL/DS, 2-37

variable expressions, 4-15

VARIANCE function, 6-203

VARRAY clause

of ALTER TABLE, 11-13

VARRAY column properties

of ALTER TABLE, 11-13, 11-44

of CREATE MATERIALIZED VIEW, 14-12

of CREATE TABLE, 15-14, 15-39

varrays, 2-39

changing returned value, 11-56

compared with nested tables, 2-48

comparison rules, 2-48

creating, 16-3, 16-8, 16-18

dropping the body of, 17-18

dropping the specification of, 17-15

modifying column properties, 11-15

storage characteristics, 11-44, 11-57, 15-39

storing out of line, 2-39

varying arrays. See varrays

view

join

and key-preserved tables, 16-48

view constraints

dropping, 17-23

views

base tables

adding rows, 17-54

changing

definition, 17-22

values in base tables, 18-59

creating

before base tables, 16-43

comments about, 12-69

multiple, 14-84

creating object subviews, 16-46

defining, 16-39

dropping constraints on, 12-32

granting

system privileges on, 17-43

modifying constraints on, 12-32

object, creating, 16-45

recompiling, 12-30

re-creating, 16-42

remote, accessing, 13-35

removing

from the database, 17-22

rows from the base table of, 16-55

renaming, 17-87

retrieving data from, 18-4

subquery of, 16-46

restricting, 16-49

synonyms for, 15-2

updatable, 16-47

with joins, making updatable, 16-48

XMLType, 16-48

XMLType, creating, 16-53

XMLType, querying, 16-48

VSIZE function, 6-204

---

W

W datetime format element, 2-70

WHEN clause

of CREATE TRIGGER, 15-106

WHEN MATCHED clause

of MERGE, 17-79

WHEN NOT MATCHED clause

of MERGE, 17-79

WHENEVER NOT SUCCESSFUL clause

of NOAUDIT, 17-85

WHENEVER SUCCESSFUL clause

of AUDIT sql_statements, 12-58

of NOAUDIT, 17-85

WHERE clause

of DELETE, 16-60

of SELECT, 8-4

of UPDATE, 18-67

WIDTH_BUCKET function, 6-205

WITH ADMIN OPTION clause

of GRANT, 17-33

WITH CHECK OPTION clause

of CREATE VIEW, 16-42, 16-49

of DELETE, 16-58

of INSERT, 17-62

of SELECT, 18-7

of UPDATE, 18-63

WITH GRANT OPTION clause

of GRANT, 17-35

WITH HIERARCHY OPTION

of GRANT, 17-35

WITH INDEX CONTEXT clause

of CREATE OPERATOR, 14-44

WITH OBJECT ID clause

of CREATE MATERIALIZED VIEW LOG, 14-37

WITH OBJECT IDENTIFIER clause

of CREATE VIEW, 16-45

WITH OBJECT OID. See WITH OBJECT IDENTIFIER.

WITH PRIMARY KEY clause

of ALTER MATERIALIZED VIEW, 9-106

of CREATE MATERIALIZED VIEW LOG, 14-37

of CREATE MATERIALIZED VIEW...REFRESH, 14-20

WITH query_name clause

of SELECT, 18-10

WITH READ ONLY clause

of CREATE VIEW, 16-42, 16-49

of DELETE, 16-58

of INSERT, 17-62

of SELECT, 18-7

of UPDATE, 18-63

WITH ROWID clause

of column ref constraints, 7-17

of CREATE MATERIALIZED VIEW LOG, 14-37

of CREATE MATERIALIZED VIEW...REFRESH, 14-20

WITH SEQUENCE clause

of CREATE MATERIALIZED VIEW LOG, 14-37

WNDS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

WNPS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

WORKAREA_SIZE_POLICY initialization parameter

setting with ALTER SESSION, 10-11

setting with ALTER SYSTEM, 10-121

WRITE object privilege

on a directory, 17-50

WW datetime format element, 2-70

---

X

X datetime format element, 2-70

X number format element, 2-65

XML data

storage of, 15-43

XML database repository

SQL access to, 5-13, 5-20

XML documents

producing from XML fragments, 6-161

retrieving from the database, 6-157

XML format models, 2-79

XML fragments, 6-61

XMLDATA pseudocolumn, 2-90

XMLGenFormatType object, 2-79

XMLType storage clause

of CREATE TABLE, 15-43

XMLType tables

creating, 15-64, 15-71

creating index on, 13-85

XMLType views, 16-48

querying, 16-48

---

Y

Y datetime format element, 2-70

Y,YYY datetime format element, 2-70

YEAR datetime format element, 2-70

YY datetime format element, 2-70

YYY datetime format element, 2-70

YYYY datetime format element, 2-70