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

- (dash)

datetime format element, 2-69

$ (dollar sign)

number format element, 2-64

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

, (comma)

datetime format element, 2-69

number format element, 2-64

: (colon)

datetime format element, 2-69

. (period)

datetime format element, 2-69

number format element, 2-64

; (semicolon)

datetime format element, 2-69

/ (slash)

datetime format element, 2-69

Numerics

0 (zero)

number format element, 2-64

20th century, 2-73

21st century, 2-73

7.3.4 release

upgrading to Oracle9i release 2, 9-24

9 (nine)

number format element, 2-64

A

ABORT LOGICAL STANDBY clause

of ALTER DATABASE, 9-47

ABS function, 6-17

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

ACTIVATE STANDBY DATABASE clause

of ALTER DATABASE, 9-43

ACTIVE_INSTANCE_COUNT initialization parameter

setting with ALTER SYSTEM, 10-33

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

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

ADD clause

of ALTER DIMENSION, 9-58

of ALTER INDEXTYPE, 9-86

of ALTER TABLE, 11-41

of ALTER VIEW, 12-32

ADD DATAFILE clause

of ALTER TABLESPACE, 11-104

ADD LOG GROUP clause

of ALTER TABLE, 11-34

ADD LOGFILE clause

of ALTER DATABASE, 9-19

ADD LOGFILE GROUP clause

of ALTER DATABASE, 9-39

ADD LOGFILE MEMBER clause

of ALTER DATABASE, 9-19, 9-39

ADD LOGFILE THREAD clause

of ALTER DATABASE, 9-38

ADD OVERFLOW clause

of ALTER TABLE, 11-40

ADD PARTITION clause

of ALTER TABLE, 11-69, 11-71, 11-72

ADD PRIMARY KEY clause

of ALTER MATERIALIZED VIEW LOG, 9-114

ADD ROWID clause

of ALTER MATERIALIZED VIEW, 9-114

of ALTER MATERIALIZED VIEW LOG, 9-114

ADD SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 9-40

ADD TEMPFILE clause

of ALTER TABLESPACE, 11-104

ADD VALUES clause

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

ADD_MONTHS function, 6-18

adding a constraint, 11-58

ADMINISTER DATABASE TRIGGER system privilege, 17-42

ADVISE clause

of ALTER SESSION, 10-3

AFTER clause

of CREATE TRIGGER, 15-98

AFTER triggers, 15-98

AGENT clause

of CREATE LIBRARY, 14-3

aggregate functions, 6-8

user-defined, creating, 13-61

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

ALL PRIVILEGES shortcut

of AUDIT, 12-57

ALL shortcut

of AUDIT, 12-57

ALL_COL_COMMENTS data dictionary view, 12-72

ALL_ROWS hint, 2-94

ALL_TAB_COMMENTS data dictionary view, 12-72

ALLOCATE EXTENT clause

of ALTER CLUSTER, 9-7, 9-8

of ALTER INDEX, 9-64, 9-70

of ALTER MATERIALIZED VIEW, 9-96

of ALTER TABLE, 11-35

ALLOW CORRUPTION clause

of ALTER DATABASE ... RECOVER, 9-28

ALTER ANY CLUSTER system privilege, 17-36

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

ALTER ANY OUTLINE system privilege, 17-39

ALTER ANY PROCEDURE system privilege, 17-39

ALTER ANY ROLE system privilege, 17-40

ALTER ANY SEQUENCE system privilege, 17-40

ALTER ANY TABLE system privilege, 17-41

ALTER ANY TRIGGER system privilege, 17-42

ALTER ANY TYPE system privilege, 17-42

ALTER CLUSTER statement, 9-6

ALTER DATABASE statement, 9-11

ALTER DATABASE system privilege, 17-37

ALTER DIMENSION statement, 9-56

ALTER FUNCTION statement, 9-59

ALTER INDEX statement, 9-62

ALTER INDEXTYPE statement, 9-85

ALTER JAVA CLASS statement, 9-87

ALTER JAVA SOURCE statement, 9-87

ALTER MATERIALIZED VIEW LOG statement, 9-110

ALTER MATERIALIZED VIEW statement, 9-90

ALTER object privilege, 17-46

on a sequence, 17-48

on a table, 17-47

ALTER OPERATOR statement, 9-117

ALTER OUTLINE statement, 9-118

ALTER PACKAGE statement, 9-120

ALTER PROCEDURE statement, 9-124

ALTER PROFILE statement, 9-127

ALTER PROFILE system privilege, 17-40

ALTER RESOURCE COST statement, 9-131

ALTER RESOURCE COST system privilege, 17-40

ALTER ROLE statement, 9-134

ALTER ROLLBACK SEGMENT statement, 9-136

ALTER ROLLBACK SEGMENT system privilege, 17-40

ALTER SEQUENCE statement, 9-140

ALTER SESSION statement, 10-2

ALTER SESSION system privilege, 17-40

ALTER SNAPSHOT LOG. See ALTER MATERIALIZED VIEW LOG

ALTER SNAPSHOT. See ALTER MATERIALIZED VIEW

ALTER statements

triggers on, 15-101

ALTER SYSTEM statement, 10-20

ALTER SYSTEM system privilege, 17-37

ALTER TABLE statement, 11-2

ALTER TABLESPACE statement, 11-102

ALTER TABLESPACE system privilege, 17-42

ALTER TRIGGER statement, 12-2

ALTER TYPE statement, 12-6

ALTER USER statement, 12-22

ALTER USER system privilege, 17-43

ALTER VIEW statement, 12-31

alter_external_table_clause

of ALTER TABLE, 11-16

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

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

American National Standards Institute (ANSI), B-1

datatypes, 2-36

conversion to Oracle datatypes, 2-36

datatypes, implicit conversion, 2-36

standards, xix, 1-2, B-2

supported datatypes, 2-5

analytic functions, 6-10

AVG, 6-22

CORR, 6-37

COUNT, 6-40

COVAR_POP, 6-42

COVAR_SAMP, 6-44

CUME_DIST, 6-47

DENSE_RANK, 6-55

FIRST, 6-67

FIRST_VALUE, 6-69

inverse distribution, 6-118, 6-121

LAG, 6-80

LAST, 6-81

LAST_VALUE, 6-84

LEAD, 6-86

linear regression, 6-129

MAX, 6-95

MIN, 6-97

NTILE, 6-109

OVER clause, 6-10, 6-12

PERCENT_CONT, 6-118

PERCENT_DISC, 6-121

PERCENT_RANK, 6-116

RANK, 6-123

RATIO_TO_REPORT, 6-125

ROW_NUMBER, 6-139

STDDEV, 6-148

STDDEV_POP, 6-149

STDDEV_SAMP, 6-151

SUM, 6-154

syntax, 6-10

user-defined, 6-12, 13-61

VAR_POP, 6-202

VAR_SAMP, 6-204

VARIANCE, 6-206

ANALYZE ANY system privilege, 17-43

ANALYZE CLUSTER statement, 12-34

ANALYZE INDEX statement, 12-34

ANALYZE TABLE statement, 12-34

ANCILLARY TO clause

of CREATE OPERATOR, 14-46

AND condition, 5-8

AND DATAFILES clause

of DROP TABLESPACE, 17-12

AND_EQUAL hint, 2-94

ANSI. See American National Standards Institute (ANSI)

ANY operator, 5-5

APPEND hint, 2-94

application servers

allowing connection as user, 12-26

applications

allowing connection as user, 12-26

securing, 13-12

validating, 13-12

AQ_ADMINISTRATOR_ROLE role, 17-45

AQ_TM_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-34

AQ_USER_ROLE role, 17-45

ARCHIVE LOG clause

of ALTER SYSTEM, 10-23

archive logs

applying to standby database, 9-31

archive mode

specifying, 13-29

ARCHIVE_LAG_TARGET initialization parameter

setting with ALTER SYSTEM, 10-34

archived redo logs

location, 9-26

storage locations, 10-69

ARCHIVELOG clause

of ALTER DATABASE, 9-19, 9-37

of CREATE CONTROLFILE, 13-20

of CREATE DATABASE, 13-29

arguments

of operators, 3-1

arithmetic

operators, 3-3

with DATE values, 2-20

AS clause

of CREATE JAVA, 13-103

AS EXTERNAL clause

of CREATE FUNCTION, 14-69

of CREATE TYPE BODY, 16-30

AS OBJECT clause

of CREATE TYPE, 16-9

AS subquery clause

of CREATE MATERIALIZED VIEW, 14-26

of CREATE TABLE, 15-61

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

ASCII

character set, 2-46

ASCII function, 6-18

ASCIISTR function, 6-19

ASIN function, 6-20

ASSOCIATE STATISTICS statement, 12-50

ATAN function, 6-21

ATAN2 function, 6-21

ATTRIBUTE clause

of ALTER DIMENSION, 9-57

of CREATE DIMENSION, 13-44, 13-47

attributes

adding to a dimension, 9-58

dropping from a dimension, 9-58

maximum number of in object type, 15-24

of dimensions, defining, 13-47

of user-defined types

mapping to Java fields, 16-12

AUDIT ANY system privilege, 17-43

AUDIT SYSTEM system privilege, 17-37

AUDIT_FILE_DEST initialization parameter

setting with ALTER SYSTEM, 10-35

AUDIT_SYS_OPERATIONS initialization parameter

setting with ALTER SYSTEM, 10-35

AUDIT_TRAIL initialization parameter

setting with ALTER SYSTEM, 10-35

auditing

options

for database objects, 12-60

for SQL statements, 12-62

policies

value-based, 12-54

SQL statements, 12-55, 12-60

by a proxy, 12-55

by a user, 12-55

SQL statements, on a directory, 12-56

SQL statements, on a schema, 12-56

SQL statements, stopping, 17-81

system privileges, 12-55

users connected to SYS schema, 10-35

AUTHENTICATED BY clause

of CREATE DATABASE LINK, 13-40

AUTHENTICATED clause

of ALTER USER, 12-27

AUTHID CURRENT_USER clause

of ALTER JAVA, 9-88

of CREATE FUNCTION, 13-58

of CREATE JAVA, 13-99, 13-101

of CREATE PACKAGE, 14-54

of CREATE PROCEDURE, 14-68

of CREATE TYPE, 12-14, 16-10

AUTHID DEFINER clause

of ALTER JAVA, 9-88

of CREATE FUNCTION, 13-58

of CREATE JAVA, 13-99, 13-101

of CREATE PACKAGE, 14-54

of CREATE PROCEDURE, 14-68

of CREATE TYPE, 12-14, 16-10

AUTOALLOCATE clause

of CREATE TABLESPACE, 15-87

AUTOEXTEND clause

of ALTER DATABASE, 9-18

of CREATE DATABASE, 13-26

of CREATE TEMPORARY TABLESPACE, 15-93

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

Automatic Undo Management mode, 9-136, 13-33

AVG function, 6-22

B

number format element, 2-64

BACKGROUND_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 10-35

BACKGROUND_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-36

BACKUP ANY TABLE system privilege, 17-41

BACKUP CONTROLFILE clause

of ALTER DATABASE, 9-20, 9-42

BACKUP_TAPE_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 10-36

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

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

BECOME USER system privilege, 17-43

BEFORE clause

of CREATE TRIGGER, 15-98

BEFORE triggers, 15-98

BEGIN BACKUP clause

of ALTER TABLESPACE, 11-107

BFILE

datatype, 2-32

locators, 2-32

BFILENAME function, 6-23

BIN_TO_NUM function, 6-25

binary large objects. See BLOB

binary operators, 3-2

BINDING clause

of CREATE OPERATOR, 14-44, 14-46

bit vectors

converting to numbers, 6-25

BITAND function, 6-25

BITMAP clause

of CREATE INDEX, 13-72

bitmap indexes, 13-72

creating join indexes, 13-67

BITMAP_MERGE_AREA_SIZE initialization parameter

setting with ALTER SYSTEM, 10-36

blank padding

specifying in format models, 2-75

suppressing, 2-76

BLANK_TRIMMING initialization parameter

setting with ALTER SYSTEM, 10-37

blank-padded comparison semantics, 2-46

BLOB datatype, 2-33

transactional support, 2-33

BLOCKSIZE clause

of CREATE TABLESPACE, 15-84

BODY clause

of ALTER PACKAGE, 9-121

BUFFER_POOL parameter

of STORAGE clause, 7-63

BUFFER_POOL_KEEP initialization parameter

setting with ALTER SYSTEM, 10-37

BUFFER_POOL_RECYCLE initialization parameter

setting with ALTER SYSTEM, 10-38

BUILD DEFERRED clause

of CREATE MATERIALIZED VIEW, 14-20

BUILD IMMEDIATE clause

of CREATE MATERIALIZED VIEW, 14-20

BY ACCESS clause

of AUDIT, 12-59

BY proxy clause

of AUDIT, 12-58

BY SESSION clause

of AUDIT, 12-59

BY user clause

of AUDIT, 12-57

BYTE character semantics, 2-10, 2-11

BYTE length semantics, 11-51

C

number format element, 2-64

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

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-35, 15-52

of CREATE CLUSTER, 13-9

of CREATE MATERIALIZED VIEW, 14-19

of CREATE MATERIALIZED VIEW LOG, 14-38

CACHE hint, 2-94

CACHE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-140

of CREATE SEQUENCE, 14-92

CACHE READS clause

of ALTER TABLE, 11-45

of CREATE TABLE, 15-53

cached cursors

execution plan for, 17-24

CALL clause

of CREATE TRIGGER, 15-106

CALL procedure statement

of CREATE TRIGGER, 15-106

call spec. See call specifications

call specifications

in procedures, 14-64

of CREATE PROCEDURE, 14-68

of CREATE TYPE, 16-15

of CREATE TYPE BODY, 16-29

CALL statement, 12-68

calls

limiting CPU time for, 14-74

limiting data blocks read, 14-74

Cartesian products, 8-11

CASCADE clause

of CREATE TABLE, 15-60

of DROP PROFILE, 16-95

of DROP USER, 17-20

CASCADE CONSTRAINTS clause

of DROP CLUSTER, 16-68

of DROP TABLE, 17-9

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

MULTISET parameter, 6-27

CATSEARCH condition, 5-2

CC datetime format element, 2-69

CEIL function, 6-30

chained rows

listing, 12-45

of clusters, 12-39

CHANGE CATEGORY clause

of ALTER OUTLINE, 9-119

CHAR character semantics, 2-10, 2-11

CHAR datatype, 2-10

ANSI, 2-36

converting to VARCHAR2, 2-63

CHAR length semantics, 11-51

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

character literal. See text

CHARACTER SET parameter

of ALTER DATABASE, 9-47

of CREATE CONTROLFILE, 13-21

of CREATE DATABASE, 13-30

character sets

changing, 9-47

common, 2-46

database, specifying, 13-30

multibyte characters, 2-112

specifying for database, 13-30

character strings

comparison rules, 2-45

exact matching, 2-76

fixed-length, 2-10

national character set, 2-10

variable length, 2-11

variable-length, 2-14

zero-length, 2-10

CHARACTER VARYING datatype

ANSI, 2-36

characters

single, comparison rules, 2-46

CHARTOROWID function, 6-30

CHECK clause

of constraints, 7-15

of CREATE TABLE, 15-26

check constraints, 7-15

CHECK DATAFILES clause

of ALTER SYSTEM, 10-26

CHECKPOINT clause

of ALTER SYSTEM, 10-25

checkpoints

forcing, 10-25

CHOOSE hint, 2-94

CHR function, 6-31

CHUNK clause

of ALTER TABLE, 11-46

of CREATE TABLE, 15-38

CIRCUITS initialization parameter

setting with ALTER SYSTEM, 10-39

CLEAR LOGFILE clause

of ALTER DATABASE, 9-19, 9-41

CLOB datatype, 2-33

transactional support, 2-33

clone databases

mounting, 9-23

CLOSE DATABASE LINK clause

of ALTER SESSION, 10-3

CLUSTER clause

of ANALYZE, 12-39

of CREATE INDEX, 13-73

of CREATE TABLE, 15-35

of TRUNCATE, 18-57

CLUSTER hint, 2-95

CLUSTER_DATABASE initialization parameter

setting with ALTER SYSTEM, 10-39

CLUSTER_DATABASE_INSTANCES initialization parameter

setting with ALTER SYSTEM, 10-39

CLUSTER_INTERCONNECTS initialization parameter

setting with ALTER SYSTEM, 10-40

clusters

assigning tables to, 15-35

caching retrieved blocks, 13-9

cluster indexes, 13-73

collecting statistics on, 12-39

creating, 13-2

deallocating unused extents, 9-7

degree of parallelism

changing, 9-7, 9-9

when creating, 13-8

dropping tables, 16-68

extents, allocating, 9-7, 9-8

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

migrated and chained rows in, 12-39, 12-45

modifying, 9-6

physical attributes

changing, 9-7

specifying, 13-5

releasing unused space, 9-9

removing from the database, 16-67

SQL examples, 16-68

storage attributes

changing, 9-7

storage characteristics, changing, 9-8

tablespace in which created, 13-6

validating structure, 12-43

COALESCE clause

for partitions, 11-72

of ALTER INDEX, 9-78

of ALTER TABLE, 11-41, 11-64

of ALTER TABLESPACE, 11-109

COALESCE function, 6-33

as a variety of CASE expression, 6-33

COALESCE SUBPARTITION clause

of ALTER TABLE, 11-64

collection types

multilevel, 15-42

collections

inserting rows into, 17-60

modifying, 11-57

modifying retrieval method, 11-10

nested tables, 2-39

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

unnesting, 18-17

examples, 18-40

varrays, 2-39

collection-typed values

converting to datatypes, 6-27

column constraints

restrictions on, 11-51

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

basing an index on, 13-74

collecting statistics on, 12-40

comments on, 12-73

creating comments about, 12-72

defining, 15-7

dropping from a table, 11-52

LOB

storage attributes, 11-45

maximum number of, 15-24

modifying existing, 11-49

parent-child relationships between, 13-43

properties, altering, 11-11, 11-43

qualifying names of, 8-2

REF

describing, 7-16

renaming, 11-56

restricting values for, 7-5

specifying as primary key, 7-13

specifying constraints on, 15-26

specifying default values, 15-25

storage properties, 15-36

substitutable, identifying type, 6-164

COLUMNS clause

of ASSOCIATE STATISTICS, 12-50, 12-52

COMMENT ANY TABLE system privilege, 17-43

COMMENT clause

of COMMIT, 12-76

COMMENT statement, 12-72

comments, 2-90

adding to objects, 12-72

associating with a transaction, 12-76

dropping from objects, 12-72

in SQL statements, 2-90

on indextypes, 12-74

on operators, 12-73

on schema objects, 2-91

on table columns, 12-73

on tables, 12-73

removing from the data dictionary, 12-72

specifying, 2-90

viewing, 12-72

commit

automatic, 12-75

COMMIT IN PROCEDURE clause

of ALTER SESSION, 10-3

COMMIT statement, 12-75

COMMIT TO SWITCHOVER clause

of ALTER DATABASE, 9-45

COMMIT_POINT_STRENGTH initialization parameter

setting with ALTER SYSTEM, 10-40

comparison conditions, 5-4

comparison functions

MAP, 16-30

ORDER, 16-30

comparison semantics

blank-padded, 2-46

nonpadded, 2-45

of character strings, 2-45

COMPATIBLE initialization parameter

setting with ALTER SYSTEM, 10-40

COMPILE clause

of ALTER DIMENSION, 9-58

of ALTER FUNCTION, 9-60

of ALTER JAVA SOURCE, 9-88

of ALTER MATERIALIZED VIEW, 9-106

of ALTER PACKAGE, 9-121

of ALTER PROCEDURE, 9-125

of ALTER TRIGGER, 12-3

of ALTER TYPE, 12-10

of ALTER VIEW, 12-33

of CREATE JAVA, 13-100

compiler switches

dropping and preserving, 9-60, 9-122, 9-125, 12-4, 12-11

COMPOSE function, 6-34

composite foreign keys, 7-14

composite partitioning

range-list, 11-63, 15-51

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

composite primary keys, 7-13

COMPOSITE_LIMIT parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

compound conditions, 5-21

compound expressions, 4-5

COMPRESS clause

of ALTER INDEX ... REBUILD, 9-75

of CREATE TABLE, 15-32

compression

of index keys, 9-65

COMPUTE STATISTICS clause

of ALTER INDEX... REBUILD, 9-75

of ANALYZE, 12-39

of CREATE INDEX, 13-80

CONCAT function, 6-35

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

CONNECT THROUGH clause

of ALTER USER, 12-27

CONNECT TO clause

of CREATE DATABASE LINK, 13-39

CONNECT_TIME parameter

of ALTER PROFILE, 9-128

of ALTER RESOURCE COST, 9-132

CONSIDER FRESH clause

of ALTER MATERIALIZED VIEW, 9-106

constant values. See literals

CONSTRAINT(S) session parameter, 10-10

constraints

adding to a table, 11-58

altering, 11-11

check, 7-15

checking

at end of transaction, 7-19

at start of transaction, 7-20

at the end of each DML statement, 7-19

column REF, 7-16

deferrable, 7-19, 18-45

enforcing, 10-10

defining, 7-5, 15-7

for a table, 15-26

on a column, 15-26

disabling, 15-55

cascading, 15-60

disabling after table creation, 11-89

disabling during table creation, 15-22

dropping, 11-11, 11-59, 17-12

enabling, 15-55, 15-58

enabling after table creation, 11-89

enabling during table creation, 15-22

foreign key, 7-14

modifying existing, 11-58

on views

dropping, 12-33, 17-23

modifying, 12-33

primary key, 7-13

attributes of index, 7-23

enabling, 15-57

referential integrity, 7-14

renaming, 11-59

restrictions, 7-11

setting state for a transaction, 18-45

storing rows in violation, 11-82

table REF, 7-16

unique

attributes of index, 7-23

enabling, 15-57

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

control files

allowing reuse, 13-18, 13-27

backing up, 9-42

force logging mode, 13-20

re-creating, 13-15

CONTROL_FILE_RECORD_KEEP_TIME initialization parameter

setting with ALTER SYSTEM, 10-41

CONTROL_FILES initialization parameter

setting with ALTER SYSTEM, 10-41

controlfile clauses

of ALTER DATABASE, 9-20

CONTROLFILE REUSE clause

of CREATE DATABASE, 13-27

controlfiles

standby, creating, 9-42

conversion

functions, 6-6

rules, string to date, 2-78

CONVERT clause

of ALTER DATABASE, 9-50

CONVERT function, 6-36

CORE_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-42

CORR function, 6-37

correlated subqueries, 8-14

correlation names

for base tables of indexes, 13-74

in DELETE, 16-60

in SELECT, 18-17

COS function, 6-39

COSH function, 6-40

COUNT function, 6-40

COVAR_POP function, 6-42

COVAR_SAMP function, 6-44

CPU_COUNT initialization parameter

setting with ALTER SYSTEM, 10-42

CPU_PER_CALL parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-74

CPU_PER_SESSION parameter

of ALTER PROFILE, 9-128

of ALTER RESOURCE COST, 9-131

of CREATE PROFILE, 14-74

CREATE ANY CLUSTER system privilege, 17-36

CREATE ANY CONTEXT system privilege, 17-36

CREATE ANY DIMENSION system privilege, 17-37

CREATE ANY DIRECTORY system privilege, 17-37

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

CREATE ANY OPERATOR system privilege, 17-39

CREATE ANY OUTLINE system privilege, 17-39

CREATE ANY PROCEDURE system privilege, 17-39

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

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

CREATE DATABASE LINK system privilege, 17-37

CREATE DATABASE statement, 13-23

CREATE DATAFILE clause

of ALTER DATABASE, 9-17, 9-34

CREATE DIMENSION

system privilege, 17-37

CREATE DIMENSION statement, 13-43

CREATE DIRECTORY statement, 13-49

CREATE FUNCTION statement, 13-52

CREATE INDEX

statement, 13-65

CREATE INDEXTYPE

statement, 13-95

CREATE INDEXTYPE system privilege, 17-37

CREATE JAVA statement, 13-98

CREATE LIBRARY statement, 14-2

CREATE LIBRARY system privilege, 17-38

CREATE MATERIALIZED VIEW LOG statement, 14-34

CREATE MATERIALIZED VIEW statement, 14-5

CREATE MATERIALIZED VIEW system privilege, 17-38

CREATE OPERATOR statement, 14-44

CREATE OPERATOR system privilege, 17-39

CREATE OUTLINE statement, 14-48

CREATE PACKAGE BODY statement, 14-57

CREATE PACKAGE statement, 14-52

CREATE PFILE statement, 14-62

CREATE PROCEDURE statement, 14-64

CREATE PROCEDURE system privilege, 17-39

CREATE PROFILE statement, 14-71

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

CREATE ROLE system privilege, 17-40

CREATE ROLLBACK SEGMENT statement, 14-82

CREATE ROLLBACK SEGMENT system privilege, 17-40

CREATE SCHEMA statement, 14-86

CREATE SEQUENCE statement, 14-89

CREATE SEQUENCE system privilege, 17-40

CREATE SESSION system privilege, 17-40

CREATE SPFILE statement, 14-94

CREATE STANDBY CONTROLFILE clause

of ALTER DATABASE, 9-20, 9-42

CREATE statements

triggers on, 15-101

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

CREATE TRIGGER statement, 15-95

CREATE TRIGGER system privilege, 17-42

CREATE TYPE BODY statement, 16-25

CREATE TYPE statement, 16-3

CREATE TYPE system privilege, 17-42

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

CREATE_STORED_OUTLINES initialization parameter

setting with ALTER SYSTEM, 10-43

CREATE_STORED_OUTLINES session parameter, 10-11

cross joins, 18-19

CUBE clause

of SELECT statements, 18-22

CUME_DIST function, 6-47

cumulative distributions, 6-47

currency symbol

ISO, 2-65

local, 2-65

setting for a session, 10-8

union, 2-66

CURRENT_DATE function, 6-49

CURRENT_SCHEMA session parameter, 10-11

CURRENT_TIMESTAMP function, 6-50

CURRENT_USER clause

of CREATE DATABASE LINKI, 13-39

CURRVAL pseudocolumn, 2-82, 14-89

CURSOR expressions, 4-7

CURSOR_SHARING initialization parameter

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

CURSOR_SPACE_FOR_TIME initialization parameter

setting with ALTER SYSTEM, 10-44

cursors

cached, 17-24

CustomDatum Java storage format, 16-11

CYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-140

of CREATE SEQUENCE, 14-92

D

number format element, 2-64

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

independence, 15-2

integrity checking on input, 2-13

retrieving, 8-2

specifying as temporary, 15-23

undo

storing, 14-82

data conversion, 2-48

between character datatypes, 2-51

implicit

disadvantages, 2-49

implicit versus explicit, 2-49

when performed implicitly, 2-49, 2-51

when specified explicitly, 2-52

data definition language (DDL)

events and triggers, 15-101

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

data manipulation language (DML)

allowing during indexing, 9-72

operations

and triggers, 15-100

during index creation, 13-80

during index rebuild, 11-87

parallelizing, 15-53

restricting operations, 10-29

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

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

statements, 9-3

PL/SQL support, 9-3

triggers

and LOB columns and attributes, 2-31

data object number

in extended rowids, 2-34

data segment compression, 9-99, 11-33, 14-18, 15-29

database links, 8-16

closing, 10-3

creating, 2-118, 13-37

creating synonyms with, 15-5

current user, 13-39

granting system privileges on, 17-37

naming, 2-118

public, 13-38

dropping, 16-70

referring to, 2-119

removing from the database, 16-70

shared, 13-38

syntax, 2-118

username and password, 2-119

database objects

dropping, 17-20

nonschema, 2-107

schema, 2-106

database triggers. See triggers

databases

accounts

creating, 16-32

allowing generation of redo logs, 9-23

allowing reuse of control files, 13-27

allowing unlimited resources to users, 14-73

archive mode

specifying, 13-29

blocks

specifying size, 15-84

cache

buffers in, 10-44

cancel-based recovery, 9-27

terminating, 9-29

change-based recovery, 9-27

changing character set, 9-47

changing characteristics, 13-15

changing global name, 9-51

changing name, 13-15, 13-18

character set, specifying, 13-30

character sets

changing, 9-47

specifying, 13-30

committing to standby status, 9-45

connect strings, 2-119

controlling, 9-51

controlling use, 9-52

converting from Oracle7 data dictionary, 9-50

create script for, 9-42

creating, 13-23

datafiles

modifying, 9-34

specifying, 13-30

designing media recovery, 9-25

ending backup of, 9-34

erasing all data from, 13-23

events

and triggers, 15-102

auditing, 15-102

transparent logging of, 15-102

granting system privileges on, 17-37

in FORCE LOGGING mode, 9-38, 13-20, 13-29

instances of, 13-29

limiting resources for users, 14-71

log files

modifying, 9-37

specifying, 13-27

managed recovery, 9-15

modifying, 9-11

mounting, 9-23, 13-23

moving a subset to a different database, 11-81

naming, 9-23

national character set

specifying, 13-30

no-data-loss mode, 9-44

online

adding log files, 9-38

opening, 9-23, 13-23

after media recovery, 9-24

prepare to re-create, 9-42

preventing changes to, 9-51

protection mode of, 9-44

quiesced state, 10-29

read-only, 9-23

read/write, 9-23

reconstructing damaged, 9-25

recovering, 9-25, 9-26

recovery

allowing corrupt blocks, 9-28

testing, 9-28

with backup control file, 9-27

re-creating control file for, 13-15

remote

accessing, 8-16

authenticating users to, 13-40

connecting to, 13-39

inserting into, 17-59

service name of, 13-40

table locks on, 17-75

resetting

current log sequence, 9-24

to an earlier version, 9-50

restricting users to read-only transactions, 9-24

resuming activity, 10-29

standby

adding log files, 9-38

suspending activity, 10-29

system user passwords, 13-27

tempfiles

modifying, 9-34

time zone

determining, 6-51

setting, valid values for, 9-48, 13-35

time-based recovery, 9-27

upgrading, 9-50

DATAFILE clause

of CREATE DATABASE, 13-30

DATAFILE clauses

of ALTER DATABASE, 9-17, 9-35

DATAFILE END BACKUP clause

of ALTER DATABASE, 9-34

DATAFILE OFFLINE clause

of ALTER DATABASE, 9-35

DATAFILE ONLINE clause

of ALTER DATABASE, 9-35

DATAFILE RESIZE clause

of ALTER DATABASE, 9-36

datafiles

bringing online, 9-35

changing size of, 9-35

creating new, 9-34

defining for a tablespace, 15-81

defining for a temporary tablespace, 15-92

defining for the database, 13-26

designing media recovery, 9-25

dropping, 17-12

enabling autoextend, 7-42

end online backup of, 9-35, 11-107

extending automatically, 7-42

mapping to logical volumes and physical devices, 10-58

online backup of, 11-107

online, updating information on, 10-26

putting online, 9-35

recover damaged, 9-25

recovering, 9-27

re-creating lost or damaged, 9-34

renaming, 9-37

resizing, 9-36

reusing, 7-41

size of, 7-41

specifying, 7-39

for a tablespace, 15-83

specifying for database, 13-30

system generated, 9-34

taking offline, 9-35

datatypes, 2-2

"Any" types, 2-40

ANSI-supported, 2-5

associating statistics with, 12-51, 12-52

BFILE, 2-9, 2-32

BLOB, 2-9, 2-33

built-in, 2-7

CHAR, 2-8, 2-10

character, 2-9

CLOB, 2-9, 2-33

comparison rules, 2-45

converting to collection-typed values, 6-27

converting to other datatypes, 6-27

DATE, 2-7, 2-18

datetime, 2-16

interval, 2-16

INTERVAL DAY TO SECOND, 2-24

INTERVAL YEAR TO MONTH, 2-24

length semantics, 2-10, 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-7, 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-23

TIMESTAMP WITH TIME ZONE, 2-21

UROWID, 2-8, 2-35

user-defined, 2-38

VARCHAR, 2-12

VARCHAR2, 2-7, 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-12

calculating daylight savings time, 2-26

datetime columns

creating from DATE columns, 11-49

datetime datatypes, 2-16

daylight savings time, 2-26

datetime expressions, 4-9

datetime field

extracting from a datetime or interval value, 6-63

datetime format elements, 2-68

and Globalization Support, 2-73

capitalization, 2-68

ISO standard, 2-73

RR, 2-73

suffixes, 2-75

datetime functions, 6-5

DAY datetime format element, 2-73

daylight savings time, 2-26

boundary cases, 2-26

going into or coming out of effect, 2-26

DB_BLOCK_BUFFERS initialization parameter

setting with ALTER SYSTEM, 10-44

DB_BLOCK_CHECKING initialization parameter

setting with ALTER SESSION, 10-7

setting with ALTER SYSTEM, 10-45

DB_BLOCK_CHECKSUM initialization parameter

setting with ALTER SYSTEM, 10-45

DB_BLOCK_SIZE initialization parameter

setting with ALTER SYSTEM, 10-46

DB_CACHE_ADVICE initialization parameter

setting with ALTER SYSTEM, 10-46

DB_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-47, 10-72, 10-87

DB_CACHE_SIZE parameter

of ALTER SYSTEM, 10-72, 10-87

DB_CREATE_FILE_DEST initialization parameter

setting with ALTER SESSION, 10-7

setting with ALTER SYSTEM, 10-47

DB_CREATE_ONLINE_LOG_DEST_n initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-47

DB_DOMAIN initialization parameter

setting with ALTER SYSTEM, 10-47

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-48

DB_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 10-48

DB_FILES initialization parameter

setting with ALTER SYSTEM, 10-49

DB_KEEP_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-50

DB_NAME initialization parameter

setting with ALTER SYSTEM, 10-50

DB_nK_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-44

DB_RECYCLE_CACHE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-51

DB_WRITER_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-51

DB2 datatypes, 2-36

conversion to Oracle datatypes, 2-37

implicit conversion, 2-37

restrictions on, 2-37

DBA role, 17-45

DBA_2PC_PENDING data dictionary view, 10-3

DBA_COL_COMMENTS data dictionary view, 12-72

DBA_ROLLBACK_SEGS data dictionary view, 16-98

DBA_TAB_COMMENTS data dictionary view, 12-72

DBLINK_ENCRYPT_LOGIN initialization parameter

setting with ALTER SYSTEM, 10-51

DBMS_OUTPUT package, 12-3

DBMS_ROWID package

and extended rowids, 2-34

DBMSSTDX.SQL script, 13-53, 14-52, 14-57, 14-64

and triggers, 15-95

DBTIMEZONE function, 6-51

DBWR_IO_SLAVES initialization parameter

setting with ALTER SYSTEM, 10-52

DD datetime format element, 2-69

DDAY datetime format element, 2-69

DDD datetime format element, 2-69

DDL. See data definition language (DDL)

DEALLOCATE UNUSED clause

of ALTER CLUSTER, 9-7, 9-9

of ALTER INDEX, 9-64

of ALTER TABLE, 11-35

DEBUG ANY PROCEDURE system privilege, 17-37

DEBUG clause

of ALTER FUNCTION, 9-60

of ALTER PACKAGE, 9-122

of ALTER PROCEDURE, 9-125

of ALTER TRIGGER, 12-4

of ALTER TYPE, 12-10

DEBUG object privilege, 17-46

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

on a table, 17-47

on a view, 17-47

on an object type, 17-49

debugging

granting system privileges for, 17-37

decimal characters, 2-56

reset for session, 10-9

specifying, 2-65

DECIMAL datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

DECODE function, 6-52

DECOMPOSE function, 6-53

DEFAULT clause

of ALTER TABLE, 11-42

of CREATE TABLE, 15-25

DEFAULT COST clause

of ASSOCIATE STATISTICS, 12-51, 12-53

default index, suppressing, 14-21

DEFAULT profile

assigning to users, 16-95

DEFAULT ROLE clause

of ALTER USER, 12-26

DEFAULT SELECTIVITY clause

of ASSOCIATE STATISTICS, 12-51, 12-53

DEFAULT storage clause

of ALTER TABLESPACE, 11-106

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

of CREATE DATABASE, 13-26

DEFERRABLE clause

of constraints, 7-19

deferrable constraints, 18-45

DEFERRED clause

of SET CONSTRAINTS, 18-45

definer-rights functions, 13-58

DELETE ANY TABLE system privilege, 17-41

DELETE object privilege, 17-46

on a table, 17-47

on a view, 17-47

DELETE statement, 16-55

triggers on, 15-100

DELETE STATISTICS clause

of ANALYZE, 12-46

DELETE_CATALOG_ROLE role, 17-45

DENSE_RANK function, 6-55

DEREF function, 6-58

DESC clause

of CREATE INDEX, 13-77

DETERMINISTIC clause

of CREATE FUNCTION, 13-59

DG_BROKER_CONFIG_FILEn initialization parameter

setting with ALTER SYSTEM, 10-52

DG_BROKER_START initialization parameter

setting with ALTER SYSTEM, 10-52

dimensions

attributes

adding, 9-58

changing, 9-56

defining, 13-47

dropping, 9-58

compiling invalidated, 9-58

creating, 13-43

defining levels, 13-44

examples, 13-47

granting system privileges on, 17-37

hierarchies

adding, 9-58

changing, 9-56

defining, 13-45

dropping, 9-58

levels

adding, 9-58

defining, 13-45

dropping, 9-58

removing from the database, 16-72

directories. See directory objects

directory objects

as aliases for operating system directories, 13-49

auditing, 12-59

creating, 13-49

granting system privileges on, 17-37

redefining, 13-50

removing from the database, 16-74

direct-path INSERT, 17-53

DISABLE ALL TRIGGERS clause

of ALTER TABLE, 11-90

DISABLE clause

of ALTER INDEX, 9-77

of ALTER TRIGGER, 12-3

of CREATE TABLE, 15-55

DISABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 10-27

DISABLE NOVALIDATE constraint state, 7-22, 15-57

DISABLE PARALLEL DML clause

of ALTER SESSION, 10-4

DISABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 9-105

of CREATE MATERIALIZED VIEW, 14-26

DISABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 10-28

DISABLE RESUMABLE clause

of ALTER SESSION, 10-6

DISABLE ROW MOVEMENT clause

of ALTER TABLE, 11-6, 11-38

of CREATE TABLE, 15-15, 15-60

DISABLE STORAGE IN ROW clause

of ALTER TABLE, 11-46

of CREATE TABLE, 15-38

DISABLE TABLE LOCK clause

of ALTER TABLE, 11-89

DISABLE THREAD clause

of ALTER DATABASE, 9-51

DISABLE VALIDATE constraint state, 7-22, 15-57

DISASSOCIATE STATISTICS statement, 16-64

DISCONNECT SESSION clause

of ALTER SYSTEM, 10-26

DISK_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 10-53

dispatcher processes

creating additional, 10-108

terminating, 10-108

DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 10-53

DISTINCT clause

of SELECT, 18-11

distinct queries, 18-11

distributed queries, 8-16

restrictions on, 8-16

distribution

hints for, 2-103

DML. See data manipulation language (DML)

DML_LOCKS initialization parameter

setting with ALTER SYSTEM, 10-55

domain indexes, 13-65, 13-84, 13-95

and LONG columns, 11-50

associating statistics with, 12-51, 12-52

creating, prerequisites, 13-85

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

example, D-2

invoking drop routines for, 17-7

modifying, 9-76

parallelizing creation of, 13-85

rebuilding, 9-72

removing from the database, 16-77

specifying alter string for, 9-76

domain_index_clause

of CREATE INDEX, 13-69

DOUBLE PRECISION datatype (ANSI), 2-36

DRIVING_SITE hint, 2-95

DROP ANY CLUSTER system privilege, 17-36

DROP ANY CONTEXT system privilege, 17-37

DROP ANY DIMENSION system privilege, 17-37

DROP ANY DIRECTORY system privilege, 17-37

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

DROP ANY OPERATOR system privilege, 17-39

DROP ANY OUTLINE system privilege, 17-39

DROP ANY PROCEDURE system privilege, 17-39

DROP ANY ROLE system privilege, 17-40

DROP ANY SEQUENCE system privilege, 17-40

DROP ANY SYNONYM system privilege, 17-41

DROP ANY TABLE system privilege, 17-41

DROP ANY TRIGGER system privilege, 17-42

DROP ANY TYPE system privilege, 17-42

DROP ANY VIEW system privilege, 17-43

DROP clause

of ALTER DIMENSION, 9-58

of ALTER INDEXTYPE, 9-86

DROP CLUSTER statement, 16-67

DROP COLUMN clause

of ALTER TABLE, 11-52

DROP CONSTRAINT clause

of ALTER TABLE, 11-59

DROP constraint clause

of ALTER VIEW, 12-33

DROP CONTEXT statement, 16-69

DROP DATABASE LINK statement, 16-70

DROP DIMENSION statement, 16-72

DROP DIRECTORY statement, 16-74

DROP FUNCTION statement, 16-75

DROP INDEX statement, 16-77

DROP INDEXTYPE statement, 16-79

DROP JAVA statement, 16-81

DROP LIBRARY statement, 16-83

DROP LOG GROUP clause

of ALTER TABLE, 11-34

DROP LOGFILE clause

of ALTER DATABASE, 9-19, 9-39

DROP LOGFILE MEMBER clause

of ALTER DATABASE, 9-19, 9-40

DROP MATERIALIZED VIEW LOG statement, 16-86

DROP MATERIALIZED VIEW statement, 16-84

DROP OPERATOR statement, 16-88

DROP OUTLINE statement, 16-90

DROP PACKAGE BODY statement, 16-91

DROP PACKAGE statement, 16-91

DROP PARTITION clause

of ALTER INDEX, 9-67, 9-81

of ALTER TABLE, 11-73

DROP PRIMARY constraint clause

of ALTER TABLE, 11-59

DROP PROCEDURE statement, 16-93

DROP PROFILE statement, 16-95

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

DROP ROLLBACK SEGMENT statement, 16-98

DROP ROLLBACK SEGMENT system privilege, 17-40

DROP SEQUENCE statement, 17-2

DROP statements

triggers on, 15-101

DROP SUPPLEMENTAL LOG DATA clause

of ALTER DATABASE, 9-41

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

DROP USER statement, 17-20

DROP USER system privilege, 17-43

DROP VALUES clause

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

DROP VIEW statement, 17-22

DRS_START initialization parameter

setting with ALTER SYSTEM, 10-55

DUAL dummy table, 2-111, 8-16

dump file

limiting size of, 10-8

DUMP function, 6-59

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

DYNAMIC_SAMPLING hint, 2-95

E

number format element, 2-64

E datetime format element, 2-69

EBCDIC character set, 2-46

EE datetime format element, 2-69

embedded SQL, 1-4, 9-4

precompiler support, 9-4

EMPTY_BLOB function, 6-61

EMPTY_CLOB function, 6-61

ENABLE ALL TRIGGERS clause

of ALTER TABLE, 11-89

ENABLE clause

of ALTER INDEX, 9-77

of ALTER TRIGGER, 12-3

of CREATE TABLE, 15-55

ENABLE DISTRIBUTED RECOVERY clause

of ALTER SYSTEM, 10-27

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

ENABLE PARALLEL DML clause

of ALTER SESSION, 10-4

ENABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 9-105

of CREATE MATERIALIZED VIEW, 14-26

ENABLE RESTRICTED SESSION clause

of ALTER SYSTEM, 10-28

ENABLE RESUMABLE clause

of ALTER SESSION, 10-6

ENABLE ROW MOVEMENT clause

of ALTER TABLE, 11-6, 11-38

of CREATE TABLE, 15-15, 15-60

ENABLE STORAGE IN ROW clause

of ALTER TABLE, 11-46

of CREATE TABLE, 15-38

ENABLE TABLE LOCK clause

of ALTER TABLE, 11-89

ENABLE THREAD clause

of ALTER DATABASE, 9-50

ENABLE VALIDATE constraint state, 7-21, 15-56

END BACKUP clause

of ALTER DATABASE ... DATAFILE, 9-36

of ALTER TABLESPACE, 11-107, 11-108

ENQUEUE_RESOURCES initialization parameter

setting with ALTER SYSTEM, 10-56

equality test, 5-4

equijoins, 8-11

defining for a dimension, 13-46

equivalency tests, 5-11

error messages

setting language of, 10-9

ERROR_ON_OVERLAP_TIME session parameter, 10-12

ESTIMATE STATISTICS clause

of ANALYZE, 12-42

EVENTS initialization parameter

setting with ALTER SYSTEM, 10-56

EXCEPTIONS INTO clause

of ALTER TABLE, 11-82

restrictions, 11-83

EXCHANGE PARTITION clause

of ALTER TABLE, 11-24, 11-81

EXCHANGE SUBPARTITION clause

of ALTER TABLE, 11-24, 11-81

exchanging partitions

restrictions on, 11-83

EXCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 9-116

of CREATE MATERIALIZED VIEW LOG, 14-41

EXCLUSIVE lock mode, 17-75

EXECUTE ANY INDEXTYPE system privilege, 17-38

EXECUTE ANY OPERATOR system privilege, 17-39

EXECUTE ANY PROCEDURE system privilege, 17-39

EXECUTE ANY TYPE system privilege, 17-42

EXECUTE object privilege, 17-46

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

on a library, 17-49

on an indextype, 17-49

on an object type, 17-49

on an operator, 17-49

EXECUTE_CATALOG_ROLE role, 17-45

execution plans

determining, 17-24

dropping outlines for, 16-90

saving, 14-48

EXEMPT ACCESS POLICY system privilege, 17-44

EXISTS condition, 5-14, 5-15

EXISTS conditions, 5-13

EXISTSNODE function, 6-61

EXP function, 6-62

EXP_FULL_DATABASE role, 17-45

EXPLAIN PLAN statement, 17-24

explicit data conversion, 2-49, 2-52

expressions

CASE, 4-6

changing declared type of, 6-191

comparing, 6-52

compound, 4-5

computing with the DUAL table, 8-16

CURSOR, 4-7

datetime, 4-9

in SQL syntax, 4-2

interval, 4-11

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

of CREATE TABLESPACE, 15-82, 15-87

of CREATE TEMPORARY TABLESPACE, 15-93

EXTENT MANAGEMENT DICTIONARY clause

of CREATE TABLESPACE, 15-87

EXTENT MANAGEMENT LOCAL clause

of CREATE DATABASE, 13-31

of CREATE TABLESPACE, 15-87

of CREATE TEMPORARY TABLESPACE, 15-94

extents

allocating for partitions, 11-35

allocating for subpartitions, 11-35

allocating for tables, 11-35

restricting access by instances, 9-70

specifying maximum number for an object, 7-61

specifying number allocated upon object creation, 7-61

specifying the first for an object, 7-59

specifying the percentage of size increase, 7-60

specifying the second for an object, 7-60

external functions, 13-52, 14-64

external LOBs, 2-28

external procedures, 14-62, 14-64

running from remote database, 14-3

external tables, 15-30

altering, 11-60

creating, 15-33

restrictions on, 15-34

external users, 14-80, 16-34

EXTRACT (datetime) function, 6-63

EXTRACT (XML) function, 6-65

EXTRACTVALUE function, 6-66

F

FAILED_LOGIN_ATTEMPTS parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

FAL_CLIENT initialization parameter

setting with ALTER SYSTEM, 10-56

FAL_SERVER initialization parameter

setting with ALTER SYSTEM, 10-57

FAST_START_IO_TARGET initialization parameter

setting with ALTER SESSION, 10-57

FAST_START_MTTR_TARGET initialization parameter

setting with ALTER SYSTEM, 10-58

FAST_START_PARALLEL_ROLLBACK initialization parameter

setting with ALTER SYSTEM, 10-58

FF datetime format element, 2-69

FILE_MAPPING initialization parameter

setting with ALTER SYSTEM, 10-58

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

FINAL clause

of CREATE TYPE, 16-13, 16-14

FIPS

compliance, B-10

flagging, 10-12

FIRST function, 6-67

FIRST_ROWS(n) hint, 2-96

FIRST_VALUE function, 6-69

FIXED_DATE initialization parameter

setting with ALTER SYSTEM, 10-59

FLAGGER session parameter, 10-12

FLASHBACK ANY TABLE system privilege, 17-39, 17-41, 17-43

FLASHBACK object privilege, 17-46

flashback queries, 18-14

using with inserts, 17-57, 18-66

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

FLUSH SHARED POOL clause

of ALTER SYSTEM, 10-28

FM format model modifier, 2-76

FM number format element, 2-64

FOR clause

of ANALYZE ... COMPUTE STATISTICS, 12-40

of ANALYZE ... ESTIMATE STATISTICS, 12-40

of CREATE INDEXTYPE, 13-96

of EXPLAIN PLAN, 17-26

FOR EACH ROW clause

of CREATE TRIGGER, 15-105

FOR UPDATE clause

of CREATE MATERIALIZED VIEW, 14-25

of SELECT, 18-10, 18-26

FORCE ANY TRANSACTION system privilege, 17-44

FORCE clause

of COMMIT, 12-76

of CREATE VIEW, 16-43

of DISASSOCIATE STATISTICS, 16-66

of DROP INDEX, 16-78

of DROP INDEXTYPE, 16-80

of DROP OPERATOR, 16-89

of DROP TYPE, 17-16

of REVOKE, 17-94

of ROLLBACK, 17-101

FORCE LOGGING clause

of ALTER DATABASE, 9-38

of ALTER TABLESPACE, 11-109

of CREATE CONTROLFILE, 13-20

of CREATE DATABASE, 13-29

of CREATE TABLESPACE, 15-85

FORCE PARALLEL DML clause

of ALTER SESSION, 10-4

FORCE TRANSACTION system privilege, 17-44

FORCE_UNION_REWRITE hint, 2-95

foreign key constraints, 7-14

foreign tables

rowids of, 2-35

format models, 2-61

changing the return format, 2-62

date, 2-68

changing, 2-68

default format, 2-68

format elements, 2-68

maximum length, 2-68

modifiers, 2-75

number, 2-63

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

of values stored in the database, 2-61

free lists

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

specifying for LOBs, 15-39

FREELIST GROUPS parameter

of STORAGE clause, 7-62

FREELISTS parameter

of STORAGE clause, 7-62

FREEPOOLS parameter

of LOB storage, 15-39

FROM clause

of queries, 8-11

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

FULL hint, 2-96

full outer joins, 18-18

function expressions

built-in, 4-11

user-defined, 4-11

function-based indexes, 13-65

and query rewrite, 10-10

creating, 13-75

disabling, 10-99

enabling, 9-72, 9-77, 10-99

enabling and disabling, 9-72

refreshing, 9-47

functions

See also SQL functions

3GL, calling, 14-2

analytic

user-defined, 13-61

associating statistics with, 12-51, 12-52

avoiding run-time compilation, 9-59

built_in

as expressions, 4-11

calling, 12-68

changing the declaration of, 13-55

changing the definition of, 13-55

datatype of return value, 13-57

datetime, 6-5

DECODE, 6-52

defining an index on, 13-75

examples, 13-62

executing, 12-68

from parallel query processes, 13-60

external, 13-52, 14-64

inverse distribution, 6-118, 6-121

issuing COMMIT or ROLLBACK statements, 10-3

linear regression, 6-129

naming rules, 2-114

partitioning

among parallel query processes, 13-60

privileges executed with, 12-14, 16-10

recompiling explicitly, 9-60

recompiling invalid, 9-59

re-creating, 13-55, 13-99

removing from the database, 16-75

returning collections, 13-60

returning results iteratively, 13-60

schema executed in, 12-14, 16-10

specifying schema and user privileges for, 13-58

statistics, assigning default cost, 12-51

statistics, defining default selectivity, 12-51

stored, 13-52

storing return value of, 12-70

synonyms for, 15-2

table, 13-60

user_defined

as expressions, 4-11

user-defined, 6-222

aggregate, 13-61

using a saved copy, 13-59

FX format model modifier, 2-76

G

G number format element, 2-64

GC_FILES_TO_LOCKS initialization parameter

setting with ALTER SYSTEM, 10-59

general recovery clause

of ALTER DATABASE, 9-13, 9-25

global database names

enforcing resolution, 10-60

global indexes. See indexes, globally partitioned

GLOBAL PARTITION BY RANGE clause

of CREATE INDEX, 7-24, 13-69, 13-81, 15-59

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

GLOBAL TEMPORARY clause

of CREATE TABLE, 15-23

global users, 14-80, 16-35

GLOBAL_CONTEXT_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-60

GLOBAL_NAMES initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-60

Globalization Support

change session settings, 10-8

globally partitioned indexes, 7-24, 13-81, 13-83, 15-59

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

GRANT CONNECT THROUGH clause

of ALTER USER, 12-24, 12-26

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

GROUP BY clause

CUBE extension, 18-22

identifying duplicate groupings, 6-72

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

ROLLUP extension of, 18-22

group comparison conditions, 5-7

GROUP_ID function, 6-72

GROUPING function, 6-74

grouping sets, 18-23

GROUPING SETS clause

of SELECT and subqueries, 18-23

GROUPING_ID function, 6-75

groupings

filtering out duplicate, 6-72

GUARD ALL clause

of ALTER DATABASE, 9-51

GUARD clause

of ALTER DATABASE, 9-51

GUARD NONE clause

of ALTER DATABASE, 9-52

GUARD STANDBY clause

of ALTER DATABASE, 9-51

H

hash clusters

creating, 13-6

single-table, creating, 13-7

specifying hash function for, 13-7

HASH hint, 2-96

HASH IS clause

of CREATE CLUSTER, 13-7

hash joins

allocating memory for, 10-8

enabling and disabling, 10-8

hash partitioning clause

of CREATE TABLE, 15-21, 15-46

hash partitions

adding, 11-71

coalescing, 11-64

HASH_AJ hint, 2-96, 2-97

HASH_AREA_SIZE initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-60

HASH_JOIN_ENABLED initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-61

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

HEXTORAW function, 6-77

HH datetime format element, 2-69

HH12 datetime format element, 2-69

HH24 datetime format element, 2-69

HI_SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 10-61

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

child rows, 2-86, 8-4

illustrated, 2-86

leaf rows, 2-86

ordering, 18-25

parent rows, 2-86, 8-4

retrieving root and node values, 6-155

hierarchical query clause

of SELECT and subqueries, 18-8

hierarchies

adding to a dimension, 9-58

dropping from a dimension, 9-58

of dimensions, defining, 13-45

HIERARCHY clause

of CREATE DIMENSION, 13-44, 13-45

high water mark

of clusters, 9-9

of indexes, 9-69

of tables, 11-35, 12-38

hints, 8-3

ALL_ROWS hint, 2-94

AND_EQUAL hint, 2-94

CACHE hint, 2-94

CLUSTER hint, 2-95

FIRST_ROWS hint, 2-96

FULL hint, 2-96

HASH hint, 2-96

in SQL statements, 2-91

INDEX hint, 2-97

INDEX_ASC hint, 2-97

INDEX_DESC hint, 2-97, 2-98

NO_EXPAND hint, 2-100

NO_MERGE hint, 2-100

NO_PUSH_PRED hint, 2-101

NO_PUSH_SUBQ hint, 2-101

NOCACHE hint, 2-99

NOPARALLEL hint, 2-100

NOREWRITE hint, 2-101

ORDERED hint, 2-101

PARALLEL hint, 2-103

passing to the optimizer, 18-59

PQ_DISTRIBUTE hint, 2-103

PUSH_PRED hint, 2-104

PUSH_SUBQ hint, 2-104

REWRITE hint, 2-104

ROWID hint, 2-104

RULE hint, 2-105

syntax, 2-92

USE_CONCAT hint, 2-106

USE_MERGE hint, 2-106

USE_NL hint, 2-106

histograms

creating equiwidth, 6-208

HS_ADMIN_ROLE role, 17-45

HS_AUTOREGISTER initialization parameter

setting with ALTER SYSTEM, 10-62

I

I datetime format element, 2-69

IDENTIFIED BY clause

of ALTER ROLE. See CREATE ROLE

of CREATE DATABASE LINK, 13-40

of SET ROLE, 18-48

IDENTIFIED EXTERNALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER. See CREATE USER

of CREATE ROLE, 14-80

of CREATE USER, 16-34

IDENTIFIED GLOBALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER, 12-25

of CREATE ROLE, 14-80

of CREATE USER, 16-35

IDLE_TIME parameter

of ALTER PROFILE, 9-128

IFILE initialization parameter

setting with ALTER SYSTEM, 10-62

IMMEDIATE clause

of SET CONSTRAINTS, 18-45

IMP_FULL_DATABASE role, 17-45

implicit data conversion, 2-49, 2-51

IN OUT parameter

of CREATE FUNCTION, 13-57

of CREATE PROCEDURE, 14-67

IN parameter

of CREATE function, 13-57

of CREATE PROCEDURE, 14-67

INCLUDING CONTENTS clause

of DROP TABLESPACE, 17-11

INCLUDING DATAFILES clause

of ALTER DATABASE TEMPFILE DROP clause, 9-37

INCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 9-116

of CREATE MATERIALIZED VIEW LOG, 14-41

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

INDEX clause

of ANALYZE, 12-36

of CREATE CLUSTER, 13-6

INDEX hint, 2-97

index keys

compression, 9-65

INDEX object privilege, 17-46

on a table, 17-47

index partitions

creating subpartitions, 13-71

dropping, 9-67

index subpartitions, 13-71

INDEX_ASC hint, 2-97

INDEX_DESC hint, 2-97, 2-98

indexed clusters

creating, 13-6

indexes, 9-71

access path, optimizing for, 10-9

allocating new extents for, 9-70

application-specific, 13-95

ascending, 13-77

based on indextypes, 13-84

bitmap, 13-72

bitmap join, 13-86

B-tree, 13-65

changing attributes, 9-71

changing parallelism of, 9-70

collecting statistics on, 12-36

on composite-partitioned tables, 13-83

creating, 13-65

creating on a cluster, 13-66

creating on a table, 13-67

deallocating unused space from, 9-69

descending, 13-77

and query rewrite, 13-77

as function-based indexes, 13-77

direct-path inserts, logging, 9-71

disassociating statistics types from, 16-78

domain, 13-65, 13-84, 13-95

domain, example, D-2

dropping index partitions, 16-78

examples, 13-87

function-based, 13-65

creating, 13-75

global partitioned, creating, 13-69

globally partitioned, 7-24, 13-81, 13-83, 15-59

updating, 11-85

granting system privileges on, 17-38

on hash-partitioned tables, 13-83

join, bitmap, 13-86

key compression of, 9-75

key compression, enabling, 9-72

keys, eliminating repetition, 9-72

locally partitioned, 13-83

logging rebuild operations, 9-72

logging rebuild operations on, 9-76

marking as UNUSABLE, 9-77

merging block contents, 9-72

merging contents of index blocks, 9-78

modifying attributes, 9-72

moving, 9-72

on clusters, 13-73

on composite-partitioned tables, creating, 13-71

on hash-partitioned tables

creating, 13-70

on index-organized tables, 13-73

on list-partitioned tables

creating, 13-70

on nested table storage tables, 13-73

on partitioned tables, 13-73

on range-partitioned tables, creating, 13-70

on scalar typed object attributes, 13-73

on table columns, 13-73

on XMLType tables, 13-88

online, 13-80

parallelizing creation of, 13-80

partitioned, 2-108, 13-65

user-defined, 7-24, 13-81, 15-59

partitioning, 13-81

partitions, 13-81

adding new, 9-81

changing default attributes, 9-79

changing physical attributes, 9-71

changing storage characteristics, 9-79

deallocating unused space from, 9-69

dropping, 9-81

marking UNUSABLE, 9-81, 11-84

modifying the real characteristics, 9-80

preventing use of, 9-77

rebuilding, 9-72

rebuilding unusable, 11-84

re-creating, 9-72

removing, 9-79

renaming, 9-81

specifying tablespace, 9-72

specifying tablespace for, 9-74

splitting, 9-79, 9-81

physical attributes, 13-77

preventing use of, 9-77

on range-partitioned tables, 13-83

rebuilding, 9-72

rebuilding while online, 9-75

re-creating, 9-72

removing from the database, 16-77

renaming, 9-72, 9-78

reverse, 9-72, 9-74, 13-79

specifying tablespace for, 9-72, 9-74

statistics on, 13-80

statistics on rebuild, 9-75

statistics on usage, 9-78

storage attributes, 13-77

subpartitions

allocating extents for, 9-82

changing default attributes, 9-79

changing physical attributes, 9-71

changing storage characteristics, 9-79

deallocating unused space from, 9-69, 9-82

marking UNUSABLE, 9-82

modifying, 9-72

moving, 9-72

preventing use of, 9-77

rebuilding, 9-72

re-creating, 9-72

renaming, 9-81

specifying tablespace, 9-72

specifying tablespace for, 9-74

tablespace containing, 13-78

unique, 13-71

unsorted, 13-78

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

validating structure, 12-43

index-organized tables

bitmap indexes on, creating, 15-32

creating, 15-7

mapping tables, 11-87

moving, 11-68

mapping tables, creating, 15-32

modifying, 11-39

moving, 11-87

overflow segments

specifying storage, 11-40, 15-47

partitioned, updating secondary indexes, 9-80

PCT_ACCESS_DIRECT statistics, 12-38

primary key indexes

coalescing, 11-41

updating, 11-41

rebuilding, 11-86

rowids of, 2-35

secondary indexes, updating, 9-79

INDEXTYPE clause

of CREATE INDEX, 13-69, 13-84

indextypes

adding operators, 9-85

altering, 9-85

associating statistics with, 12-51, 12-52

changing implementation type, 9-85

comments on, 12-74

creating, 13-95

disassociating from statistics types, 16-79

drop routines, invoking, 16-78

granting system privileges on, 17-37

indexes based on, 13-84

instances, 13-65

removing from the database, 16-79

in-doubt transactions

forcing, 12-76

forcing commit of, 12-76

forcing rollback, 17-101

rolling back, 17-99

inequality test, 5-4

INITCAP function, 6-77

INITIAL parameter

of STORAGE clause, 7-59

initialization parameters

changing session settings, 10-6

CIRCUITS, 10-39

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

INITRANS parameter

of ALTER CLUSTER, 9-8

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

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

inline views, 8-13

IN-lists, 2-106

inner joins, 8-12, 18-18

INSERT ANY TABLE system privilege, 17-41

INSERT clause

of MERGE, 17-78

INSERT object privilege, 17-46

on a table, 17-47

on a view, 17-47

INSERT statement, 17-53

append, 2-94

triggers on, 15-100

inserts

and simultaneous update, 17-77

conditional, 17-63

conventional, 17-53

direct-path, 17-53

multitable, 17-63

multitable, examples, 17-68

single-table, 17-57

using MERGE, 17-78

instance recovery

continue after interruption, 9-25

INSTANCE session parameter, 10-12

INSTANCE_GROUPS initialization parameter

setting with ALTER SYSTEM, 10-62

INSTANCE_NAME initialization parameter

setting with ALTER SYSTEM, 10-63

INSTANCE_NUMBER initialization parameter

setting with ALTER SYSTEM, 10-63

instances

global name resolution for, 10-60

making index extents available to, 9-70

memory requirements of, 10-44

setting parameters for, 10-31

INSTANTIABLE clause

of CREATE TYPE, 16-13

INSTEAD OF clause

of CREATE TRIGGER, 15-99

INSTEAD OF triggers, 15-99

INSTR function, 6-78

INSTR2 function, 6-78

INSTR4 function, 6-78

INSTRB function, 6-78

INSTRC function, 6-78

INT datatype (ANSI), 2-36

INTEGER datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

integers

generating unique, 14-89

in SQL syntax, 2-55

precision of, 2-55

specifying, 2-12

syntax of, 2-55

integrity constraints. See constraints

internal LOBs, 2-28

International Standards Organization (ISO), B-1

standards, xix, 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-11

INTERVAL YEAR TO MONTH datatype, 2-24

INTO clause

of EXPLAIN PLAN, 17-26

of INSERT, 17-57

INVALIDATE GLOBAL INDEXES clause

of ALTER TABLE, 11-85

inverse distribution functions, 6-118, 6-121

invoker rights

altering for a Java class, 9-88

altering for an object type, 12-14

defining for a function, 13-58

defining for a Java class, 13-99, 13-101

defining for a package, 14-53

defining for a procedure, 14-65

defining for an object type, 16-10

invoker-rights functions

defining, 13-58

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

IW datetime format element, 2-69

IY datetime format element, 2-69

IYY datetime format element, 2-69

IYYY datetime format element, 2-69

J

J datetime format element, 2-69

Java

class

creating, 13-98, 13-100

dropping, 16-81

resolving, 9-87, 13-100

Java source schema object

creating, 13-100

methods

return type of, 16-14

resource

creating, 13-98, 13-100

dropping, 16-81

schema object

name resolution of, 13-102

source

compiling, 9-87, 13-100

creating, 13-98

dropping, 16-81

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

JAVA_MAX_SESSIONSPACE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-64

JAVA_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-64

JOB_QUEUE_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-65

JOIN clause

of CREATE DIMENSION, 13-44

JOIN KEY clause

of ALTER DIMENSION, 9-57

of CREATE DIMENSION, 13-46

join views

example, 16-51

making updatable, 16-48

modifying, 16-59, 17-58, 18-62

joins, 8-10

conditions

defining, 8-10

cross, 18-19

equijoins, 8-11

full outer, 18-18

inner, 8-12, 18-18

left outer, 18-18

natural, 18-19

nested loop, optimizing for, 10-9

outer, 8-12

restrictions, 8-12

parallel, and PQ_DISTRIBUTE hint, 2-103

right outer, 18-18

self, 8-11

without join conditions, 8-11

Julian dates, 2-20

K

key compression, 15-32

definition, 9-75

disabling, 9-75, 13-78

enabling, 9-72

of index rebuild, 11-88

of indexes

disabling, 9-75

of index-organized tables, 15-32

key-preserved tables, 16-48

keywords, 2-111

in object names, 2-111

optional, A-4

required, A-3

KILL SESSION clause

of ALTER SYSTEM, 10-27

L

L number format element, 2-64

LAG function, 6-80

LANGUAGE clause

of CREATE PROCEDURE, 14-68

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

LAST function, 6-81

LAST_DAY function, 6-83

LAST_VALUE function, 6-84

LEAD function, 6-86

LEAST function, 6-87

left outer joins, 18-18

LENGTH function, 6-88

LENGTH2 function, 6-88

LENGTH4 function, 6-88

LENGTHB function, 6-88

LENGTHC function, 6-88

less than tests, 5-5

LEVEL clause

of ALTER DIMENSION, 9-57

of CREATE DIMENSION, 13-44, 13-45

LEVEL pseudocolumn, 2-86, 18-20

and hierarchical queries, 2-86

levels

adding to a dimension, 9-58

dropping from a dimension, 9-58

of dimensions, defining, 13-45

libraries

creating, 14-2

granting system privileges on, 17-38

re-creating, 14-2

removing from the database, 16-83

library units. See Java schema objects

LICENSE_MAX_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-66

LICENSE_MAX_USERS initialization parameter

setting with ALTER SYSTEM, 10-66

LICENSE_SESSIONS_WARNING initialization parameter

setting with ALTER SYSTEM, 10-67

licenses

changing limits, 10-66, 10-67

licensing

changing limits, 10-66

LIKE conditions, 5-15

linear regression functions, 6-129

LIST CHAINED ROWS clause

of ANALYZE, 12-45

list partitioning

adding default partition, 11-72

adding partitions, 11-65, 11-72

adding values, 11-65

creating a default partition, 15-48

creating partitions, 15-48

default partition

adding, 11-65

dropping, 11-65

dropping values, 11-65

merging default with nondefault partitions, 11-79

splitting default partition, 11-75

list subpartitions

adding, 11-63

listeners

registering, 10-31

literals

in SQL statements and functions, 2-54

in SQL syntax, 2-53

LN function, 6-89

LOB columns

adding, 11-41

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

defining properties

for materialized views, 14-12

modifying, 11-49

modifying storage, 11-45

restricted in joins, 8-11

restrictions on, 2-29

storage characteristics of materialized views, 9-99

LOB datatypes, 2-28

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

of ALTER TABLE, 11-14, 11-45

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

of CREATE TABLE, 15-14, 15-36

LOB_storage_clause

of ALTER MATERIALIZED VIEW, 9-93

LOBs

attributes, initializing, 2-29

CACHE READS setting, 2-31

columns

difference from LONG and LONG RAW, 2-28

populating, 2-29

external, 2-28

indexes for, 15-39

internal, 2-28

locators, 2-28

logging attribute, 15-28

modifying physical attributes, 11-57

number of bytes manipulated in, 15-38

saving old versions, 15-38, 15-39

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

specifying directories for, 13-49

storage

attributes, 15-36

characteristics, 7-55

in-line, 15-37

tablespace for

defining, 15-28

LOCAL clause

of CREATE INDEX, 13-69, 13-83

local users, 14-80, 16-34

LOCAL_LISTENER initialization parameter

setting with ALTER SYSTEM, 10-67

locally managed tablespaces

altering, 11-104

storage attributes, 7-59

locally partitioned indexes, 13-83

LOCALTIMESTAMP function, 6-90

location transparency, 15-2

LOCK ANY TABLE system privilege, 17-41

LOCK TABLE statement, 17-73

LOCK_NAME_SPACE initialization parameter

setting with ALTER SYSTEM, 10-68

LOCK_SGA initialization parameter

setting with ALTER SYSTEM, 10-68

locking

automatic

overriding, 17-73

locks. See table locks

log data

collection during update operations, 9-40

log file clauses

of ALTER DATABASE, 9-19

log files

adding, 9-37

dropping, 9-37

modifying, 9-37

registering, 9-45

renaming, 9-37

setting session path for, 10-8

specifying for the database, 13-27

LOG function, 6-91

log groups

dropping, 11-34

LOG_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 10-68

LOG_ARCHIVE_DEST_n initialization parameter

overriding DELAY setting, 9-31

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

LOG_ARCHIVE_DEST_STATE_n initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-70

LOG_ARCHIVE_DUPLEX_DEST initialization parameter

setting with ALTER SYSTEM, 10-71

LOG_ARCHIVE_FORMAT initialization parameter

setting with ALTER SYSTEM, 10-71

LOG_ARCHIVE_MAX_PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-72

LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-72

LOG_ARCHIVE_START parameter

of ALTER SYSTEM, 10-72

LOG_ARCHIVE_TRACE initialization parameter

setting with ALTER SYSTEM, 10-73

LOG_BUFFER initialization parameter

setting with ALTER SYSTEM, 10-73

LOG_CHECKPOINT_INTERVAL initialization parameter

setting with ALTER SYSTEM, 10-73

LOG_CHECKPOINT_TIMEOUT initialization parameter

setting with ALTER SYSTEM, 10-74

LOG_CHECKPOINTS_TO_ALERT initialization parameter

setting with ALTER SYSTEM, 10-74

LOG_FILE_NAME_CONVERT initialization parameter

setting with ALTER SYSTEM, 10-74

LOG_PARALLELISM initialization parameter

setting with ALTER SYSTEM, 10-75

LOGFILE clause

OF CREATE DATABASE, 13-27

LOGFILE GROUP clause

of CREATE CONTROLFILE, 13-18

logging, 9-71, 15-85

and redo log size, 7-46

specifying minimal, 7-46

supplemental

dropping, 9-41

supplemental, adding log groups, 11-34

supplemental, dropping log groups, 11-34

LOGGING clause

of ALTER INDEX, 9-71

of ALTER INDEX ... REBUILD, 9-76

of ALTER MATERIALIZED VIEW, 9-100

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER TABLE, 11-34

of ALTER TABLESPACE, 11-109

of CREATE MATERIALIZED VIEW, 14-17

of CREATE MATERIALIZED VIEW LOG, 14-38

of CREATE TABLE, 15-28

of CREATE TABLESPACE, 15-85

logical conditions, 5-8

logical standby database

aborting, 9-47

activating, 9-43

stopping, 9-47

LOGICAL_READS_PER_CALL parameter

of ALTER PROFILE, 9-128

LOGICAL_READS_PER_SESSION parameter

of ALTER PROFILE, 9-128

of ALTER RESOURCE COST, 9-132

LOGMNR_MAX_PERSISTENT_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-75

LOGOFF database event

triggers on, 15-103

LOGON database event

triggers on, 15-103

LONG columns

and domain indexes, 11-50

converting to LOB, 2-15, 11-50

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

LONG VARCHAR datatype

DB2, 2-37

SQL/DS, 2-37

LONG VARGRAPHIC datatype

DB2, 2-37

SQL/DS, 2-37

LOWER function, 6-91

LPAD function, 6-92

LTRIM function, 6-93

M

MAKE_REF function, 6-94

MANAGE TABLESPACE system privilege, 17-42

managed recovery

of database, 9-15

wait period of, 9-30

managed standby recovery

as background process, 9-30

overriding delays, 9-30

returning control during, 9-33

terminating automatically, 9-31

terminating existing, 9-32

MANAGED STANDBY RECOVERY clause

of ALTER DATABASE, 9-29

MAP MEMBER clause

of ALTER TYPE, 12-13

of CREATE TYPE, 16-30

MAP methods

defining for a type, 16-17

specifying, 12-13

MAPPING TABLE clause

of ALTER TABLE, 11-68, 11-87

mapping tables

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

modifying, 11-41

master databases, 14-5

master tables, 14-5

MATCHES condition, 5-2

materialized join views, 14-34

materialized view logs, 14-34

adding columns, 9-114

creating, 14-34

excluding new values from, 9-116

logging changes to, 9-114

object ID based, 9-115

parallelizing creation, 14-38

partition attributes, changing, 9-113

partitioned, 14-39

physical attributes

specifying, 14-37

physical attributes, changing, 9-113

removing from the database, 16-86

required for fast refresh, 14-34

rowid based, 9-115

saving new values in, 9-116

saving old values in, 14-41

storage attributes

specifying, 14-37

materialized view partition segments

compression of, 9-99, 14-18

materialized view segments

data compression of, 9-99, 14-18

materialized views, 9-102, 14-21

allowing update of, 14-25

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

changing to primary-key-based, 9-115

complete refresh, 9-103, 14-22

constraints on, 7-22

creating, 14-5

creating comments about, 12-72

for data warehousing, 14-5

degree of parallelism, 9-100, 9-113

during creation, 14-20

enabling and disabling query rewrite, 14-26

examples, 14-28, 14-41

fast refresh, 9-102, 14-21, 14-22

forced refresh, 9-103

index characteristics

changing, 9-100

indexes that maintain, 14-21

join, 14-34

LOB storage attributes, 9-99

logging changes to, 9-100

master table, dropping, 16-85

object type, creating, 14-15

partitions, 9-99

physical attributes, 14-17

changing, 9-98

primary key, 14-23

recording values in master table, 9-114

query rewrite

eligibility for, 7-22

enabling and disabling, 9-105

re-creating during refresh, 9-103

refresh mode

changing, 9-102

refresh time

changing, 9-102

refreshing, 9-47

refreshing after DML on master table, 9-104, 14-23

refreshing on next COMMIT, 9-103, 14-22

removing from the database, 16-84

for replication, 14-5

restricting scope of, 14-16

retrieving data from, 18-4

revalidating, 9-106

rowid, 14-24

rowid values

recording in master table, 9-114

saving blocks in a cache, 9-101

storage attributes, 14-17

changing, 9-98

subquery, 14-26

suppressing creation of default index, 14-21

synonyms for, 15-2

when to populate, 14-20

MAX function, 6-95

MAX_COMMIT_PROPAGATION_DELAY initialization parameter

setting with ALTER SYSTEM, 10-76

MAX_DISPATCHERS initialization parameter

setting with ALTER SYSTEM, 10-76

MAX_DUMP_FILE_SIZE initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-77

MAX_ENABLED_ROLES initialization parameter

setting with ALTER SYSTEM, 10-77

MAX_ROLLBACK_SEGMENTS initialization parameter

setting with ALTER SYSTEM, 10-77

MAX_SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-78

MAXDATAFILES parameter

of CREATE CONTROLFILE, 13-20

of CREATE DATABASE, 13-29

MAXEXTENTS parameter

of STORAGE clause, 7-61

MAXINSTANCES parameter

of CREATE CONTROLFILE, 13-20

OF CREATE DATABASE, 13-29

MAXLOGFILES parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-28

MAXLOGHISTORY parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-28

MAXLOGMEMBERS parameter

of CREATE CONTROLFILE, 13-19

of CREATE DATABASE, 13-28

MAXSIZE clause

of ALTER DATABASE, 9-18

MAXTRANS parameter

of ALTER CLUSTER, 9-8

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

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

media recovery

avoid on startup, 9-35

designing, 9-25

disabling, 9-34

from specified redo logs, 9-25

of database, 9-25

of datafiles, 9-25

of standby database, 9-25

of tablespaces, 9-25

parallelizing, 9-28

performing ongoing, 9-29

preparing for, 9-37, 9-38

restrictions, 9-25

sustained standby recovery, 9-29

media types

ORDSYS.ORDAudio, 2-44

ORDSYS.ORDDoc, 2-44

ORDSYS.ORDImage, 2-44

ORDSYS.ORDVideo, 2-44

median values, 6-121

MEMBER clause

of ALTER TYPE, 12-12

of CREATE TYPE, 16-12

of CREATE TYPE BODY, 16-28

membership conditions, 5-9

MERGE hint, 2-98

MERGE PARTITIONS clause

of ALTER TABLE, 11-79

MERGE statement, 17-77

MERGE_AJ hint, 2-96, 2-97

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

MI number format element, 2-64

MIGRATE clause

of ALTER DATABASE, 9-24

migrated rows

listing, 12-45

of clusters, 12-39

MIN function, 6-97

MINEXTENTS parameter

of STORAGE clause, 7-61

MINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 11-37

MINIMUM EXTENT clause

of ALTER TABLESPACE, 11-106

of CREATE TABLESPACE, 15-84

MINUS set operator, 3-6, 18-24

MINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 14-92

MM datetime format element, 2-69

MOD function, 6-98

MODE clause

of LOCK TABLE, 17-75

MODIFY clause

of ALTER TABLE, 11-49

MODIFY CONSTRAINT clause

of ALTER TABLE, 11-11, 11-58

of ALTER VIEW, 12-33

MODIFY DEFAULT ATTRIBUTES clause

of ALTER INDEX, 9-66, 9-79

of ALTER TABLE, 11-61

MODIFY LOB clause

of ALTER TABLE, 11-57

MODIFY LOB storage clause

of ALTER MATERIALIZED VIEW, 9-94, 9-99

of ALTER TABLE, 11-57

MODIFY NESTED TABLE clause

of ALTER TABLE, 11-10, 11-57

MODIFY PARTITION clause

of ALTER INDEX, 9-67, 9-80

of ALTER MATERIALIZED VIEW, 9-100

of ALTER TABLE, 11-62

MODIFY scoped_table_ref_constraint clause

of ALTER MATERIALIZED VIEW, 9-102

MODIFY SUBPARTITION clause

of ALTER INDEX, 9-68, 9-82

of ALTER TABLE, 11-66

MODIFY VARRAY clause

of ALTER TABLE, 11-15, 11-57

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

MONITORING clause

of ALTER TABLE, 11-36

of CREATE TABLE, 15-55

MONITORING USAGE clause

of ALTER INDEX, 9-78

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

MONTHS_BETWEEN function, 6-99

MOUNT clause

of ALTER DATABASE, 9-23

MOVE clause

of ALTER TABLE, 11-29, 11-86

MOVE ONLINE clause

of ALTER TABLE, 11-87

MOVE PARTITION clause

of ALTER TABLE, 11-67

MOVE SUBPARTITION clause

of ALTER TABLE, 11-68

MTS. See shared server

multilevel collections, 15-42

MULTISET parameter

of CAST function, 6-27

multitable inserts, 17-63

conditional, 17-63

examples, 17-68

unconditional, 17-63

multi-threaded server. See shared server

N

NAME clause

of SET TRANSACTION, 18-52

NAMED clause

of CREATE JAVA, 13-101

namespaces

and object naming rules, 2-112

for nonschema objects, 2-113

for schema objects, 2-112

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

of CREATE DATABASE, 13-30

national character sets

changing, 9-47

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

NCLOB datatype, 2-33

transactional support of, 2-33

negative scale, 2-13

nested loop joins

optimizing for, 10-9

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

nested tables, 2-39

changing returned value, 11-57

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

in materialized views, 14-12, 14-13

indexing columns of, 13-74

modifying, 11-57

modifying column properties, 11-12

multilevel, 15-42

storage characteristics of, 11-44, 15-41

update in a view, 15-99

NEW_TIME function, 6-100

NEXT clause

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

NEXT parameter

of STORAGE clause, 7-60

NEXT_DAY function, 6-102

NEXTVAL pseudocolumn, 2-82, 14-89

NL_SJ hint, 2-96, 2-97

NLS_CALENDAR initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-78

NLS_CHARSET_DECL_LEN function, 6-102

NLS_CHARSET_ID function, 6-103

NLS_CHARSET_NAME function, 6-104

NLS_COMP initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-79

NLS_CURRENCY initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-79

NLS_DATE_FORMAT initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-79

NLS_DATE_LANGUAGE initialization parameter, 2-73

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-80

NLS_DUAL_CURRENCY initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-80

NLS_INITCAP function, 6-104

NLS_ISO_CURRENCY initialization parameter

setting with ALTER SESSION, 10-8

setting with ALTER SYSTEM, 10-80

NLS_LANGUAGE initialization parameter, 2-73, 8-10

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_LENGTH_SEMANTICS initialization parameter

overriding, 2-10

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_LOWER function, 6-106

NLS_NCHAR_CONV_EXCP initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-81

NLS_NUMERIC_CHARACTERS initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_SORT initialization parameter, 8-10

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_TERRITORY initialization parameter, 2-73

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-82

NLS_TIMESTAMP_FORMAT initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-83

NLS_TIMESTAMP_TZ_FORMAT initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-83

NLS_UPPER function, 6-108

NLSSORT function, 6-107

NO FORCE LOGGING clause

of ALTER DATABASE, 9-38

of ALTER TABLESPACE, 11-109

NO_EXPAND hint, 2-100

NO_INDEX hint, 2-100

NO_MERGE hint, 2-100

NO_PUSH_PRED hint, 2-101

NOAPPEND hint, 2-99

NOARCHIVELOG clause

of ALTER DATABASE, 9-19, 9-37

of CREATE CONTROLFILE, 13-20

OF CREATE DATABASE, 9-25, 13-29

NOAUDIT statement, 17-81

NOCACHE clause

of ALTER CLUSTER, 9-9

of ALTER MATERIALIZED VIEW, 9-101

of ALTER MATERIALIZED VIEW LOG, 9-114

of ALTER SEQUENCE. See CREATE SEQUENCE

of ALTER TABLE, 11-36, 15-53

of CREATE CLUSTER, 13-9

of CREATE MATERIALIZED VIEW, 14-19

of CREATE MATERIALIZED VIEW LOG, 14-38

of CREATE SEQUENCE, 14-92

NOCACHE hint, 2-99

NOCOMPRESS clause

of ALTER INDEX ... REBUILD, 9-75

of CREATE INDEX, 13-78

of CREATE TABLE, 15-32

NOCOPY clause

of CREATE FUNCTION, 13-57

of CREATE PROCEDURE, 14-67

NOCYCLE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-140

of CREATE SEQUENCE, 14-92

NOFORCE clause

of CREATE JAVA, 13-100

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

NOMINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 11-37

NOMINVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-140

of CREATE SEQUENCE, 14-92

NOMONITORING clause

of ALTER TABLE, 11-36

of CREATE TABLE, 15-55

NOMONITORING USAGE clause

of ALTER INDEX, 9-78

NONE clause

of SET ROLE, 18-48

nonequivalency tests, 5-11

nonpadded comparison semantics, 2-45

nonschema objects

list of, 2-107

namespaces, 2-113

NOORDER parameter

of ALTER SEQUENCE. See CREATE SEQUENCE, 9-140

of CREATE SEQUENCE, 14-93

NOPARALLEL clause

of CREATE INDEX, 7-50, 9-10, 9-29, 9-70, 9-100, 9-113, 11-86, 13-8, 13-81, 14-20, 14-39, 15-53

NOPARALLEL hint, 2-100

NOPARALLEL_INDEX hint, 2-100

NORELY clause

of constraints, 7-22

NORESETLOGS clause

of CREATE CONTROLFILE, 13-19

NOREVERSE parameter

of ALTER INDEX ... REBUILD, 9-74

NOREWRITE hint, 2-101

NOROWDEPENDENCIES clause

of CREATE CLUSTER, 13-9

of CREATE TABLE, 15-54

NOSORT clause

of ALTER INDEX, 13-78

NOT condition, 5-8

NOT DEFERRABLE clause

of constraints, 7-19

NOT FINAL clause

of CREATE TYPE, 16-13

NOT IDENTIFIED clause

of ALTER ROLE. See CREATE ROLE

of CREATE ROLE, 14-80

NOT INSTANTIABLE clause

of CREATE TYPE, 16-13, 16-14

NOT NULL clause

of CREATE TABLE, 15-26

NOWAIT clause

of LOCK TABLE, 17-75

NTILE function, 6-109

null, 2-80

difference from zero, 2-80

in conditions, 2-81

table of, 2-82

in functions, 2-80

with comparison conditions, 2-81

null conditions, 5-13

NULLIF function, 6-110

as a form of CASE expression, 6-110

NUMBER datatype, 2-12

converting to VARCHAR2, 2-63

precision, 2-12

scale, 2-12

number format models, 2-63

number functions, 6-3

numbers

comparison rules, 2-45

floating-point, 2-12, 2-14

in SQL syntax, 2-56

precision of, 2-56

rounding, 2-13

spelling out, 2-75

syntax of, 2-56

NUMERIC datatype (ANSI), 2-36

NUMTODSINTERVAL function, 6-111

NUMTOYMINTERVAL function, 6-112

NVARCHAR2 datatype, 2-11

NVL function, 6-113

NVL2 function, 6-114

O

O7_DICTIONARY_ACCESSIBILITY initialization parameter

setting with ALTER SYSTEM, 10-83

object access expressions, 4-12

object cache, 10-9, 10-84

OBJECT IDENTIFIER clause

of CREATE TABLE, 15-63

object identifiers

contained in REFs, 2-38

of object views, 16-45

primary key, 15-63

specifying, 15-63

specifying an index on, 15-64

system-generated, 15-63

object instances

types of, 5-19

object privileges

DEBUG, 17-46

FLASHBACK, 17-46

granting, 14-79

multiple, 14-86

on specific columns, 17-34

on a database object

revoking, 17-94

ON COMMIT REFRESH, 17-46

QUERY REWRITE, 17-46

revoking, 17-90

from a role, 17-88, 17-93

from a user, 17-88, 17-92

from PUBLIC, 17-93

UNDER, 17-46

object reference functions, 6-16

object tables

adding rows to, 17-53

as part of hierarchy, 15-63

creating, 15-9, 15-63

querying, 15-63

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

updating to latest version, 11-36

upgrading, 11-36

object type columns

defining properties

for materialized views, 14-12, 14-13

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

object types, 2-38

adding methods to, 12-15

adding new member subprograms, 12-11

allowing object instances of, 16-13

allowing subtypes, 16-13

and subtypes, 12-11

and supertypes, 12-11

attributes, 2-120

in a type hierarchy, 15-36

membership in hierarchy, 11-43

substitutability, 11-43

bodies

creating, 16-25

re-creating, 16-28

SQL examples, 16-30

comparison rules, 2-48

MAP function, 2-48

ORDER function, 2-48

compiling the specification and body, 12-10

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

dropping the body of, 17-18

dropping the specification of, 17-15

evolved, rebuilding references to, 9-102

function subprogram

declaring, 16-30

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

granting system privileges on, 17-42

handling dependent types, 12-18

incomplete, 16-3, 16-5

inheritance, 16-14

invalidating dependent types, 12-18

MAP methods, 16-17

methods, 2-120

nested table, 16-9

ORDER methods, 16-17

privileges on subtypes, 17-35

procedure subprogram

declaring, 16-30

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

references to. See REFs

root, specifying, 16-11

SQL examples, 16-19

static methods of, 16-13

statistics types, 12-50

subtypes, specifying, 16-11

top-level, 16-11

user-defined

creating, 16-9

values

comparing, 16-30

varrays, 16-8

object views, 16-45

base tables

adding rows, 17-53

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

setting with ALTER SYSTEM, 10-84

OBJECT_CACHE_OPTIMAL_SIZE initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-84

objects. See object types or database objects

OF clause

of CREATE VIEW, 16-45

OFFLINE clause

of ALTER ROLLBACK SEGMENT, 9-137

of ALTER TABLESPACE, 11-106

of CREATE TABLESPACE, 15-86

OIDINDEX clause

of CREATE TABLE, 15-64

OIDs. See object identifiers

OLAP_PAGE_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-84

ON clause

of CREATE OUTLINE, 14-50

ON COMMIT clause

of CREATE TABLE, 15-27

ON COMMIT REFRESH object privilege, 17-46

on a materialized view, 17-48

ON COMMIT REFRESH system privilege, 17-39

ON DATABASE clause

of CREATE TRIGGER, 15-103

ON DEFAULT clause

of AUDIT, 12-59

of NOAUDIT, 17-84

ON DELETE CASCADE clause

of constraints, 7-15

ON DELETE SET NULL clause

of constraints, 7-15

ON DIRECTORY clause

of AUDIT, 12-59

of NOAUDIT, 17-84

ON NESTED TABLE clause

of CREATE TRIGGER, 15-103

ON object clause

of NOAUDIT, 17-84

of REVOKE, 17-94

ON PREBUILT TABLE clause

of CREATE MATERIALIZED VIEW, 14-16

ON SCHEMA clause

of CREATE TRIGGER, 15-103

online backup

of tablespaces, ending, 11-108

ONLINE clause

of ALTER ROLLBACK SEGMENT, 9-137

of ALTER TABLESPACE, 11-106

of CREATE INDEX, 13-80

of CREATE TABLESPACE, 15-86

online indexes, 13-80

rebuilding, 11-87

ONLINE parameter

of ALTER INDEX ... REBUILD, 9-75

online redo logs

reinitializing, 9-41

OPEN clause

of ALTER DATABASE, 9-23

OPEN NORESETLOGS clause

of ALTER DATABASE, 9-24

OPEN READ ONLY clause

of ALTER DATABASE, 9-24

OPEN READ WRITE clause

of ALTER DATABASE, 9-23

OPEN RESETLOGS clause

of ALTER DATABASE, 9-24

OPEN_CURSORS initialization parameter

setting with ALTER SYSTEM, 10-85

OPEN_LINKS initialization parameter

setting with ALTER SYSTEM, 10-85

OPEN_LINKS_PER_INSTANCE initialization parameter

setting with ALTER SYSTEM, 10-85

operands, 3-1

operating system files

dropping, 17-12

removing, 9-37

operators, 3-1

adding to indextypes, 9-86

altering, 9-117

arithmetic, 3-3

binary, 3-2

comments on, 12-73

concatenation, 3-4

dropping from indextypes, 9-86

granting

system privileges on, 17-39

precedence, 3-2

set, 3-6, 18-24

specifying implementation of, 14-45

unary, 3-2

user-defined, 3-6

binding to a function, 14-46

creating, 14-44

dropping, 16-88

function providing implementation, 14-47

how bindings are implemented, 14-46

implementation type, 14-46

return type of binding, 14-46

user-defined, compiling, 9-117

OPTIMAL parameter

of STORAGE clause, 7-63

OPTIMIZER_DYNAMIC_SAMPLING initialization parameter

setting with ALTER SYSTEM, 10-86

OPTIMIZER_FEATURES_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-86

OPTIMIZER_INDEX_CACCHING initialization parameter

setting with ALTER SYSTEM, 10-87

OPTIMIZER_INDEX_CACHING initialization parameter

setting with ALTER SESSION, 10-9

OPTIMIZER_INDEX_COST_ADJ initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-87

OPTIMIZER_MAX_PERMUTATIONS initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-87

OPTIMIZER_MODE initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-88

OR condition, 5-8, 5-9

OR REPLACE clause

of CREATE CONTEXT, 13-13

of CREATE DIRECTORY, 13-50

of CREATE FUNCTION, 13-55, 13-99

of CREATE LIBRARY, 14-2

of CREATE OUTLINE, 14-49

of CREATE PACKAGE, 14-53

of CREATE PACKAGE BODY, 14-58

of CREATE PROCEDURE, 14-66

of CREATE TRIGGER, 15-97

of CREATE TYPE, 16-9

of CREATE TYPE BODY, 16-28

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

ORACLE_TRACE_COLLECTION_PATH initialization parameter

setting with ALTER SYSTEM, 10-88

ORACLE_TRACE_COLLECTION_SIZE initialization parameter

setting with ALTER SYSTEM, 10-89

ORACLE_TRACE_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-89

ORACLE_TRACE_FACILITY_NAME initialization parameter

setting with ALTER SYSTEM, 10-89

ORACLE_TRACE_FACILITY_PATH initialization parameter

setting with ALTER SYSTEM, 10-90

Oracle9i Text

built-in conditions, 5-2

CATSEARCH, 5-2

CONTAINS, 5-2

MATCHES, 5-2

Oracle9i Text

CATSEARCH, 5-2

creating domain indexes, 13-85

SCORE operator, 3-2

ORDER BY clause

of queries, 8-10

of SELECT, 8-10, 18-10, 18-25

with ROWNUM, 2-88

ORDER clause

of ALTER SEQUENCE. See CREATE SEQUENCE

ORDER MEMBER clause

of ALTER TYPE, 12-13

of CREATE TYPE BODY, 16-30

ORDER methods

defining for a type, 16-17

specifying, 12-13

ORDER parameter

of CREATE SEQUENCE, 14-92

ORDER SIBLINGS BY clause

of SELECT, 18-25

ORDERED hint, 2-101

ORDERED_PREDICATES hint, 2-102

ordinal numbers

specifying, 2-75

spelling out, 2-75

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

OS_ROLES initialization parameter

setting with ALTER SYSTEM, 10-90

OUT parameter

of CREATE FUNCTION, 13-57

of CREATE PROCEDURE, 14-67

outer joins, 8-12

restrictions, 8-12

outlines

assign to a different category, 9-119

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

automatically creating and storing, 10-43

copying, 14-50

creating, 14-48

creating on statements, 14-50

dropping from the database, 16-90

enabling and disabling dynamically, 14-48

for use by current session, 14-49

for use by PUBLIC, 14-49

granting

system privileges on, 17-39

private, use by the optimizer, 10-15

rebuilding, 9-118, 9-120

recompiling, 9-118

renaming, 9-118, 9-119, 9-120

replacing, 14-49

storing during the session, 10-11

storing groups of, 14-50

use by the optimizer, 10-118

use to generate execution plans, 10-16

used to generate execution plans, 14-48

out-of-line constraints

of CREATE TABLE, 15-26

OVER clause

of analytic functions, 6-10, 6-12

OVERFLOW clause

of ALTER INDEX, 9-68

of ALTER TABLE, 11-40

of CREATE TABLE, 15-32

OVERRIDING clause

of ALTER TYPE, 12-11

of CREATE TYPE, 16-14

P

package bodies

creating, 14-57

re-creating, 14-58

removing from the database, 16-91

packaged procedures

dropping, 16-93

packages

associating statistics with, 12-51, 12-52

avoiding run-time compilation, 9-121

creating, 14-52

disassociating statistics types from, 16-92

invoker rights, 14-54

recompiling explicitly, 9-121

redefining, 14-53

removing from the database, 16-91

specifying schema and privileges of, 14-54

synonyms for, 15-2

PARALLEL clause

of ALTER CLUSTER, 9-7, 9-9

of ALTER DATABASE, 9-28

of ALTER INDEX, 9-64, 9-70

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

of ALTER MATERIALIZED VIEW LOG, 9-112, 9-113

of ALTER TABLE, 11-85

of CREATE CLUSTER, 13-8

of CREATE INDEX, 13-80

of CREATE MATERIALIZED VIEW, 14-15, 14-20

of CREATE MATERIALIZED VIEW LOG, 14-37, 14-38

of CREATE TABLE, 15-21, 15-53

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

PARALLEL_ADAPTIVE_MULTI_USER initialization parameter

setting with ALTER SYSTEM, 10-91

PARALLEL_AUTOMATIC_TUNING initialization parameter

setting with ALTER SYSTEM, 10-91

PARALLEL_ENABLE clause

of CREATE FUNCTION, 13-60

PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter

setting with ALTER SYSTEM, 10-92

PARALLEL_INSTANCE_GROUP initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-92

PARALLEL_MAX_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-92

PARALLEL_MIN_PERCENT initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-93

PARALLEL_MIN_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-93

PARALLEL_THREADS_PER_CPU initialization parameter

setting with ALTER SYSTEM, 10-94

parameter files

creating, 14-62

parameters

in syntax

optional, A-4

required, A-3

PARAMETERS clause

of ALTER INDEX ... REBUILD, 9-76

of CREATE INDEX, 13-86

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

of CREATE INDEX, 7-25, 13-82, 15-60

of CREATE TABLE, 15-45

of DELETE, 16-58

of INSERT, 17-59

of LOCK TABLE, 17-74

of UPDATE, 18-63

partition_storage_clause

of ALTER TABLE, 11-14

PARTITION_VIEW_ENABLED initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-94

partitioned indexes, 2-108, 13-65, 13-83

local, creating, 13-69

user-defined, 7-24, 13-81, 15-59

partitioned index-organized tables

secondary indexes, updating, 9-80

partitioned tables, 2-108

partition-extended table names, 2-108

in DML statements, 2-109

restrictions on, 2-109

syntax, 2-109

partitioning

by range, 15-18

clauses

of ALTER INDEX, 9-66

of ALTER TABLE, 11-60

of materialized view logs, 9-113, 14-39

of materialized views, 9-99, 14-9, 14-19

partitions

adding, 11-60

adding rows to, 17-53

allocating extents for, 11-35

based on literal values, 15-48

composite, 2-108

specifying, 15-49

converting into nonpartitioned tables, 11-81

deallocating unused space from, 11-35

dropping, 11-73

exchanging with tables, 11-24

extents

allocating for an index, 9-70

hash, 2-108

adding, 11-71

coalescing, 11-72

specifying, 15-46

index, 13-81

inserting rows into, 17-59

list, adding, 11-72

LOB storage characteristics of, 11-47

locking, 17-73

logging attribute, 15-28

logging insert operations, 11-34

merging, 11-79

modifying, 11-60, 11-62

moving to a different segment, 11-67

physical attributes

changing, 11-32

range, 2-108

adding, 11-69

specifying, 15-44

removing rows from, 11-74, 16-58

renaming, 11-74

revising values in, 18-63

splitting, 11-75

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

of CREATE PROFILE, 14-76

PASSWORD_LIFE_TIME parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

PASSWORD_LOCK_TIME parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

PASSWORD_REUSE_MAX parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

PASSWORD_REUSE_TIME parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-75

PASSWORD_VERIFY_FUNCTION parameter

of ALTER PROFILE, 9-128

of CREATE PROFILE, 14-76

passwords

expiration of, 16-37

grace period, 14-75

guaranteeing complexity, 14-75

limiting use and reuse, 14-75

locking, 14-75

making unavailable, 14-75

parameters

of ALTER PROFILE, 14-76

of CREATE PROFILE, 14-72

special characters in, 14-76

PATH_VIEW, 5-13, 5-20

PCT_ACCESS_DIRECT statistics

for index-organized tables, 12-38

PCTFREE parameter

of ALTER CLUSTER, 9-8

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

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

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

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

PERCENTILE_CONT function, 6-118

PERCENTILE_DISC function, 6-121

performance

optimize for nested loop joins, 10-9

optimizing for index access path, 10-9

session optimizer approach, 10-9

PERMANENT clause

of ALTER TABLESPACE, 11-109

of CREATE TABLESPACE, 15-86

PGA_AGGREGATE_TARGET initialization parameter

setting with ALTER SYSTEM, 10-95

physical attributes clause

of ALTER CLUSTER, 9-7

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

of ALTER TABLE, 11-32

of CREATE CLUSTER, 13-3

of CREATE MATERIALIZED VIEW, 14-11

of CREATE TABLE, 15-16, 15-27

physical standby database

activating, 9-43

PIPELINED clause

of CREATE FUNCTION, 13-60

plan stability, 14-48

PLAN_TABLE sample table, 17-24

PL/SQL

compatibility with earlier releases, 10-98

program body

of CREATE FUNCTION, 13-62

PLSQL_COMPILER_FLAGS initialization parameter

setting with ALTER SESSION, 10-9

setting with ALTER SYSTEM, 10-95

PLSQL_DEBUG session parameter, 10-13

PLSQL_NATIVE_C_COMPILER initialization parameter

setting with ALTER SYSTEM, 10-96

PLSQL_NATIVE_LIBRARY_DIR initialization parameter

setting with ALTER SYSTEM, 10-96

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter

setting with ALTER SYSTEM, 10-96

PLSQL_NATIVE_LINKER initialization parameter

setting with ALTER SYSTEM, 10-97

PLSQL_NATIVE_MAKE_FILE_NAME initialization parameter

setting with ALTER SYSTEM, 10-97

PLSQL_NATIVE_MAKE_UTILITY initialization parameter

setting with ALTER SYSTEM, 10-97

PLSQL_V2_COMPATIBILITY initialization parameter

setting with ALTER SYSTEM, 10-98

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

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

POWER function, 6-122

PQ_DISTRIBUTE hint, 2-103

PR number format element, 2-64

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

precedence

of conditions, 5-3

of operators, 3-2

precision

number of digits of, 2-56

of NUMBER datatype, 2-12

precompilers

Oracle, 1-4

PRIMARY KEY clause

of constraints, 7-13

of CREATE TABLE, 15-26

primary key constraints, 7-13

enabling, 15-57

index on, 15-58

primary keys

generating values for, 14-89

PRIOR clause

of hierarchical queries, 8-3

PRIVATE clause

of CREATE OUTLINE, 14-49

private outlines

use by the optimizer, 10-15

PRIVATE_SGA parameter

of ALTER PROFILE, 9-128

of ALTER RESOURCE COST, 9-132

privileges

on subtypes of object types, 17-35

revoking from a grantee, 17-90

See also system privileges or object privileges

procedures

3GL, calling, 14-2

avoid run-time compilation, 9-125

calling, 12-68

compile explicitly, 9-125

creating, 14-62, 14-64

declaring

as a Java method, 14-68

as C functions, 14-68

executing, 12-68

external, 14-62, 14-64

running from remote database, 14-3

granting

system privileges on, 17-39

invalidating local objects dependent on, 16-93

issuing COMMIT or ROLLBACK statements, 10-3

naming rules, 2-114

privileges executed with, 12-14, 16-10

recompiling, 9-124

re-creating, 14-66

removing from the database, 16-93

schema executed in, 12-14, 16-10

specifying schema and privileges for, 14-68

synonyms for, 15-2

PROCESSES initialization parameter

setting with ALTER SYSTEM, 10-98

PROFILE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

profiles

adding resource limits, 9-127

assigning to a user, 16-36

changing resource limits, 9-127

creating, 14-71

examples, 14-76

deassigning from users, 16-95

dropping resource limits, 9-127

granting

system privileges on, 17-40

modifying, examples, 9-129

removing from the database, 16-95

proxy clause

of ALTER USER, 12-24, 12-26

pseudocolumns, 2-82

CURRVAL, 2-82

LEVEL, 2-86

NEXTVAL, 2-82

ROWID, 2-87

ROWNUM, 2-88

uses for, 2-89

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

XMLDATA, 2-89

PUBLIC clause

of CREATE OUTLINE, 14-49

of CREATE ROLLBACK SEGMENT, 14-83

of CREATE SYNONYM, 15-3

of DROP DATABASE LINK, 16-70

public database links

dropping, 16-70

public rollback segments, 14-83

public synonyms, 15-3

dropping, 17-4

PUSH_PRED hint, 2-104

Q

Q datetime format element, 2-69

queries, 8-2, 18-4

comments in, 8-3

compound, 8-10

correlated

left correlation, 18-17

defined, 8-2

distributed, 8-16

grouping returned rows on a value, 18-21

hierarchical. See hierarchical queries

hierarchical, ordering, 18-25

hints in, 8-3

join, 8-10, 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-10

select lists of, 8-2

selecting from a random sample of rows, 18-15

sorting results, 8-10

syntax, 8-2

top-level, 8-2

top-N, 2-88

query rewrite

and dimensions, 13-43

and function-based indexes, 10-10

and rule-based optimization, 10-10

defined, 18-4

disabling, 10-99

enabling, 10-99

enabling and disabling, 10-10

QUERY REWRITE object privilege, 17-46

on a materialized view, 17-48

QUERY REWRITE system privilege, 17-38

QUERY_REWRITE_ENABLED initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-99

QUERY_REWRITE_INTEGRITY initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-99

QUIESCE RESTRICTED clause

of ALTER SYSTEM, 10-29

QUOTA clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

R

range conditions, 5-12

range partitions

adding, 11-69

creating, 15-44

values of, 15-45

RANK function, 6-123

RATIO_TO_REPORT function, 6-125

RAW datatype, 2-27

converting from CHAR data, 2-28

RAWTOHEX function, 6-126

RAWTONHEX function, 6-126

RDBMS_SERVER_DN initialization parameter

setting with ALTER SYSTEM, 10-99

READ object privilege, 17-46

on a materialized directory, 17-49

READ ONLY clause

of ALTER TABLESPACE, 11-108

READ WRITE clause

of ALTER TABLESPACE, 11-108

READ_ONLY_OPEN_DELAYED initialization parameter

setting with ALTER SYSTEM, 10-100

REAL datatype (ANSI), 2-36

REBUILD clause

of ALTER INDEX, 9-65, 9-72

of ALTER MATERIALIZED VIEW, 9-102

of ALTER OUTLINE, 9-118

REBUILD PARTITION clause

of ALTER INDEX, 9-73

REBUILD SUBPARTITION clause

of ALTER INDEX, 9-73

REBUILD UNUSABLE LOCAL INDEXES clause

of ALTER TABLE, 11-84

rebuilding, 9-102

RECOVER AUTOMATIC clause

of ALTER DATABASE, 9-26

RECOVER CANCEL clause

of ALTER DATABASE, 9-13, 9-29

RECOVER clause

of ALTER DATABASE, 9-25

RECOVER CONTINUE clause

of ALTER DATABASE, 9-13, 9-29

RECOVER DATABASE clause

of ALTER DATABASE, 9-13, 9-26

RECOVER DATAFILE clause

of ALTER DATABASE, 9-13, 9-27

RECOVER LOGFILE clause

of ALTER DATABASE, 9-13, 9-28

RECOVER MANAGED STANDBY DATABASE clause

of ALTER DATABASE, 9-15

RECOVER STANDBY DATAFILE clause

of ALTER DATABASE, 9-27

RECOVER STANDBY TABLESPACE clause

of ALTER DATABASE, 9-27

RECOVER TABLESPACE clause

of ALTER DATABASE, 9-13, 9-27

RECOVERABLE, 9-72, 15-30

See also LOGGING clause

recovery

discarding data, 9-23

distributed, enabling, 10-27

instance, continue after interruption, 9-25

media, designing, 9-25

media, performing ongoing, 9-29

of database, 9-13

parallelizing, 9-28

recovery clauses

of ALTER DATABASE, 9-13

RECOVERY_CATALOG_OWNER role, 17-45

RECOVERY_PARALLELISM initialization parameter

setting with ALTER SYSTEM, 10-100

redo allocation latch

avoiding high contention, 10-75

redo log files

specifying, 7-39

specifying for a controlfile, 13-17

redo logs, 9-23

adding, 9-37, 9-38

applying to logical standby database, 9-46

archive location, 10-25

automatic archiving, 10-23

starting, 10-25

stopping, 10-25

automatic name generation, 9-25, 9-26

clearing, 9-37

disabling specified threads in a cluster database, 9-51

dropping, 9-37, 9-39

enabling and disabling thread, 9-37

enabling specified threads in a cluster database, 9-50

manual archiving, 10-23

all, 10-25

by group number, 10-24

by SCN, 10-23

current, 10-24

next, 10-25

with sequence numbers, 10-23

members

adding to existing groups, 9-39

dropping, 9-40

renaming, 9-37

remove changes from, 9-23

reusing, 7-41

size of, 7-41

specifying, 7-39, 13-27

for media recovery, 9-28

specifying archive mode, 13-29

switching groups, 10-29

threads, 10-23

REF columns

rescoping, 9-102

specifying, 15-26

specifying from table or column level, 15-26

REF constraints

defining scope, for materialized views, 9-97

of ALTER TABLE, 11-43

REF function, 6-127

REFERENCES clause

of CREATE TABLE, 15-26

REFERENCES object privilege, 17-46

on a table, 17-47

on a view, 17-47

REFERENCING clause

of CREATE TRIGGER, 15-97, 15-104

referential integrity constraints, 7-14

REFRESH clause

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

of CREATE MATERIALIZED VIEW, 14-11

REFRESH COMPLETE clause

of ALTER MATERIALIZED VIEW, 9-103

of CREATE MATERIALIZED VIEW, 14-21

REFRESH FAST clause

of ALTER MATERIALIZED VIEW, 9-102

of CREATE MATERIALIZED VIEW, 14-21

REFRESH FORCE clause

of ALTER MATERIALIZED VIEW, 9-103

of CREATE MATERIALIZED VIEW, 14-21

REFRESH ON COMMIT clause

of ALTER MATERIALIZED VIEW, 9-103

of CREATE MATERIALIZED VIEW, 14-21

REFRESH ON DEMAND clause

of ALTER MATERIALIZED VIEW, 9-104

of CREATE MATERIALIZED VIEW, 14-21

REFs, 2-38, 7-16

as containers for OIDs, 2-38

dangling, 12-43

updating, 12-43

validating, 12-43

REFTOHEX function, 6-128

of ALTER SYSTEM, 10-31

of ALTER DATABASE, 9-45

REGR_AVGX function, 6-129

REGR_AVGY function, 6-129

REGR_COUNT function, 6-129

REGR_INTERCEPT function, 6-129

REGR_R2 function, 6-129

REGR_SLOPE function, 6-129

REGR_SXX function, 6-129

REGR_SXY function, 6-129

REGR_SYY function, 6-129

relational tables

creating, 15-8, 15-23

RELY clause

of constraints, 7-22

REMOTE_ARCHIVE_ENABLE initialization parameter

setting with ALTER SYSTEM, 10-100

REMOTE_DEPENDENCIES_MODE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-101

REMOTE_LISTENER initialization parameter

setting with ALTER SYSTEM, 10-101

REMOTE_LOGIN_PASSWORDFILE initialization parameter

and control files, 13-16

and databases, 13-24

setting with ALTER SYSTEM, 10-101

REMOTE_OS_AUTHENT initialization parameter

setting with ALTER SYSTEM, 10-102

REMOTE_OS_ROLES initialization parameter

setting with ALTER SYSTEM, 10-102

RENAME clause

of ALTER INDEX, 9-78

of ALTER OUTLINE, 9-119

of ALTER TABLE, 11-38

of ALTER TRIGGER, 12-3

RENAME CONSTRAINT clause

of ALTER TABLE, 11-59

RENAME DATAFILE clause

of ALTER TABLESPACE, 11-104

RENAME FILE clause

of ALTER DATABASE, 9-12, 9-37

RENAME GLOBAL_NAME clause

of ALTER DATABASE, 9-51

RENAME PARTITION clause

of ALTER INDEX, 9-67, 9-81

of ALTER TABLE, 11-74

RENAME statement, 17-86

RENAME SUBPARTITION clause

of ALTER INDEX, 9-67, 9-81

of ALTER TABLE, 11-74

REPLACE AS OBJECT clause

of ALTER TYPE, 12-11

REPLACE function, 6-137

replication

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

REPLICATION_DEPENDENCY_TRACKING initialization parameter

setting with ALTER SYSTEM, 10-102

reserved words, 2-111, C-1

RESET COMPATIBILITY clause

of ALTER DATABASE, 9-50

reset sequence of, 9-23

RESETLOGS parameter

of CREATE CONTROLFILE, 13-18

RESOLVE clause

of ALTER JAVA CLASS, 9-88

of CREATE JAVA, 13-100

RESOLVER clause

of ALTER JAVA CLASS, 9-88

of ALTER JAVA SOURCE, 9-88

of CREATE JAVA, 13-102

Resource Manager, 10-29

resource parameters

of CREATE PROFILE, 14-72

RESOURCE role, 17-45

RESOURCE_LIMIT initialization parameter

setting with ALTER SYSTEM, 10-102

RESOURCE_MANAGER_PLAN initialization parameter

setting with ALTER SYSTEM, 10-103

RESOURCE_VIEW, 5-13, 5-20

response time

optimizing, 2-96

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

resumable space allocation, 10-6

RESUMABLE system privilege, 17-44

RESUME clause

of ALTER SYSTEM, 10-29

RETENTION parameter

of LOB storage, 15-39

RETURN clause

of CREATE FUNCTION, 13-57

of CREATE OPERATOR, 14-46

of CREATE TYPE, 16-14

of CREATE TYPE BODY, 16-30

RETURNING clause

of DELETE, 16-61

of INSERT, 17-55, 17-62

of UPDATE, 18-61, 18-67

REUSE clause

of CREATE CONTROLFILE, 13-18

of file specifications, 7-41

REUSE SETTINGS clause

of ALTER FUNCTION, 9-60

of ALTER PACKAGE, 9-122

of ALTER PROCEDURE, 9-125

of ALTER TRIGGER, 12-4

of ALTER TYPE, 12-11

REVERSE clause

of CREATE INDEX, 13-79

reverse indexes, 13-79

REVERSE parameter

of ALTER INDEX ... REBUILD, 9-74

REVOKE clause

of ALTER USER, 12-27

REVOKE CONNECT THROUGH clause

of ALTER USER, 12-24, 12-26

REVOKE statement, 17-88

REWRITE hint, 2-104

right outer joins, 18-18

RM datetime format element, 2-69

RN number format element, 2-64

RNDS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

RNPS attribute

of PRAGMA RESTRICT_REFERENCES, 16-16

roles

application, 9-135

AQ_ADMINISTRATOR_ROLE, 17-45

AQ_USER_ROLE, 17-45

authorization

by a password, 14-80

by an external service, 14-80

by the database, 14-80

by the enterprise directory service, 14-80

changing, 9-134

CONNECT, 17-45

creating, 14-79

DBA, 17-45

DELETE_CATALOG_ROLE, 17-45

disabling

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

effect on user sessions, 9-135

enabling

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

EXECUTE_CATALOG_ROLE, 17-45

EXP_FULL_DATABASE, 17-45

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

identifying by password, 14-80

identifying externally, 14-80

identifying through enterprise directory service, 14-80

identifying using a package, 14-80

IMP_FULL_DATABASE, 17-45

RECOVERY_CATALOG_OWNER, 17-45

removing from the database, 16-97

RESOURCE, 17-45

revoking, 17-88

from another role, 16-97, 17-91

from PUBLIC, 17-91

from users, 16-97, 17-91

SELECT_CATALOG_ROLE, 17-45

SNMPAGENT, 17-45

rollback segments

bringing online, 9-136, 9-137

changing storage characteristics, 9-136, 9-137

creating, 14-82

granting

system privileges on, 17-40

public, 14-83

reducing size, 9-136, 9-138

removing from the database, 16-98

specifying optimal size of, 7-63

specifying tablespaces for, 14-84

SQL examples, 14-85

storage characteristics, 14-84

system-generated, 14-82

taking offline, 9-136, 9-137

ROLLBACK statement, 17-99

rollback undo, 9-136, 13-33

ROLLBACK_SEGMENTS initialization parameter

setting with ALTER SYSTEM, 10-103

ROLLUP clause

of SELECT statements, 18-22

ROUND function

date function, 6-139

format models, 6-221

number function, 6-138

routines

calling, 12-68

executing, 12-68

ROW EXCLUSIVE lock mode, 17-75

ROW SHARE lock mode, 17-75

ROW_LOCKING initialization parameter

setting with ALTER SYSTEM, 10-104

ROW_NUMBER function, 6-139

ROWDEPENDENCIES clause

of CREATE CLUSTER, 13-9

of CREATE TABLE, 15-54

ROWID datatype, 2-33

ROWID hint, 2-104

ROWID pseudocolumn, 2-33, 2-35, 2-87

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

ROWIDTOCHAR function, 6-141

ROWIDTONCHAR function, 6-141

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

ROWNUM pseudocolumn, 2-88

uses for, 2-89

rows

adding to a table, 17-53

allowing movement of between partitions, 15-15

inserting

into partitions, 17-59

into remote databases, 17-59

into subpartitions, 17-59

movement between partitions, 15-60

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

RPAD function, 6-142

RR datetime format element, 2-69, 2-73

RRRR datetime format element, 2-69

RTRIM function, 6-143

RULE hint, 2-105

run-time compilation

avoiding, 9-124, 12-31

S

S number format element, 2-64

SAMPLE clause

of SELECT, 18-15

of SELECT and subqueries, 18-7

SAVEPOINT statement, 18-2

savepoints

erasing, 12-75

rolling back to, 17-100

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

SCHEMA clause

of CREATE JAVA, 13-101

schema objects, 2-106

auditing

options, 12-64

defining default buffer pool for, 7-63

dropping, 17-20

in other schemas, 2-117

list of, 2-106

name resolution, 2-116

namespaces, 2-112

naming

examples, 2-114

guidelines, 2-114

rules, 2-110

object types, 2-38

on remote databases, 2-118

partitioned indexes, 2-108

partitioned tables, 2-108

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-115, 10-11

remote, accessing, 13-37

validating structure, 12-43

schemas

changing for a session, 10-11

creating, 14-86

definition of, 2-106

scientific notation, 2-65

SCOPE FOR clause

of ALTER MATERIALIZED VIEW, 9-97

of CREATE MATERIALIZED VIEW, 14-16

SCORE operator, 3-2

security

enforcing, 15-95

segment attributes clause

of CREATE TABLE, 15-15

SEGMENT MANAGEMENT FREELISTS clause

of CREATE TABLESPACE, 15-88

SEGMENT MANAGEMENT PAGETABLE clause

of CREATE TABLESPACE, 15-88

segments

space management

automatic, 15-88

manual, 15-88

using bitmaps, 15-88

using free lists, 15-88

SELECT ANY DICTIONARY system privilege, 17-44

SELECT ANY SEQUENCE system privilege, 17-40

SELECT ANY TABLE system privilege, 17-41

select lists, 8-2

ordering, 8-10

SELECT object privilege, 17-46

on a materialized view, 17-48

on a sequence, 17-48

on a table, 17-47

on a view, 17-47

SELECT statement, 8-2, 18-4

SELECT_CATALOG_ROLE role, 17-45

self joins, 8-11

sequences, 2-82, 14-89

accessing values of, 14-89

changing

the increment value, 9-140

creating, 14-89

creating without limit, 14-91

granting

system privileges on, 17-40

guarantee consecutive values, 14-92

how to use, 2-84

increment value, setting, 14-91

incrementing, 14-89

initial value, setting, 14-91

maximum value

eliminating, 9-140

setting, 14-91

setting or changing, 9-140

minimum value

eliminating, 9-140

setting, 14-92

setting or changing, 9-140

number of cached values, changing, 9-140

ordering values, 9-140

preallocating values, 14-92

recycling values, 9-140

removing from the database, 17-2

renaming, 17-86

restarting, 17-2

at a different number, 9-141

at a predefined limit, 14-91

values, 14-92

reusing, 14-89

stopping at a predefined limit, 14-91

synonyms for, 15-2

where to use, 2-83

SERIAL_REUSE initialization parameter

setting with ALTER SYSTEM, 10-104

server parameter files

creating, 14-94

SERVERERROR event

triggers on, 15-102

service name

of remote database, 13-40

SERVICE_NAMES initialization parameter

setting with ALTER SYSTEM, 10-104

session control statements, 9-4

PL/SQL support of, 9-4

session locks

releasing, 10-27

session parameters

changing settings, 10-10

INSTANCE, 10-12

PLSQL_DEBUG, 10-13

SESSION_CACHED_CURSORS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-105

SESSION_MAX_OPEN_FILES initialization parameter

setting with ALTER SYSTEM, 10-105

SESSION_ROLES view, 18-47

sessions

affecting with roles, 9-135

calculating resource cost limits, 9-131

changing resource cost limits, 9-131

disconnecting, 10-26

global name resolution for, 10-8

limiting CPU time, 9-131

limiting data block reads, 9-132

limiting inactive periods, 9-127

limiting private SGA space, 9-132

limiting resource costs, 9-131

limiting total elapsed time, 9-132

limiting total resources, 9-127

modifying characteristics of, 10-6

number of concurrent, 10-66

object cache, 10-9

restricting, 10-29

restricting to privileged users, 10-28

switching to a different instance, 10-12

terminating, 10-27

time zone setting, 10-14

SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-106

SESSIONS_PER_USER parameter

of ALTER PROFILE, 9-128

SESSIONTIMEZONE function, 6-143

SET clause

of ALTER SESSION, 10-6

of ALTER SYSTEM, 10-31

of UPDATE, 18-65

SET CONSTRAINT(S) statement, 18-45

SET DANGLING TO NULL clause

of ANALYZE, 12-43

SET DATABASE clause

of CREATE CONTROLFILE, 13-18

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

SET STATEMENT_ID clause

of EXPLAIN PLAN, 17-26

SET TIME_ZONE clause

of ALTER DATABASE, 9-22, 9-48

of ALTER SESSION, 10-14

of CREATE DATABASE, 13-26

SET TRANSACTION statement, 18-50

SET UNUSED clause

of ALTER TABLE, 11-52

SGA. See system global area (SGA)

SGA_MAX_SIZE initialization parameter

setting with ALTER SYSTEM, 10-106

SHADOW_CORE_DUMP initialization parameter

setting with ALTER SYSTEM, 10-106

SHARE ROW EXCLUSIVE lock mode, 17-75

SHARE UPDATE lock mode, 17-75

SHARED clause

of CREATE DATABASE LINK, 13-38

shared pool

flushing, 10-28

shared server

parameters

DISPATCHERS, 10-53

processes

creating additional, 10-108

terminating, 10-108

system parameters, 10-108

SHARED_MEMORY_ADDRESS initialization parameter

setting with ALTER SYSTEM, 10-107

SHARED_POOL_RESERVED_SIZE initialization parameter

setting with ALTER SYSTEM, 10-107

SHARED_POOL_SIZE initialization parameter

setting with ALTER SYSTEM, 10-107

SHARED_SERVER_SESSIONS initialization parameter

setting with ALTER SYSTEM, 10-109

SHARED_SERVERS initialization parameter

setting with ALTER SYSTEM, 10-108

SHRINK clause

of ALTER ROLLBACK SEGMENT, 9-138

SHUTDOWN clause

of ALTER SYSTEM, 10-30

SHUTDOWN event

triggers on, 15-102

siblings

ordering in a hierarchical query, 18-25

SIGN function, 6-144

simple comparison conditions, 5-5

simple expressions, 4-3

SIN function, 6-145

SINGLE TABLE clause

of CREATE CLUSTER, 13-7

single-row functions, 6-3

miscellaneous, 6-7

single-table insert, 17-57

SINH function, 6-145

SIZE clause

of ALTER CLUSTER, 9-8

of CREATE CLUSTER, 13-5

of file specifications, 7-41

SKIP_UNUSABLE_INDEXES session parameter, 10-13

SMALLINT datatype

ANSI, 2-36

DB2, 2-37

SQL/DS, 2-37

SNMPAGENT role, 17-45

SOME operator, 5-5

sort operations

changing linguistic sequence, 10-9

SORT_AREA_RETAINED_SIZE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-109

SORT_AREA_SIZE initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-110

SOUNDEX function, 6-146

SP datetime format element suffix, 2-75

special characters

in passwords, 14-76

SPECIFICATION clause

of ALTER PACKAGE, 9-121

spelled numbers

specifying, 2-75

SPFILE initialization parameter

setting with ALTER SYSTEM, 10-110

SPLIT PARTITION clause

of ALTER INDEX, 9-67, 9-81

of ALTER TABLE, 11-75

SPTH datetime format element suffix, 2-75

SQL functions

ABS, 6-17

ACOS, 6-17

ADD_MONTHS, 6-18

aggregate, 6-8

analytic, 6-10

applied to LOB columns, 6-2

ASCII, 6-18

ASCIISTR, 6-19

ASIN, 6-20

ATAN, 6-21

ATAN2, 6-21

AVG, 6-22

BFILENAME, 6-23

BIN_TO_NUM, 6-25

BITAND, 6-25

CAST, 6-27

CEIL, 6-30

character

returning character values, 6-4

returning number values, 6-5

CHARTOROWID, 6-30

CHR, 6-31

COALESCE, 6-33

COMPOSE, 6-34

CONCAT, 6-35

conversion, 6-6

CONVERT, 6-36

CORR, 6-37

COS, 6-39

COSH, 6-40

COUNT, 6-40

COVAR_POP, 6-42

COVAR_SAMP, 6-44

CUME_DIST, 6-47

CURRRENT_DATE, 6-49

CURRRENT_TIMESTAMP, 6-50

date, 6-5

DBTIMEZONE, 6-51

DECOMPOSE, 6-53

DENSE_RANK, 6-55

DEREF, 6-58

DUMP, 6-59

EMPTY_BLOB, 6-61

EMPTY_CLOB, 6-61

EXISTSNODE, 6-61

EXP, 6-62

EXTRACT (datetime), 6-63

EXTRACT (XML), 6-65

EXTRACTXML, 6-66

FIRST, 6-67

FIRST_VALUE, 6-69

FLOOR, 6-71

FROM_TZ, 6-71

GREATEST, 6-72

GROUP_ID, 6-72

GROUPING, 6-74

GROUPING_ID, 6-75

HEXTORAW, 6-77

INITCAP, 6-77

INSTR, 6-78

INSTR2, 6-78

INSTR4, 6-78

INSTRB, 6-78

INSTRC, 6-78

LAG, 6-80

LAST, 6-81

LAST_DAY, 6-83

LAST_VALUE, 6-84

LEAD, 6-86

LEAST, 6-87

LENGTH, 6-88

LENGTH2, 6-88

LENGTH4, 6-88

LENGTHB, 6-88

LENGTHC, 6-88

linear regression, 6-129

LN, 6-89

LOCALTIMESTAMP, 6-90

LOG, 6-91

LOWER, 6-91

LPAD, 6-92

LTRIM, 6-93

MAKE_REF, 6-94

MAX, 6-95

MIN, 6-97

MOD, 6-98

MONTHS_BETWEEN, 6-99

NCHR, 6-100

NEW_TIME, 6-100

NEXT_DAY, 6-102

NLS_CHARSET_DECL_LEN, 6-102

NLS_CHARSET_ID, 6-103

NLS_CHARSET_NAME, 6-104

NLS_INITCAP, 6-104

NLS_LOWER, 6-106

NLS_UPPER, 6-108

NLSSORT, 6-107

NLV2, 6-114

NTILE, 6-109

NULLIF, 6-110

number, 6-3

NUMTODSINTERVAL, 6-111

NUMTOYMINTERVAL, 6-112

NVL, 6-113

object reference, 6-16

PERCENT_RANK, 6-116

PERCENTILE_CONT, 6-118

PERCENTILE_DISC, 6-121

POWER, 6-122

RANK, 6-123

RATIO_TO_REPORT, 6-125

RAWTOHEX, 6-126

RAWTONHEX, 6-126

REF, 6-127

REFTOHEX, 6-128

REGR_AVGX, 6-129

REGR_AVGY, 6-129

REGR_COUNT, 6-129

REGR_INTERCEPT, 6-129

REGR_R2, 6-129

REGR_SLOPE, 6-129

REGR_SXX, 6-129

REGR_SXY, 6-129

REGR_SYY, 6-129

REPLACE, 6-137

ROUND (date), 6-139

ROUND (number), 6-138

ROW_NUMBER, 6-139

ROWIDTOCHAR, 6-141

ROWIDTONCHAR, 6-141

RPAD, 6-142

RTRIM, 6-143

SESSIONTIMEZONE, 6-143

SIGN, 6-144

SIN, 6-145

single-row, 6-3

miscellaneous, 6-7

SINH, 6-145

SOUNDEX, 6-146

SQRT, 6-147

STDDEV, 6-148

STDDEV_POP, 6-149

STDDEV_SAMP, 6-151

SUBSTR, 6-152

SUBSTR2, 6-152

SUBSTR4, 6-152

SUBSTRB, 6-152

SUBSTRC, 6-152

SUM, 6-154

SYS_CONNECT_BY_PATH, 6-155

SYS_CONTEXT, 6-156

SYS_DBURIGEN, 6-161

SYS_EXTRACT_UTC, 6-162

SYS_GUID, 6-163

SYS_TYPEID, 6-164

SYS_XMLAGG, 6-165

SYS_XMLGEN, 6-166

SYSDATE, 6-167

SYSTIMESTAMP, 6-168

TAN, 6-169

TANH, 6-169

TO_CHAR (character), 6-170

TO_CHAR (datetime), 6-171

TO_CHAR (number), 6-173

TO_CLOB, 6-175

TO_DATE, 6-175

TO_DSINTERVAL, 6-177

TO_LOB, 6-178

TO_MULTI_BYTE, 6-179

TO_NCHAR (character), 6-180

TO_NCHAR (datetime), 6-181

TO_NCHAR (number), 6-182

TO_NCLOB, 6-182

TO_NUMBER, 6-183

TO_SINGLE_BYTE, 6-184

TO_TIMESTAMP, 6-185

TO_YMINTERVAL, 6-187

TRANSLATE, 6-188

TRANSLATE...USING, 6-189

TREAT, 6-191

TRIM, 6-192

TRUNC (date), 6-194

TRUNC (number), 6-194

TZ_OFFSET, 6-195

UID, 6-196

UNISTR, 6-196

UPDATEXML, 6-197

UPPER, 6-199

USER, 6-199

USERENV, 6-200

VALUE, 6-202

VAR_POP, 6-202

VAR_SAMP, 6-204

VARIANCE, 6-206

VSIZE, 6-207

WIDTH_BUCKET, 6-208

SQL statements

auditing

by access, 12-59

by proxy, 12-58

by session, 12-59

by user, 12-57

stopping, 17-81

successful, 12-60

DDL, 9-2

determining the execution plan for, 17-24

DML, 9-3

organization of, 9-4

rolling back, 17-99

session control, 9-4

space allocation, resumable, 10-6

suspending and completing, 10-6

system control, 9-4

tracking the occurrence in a session, 12-54

transaction control, 9-3

type of, 9-2

undoing, 17-99

SQL*Loader inserts, logging, 9-71

SQL:99 standards, 1-2

SQL_TRACE initialization parameter

setting with ALTER SYSTEM, 10-111

SQL_TRACE session parameter, 10-14

SQL92_SECURITY initialization parameter

setting with ALTER SYSTEM, 10-111

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

SS datetime format element, 2-69

SSSSS datetime format element, 2-69

standalone procedures

dropping, 16-93

standard SQL, B-1

Oracle extensions to, B-11

standby database

recovering, 9-27

standby databases

activating, 9-43

applying archive logs, 9-31

committing to primary status, 9-45

controlling use, 9-51

designing media recovery, 9-25

mounting, 9-23

recovering, 9-26, 9-27

STANDBY_ARCHIVE_DEST initialization parameter

setting with ALTER SYSTEM, 10-111

STANDBY_FILE_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 10-112

star transformation, 2-105

STAR_TRANSFORMATION hint, 2-105

STAR_TRANSFORMATION_ENABLED initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-112

START LOGICAL STANDBY APPLY clause

of ALTER DATABASE, 9-46

START WITH clause

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

of queries and subqueries, 18-21

of SELECT and subqueries, 18-8

START WITH parameter

of CREATE SEQUENCE, 14-91

STARTUP event

triggers on, 15-102

startup_clauses

of ALTER DATABASE, 9-13

STATIC clause

of ALTER TYPE, 12-12

of CREATE TYPE, 16-13

of CREATE TYPE BODY, 16-28

statistics

collection during index rebuild, 9-72

computing exactly, 12-39

deleting from the data dictionary, 12-46

estimating, 12-42

forcing disassociation, 16-66

on index usage, 9-78

on indexes, 13-80

on scalar object attributes

collecting, 12-34

on schema objects

collecting, 12-34

deleting, 12-34

user-defined

dropping, 16-78, 16-79, 16-92, 17-7, 17-15

statistics types

associating

with columns, 12-52

associating with datatypes, 12-51, 12-52

associating with domain indexes, 12-51, 12-52

associating with functions, 12-51, 12-52

associating with indextypes, 12-51, 12-52

associating with packages, 12-51, 12-52

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

setting with ALTER SYSTEM, 10-112

STDDEV function, 6-148

STDDEV_POP function, 6-149

STDDEV_SAMP function, 6-151

STOP LOGICAL STANDBY clause

of ALTER DATABASE, 9-47

STORAGE clause

of ALTER CLUSTER, 9-8

of ALTER INDEX, 9-64, 9-71

of ALTER MATERIALIZED VIEW LOG, 9-112

of ALTER ROLLBACK SEGMENT, 9-136, 9-137

of CREATE MATERIALIZED VIEW LOG, 14-37

of CREATE MATERIALIZED VIEW LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW. See CREATE TABLE.

of CREATE ROLLBACK SEGMENTS, 14-84

of CREATE TABLE, 7-55, 15-11

of CREATE TABLESPACE, 15-82

STORAGE IN ROW clause

of ALTER TABLE, 11-46

storage parameters

default, changing, 11-106

resetting, 18-54

STORE IN clause

of ALTER TABLE, 11-40, 15-47

stored functions, 13-52

strings

converting to ASCII values, 6-19

converting to unicode, 6-34

Structured Query Language (SQL)

description, 1-2

embedded, 1-4

functions, 6-2

keywords, A-3

Oracle Tools support of, 1-5

parameters, A-3

standards, 1-2, B-1

statements

auditing, 12-60

determining the cost of, 17-24

syntax, 9-4, A-1

SUBPARTITION BY HASH clause

of CREATE TABLE, 15-19, 15-50

SUBPARTITION BY LIST clause

of CREATE TABLE, 15-51

SUBPARTITION clause

of ANALYZE, 12-39

of DELETE, 16-58

of INSERT, 17-59

of LOCK TABLE, 17-74

of UPDATE, 18-63

subpartition template

creating, 11-62

replacing, 11-62

subpartition-extended table names, 2-108

in DML statements, 2-109

restrictions on, 2-109

syntax, 2-109

subpartitions

adding, 11-63

adding rows to, 17-53

allocating extents for, 11-35, 11-66

coalescing, 11-64

converting into nonpartitioned tables, 11-81

creating, 15-19

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

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

exchanging with tables, 11-24

hash, 15-50

inserting rows into, 17-59

list, 15-51

list, adding, 11-63

locking, 17-73

logging insert operations, 11-34

moving to a different segment, 11-68

physical attributes

changing, 11-32

removing rows from, 11-74, 16-58

renaming, 11-74

revising values in, 18-63

specifying, 15-49

template, creating, 15-49

template, dropping, 11-62

template, replacing, 11-62

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

assigning names to, 18-10

containing subqueries, 8-14

correlated, 8-14

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

unnesting, 8-15

using in place of expressions, 4-13

SUBSTR function, 6-152

SUBSTR2 function, 6-152

SUBSTR4 function, 6-152

SUBSTRB function, 6-152

SUBSTRC function, 6-152

subtotal values

deriving, 18-22

subtypes, 12-11

dropping safely, 17-16

SUM function, 6-154

supertypes, 12-11

supplemental logging

identification key (full), 9-40

minimal, 9-40

SUSPEND clause

of ALTER SYSTEM, 10-29

sustained standby recovery mode, 9-29

SWITCH LOGFILE clause

of ALTER SYSTEM, 10-29

SYEAR datetime format element, 2-69

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-86, 17-87

synonyms for, 15-2

syntax diagrams, A-1

loops, A-4

multipart diagrams, A-5

SYS schema

auditing, 10-35

database triggers stored in, 15-106

functions stored in, 15-106

SYS user

assigning password for, 13-27

SYS_CONNECT_BY_PATH function, 6-155

SYS_CONTEXT function, 6-156

SYS_DBURIGEN function, 6-161

SYS_EXTRACT_UTC function, 6-162

SYS_GUID function, 6-163

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

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

SYS_TYPEID function, 6-164

SYS_XMLAGG function, 6-165

SYS_XMLGEN function, 6-166

SYSDATE function, 6-167

SYSDBA system privilege, 17-44

SYSOPER system privilege, 17-44

system control statements, 9-4

PL/SQL support of, 9-4

system date

altering, 10-59

system events

attributes of, 15-106

triggers on, 15-102

system global area

flushing, 10-28

updating, 10-26

system privileges

ADMINISTER DATABASE TRIGGER, 17-42

ALTER ANY CLUSTER, 17-36

ALTER ANY DIMENSION, 17-37

ALTER ANY INDEX, 17-38

ALTER ANY INDEXTYPE, 17-38

ALTER ANY MATERIALIZED VIEW, 17-38

ALTER ANY OUTLINE, 17-39

ALTER ANY PROCEDURE, 17-39

ALTER ANY ROLE, 17-40

ALTER ANY SEQUENCE, 17-40

ALTER ANY TABLE, 17-41

ALTER ANY TRIGGER, 17-42

ALTER ANY TYPE, 17-42

ALTER DATABASE, 17-37

ALTER PROFILE, 17-40

ALTER RESOURCE COST, 17-40

ALTER ROLLBACK SEGMENT, 17-40

ALTER SESSION, 17-40

ALTER SYSTEM, 17-37

ALTER TABLESPACE, 17-42

ALTER USER, 17-43

ANALYZE ANY, 17-43

AUDIT ANY, 17-43

AUDIT SYSTEM, 17-37

BACKUP ANY TABLE, 17-41

BECOME USER, 17-43

COMMENT ANY TABLE, 17-43

CREATE ANY CLUSTER, 17-36

CREATE ANY CONTEXT, 17-36

CREATE ANY DIMENSION, 17-37

CREATE ANY DIRECTORY, 17-37

CREATE ANY INDEX, 17-38

CREATE ANY INDEXTYPE, 17-38

CREATE ANY LIBRARY, 17-38

CREATE ANY MATERIALIZED VIEW, 17-38

CREATE ANY OPERATOR, 17-39

CREATE ANY OUTLINE, 17-39

CREATE ANY PROCEDURE, 17-39

CREATE ANY SEQUENCE, 17-40

CREATE ANY SYNONYM, 17-41

CREATE ANY TABLE, 17-41

CREATE ANY TRIGGER, 17-42

CREATE ANY TYPE, 17-42

CREATE ANY VIEW, 17-43

CREATE CLUSTER, 17-36

CREATE DATABASE LINK, 17-37

CREATE DIMENSION, 17-37

CREATE INDEXTYPE, 17-37

CREATE LIBRARY, 17-38

CREATE MATERIALIZED VIEW, 17-38

CREATE OPERATOR, 17-39

CREATE PROCEDURE, 17-39

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

CREATE SYNONYM, 17-41

CREATE TABLE, 17-41

CREATE TABLESPACE, 17-42

CREATE TRIGGER, 17-42

CREATE TYPE, 17-42

CREATE USER, 17-43

CREATE VIEW, 17-43

DEBUG ANY PROCEDURE, 17-37

DELETE ANY TABLE, 17-41

DROP ANY CLUSTER, 17-36

DROP ANY CONTEXT, 17-37

DROP ANY DIMENSION, 17-37

DROP ANY DIRECTORY, 17-37

DROP ANY INDEX, 17-38

DROP ANY INDEXTYPE, 17-38

DROP ANY LIBRARY, 17-38

DROP ANY MATERIALIZED VEIW, 17-38

DROP ANY OPERATOR, 17-39

DROP ANY OUTLINE, 17-39

DROP ANY PROCEDURE, 17-39

DROP ANY ROLE, 17-40

DROP ANY SEQUENCE, 17-40

DROP ANY SYNONYM, 17-41

DROP ANY TABLE, 17-41

DROP ANY TRIGGER, 17-42

DROP ANY TYPE, 17-42

DROP ANY VIEW, 17-43

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

EXECUTE ANY TYPE, 17-42

EXEMPT ACCESS POLICY, 17-44

FLASHBACK ANY TABLE, 17-39, 17-41, 17-43

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-79, 17-29

to a role, 17-31

to a user, 17-31

to PUBLIC, 17-32

INSERT ANY TABLE, 17-41

list of, 17-36

LOCK ANY TABLE, 17-41

MANAGE TABLESPACE, 17-42

ON COMMIT REFRESH, 17-39

QUERY REWRITE, 17-38

RESTRICTED SESSION, 17-37, 17-40

RESUMABLE, 17-44

revoking, 17-88

from a role, 17-90

from a user, 17-90

from PUBLIC, 17-91

SELECT ANY DICTIONARY, 17-44

SELECT ANY SEQUENCE, 17-40

SELECT ANY TABLE, 17-41

SYSDBA, 17-44

SYSOPER, 17-44

UNDER ANY TYPE, 17-42

UNDER ANY VIEW, 17-43

UNLIMITED TABLESPACE, 17-42

UPDATE ANY TABLE, 17-41

system resources

enabling and disabling, 10-102

SYSTEM tablespace

locally managed, 13-31

SYSTEM user

assigning password for, 13-27

SYSTIMESTAMP function, 6-168

SYYYY datetime format element, 2-69

T

table

XMLType, querying, 15-64

TABLE clause

of ANALYZE, 12-37

of DELETE, 16-60

of INSERT, 17-60

of SELECT, 18-17

of TRUNCATE, 18-55

of UPDATE, 18-62, 18-64, 18-65

table functions

creating, 13-60

table locks

disabling, 11-89

duration of, 17-73

enabling, 11-89

EXCLUSIVE, 17-74, 17-75

modes of, 17-75

on partitions, 17-74

on remote database, 17-75

on subpartitions, 17-74

and queries, 17-73

ROW EXCLUSIVE, 17-74, 17-75

ROW SHARE, 17-74, 17-75

SHARE, 17-74

SHARE ROW EXCLUSIVE, 17-75

SHARE UPDATE, 17-75

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

aliases, 2-120

in CREATE INDEX, 13-74

in DELETE, 16-60

allocating extents for, 11-35

assigning to a cluster, 15-35

changing degree of parallelism on, 11-85

changing existing values in, 18-59

collecting statistics on, 11-36, 12-37

comments on, 12-73

creating, 15-7

multiple, 14-86

creating comments about, 12-72

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

inserting using the direct-path method, 17-53

joining in a query, 18-18

LOB storage of, 7-55

locking, 17-73

logging

insert operations, 11-34

table creation, 15-28

migrated and chained rows in, 12-45

moving, 11-29

moving to a new segment, 11-86

moving, index-organized, 11-87

nested

creating, 16-19

storage characteristics, 15-41

object

creating, 15-9

object, querying, 15-63

of XMLType, creating, 15-64

organization, defining, 15-30

parallel creation of, 15-53

parallelism

setting default degree, 15-53

partition attributes of, 11-61

partitioning, 2-108, 15-7, 15-44

allowing rows to move between partitions, 11-38

default attributes of, 11-61

physical attributes

changing, 11-32

relational

creating, 15-8

remote, accessing, 13-37

removing from the database, 17-6

removing rows from, 16-55

renaming, 11-38, 17-86

restricting

records in a block, 11-37

retrieving data from, 18-4

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

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

synonyms for, 15-2

tablespace for

defining, 15-7, 15-28

temporary

duration of data, 15-27

session-specific, 15-23

transaction specific, 15-23

unclustering, 16-67

updating through views, 16-47

validating structure, 12-43

with unusable indexes, 10-13

TABLESPACE clause

of ALTER INDEX ... REBUILD, 9-74

of CREATE CLUSTER, 13-6

of CREATE INDEX, 13-78

of CREATE MATERIALIZED VIEW, 14-17

of CREATE MATERIALIZED VIEW LOG, 14-38

of CREATE ROLLBACK SEGMENTS, 14-84

of CREATE TABLE, 15-28

tablespaces, 11-106

allocating space for users, 16-36

allowing write operations on, 11-108

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

backing up datafiles, 11-107

bringing online, 11-106, 15-86

coalescing free extents, 11-109

converting

from permanent to temporary, 11-109

from temporary to permanent, 11-109

creating, 15-80

datafiles

adding, 11-104

renaming, 11-104

default temporary, 9-49

learning name of, 9-49

designing media recovery, 9-25

dropping contents, 17-11

ending online backup, 11-108

extent management, 15-94

extent size, 15-84

granting system privileges on, 17-41

in FORCE LOGGING mode, 11-109, 15-85

locally managed, 7-59

altering, 11-104

temporary, 15-94

logging attribute, 11-109, 15-85

managing extents of, 15-87

of session duration, 15-92

permanent objects in, 15-86

read only, 11-108

reconstructing lost or damaged, 9-25, 9-34

recovering, 9-25, 9-27

removing from the database, 17-10

size of free extents in, 11-106

specifying

datafiles for, 15-83

for a table, 15-27

for a user, 16-36

for index rebuild, 11-88

taking offline, 11-106, 15-86

tempfiles

adding, 11-104

temporary

creating, 15-92

specifying for a user, 16-36

temporary objects in, 15-86

temporary, defining for the database, 13-26

undo

altering, 11-104

creating, 13-33, 15-82

dropping, 17-11

TAN function, 6-169

TANH function, 6-169

TAPE_ASYNCH_IO initialization parameter

setting with ALTER SYSTEM, 10-113

TEMPFILE clause

of ALTER DATABASE, 9-17, 9-36

of CREATE TEMPORARY TABLESPACE, 15-93

tempfiles

bringing online, 9-36

defining for a tablespace, 15-81

defining for a temporary tablespace, 15-92

defining for the database, 13-26

disabling autoextend, 9-36

dropping, 9-36

enabling autoextend, 7-42, 9-36

extending automatically, 7-42

renaming, 9-37

resizing, 9-36

reusing, 7-41

size of, 7-41

specifying, 7-39, 15-93

taking offline, 9-36

TEMPORARY clause

of ALTER TABLESPACE, 11-109

of CREATE TABLESPACE, 15-86

temporary tables

creating, 15-7, 15-23

session-specific, 15-23

transaction-specific, 15-23

TEMPORARY TABLESPACE clause

of ALTER USER. See CREATE USER

of CREATE USER, 16-36

temporary tablespaces

creating, 15-92

default, 9-49

specifying extent management during database creation, 13-26

specifying extent management individually, 15-93

specifying for a user, 16-36

SQL examples, 15-94

TEST clause

of ALTER DATABASE ... RECOVER, 9-28

text

date and number formats, 2-61

in SQL syntax, 2-54

properties of CHAR and VARCHAR2 datatypes, 2-54

syntax of, 2-54

TH datetime format element suffix, 2-75

THREAD initialization parameter

setting with ALTER SYSTEM, 10-113

throughput

optimizing, 2-94

THSP datetime format element suffix, 2-75

TIME datatype

DB2, 2-37

SQL/DS, 2-37

time zone

determining for session, 6-143

formatting, 2-71

setting for the database, 13-35

time zones

converting data to particular, 4-9

TIME_ZONE session parameter, 10-14

TIMED_OS_STATISTICS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-113

TIMED_STATISTICS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-114

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

TIMESTAMP WITH TIME ZONE datatype, 2-21

TM number format element, 2-64

TO SAVEPOINT clause

of ROLLBACK, 17-100

TO_CHAR

datetime conversion function, 6-171

number conversion function, 6-173

TO_CHAR (character) function, 6-170

TO_CHAR function, 2-63, 2-68, 2-76

TO_CLOB function, 6-175

TO_DATE function, 2-68, 2-73, 2-76, 6-175

TO_DSINTERVAL function, 6-177

TO_LOB function, 6-178

TO_MULTI_BYTE function, 6-179

TO_NCHAR (character) function, 6-180

TO_NCHAR (datetime) function, 6-181

TO_NCHAR (number) function, 6-182

TO_NCLOB function, 6-182

TO_NUMBER function, 2-63, 6-183

TO_SINGLE_BYTE function, 6-184

TO_TIMESTAMP function, 6-185

TO_TIMESTAMP_TZ function

SQL functions

TO_TIMESTAMP_TZ, 6-186

TO_YMINTERVAL function, 6-187

top-N queries, 2-88

TRACE_ENABLED initialization parameter

setting with ALTER SYSTEM, 10-114

TRACEFILE_IDENTIFIER initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-114

transaction control statements, 9-3

PL/SQL support of, 9-4

TRANSACTION_AUDITING initialization parameter

setting with ALTER SYSTEM, 10-115

transactions

allowing to complete, 10-26

assigning

rollback segment to, 18-50

automatically committing, 12-75

changes, making permanent, 12-75

commenting on, 12-76

distributed, forcing, 10-3

ending, 12-75

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

in-doubt

committing, 12-75

forcing, 12-76

resolving, 18-52

isolation level, 18-50

locks, releasing, 12-75

naming, 18-52

read-only, 18-50

read/write, 18-50

rolling back, 10-27, 14-82, 17-99

to a savepoint, 17-100

savepoints for, 18-2

TRANSACTIONS initialization parameter

setting with ALTER SYSTEM, 10-115

TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameter

setting with ALTER SYSTEM, 10-116

TRANSLATE ... USING function, 6-189

TRANSLATE function, 6-188

TREAT function, 6-191

triggers

AFTER, 15-98

BEFORE, 15-98

compiling, 12-2, 12-3

creating, 15-95

multiple, 15-99

database

altering, 12-3

dropping, 17-13, 17-20

disabling, 11-90, 12-2, 12-3

enabling, 11-89, 12-2, 12-3, 15-95

executing

with a PL/SQL block, 15-106

with an external procedure, 15-106

granting

system privileges on, 17-42

INSTEAD OF, 15-99

dropping, 16-42

on database events, 15-102

on DDL events, 15-101

on DML operations, 15-97, 15-100

on views, 15-99

order of firing, 15-99

re-creating, 15-97

removing from the database, 17-13

renaming, 12-3

restrictions on, 15-105

row values

old and new, 15-104

row, specifying, 15-105

SQL examples, 15-107

statement, 15-105

TRIM function, 6-192

TRUNC function

date function, 6-194

format models, 6-221

number function, 6-194

TRUNCATE PARTITION clause

of ALTER TABLE, 11-74

TRUNCATE statement, 18-54

TRUNCATE SUBPARTITION clause

of ALTER TABLE, 11-74

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

TZD datetime format element, 2-69

TZH datetime format element, 2-69

TZM datetime format element, 2-69

TZR datetime format element, 2-69

U

U number format element, 2-64

UID function, 6-196

unary operators, 3-2

UNDER ANY TABLE system privilege, 17-42

UNDER ANY VIEW system privilege, 17-43

UNDER clause

of CREATE VIEW, 16-46

UNDER object privilege, 17-46

on a type, 17-49

on a view, 17-48

UNDER_PATH condition, 5-20

undo

rollback, 9-136, 13-33

system managed, 9-136, 13-33

UNDO tablespace clause

of CREATE DATABASE, 13-33

of CREATE TABLESPACE, 15-82

undo tablespaces

creating, 13-33, 15-82

dropping, 17-11

modifying, 11-104

UNDO_MANAGEMENT initialization parameter

setting with ALTER SYSTEM, 10-116

UNDO_RETENTION initialization parameter

setting with ALTER SYSTEM, 10-116

UNDO_SUPPRESS_ERRORS initialization parameter

setting with ALTER SESSION, 10-10

setting with ALTER SYSTEM, 10-117

UNDO_TABLESPACE initialization parameter

setting with ALTER SYSTEM, 10-117

UNIFORM clause

of CREATE TABLESPACE, 15-87

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

UNION set operator, 3-6, 18-24

UNIQUE clause

of CREATE INDEX, 13-71

of CREATE TABLE, 15-26

of SELECT, 18-11

unique constraints

enabling, 15-57

index on, 15-58

unique indexes, 13-71

unique queries, 18-11

UNISTR function, 6-196

universal rowids. See urowids

UNLIMITED TABLESPACE system privilege, 17-42

unnesting collections, 18-17

examples, 18-40

unnesting subqueries, 8-15

UNQUIESCE clause

of ALTER SYSTEM, 10-29

UNRECOVERABLE, 9-72, 15-30

See also NOLOGGING clause

unsorted indexes, 13-78

UNUSABLE clause

of ALTER INDEX, 9-77

UNUSABLE LOCAL INDEXES clause

of ALTER MATERIALIZED VIEW, 9-100

of ALTER TABLE, 11-84

UPDATE ANY TABLE system privilege, 17-41

UPDATE BLOCK REFERENCES clause

of ALTER INDEX, 9-79, 9-80

of ALTER TABLE, 11-41

UPDATE GLOBAL INDEXES clause

of ALTER TABLE, 11-85

UPDATE object privilege, 17-46

on a table, 17-47

on a view, 17-48

update operations

collecting supplemental log data for, 9-40

UPDATE SET clause

of MERGE, 17-77

UPDATE statement, 18-59

triggers on, 15-100

updates

and simultaneous insert, 17-77

using MERGE, 17-77, 17-78

UPDATEXML function, 6-197

UPGRADE clause

of ALTER TABLE, 11-36

upgrading

from release 7.3.4 to Oracle9i release 2, 9-24

UPPER function, 6-199

URLs

generating, 6-161

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

USE_MERGE hint, 2-106

USE_NL hint, 2-106

USE_PRIVATE_OUTLINES session parameter, 10-15

USE_STORED_OUTLINES initialization parameter

setting with ALTER SESSION, 10-118

USE_STORED_OUTLINES session parameter, 10-16, 10-118

USER function, 6-199

USER SYS clause

of CREATE DATABASE, 13-27

USER SYSTEM clause

of CREATE DATABASE, 13-27

USER_COL_COMMENTS data dictionary view, 12-72

USER_DUMP_DEST initialization parameter

setting with ALTER SYSTEM, 10-118

USER_TAB_COMMENTS data dictionary view, 12-72

user-defined aggregate functions, 13-61

user-defined functions, 6-222

name precedence of, 6-224

naming conventions, 6-224

restrictions on, 13-56

user-defined operators, 3-6

user-defined statistics

dropping, 16-78, 16-79, 16-92, 17-7, 17-15

user-defined types, 2-38

defining, 16-9

mapping to Java classes, 16-11

USERENV function, 6-200

users

allocating space for, 16-36

and database links, 13-39

assigning

default roles, 12-26

profiles, 16-36

authenticating to a remote server, 13-40

changing authentication, 12-27

changing global authentication, 12-25

creating, 16-32

default tablespaces, 16-36

denying access to tables and views, 17-73

external, 14-80, 16-34

global, 14-80, 16-35

granting

system privileges on, 17-43

local, 14-80, 16-34

locking accounts, 16-37

maximum concurrent, 10-66

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

USING BLOB clause

of CREATE JAVA, 13-102

USING clause

of ALTER INDEXTYPE, 9-86

of ASSOCIATE STATISTICS, 12-51, 12-52

of CREATE DATABASE LINK, 13-40

of CREATE INDEXTYPE, 13-96

of CREATE OPERATOR, 14-47

USING CLOB clause

of CREATE JAVA, 13-102

USING INDEX clause

of ALTER MATERIALIZED VIEW, 9-101

of ALTER TABLE, 11-30

of constraints, 7-23

of CREATE MATERIALIZED VIEW, 14-21

of CREATE TABLE, 15-58

USING NO INDEX clause

of CREATE MATERIALIZED VIEW, 14-21

USING ROLLBACK SEGMENT clause

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

of CREATE MATERIALIZED VIEW, 14-24

UTC

extracting from a datetime value, 6-162

UTC offset

replacing with time zone region, 2-22

UTL_FILE_DIR initialization parameter

setting with ALTER SYSTEM, 10-119

UTLCHN.SQL script, 12-45

UTLEXPT1.SQL script, 11-82

UTLXPLAN.SQL script, 17-24

V

V number format element, 2-64

VALIDATE clause

of DROP TYPE, 17-16

VALIDATE REF UPDATE clause

of ANALYZE, 12-43

VALIDATE STRUCTURE clause

of ANALYZE, 12-43

validation

of clusters, 12-43

of database objects

offline, 12-45

of database objects, online, 12-45

of indexes, 12-43

of tables, 12-43

VALUE function, 6-202

VALUES clause

of CREATE INDEX, 7-25, 13-82, 15-60

of INSERT, 17-61

VALUES LESS THAN clause

of CREATE TABLE, 15-45

VAR_POP function, 6-202

VAR_SAMP function, 6-204

VARCHAR datatype, 2-12

DB2, 2-37

SQL/DS, 2-37

VARCHAR2 datatype, 2-11

converting to NUMBER, 2-63

VARGRAPHIC datatype

DB2, 2-37

SQL/DS, 2-37

variable expressions, 4-15

VARIANCE function, 6-206

VARRAY clause

of ALTER TABLE, 11-13

VARRAY column properties

of ALTER TABLE, 11-13, 11-45

of CREATE MATERIALIZED VIEW, 14-13

of CREATE TABLE, 15-13, 15-40

varrays, 2-39

changing returned value, 11-57

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-45, 11-57, 15-40

storing out of line, 2-39

varying arrays. See varrays

view constraints

dropping, 17-23

views

base tables

adding rows, 17-53

changing

definition, 17-22

values in base tables, 18-59

creating

before base tables, 16-43

comments about, 12-72

multiple, 14-86

creating object subviews, 16-46

defining, 16-39

dropping constraints on, 12-33

granting

system privileges on, 17-43

modifying constraints on, 12-33

object, creating, 16-45

recompiling, 12-31

re-creating, 16-42

remote, accessing, 13-37

removing

from the database, 17-22

rows from the base table of, 16-55

renaming, 17-86

retrieving data from, 18-4

subquery of, 16-46

restricting, 16-49

synonyms for, 15-2

updatable, 16-47

with joins

and key-preserved tables, 16-48

with joins, making updatable, 16-48

XMLType, 16-48

XMLType, creating, 16-53

XMLType, querying, 16-48

VSIZE function, 6-207

W

W datetime format element, 2-69

WHEN clause

of CREATE TRIGGER, 15-105

WHEN MATCHED clause

of MERGE, 17-78

WHEN NOT MATCHED clause

of MERGE, 17-78

WHENEVER NOT SUCCESSFUL clause

of NOAUDIT, 17-84

WHENEVER SUCCESSFUL clause

of AUDIT sql_statements, 12-60

of NOAUDIT, 17-84

WHERE clause

of DELETE, 16-60

of queries and subqueries, 18-20

of SELECT, 8-4

of UPDATE, 18-67

WIDTH_BUCKET function, 6-208

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

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

WITH OBJECT ID clause

of CREATE MATERIALIZED VIEW LOG, 14-39

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

of CREATE MATERIALIZED VIEW LOG, 14-39

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

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

of SELECT, 18-7

of UPDATE, 18-63

WITH ROWID clause

of column ref constraints, 7-18

of CREATE MATERIALIZED VIEW LOG, 14-39

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

WITH SEQUENCE clause

of CREATE MATERIALIZED VIEW LOG, 14-39

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

setting with ALTER SYSTEM, 10-119

WRITE object privilege

on a directory, 17-49

WW datetime format element, 2-69

X

X datetime format element, 2-69

X number format element, 2-64

XML data

storage of, 15-43

XML database repository

SQL access to, 5-13, 5-20

XML documents

producing from XML fragments, 6-165

retrieving from the database, 6-161

XML format models, 2-79

XML fragments, 6-65

XMLDATA pseudocolumn, 2-89

XMLGenFormatType object, 2-79

XMLType columns

properties of, 11-48, 15-43

storage of, 11-48, 15-43

XMLType storage clause

of CREATE TABLE, 15-43

XMLType tables

creating, 15-64, 15-71

creating index on, 13-88

XMLType views, 16-48

querying, 16-48

Y

Y datetime format element, 2-69

Y,YYY datetime format element, 2-69

YEAR datetime format element, 2-69

YY datetime format element, 2-69

YYY datetime format element, 2-69

YYYY datetime format element, 2-69