Oracle8i SQL Reference
Release 8.1.5

A67779-01

Library

Product

Contents

Prev

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  Z 


Symbols

$ number format element, 2-36
(+) operator, 3-16
, (comma)
date format element, 2-41
number format element, 2-36
: (colon) date format element, 2-41
- (dash) date format element, 2-41
; (semicolon) date format element, 2-41
/ (slash) date format element, 2-41
· (period)
date format element, 2-41
number format element, 2-36

Numerics

0 number format element, 2-36
20th century, 2-42, 2-44
specifying, 2-44
21st century, 2-42, 2-44
specifying, 2-44
8 number format element, 2-36
9 number format element, 2-36

A

ABS function, 4-5
ABSI
standards, B - 1
ACCOUNT LOCK clause
of ALTER USER. See CREATE USER
of CREATE USER, 7-428
ACCOUNT UNLOCK clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-428
ACOS function, 4-6
ACTIVATE STANDBY DATABASE clause
of ALTER DATABASE, 7-11
AD (A.D.) date format element, 2-41, 2-43
ADD clause
of ALTER DIMENSION, 7-26
of ALTER TABLE, 7-130
ADD DATAFILE clause
of ALTER TABLESPACE, 7-167
ADD LOGFILE clause
of ALTER DATABASE, 7-9
ADD LOGFILE GROUP clause
of ALTER DATABASE, 7-18
ADD LOGFILE MEMBER clause
of ALTER DATABASE, 7-9, 7-18
ADD LOGFILE THREAD clause
of ALTER DATABASE, 7-18
ADD OVERFLOW clause
of ALTER TABLE, 7-143
ADD PARTITION, 7-148
ADD PARTITION clause
of ALTER TABLE, 7-147, 7-148
ADD PRIMARY KEY clause
of ALTER MATERIALIZED VIEW LOG, 7-56
ADD ROWID clause
of ALTER MATERIALIZED VIEW, 7-56
of ALTER MATERIALIZED VIEW LOG, 7-56
ADD TEMPFILE clause
of ALTER TABLESPACE, 7-167
ADD_MONTHS function, 4-6
ADMINISTER ANY TRIGGER system privilege, 7-501
ADVISE clause
of ALTER SESSION, 7-79
AFTER clause
of CREATE TRIGGER, 7-403
AFTER triggers, 7-403
aggregate functions, 4-5
aliases
for columns, 5-18
for expressions in view query, 7-432
specifying in queries and subqueries, 7-547
ALL clause
of SELECT, 7-545
of SET CONSTRAINTS, 7-568
of SET ROLE, 7-571
ALL EXCEPT clause
of SET ROLE, 7-571
ALL operator, 3-6
ALL PRIVILEGES clause
of GRANT object_privileges, 7-506
of REVOKE schema_object_privileges, 7-533
of REVOKE schema_objects_privileges, 7-533
ALL PRIVILEGES shortcut
of AUDIT sql_statements, 7-199
ALL shortcut
of AUDIT sql_statements, 7-199
ALL_COL_COMMENTS view, 7-212
ALL_ROWS hint, 2-59
ALL_TAB_COMMENTS view, 7-212
ALLOCATE EXTENT clause
of ALTER CLUSTER, 7-3, 7-4
of ALTER INDEX, 7-30, 7-34
of ALTER TABLE, 7-139
ALTER ANY CLUSTER system privilege, 7-495
ALTER ANY DIMENSION system privilege, 7-496
ALTER ANY INDEX system privilege, 7-496
ALTER ANY MATERIALIZED VIEW system privilege, 7-497
ALTER ANY OUTLINE system privilege, 7-498
ALTER ANY PROCEDURE system privilege, 7-498
ALTER ANY ROLE system privilege, 7-498
ALTER ANY SEQUENCE system privilege, 7-499
ALTER ANY SNAPSHOT system privilege, 7-499
ALTER ANY TABLE system privilege, 7-500
ALTER ANY TRIGGER system privilege, 7-501
ALTER ANY TYPE system privilege, 7-501
ALTER CLUSTER statement, 7-2
ALTER DATABASE
statement, 7-6
system privilege, 7-495
ALTER DIMENSION statement, 7-24
ALTER FUNCTION statement, 7-27
ALTER INDEX statement, 7-29
ALTER JAVA CLASS statement, 7-43
ALTER JAVA SOURCE statement, 7-43
ALTER MATERIALIZED VIEW LOG statement, 7-54
ALTER MATERIALIZED VIEW statement, 7-45
ALTER object privilege, 7-508
ALTER OUTLINE statement, 7-58
ALTER PACKAGE statement, 7-59
ALTER PROCEDURE statement, 7-62
ALTER PROFILE
statement, 7-64
system privilege, 7-498
ALTER RESOURCE COST
statement, 7-68
system privilege, 7-499
ALTER ROLE statement, 7-71
ALTER ROLLBACK SEGMENT
statement, 7-73
system privilege, 7-498
ALTER SEQUENCE statement, 7-76
ALTER SESSION
statement, 7-78
system privilege, 7-499
ALTER SNAPSHOT LOG. See ALTER MATERIALIZED VIEW LOG.
ALTER SNAPSHOT. See ALTER MATERIALIZED VIEW.
ALTER statement
triggers on, 7-405
ALTER SYSTEM
statement, 7-95
system privilege, 7-495
ALTER TABLE statement, 7-113
ALTER TABLESPACE
statement, 7-164
system privilege, 7-500
ALTER TRIGGER statement, 7-171
ALTER TYPE statement, 7-173
ALTER USER
statement, 7-179
system privilege, 7-501
ALTER VIEW statement, 7-183
AM (A.M.) date format element, 2-41, 2-43
American National Standards Institute. See ANSI.
ANALYZE ANY system privilege, 7-502
ANALYZE CLUSTER statement, 7-185
ANALYZE INDEX statement, 7-185
ANALYZE TABLE statement, 7-185
ANCILLARY TO clause
of CREATE OPERATOR, 7-321
AND operator, 3-11
AND_EQUAL hint, 2-59
ANSI, B - 1
datatypes, 2-23
conversion to Oracle datatypes, 2-23
standards, xi, 1-2
supported datatypes, 2-8
ANY operator, 3-6
APPEND hint, 2-61
application servers
allowing to connect as a user, 7-181
applications
allowing to connect as a user, 7-181
securing, 7-243
validating, 7-243
AQ_ADMINISTRATOR_ROLE role, 7-503
AQ_TM_PROCESSES parameter
of ALTER SYSTEM, 7-100
AQ_USER_ROLE role, 7-503
ARCHIVE LOG clause
of ALTER SYSTEM, 7-96
archived redo log files
location of, 7-13
storage locations, 7-83
ARCHIVELOG clause
of ALTER DATABASE, 7-9, 7-17
of CREATE CONTROLFILE, 7-248
ARCHVIELOG clause
OF CREATE DATABASE, 7-252
arguments of operators, 3-1
arithmetic operators, 3-3
AS 'filespec' clause
of CREATE LIBRARY, 7-299
AS clause
of CREATE JAVA, 7-296
AS EXTERNAL clause
of CREATE FUNCTION, 7-271, 7-337
of CREATE TYPE BODY, 7-424
AS OBJECT clause
of CREATE TYPE, 7-414
AS subquery
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301, 7-308
of CREATE TABLE, 7-385
of CREATE VIEW, 7-433
AS TABLE clause
of CREATE TYPE, 7-418
AS VARRAY clause
of CREATE TYPE, 7-417
ASC clause
of CREATE INDEX, 7-281
ascending indexes, 7-281
ASCII
character set, 2-29
function, 4-6
ASIN function, 4-7
ASSOCIATE STATISTICS statement, 7-194
ATAN function, 4-7
ATAN2 function, 4-8
ATTRIBUTE clause
of ALTER DIMENSION, 7-25
of CREATE DIMENSION, 7-262
attributes
adding to a dimension, 7-26
dropping from a dimension, 7-26
maximum number of in object type, 7-369
of dimensions, defining, 7-262
AUDIT (Schema Objects) statement, 7-205
AUDIT ANY system privilege, 7-502
AUDIT sql_statements statement, 7-197
AUDIT SYSTEM system privilege, 7-495
auditing
schema objects
stopping, 7-525
SQL statements, 7-200
SQL statements, stopping, 7-523
auditing options
for database objects, 7-200
for SQL statements, 7-202
AUTHENTICATED BY clause
of CREATE DATABASE LINK, 7-257
AUTHID CURRENT_USER clause
of ALTER JAVA, 7-44
of CREATE FUNCTION, 7-270
of CREATE JAVA, 7-295
of CREATE PACKAGE, 7-326
of CREATE PROCEDURE, 7-336
of CREATE TYPE, 7-415
AUTHID DEFINER clause
of ALTER JAVA, 7-44
of CREATE FUNCTION, 7-270
of CREATE JAVA, 7-295
of CREATE PACKAGE, 7-326
of CREATE PROCEDURE, 7-336
of CREATE TYPE, 7-415
AUTOEXTEND clause
for datafiles, 7-17
of ALTER DATABASE, 7-10
of ALTER TABLESPACE, 7-165, 7-167
of CREATE DATABASE, 7-250
of CREATE TABLESPACE, 7-394, 7-396
of CREATE TEMPORARY TABLESPACE, 7-399, 7-400
AVG function, 4-8
AY date format element, 2-41

B

BACKGROUND_DUMP_DEST parameter
of ALTER SYSTEM, 7-101
BACKUP ANY TABLE system privilege, 7-500
BACKUP CONTROLFILE clause
of ALTER DATABASE, 7-9, 7-19
BACKUP_TAPE_IO_SLAVES parameter
of ALTER SYSTEM, 7-101
BC (B.C.) date format element, 2-41, 2-43
BECOME USER system privilege, 7-502
BEFORE clause
of CREATE TRIGGER, 7-403
BEFORE triggers, 7-403
BEGIN BACKUP clause
of ALTER TABLESPACE, 7-168
BFILE
datatype, 2-20
locators, 2-20
BFILENAME function, 4-8
binary large objects. See BLOBs.
binary operators, 3-1
BINDING clause
of CREATE OPERATOR, 7-320, 7-321
BITMAP clause
of CREATE INDEX, 7-279
bitmapped indexes, 7-279
blank padding
specifying in format models, 2-46
suppressing, 2-46
BLOB
transactional support of, 2-21
BLOB datatype, 2-21
BODY clause
of ALTER PACKAGE, 7-60
BUFFER_POOL parameter
of STORAGE clause, 7-579
BUILD DEFERRED clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
BUILD IMMEDIATE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
BY ACCESS clause
of AUDIT schema_objects, 7-206
of AUDIT sql_statements, 7-199
BY proxy clause
of AUDIT (SQL statements), 7-199
of NOAUDIT sql_statements, 7-524
BY SESSION clause
of AUDIT schema_objects, 7-206
of AUDIT sql_statements, 7-199
BY user clause
of AUDIT sql_statements, 7-199
of NOAUDIT sql_statements, 7-524

C

C clause
of CREATE TYPE, 7-416
of CREATE TYPE BODY, 7-424
C method
mapping to an object type, 7-416
C number format element, 2-36
CACHE clause
of ALTER MATERIALIZED VIEW, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-56
of ALTER SEQUENCE. See CREATE SEQUENCE., 7-76
of ALTER TABLE, 7-141
of CREATE CLUSTER, 7-240
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE SEQUENCE, 7-352
of CREATE TABLE, 7-384
CACHE hint, 2-62
CALL clause
of CREATE TRIGGER, 7-407
CALL procedure statement
of CREATE TRIGGER, 7-407
call spec
in procedures, 7-334
of CREATE FUNCTION, 7-271
of CREATE PROCEDURE, 7-336
of CREATE TYPE, 7-416
of CREATE TYPE BODY, 7-424
call specifications. See call spec.
CALL statement, 7-210
Cartesian products, 5-22
CASCADE clause
of CREATE TABLE, 7-384
of DROP PROFILE, 7-468
of DROP USER, 7-483
CASCADE CONSTRAINTS clause
of DROP CLUSTER, 7-447
of DROP TABLE, 7-476
of DROP TABLESPACE, 7-478
of REVOKE schema_object_privileges, 7-534
CAST expressions, 5-7
CC date format element, 2-41
CEIL function, 4-9
century
specifying, 2-42
CHANGE CATEGORY clause
of ALTER OUTLINE, 7-58
changes
making permanent, 7-214
changing default storage parameters, 7-168
CHAR datatype, 2-11
ANSI, 2-24
converting to VARCHAR2, 2-35
CHAR VARYING datatype, ANSI, 2-24
CHARACTER datatype
ANSI, 2-24
DB2, 2-25
SQL/DS, 2-25
character functions, 4-3
character large objects. See CLOB datatype.
character literal. See text.
CHARACTER SET clause
of CREATE CONTROLFILE, 7-248
OF CREATE DATABASE, 7-252
CHARACTER SET parameter
of ALTER DATABASE, 7-16
character sets
common, 2-29
multibyte characters, 2-67
specifying for database, 7-252
character strings
comparison rules, 2-28
exact matching of, 2-46
fixed-length, 2-11
national character set, 2-11
variable length, 2-12
variable-length, 2-15
zero-length, 2-11
CHARACTER VARYING datatype
ANSI, 2-24
characters
single, comparison rules, 2-29
CHARTOROWID function, 2-32, 4-9
CHECK clause
of constraint_clause, 7-224
of CREATE TABLE, 7-370
check constraints, 7-224
CHECK DATAFILES clause
of ALTER SYSTEM, 7-98
checkpoint
forcing, 7-98
CHECKPOINT clause
of ALTER SYSTEM, 7-98
CHOOSE hint, 2-59
CHR function, 4-10
CHUNK clause
of ALTER TABLE, 7-131
of CREATE TABLE, 7-376
clause
of CREATE TABLE, 7-383
CLEAR LOGFILE clause
of ALTER DATABASE, 7-9, 7-19
CLOB datatype, 2-21
transactional support of, 2-21
clone database
mounting, 7-11
CLOSE DATABASE LINK clause
of ALTER SESSION, 7-79
CLUSTER clause
of CREATE INDEX, 7-279
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE TABLE, 7-378
of TRUNCATE, 7-583
CLUSTER hint, 2-59
cluster indexes, 7-279
cluster key
changing column names, 7-3
changing the number of columns, 7-3
clusters
allocating extents for, 7-3
assigning tables to, 7-378
caching retrieved blocks, 7-240
collecting statistics on, 7-188
creating, 7-236, 7-237
data blocks allocated to, 7-238
deallocating unused extents, 7-3
degree of parallelism
changing, 7-3
when creating, 7-240
dropping tables of, 7-447
granting
system privileges on, 7-495
hash, 7-239
single-table, 7-239
indexed, 7-239
migrated and chained rows in, 7-192
modifying, 7-2
physical attributes
changing, 7-2
specifying, 7-238
removing from the database, 7-446
space allocated for cluster key values, 7-238
SQL examples, 7-447
storage attributes
changing, 7-2
storage characteristics, 7-575
specifying, 7-238
tablespace
changing, 7-3
tablespace in which created, 7-239
validating structure of, 7-191
COALESCE clause
for partitions, 7-148
for subpartitions, 7-145
of ALTER INDEX, 7-39
of ALTER TABLESPACE, 7-169
COALESCE SUBPARTITION clause
of ALTER TABLE, 7-145
code examples
description of, xviii
collections
inserting rows into, 7-515
modifying, 7-135
nested tables, 2-27
treating as a table, 7-441, 7-515, 7-586
unnesting, 7-547
examples, 7-564
varrays, 2-26
column constraint, 7-217
of ALTER TABLE, 7-130
of CREATE TABLE, 7-370
column constraints, 7-221
column REF constraint, 7-218
of ALTER TABLE, 7-130
of CREATE TABLE, 7-369
column ref constraint
of ALTER TABLE, 7-130
column REF constraints, 7-224
columns
adding, 7-130
aliases for, 5-18
associating statistics with, 7-195
basing an index on, 7-280
collecting statistics on, 7-189
creating comments about, 7-212
defining, 7-366
LOB, storage characteristics of, 7-130
maximum number of, 7-369
modifying existing, 7-132
parent-child relationships between, 7-260
prohibiting nulls in, 7-222
qualifying names of, 5-18
REF
describing, 7-224
restricting values for, 7-220
specifying as foreign key, 7-223
specifying as primary key, 7-222
specifying constraints on, 7-370
specifying default values for, 7-369
unique values in, 7-221
COLUMNS clause
of ASSOCIATE STATISTICS, 7-194, 7-195
COMMENT ANY TABLE system privilege, 7-502
COMMENT clause
of COMMIT, 7-215
COMMENT statement, 7-212
comments, 2-56
adding to objects, 7-212
associating with a transaction, 7-215
dropping from objects, 7-212
how to specify, 2-57
in SQL statements, 2-56
on schema objects, 2-58
removing from the data dictionary, 7-212
viewing, 7-212
commit
automatic, 7-214
COMMIT IN PROCEDURE clause
of ALTER SESSION, 7-79
COMMIT statement, 7-214
comparison functions
MAP, 7-417, 7-423
ORDER, 7-417, 7-423
comparison operators, 3-5
comparison semantics
blank-padded, 2-28
nonpadded, 2-28
of character strings, 2-28
COMPILE clause
of ALTER DIMENSION, 7-26
of ALTER FUNCTION, 7-27
of ALTER JAVA SOURCE, 7-44
of ALTER MATERIALIZED VIEW, 7-51
of ALTER PACKAGE, 7-60
of ALTER PROCEDURE, 7-63
of ALTER TRIGGER, 7-172
of ALTER TYPE, 7-174
of ALTER VIEW, 7-184
of CREATE JAVA, 7-294
compiler directives, 7-416
composite foreign keys, 7-222
composite partitioning clause
of CREATE TABLE, 7-364, 7-379
composite primary keys, 7-222
composite unique constraints, 7-221
COMPOSITE_LIMIT parameter
of ALTER PROFILE, 7-64
of CREATE PROFILE, 7-341
compound conditions, 5-17
compound expressions, 5-4
COMPRESS clause
of ALTER TABLE, 7-133
of CREATE INDEX, 7-282
of CREATE TABLE, 7-374
COMPRESS parameter
of ALTER INDEX, 7-31
COMPUTE STATISTICS clause
of ANALYZE, 7-188
of CREATE INDEX, 7-283
CONCAT function, 4-10
concatenation operator, 3-3
conditions
compound, 5-17
EXISTS, 5-17
group comparison, 5-15
in SQL syntax, 5-13
LIKE, 5-17
membership, 5-16
NULL, 5-17
range, 5-16
simple comparison, 5-15
CONNECT BY clause
of SELECT, 5-20, 7-548
CONNECT clause
of SELECT and subqueries, 7-543
CONNECT role, 7-503
CONNECT shortcut
of AUDIT sql_statements, 7-198
CONNECT TO clause
of CREATE DATABASE LINK, 7-256
CONNECT_TIME parameter
of ALTER PROFILE, 7-64
of ALTER RESOURCE COST, 7-69
of CREATE PROFILE, 7-341
constant values. See literals.
DISABLE, 7-383
CONSTRAINT clause
of constraint_clause, 7-221
constraint clause, 7-217
CONSTRAINT(S) parameter
of ALTER SESSION, 7-81
constraints
adding, 7-130
check, 7-224
checking at end of transaction, 7-226
checking at start of transaction, 7-226
checking at the end of each DML statement, 7-226
column REF, 7-224
composite unique, 7-221
deferrable, 7-226, 7-568
enforcing, 7-81
defining, 7-220, 7-366
on a column, 7-370
on a table, 7-370
disabling, 7-153, 7-227, 7-382
cascading, 7-384
dropping, 7-136, 7-478
enabling, 7-153, 7-227, 7-382, 7-383
foreign key, 7-223
modifying existing, 7-133
not null, 7-222
on columns, 7-221
primary key, 7-222
attributes of index, 7-227
enabling, 7-383
recording violations, 7-384
referential integrity, 7-222, 7-223
restrictions, 7-221
scope, 7-225
setting state for a transaction, 7-568
storing rows in violation, 7-228
table REF, 7-224
unique, 7-221
attributes of index, 7-227
composite, 7-221
enabling, 7-383
validating, 7-227
constructor methods
and object types, 7-413
context namespaces
removing from the database, 7-448
contexts
creating namespaces for, 7-243
granting
system privileges on, 7-495
namespace
associating with package, 7-243
control file
backing up, 7-19
control files
allow reuse of, 7-246
allowing reuse of, 7-251
re-creating, 7-245
CONTROL_FILE_RECORD_KEEP_TIME parameter
of ALTER SYSTEM, 7-101
controlfile clauses
of ALTER DATABASE, 7-9
CONTROLFILE REUSE clause
OF CREATE DATABASE, 7-251
conversion
functions
table of, 2-32
rules, string to date, 2-48
conversion functions
SQL functions
conversion, 4-4
CONVERT clause
of ALTER DATABASE, 7-11
CONVERT function, 4-11
correlated subqueries, 5-24
correlation names
for base tables of indexes, 7-279
in DELETE, 7-441
in SELECT, 7-547
COS function, 4-12
COSH function, 4-12
COUNT function, 4-12
CPU_PER_CALL parameter
of ALTER PROFILE, 7-64
of CREATE PROFILE, 7-340
CPU_PER_SESSION parameter
of ALTER PROFILE, 7-64
of ALTER RESOURCE COST, 7-69
of CREATE PROFILE, 7-340
CREATE ANY CLUSTER system privilege, 7-495
CREATE ANY CONTEXT system privilege, 7-495
CREATE ANY DIMENSION system privilege, 7-496
CREATE ANY DIRECTORY system privilege, 7-496
CREATE ANY INDEX system privilege, 7-496
CREATE ANY INDEXTYPE system privilege, 7-496
CREATE ANY LIBRARY system privilege, 7-497
CREATE ANY MATERIALIZED VIEW system privilege, 7-497
CREATE ANY OPERATOR system privilege, 7-497
CREATE ANY OUTLINE system privilege, 7-498
CREATE ANY PROCEDURE system privilege, 7-498
CREATE ANY SEQUENCE system privilege, 7-499
CREATE ANY SNAPSHOT system privilege, 7-499
CREATE ANY SYNONYM system privilege, 7-499
CREATE ANY TABLE system privilege, 7-500
CREATE ANY TRIGGER system privilege, 7-501
CREATE ANY TYPE system privilege, 7-501
CREATE ANY VIEW system privilege, 7-502
CREATE CLUSTER
statement, 7-236
system privilege, 7-495
CREATE CONTEXT statement, 7-243
CREATE CONTROLFILE statement, 7-245
CREATE DATABASE LINK
statement, 7-255
system privilege, 7-495
CREATE DATABASE statement, 7-249
CREATE DATAFILE clause
of ALTER DATABASE, 7-8, 7-16
CREATE DIMENSION
statement, 7-259
system privilege, 7-496
CREATE DIRECTORY statement, 7-264
CREATE FUNCTION statement, 7-266
CREATE INDEX
statement, 7-273
system privilege, 7-496
CREATE INDEXTYPE
statement, 7-291
system privilege, 7-496
CREATE JAVA statement, 7-293
CREATE LIBRARY
statement, 7-298
system privilege, 7-497
CREATE MATERIALIZED VIEW / SNAPSHOT
statement, 7-300
CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG statement, 7-314
CREATE MATERIALIZED VIEW/SNAPSHOT
system privilege, 7-497
CREATE OPERATOR
statement, 7-320
system privilege, 7-497
CREATE OUTLINE statement, 7-323
CREATE PACKAGE BODY statement, 7-328
CREATE PACKAGE statement, 7-325
CREATE PROCEDURE
statement, 7-333
system privilege, 7-498
CREATE PROFILE
statement, 7-338
system privilege, 7-498
CREATE PUBLIC DATABASE LINK system privilege, 7-495
CREATE PUBLIC SYNONYM system privilege, 7-499
CREATE ROLE
statement, 7-344
system privilege, 7-498
CREATE ROLLBACK SEGMENT
statement, 7-346
system privilege, 7-498
CREATE SCHEMA statement, 7-348
CREATE SEQUENCE
statement, 7-350
system privilege, 7-499
CREATE SESSION system privilege, 7-499
CREATE SNAPSHOT system privilege, 7-499
CREATE STANDBY CONTROLFILE clause
of ALTER DATABASE, 7-9, 7-19
CREATE statement
triggers on, 7-405
CREATE SYNONYM
statement, 7-356
system privilege, 7-499
CREATE TABLE statement, 7-359
CREATE TABLESPACE
statement, 7-394
system privilege, 7-500
CREATE TEMPORARY TABLESPACE statement, 7-399
CREATE TRIGGER
statement, 7-401
system privilege, 7-500
CREATE TYPE
statement, 7-411
system privilege, 7-501
CREATE TYPE BODY statement, 7-421
CREATE USER
statement, 7-425
system privilege, 7-501
CREATE VIEW
statement, 7-430
system privilege, 7-502
CREATE_STORED_OUTLINES parameter
of ALTER SESSION, 7-81
of ALTER SYSTEM, 7-101
cross-tabulation values
deriving, 7-549
CUBE operations
of queries and subqueries, 7-549
currency symbol
ISO, 2-36
local, 2-37
union, 2-38
CURRENT_SCHEMA parameter
of ALTER SESSION, 7-81
CURRENT_USER
and database links, 7-256
CURRVAL pseudocolumn, 2-51, 7-351
CURSOR expressions, 5-9
cursors
number cached per session, 7-88
CYCLE clause
of ALTER SEQUENCE. See CREATE SEQUENCE., 7-76
of CREATE SEQUENCE, 7-352

D

D date format element, 2-41
D number format element, 2-36
data
integrity checking on input, 2-13
retrieving, 5-18
undo
storing, 7-346
data conversion, 2-31
implicit versus explicit, 2-33
when performed implicitly, 2-31
when specified explicitly, 2-32
data definition language. See DDL.
data dictionary
adding comments to, 7-212
data manipulation language (DML) statements, 6-4
data manipulation language. See DML.
data object number
in extended rowids, 2-22
database
allowing generation of redo logs, 7-12
allowing reuse of control files, 7-251
allowing unlimited resources to users, 7-340
cancel-based recovery, 7-13
terminating, 7-14
change-based recovery, 7-13
changing characteristics of, 7-245
changing global name, 7-15
changing the name of, 7-245, 7-246
character set
specifying, 7-252
converting from Oracle7 data dictionary, 7-11
creating, 7-250
designing media recovery, 7-12
enabling automatic extension of, 7-253
erasing all data from, 7-250
limiting resources for users, 7-339
managed recovery of, 7-8
modifying, 7-10
mounting, 7-11, 7-250
naming, 7-11
opening, 7-11, 7-250
after media recovery, 7-12
recovering, 7-13
with backup control file, 7-13
re-creating control file for, 7-245
redo log files
specifying, 7-246
remote
accessing, 5-25
authenticating users to, 7-257
connecting to, 7-256
service name of, 7-257
table locks on, 7-521
resetting
current log sequence, 7-12
to an earlier version, 7-15
restricting users to read-only transactions, 7-11
resuming activity, 7-100
specifying datafiles for, 7-247
suspending activity, 7-100
time-based recovery, 7-13
database accounts
creating, 7-426
database connect strings, 2-75
database events
and triggers, 7-405
database link
granting
system privileges on, 7-495
database links, 5-25
closing, 7-79
creating, 2-74, 7-255
creating synonyms with, 7-357
current user, 7-256
naming, 2-74
public, 7-256
dropping, 7-449
referring to, 2-76
removing from the database, 7-449
shared, 7-256
syntax of, 2-75
username and password, 2-75
database objects
dropping, 7-483
nonschema, 2-64
schema, 2-63
database triggers. See triggers.
databases
granting
system privileges on, 7-495
remote
inserting into, 7-515
DATAFILE clause
of ALTER DATABASE, 7-8, 7-16
of CREATE CONTROLFILE, 7-247
of CREATE DATABASE, 7-253
DATAFILE clauses
of ALTER DATABASE, 7-8
DATAFILE END BACKUP clause
of ALTER DATABASE, 7-17
DATAFILE OFFLINE clause
of ALTER DATABASE, 7-16
DATAFILE ONLINE clause
of ALTER DATABASE, 7-16
DATAFILE RESIZE clause
of ALTER DATABASE, 7-17
datafiles
bringing online, 7-16
creating new, 7-16
designing media recovery, 7-12
disabling automatic extension, 7-17
enabling automatic extension, 7-17, 7-396
modifying, 7-16
recovering, 7-13
re-creating lost, 7-16
renaming, 7-15
resizing, 7-17
reusing, 7-491
size of, 7-491
specifying, 7-490
specifying for a tablespace, 7-396
taking offline, 7-16
datatype conversion
table of, 2-32
datatypes, 2-5
ANSI-supported, 2-8
associating statistics with, 7-196
BFILE, 2-10, 2-20
BLOB, 2-10, 2-21
built-in, 2-9
syntax, 2-7
CHAR, 2-9, 2-11
character, 2-10
CLOB, 2-10, 2-21
comparison rules, 2-27
DATE, 2-9, 2-17
external, 2-6
LONG, 2-9, 2-15
LONG RAW, 2-9, 2-18
NCHAR, 2-10, 2-11
NCLOB, 2-10, 2-21
NUMBER, 2-13
NUMER, 2-9
NVARCHAR2, 2-9, 2-12
RAW, 2-9, 2-18
ROWID, 2-9, 2-21
UROWID, 2-9, 2-23
VARCHAR, 2-13
VARCHAR2, 2-9, 2-12
DATE datatype, 2-17
converting from character or numeric value, 2-17
date format elements, 2-40
and NLS, 2-43
capitalization, 2-40
ISO standard, 2-44
RR, 2-44
suffixes, 2-45
date format models, 2-40
punctuation in, 2-40
date functions, 4-4
dates
arithmetic using, 2-17
comparison rules, 2-27
Julian, 2-18
DAY date format element, 2-43
DB_BLOCK_CHECKING parameter
of ALTER SESSION, 7-82
of ALTER SYSTEM, 7-101
DB_BLOCK_CHECKSUM parameter
of ALTER SYSTEM, 7-101
DB_BLOCK_MAX_DIRTY_TARGET parameter
of ALTER SYSTEM, 7-101
DB_FILE_MULTIBLOCK_READ_COUNT parameter
of ALTER SESSION, 7-82
of ALTER SYSTEM, 7-101
DB2 datatypes, 2-23
conversion to Oracle datatypes, 2-25
restrictions on, 2-25
DBA role, 7-503
DBA shortcut
of AUDIT sql_statements, 7-198
DBA_2PC_PENDING view, 7-79
DBA_COL_COMMENTS view, 7-212
DBA_ROLLBACK_SEGS view, 7-470
DBA_TAB_COMMENTS view, 7-212
DBMS_OUTPUT package, 7-172
DBMS_ROWID package
and extended rowids, 2-22
DBMSSTDX.SQL script, 7-267, 7-325, 7-328, 7-334
and triggers, 7-402
DD date format element, 2-41
DDD date format element, 2-41
DDL events
and triggers, 7-405
DDL statements, 6-1
and implicit commit, 6-2
causing recompilation, 6-2
PL/SQL support of, 6-2
requiring exclusive access, 6-2
DEALLOCATE UNUSED clause
of ALTER CLUSTER, 7-3, 7-5
of ALTER INDEX, 7-29
of ALTER TABLE, 7-140
DEBUG clause
of ALTER FUNCTION, 7-27
of ALTER PACKAGE, 7-60
of ALTER PROCEDURE, 7-63
of ALTER TRIGGER, 7-172
of ALTER TYPE, 7-174
decimal character, 2-4
specifying, 2-37
DECIMAL datatype
ANSI, 2-24
DB2, 2-25
SQL/DS, 2-25
DECODE expressions, 5-12
DEFAULT clause
of CREATE TABLE, 7-369
DEFAULT COST clause
of ASSOCIATE STATISTICS, 7-195, 7-196
DEFAULT profile
assigning to users, 7-468
DEFAULT ROLE clause
of ALTER USER, 7-181
of CREATE USER, 7-427
DEFAULT SELECTIVITY clause
of ASSOCIATE STATISTICS, 7-195, 7-196
DEFAULT storage clause
of ALTER TABLESPACE, 7-168
of CREATE TABLESPACE, 7-397
DEFAULT TABLESPACE clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-427
DEFERRABLE clause
of constraint_clause, 7-226
deferrable constraints, 7-568
DEFERRED clause
of SET CONSTRAINTS, 7-568
DELETE
object privilege, 7-508
statement, 7-438
DELETE ANY TABLE system privilege, 7-500
DELETE statement
triggers on, 7-404
DELETE STATISTICS clause
of ANALYZE, 7-190
DELETE_CATALOG_ROLE role, 7-503
DEREF function, 4-13
DESC clause
of CREATE INDEX, 7-281
descending indexes, 7-281
DETERMINISTIC clause
of CREATE FUNCTION, 7-270
dimensions
attributes
adding, 7-26
changing, 7-25
defining, 7-262
dropping, 7-26
changing hierarchical relationships, 7-25
compiling invalidated, 7-26
creating, 7-260
creating on unspecified tables, 7-260
examples, 7-262
granting
system privileges on, 7-496
hierarchies
adding, 7-26
defining, 7-261
dropping, 7-26
levels
adding, 7-26
defining, 7-261
dropping, 7-26
removing from the database, 7-450
directories
granting
system privileges on, 7-496
directories. See directory objects.
directory objects
as aliases for OS directories, 7-264
auditing, 7-206
creating, 7-264
redefining, 7-265
removing from the database, 7-451
DISABLE ALL TRIGGERS clause
of ALTER TABLE, 7-153
DISABLE clause
of ALTER INDEX, 7-39
of ALTER TRIGGER, 7-172
of constraint_clause, 7-227
of CREATE TABLE, 7-382
DISABLE DISTRIBUTED RECOVERY clause
of ALTER SYSTEM, 7-99
DISABLE NOVALIDATE constraint state, 7-383
DISABLE PARALLEL DML clause
of ALTER SESSION, 7-79
DISABLE QUERY REWRITE clause
of ALTER MATERIALIZED VIEW, 7-50
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-308
DISABLE RESTRICTED SESSION clause
of ALTER SYSTEM, 7-99
DISABLE ROW MOVEMENT clause
of ALTER TABLE, 7-152
of CREATE TABLE, 7-361, 7-381
DISABLE STORAGE IN ROW clause
of ALTER TABLE, 7-131
of CREATE TABLE, 7-375
DISABLE TABLE LOCK clause
of ALTER TABLE, 7-153
DISABLE THREAD clause
of ALTER DATABASE, 7-15
DISABLE VALIDATE constraint state, 7-383
DISASSOCIATE STATISTICS statement, 7-444
DISCONNECT SESSION clause
of ALTER SYSTEM, 7-99
dispatcher processes
creating additional, 7-105
terminating, 7-105
DISTINCT clause
of SELECT, 7-545
distinct queries, 7-545
distributed queries, 5-25
DML operations
and triggers, 7-404
during index creation, 7-283
during index rebuild, 7-133
DML statements, 6-4
PL/SQL support of, 6-4
domain indexes, 7-277, 7-285, 7-291
associating statistics with, 7-196
determining user-defined CPU and I/O costs, 7-486
invoking drop routines for, 7-475
removing from the database, 7-454
specifying alter string for, 7-38
DOUBLE PRECISION datatype
ANSI, 2-24
DRIVING_SITE hint, 2-61
DROP ANY CLUSTER system privilege, 7-495
DROP ANY CONTEXT system privilege, 7-495
DROP ANY DIMENSION system privilege, 7-496
DROP ANY DIRECTORY system privilege, 7-496
DROP ANY INDEX system privilege, 7-496
DROP ANY INDEXTYPE system privilege, 7-496
DROP ANY LIBRARY system privilege, 7-497
DROP ANY MATERIALIZED VIEW system privilege, 7-497
DROP ANY OPERATOR system privilege, 7-497
DROP ANY OUTLINE system privilege, 7-498
DROP ANY PROCEDURE system privilege, 7-498
DROP ANY ROLE system privilege, 7-498
DROP ANY SEQUENCE system privilege, 7-499
DROP ANY SNAPSHOT system privilege, 7-499
DROP ANY SYNONYM system privilege, 7-499
DROP ANY TABLE system privilege, 7-500
DROP ANY TRIGGER system privilege, 7-501
DROP ANY TYPE system privilege, 7-501
DROP ANY VIEW system privilege, 7-502
DROP clause
of ALTER DIMENSION, 7-26
DROP CLUSTER statement, 7-446
DROP COLUMN clause
of ALTER TABLE, 7-136
DROP CONSTRAINT clause
of ALTER TABLE, 7-136
DROP CONTEXT statement, 7-448
DROP DATABASE LINK statement, 7-449
DROP DIMENSION statement, 7-450
DROP DIRECTORY statement, 7-451
DROP FUNCTION statement, 7-452
DROP INDEX statement, 7-454
DROP INDEXTYPE statement, 7-456
DROP JAVA statement, 7-457
DROP LIBRARY
statement, 7-458
system privilege, 7-497
DROP LOGFILE clause
of ALTER DATABASE, 7-9, 7-18
DROP LOGFILE MEMBER clause
of ALTER DATABASE, 7-9, 7-18
DROP MATERIALIZED VIEW / SNAPSHOT statement, 7-459
DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG statement, 7-461
DROP OPERATOR statement, 7-463
DROP OUTLINE statement, 7-464
DROP PACKAGE BODY statement, 7-465
DROP PACKAGE statement, 7-465
DROP PARTITION clause
of ALTER INDEX, 7-40
of ALTER TABLE, 7-148
DROP PRIMARY constraint clause
of ALTER TABLE, 7-136
DROP PROCEDURE statement, 7-467
DROP PROFILE
statement, 7-468
system privilege, 7-498
DROP PUBLIC DATABASE LINK system privilege, 7-496
DROP PUBLIC SYNONYM system privilege, 7-500
DROP ROLE statement, 7-469
DROP ROLLBACK SEGMENT
statement, 7-470
system privilege, 7-498
DROP SEQUENCE statement, 7-471
DROP statement
triggers on, 7-405
DROP STORAGE clause
of TRUNCATE, 7-583
DROP SYNONYM statement, 7-474
DROP TABLE statement, 7-475
DROP TABLESPACE
statement, 7-477
system privilege, 7-500
DROP TRIGGER statement, 7-479
DROP TYPE BODY statement, 7-482
DROP TYPE statement, 7-480
DROP UNIQUE constraint clause
of ALTER TABLE, 7-136
DROP USER
statement, 7-483
system privilege, 7-502
DROP VIEW statement, 7-485
DUAL dummy table, 2-68, 5-24
DUMP function, 4-14
DY date format element, 2-41, 2-43

E

E date format element, 2-41
E number format element, 2-36
EBCDIC character set, 2-29
EE date format element, 2-41
embedded SQL statements, xi, 1-3, 6-5
precompiler support of, 6-5
EMPTY_BLOB function, 4-15
EMPTY_CLOB function, 4-15
ENABLE ALL TRIGGERS clause
of ALTER TABLE, 7-153
ENABLE clause
of ALTER INDEX, 7-38
of ALTER TRIGGER, 7-171
of constraint_clause, 7-227
of CREATE TABLE, 7-382
ENABLE DISTRIBUTED RECOVERY clause
of ALTER SYSTEM, 7-99
ENABLE NOVALIDATE constraint state, 7-382
ENABLE PARALLEL DML clause
of ALTER SESSION, 7-79
ENABLE QUERY REWRITE clause
of ALTER MATERIALIZED VIEW, 7-50
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-308
ENABLE RESTRICTED SESSION clause
of ALTER SYSTEM, 7-99
ENABLE ROW MOVEMENT clause
of ALTER TABLE, 7-152
of CREATE TABLE, 7-361, 7-381
ENABLE STORAGE IN ROW clause
of ALTER TABLE, 7-131
of CREATE TABLE, 7-375
ENABLE TABLE LOCK clause
of ALTER TABLE, 7-153
ENABLE THREAD clause
of ALTER DATABASE, 7-15
ENABLE VALIDATE constraint state, 7-382
ENABLE/DISABLE clause
of ALTER TABLE, 7-127, 7-153
of CREATE TABLE, 7-366
END BACKUP clause
of ALTER TABLESPACE, 7-168
equality test, 3-5
equijoins, 5-21
defining for a dimension, 7-261
equivalency tests, 3-5
ESTIMATE STATISTICS clause
of ANALYZE, 7-188
EXCEPTIONS INTO clause
of constraint_clause, 7-228
of CREATE TABLE, 7-384
EXCHANGE PARTITION clause
of ALTER TABLE, 7-151
EXCHANGE SUBPARTITION clause
of ALTER TABLE, 7-151
EXCLUDING NEW VALUES clause
of ALTER MATERIALIZED VIEW LOG, 7-57
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-317
EXCLUSIVE lock mode, 7-521
EXECUTE ANY INDEXTYPE system privilege, 7-496
EXECUTE ANY OPERATOR system privilege, 7-497
EXECUTE ANY PROCEDURE system privilege, 7-498
EXECUTE ANY TYPE system privilege, 7-501
EXECUTE object privilege, 7-508
EXECUTE_CATALOG_ROLE role, 7-503
execution plans
determining, 7-486
dropping outlines for, 7-464
saving, 7-323
EXISTS
conditions, 5-17
operator, 3-6
EXP function, 4-15
EXP_FULL_DATABASE role, 7-503
EXPLAIN PLAN statement, 7-486
explicit data conversion, 2-32, 2-33
expressions
CAST, 5-7
compound, 5-4
computing with the DUAL table, 5-24
CURSOR, 5-9
DECODE, 5-12
function, built-in, 5-5
in SQL syntax, 5-1
list of, 5-13
object access, 5-10
simple, 5-3
type constructor, 5-6
user-defined function, 5-5
variable, 5-4
extended rowids, 2-22
not directly available, 2-22
EXTENT MANAGEMENT clause
for temporary tablespaces, 7-400
of CREATE TABLESPACE, 7-395, 7-397
extents
allocating for partitions, 7-139
allocating for subpartitions, 7-139
allocating for tables, 7-139
restricting access by instances, 7-34
specifying maximum number for an object, 7-578
specifying number allocated upon object creation, 7-577
specifying the first for an object, 7-577
specifying the percentage of size increase, 7-577
specifying the second for an object, 7-577
external datatypes
associated with host variables, 2-6
recognized by precompilers, 2-6
external functions, 7-267, 7-334
external LOBs, 2-19
external procedures, 7-334
external users, 7-345, 7-427

F

FAILED_LOGIN_ATTEMPTS parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
FAST_START_IO_TARGET parameter
of ALTER SESSION, 7-82, 7-102
FAST_START_PARALLEL_ROLLBACK parameter
of ALTER SYSTEM, 7-102
files
specifying as a redo log file group, 7-490
specifying as datafiles, 7-490
specifying as tempfiles, 7-490
filespec clause, 7-490
of CREATE CONTROLFILE, 7-245
of CREATE DATABASE, 7-250
of CREATE LIBRARY, 7-298
of CREATE TABLESPACE, 7-394
of CREATE TEMPORARY TABLESPACE, 7-399
FIPS compliance, B - 3
FIPS flagging, 7-82
FIRST_ROWS hint, 2-59
FIXED_DATE parameter
of ALTER SYSTEM, 7-102
FLAGGER parameter
of ALTER SESSION, 7-82
FLOAT datatype
ANSI, 2-24
DB2, 2-25
SQL/DS, 2-25
floating-point numbers, 2-15
FLOOR function, 4-15
FLUSH SHARED POOL clause
of ALTER SYSTEM, 7-99
FM format model modifier, 2-46
FM number format element, 2-36
FOR CATEGORY clause
of CREATE OUTLINE, 7-323
FOR clause
of ANALYZE ... COMPUTE STATISTICS, 7-189
of ANALYZE ... ESTIMATE STATISTICS, 7-189
of CREATE INDEXTYPE, 7-292
of CREATE SYNONYM, 7-357
of EXPLAIN PLAN, 7-487
FOR EACH ROW clause
of CREATE TRIGGER, 7-406
FOR UPDATE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-308
of SELECT, 7-544, 7-551
FORCE ANY TRANSACTION system privilege, 7-502
FORCE CLAUSE
of DROP OPERATOR, 7-463
FORCE clause
of COMMIT, 7-215
of CREATE DIMENSION, 7-260
of CREATE VIEW, 7-432
of DISASSOCIATE STATISTICS, 7-445
of DROP INDEX, 7-455
of DROP INDEXTYPE, 7-456
of DROP TYPE, 7-481
of REVOKE schema_object_privileges, 7-534
of ROLLBACK, 7-538
FORCE PARALLEL DML clause
of ALTER SESSION, 7-79
FORCE TRANSACTION system privilege, 7-502
FOREIGN KEY clause
of constraint_clause, 7-219, 7-223
foreign key constraints, 7-223
foreign tables
rowids of, 2-23
format models, 2-33
changing the return format, 2-34
date, 2-40
date, changing, 2-40
date, default, 2-40
date, format elements, 2-40
date, maximum length, 2-40
modifiers, 2-46
number, 2-35
number, elements of, 2-36
specifying, 2-35
formats
for dates and numbers. See format models.
of return values from the database, 2-33
of values stored in the database, 2-33
FREELIST GROUPS parameter
of STORAGE clause, 7-578
freelists
specifying for a table, partition, cluster, or index, 7-578
FREELISTS parameter
of STORAGE clause, 7-578
FROM clause
of queries, 5-22
of REVOKE system_privileges_and_roles, 7-530
of SELECT, 7-546
FROM COLUMNS clause
of DISASSOCIATE STATISTICS, 7-445
FROM FUNCTIONS clause
of DISASSOCIATE STATISTICS, 7-445
FROM INDEXES clause
of DISASSOCIATE STATISTICS, 7-445
FROM INDEXTYPES clause
of DISASSOCIATE STATISTICS, 7-445
FROM PACKAGES clause
of DISASSOCIATE STATISTICS, 7-445
FROM PUBLIC clause
of REVOKE schema_object_privileges, 7-534
FROM role clause
of REVOKE schema_object_privileges, 7-534
FROM TYPES clause
of DISASSOCIATE STATISTICS, 7-445
FROM user clause
of REVOKE schema_object_privileges, 7-534
FULL hint, 2-59
function expressions
built-in, 5-5
function specification
of CREATE TYPE, 7-416
function-based indexes, 7-277
and query rewrite, 7-87
creating, 7-280
disabling, 7-39, 7-106
enabling, 7-36, 7-38, 7-106
functions
3GL, calling, 7-298
access to tables and packages, 7-416
associating statistics with, 7-196
avoiding run-time compilation, 7-27
calling, 7-210
changing the declaration of, 7-27
datatype of return value, 7-270
declaring
as a Java method, 7-271
as C functions, 7-271
defining an index on, 7-280
disassociating statistics types from, 7-452
examples, 7-271
executing, 7-210
from parallel query process, 7-270
external, 7-267, 7-334
invalidating local objects dependent on, 7-452
issuing COMMIT or ROLLBACK statements, 7-79
naming rules, 2-69
privileges executed with, 7-415
recompiling invalid, 7-27
re-creating, 7-268, 7-294
removing from the database, 7-452
schema executed in, 7-415
specifying schema and user privileges for, 7-270
stored, 7-267
storing return value of, 7-211
synonyms for, 7-356
user-defined, 4-56
using a saved copy of, 7-270
FUNCTIONS clause
of ASSOCIATE STATISTICS, 7-194, 7-196
FX format model modifier, 2-46

G

G number format element, 2-36
GC_DEFER_TIME parameter
of ALTER SYSTEM, 7-102
general recovery clause
of ALTER DATABASE, 7-7, 7-12
GLOBAL PARTITION BY RANGE clause
of CREATE INDEX, 7-283
GLOBAL QUERY REWRITE system privilege, 7-496, 7-497, 7-499
GLOBAL TEMPORARY clause
of CREATE TABLE, 7-368
global users, 7-345, 7-427
GLOBAL_NAMES parameter
of ALTER SESSION, 7-82
of ALTER SYSTEM, 7-102
globally partitioned indexes, 7-283, 7-284
GRANT ANY PRIVILEGE system privilege, 7-502
GRANT ANY ROLE system privilege, 7-498
GRANT CONNECT THROUGH clause
of ALTER USER, 7-180, 7-181
GRANT object_privileges, 7-505
GRANT system_privileges_and_roles statement, 7-493
GRAPHIC datatype (SQL/DS or DB2), 2-25
greater than or equal to tests, 3-5
greater than tests, 3-5
GREATEST function, 4-16
GROUP BY clause
of SELECT, 7-549
of SELECT and subqueries, 7-543
group comparison conditions, 5-15
GROUPING function, 4-16

H

hash clusters
creating, 7-239
single-table, creating, 7-239
specifying hash function for, 7-239
HASH hint, 2-59
HASH IS clause
of CREATE CLUSTER, 7-239
hash partition
adding, 7-148
hash partitioning clause
of CREATE TABLE, 7-365, 7-378
HASH_AJ hint, 2-59
HASH_AREA_SIZE parameter
of ALTER SESSION, 7-82
HASH_JOIN_ENABLED parameter
of ALTER SESSION, 7-83
HASH_MULTIBLOCK_IO_COUNT parameter
of ALTER SESSION, 7-83
of ALTER SYSTEM, 7-102
HASH_SJ hint, 2-59
HASHKEYS clause
of CREATE CLUSTER, 7-239
HAVING condition
of GROUP BY clause, 7-550
heap-organized tables
creating, 7-366
hexadecimal value
returning, 2-38
HEXTORAW function, 2-32, 4-17
HH date format element, 2-41
HH12 date format element, 2-41
HH24 date format element, 2-41
hierarchical queries, 2-53, 5-19, 7-548
child nodes of, 2-53
child rows of, 5-19
illustrated, 2-54
leaf nodes of, 2-53
parent nodes of, 2-53
parent rows of, 5-19
hierarchical query clause
of SELECT and subqueries, 7-543
hierarchies
adding to a dimension, 7-26
dropping from a dimension, 7-26
of dimensions, defining, 7-261
HIERARCHY clause
of ALTER DIMENSION, 7-24
of CREATE DIMENSION, 7-261
high water mark
of clusters, 7-5
of indexes, 7-34
of tables, 7-140, 7-188
hints, 5-18
in SQL statements, 2-58
passing to the optimizer, 7-585
syntax, 2-59
HS_ADMIN_ROLE role, 7-503
HS_AUTOREGISTER parameter
of ALTER SYSTEM, 7-103

I

I date format element, 2-41
IDENTIFIED BY clause
of ALTER ROLE. See CREATE ROLE.
of CREATE ROLE, 7-345
IDENTIFIED BY password clause
of CREATE DATABASE LINK, 7-257
of CREATE USER, 7-426
of SET ROLE, 7-571
IDENTIFIED EXTERNALLY clause
of ALTER ROLE. See CREATE ROLE.
of ALTER USER. See CREATE USER.
of CREATE ROLE, 7-345
of CREATE USER, 7-426, 7-427
IDENTIFIED GLOBALLY clause
of ALTER ROLE. See CREATE ROLE.
of ALTER USER, 7-181
of CREATE ROLE, 7-345
of CREATE USER, 7-427
of CREATE USERIDENTIFIED BY clause
of ALTER USER. See CREATE USER.
IDLE_TIME parameter
of ALTER PROFILE, 7-64
of CREATE PROFILE, 7-341
IMMEDIATE clause
of SET CONSTRAINTS, 7-568
IMP_FULL_DATABASE role, 7-503
implicit data conversion, 2-31, 2-33
IN OUT parameter
of CREATE FUNCTION, 7-269
of CREATE PROCEDURE, 7-335
IN parameter
of CREATE function, 7-269
of CREATE PROCEDURE, 7-335
INCLUDING clause
of ALTER TABLE, 7-143
INCLUDING CONTENTS clause
of DROP TABLESPACE, 7-477
INCLUDING NEW VALUES clause
of ALTER MATERIALIZED VIEW LOG, 7-57
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-317
INCLUDING TABLES clause
of DROP CLUSTER, 7-447
incomplete object types
creating, 7-413
incomplete types, 7-413
creating, 7-411
INCREMENT BY clause
of ALTER SEQUENCE. See CREATE SEQUENCE.
of CREATE SEQUENCE, 7-352
INDEX clause
of CREATE CLUSTER, 7-239
INDEX hint, 2-59
INDEX object privilege, 7-508
index partitions
changing physical attributes of, 7-35
deallocating unused space from, 7-34
dropping, 7-40
marking UNUSABLE, 7-145
modifying the real characteristics of, 7-39
rebuilding, 7-36
unusable, 7-145
renaming, 7-40
specifying tablespace for, 7-37
splitting, 7-40
index subpartitions
allocating extents for, 7-40
changing physical attributes of, 7-35
deallocating unused space from, 7-34, 7-40
marking UNUSABLE, 7-40
rebuilding, 7-36
renaming, 7-40
specifying tablespace for, 7-37
INDEX_ASC hint, 2-59
INDEX_COMBINE hint, 2-59
INDEX_DESC hint, 2-59
INDEX_FFS hint, 2-59
indexed clusters
creating, 7-239
indexes
allocating new extents for, 7-34
application-specific, 7-291
ascending, 7-281
based on indextypes, 7-285
bitmapped, 7-279
changing attributes of, 7-35
cluster, 7-279
collecting statistics on, 7-38, 7-186
on composite-partitioned tables, 7-285
creating, 7-277
deallocating unused space from, 7-34
descending, 7-281
and query rewrite, 7-281
as function-based indexes, 7-281
disassociating statistics types from, 7-454
domain, 7-277, 7-285, 7-291
dropping index partitions of, 7-454
examples, 7-286
function-based, 7-277
creating, 7-280
globally partitioned, 7-283, 7-284
granting
system privileges on, 7-496
on hash-partitioned tables, 7-284
key compression of, 7-37, 7-282
locally partitioned, 7-284
logging attributes of, 7-282
logging rebuild operations on, 7-38
marking as UNUSABLE, 7-39
merging contents of index blocks, 7-39
online, 7-283
parallel queries and DML on, 7-35
parallelizing creation of, 7-286
partitioned, 2-65, 7-277
user-defined, 7-283
partitions
adding new, 7-40
physical attributes of, 7-281
on range-partitioned tables, 7-284
rebuilding, 7-36
rebuilding while online, 7-38
removing from the database, 7-454
renaming, 7-39
reverse, 7-37, 7-282
specifying tablespace for, 7-37
statistics on, 7-283
storage characteristics of, 7-281, 7-575
tablespace containing, 7-282
unique, 7-278
unsorted, 7-282
validating structure of, 7-191
INDEXES clause
of ASSOCIATE STATISTICS, 7-194, 7-196
indexes partitions
marking UNUSABLE, 7-40
index-organized table clause
of CREATE TABLE, 7-362, 7-373
index-organized tables
creating, 7-366, 7-373
modifying, 7-142
rebuilding, 7-133
reserving space in the index block, 7-143
rowids of, 2-23
INDEXTYPE clause
of CREATE INDEX, 7-285
indextypes
associating statistics with, 7-196
creating, 7-291
disassociating from statistics types, 7-456
drop routines, invoking, 7-454
granting
system privileges on, 7-496
indexes based on, 7-285
instances of, 7-277
removing from the database, 7-456
INDEXTYPES clause
of ASSOCIATE STATISTICS, 7-194, 7-196
in-doubt transactions
forcing, 7-215
forcing commit of, 7-215
forcing rollback, 7-538
forcing rollback of, 7-538
rolling back, 7-537
inequality test, 3-5
INITCAP function, 4-18
INITIAL parameter
of STORAGE clause, 7-577
INITIALLY DEFERRED clause
of constraint_clause, 7-226
INITIALLY IMMEDIATE clause
of constraint_clause, 7-226
INITRANS parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of CREATE CLUSTER, 7-238
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, 7-371
INSERT
object privilege, 7-508
statement, 7-512
INSERT ANY TABLE system privilege, 7-500
INSERT statement
triggers on, 7-404
instance
global name resolution for, 7-102
setting parameters for, 7-100
INSTANCE parameter
of ALTER SESSION, 7-83
INSTEAD OF clause
of CREATE TRIGGER, 7-404
INSTEAD OF triggers, 7-404
INSTR function, 4-18
INSTRB function, 4-19
INT datatype (ANSI), 2-24
INTEGER datatype
ANSI, 2-24
DB2, 2-25
SQL/DS, 2-25
integers
generating unique, 7-350
in SQL syntax, 2-3
precision of, 2-3
specifying, 2-13
syntax of, 2-3
integrity constraints. See constraints.
internal LOBs, 2-19
International Standards Organization. See ISO.
INTERSECT operator, 3-12, 7-550
INTO clause
of EXPLAIN PLAN, 7-487
of INSERT, 7-514
INTO host_variable clause
of CALL, 7-211
invoker rights clause
of ALTER JAVA, 7-44
of CREATE FUNCTION, 7-270
of CREATE JAVA, 7-295
of CREATE PACKAGE, 7-325
of CREATE PROCEDURE, 7-333
of CREATE TYPE, 7-415
IS NOT NULL operator, 3-7
IS NULL operator, 3-7
ISO, B - 1
standards, xi, 1-2, B - 1
ISOLATION LEVEL READ COMMITTED clause
of SET TRANSACTION, 7-573
ISOLATION LEVEL SERIALIZABLE clause
of SET TRANSACTION, 7-573
ISOLATION_LEVEL parameter
of ALTER SESSION, 7-83
IW date format element, 2-41
IY date format element, 2-41
IYY date format element, 2-41
IYYY date format element, 2-41

J

J date format element, 2-41
Java class schema object
creating, 7-294, 7-295
dropping, 7-457
resolving, 7-43, 7-294
JAVA clause
of CREATE TYPE, 7-416
of CREATE TYPE BODY, 7-424
Java method
mapping to an object type, 7-416
Java resource schema object
creating, 7-294, 7-295
dropping, 7-457
Java schema object
name resolution of, 7-296
Java source schema object
compiling, 7-43, 7-294
creating, 7-294
dropping, 7-457
java source schema object
creating, 7-295
JOB_QUEUE_PROCESSES parameter
of ALTER SYSTEM, 7-103
JOIN KEY clause
of ALTER DIMENSION, 7-25
of CREATE DIMENSION, 7-261
join views
modifying, 7-440, 7-515, 7-587
joins, 5-21
conditions
defining, 5-21
equijoins, 5-21
outer, 5-22
restrictions, 5-22
self, 5-22
without join conditions, 5-22
Julian day, specifying, 2-42

K

key compression, 7-37, 7-282, 7-374
disabling, 7-38, 7-282
of index rebuild, 7-133
of indexes, 7-37, 7-282
disabling, 7-38
of index-organized tables, 7-374
keywords, 2-68
in syntax diagrams, xvi
optional, A-2
required, A-1
KILL SESSION clause
of ALTER SYSTEM, 7-100

L

L number format element, 2-36
LANGUAGE clause
of CREATE FUNCTION, 7-271
of CREATE PROCEDURE, 7-336
of CREATE TYPE, 7-416
of CREATE TYPE BODY, 7-424
large objects. See LOBs.
LAST_DAY function, 4-19
LEAST function, 4-20
LENGTH function, 4-20
LENGTHB function, 4-21
less than tests, 3-5
LEVEL clause
of ALTER DIMENSION, 7-24
of CREATE DIMENSION, 7-261
LEVEL pseudocolumn, 2-53, 7-548
and hierarchical queries, 2-53
levels
adding to a dimension, 7-26
dropping from a dimension, 7-26
of dimensions, defining, 7-261
libraries
creating, 7-298
granting
system privileges on, 7-497
re-creating, 7-298
removing from the database, 7-458
library units. See Java schema objects
LICENSE_MAX_SESSIONS parameter
of ALTER SYSTEM, 7-103
LICENSE_MAX_USERS parameter
of ALTER SYSTEM, 7-103
LICENSE_SESSIONS_WARNING parameter
of ALTER SYSTEM, 7-103
LIKE conditions, 5-17
LIKE operator, 3-7
LIST CHAINED ROWS clause
of ANALYZE, 7-192
literals
in SQL statements and functions, 2-2
in SQL syntax, 2-2
LN function, 4-21
LOB
storage characteristics, 7-371
LOB columns
difference from LONG and LONG RAW, 2-19
initializing, 2-20
LOB datatypes, 2-19
LOB index clause
of ALTER TABLE, 7-131
of CREATE TABLE, 7-376
LOB locators, 2-19
LOB storage clause
of ALTER MATERIALIZED VIEW, 7-48
for partitions, 7-131
of ALTER MATERIALIZED VIEW, 7-45
of ALTER TABLE, 7-130
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-303
of CREATE TABLE, 7-363, 7-374
LOBS
logging attribute of, 7-372
tablespace for
defining, 7-371
LOBs
external, 2-19
indexes for, 7-376
internal, 2-19
locators for, 7-375
modifying physical attributes of, 7-135
number of bytes manipulated in, 7-376
specifying directories for, 7-264
storage
in-line, 7-375
outside of row, 7-375
storage characteristics of, 7-374
LOCAL clause
of CREATE INDEX, 7-284
local users, 7-345, 7-426
locally managed tablespaces
storage characteristics, 7-576
locally partitioned indexes, 7-284
LOCK ANY TABLE system privilege, 7-500
LOCK TABLE statement, 7-520
locking
automatic
overriding, 7-520
locks. See table locks.
LOG function, 4-21
LOG_ARCHIVE_DEST parameter
of ALTER SYSTEM, 7-104
LOG_ARCHIVE_DEST_n parameter
of ALTER SESSION, 7-83
of ALTER SYSTEM, 7-104
LOG_ARCHIVE_DEST_STATE_n parameter
of ALTER SESSION, 7-84
of ALTER SYSTEM, 7-104
LOG_ARCHIVE_DUPLEX_DEST parameter
of ALTER SYSTEM, 7-104
LOG_ARCHIVE_MAX_PROCESSES parameter
of ALTER SYSTEM, 7-105
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter
of ALTER SESSION, 7-84
of ALTER SYSTEM, 7-105
LOG_CHECKPOINT_INTERVAL parameter
of ALTER SYSTEM, 7-105
LOG_CHECKPOINT_TIMEOUT parameter
of ALTER SYSTEM, 7-105
LOGFILE clause
of CREATE CONTROLFILE, 7-246
OF CREATE DATABASE, 7-251
logfile clauses
of ALTER DATABASE, 7-9
LOGFILE GROUP clause
of CREATE CONTROLFILE, 7-247
logging
and redo log size, 7-372
specifying minimal, 7-372
LOGGING clause
of ALTER INDEX, 7-36
of ALTER MATERIALIZED VIEW, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-56
of ALTER TABLE, 7-141
of ALTER TABLESPACE, 7-166
of CREATE INDEX, 7-282
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE TABLE, 7-372
of CREATE TABLESPACE, 7-396
logical operators, 3-10
LOGICAL_READS_PER_CALL parameter
of ALTER PROFILE, 7-64
of CREATE PROFILE, 7-341
LOGICAL_READS_PER_SESSION parameter
of ALTER PROFILE, 7-64
of ALTER RESOURCE COST, 7-69
of CREATE PROFILE, 7-341
LOGOFF
triggers on, 7-405
LOGOFF event
triggers on, 7-405
LOGON
triggers on, 7-405
LOGON event
triggers on, 7-405
LONG columns
converting to LOB columns, 2-15, 2-18
restrictions on, 2-15
to store text strings, 2-15
to store view definitions, 2-15
where referenced from, 2-15
LONG datatype, 2-15
in triggers, 2-16
LONG RAW
data
converting from CHAR data, 2-19
datatype, 2-18
LONG VARCHAR datatype
DB2, 2-25
SQL/DS, 2-25
LONG VARGRAPHIC datatype (SQL/DS or DB2), 2-25
LOWER function, 4-22
LPAD function, 4-22
LTRIM function, 4-22

M

MAKE_REF function, 4-23
MANAGE TABLESPACE system privilege, 7-500
managed recovery
of database, 7-8
MANAGED STANDBY RECOVERY clause
of ALTER DATABASE, 7-14
MAP MEMBER clause
of ALTER TYPE, 7-175
of CREATE TYPE, 7-417, 7-423
MAP methods
specifying, 7-175
master databases, 7-302
master tables, 7-302
materialized join views, 7-316
materialized view logs, 7-315
creating, 7-315
logging changes to, 7-56
parallelizing creation of, 7-317
partition attributes
changing, 7-56
partitioned, 7-317
physical attributes
changing, 7-55
specifying, 7-316
removing from the database, 7-461
required for fast refresh, 7-315
saving old values in, 7-57, 7-317
storage characteristics
specifying, 7-316
materialized views
allowing update of, 7-308
complete refresh, 7-49, 7-306
constraints on, 7-226
creating, 7-302
creating comments about, 7-212
for data warehousing, 7-302
degree of parallelism, 7-48, 7-56
during creation, 7-304
detail table of, dropping, 7-460
enabling and disabling query rewrite, 7-308
examples, 7-310, 7-318
fast refresh, 7-49, 7-305, 7-306
forced refresh, 7-49
from existing tables, 7-304
granting
system privileges on, 7-497
index characteristics
changing, 7-49
indexes that maintain, 7-305
join, 7-316
LOB storage characteristics of, 7-48
logging changes to, 7-48
partitions of, 7-48
physical and storage attributes
changing, 7-48
physical attributes of, 7-303
primary key, 7-307
recording values in master table, 7-56
query rewrite
eligibility for, 7-226
enabling and disabling, 7-50
re-creating during refresh, 7-49
refresh mode
changing, 7-49
refresh time
changing, 7-49
refreshing after DML on master table, 7-50, 7-306
refreshing on next COMMIT, 7-49, 7-306
removing from the database, 7-459
for replication, 7-302
retrieving data from, 7-544
revalidating, 7-51
rowid, 7-307
rowid values
recording in master table, 7-56
saving blocks in a cache, 7-48
storage characteristics of, 7-303
subquery, 7-308
synonyms for, 7-356
when to populate, 7-304
MAX function, 4-24
MAX_DUMP_FILE_SIZE parameter
of ALTER SESSION, 7-84
of ALTER SYSTEM, 7-105
MAXDATAFILES parameter
of CREATE CONTROLFILE, 7-247
OF CREATE DATABASE, 7-252
MAXEXTENTS parameter
of STORAGE clause, 7-578
MAXINSTANCES parameter
of CREATE CONTROLFILE, 7-248
OF CREATE DATABASE, 7-252
MAXLOGFILES parameter
of CREATE CONTROLFILE, 7-247
OF CREATE DATABASE, 7-252
MAXLOGHISTORY parameter
of CREATE CONTROLFILE, 7-247
OF CREATE DATABASE, 7-252
MAXLOGMEMBERS parameter
of CREATE CONTROLFILE, 7-247
OF CREATE DATABASE, 7-252
MAXSIZE clause
of ALTER DATABASE, 7-10
of CREATE DATABASE, 7-250
of CREATE TABLESPACE, 7-395
of CREATE TEMPORARY TABLESPACE, 7-399
MAXTRANS parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of CREATE CLUSTER, 7-238
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, 7-371
MAXVALUE clause
of CREATE SEQUENCE, 7-352
MAXVALUE parameter
of ALTER SEQUENCE. See CREATE SEQUENCE.
media recovery
disabling, 7-17
of database, 7-12
of datafiles, 7-12
of standby database, 7-12
of tablespaces, 7-12
parallelizing, 7-14
restrictions, 7-12
sustained standby recovery, 7-14
MEMBER clause
of ALTER TYPE, 7-174
of CREATE TYPE, 7-415
of CREATE TYPE BODY, 7-422
membership conditions, 5-16
MERGE hint, 2-62
MERGE PARTITIONS clause
of ALTER TABLE, 7-150
MERGE_AJ hint, 2-59
MERGE_SJ hint, 2-59
MI date format element, 2-41
MI number format element, 2-36
MIN function, 4-24
MINEXTENTS parameter
of STORAGE clause, 7-577
MINIMIZE RECORDS PER BLOCK clause
of ALTER TABLE, 7-142
MINIMUM EXTENT clause
of ALTER TABLESPACE, 7-168
of CREATE TABLESPACE, 7-396
MINUS operator, 3-12, 7-550
MINVALUE
of ALTER SEQUENCE. See CREATE SEQUENCE.
MINVALUE clause
of CREATE SEQUENCE, 7-352
MM date format element, 2-41
MOD function, 4-24
MODE clause
of LOCK TABLE, 7-521
MODIFY clause
of ALTER TABLE, 7-132
MODIFY CONSTRAINT clause
of ALTER TABLE, 7-133
MODIFY DEFAULT ATTRIBUTES clause
of ALTER INDEX, 7-32, 7-39
of ALTER TABLE, 7-144
MODIFY LOB clause
of ALTER TABLE, 7-135
MODIFY LOB storage clause
of ALTER MATERIALIZED VIEW, 7-45, 7-48
of ALTER TABLE, 7-135
MODIFY NESTED TABLE clause
of ALTER TABLE, 7-135
MODIFY PARTITION clause
of ALTER INDEX, 7-32, 7-39
of ALTER MATERIALIZED VIEW, 7-49
of ALTER TABLE, 7-144
MODIFY SUBPARTITION clause
of ALTER INDEX, 7-33, 7-40
of ALTER TABLE, 7-145
MODIFY VARRAY clause
of ALTER TABLE, 7-135
modifying space for each cluster key, 7-4
MON date format element, 2-41, 2-43
MONITORING clause
of ALTER TABLE, 7-141
of CREATE TABLE, 7-384
MONTH date format element, 2-41, 2-43
MONTHS_BETWEEN function, 4-25
MOUNT clause
of ALTER DATABASE, 7-11
MOVE clause
of ALTER TABLE, 7-133
MOVE ONLINE clause
of ALTER TABLE, 7-133
MOVE PARTITION clause
of ALTER TABLE, 7-146
MOVE SUBPARTITION clause
of ALTER TABLE, 7-147
MTS_DISPATCHERS parameter
of ALTER SYSTEM, 7-106
MTS_SERVERS parameter
of ALTER SYSTEM, 7-106
multi-threaded server
system parameters, 7-105

N

NAMED clause
of CREATE JAVA, 7-295
namespaces
and object naming rules, 2-68
for nonschema objects, 2-69
for schema objects, 2-68, 2-69
NATIONAL CHAR datatype (ANSI), 2-24
NATIONAL CHAR VARYING datatype (ANSI), 2-24
NATIONAL CHARACTER datatype (ANSI), 2-24
national character set
fixed vs. variable width, 2-11, 2-12
multibyte character data, 2-21
multibyte character sets, 2-11, 2-12
variable-length strings, 2-12
NATIONAL CHARACTER SET clause
of CREATE DATABASE, 7-253
NATIONAL CHARACTER SET parameter
of ALTER DATABASE, 7-16
NATIONAL CHARACTER VARYING datatype
ANSI, 2-24
national language support. See NLS.
NCHAR datatype, 2-11
ANSI, 2-24
NCHAR VARYING datatype (ANSI), 2-24
NCLOB datatype, 2-21
transactional support of, 2-21
negative scale, 2-14
NESTED TABLE clause
of ALTER TABLE, 7-135
of CREATE TABLE, 7-364, 7-377
nested table types, 2-27
compared with varrays, 2-31
comparison rules, 2-31
creating, 7-413
dropping the body of, 7-482
dropping the specification of, 7-480
modifying, 7-135
nested tables
changing returned value, 7-135
creating, 7-418
defining as index-organized tables, 7-135
storage characteristics of, 7-135, 7-377
NEW_TIME function, 4-25
NEXT clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-50
NEXT parameter
of STORAGE clause, 7-577
NEXT_DAY function, 4-26
NEXTVAL pseudocolumn, 2-51, 7-351
NLS parameters
NLS_CALENDAR parameter
of ALTER SESSION, 7-84
NLS_CHARSET_DECL_LEN function, 4-27
NLS_CHARSET_ID function, 4-27
NLS_CHARSET_NAME function, 4-28
NLS_COMP parameter
of ALTER SESSION, 7-84
NLS_CURRENCY parameter
of ALTER SESSION, 7-84
NLS_DATE_FORMAT parameter
of ALTER SESSION, 7-84
NLS_DATE_LANGUAGE parameter, 2-43
of ALTER SESSION, 7-84
NLS_INITCAP function, 4-29
NLS_ISO_CURRENCY parameter
of ALTER SESSION, 7-84
NLS_LANGUAGE parameter, 2-43, 5-20
of ALTER SESSION, 7-85
NLS_LOWER function, 4-29
NLS_NUMERIC_CHARACTERS parameter
of ALTER SESSION, 7-85
NLS_SORT parameter, 5-20
of ALTER SESSION, 7-85
NLS_TERRITORY parameter, 2-43
of ALTER SESSION, 7-85
NLS_UNION_CURRENCY parameter
of ALTER SESSION, 7-85
NLS_UPPER function, 4-30
NLSSORT function, 4-30
NO_EXPAND hint, 2-59
NO_INDEX hint, 2-59
NO_MERGE hint, 2-62
NO_PUSH_JOIN_PRED hint, 2-62
NOAPPEND hint, 2-61
NOARCHIVELOG clause
of ALTER DATABASE, 7-9, 7-17
of CREATE CONTROLFILE, 7-248
OF CREATE DATABASE, 7-252
NOAUDIT schema_objects statement, 7-525
NOAUDIT sql_statements statement, 7-523
NOCACHE clause
of ALTER MATERIALIZED VIEW, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-56
of ALTER SEQUENCE. See CREATE SEQUENCE.
of ALTER TABLE, 7-141
of CREATE CLUSTER, 7-240
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE SEQUENCE, 7-352
of CREATE TABLE, 7-384
NOCACHE hint, 2-62
NOCOMPRESS clause
of ALTER TABLE, 7-133
of CREATE INDEX, 7-282
of CREATE TABLE, 7-374
NOCOPY clause
of CREATE FUNCTION, 7-269
of CREATE PROCEDURE, 7-335
NOCYCLE clause
of ALTER SEQUENCE. See CREATE SEQUENCE., 7-76
of CREATE SEQUENCE, 7-352
NOFORCE clause
of CREATE DIMENSION, 7-260
of CREATE JAVA, 7-295
of CREATE VIEW, 7-432
NOLOGGING clause
of ALTER INDEX, 7-36
of ALTER MATERIALIZED VIEW, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-56
of ALTER TABLE, 7-141
of ALTER TABLESPACE, 7-166
of CREATE INDEX, 7-282
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE TABLE, 7-372
of CREATE TABLESPACE, 7-396
NOMAXVALUE clause
of CREATE SEQUENCE, 7-352
NOMAXVALUE parameter
of ALTER SEQUENCE. See CREATE SEQUENCE.
NOMINIMIZE RECORDS PER BLOCK clause
of ALTER TABLE, 7-142
NOMINVALUE
of ALTER SEQUENCE. See CREATE SEQUENCE.
NOMINVALUE clause
of CREATE SEQUENCE, 7-352
NOMONITORING clause
of ALTER TABLE, 7-141
of CREATE TABLE, 7-384
NONE clause
of SET ROLE, 7-571
nonequivilancy tests, 3-6
nonschema objects
list of, 2-64
namespaces, 2-69
NOORDER clause
of ALTER SEQUENCE. See CREATE SEQUENCE.
of CREATE SEQUENCE, 7-353
NOPARALLEL clause
of CREATE CLUSTER, 7-240
of CREATE INDEX, 7-286
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE TABLE, 7-381
NOPARALLEL hint, 2-61
NOPARALLEL_INDEX hint, 2-61
NORELY clause
of constraint_clause, 7-226
NORESETLOGS clause
of CREATE CONTROLFILE, 7-247
NOREWRITE hint, 2-59
NOSORT clause
of ALTER INDEX, 7-282
of constraint_clause, 7-227
NOT DEFERRABLE clause
of constraint_clause, 7-226
NOT IDENTIFIED clause
of ALTER ROLE. See CREATE ROLE.
of CREATE ROLE, 7-345
NOT IN operator, 3-7
NOT NULL clause
of constraint_clause, 7-222
of CREATE TABLE, 7-370
NOT NULL constraints, 7-222
not null constraints, 7-222
NOT operator, 3-11
NOWAIT clause
of LOCK TABLE, 7-521
null, 2-49
difference from zero, 2-49
in conditions, 2-50
table of, 2-50
in functions, 2-49
with comparison operators, 2-49
NULL clause
of constraint_clause, 7-222
NULL conditions, 5-17
NUMBER datatype, 2-13
converting to VARCHAR2, 2-35
precision, 2-13
scale, 2-13
number format models, 2-35
number functions, 4-3
numbers
comparison rules, 2-27
floating-point, 2-13, 2-15
in SQL syntax, 2-4
precision of, 2-4
rounding, 2-14
spelling out, 2-45
syntax of, 2-4
NUMERIC datatype (ANSI), 2-24
NVARCHAR2 datatype, 2-12
NVL function, 4-31

O

object access expressions, 5-10
object cache, 7-86, 7-107
OBJECT IDENTIFIER clause
of CREATE TABLE, 7-378
object identifiers
contained in REFs, 2-26
of object views, 7-433
primary key, 7-378
specifying, 7-378
specifying an index on, 7-378
system-generated, 7-378
object privileges
granting, 7-344
multiple, 7-348
on specific columns, 7-506
to a role, 7-505
to a user, 7-505
to PUCLIC, 7-506
on a database object
revoking, 7-534
on a directory
revoking, 7-533
revoking
all, from a user, 7-533
from a role, 7-532, 7-533
from a user, 7-532, 7-533
from all users, 7-534
from PUBLIC, 7-533
object reference functions, 4-4
object tables
adding rows to, 7-513
creating, 7-359
object type bodies
creating, 7-422
re-creating, 7-422
SQL examples, 7-424
object type tables
creating, 7-368
object type values
comparing, 7-417, 7-423
object types, 2-26
adding new member subprograms, 7-174
associating functions or procedures, 7-174
attributes, 2-77
comparison rules, 2-30
MAP function, 2-30
ORDER function, 2-30
compiling the specification and body, 7-174
components of, 2-26
creating, 7-411, 7-413
defining member methods of, 7-422
disassociating statistics types from, 7-480
dropping the body of, 7-482
dropping the specification of, 7-480
function subprogram
declaring, 7-423
function subprogram of
specifying, 7-416
function subprograms of, 7-415, 7-422
incomplete, 7-413
methods, 2-77
procedure subprogram
declaring, 7-423
procedure subprogram of
specifying, 7-416
procedure subprograms of, 7-415, 7-422
SQL examples, 7-418
statistics types, 7-195
user-defined
creating, 7-414
object views
adding rows to the base table of, 7-513
defining, 7-430
OBJECT_CACHE_MAX_SIZE_PERCENT parameter
of ALTER SESSION, 7-86
of ALTER SYSTEM, 7-107
OBJECT_CACHE_OPTIMAL_SIZE parameter
of ALTER SESSION, 7-86
of ALTER SYSTEM, 7-107
objects. See object types or database objects.
OF clause
of CREATE VIEW, 7-432
OF object_type clause
of CREATE TABLE, 7-368
OFFLINE clause
of ALTER ROLLBACK SEGMENT, 7-74
of ALTER TABLESPACE, 7-168
of CREATE TABLESPACE, 7-397
OIDINDEX clause
of CREATE TABLE, 7-378
OIDs. See object identifiers.
ON clause
of CREATE OUTLINE, 7-323
ON COMMIT clause
of CREATE TABLE, 7-378
ON DATABASE clause
of CREATE TRIGGER, 7-406
ON DEFAULT clause
of AUDIT schema_objects, 7-206
of NOAUDIT schema_objects, 7-525
ON DELETE CASCADE clause
of constraint_clause, 7-224
ON DELETE SET NULL clause
of constraint_clause, 7-224
ON DIRECTORY clause
of AUDIT schema_objects, 7-206
of GRANT object_privileges, 7-506
of NOAUDIT schema_objects, 7-525
of REVOKE schema_object_privileges, 7-533
ON JAVA RESOURCE clause
of GRANT object_privileges, 7-506
ON JAVA SOURCE clause
of GRANT object_privileges, 7-506
ON NESTED TABLE clause
of CREATE TRIGGER, 7-406
ON object clause
of GRANT object_privileges, 7-506
of NOAUDIT schema_objects, 7-525
of REVOKE schema_object_privileges, 7-534
ON PREBUILT TABLE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
ON SCHEMA clause
of CREATE TRIGGER, 7-406
ONLINE clause
of ALTER ROLLBACK SEGMENT, 7-73
of ALTER TABLESPACE, 7-168
of CREATE INDEX, 7-283
of CREATE TABLESPACE, 7-397
online indexes, 7-283
rebuilding, 7-133
online redo logs
reinitializing, 7-19
OPEN NORESETLOGS clause
of ALTER DATABASE, 7-12
OPEN READ ONLY clause
of ALTER DATABASE, 7-11
OPEN READ WRITE clause
of ALTER DATABASE, 7-12
OPEN RESETLOGS clause
of ALTER DATABASE, 7-12
operands, 3-1
operator precedence, 3-2
operators, 3-1
arithmetic, 3-3
binary, 3-1
comparison, 3-5
concatenation, 3-3
granting
system privileges on, 7-497
logical, 3-10
set, 3-12, 7-550
unary, 3-1
user-defined, 3-16
binding to a function, 7-321
creating, 7-321
dropping, 7-463
function providing implementation, 7-322
how bindings are implemented, 7-321
implementation type, 7-322
return type of binding, 7-321
OPTIMAL parameter
of STORAGE clause, 7-347, 7-578
optimizer
setting session parameters, 7-86
OPTIMIZER_INDEX_CACHING parameter
of ALTER SESSION, 7-86
OPTIMIZER_INDEX_COST_ADJ parameter
of ALTER SESSION, 7-86
OPTIMIZER_MAX_PERMUTATIONS parameter
of ALTER SESSION, 7-86
OPTIMIZER_MODE parameter
of ALTER SESSION, 7-86
OPTIMIZER_PERCENT_PARALLEL parameter
of ALTER SESSION, 7-86
OPTIMIZER_SEARCH_LIMIT parameter
of ALTER SESSION, 7-86
OR operator, 3-11, 3-12
OR REPLACE clause
of CREATE CONTEXT, 7-243
of CREATE DIRECTORY, 7-265
of CREATE FUNCTION, 7-268, 7-294
of CREATE LIBRARY, 7-298
of CREATE OUTLINE, 7-323
of CREATE PACKAGE, 7-326
of CREATE PACKAGE BODY, 7-329
of CREATE PROCEDURE, 7-335
of CREATE TRIGGER, 7-403
of CREATE TYPE, 7-414
of CREATE TYPE BODY, 7-422
of CREATE VIEW, 7-432
Oracle precompilers, 1-3
Oracle reserved words, C -1
Oracle Tools
support of SQL, 1-5
Oracle8i
Enterprise Edition
features and functionality, xi
features and functionality, xi
new features, xiii
ORDER BY clause
of CREATE TABLE, 7-385
of queries, 5-20
of SELECT, 5-20, 7-543, 7-551
with ROWNUM, 2-56
of subqueries in CREATE TABLE, 7-385
ORDER clause
of ALTER SEQUENCE. See CREATE SEQUENCE.
of CREATE SEQUENCE, 7-353
ORDER MEMBER clause
of ALTER TYPE, 7-175
of CREATE TYPE, 7-417
of CREATE TYPE BODY, 7-423
ORDER methods
specifying, 7-175
ORDERED hint, 2-61
ORDERED_PREDICATES hint, 2-59
ordinal numbers
specifying, 2-45
spelling out, 2-45
ORGANIZATION HEAP clause
of CREATE TABLE, 7-373
ORGANIZATION INDEX clause
of CREATE TABLE, 7-373
OUT parameter
of CREATE FUNCTION, 7-269
of CREATE PROCEDURE, 7-335
outer joins, 5-22, 7-548
restrictions, 5-22
outlines
assigning to a different category, 7-58
automatically creating and storing, 7-101
creating, 7-323
dropping from the database, 7-464
enabling and disabling dynamically, 7-323
granting
system privileges on, 7-497
rebuilding, 7-58
renaming, 7-58
replacing, 7-323
storing during the session, 7-81
storing for the instance, 7-108
use by the optimizer, 7-89, 7-108
used to generate execution plans, 7-323
OVERFLOW clause
of ALTER INDEX, 7-33
of ALTER TABLE, 7-143
of CREATE TABLE, 7-373

P

package bodies
creating, 7-328
recompiling, 7-59
re-creating, 7-329
removing from the database, 7-465
PACKAGE clause
of ALTER PACKAGE, 7-60
packaged procedures
dropping, 7-467
packages
associating statistics with, 7-196
avoiding run-time compilation, 7-59
changing the declaration of, 7-59
creating, 7-325
disassociating statistics types from, 7-466
invoker rights, 7-326
recompiling, 7-59
redefining, 7-59, 7-326
removing from the database, 7-465
specifying schema and privileges of, 7-326
synonyms for, 7-356
PACKAGES clause
of ASSOCIATE STATISTICS, 7-194, 7-196
PARALLEL clause
of ALTER CLUSTER, 7-3, 7-5
of ALTER DATABASE, 7-14
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-55, 7-56
of ALTER TABLE, 7-153
of CREATE CLUSTER, 7-240
of CREATE INDEX, 7-286
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-315
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
of CREATE TABLE, 7-366, 7-381
parallel execution
of DDL statements, 7-79
of DML statements, 7-79
PARALLEL hint, 2-61
PARALLEL_ADAPTIVE_MULTI_USER parameter
of ALTER SYSTEM, 7-107
PARALLEL_BROADCAST_ENABLED parameter
of ALTER SESSION, 7-87
PARALLEL_ENABLE clause
of CREATE FUNCTION, 7-270
PARALLEL_INDEX hint, 2-61
PARALLEL_INSTANCE_GROUP parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-107
PARALLEL_MIN_PERCENT parameter
of ALTER SESSION parameter, 7-87
PARALLEL_THREADS_PER_CPU parameter
of ALTER SYSTEM, 7-107
parameters
in syntax diagrams, xvi
optional, A-2
required, A-1
PARAMETERS clause
of CREATE INDEX, 7-286
partition
storage characteristics, 7-371
PARTITION ... LOB storage clause
of ALTER TABLE, 7-131
PARTITION BY HASH clause
of CREATE TABLE, 7-378
PARTITION BY RANGE clause
of CREATE TABLE, 7-364, 7-379
PARTITION clause
of ANALYZE, 7-188
of CREATE INDEX, 7-284
of CREATE TABLE, 7-380
of DELETE, 7-441
of INSERT, 7-515
of LOCK TABLE, 7-521
of SELECT, 7-546
of UPDATE, 7-587
PARTITION_VIEW_ENABLED parameter
of ALTER SESSION, 7-87
partitioned indexes, 2-65, 7-277, 7-284
user-defined, 7-283
partitioned tables, 2-65
partition-extended table names, 2-65
in DML statements, 2-66
restrictions on, 2-66
syntax, 2-66
partitioning
by range, 7-364
partitioning clauses
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of ALTER INDEX, 7-31
of ALTER MATERIALIZED VIEW, 7-45, 7-48
of ALTER MATERIALIZED VIEW LOG, 7-54, 7-56
of ALTER TABLE, 7-144
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-315
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-304
partitions
adding rows to, 7-513
allocating extents for, 7-139
composite, 2-65
specifying, 7-379
converting into nonpartitioned tables, 7-151
deallocating unused space from, 7-140
dropping, 7-148
extents
allocating new, 7-34
hash, 2-65
adding, 7-148
coalescing, 7-148
specifying, 7-378
inserting rows into, 7-515
LOB storage characteristics of, 7-131
locking, 7-520
logging attribute of, 7-372
logging insert operations, 7-141
merging, 7-150
modifying, 7-144
moving to a different segment, 7-146
physical attributes
changing, 7-134
range, 2-65
adding, 7-147
specifying, 7-379
removing rows from, 7-149, 7-441
renaming, 7-146
revising values in, 7-587
splitting, 7-149
tablespace for
defining, 7-371
PASSWORD EXPIRE clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-427
password parameters
of ALTER PROFILE, 7-342
of CREATE PROFILE, 7-339
PASSWORD_GRACE_TIME parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
PASSWORD_LIFE_TIME parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
PASSWORD_LOCK_TIME parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
PASSWORD_REUSE_MAX parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
PASSWORD_REUSE_TIME parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-341
PASSWORD_VERIFY_FUNCTION parameter
of ALTER PROFILE, 7-65
of CREATE PROFILE, 7-342
passwords, expiration of, 7-427
PCTFREE parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of CREATE CLUSTER, 7-238
of CREATE INDEX, 7-281
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE.
of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE.
of CREATE TABLE, 7-370
PCTINCREASE parameter
of STORAGE clause, 7-577
PCTTHRESHOLD parameter
of ALTER TABLE, 7-143
of CREATE TABLE, 7-373
PCTUSED parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of CREATE CLUSTER, 7-238
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, 7-370
PCTVERSION parameter
of CREATE TABLE, 7-376
of LOB storage clause, 7-131
PERMANENT clause
of ALTER TABLESPACE, 7-169
of CREATE TABLESPACE, 7-397
physical attributes clause
of a constraint, 7-220
of ALTER CLUSTER, 7-2
of ALTER INDEX, 7-30, 7-35
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of ALTER TABLE, 7-134
of CREATE CLUSTER, 7-236
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-315
of CREATE TABLE, 7-362, 7-370
PLAN_TABLE sample table, 7-486
PL/SQL, xi
compatibility with earlier releases, 7-87, 7-107
PL/SQL blocks
syntax of, xviii
PL/SQL program body
of CREATE FUNCTION, 7-271
PLSQL_V2_COMPATIBILITY parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-107
PM (P.M.) date format element, 2-41, 2-43
POWER function, 4-31
PQ_DISTRIBUTE hint, 2-61
PR number format element, 2-36
PRAGMA clause
of ALTER TYPE, 7-175
of CREATE TYPE, 7-412, 7-416
PRAGMA RESTRICT_REFERENCES, 7-175, 7-416
precedence
of operators, 3-2
precision
number of digits of, 2-4
of NUMBER datatype, 2-13
PRESERVE SNAPSHOT LOG clause
of TRUNCATE, 7-582
PRIMARY KEY clause
of constraint_clause, 7-222
of CREATE TABLE, 7-370
primary key constraints, 7-222
enabling, 7-383
index on, 7-383
primary keys
generating values for, 7-350
PRIOR operator, 3-16
PRIVATE_SGA parameter
of ALTER PROFILE, 7-64
of ALTER RESOURCE COST, 7-69
of CREATE PROFILE, 7-341
privileges. See system privileges or object privileges.
procedure specification
of CREATE TYPE, 7-416
procedures
3GL, calling, 7-298
calling, 7-210
changing the declaration of, 7-62
changing the definition of, 7-62
creating, 7-334
declaring as a Java method, 7-336
declaring as C functions, 7-336
executing, 7-210
external, 7-334
granting
system privileges on, 7-498
invalidating local objects dependent on, 7-467
issuing COMMIT or ROLLBACK statements, 7-79
naming rules, 2-69
privileges executed with, 7-415
recompiling, 7-62
re-creating, 7-335
removing from the database, 7-467
schema executed in, 7-415
specifying schema and privileges for, 7-336
synonyms for, 7-356
PROFILE clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-427
profiles
assigning to a user, 7-427
creating, 7-339
examples, 7-342
deassigning from users, 7-468
granting
system privileges on, 7-498
modifying, examples, 7-66
removing from the database, 7-468
proxy clause
of ALTER USER, 7-180, 7-181
pseudocolumns, 2-51
CURRVAL, 2-51
LEVEL, 2-53
NEXTVAL, 2-51
ROWID, 2-54
ROWNUM, 2-55
uses for, 2-56
PUBLIC clause
of CREATE ROLLBACK SEGMENT, 7-346
of CREATE SYNONYM, 7-357
of DROP DATABASE LINK, 7-449
public database links
dropping, 7-449
public rollback segments, 7-346
public synonyms, 7-357
dropping, 7-474
PURGE SNAPSHOT LOG clause
of TRUNCATE, 7-582
PUSH_JOIN_PRED hint, 2-62
PUSH_SUBQ hint, 2-62

Q

Q date format element, 2-41
queries, 5-18, 7-544
comments in, 5-18
compound, 5-20
correlated
left correlation, 7-547
defined, 5-18
distributed, 5-25
grouping returned rows on a value, 7-549
hierarchical. See hierarchical queries
hints in, 5-18
join, 5-21
locking rows during, 7-551
ordering returned rows, 7-551
outer joins in, 7-547, 7-548
referencing multiple tables, 5-21
restricting results of, 7-548
select lists of, 5-18
selecting from a random sample of rows, 7-546
selecting from specified partitions, 7-546
sorting results, 5-20
syntax, 5-18
top-level, 5-18
top-N, 2-56
query rewrite
and dimensions, 7-260
and function-based indexes, 7-87
and the rule-based optimizer, 7-87
consistency level, 7-88
defined, 7-544
disabling, 7-87, 7-106
enabling, 7-87, 7-106
QUERY REWRITE system privilege, 7-496, 7-497, 7-499
QUERY_REWRITE_ENABLED parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-106
QUERY_REWRITE_INTEGRITY parameter
of ALTER SESSION, 7-88
QUOTA clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-427

R

range conditions, 5-16
range partition
adding, 7-147
creating, 7-379
RAW data
converting from CHAR data, 2-19
RAW datatype, 2-18
RAWTOHEX function, 2-32, 4-31
READ object privilege, 7-508
READ ONLY clause
of ALTER TABLESPACE, 7-169
of SET TRANSACTION, 7-572
READ WRITE clause
of ALTER TABLESPACE, 7-169
of SET TRANSACTION, 7-573
REAL datatype
ANSI, 2-24
REBUILD clause
of ALTER INDEX, 7-31, 7-36
of ALTER OUTLINE, 7-58
REBUILD COMPRESS clause
of ALTER INDEX, 7-37
REBUILD COMPUTE STATISTICS clause
of ALTER INDEX, 7-38
REBUILD LOGGING clause
of ALTER INDEX, 7-38
REBUILD NOCOMPRESS clause
of ALTER INDEX, 7-38
REBUILD NOLOGGING clause
of ALTER INDEX, 7-38
REBUILD NOREVERSE clause
of ALTER INDEX, 7-37
REBUILD ONLINE clause
of ALTER INDEX, 7-38
REBUILD PARAMETERS clause
of ALTER INDEX, 7-38
REBUILD PARTITION clause
of ALTER INDEX, 7-37
REBUILD REVERSE clause
of ALTER INDEX, 7-37
REBUILD SUBPARTITION clause
of ALTER INDEX, 7-37
REBUILD TABLESPACE clause
of ALTER INDEX, 7-37
REBUILD UNUSABLE LOCAL INDEXES clause
of ALTER TABLE, 7-145
RECOVER AUTOMATIC clause
of ALTER DATABASE, 7-13
RECOVER CANCEL clause
of ALTER DATABASE, 7-7, 7-14
RECOVER clause
of ALTER DATABASE, 7-7, 7-12
RECOVER CONTINUE clause
of ALTER DATABASE, 7-7, 7-14
RECOVER DATABASE clause
of ALTER DATABASE, 7-7, 7-13
RECOVER DATAFILE clause
of ALTER DATABASE, 7-7, 7-13
RECOVER LOGFILE clause
of ALTER DATABASE, 7-7, 7-14
RECOVER MANAGED STANDBY DATABASE clause
of ALTER DATABASE, 7-8
RECOVER STANDBY DATABASE clause
of ALTER DATABASE, 7-13
RECOVER STANDBY DATAFILE clause
of ALTER DATABASE, 7-14
RECOVER STANDBY TABLESPACE clause
of ALTER DATABASE, 7-14
RECOVER TABLESPACE clause
of ALTER DATABASE, 7-7, 7-13
RECOVERABLE, 7-36, 7-372
See also LOGGING clause.
recovery
distributed, enabling, 7-99
of database, 7-7
RECOVERY_CATALOG_OWNER role, 7-503
redo log file groups
switching, 7-100
redo log file members
adding to existing groups, 7-18
dropping, 7-18
renaming, 7-15
redo log files
adding, 7-18
automatic archiving of, 7-96
automatic name generation, 7-13
disabling specified threads in a parallel server, 7-15
dropping, 7-18
enabling specified threads in a parallel server, 7-15
reusing, 7-491
size of, 7-491
specifying, 7-490
for media recovery, 7-14
REF columns
specifying, 7-369
specifying from table or column level, 7-369
REF function, 4-32
REFERENCES clause
of constraint_clause, 7-223
of CREATE TABLE, 7-370
REFERENCES object privilege, 7-508
references to objects. See REFs.
REFERENCING clause
of CREATE TRIGGER, 7-402, 7-406
referential integrity constraints, 7-222, 7-223
REFRESH clause
of ALTER MATERIALIZED VIEW, 7-46, 7-49
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-301
REFRESH COMPLETE clause
of ALTER MATERIALIZED VIEW, 7-49
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
REFRESH FAST clause
of ALTER MATERIALIZED VIEW, 7-49
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
REFRESH FORCE clause
of ALTER MATERIALIZED VIEW, 7-49
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
REFRESH ON COMMIT clause
of ALTER MATERIALIZED VIEW, 7-49
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
REFRESH ON DEMAND clause
of ALTER MATERIALIZED VIEW, 7-50
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
REFs, 2-26, 7-224
as containers for OIDs, 2-26
dangling, 7-190
validating, 7-190
REFTOHEX function, 4-32
relational tables
creating, 7-359
RELY clause
of constraint_clause, 7-226
REMOTE_DEPENDENCIES_MODE parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-107
REMOTE_LOGIN_PASSWORDFILE parameter
and control files, 7-246
and databases, 7-251
RENAME clause
of ALTER INDEX, 7-39
of ALTER OUTLINE, 7-58
of ALTER TABLE, 7-142
RENAME DATAFILE clause
of ALTER TABLESPACE, 7-167
RENAME FILE clause
of ALTER DATABASE, 7-15
RENAME GLOBAL_NAME clause
of ALTER DATABASE, 7-15
RENAME PARTITION clause
of ALTER INDEX, 7-32, 7-40
of ALTER TABLE, 7-146
RENAME statement, 7-527
RENAME SUBPARTITION clause
of ALTER INDEX, 7-32, 7-40
of ALTER TABLE, 7-146
REPLACE AS OBJECT clause
of ALTER TYPE, 7-174
REPLACE function, 4-33
reserved words, 2-68, C -1
RESET COMPATIBILITY clause
of ALTER DATABASE, 7-15
RESETLOGS parameter
of CREATE CONTROLFILE, 7-247
RESOLVE clause
of ALTER JAVA CLASS, 7-44
of CREATE JAVA, 7-294
RESOLVER clause
of ALTER JAVA CLASS, 7-44
of ALTER JAVA SOURCE, 7-44
of CREATE JAVA, 7-296
resource parameters
of CREATE PROFILE, 7-338
RESOURCE role, 7-503
RESOURCE shortcut
of AUDIT sql_statements, 7-198
RESOURCE_LIMIT parameter
of ALTER SYSTEM, 7-107
RESOURCE_MANAGER_PLAN parameter
of ALTER SYSTEM, 7-108
RESTRICT_REFERENCES pragma
of ALTER TYPE, 7-175
restricted rowids, 2-22
compatibility and migration of, 2-23
RESTRICTED SESSION system privilege, 7-499
RESUME clause
of ALTER SYSTEM, 7-100
RETURN clause
of CREATE FUNCTION, 7-270
of CREATE OPERATOR, 7-321
of CREATE TYPE BODY, 7-423
RETURNING clause
of DELETE, 7-441
of INSERT, 7-513, 7-517
of UPDATE, 7-585, 7-588
REUSE clause
of CREATE CONTROLFILE, 7-246
of filespec clause, 7-491
REUSE STORAGE clause
of TRUNCATE, 7-583
REVERSE clause
of CREATE INDEX, 7-282
reverse indexes, 7-282
REVOKE CONNECT THROUGH clause
of ALTER USER, 7-180, 7-181
REVOKE schema_object_privileges statement, 7-532
REVOKE system_privileges_and_roles statement, 7-529
REWRITE hint, 2-59
RM date format element, 2-41
RN number format element, 2-36
RNDS parameter
of PRAGMA RESTRICT_REFERENCES, 7-175
RNPS parameter
of PRAGMA RESTRICT_REFERENCES, 7-175
roles
assigning to a user, 7-427
authorized by a password, 7-345
authorized by an external service, 7-345
authorized by the database, 7-345
authorizes by the enterprise directory service, 7-345
changing authorization for, 7-71
creating, 7-344
disabling for the current session, 7-571
disabling for the session, 7-570
enabling for the current session, 7-571
enabling for the session, 7-570
granting, 7-493
system privileges on, 7-498
to a user, 7-494
to all users, 7-494
to another role, 7-494
to PUBLIC, 7-494
removing from the database, 7-469
revoking, 7-529
from a user, 7-530
from all users, 7-530
from another role, 7-469, 7-530
from PUBLIC, 7-530
from users, 7-469
rollback segments
bringing online, 7-73, 7-347
changing storage characteristics, 7-73
creating, 7-346
granting
system privileges on, 7-498
multiple, 7-347
public, 7-346
reducing size, 7-73
removing from the database, 7-470
specifying optimal size of, 7-578
specifying tablespaces for, 7-346
SQL examples, 7-347
storage characteristics, 7-575
storage characteristics of, 7-347
taking offline, 7-73
ROLLBACK statement, 7-537
ROLLUP operation
example, 4-17
of queries and subqueries, 7-549
ROUND
date function, 4-34
number function, 4-33
ROUND function
format models, 4-55
routines
calling, 7-210
executing, 7-210
ROW EXCLUSIVE lock mode, 7-521
ROW SHARE lock mode, 7-521
ROWID
datatype, 2-21
hint, 2-59
pseudocolumn, 2-21, 2-23, 2-54
rowids
block portion of, 2-22
description of, 2-21
extended, 2-22
not directly available, 2-22
file portion of, 2-22
nonphysical, 2-23
of foreign tables, 2-23
of index-organized tables, 2-23
restricted, 2-22
compatibility and migration of, 2-23
row portion of, 2-22
uses for, 2-54
ROWIDTOCHAR function, 2-32, 4-34
ROWNUM pseudocolumn, 2-55
uses for, 2-56
rows
adding to a table, 7-513
allowing movement of between partitions, 7-361
insert
into remote databases, 7-515
inserting
into partitions, 7-515
into subpartitions, 7-515
movement between partitions, 7-381
order of storage, 7-373
removing
from a cluster, 7-581
from a table, 7-581
removing from partitions and subpartitions, 7-441
removing from tables and views, 7-439
selecting in hierarchical order, 5-19
specifying constraints on, 7-224
stored in ascending order, 7-227
storing if in violation of constraints, 7-228
RPAD function, 4-35
RR date format element, 2-41, 2-44
interpreting, 2-44
RRRR date format element, 2-41
RTRIM function, 4-35
RULE hint, 2-59
run-time compilation
avoiding, 7-62, 7-183

S

S number format element, 2-36
SAMPLE clause
of SELECT, 7-546
of SELECT and subqueries, 7-542
SAVEPOINT statement, 7-539
savepoints
erasing, 7-214
rolling back to, 7-537
specifying, 7-539
scale
greater than precision, 2-14
negative, 2-14
of NUMBER datatype, 2-13
SCC date format element, 2-41
schema
definition of, 2-63
SCHEMA clause
of CREATE JAVA, 7-295
schema objects, 2-63
auditing
by access, 7-206
by session, 7-206
options, 7-207
successful
SQL statements on, 7-206
defining default buffer pool for, 7-579
dropping, 7-483
in other schemas, 2-74
list of, 2-63
name resolution, 2-73
namespaces, 2-68
naming examples, 2-70
naming guidelines, 2-71
naming rules, 2-67
object types, 2-26
on remote databases, 2-74
partitioned indexes, 2-65
partitioned tables, 2-65
parts of, 2-64
reauthorizing, 6-2
recompiling, 6-2
referring to, 2-71, 7-81
remote, accessing, 7-255
stopping auditing of, 7-525
schemas
changing for a session, 7-81
creating, 7-348
scientific notation, 2-37
SCOPE clause
of column ref constraints, 7-225
scope constraints, 7-225
segment attributes clause
of CREATE TABLE, 7-361, 7-370
SELECT
object privilege, 7-508
statement, 7-541
SELECT ANY SEQUENCE system privilege, 7-499
SELECT ANY TABLE system privilege, 7-500
select lists, 5-18
ordering, 5-20
SELECT statement, 5-18
SELECT_CATALOG_ROLE role, 7-503
self joins, 5-22
sequences, 2-51, 7-351
accessing values of, 7-351
changing the increment value, 7-76
changing the number of cached values, 7-76
creating, 7-350
creating without limit, 7-351
granting
system privileges on, 7-499
how to use, 2-52
incrementing, 7-350, 7-352
maximum value
setting or changing, 7-76
minimum value
setting or changing, 7-76
ordering values, 7-76
recycling values, 7-76
removing from the database, 7-471
renaming, 7-527
restarting, 7-471
at a different number, 7-77
restarting at a predefined limit, 7-351
reusing, 7-351
stopping at a predefined limit, 7-351
synonyms for, 7-356
where to use, 2-52
SERVERERROR
triggers on, 7-405
SERVERERROR event
triggers on, 7-405
service name
of remote database, 7-257
session
global name resolution for, 7-82
session control statements, 6-5
PL/SQL support of, 6-5
session locks
releasing, 7-100
SESSION_CACHED_CURSORS parameter
of ALTER SESSION, 7-88
SESSION_ROLES view, 7-570
sessions
calculating resource cost limits, 7-68
changing resource cost limits, 7-68
disconnecting, 7-99
granting
system privileges on, 7-499
limiting resource costs, 7-68
modifying characteristics of, 7-81
number of concurrent, 7-103
object cache, 7-86
restricted, 7-99
terminating, 7-100
SESSIONS_PER_USER parameter
of ALTER PROFILE, 7-64
of CREATE PROFILE, 7-340
SET clause
of ALTER SESSION, 7-81
of ALTER SYSTEM, 7-100
of UPDATE, 7-588
SET CONSTRAINT(S) statement, 7-568
SET DATABASE clause
of CREATE CONTROLFILE, 7-246
set operators, 3-12, 7-550
SET ROLE statement, 7-570
SET STATEMENT_ID clause
of EXPLAIN PLAN, 7-487
SET TRANSACTION statement, 7-572
SET UNUSED clause
of ALTER TABLE, 7-136
SGA
flushing, 7-99
updating, 7-98
SHARE ROW EXCLUSIVE lock mode, 7-521
SHARE UPDATE lock mode, 7-521
SHARED clause
of CREATE DATABASE LINK, 7-256
shared server processes
creating additional, 7-105
terminating, 7-105
SHRINK clause
of ALTER ROLLBACK SEGMENT, 7-74
SHUTDOWN
triggers on, 7-405
SHUTDOWN event
triggers on, 7-405
SIGN function, 4-36
simple comparison conditions, 5-15
simple expressions, 5-3
SIN function, 4-36
SINGLE TABLE clause
of CREATE CLUSTER, 7-239
single-row functions, 4-3
miscellaneous, 4-4
SINH function, 4-36
SIZE clause
of CREATE CLUSTER, 7-238
of filespec clause, 7-491
SIZE parameter
of ALTER CLUSTER, 7-4
SKIP_UNUSABLE_INDEXES parameter
of ALTER SESSION, 7-88
SMALLINT datatype
ANSI, 2-24
DB2, 2-25
SQL/DS, 2-25
snapshot logs. See materialized view logs.
snapshots. See materialized views.
SNMPAGENT role, 7-503
SOME operator, 3-6
SORT_AREA_RETAINED_SIZE parameter
of ALTER SESSION, 7-88
of ALTER SYSTEM, 7-108
SORT_AREA_SIZE parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-108
SORT_MULTIBLOCK_READ_COUNT parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-108
SOUNDEX function, 4-37
SP date format element suffix, 2-45
SPECIFICATION clause
of ALTER PACKAGE, 7-60
spelled numbers
specifying, 2-45
SPLIT PARTITION clause
of ALTER INDEX, 7-33, 7-40
of ALTER TABLE, 7-149
SPTH date format element suffix, 2-45
SQL
description of, 1-2
embedded, 1-3
functions, 4-1
keywords, A-1
Oracle Tools support of, 1-5
parameters, A-1
standards, 1-2, B - 1
statements
auditing, 7-200
determining the cost of, 7-486
syntax, 7-1, A-1
SQL functions
aggregate, 4-5
character
returning character values, 4-3
returning number values, 4-3
date, 4-4
number, 4-3
object reference, 4-4
single-row, 4-3
miscellaneous, 4-4
SQL statements
auditing by access, 7-199
auditing by proxy, 7-199
auditing by session, 7-199
auditing by user, 7-199
auditing successful, 7-199
determining the execution plan for, 7-486
rolling back, 7-537
stopping auditing of, 7-523
tracking the occurrence in a session, 7-197
undoing, 7-537
SQL_TRACE parameter
of ALTER SESSION, 7-89
SQL92, 1-2
Oracle compliance with, B - 2
SQL/DS datatypes, 2-23
conversion to Oracle datatypes, 2-25
restrictions on, 2-25
SQRT function, 4-38
SS date format element, 2-41
SSSSS date format element, 2-41
standalone procedures
dropping, 7-467
standard SQL, B - 1
Oracle extensions to, B - 5
standby control file
creating, 7-19
standby database
activating, 7-11
designing media recovery, 7-12
mounting, 7-11
recovering, 7-13, 7-14
STANDBY_ARCHIVE_DEST parameter
of ALTER SYSTEM, 7-108
STAR hint, 2-61
STAR_TRANSFORMATION hint, 2-62
STAR_TRANSFORMATION_ENABLED parameter
of ALTER SESSION, 7-89
START WITH clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-50
of CREATE SEQUENCE, 7-352
of SELECT, 7-548
of SELECT and subqueries, 7-543
STARTUP
triggers on, 7-405
STARTUP event
triggers on, 7-405
STATIC clause
of ALTER TYPE, 7-174
of CREATE TYPE, 7-416
of CREATE TYPE BODY, 7-422
statistics
associating
with columns, 7-195
with datatypes, 7-196
with domain indexes, 7-196
with functions, 7-196
with indextypes, 7-196
with packages, 7-196
computing exactly, 7-188
deleting from the data dictionary, 7-190
estimating, 7-188
forcing disassociation, 7-445
on indexes, 7-283
user-defined
dropping, 7-452, 7-454, 7-456, 7-466, 7-475, 7-480
statistics types
disassociating
from columns, 7-444
from functions, 7-444
from packages, 7-444
from types, 7-444
disassociating from domain indexes, 7-444
disassociating from indextypes, 7-444
STDDEV function, 4-38
storage characteristics
resetting, 7-581
STORAGE clause, 7-575
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-30
of ALTER MATERIALIZED VIEW, 7-46
of ALTER MATERIALIZED VIEW LOG, 7-54
of ALTER ROLLBACK SEGMENT, 7-73, 7-74
of CREATE INDEX, 7-281
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-315
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG. See CREATE TABLE.
of CREATE MATERIALIZED VIEW/SNAPSHOT. See CREATE TABLE.
of CREATE ROLLBACK SEGMENTS, 7-347
of CREATE TABLE, 7-362, 7-371
of CREATE TABLESPACE, 7-395
STORAGE IN ROW clause
of ALTER TABLE, 7-131
STORAGE parameter
of ALTER INDEX, 7-30, 7-35
storage_clause
of CREATE CLUSTER, 7-238
STORE IN DEFAULT clause
of CREATE INDEX, 7-285
STORE IN tablespace clause
of CREATE INDEX, 7-285
stored functions, 7-267
Structured Query Language. See SQL.
SUBPARTITION BY HASH clause
of CREATE TABLE, 7-365, 7-379
SUBPARTITION clause
of ANALYZE, 7-188
of CREATE INDEX, 7-285
of CREATE TABLE, 7-380
of DELETE, 7-441
of INSERT, 7-515
of LOCK TABLE, 7-521
of SELECT, 7-546
of UPDATE, 7-587
subpartition-extended table names, 2-65
in DML statements, 2-66
restrictions on, 2-66
syntax, 2-66
subpartitions
adding, 7-145
adding rows to, 7-513
allocating extents for, 7-139, 7-145
coalescing, 7-145
converting into nonpartitioned tables, 7-151
creating, 7-365, 7-380
deallocating unused space from, 7-140, 7-145
inserting rows into, 7-515
locking, 7-520
logging insert operations, 7-141
moving to a different segment, 7-147
physical attributes
changing, 7-134
removing rows from, 7-149, 7-441
renaming, 7-146
revising values in, 7-587
specifying, 7-379
SUBPARTITIONS clause
of CREATE TABLE, 7-379
subqueries, 5-18, 7-541, 7-544
containing subqueries, 5-24
correlated, 5-24
defined, 5-18
to insert table data, 7-385
SUBSTR function, 4-38
SUBSTRB function, 4-39
subtotal values
deriving, 7-549
SUM function, 4-40
SUSPEND clause
of ALTER SYSTEM, 7-100
sustained standby recovery mode, 7-14
terminating, 7-14
timeout period, 7-14
SWITCH LOGFILE clause
of ALTER SYSTEM, 7-100
SYEAR date format element, 2-41
synonyms
changing the definition of, 7-474
creating, 7-356
granting
system privileges on, 7-499
local, 7-357
private, dropping, 7-474
public, 7-357
dropping, 7-474
remote, 7-357
removing from the database, 7-474
renaming, 7-527
synonyms for, 7-356
syntax
loops, A-2
multipart diagrams, A-3
syntax diagrams, A-1
explanation of, xv
keywords, xvi
parameters, xvi
SYS schema
database triggers stored in, 7-407
functions stored in, 7-407
SYS_CONTEXT function, 4-40
SYS_GUID function, 4-41
SYSDATE function, 4-42
SYSDBA system privilege, 7-502
SYSOPER system privilege, 7-502
system control statements, 6-5
PL/SQL support of, 6-5
system date
altering, 7-102
system events
attributes of, 7-407
triggers on, 7-405
system global area. See SGA.
system privileges
granting, 7-344, 7-493
to a role, 7-494
to a user, 7-494
to all users, 7-494
to PUBLIC, 7-494
list of, 7-495
revoking, 7-529
from a role, 7-530
from a user, 7-529
from all users, 7-530
from PUBLIC, 7-530
SYYYY date format element, 2-41

T

table alias
in CREATE INDEX, 7-279
table aliases, 2-77
in DELETE, 7-441
TABLE clause
of DELETE, 7-441
of INSERT, 7-515
of SELECT, 7-547
of TRUNCATE, 7-582
of UPDATE, 7-586, 7-587
table constraint
defined, 7-217
of ALTER TABLE, 7-130
of CREATE TABLE, 7-370
table locks
disabling, 7-153
duration of, 7-520
enabling, 7-153
EXCLUSIVE, 7-521
modes of, 7-521
on partitions, 7-521
on remote database, 7-521
on subpartitions, 7-521
and queries, 7-520
ROW EXCLUSIVE, 7-521
ROW SHARE, 7-521
SHARE, 7-521
SHARE ROW EXCLUSIVE, 7-521
SHARE UPDATE, 7-521
table REF constraint
of ALTER TABLE, 7-130
table ref constraint, 7-218
of ALTER TABLE, 7-130
of CREATE TABLE, 7-369
table REF constraints, 7-224
tables
adding rows to, 7-513
allocating extents for, 7-139
assigning to a cluster, 7-378
changing degree of parallelism on, 7-153
changing existing values in, 7-585
collecting modification statistics on, 7-141
collecting statistics on, 7-187
creating, 7-366
multiple, 7-348
creating comments about, 7-212
deallocating unused space from, 7-140
default physical attributes
changing, 7-134
degree of parallelism
specifying, 7-366
disassociating statistics types from, 7-475
dropping
along with owner, 7-483
dropping along with cluster, 7-447
dropping indexes of, 7-475
dropping partitions of, 7-475
granting
system privileges on, 7-500
index-organized
creating, 7-373
overflow segment for, 7-373
space in index block, 7-373
inserting rows with a subquery, 7-385
LOB storage of, 7-371
locking, 7-520
logging
table creation, 7-372
logging insert operations, 7-141
migrated and chained rows in, 7-192
moving to a new segment, 7-133
nested
creating, 7-418
storage characteristics, 7-377
object
creating, 7-359
order of row storage, 7-373
ordering rows from, 7-385
parallel creation of, 7-381
parallelism
setting default degree, 7-381
partition attributes of, 7-144
partitioned
allowing rows to move between partitions, 7-152
default attributes of, 7-144
partitioning of, 2-65, 7-366
physical attributes
changing, 7-134
specifying, 7-370
relational
creating, 7-359
remote, accessing, 7-255
removing from the database, 7-475
removing rows from, 7-439
renaming, 7-142, 7-527
restricting records per block, 7-142
restricting references to, 7-225
retrieving data from, 7-544
rows
ordering by primary key, 7-373
saving blocks in a cache, 7-141, 7-384
SQL examples, 7-386
storage characteristics, 7-575
defining, 7-366, 7-371
subpartition attributes of, 7-144
synonyms for, 7-356
tablespace for
defining, 7-366, 7-371
temporary
duration of data, 7-378
session-specific, 7-368
transaction specific, 7-368
unclustering, 7-446
validating structure of, 7-191
with unusable indexes, 7-88
TABLESPACE clause
of CREATE CLUSTER, 7-239
of CREATE INDEX, 7-282
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-303
of CREATE ROLLBACK SEGMENTS, 7-346
of CREATE TABLE, 7-371
tablespaces, 7-168
allocating space for users, 7-427
allowing write operations on, 7-169
backing up datafiles of, 7-168
bringing online, 7-168, 7-397
coalescing free extents, 7-169
converting
from permanent to temporary, 7-169
from temporary to permanent, 7-169
creating, 7-395
datafile
adding, 7-167
renaming, 7-167
default storage characteristics, 7-575
defining as read only, 7-169
designing media recovery, 7-12
dropping the contents of, 7-477
enable autoextension of, 7-167
extent management of, 7-400
extent size of, 7-396
granting
system privileges on, 7-500
locally managed, 7-397, 7-576
temporary, 7-400
logging attribute of, 7-166, 7-396
managed using dictionary tables, 7-397
managing extents of, 7-397
of session duration, 7-399
permanent objects in, 7-397
recovering, 7-13
removing from the database, 7-477
size of free extents in, 7-168
specifying datafiles for, 7-396
specifying for a user, 7-427
specifying for index rebuild, 7-134
taking offline, 7-168, 7-397
tempfile
adding, 7-167
temporary
specifying for a user, 7-427
temporary objects in, 7-397
temporary, creating, 7-399
TAN function, 4-42
TANH function, 4-42
TEMPFILE clause
of ALTER DATABASE, 7-8, 7-17
of CREATE TEMPORARY TABLESPACE, 7-400
TEMPFILE clauses
of ALTER DATABASERENAME FILE clause
OF ALTER DATABASE, 7-8
tempfiles
automatic extension of, 7-400
bringing online, 7-17
disabling automatic extension, 7-17
dropping, 7-17
enabling automatic extension, 7-17
modifying, 7-16
resizing, 7-17
reusing, 7-491
size of, 7-491
specifying, 7-400, 7-490
taking offline, 7-17
TEMPORARAY clause
of ALTER TABLESPACE, 7-169
TEMPORARY clause
of CREATE TABLESPACE, 7-397
temporary tables
creating, 7-366, 7-368
session-specific, 7-368
transaction-specific, 7-368
TEMPORARY TABLESPACE clause
of ALTER USER. See CREATE USER.
of CREATE USER, 7-427
temporary tablespaces
creating, 7-399
specifying for a user, 7-427
SQL examples, 7-400
text
conventions, xv
date and number formats, 2-33
in SQL syntax, 2-2
properties of CHAR and VARCHAR2 datatypes, 2-2
syntax of, 2-2
text date format element, 2-41
text in, 2-40
TH date format element suffix, 2-45
THSP date format element suffix, 2-45
TIME datatype (SQL/DS or DB2), 2-25
TIMED_OS_STATISTICS parameter
of ALTER SYSTEM, 7-108
TIMED_STATISTICS parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-108
TIMESTAMP datatype (SQL/DS or DB2), 2-25
TM number format element, 2-36
TO PUBLIC clause
of GRANT object_privileges, 7-507
of GRANT system_privileges_and_roles, 7-494
TO role clause
of GRANT object_privileges, 7-507
of GRANT system_privileges_and_roles, 7-494
TO SAVEPOINT clause
of ROLLBACK, 7-537
TO user clause
of GRANT object_privileges, 7-507
of GRANT system_privileges_and_roles, 7-494
TO_CHAR
date conversion function, 4-43
number conversion function, 4-43
TO_CHAR function, 2-32, 2-35, 2-40, 2-46
TO_DATE function, 2-32, 2-40, 2-44, 2-46, 4-45
TO_LOB function, 2-32, 4-45
TO_MULTI_BYTE function, 4-46
TO_NUMBER function, 2-32, 2-35, 4-46
TO_SINGLE_BYTE function, 4-47
top-N queries, 2-56
transaction control statements, 6-4
PL/SQL support of, 6-4
TRANSACTION_AUDITING parameter
of ALTER SYSTEM, 7-108
transactions
allowing to complete, 7-99
assigning rollback segment to, 7-572
assigning to a specific rollback segment, 7-573
automatically committing, 7-214
commenting on, 7-215
distributed, forcing, 7-79
ending, 7-214
establish as read-only, 7-572
establish as read-write, 7-572
implicit commit of, 6-2, 6-4, 6-5
in-doubt, committing, 7-214
in-doubt, forcing, 7-215
isolation level, 7-572
locks, releasing, 7-214
rolling back, 7-100, 7-346, 7-537
to a savepoint, 7-537
savepoints for, 7-539
setting read-committed isolation mode, 7-573
setting serializable isolation mode, 7-573
TRANSLATE ... USING function, 4-48
TRANSLATE function, 4-47
triggers
AFTER, 7-403
BEFORE, 7-403
compiling, 7-171
creating, 7-402
creating multiple, 7-404
database
altering, 7-171
dropping, 7-479, 7-483
disabling, 7-153, 7-171
enabling, 7-153, 7-171, 7-402
executing with a PL/SQL block, 7-407
executing with an external procedure, 7-407
granting
system privileges on, 7-500
INSTEAD OF, 7-404
dropping, 7-432
on database events, 7-405
on DDL events, 7-405
on DML operations, 7-404
on views, 7-404
order of firing, 7-404
re-creating, 7-403
removing from the database, 7-479
restrictions on, 7-407
row values
old and new, 7-406
row, specifying, 7-406
SQL examples, 7-407
statement, 7-406
TRIM function, 4-49
TRUNC
date function, 4-51
number function, 4-50
TRUNC function
format models, 4-55
TRUNCATE PARTITION clause
of ALTER TABLE, 7-149
TRUNCATE statement, 7-581
TRUNCATE SUBPARTITION clause
of ALTER TABLE, 7-149
TRUST parameter
of PRAGMA RESTRICT_REFERENCES, 7-175
Trusted Oracle, 1-5
type constructor expressions, 5-6
types
granting
system privileges on, 7-501
incomplete
creating, 7-411
nested table
creating, 7-413
object
creating, 7-411
varray
creating, 7-413
TYPES clause
of ASSOCIATE STATISTICS, 7-194, 7-196
types. See object types or datatypes.

U

U number format element, 2-36
UID function, 4-51
unary operators, 3-1
UNION ALL operator, 3-12
UNION ALL set operator, 7-550
UNION operator, 3-12
UNION set operator, 7-550
UNIQUE clause
of constraint_clause, 7-221
of CREATE INDEX, 7-278
of CREATE TABLE, 7-370
of SELECT, 7-545
unique constraints
enabling, 7-383
index on, 7-383
unique indexes, 7-278
unique queries, 7-545
universal rowids. See urowids
universal rowids. See urowids.
UNLIMITED TABLESPACE system privilege, 7-500
unnesting collections, 7-547
examples, 7-564
UNRECOVERABLE, 7-36, 7-372
See also NOLOGGING clause.
unsorted indexes, 7-282
UNUSABLE clause
of ALTER INDEX, 7-39
UNUSABLE LOCAL INDEXES clause
of ALTER MATERIALIZED VIEW, 7-49
of ALTER TABLE, 7-145
UPDATE
object privilege, 7-508
statement, 7-584
UPDATE ANY TABLE system privilege, 7-500
UPDATE statement
triggers on, 7-404
UPPER function, 4-51
UROWID datatype, 2-23
urowids
and foreign tables, 2-23
and heap-organized tables, 2-23
and index-organized tables, 2-23
description of, 2-23
USE ROLLBACK SEGMENT clause
of SET TRANSACTION, 7-573
USE_CONCAT hint, 2-59
USE_HASH hint, 2-61
USE_MERGE hint, 2-61
USE_NL hint, 2-61
USE_STORED_OUTLINES parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-108
USER function, 4-52
USER_COL_COMMENTS view, 7-212
USER_DUMP_DEST parameter
of ALTER SYSTEM, 7-109
USER_TAB_COMMENTS view, 7-212
user-defined functions, 4-56
expressions, 5-5
name precedence of, 4-57
naming conventions, 4-58
restrictions on, 7-268
user-defined object types
defining, 7-414
user-defined operators, 3-16
user-defined statistics
dropping, 7-452, 7-454, 7-456, 7-466, 7-475, 7-480
user-defined types
categories of, 2-25
USERENV function, 4-52
users
allocating space for, 7-427
assigning default roles, 7-181
assigning profiles to, 7-427
assigning roles to, 7-427
authenticating to a remote server, 7-257
changing global authentication, 7-181
creating, 7-426
default tablespaces of, 7-427
denying access to tables and views, 7-520
external, 7-345, 7-427
global, 7-345, 7-427
granting
system privileges on, 7-501
local, 7-345, 7-426
locking accounts of, 7-428
maximum concurrent, 7-103
password expiration of, 7-427
removing from the database, 7-483
SQL examples, 7-428
temporary tablespaces for, 7-427
USING BFILE clause
of CREATE JAVA, 7-296
USING BLOB clause
of CREATE JAVA, 7-296
USING clause
of ASSOCIATE STATISTICS, 7-195, 7-196
of CREATE DATABASE LINK, 7-257
of CREATE INDEXTYPE, 7-292
of CREATE OPERATOR, 7-320, 7-322
USING CLOB clause
of CREATE JAVA, 7-296
USING INDEX clause
of ALTER MATERIALIZED VIEW, 7-49
of constraint_clause, 7-227
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-305
of CREATE TABLE, 7-366, 7-383
USING ROLLBACK SEGMENT clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-50
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-307
UTLXPLAN.SQL script, 7-486

V

V number format element, 2-36
V$NLS_PARAMETERS view
VALIDATE REF UPDATE clause
of ANALYZE, 7-190
VALIDATE STRUCTURE clause
of ANALYZE, 7-191
VALUE function, 4-53
VALUES clause
of CREATE INDEX, 7-284
of INSERT, 7-516
VALUES LESS THAN clause
of CREATE TABLE, 7-380
VARCHAR datatype, 2-13
DB2, 2-25
SQL/DS, 2-25
VARCHAR2 datatype, 2-12
converting to NUMBER, 2-35
VARGRAPHIC datatype (SQL/DS or DB2), 2-25
variable expressions, 5-4
VARIANCE function, 4-54
varray
changing returned value, 7-135
VARRAY storage clause
of ALTER TABLE, 7-135
of CREATE TABLE, 7-363, 7-376
varray types
creating, 7-413
varrays, 2-26
compared with nested tables, 2-31
comparison rules, 2-31
creating, 7-413, 7-417
dropping the body of, 7-482
dropping the specification of, 7-480
storage characteristics of, 7-135, 7-376
storing out of line, 2-27
varying arrays. See varrays.
views
adding rows to the base table of, 7-513
changing the definition of, 7-485
changing values in base tables of, 7-585
creating
multiple, 7-348
creating before base tables, 7-432
creating comments about, 7-212
defining, 7-430
granting
system privileges on, 7-502
recompiling, 7-183
re-creating, 7-432
remote, accessing, 7-255
removing from the database, 7-485
removing rows from the base table of, 7-439
renaming, 7-527
retrieving data from, 7-544
subquery of, 7-433
restricting, 7-434
synonyms for, 7-356
VSIZE function, 4-55

W

W date format element, 2-41
WHEN clause
of CREATE TRIGGER, 7-407
WHENEVER NOT SUCCESSFUL clause
of NOAUDIT schema_objects, 7-526
WHENEVER SUCCESSFUL clause
of AUDIT schema_objects, 7-206
of AUDIT sql_statements, 7-199
of NOAUDIT schema_objects, 7-526
of NOAUDIT sql_statements, 7-524
WHERE clause
of DELETE, 7-441
of SELECT, 5-19, 7-548
of UPDATE, 7-588
WITH ADMIN OPTION clause
of GRANT system_privileges_and_roles, 7-495
WITH CHECK OPTION clause
of CREATE VIEW, 7-430, 7-434
of DELETE, 7-441
of INSERT, 7-515
of SELECT, 7-542, 7-547
of UPDATE, 7-587
WITH GRANT OPTION clause
of GRANT object_privileges, 7-507
WITH INDEX CONTEXT clause
of CREATE OPERATOR, 7-320, 7-322
WITH OBJECT IDENTIFIER clause
of CREATE VIEW, 7-433
WITH OBJECT OID. See WITH OBJECT IDENTIFIER.
WITH PRIMARY KEY clause
of ALTER MATERIALIZED VIEW, 7-50
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-305
WITH READ ONLY clause
of CREATE VIEW, 7-430, 7-434
of DELETE, 7-441
of INSERT, 7-515
of SELECT, 7-542, 7-547
of UPDATE, 7-587
WITH ROWID clause
of column ref constraints, 7-225
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-317
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-305
WNDS parameter
of PRAGMA RESTRICT_REFERENCES, 7-175
WNPS parameter
of PRAGMA RESTRICT_REFERENCES, 7-175
WW date format element, 2-41

X

X number format element, 2-36

Y

Y date format element, 2-41
YEAR date format element, 2-41
YY date format element, 2-41
YYY date format element, 2-41
YYYY date format element, 2-41


Prev
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents