Oracle8 SQL Reference
Release 8.0
A58225-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
Z
. (period) number format element, 3-65
Symbols
!= comparison operator, 3-5
$ format element, 3-65
% character
in pattern matching, 3-9
(+) outer join operator, 3-16
, (comma) format element, 3-65
< comparison operator, 3-5
<= comparison operator, 3-5
= comparison operator, 3-5
> comparison operator, 3-5
>= comparison operator, 3-5
^= comparison operator, 3-5
_ character
in pattern matching, 3-9
" double quotation marks
with object names, 2-49
Numerics
0 format element, 3-65
9 format element, 3-65
A
ABS numeric function, 3-18
AD/A.D. format element, 3-72
ADD clause
of ALTER SNAPSHOT LOG command, 4-87
of ALTER TABLE command, 4-117
ADD DATAFILE clause
of ALTER TABLESPACE command, 4-138
ADD LOGFILE clause
of ALTER DATABASE command, 4-20
ADD LOGFILE MEMBER clause
of ALTER DATABASE command, 4-21
ADD OVERFLOW option
of ALTER TABLE command, 4-118
ADD PARTITION option
of ALTER SNAPSHOT command, 4-80,
4-86
of ALTER TABLE command, 4-122
add_column_options_clause
of ALTER TABLE, 4-110
ADD_MONTHS date function, 3-36
add_overflow_clause
of ALTER TABLE, 4-114
add_partition_clause
of ALTER TABLE, 4-115
adding
columns to tables, 4-107,
4-117,
4-123
Comments to objects, 4-184
datafiles, 4-138
datafiles to databases, 4-220,
4-224
datafiles to tablespaces, 4-330
integrity constraints to columns, 4-117,
4-124
integrity constraints to tables, 4-117,
4-124
members to redo log file groups, 4-21
procedures to packages, 4-252
redo log file groups to threads, 4-20
redo log files to databases, 4-220,
4-222
REF columns to tables, 4-128
resource limits to profiles, 4-44,
4-266
stored functions to packages, 4-252
tables to clusters, 4-214,
4-319
triggers to tables, 4-334
ADMIN OPTION
of GRANT command, 4-444
ADVISE clause
of ALTER SESSION command, 4-63
AFTER option
of CREATE TRIGGER command, 4-336
alias
table, 4-550
ALL comparison operator, 3-5
ALL option
of ARCHIVE LOG clause, 4-169
of SELECT command, 4-494
of SET ROLE command, 4-519
of SQL group functions, 3-57
ALL PRIVILEGES option
of GRANT command, 4-448
of REVOKE command, 4-482
ALL statement auditing short cut, 4-178
ALL TRIGGERS option
of DISABLE clause, 4-382
of ENABLE clause, 4-422
ALL_INDEXES view, 4-162
ALL_TAB_COLUMNS view, 4-164
ALL_TABLES view, 4-162
ALLOCATE EXTENT clause
of ALTER CLUSTER command, 4-12
of ALTER TABLE command, 4-119
allocate_extent_clause
of ALTER CLUSTER, 4-12
of ALTER INDEX, 4-32
of ALTER TABLE, 4-113
allocating
extents for tables, 4-107
ALTER CLUSTER
deallocate_unused_clause. See DEALLOCATE UNUSED Clause.
physical_attributes_clause, 4-11
ALTER CLUSTER command, 4-11
allocate_extent_clause, 4-12
deallocate_unused_clause. See DEALLOCATE UNUSED Clause.
ALTER DATABASE command, 4-15
autoextend_clause, 4-19
examples, 4-24,
4-25,
4-26,
4-474
recover_clause. See RECOVER Clause.
ALTER FUNCTION command, 4-27
examples, 4-28
ALTER INDEX command, 4-29
allocate_extent_clause, 4-32
deallocate_unused_clause. See DEALLOCATE UNUSED clause.
examples, 4-36
index_physical_attributes_clause, 4-31
parallel_clause. See PARALLEL Clause.
partition_description_clause, 4-32
split_partition_clause, 4-32
storage_clause. See STORAGE clause.
ALTER object auditing option, 4-175
ALTER object privilege
on sequences, 4-451
on tables, 4-450
ALTER PACKAGE command
examples, 4-40,
4-41
ALTER PROCEDURE command, 4-42
examples, 4-43
ALTER PROFILE command, 4-44
examples, 4-47,
4-48
ALTER RESOURCE COST command, 4-49
examples, 4-50
ALTER ROLE command, 4-52
examples, 4-53
ALTER ROLLBACK SEGMENT command, 4-54
examples, 4-56
storage_clause. See STORAGE clause.
ALTER SEQUENCE command, 4-57
examples, 4-58
ALTER SESSION command, 4-59
examples, 4-68,
4-69,
4-70,
4-71,
4-74
ALTER SNAPSHOT command, 4-77
add_partition_clause. See ALTER TABLE command.
examples, 4-82
LOB_storage_clause. See ALTER TABLE command.
modify_default_attributes_clause. See ALTER TABLE command.
modify_LOB_storage_clause. See ALTER TABLE command.
modify_partition_clause. See ALTER TABLE command.
move_partition_clause. See ALTER TABLE command.
parallel_clause. See PARALLEL clause.
physical_attributes_clause. See ALTER TABLE command.
rename_partition_clause. See ALTER TABLE command.
split_partition_clause. See ALTER TABLE command.
storage_clause. See STORAGE clause.
ALTER SNAPSHOT LOG command, 4-85
add_partition_clause. See ALTER TABLE command.
examples, 4-87
modify_default_attributes_clause. See ALTER TABLE command.
modify_partition_clause. See ALTER TABLE command.
move_partition_clause. See ALTER TABLE command.
physical_attributes_clause. See ALTER TABLE command.
rename_partition_clause. See ALTER TABLE command.
split_partition_clause. See ALTER TABLE command.
ALTER SYSTEM command, 4-89
archive_log_clause. See ARCHIVE LOG clause.
dispatch_clause, 4-93
examples, 4-99,
4-101,
4-103,
4-105
opts_clause, 4-94
set_clause, 4-91
ALTER TABLE command, 4-107
add_column_options_clause, 4-110
add_overflow_clause, 4-114
add_partition_clause, 4-115
allocate_extent_clause, 4-113
column_constraint, table_constraint. See CONSTRAINT clause.
column_ref_clause, 4-110
deallocate_unused_clause. See DEALLOCATE UNUSED clause.
drop_clause. See DROP clause.
examples, 4-125
exchange_partition_clause, 4-116
index_organized_table_clauses, 4-113
LOB_index_clause, 4-112
LOB_parameters, 4-111
LOB_storage_clause, 4-111
modify_column_options_clause, 4-110
modify_LOB_index_clause, 4-113
modify_LOB_storage_clause, 4-112
modify_partition_clause, 4-115
move_partition_clause, 4-115,
4-116
nested_table_storage_clause, 4-113
overflow_clause, 4-114
parallel_clause. See PARALLEL clause.
partitioning_clauses, 4-114
physical_attributes_clause, 4-111
segment_partition_clause, 4-116
split_partition_clause, 4-116
storage_clause. See STORAGE clause.
table_ref_clause, 4-110
ALTER TABLESPACE command, 4-134
autoextend_clause, 4-137
examples, 4-140
filespec. See "Filespec".
storage_clause. See STORAGE clause.
ALTER TRIGGER command, 4-142
examples, 4-143
ALTER TYPE
examples, 4-148
ALTER TYPE command, 4-145
pragma_clause, 4-146
ALTER USER command, 4-151
examples, 4-153
ALTER VIEW command, 4-155
examples, 4-156
altering
costs of resources, 4-49
databases, 4-15
indexes, 4-32
profiles, 4-44
sequences, 4-57
snapshot logs, 4-85
snapshots, 4-77
tables, 4-107
AM/A.M. format element, 3-72
American National Standards Committee (ANSI), 1-2
ANALYZE command, 4-157
examples, 4-164,
4-165
for_clause, 4-158
AND logical operator, 3-11
in a condition, 3-91
truth table, 3-12
ANSI
American National Standards Institute, 1-2
datatypes, 2-20
X3.135-1992, 1-2
ANY comparison operator, 3-5
application failover
See
DISCONNECT SESSION clause of ALTER SYSTEM command
ARCHIVE LOG clause, 4-168
examples, 4-170
of ALTER SYSTEM command, 4-97
ARCHIVELOG option
of ALTER DATABASE command, 4-20
of CREATE CONTROLFILE command, 4-218
of CREATE DATABASE command, 4-223
archiving redo log files
disabling, 4-20
enabling, 4-20
arithmetic
with DATE values, 2-14
arithmetic operator, 3-3
AS clause
of CREATE SNAPSHOT command, 4-292
of CREATE TABLE command, 4-321
of CREATE VIEW command, 4-366
AS EXTERNAL clause
of CREATE FUNCTION command, 4-236
AS OBJECT option
of CREATE TYPE command, 4-350
AS TABLE option
of CREATE TYPE command, 4-350
AS VARRAY option
of CREATE TYPE command, 4-350
ASC option
of CREATE INDEX command, 4-241
of ORDER BY clause, 4-496
ASCII
and EBCDIC, 3-4
character function, 3-33
character set, 2-26
attribute reference
expression syntax, 3-88
attribute_name parameter
of ALTER TYPE command, 4-146
of CREATE TYPE command, 4-350
attributes, 2-23
AUDIT command, 4-171,
4-179
examples, 4-176,
4-182
AUDIT object auditing option, 4-175
AUDIT_TRAIL, 4-172
AUTHENTICATED BY clause
of CREATE DATABASE LINK command, 4-227
authenticated_clause
of CREATE DATABASE LINK, 4-227
AUTOEXTEND
datafile size in tablespace, 4-138,
4-330
AUTOEXTEND clause
of ALTER DATABASE command, 4-24
of CREATE DATABASE command, 4-224
autoextend_clause
of ALTER DATABASE, 4-19
of ALTER TABLESPACE, 4-137
of CREATE DATABASE, 4-221
of CREATE TABLESPACE, 4-330
AUTOMATIC option
of RECOVER clause, 4-473
AVG group function, 3-57
B
B format element, 3-65
BACKUP CONTROLFILE clause
of ALTER DATABASE command, 4-22
BC/B.C. format element, 3-72
BEFORE option
of CREATE TRIGGER command, 4-335
BEGIN BACKUP option
of ALTER TABLESPACE command, 4-139
BETWEEN comparison operator, 3-5
BFILE datatype, 2-17
BFILENAME function, 3-50
BFILEs
accessing, 4-232
BITMAP option
of CREATE INDEX command, 4-241
blank-padded comparison semantics, 2-25
BLOB datatype, 2-17
block size
effect on PCTINCREASE, 4-528
BODY option
of ALTER PACKAGE command, 4-39
of COMPILE clause
of ALTER TYPE command, 4-146
of DROP PACKAGE command, 4-395
BUFFER_POOL option
of STORAGE clause, 4-529
BY ACCESS option
of AUDIT command, 4-172,
4-180
BY clause
of AUDIT command, 4-172
of NOAUDIT command, 4-464
BY SESSION option
of AUDIT command, 4-172,
4-180
C
C format element, 3-65
CACHE option
of CREATE TABLE, 4-321
CACHE parameter
of CREATE SEQUENCE command, 4-283,
4-285
CACHE_INSTANCES parameter
of ALTER SYSTEM command, 4-96
CALLING STANDARD clause
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
CANCEL option
of RECOVER clause, 4-474
capitalizing
date format elements, 3-74
cartesian product, 4-510
CASCADE CONSTRAINT option
of REVOKE (Schema Object Privileges) command, 4-482
CASCADE option
of ANALYZE command, 4-160
of DISABLE clause, 4-382
of DROP clause, 4-385
case sensitivity
in pattern matching, 3-9
in SQL statements, 1-5
CAST expression, 3-84
CAST operator
expression syntax, 3-83
CEIL number function, 3-19,
3-20
century
storing, 2-14
change display format
format model, 3-63
CHANGE parameter
of ARCHIVE LOG clause, 4-169
changing
goal for the cost-based optimization approach, 4-72
optimization approach, 4-72
passwords, 4-151
CHAR datatype, 2-8
comparing values of, 2-25
character
comparison rules, 2-25
datatypes, 2-7
literal, 2-2
operator, 3-4
SQL functions, 3-25
CHARACTER SET parameter
of CREATE DATABASE command, 4-223
character sets, ASCII and EBCDIC, 2-26
CHARTOROWID conversion function, 3-42
CHECK constraints, 3-40,
4-202
CHECKPOINT clause
of ALTER SYSTEM command, 4-95,
4-98
CHR character function, 3-25
CHUNK option
of LOB storage clause, 4-319
CHUNK parameter
of LOB storage clause
of ALTER TABLE command, 4-119
CLEAR LOGFILE clause
of ALTER DATABASE command, 4-21
CLOB datatype, 2-18
CLONE DATABASE option
of ALTER DATABASE command, 4-19
CLOSE DATABASE LINK option
of ALTER SESSION command, 4-63
CLOSE_OPEN_CACHED_CURSORS parameter
of ALTER SESSION command, 4-64
closing
database links, 4-73
CLUSTER clause
of CREATE SNAPSHOT command, 4-290
of CREATE TABLE command, 4-319
cluster keys, 4-212
distinct values of, 4-212
CLUSTER option
of ANALYZE command, 4-159
of CREATE INDEX command, 4-241
of TRUNCATE command, 4-542
clusters, 4-208
adding snapshots to, 4-290
adding tables to, 4-214,
4-319
altering, 4-11
cluster indexes, 4-246
creating, 4-208
definition, 4-211
dropping, 4-387
indexed clusters, 4-212
order of columns in, 4-212
physical storage of tables in, 4-211
removing tables from, 4-387
size, 4-213
specifying tablespaces for, 4-210
storage characteristics of, 4-11,
4-209,
4-210
truncating, 4-541
column constraints, 4-189
column_ref_clause
of ALTER TABLE, 4-110
of CREATE TABLE, 4-310
columns
adding Comments to, 4-184
adding integrity constraints to, 4-117,
4-124
adding to tables, 4-107,
4-117,
4-123
changing datatypes of, 4-117,
4-124
changing default values of, 4-117,
4-124
defining, 4-313
maximum number in a table, 4-313
maximum number in indexes, 4-244
of cluster keys, 4-212
order in indexes, 4-244
qualifying names with tables and schemas, 4-496
redefining, 4-107,
4-117,
4-124
removing Comments from, 4-184
removing integrity constraints from, 4-117,
4-124
renaming, 4-477
selecting from tables, 4-492
specifying datatypes for, 4-314
vs. pseudocolumns, 2-32
COMMENT clause
of COMMIT command, 4-186
COMMENT command, 4-184
examples, 4-184
COMMENT object auditing option, 4-175
Comments
adding to objects, 4-184
examples, 2-39
removing from objects, 4-184
within SQL statements, 2-38
COMMIT command, 4-186
ending a transaction, 4-488,
4-524
examples, 4-187
summary, 4-8
COMMIT option
of ADVISE clause, 4-63
committing
transactions, 4-186
comparison operators, 3-5
comparison rules, 2-24
comparison semantics
blank-padded, 2-25
non-padded, 2-25
COMPILE option
of ALTER FUNCTION command, 4-27,
4-39
of ALTER PROCEDURE command, 4-42
of ALTER TYPE command, 4-146
of ALTER VIEW command, 4-155
compiling
procedures, 4-42
stored functions, 4-27
type bodies, 4-146
type specifications, 4-146
COMPLETE option
of REFRESH clause, 4-81,
4-291
complex snapshots, 4-292
CONCAT character function, 3-4
condition
example, 3-90
multiple, 3-91
syntax, 3-90
CONNECT BY clause
examples, 4-500
of SELECT command, 4-495,
4-500,
4-501
CONNECT role, 4-274
CONNECT statement auditing short cut, 4-177
CONNECT TO clause
of CREATE DATABASE LINK command, 4-227
CONNECT_TIME parameter
of CREATE PROFILE command, 4-268
constant
literal, 2-2
CONSTRAINT clause
examples, 4-199,
4-201
foreign_key_clause, 4-190
index_physical_attributes_clause, 4-191
storage_clause. See STORAGE clause.
CONSTRAINT identifier
of CONSTRAINT clause, 4-191
of WITH CHECK OPTION clause, 4-367
CONSTRAINT option
of DISABLE clause, 4-382
of DROP clause, 4-385
constraint states, 4-206,
4-422
CONSTRAINT(S) DEFAULT option
of ALTER SESSION command, 4-64
CONSTRAINT(S) DEFERRED option
of ALTER SESSION command, 4-64
constraints
deferred, 4-205
ENABLE NOVALIDATE, 4-193,
4-422
ENABLE VALIDATE, 4-193,
4-421
enabling disabled constraints, 4-423
Integrity constraints, 4-189
on object type columns, 4-327
CONTINUE option
of RECOVER clause, 4-474
CONVERT conversion function, 3-42
CONVERT option
of ALTER DATABASE command, 4-19
converting
strings to dates, 3-77
converting values, 2-28
explicitly, 2-29,
2-30
implicitly, 2-28,
2-30
correlated subqueries, 4-495,
4-537
correlated update, 4-550
COSH number function, 3-20
cost
of executing SQL statements, 4-430
COUNT group function, 3-58
CPU_PER_CALL parameter
of CREATE PROFILE command, 4-268
create
object view, 4-364
CREATE CLUSTER command, 4-208
examples, 4-214
parallel_clause, 4-208
parallel_clause. See PARALLEL clause.
physical_attributes_clause, 4-209
storage clause. See STORAGE clause.
CREATE CONTROLFILE command, 4-216
examples, 4-219
filespec. See "FILESPEC".
CREATE DATABASE command, 4-220
autoextend_clause, 4-221
CREATE DATABASE LINK command, 4-226
authenticated_clause, 4-227
CREATE DIRECTORY command, 4-231
CREATE FUNCTION command, 4-233
CREATE INDEX command, 4-238
global_index_clause, 4-239
global_partition_clause, 4-240
index_physical_attributes_clause, 4-240
local_index_clause, 4-240
parallel clause. See PARALLEL clause.
storage_clause. See STORAGE clause.
CREATE LIBRARY command, 4-249
filespec. See "Filespec".
CREATE PACKAGE BODY command, 4-255
examples, 4-256
CREATE PACKAGE command, 4-251
examples, 4-253
CREATE PROCEDURE command, 4-260
CREATE PROFILE command, 4-266
examples, 4-270
CREATE ROLE command, 4-273
examples, 4-275
CREATE ROLLBACK SEGMENT command, 4-276
examples, 4-277
storage_clause. See STORAGE clause.
CREATE SCHEMA command, 4-279
examples, 4-280
CREATE SEQUENCE command, 4-282
examples, 4-286
CREATE SNAPSHOT command, 4-287
examples, 4-294
index_physical_attributes_clause. See ALTER TABLE command
LOB_storage_clause. See CREATE TABLE command
parallel_clause. See PARALLEL clause.
physical_attributes_clause. See ALTER TABLE command
select_command. See SELECT command
table_partition_clause. See CREATE TABLE command
CREATE SNAPSHOT LOG command, 4-298
examples, 4-301
LOB_storage_clause. See CREATE TABLE command
parallel_clause. See PARALLEL clause.
physical_attributes_clause. See CREATE TABLE command
storage_clause. See STORAGE clause.
table_partition_clause. See CREATE TABLE command
CREATE STANDBY CONTROLFILE option
of ALTER DATABASE command, 4-22
CREATE SYNONYM command, 4-303
examples, 4-305
CREATE TABLE command, 4-307
column_ref_clause, 4-310
disable_clause. See DISABLE clause.
enable_clause. See ENABLE clause.
examples, 4-322
index_organized_table_clause, 4-311
LOB_index_clause, 4-312
LOB_storage_clause, 4-311
nested_table_storage_clause, 4-313
parallel_clause. See PARALLEL clause.
physical_attributes_clause, 4-311
segment_attributes_clause, 4-310
storage_clause. See STORAGE clause.
subquerye. See "Subqueries".
table_partition_clause, 4-313
table_ref_clause, 4-310
CREATE TABLESPACE command, 4-329
autoextend_clause, 4-330
examples, 4-332
filespec, 4-329
storage_clause. See STORAGE clause., 4-330
CREATE TRIGGER command, 4-334
examples, 4-341
CREATE TYPE BODY
examples, 4-356
CREATE TYPE BODY command, 4-354
CREATE TYPE command, 4-346
examples, 4-352
pragma_clause, 4-348
CREATE USER command, 4-358
examples, 4-361
CREATE VIEW command, 4-364
examples, 4-371
subquery, 4-365
creating
clusters, 4-208
database links, 4-226
databases, 4-220
directories, 4-231
dispatcher processes (DISP), 4-101
external functions, 4-233
indexes, 4-238
object views, 4-371
packages, 4-251,
4-255
procedures, 4-260
profiles, 4-266
roles, 4-273
rollback segments, 4-276
savepoints, 4-490
schemas, 4-279
sequences, 4-282
shared server processes, 4-100
snapshot logs, 4-298
snapshots, 4-287
stored functions, 4-233
synonyms, 4-303
tables, 4-307
tablespaces, 4-329
triggers, 4-334
users, 4-358
views, 4-364
creating object tables, 4-313
creating partitioned tables, 4-320
creating REF columns
in tables, 4-326
CURRENT option
of ARCHIVE LOG clause, 4-169
CURRENT_USER option of CONNECT TO clause
of CREATE DATABASE LINK command, 4-227
CURRVAL pseudocolumn, 2-33
examples, 2-35,
4-539
cursors
storing in session cache, 4-72
CYCLE option
of CREATE SEQUENCE command, 4-283
D
D format element, 3-65
DANGLING REFs, 2-23
data complexity
hiding with a view, 4-367
data definition language (DDL), 4-2
data independence
via synonyms, 4-305
data manipulation language (DML), 4-7
data_item parameter of RETURNING clause
of DELETE command, 4-377,
4-548
of INSERT command, 4-456
database
deleting rollback segments from, 4-401
database links
closing, 4-73,
4-74
creating, 4-226
definition, 4-226
dropping, 4-389
using in DELETE command, 4-377
using in INSERT command, 4-456
using in LOCK TABLE command, 4-461
using in SELECT command, 4-495,
4-539
using in UPDATE command, 4-547
using with synonyms, 4-229
database objects
definition of, 2-44
DATABASE option
of RECOVER clause, 4-474
DATABASE parameter
of CREATE CONTROLFILE command, 4-217
databases
adding datafiles to, 4-220,
4-224
adding redo log files to, 4-220,
4-222
altering, 4-15
archiving redo log files, 4-20,
4-223
creating, 4-220
maximum number
of datafiles, 4-220
of instances, 4-223
of redo log files, 4-220,
4-222
mounting and dismounting, 4-19
opening and closing, 4-19
DATAFILE clause
of ALTER DATABASE command, 4-23
of CREATE CONTROLFILE clause, 4-218
of CREATE DATABASE command, 4-224
of CREATE TABLESPACE command, 4-330
DATAFILE option
of RECOVER clause, 4-474
DATAFILE parameter
of ALLOCATE EXTENT clause, 4-12,
4-34,
4-120
datafiles
adding, 4-138
adding to databases, 4-220,
4-224
adding to tablespaces, 4-134,
4-330
backing up, 4-134,
4-139
maximum number
for databases, 4-220
renaming, 4-22,
4-134
specifying, 4-434
datatypes, 2-5
ANSI, 2-20
changing for columns, 4-124
converting between values of different, 2-28
converting between with SQL functions, 3-42
DB2, 2-20
explicit conversion, 2-29
implicit conversion, 2-28
of conditions, 3-90
of expressions, 3-78
specifying for columns, 4-209,
4-314
SQL/DS, 2-20
summary, 2-5
date
arithmetic, 2-14
comparison rules, 2-24
DATE datatype, 2-13
comparing values of, 2-24
julian, 2-14
DATE format element, 3-65
date format elements, 3-69
capitalizing, 3-74
date format model, 3-69
default, 3-69
examples, 3-64,
3-76
modifiers, 3-75
suffixes, 3-74
DAY format element, 3-72
DB2
datatypes, 2-20
DBA role, 4-274
DBA statement auditing short cut, 4-178
DBA_CLUSTERS view, 4-164
DBA_INDEXES view, 4-162
DBA_TAB_COLUMNS view, 4-164
DBA_TABLES view, 4-162
DBMS_SNAPSHOT.REFRESH() procedure, 4-293
DBMSSTDX.SQL, 4-233,
4-251,
4-255,
4-260,
4-334
DDL (data definition language), 4-2
DEALLOCATE UNUSED clause, 4-373
of ALTER TABLE command, 4-120
deallocating space, 4-120
decimal places
negative, 2-11
DECODE expression, 3-89
default
cluster key, 4-246
DEFAULT option
of ALTER PROFILE command, 4-49
of BUFFER_POOL option
of STORAGE clause, 4-529
of CREATE PROFILE command, 4-269
of CREATE TABLE command, 4-314
of NOAUDIT command, 4-467
of ROLLBACK SEGMENT clause, 4-290
of ALTER SNAPSHOT command, 4-81
default privilege domain, 4-520
DEFAULT profile, 4-270,
4-360,
4-399
DEFAULT ROLE clause
of ALTER USER command, 4-153
DEFERRABLE constraints, 4-205
DEFERRABLE option
of CONSTRAINT clause, 4-192
deferring constraints, 4-205
DELETE command, 4-375
returning_clause, 4-376
subquery, 4-376
summary, 4-7
DELETE object auditing option, 4-175
DELETE object privilege
on tables, 4-450
on views, 4-450
DELETE option
of CREATE TRIGGER command, 4-336,
4-339
DELETE_CATALOG_ROLE role, 4-275
deleting
rows from tables, 4-408
rows from tables and views, 4-375
deleting from a single partition, 4-379
deleting referenced key values, 4-198
delimited names
quoted names, 2-49
DEREF function, 3-56
DESC option
of CREATE INDEX command, 4-241
of ORDER BY clause, 4-496
directories
creating, 4-231
definition, 3-50,
4-231
directory object
defined, 4-232
DIRECTORY option
of GRANT (Object Privileges), 4-448
directory parameter
of AUDIT (Schema Objects) command, 4-180
of NOAUDIT (Schema Objects) command, 4-467
DISABLE clause, 4-381
examples, 4-383
of ALTER DATABASE command, 4-23
of CREATE TABLE command, 4-320
DISABLE COMMIT IN PROCEDURE option
of ALTER SESSION command, 4-63
DISABLE option
of ALTER TRIGGER command, 4-142
of CONSTRAINT clause, 4-193
of PARALLEL DML clause
of ALTER SESSION command, 4-64
DISABLE STORAGE IN ROW option
of LOB storage clause
of ALTER TABLE command, 4-118
of CREATE TABLE command, 4-319
disabled constraints
enabling, 4-423
disabling
distributed recovery, 4-104
integrity constraints, 4-320,
4-381
redo log threads, 4-23
resource limits, 4-89,
4-99
roles for sessions, 4-519
SQL trace facility for sessions, 4-67,
4-68
triggers, 4-143
DISCONNECT SESSION clause
of ALTER SYSTEM command, 4-97
disconnecting
sessions, 4-97,
4-105
dispatch_clause
of ALTER SYSTEM, 4-93
dispatcher processes (DISP)
creating and terminating, 4-101
DISTINCT clause
with ORDER BY clause, 4-505
DISTINCT option
of SELECT command, 4-494
of SQL group functions, 3-57
distributed query, 4-539
examples, 4-540
restrictions on, 4-539
distributed recovery
disabling, 4-89,
4-104
enabling in a single-process environment, 4-89
enabling in single-process environments, 4-104
distributed transactions, 4-187,
4-489
DML (data manipulation language), 4-7
DROP clause, 4-385
examples, 4-386
of ALTER TABLE command, 4-119
DROP CLUSTER command, 4-387
examples, 4-388
DROP DATABASE LINK command, 4-389
examples, 4-389
DROP DIRECTORY command, 4-390
examples, 4-390
DROP FUNCTION command, 4-391
examples, 4-392
DROP INDEX command, 4-393
examples, 4-393
DROP LIBRARY command, 4-394
DROP LOGFILE clause
of ALTER DATABASE command, 4-21
DROP LOGFILE MEMBER clause
of ALTER DATABASE command, 4-21
DROP PACKAGE command, 4-395
DROP PARTITION option
of ALTER TABLE command, 4-122
DROP PROCEDURE command, 4-397
examples, 4-396,
4-397
DROP PROFILE command, 4-399
examples, 4-399
DROP ROLE command, 4-400
examples, 4-400
DROP ROLLBACK SEGMENT command, 4-401
examples, 4-401
DROP SEQUENCE command, 4-402
examples, 4-402
DROP SNAPSHOT command, 4-404
examples, 4-404
DROP SNAPSHOT LOG command, 4-406
examples, 4-406
DROP STORAGE option
of TRUNCATE command, 4-542
DROP SYNONYM command, 4-407
examples, 4-407
DROP TABLE command, 4-408
examples, 4-409
DROP TABLESPACE command, 4-410
examples, 4-411
DROP TRIGGER command, 4-412
examples, 4-412
DROP TYPE BODY command, 4-415
DROP TYPE command, 4-413
DROP USER command, 4-417
examples, 4-418
DROP VIEW command
examples, 4-419
dropping
clusters, 4-387
database links, 4-389
indexes, 4-393,
4-394
integrity constraints from tables, 4-119,
4-385
members from redo log file groups, 4-21
objects owned by users, 4-417
package bodies, 4-395
packages, 4-395
procedures, 4-249,
4-397
profiles, 4-399
redo log file groups, 4-21
roles, 4-400
sequences, 4-402
snapshots, 4-404
snapshots logs, 4-406
stored functions, 4-391
synonyms, 4-407
tables, 4-408
tablespaces, 4-410
triggers from tables, 4-412
users, 4-417
views, 4-419
dropping referential integrity constraints, 4-482
DUAL data dictionary table
definition, 4-538
example of selecting from, 4-538
DUMMY column
of DUAL table, 4-538
DUMP function, 3-49
DY format element, 3-72
dynamic performance tables
V$LOG, 4-222
V$NLS_PARAMETERS, 4-68
dynamic performance views
V$LOG, 4-21
E
EBCDIC
and ASCII, 3-4
character set, 2-26
EEEE format element, 3-65
embedded SQL, 1-3
EMPTY_BLOB fucntion, 3-50
EMPTY_CLOB function, 3-50
ENABLE clause, 4-420
examples, 4-425
except_clause, 4-421
exceptions_clause, 4-421
of ALTER DATABASE command, 4-23
of CREATE TABLE command, 4-320
storage_clause. See STORAGE clause.
using_index_clause, 4-421
ENABLE DISTRIBUTED RECOVERY option
of ALTER SYSTEM command, 4-104
ENABLE NOVALIDATE, 4-423
ENABLE NOVALIDATE constraints, 4-193,
4-422
ENABLE NOVALIDATE option
of CONSTRAINT clause, 4-193
ENABLE option
of ALTER TRIGGER command, 4-142
of PARALLEL DML clause
of ALTER SESSION command, 4-64
ENABLE STORAGE IN ROW option
of LOB storage clause
of ALTER TABLE command, 4-118
of CREATE TABLE command, 4-318
ENABLE VALIDATE, 4-423
ENABLE VALIDATE constraints, 4-193,
4-421
ENABLE VALIDATE option
of CONSTRAINT clause, 4-193
enabling
distributed recovery, 4-89,
4-104
integrity constraints, 4-320,
4-420
redo log threads, 4-23
resource limits, 4-89,
4-99,
4-270
roles for sessions, 4-519
SQL trace facility for sessions, 4-67,
4-68
triggers, 4-143
enabling and diabling constraints, 4-206,
4-422
enabling constraints, 4-423
primary and unique key, 4-206,
4-423
END BACKUP
of ALTER DATABASE command, 4-24
END BACKUP option
of ALTER TABLESPACE command, 4-139
ending
transactions, 4-186
ENTRYID option
of USERENV function, 3-55
equijoins, 4-508
ESCAPE character
of LIKE operator, 3-8
examples
of Comments, 2-39
EXCEPT clause
of SET ROLE command, 4-519
except_clause
of ENABLE clause, 4-421
EXCEPTIONS INTO clause
of CONSTRAINT clause, 4-192
exceptions_clause
of ENABLE, 4-421
EXCHANGE PARTITION option
of ALTER TABLE command, 4-122
exchange_partition_clause
of ALTER TABLE, 4-116
EXECUTE object auditing option, 4-175
EXECUTE object privilege
on procedures, functions, and packages, 4-451
EXECUTE_CATALOG_ROLE role, 4-275
executing
triggers, 4-337
EXISTS comparison operator, 3-5
EXP number function, 3-20
EXPLAIN PLAN
analyzing partitioned tables, 4-430
EXPLAIN PLAN command, 4-428
examples, 4-430
summary, 4-7
expression, 3-78
examples, 3-78
use in condition, 3-90
expression syntax
attribute reference, 3-88
method invocation, 3-88
VALUE operator, 3-87
extents
allocating for tables, 4-107
deallocating space, 4-373
INITIAL size, 4-527
MAXEXTENTS limit, 4-528
EXTERNAL clause
of CREATE PROCEDURE command, 4-263
external function
defined, 4-233
external functions
creating, 4-233
external procedures
definition, 4-260
EXTERNALLY option
of IDENTIFIED clause, 4-273,
4-360,
4-361
F
FAILED_LOGIN_ATTEMPTS option
of CREATE PROFILE command, 4-268
FALSE
result of a condition, 3-90
FAST option
of REFRESH clause, 4-81,
4-291
Federal Information Processing Standard (FIPS), 1-2
filespec, 4-434
examples, 4-435
of CREATE TABLESPACE, 4-329
fill mode
trims trailing blanks, 3-75
FIPS, 4-64
Federal Information Processing Standard, 1-2
flagging, 4-72
PUB 127-2, 1-2
FIPS Flagger, B-13
firing
triggers, 4-337
FLAGGER clause
of ALTER SESSION command, 4-64
flattened subqueries
definition, 4-535
in DELETE statements, 4-377
using, 4-536
using in UPDATE command, 4-547
FLOAT
ANSI datatype, 2-12
FLOOR number function, 3-20
FM date format element prefix
examples, 3-76
FM format model modifier, 3-75
FOR clause
of EXPLAIN PLAN command, 4-429
FOR EACH ROW option
of CREATE TRIGGER command, 4-337
FOR RECOVER option
of ALTER TABLESPACE command, 4-139
FOR UPDATE clause
of SELECT command, 4-496,
4-506
FOR UPDATE OF
example, 4-507
FOR UPDATE option
of CREATE SNAPSHOT command, 4-291
for_clause
of ANALYZE, 4-158
FORCE clause
of COMMIT command, 4-187
of ROLLBACK command, 4-487
FORCE option
of CREATE VIEW command, 4-365
of PARALLEL DML clause
of ALTER SESSION command, 4-64
of REFRESH clause, 4-81,
4-291
of REVOKE (Schema Object Privileges) command, 4-482
foreign key constraints, 4-197
FOREIGN KEY option
of CONSTRAINT clause, 4-198
foreign_key_clause
of CONSTRAINT clause, 4-190
format model
date, 3-69
definition, 3-63
examples, 3-63,
3-64,
3-76
number, 3-65
formatting
date values, 3-69
number values, 3-65
FROM clause
of REVOKE command, 4-478,
4-482
FROM parameter
of RECOVER clause, 4-473
function_specification parameter
of CREATE TYPE command, 4-148
function_specification parameter of CREATE TYPE command, 4-351
functions
PL/SQL, 3-60
SQL, 3-16
stored functions, 4-233
user, 3-60
FX format model modifier, 3-75
G
G format element, 3-65
GLOBAL option
of CHECK DATAFILES clause, 4-95
of CHECKPOINT clause, 4-95
global_index_clause
of CREATE INDEX, 4-239
GLOBAL_NAMES, 4-100
global_partition_clause
of CREATE INDEX, 4-240
GLOBALLY AS option
of IDENTIFIED clause, 4-360
GLOBALLY option
of IDENTIFIED clause, 4-274
GRANT command, 4-437,
4-447
examples, 4-445,
4-452
part of CREATE SCHEMA command, 4-279
GRANT object auditing option, 4-175
GRANT OPTION
of GRANT command, 4-449
granting
object privileges to users and roles, 4-447
roles, 4-437
system privileges and roles to users, roles, 4-437
GRAPHIC datatype, 2-22
GREATEST function, 3-51
group
SQL functions, 3-57
GROUP BY clause
group SQL functions and, 3-17
of SELECT command, 4-495,
4-502
GROUP parameter
of ADD LOGFILE MEMBER clause, 4-21
of DROP LOGFILE clause, 4-21
H
HASH parameter
of CREATE CLUSTER command, 4-210
HAVING clause
of SELECT command, 4-496,
4-503
HEXTORAW conversion function, 3-43
hints, 2-40
in DELETE statements, 4-378
in SELECT statements, 4-497
in UPDATE statements, 4-545
I
I date format element, 3-72
IDENTIFIED clause
of CREATE ROLE command, 4-273
of CREATE USER command, 4-359
identifiers
names, 2-47
identifiers, ORACLE
how to form, A-3
IDLE_TIME parameter
of CREATE PROFILE command, 4-268
IEC (International Electrotechnical Commission), 1-2
IMMEDIATE option
of ALTER TABLESPACE command, 4-139
implies, 4-192
IN comparison operator, 3-5
definition, 3-5
INCLUDING clause
of ALTER TABLE command, 4-118
of CREATE TABLE command, 4-318
INCREMENT BY clause
of CREATE SEQUENCE command, 4-283
incrementing
sequence values, 2-33,
4-539
INDEX object auditing option, 4-175
INDEX object privilege
on tables, 4-450
INDEX option
of ANALYZE command, 4-159
of CREATE CLUSTER command, 4-210
INDEX parameter
of LOB storage clause, 4-319
index_organized_table_clause
of CREATE TABLE, 4-311
index_organized_table_clauses
of ALTER TABLE, 4-113
index_physical_attributes_clause
of ALTER INDEX, 4-31
of CONSTRAINT clause, 4-191
of CREATE INDEX, 4-240
INDEX_STATS view, 4-165
indexed clusters, 4-212
indexes
altering, 4-29
and LIKE operator, 3-9
bitmap indexes, 4-247
cluster indexes, 4-246
creating, 4-238
definition, 4-238
dropping, 4-393,
4-394,
4-408
LONG RAW datatypes prohibit, 2-15
multiple per table, 4-245
nested table columns, 4-248
partitioned indexes, 4-247
storage characteristics of, 4-29,
4-242
indexing
specifying tablespaces for, 4-242
index-organized tables, 4-324
creating, 4-317
examples, 4-324
INITCAP character function, 3-26
INITIAL parameter
of STORAGE clause, 4-527,
4-530
initialization parameters
AUDIT_TRAIL, 4-172
GLOBAL_NAMES, 4-100
LOG_FILES, 4-222
MAX_ENABLED_ROLES, 4-520
MTS_MAX_DISPATCHERS, 4-101
MTS_MAX_SERVERS, 4-100
MTS_SERVERS, 4-100
NLS_DATE_FORMAT, 3-69
NLS_DATE_LANGUAGE, 3-72
NLS_LANGUAGE, 3-72
NLS_TERRITORY, 3-68,
3-69,
3-72
OPEN_LINKS, 4-73,
4-228
OPTIMIZER_MODE, 4-72
OS_AUTHENT_PREFIX, 4-362
OS_ROLES, 4-445
ROLLBACK_SEGMENTS, 4-55
SNAPSHOT_REFRESH_INTERVAL, 4-294
SNAPSHOT_REFRESH_KEEP_CONNECTIONS, 4-294
SNAPSHOT_REFRESH_PROCESSES, 4-294
SQL_TRACE, 4-68
THREAD, 4-23
INITIALLY DEFERRABLE option
of CONSTRAINT clause, 4-192
INITIALLY IMMEDIATE option
of CONSTRAINT clause, 4-192
INITRANS parameter
of ALTER CLUSTER command, 4-12
of ALTER INDEX command, 4-33
of ALTER SNAPSHOT command, 4-80
of ALTER TABLE command, 4-86,
4-118
of CREATE CLUSTER command, 4-210
of CREATE INDEX command, 4-242
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-316
inline LOB storage, 4-118,
4-318
INSERT command, 2-12,
4-454
examples, 2-35,
4-458
summary, 4-7
INSERT object auditing option, 4-175
INSERT object privilege
on tables, 4-450
on views, 4-450
INSERT option
of CREATE TRIGGER command, 4-336
inserting
rows into tables and views, 4-454
inserting into views, 4-457
INSTANCE clause
of ALTER SESSION command, 4-65
INSTANCE parameter
of ALLOCATE EXTENT clause, 4-13,
4-34,
4-120
instances
maximum number
for databases, 4-223
INSTEAD OF option
of CREATE TRIGGER command, 4-336
instead of triggers
using, 4-343
INSTR character function, 3-34
INTEGER datatype, 2-3
integrity constraints
adding to columns, 4-117,
4-124
adding to tables, 4-117,
4-124
CHECK, 4-192,
4-202
column definition, 4-189
creating as parts of tables, 4-314
defining, 4-189
definition, 4-189
disabling, 4-320,
4-381
dropping from tables, 4-119
enabling, 4-320,
4-420
NOT NULL, 4-194
PRIMARY KEY, 4-191,
4-196
referential, 4-191,
4-197
removing from columns, 4-117,
4-124
table definition, 4-117,
4-189,
4-193,
4-314
UNIQUE, 4-191,
4-194
International Electrotechnical Commission (IEC), 1-2
International Standards Organization (ISO), 1-2
INTERSECT set operator, 4-496
examples, 3-15
INTO clause
of ANALYZE command, 4-160
of EXPLAIN PLAN command, 4-429
IS DANGLING, 2-23
IS NOT DANGLING, 2-23
IS NOT NULL comparison operator, 3-5
IS NULL comparison operator, 3-5
ISDBA option
of USERENV function, 3-55
ISO
International Standards Organization, 1-2
ISO/IEC 9075
1992, 1-2
ISOLATION LEVEL clause
of SET TRANSACTION command, 4-523
ISOLATION_LEVEL clause
of ALTER SESSION command, 4-65
IW date format element, 3-72
IY date format element, 3-72
IYY date format element, 3-72
IYYY date format element, 3-72
J
job queue processes, 4-294
join view, 4-369
joins
and clusters, 4-211
examples, 4-509,
4-512
simple, 4-508
julian dates, 2-14
K
KEEP option
of BUFFER_POOL option
of STORAGE clause, 4-529
KILL SESSION clause
of ALTER SYSTEM command, 4-97
L
L format element, 3-65
LANGUAGE clause
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
LANGUAGE option
of USERENV function, 3-55
LAST_DAY date function, 3-37
LEAST function, 3-51
LENGTH character function, 3-35
LENGTHB character function, 3-35
LEVEL pseudocolumn, 2-35
lexical conventions
SQL, 1-4
LIBRARY clause
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
LIKE comparison operator, 3-5
definition, 3-8
links
database links, 4-226
LIST CHAINED ROWS clause
of ANALYZE command, 4-160
literal
character, 2-2
definition of, 2-2
numeric, 2-2
LN number function, 3-21
LOB columns
adding to tables, 4-126
LOB datatypes, 2-15
LOB functions, 3-50
LOB storage
in row, 4-118,
4-318
out of line, 4-118,
4-319
LOB storage clause
of ALTER SNAPSHOT command, 4-80
of CREATE TABLE command, 4-318
LOB storage parameters
specifying in ALTER TABLE command, 4-119
LOB_index_clause
of ALTER TABLE, 4-112
of CREATE TABLE, 4-312
LOB_parameters
of ALTER TABLE, 4-111
LOB_storage_clause
of ALTER TABLE, 4-111
of CREATE TABLE, 4-311
LOCAL option
of CHECK DATAFILES clause, 4-95
of CHECKPOINT clause, 4-95
of ROLLBACK SEGMENT clause, 4-290
of ALTER SNAPSHOT command, 4-82
LOCAL parameter
of ANALYZE command, 4-160
local_index_clause
of CREATE INDEX, 4-240
location transparency
via synonyms, 4-305
lock
and queries, 4-462
exclusive, 4-462
multiple, 4-462
released by ROLLBACK statement, 4-488
releasing with COMMIT command, 4-186
table, 4-462
types of, 4-462
LOCK object auditing option, 4-175
LOCK TABLE command, 4-461
examples, 4-462
summary, 4-7
locking
tables and views, 4-461
LOG number function, 3-21
LOG_FILES, 4-222
logarithms
LN number function, 3-21
LOG number function, 3-21
LOGFILE clause
of CREATE CONTROLFILE command, 4-217
of CREATE DATABASE command, 4-222
LOGFILE parameter
of ARCHIVE LOG clause, 4-169
of RECOVER clause, 4-474
LOGGING option
of ALTER INDEX command, 4-34
of ALTER TABLE command, 4-121
of ALTER TABLESPACE command, 4-137
of CREATE INDEX command, 4-242
of CREATE TABLE command, 4-317
of CREATE TABLESPACE command, 4-330
logical operator
definition, 3-11
use in condition, 3-90
LONG datatype, 2-12
maximum length, 2-12
restrictions on, 2-12
LONG RAW datatype, 2-15
indexing prohibited on, 2-15
similarity to LONG datatype, 2-15
LONG VARGRAPHIC datatype, 2-22
LOWER character function, 3-27
lowercase
significance in SQL statements, 1-5
lowercase and uppercase
of schema object names, 2-47
significance in pattern matching, 3-9
LPAD character function, 3-27
LTRIM character function, 3-27
M
MAKE_REF function, 3-56
MAP MEMBER clause
of CREATE TYPE command, 4-147
MAP MEMBER clause of CREATE TYPE command, 4-351
MAP method
object value comparisons, 2-28
MASTER option
of ROLLBACK SEGMENT clause, 4-290
of ALTER SNAPSHOT command, 4-82
MAX group function, 3-58
MAX_DUMP_FILE_SIZE option
of ALTER SESSION command, 4-65
of ALTER SYSTEM command, 4-97
MAX_ENABLE_ROLES, 4-520
MAXDATAFILES parameter
of CREATE CONTROLFILE command, 4-218
of CREATE DATABASE command, 4-223
MAXEXTENTS parameter
of STORAGE clause, 4-528,
4-530
MAXINSTANCES parameter
of CREATE CONTROLFILE command, 4-218
of CREATE DATABASE command, 4-223
MAXLOGFILES parameter
of CREATE CONTROLFILE command, 4-218
of CREATE DATABASE command, 4-222
MAXLOGHISTORY parameter
of CREATE DATABASE command, 4-223
MAXLOGMEMBERS parameter
of CREATE DATABASE command, 4-223
MAXSIZE clause
of ALTER DATABASE command, 4-24
MAXTRANS parameter
of ALTER CLUSTER command, 4-12
of ALTER INDEX command, 4-33
of ALTER SNAPSHOT command, 4-80
of ALTER TABLE command, 4-86,
4-118
of CREATE CLUSTER command, 4-210
of CREATE INDEX command, 4-242
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-316
MAXVALUE parameter
of CREATE SEQUENCE command, 4-283
of partitioning clause, 4-320
MEMBER clause
of CREATE TYPE command, 4-147
MEMBER clause of CREATE TYPE command, 4-351
method invocation
expression syntax, 3-88
method_name parameter
of CREATE TYPE command, 4-148
method_name parameter of CREATE TYPE command, 4-352
methods, 2-23
MI format element, 3-65
MIN group function, 3-59
MINEXTENTS parameter
of STORAGE clause, 4-528
MINIMUM EXTENT parameter
of ALTER TABLESPACE command, 4-138
of CREATE TABLESPACE command, 4-330
MINUS set operator, 4-496
examples, 3-15
MINVALUE parameter
of CREATE SEQUENCE command, 4-283
miscellaneous operators, 3-16
MLSLABEL datatype, 2-20
MODIFY clause
of ALTER TABLE command, 4-117
MODIFY DEFAULT ATTRIBUTES
of ALTER INDEX command, 4-35,
4-87
MODIFY DEFAULT ATTRIBUTES option
of ALTER SNAPSHOT command, 4-80
of ALTER TABLE command, 4-117
modify LOB storage clause
of ALTER SNAPSHOT command, 4-80
MODIFY PARTITION option
of ALTER SNAPSHOT command, 4-80,
4-86
of ALTER TABLE command, 4-121
modify_column_options_clause
of ALTER TABLE, 4-110
modify_LOB_index_clause
of ALTER TABLE, 4-113
modify_LOB_storage_clause
of ALTER TABLE, 4-112
modify_partition_clause
of ALTER TABLE, 4-115
modifying
column definitions, 4-107,
4-117,
4-124
resource limits, 4-44
MON format element, 3-72
MONTH format element, 3-72
MONTHS_BETWEEN date function, 3-38
MOUNT option
of ALTER DATABASE command, 4-19
mounting
databases, 4-19
MOVE PARTITION option
of ALTER SNAPSHOT command, 4-80,
4-86
of ALTER TABLE command, 4-122
move_partition_clause
of ALTER TABLE, 4-115,
4-116
MTS_DISPATCHERS parameter
of ALTER SYSTEM command, 4-96,
4-100
MTS_MAX_DISPATCHERS, 4-101
MTS_MAX_SERVERS, 4-100
MTS_SERVERS, 4-100
MTS_SERVERS parameter
of ALTER SYSTEM command, 4-96,
4-100
multi-threaded server
managing processes for, 4-96,
4-100
N
NAME clause
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
names
for schema objects, 2-47
lowercase and uppercase, 2-47
quoted, 2-49
namespaces
for schema objects, 2-48
naming
of database objects, A-3
naming schema objects, 2-47
NATIONAL CHARACTER SET parameter
of CREATE DATABASE command, 4-224
National Institute for Standards and Technology (NIST), 1-2
National Language Support (NLS)
session settings, 4-65,
4-68
natural logarithms, 3-21
navigation
automatic, 1-2
NCHAR datatype, 2-8
NCLOB datatype, 2-18
negative scale, 2-11
nested CURSOR
expression syntax, 3-85
nested table columns
adding to tables, 4-127
identifying in a subquery, 4-535
indexes, 4-248
nested table storage
creating, 4-326
NESTED TABLE storage clause
of ALTER TABLE command, 4-119
of CREATE TABLE command, 4-319
nested table types, 2-24
nested_table_storage_clause
of ALTER TABLE, 4-113
of CREATE TABLE, 4-313
NEW_TIME date function, 3-38
NEXT clause
of ALTER DATABASE command, 4-24
NEXT option
of ARCHIVE LOG clause, 4-169
NEXT parameter
of REFRESH clause, 4-81,
4-291
of STORAGE clause, 4-528
NEXT_DAY date function, 3-39
NEXTVAL pseudocolumn, 2-33
examples, 2-35,
4-459,
4-539
NIST
National Institute for Standards and Technology, 1-2
NLS_CHARSET_DECL_LEN function, 3-52
NLS_CHARSET_ID function, 3-52
NLS_CHARSET_NAME function, 3-53
NLS_CURRENCY parameter
of ALTER SESSION command, 4-66
NLS_DATE_FORMAT parameter
of ALTER SESSION command, 4-66
NLS_DATE_LANGUAGE parameter
of ALTER SESSION command, 4-66
NLS_INITCAP character function, 3-28
NLS_ISO_CURRENCY parameter
of ALTER SESSION command, 4-66
NLS_LANGUAGE parameter
of ALTER SESSION command, 4-65
NLS_LOWER character function, 3-23,
3-26,
3-28
NLS_NUMERIC_CHARACTERS parameter
of ALTER SESSION command, 4-66
NLS_SORT parameter
of ALTER SESSION command, 4-66
NLS_TERRITORY parameter
of ALTER SESSION command, 4-66
NLS_UPPER character function, 3-29
NLSSORT character function, 3-36
NOARCHIVELOG option
of ALTER DATABASE command, 4-20
of CREATE DATABASE command, 4-223
NOAUDIT command, 4-464,
4-466
examples, 4-465,
4-467
NOCACHE option
of ALTER TABLE command, 4-120
of CREATE SEQUENCE command, 4-284
of CREATE TABLE, 4-321
NOCYCLE option
of CREATE SEQUENCE command, 4-283
NOFORCE option
of CREATE VIEW command, 4-365
NOLOGGING option
of ALTER INDEX command, 4-34
of ALTER TABLE command, 4-121
of ALTER TABLESPACE command, 4-137
of CREATE INDEX command, 4-246
of CREATE TABLE command, 4-317
of CREATE TABLESPACE command, 4-330
NOMAXVALUE option
of CREATE SEQUENCE command, 4-283
NOMINVALUE option
of CREATE SEQUENCE command, 4-283
NONE option
of SET ROLE command, 4-520
non-padded comparison semantics, 2-25
NOORDER option
of CREATE SEQUENCE command, 4-284
NORESETLOGS option
of CREATE CONTROLFILE command, 4-217
NOREVERSE option
of ALTER INDEX command, 4-33
normal exit from Oracle7, 4-188
NORMAL option
of ALTER TABLESPACE command, 4-138
NOSORT option
of CREATE INDEX command, 4-242,
4-245
NOT DEFERRABLE option
of CONSTRAINT clause, 4-192
NOT IN comparison operator, 3-5
examples, 3-7
NOT LIKE comparison operator, 3-5
NOT logical operator, 3-8
truth table, 3-12
NOT NULL clause
ALTER TABLE command, 4-125
NOT NULL constraints, 4-194
NOT option
of WHENEVER clause, 4-172,
4-180,
4-181,
4-464,
4-467
NOVALIDATE option
of ENABLE clause, 4-422
ofCONSTRAINT clause, 4-193
NOWAIT option
of FOR UPDATE clause, 4-496
of LOCK TABLE command, 4-462
NULL
constraint, example, 4-194
in a condition, 3-94
null, 2-31
in a bitmap index, 4-246
in an index, 4-246
NULL value
of OPTIMAL parameter, 4-277,
4-529
number
comparison rules, 2-24
literal, 2-2
NUMBER datatype, 2-10
comparing values of, 2-24
number format elements, 3-65
number format models, 3-65
examples, 3-63
numeric
literal, 2-2
NVARCHAR2 datatype, 2-9
NVL function, 2-31,
3-53
O
object auditing options, 4-175,
4-180
object auditing short cuts, 4-181
object privileges, 4-449
granting to users and roles, 4-447
on synonyms, 4-451
on tables, 4-450
revoking from users and roles, 4-481
object reference
functions, 3-56
object tables, 4-325
creating, 4-313
index on object identifier column, 4-315
object type attributes and methods
referencing, 2-57
object type bodies
creating, 4-354
dropping, 4-415
object type column constraints, 4-327
object types, 2-23
attributes and methods, referencing, 2-57
comparing values of, 2-27
creating, 4-346
dropping, 4-413
revoking privileges, 4-482
object view
example, 4-372
object views
creating, 4-364,
4-371
OF datatype clause
of CREATE TYPE command, 4-350
OFFLINE option
of ALTER ROLLBACK SEGMENT command, 4-55
of ALTER TABLESPACE command, 4-138
of CREATE TABLESPACE command, 4-331
of DATAFILE clause, 4-24
OIDINDEX clause
of CREATE TABLE command, 4-315
ON clause
of CREATE TRIGGER command, 4-336,
4-339
of GRANT command, 4-448
of NOAUDIT command, 4-466
of REVOKE command, 4-482
ON DELETE CASCADE option
of CONSTRAINT clause, 4-198
ONLINE option
of ALTER ROLLBACK SEGMENT command, 4-55
of ALTER TABLESPACE command, 4-138
of CREATE TABLESPACE command, 4-331
of DATAFILE clause, 4-24
OPEN option
of ALTER DATABASE command, 4-19
OPEN_LINKS, 4-228
opening
databases, 4-19
operator
arithmetic, 3-3
character, 3-4
definition, 3-1
logical, 3-5,
3-11
miscellaneous, 3-16
NOT IN, 3-7
set, 3-12
use in expression, 3-78
optimal size
of rollback segments, 4-277,
4-529
optimizer
hints, 2-40
SQL, 1-3
OPTIMIZER_MODE, 4-72
OPTIMIZER_MODE parameter
of ALTER SESSION command, 4-67
opts_clause
of ALTER SYSTEM, 4-94
OR logical operator
truth table, 3-12
OR REPLACE option
of CREATE FUNCTION command, 4-235
of CREATE PACKAGE BODY command, 4-256
of CREATE PACKAGE command, 4-251
of CREATE PROCEDURE command, 4-262
of CREATE TRIGGER command, 4-335
of CREATE TYPE command, 4-349
of CREATE VIEW command, 4-365
ORACLE identifiers
how to form, A-3
ORDER BY clause
and ROWNUM pseudocolumn, 2-38
of SELECT command, 4-496,
4-504
ORDER MEMBER clause of CREATE TRIGGER command, 4-351
ORDER method
object value comparisons, 2-28
ORDER option
of CREATE SEQUENCE command, 4-284
ORGANIZATION HEAP option
of CREATE TABLE command, 4-318
ORGANIZATION INDEX option
of CREATE TABLE command, 4-317
OS_AUTHENT_PREFIX
initialization parameter, 4-362
OS_ROLES, 4-445
out of line LOB storage, 4-118,
4-319
outer join, 3-16,
4-511
outer joins
examples, 4-512
outer_join
of SELECT, 4-511
OVERFLOW clause
of ALTER TABLE command, 4-118
of CREATE TABLE command, 4-318
overflow_clause
of ALTER TABLE, 4-114
overloading
procedures and stored functions, 2-49,
4-252
P
package bodies
dropping, 4-395
PACKAGE option
of ALTER PACKAGE command, 4-39
package specifications, 4-251
packages, 4-251,
4-252,
4-255
adding procedures to, 4-252
adding stored functions to, 4-252
creating, 4-251,
4-255
creating package bodies for, 4-255
creating package specifications for, 4-251
dropping, 4-395
redefining, 4-251,
4-256
removing procedures from, 4-397
removing stored functions from, 4-391
packages bodies, 4-255
PARALLEL clause, 4-468
of RECOVER clause, 4-474
parallel clause
of ALTER SNAPSHOT command, 4-81,
4-87
of ALTER TABLE command, 4-123
parallel DML
enabling and disabling for sessions, 4-63
PARALLEL DML option
of ALTER SESSION command, 4-63
parallel query clause, 4-13
of CREATE TABLE command, 4-320
parallel server
setting the instance, 4-73
parallel_clause
of CREATE CLUSTER, 4-208
PARALLEL_DEFAULT_SCANSIZE parameter, 4-469
PARAMETERS clause
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
parentheses
around expressions, 3-90
overriding operator precedence, 3-3
PARTITION BY RANGE clause
of CREATE TABLE command, 4-320
PARTITION clause
of UPDATE command, 4-547
PARTITION option
of ANALYZE command, 4-159
partition views, 4-371
partition_description_clause
of ALTER INDEX, 4-32
partition_start column
of EXPLAIN PLAN, 4-431
partition_stop column
of EXPLAIN PLAN, 4-431
partitioned indexes
altering, 4-35
definition, 4-238
partitioned snapshots, 4-84,
4-297
partitioned table analysis
EXPLAIN PLAN, 4-430
partitioned table update, 4-549
partitioned tables
creating, 4-320
partitioning
partitioned indexes, 4-247
partitioning_clauses
of ALTER TABLE, 4-114
partitions
efficient deletes from, 4-379
password history parameters, 4-46
PASSWORD_GRACE_TIME option
of CREATE PROFILE command, 4-268
PASSWORD_LIFE_TIME option
of CREATE PROFILE command, 4-268
PASSWORD_LOCK_TIME option
of CREATE PROFILE command, 4-268
PASSWORD_REUSE_MAX option
of CREATE PROFILE command, 4-268
PASSWORD_REUSE_TIME option
of CREATE PROFILE command, 4-268
PASSWORD_VERIFY_FUNCTION option
of CREATE PROFILE command, 4-269
passwords
changing, 4-151
establishing for users, 4-273,
4-360
pattern matching
definition, 3-8
PCTFREE parameter
of ALTER CLUSTER command, 4-12
of ALTER SNAPSHOT command, 4-80
of ALTER TABLE command, 4-86,
4-118
of CREATE CLUSTER command, 4-209
of CREATE INDEX command, 4-242
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-315
PCTINCREASE parameter
of STORAGE clause, 4-528
PCTTHRESHOLD option
of ALTER TABLE command, 4-118
of CREATE TABLE command, 4-318
PCTUSED parameter
of ALTER CLUSTER command, 4-12
of ALTER SNAPSHOT command, 4-80
of ALTER TABLE command, 4-86,
4-118
of CREATE CLUSTER command, 4-209
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-315
PCTVERSION parameter
of LOB storage clause, 4-319
physical_attributes_clause
of ALTER CLUSTER, 4-11
of ALTER TABLE, 4-111
of CREATE CLUSTER, 4-209
of CREATE TABLE, 4-311
PL/SQL
functions, 3-60
PLSQL_V2_COMPATABILITY option
of ALTER SESSION command, 4-67
of ALTER SYSTEM command, 4-97
PM/P.M. format element, 3-72
POST_TRANSACTION option
of DISCONNECT SESSION clause
of ALTER SYSTEM command, 4-97
POWER number function, 3-22
PR format element, 3-65
PRAGMA RESTRICT_REFERENCES clause
of CREATE TYPE command, 4-148
PRAGMA RESTRICT_REFERENCES clause of CREATE TYPE command, 4-352
pragma_clause
of ALTER TYPE, 4-146
of CREATE TYPE, 4-348
precedence
definition, 3-2
precision
of NUMBER columns, 2-10
preface
Send Us Your Comments, xv
primary key
snapshots, 4-296
PRIMARY KEY option
of ADD clause
of ALTER SNAPSHOT LOG command, 4-87
of DISABLE clause, 4-382
of DROP clause, 4-385
of ENABLE clause, 4-422
of REFRESH clause, 4-81,
4-291
primary keys, 4-191,
4-196
PRIOR operator, 3-16
PRIVATE_SGA parameter
of ALTER RESOURCE COST command, 4-49
privilege domain
changing, 4-520
privileges, 4-449
procedure_specification parameter
of CREATE TYPE command, 4-147
procedure_specification parameter of CREATE TYPE command, 4-351
procedures
adding to packages, 4-252
creating, 4-260
definition, 4-260,
4-263
dropping, 4-249,
4-397
granting object privileges on, 4-451
overloading, 2-49,
4-252
recompiling, 4-42
redefining, 4-260
removing from packages, 4-397
PROFILE clause
of CREATE USER command, 4-360
profiles
adding resource limits to, 4-44,
4-266
altering, 4-44
assigning to users, 4-151,
4-360
creating, 4-266
DEFAULT profile, 4-270,
4-360,
4-399
definition, 4-266,
4-269
dropping, 4-399
modifying resource limits in, 4-44
PUBLIC_DEFAULT profile, 4-47,
4-49
removing resource limits from, 4-44
pseudocolumns, 2-32
PUBLIC option
of CREATE DATABASE LINK command, 4-227
of CREATE ROLLBACK SEGMENT command, 4-276
of CREATE SYNONYM command, 4-303
of DROP DATABASE LINK command, 4-389
of DROP SYNONYM command, 4-407
of ENABLE clause, 4-23
of FROM clause, 4-478,
4-482
of TO clause, 4-438,
4-449
public rollback segments, 4-276
PUBLIC_DEFAULT profile, 4-47,
4-49
punctuation
in date format models, 3-76
Q
queries, 4-533
examples, 4-536
SELECT, 4-492
QUOTA clause
multiple in CREATE USER statement, 4-361
of CREATE USER command, 4-360,
4-361
quote marks
using in text literals, 2-2
quoted names, 2-49
R
RAW datatype, 2-15
RAWTOHEX conversion function, 3-43
RDBMS (relational database management system), 1-1
read consistency
default, 4-523
READ ONLY option
of SET TRANSACTION command, 4-522
READ WRITE option
of SET TRANSACTION command, 4-522
REBUILD UNUSABLE LOCAL INDEXES option
of ALTER SNAPSHOT command, 4-81
of ALTER TABLE command, 4-121
recompiling
procedures, 4-42
stored functions, 4-27
RECOVER clause, 4-472
examples, 4-474
of ALTER DATABASE command, 4-20
parallel_clause. See PARALLEL clause.
recoverability
of tables, 4-317
recovery
disabling for distributed transactions, 4-89,
4-104
enabling for distributed transactions, 4-89,
4-104
RECYCLE option
of BUFFER_POOL option
of STORAGE clause, 4-529
redefining
columns, 4-117
packages, 4-251,
4-256
procedures, 4-262
stored functions, 4-235
redo log file groups
adding members to, 4-21
adding to threads, 4-20
assigning to redo log threads, 4-217
dropping, 4-21
dropping members from, 4-21
maximum number
of members, 4-223
redo log file members
adding to redo log file groups, 4-21
dropping from redo log file groups, 4-21
maximum number
for redo log file groups, 4-223
for redo log files, 4-220
renaming, 4-22
specifying, 4-434
redo log files
adding to databases, 4-220,
4-222
archiving, 4-20,
4-220,
4-223
maximum number
for databases, 4-220,
4-222
of members, 4-220
specifying, 4-434
switching, 4-89,
4-103
redo log threads
adding redo log file groups to, 4-20
assigning redo log file groups to, 4-217
disabling, 4-23
dropping redo log file groups from, 4-21
enabling, 4-23
REF clause
of CREATE TYPE command, 4-350
REF columns
adding to tables, 4-128
creating in tables, 4-326
REF constructor
expression syntax, 3-87
REFERENCES object privilege
on tables, 4-450
REFERENCES options
of CONSTRAINT clause, 4-198
REFERENCING clause
of CREATE TRIGGER command, 4-336
referential integrity constraints, 4-191,
4-197
defining, 4-199
dropping, 4-482
maintaining, 4-200
REFRESH clause
of ALTER SNAPSHOT command, 4-81
of CREATE SNAPSHOT command, 4-291
refresh modes
for snapshots, 4-293
refresh times
for snapshots, 4-294
refreshing
snapshots, 4-81,
4-291
snapshots with snapshot logs, 4-300
REFs, 2-23
DANGLING, 2-23
scoped, 4-326
REFTOHEX function, 3-56
remote query, 4-539
remote table
identifying, 4-539
removing
Comments from objects, 4-184
integrity constraints from columns, 4-117,
4-124
procedures from packages, 4-397
resource limits from profiles, 4-44
stored functions from packages, 4-391
RENAME command, 4-476
examples, 4-476
RENAME FILE clause
of ALTER DATABASE command, 4-22
RENAME object auditing option, 4-175
RENAME option
of ALTER TABLE command, 4-121
RENAME PARTITION option
of ALTER SNAPSHOT command, 4-80,
4-86
of ALTER TABLE command, 4-122
renaming
datafiles, 4-22,
4-134
objects, 4-476
redo log file members, 4-22
REPLACE character function, 3-29
REPLACE option
of ALTER TYPE command, 4-146
RESETLOGS option
of ALTER DATABASE command, 4-20
of CREATE CONTROLFILE command, 4-217
RESIZE clause
of ALTER DATABASE command, 4-24
resource limits
adding to profiles, 4-44,
4-266
assigning to users, 4-151
costs of resources, 4-49
disabling, 4-89,
4-99
enabling, 4-89,
4-99,
4-270
exceeding, 4-269
modifying, 4-44
removing profiles from, 4-44
RESOURCE role, 4-274
RESOURCE statement auditing short cut, 4-178
RESOURCE_LIMIT option
of ALTER SYSTEM command, 4-99
RETURNING clause
of DELETE command, 4-377,
4-548
of INSERT command, 4-456
of the DELETE command, 4-379
of the UPDATE command, 4-552
retrieving deleted rows, 4-379
retrieving inserted rows, 4-458
retrieving updated rows, 4-552
returning_clause
of DELETE, 4-376
of INSERT
INSERT command
returning_clause, 4-455
of UPDATE, 4-547
REUSE option
of BACKUP CONTROLFILE clause, 4-22
of CREATE CONTROLFILE command, 4-217
of filespec, 4-435
REUSE STORAGE option
of TRUNCATE command, 4-542
REVERSE option
of ALTER INDEX command, 4-33
of CREATE INDEX command, 4-242
REVOKE command, 4-478,
4-481
examples, 4-480,
4-484
revoking
object privileges from users and roles, 4-481
system privileges and roles from users, roles, 4-478
revoking object type privileges, 4-482
RN format element, 3-65
RNDS parameter
of CREATE TYPE command, 4-148
RNDS parameter of CREATE TYPE command, 4-352
RNPS parameter
of CREATE TYPE command, 4-148
RNPS parameter of CREATE TYPE command, 4-352
roles
CONNECT role, 4-274
creating, 4-273
DBA role, 4-274
definition, 4-273
DELETE_CATALOG_ROLE role, 4-275
dropping, 4-400
enabling or disabling for sessions, 4-519
establishing default roles for users, 4-151,
4-153
EXECUTE_CATALOG_ROLE role, 4-275
granting, 4-438
granting object privileges to, 4-447
granting system privileges and roles to, 4-437
granting to users and roles, 4-437
predefined by Oracle8, 4-274
RESOURCE role, 4-274
revoking from users and roles, 4-478
revoking object privileges from, 4-481
revoking system privileges and roles from, 4-478
SELECT_CATALOG_ROLE role, 4-275
roll back
to the same savepoint multiple times, 4-488
ROLLBACK command, 4-487
ending a transaction, 4-488
examples, 4-488
summary, 4-8
ROLLBACK option
of ADVISE clause, 4-63
ROLLBACK SEGMENT option
of USING INDEX clause, 4-290
rollback segments
altering, 4-54
creating, 4-276
definition, 4-276
dropping, 4-401
optimal size of, 4-277,
4-529
shrinking size, 4-55,
4-277,
4-529
snapshots, 4-295
specifying tablespaces for, 4-276,
4-277
storage characteristics of, 4-276
SYSTEM rollback segment, 4-329
taking online and offline, 4-55,
4-401
ROLLBACK_SEGMENTS, 4-55
rolling back
transactions, 4-487
ROUND date function, 3-39,
3-40
format models for, 3-40
ROUND number function, 3-22
rounding numeric data, 2-11
by using scale, 2-11
row address
ROWID, 2-18
row exclusive locks, 4-462
row share locks, 4-462
ROWID
description of, 2-18
pseudocolumn, 2-36
ROWID option
of ADD clause
of ALTER SNAPSHOT LOG command, 4-87
ROWIDTOCHAR conversion function, 3-44
ROWNUM pseudocolumn, 2-37
and ORDER BY clause, 2-38
rows
accessing via ROWID, 2-36
deleting from tables and views, 4-375
identifying with ROWID values, 2-37
inserting into tables and views, 4-454
ordering, 4-504
selecting from tables, 4-492
updating, 4-545
RPAD character function, 3-29
RR date format element, 3-72
RTRIM character function, 3-30
RX locks, 4-462
S
S format element, 3-65
SAMPLE parameter
of ANALYZE command, 4-159
SAVEPOINT command, 4-490
examples, 4-491
summary, 4-8
savepoints
creating, 4-490
erasing with COMMIT command, 4-186
scalar
definition, 4-147,
4-351
scale
negative, 2-11
of NUMBER columns, 2-10
SCAN_INSTANCES parameter
of ALTER SYSTEM command, 4-95
schema object
naming rules, 2-47
schema object name
qualifiers, 2-47
schema object naming, 2-47
schema objects
definition of, 2-44
namespaces for, 2-48
schemas
creating, 4-279
SCOPE clause
defined, 4-326
SCOPE IS clause
of CREATE TABLE command, 4-314,
4-315
searching
for rows with an index, 4-244
security
provided by views, 4-367
security domains, 4-153
segment_attributes_clause
of CREATE TABLE, 4-310
segment_partition_clause
of ALTER TABLE, 4-116
SELECT
outer_join, 4-511
SELECT clause
INSERT command, 4-457
UPDATE command, 4-548,
4-551
SELECT command, 4-492
examples, 2-35,
4-497,
4-501,
4-503,
4-505,
4-506,
4-507,
4-509,
4-536,
4-540
summary, 4-7
WITH_clause, 4-494
select list, 4-496
SELECT object auditing option, 4-175
SELECT object privilege
on sequences, 4-451
on tables, 4-450
on views, 4-450
SELECT_CATALOG_ROLE role, 4-275
self joins, 4-510
Send Us Your Comments
boilerplate, xv
SEQ parameter
of ARCHIVE LOG clause, 4-169
SEQUEL (Structured English Query Language), 1-1
sequences, 4-282
accessing values of, 2-33,
4-539
altering, 4-57
creating, 4-282
cycling values of, 4-285
dropping, 4-402
increment between values, 4-57,
4-283
incrementing values of, 2-33,
4-539
limiting values of, 4-285
losing values of, 4-286
performance benefits of, 4-284
renaming, 4-476
restarting, 4-402
skipping values of, 4-284
SERIALIZABLE option
of ISOLATION_LEVEL parameter
of ALTER SESSION command, 4-65
serializing transactions, 4-65
session control commands, 4-8
session cursors, 4-73
SESSION_CACHED_CURSORS, 4-73
SESSION_CACHED_CURSORS parameter
of ALTER SESSION command, 4-67
SESSIONID option
of USERENV function, 3-55
sessions
altering, 4-59
closing database links for, 4-73
disconnecting, 4-97,
4-105
enabling and disabling error reporting of unusable indexes, 4-67
enabling and disabling roles for, 4-519
enabling and disabling SQL trace facility for, 4-67,
4-68
National Language Support (NLS) settings for, 4-65,
4-68
terminating, 4-97,
4-104
SET clause
of EXPLAIN PLAN command, 4-429
of UPDATE command, 4-548
SET CONSTRAINT(S) command, 4-517
SET DATABASE parameter
of CREATE CONTROLFILE command, 4-217
set operators, 4-504
SET ROLE command, 4-519
examples, 4-521
SET TRANSACTION command, 4-522
examples, 4-524
summary, 4-8
set_clause
of ALTER SYSTEM, 4-91
share locks, 4-462
share row exclusive locks, 4-462
share update locks, 4-462
SHARED option
of CREATE DATABASE LINK command, 4-227
shared pool
clearing, 4-98
shared server processes
creating and terminating, 4-100
shared SQL area
session cursors, 4-72
SHRINK clause
of ALTER ROLLBACK SEGMENT command, 4-55
shrinking
rollback segments, 4-277,
4-529
simple join
example, 4-509
simple snapshots, 4-292
simultaneous update and query on tables, 4-523
SIN number function, 3-23,
3-24
SINH number function, 3-23
SIZE parameter
of ALLOCATE EXTENT clause, 4-12,
4-34,
4-119
of ALTER CLUSTER command, 4-12
of CREATE CLUSTER command, 4-210,
4-213
of filespec, 4-435
SKIP_UNUSABLE_INDEXES option
of ALTER SESSION command, 4-67
snapshot logs
altering, 4-85
creating, 4-298
definition, 4-298
dropping, 4-406
storage characteristics of, 4-300
snapshot refresh, job queue processes, 4-294
SNAPSHOT_REFRESH_INTERVAL, 4-294
SNAPSHOT_REFRESH_KEEP_CONNECTIONS, 4-294
SNAPSHOT_REFRESH_PROCESSES, 4-294
snapshots
adding Comments to, 4-184
adding to clusters, 4-290
altering, 4-77
complex, 4-292
creating, 4-287
definition, 4-287,
4-292
dropping, 4-404
partitioned, 4-84,
4-297
primary key, 4-296
refresh modes, 4-293
refresh times, 4-294
refreshing, 4-81,
4-291
refreshing with snapshot logs, 4-300
removing Comments from, 4-184
rollback segments, 4-295
rowid, 4-296
simple, 4-292
storage characteristics of, 4-80,
4-290
types of, 4-292
SOME comparison operator, 3-5
SOUNDEX character function, 3-30
space
deallocating, 4-373
SPECIFICATION option
of COMPILE clause
of ALTER TYPE command, 4-146
SPLIT PARTITION option
of ALTER SNAPSHOT command, 4-80,
4-87
of ALTER TABLE command, 4-122
split_partition_clause
of ALTER INDEX, 4-32
of ALTER TABLE, 4-116
SQL
conversion functions, 3-42
embedded, 1-3
functions, 3-16,
3-78
history of, 1-1
lexical conventions, 1-4
optimizer, 1-3
standards, 1-2
summary of commands, 4-2
unified language, 1-3
SQL (Structured Query Language), 1-1
SQL functions
character, 3-25
group, 3-57
SQL trace facility
enabling and disabling for sessions, 4-67,
4-68
SQL/DS
datatypes, 2-20
SQL_TRACE, 4-68
SQL2, 1-2
SQL-92, 1-2
SRX locks, 4-462
standard deviation, 3-59
standby database
RECOVER clause, 4-473
START WITH clause
of CREATE SEQUENCE command, 4-283
of SELECT command, 4-495,
4-499,
4-501
START WITH parameter
of REFRESH clause, 4-81,
4-291
statement auditing options, 4-173
statement auditing short cuts, 4-177
states of constraints, 4-206,
4-422
STDDEV group function, 3-59
storage
ALTER TABLESPACE, 4-134
storage characteristics
of clusters, 4-11,
4-209,
4-210
of indexes, 4-29,
4-242
of rollback segments, 4-276
of snapshot logs, 4-300
of snapshots, 4-80,
4-290
of tables, 4-315,
4-316
STORAGE clause, 4-526
examples, 4-530
of ALTER CLUSTER command, 4-12
of ALTER INDEX command, 4-33
of ALTER TABLE command, 4-118
of CREATE CLUSTER command, 4-210
of CREATE INDEX command, 4-242
of CREATE ROLLBACK SEGMENT command, 4-276
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-316
stored functions
adding to packages, 4-252
creating, 4-233
definition, 4-233
dropping, 4-391
overloading, 2-49,
4-252
PL/SQL, 3-60
recompiling, 4-27
redefining, 4-235
removing from packages, 4-391
stored procedures
procedures, 4-260
storing LOBs, 4-118,
4-318,
4-319
storing nested tables, 4-119
string to date conversion, 3-77
Subqueries
WITH_clause, 4-534
subqueries, 4-533
correlated, 4-537
flattened, 4-536
subquery
of CREATE VIEW, 4-365
of DELETE, 4-376
SUBSTR character function, 3-31
SUM group function, 3-59,
3-60
suppressing blank padding
in date format models, 3-75
SWITCH LOGFILE option
of ALTER SYSTEM command, 4-103
switching
redo log files, 4-89,
4-103
SYEAR date format element, 3-72
synonyms
auditing, 4-180
creating, 4-303
definition, 4-303
dropping, 4-407
granting object privileges on, 4-451
renaming, 4-476
scope of, 4-305
using with database links, 4-229
SYSDATE date function, 3-40
system change numbers
specifying for forced transactions, 4-187
system control commands, 4-9
system privileges
granting, 4-438
granting to users and roles, 4-437
revoking from users and roles, 4-478
SYSTEM rollback segment, 4-329
SYSTEM tablespace, 4-329,
4-332
T
table alias, 4-550
table constraints, 4-189
example, 4-204
TABLE option
of ANALYZE command, 4-159
of subquery, 4-535
of TRUNCATE command, 4-541
table partitions
modifying, 4-129
table_partition_clause
of CREATE TABLE, 4-313
table_ref_clause
of ALTER TABLE, 4-110
of CREATE TABLE, 4-310
tables
adding columns to, 4-107,
4-117,
4-123
adding Comments to, 4-184
adding integrity constraints to, 4-117,
4-124
adding LOB columns to, 4-126
adding to clusters, 4-319
adding triggers to, 4-334
aliases for, 4-495
allocating extents for, 4-107
allowing writes, 4-107
altering, 4-107
creating, 4-307
creating index-organized tables, 4-307
creating snapshot logs for, 4-298
creating views on, 4-364
definition, 4-307
deleting rows from, 4-375,
4-408,
4-541
disallowing writes, 4-107
dropping, 4-408
dropping integrity constraints from, 4-385
dropping triggers from, 4-412
granting object privileges on, 4-450
inserting rows into, 4-454
LOB storage characteristics of, 4-118,
4-318
locking, 4-461
recoverability, 4-317
redefining columns of, 4-107
removing Comments from, 4-184
removing from clusters, 4-387
renaming, 4-476
selecting data from, 4-492
specifying tablespaces for, 4-316
storage characteristics of, 4-107,
4-118,
4-307,
4-315,
4-316
truncating, 4-541
updating rows in, 4-545
TABLESPACE option
of CREATE CLUSTER command, 4-210
of CREATE INDEX command, 4-242
of CREATE ROLLBACK SEGMENT command, 4-276
of CREATE SNAPSHOT command, 4-290,
4-300
of CREATE TABLE command, 4-316
of RECOVER clause, 4-474
tablespaces, 4-329
altering, 4-134
assigning to users, 4-151
backing up, 4-134,
4-139
changing future storage allocations, 4-134
creating, 4-329,
4-332
creating clusters in, 4-210
creating indexes in, 4-242
creating rollback segments in, 4-276,
4-277
datafiles of, 4-134,
4-138,
4-330
dropping, 4-410
establishing default tablespaces for users, 4-151
establishing tablespace quotas for users, 4-360,
4-361
establishing temporary tablespaces for users, 4-151
specifying for tables, 4-316
SYSTEM tablespace, 4-329,
4-332
taking online and offline, 4-134,
4-138,
4-331
TAN number function, 3-24
TANH number function, 3-24
TEMPORARY option
of ALTER TABLESPACE command, 4-139
TERMINAL option
of USERENV function, 3-55
terminating
dispatcher processes (DISP), 4-101
sessions, 4-97,
4-104
shared shadow processes, 4-100
text
definition of, 2-2
THE keyword
in flattened subqueries, 4-536
of SELECT command, 4-495
of subquery, 4-535
THREAD parameter
of ADD LOGFILE clause, 4-20
of ARCHIVE LOG clause, 4-169
threads
redo log threads, 4-222
TO clause
of GRANT command, 4-449
of ROLLBACK command, 4-487
TO parameter
of ARCHIVE LOG clause, 4-169
TO_CHAR conversion function, 3-44
examples, 3-63,
3-64,
3-76
TO_DATE conversion function, 3-46
TO_MULTI_BYTE conversion function, 3-47
TO_NUMBER conversion function, 3-47
TO_SINGLE_BYTE conversion function, 3-47
transaction control commands, 4-8
transactions, 4-187
committing, 4-186
distributed, 4-187,
4-489
establishing as read-only, 4-522
read consistency, 4-523
read only, 4-524
rolling back, 4-487
serializing, 4-65
TRANSLATE character function, 3-32
TRANSLATE USING conversion function, 3-48
triggered action, 4-340
triggers
creating, 4-334
definition, 4-334
dropping from tables, 4-412
enabling and disabling, 4-143
executing, 4-337
firing, 4-337
INSTEAD OF, 4-343
LONG datatype, 2-13
types of, 4-340
TRUE
result of a condition, 3-90
TRUNC date function, 3-40
format models for, 3-40
TRUNC number function, 3-25
TRUNCATE command, 4-541
examples, 4-543
TRUNCATE PARTITION option
of ALTER TABLE command, 4-122
truncating
clusters, 4-541
tables, 4-541
truth tables, 3-12
type bodies
compiling, 4-146
type constructor
expression syntax, 3-82
type specifications
compiling, 4-146
types
user-defined, 2-22
U
UID function, 3-54
UNARCHIVED option
of CLEAR LOGFILE clause, 4-21
undo a transaction, 4-487
UNION ALL set operator, 4-496
examples, 3-14
UNION set operator, 4-496
examples, 3-14
unique keys, 4-191,
4-194
UNIQUE option
of DISABLE clause, 4-382
of ENABLE clause, 4-422
UNLIMITED clause
of ALTER DATABASE command, 4-24
UNLIMITED option
of ALTER PROFILE command, 4-45
of CREATE PROFILE command, 4-269
of QUOTA clause, 4-360
UNTIL CANCEL option
of RECOVER clause, 4-474
UNTIL CHANGE parameter
of RECOVER clause, 4-474
UNTIL TIME parameter
of RECOVER clause, 4-474
UNUSABLE LOCAL INDEXES option
of ALTER SNAPSHOT command, 4-81
of ALTER TABLE command, 4-121
UPDATE command, 4-545,
4-551
examples, 4-550
returning_clause, 4-547
subquery, 4-548
UPDATE object auditing options, 4-175
UPDATE object privilege
on tables, 4-450
on views, 4-450
UPDATE option
of CREATE TRIGGER command, 4-336
updating
rows in tables and views, 4-545
updating simple snapshots, 4-291
UPPER character function, 3-33
uppercase
significance in SQL statements, 1-5
uppercase and lowercase
significance in pattern matching, 3-9
user access verification
security domains, 4-153
user authentication
changing, 4-151
USER function, 3-54
user function, 4-233
expression syntax, 3-81
user functions, 3-60
USER_CLUSTERS view, 4-164
USER_INDEXES view, 4-162
USER_TAB_COLUMNS view, 4-164
USER_TABLES view, 4-162
user-defined types, 2-22
nested tables, 2-24
object types, 2-23
REFs, 2-23
VARRAYs, 2-23
USERENV function, 3-54
users
altering, 4-151
assigning profiles to, 4-151,
4-360
assigning resource limits to, 4-151
assigning tablespaces to, 4-151
changing authentication mechanism, 4-151
changing passwords, 4-151
creating, 4-358
definition, 4-358
dropping, 4-417
establishing default roles for, 4-151,
4-153
establishing default tablespaces for, 4-151
establishing passwords for, 4-273,
4-360
establishing tablespace quotas for, 4-360,
4-361
establishing temporary tablespaces for, 4-151
granting object privileges to, 4-447
granting system privileges and roles to, 4-437
revoking object privileges from, 4-481
revoking system privileges and roles from, 4-478
USING clause
of CREATE DATABASE LINK command, 4-227
USING INDEX option
of ALTER SNAPSHOT command, 4-81
of CONSTRAINT clause, 4-192
of CREATE SNAPSHOT command, 4-290
of ENABLE clause, 4-422
USING MASTER ROLLBACK SEGMENT clause
of ALTER SNAPSHOT command, 4-81
using_index_clause
of ENABLE clause, 4-421
ENABLE clause
using_index_clause, 4-421
UTLEXCPT.SQL, 4-424
UTLSAMPL.SQL, xxii
UTLXPLAN.SQL, 4-429
V
V format element, 3-65
V$LOG table, 4-21,
4-222
V$NLS_PARAMETERS table, 4-68
VALIDATE option
of ENABLE clause, 4-421
ofCONSTRAINT clause, 4-193
VALIDATE REF UPDATE option
of ANALYZE command, 4-159
validating constraints, 4-193
value
use in expression, 3-78
VALUE operator
expression syntax, 3-87
VALUES clause
of INSERT command, 4-456,
4-457
VALUES LESS THAN clause
of CREATE TABLE command, 4-320
VARCHAR datatype, 2-10
VARCHAR2 datatype, 2-9
similarity to RAW datatype, 2-15
VARGRAPHIC datatype, 2-22
variable length
date format models, 3-75
VARRAYs, 2-23
views
adding Comments to, 4-184
and DML commands, 4-368
creating, 4-364
definition, 4-364
deleting rows from, 4-375
dropping, 4-419
inherently updatable, 4-368
inserting rows into, 4-454
join views, 4-369
updatable, 4-369
locking, 4-461
partition, 4-371
redefining, 4-419
removing Comments from, 4-184
renaming, 4-476
updating rows in, 4-545
uses of, 4-367
VSIZE function, 3-55
W
WHEN clause
of CREATE TRIGGER command, 4-337
WHENEVER SUCCESSFUL clause
of AUDIT (Schema Objects) command, 4-180
WHERE clause
of SELECT command, 4-495
of UPDATE command, 4-548
wildcard characters
in pattern matching, 3-9
pattern matching, 3-8
WITH CONTEXT option
of CREATE FUNCTION command, 4-236
of CREATE PROCEDURE command, 4-263
WITH GRANT OPTION, 4-449
WITH_clause
of SELECT command, 4-494
of subqueries, 4-534
WNDS parameter
of CREATE TYPE command, 4-148
WNDS parameter of CREATE TYPE command, 4-352
WNPS parameter
of CREATE TYPE command, 4-148
WNPS parameter of CREATE TYPE command, 4-352
WORK option
of COMMIT command, 4-186
of ROLLBACK command, 4-487
Y
year
storing, 2-14
YEAR date format element, 3-72
Prev
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Library
Product
Contents
