Index

of ALTER MATERIALIZED VIEW, 7-79

ADD ROWID clause

of ALTER TABLESPACE, 8-70

ADD TEMPFILE clause

ADD_MONTHS function, 4-15

ADMINISTER ANY TRIGGER system privilege, 11-43

ADVISE clause

of ALTER SESSION, 7-105

AFTER clause

of CREATE TRIGGER, 10-69

AFTER triggers, 10-69

aggregate functions, 4-6

aliases

for columns, 5-21

for expressions in view query, 10-108

specifying in queries and subqueries, 11-97

ALL clause

of SELECT, 11-92

of SET CONSTRAINTS, 11-120

ALL EXCEPT clause

of GRANT object_privileges, 11-36

ALL operator, 3-6

ALL PRIVILEGES clause

of AUDIT sql_statements, 8-117

ALL PRIVILEGES shortcut

ALL shortcut

of AUDIT sql_statements, 8-117

ALL_COL_COMMENTS view, 8-131

ALL_ROWS hint, 2-68

ALL_TAB_COMMENTS view, 8-131

ALLOCATE EXTENT clause

of ALTER INDEX, 7-41, 7-45

of ALTER TABLE, 8-34

ALTER ANY CLUSTER system privilege, 11-38

ALTER ANY DIMENSION system privilege, 11-38

ALTER ANY INDEX system privilege, 11-39

ALTER ANY INDEXTYPE system privilege, 11-39

ALTER ANY MATERIALIZED VIEW system privilege, 11-40

ALTER ANY OUTLINE system privilege, 11-40

ALTER ANY PROCEDURE system privilege, 11-40

ALTER ANY ROLE system privilege, 11-41

ALTER ANY SEQUENCE system privilege, 11-41

ALTER ANY SNAPSHOT system privilege, 11-41

ALTER ANY TABLE system privilege, 11-42

ALTER ANY TRIGGER system privilege, 11-43

ALTER ANY TYPE system privilege, 11-43

ALTER CLUSTER statement, 7-3

ALTER DATABASE

statement, 7-8

ALTER DIMENSION statement, 7-33

ALTER FUNCTION statement, 7-37

ALTER INDEX statement, 7-39

ALTER JAVA CLASS statement, 7-57

ALTER JAVA SOURCE statement, 7-57

ALTER MATERIALIZED VIEW LOG statement, 7-75

ALTER MATERIALIZED VIEW statement, 7-60

ALTER object privilege, 11-46

ALTER OUTLINE statement, 7-82

ALTER PACKAGE statement, 7-84

ALTER PROCEDURE statement, 7-87

ALTER PROFILE

statement, 7-90

ALTER RESOURCE COST

statement, 7-94

ALTER ROLE statement, 7-97

ALTER ROLLBACK SEGMENT

statement, 7-99

ALTER SEQUENCE statement, 7-102

ALTER SESSION

statement, 7-104

ALTER SNAPSHOT LOG. See ALTER MATERIALIZED VIEW LOG

ALTER SNAPSHOT. See ALTER MATERIALIZED VIEW

ALTER statement

triggers on, 10-71

ALTER SYSTEM

statement, 7-126

ALTER TABLE statement, 8-2

ALTER TABLESPACE

statement, 8-67

ALTER TRIGGER statement, 8-76

ALTER TYPE statement, 8-79

ALTER USER

statement, 8-88

ALTER VIEW statement, 8-94

AM (A.M.) date format element, 2-49, 2-51

American National Standards Institute. See ANSI

analytic functions, 4-8

LAG, 4-45

LEAD, 4-49

RANK, 4-74

RATIO_TO_REPORT, 4-75

ROW_NUMBER, 4-87

ANALYZE ANY system privilege, 11-44

ANALYZE CLUSTER statement, 8-96

ANALYZE INDEX statement, 8-96

ANALYZE TABLE statement, 8-96

ANCILLARY TO clause

of CREATE OPERATOR, 9-117

AND operator, 3-11, 3-12

AND_EQUAL hint, 2-69

ANSI, B-1

datatypes, 2-22

conversion to Oracle datatypes, 2-22

standards, xlv, 1-2

supported datatypes, 2-5

ANY operator, 3-6

APPEND hint, 2-73

application servers

allowing to connect as a user, 8-91

applications

allowing to connect as a user, 8-91

securing, 9-13

validating, 9-13

AQ_ADMINISTRATOR_ROLE role, 11-45

AQ_TM_PROCESSES parameter

of ALTER SYSTEM, 7-135

AQ_USER_ROLE role, 11-45

ARCHIVE LOG clause

of ALTER SYSTEM, 7-127

archived redo logs

location of, 7-15

storage locations, 7-112, 7-141

ARCHIVELOG clause

of ALTER DATABASE, 7-12, 7-21

arguments of operators, 3-1

arithmetic operators, 3-3

AS 'filespec' clause

of CREATE LIBRARY, 9-87

AS clause

of CREATE JAVA, 9-84

AS EXTERNAL clause

of CREATE FUNCTION, 9-50, 9-137

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-92, 9-101

AS OBJECT clause

of CREATE TYPE, 10-84

AS subquery

of CREATE TABLE, 10-46

of CREATE VIEW, 10-110

AS TABLE clause

of CREATE TYPE, 10-90

AS VARRAY clause

of CREATE TYPE, 10-89

ASC clause

of CREATE INDEX, 9-63

ascending indexes, 9-63

ASCII

character set, 2-28

ASCII function, 4-15

ASIN function, 4-16

ASSOCIATE STATISTICS statement, 8-110

ATAN function, 4-16

ATAN2 function, 4-17

ATTRIBUTE clause

of CREATE DIMENSION, 9-38

attributes

adding to a dimension, 7-35

dropping from a dimension, 7-35

maximum number of in object type, 10-19

of dimensions, defining, 9-38

AUDIT ANY system privilege, 11-44

AUDIT SYSTEM system privilege, 11-38

auditing

options

for database objects, 8-120

for SQL statements, 8-122

SQL statements, 8-120

SQL statements, stopping, 11-66

AUTHENTICATED BY clause

of CREATE DATABASE LINK, 9-31

AUTHID CURRENT_USER clause

of ALTER JAVA, 7-58

of CREATE PROCEDURE, 9-136

of CREATE PACKAGE, 9-124

of CREATE TYPE, 8-84, 10-85

AUTHID DEFINER clause

of ALTER JAVA, 7-58

of CREATE PROCEDURE, 9-136

of CREATE PACKAGE, 9-124

of CREATE TYPE, 8-84, 10-85

AUTOEXTEND clause

for datafiles, 7-20

of ALTER DATABASE, 7-12

of ALTER TABLESPACE, 8-69, 8-70

of CREATE DATABASE, 9-22

of CREATE TABLESPACE, 10-57, 10-59

of CREATE TEMPORARY TABLESPACE, 10-63, 10-64

AVG function, 4-18

AY date format element, 2-49

BACKGROUND_DUMP_DEST parameter

of ALTER SYSTEM, 7-135

BACKUP ANY TABLE system privilege, 11-42

BACKUP CONTROLFILE clause

of ALTER DATABASE, 7-13, 7-24

BACKUP_TAPE_IO_SLAVES parameter

BC (B.C.) date format element, 2-49, 2-51

BECOME USER system privilege, 11-44

BEFORE clause

of CREATE TRIGGER, 10-68

BEFORE triggers, 10-68

BEGIN BACKUP clause

of CREATE OPERATOR, 9-115, 9-117

BFILE

datatype, 2-18

locators, 2-18

BFILENAME function, 4-19

binary large objects. See BLOBs

binary operators, 3-2

BINDING clause

BITAND function, 4-20

BITMAP clause

of CREATE INDEX, 9-59

bitmap indexes, 9-59

blank padding

specifying in format models, 2-54

suppressing, 2-54

blank-padded comparison semantics, 2-27

BLOB datatype, 2-19

transactional support of, 2-19

BODY clause

of STORAGE clause, 11-135

BUFFER_POOL parameter

BUILD DEFERRED clause

BUILD IMMEDIATE clause

of AUDIT sql_statements, 8-119

BY ACCESS clause

BY proxy clause

of AUDIT (SQL statements), 8-117

of NOAUDIT sql_statements, 11-68

BY SESSION clause

of AUDIT sql_statements, 8-119

BY user clause

of AUDIT sql_statements, 8-117

of NOAUDIT sql_statements, 11-68

C clause

mapping to an object type, 10-87

C method

C number format element, 2-44

CACHE clause

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of ALTER SEQUENCE. See CREATE SEQUENCE, 7-102

of ALTER TABLE, 8-36

of CREATE CLUSTER, 9-10

of CREATE PROCEDURE, 9-137

of CREATE TABLE, 10-39

CACHE hint, 2-77

CACHE READS clause

of ALTER TABLE, 8-36

of CREATE TABLE, 10-40

CALL clause

of CREATE TRIGGER, 10-76

CALL procedure statement

of CREATE TRIGGER, 10-76

call spec

in procedures, 9-132

of CREATE FUNCTION, 9-50

call specifications. See call spec

CALL statement, 8-128

Cartesian products, 5-25

CASCADE clause

of CREATE TABLE, 10-45

of DROP PROFILE, 10-154

of DROP USER, 11-19

CASCADE CONSTRAINTS clause

of DROP CLUSTER, 10-127

of DROP TABLE, 11-9

of DROP TABLESPACE, 11-12

schema object names, 2-86

CASE expressions, 5-14

case sensitivity

CAST expressions, 5-8

CC date format element, 2-49

CEIL function, 4-21

century

specifying, 2-50

chained rows

listing, 8-106

CHANGE CATEGORY clause

of ALTER OUTLINE, 7-82

changes

making permanent, 8-133

changing default storage parameters, 8-71

CHAR datatype, 2-8

converting to VARCHAR2, 2-43

CHAR VARYING datatype, ANSI, 2-22

CHARACTER datatype

character functions, 4-4, 4-5

character large objects. See CLOB datatype

character literal. See text

CHARACTER SET clause

OF CREATE DATABASE, 9-26

CHARACTER SET parameter

multibyte characters, 2-83

character sets

common, 2-28

specifying for database, 9-26

character strings

comparison rules, 2-26

exact matching of, 2-55

fixed-length, 2-8

national character set, 2-8

variable length, 2-9

variable-length, 2-12

zero-length, 2-8

CHARACTER VARYING datatype

single, comparison rules, 2-28

characters

CHARTOROWID function, 2-32, 4-21

CHECK clause

of ALTER DATABASE, 7-12, 7-23

check constraints, 8-144

CHECK DATAFILES clause

of ALTER SYSTEM, 7-131

checkpoint

forcing, 7-130

CHECKPOINT clause

of ALTER SYSTEM, 7-130

CHR function, 4-22

CHUNK clause

of ALTER TABLE, 8-22

of CREATE TABLE, 10-31

CLEAR LOGFILE clause

CLOB datatype, 2-19

transactional support of, 2-19

clone database

mounting, 7-25

CLOSE DATABASE LINK clause

of ALTER SESSION, 7-105

CLUSTER clause

of CREATE INDEX, 9-59

of CREATE TABLE, 10-29

CLUSTER hint, 2-69

clusters

COALESCE clause

for partitions, 8-48

for subpartitions, 8-43

COALESCE SUBPARTITION clause

inserting rows into, 11-55

code examples

description of, lii

collections

modifying, 8-28

nested tables, 2-26

treating as a table, 10-118, 11-55, 11-145

unnesting, 11-96

examples, 11-115

varrays, 2-25

column constraints, 8-137, 8-140

column REF constraints, 8-137, 8-145

of ALTER TABLE, 8-20

of ASSOCIATE STATISTICS, 8-110, 8-112

columns

COLUMNS clause

COMMENT ANY TABLE system privilege, 11-44

COMMENT clause

of COMMIT, 8-134

COMMENT statement, 8-131

comments, 2-66

adding to objects, 8-131

associating with a transaction, 8-134

dropping from objects, 8-131

how to specify, 2-66

in SQL statements, 2-66

on schema objects, 2-67

removing from the data dictionary, 8-131

viewing, 8-131

commit

automatic, 8-133

COMMIT IN PROCEDURE clause

of ALTER SESSION, 7-105

COMMIT statement, 8-133

comparison functions

MAP, 10-88, 10-96

ORDER, 10-89, 10-96

comparison operators, 3-5

comparison semantics

blank-padded, 2-27

nonpadded, 2-27

of character strings, 2-26

COMPILE clause

of ALTER DIMENSION, 7-35

of ALTER FUNCTION, 7-38

of ALTER JAVA SOURCE, 7-58

of ALTER MATERIALIZED VIEW, 7-71

compiler directives, 10-87

of ALTER PROCEDURE, 7-88

of ALTER TRIGGER, 8-77

of ALTER TYPE, 8-80

of ALTER VIEW, 8-95

of CREATE JAVA, 9-81

composite foreign keys, 8-143

composite partitioning clause

of CREATE TABLE, 10-14, 10-36

composite primary keys, 8-142

composite unique constraints, 8-141

COMPOSITE_LIMIT parameter

compound conditions, 5-20

compound expressions, 5-4

COMPRESS clause

of ALTER INDEX, 7-42

of ALTER TABLE, 8-26

concatenation operator, 3-4

of CREATE TABLE, 10-28

COMPUTE STATISTICS clause

of ANALYZE, 8-101

of CREATE INDEX, 9-66

CONCAT function, 4-23

conditions

compound, 5-20

EXISTS, 5-20

group comparison, 5-18

in SQL syntax, 5-15

LIKE, 5-20

membership, 5-19

NULL, 5-20

range, 5-20

simple comparison, 5-17

CONNECT BY clause

of SELECT, 5-23, 11-98

CONNECT clause

of CREATE DATABASE LINK, 9-30

CONNECT role, 11-45

CONNECT TO clause

CONNECT_TIME parameter

of ALTER MATERIALIZED VIEW, 7-71

CONSIDER FRESH clause

constant values. See literals

CONSTRAINT clause

of constraint_clause, 8-141

CONSTRAINT(S) parameter

of ALTER SESSION, 7-108

constraints

adding, 8-19

check, 8-144

checking at end of transaction, 8-147

checking at start of transaction, 8-147

checking at the end of each DML statement, 8-147

column REF, 8-145

composite unique, 8-141

deferrable, 8-147, 11-120

enforcing, 7-108

defining, 8-136, 10-7

on a column, 10-20

on a table, 10-20

disabling, 8-55, 8-150, 10-41

cascading, 10-45

dropping, 8-29, 11-12

enabling, 8-55, 8-149, 10-41, 10-44

foreign key, 8-144

modifying existing, 8-25

not null, 8-142

on columns, 8-140

primary key, 8-142

attributes of index, 8-148

enabling, 10-44

referential integrity, 8-143, 8-144

restrictions, 8-141

scope, 8-146

setting state for a transaction, 11-120

storing rows in violation, 8-52

table REF, 8-145

unique, 8-141

attributes of index, 8-148

composite, 8-141

enabling, 10-44

validating, 8-149, 8-150

constructor methods

and object types, 10-80

context namespaces

removing from the database, 10-128

contexts

creating namespaces for, 9-13

granting

system privileges on, 11-38

namespace

associating with package, 9-13

control files

allow reuse of, 9-17

allowing reuse of, 9-23

backing up, 7-24

re-creating, 9-15

CONTROL_FILE_RECORD_KEEP_TIME parameter

rules, string to date, 2-57

controlfile clauses

of ALTER DATABASE, 7-13

CONTROLFILE REUSE clause

of CREATE DATABASE, 9-23

conversion

conversion functions, 4-5

table of, 2-32

CONVERT clause

of ALTER DATABASE, 7-25

CONVERT function, 4-24

CORE_DUMP_DEST parameter

correlated subqueries, 5-27

CORR function, 4-25

correlation names

for base tables of indexes, 9-60

CPU_PER_CALL parameter

of CREATE PROFILE, 9-142

CPU_PER_SESSION parameter

CREATE ANY CLUSTER system privilege, 11-38

of CREATE PROFILE, 9-142

CREATE ANY CONTEXT system privilege, 11-38

CREATE ANY DIMENSION system privilege, 11-38

CREATE ANY DIRECTORY system privilege, 11-39

CREATE ANY INDEX system privilege, 11-39

CREATE ANY INDEXTYPE system privilege, 11-39

CREATE ANY LIBRARY system privilege, 11-39

CREATE ANY MATERIALIZED VIEW system privilege, 11-39

CREATE ANY OPERATOR system privilege, 11-40

CREATE ANY OUTLINE system privilege, 11-40

CREATE ANY PROCEDURE system privilege, 11-40

CREATE ANY SEQUENCE system privilege, 11-41

CREATE ANY SNAPSHOT system privilege, 11-41

CREATE ANY SYNONYM system privilege, 11-42

CREATE ANY TABLE system privilege, 11-42

CREATE ANY TRIGGER system privilege, 11-43

CREATE ANY TYPE system privilege, 11-43

CREATE ANY VIEW system privilege, 11-44

CREATE CLUSTER

statement, 9-3

CREATE CONTEXT statement, 9-13

CREATE CONTROLFILE statement, 9-15

CREATE DATABASE LINK

statement, 9-28

CREATE DATABASE statement, 9-21

CREATE DATAFILE clause

of ALTER DATABASE, 7-11, 7-19

CREATE DIMENSION

statement, 9-34

CREATE DIRECTORY statement, 9-40

CREATE FUNCTION statement, 9-43

CREATE INDEX

statement, 9-52

CREATE INDEXTYPE

statement, 9-76

CREATE JAVA statement, 9-79

CREATE LIBRARY

statement, 9-86

CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG statement, 9-107

CREATE MATERIALIZED VIEW / SNAPSHOT

statement, 9-88

CREATE MATERIALIZED VIEW/SNAPSHOT

CREATE OPERATOR

statement, 9-115

CREATE OUTLINE statement, 9-119

CREATE PACKAGE BODY statement, 9-127

CREATE PACKAGE statement, 9-122

CREATE PROCEDURE

statement, 9-132

CREATE PROFILE

statement, 9-139

CREATE PUBLIC DATABASE LINK system privilege, 11-38

CREATE PUBLIC SYNONYM system privilege, 11-42

CREATE ROLE

statement, 9-146

CREATE ROLLBACK SEGMENT

statement, 9-149

CREATE SCHEMA statement, 9-152

CREATE SEQUENCE

statement, 9-155

CREATE SESSION system privilege, 11-41

CREATE SNAPSHOT system privilege, 11-41

CREATE STANDBY CONTROLFILE clause

of ALTER DATABASE, 7-13, 7-24

CREATE statement

triggers on, 10-71

CREATE SYNONYM

statement, 10-3

CREATE TABLE statement, 10-7

CREATE TABLESPACE

statement, 10-56

CREATE TEMPORARY TABLESPACE statement, 10-63

CREATE TRIGGER

statement, 10-66

CREATE TYPE

statement, 10-80

CREATE TYPE BODY statement, 10-93

CREATE USER

statement, 10-99

CREATE VIEW

statement, 10-105

system privilege, 11-44

CREATE_STORED_OUTLINES parameter

of ALTER SESSION, 7-109

of SELECT statements, 11-100

CUBE clause

CUME_DIST function, 4-33

currency symbol

ISO, 2-44

local, 2-45

union, 2-46

CURRENT_SCHEMA parameter

of ALTER SESSION, 7-109

CURRENT_USER

and database links, 9-30

CURRVAL pseudocolumn, 2-59, 9-155

CURSOR expressions, 5-11

CURSOR_SHARING parameter

of ALTER SESSION, 7-110, 7-136

cursors

number cached per session, 7-119

CYCLE clause

of ALTER SEQUENCE. See CREATE SEQUENCE, 7-102

D date format element, 2-49

D number format element, 2-44

data

integrity checking on input, 2-11

retrieving, 5-21

undo

storing, 9-149

data conversion, 2-30

implicit

disadvantages of, 2-32

implicit versus explicit, 2-32

when performed implicitly, 2-30

when specified explicitly, 2-31

data definition language

events and triggers, 10-71

statements, 6-2

and implicit commit, 6-2

causing recompilation, 6-2

PL/SQL support of, 6-2

data dictionary

adding comments to, 8-131

data manipulation language

operations

and triggers, 10-70

during index creation, 9-66

during index rebuild, 8-26

retrieving rows affected by, 10-120, 11-57, 11-149

statements, 6-4

PL/SQL support of, 6-4

data object number

in extended rowids, 2-20

database

accounts

creating, 10-99

allowing generation of redo logs, 7-26

allowing reuse of control files, 9-23

allowing unlimited resources to users, 9-141

cancel-based recovery, 7-16

terminating, 7-17

change-based recovery, 7-16

changing characteristics of, 9-15

changing global name, 7-27

changing the name of, 9-15, 9-17

character set

specifying, 9-26

connect strings, 2-91

converting from Oracle7 data dictionary, 7-25

creating, 9-21

enabling automatic extension of, 9-26

erasing all data from, 9-21

granting system privileges on, 11-38

limiting resources for users, 9-139

managed recovery of, 7-11

after media recovery, 7-26

recovering, 7-15

with backup control file, 7-16

re-creating control file for, 9-15

redo log files

specifying, 9-17

remote

accessing, 5-29

authenticating users to, 9-31

connecting to, 9-30

inserting into, 11-54

service name of, 9-31

table locks on, 11-63

resetting

current log sequence, 7-26

to an earlier version, 7-27

restricting users to read-only transactions, 7-26

resuming activity, 7-134

specifying datafiles for, 9-18

suspending activity, 7-134

time-based recovery, 7-16

database events

creating synonyms with, 10-5

current user, 9-30

granting system privileges on, 11-38

removing from the database, 10-129

shared, 9-29

syntax of, 2-91

username and password, 2-91

database objects

dropping, 11-19

nonschema, 2-80

schema, 2-79

database triggers. See triggers

DATAFILE clause

of ALTER DATABASE, 7-11, 7-19

of CREATE DATABASE, 9-26

DATAFILE clauses

of ALTER DATABASE, 7-11

DATAFILE END BACKUP clause

of ALTER DATABASE, 7-20

DATAFILE OFFLINE clause

of ALTER DATABASE, 7-19

DATAFILE ONLINE clause

of ALTER DATABASE, 7-19

DATAFILE RESIZE clause

of ALTER DATABASE, 7-19

datafiles

bringing online, 7-19

creating new, 7-19

disabling automatic extension, 7-20

enabling automatic extension, 7-20, 10-59

recovering, 7-16

re-creating lost, 7-19

for a tablespace, 10-58

taking offline, 7-19

datatypes, 2-2

DATE datatype, 2-14

converting from character or numeric value, 2-14

date format elements, 2-48

RR, 2-52

date format models, 2-47

punctuation in, 2-48

text in, 2-48

date functions, 4-5

dates

arithmetic using, 2-15

comparison rules, 2-26

converting DATE values into strings, 2-14

converting from character or numeric values, 2-14

Julian, 2-15

specifying nondefault formats for, 2-14

DAY date format element, 2-51

DB_BLOCK_CHECKING parameter

of ALTER SESSION, 7-110

of ALTER SYSTEM, 7-137

DB_BLOCK_CHECKSUM parameter

of ALTER SYSTEM, 7-137

DB_BLOCK_MAX_DIRTY_TARGET parameter

of ALTER SYSTEM, 7-137

DB_FILE_MULTIBLOCK_READ_COUNT parameter

of ALTER SESSION, 7-110

conversion to Oracle datatypes, 2-23

DB2 datatypes, 2-22

restrictions on, 2-24

DBA role, 11-45

DBA_2PC_PENDING view, 7-105

DBA_COL_COMMENTS view, 8-131

DBA_ROLLBACK_SEGS view, 10-157

DBA_TAB_COMMENTS view, 8-131

DBMS_OUTPUT package, 8-77

DBMS_ROWID package

and extended rowids, 2-21

DBMSSTDX.SQL script, 9-44, 9-122, 9-127, 9-132

and triggers, 10-66

DD date format element, 2-49

DDD date format element, 2-49

DDL. See data definition language

DDL statements

requiring exclusive access, 6-2

DEALLOCATE UNUSED clause

of ALTER CLUSTER, 7-5

of ALTER INDEX, 7-40

of ALTER TABLE, 8-35

DEBUG clause

of ALTER FUNCTION, 7-38

of ALTER PACKAGE, 7-86

of ALTER PROCEDURE, 7-88

of ALTER TRIGGER, 8-77

of ALTER TYPE, 8-80

decimal characters, 2-36

specifying, 2-45

DECIMAL datatype

DECODE expressions, 5-13

DEFAULT clause

DEFAULT COST clause

assigning to users, 10-154

DEFAULT profile

DEFAULT ROLE clause

of ALTER USER, 8-91

DEFAULT SELECTIVITY clause

DEFAULT storage clause

DEFAULT TABLESPACE clause

of ALTER USER. See CREATE USER

of constraint_clause, 8-147

DEFERRABLE clause

deferrable constraints, 11-120

DEFERRED clause

of SET CONSTRAINTS, 11-120

DELETE

object privilege, 11-46

statement, 10-115

DELETE ANY TABLE system privilege, 11-42

DELETE statement

triggers on, 10-70

DELETE STATISTICS clause

of ANALYZE, 8-104

DELETE_CATALOG_ROLE role, 11-45

DENSE_RANK function, 4-34

DEREF function, 4-35

DESC clause

of CREATE INDEX, 9-63

descending indexes, 9-63

DETERMINISTIC clause

changing hierarchical relationships, 7-33

dimensions

attributes

compiling invalidated, 7-35

creating, 9-34

examples, 9-38

granting

system privileges on, 11-38

hierarchies

levels

removing from the database, 10-131

directories. See directory objects

directory objects

as aliases for OS directories, 9-40

auditing, 8-119

creating, 9-40

granting system privileges on, 11-39

redefining, 9-41

removing from the database, 10-133

DISABLE [constraint] clause

of CREATE TABLE, 10-43

DISABLE ALL TRIGGERS clause

DISABLE clause

of constraint_clause, 8-150

of ALTER TRIGGER, 8-77

of CREATE TABLE, 10-41

DISABLE DISTRIBUTED RECOVERY clause

DISABLE NOVALIDATE constraint state, 10-44

DISABLE PARALLEL DML clause

of ALTER SESSION, 7-106

DISABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 7-70

of CREATE MATERIALIZED VIEW/SNAPSHOT, 9-100

DISABLE RESTRICTED SESSION clause

of CREATE TABLE, 10-11, 10-38

DISABLE ROW MOVEMENT clause

of ALTER TABLE, 8-54

DISABLE STORAGE IN ROW clause

of CREATE TABLE, 10-31

DISABLE TABLE LOCK clause

DISABLE THREAD clause

DISABLE VALIDATE constraint state, 10-43

DISASSOCIATE STATISTICS statement, 10-123

DISCONNECT SESSION clause

of ALTER SYSTEM, 7-131

dispatcher processes

creating additional, 7-143

terminating, 7-143

DISTINCT clause

of SELECT, 11-92

distinct queries, 11-92

distributed queries, 5-29

restrictions on, 5-29

distribution

hints for, 2-75

DML. See data manipulation language

domain indexes, 9-52, 9-70, 9-76

determining user-defined CPU and I/O costs, 11-23

invoking drop routines for, 11-7

removing from the database, 10-136

specifying alter string for, 7-51

DOUBLE PRECISION datatype

DROP ANY CLUSTER system privilege, 11-38

DROP ANY CONTEXT system privilege, 11-38

DROP ANY DIMENSION system privilege, 11-38

DROP ANY DIRECTORY system privilege, 11-39

DROP ANY INDEX system privilege, 11-39

DROP ANY INDEXTYPE system privilege, 11-39

DROP ANY LIBRARY system privilege, 11-39

DROP ANY MATERIALIZED VIEW system privilege, 11-40

DROP ANY OPERATOR system privilege, 11-40

DROP ANY OUTLINE system privilege, 11-40

DROP ANY PROCEDURE system privilege, 11-40

DROP ANY ROLE system privilege, 11-41

DROP ANY SEQUENCE system privilege, 11-41

DROP ANY SNAPSHOT system privilege, 11-41

DROP ANY SYNONYM system privilege, 11-42

DROP ANY TABLE system privilege, 11-42

DROP ANY TRIGGER system privilege, 11-43

DROP ANY TYPE system privilege, 11-43

DROP ANY VIEW system privilege, 11-44

DROP clause

of ALTER DIMENSION, 7-35

DROP CLUSTER statement, 10-126

DROP COLUMN clause

of ALTER TABLE, 8-30

DROP CONSTRAINT clause

DROP CONTEXT statement, 10-128

DROP DATABASE LINK statement, 10-129

DROP DIMENSION statement, 10-131

DROP DIRECTORY statement, 10-133

DROP FUNCTION statement, 10-134

DROP INDEX statement, 10-136

DROP INDEXTYPE statement, 10-138

DROP JAVA statement, 10-140

DROP LIBRARY

statement, 10-142

of ALTER DATABASE, 7-12, 7-22

DROP LOGFILE clause

DROP LOGFILE MEMBER clause

of ALTER DATABASE, 7-12, 7-23

DROP MATERIALIZED VIEW LOG statement, 10-145

DROP MATERIALIZED VIEW statement, 10-143

DROP OPERATOR statement, 10-147

DROP OUTLINE statement, 10-149

DROP PACKAGE BODY statement, 10-150

DROP PACKAGE statement, 10-150

DROP PARTITION clause

of ALTER INDEX, 7-54

of ALTER TABLE, 8-48

DROP PRIMARY constraint clause

DROP PROCEDURE statement, 10-152

DROP PROFILE

statement, 10-154

DROP PUBLIC DATABASE LINK system privilege, 11-38

DROP PUBLIC SYNONYM system privilege, 11-42

DROP ROLE statement, 10-156

DROP ROLLBACK SEGMENT

statement, 10-157

DROP SEQUENCE statement, 11-3

DROP statement

triggers on, 10-71

DROP STORAGE clause

DROP SYNONYM statement, 11-5

DROP TABLE statement, 11-7

DROP TABLESPACE

statement, 11-10

DROP TRIGGER statement, 11-13

DROP TYPE BODY statement, 11-17

DROP TYPE statement, 11-15

DROP UNIQUE constraint clause

DROP VIEW statement, 11-21

DROP USER

statement, 11-19

system privilege, 11-44

DUAL dummy table, 2-84, 5-28

DUMP function, 4-36

DY date format element, 2-49, 2-51

E date format element, 2-49

E number format element, 2-44

EBCDIC character set, 2-28

EE date format element, 2-49

embedded SQL, xlv, 1-4, 6-5

precompiler support of, 6-5

EMPTY_BLOB function, 4-37

EMPTY_CLOB function, 4-37

ENABLE ALL TRIGGERS clause

of ALTER INDEX, 7-51, 7-52

ENABLE clause

of constraint_clause, 8-149

of CREATE TABLE, 10-41

ENABLE DISTRIBUTED RECOVERY clause

ENABLE NOVALIDATE constraint state, 10-42

ENABLE PARALLEL DML clause

of ALTER SESSION, 7-106

ENABLE QUERY REWRITE clause

of ALTER MATERIALIZED VIEW, 7-70

of CREATE MATERIALIZED VIEW/SNAPSHOT, 9-100

ENABLE RESTRICTED SESSION clause

of CREATE TABLE, 10-11, 10-38

ENABLE ROW MOVEMENT clause

of ALTER TABLE, 8-54

ENABLE STORAGE IN ROW clause

of CREATE TABLE, 10-31

ENABLE TABLE LOCK clause

ENABLE THREAD clause

ENABLE VALIDATE constraint state, 10-42

enable_disable_clause

of ALTER TABLE, 8-55

ENABLE/DISABLE clause

of ALTER TABLE, 8-17

of CREATE TABLE, 10-16

END BACKUP clause

defining for a dimension, 9-37

equality test, 3-5

equijoins, 5-24

equivalency tests, 3-6

ESTIMATE STATISTICS clause

of ANALYZE, 8-103

EXCEPTIONS INTO clause

of ALTER TABLE, 8-52

restrictions on, 8-53

EXCHANGE PARTITION clause

of ALTER TABLE, 8-51

EXCHANGE SUBPARTITION clause

of ALTER TABLE, 8-51

exchanging partitions

restrictions on, 8-53

EXCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 7-80

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-113

EXCLUSIVE lock mode, 11-64

EXECUTE ANY INDEXTYPE system privilege, 11-39

EXECUTE ANY OPERATOR system privilege, 11-40

EXECUTE ANY PROCEDURE system privilege, 11-40

EXECUTE ANY TYPE system privilege, 11-43

EXECUTE object privilege, 11-46

EXECUTE_CATALOG_ROLE role, 11-45

execution plans

determining, 11-23

dropping outlines for, 10-149

EXISTS

EXP_FULL_DATABASE role, 11-45

EXPLAIN PLAN statement, 11-23

explicit data conversion, 2-31, 2-32

expressions

CASE, 5-14

CAST, 5-8

compound, 5-4

computing with the DUAL table, 5-28

CURSOR, 5-11

DECODE, 5-13

function, 5-6

function, built-in, 5-6

in SQL syntax, 5-2

list of, 5-15

object access, 5-12

scalar subqueries as, 5-27

simple, 5-3

type constructor, 5-7

variable, 5-5

extended rowids, 2-20

base 64, 2-21

not directly available, 2-21

EXTENT MANAGEMENT clause

for temporary tablespaces, 10-65

of CREATE TABLESPACE, 10-58, 10-61

extents

allocating for partitions, 8-34

allocating for subpartitions, 8-34

allocating for tables, 8-34

restricting access by instances, 7-45

specifying maximum number for an object, 11-133

specifying number allocated upon object creation, 11-133

specifying the first for an object, 11-131

specifying the percentage of size increase, 11-132

specifying the second for an object, 11-132

external functions, 9-43, 9-132

external LOBs, 2-16

external procedures, 9-132

external users, 9-147, 10-101

FAILED_LOGIN_ATTEMPTS parameter

of ALTER SESSION, 7-110, 7-138

FAST_START_IO_TARGET parameter

FAST_START_PARALLEL_ROLLBACK parameter

specifying as a redo log file group, 11-27

features

new, xlvi

files

specifying as datafiles, 11-27

specifying as tempfiles, 11-27

filespec clause, 11-27

of CREATE CONTROLFILE, 9-16

of CREATE DATABASE, 9-23

of CREATE LIBRARY, 9-86

of CREATE TABLESPACE, 10-57

of CREATE TEMPORARY TABLESPACE, 10-63

FIPS compliance, B-2

FIPS flagging, 7-110

FIRST_ROWS hint, 2-69

FIRST_VALUE function, 4-38

FIXED_DATE parameter

FLAGGER parameter

of ALTER SESSION, 7-110

FLOAT datatype, 2-12

floating-point numbers, 2-12

FLOOR function, 4-40

FLUSH SHARED POOL clause

FM format model modifier, 2-54

FM number format element, 2-44

FOR CATEGORY clause

of CREATE OUTLINE, 9-120

FOR clause

of ANALYZE ... COMPUTE STATISTICS, 8-101

of ANALYZE ... ESTIMATE STATISTICS, 8-101

of CREATE INDEXTYPE, 9-77

of CREATE SYNONYM, 10-5

of EXPLAIN PLAN, 11-25

FOR EACH ROW clause

of CREATE TRIGGER, 10-75

FOR UPDATE clause

of CREATE MATERIALIZED VIEW/SNAPSHOT, 9-100

of SELECT, 11-92, 11-103

FORCE ANY TRANSACTION system privilege, 11-44

FORCE CLAUSE

of DROP OPERATOR, 10-147

FORCE clause

of COMMIT, 8-134

of CREATE VIEW, 10-108

of DISASSOCIATE STATISTICS, 10-125

of DROP INDEX, 10-137

of DROP INDEXTYPE, 10-139

of DROP TYPE, 11-16

FORCE TRANSACTION system privilege, 11-44

of ROLLBACK, 11-85

FORCE PARALLEL DML clause

of ALTER SESSION, 7-106

FOREIGN KEY clause

of constraint_clause, 8-140, 8-144

foreign key constraints, 8-144

foreign tables

rowids of, 2-21

format models, 2-41

changing the return format, 2-41

date, 2-47

date, changing, 2-48

date, format elements, 2-48

date, maximum length, 2-48

dates, default format, 2-48

modifiers, 2-54

number, 2-43

number, elements of, 2-44

specifying, 2-43

formats

for dates and numbers. See format models

of return values from the database, 2-41

of values stored in the database, 2-41

free lists

specifying for a table, partition, cluster, or index, 11-134

FREELIST GROUPS parameter

of STORAGE clause, 11-134

FREELISTS parameter

of STORAGE clause, 11-134

FROM clause

of queries, 5-25

FROM COLUMNS clause

FROM FUNCTIONS clause

FROM INDEXES clause

FROM INDEXTYPES clause

FROM PACKAGES clause

FROM TYPES clause

function-based indexes, 9-52

FULL hint, 2-69

function expressions

built-in, 5-6

and query rewrite, 7-118

creating, 9-61

disabling, 7-145

enabling, 7-48, 7-51, 7-52, 7-145

functions

3GL, calling, 9-86

See also SQL functions

access to tables and packages, 10-87

avoiding run-time compilation, 7-37

calling, 8-128

datatype of return value, 9-47

declaring

as a Java method, 9-50

as C functions, 9-50

defining an index on, 9-61

examples, 9-50

executing, 8-128

from parallel query process, 9-49

expressions, 5-6

external, 9-43, 9-132

issuing COMMIT or ROLLBACK statements, 7-105

naming rules, 2-86

privileges executed with, 8-84, 10-85

recompiling invalid, 7-37

re-creating, 9-45, 9-80

removing from the database, 10-134

schema executed in, 8-84, 10-85

specifying schema and user privileges for, 9-48

stored, 9-43

storing return value of, 8-129

using a saved copy of, 9-48

user-defined, 4-128

FUNCTIONS clause

FX format model modifier, 2-55

G number format element, 2-44

GC_DEFER_TIME parameter

of ALTER DATABASE, 7-10, 7-14

general recovery clause

GLOBAL PARTITION BY RANGE clause

of CREATE INDEX, 9-67

GLOBAL QUERY REWRITE system privilege, 11-39, 11-40, 11-42

GLOBAL TEMPORARY clause

of CREATE TABLE, 10-17

global users, 9-147, 10-101

GLOBAL_NAMES parameter

globally partitioned indexes, 9-67, 9-68

GRANT ANY PRIVILEGE system privilege, 11-44

GRANT ANY ROLE system privilege, 11-41

GRANT CONNECT THROUGH clause

of ALTER USER, 8-90, 8-91

GRAPHIC datatype (SQL/DS or DB2), 2-24

greater than or equal to tests, 3-6

greater than tests, 3-6

GREATEST function, 4-40

GROUP BY clause

CUBE extension of, 11-100

of SELECT, 11-99

ROLLUP extension of, 11-99

group comparison conditions, 5-18

GROUPING function, 4-41

hash clusters

creating, 9-7

single-table, creating, 9-8

specifying hash function for, 9-8

HASH hint, 2-70

HASH IS clause

of CREATE CLUSTER, 9-8

hash partition

adding, 8-47

hash partitioning clause

of CREATE TABLE, 10-15, 10-35

HASH_AJ hint, 2-72

HASH_AREA_SIZE parameter

HASH_JOIN_ENABLED parameter

HASH_MULTIBLOCK_IO_COUNT parameter

of ALTER SYSTEM, 7-139

HASHKEYS clause

of GROUP BY clause, 11-100

HAVING condition

heap-organized tables

creating, 10-7

hexadecimal value

returning, 2-46

HEXTORAW function, 2-32, 4-42

HH date format element, 2-49

HH12 date format element, 2-49

HH24 date format element, 2-49

hierarchical queries, 2-62, 5-22, 11-98

parent nodes of, 2-62

parent rows of, 5-22

hierarchical query clause

adding to a dimension, 7-35

hierarchies

dropping from a dimension, 7-35

of dimensions, defining, 9-36

HIERARCHY clause

of CREATE DIMENSION, 9-36

high water mark

of clusters, 7-5

of indexes, 7-45

of tables, 8-35, 8-99

hints, 5-22

HS_ADMIN_ROLE role, 11-45

HS_AUTOREGISTER parameter

of ALTER SYSTEM, 7-139

I date format element, 2-49

IDENTIFIED BY clause

of ALTER ROLE. See CREATE ROLE

of ALTER ROLE. See CREATE ROLE.

of CREATE DATABASE LINK, 9-31

IDENTIFIED BY password clause

IDENTIFIED EXTERNALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER USER. See CREATE USER

of CREATE USER, 10-101

IDENTIFIED GLOBALLY clause

of ALTER ROLE. See CREATE ROLE

of ALTER ROLE. See CREATE ROLE.

of ALTER USER, 8-91

of CREATE USER, 10-101

IDLE_TIME parameter

of SET CONSTRAINTS, 11-120

IMMEDIATE clause

IMP_FULL_DATABASE role, 11-45

implicit data conversion, 2-30, 2-32

IN OUT parameter

of CREATE FUNCTION, 9-47

IN parameter

of CREATE function, 9-46

of DROP TABLESPACE, 11-11

INCLUDING CONTENTS clause

INCLUDING NEW VALUES clause

of ALTER MATERIALIZED VIEW LOG, 7-80

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-113

INCLUDING TABLES clause

of DROP CLUSTER, 10-127

incomplete object types, 10-80

creating, 10-80, 10-81

INCREMENT BY clause

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 9-157

INDEX clause

INDEX object privilege, 11-46

INDEX hint, 2-70

index partitions

changing physical attributes of, 7-47

deallocating unused space from, 7-45

dropping, 7-54

marking UNUSABLE, 8-43

modifying the real characteristics of, 7-53

rebuilding, 7-48

unusable, 8-43

renaming, 7-53

specifying tablespace for, 7-50

splitting, 7-54

index subpartitions

allocating extents for, 7-54

changing physical attributes of, 7-47

deallocating unused space from, 7-45, 7-54

marking UNUSABLE, 7-54

rebuilding, 7-48

renaming, 7-53

specifying tablespace for, 7-50

INDEX_ASC hint, 2-70

INDEX_DESC hint, 2-70

indexed clusters

creating, 9-7

indexes

allocating new extents for, 7-45

application-specific, 9-76

ascending, 9-63

based on indextypes, 9-70

bitmap, 9-59

changing attributes of, 7-47

collecting statistics on, 8-98

on composite-partitioned tables, 9-69

creating, 9-52

creating as cluster indexes, 9-59

deallocating unused space from, 7-45

descending, 9-63

and query rewrite, 9-63

as function-based indexes, 9-63

disassociating statistics types from, 10-136

domain, 9-52, 9-70, 9-76

dropping index partitions of, 10-136

examples, 9-71

function-based, 9-52

creating, 9-61

globally partitioned, 9-67, 9-68

granting

on hash-partitioned tables, 9-69

key compression of, 7-50, 9-64

locally partitioned, 9-68

logging attributes of, 9-65

logging rebuild operations on, 7-51

marking as UNUSABLE, 7-52

merging contents of index blocks, 7-52

online, 9-66

parallelizing creation of, 9-67

partitioned, 2-81, 9-52

user-defined, 9-67

partitions

adding new, 7-54

marking UNUSABLE, 7-54

physical attributes of, 9-64

on range-partitioned tables, 9-69

rebuilding, 7-48

rebuilding while online, 7-50

removing from the database, 10-136

renaming, 7-52

reverse, 7-49, 9-65

specifying tablespace for, 7-50

statistics on, 9-66

statistics on rebuild, 7-50

storage characteristics of, 9-64, 11-129

tablespace containing, 9-64

unique, 9-58

unsorted, 9-65

validating structure of, 8-104

INDEXES clause

of CREATE TABLE, 10-12, 10-26

index-organized table clause

index-organized tables

INDEXTYPE clause

indextypes

disassociating from statistics types, 10-138

creating, 9-76

drop routines, invoking, 10-136

granting

removing from the database, 10-138

indexes based on, 9-70

instances of, 9-52

INDEXTYPES clause

forcing rollback of, 11-85

in-doubt transactions

forcing, 8-134

forcing commit of, 8-134

forcing rollback, 11-85

rolling back, 11-83

inequality test, 3-5

INITCAP function, 4-43

INITIAL parameter

of STORAGE clause, 11-131

INITIALLY DEFERRED clause

of constraint_clause, 8-147

INITIALLY IMMEDIATE clause

INITRANS parameter

INSERT ANY TABLE system privilege, 11-42

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE

of CREATE TABLE, 10-23

inline views, 5-26

IN-lists, 2-77

INSERT object privilege, 11-46

INSERT statement, 11-51

append, 2-73

triggers on, 10-70

INSTANCE parameter

of ALTER SESSION, 7-112

instances

global name resolution for, 7-138

setting parameters for, 7-135

INSTEAD OF clause

of CREATE TRIGGER, 10-69

INSTEAD OF triggers, 10-69

INSTR function, 4-43

INSTRB function, 4-44

INT datatype (ANSI), 2-22

INTEGER datatype

INTERSECT set operator, 3-13, 11-101

integers

generating unique, 9-155

integrity constraints. See constraints

internal LOBs, 2-16

International Standards Organization. See ISO

INTERSECT operator, 3-12

INTO clause

of EXPLAIN PLAN, 11-24

of INSERT, 11-53

INTO host_variable clause

of CALL, 8-129

invoker rights clause

of ALTER JAVA, 7-58

of CREATE PROCEDURE, 9-133

of CREATE PACKAGE, 9-123

of CREATE TYPE, 8-84, 10-85

IS NOT NULL operator, 3-7

IS NULL operator, 3-7

ISO, B-1

standards, xlv, 1-2, B-1

ISOLATION_LEVEL parameter

of ALTER SESSION, 7-112

IW date format element, 2-49

IY date format element, 2-49

IYY date format element, 2-49

IYYY date format element, 2-49

J date format element, 2-49

Java class schema object

creating, 9-79, 9-81

dropping, 10-140

resolving, 7-57, 9-81

JAVA clause

mapping to an object type, 10-87

Java method

Java resource schema object

creating, 9-79, 9-81

dropping, 10-140

Java schema object

name resolution of, 9-83

Java source schema object

compiling, 7-57, 9-81

creating, 9-79, 9-81

dropping, 10-140

JOB_QUEUE_PROCESSES parameter

of ALTER SYSTEM, 7-139

JOIN KEY clause

of CREATE DIMENSION, 9-37

join views

modifying, 10-118, 11-54, 11-146

conditions

parallel, and PQ_DISTRIBUTE hint, 2-75

self, 5-25

without join conditions, 5-25

Julian day, specifying, 2-50

key compression, 7-50, 9-64, 10-28

disabling, 7-50, 9-65

of index rebuild, 8-26

of indexes, 7-50, 9-64

disabling, 7-50

of index-organized tables, 10-28

keywords, 2-84

in syntax diagrams, lii

optional, A-4

required, A-3

KILL SESSION clause

of ALTER SYSTEM, 7-132

L number format element, 2-44

LAG function, 4-45

LANGUAGE clause

of CREATE FUNCTION, 9-50

of CREATE PROCEDURE, 9-137

large objects. See LOBs

LAST_DAY function, 4-46

LAST_VALUE function, 4-47

LEAD function, 4-49

LEAST function, 4-50

LENGTH function, 4-51

LENGTHB function, 4-51

less than tests, 3-6

LEVEL clause

of CREATE DIMENSION, 9-36

LEVEL pseudocolumn, 2-62, 11-98

and hierarchical queries, 2-62

levels

adding to a dimension, 7-35

dropping from a dimension, 7-35

of dimensions, defining, 9-36

libraries

creating, 9-86

granting

removing from the database, 10-142

re-creating, 9-86

library units. See Java schema objects

LICENSE_MAX_SESSIONS parameter

of ALTER SYSTEM, 7-140

LICENSE_MAX_USERS parameter

of ALTER SYSTEM, 7-140

LICENSE_SESSIONS_WARNING parameter

of ALTER SYSTEM, 7-140

LIKE conditions, 5-20

LIKE operator, 3-8

linear regression functions, 4-78

LIST CHAINED ROWS clause

of ANALYZE, 8-106

literals

in SQL statements and functions, 2-33

LOB index clause

of CREATE TABLE, 10-32

LOB storage clause

for partitions, 8-23

of ALTER MATERIALIZED VIEW, 7-63, 7-65

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-92

of CREATE TABLE, 10-12, 10-29

LOBs

attributes

initializing, 2-17

columns

difference from LONG and LONG RAW, 2-17

locators, 2-17, 10-31

logging attribute of, 10-25

modifying physical attributes of, 8-29

number of bytes manipulated in, 10-31

saving values in a cache, 8-36, 10-40

specifying directories for, 9-40

storage

characteristics, 10-24, 10-29

in-line, 10-30

outside of row, 10-31

tablespace for

defining, 10-24

LOCAL clause

of CREATE INDEX, 9-68

local users, 9-147, 10-101

locally managed tablespaces

storage characteristics, 11-129

locally partitioned indexes, 9-68

LOCK ANY TABLE system privilege, 11-42

LOCK TABLE statement, 11-62

locking

automatic

overriding, 11-62

locks. See table locks

LOG function, 4-52

LOG_ARCHIVE_DEST parameter

of ALTER SYSTEM, 7-141

LOG_ARCHIVE_DEST_n parameter

of ALTER SESSION, 7-112, 7-141

of ALTER SYSTEM, 7-141

LOG_ARCHIVE_DEST_STATE_n parameter

of ALTER SYSTEM, 7-141

LOG_ARCHIVE_DUPLEX_DEST parameter

LOG_ARCHIVE_MAX_PROCESSES parameter

LOG_ARCHIVE_MIN_SUCCEED_DEST parameter

LOG_ARCHIVE_TRACE parameter

LOG_CHECKPOINT_INTERVAL parameter

LOG_CHECKPOINT_TIMEOUT parameter

of ALTER SYSTEM, 7-143

LOGFILE clause

OF CREATE DATABASE, 9-24

logfile clauses

of ALTER DATABASE, 7-12

LOGFILE GROUP clause

specifying minimal, 10-25

logging

and redo log size, 10-26

LOGGING clause

of ALTER INDEX, 7-47

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of CREATE TABLESPACE, 10-59

of CREATE TABLE, 10-25

logical operators, 3-11

LOGICAL_READS_PER_CALL parameter

LOGICAL_READS_PER_SESSION parameter

to store text strings, 2-12

LOGOFF

LOGOFF event

LOGON

LOGON event

LONG columns

restrictions on, 2-13

to store view definitions, 2-12

where referenced from, 2-13

LONG datatype, 2-12

in triggers, 2-14

LONG RAW

data

converting from CHAR data, 2-16

datatype, 2-16

LONG VARCHAR datatype

LONG VARGRAPHIC datatype (SQL/DS or DB2), 2-24

LOWER function, 4-53

LPAD function, 4-53

LTRIM function, 4-54

MAKE_REF function, 4-55

MANAGE TABLESPACE system privilege, 11-42

managed recovery

of database, 7-11

MANAGED STANDBY RECOVERY clause

of ALTER DATABASE, 7-17

MAP MEMBER clause

of ALTER TYPE, 8-82, 8-83

of CREATE TYPE, 10-88, 10-96

MAP methods

specifying, 8-82, 8-83

master databases, 9-88

master tables, 9-88

materialized join views, 9-107

materialized view logs, 9-107

creating, 9-107

excluding new values from, 7-80

logging changes to, 7-79

parallelizing creation of, 9-111

partition attributes

physical attributes

removing from the database, 10-145

required for fast refresh, 9-107

saving new values in, 7-80

saving old values in, 9-113

storage characteristics

specifying, 9-110

materialized views

allowing update of, 9-100

complete refresh, 7-68, 9-97

constraints on, 8-148

creating, 9-88

creating comments about, 8-131

for data warehousing, 9-88

degree of parallelism, 7-66, 7-78

during creation, 9-94

detail table of, dropping, 10-144

enabling and disabling query rewrite, 9-100

examples, 9-102, 9-113

fast refresh, 7-68, 9-96, 9-97

forced refresh, 7-69

granting

indexes that maintain, 9-96

index characteristics

changing, 7-66

join, 9-107

LOB storage characteristics of, 7-65

logging changes to, 7-67

partitions of, 7-65

physical and storage attributes

changing, 7-65

physical attributes of, 9-92

primary key, 9-98

recording values in master table, 7-79

query rewrite

eligibility for, 8-148

enabling and disabling, 7-70

re-creating during refresh, 7-68

refresh mode

changing, 7-67

refresh time

changing, 7-67

refreshing after DML on master table, 7-69, 9-98

refreshing on next COMMIT, 7-69, 9-97

removing from the database, 10-143

for replication, 9-88

retrieving data from, 11-88

revalidating, 7-71

rowid, 9-98

rowid values

recording in master table, 7-79

saving blocks in a cache, 7-67

storage characteristics of, 9-92

subquery, 9-101

when to populate, 9-94

MAX function, 4-56

MAX_DUMP_FILE_SIZE parameter

of ALTER SYSTEM, 7-143

MAXDATAFILES parameter

of STORAGE clause, 11-133

MAXEXTENTS parameter

MAXINSTANCES parameter

MAXLOGFILES parameter

OF CREATE DATABASE, 9-24

MAXLOGHISTORY parameter

MAXLOGMEMBERS parameter

of CREATE TABLESPACE, 10-58

OF CREATE DATABASE, 9-24

MAXSIZE clause

of ALTER DATABASE, 7-13

of CREATE DATABASE, 9-23

of CREATE TEMPORARY TABLESPACE, 10-64

MAXTRANS parameter

of CREATE SEQUENCE, 9-157

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE

of CREATE TABLE, 10-23

MAXVALUE clause

MAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

media recovery

disabling, 7-20

of database, 7-14

of datafiles, 7-14

of standby database, 7-14

of tablespaces, 7-14

parallelizing, 7-18

restrictions, 7-14

sustained standby recovery, 7-17

MEMBER clause

of CREATE TYPE BODY, 10-95

of CREATE TYPE, 10-86

membership conditions, 5-19

MERGE hint, 2-75

MERGE PARTITIONS clause

of ALTER TABLE, 8-50

MERGE_AJ hint, 2-72

MI date format element, 2-49

MI number format element, 2-44

migrated rows

listing, 8-106

MIN function, 4-58

MINEXTENTS parameter

of STORAGE clause, 11-133

MINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 8-39

MINIMUM EXTENT clause

of CREATE TABLESPACE, 10-59

MINUS operator, 3-12

MINUS set operator, 3-13, 11-101

MINVALUE

of ALTER SEQUENCE. See CREATE SEQUENCE

MINVALUE clause

MM date format element, 2-49

MOD function, 4-59

MODE clause

of LOCK TABLE, 11-64

MODIFY clause

of ALTER TABLE, 8-23

MODIFY CONSTRAINT clause

of ALTER TABLE, 8-25

MODIFY DEFAULT ATTRIBUTES clause

of ALTER TABLE, 8-41

MODIFY LOB clause

of ALTER MATERIALIZED VIEW, 7-63, 7-65

MODIFY LOB storage clause

MODIFY NESTED TABLE clause

of ALTER TABLE, 8-28

MODIFY PARTITION clause

of ALTER MATERIALIZED VIEW, 7-66

of ALTER TABLE, 8-42

MODIFY SUBPARTITION clause

of ALTER INDEX, 7-44, 7-54

MODIFY VARRAY clause

modifying space for each cluster key, 7-4

MON date format element, 2-49, 2-51

MONITORING clause

MONTH date format element, 2-49, 2-51

of CREATE TABLE, 10-40

MONTHS_BETWEEN function, 4-60

MOUNT clause

of ALTER DATABASE, 7-25

MOVE clause

of ALTER TABLE, 8-25

MOVE ONLINE clause

of ALTER TABLE, 8-26

MOVE PARTITION clause

of ALTER TABLE, 8-44

MOVE SUBPARTITION clause

of ALTER TABLE, 8-45

MTS_DISPATCHERS parameter

of ALTER SYSTEM, 7-143

MTS_SERVERS parameter

of ALTER SYSTEM, 7-144

multi-threaded server

system parameters, 7-143

NAMED clause

and object naming rules, 2-84

names

schema objects, 2-83

namespaces

for nonschema objects, 2-85

for schema objects, 2-84

NATIONAL CHAR datatype (ANSI), 2-22

NATIONAL CHAR VARYING datatype (ANSI), 2-22

NATIONAL CHARACTER datatype (ANSI), 2-22

national character set

fixed vs. variable width, 2-8, 2-9

multibyte character data, 2-19

multibyte character sets, 2-8, 2-9

variable-length strings, 2-9

NATIONAL CHARACTER SET clause

of CREATE DATABASE, 9-26

NATIONAL CHARACTER SET parameter

NATIONAL CHARACTER VARYING datatype

national language support. See NLS

NCHAR datatype, 2-8

NCHAR VARYING datatype (ANSI), 2-22

NCLOB datatype, 2-19

transactional support of, 2-19

negative scale, 2-11

nested subqueries, 5-26

NESTED TABLE clause

of ALTER TABLE, 8-23

of CREATE TABLE, 10-13, 10-33

nested table types, 2-26

compared with varrays, 2-30

comparison rules, 2-30

creating, 10-80, 10-84

dropping the body of, 11-17

dropping the specification of, 11-15

modifying, 8-28

nested tables

changing returned value, 8-28

creating, 10-90

defining as index-organized tables, 8-23

storage characteristics of, 8-23, 10-33

new features, xlvi

NEW_TIME function, 4-61

NEXT clause

of ALTER MATERIALIZED VIEW...REFRESH, 7-69

NEXT parameter

of STORAGE clause, 11-132

NEXT_DAY function, 4-62

NEXTVAL pseudocolumn, 2-59, 9-155

NLS parameters

NLS_CALENDAR parameter

NLS_CHARSET_DECL_LEN function, 4-62

NLS_CHARSET_ID function, 4-63

NLS_CHARSET_NAME function, 4-64

NLS_COMP parameter

NLS_CURRENCY parameter

NLS_DATE_FORMAT parameter

NLS_DATE_LANGUAGE parameter, 2-52

NLS_INITCAP function, 4-64

NLS_ISO_CURRENCY parameter

NLS_LANGUAGE parameter, 2-52, 5-24

NLS_TERRITORY parameter, 2-52

NLS_LOWER function, 4-65

NLS_NUMERIC_CHARACTERS parameter

of ALTER SESSION, 7-115

NLS_SORT parameter, 5-24

of ALTER SESSION, 7-115

of ALTER SESSION, 7-115

NLS_UNION_CURRENCY parameter

of ALTER DATABASE, 7-12, 7-21

NLS_UPPER function, 4-67

NLSSORT function, 4-66

NO_EXPAND hint, 2-76

NO_INDEX hint, 2-71

NO_MERGE hint, 2-76

NO_PUSH_PRED hint, 2-78

NOAPPEND hint, 2-74

NOARCHIVELOG clause

NOAUDIT statement, 11-66

NOCACHE clause

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of ALTER SEQUENCE. See CREATE SEQUENCE

of ALTER TABLE, 8-36

of CREATE CLUSTER, 9-10

of CREATE TABLE, 10-39

NOCACHE hint, 2-77

NOCOMPRESS clause

of ALTER TABLE, 8-26

of CREATE TABLE, 10-28

NOCOPY clause

of CREATE FUNCTION, 9-47

NOCYCLE clause

of ALTER SEQUENCE. See CREATE SEQUENCE, 7-102

NOFORCE clause

of CREATE JAVA, 9-81

of CREATE VIEW, 10-108

NOLOGGING clause

of ALTER INDEX, 7-47

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of CREATE TABLESPACE, 10-59

of CREATE TABLE, 10-25

NOMAXVALUE clause

NOMAXVALUE parameter

of ALTER SEQUENCE. See CREATE SEQUENCE

NOMINIMIZE RECORDS PER BLOCK clause

of ALTER TABLE, 8-39

NOMINVALUE clause

of ALTER SEQUENCE. See CREATE SEQUENCE, 7-102

NOMONITORING clause

of CREATE TABLE, 10-40

NONE clause

nonpadded comparison semantics, 2-27

nonequivilancy tests, 3-6

nonschema objects

list of, 2-80

namespaces, 2-85

NOORDER clause

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 9-159

NOPARALLEL clause

of CREATE INDEX, 7-6, 7-18, 7-46, 7-66, 7-78, 8-55, 9-10, 9-67, 9-94, 9-112, 10-40

NOPARALLEL hint, 2-74

NOPARALLEL_INDEX hint, 2-75

NORELY clause

NORESETLOGS clause

of constraint_clause, 8-149

NOREWRITE hint, 2-76

NOSORT clause

of ALTER INDEX, 9-65

NOT DEFERRABLE clause

of constraint_clause, 8-147

NOT IDENTIFIED clause

of ALTER ROLE. See CREATE ROLE

of constraint_clause, 8-142

NOT IN operator, 3-7

NOT NULL clause

NOT NULL constraints, 8-142

not null constraints, 8-142

NOT operator, 3-11, 3-12

NOWAIT clause

of LOCK TABLE, 11-64

NTILE function, 4-67

null, 2-57

difference from zero, 2-57

in conditions, 2-58

table of, 2-59

in functions, 2-58

with comparison operators, 2-58

NULL clause

of constraint_clause, 8-142

NULL conditions, 5-20

NUMBER datatype, 2-10

converting to VARCHAR2, 2-43

precision, 2-10

scale, 2-10

number format models, 2-43

number functions, 4-4

numbers

comparison rules, 2-26

floating-point, 2-10, 2-12

NUMERIC datatype (ANSI), 2-22

NUMTODSINTERVAL function, 4-69

NUMTOYMINTERVAL function, 4-70

NVARCHAR2 datatype, 2-9

NVL function, 4-71

NVL2 function, 4-72

object access expressions, 5-12

object cache, 7-116, 7-144

OBJECT IDENTIFIER clause

of CREATE TABLE, 10-21

object identifiers

contained in REFs, 2-25

of object views, 10-109

primary key, 10-21

specifying, 10-21

specifying an index on, 10-21

system-generated, 10-21

object privileges

granting, 9-146

multiple, 9-152

on specific columns, 11-36

on a database object

revoking, 11-78

revoking

from a role, 11-73, 11-77

from a user, 11-73, 11-77

from PUBLIC, 11-77

object reference functions, 4-14

object tables

adding rows to, 11-51

object type bodies

object type tables

object type values

comparing, 10-88, 10-96

object types, 2-24

object views

adding rows to the base table of, 11-51

defining, 10-105

OBJECT_CACHE_MAX_SIZE_PERCENT parameter

of ALTER SYSTEM, 7-144

OBJECT_CACHE_OPTIMAL_SIZE parameter

of ALTER ROLLBACK SEGMENT, 7-100

of ALTER SYSTEM, 7-144

objects. See object types or database objects

OF clause

of CREATE VIEW, 10-109

OF object_type clause

of CREATE TABLE, 10-18

OFFLINE clause

of AUDIT schema_objects, 8-118

OIDINDEX clause

of CREATE TABLE, 10-21

OIDs. See object identifiers

ON clause

of CREATE OUTLINE, 9-120

ON COMMIT clause

of CREATE TABLE, 10-21

ON DATABASE clause

of CREATE TRIGGER, 10-73

ON DEFAULT clause

ON DELETE CASCADE clause

ON DELETE SET NULL clause

of AUDIT schema_objects, 8-119

ON DIRECTORY clause

ON NESTED TABLE clause

of CREATE TRIGGER, 10-73

ON object clause

of CREATE MATERIALIZED VIEW, 9-95

ON PREBUILT TABLE clause

ON SCHEMA clause

of CREATE TRIGGER, 10-73

ONLINE clause

of ALTER ROLLBACK SEGMENT, 7-99

of CREATE INDEX, 9-66

online indexes, 9-66

rebuilding, 8-26

online redo logs

reinitializing, 7-23

OPEN NORESETLOGS clause

OPEN READ ONLY clause

OPEN READ WRITE clause

OPEN RESETLOGS clause

system privileges on, 11-40

granting

unary, 3-2

binding to a function, 9-117

creating, 9-115

dropping, 10-147

function providing implementation, 9-117

how bindings are implemented, 9-117

implementation type, 9-117

return type of binding, 9-117

OPTIMAL parameter

of STORAGE clause, 11-135

optimization

setting session parameters, 7-116

OPTIMIZER_INDEX_CACHING parameter

OPTIMIZER_INDEX_COST_ADJ parameter

OPTIMIZER_MAX_PERMUTATIONS parameter

OPTIMIZER_MODE parameter

OPTIMIZER_PERCENT_PARALLEL parameter

of CREATE FUNCTION, 9-45, 9-80

OR operator, 3-11, 3-12

OR REPLACE clause

of CREATE CONTEXT, 9-13

of CREATE DIRECTORY, 9-41

of CREATE LIBRARY, 9-86

of CREATE OUTLINE, 9-120

of CREATE PACKAGE, 9-123

of CREATE PACKAGE BODY, 9-128

of CREATE PROCEDURE, 9-134

of CREATE TRIGGER, 10-68

of CREATE TYPE, 10-84

of CREATE TYPE BODY, 10-95

of CREATE VIEW, 10-107

Oracle reserved words, C -1

Oracle Tools

support of SQL, 1-5

Oracle8i

Enterprise Edition

features and functionality, xlv

new features, xlvi

ORDER BY clause

of CREATE TABLE, 10-47

of queries, 5-23

of SELECT, 5-23, 11-91, 11-102

with ROWNUM, 2-65

of subqueries in CREATE TABLE, 10-47

ORDER clause

of ALTER SEQUENCE. See CREATE SEQUENCE

of CREATE SEQUENCE, 9-159

ORDER MEMBER clause

of ALTER TYPE, 8-82, 8-83

of CREATE TYPE, 10-89

of CREATE TYPE BODY, 10-96

ORDER methods

specifying, 8-82, 8-83

ORDERED hint, 2-71

ORDERED_PREDICATES hint, 2-78

ordinal numbers

specifying, 2-54

spelling out, 2-54

OUT parameter

of CREATE FUNCTION, 9-46

assigning to a different category, 7-82, 7-84

outer joins, 5-25, 11-97

restrictions, 5-25

outlines

automatically creating and storing, 7-136

creating, 9-119

dropping from the database, 10-149

enabling and disabling dynamically, 9-119

granting

system privileges on, 11-40

rebuilding, 7-82, 7-84

renaming, 7-82, 7-84

replacing, 9-120

storing during the session, 7-109

storing for the instance, 7-148

use by the optimizer, 7-121, 7-148

used to generate execution plans, 9-119

OVERFLOW clause

of ALTER INDEX, 7-44

of ALTER TABLE, 8-40

of CREATE TABLE, 10-28

package bodies

creating, 9-127

re-creating, 9-128

removing from the database, 10-150

PACKAGE clause

packaged procedures

dropping, 10-152

packages

disassociating statistics types from, 10-151

creating, 9-122

invoker rights, 9-124

redefining, 9-123

removing from the database, 10-150

specifying schema and privileges of, 9-124

PACKAGES clause

of ALTER MATERIALIZED VIEW, 7-63, 7-66

PARALLEL clause

of ALTER CLUSTER, 7-5

of ALTER DATABASE, 7-18

of ALTER INDEX, 7-41

of ALTER MATERIALIZED VIEW LOG, 7-77, 7-78

of ALTER TABLE, 8-54

of CREATE CLUSTER, 9-9

of CREATE INDEX, 9-67

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-91

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-110

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of CREATE TABLE, 10-16, 10-40

parallel execution

hints, 2-74

of DDL statements, 7-106

of DML statements, 7-106

PARALLEL hint, 2-74

parallel joins

and PQ_DISTRIBUTE hint, 2-75

PARALLEL_ADAPTIVE_MULTI_USER parameter

PARALLEL_BROADCAST_ENABLED parameter

parallel_clause

of ALTER INDEX, 7-46

PARALLEL_ENABLE clause

of CREATE FUNCTION, 9-49

PARALLEL_INSTANCE_GROUP parameter

of ALTER SESSION parameter, 7-117

PARALLEL_MIN_PERCENT parameter

PARALLEL_THREADS_PER_CPU parameter

of CREATE TABLE, 10-14, 10-34

parameters

in syntax diagrams, lii

optional, A-4

required, A-3

PARAMETERS clause

of CREATE INDEX, 9-71

PARTITION ... LOB storage clause

of ALTER TABLE, 8-23

PARTITION BY HASH clause

of CREATE TABLE, 10-35

PARTITION BY RANGE clause

PARTITION clause

of ANALYZE, 8-100

of CREATE INDEX, 9-68

of CREATE TABLE, 10-36

PARTITION_VIEW_ENABLED parameter

partitioned indexes, 2-81, 9-52, 9-68

user-defined, 9-67

partitioned tables, 2-81

partition-extended table names, 2-81

in DML statements, 2-82

restrictions on, 2-82

syntax, 2-82

partitioning

by range, 10-14

clauses

of ALTER INDEX, 7-42

of ALTER MATERIALIZED VIEW, 7-63, 7-65

of ALTER MATERIALIZED VIEW LOG, 7-77, 7-78

of ALTER TABLE, 8-41

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-92

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-110

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-112

partitions

PASSWORD EXPIRE clause

of ALTER USER. See CREATE USER

of CREATE USER, 10-103

PASSWORD_GRACE_TIME parameter

PASSWORD_LIFE_TIME parameter

PASSWORD_LOCK_TIME parameter

PASSWORD_REUSE_MAX parameter

PASSWORD_REUSE_TIME parameter

PASSWORD_VERIFY_FUNCTION parameter

passwords

expiration of, 10-103

parameters

of ALTER PROFILE, 9-144

of CREATE PROFILE, 9-140

PCTFREE parameter

of STORAGE clause, 11-132

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE.

of CREATE TABLE, 10-22

PCTINCREASE parameter

PCTTHRESHOLD parameter

of CREATE TABLE, 8-40, 10-27

PCTUSED parameter

of LOB storage clause, 8-22

of CREATE INDEX. See CREATE TABLE

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE.

of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE.

of CREATE TABLE, 10-22

PCTVERSION parameter

of CREATE TABLE, 10-32

PERCENT_RANK function, 4-73

PERMANENT clause

physical attributes clause

of a constraint, 8-140

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-91

of ALTER TABLE, 8-27

of CREATE CLUSTER, 9-4

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-110

of CREATE TABLE, 10-11, 10-22

plan stability, 9-119

PLAN_TABLE sample table, 11-23

PL/SQL, xlv

blocks

syntax of, liii

compatibility with earlier releases, 7-117, 7-145

program body

of CREATE FUNCTION, 9-49

PLSQL_V2_COMPATIBILITY parameter

PM (P.M.) date format element, 2-49, 2-51

POWER function, 4-74

PQ_DISTRIBUTE hint, 2-75

PR number format element, 2-44

PRAGMA clause

of CREATE TYPE, 10-82, 10-87

PRAGMA RESTRICT_REFERENCES, 8-81, 10-87

precedence

of operators, 3-2

precision

number of digits of, 2-36

of NUMBER datatype, 2-10

precompilers

Oracle, 1-4

PRESERVE SNAPSHOT LOG clause

of constraint_clause, 8-142

PRIMARY KEY clause

primary key constraints, 8-142

enabling, 10-44

index on, 10-44

primary keys

generating values for, 9-155

PRIOR operator, 3-16

PRIVATE_SGA parameter

privileges. See system privileges or object privileges

procedures

PROFILE clause

of ALTER USER. See CREATE USER

assigning to a user, 10-102

profiles

creating, 9-139

examples, 9-144

deassigning from users, 10-154

granting

system privileges on, 11-40

modifying, examples, 7-92

removing from the database, 10-154

proxy clause

of ALTER USER, 8-90, 8-91

PUBLIC clause

of CREATE ROLLBACK SEGMENT, 9-149

of CREATE SYNONYM, 10-4

of DROP DATABASE LINK, 10-129

public database links

dropping, 10-129

public rollback segments, 9-149

public synonyms, 10-4

dropping, 11-5

PURGE SNAPSHOT LOG clause

Q date format element, 2-49

PUSH_PRED hint, 2-78

queries, 5-21, 11-88

query rewrite

and dimensions, 9-34

and function-based indexes, 7-118

and rule-based optimization, 7-118

consistency level, 7-118, 7-146

defined, 11-88

disabling, 7-118, 7-145

enabling, 7-118, 7-145

QUERY REWRITE system privilege, 11-39, 11-40, 11-42

QUERY_REWRITE_ENABLED parameter

of ALTER SESSION, 7-118

QUERY_REWRITE_INTEGRITY parameter

of ALTER SESSION, 7-118

of ALTER SYSTEM, 7-146

QUOTA clause

of ALTER USER. See CREATE USER

use with database object names, 2-86

quotation marks

range conditions, 5-20

range partition

adding, 8-46

creating, 10-34

RANK function, 4-74

RATIO_TO_REPORT function, 4-75

RAW data

converting from CHAR data, 2-16

RAW datatype, 2-16

RAWTOHEX function, 2-32, 4-76

READ object privilege, 11-46

READ ONLY clause

READ WRITE clause

REAL datatype

of ALTER INDEX, 7-42, 7-48

REBUILD clause

of ALTER OUTLINE, 7-82

REBUILD COMPRESS clause

REBUILD COMPUTE STATISTICS clause

REBUILD LOGGING clause

of ALTER INDEX, 7-51

REBUILD NOCOMPRESS clause

REBUILD NOLOGGING clause

of ALTER INDEX, 7-51

REBUILD NOREVERSE clause

REBUILD ONLINE clause

REBUILD PARAMETERS clause

of ALTER INDEX, 7-51

REBUILD PARTITION clause

REBUILD REVERSE clause

REBUILD SUBPARTITION clause

REBUILD TABLESPACE clause

REBUILD UNUSABLE LOCAL INDEXES clause

of ALTER DATABASE, 7-10, 7-17

RECOVER AUTOMATIC clause

of ALTER DATABASE, 7-15

RECOVER CANCEL clause

RECOVER clause

of ALTER DATABASE, 7-10, 7-14

RECOVER CONTINUE clause

of ALTER DATABASE, 7-10, 7-15

RECOVER DATABASE clause

RECOVER DATAFILE clause

RECOVER LOGFILE clause

RECOVER MANAGED STANDBY DATABASE clause

of ALTER DATABASE, 7-11

RECOVER STANDBY DATAFILE clause

of ALTER DATABASE, 7-16

RECOVER STANDBY TABLESPACE clause

of ALTER DATABASE, 7-16

RECOVER TABLESPACE clause

distributed, enabling, 7-133

RECOVERABLE, 7-48, 10-26

See also LOGGING clause

recovery

of database, 7-10

RECOVERY_CATALOG_OWNER role, 11-45

redo logs

adding, 7-21

automatic archiving of, 7-127

automatic name generation, 7-15

disabling specified threads in a parallel server, 7-28

dropping, 7-22

enabling specified threads in a parallel server, 7-28

members

adding to existing groups, 7-22

for media recovery, 7-16

switching groups, 7-134

REF columns

specifying, 10-20

specifying from table or column level, 10-20

REF function, 4-77

REFERENCES clause

REFERENCES object privilege, 11-46

references to objects. See REFs

REFERENCING clause

of CREATE TRIGGER, 10-68, 10-74

referential integrity constraints, 8-143, 8-144

REFRESH clause

of ALTER MATERIALIZED VIEW, 7-64, 7-67

of CREATE MATERIALIZED VIEW / SNAPSHOT, 9-91

REFRESH COMPLETE clause

of ALTER MATERIALIZED VIEW, 7-68

of ALTER MATERIALIZED VIEW, 7-68

REFRESH FAST clause

of ALTER MATERIALIZED VIEW, 7-69

REFRESH FORCE clause

of ALTER MATERIALIZED VIEW, 7-69

REFRESH ON COMMIT clause

of ALTER MATERIALIZED VIEW, 7-69

REFRESH ON DEMAND clause

as containers for OIDs, 2-25

REFs, 2-25, 8-145

dangling, 8-104

validating, 8-104

REFTOHEX function, 4-78

REGR_AVGX function, 4-78

REGR_AVGY function, 4-78

REGR_COUNT function, 4-78

REGR_INTERCEPT function, 4-78

REGR_R2 function, 4-78

REGR_SLOPE function, 4-78

REGR_SXX function, 4-78

REGR_SXY function, 4-78

REGR_SYY function, 4-78

relational tables

creating, 10-8

RELY clause

REMOTE_DEPENDENCIES_MODE parameter

of ALTER SESSION, 7-119

of ALTER SYSTEM, 7-147

REMOTE_LOGIN_PASSWORDFILE parameter

and control files, 9-15

and databases, 9-21

RENAME clause

of ALTER TABLESPACE, 8-70

of ALTER OUTLINE, 7-82

of ALTER TABLE, 8-38

RENAME DATAFILE clause

RENAME FILE clause

of ALTER DATABASE, 7-9, 7-27

RENAME GLOBAL_NAME clause

of ALTER DATABASE, 7-27

RENAME PARTITION clause

of ALTER TABLE, 8-44

RENAME statement, 11-71

RENAME SUBPARTITION clause

of ALTER TABLE, 8-44

REPLACE AS OBJECT clause

reserved words, 2-84, C -1

REPLACE function, 4-85

RESET COMPATIBILITY clause

of ALTER DATABASE, 7-27

RESETLOGS parameter

of ALTER JAVA SOURCE, 7-58

RESOLVE clause

of ALTER JAVA CLASS, 7-58

of CREATE JAVA, 9-81

RESOLVER clause

of ALTER JAVA CLASS, 7-58

resource parameters

of CREATE PROFILE, 9-140

RESOURCE role, 11-45

RESOURCE_LIMIT parameter

of ALTER SYSTEM, 7-147

RESOURCE_MANAGER_PLAN parameter

of ALTER SYSTEM, 7-147

response time

optimizing, 2-69

RESTRICT_REFERENCES pragma

compatibility and migration of, 2-21

restricted rowids, 2-20

RESTRICTED SESSION system privilege, 11-41

RESUME clause

RETURN clause

of CREATE FUNCTION, 9-47

of CREATE OPERATOR, 9-117

RETURNING clause

of INSERT, 11-53, 11-57

of UPDATE, 11-144

REUSE clause

of filespec clause, 11-28

REUSE STORAGE clause

of TRUNCATE, 11-141

REVERSE clause

of ALTER USER, 8-90, 8-91

reverse indexes, 9-65

REVOKE CONNECT THROUGH clause

REVOKE statement, 11-73

REWRITE hint, 2-76

RM date format element, 2-49

RN number format element, 2-44

RNDS parameter

RNPS parameter

changing storage characteristics, 7-99

roles

rollback segments

bringing online, 7-99

creating, 9-149

granting

system privileges on, 11-41

public, 9-149

reducing size, 7-99

removing from the database, 10-157

specifying optimal size of, 11-135

specifying tablespaces for, 9-150

SQL examples, 9-151

storage characteristics, 9-150, 11-129

taking offline, 7-99

ROLLBACK statement, 11-83

ROLLUP clause

of SELECT statements, 11-99

ROLLUP operation

of queries and subqueries, 11-99

ROUND function

date function, 4-87

format models, 4-127

number function, 4-86

routines

calling, 8-128

executing, 8-128

ROW EXCLUSIVE lock mode, 11-64

ROW SHARE lock mode, 11-64

ROW_NUMBER function, 4-87

ROWID datatype, 2-20

ROWID hint, 2-71

ROWID pseudocolumn, 2-20, 2-21, 2-63

rowids

block portion of, 2-20

description of, 2-20

extended, 2-20

base 64, 2-21

not directly available, 2-21

file portion of, 2-20

nonphysical, 2-21

of foreign tables, 2-21

of index-organized tables, 2-21

restricted, 2-20

compatibility and migration of, 2-21

row portion of, 2-20

uses for, 2-64

ROWIDTOCHAR function, 2-32, 4-89

ROWNUM pseudocolumn, 2-64

uses for, 2-65

rows

adding to a table, 11-51

allowing movement of between partitions, 10-11

inserting

into partitions, 11-54

into remote databases, 11-54

into subpartitions, 11-54

movement between partitions, 10-38

removing

from a cluster, 11-138

from a table, 11-138

from partitions and subpartitions, 10-117

from tables and views, 10-115

selecting in hierarchical order, 5-22

specifying constraints on, 8-144

stored in ascending order, 8-149

storing if in violation of constraints, 8-52

RPAD function, 4-89

RR date format element, 2-49, 2-52

interpreting, 2-53

RRRR date format element, 2-49

RTRIM function, 4-90

RULE hint, 2-69

run-time compilation

avoiding, 7-87, 8-94

S number format element, 2-44

SALES sample table, 4-3

SAMPLE clause

of SELECT, 11-95

of SELECT and subqueries, 11-90

SAVEPOINT statement, 11-86

savepoints

erasing, 8-133

rolling back to, 11-84

specifying, 11-86

scalar subqueries, 5-27

scale

greater than precision, 2-11

negative, 2-11

of NUMBER datatype, 2-10

SCC date format element, 2-49

SCHEMA clause

changing for a session, 7-109

schema objects, 2-79

schemas

creating, 9-152

definition of, 2-79

scientific notation, 2-45

SCOPE clause

of column ref constraints, 8-146

scope constraints, 8-146

segment attributes clause

of CREATE TABLE, 10-11

SELECT

object privilege, 11-46

statement, 11-88

SELECT ANY SEQUENCE system privilege, 11-41

SELECT ANY TABLE system privilege, 11-42

select lists, 5-21

ordering, 5-23

SELECT statement, 5-21

SELECT_CATALOG_ROLE role, 11-45

self joins, 5-25

sequences, 2-59, 9-155

SERVERERROR event

triggers on, 10-72, 10-73

service name

of remote database, 9-31

session control statements, 6-5

PL/SQL support of, 6-5

session locks

releasing, 7-132

SESSION_CACHED_CURSORS parameter

of ALTER SESSION, 7-119

SESSION_ROLES view, 11-122

sessions

calculating resource cost limits, 7-94

changing resource cost limits, 7-94

disconnecting, 7-131

global name resolution for, 7-111

granting

system privileges on, 11-41

limiting resource costs, 7-94

modifying characteristics of, 7-108

number of concurrent, 7-140

object cache, 7-116

restricted, 7-133

terminating, 7-132

SESSIONS_PER_USER parameter

SET CONSTRAINT(S) statement, 11-120

SET clause

of ALTER SESSION, 7-108

of ALTER SYSTEM, 7-135

of UPDATE, 11-147

SET DATABASE clause

set operators, 3-12, 11-101

SET ROLE statement, 11-122

SET STATEMENT_ID clause

of EXPLAIN PLAN, 11-24

SET TRANSACTION statement, 11-125

SET UNUSED clause

of ALTER TABLE, 8-30

SGA. See system global area

SHARE ROW EXCLUSIVE lock mode, 11-64

SHARE UPDATE lock mode, 11-64

SHARED clause

of CREATE DATABASE LINK, 9-29

shared server processes

creating additional, 7-143

terminating, 7-143

SHRINK clause

of ALTER ROLLBACK SEGMENT, 7-100

SHUTDOWN clause

simple comparison conditions, 5-17

SHUTDOWN event

triggers on, 10-72

SIGN function, 4-90

simple expressions, 5-3

SIN function, 4-91

SINGLE TABLE clause

of CREATE CLUSTER, 9-8

single-row functions, 4-4

miscellaneous, 4-6

SINH function, 4-91

SIZE clause

of filespec clause, 11-28

SKIP_UNUSABLE_INDEXES parameter

SMALLINT datatype

snapshot logs. See materialized view logs

snapshots. See materialized views

SNMPAGENT role, 11-45

SOME operator, 3-6

SORT_AREA_RETAINED_SIZE parameter

SORT_AREA_SIZE parameter

SORT_MULTIBLOCK_READ_COUNT parameter

SP date format element suffix, 2-54

SOUNDEX function, 4-92

SPECIFICATION clause

of ALTER INDEX, 7-44, 7-54

spelled numbers

specifying, 2-54

SPLIT PARTITION clause

of ALTER TABLE, 8-49

SPTH date format element suffix, 2-54

SQL

Oracle Tools support of, 1-5

parameters, A-3

standards, 1-2, B-1

statements

auditing, 8-120

determining the cost of, 11-23

syntax, 7-1, A-1

SQL function

ACOS, 4-14

SQL functions

ABS, 4-14

ASIN, 4-16

ATAN, 4-16

ATAN2, 4-17

AVG, 4-18

BFILENAME, 4-19

BITAND, 4-20

CEIL, 4-21

character

returning character values, 4-4

returning number values, 4-5

CHR, 4-22

CORR, 4-25

COS, 4-26

COSH, 4-27

date, 4-5

DUMP, 4-36

EXP, 4-38

LAG, 4-45

LEAD, 4-49

linear regression, 4-78

LN, 4-52

LOG, 4-52

LOWER, 4-53

LPAD, 4-53

LTRIM, 4-54

MAKE_REF, 4-55

MAX, 4-56

MIN, 4-58

MOD, 4-59

MONTHS_BETWEEN, 4-60

NEW_TIME, 4-61

NEXT_DAY, 4-62

NLS_CHARSET_DECL_LEN, 4-62

NLS_CHARSET_ID, 4-63

NLS_CHARSET_NAME, 4-64

NLV2, 4-72

NUMTODSINTERVAL, 4-69

NUMTOYMINTERVAL, 4-70

NVL, 4-71

object reference, 4-14

PERCENT_RANK, 4-73

POWER, 4-74

RANK, 4-74

RATIO_TO_REPORT, 4-75

REF, 4-77

RPAD, 4-89

SIGN, 4-90

SIN, 4-91

SINH, 4-91

SQRT, 4-93

SUM, 4-99

TAN, 4-107

TO_CHAR (date), 4-108

TO_CHAR (number), 4-109

TO_SINGLE_BYTE, 4-113

TRANSLATE, 4-113

TRANSLATE...USING, 4-114

TRIM, 4-116

TRUNC (date), 4-117

TRUNC (number), 4-117

UID, 4-118

SQL statements

auditing

DDL, 6-2

determining the execution plan for, 11-23

DML, 6-4

rolling back, 11-83

session control, 6-5

system control, 6-5

tracking the occurrence in a session, 8-114

transaction control, 6-4

undoing, 11-83

SQL_TRACE parameter

Oracle compliance with, B-2

SQL92, 1-2

SQL/DS datatypes, 2-22

conversion to Oracle datatypes, 2-23

restrictions on, 2-24

SQRT function, 4-93

SS date format element, 2-49

SSSSS date format element, 2-49

standalone procedures

dropping, 10-152

standard SQL, B-1

Oracle extensions to, B-5

standby control file

creating, 7-24

standby database

activating, 7-25

mounting, 7-25

recovering, 7-16

STANDBY_ARCHIVE_DEST parameter

STAR_TRANSFORMATION hint, 2-76

star transformation, 2-76

STAR_TRANSFORMATION_ENABLED parameter

of ALTER SESSION, 7-121

START WITH clause

of ALTER MATERIALIZED VIEW...REFRESH, 7-69

of CREATE SEQUENCE, 9-157

of SELECT, 11-98

STARTUP event

triggers on, 10-72

STATIC clause

of CREATE TYPE BODY, 10-95

of CREATE TYPE, 10-86

statistics

computing exactly, 8-101

deleting from the data dictionary, 8-104

estimating, 8-103

forcing disassociation, 10-125

on indexes, 9-66

user-defined

dropping, 10-136, 10-138, 10-151, 11-7, 11-15

statistics types

associating

with columns, 8-112

with datatypes, 8-112

with domain indexes, 8-112

with functions, 8-112

with indextypes, 8-112

with packages, 8-112

disassociating

from columns, 10-123

from domain indexes, 10-123

from functions, 10-123

from indextypes, 10-123

from packages, 10-123

from types, 10-123

STDDEV function, 4-93

STDDEVP function, 4-95

STDDEVS function, 4-96

storage characteristics

resetting, 11-138

STORAGE clause, 11-129

of ALTER INDEX, 7-41, 7-42, 7-47

of ALTER ROLLBACK SEGMENT, 7-99, 7-100

of CREATE CLUSTER, 9-6

of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 9-110

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE

of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE.

of CREATE ROLLBACK SEGMENTS, 9-150

of CREATE TABLE, 10-11, 10-24

of CREATE TABLESPACE, 10-58

STORAGE IN ROW clause

STORE IN DEFAULT clause

STORE IN tablespace clause

of CREATE TABLE, 10-14, 10-36

stored functions, 9-43

Structured Query Language. See SQL

SUBPARTITION BY HASH clause

SUBPARTITION clause

of ANALYZE, 8-100

subpartition-extended table names, 2-81

of CREATE TABLE, 10-38

in DML statements, 2-82

restrictions on, 2-82

syntax, 2-82

subpartitions

SUBPARTITIONS clause

of CREATE TABLE, 10-36

subqueries, 5-21, 5-26, 11-88

containing subqueries, 5-26

correlated, 5-27

defined, 5-21

extended subquery unnesting, 5-28

inline views, 5-26

nested, 5-26

scalar, 5-27

to insert table data, 10-46

unnesting, 5-28

using in place of expressions, 5-27

SUBSTR function, 4-98

SUBSTRB function, 4-99

subtotal values

deriving, 11-99

SUM function, 4-99

SUSPEND clause

sustained standby recovery mode, 7-17

terminating, 7-17

timeout period, 7-17

SWITCH LOGFILE clause

SYEAR date format element, 2-49

synonyms

changing the definition of, 11-5

creating, 10-3

granting

system privileges on, 11-42

local, 10-5

private, dropping, 11-5

public, 10-4

dropping, 11-5

remote, 10-5

removing from the database, 11-5

loops, A-4

multipart diagrams, A-5

parameters, lii

SYS schema

database triggers stored in, 10-75

functions stored in, 10-75

SYS_CONTEXT function, 4-101

SYS_GUID function, 4-105

SYSDATE function, 4-106

SYSDBA system privilege, 11-44

SYSOPER system privilege, 11-44

system control statements, 6-5

PL/SQL support of, 6-5

system date

system events

system global area

system privileges

granting, 9-146, 11-31

SYYYY date format element, 2-49

TABLE clause

of UPDATE, 11-145, 11-146

table constraints

defined, 8-136

ROW EXCLUSIVE, 11-63, 11-64

table locks

disabling, 8-56

duration of, 11-62

enabling, 8-56

EXCLUSIVE, 11-63, 11-64

modes of, 11-64

on partitions, 11-63

on remote database, 11-63

on subpartitions, 11-63

and queries, 11-62

ROW SHARE, 11-63, 11-64

SHARE, 11-63

SHARE ROW EXCLUSIVE, 11-64

SHARE UPDATE, 11-64

table REF constraints, 8-137, 8-145

of ALTER TABLE, 8-20

allocating extents for, 8-34

tables

adding rows to, 11-51

aliases, 2-93

in CREATE INDEX, 9-60

in DELETE, 10-119

assigning to a cluster, 10-29

changing degree of parallelism on, 8-54

changing existing values in, 11-142

collecting modification statistics on, 8-37

collecting statistics on, 8-99

creating, 10-7

multiple, 9-152

creating comments about, 8-131

deallocating unused space from, 8-35

default physical attributes

changing, 8-27

degree of parallelism

specifying, 10-7

disassociating statistics types from, 11-7

dropping

along with cluster, 10-127

along with owner, 11-19

indexes of, 11-7

partitions of, 11-7

granting

system privileges on, 11-42

index-organized

overflow segment for, 10-28

space in index block, 8-40, 10-27

inserting rows with a subquery, 10-46

LOB storage of, 10-24

locking, 11-62

logging

insert operations, 8-37

table creation, 10-25

migrated and chained rows in, 8-106

moving to a new segment, 8-25

nested

creating, 10-90

storage characteristics, 10-33

object

creating, 10-9

ordering rows from, 10-47

parallel creation of, 10-40

parallelism

setting default degree, 10-40

partition attributes of, 8-41

partitioned

allowing rows to move between partitions, 8-54

default attributes of, 8-41

partitioning of, 2-81, 10-7

physical attributes

changing, 8-27

relational

creating, 10-8

remote, accessing, 9-28

removing from the database, 11-7

removing rows from, 10-115

renaming, 8-38, 11-71

restricting

records per block, 8-39

references to, 8-146

retrieving data from, 11-88

saving blocks in a cache, 8-36, 10-39

SQL examples, 10-47

storage characteristics, 11-129

defining, 10-7, 10-24

subpartition attributes of, 8-41

transaction specific, 10-17

tablespace for

defining, 10-7, 10-24

temporary

duration of data, 10-21

session-specific, 10-17

unclustering, 10-126

validating structure of, 8-104

with unusable indexes, 7-120

TABLESPACE clause

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-111

of CREATE ROLLBACK SEGMENTS, 9-150

of CREATE TABLE, 10-24

tablespaces, 8-71

allocating space for users, 10-102

allowing write operations on, 8-72

backing up datafiles of, 8-72

bringing online, 8-71, 10-60

coalescing free extents, 8-73

converting

from permanent to temporary, 8-73

from temporary to permanent, 8-73

creating, 10-56

datafile

adding, 8-70

renaming, 8-70

default storage characteristics, 11-129

defining as read only, 8-72

dropping the contents of, 11-11

enable autoextension of, 8-70

extent management of, 10-65

extent size of, 10-59

granting

system privileges on, 11-42

locally managed, 10-61, 11-129

temporary, 10-65

logging attribute of, 8-73, 10-59

managed using dictionary tables, 10-61

managing extents of, 10-61

of session duration, 10-63

permanent objects in, 10-60

recovering, 7-16

removing from the database, 11-10

size of free extents in, 8-71

specifying

datafiles for, 10-58

for a user, 10-102

for index rebuild, 8-26

taking offline, 8-71, 10-60

tempfile

adding, 8-70

temporary

creating, 10-63

specifying for a user, 10-102

temporary objects in, 10-61

TAN function, 4-107

TANH function, 4-107

TEMPFILE clause

of ALTER DATABASE, 7-11, 7-20

of CREATE TEMPORARY TABLESPACE, 10-64

tempfiles

automatic extension of, 10-64

bringing online, 7-20

disabling automatic extension, 7-20

dropping, 7-20

enabling automatic extension, 7-20

resizing, 7-20

reusing, 11-28

size of, 11-28

specifying, 10-64, 11-27

taking offline, 7-20

TEMPORARY clause

of CREATE TABLESPACE, 10-61

temporary tables

creating, 10-7, 10-17

session-specific, 10-17

transaction-specific, 10-17

TEMPORARY TABLESPACE clause

of ALTER USER. See CREATE USER

specifying for a user, 10-102

temporary tablespaces

creating, 10-63

SQL examples, 10-65

text

conventions, li

date and number formats, 2-41

in SQL syntax, 2-33

properties of CHAR and VARCHAR2 datatypes, 2-34

syntax of, 2-33

text date format element, 2-49

TH date format element suffix, 2-54

throughput

optimizing, 2-68

THSP date format element suffix, 2-54

TIME datatype (SQL/DS or DB2), 2-24

TIMED_OS_STATISTICS parameter

TIMED_STATISTICS parameter

of ALTER SESSION, 7-121

TIMESTAMP datatype (SQL/DS or DB2), 2-24

TM number format element, 2-44

TO SAVEPOINT clause

of ROLLBACK, 11-84

TO_CHAR

date conversion function, 4-108

number conversion function, 4-109

TO_CHAR function, 2-32, 2-43, 2-47, 2-54

TO_DATE function, 2-32, 2-47, 2-52, 2-55, 4-110

TO_LOB function, 2-32, 4-111

TO_MULTI_BYTE function, 4-112

TO_NUMBER function, 2-32, 2-43, 4-112

TO_SINGLE_BYTE function, 4-113

top-N queries, 2-65

transaction control statements, 6-4

PL/SQL support of, 6-4

TRANSACTION_AUDITING parameter

TRANSLATE ... USING function, 4-114

transactions

TRANSLATE function, 4-113

triggers

TRIM function, 4-116

TRUNC function

date function, 4-117

format models, 4-127

number function, 4-117

TRUNCATE PARTITION clause

of ALTER TABLE, 8-48

TRUNCATE statement, 11-138

TRUNCATE SUBPARTITION clause

of ALTER TABLE, 8-48

TRUST parameter

type constructor expressions, 5-7

Trusted Oracle, 1-5

TYPES clause

U number format element, 2-44

types. See object types or datatypes

UID function, 4-118

unary operators, 3-2

UNION ALL operator, 3-12

UNION ALL set operator, 3-13, 11-101

UNION operator, 3-12

UNION set operator, 3-13, 11-101

UNIQUE clause

of constraint_clause, 8-141

of CREATE INDEX, 9-58

UNLIMITED TABLESPACE system privilege, 11-43

unique constraints

unique queries, 11-92

universal rowids. See urowids

UNNEST_SUBQUERY parameter, 2-79

unnesting collections, 11-96

examples, 11-115

unnesting subqueries, 5-28

UNRECOVERABLE, 7-48, 10-26

See also NOLOGGING clause

unsorted indexes, 9-65

UNUSABLE clause

of ALTER MATERIALIZED VIEW, 7-66

UNUSABLE LOCAL INDEXES clause

UPDATE ANY TABLE system privilege, 11-42

UPDATE object privilege, 11-46

UPDATE statement, 11-142

triggers on, 10-70

UPPER function, 4-118

UROWID datatype, 2-21

urowids

and foreign tables, 2-21

and index-organized tables, 2-21

description of, 2-21

USE_CONCAT hint, 2-77

USE_MERGE hint, 2-73

USE_NL hint, 2-73

USE_STORED_OUTLINES parameter

of ALTER SESSION, 7-121

USER_COL_COMMENTS view, 8-131

USER function, 4-119

USER_DUMP_DEST parameter

of ALTER SYSTEM, 7-149

USER_TAB_COMMENTS view, 8-131

user-defined functions, 4-128

name precedence of, 4-129

naming conventions, 4-130

restrictions on, 9-46

user-defined operators, 3-16

user-defined statistics

dropping, 10-136, 10-138, 10-151, 11-7, 11-15

user-defined types

categories of, 2-24

defining, 10-84

USERENV function, 4-120

users

USING BFILE clause

USING BLOB clause

USING clause

of CREATE DATABASE LINK, 9-31

of CREATE INDEXTYPE, 9-77

of CREATE OPERATOR, 9-116, 9-117

USING CLOB clause

USING INDEX clause

of CREATE TABLE, 10-16, 10-44

USING ROLLBACK SEGMENT clause

of ALTER MATERIALIZED VIEW...REFRESH, 7-70

of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 9-99

UTLCHN.SQL script, 8-106

UTLEXPT1.SQL script, 8-52

UTLXPLAN.SQL script, 11-23

V number format element, 2-44

V$NLS_PARAMETERS view

VALIDATE REF UPDATE clause

of ANALYZE, 8-104

VALIDATE STRUCTURE clause

of ANALYZE, 8-104

VALUE function, 4-121

VALUES clause

of CREATE INDEX, 9-68

of INSERT, 11-56

VALUES LESS THAN clause

of CREATE TABLE, 10-37

VARCHAR datatype, 2-10

converting to NUMBER, 2-43

VARCHAR2 datatype, 2-9

VARGRAPHIC datatype (SQL/DS or DB2), 2-24

variable expressions, 5-5

VARIANCE function, 4-125

VARP function, 4-122

VARRAY storage clause

of ALTER TABLE, 8-22

of CREATE TABLE, 10-13, 10-32

varrays, 2-25

changing returned value, 8-28

compared with nested tables, 2-30

comparison rules, 2-30

creating, 10-80, 10-83, 10-89

dropping the body of, 11-17

dropping the specification of, 11-15

storage characteristics of, 8-22, 8-29, 10-32

storing out of line, 2-25

VARS function, 4-123

varying arrays. See varrays

views

adding rows to the base table of, 11-51

changing

definition, 11-21

values in base tables, 11-142

creating

before base tables, 10-108

comments about, 8-131

multiple, 9-152

defining, 10-105

granting

system privileges on, 11-44

recompiling, 8-94

re-creating, 10-107

remote, accessing, 9-28

removing

from the database, 11-21

rows from the base table of, 10-115

renaming, 11-71

retrieving data from, 11-88

subquery of, 10-110

restricting, 10-111

W date format element, 2-49

VSIZE function, 4-126

WHEN clause

of CREATE TRIGGER, 10-75

WHENEVER NOT SUCCESSFUL clause

of AUDIT sql_statements, 8-119

WHENEVER SUCCESSFUL clause

of GRANT system_privileges_and_roles, 11-35

WHERE clause

of DELETE, 10-119

of SELECT, 5-22, 11-97

of UPDATE, 11-149

WITH ADMIN OPTION clause

WITH CHECK OPTION clause

of CREATE VIEW, 10-107, 10-111

of DELETE, 10-118

of INSERT, 11-55

of SELECT, 11-90, 11-96

of UPDATE, 11-145

WITH GRANT OPTION clause

of GRANT object_privileges, 11-36

WITH INDEX CONTEXT clause

of CREATE OPERATOR, 9-116, 9-117

WITH OBJECT IDENTIFIER clause

of CREATE VIEW, 10-109

WITH OBJECT OID. See WITH OBJECT IDENTIFIER.

WITH PRIMARY KEY clause

of ALTER MATERIALIZED VIEW, 7-70

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-112

of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 9-96

WITH READ ONLY clause

of CREATE VIEW, 10-107, 10-111

of DELETE, 10-118

of INSERT, 11-55

of SELECT, 11-90, 11-96

of UPDATE, 11-145

WITH ROWID clause

of column ref constraints, 8-146

of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 9-112

of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 9-96

WNDS parameter

WNPS parameter