Oracle8
i
SQL Reference
Release 2 (8.1.6)
A76989-01
Library
Product
Contents
Index
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Symbols
$ number format element, 2-41
(+) operator, 3-16
, (comma)
date format element, 2-46
number format element, 2-41
: (colon) date format element, 2-46
- (dash) date format element, 2-46
; (semicolon) date format element, 2-46
/ (slash) date format element, 2-46
·
(period)
date format element, 2-46
number format element, 2-41
Numerics
0 number format element, 2-41
20th century, 2-47,
2-49
specifying, 2-49
21st century, 2-47,
2-49
specifying, 2-49
8 number format element, 2-41
9 number format element, 2-41
A
ABS function, 4-12
ABSI
standards, B - 1
ACCOUNT LOCK clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
ACCOUNT UNLOCK clause
of ALTER USER.
See
CREATE USER
of ALTER USER.
See
CREATE USER.
of CREATE USER, 7-453
ACOS function, 4-13
ACTIVATE STANDBY DATABASE clause
of ALTER DATABASE, 7-11
AD (A.D.) date format element, 2-46,
2-48
ADD clause
of ALTER DIMENSION, 7-27
of ALTER TABLE, 7-140
ADD DATAFILE clause
of ALTER TABLESPACE, 7-179
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-155
ADD PARTITION, 7-160
ADD PARTITION clause
of ALTER TABLE, 7-158,
7-160
ADD PRIMARY KEY clause
of ALTER MATERIALIZED VIEW LOG, 7-61
ADD ROWID clause
of ALTER MATERIALIZED VIEW, 7-61
of ALTER MATERIALIZED VIEW LOG, 7-61
ADD TEMPFILE clause
of ALTER TABLESPACE, 7-179
ADD_MONTHS function, 4-13
ADMINISTER ANY TRIGGER system privilege, 7-527
ADVISE clause
of ALTER SESSION, 7-84
AFTER clause
of CREATE TRIGGER, 7-428
AFTER triggers, 7-428
aggregate functions, 4-6
aliases
for columns, 5-19
for expressions in view query, 7-458
specifying in queries and subqueries, 7-576
ALL clause
of SELECT, 7-573
of SET CONSTRAINTS, 7-598
of SET ROLE, 7-601
ALL EXCEPT clause
of SET ROLE, 7-601
ALL operator, 3-6
ALL PRIVILEGES clause
of GRANT object_privileges, 7-533
of REVOKE schema_object_privileges, 7-560
ALL PRIVILEGES shortcut
of AUDIT sql_statements, 7-214
ALL shortcut
of AUDIT sql_statements, 7-214
ALL_COL_COMMENTS view, 7-228
ALL_ROWS hint, 2-64
ALL_TAB_COMMENTS view, 7-228
ALLOCATE EXTENT clause
of ALTER CLUSTER, 7-3,
7-4
of ALTER INDEX, 7-31,
7-35
of ALTER TABLE, 7-150
ALTER ANY CLUSTER system privilege, 7-521
ALTER ANY DIMENSION system privilege, 7-522
ALTER ANY INDEX system privilege, 7-522
ALTER ANY INDEXTYPE system privilege, 7-522
ALTER ANY MATERIALIZED VIEW system privilege, 7-523
ALTER ANY OUTLINE system privilege, 7-524
ALTER ANY PROCEDURE system privilege, 7-524
ALTER ANY ROLE system privilege, 7-525
ALTER ANY SEQUENCE system privilege, 7-525
ALTER ANY SNAPSHOT system privilege, 7-526
ALTER ANY TABLE system privilege, 7-526
ALTER ANY TRIGGER system privilege, 7-527
ALTER ANY TYPE system privilege, 7-528
ALTER CLUSTER statement, 7-2
ALTER DATABASE
statement, 7-6
system privilege, 7-521
ALTER DIMENSION statement, 7-25
ALTER FUNCTION statement, 7-28
ALTER INDEX statement, 7-30
ALTER JAVA CLASS statement, 7-45
ALTER JAVA SOURCE statement, 7-45
ALTER MATERIALIZED VIEW LOG statement, 7-58
ALTER MATERIALIZED VIEW statement, 7-47
ALTER object privilege, 7-535
ALTER OUTLINE statement, 7-63
ALTER PACKAGE statement, 7-64
ALTER PROCEDURE statement, 7-67
ALTER PROFILE
statement, 7-69
system privilege, 7-525
ALTER RESOURCE COST
statement, 7-73
system privilege, 7-525
ALTER ROLE statement, 7-76
ALTER ROLLBACK SEGMENT
statement, 7-78
system privilege, 7-525
ALTER SEQUENCE statement, 7-81
ALTER SESSION
statement, 7-83
system privilege, 7-525
ALTER SNAPSHOT LOG.
See
ALTER MATERIALIZED VIEW LOG
ALTER SNAPSHOT.
See
ALTER MATERIALIZED VIEW
ALTER statement
triggers on, 7-430
ALTER SYSTEM
statement, 7-102
system privilege, 7-521
ALTER TABLE statement, 7-123
ALTER TABLESPACE
statement, 7-176
system privilege, 7-527
ALTER TRIGGER statement, 7-183
ALTER TYPE statement, 7-186
ALTER USER
statement, 7-193
system privilege, 7-528
ALTER VIEW statement, 7-198
AM (A.M.) date format element, 2-46,
2-48
American National Standards Institute.
See
ANSI
analytic functions, 4-7
CUME_DIST, 4-28
FIRST_VALUE, 4-34
LAG, 4-40
LAST_VALUE, 4-42
LEAD, 4-44
NTILE, 4-61
PERCENT_RANK, 4-66
RANK, 4-67
RATIO_TO_REPORT, 4-68
ROW_NUMBER, 4-80
ANALYZE ANY system privilege, 7-529
ANALYZE CLUSTER statement, 7-200
ANALYZE INDEX statement, 7-200
ANALYZE TABLE statement, 7-200
ANCILLARY TO clause
of CREATE OPERATOR, 7-341
AND operator, 3-11,
3-12
AND_EQUAL hint, 2-65
ANSI, B - 1
datatypes, 2-28
conversion to Oracle datatypes, 2-28
standards, xv,
1-2
supported datatypes, 2-12
ANY operator, 3-6
APPEND hint, 2-67
application servers
allowing to connect as a user, 7-195
applications
allowing to connect as a user, 7-195
securing, 7-261
validating, 7-261
AQ_ADMINISTRATOR_ROLE role, 7-530
AQ_TM_PROCESSES parameter
of ALTER SYSTEM, 7-108
AQ_USER_ROLE role, 7-530
ARCHIVE LOG clause
of ALTER SYSTEM, 7-103
archived redo logs
location of, 7-13
storage locations, 7-89,
7-112
ARCHIVELOG clause
of ALTER DATABASE, 7-9,
7-18
of CREATE CONTROLFILE, 7-266
ARCHVIELOG clause
OF CREATE DATABASE, 7-270
arguments of operators, 3-1
arithmetic operators, 3-3
AS 'filespec' clause
of CREATE LIBRARY, 7-317
AS clause
of CREATE JAVA, 7-314
AS EXTERNAL clause
of CREATE FUNCTION, 7-289,
7-357
of CREATE TYPE BODY, 7-450
AS OBJECT clause
of CREATE TYPE, 7-440
AS subquery
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319,
7-328
of CREATE TABLE, 7-409
of CREATE VIEW, 7-459
AS TABLE clause
of CREATE TYPE, 7-444
AS VARRAY clause
of CREATE TYPE, 7-444
ASC clause
of CREATE INDEX, 7-299
ascending indexes, 7-299
ASCII
character set, 2-34
ASCII function, 4-14
ASIN function, 4-15
ASSOCIATE STATISTICS statement, 7-210
ATAN function, 4-15
ATAN2 function, 4-16
ATTRIBUTE clause
of ALTER DIMENSION, 7-26
of CREATE DIMENSION, 7-280
attributes
adding to a dimension, 7-27
dropping from a dimension, 7-27
maximum number of in object type, 7-392
of dimensions, defining, 7-280
AUDIT (Schema Objects) statement, 7-221
AUDIT ANY system privilege, 7-529
AUDIT sql_statements statement, 7-213
AUDIT SYSTEM system privilege, 7-521
auditing
options
for database objects, 7-215
for SQL statements, 7-218
schema objects
stopping, 7-552
SQL statements, 7-215
SQL statements, stopping, 7-550
AUTHENTICATED BY clause
of CREATE DATABASE LINK, 7-275
AUTHID CURRENT_USER clause
of ALTER JAVA, 7-46
of CREATE FUNCTION, 7-288
of CREATE JAVA, 7-313
of CREATE PACKAGE, 7-345
of CREATE PROCEDURE, 7-357
of CREATE TYPE, 7-188,
7-441
AUTHID DEFINER clause
of ALTER JAVA, 7-46
of CREATE FUNCTION, 7-288
of CREATE JAVA, 7-313
of CREATE PACKAGE, 7-345
of CREATE PROCEDURE, 7-357
of CREATE TYPE, 7-188,
7-441
AUTOEXTEND clause
for datafiles, 7-17
of ALTER DATABASE, 7-10
of ALTER TABLESPACE, 7-177,
7-179
of CREATE DATABASE, 7-268
of CREATE TABLESPACE, 7-419,
7-421
of CREATE TEMPORARY TABLESPACE, 7-424,
7-425
AVG function, 4-16
AY date format element, 2-46
B
BACKGROUND_DUMP_DEST parameter
of ALTER SYSTEM, 7-108
BACKUP ANY TABLE system privilege, 7-526
BACKUP CONTROLFILE clause
of ALTER DATABASE, 7-9,
7-20
BACKUP_TAPE_IO_SLAVES parameter
of ALTER SYSTEM, 7-108
BC (B.C.) date format element, 2-46,
2-48
BECOME USER system privilege, 7-528
BEFORE clause
of CREATE TRIGGER, 7-428
BEFORE triggers, 7-428
BEGIN BACKUP clause
of ALTER TABLESPACE, 7-180
BFILE
datatype, 2-25
locators, 2-25
BFILENAME function, 4-17
binary large objects.
See
BLOBs
binary operators, 3-1
BINDING clause
of CREATE OPERATOR, 7-339,
7-340
BITMAP clause
of CREATE INDEX, 7-297
bitmap indexes, 7-297
blank padding
specifying in format models, 2-51
suppressing, 2-51
BLOB datatype, 2-25
transactional support of, 2-25
BODY clause
of ALTER PACKAGE, 7-65
BUFFER_POOL parameter
of STORAGE clause, 7-609
BUILD DEFERRED clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
BUILD IMMEDIATE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
BY ACCESS clause
of AUDIT schema_objects, 7-222
of AUDIT sql_statements, 7-215
BY proxy clause
of AUDIT (SQL statements), 7-214
of NOAUDIT sql_statements, 7-551
BY SESSION clause
of AUDIT schema_objects, 7-222
of AUDIT sql_statements, 7-214
BY user clause
of AUDIT sql_statements, 7-214
of NOAUDIT sql_statements, 7-551
C
C clause
of CREATE TYPE, 7-442
of CREATE TYPE BODY, 7-450
C method
mapping to an object type, 7-442
C number format element, 2-41
CACHE clause
of ALTER MATERIALIZED VIEW, 7-51
of ALTER MATERIALIZED VIEW LOG, 7-61
of ALTER SEQUENCE.
See
CREATE SEQUENCE, 7-81
of ALTER TABLE, 7-151
of CREATE CLUSTER, 7-258
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
of CREATE SEQUENCE, 7-374
of CREATE TABLE, 7-408
CACHE hint, 2-67
CALL clause
of CREATE TRIGGER, 7-433
CALL procedure statement
of CREATE TRIGGER, 7-433
call spec
in procedures, 7-354
of CREATE FUNCTION, 7-289
of CREATE PROCEDURE, 7-357
of CREATE TYPE, 7-442
of CREATE TYPE BODY, 7-450
call specifications.
See
call spec
CALL statement, 7-226
Cartesian products, 5-23
CASCADE clause
of CREATE TABLE, 7-408
of DROP PROFILE, 7-494
of DROP USER, 7-510
CASCADE CONSTRAINTS clause
of DROP CLUSTER, 7-473
of DROP TABLE, 7-502
of DROP TABLESPACE, 7-504
of REVOKE schema_object_privileges, 7-561
CASE expressions, 5-13
CAST expressions, 5-7
CC date format element, 2-46
CEIL function, 4-18
century
specifying, 2-47
chained rows
listing, 7-207
CHANGE CATEGORY clause
of ALTER OUTLINE, 7-63
changes
making permanent, 7-230
changing default storage parameters, 7-180
CHAR datatype, 2-15
ANSI, 2-28
converting to VARCHAR2, 2-40
CHAR VARYING datatype, ANSI, 2-28
CHARACTER datatype
ANSI, 2-28
DB2, 2-29
SQL/DS, 2-29
character functions, 4-4,
4-5
character large objects.
See
CLOB datatype
character literal.
See
text
CHARACTER SET clause
of CREATE CONTROLFILE, 7-266
OF CREATE DATABASE, 7-271
CHARACTER SET parameter
of ALTER DATABASE, 7-16
character sets
common, 2-33
multibyte characters, 2-72
specifying for database, 7-271
character strings
comparison rules, 2-32
exact matching of, 2-51
fixed-length, 2-15
national character set, 2-15
variable length, 2-16
variable-length, 2-19
zero-length, 2-15
CHARACTER VARYING datatype
ANSI, 2-28
characters
single, comparison rules, 2-33
CHARTOROWID function, 2-37,
4-19
CHECK clause
of constraint_clause, 7-241
of CREATE TABLE, 7-393
check constraints, 7-241
CHECK DATAFILES clause
of ALTER SYSTEM, 7-105
checkpoint
forcing, 7-105
CHECKPOINT clause
of ALTER SYSTEM, 7-105
CHOOSE hint, 2-64
CHR function, 4-19
CHUNK clause
of ALTER TABLE, 7-141
of CREATE TABLE, 7-399
CLEAR LOGFILE clause
of ALTER DATABASE, 7-9,
7-19
CLOB datatype, 2-25
transactional support of, 2-25
clone database
mounting, 7-11
CLOSE DATABASE LINK clause
of ALTER SESSION, 7-84
CLUSTER clause
of CREATE INDEX, 7-297
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-322
of CREATE TABLE, 7-401
of TRUNCATE, 7-613
CLUSTER hint, 2-65
clusters
allocating extents for, 7-3
assigning tables to, 7-401
caching retrieved blocks, 7-258
cluster indexes on, 7-297
collecting statistics on, 7-204
creating, 7-254,
7-255
data blocks allocated to, 7-256
deallocating unused extents, 7-3
degree of parallelism
changing, 7-3
when creating, 7-258
dropping tables of, 7-473
granting
system privileges on, 7-521
hash, 7-257
single-table, 7-257
indexed, 7-257
key
changing column names, 7-3
changing the number of columns, 7-3
migrated and chained rows in, 7-207
modifying, 7-2
physical attributes
changing, 7-2
specifying, 7-256
removing from the database, 7-472
space allocated for cluster key values, 7-256
SQL examples, 7-473
storage attributes
changing, 7-2
storage characteristics, 7-605
specifying, 7-256
tablespace
changing, 7-3
tablespace in which created, 7-257
validating structure of, 7-206
COALESCE clause
for partitions, 7-160
for subpartitions, 7-156
of ALTER INDEX, 7-40
of ALTER TABLESPACE, 7-181
COALESCE SUBPARTITION clause
of ALTER TABLE, 7-156
code examples
description of, xxiii
collections
inserting rows into, 7-542
modifying, 7-145
nested tables, 2-31
treating as a table, 7-467,
7-542,
7-617
unnesting, 7-576
examples, 7-593
varrays, 2-31
column constraints, 7-233,
7-237
of ALTER TABLE, 7-141
of CREATE TABLE, 7-393
column REF constraints, 7-234,
7-242
of ALTER TABLE, 7-140
of CREATE TABLE, 7-393
columns
adding, 7-140
aliases for, 5-19
associating statistics with, 7-211
basing an index on, 7-298
collecting statistics on, 7-204
creating comments about, 7-228
defining, 7-390
LOB, storage characteristics of, 7-141
maximum number of, 7-392
modifying existing, 7-143
parent-child relationships between, 7-278
prohibiting nulls in, 7-239
qualifying names of, 5-19
REF
describing, 7-242
restricting values for, 7-237
specifying as foreign key, 7-240
specifying as primary key, 7-239
specifying constraints on, 7-393
specifying default values for, 7-392
unique values in, 7-238
COLUMNS clause
of ASSOCIATE STATISTICS, 7-210,
7-211
COMMENT ANY TABLE system privilege, 7-529
COMMENT clause
of COMMIT, 7-231
COMMENT statement, 7-228
comments, 2-62
adding to objects, 7-228
associating with a transaction, 7-231
dropping from objects, 7-228
how to specify, 2-62
in SQL statements, 2-62
on schema objects, 2-63
removing from the data dictionary, 7-228
viewing, 7-228
commit
automatic, 7-230
COMMIT IN PROCEDURE clause
of ALTER SESSION, 7-84
COMMIT statement, 7-230
comparison functions
MAP, 7-443,
7-449
ORDER, 7-443,
7-449
comparison operators, 3-5
comparison semantics
blank-padded, 2-33
nonpadded, 2-33
of character strings, 2-32
COMPILE clause
of ALTER DIMENSION, 7-27
of ALTER FUNCTION, 7-28
of ALTER JAVA SOURCE, 7-46
of ALTER MATERIALIZED VIEW, 7-54
of ALTER PACKAGE, 7-65
of ALTER PROCEDURE, 7-68
of ALTER TRIGGER, 7-184
of ALTER TYPE, 7-187
of ALTER VIEW, 7-199
of CREATE JAVA, 7-312
compiler directives, 7-442
composite foreign keys, 7-239
composite partitioning clause
of CREATE TABLE, 7-386,
7-403
composite primary keys, 7-239
composite unique constraints, 7-238
COMPOSITE_LIMIT parameter
of ALTER PROFILE, 7-69
of CREATE PROFILE, 7-362
compound conditions, 5-18
compound expressions, 5-4
COMPRESS clause
of ALTER INDEX, 7-32
of ALTER TABLE, 7-144
of CREATE INDEX, 7-300
of CREATE TABLE, 7-397
COMPUTE STATISTICS clause
of ANALYZE, 7-204
of CREATE INDEX, 7-301
CONCAT function, 4-20
concatenation operator, 3-3
conditions
compound, 5-18
EXISTS, 5-18
group comparison, 5-16
in SQL syntax, 5-14
LIKE, 5-18
membership, 5-17
NULL, 5-18
range, 5-18
simple comparison, 5-16
CONNECT BY clause
of SELECT, 5-21,
7-577
CONNECT clause
of SELECT and subqueries, 7-571
CONNECT role, 7-530
CONNECT TO clause
of CREATE DATABASE LINK, 7-274
CONNECT_TIME parameter
of ALTER PROFILE, 7-69
of ALTER RESOURCE COST, 7-74
of CREATE PROFILE, 7-361
CONSIDER FRESH clause
of ALTER MATERIALIZED VIEW, 7-55
constant values.
See
literals
CONSTRAINT clause
of constraint_clause, 7-237
constraint clause, 7-233
CONSTRAINT(S) parameter
of ALTER SESSION, 7-86
constraints
adding, 7-140
check, 7-241
checking at end of transaction, 7-243
checking at start of transaction, 7-243
checking at the end of each DML statement, 7-243
column REF, 7-242
composite unique, 7-238
deferrable, 7-243,
7-598
enforcing, 7-86
defining, 7-237,
7-390
on a column, 7-393
on a table, 7-393
disabling, 7-165,
7-245,
7-405
cascading, 7-408
dropping, 7-146,
7-504
enabling, 7-165,
7-244,
7-405,
7-407
foreign key, 7-240
modifying existing, 7-144
not null, 7-239
on columns, 7-237
primary key, 7-239
attributes of index, 7-244
enabling, 7-407
referential integrity, 7-239,
7-240
restrictions, 7-237
scope, 7-242
setting state for a transaction, 7-598
storing rows in violation, 7-163,
7-245,
7-407
table REF, 7-242
unique, 7-238
attributes of index, 7-244
composite, 7-238
enabling, 7-407
validating, 7-244,
7-245
constructor methods
and object types, 7-439
context namespaces
removing from the database, 7-474
contexts
creating namespaces for, 7-261
granting
system privileges on, 7-521
namespace
associating with package, 7-261
control files
allow reuse of, 7-264
allowing reuse of, 7-269
backing up, 7-20
re-creating, 7-263
CONTROL_FILE_RECORD_KEEP_TIME parameter
of ALTER SYSTEM, 7-108
controlfile clauses
of ALTER DATABASE, 7-9
CONTROLFILE REUSE clause
of CREATE DATABASE, 7-269
conversion
rules, string to date, 2-53
conversion functions, 4-5
table of, 2-37
CONVERT clause
of ALTER DATABASE, 7-11
CONVERT function, 4-21
CORE_DUMP_DEST parameter
of ALTER SYSTEM, 7-108
CORR function, 4-22
correlated subqueries, 5-25
correlation names
for base tables of indexes, 7-298
in DELETE, 7-467
in SELECT, 7-576
COS function, 4-23
COSH function, 4-24
COUNT function, 4-24
CPU_PER_CALL parameter
of ALTER PROFILE, 7-69
of CREATE PROFILE, 7-361
CPU_PER_SESSION parameter
of ALTER PROFILE, 7-69
of ALTER RESOURCE COST, 7-74
of CREATE PROFILE, 7-361
CREATE ANY CLUSTER system privilege, 7-521
CREATE ANY CONTEXT system privilege, 7-521
CREATE ANY DIMENSION system privilege, 7-522
CREATE ANY DIRECTORY system privilege, 7-522
CREATE ANY INDEX system privilege, 7-522
CREATE ANY INDEXTYPE system privilege, 7-522
CREATE ANY LIBRARY system privilege, 7-523
CREATE ANY MATERIALIZED VIEW system privilege, 7-523
CREATE ANY OPERATOR system privilege, 7-524
CREATE ANY OUTLINE system privilege, 7-524
CREATE ANY PROCEDURE system privilege, 7-524
CREATE ANY SEQUENCE system privilege, 7-525
CREATE ANY SNAPSHOT system privilege, 7-526
CREATE ANY SYNONYM system privilege, 7-526
CREATE ANY TABLE system privilege, 7-526
CREATE ANY TRIGGER system privilege, 7-527
CREATE ANY TYPE system privilege, 7-528
CREATE ANY VIEW system privilege, 7-529
CREATE CLUSTER
statement, 7-254
system privilege, 7-521
CREATE CONTEXT statement, 7-261
CREATE CONTROLFILE statement, 7-263
CREATE DATABASE LINK
statement, 7-273
system privilege, 7-522
CREATE DATABASE statement, 7-267
CREATE DATAFILE clause
of ALTER DATABASE, 7-8,
7-16
CREATE DIMENSION
statement, 7-277
system privilege, 7-522
CREATE DIRECTORY statement, 7-282
CREATE FUNCTION statement, 7-284
CREATE INDEX
statement, 7-291
CREATE INDEXTYPE
statement, 7-309
system privilege, 7-522
CREATE JAVA statement, 7-311
CREATE LIBRARY
statement, 7-316
system privilege, 7-523
CREATE MATERIALIZED VIEW / SNAPSHOT
statement, 7-318
CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG statement, 7-333
CREATE MATERIALIZED VIEW/SNAPSHOT
system privilege, 7-523
CREATE OPERATOR
statement, 7-339
system privilege, 7-524
CREATE OUTLINE statement, 7-342
CREATE PACKAGE BODY statement, 7-348
CREATE PACKAGE statement, 7-344
CREATE PROCEDURE
statement, 7-353
system privilege, 7-524
CREATE PROFILE
statement, 7-359
system privilege, 7-524
CREATE PUBLIC DATABASE LINK system privilege, 7-522
CREATE PUBLIC SYNONYM system privilege, 7-526
CREATE ROLE
statement, 7-365
system privilege, 7-525
CREATE ROLLBACK SEGMENT
statement, 7-367
system privilege, 7-525
CREATE SCHEMA statement, 7-369
CREATE SEQUENCE
statement, 7-371
system privilege, 7-525
CREATE SESSION system privilege, 7-525
CREATE SNAPSHOT system privilege, 7-526
CREATE STANDBY CONTROLFILE clause
of ALTER DATABASE, 7-9,
7-20
CREATE statement
triggers on, 7-430
CREATE SYNONYM
statement, 7-377
system privilege, 7-526
CREATE TABLE statement, 7-381
CREATE TABLESPACE
statement, 7-419
system privilege, 7-527
CREATE TEMPORARY TABLESPACE statement, 7-424
CREATE TRIGGER
statement, 7-426
system privilege, 7-527
CREATE TYPE
statement, 7-437
system privilege, 7-528
CREATE TYPE BODY statement, 7-447
CREATE USER
statement, 7-451
system privilege, 7-528
CREATE VIEW
statement, 7-456
system privilege, 7-529
CREATE_STORED_OUTLINES parameter
of ALTER SESSION, 7-86
of ALTER SYSTEM, 7-109
cross-tabulation values
deriving, 7-578
CUBE operations
of queries and subqueries, 7-578
CUME_DIST function, 4-28
currency symbol
ISO, 2-41
local, 2-42
union, 2-43
CURRENT_SCHEMA parameter
of ALTER SESSION, 7-87
CURRENT_USER
and database links, 7-274
CURRVAL pseudocolumn, 2-56,
7-372
CURSOR expressions, 5-10
CURSOR_SHARING parameter
of ALTER SESSION, 7-87,
7-109
cursors
number cached per session, 7-94
CYCLE clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE, 7-81
of CREATE SEQUENCE, 7-373
D
D date format element, 2-46
D number format element, 2-41
data
integrity checking on input, 2-18
retrieving, 5-19
undo
storing, 7-367
data conversion, 2-36
implicit versus explicit, 2-38
when performed implicitly, 2-36
when specified explicitly, 2-37
data definition language
events and triggers, 7-430
statements, 6-1
and implicit commit, 6-2
causing recompilation, 6-2
PL/SQL support of, 6-2
data dictionary
adding comments to, 7-228
data manipulation language
operations
and triggers, 7-429
during index creation, 7-301
during index rebuild, 7-144
statements, 6-4
PL/SQL support of, 6-4
data object number
in extended rowids, 2-27
database
accounts
creating, 7-451
allowing generation of redo logs, 7-12
allowing reuse of control files, 7-269
allowing unlimited resources to users, 7-361
cancel-based recovery, 7-13
terminating, 7-14
change-based recovery, 7-14
changing characteristics of, 7-263
changing global name, 7-15
changing the name of, 7-263,
7-264
character set
specifying, 7-271
connect strings, 2-80
converting from Oracle7 data dictionary, 7-11
creating, 7-268
designing media recovery, 7-12
enabling automatic extension of, 7-271
erasing all data from, 7-268
granting system privileges on, 7-521
limiting resources for users, 7-360
managed recovery of, 7-8
modifying, 7-10
mounting, 7-11,
7-268
naming, 7-11
opening, 7-11,
7-268
after media recovery, 7-12
recovering, 7-13
with backup control file, 7-14
re-creating control file for, 7-263
redo log files
specifying, 7-265
remote
accessing, 5-26
authenticating users to, 7-275
connecting to, 7-274
inserting into, 7-542
service name of, 7-275
table locks on, 7-548
resetting
current log sequence, 7-12
to an earlier version, 7-15
restricting users to read-only transactions, 7-12
resuming activity, 7-108
specifying datafiles for, 7-265
suspending activity, 7-107
time-based recovery, 7-13
database events
and triggers, 7-431
database links, 5-26
closing, 7-84
creating, 2-79,
7-273
creating synonyms with, 7-379
current user, 7-274
granting system privileges on, 7-521
naming, 2-79
public, 7-274
dropping, 7-475
referring to, 2-81
removing from the database, 7-475
shared, 7-274
syntax of, 2-80
username and password, 2-80
database objects
dropping, 7-510
nonschema, 2-69
schema, 2-68
database triggers.
See
triggers
DATAFILE clause
of ALTER DATABASE, 7-8,
7-16
of CREATE CONTROLFILE, 7-265
of CREATE DATABASE, 7-271
DATAFILE clauses
of ALTER DATABASE, 7-8
DATAFILE END BACKUP clause
of ALTER DATABASE, 7-17
DATAFILE OFFLINE clause
of ALTER DATABASE, 7-17
DATAFILE ONLINE clause
of ALTER DATABASE, 7-17
DATAFILE RESIZE clause
of ALTER DATABASE, 7-17
datafiles
bringing online, 7-17
creating new, 7-16
designing media recovery, 7-12
disabling automatic extension, 7-17
enabling automatic extension, 7-17,
7-421
modifying, 7-16
recovering, 7-14
re-creating lost, 7-16
renaming, 7-15
resizing, 7-17
reusing, 7-517
size of, 7-517
specifying, 7-516
for a tablespace, 7-421
taking offline, 7-17
datatypes, 2-9
ANSI-supported, 2-12
associating statistics with, 7-212
BFILE, 2-14,
2-25
BLOB, 2-14,
2-25
built-in, 2-13
syntax, 2-11
CHAR, 2-13,
2-15
character, 2-14
CLOB, 2-14,
2-25
comparison rules, 2-32
conversion
table of, 2-37
DATE, 2-13,
2-21
external, 2-10
LONG, 2-13,
2-19
LONG RAW, 2-13,
2-22
NCHAR, 2-14,
2-15
NCLOB, 2-14,
2-26
NUMBER, 2-17
NUMER, 2-13
NVARCHAR2, 2-13,
2-16
RAW, 2-13,
2-22
ROWID, 2-13,
2-26
UROWID, 2-13,
2-27
VARCHAR, 2-17
VARCHAR2, 2-13,
2-16
DATE datatype, 2-21
converting from character or numeric value, 2-21
date format elements, 2-45
and NLS, 2-48
capitalization, 2-45
ISO standard, 2-49
RR, 2-49
suffixes, 2-50
date format models, 2-45
punctuation in, 2-45
text in, 2-45
date functions, 4-5
dates
arithmetic using, 2-21
comparison rules, 2-32
Julian, 2-22
DAY date format element, 2-48
DB_BLOCK_CHECKING parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-109
DB_BLOCK_CHECKSUM parameter
of ALTER SYSTEM, 7-109
DB_BLOCK_MAX_DIRTY_TARGET parameter
of ALTER SYSTEM, 7-109
DB_FILE_DIRECT_IO_COUNT parameter
of ALTER SYSTEM, 7-110
DB_FILE_MULTIBLOCK_READ_COUNT parameter
of ALTER SESSION, 7-87
of ALTER SYSTEM, 7-110
DB2 datatypes, 2-28
conversion to Oracle datatypes, 2-29
restrictions on, 2-30
DBA role, 7-530
DBA_2PC_PENDING view, 7-84
DBA_COL_COMMENTS view, 7-228
DBA_ROLLBACK_SEGS view, 7-496
DBA_TAB_COMMENTS view, 7-228
DBMS_OUTPUT package, 7-184
DBMS_ROWID package
and extended rowids, 2-27
DBMSSTDX.SQL script, 7-285,
7-344,
7-348,
7-355
and triggers, 7-427
DD date format element, 2-46
DDD date format element, 2-46
DDL.
See
data definition language
DDL statements
requiring exclusive access, 6-2
DEALLOCATE UNUSED clause
of ALTER CLUSTER, 7-3,
7-5
of ALTER INDEX, 7-30
of ALTER TABLE, 7-150
DEBUG clause
of ALTER FUNCTION, 7-28
of ALTER PACKAGE, 7-65
of ALTER PROCEDURE, 7-68
of ALTER TRIGGER, 7-184
of ALTER TYPE, 7-188
decimal characters, 2-4
specifying, 2-42
DECIMAL datatype
ANSI, 2-28
DB2, 2-29
SQL/DS, 2-29
DECODE expressions, 5-12
DEFAULT clause
of CREATE TABLE, 7-392
DEFAULT COST clause
of ASSOCIATE STATISTICS, 7-211,
7-212
DEFAULT profile
assigning to users, 7-494
DEFAULT ROLE clause
of ALTER USER, 7-195
DEFAULT SELECTIVITY clause
of ASSOCIATE STATISTICS, 7-211,
7-212
DEFAULT storage clause
of ALTER TABLESPACE, 7-180
of CREATE TABLESPACE, 7-422
DEFAULT TABLESPACE clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
DEFERRABLE clause
of constraint_clause, 7-243
deferrable constraints, 7-598
DEFERRED clause
of SET CONSTRAINTS, 7-598
DELETE
object privilege, 7-535
statement, 7-464
DELETE ANY TABLE system privilege, 7-526
DELETE statement
triggers on, 7-429
DELETE STATISTICS clause
of ANALYZE, 7-206
DELETE_CATALOG_ROLE role, 7-530
DENSE_RANK function, 4-29
DEREF function, 4-30
DESC clause
of CREATE INDEX, 7-299
descending indexes, 7-299
DETERMINISTIC clause
of CREATE FUNCTION, 7-288
dimensions
attributes
adding, 7-27
changing, 7-26
defining, 7-280
dropping, 7-27
changing hierarchical relationships, 7-26
compiling invalidated, 7-27
creating, 7-278
examples, 7-280
granting
system privileges on, 7-522
hierarchies
adding, 7-27
defining, 7-279
dropping, 7-27
levels
adding, 7-27
defining, 7-279
dropping, 7-27
removing from the database, 7-476
directories.
See
directory objects
directory objects
as aliases for OS directories, 7-282
auditing, 7-222
creating, 7-282
granting system privileges on, 7-522
redefining, 7-283
removing from the database, 7-477
DISABLE [constraint] clause
of CREATE TABLE, 7-406
DISABLE ALL TRIGGERS clause
of ALTER TABLE, 7-165
DISABLE clause
of ALTER INDEX, 7-40
of ALTER TRIGGER, 7-184
of constraint_clause, 7-245
of CREATE TABLE, 7-405
DISABLE DISTRIBUTED RECOVERY clause
of ALTER SYSTEM, 7-106
DISABLE NOVALIDATE constraint state, 7-407
DISABLE PARALLEL DML clause
of ALTER SESSION, 7-84
DISABLE QUERY REWRITE clause
of ALTER MATERIALIZED VIEW, 7-54
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-327
DISABLE RESTRICTED SESSION clause
of ALTER SYSTEM, 7-106
DISABLE ROW MOVEMENT clause
of ALTER TABLE, 7-164
of CREATE TABLE, 7-383,
7-404
DISABLE STORAGE IN ROW clause
of ALTER TABLE, 7-141
of CREATE TABLE, 7-398
DISABLE TABLE LOCK clause
of ALTER TABLE, 7-165
DISABLE THREAD clause
of ALTER DATABASE, 7-16
DISABLE VALIDATE constraint state, 7-406
DISASSOCIATE STATISTICS statement, 7-470
DISCONNECT SESSION clause
of ALTER SYSTEM, 7-106
dispatcher processes
creating additional, 7-114
terminating, 7-114
DISTINCT clause
of SELECT, 7-573
distinct queries, 7-573
distributed queries, 5-26
restrictions on, 5-26
DML.
See
data manipulation language
domain indexes, 7-295,
7-304,
7-309
associating statistics with, 7-212
determining user-defined CPU and I/O costs, 7-512
invoking drop routines for, 7-501
removing from the database, 7-480
specifying alter string for, 7-40
DOUBLE PRECISION datatype
ANSI, 2-28
DRIVING_SITE hint, 2-66
DROP ANY CLUSTER system privilege, 7-521
DROP ANY CONTEXT system privilege, 7-521
DROP ANY DIMENSION system privilege, 7-522
DROP ANY DIRECTORY system privilege, 7-522
DROP ANY INDEX system privilege, 7-522
DROP ANY INDEXTYPE system privilege, 7-522
DROP ANY LIBRARY system privilege, 7-523
DROP ANY MATERIALIZED VIEW system privilege, 7-523
DROP ANY OPERATOR system privilege, 7-524
DROP ANY OUTLINE system privilege, 7-524
DROP ANY PROCEDURE system privilege, 7-524
DROP ANY ROLE system privilege, 7-525
DROP ANY SEQUENCE system privilege, 7-525
DROP ANY SNAPSHOT system privilege, 7-526
DROP ANY SYNONYM system privilege, 7-526
DROP ANY TABLE system privilege, 7-527
DROP ANY TRIGGER system privilege, 7-527
DROP ANY TYPE system privilege, 7-528
DROP ANY VIEW system privilege, 7-529
DROP clause
of ALTER DIMENSION, 7-27
DROP CLUSTER statement, 7-472
DROP COLUMN clause
of ALTER TABLE, 7-147
DROP CONSTRAINT clause
of ALTER TABLE, 7-146
DROP CONTEXT statement, 7-474
DROP DATABASE LINK statement, 7-475
DROP DIMENSION statement, 7-476
DROP DIRECTORY statement, 7-477
DROP FUNCTION statement, 7-478
DROP INDEX statement, 7-480
DROP INDEXTYPE statement, 7-482
DROP JAVA statement, 7-483
DROP LIBRARY
statement, 7-484
system privilege, 7-523
DROP LOGFILE clause
of ALTER DATABASE, 7-9,
7-19
DROP LOGFILE MEMBER clause
of ALTER DATABASE, 7-9,
7-19
DROP MATERIALIZED VIEW / SNAPSHOT statement, 7-485
DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG statement, 7-487
DROP OPERATOR statement, 7-489
DROP OUTLINE statement, 7-490
DROP PACKAGE BODY statement, 7-491
DROP PACKAGE statement, 7-491
DROP PARTITION clause
of ALTER INDEX, 7-41
of ALTER TABLE, 7-160
DROP PRIMARY constraint clause
of ALTER TABLE, 7-146
DROP PROCEDURE statement, 7-493
DROP PROFILE
statement, 7-494
system privilege, 7-525
DROP PUBLIC DATABASE LINK system privilege, 7-522
DROP PUBLIC SYNONYM system privilege, 7-526
DROP ROLE statement, 7-495
DROP ROLLBACK SEGMENT
statement, 7-496
system privilege, 7-525
DROP SEQUENCE statement, 7-497
DROP statement
triggers on, 7-430
DROP STORAGE clause
of TRUNCATE, 7-613
DROP SYNONYM statement, 7-500
DROP TABLE statement, 7-501
DROP TABLESPACE
statement, 7-503
system privilege, 7-527
DROP TRIGGER statement, 7-505
DROP TYPE BODY statement, 7-508
DROP TYPE statement, 7-506
DROP UNIQUE constraint clause
of ALTER TABLE, 7-146
DROP USER
statement, 7-509
system privilege, 7-528
DROP VIEW statement, 7-511
DUAL dummy table, 2-73,
5-26
DUMP function, 4-31
DY date format element, 2-46,
2-48
E
E date format element, 2-46
E number format element, 2-41
EBCDIC character set, 2-34
EE date format element, 2-46
embedded SQL, xv,
1-4,
6-5
precompiler support of, 6-5
EMPTY_BLOB function, 4-33
EMPTY_CLOB function, 4-33
ENABLE ALL TRIGGERS clause
of ALTER TABLE, 7-165
ENABLE clause
of ALTER INDEX, 7-40
of ALTER TRIGGER, 7-184
of constraint_clause, 7-244
of CREATE TABLE, 7-405
ENABLE DISTRIBUTED RECOVERY clause
of ALTER SYSTEM, 7-106
ENABLE NOVALIDATE constraint state, 7-406
ENABLE PARALLEL DML clause
of ALTER SESSION, 7-84
ENABLE QUERY REWRITE clause
of ALTER MATERIALIZED VIEW, 7-54
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-327
ENABLE RESTRICTED SESSION clause
of ALTER SYSTEM, 7-106
ENABLE ROW MOVEMENT clause
of ALTER TABLE, 7-164
of CREATE TABLE, 7-383,
7-404
ENABLE STORAGE IN ROW clause
of ALTER TABLE, 7-141
of CREATE TABLE, 7-398
ENABLE TABLE LOCK clause
of ALTER TABLE, 7-165
ENABLE THREAD clause
of ALTER DATABASE, 7-15
ENABLE VALIDATE constraint state, 7-406
ENABLE/DISABLE clause
of ALTER TABLE, 7-137,
7-165
of CREATE TABLE, 7-388
END BACKUP clause
of ALTER TABLESPACE, 7-180
equality test, 3-5
equijoins, 5-22
defining for a dimension, 7-280
equivalency tests, 3-6
ESTIMATE STATISTICS clause
of ANALYZE, 7-204
EXCEPTIONS INTO clause
of ALTER TABLE, 7-163
of constraint_clause, 7-245
of CREATE TABLE, 7-407
EXCHANGE PARTITION clause
of ALTER TABLE, 7-162
EXCHANGE SUBPARTITION clause
of ALTER TABLE, 7-162
EXCLUDING NEW VALUES clause
of ALTER MATERIALIZED VIEW LOG, 7-61
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-337
EXCLUSIVE lock mode, 7-548
EXECUTE ANY INDEXTYPE system privilege, 7-522
EXECUTE ANY OPERATOR system privilege, 7-524
EXECUTE ANY PROCEDURE system privilege, 7-524
EXECUTE ANY TYPE system privilege, 7-528
EXECUTE object privilege, 7-535
EXECUTE_CATALOG_ROLE role, 7-530
execution plans
determining, 7-512
dropping outlines for, 7-490
saving, 7-342
EXISTS
conditions, 5-18
operator, 3-6
EXP function, 4-33
EXP_FULL_DATABASE role, 7-530
EXPLAIN PLAN statement, 7-512
explicit data conversion, 2-37,
2-38
expressions
CASE, 5-13
CAST, 5-7
compound, 5-4
computing with the DUAL table, 5-26
CURSOR, 5-10
DECODE, 5-12
function, 5-5
function, built-in, 5-5
in SQL syntax, 5-1
list of, 5-14
object access, 5-11
simple, 5-3
type constructor, 5-6
variable, 5-4
extended rowids, 2-27
not directly available, 2-27
EXTENT MANAGEMENT clause
for temporary tablespaces, 7-425
of CREATE TABLESPACE, 7-420,
7-422
extents
allocating for partitions, 7-150
allocating for subpartitions, 7-150
allocating for tables, 7-150
restricting access by instances, 7-35
specifying maximum number for an object, 7-608
specifying number allocated upon object creation, 7-607
specifying the first for an object, 7-607
specifying the percentage of size increase, 7-607
specifying the second for an object, 7-607
external datatypes
associated with host variables, 2-10
recognized by precompilers, 2-10
external functions, 7-285,
7-354
external LOBs, 2-23
external procedures, 7-354
external users, 7-366,
7-452
F
FAILED_LOGIN_ATTEMPTS parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
FAST_START_IO_TARGET parameter
of ALTER SESSION, 7-87,
7-110
FAST_START_PARALLEL_ROLLBACK parameter
of ALTER SYSTEM, 7-110
files
specifying as a redo log file group, 7-516
specifying as datafiles, 7-516
specifying as tempfiles, 7-516
filespec clause, 7-516
of CREATE CONTROLFILE, 7-263
of CREATE DATABASE, 7-268
of CREATE LIBRARY, 7-316
of CREATE TABLESPACE, 7-419
of CREATE TEMPORARY TABLESPACE, 7-424
FIPS compliance, B - 3
FIPS flagging, 7-88
FIRST_ROWS hint, 2-64
FIRST_VALUE function, 4-34
FIXED_DATE parameter
of ALTER SYSTEM, 7-110
FLAGGER parameter
of ALTER SESSION, 7-88
FLOAT datatype, 2-19
ANSI, 2-28
DB2, 2-29
SQL/DS, 2-29
floating-point numbers, 2-19
FLOOR function, 4-35
FLUSH SHARED POOL clause
of ALTER SYSTEM, 7-107
FM format model modifier, 2-51
FM number format element, 2-41
FOR CATEGORY clause
of CREATE OUTLINE, 7-342
FOR clause
of ANALYZE ... COMPUTE STATISTICS, 7-204
of ANALYZE ... ESTIMATE STATISTICS, 7-204
of CREATE INDEXTYPE, 7-310
of CREATE SYNONYM, 7-378
of EXPLAIN PLAN, 7-514
FOR EACH ROW clause
of CREATE TRIGGER, 7-432
FOR UPDATE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-327
of SELECT, 7-572,
7-580
FORCE ANY TRANSACTION system privilege, 7-529
FORCE CLAUSE
of DROP OPERATOR, 7-489
FORCE clause
of COMMIT, 7-231
of CREATE VIEW, 7-458
of DISASSOCIATE STATISTICS, 7-471
of DROP INDEX, 7-481
of DROP INDEXTYPE, 7-482
of DROP TYPE, 7-507
of REVOKE schema_object_privileges, 7-561
of ROLLBACK, 7-565
FORCE PARALLEL DML clause
of ALTER SESSION, 7-84
FORCE TRANSACTION system privilege, 7-529
FOREIGN KEY clause
of constraint_clause, 7-236,
7-240
foreign key constraints, 7-240
foreign tables
rowids of, 2-27
format models, 2-38
changing the return format, 2-39
date, 2-45
date, changing, 2-45
date, default, 2-45
date, format elements, 2-45
date, maximum length, 2-45
modifiers, 2-51
number, 2-40
number, elements of, 2-41
specifying, 2-40
formats
for dates and numbers.
See
format models
of return values from the database, 2-38
of values stored in the database, 2-38
free lists
specifying for a table, partition, cluster, or index, 7-608
FREELIST GROUPS parameter
of STORAGE clause, 7-608
FREELISTS parameter
of STORAGE clause, 7-608
FROM clause
of queries, 5-23
of REVOKE system_privileges_and_roles, 7-557
of SELECT, 7-574
FROM COLUMNS clause
of DISASSOCIATE STATISTICS, 7-471
FROM FUNCTIONS clause
of DISASSOCIATE STATISTICS, 7-471
FROM INDEXES clause
of DISASSOCIATE STATISTICS, 7-471
FROM INDEXTYPES clause
of DISASSOCIATE STATISTICS, 7-471
FROM PACKAGES clause
of DISASSOCIATE STATISTICS, 7-471
FROM PUBLIC clause
of REVOKE schema_object_privileges, 7-561
FROM role clause
of REVOKE schema_object_privileges, 7-561
FROM TYPES clause
of DISASSOCIATE STATISTICS, 7-471
FROM user clause
of REVOKE schema_object_privileges, 7-561
FULL hint, 2-65
function expressions
built-in, 5-5
function-based indexes, 7-295
and query rewrite, 7-93
creating, 7-298
disabling, 7-40,
7-116
enabling, 7-37,
7-40,
7-116
functions
See also
SQL functions
3GL, calling, 7-316
access to tables and packages, 7-442
associating statistics with, 7-212
avoiding run-time compilation, 7-28
calling, 7-226
changing the declaration of, 7-28
datatype of return value, 7-288
declaring
as a Java method, 7-289
as C functions, 7-289
defining an index on, 7-298
disassociating statistics types from, 7-478
examples, 7-289
executing, 7-226
from parallel query process, 7-288
expressions, 5-5
external, 7-285,
7-354
invalidating local objects dependent on, 7-478
issuing COMMIT or ROLLBACK statements, 7-84
naming rules, 2-74
privileges executed with, 7-188,
7-441
recompiling invalid, 7-28
re-creating, 7-286,
7-312
removing from the database, 7-478
schema executed in, 7-188,
7-441
specification of
in CREATE TYPE, 7-442
specifying schema and user privileges for, 7-288
stored, 7-285
storing return value of, 7-227
synonyms for, 7-377
user-defined, 4-118
using a saved copy of, 7-288
FUNCTIONS clause
of ASSOCIATE STATISTICS, 7-210,
7-212
FX format model modifier, 2-51
G
G number format element, 2-41
GC_DEFER_TIME parameter
of ALTER SYSTEM, 7-110
general recovery clause
of ALTER DATABASE, 7-7,
7-12
GLOBAL PARTITION BY RANGE clause
of CREATE INDEX, 7-302
GLOBAL QUERY REWRITE system privilege, 7-523,
7-524,
7-526
GLOBAL TEMPORARY clause
of CREATE TABLE, 7-391
global users, 7-366,
7-452
GLOBAL_NAMES parameter
of ALTER SESSION, 7-88
of ALTER SYSTEM, 7-110
globally partitioned indexes, 7-302
GRANT ANY PRIVILEGE system privilege, 7-529
GRANT ANY ROLE system privilege, 7-525
GRANT CONNECT THROUGH clause
of ALTER USER, 7-194,
7-195
GRANT object_privileges, 7-532
GRANT system_privileges_and_roles statement, 7-519
GRAPHIC datatype (SQL/DS or DB2), 2-30
greater than or equal to tests, 3-5
greater than tests, 3-5
GREATEST function, 4-36
GROUP BY clause
of SELECT, 7-578
of SELECT and subqueries, 7-571
group comparison conditions, 5-16
GROUPING function, 4-36
H
hash clusters
creating, 7-257
single-table, creating, 7-257
specifying hash function for, 7-257
HASH hint, 2-65
HASH IS clause
of CREATE CLUSTER, 7-257
hash partition
adding, 7-160
hash partitioning clause
of CREATE TABLE, 7-387,
7-402
HASH_AJ hint, 2-65
HASH_AREA_SIZE parameter
of ALTER SESSION, 7-88
HASH_JOIN_ENABLED parameter
of ALTER SESSION, 7-88
HASH_MULTIBLOCK_IO_COUNT parameter
of ALTER SESSION, 7-88
of ALTER SYSTEM, 7-111
HASH_SJ hint, 2-65
HASHKEYS clause
of CREATE CLUSTER, 7-257
HAVING condition
of GROUP BY clause, 7-579
heap-organized tables
creating, 7-390
hexadecimal value
returning, 2-43
HEXTORAW function, 2-37,
4-37
HH date format element, 2-46
HH12 date format element, 2-46
HH24 date format element, 2-46
hierarchical queries, 2-58,
5-20,
7-577
child nodes of, 2-58
child rows of, 5-20
illustrated, 2-59
leaf nodes of, 2-58
parent nodes of, 2-58
parent rows of, 5-20
hierarchical query clause
of SELECT and subqueries, 7-571
hierarchies
adding to a dimension, 7-27
dropping from a dimension, 7-27
of dimensions, defining, 7-279
HIERARCHY clause
of ALTER DIMENSION, 7-25
of CREATE DIMENSION, 7-279
high water mark
of clusters, 7-5
of indexes, 7-35
of tables, 7-150,
7-203
hints, 5-19
in SQL statements, 2-63
passing to the optimizer, 7-616
syntax, 2-64
HS_ADMIN_ROLE role, 7-530
HS_AUTOREGISTER parameter
of ALTER SYSTEM, 7-111
I
I date format element, 2-46
IDENTIFIED BY clause
of ALTER ROLE.
See
CREATE ROLE
of ALTER ROLE.
See
CREATE ROLE.
of CREATE ROLE, 7-366
IDENTIFIED BY password clause
of CREATE DATABASE LINK, 7-275
of CREATE USER, 7-452
of SET ROLE, 7-601
IDENTIFIED EXTERNALLY clause
of ALTER ROLE.
See
CREATE ROLE
of ALTER USER.
See
CREATE USER
of CREATE ROLE, 7-366
of CREATE USER, 7-452
IDENTIFIED GLOBALLY clause
of ALTER ROLE.
See
CREATE ROLE
of ALTER ROLE.
See
CREATE ROLE.
of ALTER USER, 7-195
of CREATE ROLE, 7-366
of CREATE USER, 7-452
of CREATE USERIDENTIFIED BY clause
of ALTER USER.
See
CREATE USER
IDLE_TIME parameter
of ALTER PROFILE, 7-69
of CREATE PROFILE, 7-361
IMMEDIATE clause
of SET CONSTRAINTS, 7-598
IMP_FULL_DATABASE role, 7-530
implicit data conversion, 2-36,
2-38
IN OUT parameter
of CREATE FUNCTION, 7-287
of CREATE PROCEDURE, 7-356
IN parameter
of CREATE function, 7-287
of CREATE PROCEDURE, 7-356
INCLUDING clause
of ALTER TABLE, 7-154
INCLUDING CONTENTS clause
of DROP TABLESPACE, 7-504
INCLUDING NEW VALUES clause
of ALTER MATERIALIZED VIEW LOG, 7-61
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-337
INCLUDING TABLES clause
of DROP CLUSTER, 7-473
incomplete object types, 7-439
creating, 7-437,
7-439
INCREMENT BY clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE
of CREATE SEQUENCE, 7-373
INDEX clause
of CREATE CLUSTER, 7-257
INDEX hint, 2-65
INDEX object privilege, 7-535
index partitions
changing physical attributes of, 7-36
deallocating unused space from, 7-35
dropping, 7-41
marking UNUSABLE, 7-157
modifying the real characteristics of, 7-41
rebuilding, 7-37
unusable, 7-157
renaming, 7-41
specifying tablespace for, 7-38
splitting, 7-41
index subpartitions
allocating extents for, 7-42
changing physical attributes of, 7-36
deallocating unused space from, 7-35,
7-42
marking UNUSABLE, 7-42
rebuilding, 7-37
renaming, 7-41
specifying tablespace for, 7-38
INDEX_ASC hint, 2-65
INDEX_COMBINE hint, 2-65
INDEX_DESC hint, 2-65
INDEX_FFS hint, 2-65
indexed clusters
creating, 7-257
indexes
allocating new extents for, 7-35
application-specific, 7-309
ascending, 7-299
based on indextypes, 7-304
bitmap, 7-297
changing attributes of, 7-36
collecting statistics on, 7-202
on composite-partitioned tables, 7-303
creating, 7-295
creating as cluster indexes, 7-297
deallocating unused space from, 7-35
descending, 7-299
and query rewrite, 7-299
as function-based indexes, 7-299
disassociating statistics types from, 7-480
domain, 7-295,
7-304,
7-309
dropping index partitions of, 7-480
examples, 7-305
function-based, 7-295
creating, 7-298
globally partitioned, 7-302
granting
system privileges on, 7-522
on hash-partitioned tables, 7-303
key compression of, 7-39,
7-300
locally partitioned, 7-302
logging attributes of, 7-301
logging rebuild operations on, 7-39
marking as UNUSABLE, 7-40
merging contents of index blocks, 7-40
online, 7-301
parallel queries and DML on, 7-36
parallelizing creation of, 7-304
partitioned, 2-70,
7-295
user-defined, 7-302
partitions
adding new, 7-41
marking UNUSABLE, 7-41
physical attributes of, 7-300
on range-partitioned tables, 7-302
rebuilding, 7-37
rebuilding while online, 7-39
removing from the database, 7-480
renaming, 7-40
reverse, 7-38,
7-300
specifying tablespace for, 7-38
statistics on, 7-301
statistics on rebuild, 7-39
storage characteristics of, 7-300,
7-605
tablespace containing, 7-300
unique, 7-296
unsorted, 7-300
validating structure of, 7-206
INDEXES clause
of ASSOCIATE STATISTICS, 7-210,
7-212
index-organized table clause
of CREATE TABLE, 7-384,
7-396
index-organized tables
creating, 7-390,
7-396
modifying, 7-153
rebuilding, 7-144
reserving space in the index block, 7-154
rowids of, 2-27
INDEXTYPE clause
of CREATE INDEX, 7-304
indextypes
associating statistics with, 7-212
creating, 7-309
disassociating from statistics types, 7-482
drop routines, invoking, 7-480
granting
system privileges on, 7-522
indexes based on, 7-304
instances of, 7-295
removing from the database, 7-482
INDEXTYPES clause
of ASSOCIATE STATISTICS, 7-210,
7-212
in-doubt transactions
forcing, 7-231
forcing commit of, 7-231
forcing rollback, 7-565
forcing rollback of, 7-565
rolling back, 7-564
inequality test, 3-5
INITCAP function, 4-38
INITIAL parameter
of STORAGE clause, 7-607
INITIALLY DEFERRED clause
of constraint_clause, 7-243
INITIALLY IMMEDIATE clause
of constraint_clause, 7-243
INITRANS parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of CREATE CLUSTER, 7-256
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-394
INSERT ANY TABLE system privilege, 7-527
INSERT object privilege, 7-535
INSERT statement, 7-539
triggers on, 7-429
INSTANCE parameter
of ALTER SESSION, 7-88
instances
global name resolution for, 7-110
setting parameters for, 7-108
INSTEAD OF clause
of CREATE TRIGGER, 7-429
INSTEAD OF triggers, 7-429
INSTR function, 4-38
INSTRB function, 4-39
INT datatype (ANSI), 2-28
INTEGER datatype
ANSI, 2-28
DB2, 2-29
SQL/DS, 2-29
integers
generating unique, 7-371
in SQL syntax, 2-3
precision of, 2-3
specifying, 2-17
syntax of, 2-3
integrity constraints.
See
constraints
internal LOBs, 2-23
International Standards Organization.
See
ISO
INTERSECT set operator, 3-13,
7-579
INTO clause
of EXPLAIN PLAN, 7-513
of INSERT, 7-540
INTO host_variable clause
of CALL, 7-227
invoker rights clause
of ALTER JAVA, 7-46
of CREATE FUNCTION, 7-288
of CREATE JAVA, 7-313
of CREATE PACKAGE, 7-344
of CREATE PROCEDURE, 7-353
of CREATE TYPE, 7-188,
7-441
IS NOT NULL operator, 3-7
IS NULL operator, 3-7
ISO, B - 1
standards, xv,
1-2,
B - 1
ISOLATION LEVEL READ COMMITTED clause
of SET TRANSACTION, 7-603
ISOLATION LEVEL SERIALIZABLE clause
of SET TRANSACTION, 7-603
ISOLATION_LEVEL parameter
of ALTER SESSION, 7-89
IW date format element, 2-46
IY date format element, 2-46
IYY date format element, 2-46
IYYY date format element, 2-46
J
J date format element, 2-46
Java class schema object
creating, 7-312,
7-313
dropping, 7-483
resolving, 7-45,
7-312
JAVA clause
of CREATE TYPE, 7-442
of CREATE TYPE BODY, 7-450
Java method
mapping to an object type, 7-442
Java resource schema object
creating, 7-312,
7-313
dropping, 7-483
Java schema object
name resolution of, 7-314
Java source schema object
compiling, 7-45,
7-312
creating, 7-312,
7-313
dropping, 7-483
JOB_QUEUE_PROCESSES parameter
of ALTER SYSTEM, 7-111
JOIN KEY clause
of ALTER DIMENSION, 7-26
of CREATE DIMENSION, 7-280
join views
modifying, 7-466,
7-541,
7-618
joins, 5-22
conditions
defining, 5-22
equijoins, 5-22
outer, 5-23
restrictions, 5-23
self, 5-23
without join conditions, 5-23
Julian day, specifying, 2-47
K
key compression, 7-39,
7-300,
7-397
disabling, 7-39,
7-300
of index rebuild, 7-144
of indexes, 7-39,
7-300
disabling, 7-39
of index-organized tables, 7-397
keywords, 2-73
in syntax diagrams, xxi
optional, A-2
required, A-1
KILL SESSION clause
of ALTER SYSTEM, 7-107
L
L number format element, 2-41
LAG function, 4-40
LANGUAGE clause
of CREATE FUNCTION, 7-289
of CREATE PROCEDURE, 7-357
of CREATE TYPE, 7-442
of CREATE TYPE BODY, 7-450
large objects.
See
LOBs
LAST_DAY function, 4-41
LAST_VALUE function, 4-42
LEAD function, 4-44
LEAST function, 4-45
LENGTH function, 4-45
LENGTHB function, 4-46
less than tests, 3-5
LEVEL clause
of ALTER DIMENSION, 7-25
of CREATE DIMENSION, 7-279
LEVEL pseudocolumn, 2-58,
7-577
and hierarchical queries, 2-58
levels
adding to a dimension, 7-27
dropping from a dimension, 7-27
of dimensions, defining, 7-279
libraries
creating, 7-316
granting
system privileges on, 7-523
re-creating, 7-316
removing from the database, 7-484
library units.
See
Java schema objects
LICENSE_MAX_SESSIONS parameter
of ALTER SYSTEM, 7-111
LICENSE_MAX_USERS parameter
of ALTER SYSTEM, 7-112
LICENSE_SESSIONS_WARNING parameter
of ALTER SYSTEM, 7-112
LIKE conditions, 5-18
LIKE operator, 3-8
linear regression functions, 4-71
LIST CHAINED ROWS clause
of ANALYZE, 7-207
literals
in SQL statements and functions, 2-2
in SQL syntax, 2-2
LN function, 4-46
LOB datatypes, 2-23
LOB index clause
of ALTER TABLE, 7-142
of CREATE TABLE, 7-399
LOB storage clause
for partitions, 7-142
of ALTER MATERIALIZED VIEW, 7-47,
7-51
of ALTER TABLE, 7-141
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-322
of CREATE TABLE, 7-385,
7-398
LOBs
attributes
initializing, 2-24
columns
difference from LONG and LONG RAW, 2-23
populating, 2-24
external, 2-23
indexes for, 7-399
internal, 2-23
locators, 2-23,
7-399
logging attribute of, 7-395
modifying physical attributes of, 7-146
number of bytes manipulated in, 7-399
specifying directories for, 7-282
storage
characteristics, 7-394,
7-398
in-line, 7-398
outside of row, 7-398
tablespace for
defining, 7-394
LOCAL clause
of CREATE INDEX, 7-302
local users, 7-366,
7-452
locally managed tablespaces
storage characteristics, 7-606
locally partitioned indexes, 7-302
LOCK ANY TABLE system privilege, 7-527
LOCK TABLE statement, 7-547
locking
automatic
overriding, 7-547
locks.
See
table locks
LOG function, 4-47
LOG_ARCHIVE_DEST parameter
of ALTER SYSTEM, 7-112
LOG_ARCHIVE_DEST_n parameter
of ALTER SESSION, 7-89,
7-112
of ALTER SYSTEM, 7-112
LOG_ARCHIVE_DEST_STATE_n parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-113
LOG_ARCHIVE_DUPLEX_DEST parameter
of ALTER SYSTEM, 7-113
LOG_ARCHIVE_MAX_PROCESSES parameter
of ALTER SYSTEM, 7-113
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter
of ALTER SESSION, 7-89
of ALTER SYSTEM, 7-113
LOG_ARCHIVE_TRACE parameter
of ALTER SYSTEM, 7-113
LOG_CHECKPOINT_INTERVAL parameter
of ALTER SYSTEM, 7-113
LOG_CHECKPOINT_TIMEOUT parameter
of ALTER SYSTEM, 7-114
LOGFILE clause
of CREATE CONTROLFILE, 7-265
OF CREATE DATABASE, 7-269
logfile clauses
of ALTER DATABASE, 7-9
LOGFILE GROUP clause
of CREATE CONTROLFILE, 7-265
logging
and redo log size, 7-396
specifying minimal, 7-395
LOGGING clause
of ALTER INDEX, 7-36
of ALTER MATERIALIZED VIEW, 7-50
of ALTER MATERIALIZED VIEW LOG, 7-60
of ALTER TABLE, 7-152
of ALTER TABLESPACE, 7-178
of CREATE INDEX, 7-301
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-322
of CREATE TABLE, 7-395
of CREATE TABLESPACE, 7-421
logical operators, 3-11
LOGICAL_READS_PER_CALL parameter
of ALTER PROFILE, 7-69
of CREATE PROFILE, 7-362
LOGICAL_READS_PER_SESSION parameter
of ALTER PROFILE, 7-69
of ALTER RESOURCE COST, 7-74
of CREATE PROFILE, 7-362
LOGOFF
triggers on, 7-431
LOGOFF event
triggers on, 7-431
LOGON
triggers on, 7-431
LOGON event
triggers on, 7-431
LONG columns
converting to LOB columns, 2-19,
2-22
restrictions on, 2-20
to store text strings, 2-19
to store view definitions, 2-19
where referenced from, 2-20
LONG datatype, 2-19
in triggers, 2-20
LONG RAW
data
converting from CHAR data, 2-23
datatype, 2-22
LONG VARCHAR datatype
DB2, 2-29
SQL/DS, 2-29
LONG VARGRAPHIC datatype (SQL/DS or DB2), 2-30
LOWER function, 4-47
LPAD function, 4-48
LTRIM function, 4-48
M
MAKE_REF function, 4-49
MANAGE TABLESPACE system privilege, 7-527
managed recovery
of database, 7-8
MANAGED STANDBY RECOVERY clause
of ALTER DATABASE, 7-14
MAP MEMBER clause
of ALTER TYPE, 7-189
of CREATE TYPE, 7-443,
7-449
MAP methods
specifying, 7-189
master databases, 7-320
master tables, 7-320
materialized join views, 7-335
materialized view logs, 7-334
creating, 7-334
excluding new values from, 7-61
logging changes to, 7-60
parallelizing creation of, 7-336
partition attributes
changing, 7-60
partitioned, 7-336
physical attributes
changing, 7-60
specifying, 7-336
removing from the database, 7-487
required for fast refresh, 7-334
saving new values in, 7-61
saving old values in, 7-337
storage characteristics
specifying, 7-336
materialized views
allowing update of, 7-327
complete refresh, 7-52,
7-325
constraints on, 7-244
creating, 7-320
creating comments about, 7-228
for data warehousing, 7-320
degree of parallelism, 7-51,
7-60
during creation, 7-323
detail table of, dropping, 7-486
enabling and disabling query rewrite, 7-327
examples, 7-329,
7-337
fast refresh, 7-52,
7-324,
7-325
forced refresh, 7-53
from existing tables, 7-323
granting
system privileges on, 7-523
index characteristics
changing, 7-51
indexes that maintain, 7-324
join, 7-335
LOB storage characteristics of, 7-51
logging changes to, 7-50
partitions of, 7-51
physical and storage attributes
changing, 7-50
physical attributes of, 7-322
primary key, 7-326
recording values in master table, 7-61
query rewrite
eligibility for, 7-244
enabling and disabling, 7-54
re-creating during refresh, 7-52
refresh mode
changing, 7-52
refresh time
changing, 7-52
refreshing after DML on master table, 7-53,
7-325
refreshing on next COMMIT, 7-53,
7-325
removing from the database, 7-485
for replication, 7-320
retrieving data from, 7-572
revalidating, 7-54
rowid, 7-326
rowid values
recording in master table, 7-61
saving blocks in a cache, 7-51
storage characteristics of, 7-322
subquery, 7-328
synonyms for, 7-377
when to populate, 7-323
MAX function, 4-50
MAX_DUMP_FILE_SIZE parameter
of ALTER SESSION, 7-90
of ALTER SYSTEM, 7-114
MAXDATAFILES parameter
of CREATE CONTROLFILE, 7-265
OF CREATE DATABASE, 7-270
MAXEXTENTS parameter
of STORAGE clause, 7-608
MAXINSTANCES parameter
of CREATE CONTROLFILE, 7-266
OF CREATE DATABASE, 7-270
MAXLOGFILES parameter
of CREATE CONTROLFILE, 7-265
OF CREATE DATABASE, 7-270
MAXLOGHISTORY parameter
of CREATE CONTROLFILE, 7-265
OF CREATE DATABASE, 7-270
MAXLOGMEMBERS parameter
of CREATE CONTROLFILE, 7-265
OF CREATE DATABASE, 7-270
MAXSIZE clause
of ALTER DATABASE, 7-10
of CREATE DATABASE, 7-268
of CREATE TABLESPACE, 7-420
of CREATE TEMPORARY TABLESPACE, 7-424
MAXTRANS parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of CREATE CLUSTER, 7-256
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-394
MAXVALUE clause
of CREATE SEQUENCE, 7-373
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-15
restrictions, 7-13
sustained standby recovery, 7-14
MEMBER clause
of ALTER TYPE, 7-188
of CREATE TYPE, 7-441
of CREATE TYPE BODY, 7-448
membership conditions, 5-17
MERGE hint, 2-67
MERGE PARTITIONS clause
of ALTER TABLE, 7-162
MERGE_AJ hint, 2-65
MERGE_SJ hint, 2-65
MI date format element, 2-46
MI number format element, 2-41
migrated rows
listing, 7-207
MIN function, 4-52
MINEXTENTS parameter
of STORAGE clause, 7-607
MINIMIZE RECORDS PER BLOCK clause
of ALTER TABLE, 7-153
MINIMUM EXTENT clause
of ALTER TABLESPACE, 7-180
of CREATE TABLESPACE, 7-421
MINUS set operator, 3-13,
7-579
MINVALUE
of ALTER SEQUENCE.
See
CREATE SEQUENCE
MINVALUE clause
of CREATE SEQUENCE, 7-373
MM date format element, 2-46
MOD function, 4-53
MODE clause
of LOCK TABLE, 7-548
MODIFY clause
of ALTER TABLE, 7-143
MODIFY CONSTRAINT clause
of ALTER TABLE, 7-144
MODIFY DEFAULT ATTRIBUTES clause
of ALTER INDEX, 7-33,
7-41
of ALTER TABLE, 7-155
MODIFY LOB clause
of ALTER TABLE, 7-146
MODIFY LOB storage clause
of ALTER MATERIALIZED VIEW, 7-48,
7-51
of ALTER TABLE, 7-146
MODIFY NESTED TABLE clause
of ALTER TABLE, 7-145
MODIFY PARTITION clause
of ALTER INDEX, 7-33,
7-41
of ALTER MATERIALIZED VIEW, 7-51
of ALTER TABLE, 7-156
MODIFY SUBPARTITION clause
of ALTER INDEX, 7-34,
7-42
of ALTER TABLE, 7-157
MODIFY VARRAY clause
of ALTER TABLE, 7-146
modifying space for each cluster key, 7-4
MON date format element, 2-46,
2-48
MONITORING clause
of ALTER TABLE, 7-152
of CREATE TABLE, 7-408
MONTH date format element, 2-46,
2-48
MONTHS_BETWEEN function, 4-54
MOUNT clause
of ALTER DATABASE, 7-11
MOVE clause
of ALTER TABLE, 7-144
MOVE ONLINE clause
of ALTER TABLE, 7-144
MOVE PARTITION clause
of ALTER TABLE, 7-157
MOVE SUBPARTITION clause
of ALTER TABLE, 7-158
MTS_DISPATCHERS parameter
of ALTER SYSTEM, 7-114
MTS_SERVERS parameter
of ALTER SYSTEM, 7-115
multi-threaded server
system parameters, 7-114
N
NAMED clause
of CREATE JAVA, 7-313
namespaces
and object naming rules, 2-73
for nonschema objects, 2-74
for schema objects, 2-73
NATIONAL CHAR datatype (ANSI), 2-28
NATIONAL CHAR VARYING datatype (ANSI), 2-28
NATIONAL CHARACTER datatype (ANSI), 2-28
national character set
fixed vs. variable width, 2-15,
2-16
multibyte character data, 2-26
multibyte character sets, 2-15,
2-16
variable-length strings, 2-16
NATIONAL CHARACTER SET clause
of CREATE DATABASE, 7-271
NATIONAL CHARACTER SET parameter
of ALTER DATABASE, 7-16
NATIONAL CHARACTER VARYING datatype
ANSI, 2-28
national language support.
See
NLS
NCHAR datatype, 2-15
ANSI, 2-28
NCHAR VARYING datatype (ANSI), 2-28
NCLOB datatype, 2-26
transactional support of, 2-26
negative scale, 2-18
NESTED TABLE clause
of ALTER TABLE, 7-142
of CREATE TABLE, 7-386,
7-400
nested table types, 2-31
compared with varrays, 2-35
comparison rules, 2-35
creating, 7-439
dropping the body of, 7-508
dropping the specification of, 7-506
modifying, 7-145
nested tables
changing returned value, 7-145
creating, 7-444
defining as index-organized tables, 7-142
storage characteristics of, 7-142,
7-400
NEW_TIME function, 4-55
NEXT clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-53
NEXT parameter
of STORAGE clause, 7-607
NEXT_DAY function, 4-55
NEXTVAL pseudocolumn, 2-56,
7-372
NLS parameters
NLS_CALENDAR parameter
of ALTER SESSION, 7-90
NLS_CHARSET_DECL_LEN function, 4-56
NLS_CHARSET_ID function, 4-56
NLS_CHARSET_NAME function, 4-57
NLS_COMP parameter
of ALTER SESSION, 7-90
NLS_CURRENCY parameter
of ALTER SESSION, 7-90
NLS_DATE_FORMAT parameter
of ALTER SESSION, 7-90
NLS_DATE_LANGUAGE parameter, 2-48
of ALTER SESSION, 7-90
NLS_INITCAP function, 4-58
NLS_ISO_CURRENCY parameter
of ALTER SESSION, 7-90
NLS_LANGUAGE parameter, 2-48,
5-21
of ALTER SESSION, 7-90
NLS_LOWER function, 4-59
NLS_NUMERIC_CHARACTERS parameter
of ALTER SESSION, 7-91
NLS_SORT parameter, 5-21
of ALTER SESSION, 7-91
NLS_TERRITORY parameter, 2-48
of ALTER SESSION, 7-91
NLS_UNION_CURRENCY parameter
of ALTER SESSION, 7-90
NLS_UPPER function, 4-60
NLSSORT function, 4-59
NO_EXPAND hint, 2-65
NO_INDEX hint, 2-65
NO_MERGE hint, 2-67
NO_PUSH_JOIN_PRED hint, 2-67
NOAPPEND hint, 2-67
NOARCHIVELOG clause
of ALTER DATABASE, 7-9,
7-18
of CREATE CONTROLFILE, 7-266
OF CREATE DATABASE, 7-270
NOAUDIT schema_objects statement, 7-552
NOAUDIT sql_statements statement, 7-550
NOCACHE clause
of ALTER MATERIALIZED VIEW, 7-51
of ALTER MATERIALIZED VIEW LOG, 7-61
of ALTER SEQUENCE.
See
CREATE SEQUENCE
of ALTER TABLE, 7-151
of CREATE CLUSTER, 7-258
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
of CREATE SEQUENCE, 7-374
of CREATE TABLE, 7-408
NOCACHE hint, 2-67
NOCOMPRESS clause
of ALTER TABLE, 7-144
of CREATE INDEX, 7-300
of CREATE TABLE, 7-397
NOCOPY clause
of CREATE FUNCTION, 7-287
of CREATE PROCEDURE, 7-356
NOCYCLE clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE, 7-81
of CREATE SEQUENCE, 7-373
NOFORCE clause
of CREATE JAVA, 7-313
of CREATE VIEW, 7-458
NOLOGGING clause
of ALTER INDEX, 7-36
of ALTER MATERIALIZED VIEW, 7-50
of ALTER MATERIALIZED VIEW LOG, 7-60
of ALTER TABLE, 7-152
of ALTER TABLESPACE, 7-178
of CREATE INDEX, 7-301
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-322
of CREATE TABLE, 7-395
of CREATE TABLESPACE, 7-421
NOMAXVALUE clause
of CREATE SEQUENCE, 7-373
NOMAXVALUE parameter
of ALTER SEQUENCE.
See
CREATE SEQUENCE
NOMINIMIZE RECORDS PER BLOCK clause
of ALTER TABLE, 7-153
NOMINVALUE clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE, 7-81
of CREATE SEQUENCE, 7-373
NOMONITORING clause
of ALTER TABLE, 7-152
of CREATE TABLE, 7-409
NONE clause
of SET ROLE, 7-601
nonequivilancy tests, 3-6
nonschema objects
list of, 2-69
namespaces, 2-74
NOORDER clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE
of CREATE SEQUENCE, 7-374
NOPARALLEL clause
of CREATE CLUSTER, 7-258
of CREATE INDEX, 7-304
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
of CREATE TABLE, 7-405
NOPARALLEL hint, 2-67
NOPARALLEL_INDEX hint, 2-67
NORELY clause
of constraint_clause, 7-244
NORESETLOGS clause
of CREATE CONTROLFILE, 7-265
NOREWRITE hint, 2-65
NOSORT clause
of ALTER INDEX, 7-300
of constraint_clause, 7-244
NOT DEFERRABLE clause
of constraint_clause, 7-243
NOT IDENTIFIED clause
of ALTER ROLE.
See
CREATE ROLE
of CREATE ROLE, 7-366
NOT IN operator, 3-7
NOT NULL clause
of constraint_clause, 7-239
of CREATE TABLE, 7-393
NOT NULL constraints, 7-239
not null constraints, 7-239
NOT operator, 3-11
NOWAIT clause
of LOCK TABLE, 7-548
NTILE function, 4-61
null, 2-54
difference from zero, 2-54
in conditions, 2-55
table of, 2-55
in functions, 2-54
with comparison operators, 2-55
NULL clause
of constraint_clause, 7-239
NULL conditions, 5-18
NUMBER datatype, 2-17
converting to VARCHAR2, 2-40
precision, 2-17
scale, 2-17
number format models, 2-40
number functions, 4-4
numbers
comparison rules, 2-32
floating-point, 2-17,
2-19
in SQL syntax, 2-4
precision of, 2-4
rounding, 2-18
spelling out, 2-51
syntax of, 2-4
NUMERIC datatype (ANSI), 2-28
NUMTODSINTERVAL function, 4-62
NUMTOYMINTERVAL function, 4-63
NVARCHAR2 datatype, 2-16
NVL function, 4-64
NVL2 function, 4-65
O
object access expressions, 5-11
object cache, 7-91,
7-115
OBJECT IDENTIFIER clause
of CREATE TABLE, 7-401
object identifiers
contained in REFs, 2-31
of object views, 7-459
primary key, 7-401
specifying, 7-401
specifying an index on, 7-402
system-generated, 7-401
object privileges
granting, 7-365
multiple, 7-369
on specific columns, 7-533
to a role, 7-532
to a user, 7-532
to PUCLIC, 7-533
on a database object
revoking, 7-561
on a directory
revoking, 7-560
revoking
all, from a user, 7-560
from a role, 7-559,
7-560
from a user, 7-559,
7-560
from all users, 7-561
from PUBLIC, 7-560
object reference functions, 4-12
object tables
adding rows to, 7-540
creating, 7-381
object type bodies
creating, 7-448
re-creating, 7-448
SQL examples, 7-450
object type tables
creating, 7-391
object type values
comparing, 7-443,
7-449
object types, 2-30
adding new member subprograms, 7-188
associating functions or procedures, 7-188
attributes, 2-82
comparison rules, 2-35
MAP function, 2-35
ORDER function, 2-35
compiling the specification and body, 7-187
components of, 2-30
creating, 7-437,
7-439
defining member methods of, 7-448
disassociating statistics types from, 7-506
dropping the body of, 7-508
dropping the specification of, 7-506
function subprogram
declaring, 7-449
function subprogram of
specifying, 7-442
function subprograms of, 7-441,
7-448
granting system privileges on, 7-528
incomplete, 7-437,
7-439
methods, 2-82
nested table, 7-439
procedure subprogram
declaring, 7-449
procedure subprogram of
specifying, 7-442
procedure subprograms of, 7-441,
7-448
SQL examples, 7-444
statistics types, 7-211
user-defined
creating, 7-440
varrays, 7-439
object views
adding rows to the base table of, 7-540
defining, 7-456
OBJECT_CACHE_MAX_SIZE_PERCENT parameter
of ALTER SESSION, 7-91
of ALTER SYSTEM, 7-115
OBJECT_CACHE_OPTIMAL_SIZE parameter
of ALTER SESSION, 7-91
of ALTER SYSTEM, 7-115
objects.
See
object types or database objects
OF clause
of CREATE VIEW, 7-459
OF object_type clause
of CREATE TABLE, 7-391
OFFLINE clause
of ALTER ROLLBACK SEGMENT, 7-79
of ALTER TABLESPACE, 7-180
of CREATE TABLESPACE, 7-422
OIDINDEX clause
of CREATE TABLE, 7-402
OIDs.
See
object identifiers
ON clause
of CREATE OUTLINE, 7-342
ON COMMIT clause
of CREATE TABLE, 7-401
ON DATABASE clause
of CREATE TRIGGER, 7-432
ON DEFAULT clause
of AUDIT schema_objects, 7-222
of NOAUDIT schema_objects, 7-552
ON DELETE CASCADE clause
of constraint_clause, 7-241
ON DELETE SET NULL clause
of constraint_clause, 7-241
ON DIRECTORY clause
of AUDIT schema_objects, 7-222
of GRANT object_privileges, 7-533
of NOAUDIT schema_objects, 7-552
of REVOKE schema_object_privileges, 7-560
ON JAVA RESOURCE clause
of GRANT object_privileges, 7-533
ON JAVA SOURCE clause
of GRANT object_privileges, 7-533
ON NESTED TABLE clause
of CREATE TRIGGER, 7-432
ON object clause
of GRANT object_privileges, 7-533
of NOAUDIT schema_objects, 7-552
of REVOKE schema_object_privileges, 7-561
ON PREBUILT TABLE clause
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
ON SCHEMA clause
of CREATE TRIGGER, 7-432
ONLINE clause
of ALTER ROLLBACK SEGMENT, 7-78
of ALTER TABLESPACE, 7-180
of CREATE INDEX, 7-301
of CREATE TABLESPACE, 7-422
online indexes, 7-301
rebuilding, 7-144
online redo logs
reinitializing, 7-19
OPEN NORESETLOGS clause
of ALTER DATABASE, 7-12
OPEN READ ONLY clause
of ALTER DATABASE, 7-12
OPEN READ WRITE clause
of ALTER DATABASE, 7-12
OPEN RESETLOGS clause
of ALTER DATABASE, 7-12
operands, 3-1
operators, 3-1
arithmetic, 3-3
binary, 3-1
comparison, 3-5
concatenation, 3-3
granting
system privileges on, 7-524
logical, 3-11
precedence, 3-2
set, 3-12,
7-579
unary, 3-1
user-defined, 3-16
binding to a function, 7-340
creating, 7-340
dropping, 7-489
function providing implementation, 7-341
how bindings are implemented, 7-341
implementation type, 7-341
return type of binding, 7-340
OPTIMAL parameter
of STORAGE clause, 7-368,
7-608
optimization
setting session parameters, 7-92
OPTIMIZER_INDEX_CACHING parameter
of ALTER SESSION, 7-92
OPTIMIZER_INDEX_COST_ADJ parameter
of ALTER SESSION, 7-92
OPTIMIZER_MAX_PERMUTATIONS parameter
of ALTER SESSION, 7-92,
7-115
OPTIMIZER_MODE parameter
of ALTER SESSION, 7-92
OPTIMIZER_PERCENT_PARALLEL parameter
of ALTER SESSION, 7-92
OR operator, 3-11,
3-12
OR REPLACE clause
of CREATE CONTEXT, 7-261
of CREATE DIRECTORY, 7-283
of CREATE FUNCTION, 7-286,
7-312
of CREATE LIBRARY, 7-316
of CREATE OUTLINE, 7-342
of CREATE PACKAGE, 7-345
of CREATE PACKAGE BODY, 7-349
of CREATE PROCEDURE, 7-356
of CREATE TRIGGER, 7-428
of CREATE TYPE, 7-440
of CREATE TYPE BODY, 7-448
of CREATE VIEW, 7-458
Oracle reserved words, C -1
Oracle Tools
support of SQL, 1-5
Oracle8
i
Enterprise Edition
features and functionality, xv
features and functionality, xv
new features, xvi
ORDER BY clause
of CREATE TABLE, 7-409
of queries, 5-21
of SELECT, 5-21,
7-571,
7-580
with ROWNUM, 2-61
of subqueries in CREATE TABLE, 7-409
ORDER clause
of ALTER SEQUENCE.
See
CREATE SEQUENCE
of CREATE SEQUENCE, 7-374
ORDER MEMBER clause
of ALTER TYPE, 7-189
of CREATE TYPE, 7-443
of CREATE TYPE BODY, 7-449
ORDER methods
specifying, 7-189
ORDERED hint, 2-66
ORDERED_PREDICATES hint, 2-65
ordinal numbers
specifying, 2-51
spelling out, 2-51
ORGANIZATION HEAP clause
of CREATE TABLE, 7-396
ORGANIZATION INDEX clause
of CREATE TABLE, 7-396
OUT parameter
of CREATE FUNCTION, 7-287
of CREATE PROCEDURE, 7-356
outer joins, 5-23,
7-577
restrictions, 5-23
outlines
assigning to a different category, 7-63
automatically creating and storing, 7-109
creating, 7-342
dropping from the database, 7-490
enabling and disabling dynamically, 7-342
granting
system privileges on, 7-524
rebuilding, 7-63
renaming, 7-63
replacing, 7-342
storing during the session, 7-86
storing for the instance, 7-118
use by the optimizer, 7-95,
7-118
used to generate execution plans, 7-342
OVERFLOW clause
of ALTER INDEX, 7-34
of ALTER TABLE, 7-154
of CREATE TABLE, 7-397
P
package bodies
creating, 7-348
recompiling, 7-64
re-creating, 7-349
removing from the database, 7-491
PACKAGE clause
of ALTER PACKAGE, 7-65
packaged procedures
dropping, 7-493
packages
associating statistics with, 7-212
avoiding run-time compilation, 7-64
changing the declaration of, 7-64
creating, 7-344
disassociating statistics types from, 7-492
invoker rights, 7-345
recompiling, 7-64
redefining, 7-64,
7-345
removing from the database, 7-491
specifying schema and privileges of, 7-345
synonyms for, 7-377
PACKAGES clause
of ASSOCIATE STATISTICS, 7-210,
7-212
PARALLEL clause
of ALTER CLUSTER, 7-3,
7-5
of ALTER DATABASE, 7-15
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-48,
7-51
of ALTER MATERIALIZED VIEW LOG, 7-59,
7-60
of ALTER TABLE, 7-164
of CREATE CLUSTER, 7-258
of CREATE INDEX, 7-304
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-334
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
of CREATE TABLE, 7-388,
7-405
parallel execution
of DDL statements, 7-84
of DML statements, 7-84
PARALLEL hint, 2-67
PARALLEL_ADAPTIVE_MULTI_USER parameter
of ALTER SYSTEM, 7-115
PARALLEL_BROADCAST_ENABLED parameter
of ALTER SESSION, 7-92
PARALLEL_ENABLE clause
of CREATE FUNCTION, 7-288
PARALLEL_INDEX hint, 2-67
PARALLEL_INSTANCE_GROUP parameter
of ALTER SESSION, 7-92
of ALTER SYSTEM, 7-115
PARALLEL_MIN_PERCENT parameter
of ALTER SESSION parameter, 7-92
PARALLEL_THREADS_PER_CPU parameter
of ALTER SYSTEM, 7-116
parameters
in syntax diagrams, xxi
optional, A-2
required, A-1
PARAMETERS clause
of CREATE INDEX, 7-304
PARTITION ... LOB storage clause
of ALTER TABLE, 7-142
PARTITION BY HASH clause
of CREATE TABLE, 7-402
PARTITION BY RANGE clause
of CREATE TABLE, 7-386,
7-403
PARTITION clause
of ANALYZE, 7-203
of CREATE INDEX, 7-302
of CREATE TABLE, 7-403
of DELETE, 7-466
of INSERT, 7-542
of LOCK TABLE, 7-548
of SELECT, 7-575
of UPDATE, 7-618
PARTITION_VIEW_ENABLED parameter
of ALTER SESSION, 7-93
partitioned indexes, 2-70,
7-295,
7-302
user-defined, 7-302
partitioned tables, 2-70
partition-extended table names, 2-70
in DML statements, 2-71
restrictions on, 2-71
syntax, 2-71
partitioning
by range, 7-386
clauses
of ALTER INDEX, 7-32
of ALTER MATERIALIZED VIEW, 7-48,
7-51
of ALTER MATERIALIZED VIEW LOG, 7-59,
7-60
of ALTER TABLE, 7-155
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-334
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-323
partitions
adding rows to, 7-540
allocating extents for, 7-150
composite, 2-70
specifying, 7-403
converting into nonpartitioned tables, 7-162
deallocating unused space from, 7-150
dropping, 7-160
extents
allocating new, 7-35
hash, 2-70
adding, 7-160
coalescing, 7-160
specifying, 7-402
inserting rows into, 7-542
LOB storage characteristics of, 7-142
locking, 7-547
logging attribute of, 7-395
logging insert operations, 7-152
merging, 7-162
modifying, 7-156
moving to a different segment, 7-157
physical attributes
changing, 7-145
range, 2-70
adding, 7-158
specifying, 7-403
removing rows from, 7-160,
7-466
renaming, 7-157
revising values in, 7-618
splitting, 7-161
storage characteristics, 7-394
tablespace for
defining, 7-394
PASSWORD EXPIRE clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
PASSWORD_GRACE_TIME parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
PASSWORD_LIFE_TIME parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
PASSWORD_LOCK_TIME parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
PASSWORD_REUSE_MAX parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
PASSWORD_REUSE_TIME parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
PASSWORD_VERIFY_FUNCTION parameter
of ALTER PROFILE, 7-70
of CREATE PROFILE, 7-362
passwords
expiration of, 7-453
parameters
of ALTER PROFILE, 7-363
of CREATE PROFILE, 7-360
PCTFREE parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of CREATE CLUSTER, 7-256
of CREATE INDEX, 7-300
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG.
See
CREATE TABLE.
of CREATE MATERIALIZED VIEW/SNAPSHOT.
See
CREATE TABLE.
of CREATE TABLE, 7-393
PCTINCREASE parameter
of STORAGE clause, 7-607
PCTTHRESHOLD parameter
of ALTER TABLE, 7-154
of CREATE TABLE, 7-397
PCTUSED parameter
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of CREATE CLUSTER, 7-256
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-393
PCTVERSION parameter
of CREATE TABLE, 7-399
of LOB storage clause, 7-142
PERCENT_RANK function, 4-66
PERMANENT clause
of ALTER TABLESPACE, 7-181
of CREATE TABLESPACE, 7-422
physical attributes clause
of a constraint, 7-236
of ALTER CLUSTER, 7-2
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of ALTER TABLE, 7-145
of CREATE CLUSTER, 7-254
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-334
of CREATE TABLE, 7-384,
7-393
plan stability, 7-342
PLAN_TABLE sample table, 7-512
PL/SQL, xv
blocks
syntax of, xxiii
compatibility with earlier releases, 7-93,
7-116
program body
of CREATE FUNCTION, 7-289
PLSQL_V2_COMPATIBILITY parameter
of ALTER SESSION, 7-93
of ALTER SYSTEM, 7-116
PM (P.M.) date format element, 2-46,
2-48
POWER function, 4-67
PQ_DISTRIBUTE hint, 2-67
PR number format element, 2-41
PRAGMA clause
of ALTER TYPE, 7-189
of CREATE TYPE, 7-438,
7-442
PRAGMA RESTRICT_REFERENCES, 7-189,
7-442
precedence
of operators, 3-2
precision
number of digits of, 2-4
of NUMBER datatype, 2-17
precompilers
Oracle, 1-4
PRESERVE SNAPSHOT LOG clause
of TRUNCATE, 7-613
PRIMARY KEY clause
of constraint_clause, 7-239
of CREATE TABLE, 7-393
primary key constraints, 7-239
enabling, 7-407
index on, 7-407
primary keys
generating values for, 7-371
PRIOR operator, 3-16
PRIVATE_SGA parameter
of ALTER PROFILE, 7-69
of ALTER RESOURCE COST, 7-74
of CREATE PROFILE, 7-362
privileges.
See
system privileges or object privileges
procedures
3GL, calling, 7-316
calling, 7-226
changing the declaration of, 7-67
changing the definition of, 7-67
creating, 7-354
declaring
as a Java method, 7-357
as C functions, 7-357
executing, 7-226
external, 7-354
granting
system privileges on, 7-524
invalidating local objects dependent on, 7-493
issuing COMMIT or ROLLBACK statements, 7-84
naming rules, 2-74
privileges executed with, 7-188,
7-441
recompiling, 7-67
re-creating, 7-356
removing from the database, 7-493
schema executed in, 7-188,
7-441
specification of
in CREATE TYPE, 7-442
specifying schema and privileges for, 7-357
synonyms for, 7-377
PROFILE clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
profiles
assigning to a user, 7-453
creating, 7-360
examples, 7-363
deassigning from users, 7-494
granting
system privileges on, 7-524
modifying, examples, 7-71
removing from the database, 7-494
proxy clause
of ALTER USER, 7-194,
7-195
pseudocolumns, 2-56
CURRVAL, 2-56
LEVEL, 2-58
NEXTVAL, 2-56
ROWID, 2-59
ROWNUM, 2-60
uses for, 2-61
PUBLIC clause
of CREATE ROLLBACK SEGMENT, 7-367
of CREATE SYNONYM, 7-378
of DROP DATABASE LINK, 7-475
public database links
dropping, 7-475
public rollback segments, 7-367
public synonyms, 7-378
dropping, 7-500
PURGE SNAPSHOT LOG clause
of TRUNCATE, 7-613
PUSH_JOIN_PRED hint, 2-67
PUSH_SUBQ hint, 2-67
Q
Q date format element, 2-46
queries, 5-19,
7-572
comments in, 5-19
compound, 5-21
correlated
left correlation, 7-576
defined, 5-19
distributed, 5-26
grouping returned rows on a value, 7-578
hierarchical.
See
hierarchical queries
hints in, 5-19
join, 5-22
locking rows during, 7-580
ordering returned rows, 7-580
outer joins in, 7-576,
7-577
referencing multiple tables, 5-22
restricting results of, 7-577
select lists of, 5-19
selecting from a random sample of rows, 7-574
selecting from specified partitions, 7-575
sorting results, 5-21
syntax, 5-19
top-level, 5-19
top-N, 2-61
query rewrite
and dimensions, 7-278
and function-based indexes, 7-93
and rule-based optimization, 7-93
consistency level, 7-93,
7-116
defined, 7-572
disabling, 7-93,
7-116
enabling, 7-93,
7-116
QUERY REWRITE system privilege, 7-523,
7-526
QUERY_REWRITE_ENABLED parameter
of ALTER SESSION, 7-93
of ALTER SYSTEM, 7-116
QUERY_REWRITE_INTEGRITY parameter
of ALTER SESSION, 7-93
of ALTER SYSTEM, 7-116
QUOTA clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
R
range conditions, 5-18
range partition
adding, 7-158
creating, 7-403
RANK function, 4-67
RATIO_TO_REPORT function, 4-68
RAW data
converting from CHAR data, 2-23
RAW datatype, 2-22
RAWTOHEX function, 2-37,
4-69
READ object privilege, 7-535
READ ONLY clause
of ALTER TABLESPACE, 7-181
of SET TRANSACTION, 7-602
READ WRITE clause
of ALTER TABLESPACE, 7-181
of SET TRANSACTION, 7-603
REAL datatype
ANSI, 2-28
REBUILD clause
of ALTER INDEX, 7-32,
7-37
of ALTER OUTLINE, 7-63
REBUILD COMPRESS clause
of ALTER INDEX, 7-39
REBUILD COMPUTE STATISTICS clause
of ALTER INDEX, 7-39
REBUILD LOGGING clause
of ALTER INDEX, 7-39
REBUILD NOCOMPRESS clause
of ALTER INDEX, 7-39
REBUILD NOLOGGING clause
of ALTER INDEX, 7-39
REBUILD NOREVERSE clause
of ALTER INDEX, 7-38
REBUILD ONLINE clause
of ALTER INDEX, 7-39
REBUILD PARAMETERS clause
of ALTER INDEX, 7-40
REBUILD PARTITION clause
of ALTER INDEX, 7-38
REBUILD REVERSE clause
of ALTER INDEX, 7-38
REBUILD SUBPARTITION clause
of ALTER INDEX, 7-38
REBUILD TABLESPACE clause
of ALTER INDEX, 7-38
REBUILD UNUSABLE LOCAL INDEXES clause
of ALTER TABLE, 7-157
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-14
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-14
RECOVERABLE, 7-37,
7-396
See also
LOGGING clause
recovery
distributed, enabling, 7-106
of database, 7-7
RECOVERY_CATALOG_OWNER role, 7-530
redo logs
adding, 7-18
automatic archiving of, 7-103
automatic name generation, 7-13
disabling specified threads in a parallel server, 7-16
dropping, 7-19
enabling specified threads in a parallel server, 7-15
members
adding to existing groups, 7-18
dropping, 7-19
renaming, 7-15
reusing, 7-517
size of, 7-517
specifying, 7-516
for media recovery, 7-14
switching groups, 7-107
REF columns
specifying, 7-393
specifying from table or column level, 7-393
REF function, 4-70
REFERENCES clause
of constraint_clause, 7-241
of CREATE TABLE, 7-393
REFERENCES object privilege, 7-535
references to objects.
See
REFs
REFERENCING clause
of CREATE TRIGGER, 7-427,
7-432
referential integrity constraints, 7-239,
7-241
REFRESH clause
of ALTER MATERIALIZED VIEW, 7-48,
7-52
of CREATE MATERIALIZED VIEW / SNAPSHOT, 7-319
REFRESH COMPLETE clause
of ALTER MATERIALIZED VIEW, 7-52
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
REFRESH FAST clause
of ALTER MATERIALIZED VIEW, 7-52
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
REFRESH FORCE clause
of ALTER MATERIALIZED VIEW, 7-53
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
REFRESH ON COMMIT clause
of ALTER MATERIALIZED VIEW, 7-53
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
REFRESH ON DEMAND clause
of ALTER MATERIALIZED VIEW, 7-53
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
REFs, 2-31,
7-242
as containers for OIDs, 2-31
dangling, 7-206
validating, 7-206
REFTOHEX function, 4-70
REGR_AVGX function, 4-71
REGR_AVGY function, 4-71
REGR_COUNT function, 4-71
REGR_INTERCEPT function, 4-71
REGR_R2 function, 4-71
REGR_SLOPE function, 4-71
REGR_SXX function, 4-71
REGR_SXY function, 4-71
REGR_SYY function, 4-71
relational tables
creating, 7-381
RELY clause
of constraint_clause, 7-244
REMOTE_DEPENDENCIES_MODE parameter
of ALTER SESSION, 7-94
of ALTER SYSTEM, 7-117
REMOTE_LOGIN_PASSWORDFILE parameter
and control files, 7-264
and databases, 7-269
RENAME clause
of ALTER INDEX, 7-40
of ALTER OUTLINE, 7-63
of ALTER TABLE, 7-153
RENAME DATAFILE clause
of ALTER TABLESPACE, 7-179
RENAME FILE clause
of ALTER DATABASE, 7-6,
7-15
RENAME GLOBAL_NAME clause
of ALTER DATABASE, 7-15
RENAME PARTITION clause
of ALTER INDEX, 7-33,
7-41
of ALTER TABLE, 7-157
RENAME statement, 7-554
RENAME SUBPARTITION clause
of ALTER INDEX, 7-33,
7-41
of ALTER TABLE, 7-157
REPLACE AS OBJECT clause
of ALTER TYPE, 7-188
REPLACE function, 4-78
reserved words, 2-73,
C -1
RESET COMPATIBILITY clause
of ALTER DATABASE, 7-15
RESETLOGS parameter
of CREATE CONTROLFILE, 7-265
RESOLVE clause
of ALTER JAVA CLASS, 7-46
of CREATE JAVA, 7-312
RESOLVER clause
of ALTER JAVA CLASS, 7-46
of ALTER JAVA SOURCE, 7-46
of CREATE JAVA, 7-314
resource parameters
of CREATE PROFILE, 7-359
RESOURCE role, 7-530
RESOURCE_LIMIT parameter
of ALTER SYSTEM, 7-117
RESOURCE_MANAGER_PLAN parameter
of ALTER SYSTEM, 7-117
RESTRICT_REFERENCES pragma
of ALTER TYPE, 7-189
restricted rowids, 2-26
compatibility and migration of, 2-27
RESTRICTED SESSION system privilege, 7-525
RESUME clause
of ALTER SYSTEM, 7-108
RETURN clause
of CREATE FUNCTION, 7-288
of CREATE OPERATOR, 7-340
of CREATE TYPE BODY, 7-449
RETURNING clause
of DELETE, 7-467
of INSERT, 7-540,
7-544
of UPDATE, 7-616,
7-620
REUSE clause
of CREATE CONTROLFILE, 7-264
of filespec clause, 7-517
REUSE STORAGE clause
of TRUNCATE, 7-613
REVERSE clause
of CREATE INDEX, 7-300
reverse indexes, 7-300
REVOKE CONNECT THROUGH clause
of ALTER USER, 7-194,
7-195
REVOKE schema_object_privileges statement, 7-559
REVOKE system_privileges_and_roles statement, 7-556
REWRITE hint, 2-65
RM date format element, 2-46
RN number format element, 2-41
RNDS parameter
of PRAGMA RESTRICT_REFERENCES, 7-189
RNPS parameter
of PRAGMA RESTRICT_REFERENCES, 7-189
roles
authorization
by a password, 7-366
by an external service, 7-366
by the database, 7-366
by the enterprise directory service, 7-366
changing, 7-76
creating, 7-365
disabling
for the current session, 7-600,
7-601
enabling
for the current session, 7-600,
7-601
granting, 7-519
system privileges on, 7-525
to a user, 7-520
to all users, 7-520
to another role, 7-520
to PUBLIC, 7-520
removing from the database, 7-495
revoking, 7-556
from another role, 7-495,
7-557
from PUBLIC, 7-557
from users, 7-495,
7-557
rollback segments
bringing online, 7-78,
7-368
changing storage characteristics, 7-78
creating, 7-367
granting
system privileges on, 7-525
multiple, 7-368
public, 7-367
reducing size, 7-78
removing from the database, 7-496
specifying optimal size of, 7-608
specifying tablespaces for, 7-367
SQL examples, 7-368
storage characteristics, 7-368,
7-605
taking offline, 7-78
ROLLBACK statement, 7-564
ROLLUP operation
of queries and subqueries, 7-578
ROUND function
date function, 4-79
format models, 4-117
number function, 4-79
routines
calling, 7-226
executing, 7-226
ROW EXCLUSIVE lock mode, 7-548
ROW SHARE lock mode, 7-548
ROW_NUMBER function, 4-80
ROWID datatype, 2-26
ROWID hint, 2-65
ROWID pseudocolumn, 2-26,
2-27,
2-59
rowids
block portion of, 2-26
description of, 2-26
extended, 2-27
not directly available, 2-27
file portion of, 2-26
nonphysical, 2-27
of foreign tables, 2-27
of index-organized tables, 2-27
restricted, 2-26
compatibility and migration of, 2-27
row portion of, 2-26
uses for, 2-60
ROWIDTOCHAR function, 2-37,
4-81
ROWNUM pseudocolumn, 2-60
uses for, 2-61
rows
adding to a table, 7-540
allowing movement of between partitions, 7-383
inserting
into partitions, 7-542
into remote databases, 7-542
into subpartitions, 7-542
movement between partitions, 7-404
order of storage, 7-396
removing
from a cluster, 7-611
from a table, 7-611
from partitions and subpartitions, 7-466
from tables and views, 7-465
selecting in hierarchical order, 5-20
specifying constraints on, 7-241
stored in ascending order, 7-244
storing if in violation of constraints, 7-163,
7-245,
7-407
RPAD function, 4-82
RR date format element, 2-46,
2-49
interpreting, 2-49
RRRR date format element, 2-46
RTRIM function, 4-82
RULE hint, 2-64
run-time compilation
avoiding, 7-67,
7-198
S
S number format element, 2-41
SALES sample table, 4-3
SAMPLE clause
of SELECT, 7-574
of SELECT and subqueries, 7-570
SAVEPOINT statement, 7-567
savepoints
erasing, 7-230
rolling back to, 7-565
specifying, 7-567
scale
greater than precision, 2-18
negative, 2-18
of NUMBER datatype, 2-17
SCC date format element, 2-46
SCHEMA clause
of CREATE JAVA, 7-313
schema objects, 2-68
auditing
by access, 7-222
by session, 7-222
options, 7-223
successful
SQL statements on, 7-222
defining default buffer pool for, 7-609
dropping, 7-509
in other schemas, 2-79
list of, 2-68
name resolution, 2-78
namespaces, 2-73
naming
examples, 2-75
guidelines, 2-76
rules, 2-72
object types, 2-30
on remote databases, 2-79
partitioned indexes, 2-70
partitioned tables, 2-70
parts of, 2-70
reauthorizing, 6-2
recompiling, 6-2
referring to, 2-76,
7-87
remote, accessing, 7-273
stopping auditing of, 7-552
schemas
changing for a session, 7-87
creating, 7-369
definition of, 2-68
scientific notation, 2-42
SCOPE clause
of column ref constraints, 7-242
scope constraints, 7-242
segment attributes clause
of CREATE TABLE, 7-383,
7-393
SELECT
object privilege, 7-535
statement, 7-569
SELECT ANY SEQUENCE system privilege, 7-525
SELECT ANY TABLE system privilege, 7-527
select lists, 5-19
ordering, 5-21
SELECT statement, 5-19
SELECT_CATALOG_ROLE role, 7-530
self joins, 5-23
sequences, 2-56,
7-372
accessing values of, 7-372
changing
the increment value, 7-81
the number of cached values, 7-81
creating, 7-371
creating without limit, 7-373
granting
system privileges on, 7-525
how to use, 2-57
incrementing, 7-371,
7-373
maximum value
setting or changing, 7-81
minimum value
setting or changing, 7-81
ordering values, 7-81
recycling values, 7-81
removing from the database, 7-497
renaming, 7-554
restarting, 7-497
at a different number, 7-82
at a predefined limit, 7-373
reusing, 7-372
stopping at a predefined limit, 7-373
synonyms for, 7-377
where to use, 2-57
SERVERERROR event
triggers on, 7-431
service name
of remote database, 7-275
session control statements, 6-5
PL/SQL support of, 6-5
session locks
releasing, 7-107
SESSION_CACHED_CURSORS parameter
of ALTER SESSION, 7-94
SESSION_ROLES view, 7-600
sessions
calculating resource cost limits, 7-73
changing resource cost limits, 7-73
disconnecting, 7-106
global name resolution for, 7-88
granting
system privileges on, 7-525
limiting resource costs, 7-73
modifying characteristics of, 7-86
number of concurrent, 7-111
object cache, 7-91
restricted, 7-106
terminating, 7-107
SESSIONS_PER_USER parameter
of ALTER PROFILE, 7-69
of CREATE PROFILE, 7-361
SET clause
of ALTER SESSION, 7-86
of ALTER SYSTEM, 7-108
of UPDATE, 7-619
SET CONSTRAINT(S) statement, 7-598
SET DATABASE clause
of CREATE CONTROLFILE, 7-264
set operators, 3-12,
7-579
SET ROLE statement, 7-600
SET STATEMENT_ID clause
of EXPLAIN PLAN, 7-513
SET TRANSACTION statement, 7-602
SET UNUSED clause
of ALTER TABLE, 7-147
SGA.
See
system global area
SHARE ROW EXCLUSIVE lock mode, 7-548
SHARE UPDATE lock mode, 7-548
SHARED clause
of CREATE DATABASE LINK, 7-274
shared server processes
creating additional, 7-114
terminating, 7-114
SHRINK clause
of ALTER ROLLBACK SEGMENT, 7-79
SHUTDOWN event
triggers on, 7-431
SIGN function, 4-83
simple comparison conditions, 5-16
simple expressions, 5-3
SIN function, 4-83
SINGLE TABLE clause
of CREATE CLUSTER, 7-257
single-row functions, 4-4
miscellaneous, 4-6
SINH function, 4-84
SIZE clause
of ALTER CLUSTER, 7-4
of CREATE CLUSTER, 7-256
of filespec clause, 7-517
SKIP_UNUSABLE_INDEXES parameter
of ALTER SESSION, 7-94
SMALLINT datatype
ANSI, 2-28
DB2, 2-29
SQL/DS, 2-29
snapshot logs.
See
materialized view logs
snapshots.
See
materialized views
SNMPAGENT role, 7-530
SOME operator, 3-6
SORT_AREA_RETAINED_SIZE parameter
of ALTER SESSION, 7-95
of ALTER SYSTEM, 7-118
SORT_AREA_SIZE parameter
of ALTER SESSION, 7-95
of ALTER SYSTEM, 7-118
SORT_MULTIBLOCK_READ_COUNT parameter
of ALTER SESSION, 7-95
of ALTER SYSTEM, 7-118
SOUNDEX function, 4-84
SP date format element suffix, 2-51
SPECIFICATION clause
of ALTER PACKAGE, 7-65
spelled numbers
specifying, 2-51
SPLIT PARTITION clause
of ALTER INDEX, 7-34,
7-41
of ALTER TABLE, 7-161
SPTH date format element suffix, 2-51
SQL
description of, 1-2
embedded, 1-4
functions, 4-2
keywords, A-1
Oracle Tools support of, 1-5
parameters, A-1
standards, 1-2,
B - 1
statements
auditing, 7-215
determining the cost of, 7-512
syntax, 7-1,
A-1
SQL function
ACOS, 4-13
SQL functions
ABS, 4-12
ADD_MONTHS, 4-13
aggregate, 4-6
analytic, 4-7
ASCII, 4-14
ASIN, 4-15
ATAN, 4-15
ATAN2, 4-16
AVG, 4-16
BFILENAME, 4-17
CEIL, 4-18
character
returning character values, 4-4
returning number values, 4-5
CHARTOROWID, 4-19
CHR, 4-19
CONCAT, 4-20
conversion, 4-5
CONVERT, 4-21
CORR, 4-22
COS, 4-23
COSH, 4-24
COUNT, 4-24
CUME_DIST, 4-28
date, 4-5
DENSE_RANK, 4-29
DEREF, 4-30
DUMP, 4-31
EMPTY_BLOB, 4-33
EMPTY_CLOB, 4-33
EXP, 4-33
FIRST_VALUE, 4-34
FLOOR, 4-35
GREATEST, 4-36
GROUPING, 4-36
HEXTORAW, 4-37
INITCAP, 4-38
INSTR, 4-38
INSTRB, 4-39
LAG, 4-40
LAST_DAY, 4-41
LAST_VALUE, 4-42
LEAD, 4-44
LEAST, 4-45
LENGTH, 4-45
LENGTHB, 4-46
linear regression, 4-71
LN, 4-46
LOG, 4-47
LOWER, 4-47
LPAD, 4-48
LTRIM, 4-48
MAKE_REF, 4-49
MAX, 4-50
MIN, 4-52
MOD, 4-53
MONTHS_BETWEEN, 4-54
NEW_TIME, 4-55
NEXT_DAY, 4-55
NLS_CHARSET_DECL_LEN, 4-56
NLS_CHARSET_ID, 4-56
NLS_CHARSET_NAME, 4-57
NLS_INITCAP, 4-58
NLS_LOWER, 4-59
NLS_UPPER, 4-60
NLSSORT, 4-59
NLV2, 4-65
NTILE, 4-61
number, 4-4
NUMTODSINTERVAL, 4-62
NUMTOYMINTERVAL, 4-63
NVL, 4-64
object reference, 4-12
PERCENT_RANK, 4-66
POWER, 4-67
RANK, 4-67
RATIO_TO_REPORT, 4-68
RAWTOHEX, 4-69
REF, 4-70
REFTOHEX, 4-70
REGR_AVGX, 4-71
REGR_AVGY, 4-71
REGR_COUNT, 4-71
REGR_INTERCEPT, 4-71
REGR_R2, 4-71
REGR_SLOPE, 4-71
REGR_SXX, 4-71
REGR_SXY, 4-71
REGR_SYY, 4-71
REPLACE, 4-78
ROUND (date), 4-79
ROUND (number), 4-79
ROW_NUMBER, 4-80
ROWIDTOCHAR, 4-81
RPAD, 4-82
RTRIM, 4-82
SIGN, 4-83
SIN, 4-83
single-row, 4-4
miscellaneous, 4-6
SINH, 4-84
SOUNDEX, 4-84
SQRT, 4-85
STDDEV, 4-86
STDDEVP, 4-87
STDDEVS, 4-88
SUBSTR, 4-90
SUBSTRB, 4-91
SUM, 4-91
SYS_CONTEXT, 4-93
SYS_GUID, 4-97
SYSDATE, 4-98
TAN, 4-98
TANH, 4-99
TO_CHAR (date), 4-99
TO_CHAR (number), 4-100
TO_DATE, 4-101
TO_LOB, 4-102
TO_MULTI_BYTE, 4-103
TO_NUMBER, 4-103
TO_SINGLE_BYTE, 4-104
TRANSLATE, 4-105
TRANSLATE...USING, 4-106
TRIM, 4-107
TRUNC (date), 4-109
TRUNC (number), 4-108
UID, 4-109
UPPER, 4-110
USER, 4-110
USERENV, 4-111
VALUE, 4-112
VARIANCE, 4-115
VARP, 4-113
VARS, 4-114
VSIZE, 4-116
SQL statements
auditing
by access, 7-215
by proxy, 7-214
by session, 7-214
by user, 7-214
stopping, 7-550
successful, 7-215
determining the execution plan for, 7-512
rolling back, 7-564
tracking the occurrence in a session, 7-213
undoing, 7-564
SQL_TRACE parameter
of ALTER SESSION, 7-95
SQL92, 1-2
Oracle compliance with, B - 2
SQL/DS datatypes, 2-28
conversion to Oracle datatypes, 2-29
restrictions on, 2-30
SQRT function, 4-85
SS date format element, 2-46
SSSSS date format element, 2-46
standalone procedures
dropping, 7-493
standard SQL, B - 1
Oracle extensions to, B - 5
standby control file
creating, 7-20
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-118
STAR hint, 2-66
STAR_TRANSFORMATION hint, 2-67
STAR_TRANSFORMATION_ENABLED parameter
of ALTER SESSION, 7-95
START WITH clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-53
of CREATE SEQUENCE, 7-373
of SELECT, 7-577
of SELECT and subqueries, 7-571
STARTUP event
triggers on, 7-431
STATIC clause
of ALTER TYPE, 7-188
of CREATE TYPE, 7-442
of CREATE TYPE BODY, 7-448
statistics
computing exactly, 7-204
deleting from the data dictionary, 7-206
estimating, 7-204
forcing disassociation, 7-471
on indexes, 7-301
user-defined
dropping, 7-478,
7-480,
7-482,
7-492,
7-501,
7-506
statistics types
associating
with columns, 7-211
with datatypes, 7-212
with domain indexes, 7-212
with functions, 7-212
with indextypes, 7-212
with packages, 7-212
disassociating
from columns, 7-470
from domain indexes, 7-470
from functions, 7-470
from indextypes, 7-470
from packages, 7-470
from types, 7-470
STDDEV function, 4-86
STDDEVP function, 4-87
STDDEVS function, 4-88
storage characteristics
resetting, 7-611
STORAGE clause, 7-605
of ALTER CLUSTER, 7-4
of ALTER INDEX, 7-31,
7-36
of ALTER MATERIALIZED VIEW, 7-49
of ALTER MATERIALIZED VIEW LOG, 7-58
of ALTER ROLLBACK SEGMENT, 7-78,
7-79
of CREATE CLUSTER, 7-256
of CREATE INDEX, 7-300
of CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG, 7-334
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG.
See
CREATE TABLE
of CREATE MATERIALIZED VIEW/SNAPSHOT.
See
CREATE TABLE.
of CREATE ROLLBACK SEGMENTS, 7-368
of CREATE TABLE, 7-384,
7-394
of CREATE TABLESPACE, 7-420
STORAGE IN ROW clause
of ALTER TABLE, 7-141
STORE IN DEFAULT clause
of CREATE INDEX, 7-303
STORE IN tablespace clause
of CREATE INDEX, 7-303
stored functions, 7-285
Structured Query Language.
See
SQL
SUBPARTITION BY HASH clause
of CREATE TABLE, 7-387,
7-403
SUBPARTITION clause
of ANALYZE, 7-203
of CREATE INDEX, 7-303
of CREATE TABLE, 7-404
of DELETE, 7-466
of INSERT, 7-542
of LOCK TABLE, 7-548
of SELECT, 7-575
of UPDATE, 7-618
subpartition-extended table names, 2-70
in DML statements, 2-71
restrictions on, 2-71
syntax, 2-71
subpartitions
adding, 7-156
adding rows to, 7-540
allocating extents for, 7-150,
7-157
coalescing, 7-156
converting into nonpartitioned tables, 7-162
creating, 7-387,
7-404
deallocating unused space from, 7-150,
7-157
inserting rows into, 7-542
locking, 7-547
logging insert operations, 7-152
moving to a different segment, 7-158
physical attributes
changing, 7-145
removing rows from, 7-160,
7-466
renaming, 7-157
revising values in, 7-618
specifying, 7-403
SUBPARTITIONS clause
of CREATE TABLE, 7-403
subqueries, 5-19,
7-569,
7-572
containing subqueries, 5-25
correlated, 5-25
defined, 5-19
to insert table data, 7-409
SUBSTR function, 4-90
SUBSTRB function, 4-91
subtotal values
deriving, 7-578
SUM function, 4-91
SUSPEND clause
of ALTER SYSTEM, 7-107
sustained standby recovery mode, 7-14
terminating, 7-15
timeout period, 7-14
SWITCH LOGFILE clause
of ALTER SYSTEM, 7-107
SYEAR date format element, 2-46
synonyms
changing the definition of, 7-500
creating, 7-377
granting
system privileges on, 7-526
local, 7-379
private, dropping, 7-500
public, 7-378
dropping, 7-500
remote, 7-379
removing from the database, 7-500
renaming, 7-554
synonyms for, 7-377
syntax diagrams, A-1
explanation of, xx
keywords, xxi
loops, A-2
multipart diagrams, A-3
parameters, xxi
SYS schema
database triggers stored in, 7-433
functions stored in, 7-433
SYS_CONTEXT function, 4-93
SYS_GUID function, 4-97
SYSDATE function, 4-98
SYSDBA system privilege, 7-529
SYSOPER system privilege, 7-529
system control statements, 6-5
PL/SQL support of, 6-5
system date
altering, 7-110
system events
attributes of, 7-433
triggers on, 7-431
system global area
flushing, 7-107
updating, 7-105
system privileges
granting, 7-365,
7-519
to a role, 7-520
to a user, 7-520
to all users, 7-520
to PUBLIC, 7-520
list of, 7-521
revoking, 7-556
from a role, 7-557
from a user, 7-556
from PUBLIC, 7-557
from users, 7-557
SYYYY date format element, 2-46
T
TABLE clause
of DELETE, 7-467
of INSERT, 7-542
of SELECT, 7-576
of TRUNCATE, 7-612
of UPDATE, 7-617,
7-618
table constraints
defined, 7-233
of ALTER TABLE, 7-141
of CREATE TABLE, 7-393
table locks
disabling, 7-165
duration of, 7-547
enabling, 7-165
EXCLUSIVE, 7-548
modes of, 7-548
on partitions, 7-548
on remote database, 7-548
on subpartitions, 7-548
and queries, 7-547
ROW EXCLUSIVE, 7-548
ROW SHARE, 7-548
SHARE, 7-548
SHARE ROW EXCLUSIVE, 7-548
SHARE UPDATE, 7-548
table REF constraints, 7-234,
7-242
of ALTER TABLE, 7-140
of CREATE TABLE, 7-393
tables
adding rows to, 7-540
aliases, 2-82
in CREATE INDEX, 7-298
in DELETE, 7-467
allocating extents for, 7-150
assigning to a cluster, 7-401
changing degree of parallelism on, 7-164
changing existing values in, 7-616
collecting modification statistics on, 7-152
collecting statistics on, 7-203
creating, 7-390
multiple, 7-369
creating comments about, 7-228
deallocating unused space from, 7-150
default physical attributes
changing, 7-145
degree of parallelism
specifying, 7-390
disassociating statistics types from, 7-501
dropping
along with cluster, 7-473
along with owner, 7-510
indexes of, 7-501
partitions of, 7-501
granting
system privileges on, 7-526
index-organized
creating, 7-396
overflow segment for, 7-397
space in index block, 7-397
inserting rows with a subquery, 7-409
LOB storage of, 7-394
locking, 7-547
logging
insert operations, 7-152
table creation, 7-395
migrated and chained rows in, 7-207
moving to a new segment, 7-144
nested
creating, 7-444
storage characteristics, 7-400
object
creating, 7-381
order of row storage, 7-396
ordering rows from, 7-409
parallel creation of, 7-405
parallelism
setting default degree, 7-405
partition attributes of, 7-155
partitioned
allowing rows to move between partitions, 7-164
default attributes of, 7-155
partitioning of, 2-70,
7-390
physical attributes
changing, 7-145
specifying, 7-393
relational
creating, 7-381
remote, accessing, 7-273
removing from the database, 7-501
removing rows from, 7-465
renaming, 7-153,
7-554
restricting
records per block, 7-153
references to, 7-242
retrieving data from, 7-572
rows
ordering by primary key, 7-396
saving blocks in a cache, 7-151,
7-408
SQL examples, 7-410
storage characteristics, 7-605
defining, 7-390,
7-394
subpartition attributes of, 7-155
synonyms for, 7-377
tablespace for
defining, 7-390,
7-394
temporary
duration of data, 7-401
session-specific, 7-391
transaction specific, 7-391
unclustering, 7-472
validating structure of, 7-206
with unusable indexes, 7-94
TABLESPACE clause
of CREATE CLUSTER, 7-257
of CREATE INDEX, 7-300
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-322
of CREATE ROLLBACK SEGMENTS, 7-367
of CREATE TABLE, 7-394
tablespaces, 7-180
allocating space for users, 7-453
allowing write operations on, 7-181
backing up datafiles of, 7-180
bringing online, 7-180,
7-422
coalescing free extents, 7-181
converting
from permanent to temporary, 7-181
from temporary to permanent, 7-181
creating, 7-420
datafile
adding, 7-179
renaming, 7-179
default storage characteristics, 7-605
defining as read only, 7-181
designing media recovery, 7-12
dropping the contents of, 7-504
enable autoextension of, 7-179
extent management of, 7-425
extent size of, 7-421
granting
system privileges on, 7-527
locally managed, 7-422,
7-606
temporary, 7-425
logging attribute of, 7-178,
7-421
managed using dictionary tables, 7-422
managing extents of, 7-422
of session duration, 7-424
permanent objects in, 7-422
recovering, 7-14
removing from the database, 7-503
size of free extents in, 7-180
specifying
datafiles for, 7-421
for a user, 7-453
for index rebuild, 7-145
taking offline, 7-180,
7-422
tempfile
adding, 7-179
temporary
creating, 7-424
specifying for a user, 7-453
temporary objects in, 7-422
TAN function, 4-98
TANH function, 4-99
TEMPFILE clause
of ALTER DATABASE, 7-8,
7-17
of CREATE TEMPORARY TABLESPACE, 7-425
tempfiles
automatic extension of, 7-425
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-517
size of, 7-517
specifying, 7-425,
7-516
taking offline, 7-17
TEMPORARY clause
of ALTER TABLESPACE, 7-181
of CREATE TABLESPACE, 7-422
temporary tables
creating, 7-390,
7-391
session-specific, 7-391
transaction-specific, 7-391
TEMPORARY TABLESPACE clause
of ALTER USER.
See
CREATE USER
of CREATE USER, 7-453
temporary tablespaces
creating, 7-424
specifying for a user, 7-453
SQL examples, 7-425
text
conventions, xx
date and number formats, 2-38
in SQL syntax, 2-2
properties of CHAR and VARCHAR2 datatypes, 2-2
syntax of, 2-2
text date format element, 2-46
TH date format element suffix, 2-51
THSP date format element suffix, 2-51
TIME datatype (SQL/DS or DB2), 2-30
TIMED_OS_STATISTICS parameter
of ALTER SYSTEM, 7-118
TIMED_STATISTICS parameter
of ALTER SESSION, 7-95
of ALTER SYSTEM, 7-118
TIMESTAMP datatype (SQL/DS or DB2), 2-30
TM number format element, 2-41
TO PUBLIC clause
of GRANT object_privileges, 7-534
of GRANT system_privileges_and_roles, 7-520
TO role clause
of GRANT object_privileges, 7-534
of GRANT system_privileges_and_roles, 7-520
TO SAVEPOINT clause
of ROLLBACK, 7-565
TO user clause
of GRANT object_privileges, 7-534
of GRANT system_privileges_and_roles, 7-520
TO_CHAR
date conversion function, 4-99
number conversion function, 4-100
TO_CHAR function, 2-37,
2-40,
2-45,
2-51
TO_DATE function, 2-37,
2-45,
2-49,
2-51,
4-101
TO_LOB function, 2-37,
4-102
TO_MULTI_BYTE function, 4-103
TO_NUMBER function, 2-37,
2-40,
4-103
TO_SINGLE_BYTE function, 4-104
top-N queries, 2-61
transaction control statements, 6-4
PL/SQL support of, 6-4
TRANSACTION_AUDITING parameter
of ALTER SYSTEM, 7-118
transactions
allowing to complete, 7-106
assigning
rollback segment to, 7-602
to a specific rollback segment, 7-603
automatically committing, 7-230
commenting on, 7-231
distributed, forcing, 7-84
ending, 7-230
implicit commit of, 6-2,
6-4,
6-5
in-doubt
committing, 7-230
forcing, 7-231
isolation level, 7-602
locks, releasing, 7-230
read-committed isolation mode, 7-603
read-only, 7-602
read-write, 7-602
rolling back, 7-107,
7-367,
7-564
to a savepoint, 7-565
savepoints for, 7-567
serializable isolation mode, 7-603
TRANSLATE ... USING function, 4-106
TRANSLATE function, 4-105
triggers
AFTER, 7-428
BEFORE, 7-428
compiling, 7-183
creating, 7-427
creating multiple, 7-429
database
altering, 7-183
dropping, 7-505,
7-510
disabling, 7-165,
7-183
enabling, 7-165,
7-183,
7-427
executing
with a PL/SQL block, 7-433
with an external procedure, 7-433
granting
system privileges on, 7-527
INSTEAD OF, 7-429
dropping, 7-458
on database events, 7-431
on DDL events, 7-430
on DML operations, 7-429
on views, 7-429
order of firing, 7-429
re-creating, 7-428
removing from the database, 7-505
restrictions on, 7-433
row values
old and new, 7-432
row, specifying, 7-432
SQL examples, 7-434
statement, 7-432
TRIM function, 4-107
TRUNC function
date function, 4-109
format models, 4-117
number function, 4-108
TRUNCATE PARTITION clause
of ALTER TABLE, 7-160
TRUNCATE statement, 7-611
TRUNCATE SUBPARTITION clause
of ALTER TABLE, 7-160
TRUST parameter
of PRAGMA RESTRICT_REFERENCES, 7-189
Trusted Oracle, 1-5
type constructor expressions, 5-6
TYPES clause
of ASSOCIATE STATISTICS, 7-210,
7-212
types.
See
object types or datatypes
U
U number format element, 2-41
UID function, 4-109
unary operators, 3-1
UNION ALL set operator, 3-13,
7-579
UNION set operator, 3-13,
7-579
UNIQUE clause
of constraint_clause, 7-238
of CREATE INDEX, 7-296
of CREATE TABLE, 7-393
of SELECT, 7-573
unique constraints
enabling, 7-407
index on, 7-407
unique indexes, 7-296
unique queries, 7-573
universal rowids.
See
urowids
UNLIMITED TABLESPACE system privilege, 7-527
unnesting collections, 7-576
examples, 7-593
UNRECOVERABLE, 7-37,
7-396
See also
NOLOGGING clause
unsorted indexes, 7-300
UNUSABLE clause
of ALTER INDEX, 7-40
UNUSABLE LOCAL INDEXES clause
of ALTER MATERIALIZED VIEW, 7-51
of ALTER TABLE, 7-157
UPDATE ANY TABLE system privilege, 7-527
UPDATE object privilege, 7-535
UPDATE statement, 7-615
triggers on, 7-429
UPPER function, 4-110
UROWID datatype, 2-27
urowids
and foreign tables, 2-27
and heap-organized tables, 2-28
and index-organized tables, 2-27
description of, 2-27
USE ROLLBACK SEGMENT clause
of SET TRANSACTION, 7-603
USE_CONCAT hint, 2-65
USE_HASH hint, 2-66
USE_MERGE hint, 2-66
USE_NL hint, 2-66
USE_STORED_OUTLINES parameter
of ALTER SESSION, 7-95
of ALTER SYSTEM, 7-118
USER function, 4-110
USER_COL_COMMENTS view, 7-228
USER_DUMP_DEST parameter
of ALTER SYSTEM, 7-119
USER_TAB_COMMENTS view, 7-228
user-defined functions, 4-118
name precedence of, 4-119
naming conventions, 4-120
restrictions on, 7-286
user-defined operators, 3-16
user-defined statistics
dropping, 7-478,
7-480,
7-482,
7-492,
7-501,
7-506
user-defined types
categories of, 2-30
defining, 7-440
USERENV function, 4-111
users
allocating space for, 7-453
assigning
default roles, 7-195
profiles, 7-453
authenticating to a remote server, 7-275
changing global authentication, 7-195
creating, 7-451
default tablespaces of, 7-453
denying access to tables and views, 7-547
external, 7-366,
7-452
global, 7-366,
7-452
granting
system privileges on, 7-528
local, 7-366,
7-452
locking accounts of, 7-453
maximum concurrent, 7-112
password expiration of, 7-453
removing from the database, 7-509
SQL examples, 7-453
temporary tablespaces for, 7-453
USING BFILE clause
of CREATE JAVA, 7-314
USING BLOB clause
of CREATE JAVA, 7-314
USING clause
of ASSOCIATE STATISTICS, 7-211,
7-212
of CREATE DATABASE LINK, 7-275
of CREATE INDEXTYPE, 7-310
of CREATE OPERATOR, 7-339,
7-341
USING CLOB clause
of CREATE JAVA, 7-314
USING INDEX clause
of ALTER MATERIALIZED VIEW, 7-52
of constraint_clause, 7-244
of CREATE MATERIALIZED VIEW/SNAPSHOT, 7-324
of CREATE TABLE, 7-389,
7-407
USING ROLLBACK SEGMENT clause
of ALTER MATERIALIZED VIEW...REFRESH, 7-53
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-326
UTLCHN.SQL script, 7-207
UTLEXPT1.SQL script, 7-163,
7-245,
7-407
UTLXPLAN.SQL script, 7-512
V
V number format element, 2-41
V$NLS_PARAMETERS view
VALIDATE REF UPDATE clause
of ANALYZE, 7-206
VALIDATE STRUCTURE clause
of ANALYZE, 7-206
VALUE function, 4-112
VALUES clause
of CREATE INDEX, 7-302
of INSERT, 7-542
VALUES LESS THAN clause
of CREATE TABLE, 7-403
VARCHAR datatype, 2-17
DB2, 2-29
SQL/DS, 2-29
VARCHAR2 datatype, 2-16
converting to NUMBER, 2-40
VARGRAPHIC datatype (SQL/DS or DB2), 2-30
variable expressions, 5-4
VARIANCE function, 4-115
VARP function, 4-113
VARRAY storage clause
of ALTER TABLE, 7-142
of CREATE TABLE, 7-385,
7-399
varrays, 2-31
changing returned value, 7-145
compared with nested tables, 2-35
comparison rules, 2-35
creating, 7-439,
7-444
dropping the body of, 7-508
dropping the specification of, 7-506
storage characteristics of, 7-142,
7-146,
7-399
storing out of line, 2-31
VARS function, 4-114
varying arrays.
See
varrays
views
adding rows to the base table of, 7-540
changing
definition, 7-511
values in base tables, 7-616
creating
before base tables, 7-458
comments about, 7-228
multiple, 7-369
defining, 7-456
granting
system privileges on, 7-529
recompiling, 7-198
re-creating, 7-458
remote, accessing, 7-273
removing
from the database, 7-511
rows from the base table of, 7-465
renaming, 7-554
retrieving data from, 7-572
subquery of, 7-459
restricting, 7-461
synonyms for, 7-377
VSIZE function, 4-116
W
W date format element, 2-46
WHEN clause
of CREATE TRIGGER, 7-433
WHENEVER NOT SUCCESSFUL clause
of NOAUDIT schema_objects, 7-553
WHENEVER SUCCESSFUL clause
of AUDIT schema_objects, 7-222
of AUDIT sql_statements, 7-215
of NOAUDIT schema_objects, 7-553
of NOAUDIT sql_statements, 7-551
WHERE clause
of DELETE, 7-467
of SELECT, 5-20,
7-577
of UPDATE, 7-620
WITH ADMIN OPTION clause
of GRANT system_privileges_and_roles, 7-521
WITH CHECK OPTION clause
of CREATE VIEW, 7-456,
7-461
of DELETE, 7-467
of INSERT, 7-542
of SELECT, 7-570,
7-575
of UPDATE, 7-618
WITH GRANT OPTION clause
of GRANT object_privileges, 7-534
WITH INDEX CONTEXT clause
of CREATE OPERATOR, 7-339,
7-341
WITH OBJECT IDENTIFIER clause
of CREATE VIEW, 7-459
WITH OBJECT OID.
See
WITH OBJECT IDENTIFIER.
WITH PRIMARY KEY clause
of ALTER MATERIALIZED VIEW, 7-53
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-324
WITH READ ONLY clause
of CREATE VIEW, 7-456,
7-461
of DELETE, 7-467
of INSERT, 7-542
of SELECT, 7-570,
7-575
of UPDATE, 7-618
WITH ROWID clause
of column ref constraints, 7-242
of CREATE MATERIALIZED VIEW LOG/SNAPSHOT LOG, 7-336
of CREATE MATERIALIZED VIEW/SNAPSHOT...REFRESH, 7-324
WNDS parameter
of PRAGMA RESTRICT_REFERENCES, 7-189
WNPS parameter
of PRAGMA RESTRICT_REFERENCES, 7-189
WW date format element, 2-46
X
X number format element, 2-41
Y
Y date format element, 2-46
YEAR date format element, 2-46
YY date format element, 2-46
YYY date format element, 2-46
YYYY date format element, 2-46
Copyright © 1999 Oracle Corporation.
All Rights Reserved.
Library
Product
Contents