1 – CONSTRAINT
Alters a constraint.
1.1 – Environment
You can use the ALTER CONSTRAINT statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
1.2 – Format
ALTER CONSTRAINT <constraint-name> ---------+
+---------------------------------------+
+--+--+--> COMMENT IS +-> '<text-literal>' -+--+--+-->
| | +------- / <----------+ | |
| +--> constraint-attributes --------------+ |
| +--> RENAME TO <new-constraint-name> ----+ |
+-------------------- <------------------------+
constraint-attributes =
-+-> DEFERRABLE -------------+------------------------------+-+->
| +-> INITIALLY +-> IMMEDIATE --++ |
| +-> DEFERRED ---+ |
+-> NOT DEFERRABLE ---------+-------------------------+------+
| +-> INITIALLY IMMEDIATE --+ |
+-> INITIALLY IMMEDIATE ----+-------------------+------------+
| +-> DEFERRABLE -----+ |
| +-> NOT DEFERRABLE -+ |
+-> INITIALLY DEFERRED -----+-------------------+------------+
+-> DEFERRABLE -----+
1.3 – Arguments
1.3.1 – COMMENT_IS
Adds a comment about the constraint. SQL displays the text of the
comment when it executes a SHOW CONSTRAINTS statement. Enclose
the comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
1.3.2 – constraint-attributes
See the ALTER TABLE statement.
1.3.3 – constraint-name
The name of the table whose definition you want to change.
1.3.4 – RENAME_TO
Changes the name of the constraint being altered. See the the
RENAME statement for further discussion. If the new name is the
name of a synonym then an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE statement SYNONYMS ARE
ENABLED clause. Note that these synonyms may be deleted if they
are no longer used by database definitions or applications.
1.4 – Example
This example shows how ALTER CONSTRAINT can be used to change the
constraint attributes and add a comment to a constraint.
SQL> set dialect 'sql99';
SQL> attach 'file db$:mf_personnel';
SQL>
SQL> create table PERSON
cont> (last_name char(20)
cont> constraint MUST_HAVE_LAST_NAME
cont> not null
cont> deferrable,
cont> first_name char(20),
cont> birthday date
cont> constraint MUST_BE_IN_PAST
cont> check (birthday < current_date)
cont> not deferrable,
cont> constraint ALL_UNIQUE
cont> unique (last_name, first_name, birthday)
cont> deferrable initially immediate
cont> );
SQL>
SQL> show table (constraint) PERSON
Information for table PERSON
Table constraints for PERSON:
ALL_UNIQUE
Unique constraint
Null values are considered distinct
Table constraint for PERSON
Evaluated on each VERB
Source:
UNIQUE (last_name, first_name, birthday)
MUST_BE_IN_PAST
Check constraint
Column constraint for PERSON.BIRTHDAY
Evaluated on UPDATE, NOT DEFERRABLE
Source:
CHECK (birthday < current_date)
MUST_HAVE_LAST_NAME
Not Null constraint
Column constraint for PERSON.LAST_NAME
Evaluated on COMMIT
Source:
PERSON.LAST_NAME NOT null
Constraints referencing table PERSON:
No constraints found
SQL>
SQL> alter constraint ALL_UNIQUE
cont> deferrable initially deferred;
SQL>
SQL> alter constraint MUST_HAVE_LAST_NAME
cont> comment is 'We must assume all persons have a name'
cont> not deferrable;
SQL>
SQL> alter constraint MUST_BE_IN_PAST
cont> deferrable initially immediate;
SQL>
SQL> show table (constraint) PERSON
Information for table PERSON
Table constraints for PERSON:
ALL_UNIQUE
Unique constraint
Null values are considered distinct
Table constraint for PERSON
Evaluated on COMMIT
Source:
UNIQUE (last_name, first_name, birthday)
MUST_BE_IN_PAST
Check constraint
Column constraint for PERSON.BIRTHDAY
Evaluated on each VERB
Source:
CHECK (birthday < current_date)
MUST_HAVE_LAST_NAME
Not Null constraint
Column constraint for PERSON.LAST_NAME
Evaluated on UPDATE, NOT DEFERRABLE
Comment: We must assume all persons have a name
Source:
PERSON.LAST_NAME NOT null
Constraints referencing table PERSON:
No constraints found
SQL>
SQL> commit;
2 – DATABASE
Alters a database in any of the following ways:
o For single-file and multifile databases, the ALTER DATABASE
statement changes the characteristics of the database root
file.
The ALTER DATABASE statement lets you override certain
characteristics specified in the database root file parameters
of the CREATE DATABASE statement, such as whether or not a
snapshot file is disabled. In addition, ALTER DATABASE lets
you control other characteristics that you cannot specify in
the CREATE DATABASE database root file parameters, such as
whether or not after-image journaling is enabled.
o For single-file and multifile databases, the ALTER DATABASE
statement changes the storage area parameters.
o For multifile databases only, the ALTER DATABASE statement
adds, alters, or deletes storage areas.
2.1 – Environment
You can use the ALTER DATABASE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2.2 – Format
ALTER DATABASE -+-> FILENAME <db-attach-spec> -++---------------------++
+-> PATHNAME <path-name> ------++> literal-user-auth -+|
+----------------------------------------------------------------------+
+-+-+-----------------------------------------+-+---------------------->
| +-> alter-root-file-params1 --------------+ |
| +-> alter-root-file-params2 --------------+ |
| +-> alter-root-file-params3 --------------+ |
| +-> alter-journal-params -----------------+ |
| +-> alter-storage-area-params ------------+ |
| +-> add-row-cache-clause -----------------+ |
| +-> add-journal-clause -------------------+ |
| +-> add-storage-area-clause --------------+ |
| +-> alter-row-cache-clause ---------------+ |
| +-> alter-journal-clause -----------------+ |
| +-> alter-storage-area-clause ------------+ |
| +-> drop-clause --------------------------+ |
+--------------------<------------------------+
db-attach-spec =
--+----------------+-> <file-spec> --->
+-> <node-spec> -+
node-spec =
-+-> <nodename> -+-------------------+-+->
| +-> <access-string> + |
+------------------ :: <--------------+
access-string =
-+-> " <user-name> <password> " --+->
+-> " <VMS-proxy-user-name> " ---+
literal-user-auth =
---> USER '<username>' -+------------------------+-->
+-> USING '<password>' -+
alter-root-file-params1 =
-+-> attach-options ----------------------------------------------+->
+-> NUMBER OF USERS IS --> <number-users> -----------------------+
+-> NUMBER OF BUFFERS IS --> <number-buffers> -------------------+
+-> NUMBER OF CLUSTER NODES IS -> <number-nodes> ---+ |
| +-----------------------------------------------+ |
| ++------------------------------------------+----------------+
| +> ( -+-> SINGLE ---+--> INSTANCE --> ) ---+ |
| +-> MULTIPLE -+ |
+-> NUMBER OF RECOVERY BUFFERS IS -> <number-buffers> -----------+
+-> BUFFER SIZE IS <buffer-blocks> BLOCKS -----------------------+
+-> SNAPSHOT IS -+> ENABLED -+------>------+-+-------------------+
| | +> IMMEDIATE -+ | |
| | +> DEFERRED --+ | |
| +> DISABLED ----------------+ |
+-> DICTIONARY IS ------+-> REQUIRED -------+--------------------+
| +-> NOT REQUIRED ---+ |
+-> DICTIONARY IS ------+-> USED -----------+--------------------+
| +-> NOT USED -------+ |
+-> ADJUSTABLE LOCK GRANULARITY IS -+-> ENABLED -> alg-options -++
+-> DISABLED ---------------+
attach-options =
-+> MULTISCHEMA IS +> ON ---+--------------------------------------+->
| +> OFF --+ |
+> OPEN IS -+> MANUAL --------------------------------------------+
+> AUTOMATIC +---------------------------------------++
+> ( WAIT <n> -> MINUTES -> FOR CLOSE ) +
alg-options =
--+------------------------------+-->
+-> ( --> COUNT IS <n> --> ) --+
alter-root-file-params2 =
-+-> global-buffer-params ---------------------------------------+->
+-> CARDINALITY COLLECTION IS ------------+-+-> ENABLED ----+---+
+-> CARRY OVER LOCKS ARE -----------------+ +-> DISABLED ---+ |
+-> GALAXY SUPPORT IS --------------------+ |
+-> LOCK PARTITIONING IS -----------------+ |
+-> LOGMINER SUPPORT IS ------------------+ |
+-> METADATA CHANGES ARE -----------------+ |
+-> STATISTICS COLLECTION IS -------------+ |
+-> WORKLOAD COLLECTION IS ---------------+ |
+-> prestarted-transaction-params ------------------------------+
+-> LOCK TIMEOUT INTERVAL IS <number-seconds> SECONDS ----------+
+-> RESERVE <n> +-> CACHE SLOTS ----+---------------------------+
| +-> JOURNALS -------+ |
| +-> STORAGE AREAS --+ |
| +-> SEQUENCES ------+ |
+-> ROW CACHE IS -+-> ENABLED --+--+-----------------------+----+
| +-> DISABLED -+ +-> row-cache-options -+ |
+-> SET --+--> TRANSACTION MODES -+--> txn-modes -+---->) -+
+-> ALTER -+ +-----> , ----------+
global-buffer-params=
--> GLOBAL BUFFERS ARE -+-> ENABLED --+--------------+
+-> DISABLED -+ |
+----------------------------------------------------+
+---------------------------------------------------+--->
+> ( -++-> NUMBER IS <number-glo-buffers> ----++> ) +
|+-> USER LIMIT IS <max-glo-buffers> ---+|
|+-> PAGE TRANSFER VIA --+-> DISK ---+--+|
|| +-> MEMORY -+ |
|+-> LARGE MEMORY IS --+--> ENABLED --+--+
| +--> DISABLED -+ |
+------------------- , <----------------+
prestarted-transaction-params =
-> PRESTARTED TRANSACTIONS ARE -++--> ENABLED -++----------------------------++>
|+--> ON -----++->(prestart-trans-options) -+|
+-+-> DISABLED --+----------------------------+
+-> OFF -------+
prestart-trans-options =
-+---------------------------------+->
+-> WAIT <n> SECONDS FOR TIMEOUT -+
+-> WAIT <n> MINUTES FOR TIMEOUT -+
+-> NO TIMEOUT -------------------+
row-cache-options =
-> ( -++-> CHECKPOINT -+-> ALL ROWS TO BACKING FILE -----------+-++-> ) -->
|| +-> TIMED EVERY <n> SECONDS ------------+ ||
|| +-> UPDATED ROWS TO -+-> BACKING FILE -++ ||
|| +-> DATABASE -----+ ||
|+-> LOCATION IS --> <directory-spec> ---------------------+|
|+-> NO --+--> LOCATION --------+--------------------------+|
|| +--> SWEEP INTERVAL --+ ||
|+-> NUMBER OF SWEEP ROWS IS <n> --------------------------+|
|+-> SWEEP INTERVAL IS <n> SECONDS ------------------------+|
+-------------------------- , <-----------------------------+
txn-modes =
-+-+-------+-+-> READ ONLY -----------------+-->
| +-> NO -+ +-> READ WRITE ----------------+
| +-> BATCH UPDATE --------------+
| +-> SHARED ----+--+----------+-+
| +-> PROTECTED -+ +-> READ --+ |
| +-> EXCLUSIVE -+ +-> WRITE -+ |
+-------------> ALL -----------------------+
+-------------> NONE ----------------------+
alter-root-file-params3 =
-+> ASYNC BATCH WRITES ARE -+-> ENABLED -> async-bat-wr-options ++>
| +-> DISABLED -----------------------+|
++------------+-> ASYNC PREFETCH IS --+ |
|+> DETECTED -+ +-------------------+ |
| +-+-> ENABLED ---> async-prefetch-options +---+
| +-> DISABLED ---------------------------+ |
++------+-> INCREMENTAL BACKUP SCAN OPTIMIZATION ---------------+
|+> NO -+ |
+> RECOVERY JOURNAL -> ( -> ruj-options -> ) -------------------+
+> SECURITY CHECKING IS ---> security-checking-options ---------+
+> SYNONYMS ARE ENABLED ----------------------------------------+
+> SHARED MEMORY IS --+-> SYSTEM -------------------+-----------+
| +-> PROCESS --+-------------+-+ |
| +-> RESIDENT -+ |
+> NOTIFY IS -+->ENABLED ---> notify-options -+---------------+
+->DISABLED ----------------------+
asynch-bat-wr-options =
-+----------------------------->-------------------------------+->
+ ( ++-> CLEAN BUFFER COUNT IS <buffer-count> BUFFERS --++> ) +
|+-> MAXIMUM BUFFER COUNT IS <buffer-count> BUFFERS +|
+------------------- , <-----------------------------+
async-prefetch-options =
--+-----------------------------------------------------------+>
+-> ( -+-+-> DEPTH IS <number-buffers> BUFFERS -----+--+> )-+
| +-> THRESHOLD IS <number-buffers> BUFFERS -+ |
+----------------- , <--------------------------+
ruj-options =
-+-> LOCATION IS --> <directory-spec> -+-->
+-> NO LOCATION ----------------------+
+-> BUFFER MEMORY IS -+-> LOCAL ------+
+-> GLOBAL -----+
security-checking-options =
-+-> EXTERNAL -+------------------------------------------------++->
| +-> ( -> PERSONA SUPPORT IS -+-> ENABLED --+> ) -+|
| +-> DISABLED -+ |
+-> INTERNAL -+-----------------------------------------------+-+
+-> ( -> ACCOUNT CHECK IS -+---> ENABLED --+> ) +
+---> DISABLED -+
alter-journal-params =
-> JOURNAL IS ---+
+----------------+
+-+> ENABLED +----------------------------------------++->
| +> ( +-+-> aij-control-options-1 -+-+> ) +|
| | +-> aij-control-options-2 -+ | |
| +------------- , <-------------+ |
+> DISABLED -----------------------------------------+
aij-control-options-1 =
--+-> ALLOCATION IS <n> BLOCKS ----------------------------+->
+-> BACKUP SERVER IS +> AUTOMATIC +> <backup-file-spec>--+
| +> MANUAL ---+ |
+-> BACKUP FILENAME ---> <backup-file-spec> --+ |
| +-----------------------------------------+ |
| +-> backup-filename-options -------------------------+
+-> NO BACKUP FILENAME ----------------------------------+
+-> EXTENT IS <n> BLOCKS --------------------------------+
backup-filename-options =
-+------------------------------------------------------+->
+-> ( -+-> NO EDIT STRING ----------------------+-> ) -+
+-> EDIT STRING IS -+-+-> SEQUENCE --+-+-+
| +-> YEAR ------+ |
| +-> MONTH -----+ |
| +-> DAY -------+ |
| +-> HOUR ------+ |
| +-> MINUTE ----+ |
| +-> JULIAN ----+ |
| +-> WEEKDAY ---+ |
| +-> literal ---+ |
+--------- + <-----+
aij-control-options-2 =
-+-> FAST COMMIT IS -+> ENABLED ---> fc-options --+-------+->
| +> DISABLED -----------------+ |
+-> LOG SERVER IS -+> MANUAL ----+-----------------------+
| +> AUTOMATIC -+ |
+-> OVERWRITE IS --+> ENABLED --+------------------------+
| +> DISABLED -+ |
+-> SHUTDOWN TIME IS <n> MINUTES ------------------------+
fc-options =
--+------------------------------------------------------------------+->
+-> ( -++> CHECKPOINT -+-> INTERVAL IS <n> BLOCKS ------+-+-+-> ) -+
|| +-> TIMED EVERY <n> SECONDS -----+ | |
|| +-> EVERY <n> TRANSACTIONS ------+ | |
|++-------+-> COMMIT TO JOURNAL OPTIMIZATION ------+ |
||+-> NO -+ | |
|+-> TRANSACTION INTERVAL IS <number-txns> --------+ |
+----------------- , <-------------------------------+
notify-options =
--+-----------------------------------------------------+-->
+--> ( ALERT OPERATOR -+--> operator-class -+-- ) --+
+--------- + <---------+
operator-class =
---+--+------+-+--> CENTRAL -----+--->
| +> NO -+ +--> DISKS -------+
| +--> CLUSTER -----+
| +--> CONSOLE -----+
| +--> SECURITY ----+
| +--> OPER1 -------+
| +--> OPER2 -------+
| +--> OPER3 -------+
| +--> OPER4 -------+
| +--> OPER5 -------+
| +--> OPER6 -------+
| +--> OPER7 -------+
| +--> OPER8 -------+
| +--> OPER9 -------+
| +--> OPER10 ------+
| +--> OPER11 ------+
| +--> OPER12 ------+
+--------------> ALL ---------+
+--------------> NONE --------+
extent-params =
--+-> EXTENT IS -+-> ENABLED -------------------++-->
| +-> DISABLED ------------------+|
| +-> <extent-pages> --> PAGES --+|
| +-> (extension-options) -------+|
+---------------------<------------------------+
extension-options =
---> MINIMUM OF <min-pages> PAGES, --+
+------------------------------------+
+--> MAXIMUM OF <max-pages> PAGES, --+
+------------------------------------+
+--> PERCENT GROWTH IS <growth> ------->
add-journal-clause =
---> ADD JOURNAL -----> <journal-name> ------+
+--------------------------------------------+
++---------------------------------+-+-+--------------------+-+->
+-> FILENAME <journal-file-spec> -+ | +-> add-aij-options -+ |
+---------- <------------+
add-aij-options =
-+-> ALLOCATION IS --> <n> -> BLOCKS ---------------+-->
+-> EXTENT IS --> <n> -> BLOCKS -------------------+
+-> BACKUP FILENAME -> <backup-file-spec> ---+ |
| +------------------------------------------+ |
| +--> backup-filename-options --------------------+
+-> SAME BACKUP FILENAME AS JOURNAL ---------------+
+-> NO BACKUP FILENAME ----------------------------+
add-storage-area-clause =
---> ADD STORAGE AREA <area-name> --+
+-----------------------------------+
+-+-------------------------+-++-> storage-area-params-1 -+-+->
+-> FILENAME <file-spec> -+ |+-> storage-area-params-2 -+ |
+-------------- <-------------+
storage-area-params-1 =
--+-> ALLOCATION IS ---> <number-pages> --> PAGES -----+->
+-> CACHE USING <row-cache-name> --------------------+
+-> NO ROW CACHE ------------------------------------+
+-> extent-params -----------------------------------+
+-> INTERVAL IS --> <number-data-pages> -------------+
+-> LOCKING IS --+-> ROW --+--> LEVEL ---------------+
| +-> PAGE -+ |
+-> PAGE FORMAT IS +-> UNIFORM -+--------------------+
| +-> MIXED ---+ |
+-> PAGE SIZE IS ----> <page-blocks> --> BLOCKS -----+
storage-area-params-2 =
--+-> CHECKSUM CALCULATION IS -----------+--+-> ENABLED --+-+->
+-> SNAPSHOT CHECKSUM CALCULATION IS --+ +-> DISABLED -+ |
+-> SNAPSHOT ALLOCATION IS --> <snp-pages> ----> PAGES ---+
+-> SNAPSHOT EXTENT IS -+-> <extent-pages> ----> PAGES --++
| +-> (extension-options) ---------+|
+-> SNAPSHOT FILENAME --> <file-spec> --------------------+
+-> THRESHOLDS ARE ( <val1> +-----------------------+> ) -+
| +> ,<val2> -+----------++ |
| +> ,<val3> + |
+------------------------------<--------------------------+
add-row-cache-clause =
---> ADD CACHE <row-cache-name> -+-+----------------------+-+->
| +-> row-cache-params1 -+ |
| +-> row-cache-params2 -+ |
+-------------<------------+
alter-row-cache-clause =
---> ALTER CACHE <row-cache-name> -+-+----------------------+-+->
| +-> row-cache-params1 -+ |
| +-> row-cache-params2 -+ |
+-------------<------------+
row-cache-params1 =
--+-> ALLOCATION IS <n> -+--+-----------+----------------+->
+-> EXTENT IS <n> -----+ +-> BLOCK --+ |
| +-> BLOCKS -+ |
+-> CACHE SIZE IS <n> -+----> ROW --+------------------+
| +----> ROWS -+ |
+-> CHECKPOINT -+> UPDATED ROWS TO -+> BACKING FILE -+-+
| | +> DATABASE -----+ |
| +> ALL ROWS TO BACKING FILE ---------+ |
+-> LARGE MEMORY IS ----+-+-> ENABLED --+--------------+
+-> ROW REPLACEMENT IS -+ +-> DISABLED -+ |
+-> LOCATION IS --> <directory-spec> ------------------+
+-> NO LOCATION ---------------------------------------+
row-cache-params2 =
--+-> NUMBER OF -+-> RESERVED -+-> ROWS IS <n> --------------+->
| +-> SWEEP ----+ |
+-> ROW LENGTH IS <n> -+-------------+---------------------+
| +----> BYTE --+ |
| +----> BYTES -+ |
| |
+-> ROW SNAPSHOT IS --+-> ENABLED rs-opt -+---------------+
| +-> DISABLED --------+ |
| |
+-> SHARED MEMORY IS --+----> SYSTEM -------------------+--+
| +----> PROCESS --+-------------+-+ |
| | | |
| +-> RESIDENT -+ |
| |
+-> WINDOW COUNT IS <n> -----------------------------------+
rs-opt =
--+-------------------------------+--->
+-> (CACHE SIZE IS <n> ROWS) ---+
alter-journal-clause =
---> ALTER JOURNAL ---+-> <journal-name> -++
+-> RDB$JOURNAL ----+|
+------------------------------------------+
++-> alter-aij-options --+------------------->
+----------<------------+
alter-aij-options =
-+-> JOURNAL IS --> UNSUPPRESSED ------------------+--->
+-> BACKUP FILENAME -----> <backup-file-spec> -+ |
| +------------------------------------------+ |
| +-> backup-filename-options ------------------+
+-> SAME BACKUP FILENAME AS JOURNAL---------------+
+-> NO BACKUP FILENAME ---------------------------+
alter-storage-area-clause =
---> ALTER STORAGE AREA <area-name> -+
+------------------------------------+
++---> alter-storage-area-params ---------------+-->
+----------------------------<-----------------+
alter-storage-area-params =
-+-> ALLOCATION IS --> <number-pages> --> PAGES ----------+->
+-> extent-params ---------------------------------------+
+-> CACHE USING <row-cache-name> ------------------------+
+-> NO ROW CACHE ----------------------------------------+
+-> LOCKING IS -+-> ROW -+-> LEVEL ---------------------+
| +-> PAGE -+ |
+-> READ WRITE ------------------------------------------+
+-> READ ONLY -------------------------------------------+
+-> SNAPSHOT ALLOCATION IS --> <snp-pages> --> PAGES ----+
+-> SNAPSHOT EXTENT IS +-> <extent-pages> --> PAGES ---+-+
| +-> (extension-options) --------+ |
+-> CHECKSUM CALCULATION IS ----------+--+-> ENABLED --+-+
+-> SNAPSHOT CHECKSUM CALCULATION IS -+ +-> DISABLED -+
drop-clause =
--+-> DROP CACHE <row-cache-name> ---+-+-------------+-+->
+-> DROP STORAGE AREA <area-name> -+ +-> CASCADE --+ |
| +-> RESTRICT -+ |
+-> DROP JOURNAL <journal-name> ---------------------+
2.3 – Arguments
2.3.1 – ADD CACHE row-cache-name
Adds a new row cache. For information regarding the row-cache-
params-1 and row-cache-params-2, see the descriptions under the
CREATE CACHE clause.
2.3.2 – ADD_JOURNAL
Creates a new journal file.
2.3.3 – ADD_STORAGE_AREA
Specifies the name and file specification for a storage area you
want to add to the database. You can use the ADD STORAGE AREA
clause only on multifile databases. The storage area name cannot
be the same as any other storage area name in the database.
The ADD STORAGE AREA clause creates two files: a storage area
file with an .rda file extension and a snapshot file with an
.snp file extension. If you omit the FILENAME argument, the file
specification uses the following defaults:
o Device-the current device for the process
o Directory-the current directory for the process
o File name-the name specified for the storage area
The file specification is used for the storage area and snapshot
files that comprise the storage area (unless you use the SNAPSHOT
FILENAME argument to specify a different file for the snapshot
file, which you can only specify with a multifile database).
Because the ADD STORAGE AREA clause creates two files with
different file extensions, do not specify a file extension with
the file specification.
If you use the ALTER DATABASE statement to add a storage area,
the change is journaled, however, you should back up your
database before making such a change.
2.3.4 – ADJUSTABLE_LOCK_GRANULARITY
Syntax options:
ADJUSTABLE LOCK GRANULARITY IS ENABLED | ADJUSTABLE LOCK
GRANULARITY IS DISABLED
Enables or disables whether or not the database system
automatically maintains as few locks as possible on database
resources. The default, ENABLED, results in fewer locks against
the database. However, if contention for database resources is
high, the automatic adjustment of locks can become a CPU drain.
You can trade more restrictive locking for less CPU usage in such
databases by disabling adjustable lock granularity.
2.3.5 – ALERT_OPERATOR
Specifies which operator will be notified of the occurrence of
a database system event. You can specify the following operator
classes:
Operator
Class Meaning
ALL The ALL operator class broadcasts a message to
all terminals that are enabled as operators and
that are attached to the system or cluster. These
terminals must be turned on and have broadcast-
message reception enabled.
NONE The NONE operator class inhibits the display of
messages to the entire system or cluster.
[NO] CENTRAL The CENTRAL operator class broadcasts messages
sent to the central system operator. The NO
CENTRAL operator class inhibits the display of
messages sent to the central system operator.
[NO] DISKS The DISKS operator class broadcasts messages
pertaining to mounting and dismounting disk
volumes. The NO DISKS operator class inhibits
the display of messages pertaining to mounting and
dismounting disk volumes.
[NO] CLUSTER The CLUSTER operator class broadcasts messages
from the connection manager pertaining to
cluster state changes. The NO CLUSTER operator
class inhibits the display of messages from the
connection manager pertaining to cluster state
changes.
[NO] CONSOLE The CONSOLE class broadcasts messages to the
Oracle Enterprise Manager (OEM). NO CONSOLE
inhibits broadcast to OEM.
[NO] The SECURITY operator class displays messages
SECURITY pertaining to security events. The NO SECURITY
operator class inhibits the display of messages
pertaining to security events.
[NO] OPER1 The OPER1 through OPER12 operator classes display
through [NO] messages to operators identified as OPER1 through
OPER12 OPER12. The NO OPER1 through NO OPER12 operator
classes inhibit messages from being sent to the
specified operator.
2.3.6 – ALLOCATION blocks for aij
Syntax option:
ALLOCATION IS n BLOCKS
Specifies the number of blocks allocated for the .aij file. The
default and minimum is 512 blocks. Even if you specify a value
less than 512 blocks, the .aij file is allocated 512 blocks.
For information on determining the allocation value, see the
Oracle Rdb Guide to Database Design and Definition.
2.3.7 – ALLOCATION IS n PAGES
Specifies the number of database pages allocated to the storage
area. The initial allocation never changes and is used for
the hash algorithm. The new allocation becomes the current
allocation. If you execute the RMU Dump/Header command, you see
the initial and the current allocation.
SQL automatically extends the allocation to handle the storage
requirements. Pages are allocated in groups of three (known as a
clump). An ALLOCATION of 25 pages actually provides for 27 pages
of data and subsequent expansion. The default is 700 pages.
The altered area is extended if the specified value exceeds
the current area allocation. Otherwise the specified value is
ignored.
2.3.8 – ALTER_CACHE
Alters an existing row cache.
For more information, see the following arguments:
o ALLOCATION_blocks_for_rdc
o CACHE_SIZE_IS_n_ROWS
o EXTENT_blocks_for_rdc
o LARGE_MEMORY
o LOCATION
o NO_LOCATION
o NUMBER_OF_RESERVED_ROWS
o SHARED_MEMORY
o ROW_REPLACEMENT
o ROW_LENGTH
o WINDOW_COUNT
2.3.9 – ALTER_JOURNAL
Alters existing journal files. RDB$JOURNAL is the default journal
name if no name is specified.
2.3.10 – alter-root-file-params
Parameters that control the characteristics of the database
root file associated with the database or that control the
characteristics that apply to the entire database. You can
specify these parameters for either single-file or multifile
databases except as noted in the individual parameter
descriptions. For more information about database parameters
and details about how they affect performance, see the Oracle
Rdb7 Guide to Database Performance and Tuning.
The ALTER DATABASE statement does not let you change all database
root file parameters that you can specify in the CREATE DATABASE
statement. You must use the EXPORT and IMPORT statements to
change a number of storage area parameters. For more information
on changing storage area parameters, see the IMPORT statement.
2.3.11 – alter-storage-area-params
Parameters that change the characteristics of database storage
area files. You can specify the same storage area parameters for
either single-file or multifile databases, but the effect of the
clauses in this part of an ALTER DATABASE statement differs.
o For single-file databases, the storage area parameters
change the characteristics for the single storage area in
the database.
o For multifile databases, the storage area parameters change
the characteristics of the RDB$SYSTEM storage area.
You can also change some of the characteristics of the
RDB$SYSTEM storage area using the ALTER STORAGE AREA clause.
However, you can only change the read-only and read/write
parameters in this part of the ALTER DATABASE statement. See
the ALTER_STORAGE_AREA topic in this Arguments list for more
information about the RDB$SYSTEM characteristics that you are
allowed to alter.
The ALTER DATABASE statement does not let you change all storage
area parameters you can specify in the CREATE DATABASE statement.
You must use the EXPORT and IMPORT statements to change the
following database root file parameters:
o INTERVAL
o PAGE FORMAT
o PAGE SIZE
o SNAPSHOT FILENAME
o THRESHOLDS
2.3.12 – ALTER_STORAGE_AREA
Specifies the name of an existing storage area in the database
that you want to alter. You can use the ALTER STORAGE AREA clause
only on multifile databases.
You can specify RDB$SYSTEM for the area-name if you are altering
the following clauses:
o ALLOCATION IS number-pages PAGES
o extent-params
o CACHE USING row-cache-name
o NO ROW CACHE
o SNAPSHOT ALLOCATION IS snp-pages PAGES
o SHAPSHOT EXTENT
o CHECKSUM CALCULTION
o SNAPSHOT CHECKSUM CALCULATION
Oracle Rdb generates an error if you specify RDB$SYSTEM or the
DEFAULT storage area as the area-name when altering the following
clauses:
o LOCKING IS PAGE LEVEL
o READ WRITE
o READ ONLY
If you want to change the read-only and read/write parameters of
the RDB$SYSTEM storage area using the ALTER DATABASE statement,
you must specify these parameters outside of the ALTER STORAGE
AREA clause.
2.3.13 – ALTER_TRANSACTION_MODES
Enables or disables the modes specified leaving the previously
defined or default modes enabled. This is an offline operation
and requires exclusive database access.
If the current transaction modes are SHARED and READ ONLY and you
want to add the EXCLUSIVE mode, use the following statement:
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER TRANSACTION MODES (EXCLUSIVE);
2.3.14 – ASYNC_BATCH_WRITES
Syntax options:
ASYNC BATCH WRITES ARE ENABLED | ASYNC BATCH WRITES ARE DISABLED
Specifies whether asynchronous batch-writes are enabled or
disabled.
Asynchronous batch-writes allow a process to write batches of
modified data pages to disk asynchronously (the process does not
stall while waiting for the batch-write operation to complete).
Asynchronous batch-writes improve the performance of update
applications without the loss of data integrity.
By default, batch-writes are enabled.
For more information about when to use asynchronous batch-writes,
see the Oracle Rdb7 Guide to Database Performance and Tuning.
You can enable asynchronous batch-writes by defining the logical
name RDM$BIND_ABW_ENABLED.
2.3.15 – ASYNC_PREFETCH
Syntax options:
ASYNC PREFETCH IS ENABLED | ASYNC PREFETCH IS DISABLED
Specifies whether or not Oracle Rdb reduces the amount of time
that a process waits for pages to be read from disk by fetching
pages before a process actually requests the pages.
Prefetch can significantly improve performance, but it may
cause excessive resource usage if it is used inappropriately.
Asynchronous prefetch is enabled by default. For more information
about asynchronous prefetch, see the Oracle Rdb7 Guide to
Database Performance and Tuning.
You can enable asynchronous prefetch by defining the logical name
RDM$BIND_APF_ENABLED.
2.3.16 – BACKUP_FILENAME
Syntax option:
BACKUP FILENAME backup-file-spec
Specifies the default file specification to be used by the backup
server.
During execution, the backup server and the RMU Backup After_
Journal command use this file specification as the name of the
backup file. You can override this value by specifying a file
name for the journal file using the RMU Backup After_Journal
command.
2.3.17 – backup-filename-options
Specifies whether or not the backup file name includes an edit
string. When the EDIT STRING clause is used, the specified backup
file name is edited by appending any or all of the edit string
options listed in the following table.
Edit String
Option Meaning
SEQUENCE The journal sequence number of the first journal
file in the backup operation.
YEAR The current year expressed as a 4-digit integer.
MONTH The current month expressed as a 2-digit integer
(01-12).
DAY The current day of the month expressed as a 2-
digit integer (00-31).
HOUR The current hour of the day expressed as a 2-digit
integer (00-23).
MINUTE The current minute of the hour expressed as a
2-digit integer (00-59).
JULIAN The current day of the year expressed as a 3-digit
integer (001-366).
WEEKDAY The current day of the week expressed as a 1-digit
integer (1-7) where 1 is Sunday and 7 is Saturday.
literal Any string literal. This string literal is copied
to the file specification. See Quoted Character
String for more information about string literals.
Use a plus sign (+) between multiple edit string options. The
edit string should be 32 characters or less in length.
The default is NO EDIT STRING which means the BACKUP FILENAME
supplied is all that is used to name the backup file.
2.3.18 – BACKUP_SERVER
Syntax options:
BACKUP SERVER IS AUTOMATIC backup-file-spec | BACKUP SERVER IS
MANUAL backup-file-spec
Specifies whether the backup server runs automatically or
manually.
If BACKUP SERVER IS MANUAL is specified, you must execute the
RMU Backup After_Journal command manually. If BACKUP SERVER
IS AUTOMATIC is specified, a special backup server runs when
a journal file in the set is full and causes a switch over to
another journal file.
The default is MANUAL.
2.3.19 – BUFFER_SIZE
Syntax option:
BUFFER SIZE IS buffer-blocks BLOCKS
Specifies the number of blocks Oracle Rdb allocates per buffer.
You need to specify an unsigned integer greater than zero. The
default buffer size is 3 times the PAGE SIZE value (6 blocks for
the default PAGE SIZE of 2).
The buffer size is a global parameter and the number of blocks
per page (or buffer) is constrained to 64 blocks per page. The
page size can vary by storage area for multifile databases, and
the page size should be determined by the sizes of the records
that will be stored in each storage area.
When choosing the number of blocks per buffer, choose a number so
that a round number of pages fits in the buffer. In other words,
the buffer size is wholly divisible by all page sizes for all
storage areas in your multifile database. For example, if you
have three storage areas with page sizes of 2, 3, and 4 blocks
each respectively, choosing a buffer size of 12 blocks ensures
optimal buffer utilization. In contrast, choosing a buffer size
of 8 wastes 2 blocks per buffer for the storage area with a page
size of 3 pages. Oracle Rdb reads as many pages as fit into the
buffer; in this instance it reads two 3-block pages into the
buffer, leaving 2 wasted blocks.
The altered buffer size must allow for existing page sizes. You
cannot specify a buffer size smaller than the largest existing
page size.
2.3.20 – CACHE_USING
Specifies that the named row cache is the default physical row
cache for all storage areas in the database. All rows stored in
each storage area are cached, regardless of whether they consist
of table data, segemented string data, or are special rows such
as index nodes.
You must either add the specified cache before completing the
ALTER DATABASE statement, or it must already exist.
Alter the database and storage area to asign a new physical
area row cache that overrides the database default physical area
row cache. Only one physical area row cache is allowed for each
storage area.
You can have multiple row caches that contain rows for a single
storage area by defining logical area row caches, where the row
cache name matches the name of a table or index.
If you do not specify the CACHE USING clause or the NO ROW CACHE
clause, then the NO ROW CACHE clause is the default.
2.3.21 – CARDINALITY_COLLECTION
Syntax options:
CARDINALITY COLLECTION IS ENABLED | CARDINALITY COLLECTION IS
DISABLED
Specifies whether or not the optimizer records cardinality
updates in the system tables. When enabled, the optimizer
collects cardinalities for tables and indexes as rows are
inserted or deleted from tables. The update of the cardinalities
is performed at commit time, if sufficient changes have
accumulated, or at disconnect time.
In high update environments, it may be more convenient to
disable cardinality updates. If you disable this feature, you
should manually maintain the cardinalities using the RMU Collect
Optimizer_Statistics command so that the optimizer is given the
most accurate values for estimation purposes.
Cardinality collection is enabled by default.
2.3.22 – CARRY_OVER_LOCKS
Syntax options:
CARRY OVER LOCKS ARE ENABLED | CARRY OVER LOCKS ARE DISABLED
Enables or disables carry-over lock optimization. Carry-over lock
optimization holds logical area locks (table and index) across
transactions. Carry-over locks are enabled by default and are
available as an online database modification.
For more information on carry-over lock optimization, see the
CREATE DATABASEstatement.
2.3.23 – CHECKPOINT EVERY n TRANSACTIONS
A FAST COMMIT option which allows the checkpoint to be generated
after a set number of transactions.
See the following example.
SQL> alter database
cont> filename db$:scratch
cont>
cont> journal is enabled
cont> (fast commit is enabled
cont> (checkpoint every 20 transactions,
cont> checkpoint timed every 20 seconds
cont> )
cont> )
cont> add journal rdb$journal
cont> filename db$:scratch_aij
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
2.3.24 – CHECKPOINT_INTERVAL
Syntax option:
CHECKPOINT INTERVAL IS n BLOCKS
You can limit how many transactions the database recovery process
(DBR) must redo by setting a checkpoint interval. Setting a
checkpoint interval instructs Oracle Rdb to periodically write
modified pages to disk. This shortens recovery time.
The value you assign to the checkpoint interval specifies the
number of blocks the .aij file is allowed to increase to before
updated pages are transferred. For example, if you set the
checkpoint interval value equal to 100, all processes transfer
updated pages to the disk when 100 blocks were written to
the .aij file since the last checkpoint. Thus all processes
contribute to .aij growth.
If no checkpoint interval is established and a process completes
1000 transactions but fails during number 1001, the DBR must redo
transactions 1 through 1000 and undo number 1001.
When a process attaches to the database, it writes a checkpoint
record to the .aij file and notes the virtual block number (VBN)
of the .aij file at which the checkpoint record is located. If
the checkpoint is located at VBN 120 and the checkpoint interval
is 100 blocks, the process checkpoints again when VBN 220 is
reached.
Because all processes contribute to .aij file growth, a process
may be able to commit many transactions before checkpointing
if update activity by other processes is low. Conversely, if a
process' first transaction is long and if update activity by
other processes is high, the process may be forced to checkpoint
when it commits its first transaction.
When the database checkpoint interval value is reached, Oracle
Rdb executes the following steps:
1. Writes updated pages to the disk.
2. Writes a checkpoint record to the .aij file.
3. Updates the run-time user process block (RTUPB) for each
process to indicate where the checkpoint record is stored
in the .aij file.
The RTUPB is a data structure in the database root file that
maintains information on each process accessing the database.
The database recovery process (DBR) uses the RTUPB checkpoint
entry to determine where in the .aij file recovery must start.
2.3.25 – CHECKPOINT TIMED for fast commit
Assigns a value to the checkpoint interval specifying the number
of seconds that can pass before updated pages are written. When
the specified number of seconds elapsed, Oracle Rdb executes the
checkpoint steps.
For example, if you specify TIMED EVERY 100 SECONDS, each process
checkpoints after at least 100 seconds have passed since its last
checkpoint.
You can set both a checkpoint based on time and a checkpoint
based on .aij file growth; Oracle Rdb performs a checkpoint
operation at whichever checkpoint it reaches first.
The following statement enables fast commit processing and
specifies checkpoint intervals of 512 blocks and 12 seconds:
SQL> ALTER DATABASE FILENAME test1
cont> JOURNAL IS ENABLED
cont> (FAST COMMIT ENABLED
cont> (CHECKPOINT INTERVAL IS 512 BLOCKS,
cont> CHECKPOINT TIMED EVERY 12 SECONDS)
cont> );
2.3.26 – Checkpoint Rows
Syntax options:
CHECKPOINT UPDATED ROWS TO BACKING FILE
CHECKPOINT UPDATED ROWS TO DATABASE
CHECKPOINT ALL ROWS TO BACKING FILE
Specifies the default source and target during checkpoint
operations for all row caches. If ALL ROWS is specified, then the
source records written during each checkpoint operation are both
the modified and the unmodified rows in a row cache. If UPDATED
ROWS is specified, then just the modified rows in a row cache are
checkpointed each time.
If the target of the checkpoint operation is BACKING FILE,
then the RCS process writes the source row cache entries to
the backing (.rdc) files. The row cache LOCATION, ALLOCATION,
and EXTENT clauses are used to create the backing files. Upon
recovery from a node failure, the database recovery process is
able to repopulate the row caches in memory from the rows found
in the backing files.
If the target is DATABASE, then updated row cache entries
are written back to the database. The row cache LOCATION,
ALLOCATION, and EXTENT clauses are ignored. Upon recovery from
a node failure, the database recovery process has no data on the
contents of the row cache. Therefore, it does not repopulate the
row caches in memory.
The CHECKPOINT clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this database-level CHECKPOINT clause.
2.3.27 – CHECKSUM_CALCULATION
Syntax options:
CHECKSUM CALCULATION
SNAPSHOT CHECKSUM CALCULATION
This option allows you to enable or disable calculations of page
checksums when pages are read from or written to the storage area or
snapshot files.
The default is ENABLED.
NOTE
Oracle Corporation recommends that you leave checksum
calculations enabled, which is the default.
With current technology, it is possible that errors may occur
that the checksum calculation can detect but that may not
be detected by either the hardware, firmware, or software.
Unexpected application results and database corruption may occur
if corrupt pages exist in memory or on disk but are not detected.
Oracle Corporation recommends performing checksum calculations,
except in the following specific circumstances:
o Your application is stable and has run without errors on the
current hardware and software configuration for an extended
period of time.
o You have reached maximum CPU utilization in your current
configuration. Actual CPU utilization by the checksum
calculation depends primarily on the size of the database
pages in your database. The larger the database page, the
more noticeable the CPU usage by the checksum calculation may
become.
NOTE
Oracle Corporation recommends that you carefully evaluate
the trade-off between reducing CPU usage by the checksum
calculation and the potential for loss of database integrity
if checksum calculations are disabled.
Oracle Corporation allows you to disable and, subsequently,
re-enable checksum calculation without error. However, once
checksum calculations have been disabled, corrupt pages may
not be detected even if checksum calculations are subsequently
re-enabled.
2.3.28 – CLEAN_BUFFER_COUNT
Syntax option:
CLEAN BUFFER COUNT IS buffer-count BUFFERS
Specifies the number of buffers to be kept available for
immediate reuse.
The default is five buffers. The minimum value is one; the
maximum value can be as large as the buffer pool size.
You can override the number of clean buffers by defining the
logical name RDM$BIND_CLEAN_BUF_CNT. For information about how to
set the values, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
2.3.29 – COMMIT_TO_JOURNAL_OPTIMIZATION
Syntax options:
COMMIT TO JOURNAL OPTIMIZATION | NO COMMIT TO JOURNAL
OPTIMIZATION
If you enable COMMIT TO JOURNAL OPTIMIZATION when you enable fast
commit processing, Oracle Rdb does not write commit information
to the database root file. This option enhances performance
in database environments that are update-intensive. Because
of the prerequisites for enabling the journal optimization
option, general-use databases or databases that have many read-
only transactions may not benefit from this feature. For more
information, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
NOTE
If you specify COMMIT TO JOURNAL OPTIMIZATION, you must
disable or defer snapshots.
If you change snapshots to ENABLED IMMEDIATE, then you must
specify NO COMMIT TO JOURNAL OPTIMIZATION.
2.3.30 – COUNT IS n
Specifies the number of levels on the page lock tree used to
manage locks. For example, if you specify COUNT IS 3, the fanout
factor is (10, 100, 1000). Oracle Rdb locks a range of 1000 pages
and adjusts downward to 100 and then to 10 and then to 1 page
when necessary.
If the COUNT IS clause is omitted, the default is 3. The value of
n can range from 1 through 8.
2.3.31 – DEPTH buffers option
Syntax option:
DEPTH IS number-buffers BUFFERS
Specifies the number of buffers to prefetch for a process.
The default is one-quarter of the buffer pool, but not more than
eight buffers. You can override the number of buffers specified
in the CREATE or ALTER DATABASE statements by using the logical
name RDM$BIND_APF_DEPTH.
You can also specify this option with the DETECTED ASYNC PREFETCH
clause.
2.3.32 – DETECTED_ASYNC_PREFETCH
Syntax options:
DETECTED ASYNC PREFETCH IS ENABLED | DETECTED ASYNC PREFETCH IS
DISABLED
Specifies whether or not Oracle Rdb reduces the amount of time
that a process waits for pages to be read from disk.
By using heuristics, detected asynchronous prefetch determines if
an I/O pattern is sequential in behavior even if sequential I/O
is not actually executing at the time. For example, when a LIST
OF BYTE VARYING column is fetched, the heuristics detect that
the pages being fetched are sequential and asynchronously fetches
pages further in the sequence. This avoids wait times when the
page is really needed.
Detected asynchronous prefetch is enabled by default.
2.3.33 – DICTIONARY required option
Syntax options:
DICTIONARY IS REQUIRED | DICTIONARY IS NOT REQUIRED
Specifies whether or not definition statements issued for the
database must also be stored in the repository. If you specify
the REQUIRED option, any data definition statements issued after
an ATTACH or DECLARE ALIAS statement that does not specify the
PATHNAME argument fails.
If you specify the DICTIONARY argument in an ALTER DATABASE
statement, you cannot specify any other database root file or
storage area parameters.
If you omitted the PATHNAME clause from the database root file
parameters in the CREATE DATABASE statement that created the
database, SQL generates an error if you specify DICTIONARY IS
REQUIRED in an ALTER DATABASE statement for the same database.
This is not true if you use the INTEGRATE statement with the
CREATE PATHNAME clause to copy database definitions to the
repository before specifying the DICTIONARY IS REQUIRED clause
in an ALTER DATABASE statement for that database.
2.3.34 – DICTIONARY used option
Syntax options:
DICTIONARY IS USED | DICTIONARY IS NOT USED
Specifies whether or not to remove the link between the
repository and the database. If you specify the DICTIONARY IS NOT
USED clause, the definitions in both the repository and database
are still maintained. After removing the links, you can integrate
the database to a new repository.
The DICTIONARY IS USED clause is the default.
2.3.35 – DROP_CACHE
Syntax options:
DROP CACHE row-cache-name CASCADE | DROP CACHE row-cache-name
RESTRICT
Deletes the specified row cache from the database. If the mode is
RESTRICT, then an exeption is raised if the row cache is assigned
to a storage area. If the mode is CASCADE, then the row cache is
removed from all referencing storage areas.
The default is RESTRICT if no mode is specified.
2.3.36 – DROP_JOURNAL
Deletes the specified journal file from the database.
You can only delete an .aij file that is not current and that has
been backed up.
2.3.37 – DROP_STORAGE_AREA
Syntax options:
DROP STORAGE AREA area-name CASCADE | DROP STORAGE AREA area-name
RESTRICT
Deletes the specified storage area definition and the associated
storage area and snapshot files. You can use the DROP STORAGE
AREA clause only on multifile databases.
If you use the RESTRICT keyword, you cannot delete a storage area
if any database object, such as a storage map, refers to the area
or if there is data in the storage area.
If you use the CASCADE keyword, Oracle Rdb modifies all objects
that refer to the storage area so that they no longer refer to
it. However, Oracle Rdb does not delete objects if doing so makes
the database inconsistent.
If you use the ALTER DATABASE statement to delete a storage
area, the change is journaled, however, you should back up your
database before making such a change.
2.3.38 – EXTENT blocks for aij
Specifies the number of blocks of each .aij file extent. The
default and minimum extent for .aij files is 512 blocks.
2.3.39 – EXTENT clause
Syntax options:
EXTENT ENABLED | EXTENT DISABLED
Enables or disables extents. Extents are ENABLED by default
and can be changed on line; however, the new extents are not
immediately effective on all nodes of a cluster. On the node on
which you have changed extents, the new storage area extents are
immediately effective for all users. The new storage area extents
become effective as the database is attached on each node of the
cluster.
You can encounter performance problems when creating hashed
indexes in storage areas with the mixed page format if the
storage area was created specifying the wrong size for the area
and if extents are enabled. By disabling extents, this problem
can be diagnosed early and corrected to improve performance.
2.3.40 – EXTENT pages
Syntax options:
EXTENT IS extent-pages PAGES | EXTENT IS (extension-options)
Changes the number of pages of each storage area file extent. See
the description under the SNAPSHOT EXTENT argument.
2.3.41 – FAST_COMMIT
Syntax options:
FAST COMMIT IS ENABLED | FAST COMMIT IS DISABLED
By default, Oracle Rdb writes updated database pages to the
disk each time a transaction executes the COMMIT statement. If
a transaction fails before committing, Oracle Rdb only needs to
roll back (undo) the current failed transaction; it never has to
redo previous successful transactions.
You can change the commit processing method by enabling journal
fast commit processing. With journal fast commit enabled, Oracle
Rdb keeps updated pages in the buffer pool (in memory) and does
not write the pages to the disk when a transaction commits. The
updated pages remain in the buffer pool until the process meets a
condition specified by the database administrator or applications
programmer. At the moment the condition is met (the checkpoint),
all the pages the process updated for multiple transactions are
written to the disk.
You can set a checkpoint for your process when:
o A fixed number of transactions are committed or aborted. You
set this by specifying CHECKPOINT EVERY n TRANSACTIONS.
o A specified time interval elapsed. You set this by specifying
the CHECKPOINT TIMED EVERY n SECONDS clause.
o The after-image journal (.aij) file increased by a specified
number of blocks. You set this by specifying the CHECKPOINT
INTERVAL IS n BLOCKS clause.
If a transaction fails, Oracle Rdb must undo the current, failed
transaction and redo all the committed transactions since the
last checkpoint. Redoing updates involves reading the .aij file
and reapplying the changes to the relevant data pages.
Fast commit processing applies only to data updates: erase,
modify, and store operations. Transactions that include
data definition statements, such as create logical area or
create index operations, force a checkpoint at the end of the
transaction. If you do not specify values with the FAST COMMIT
clause, the default values are applied.
NOTE
To enable FAST COMMIT, you must first enable after-image
journaling.
2.3.42 – FILENAME file spec
Identifies the database root file associated with the database.
If you specify a repository path name, the path name indirectly
specifies the database root file. The ALTER DATABASE statement
does not change any definitions in the repository, so there is no
difference in the effect of the PATHNAME and FILENAME arguments.
If you specify PATHNAME, SQL does not use the repository's fully
qualified name. Instead, SQL uses the name stored as the user-
supplied name in the repository. In the following example, SQL
uses the name TEST as the file name, not DB$DISK:[DBDIR]TEST.RDB.
As a result, the database root file must be located in your
present working directory or the database name must be a logical
name when you use the PATHNAME clause.
$ REPOSITORY OPERATOR
.
.
.
CDO> show database/full test
Definition of database TEST
| database uses RDB database TEST
| database in file TEST
| | fully qualified file DB$DISK:[DBDIR]TEST.RDB;
| | user-specified file DB$DISK:[DBDIR]test.rdb
If the database referred to in the PATHNAME or FILENAME argument
has been attached, the ALTER DATABASE statement will fail with a
file access conflict error.
2.3.43 – FILENAME journal file spec
Specifies the journal file specification with the default file
extension .aij.
2.3.44 – GALAXY_SUPPORT
Syntax options:
GALAXY SUPPORT IS ENABLED|GALAXY SUPPORT IS DISABLED
Allows global memory to be shared in an OpenVMS Galaxy
configuration. Galaxy support is disabled by default.
OpenVMS Galaxy is a software architecture for the OpenVMS Alpha
operating system that enables multiple instances of OpenVMS to
execute cooperatively in a single computer. An instance refers
to a copy of the OpenVMS Alpha operating system. As an extension
of the existing OpenVMS cluster support within Oracle Rdb, Oracle
Rdb provides support for databases opened on multiple instances
(or nodes) within a Galaxy system to share data structures in
memory. Within an Oracle Rdb Galaxy environment, all instances
with an open database share:
o Database root objects (for example, TSN blocks and SEQ blocks)
o Global buffers (if enabled)
o Row caches and Row Cache Server process (RCS) (if enabled)
2.3.45 – GLOBAL_BUFFERS
Syntax options:
GLOBAL BUFFERS ARE ENABLED | GLOBAL BUFFERS ARE DISABLED
Specifies whether or not Oracle Rdb maintains one global buffer
pool per VMScluster node for each database. By default, Oracle
Rdb maintains a local buffer pool for each attach (GLOBAL BUFFERS
ARE DISABLED). For more than one attach to use the same page,
each must read it from the disk into their local buffer pool.
A page in the global buffer pool can be read by more than one
attach at the same time, although only one attach reads the page
from the disk into the global buffer pool. Global buffers improve
performance because the I/O is reduced, and memory is better
utilized.
NOTE
If GALAXY SUPPORT is enabled, then a single global buffer
pool is shared by all Galaxy nodes.
2.3.46 – INCREMENTAL_BACKUP_SCAN_OPTIMIZATION
Syntax options:
INCREMENTAL BACKUP SCAN OPTIMIZATION | NO INCREMENTAL BACKUP SCAN
OPTIMIZATION
Specifies whether Oracle Rdb checks each area's SPAM pages or
each database page to find changes during incremental backup.
If you specify INCREMENTAL BACKUP SCAN OPTIMIZATION, Oracle Rdb
checks each area's SPAM pages and scans the SPAM interval of
pages only if the SPAM transaction number (TSN) is higher than
the last full backup TSN, which indicates that a page in the SPAM
interval has been updated since the last full backup operation.
Specify INCREMENTAL BACKUP SCAN OPTIMIZATION if your database has
large SPAM intervals or infrequently occurring updates, and you
want to increase the speed of incremental backups. If you disable
the attribute (using the NO INCREMENTAL BACKUP SCAN OPTIMIZATION
clause), you cannot enable it until immediately after the next
full backup.
If you specify NO INCREMENTAL BACKUP SCAN OPTIMIZATION, Oracle
Rdb checks each page to find changes during incremental backup.
Specify the NO INCREMENTAL BACKUP SCAN OPTIMIZATION clause if
your database has frequently occurring updates, uses bulk-load
operations, or does not use incremental backups, or if you want
to improve run-time performance.
The default is INCREMENTAL BACKUP SCAN OPTIMIZATION.
2.3.47 – JOURNAL clause for aij
Syntax options:
JOURNAL IS ENABLED | JOURNAL IS DISABLED
Specifies whether or not journaling is enabled.
If journal files already exist, the JOURNAL IS ENABLED clause
simply restarts the journaling feature.
If no journal files exist when the ALTER DATABASE . . . JOURNAL IS
ENABLED statement completes, an exception is raised. For example:
SQL> ALTER DATABASE FILENAME sample
cont> JOURNAL IS ENABLED;
%RDMS-F-NOAIJENB, cannot enable after-image journaling without any AIJ journals
Use the ADD JOURNAL clause to create journal files.
The ENABLED option can be followed by a list of database journal
options.
All journal files remain unchanged but become inaccessible when
you disable them. You cannot specify database journal options
with the DISABLED option.
2.3.48 – JOURNAL_IS_UNSUPPRESSED
If a journal file becomes inaccessible, it is disabled by the
journaling system. It remains in that state until you correct the
problem and manually unsuppress that journal file.
2.3.49 – literal-user-auth
Specifies the user name and password for access to databases,
particularly remote database.
This literal lets you explicitly provide user name and password
information in the ALTER DATABASE statement.
2.3.50 – LOCATION IS directory-spec
Specifies the name of the default directory to which row
cache backing file information is written. The database system
generates a file name (row-cache-name.rdc) automatically for each
row cache backing file it creates when the RCS process starts up.
Specify a device name and directory name enclosed within single
quotation marks ('); do not include a file specification. The
file name is the row-cache-name specified when creating the row
cache. By default, the location is the directory of the database
root file.
The LOCATION clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this location, which is the default for
the database.
This clause is ignored if the row cache is defined to checkpoint
to the database.
2.3.51 – LOCK_PARTITIONING
Syntax options:
LOCK PARTITIONING IS ENABLED | LOCK PARTITIONING IS DISABLED
Specifies whether more than one lock tree is used for the
database or all lock trees for a database are mastered by one
database resource tree.
When partitioned lock trees are enabled for a database, locks for
storage areas are separated from the database resource tree and
all locks for each storage area are independently mastered on the
VMScluster node that has the highest traffic for that resource.
OpenVMS determines the node that is using each resource the most
and moves the resource hierarchy to that node.
You cannot enable lock partitioning for single-file databases.
You should not enable lock partitioning for single-node systems,
because all lock requests are local on single-node systems.
By default, lock partitioning is disabled.
2.3.52 – LOCK_TIMEOUT_INTERVAL
Syntax option:
LOCK TIMEOUT INTERVAL IS number-seconds SECONDS
Specifies the number of seconds for processes to wait during a
lock conflict before timing out. The number can be between 1 and
65,000 seconds.
Specifying 0 is interpreted as no lock timeout interval being
set. It is not interpreted as 0 seconds.
The lock timeout interval is database-wide; it is used as
the default and the upper limit when determining the timeout
interval. For example, if the database definer specified LOCK
TIMEOUT INTERVAL IS 25 SECONDS in the ALTER DATABASE statement,
and a user of that database specified SET TRANSACTION WAIT 30 or
changed the logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL to 30,
SQL still uses the interval 25. For more information on timeout
intervals, see the Oracle Rdb7 Guide to Distributed Transactions.
2.3.53 – LOCKING level
Syntax options:
LOCKING IS ROW LEVEL | LOCKING IS PAGE LEVEL
Specifies if locking is at the page or row level. This clause
provides an alternative to requesting locks on records. The
default is ROW LEVEL.
When many records are accessed in the same area and on the same
page, the LOCKING IS PAGE LEVEL clause reduces the number of lock
operations perfomed to process a transaction; however, this is at
the expense of reduced concurrency because these page locks are
held until COMMIT/ROLLBACK time. Transactions that benefit most
with page-level locking are of short duration and also access
several database records on the same page.
Use the LOCKING IS ROW LEVEL clause if transactions are long in
duration and lock many rows.
The LOCKING IS PAGE LEVEL clause causes fewer blocking
asynchronous system traps and provides better response time
and utilization of system resources. However, there is a higher
contention for pages and increased potential for deadlocks and
long transactions may use excessive locks.
Page-level locking is never applied to RDB$SYSTEM or the DEFAULT
storage area, either implicitly or explicitly, because the
locking protocol can stall metadata users.
You cannot specify page-level locking on single-file databases.
2.3.54 – LOG_SERVER
Syntax options:
LOG SERVER IS MANUAL | LOG SERVER IS AUTOMATIC
Specifies if the AIJ log server (ALS) is activated manually or
automatically. The default is manual.
Multiple-user databases with medium to high update activity
can experience after-image journal (.aij) file bottlenecks.
To alleviate these bottlenecks, you can specify the LOG SERVER
clause to transfer log data to the .aij file either automatically
or manually. On a single node with ALS, there is no AIJ locking.
If the log server is set to MANUAL, you must execute the RMU
Server After_Journal command with the Start qualifier to start
the log server. In this case, the database must already be open.
If the OPEN IS MANUAL clause was specified, an explicit RMU Open
command needs to be executed before the log server is started.
If the OPEN IS AUTOMATIC clause was specified, at least one
user should be attached to the database before the log server
is started.
If the log server is set to AUTOMATIC, the log server starts when
the database is opened, automatically or manually, and is shut
down when the database is closed.
For more information on setting log servers, see the Oracle Rdb7
Guide to Database Performance and Tuning.
2.3.55 – LOGMINER_SUPPORT
Syntax options:
LOGMINER SUPPORT IS ENABLED|LOGMINER SUPPORT IS DISABLED
Allows additional information to be written to the after-image
journal file to allow the use of the RMU Unload After_Image
command. See the Oracle RMU Reference Manual for more details.
Logminer support is disabled by default.
The LOGMINER SUPPORT clause allows the continuous mode for
LogMiner to be enabled and disabled.
o LOGMINER SUPPORT IS ENABLED (CONTINUOUS)
Enables continuous LogMiner.
o LOGMINER SUPPORT IS ENABLED (NOT CONTINUOUS)
Disables continuous LogMiner, but leaves LogMiner enabled.
o LOGMINER SUPPORT IS DISABLED
Disables LogMiner, including disabling continuous LogMiner.
2.3.56 – MAXIMUM_BUFFER_COUNT
Syntax option:
MAXIMUM BUFFER COUNT IS buffer-count
Specifies the number of buffers a process will write
asynchronously.
The default is one-fifth of the buffer pool, but not more than 10
buffers. The minimum value is 2 buffers; the maximum value can be
as large as the buffer pool.
You can override the number of buffers to be written
asynchronously by defining the logical name RDM$BIND_BATCH_MAX.
For information about how to set the values, see the Oracle Rdb7
Guide to Database Performance and Tuning.
2.3.57 – MAXIMUM_PAGES
Syntax option:
MAXIMUM OF max-pages PAGES
Specifies the maximum number of pages of each extent. The default
is 9999 pages.
2.3.58 – METADATA_CHANGES
Syntax options:
METADATA CHANGES ARE ENABLED | METADATA CHANGES ARE DISABLED
Specifies whether or not data definition changes are allowed
to the database. This attribute becomes effective at the
next database attach and affects all ALTER, CREATE, and DROP
statements (except ALTER DATABASE which is needed for database
tuning) and the GRANT, REVOKE, and TRUNCATE TABLE statements. For
example:
SQL> CREATE DATABASE FILENAME sample;
SQL> CREATE TABLE t (a INTEGER);
SQL> DISCONNECT ALL;
SQL> ALTER DATABASE FILENAME sample
cont> METADATA CHANGES ARE DISABLED;
SQL> ATTACH 'FILENAME sample';
SQL> CREATE TABLE s (b INTEGER);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-NOMETADATA, metadata operations are disabled
The METADATA CHANGES ARE DISABLED clause prevents data definition
changes to the database.
The METADATA CHANGES ARE ENABLED clause allows data definition
changes to the database by users granted the DBADMIN privilege.
METADATA CHANGES ARE ENABLED is the default.
2.3.59 – MINIMUM_PAGES
Syntax option:
MINIMUM OF min-pages PAGES
Specifies the minimum number of pages of each extent. The default
is 99 pages.
2.3.60 – MULTISCHEMA
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
Specifies the multischema attribute for the database. If a
database has the multischema attribute, you can create multiple
schemas in that database and group them within catalogs. The
MULTISCHEMA IS ON option is the default for databases created
with the multischema attribute. MULTISCHEMA IS OFF is the default
for databases created without the multischema attribute.
You can create a database using the CREATE DATABASE MULTISCHEMA
IS ON clause, but you cannot use ALTER DATABASE MULTISCHEMA IS
OFF to take away the multischema attribute. Once a database has
the multischema attribute, you cannot change it.
2.3.61 – NO_BACKUP_FILENAME
Removes a previously established backup file specification.
2.3.62 – NO_LOCATION
This is a subclause of other clauses and has different effects,
depending upon the clause in which it is used, as follows:
o In the row-cache-options clause
Removes the location previously specified in a LOCATION IS
clause for the row cache. If you specify NO LOCATION, the
row cache location becomes the directory of the database root
file.
The LOCATION clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this location, which is the default for
the database.
o In a CREATE CACHE, ADD CACHE, or ALTER CACHE clause (row-
cache-params1 clause)
Removes the location previously specified in a LOCATION IS
clause for the row cache backing file. If you specify NO
LOCATION, the row cache location becomes the directory of
the database root file.
This clause is ignored if the row cache is defined to
checkpoint to the database.
2.3.63 – NO_ROW_CACHE
Specifies that the database default is to not assign a row cache
to all storage areas in the database. You cannot specify the NO
ROW CACHE clause if you specify the CACHE USING clause.
Alter the storage area and name a row cache to override the
database default. Only one row cache is allowed for each storage
area.
If you do not specify the NO ROW CACHE clause or the CACHE USING
clause, then the NO ROW CACHE clause is the default.
2.3.64 – NO_SWEEP_INTERVAL
NO SWEEP INTERVAL disables periodic timed sweeps.
2.3.65 – NOTIFY
Syntax options:
NOTIFY IS ENABLED | NOTIFY IS DISABLED
Specifies whether system notification is enabled or disabled.
When the system notification is enabled, the system is notified
(using the OpenVMS OPCOM facility) in the event of events such as
running out of disk space for a journal.
If you specify the NOTIFY IS ENABLED clause and do not specify
the ALERT OPERATOR clause, the operator classes used are CENTRAL
and CLUSTER. To specify other operator classes, use the ALERT
OPERATOR clause.
The NOTIFY IS ENABLED clause replaces any operator classes set by
the RMU Set After_Journal Notify command.
The default is disabled.
2.3.66 – NUMBER global buffers
Syntax option:
NUMBER IS number-glo-buffers
Specifies the total number of buffers in the global buffer pool.
This number appears as "global buffer count" in RMU Dump command
output. Base this value on the database users' needs and the
number of attachments. The default is the maximum number of
attachments multiplied by 5.
NOTE
Do not confuse the NUMBER IS parameter with the NUMBER OF
BUFFERS IS parameter. The NUMBER OF BUFFERS IS parameter
determines the default number of buffers Oracle Rdb
allocates to each user's process that attaches to the
database. The NUMBER OF BUFFERS IS parameter applies to,
and has the same meaning for, local and global buffering.
The NUMBER IS parameter has meaning only within the context
of global buffering.
You can override the default number of user-allocated buffers by
defining a value for the logical name RDM$BIND_BUFFERS. For more
information on user-allocated buffers, see Oracle Rdb7 Guide to
Database Performance and Tuning.
Although you can change the NUMBER IS parameter on line, the
change does not take effect until the next time the database is
opened.
2.3.67 – NUMBER_OF_BUFFERS
Syntax option:
NUMBER OF BUFFERS IS number-buffers
The number of buffers SQL allocates for each process using this
database. Specify an unsigned integer with a value greater than
or equal to 2 and less than or equal to 32,767. The default is 20
buffers.
2.3.68 – NUMBER_OF_CLUSTER_NODES
Syntax option:
NUMBER OF CLUSTER NODES IS number-nodes
Sets the upper limit on the maximum number of VMS cluster nodes
from which users can access the shared database. Specify this
clause only if the database named in the ALTER DATABASE statement
refers to a multifile database. The default is 16 nodes. The
range is 1 to 96 nodes. The actual maximum limit is the current
VMS cluster node limit set by your system administrator.
The Oracle Rdb root file data structures (.rdb) are mapped to
shared memory, each such shared memory copy is known as an Rdb
instance. When there is only one copy of shared memory containing
root file information, several optimizations are enabled to
reduce locking and root file I/O. activity. Specify NUMBER OF
CLUSTER NODES is set to 1, or use the SINGLE INSTANCE clause to
enable these optimizations.
MULTIPLE INSTANCE means that the Oracle Rdb root file data
structures are mapped on different system and are kept consistent
through disk I/O. Such systems can not benefit from single
instance optimizations. MULTIPLE INSTANCE is the default.
2.3.69 – NUMBER_OF_RECOVERY_BUFFERS
Syntax option:
NUMBER OF RECOVERY BUFFERS IS number-buffers
Specifies the number of buffers allocated to the automatic
recovery process that Oracle Rdb initiates after a system or
process failure. This recovery process uses the recovery-unit
journal file (.ruj file extension).
You can specify any number greater than or equal to 2 and less
than or equal to 32,767. The default value for the NUMBER OF
RECOVERY BUFFERS parameter is 20. If you have a large, multifile
database and you work on a system with a large amount of memory,
specify a large number of buffers. The result is faster recovery
time. However, make sure your buffer pool does not exceed the
amount of memory you can allocate for the pool.
Use the NUMBER OF RECOVERY BUFFERS option to increase the number
of buffers allocated to the recovery process.
SQL> ALTER DATABASE FILENAME personnel
cont> NUMBER OF RECOVERY BUFFERS IS 150;
This option is used only if the NUMBER OF RECOVERY BUFFERS value
is larger than the NUMBER OF BUFFERS value. For more information
on allocating recovery buffers, see the Oracle Rdb Guide to
Database Maintenance.
2.3.70 – NUMBER_OF_SWEEP_ROWS
Syntax option:
NUMBER OF SWEEP ROWS IS n
Specifies the number of modified rows that will be written from
the row cache back to the database by the row cache server (RCS)
process during a sweep operation. When the RCS is notified that
a cache is "full" of modified data, the RCS starts a sweep to
make space available in the cache for subsequent transactions
to be able to insert rows into the cache. Oracle Corporation
recommends that you initially specify the number of sweep rows
to be approximately 5 percent of the total number of rows in
the cache. Then monitor performance and adjust the number of
sweep rows, if necessary. Allowable values must be in the range 2
through 524288. If not specified, the default is 3,000 rows.
2.3.71 – NUMBER_OF_USERS
Syntax option:
NUMBER OF USERS IS number-users
Limits the maximum number of users allowed to access the database
at one time. Specify this clause only if the database named in
the ALTER DATABASE statement refers to a multifile database.
The default is 50 users. After the maximum is reached, the next
user who tries to invoke the database receives an error message
and must wait. The maximum number of users you can specify is
16368 and the minimum is 1 user.
Note that number of users is defined as the number of active
attachments to the database. Therefore, if a single process
is running one program but that program performs 12 attach
operations, the process is responsible for 12 active users.
If you use the ALTER DATABASE statement to change the current
number of users, the change is not journaled. Therefore, back up
your database before making such a change.
2.3.72 – OPEN
Syntax options:
OPEN IS AUTOMATIC | OPEN IS MANUAL
Specifies whether or not the database must be explicitly opened
before users can attach to it. The default, OPEN IS AUTOMATIC,
means that any user can open a previously unopened or a closed
database by attaching to it and executing a statement. The
OPEN IS MANUAL option means that a privileged user must issue
an explicit OPEN statement through Oracle RMU, the Oracle
Rdb management utility, before other users can attach to the
database.
To issue the RMU Open command, you must have the RMU$OPEN
privilege for the database.
The OPEN IS MANUAL option limits access to databases.
You will receive an error message if you specify both OPEN IS
AUTOMATIC and OPEN IS MANUAL options.
2.3.73 – OVERWRITE
Syntax options:
OVERWRITE IS ENABLED | OVERWRITE IS DISABLED
Specifies whether the overwrite option is enabled or disabled.
After-image journal files are used for database recovery in case
of media failure and for transaction recovery as part of the
fast commit feature. In some environments, only the fast commit
feature is of interest and a small set of journal files can be
used as a circular fast commit log with no backup of the contents
required. The OVERWRITE option instructs Oracle Rdb to write over
journal records that would normally be used for media recovery.
The resulting set of journal files is unable to be used by the
RMU Recover command for media recovery.
The OVERWRITE option is ignored when only one after-image journal
(.aij) file exists. Adding subsequent journal files activates the
OVERWRITE option.
The default is DISABLED.
2.3.74 – PAGE_TRANSFER
Syntax options:
PAGE TRANSFER VIA DISK | PAGE TRANSFER VIA MEMORY
Specifies whether Oracle Rdb transfers (flushes) pages to disk or
to memory.
When you specify PAGE TRANSFER VIA MEMORY, processes on a single
node can share and update database pages in memory without
transferring the pages to disk. It is not necessary for a process
to write a modified page to disk before another process accesses
the page.
The default is to DISK. If you specify PAGE TRANSFER VIA MEMORY,
the database must have the following characteristics:
o The NUMBER OF NODES must be one, or SINGLE INSTANCE must be
specified in the NUMBER OF CLUSTER NODES clause.
o GLOBAL BUFFERS must be enabled.
o After-image journaling must be enabled.
o FAST COMMIT must be enabled.
If the database does not have these characteristics, Oracle Rdb
will perform page transfers via disk.
For more information about page transfers, see the Oracle Rdb7
Guide to Database Performance and Tuning.
2.3.75 – PATHNAME path name
Identifies the database root file associated with the database.
If you specify a repository path name, the path name indirectly
specifies the database root file. The ALTER DATABASE statement
does not change any definitions in the repository, so there is no
difference in the effect of the PATHNAME and FILENAME arguments.
If the database referred to in the PATHNAME or FILENAME argument
has been attached, the ALTER DATABASE statement will fail with a
file access conflict error.
2.3.76 – PERCENT_GROWTH
Syntax option:
PERCENT GROWTH IS growth
Specifies the percent growth of each extent. The default is 20
percent growth.
2.3.77 – PRESTARTED_TRANSACTIONS
Syntax options:
PRESTARTED TRANSACTIONS ARE ENABLED (prestart-trans-options)
PRESTARTED TRANSACTIONS ARE DISABLED
Enables or disables the prestarting of transactions.
Note that the keyword OFF, available in previous versions, is
synonymous with DISABLED.
2.3.78 – READ_WRITE,READ_ONLY
The READ options of the alter-storage-area-params clause permit
you to change existing storage area access as follows:
o Select the READ WRITE option to change any storage area to
read/write access.
o Select the READ ONLY option to change any storage area to
read-only access.
If you want to change the read-only and read/write parameters of
the RDB$SYSTEM storage area, you must specify these parameters at
this point of your ALTER DATABASE statement and not in the ALTER
STORAGE AREA clause. For example:
SQL> -- You can change the RDB$SYSTEM storage area by altering
SQL> -- the database.
SQL> --
SQL> ALTER DATABASE FILENAME mf_personnel
cont> READ ONLY;
SQL> --
SQL> -- An error is returned if you try to change the RDB$SYSTEM storage
SQL> -- area to read-only using the ALTER STORAGE AREA clause.
SQL> --
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER STORAGE AREA RDB$SYSTEM
cont> READ ONLY;
%RDB-E-BAD_DPB_CONTENT, invalid database parameters in the database
parameter block (DPB)
-RDMS-E-NOCHGRDBSYS, cannot change RDB$SYSTEM storage area explicitly
SQL provides support for read-only databases and databases with
one or more read-only storage areas.
You can take advantage of read-only support if you have a
stable body of data that is never (or rarely) updated. When the
RDB$SYSTEM storage area is changed to read-only, lock conflicts
occur less frequently, and the automatic updating of index and
table cardinality is inhibited.
Read-only databases consist of:
o A read/write database root file
o One or more read-only storage areas and no read/write storage
areas
Read-only databases can be published and distributed on CD-ROM.
Read-only storage areas:
o Have snapshot files but do not use them. (Data in a read-only
storage area is not updated; specify a small number for the
initial snapshot file size for a read-only storage area.)
o Eliminate page and record locking in the read-only storage
areas.
o Are backed up by the RMU Backup command by default unless you
explicitly state the Noread_Only qualifier, which excludes
read-only areas without naming them.
o Are restored by the RMU Restore command if they were
previously backed up.
o Are recovered by the RMU Recover command. However, unless the
read-only attribute was modified, the read-only area does not
change.
o Are not recovered by the RMU Recover command with the Area=*
qualifier, in which you are not explicitly naming the areas
needing recovery, unless they are inconsistent.
You use the READ ONLY option to change a storage area from
read/write to read-only access. If you wanted to facilitate
batch-update transactions to infrequently changed data, you would
use the READ WRITE option to change a read-only storage area back
to read/write.
If you change a read/write storage area to read-only, you cannot
specify the EXTENT, SNAPSHOT ALLOCATION, and SNAPSHOT EXTENT
clauses.
A database with both read/write and read-only storage areas can
be fully recovered after a system failure only if after-image
journaling is enabled on the database. If your database has
both read/write and read-only storage areas but does not have
after-image journaling enabled, perform full backup operations
(including read-only areas) at all times. Doing full backup
operations enables you to recover the entire database to its
condition at the time of the previous backup operation.
For a complete description of read-only databases and read-only
storage areas, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
2.3.79 – RECOVERY_JOURNAL_(BUFFER_MEMORY)
Syntax options:
BUFFER MEMORY IS LOCAL
BUFFER MEMORY IS GLOBAL
Specifies whether RUJ buffers will be allocated in global or
local memory.
The RUJ buffers used by each process are normally allocated in
local virtual memory. With the introduction of row caching, these
buffers now can be assigned to a shared global section (global
memory) on OpenVMS, so that the recovery process can process this
in-memory buffer and possibly avoid a disk access.
You can define this buffer memory to be global to improve row
caching performance for recovery. If row caching is disabled,
then buffer memory is always local.
2.3.80 – RECOVERY_JOURNAL_(LOCATION)
Syntax options:
RECOVERY JOURNAL (LOCATION IS directory-spec)
Specifies the location, including device and directory, in which
the recovery-unit journal (.ruj) file is written. Do not include
network node names, file names or process-concealed logical
names. The default is the current user's login device.
See the Oracle Rdb Guide to Database Maintenance for more
information on recovery-unit journal files.
Following is an example using this clause:
SQL> ALTER DATABASE FILENAME SAMPLE
cont> RECOVERY JOURNAL (LOCATION IS 'SQL_USER1:[DBDIR.RECOVER]');
2.3.81 – RECOVERY_JOURNAL_(NO_LOCATION)
Removes a location previously defined by a RECOVERY JOURNAL
LOCATION IS clause. This causes the recovery journal to revert
to the default location.
2.3.82 – RESERVE n CACHE SLOTS
Specifies the number of row caches for which slots are reserved
in the database.
You can use the RESERVE CACHE SLOTS clause to reserve slots in
the database root file for furture use by the ADD CACHE clause.
You can only add row caches if row cache slots are available.
Slots become available after you issue a DROP CACHE clause or a
RESERVE CACHE SLOTS clause.
You cannot reduce the number of reserved slots for row caching.
If you reserve 10 slots and later reserve 5 slots, a total of 15
slots are reserved for row caches.
2.3.83 – RESERVE n JOURNALS
Specifies the number of journal files for which slots are to
reserve in the database. The number of slots for journal files
must be a positive number greater than zero.
This feature is additive in nature. In other words, the number
of reserved slots for journal files cannot be decreased once
the RESERVE clause has been issued. If you reserve 10 slots and
later reserve 5 slots, you have a total of 15 reserved slots for
journal files plus 1 slot (totaling 16 reserved slots) because
you initially get 1 pre-reserved slot.
You must reserve slots or delete an existing journal file before
you can add new journal files to the database.
You cannot reserve journal files for a single-file database.
2.3.84 – RESERVE n SEQUENCES
Specifies the number of sequences for which slots are reserved in
the database. Sequences are reserved in multiples of 32. Thus, if
you specify a value less than 32 for n, 32 slots are reserved. If
you specify a value of 33, 64 slots are reserved, and so on.
You can use the RESERVE SEQUENCES clause to reserve slots in
the database root file for future use by the CREATE SEQUENCE
statement. Sequences can be created only if sequence slots are
available. Slots become available after a DROP SEQUENCE statement
or a RESERVE SEQUENCES clause of the ALTER DATABASE statement is
executed.
2.3.85 – RESERVE n STORAGE AREAS
Specifies the number of storage areas for which slots are to
reserve in the database. The number of slots for storage areas
must be a positive number greater than zero.
You can use the RESERVE STORAGE AREA clause to reserve slots
in the database root file for future use by the ADD STORAGE
AREA clause of the ALTER DATABASE statement. Storage areas can
be added only if there are storage area slots available. Slots
become available after a DROP STORAGE AREA clause or a RESERVE
STORAGE AREA clause is issued.
This feature is additive in nature. In other words, the number
of reserved slots for storage areas cannot be decreased once
the RESERVE clause is issued. If you reserve 10 slots and later
reserve 5 slots, you have a total of 15 reserved slots for
storage areas.
You must reserve slots or delete an existing storage area before
you can add new storage areas to the database.
If you do not specify the RESERVE STORAGE AREA clause, the
default number of reserved storage areas is zero.
2.3.86 – ROW_CACHE
Syntax options:
ROW CACHE IS ENABLED | ROW CACHE IS DISABLED
Specifies whether or not the row caching feature is enabled.
Enabling row caching does not affect database operations until a
cache is created and assigned to one or more storage areas.
When row caching is disabled, all previously created and assigned
caches remain and will be available if row caching is enabled
again.
The following conditions must be true in order to use row caches:
o The number of cluster nodes is one
o After-image journaling is enabled
o Fast commit is enabled
o One or more cache slots are reserved
o Row caching is enabled
Use the RMU Dump Header command to check if you have met the
requirements for using row caches. The following command output
displays a warning for every requirement that is not met:
.
.
.
Row Caches...
- Active row cache count is 0
- Reserved row cache count is 1
- Sweep interval is 1 second
- Default cache file directory is ""
- WARNING: Maximum node count is 16 instead of 1
- WARNING: After-image journaling is disabled
- WARNING: Fast commit is disabled
.
.
2.3.87 – SAME_BACKUP_FILENAME_AS_JOURNAL
During execution, the backup server assigns the same name to the
backup file as it does to the journal file. This is a quick form
of backup as a new file is created.
NOTE
Oracle Corporation recommends that you save the old journal
file on tape or other media to prevent accidental purging of
these files.
2.3.88 – SECURITY_CHECKING
Traditionally Oracle Rdb has performed security checking using
the operating system security layer (for example, the UIC and
rights identifiers of the OpenVMS operating system).
The access control list (ACL) information stored in the database
contains a granted privilege mask and a set of users represented
by a unique integer (for example, a UIC).
There are two modes of security checking:
1. SECURITY CHECKING IS EXTERNAL
This is the default. External security checking recognizes
database users as operating system user identification codes
(UICs) and roles as special rights identifiers or groups.
PERSONA support is enabled or disabled as follows:
o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS ENABLED)
Enables the full impersonation of an OpenVMS user. This
means the UIC and the granted right identifiers are used to
check access control list permissions.
o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS DISABLED)
Disables the full impersonation of an OpenVMS user. Only
the UIC is used to check access control list permissions.
This is the default for a new database, or for a database
converted from a prior version of Oracle Rdb.
2. SECURITY CHECKING IS INTERNAL
In this mode, Oracle Rdb records users (username and UIC) and
roles (rights identifiers) in the database. The CREATE USER
and CREATE ROLE statements perform this action explicitly, and
GRANT will perform this implicitly. This type of database can
now be moved to another system and is only dependent on the
names of the users and roles.
o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS ENABLED)
The ACCOUNT CHECK clause ensures that Oracle Rdb validates
the current database user with the user name (such as
defined with a CREATE USER statement) stored in the
database. This prevents different users with the same name
from accessing the database. Therefore, this clause might
prevent a breach in security.
The ACCOUNT CHECK IS ENABLED clause on OpenVMS forces the
user session to have the same user name and UIC as recorded
in the database.
o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS DISABLED)
If you specify the ACCOUNT CHECK IS DISABLED clause, then
a user with a matching UIC (also called a profile-id) is
considered the same as the user even if his or her user
name is different. This allows support for multiple OpenVMS
users with the same UIC.
2.3.89 – SET_TRANSACTION_MODES
Enables only the modes specified, disabling all other previously
defined modes. This is an offline operation and requires
exclusive database access. For example, if a database is used for
read-only access and you want to disable all other transaction
modes, specify the following statement:
SQL> ALTER DATABASE FILENAME mf_personnel
cont> SET TRANSACTION MODES (READ ONLY);
Specifying a negated txn-mode or specifying NONE disables all
transaction usage. Disabling all transaction usage would be
useful when, for example, you want to perform major restructuring
of the physical database. Execute the ALTER DATABASE statement to
re-enable transaction modes.
2.3.90 – SHARED_MEMORY
Syntax options:
SHARED MEMORY IS SYSTEM
SHARED MEMORY IS PROCESS
SHARED MEMORY IS PROCESS RESIDENT
Determines whether database root global sections (including global
buffers when enabled) are created in system space or process space.
The default is PROCESS.
When you use global sections created in the process space, you
and other users share physical memory and the OpenVMS operating
system maps a row cache to a private address space for each user.
As a result, all users are limited by the free virtual address
range and each use a percentage of memory in overhead. If many
users are accessing the database, the overhead can be high.
When many users are accessing the database, consider using SHARED
MEMORY IS SYSTEM. This gives users more physical memory because
they share the system space of memory and there is none of the
overhead associated with the process space of memory.
The default is SHARED MEMORY IS PROCESS.
When you use this clause as a cache attribute, it controls
whether Oracle Rdb creates cache global sections in system space
or process space. The default is PROCESS.
The SHARED MEMORY clause determines whether database root global
sections (including global buffers when enabled) or whether the
cache global sections are created in system space or process
space. The RESIDENT option extends the PROCESS option by making
the global section memory resident.
To enable or disable SHARED MEMORY IS PROCESS RESIDENT, the
process executing the command must be granted the VMS$MEM_
RESIDENT_USER rights identifier. When this feature is enabled,
the process that opens the database must also be granted the
VMS$MEM_RESIDENT_USER rights identifier. Oracle Corporation
recommends using the RMU Open command when utilizing this
feature.
2.3.91 – SHUTDOWN_TIME
Syntax option:
SHUTDOWN TIME IS n MINUTES
Specifies the number of minutes the database system will wait
after a catastrophic event before it shuts down the database.
The shutdown time is the period, in minutes, between the point
when the after-image journaling subsystem becomes unavailable
and the point when the database is shut down. During the after-
image journaling shutdown period, all database update activity is
stalled.
If notification is enabled with the NOTIFY IS clause, operator
messages will be broadcast to all enabled operator classes.
To recover from the after-image journaling shutdown state and
to resume normal database operations, you must make an .aij file
available for use. You can do this by backing up an existing
modified journal file, or, if you have a journal file reservation
available, by adding a new journal file to the after-image
journaling subsystem. If you do not make a journal file available
before the after-image journal shutdown time expires, the
database will be shut down and all active database attachments
will be terminated.
The after-image journaling shutdown period is only in effect when
a fixed-size .aij file is used. When a single extensible .aij
file is used, the default action is to shut down all database
operations when the .aij file becomes unavailable.
The default is 60 minutes. The minimum value is 1 minute; the
maximum value is 4320 minutes (3 days).
2.3.92 – SNAPSHOT_ALLOCATION
Syntax option:
SNAPSHOT ALLOCATION IS snp-pages PAGES
Changes the number of pages allocated for the snapshot file. The
default is 100 pages. If you have disabled the snapshot file, you
can set the snapshot allocation to 0 pages.
2.3.93 – SNAPSHOT_EXTENT
Syntax options:
SNAPSHOT EXTENT IS extent-pages PAGES | SNAPSHOT EXTENT IS
(extension-options)
Changes the number of pages of each snapshot or storage area file
extent. The default extent for storage area files is 100 pages.
Specify a number of pages for simple control over the file
extent. For greater control, and particularly for multivolume
databases, use the MINIMUM, MAXIMUM, and PERCENT GROWTH extension
options instead.
If you use the MINIMUM, MAXIMUM, and PERCENT GROWTH parameters,
you must enclose them in parentheses.
2.3.94 – SNAPSHOT_IS_ENABLED
Syntax options:
SNAPSHOT IS IMMEDIATE | SNAPSHOT IS DEFERRED
Specifies when read/write transactions write database changes to
the snapshot file used by read-only transactions.
The ENABLED IMMEDIATE option is the default and causes read/write
transactions to write copies of rows they modify to the snapshot
file, regardless of whether or not a read-only transaction is
active. Although ENABLED IMMEDIATE is the default, if you set
snapshots ENABLED DEFERRED, you must specify both ENABLED and
IMMEDIATE options to return the database to the default setting.
The ENABLED DEFERRED option lets read/write transactions
avoid writing copies of rows they modify to the snapshot file
(unless a read-only transaction is already active). Deferring
snapshot writing in this manner improves the performance for
the read/write transaction. However, read-only transactions that
start after an active read/write transaction starts must wait for
all active read/write users to complete their transactions.
2.3.95 – SNAPSHOT_IS_DISABLED
Specifies that snapshot writing be disabled. Snapshot writing is
enabled by default.
In this mode any READ ONLY transaction will be converted to READ
WRITE mode automatically.
2.3.96 – STATISTICS_COLLECTION
Syntax options:
STATISTICS COLLECTION IS ENABLED | STATISTICS COLLECTION IS
DISABLED
Specifies whether the collection of statistics for the database
is enabled or disabled. When you disable statistics for the
database, statistics are not displayed for any of the processes
attached to the database. Statistics are displayed using the RMU
Show Statistics command.
The default is STATISTICS COLLECTION IS ENABLED. You can disable
statistics using the ALTER DATABASE and IMPORT statements.
For more information on the RMU Show Statistics command, see the
Oracle RMU Reference Manual.
You can enable statistics collection by defining the logical
name RDM$BIND_STATS_ENABLED. For more information about when to
use statistics collection, see the Oracle Rdb7 Guide to Database
Performance and Tuning.
2.3.97 – storage-area-params
Parameters that control the characteristics of the storage area.
For more information on the parameters, see the CREATE STORAGE_
AREA statement.
2.3.98 – SWEEP_INTERVAL
Syntax option:
SWEEP INTERVAL IS n SECONDS
Specifies the interval, in seconds, between each Record Cache
Server (RCS) sweep. Allowable values must be in the range from 1
second to 3600 seconds (1 hour). The default is 1.
The Record Cache Server (RCS) is a detached server process
automatically invoked by the monitor when row caching is active.
A sweep is one full pass through all active row cache areas to
write modified rows back to the database storage areas.
2.3.99 – SYNONYMS_ARE_ENABLED
Adds the optional system table RDB$OBJECT_SYNONYMS that is
used for the CREATE SYNONYM, ALTER . . . RENAME TO and RENAME
statements. The default if omitted is disabled.
2.3.100 – THRESHOLD buffers option
Syntax option:
THRESHOLD IS number-buffers PAGES
This number represents the number of sequential buffer accesses
that must be detected before prefetching is started. The default
is four buffers.
If you specify the THRESHOLD option, you must have also specified
the DETECTED ASYNC PREFETCH clause. You receive an error if you
attempt to specify the THRESHOLD option with the ASYNC PREFETCH
clause.
2.3.101 – TRANSACTION_INTERVAL
Syntax option:
TRANSACTION INTERVAL IS number-txns
The TRANSACTION INTERVAL IS clause specifies the size of the
transaction sequence number (TSN) range where number-txns equals
the number of TSNs. Oracle Rdb uses transaction sequence numbers
to ensure database integrity. When you specify NO COMMIT TO
JOURNAL OPTIMIZATION, Oracle Rdb assigns TSNs to users one at
a time. When you enable the journal optimization option, Oracle
Rdb preassigns a range of TSNs to each user. Assigning a range
of TSNs means that commit information need not be written to
the database root for each transaction. Oracle Rdb writes all
transaction information to the .aij file except for each user's
allocated TSN range, which it writes to the root file.
The transaction interval value (the TSN range) must be a number
between 8 and 1024. The default value is 256.
In general, if your database has few users or if all user
sessions are long, select a large transaction interval. If your
database has many users or if user sessions are short, select a
smaller transaction interval.
2.3.102 – txn-modes
Specifies the transaction modes for the database.
Mode Description
Transaction Types
[NO]READ Allows read-only transactions on the database.
ONLY
[NO]READ Allows read/write transactions on the database.
WRITE
[NO] BATCH Allows batch-update transactions on the database.
UPDATE This mode executes without the overhead, or
security, or a recovery-unit journal file.
The batch-update transaction is intended for
the initial loading of a database. Oracle Rdb
recommends that this mode be disabled.
Reserving Modes
[NO] SHARED Allows tables to be reserved for shared mode. That
[READ | is, other users can work with those tables.
WRITE]
[NO] Allows tables to be reserved for protected mode.
PROTECTED That is, other users can read from those tables.
[READ |
WRITE]
[NO] Allows tables to be reserved for exclusive access.
EXCLUSIVE That is, other users are prevented access to those
[READ | tables, even in READ ONLY transactions.
WRITE]
ALL Allows other users to work with all tables.
NONE Allows no access to tables.
For detailed information about the txn-modes, see the SET_
TRANSACTION.
2.3.103 – USER_LIMIT
Syntax option:
USER LIMIT IS max-glo-buffers
Specifies the maximum number of global buffers each user
allocates. Because global buffer pools are shared by all users,
you must define an upper limit on how many global buffers a
single user can allocate. This limit prevents a user from
defining RDM$BIND_BUFFERS to use all the buffers in the global
buffer pool. The user limit cannot be greater than the total
number of global buffers. The default is 5 global buffers.
Decide the maximum number of global buffers a user can allocate
by dividing the total number of global buffers by the total
number of users for whom you want to guarantee access to the
database. For example, if the total number of global buffers is
200 and you want to guarantee access to the database for at least
10 users, set the maximum number of global buffers per user to
20.
For maximum performance on a VMScluster system, tune the two
global buffer parameters on each node in the cluster using the
RMU Open command with the Global_Buffers qualifier.
Although you can change the USER LIMIT IS parameter on line, the
change does not take effect until the next time the database is
opened.
The NUMBER IS and USER LIMIT IS parameters are the only two
buffer parameters specific to global buffers. They are in effect
on a per node basis rather than a per process basis.
2.3.104 – USER username
Syntax options:
USER 'username'
A character string literal that specifies the operating system
user name that the database system uses for privilege checking.
This clause also sets the value of the SYSTEM_USER value
expression.
2.3.105 – USING password
Syntax options:
USING 'password'
A character string literal that specifies the user's password for
the user name specified in the USER clause.
2.3.106 – WAIT option
Syntax option:
WAIT n MINUTES FOR CLOSE
Specifies the amount of time that Oracle Rdb waits before
automatically closing a database. If anyone attaches during that
wait time, the database is not closed.
The default value for n is zero (0) if the WAIT clause is
not specified. The value for n can range from zero (0) to
35,791,394. However, Oracle Rdb does not recommend using large
values.
2.3.107 – WORKLOAD_COLLECTION
Syntax options:
WORKLOAD COLLECTION IS ENABLED | WORKLOAD COLLECTION IS DISABLED
Specifies whether or not the optimizer records workload
information in the system table RDB$WORKLOAD. The WORKLOAD
COLLECTION IS ENABLED clause creates this system table if
it does not exist. If you later disable workload collection,
the RDB$WORKLOAD system table is not deleted, nor is the data
deleted.
A workload profile is a description of the interesting table
and column references used by queries in a database work load.
When workload collection is enabled, the optimizer collects
and records these references in the RDB$WORKLOAD system table.
This work load is then processed by the RMU Collect Optimizer-
Statistics command which records useful statistics about the work
load. These workload statistics are used by the optimizer at run
time to deliver more accurate access strategies.
Workload collection is disabled by default.
2.4 – Examples
Example 1: Changing a read/write storage area to a read-only
storage area
This example changes the SALARY_HISTORY storage area from a
read/write storage area to a read-only storage area.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER STORAGE AREA salary_history
cont> READ ONLY;
Example 2: Adding multiple, fixed-size journal files
This example demonstrates reserving slots for journal files,
enabling the journaling feature, and adding multiple, fixed-size
journal files.
SQL> CREATE DATABASE FILENAME test
cont> RESERVE 5 JOURNALS
cont> CREATE STORAGE AREA sa_one
cont> ALLOCATION IS 10 PAGES;
SQL> DISCONNECT ALL;
SQL>
SQL> ALTER DATABASE FILENAME test
cont> JOURNAL IS ENABLED
cont> ADD JOURNAL AIJ_ONE
cont> FILENAME aij_one
cont> BACKUP FILENAME aij_one
cont> ADD JOURNAL AIJ_TWO
cont> FILENAME aij_two
cont> BACKUP FILENAME aij_two
cont> ;
You should place journal files and backup files on disks other
than those that contain the database.
Example 3: Reserving and using slots for storage areas
This example demonstrates reserving slots for storage areas
and adding storage areas to the database that utilizes those
slots. Use the SHOW DATABASE statement to see changes made to the
database.
SQL> CREATE DATABASE FILENAME sample
cont> RESERVE 5 STORAGE AREAS
cont> CREATE STORAGE AREA RDB$SYSTEM
cont> FILENAME sample_system
cont> --
cont> -- Storage areas created when the database is created do not use
cont> -- the reserved storage area slots because this operation is being
cont> -- executed off line.
cont> --
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future
recovery
SQL> --
SQL> -- Reserving storage area slots is not a journaled activity.
SQL> --
SQL> -- To use the reserved slots, you must alter the database and
SQL> -- add storage areas.
SQL> --
SQL> DISCONNECT ALL;
SQL> ALTER DATABASE FILENAME sample
cont> ADD STORAGE AREA SAMPLE_1
cont> FILENAME sample_1
cont> ADD STORAGE AREA SAMPLE_2
cont> FILENAME sample_2;
Example 4: Reserving Slots for Sequences
This example shows that reserving extra sequences in the database
adds to the existing 32 that are provided by default, and the
count rounded up to the next multiple of 32 (that is, 64).
$ SQL$ ALTER DATABASE FILENAME MF_PERSONNEL RESERVE 10 SEQUENCES;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
$ RMU/DUMP/HEADER=SEQUENCE MF_PERSONNEL
*------------------------------------------------------------------------------
* Oracle Rdb V7.1-200 15-AUG-2003 14:54:26.55
*
* Dump of Database header
* Database: USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1
*
*------------------------------------------------------------------------------
Database Parameters:
Root filename is "USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1"
Sequence Numbers...
.
.
.
Client sequences...
- 64 client sequences have been allocated
- 0 client sequences in use
Example 5: Adding and Enabling a Row Cache on OpenVMS
The MF_PERSONNEL database is altered to add a row cache, apply it
to several storage areas and enable row caching. The example
further assumes that after image journals have already been
defined for the database, they are required for the JOURNAL IS
ENABLED clause to succeed.
SQL> /*
***> Prepare the database for ROW CACHE, include extra
***> capacity for later additions
***> */
SQL> alter database
cont> filename MF_PERSONNEL
cont> number of cluster nodes is 1
cont> journal is ENABLED (fast commit is enabled)
cont> reserve 20 cache slots
cont> row cache is ENABLED
cont>
cont> /*
***> Create a physical cache for all the employee rows
***> */
cont> add cache EMPIDS_RCACHE
cont> shared memory is SYSTEM
cont> row length is 126 bytes
cont> cache size is 204 rows
cont> checkpoint updated rows to database
cont>
cont> /*
***> Apply the cache to each of the relevant storage areas
***> */
cont> alter storage area EMPIDS_LOW
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_MID
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_OVER
cont> cache using EMPIDS_RCACHE
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
Example 6: Establishing a Timeout Value for Prestarted
Transactions
SQL> ALTER DATABASE
cont> FILENAME SAMPLE
cont> PRESTARTED TRANSACTIONS ARE ENABLED
cont> (WAIT 90 SECONDS FOR TIMEOUT)
cont> ;
Example 7: Altering a Database Specifying the SINGLE INSTANCE
Option
This example prepares a database to be run in a 4 node GALAXY
cluster. The SINGLE INSTANCE clause is used to enable special
optimizations that are available because of the galaxy shared
memory.
SQL> alter database
cont> filename MF_PERSONNEL
cont> galaxy support is ENABLED
cont> number of cluster nodes is 4 (single instance);
Example 8: Disabling storage if snapshot rows
The following example demonstrates using SQL to modify the "C1"
cache to disable storage of snapshot rows in cache and to modify
the "C5" cache to enable storage of snapshot rows in the cache
with a snapshot cache size of 12345 rows:
SQL> ALTER DATABASE FILE EXAMPLE_DB
cont> ALTER CACHE C1
cont> ROW SNAPSHOT IS DISABLED;
cont> ALTER CACHE C5
cont> ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12345 ROWS);
Example 9: Using the SWEEP INTERVAL clause
Here is an example of using the SWEEP INTERVAL clause.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ROW CACHE IS ENABLED (SWEEP INTERVAL IS 100 seconds)
cont> ;
SQL> attach 'filename MF_PERSONNEL';
SQL> show database rdb$dbhandle
Default alias:
Oracle Rdb database in file MF_PERSONNEL
.
.
.
Row Cache is Enabled
Row cache: sweep interval is 100 seconds
Row cache: No Location
Row cache: checkpoint updated rows to backing file
.
.
.
3 – DOMAIN
Alters a domain definition.
The ALTER DOMAIN statement lets you change the character set,
data type, optional default value, optional collating sequence,
or optional formatting clauses associated with a domain name. Any
table or view definitions that refer to that domain reflect the
changes.
3.1 – Environment
You can use the ALTER DOMAIN statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
3.2 – Format
ALTER DOMAIN --> <domain-name> +------------------+---+
+-> IS data-type --+ |
+----------------------------------------------------+
+-+-------------------------------+------------------+
+-> SET DEFAULT value-expr -----+ |
+-> DROP DEFAULT ---------------+ |
+----------------------------------------------------+
+-+---------------------------------------------+-+
+---> COLLATING SEQUENCE IS <collation-name> -+ |
+---> NO COLLATING SEQUENCE ------------------+ |
+-------------------------------------------------+
+--+-----------------------+-+------------------------+->
+-> domain-constraint --+ ++> sql-and-dtr-clause -++
+----------<-----------+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
domain-constraint =
--+---------------------------------------------+->
+-> ADD CHECK ( predicate ) NOT DEFERRABLE --+
+-> DROP ALL CONSTRAINTS ---------------------+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> ------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> +
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS <literal> ---+
| +-> DATATRIEVE -+ |
+-> NO QUERY HEADER ---------------------------------------+
+-> NO EDIT STRING ----------------------------------------+
+-> NO QUERY NAME ----+--> FOR -+-> DTR --------+----------+
+-> NO DEFAULT VALUE -+ +-> DATATRIEVE -+ |
+-> COMMENT IS -+-> <quoted-string> -+---------------------+
| +------ / <----------+ |
+-> RENAME TO <new-name> ----------------------------------+
3.3 – Arguments
3.3.1 – char-data-types
A valid SQL character data type. For more information on
character data types, see the Data_Types HELP topic.
3.3.2 – character-set-name
A valid character set name. For a list of allowable character set
names, see the Supported_Character_Sets HELP topic.
3.3.3 – COLLATING_SEQUENCE
Specifies a new collating sequence for the named domain.
The OpenVMS National Character Set (NCS) utility provides a
set of predefined collating sequences and also lets you define
collating sequences of your own. The COLLATING SEQUENCE clause
accepts both predefined and user-defined NCS collating sequences.
Before you use the COLLATING SEQUENCE clause in an ALTER DOMAIN
statement, you must first specify the NCS collating sequence for
SQL using the CREATE COLLATING SEQUENCE statement. The sequence
name argument in the COLLATING SEQUENCE clause must be the same
as the sequence name in the CREATE COLLATING SEQUENCE statement.
3.3.4 – COMMENT_IS
Adds a comment about the domain. SQL displays the text of the
comment when it executes a SHOW DOMAIN statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.3.5 – date-time-data-types
A data type that specifies a date, time, or interval. For more
information on date-time data types, see the Data_Types HELP
topic.
3.3.6 – DEFAULT value-expr
Provides a default value for a domain.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a DEFAULT for a column, it inherits the
DEFAULT from the domain. If you do not specify a default for
either the column or domain, SQL assigns NULL as the default
value.
3.3.7 – domain-constraint
Adds or modifies a constraint for the existing named domain.
Domain constraints specify that columns based on the domain
contain only certain data values or that data values can or
cannot be null.
Use the CHECK clause to specify that a value must be within a
specified range or that it matches a list of values. When you
specify a CHECK clause for a domain constraint, you ensure that
all values stored in columns based on the domain are checked
consistently.
To refer to the values of all columns of a domain constraint, use
the VALUE keyword. For example:
SQL> CREATE DOMAIN dom1 CHAR(1)
cont> CHECK (VALUE IN ('F','M'))
cont> NOT DEFERRABLE;
For any dialect other than SQL99, SQL92, ORACLE LEVEL 1 or
ORACLE LEVEL 2, you must specify that domain constraints are
NOT DEFERRABLE.
When you add (or modify) a domain constraint, SQL propagates
the new constraint definition to all the columns that are based
on the domain. If columns that are based on the domain contain
data that does not conform to the constraint, SQL returns the
following error:
%RDB-E-NOT_VALID, validation on field DATE_COL caused operation to fail
3.3.8 – domain-name
The name of a domain you want to alter. The domain name must be
unique among domain names in the database.
3.3.9 – DROP_DEFAULT
Deletes (drops) the default value of a domain.
3.3.10 – IS datatype
A valid SQL data type. For more information on data types, see
the Data_Types HELP topic.
3.3.11 – NO_COLLATING_SEQUENCE
Specifies that the named domain uses the standard default
collating sequence, that is, ASCII. Use the NO COLLATING SEQUENCE
clause to override the collating sequence defined for the schema
in the CREATE SCHEMA or ALTER SCHEMA statement, or the domain in
the CREATE DOMAIN statement.
3.3.12 – RENAME_TO
Changes the name of the domain being altered. See the RENAME for
further discussion. If the new name is the name of a synonym then
an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE statement SYNONYMS ARE
ENABLED clause. Note that these synonyms may be deleted if they
are no longer used by database definitions or applications.
3.3.13 – SET_DEFAULT
Provides a default value for a column if the row that is inserted
does not include a value for that column. A column default
value overrides a domain default value. If you do not specify
a default value, SQL assigns NULL as the default value. For more
information about NULL, see the NULL_Keyword HELP topic.
3.3.14 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. For more
information on the formatting clauses, see the DATATRIEVE HELP
topic.
3.3.15 – value-expr
Specifies the default value of a domain.
3.4 – Examples
Example 1: Altering the domain POSTAL_CODE_DOM
This example alters the domain POSTAL_CODE_DOM so that it
accommodates longer postal codes:
SQL> --
SQL> -- The data type of the current domain POSTAL_CODE_DOM is CHAR(5):
SQL> --
SQL> SHOW DOMAIN POSTAL_CODE_DOM
POSTAL_CODE_DOM CHAR(5)
Comment: standard definition of ZIP
Rdb default:
SQL> --
SQL> -- Now, alter the domain to accommodate larger postal codes:
SQL> --
SQL> ALTER DOMAIN POSTAL_CODE_DOM IS CHAR(10);
SQL> --
SQL> -- The SHOW TABLES statement shows how changing the
SQL> -- domain POSTAL_CODE_DOM changes all the
SQL> -- columns that were created using the domain:
SQL> --
SQL> SHOW TABLE COLLEGES
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
.
.
.
POSTAL_CODE CHAR(10) POSTAL_CODE_DOM
.
.
.
SQL> SHOW TABLE EMPLOYEES
Information for table EMPLOYEES
Comment on table EMPLOYEES:
personal information about each employee
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
.
.
.
POSTAL_CODE CHAR(10) POSTAL_CODE_DOM
Example 2: Altering the domain ID_DOM
The following example alters the data type for the domain ID_DOM,
which is a standard definition of the employee identification
field.
In Example 1, there were no indexes based on the domain POSTAL_
CODE_DOM. In this example, several indexes that refer to columns
were created based on ID_DOM. As the following example shows, you
must first delete the indexes before altering the domain:
SQL> -- The data type for the domain ID_DOM is CHAR(5):
SQL> --
SQL> SHOW DOMAIN ID_DOM
ID_DOM CHAR(5)
Comment: standard definition of employee id
SQL> --
SQL> -- The first attempt to alter the domain ID_DOM fails.
SQL> -- You must first delete all constraints that use the
SQL> -- field EMPLOYEE_ID.
SQL> --
SQL> ALTER DOMAIN ID_DOM CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field EMPLOYEE_ID is referenced in constraint
RESUMES_FOREIGN1
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> ALTER TABLE RESUMES DROP CONSTRAINT RESUMES_FOREIGN1;
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field EMPLOYEE_ID is referenced in constraint
DEGREES_FOREIGN1
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> ALTER TABLE DEGREES DROP CONSTRAINT DEGREES_FOREIGN1;
.
.
.
SQL> -- You must then delete all indexes.
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINUSE, field EMPLOYEE_ID is referenced in index EMP_EMPLOYEE_ID
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> DROP INDEX EMP_EMPLOYEE_ID;
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINUSE, field EMPLOYEE_ID is referenced in index JH_EMPLOYEE_ID
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> DROP INDEX JH_EMPLOYEE_ID;
SQL> --
.
.
.
SQL> --
SQL> -- You can now alter the domain.
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
SQL> SHOW DOMAIN ID_DOM;
ID_DOM CHAR(6)
Comment: standard definition of employee id
Example 3: Specifying default values with the ALTER DOMAIN
statement
The following example alters domains, specifying default values
for those domains:
SQL> -- If no date is entered, use the NULL default.
SQL> --
SQL> ALTER DOMAIN DATE_DOM
cont> SET DEFAULT NULL;
SQL> --
SQL> -- If the street address takes only one line,
SQL> -- use "NONE" for the default for the second line.
SQL> --
SQL> ALTER DOMAIN ADDRESS_DATA_2_DOM
cont> SET DEFAULT 'NONE';
SQL> --
SQL> -- If most employees work full-time, make the code
SQL> -- for full-time, 1, the default work status.
SQL> --
SQL> ALTER DOMAIN STATUS_CODE_DOM
cont> SET DEFAULT '1';
Example 4: Specifying an edit string with the ALTER DOMAIN
statement
The following example specifies an EDIT STRING clause that
controls how SQL displays columns based on the domain MIDDLE_
INITIAL_DOM. The edit string in the example, "X.?'No middle
initial'", specifies that columns based on the domain are
displayed as one character followed by a period. If there is
no value for the column, SQL displays the string following the
question mark, 'No middle initial'.
SQL> ALTER DOMAIN MIDDLE_INITIAL_DOM
cont> EDIT STRING 'X.?''No middle initial';
SQL> SELECT MIDDLE_INITIAL FROM EMPLOYEES;
MIDDLE_INITIAL
A.
D.
No middle initial
No middle initial
.
.
.
Example 5: Specifying a new collating sequence with the ALTER
DOMAIN statement
The following example creates a domain with the predefined NCS
collating sequence FRENCH. You must first execute the CREATE
COLLATING SEQUENCE statement. The example then changes the
collating sequence to Finnish, and then specifies that the domain
has no collating sequence.
SQL> CREATE COLLATING SEQUENCE FRENCH FRENCH;
SQL> CREATE DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> COLLATING SEQUENCE IS FRENCH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Collating sequence: FRENCH
SQL> --
SQL> -- Now, change the collating sequence to Finnish. You must first
SQL> -- execute the CREATE COLLATING SEQUENCE statement.
SQL> --
SQL> CREATE COLLATING SEQUENCE FINNISH FINNISH;
SQL> ALTER DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> COLLATING SEQUENCE IS FINNISH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Collating sequence: FINNISH
SQL> --
SQL> -- Now, alter the domain so there is no collating sequence.
SQL> --
SQL> ALTER DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> NO COLLATING SEQUENCE;
SQL>
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Assume the following for Examples 6 and 7:
o The database was created specifying the database default
character set as DEC_KANJI and the national character set
as KANJI.
o The domain DEC_KANJI_DOM was created specifying the database
default character set.
o The table COLOURS was created assigning the DEC_KANJI_DOM
domain to the column ROMAJI.
Example 6: Altering the domain DEC_KANJI_DOM
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> SHOW DOMAIN DEC_KANJI_DOM;
DEC_KANJI_DOM CHAR(8)
SQL> ALTER DOMAIN DEC_KANJI_DOM NCHAR(8);
SQL> SHOW DOMAIN DEC_KANJI_DOM;
DEC_KANJI_DOM CHAR(8)
KANJI 8 Characters, 16 Octets
SQL>
Example 7: Error altering a domain used in a table containing
data
In the following example, the column ROMAJI is based on the
domain DEC_KANJI_DOM. If the column ROMAJI contains data before
you alter the character set of the domain, SQL displays the
following error when you try to retrieve data after altering
the domain.
SQL> SELECT ROMAJI FROM COLOURS;
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-CSETBADASSIGN, incompatible character sets prohibits the requested
assignment
SQL> --
SQL> -- To recover, use the ROLLBACK statement or reset the character set to
SQL> -- its original value.
SQL> --
SQL>ROLLBACK;
SQL> SELECT ROMAJI FROM COLOURS;
ROMAJI
kuro
shiro
ao
aka
ki
midori
6 rows selected
SQL>
Example 8: Modifying a domain constraint
The following example shows how to modify an existing constraint
on a domain:
SQL> SHOW DOMAIN TEST_DOM
TEST_DOM DATE ANSI
Rdb default: NULL
VALID IF: (VALUE > DATE'1900-01-01' OR
VALUE IS NULL)
SQL> --
SQL> -- Add the new domain constraint definition.
SQL> --
SQL> ALTER DOMAIN TEST_DOM
cont> ADD CHECK (VALUE > DATE'1985-01-01')
cont> NOT DEFERRABLE;
Example 9: Creating stored procedure domain dependencies
The following code fragment from a stored module shows a domain
in a parameter list and a domain in a stored procedure block:
SQL> create module SAMPLE
cont> procedure FIRST_NAME
cont> (in :id id_dom
cont> ,out :first_name char(40));
cont> begin
cont> declare :fn first_name_dom;
cont> select first_name into :fn
cont> from employees
cont> where employee_id = :id;
cont> -- return capitalized first name
cont> set :first_name =
cont> UPPER (substring (:fn from 1 for 1)) ||
cont> LOWER (substring (:fn from 2));
cont> end;
cont> end module;
SQL>
SQL> declare :first_name first_name_dom;
SQL> call FIRST_NAME ('00164', :first_name);
FIRST_NAME
Alvin
SQL>
SQL> alter domain id_dom
cont> char(10);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RTNEXI, field "ID_DOM" is used in routine "FIRST_NAME"
-RDMS-F-FLDNOTCHG, field ID_DOM has not been changed
SQL>
SQL> alter domain first_name_dom
cont> char(60);
o Domain specified in a parameter list
When you specify a domain in a parameter list (id_number)
of a stored routine and you subsequently try to alter that
domain, the ALTER DOMAIN statement fails because SQL sets up a
dependency between the domain and the stored routine in which
the domain resides. Because the statement fails, Oracle Rdb
does not invalidate the stored routine. Oracle Rdb keeps this
domain parameter list dependency in RDB$PARAMETERS.
o Domain specified in a stored routine block
When you specify a domain (last_name) within a stored routine
block and you subsequently try to alter that domain, the ALTER
DOMAIN statement succeeds. Future calls to the stored routine
will use the new definition of the domain.
Example 10: Altering a Domain to Provide a Default Value
This examples demonstrates that the default value added to the
domain is propagated to the tables using that domain.
SQL> -- Display the current domain definition.
SQL> SHOW DOMAIN DEPARTMENT_NAME
DEPARTMENT_NAME CHAR(30)
Comment: Department name
Missing Value: None
SQL> -- Alter the domain to provide a default value
SQL> -- for DEPARTMENT_NAME.
SQL> ALTER DOMAIN DEPARTMENT_NAME
cont> SET DEFAULT 'Not Recorded';
SQL> -- Display the altered domain definition.
SQL> SHOW DOMAIN DEPARTMENT_NAME;
DEPARTMENT_NAME CHAR(30)
Comment: Department name
Oracle Rdb default: Not Recorded
Missing Value: None
SQL> -- Insert a record and omit the value for DEPARTMENT_NAME.
SQL> INSERT INTO DEPARTMENTS (DEPARTMENT_CODE)
cont> VALUES
cont> ('GOGO');
1 row inserted
SQL> COMMIT;
SQL> -- Select the newly inserted record to show that the
SQL> -- default for the DEPARTMENT_NAME domain was inserted.
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_CODE='GOGO';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
GOGO Not Recorded NULL
NULL NULL
1 row selected
4 – FUNCTION
Allows attributes to be changed for a function that was created
using the CREATE MODULE statement or the CREATE FUNCTION
statement.
It can be used to:
o Force a stored (SQL) function to be compiled (COMPILE option)
o Modify attributes of external functions
o Change the comment on a function
4.1 – Environment
You can use the ALTER FUNCTION statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
4.2 – Format
ALTER FUNCTION <function-name> ++-> COMMENT IS +-> '<string>' -------+++-+->
|| +------- / <----------+|| |
|+-> COMPILE --------------------------+| |
|+-> NAME <external-body-name> ---------+ |
|+-> external-location-clause ----------+ |
|+-> LANGUAGE language-name ------------+ |
|+-> notify-clause ---------------------+ |
|+-> RETURNS NULL ON NULL INPUT --------+ |
|+-> CALLED ON NULL INPUT --------------+ |
|+-> bind-site-clause ------------------+ |
|+-> bind-scope-clause -----------------+ |
|+-+--------+--+-------------------+----+ |
|| +-> NOT -+ +-> VARIANT --------+ | |
|| +-> DETERMINISTIC --+ | |
|+> RENAME TO <new-function-name> ------+ |
+--------------<--------------------------+
external-location-clause =
---+--> DEFAULT LOCATION ------------+-------------------------+
+--> LOCATION '<image-location>' -+ |
+-------------------------------<-----------------------------+
+-+----------------------------->--------------------------+-->
+--> WITH --+-> ALL -----+--> LOGICAL_NAME TRANSLATION --+
+-> SYSTEM --+
notify-clause =
-> NOTIFY notify-entry-name --> ON -+-+-> BIND ---------+-+->
| +-> CONNECT ------+ |
| +-> TRANSACTION --+ |
+--------- , <--------+
bind-site-clause =
--> BIND ON --+--> CLIENT --+--> SITE --->
+--> SERVER --+
bind-scope-clause =
---> BIND SCOPE --+-> CONNECT ------+-->
+-> TRANSACTION --+
4.3 – Arguments
4.3.1 – bind-site-clause
Syntax options:
BIND ON CLIENT SITE | BIND ON SERVER SITE
Selects the execution model and environment for external routine
execution.
CLIENT site binding causes the external routine to be activated
and executed in the OpenVMS database client (application)
process. This is the default binding. This binding offers
the most efficient execution characteristics, allows sharing
resources such as I/O devices, and allows debugging of external
routines as if they were part of the client application. However,
this binding may suffer from address space limitations. Because
it shares virtual memory with the database buffers, this binding
is restricted to the client process system user environment, and
prohibits external routine execution in cases of an application
running with elevated privileges.
SERVER site binding causes the external routine to be activated
in a separate process from the database client and server. The
process is started on the same node at the database process.
This binding offers reasonable execution characteristics, a
larger address space, a true session user environment, and has
no restrictions regarding client process elevated privileges.
However, this binding does not permit sharing resources such
as I/O devices with the client (in particular, there is no
connection to the client interactive terminal), and debugging
of routines is generally not possible.
4.3.2 – bind-scope-clause
Syntax options:
BIND SCOPE CONNECT | BIND SCOPE TRANSACTION
Defines the scope during which an external routine is activated
and at what point the external routine is deactivated. The
default scope is CONNECT.
o CONNECT
An active routine is deactivated when you detach from the
database (or exit without detaching).
o TRANSACTION
An active routine is deactivated when a transaction is
terminated (COMMIT or ROLLBACK). In the event that a
transaction never occurs, the scope reverts to CONNECT.
4.3.3 – COMMENT IS string
Adds a comment about the function. SQL displays the text of the
comment when it executes a SHOW FUNCTIONS statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
This clause is equivalent to the COMMENT ON FUNCTION statement.
4.3.4 – COMPILE
The COMPILE option forces the Oracle Rdb server to recompile the
stored (SQL) function. External functions are not affected.
Use COMPILE when a function has been made invalid by the
execution of a DROP . . . CASCADE operation. This mechanism is
preferred over the SET FLAGS 'VALIDATE_ROUTINE' method available
in previous versions.
4.3.5 – DETERMINISTIC
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
These clauses are synonyms for the VARIANT and NOT VARIANT
clauses for conformance to the SQL/PSM standard.
The DETERMINISTIC clause indicates that the same inputs to the
function will generate the same output. It is the same as the NOT
VARIANT clause.
The NOT DETERMINISTIC clause indicates that the output of the
function does not depend on the inputs. It is the same as the
VARIANT clause.
4.3.6 – external-body-clause
Identifies key characteristics of the routine: its name, where
the executable image of the routine is located, the language in
which the routine is coded, and so forth.
4.3.7 – external-body-name
The name of the external routine. If you do not specify a name,
SQL uses the name you specify in the external-routine-name
clause.
This name defines the routine entry address that is called for
each invocation of the routine body. The named routine must exist
in the external routine image selected by the location clause.
Unquoted names are converted to uppercase characters.
4.3.8 – external-location-clause
Syntax options:
DEFAULT LOCATION
LOCATION 'image-location'
A default or specific location for the external routine image.
The resulting file specification must include the type .exe.
This can be an image file specification or merely a logical name.
SQL selects a routine based on a combination of factors:
o Image string
The location defaults to DEFAULT LOCATION, which represents
the file specification string RDB$ROUTINES.
o Logical name translation
The WITH ALL LOGICAL_NAME TRANSLATION and the WITH SYSTEM
LOGICAL_NAME TRANSLATION clauses specify how logical names in
the location string are to be translated.
If no translation option is specified, or if WITH ALL LOGICAL_
NAME TRANSLATION is specified, logical names are translated in
the default manner.
If WITH SYSTEM LOGICAL_NAME TRANSLATION is specified, any
logical names in the location string are expanded using only
EXECUTIVE_MODE logical names from the SYSTEM logical name
table.
4.3.9 – LANGUAGE language-name
The name of the host language in which the external routine
was coded. You can specify ADA, C, COBOL, FORTRAN, PASCAL, or
GENERAL. The GENERAL keyword allows you to call routines written
in any language.
4.3.10 – notify-clause
Specifies the name of a second external routine called (notified)
when certain external routine or database-related events occur.
This name defines the routine entry address that is called, for
each invocation of the notify routine. The named routine must
exist in the external routine image selected by the location
clause.
The events of interest to the notify routine are ON BIND, ON
CONNECT, and ON TRANSACTION. Multiple events can be specified.
The following describes the events and scope of each event:
BIND Routine activation to routine deactivation
CONNECT Database attach to database disconnect
TRANSACTION Start transaction to commit or roll back
transaction
4.3.11 – RENAME_TO
Changes the name of the function being altered. See the RENAME
for further discussion. If the new name is the name of a synonym
then an error will be raised.
4.3.12 – ON_NULL_INPUT
Syntax options:
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
These clauses control how an external function is invoked when
one or more of the function arguments is NULL. The CALLED ON
NULL INPUT clause specifies that the function should be executed
normally. A normal execution when the PARAMETER STYLE GENERAL
clause is specified means that SQL should return a run-time error
when the NULL value is detected.
The RETURNS NULL ON NULL INPUT clause instructs Oracle Rdb to
avoid the function call and just return a NULL result. This
option is valuable for library functions such as SIN, COS,
CHECKSUM, SOUNDEX, and so on, that usually return an UNKNOWN
result if an argument is NULL.
The CALLED ON NULL INPUT clause is the default.
4.3.13 – VARIANT
Syntax options:
VARIANT | NOT VARIANT
These clauses are synonyms for the DETERMINISTIC and NOT
DETERMINISTIC clauses for conformance to the SQL/PSM standard.
The DETERMINISTIC clause indicates that the same inputs to the
function will generate the same output. It is the same as the NOT
VARIANT clause. The NOT DETERMINISTIC clause indicates that the
output of the function does not depend on the inputs. It is the
same as the VARIANT clause.
4.4 – Examples
Example 1: Changing a function to be NOT DETERMINISTIC
When a function is created it is assumed to be DETERMINISTIC.
That is, given the same input values it should return the same
result. When a routine has no parameters, such as the GET_TIME
function shown below, then there is never any variation in the
input. In this case the function should have been defined as NOT
DETERMINISTIC to ensure that the Rdb optimizer calls it for each
row processed, instead of using the previously returned result
for each row.
Although DROP FUNCTION and CREATE FUNCTION could have performed
the same function, ALTER FUNCTION preserves the dependencies that
exist in the database.
SQL> alter function GET_TIME
cont> not deterministic
cont> comment 'Fetch time from clock'
cont> / 'Every call must be executed, so change to be'
cont> / 'NOT DETERMINISTIC';
SQL>
SQL> show function GET_TIME;
Information for function GET_TIME
Function is Not Deterministic (variant)
Function ID is: 262
External Location is: SYS$SHARE:CLOCKSHR.EXE
Entry Point is: GET_TIME
Comment: Fetch time from clock
Every call must be executed, so change to be
NOT DETERMINISTIC
Language is: COBOL
GENERAL parameter passing style used
Number of parameters is: 0
Parameter Name Data Type Domain or Type
-------------- --------- --------------
TIME(2)
Function result datatype
Return value is passed by value
5 – INDEX
Changes an index. The ALTER INDEX statement allows you to:
o Change the characteristics of index nodes (sorted indexes
only)
o Change the names of the storage areas that contain the index
o Enable or disable logging to the .aij and .ruj files
o Alter index partitions
o Change a partition name
o Change the description of a partition
o Specify whether or not the index is UNIQUE
You cannot change:
o The columns that comprise an index
o A hashed index to a sorted index
o A sorted index to a hashed index
o A sorted, nonranked index to a sorted, ranked index
o A sorted, ranked index to a sorted, nonranked index
o The duplicates compression of a sorted, ranked index
5.1 – Environment
You can use the ALTER INDEX statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
5.2 – Format
ALTER INDEX <index-name> -------+
+-------------------------------+
+---+-> add-partition-clause -------------------------------------+->
+-> BUILD PARTITION <partition-name> -------------------------+
+-> BUILD ALL PARTITIONS -------------------------------------+
+-> DROP PARTITION <partition-name> --------------------------+
+-> MOVE PARTITION <partition-name> TO area-spec -----------+
+-> REBUILD PARTITION <partition-name> -----------------------+
+-> REBUILD ALL PARTITIONS -----------------------------------+
+-> RENAME PARTITION <partition-name> TO <new-partition-name> +
+-> TRUNCATE PARTITION <partition-name> ----------------------+
+-> TRUNCATE ALL PARTITIONS ----------------------------------+
+-> alter-index-attributes -+------------------------+--------+
+-> index-store-clause --+
add-partition-clause =
-> ADD PARTITION <partition-name> ---+
+------------------------------------+
+-> USING -> ( -+-> <column-name> -+-> ) -+
+------- , <-------+ |
+-----------------------------------------+
+-> IN area-spec -------------------------+
+-----------------------------------------+
+--+----------------------------------------------+->
+-> WITH LIMIT OF -> ( -+-> <literal> -+-> ) --+
+------ , <----+
area-spec =
--> <area-name> -+---------------------------------------------------+->
+-> ( -++-> threshold-clause ---------------++-> ) -+
|+-> LOGGING ------------------------+|
|+-> NOLOGGING ----------------------+|
|+-> PARTITION <name> ---------------+|
|+-> COMMENT IS -+--> 'string' ---+--+|
| +------- / <-----+ |
+---------------- , -----------------+
alter-index-attributes=
-+-+---------------------------------------------------------+-+->
| +-> threshold-clause -------------------------------------+ |
| +-> DUPLICATES ARE ALLOWED -------------------------------+ |
| +-> LOGGING ----------------------------------------------+ |
| +-> NOLOGGING --------------------------------------------+ |
| +-> NODE SIZE <number-bytes> -----------------------------+ |
| +-> PERCENT FILL <percentage> ----------------------------+ |
| +-> PREFIX CARDINALITY COLLECTION IS -+-> ENABLED ------+-+ |
| | +-> ENABLED FULL -+ | |
| | +-> DISABLED -----+ | |
| +-> USAGE -+-> UPDATE -+----------------------------------+ |
| | +-> QUERY --+ | |
| +-> COMMENT IS -+-> '<string>' --+------------------------+ |
| | +----- / --------+ | |
| +-> MAINTENANCE IS -+-> DISABLED -----------------+-------+ |
| +-> ENABLED -+--------------+-+ |
| +-> DEFERRED --+ |
| +-> IMMEDIATE -+ |
+---------------------------- <-------------------------------+
threshold-clause =
--+-> THRESHOLD -+-> IS -+-> ( --> <val1> --> ) ---------+->
| +-> OF -+ |
| |
+-> THRESHOLDS -+-> ARE -+-----------+ |
+-> OF -+ | |
+----------------------------------+ |
+-> ( --> <val1> -+---------------------------+-> ) -+
+-> , <val2> -+-------------+
+-> , <val3> -+
index-store-clause =
STORE ----------------------+
+---------------------------+
++-> IN area-spec ---------------------------------------------+>
+-> USING -> ( -+--> <column-name> -+-> ) ----------+ |
+------- , <--------+ | |
+---------------------------------------------------+ |
++> IN area-spec -------------------------------------+ |
| +--------------------------------------------------+ |
| +-> WITH LIMIT OF -> ( -+--> <literal> -+-> ) ---+---+ |
| +------ , <-----+ | | |
+-----------------------<--------------------------+ | |
+--------------------------------------------------------+ |
++------------------------------------------------------------++
+-> OTHERWISE IN area-spec ----------------------------------+
5.3 – Arguments
5.3.1 – ADD_PARTITION
Adds the named partition to an existing hashed index. The
partition name must be unique within the index being altered.
No other clauses may appear in the same ALTER INDEX statement.
5.3.2 – BUILD_ALL_PARTITIONS
This clause operates on an index in build-pending state (created
using MAINTENANCE IS ENABLED DEFERRED) and builds all incomplete
partitions. If the index is not in build-pending state then the
statement completes successfully with a warning.
No other clauses may appear in the same ALTER INDEX statement.
5.3.3 – BUILD PARTITION partition-name
This clause operates on an index in build-pending state (created
using MAINTENANCE IS ENABLED DEFERRED) and builds the named
partition. If the index is not in build-pending state then the
statement completes successfully with a warning.
No other clauses may appear in the same ALTER INDEX statement.
5.3.4 – COMMENT_IS
Adds a comment about the index. SQL displays the text of the
comment when it executes a SHOW INDEX statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
5.3.5 – DROP_PARTITION
Specifies that the data in the named partition be migrated to
the next partition in the map and the named partition be dropped.
The last partition in the index cannot be dropped. The referenced
storage area is not dropped, only the index partition stored in
that area.
5.3.6 – DUPLICATES_ARE_ALLOWED
Converts a UNIQUE index to a non-unique index. An index altered
in this manner allows duplicate key values into the index. Note
that there is no way for you to reverse this change once you
commit the ALTER INDEX statement, other than by dropping and
redefining the index.
5.3.7 – IN
When specified as part of an ADD PARTITION clause, the IN area-
spec inserts a new partition in the index. If you do not specify
a WITH LIMIT OF clause or OTHERWISE clause, the IN area-spec
clause creates a new final partition.
When specified as part of an index STORE clause, the IN area-spec
clause associates the index directly with a single storage area,
and all entries in the index are stored in the area you specify.
5.3.8 – index-name
The name of the index.
5.3.9 – index-store-clause
A storage map definition for the index. You can specify a store
clause for indexes in a multifile database only. The STORE clause
lets you specify which storage area files are used to store the
index entries.
If you omit the storage map definition, the default is to store
all entries for the index in the default storage area.
See the the CREATE INDEX statement for details of the arguments
in an index store clause.
5.3.10 – LOGGING
Syntax options:
LOGGING | NOLOGGING
The LOGGING clause specifies that updates to new index partitions
should be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij).
The NOLOGGING clause specifies that updates to new index
partitions should not be logged in the recovery-unit journal
file (.ruj) and after-image journal file (.aij).
If no store clause is used, then these attributes provide the
setting for the ALTER INDEX statement.
The LOGGING and NOLOGGING clauses are mutually exclusive; specify
only one. The LOGGING clause is the default.
5.3.11 – MAINTENANCE_IS_DISABLED
Disables, but does not delete, the specified index.
When managing a very large database, an index can become corrupt
or unsuitable for query optimization. If the table on which the
index has been defined is very large, it may take a considerable
amount of time to execute the DROP INDEX statement. Using the
MAINTENANCE IS DISABLED clause of the ALTER INDEX statement
disables the index so that it is no longer used by the optimizer
nor is it maintained. You can then execute the DROP INDEX
statement at a later time even when the table is in use.
Once an index has been disabled, it may be enabled again using
the REBUILD PARTITION clause.
To disable an index, you must have DROP privileges to the table
on which the index is defined, and there can be no active queries
on the table.
5.3.12 – MAINTENANCE_IS_ENABLED_DEFERRED
An index created using this clause does not contain index keys
for the current rows in the table. Until this index is built
(using ALTER INDEX . . . BUILD), the index is placed in a build-
pending state. Any table with a build-pending index can not be
updated using the INSERT, DELETE, or UPDATE statements.
5.3.13 – MAINTENANCE_IS_ENABLED_IMMEDIATE
This is the default behavior for CREATE INDEX. This clause
on ALTER INDEX allows a build-pending index to be made fully
operational.
5.3.14 – MOVE_PARTITION
Specifies that the data in the named partition be moved to
the partition identified in the area-spec clause and that the
current partition is to be dropped after the data is migrated.
For example, this clause allows a single hashed index partition
to be moved to a larger storage area when too many mixed area
extends are observed.
No other clauses may appear in the same ALTER INDEX statement.
5.3.15 – NODE_SIZE
The size, in bytes, of each index node in a sorted index. You
cannot specify this argument in an ALTER INDEX statement that
refers to a hashed index. See the CREATE INDEX statement for
details of the NODE SIZE clause.
This new node size is not applied to the existing index.
However, it will be used in subsequent rebuild operations and
by EXPORT/IMPORT to rebuild the database.
5.3.16 – PARTITION
Names the partition. The name can be a delimited identifier if
the dialect is set to SQL99 or quoting rules are set to SQL99.
Partition names must be unique within the index. If you do not
specify this clause, Oracle Rdb generates a default name for
the partition. The partition name is stored in the database and
validated.
5.3.17 – PERCENT_FILL
Specifies how much each index node should be filled as a
percentage of its size. You cannot specify this argument in an
ALTER INDEX statement that refers to a hashed index. The valid
range is 1 percent to 100 percent. The default is 70 percent.
Both the PERCENT FILL and USAGE clauses specify how full an index
node should be initially. You should specify either the PERCENT
FILL or USAGE clause but not both.
5.3.18 – PREFIX_CARDINALITY_COLLECTION_IS_DISABLED
This setting disables the cardinality collection and, instead,
uses a fixed scaling algorithm which assumes a well balanced
index tree. The action of this clause will also set the existing
index column cardinalities to zero.
5.3.19 – PREFIX_CARDINALITY_COLLECTION_IS_ENABLED
This is the default behavior for CREATE INDEX. The Oracle Rdb
optimizer collects approximate cardinality values for the index
columns to help in future query optimization. Note that no extra
I/O is incurred to collect these values and, therefore, adjacent
key values from other index nodes can not be checked. Hence, some
inaccuracy may be seen for these indexes. In most cases, this
is adequate for query optimizations. If this clause is used on
an index that is currently set to PREFIX CARDINALITY COLLECTION
DISABLED, the RMU Collect Optimizer_Statistics command needs to
be executed as soon as possible to load the correct values.
5.3.20 – PREFIX_CARDINALITY_COLLECTION_IS_ENABLED_FULL
This setting requests that extra I/O be performed, if required,
to ensure that the cardinality values reflect the key value
changes of adjacent index nodes. If this clause is used on an
index which is currently set to PREFIX CARDINALITY COLLECTION
DISABLED or ENABLED, the RMU Collect Optimizer_Statistics command
needs to be executed as soon as possible to load the correct
values.
5.3.21 – REBUILD_ALL_PARTITIONS
This clause combines the TRUNCATE and BUILD actions into a single
function. No other clauses may appear in the same ALTER INDEX
statement.
5.3.22 – REBUILD PARTITION partition-name
This clause combines the TRUNCATE and BUILD actions into a single
function for the named partition. No other clauses may appear in
the same ALTER INDEX statement.
5.3.23 – RENAME_PARTITION
Changes the name of a partition. This clause can be applied to
all types of indexes. It is particularly useful for specifying
a more meaningful name for the default partition. Use the SHOW
INDEX (PARTITION) statement to display the default names of the
partitions. See Example 4 in the Examples section.
No other clauses may appear in the same ALTER INDEX statement.
5.3.24 – threshold-clause
Specifies one, two, or three default threshold values for logical
areas that contain the index in storage areas with uniform
page formats. By setting threshold values, you can make sure
that Oracle Rdb does not overlook a page with sufficient space
to store compressed data. The threshold values (val1, val2,
and val3) represent a fullness percentage on a data page and
establish three possible ranges of guaranteed free space on the
data pages.
If you use data compression, you should use logical area
thresholds to obtain optimum storage performance.
You cannot specify the thresholds for the storage map attribute
for any area that is a mixed page format. If you have a mixed
page format, set the thresholds for the storage area using the
ADD STORAGE AREA or CREATE STORAGE AREA clause of the ALTER
DATABASE, CREATE DATABASE, or IMPORT statement.
For more information about SPAM pages, see the Oracle Rdb Guide
to Database Design and Definition.
5.3.25 – TRUNCATE_ALL_PARTITIONS
This clause operates in a similar way to TRUNCATE TABLE, but just
on one index. The index is automatically set to MAINTENANCE IS
ENABLED DEFERRED (i.e. build-pending state) if it was currently
ENABLED IMMEDIATE. Otherwise is stays in a disabled state.
No other clauses may appear in the same ALTER INDEX statement.
5.3.26 – TRUNCATE PARTITION partition-name
This clause operates on just the named index partition. The index
is automatically set to MAINTENANCE IS ENABLED DEFERRED (that
is, build-pending state) if it was currently ENABLED IMMEDIATE.
Otherwise is stays in a disabled state.
No other clauses may appear in the same ALTER INDEX statement.
5.3.27 – USAGE
Syntax options:
USAGE UPDATE | USAGE QUERY
Specifies a PERCENT FILL value appropriate for update-intensive
or query-intensive applications. You cannot specify this argument
in an ALTER INDEX statement that refers to a hashed index. The
USAGE UPDATE argument sets the PERCENT FILL value at 70 percent.
The USAGE QUERY argument sets the PERCENT FILL value at 100
percent.
You should specify either the PERCENT FILL or USAGE clause, but
not both.
5.3.28 – USING
Specifies columns whose values are used as limits for
partitioning the index across multiple storage areas. You cannot
name columns not specified as index key segments.
If the index key is multisegmented, you can include some or all
of the columns that are joined to form the index key. You must
specify the columns in the order in which they were specified
when the index key was defined. If you include only a subset of
the columns from the multisegmented index, you must include the
leading columns of the index key.
5.3.29 – WITH_LIMIT_OF
Specifies the highest value for the index key that resides in a
particular storage area if ASCENDING is defined. If DESCENDING
is defined, the lowest value is specified for the index key
that resides in a particular storage area. For multicolumn index
keys, specify a literal value for each column listed in the USING
clause.
The WITH LIMIT OF clause must specify a new unique set of values
for the partition. The number of literals in the list must be
the same as the number of columns in the USING clause. The data
type of the literals must agree with the data type of the column.
For character columns, enclose the literals in single quotation
marks.
5.4 – Examples
Example 1: Disabling an index
The following example shows how to disable an index that can be
deleted at a later time when the database table can be taken off
line:
SQL> alter index COLL_COLLEGE_CODE
cont> maintenance is disabled;
SQL> show index COLL_COLLEGE_CODE
Indexes on table COLLEGES:
COLL_COLLEGE_CODE with column COLLEGE_CODE
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Index is no longer maintained
Node size 430
Example 2: Changing a Unique Index to Non-Unique
SQL> show table (index) DEPARTMENTS
Information for table DEPARTMENTS
Indexes on table DEPARTMENTS:
DEPARTMENTS_INDEX with column DEPARTMENT_CODE
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
SQL> insert into DEPARTMENTS (DEPARTMENT_CODE) values ('SUSO');
%RDB-E-NO_DUP, index field value already exists;
duplicates not allowed for DEPARTMENTS_INDEX
SQL> alter index DEPARTMENTS_INDEX duplicates are allowed;
SQL> insert into DEPARTMENTS (DEPARTMENT_CODE) values ('SUSO');
1 row inserted
SQL> show table (index) DEPARTMENTS
Information for table DEPARTMENTS
Indexes on table DEPARTMENTS:
DEPARTMENTS_INDEX with column DEPARTMENT_CODE
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
Example 3: Adding an Index Partition Before and After the Final
Partition
SQL> CREATE UNIQUE INDEX EMPLOYEES_INDEX
cont> ON EMPLOYEES (EMPLOYEE_ID)
cont> TYPE IS HASHED
cont> STORE USING (EMPLOYEE_ID)
cont> IN JOBS WITH LIMIT OF ('00999');
SQL> COMMIT;
SQL> -- To add a partition before the final partition requires
SQL> -- that the final partition (which now follows the new partition)
SQL> -- be scanned and matching keys moved to the new partition.
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES for EXCLUSIVE WRITE;
SQL> SET FLAGS INDEX_STATS;
SQL> ALTER INDEX EMPLOYEES_INDEX
cont> ADD PARTITION NEW_EMPS_200
cont> USING (EMPLOYEE_ID)
cont> IN EMP_INFO WITH LIMIT OF ('00200');
~Ai alter index "EMPLOYEES_INDEX" (hashed=1, ordered=0)
~Ai add partition "NEW_EMPS_200" : area "EMP_INFO"
~Ai storage area "EMP_INFO" larea=85
~Ai splitting partition #1
~Ai split complete: total 100 keys, moved 37 (dups 0)
~Ai reads: async 136 synch 30, writes: async 57 synch 0
SQL> COMMIT;
SQL> -- Now add a partition after the final partition of
SQL> -- the index. This requires no I/O to the partition because
SQL> -- there is no following partition and therefore no keys
SQL> -- to be moved.
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES FOR EXCLUSIVE WRITE;
SQL> ALTER INDEX EMPLOYEES_INDEX
cont> ADD PARTITION NEW_EMPS_1400
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_OVER WITH LIMIT OF ('01400');
~Ai alter index "EMPLOYEES_INDEX" (hashed=1, ordered=0)
~Ai add partition "NEW_EMPS_1400" : area "EMPIDS_OVER"
~Ai storage area "EMPIDS_OVER" larea=122
~Ai adding new final partition 3
SQL> COMMIT;
SQL> -- Show the index. It shows the ADD PARTITION syntax appended
SQL> -- to the original source of the index.
SQL> SHOW INDEX EMPLOYEES_INDEX
Indexes on table EMPLOYEES:
EMPLOYEES_INDEX with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key Suffix Compression is DISABLED
Store clause: STORE using (EMPLOYEE_ID)
in JOBS with limit of ('00999')
Add Partition partition NEW_EMPS_200
using (EMPLOYEE_ID)
in EMP_INFO with limit of ('00200')
Add Partition partition NEW_EMPS_1400
using (EMPLOYEE_ID)
in EMPIDS_OVER with limit of ('01400')
Example 4: Renaming a Partition
$ rmu /extract /item=index mf_personnel.rdb
.
.
.
create unique index EMPLOYEES_HASH
on EMPLOYEES (
EMPLOYEE_ID)
type is HASHED
store
using (EMPLOYEE_ID)
in EMPIDS_LOW(
partition "SYS_P00076"
)
with limit of ('00200')
in EMPIDS_MID(
partition "SYS_P00077"
)
with limit of ('00400')
otherwise in EMPIDS_OVER(
partition "SYS_P00078"
);
commit work;
$SQL$
SQL> ATTACH FILENAME MF_PERSONNEL.RDB;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00076 TO IDS_LOW;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00077 TO IDS_MID;
SQL> ALTER INDEX EMPLOYEES_HASH
cont> RENAME PARTITION SYS_P00078 TO IDS_HIGH;
SQL> COMMIT;
SQL> SHOW INDEX EMPLOYEES_HASH;
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key Suffix Compression is DISABLED
Store clause: STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF ('00200')
IN EMPIDS_MID WITH LIMIT OF ('00400')
OTHERWISE IN EMPIDS_OVER
Rename PARTITION SYS_P00076 TO IDS_LOW
Rename PARTITION SYS_P00077 TO IDS_MID
Rename PARTITION SYS_P00078 TO IDS_HIGH
Example 5: Creating a Large Index Partitioned Across Many Storage
Areas
First, create the database definition:
SQL> CREATE INDEX ... MAINTENANCE IS ENABLED DEFERRED ...;
Next submit batch jobs to build each partition in parallel. For
example, each batch job would execute a script similar to the
following:
ATTACH 'filename testdatabase';
SET FLAGS 'index_stats';
ALTER INDEX TRANSACTIONS_INDEX BUILD PARTITION PART_1;
COMMIT;
Finally, after the batch jobs have completed, the database
administrator must make the index active for query usage by
changing the maintenance mode to ENABLED IMMEDIATE. A BUILD
ALL PARTITIONS clause could be added in case any step failed
(possibly due to resource limitations or a failed node).
SQL> SET FLAGS 'index_stats';
SQL> SET TRANSLATION READ WRITE RESERVING...FOR EXCLUSIVE WRITES;
SQL> ALTER INDEX ... BUILD ALL PARTITIONS;
SQL> ALTER INDEX ... MAINTENANCE IS ENABLED IMMEDIATE;
SQL> COMMIT;
This scheme has several advantages over issuing a CREATE INDEX
statement directly:
o The build actions can be run in parallel, which allows
better resource usage (read and sort fewer rows), and reduced
execution time for the index creation.
o The partitions being processed are relatively small when
compared to the full index and, therefore, smaller quantities
of data will be processed. This will result in smaller .ruj
files and less AIJ file space for these transactions.
o Each build partition runs in a separate transaction, can
easily be repeated if a step fails, and does not require
repeating the entire CREATE INDEX statement.
o If any steps have failed, they will also be repeated by the
BUILD ALL PARTITIONS clause included in the script.
Example 6: Deleting a Large Index Partitioned Across Many Storage
Areas
First, disable the index:
SQL> ALTER INDEX TRANSACTIONS_INDEX MAINTENANCE IS DISABLED;
Next, submit batch jobs to truncate the partitions in parallel:
SQL> ALTER INDEX TRANSACTIONS_INDEX TRUNCATE PARTITION PART_1;
SQL> COMMIT;
Finally, after the batch jobs are complete, remove the metadata:
SQL> DROP INDEX TRANSACTIONS_INDEX;
This scheme has several advantages over a issuing a DROP INDEX
statement directly:
o The truncate actions can be run in parallel, which allows
better resource usage and reduced execution time for the index
deletion.
o The partitions being processed are relatively small when
compared to the full index and, therefore, smaller quantities
of data will be processed. This will result in smaller .ruj
files and less AIJ file space for these transactions.
o Each truncate partition runs in a separate transaction, can
easily be repeated if a step fails, and does not require
repeating the entire action.
o If any steps have failed, they will also be repeated by a DROP
INDEX statement.
Example 7: Using the TRUNCATE PARTITION statement
The following example illustrates using the TRUNCATE PARTITION
statement for the MF_PERSONNEL database.
SQL> show index (partition) EMPLOYEES_HASH
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key suffix compression is DISABLED
Partition information for index:
Partition: (1) SYS_P00076
Storage Area: EMPIDS_LOW
Partition: (2) SYS_P00077
Storage Area: EMPIDS_MID
Partition: (3) SYS_P00078
Storage Area: EMPIDS_OVER
SQL> alter index employees_hash truncate partition SYS_P00077;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-IDXBLDPEND, index in build pending state - maintenance is disabled
SQL> insert into employees default values;
%RDB-E-READ_ONLY_REL, relation EMPLOYEES was reserved for read access; updates
not allowed
-RDMS-F-BUILDPENDING, index in build pending state - operation not permitted
Until the index is made complete it will not be used by the query
optimizer, nor can the table on which it is defined be updated.
The SHOW INDEX command reports this state.
SQL> show index employees_hash
Indexes on table EMPLOYEES:
EMPLOYEES_HASH with column EMPLOYEE_ID
No Duplicates allowed
Type is Hashed Scattered
Key suffix compression is DISABLED
Maintenance is Deferred - build pending
6 – MODULE
Alters a module to add or drop routines, change a comment, or
compile stored routines.
6.1 – Environment
You can use the ALTER MODULE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
6.2 – Format
ALTER MODULE <module-name> -+-> alter-module-clauses -+-+----------------+--->
+-----------<-------------+ +-> END MODULE --+
alter-module-clauses =
-+-> ADD routine-clause ---------------+->
+-> COMMENT IS +-> '<text-literal>' -++
| +------- / <----------+|
+-> COMPILE --------------------------+
+-> drop-routine-clause --------------+
+-> RENAME TO <new-module-name> ------+
drop-routine-clause =
DROP -+-> FUNCTION --+> <routine-name> -+-+---------------+-+->
+-> PROCEDURE -+ | +-> CASCADE ----+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
6.3 – Arguments
6.3.1 – ADD routine-clause
Allows new functions and procedures to be added to the module.
Refer to the CREATE MODULE for details on the routine-clause. The
END MODULE clause must be used to end the ALTER MODULE clause to
provide an unambiguous statement termination.
6.3.2 – COMMENT_IS
Adds a comment about the module. Enclose the comment within
single quotation marks (') and separate multiple lines in a
comment with a slash mark (/). This clause is equivalent to the
COMMENT ON MODULE statement.
6.3.3 – COMPILE
Recompiles stored routines in the module. Any that were
marked invalid will have this flag cleared if the compile was
successful.
6.3.4 – drop-routine-clause
The DROP FUNCTION and DROP PROCEDURE clauses will drop the named
routines from this module. All DROP clauses are executed prior to
the COMPILE and ADD clauses in this ALTER statement.
6.3.5 – END_MODULE
This terminating clause is required when using ADD FUNCTION or
ADD PROCEDURE since there is no way to distinguish between the
end of a compound statement and the end of the ALTER MODULE
statement.
6.3.6 – RENAME_TO
Changes the name of the module being altered. See the RENAME for
further discussion. If the new name is the name of a synonym then
an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
The old name will be used to create a synonym for the new name of
this module. This synonym can be dropped if the name is no longer
used by applications.
6.4 – Examples
Example 1: Changing the comment on a module
A comment can be added or changed on a module using the COMMENT
IS clause as shown in this example.
SQL> alter module EMPLOYEES_MAINTENANCE
cont> comment is
cont> 'routines to add and remove employee rows'
cont> / 'Fix: also record the employees birthday';
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
IS_CURRENT_EMPLOYEE
REMOVE_EMPLOYEE
Example 2: Revalidating all routines in a module
The COMPILE clause can be used to check each stored procedure or
function to ensure that it can be executed. If the compile fails
it will report the first reason, in this example a missing table.
SQL> alter module EMPLOYEES_MAINTENANCE compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown relation symbol - ARCHIVE_EMPLOYEES
Example 3: Replacing a routine in a module
The following example creates a simple module and shows the
effect of DROP TABLE . . . CASCADE. That is, the procedure REMOVE_
EMPLOYEE is marked as invalid. The COMPILE clause is used to
attempt to re-validate the procedure, however, a referenced table
no longer exists. After replacing the table the COMPILE completes
successfully.
SQL> set dialect 'sql99';
SQL> attach 'file PERSONNEL1';
SQL>
SQL> create table EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> birthday date,
cont> start_date date default current_date);
SQL>
SQL> create table ARCHIVE_EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> archive_date date default current_date);
SQL>
SQL> create module EMPLOYEES_MAINTENANCE
cont>
cont> procedure REMOVE_EMPLOYEE (in :employee_id integer);
cont> begin
cont> -- take copy of the old row
cont> insert into ARCHIVE_EMPLOYEES
cont> (employee_id, last_name, first_name)
cont> select employee_id, last_name, first_name
cont> from EMPLOYEES
cont> where employee_id = :employee_id;
cont> -- remove the old row
cont> delete from EMPLOYEES
cont> where employee_id = :employee_id;
cont> end;
cont>
cont> procedure ADD_EMPLOYEE
cont> (in :employee_id integer,
cont> in :last_name char(40),
cont> in :first_name char(40),
cont> in :birthday date);
cont> insert into EMPLOYEES
cont> (employee_id, last_name, first_name, birthday)
cont> values (:employee_id, :last_name, :first_name, :birthday);
cont>
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
REMOVE_EMPLOYEE
SQL>
SQL> drop table ARCHIVE_EMPLOYEES cascade;
SQL>
SQL> show procedure REMOVE_EMPLOYEE;
Information for procedure REMOVE_EMPLOYEE
Current state is INVALID
Can be revalidated
Procedure ID is: 8
Source:
REMOVE_EMPLOYEE (in :employee_id integer);
begin
-- take copy of the old row
insert into ARCHIVE_EMPLOYEES
(employee_id, last_name, first_name)
select employee_id, last_name, first_name
from EMPLOYEES
where employee_id = :employee_id;
-- remove the old row
delete from EMPLOYEES
where employee_id = :employee_id;
end
No description found
Module name is: EMPLOYEES_MAINTENANCE
Module ID is: 7
Number of parameters is: 1
Parameter Name Data Type Domain or Type
-------------- --------- --------------
EMPLOYEE_ID INTEGER
Parameter position is 1
Parameter is IN (read)
Parameter is passed by reference
SQL>
SQL> -- COMPILE reports the missing table
SQL> alter module EMPLOYEES_MAINTENANCE compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown relation symbol - ARCHIVE_EMPLOYEES
SQL>
SQL> create table ARCHIVE_EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> birthday date,
cont> archive_date date default current_date);
SQL>
SQL> -- new table definition is compatible
SQL> alter module EMPLOYEES_MAINTENANCE compile;
SQL>
SQL> alter module EMPLOYEES_MAINTENANCE
cont> comment is
cont> 'routines to add and remove employee rows'
cont> / 'Fix: also record the employees birthday'
cont>
cont> drop procedure REMOVE_EMPLOYEE if exists
cont>
cont> add procedure REMOVE_EMPLOYEE (in :employee_id integer);
cont> begin
cont> -- take copy of the old row
cont> insert into ARCHIVE_EMPLOYEES
cont> (employee_id, last_name, first_name, birthday)
cont> select employee_id, last_name, first_name, birthday
cont> from EMPLOYEES
cont> where employee_id = :employee_id;
cont> -- remove the old row
cont> delete from EMPLOYEES
cont> where employee_id = :employee_id;
cont> end;
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
REMOVE_EMPLOYEE
Example 4: Adding a new function to a module
In the following example the ADD clause is used to add a new
function to an existing module.
SQL> alter module EMPLOYEES_MAINTENANCE
cont> add function IS_CURRENT_EMPLOYEE (in :employee_id integer)
cont> returns integer;
cont> return (case
cont> when exists (select *
cont> from EMPLOYEES
cont> where employee_id = :employee_id)
cont> then 1
cont> else 0
cont> end);
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
IS_CURRENT_EMPLOYEE
REMOVE_EMPLOYEE
7 – OUTLINE
Alters an outline definition.
7.1 – Environment
You can use the ALTER OUTLINE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
7.2 – Format
ALTER OUTLINE <outline-name> ----+
+--------------------------------+
+-+-+-> COMMENT IS --+-------> '<string>' ---------+--+-+->
| | +------------ / <--------------+ | |
| +-> COMPILE ---------------------------------------+ |
| +-> MOVE TO <new-schema-name> -----------------+ |
| +-> RENAME TO <new-outline-name> -----------------+ |
+-------------------------- <--------------------------+
7.3 – Arguments
7.3.1 – COMMENT IS string
The COMMENT IS clause can be used to modify the comment stored
with the query outline. The COMMENT ON statement is identical in
function to the ALTER OUTLINE . . . COMMENT IS clause.
This clause is equivalent to the COMMENT ON procedure.
7.3.2 – COMPILE
The COMPILE option can be applied to query outlines that have
been made invalid by DROP TABLE or DROP INDEX. If the tables and
indices have been recreated then the query outline will be made
valid again. For example, once re-validated the optimizer will
try to use that query outline.
NOTE
There is a possibility that the query outline although
marked valid will not be used because of changes in the
index definition. There is too little information stored
with the query outline to perform a complete consistency
check. If possible, queries using this outline should be run
to verify correct index and table usage.
If the query outline is currently valid then this clause is
ignored by Oracle Rdb.
7.3.3 – MOVE_TO
MOVE TO is valid only for multischema databases. You must be
attached explicitly or implicitly with the MULTISCHEMA IS ON
clause. The MOVE TO clause can be used to move the query outline
to a different catalog and schema. An error will be raised if
this clause is specified in a non-multischema environment.
The target catalog and schema must exist in this database.
7.3.4 – RENAME_TO
The RENAME TO clause can be used to change the name of the
outline. The new name must not already exist in the database.
If RENAME TO is used in a multischema database, attached with
MULTISCHEMA IS ON, then only the multischema name is modified
not the STORED NAME of the object. To change the STORED NAME of
the query outline you must attach to the database explicitly with
the MULTISCHEMA IS OFF clause (see the example below). Please
note that the STORED NAME for the query outline may have been
generated by Oracle Rdb.
NOTE
Any queries using the OPTIMIZE USING clause will also need
to be changed to reference this new outline name.
7.4 – Examples
Example 1: Changing the comment on a query outline
SQL> alter outline show_tables
cont> comment is 'show the tables query'
cont> / 'derived from a stored procedure';
SQL> show outline show_tables
SHOW_TABLES
Comment: show the tables query
derived from a stored procedure
Source:
-- Rdb Generated Outline : 8-FEB-2002 16:17
create outline SHOW_TABLES
id '4D5B5CC5B46C6DD21B0E1999C0EB8BF3'
mode 0
as (
query (
-- For loop
subquery (
RDB$RELATIONS 0 access path index RDB$REL_REL_NAME_NDX
)
)
)
compliance optional ;
Example 2: Using the alternate COMMENT ON syntax to change the
comment
SQL> comment on outline show_tables
cont> is 'show the tables query'
cont> / 'derived from the stored procedure'
cont> / 'SHOW_TABLES';
Example 3: Changing the name of a query outline
SQL> alter outline show_tables
cont> rename to show_the_tables;
SQL> show outline show_the_tables
SHOW_THE_TABLES
Comment: show the tables query
derived from the stored procedure
testing new COMMENT ON OUTLINE
Source:
-- Rdb Generated Outline : 8-FEB-2002 16:17
create outline SHOW_THE_TABLES
id '4D5B5CC5B46C6DD21B0E1999C0EB8BF3'
mode 0
as (
query (
-- For loop
subquery (
RDB$RELATIONS 0 access path index RDB$REL_REL_NAME_NDX
)
)
)
compliance optional ;
Example 4: This example shows setting a query outline valid after
a DROP INDEX
First, our stored procedure is executed with the STRATEGY flag
defined so we can see that it is using a query outline named MY_
OUTLINE.
SQL> set flags 'strategy';
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" used
Aggregate Conjunct Index only retrieval of relation MY_TABLE
Index name MY_INDEX [1:1]
Now the index that was used by the query (and referenced by the
query outline) is dropped. This causes the query outline to be
set invalid (as shown by using the WARN_INVALID flag). The query
now uses sequential access strategy when the stored procedure is
executed.
SQL> set flags 'warn_invalid';
SQL> drop index my_index;
~Xw: Outline "MY_OUTLINE" marked invalid (index "MY_INDEX" dropped)
SQL>
SQL> set flags 'strategy';
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" is invalid and can not be used
Aggregate Conjunct Get
Retrieval sequentially of relation MY_TABLE
SQL> show outline my_outline
MY_OUTLINE
Outline has been marked invalid
.
.
.
The ALTER OUTLINE ... COMPILE clause is now used to make the
outline valid. The first attempt reports that the index is
missing. After the index is recreated the COMPILE succeeds.
Calling the stored procedure now uses this query outline.
SQL> alter outline my_outline compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-INDNOTEXI, index MY_INDEX does not exist in this database
SQL> -- must redefine the index
SQL> create index my_index on my_table (b desc);
SQL> alter outline my_outline compile;
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" used
Aggregate Conjunct Index only retrieval of relation MY_TABLE
Index name MY_INDEX [1:1]
SQL>
Example 5: Changing the STORED NAME of a query outline in a
multischema database
This example shows how to change the STORED NAME of a multischema
outline. Here we explicitly provide the STORED NAME, however,
the same technique can be used when SQL generates a unique STORED
NAME for the outline.
SQL> attach 'filename mschema';
SQL> create outline SHOW_TABLE
cont> stored name SHOW_TABLE_01
cont> on procedure name SHOW_TABLES;
SQL> commit;
SQL> disconnect all;
SQL> attach 'filename mschema MULTISCHEMA IS OFF';
SQL> alter outline SHOW_TABLE_01
cont> rename to SHOW_THE_TABLES;
SQL> commit;
8 – PROCEDURE
Allows attributes to be changed for a procedure that was created
using the CREATE MODULE statement or the CREATE PROCEDURE
statement.
It can be used to:
o Force a stored (SQL) procedure to be compiled (COMPILE option)
o Modify attributes of an external procedure
o Change the comment on a procedure
8.1 – Environment
You can use the ALTER PROCEDURE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
8.2 – Format
ALTER PROCEDURE <proc-name> ---++-> COMMENT IS +-> '<string>' -------+++->
|| +------- / <----------+||
|+-> COMPILE --------------------------+|
|+-> NAME <external-body-name> --------+|
|+-> external-location-clause ---------+|
|+-> LANGUAGE language-name -----------+|
|+-> notify-clause --------------------+|
|+-> bind-site-clause -----------------+|
|+-> bind-scope-clause ----------------+|
|+-> RENAME TO <new-procedure-name> ---+|
+--------------<------------------------+
external-location-clause =
---+--> DEFAULT LOCATION ------------+-------------------------+
+--> LOCATION '<image-location>' -+ |
+-------------------------------<-----------------------------+
+-+----------------------------->--------------------------+-->
+--> WITH --+-> ALL -----+--> LOGICAL_NAME TRANSLATION --+
+-> SYSTEM --+
notify-clause =
-> NOTIFY notify-entry-name --> ON -+-+-> BIND ---------+-+->
| +-> CONNECT ------+ |
| +-> TRANSACTION --+ |
+--------- , <--------+
bind-site-clause =
--> BIND ON --+--> CLIENT --+--> SITE --->
+--> SERVER --+
bind-scope-clause =
---> BIND SCOPE --+-> CONNECT ------+-->
+-> TRANSACTION --+
8.3 – Arguments
8.3.1 – bind-site-clause
Syntax options:
BIND ON CLIENT SITE | BIND ON SERVER SITE
Selects the execution model and environment for external routine
execution.
CLIENT site binding causes the external routine to be activated
and executed in the OpenVMS database client (application)
process. This is the default binding. This binding offers
the most efficient execution characteristics, allows sharing
resources such as I/O devices, and allows debugging of external
routines as if they were part of the client application. However,
this binding may suffer from address space limitations. Because
it shares virtual memory with the database buffers, this binding
is restricted to the client process system user environment, and
prohibits external routine execution in cases of an application
running with elevated privileges.
SERVER site binding causes the external routine to be activated
in a separate process from the database client and server. The
process is started on the same node at the database process.
This binding offers reasonable execution characteristics, a
larger address space, a true session user environment, and has
no restrictions regarding client process elevated privileges.
However, this binding does not permit sharing resources such
as I/O devices with the client (in particular, there is no
connection to the client interactive terminal), and debugging
of routines is generally not possible.
8.3.2 – bind-scope-clause
Syntax options:
BIND SCOPE CONNECT | BIND SCOPE TRANSACTION
Defines the scope during which an external routine is activated
and at what point the external routine is deactivated. The
default scope is CONNECT.
o CONNECT
An active routine is deactivated when you detach from the
database (or exit without detaching).
o TRANSACTION
An active routine is deactivated when a transaction is
terminated (COMMIT or ROLLBACK). In the event that a
transaction never occurs, the scope reverts to CONNECT.
8.3.3 – COMMENT IS string
Adds a comment about the procedure. SQL displays the text of the
comment when it executes a SHOW PROCEDURES statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
This clause is equivalent to the COMMENT ON PROCEDURE statement.
8.3.4 – COMPILE
The COMPILE option forces the Oracle Rdb server to recompile the
stored (SQL) procedure. External procedures are not affected.
Use COMPILE when a procedure has been made invalid by the
execution of a DROP . . . CASCADE operation. This mechanism is
preferred over the SET FLAGS 'VALIDATE_ROUTINE' method available
in previous versions.
8.3.5 – external-body-clause
Identifies key characteristics of the routine: its name, where
the executable image of the routine is located, the language in
which the routine is coded, and so forth.
8.3.6 – external-body-name
The name of the external routine. If you do not specify a name,
SQL uses the name you specify in the external-routine-name
clause.
This name defines the routine entry address that is called for
each invocation of the routine body. The named routine must exist
in the external routine image selected by the location clause.
Unquoted names are converted to uppercase characters.
8.3.7 – external-location-clause
Syntax options:
DEFAULT LOCATION
LOCATION 'image-location'
A default or specific location for the external routine image.
The resulting file specification must include the type .exe.
This can be an image file specification or merely a logical name.
SQL selects a routine based on a combination of factors:
o Image string
The location defaults to DEFAULT LOCATION, which represents
the file specification string RDB$ROUTINES.
o Logical name translation
The WITH ALL LOGICAL_NAME TRANSLATION and the WITH SYSTEM
LOGICAL_NAME TRANSLATION clauses specify how logical names in
the location string are to be translated.
If no translation option is specified, or if WITH ALL LOGICAL_
NAME TRANSLATION is specified, logical names are translated in
the default manner.
If WITH SYSTEM LOGICAL_NAME TRANSLATION is specified, any
logical names in the location string are expanded using only
EXECUTIVE_MODE logical names from the SYSTEM logical name
table.
8.3.8 – LANGUAGE language-name
The name of the host language in which the external routine
was coded. You can specify ADA, C, COBOL, FORTRAN, PASCAL, or
GENERAL. The GENERAL keyword allows you to call routines written
in any language.
8.3.9 – notify-clause
Specifies the name of a second external routine called (notified)
when certain external routine or database-related events occur.
This name defines the routine entry address that is called, for
each invocation of the notify routine. The named routine must
exist in the external routine image selected by the location
clause.
The events of interest to the notify routine are ON BIND, ON
CONNECT, and ON TRANSACTION. Multiple events can be specified.
The following describes the events and scope of each event:
BIND Routine activation to routine deactivation
CONNECT Database attach to database disconnect
TRANSACTION Start transaction to commit or roll back
transaction
8.3.10 – RENAME_TO
Changes the name of the procedure being altered. See the RENAME
for further discussion. If the new name is the name of a synonym
then an error will be raised.
8.4 – Examples
Example 1: Using ALTER PROCEDURE to target a new routine and
sharable image
This example shows ALTER PROCEDURE updating the location, routine
name and language for an external procedure.
SQL> show procedure SEND_MAIL
Information for procedure SEND_MAIL
Procedure ID is: 261
External Location is: SYS$SHARE:SENDMAILSHR.EXE
Entry Point is: SEND_MAIL
Language is: COBOL
GENERAL parameter passing style used
Number of parameters is: 2
Parameter Name Data Type Domain or Type
-------------- --------- --------------
USR CHAR(30)
Parameter position is 1
Parameter is IN (read)
Parameter is passed by reference
TXT VARCHAR(1000)
Parameter position is 2
Parameter is IN (read)
Parameter is passed by reference
SQL> /*
***> The routine has been rewritten. Use ALTER PROCEDURE
***> to retarget the external routine to use the new
***> implementation, instead of using DROP/CREATE
***> */
SQL>
SQL> set quoting rules 'SQL99';
SQL>
SQL> alter procedure SEND_MAIL
cont> name "send_mail_ext"
cont> location 'SYS$SHARE:SENDMAILSHR30.EXE'
cont> language C
cont> comment 'Use new V3.0 interface routine';
SQL>
9 – PROFILE
Alters a profile definition.
9.1 – Environment
You can use the ALTER PROFILE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
9.2 – Format
ALTER -+-> PROFILE --> <profilename> --------------+--+-> profile-options -+->
+-> DEFAULT PROFILE -+---------------------++ +--------<-----------+
+-> ALIAS aliasname --+
profile-options =
--+-> COMMENT IS -+-> char-literal --+--------------------+>
| +------- / <-------+ |
+-> RENAME TO <new-profile-name> -----------------------+
+-> DEFAULT TRANSACTION --> txn-options ----------------+
+-> TRANSACTION MODES --> (txn-modes) ------------------+
+-> LIMIT -+-+-> ROWS limit-value --------------------+++
| | +-> TIME limit-value -----+-+-----------++||
| | +-> CPU TIME limit-value -+ +-> SECONDS + ||
| | +-> MINUTES + ||
| +-------------------- <---------------------+|
+-> NO -+> DEFAULT TRANSACTION --+----------------------+
+> TRANSACTION MODES -+-+
+> LIMIT-+-> CPU TIME -+
+-> ROWS ---+
+-> TIME ----+
9.3 – Arguments
9.3.1 – ALIAS aliasname
When attached to multiple databases, the aliasname is required to
direct the ALTER command to the appropriate database.
9.3.2 – COMMENT_IS
Adds a comment about the profile. SQL displays the text of the
comment when it executes a SHOW PROFILES statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
9.3.3 – DEFAULT_PROFILE
Alters the special profile RDB$DEFAULT_PROFILE. This profile
will be used by any user who is not assigned a profile using the
PROFILE clause of CREATE or ALTER PROFILE.
9.3.4 – DEFAULT_TRANSACTION
DEFAULT TRANSACTION provides a default transaction for the user.
By default, Oracle Rdb starts a READ WRITE transaction if none is
explicitly started. Use the DECLARE TRANSACTION or START DEFAULT
TRANSACTION statement to make use of this definition. You can
override this clause with a DECLARE or SET TRANSACTION statement.
NOTE
Oracle Rdb does not permit the RESERVING or EVALUATING
clauses to appear in the default transaction.
9.3.5 – LIMIT_CPU_TIME
Syntax options:
LIMIT CPU TIME | NO LIMIT CPU TIME
LIMIT CPU TIME sets the maximum CPU time that can be used by the
query compiler. The keyword DEFAULT indicates that no value is
defined by this profile and is equivalent to NO LIMIT CPU TIME.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT CPU TIME
statement is present in the session, or when the logical name
RDMS$BIND_QG_CPU_TIMEOUT is defined.
NO LIMIT CPU TIME is the default. Units can be specified as
seconds or minutes.
9.3.6 – LIMIT_ROWS
Syntax options:
LIMIT ROWS | NO LIMIT ROWS
LIMIT ROWS sets the maximum number of rows that can be returned
by a query started by the user. The keyword DEFAULT indicates
that no value is defined by this profile and is equivalent to NO
LIMIT ROWS.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT ROWS
statement is present in the session, or when the logical name
RDMS$BIND_QG_REC_LIMIT is defined.
NO LIMIT ROWS is the default.
9.3.7 – LIMIT_TIME
Syntax options:
LIMIT TIME | NO LIMIT TIME
LIMIT TIME sets the maximum elapsed time that can be used by the
query compiler. The keyword DEFAULT indicates that no value is
defined by this profile and is equivalent to NO LIMIT TIME.
If a numeric value or the keyword UNLIMITED is specified then
this value will be used even when the SET QUERY LIMIT TIME
statement is present in the session, or when the logical name
RDMS$BIND_QG_TIMEOUT is defined.
NO LIMIT TIME is the default. Units can be specified as seconds
or minutes.
9.3.8 – negated-attributes
Syntax options:
NO DEFAULT TRANSACTION
NO TRANSACTION MODES
NO LIMIT CPU TIME
NO LIMIT ROWS N
NO LIMIT TIME
These options explicitly record the negated attribute setting.
These clauses will remove the current setting of any clause being
negated.
9.3.9 – RENAME_TO
Changes the name of the profile being altered. See the RENAME
statement for further discussion.
9.3.10 – TRANSACTION_MODES
Syntax options:
TRANSACTION MODES | NO TRANSACTION MODES
TRANSACTION MODES provides the list of allowable transactions
for this user. Please see the SET TRANSACTION MODES clause of the
CREATE DATABASE and ALTER DATABASE statements for more details of
txn-modes.
The transaction modes specified may include modes disabled
for all database users by CREATE, IMPORT, or ALTER DATABASE
statements. However, only the subset allowed by both profile and
database settings will be used. For instance, if the database
specifies (READ ONLY, SHARED READ, PROTECTED READ) and the
profile specifies (READ ONLY, SHARED), the session will be
allowed the subset (READ ONLY, SHARED READ).
See the description in the CREATE PROFILE statement for all other
attributes supported by ALTER PROFILE.
9.4 – Examples
The following example changes a default transaction for an
existing profile.
SQL> ALTER PROFILE DECISION_SUPPORT
cont> DEFAULT TRANSACTION READ ONLY;
10 – ROLE
Allows you to change the role name or add a comment to a role.
10.1 – Environment
You can use the ALTER ROLE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
10.2 – Format
ALTER ROLE ----> <role-name> --+----------------------+------>
+--> alter-role-opts --+
alter-role-opts =
--+--> IDENTIFIED EXTERNALLY -------------+---->
+--> NOT IDENTIFIED --------------------+
+--> RENAME TO <new-role-name> ---------+
+--> COMMENT IS --+--> 'string' --+-----+
+--<--- / ------+
10.3 – Arguments
10.3.1 – COMMENT_IS
Adds a comment about the role. SQL displays the text of the
comment when it executes a SHOW ROLES statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
10.3.2 – IDENTIFIED_EXTERNALLY
Specifies whether SQL should inherit roles from the operating
system. If you specify one of these clauses, you must specify
the same clause as was specified when the role was created.
You cannot use the ALTER ROLE statement to change roles from
IDENTIFIED EXTERNALLY to NOT IDENTIFIED or from NOT IDENTIFIED to
IDENTIFIED EXTERNALLY.
The IDENTIFIED EXERNALLY clause indicates that SQL inherits the
roles defined by the facilities of the operating system, such as
OpenVMS rights identifiers.
The NOT IDENTIFIED clause indicates that SQL does not inherit any
roles defined by the facilities of the operating system; instead,
the role is private to the database.
10.3.3 – NOT_IDENTIFIED
Specifies whether SQL should inherit roles from the operating
system. If you specify one of these clauses, you must specify
the same clause as was specified when the role was created.
You cannot use the ALTER ROLE statement to change roles from
IDENTIFIED EXTERNALLY to NOT IDENTIFIED or from NOT IDENTIFIED to
IDENTIFIED EXTERNALLY.
The IDENTIFIED EXERNALLY clause indicates that SQL inherits the
roles defined by the facilities of the operating system, such as
OpenVMS rights identifiers.
The NOT IDENTIFIED clause indicates that SQL does not inherit any
roles defined by the facilities of the operating system; instead,
the role is private to the database. This is the default.
10.3.4 – RENAME_TO
Changes an existing role name to a new role name without
changing the privileges granted to the role. You might change
the name of a role that corresponds to a department name when the
department is renamed. For example, if the personnel department
is renamed human resources, you might change the role used by
that department from PERSONNEL to HUMAN_RESOURCES. The new role
name must not already exist in the database. The old role name is
removed from the database when the transaction is committed. The
old role name can be re-created and reused, if desired. If the
new role name is identified externally, then it must exist as an
operating system group or rights identifier.
See the RENAME statement for further discussion.
10.3.5 – role-name
The name of an existing role (such as one created with the CREATE
ROLE statement).
10.4 – Example
Example 1: Renaming a Role
SQL> -- Change the name of the role from WRITER to DOCUMENTATION.
SQL> -- Any privileges granted to the role WRITER are transferred to the role
SQL> -- DOCUMENTATION. The role WRITER is deleted from the database.
SQL> ALTER ROLE WRITER
cont> RENAME TO DOCUMENTATION;
SQL> SHOW ROLES;
Roles in database with filename mf_personnel.rdb
DOCUMENTATION
11 – SEQUENCE
Alters a sequence. A sequence is a database object from which
multiple users can generate unique integers. You can use
sequences to automatically generate primary key values.
11.1 – Environment
You can use the ALTER SEQUENCE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
11.2 – Format
ALTER SEQUENCE <sequence-name> -++--> <sequence-attributes> --------++->
|+--> RENAME TO <new-sequence-name> -+|
+--------------<----------------------+
sequence-attributes =
--+--> INCREMENT BY <numeric-value> ---------+->
+--> sequence-range -----------------------+
+--> CYCLE --------------------------------+
+--> NOCYCLE ------------------------------+
+--> CACHE <numeric-value> ----------------+
+--> NOCACHE ------------------------------+
+--> ORDER --------------------------------+
+--> NOORDER ------------------------------+
+--> RANDOMIZE ----------------------------+
+--> NORANDOMIZE --------------------------+
+--> RESTART WITH -------------------------+
+--> WAIT ---------------------------------+
+--> NOWAIT -------------------------------+
+--> DEFAULT WAIT -------------------------+
+--> COMMENT -+-------+--+-> '<string>' -+-+
+-> IS -+ +------ / <-----+
sequence-range =
-+-> MINVALUE -+-+--> <numeric-value> -+--+-->
+-> MAXVALUE -+ +--> TINYINT ----+ |
| +--> SMALLINT ---+ |
| +--> INTEGER ----+ |
| +--> BIGINT -----+ |
+-> NOMINVALUE --------------------------+
+-> NOMAXVALUE --------------------------+
11.3 – Arguments
11.3.1 – CACHE numeric-value
Syntax options:
CACHE numeric-value| NOCACHE
The CACHE clause specifies how many values of the sequence Oracle
Rdb should preallocate and keep in memory for faster access.
The mumeric value must be a value between 2 and 2147483647.
You cannot cache more values than will fit in a given cycle of
sequence numbers; thus, the maximum value allowed for the CACHE
clause must be less than the value resulting from the following
formula:
(MAXVALUE-MINVALUE)/ABS(INCREMENT)
You can alter the CACHE value if it is currently a value of 2
or higher. When you alter the CACHE value, existing users of the
sequence continue to use the original setting. You can use the
SET FLAGS 'SEQ_CACHE' statement to adjust the cache size for a
single process. See the See the SET_FLAGS statement for details.
If NOCACHE is currently enabled or the CACHE value is 1, you can
alter the CACHE value, but may have to wait until other users
of the sequence have released locks on it. (Note that CACHE 1 is
equivalent to NOCACHE.)
A cache for a given sequence is populated at the first request
for a number from that sequence, and whenever a value is
requested when the cache is empty. If a system failure occurs,
or when the cache is released any unfetched values will be
discarded. The maximum number of lost values is equal to the
current cache size. This may be the value specified by CACHE or
by the SET FLAGS SEQ_CACHE option.
The NOCACHE clause specifies that values will be allocated one
at a time. This will require more I/O to the Rdb root file than
using a CACHE value.
Note that even after you alter the CACHE value, users who
were using the sequence at the time you altered the CACHE will
continue to use the original setting.
11.3.2 – COMMENT_IS
Adds a comment about the sequence. SQL displays the text of the
comment when it executes a SHOW SEQUENCE statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
11.3.3 – CYCLE
Syntax options:
CYCLE | NOCYCLE
The CYCLE clause specifies that the sequence is to continue
generating values after reaching either the MINVALUE or MAXVALUE.
After an ascending sequence reaches the MAXVALUE, the sequence
starts again from its MINVALUE. After a descending sequence
reaches its MINVALUE, the sequence starts again at its MAXVALUE.
The NOCYCLE clause specifies that the sequence should not
continue generating values after reaching either its minimum
or maximum value. An error is generated if an attempt is made to
increment the sequence beyond its limits.
Note that even after you alter the CYCLE clause, those who
were using the sequence at the time you altered the CYCLE will
continue to use the original setting.
11.3.4 – INCREMENT BY numeric-value
Specifies the size of the increment and the direction (ascending
or descending) of the sequence. This numeric value must be in the
range -2147483648 through 2147483647, excluding 0. The absolute
value must be less than the difference between MAXVALUE and
MINVALUE. A negative value specifies a descending sequence; a
positive value specifies an ascending sequence. If the existing
value is positive, then the new value must also be positive.
Likewise, if the existing value is negative, then the new value
must also be negative. That is, you cannot change a sequence from
ascending to descending or from descending to ascending.
11.3.5 – MAXVALUE
Syntax options:
MAXVALUE numeric-value | NOMAXVALUE
The MAXVALUE clause specifies the maximum BIGINT value that the
sequence can generate. For an ascending sequence, the new maximum
value must be greater than or equal to the existing RDB$NEXT_
SEQUENCE_VALUE. For a descending sequence, the new maximum
value must be greater than or equal to the existing MAXVALUE.
This ensures that the MAXVALUE is not less than any currently
issued values. In addition, the numeric value must be between
-9223372036854775808 and 9223372036854775808. The MAXVALUE must
be greater than the value specified with the MINVALUE clause.
The NOMAXVALUE clause specifies that the maximum value for an
ascending sequence is 9223372036854775808 (minus the cache size),
and -1 for a descending sequence. The NOMAXVALUE clause is the
default.
11.3.6 – MAXVALUE integer-option
Syntax options:
MAXVALUE TINYINT
MAXVALUE SMALLINT
MAXVALUE INTEGER
MAXVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MAXVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the largest positive value that can be assigned
to this data type.
11.3.7 – MINVALUE
Syntax options:
MINVALUE numeric-value | NOMINVALUE
The MINVALUE clause specifies the minimum signed quadword
(BIGINT) value that the sequence can generate. For an ascending
sequence, the new minimum value must be less than or equal
to the existing MINVALUE. For a descending sequence, the
new minimum value must be less than or equal to the existing
RDB$NEXT_SEQUENCE_VALUE. This prevents the minimum value
from being greater than any currently issued values. In
addition, the numeric value must be equal to or greater than -
9223372036854775808. The MINVALUE must be less than the value
specified with the MAXVALUE clause. The NOMINVALUE clause
specifies that the minimum value for an ascending sequence is 1,
and -9223372036854775808 (plus the cache size) for a descending
sequence.
The NOMINVALUE clause is the default.
11.3.8 – MINVALUE integer-option
Syntax options:
MINVALUE TINYINT
MINVALUE SMALLINT
MINVALUE INTEGER
MINVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MINVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the smallest negative value that can be assigned
to this data type.
11.3.9 – ORDER
Syntax options:
ORDER | NOORDER
The ORDER clause specifies that sequence numbers are guaranteed
to be assigned in order for each requesting process, thus
maintaining a strict history of requests. The NOORDER clause
specifies that sequence numbers are not guaranteed to be
generated in order of request.
11.3.10 – RANDOMIZE
Syntax options:
RANDOMIZE | NORANDOMIZE
The RANDOMIZE clause specifies that the sequence numbers are to
be returned with a random value in the most significant bytes
of the BIGINT value. This allows unique values to be generated
that have a random distribution. When you specify the NORANDOMIZE
clause, sequence numbers are close in value to others created at
the same time.
The advantage of the RANDOMIZE clause is that updates to columns
of a stored index to which these values are written occur in
different locations in the index structure and so may improve
concurrent access for large indexes as leaf nodes in different
parts of the index can be updated independently. In contrast,
the sequence numbers generated when you specify the NORANDOMIZE
clause are likely to be close in numeric value to other sequence
values generated at the same time. This may cause index updates
to occur in the same or nearby index nodes, which may lead to
contention in one part of the sorted index.
The full range of values in the BIGINT value returned for the
sequence are used; therefore, the NOMAXVALUE and NOMINVALUE
clauses must be specified (or defaulted to) for the sequence
definition. The most significant bits of the BIGINT value
are set to a randomly generated positive value. A generated
distinct value is returned in the least significant 32 bits so
that uniqueness is guaranteed. If you also specify the CYCLE
clause, then only the least significant 32 bits are cycled. When
a query is performed on the column RDB$NEXT_SEQUENCE_VALUE in
the RDB$SEQUENCES table, only the generated value of the least
significant bits is returned, because the most significant bits
are not assigned until the NEXTVAL pseudocolumn is referenced.
If you specify RANDOMIZE, you cannot also specify ORDER,
MAXVALUE, or MINVALUE.
11.3.11 – RENAME_TO
Changes the name of the sequence being altered. See the RENAME
statement for further discussion. If the new name is the name of
a synonym then an error will be raised.
The new name must not exist as the name of an existing sequence,
synonym, table or view. You may not rename a system sequence.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
11.3.12 – RESTART_WITH
The RESTART WITH clause allows the database administrator to
reset the sequence to a specified value. The value must be
within the range of MINVALUE and MAXVALUE. This command requires
exclusive access to the sequence. Once the ALTER SEQUENCE
statement is successfully committed, applications that use the
sequence will start with a value based on the restarted value.
NOTE
The TRUNCATE TABLE statement issued for a table with
an IDENTITY column implicitly executes an ALTER
SEQUENCE...RESTART WITH process on the sequence, applying
the MINVALUE if it is an ascending sequence, or MAXVALUE if
it is a descending sequence.
11.3.13 – sequence-name
The name of the sequence whose definition you want to change.
11.3.14 – WAIT
Syntax options:
WAIT | NOWAIT | DEFAULT WAIT
Specifies which wait state is used when a reference to NEXTVAL
is used. A reference to NEXTVAL for a sequence may require
synchronization with other users of the sequence. When you
specify DEFAULT WAIT the wait state (WAIT or NOWAIT) of the
current transaction is used. This may mean that no waiting is
performed during a NOWAIT transaction.
If you specify WAIT (the default) for the sequence, then
regardless of the wait state set for the current transaction, all
synchronization waits for the next value. This is the recommended
setting if the application uses NOWAIT transactions. The current
WAIT timeout interval defined for the transaction or database is
used.
If you specify NOWAIT for the sequence, then regardless of the
current transaction setting, all synchronization will not wait
for the next value.
Note that even after you alter the WAIT value, users who were
using the sequence at the time you altered WAIT will continue to
use the original setting.
11.4 – Examples
Example 1: Altering a sequence
SQL> -- Show current sequence definition:
SQL> --
SQL> SHOW SEQUENCE EMPIDS
EMPIDS
Sequence Id: 1
Initial Value: 1
Minimum Value: 1
Maximum Value: 9223372036854775787
Next Sequence Value: 1
Increment by: 1
Cache Size: 20
No Order
No Cycle
No Randomize
SQL> --
SQL> -- Alter the sequence.
SQL> --
SQL> ALTER SEQUENCE EMPIDS
cont> MINVALUE 0
cont> MAXVALUE 2000
cont> CACHE 30
cont> ORDER
cont> CYCLE;
SQL> --
SQL> -- Show new definition.
SQL> --
SQL> SHOW SEQUENCE EMPIDS
EMPIDS
Sequence Id: 1
Initial Value: 1
Minimum Value: (none)
Maximum Value: 2000
Next Sequence Value: 1
Increment by: 1
Cache Size: 30
Order
Cycle
No Randomize
Example 2: Reset the sequence to a specified value
SQL> show sequence NEW_EMPLOYEE_ID
NEW_EMPLOYEE_ID
Sequence Id: 1
Initial Value: 472
.
.
.
SQL>
SQL> alter sequence NEW_EMPLOYEE_ID
cont> restart with 500;
SQL>
SQL> show sequence NEW_EMPLOYEE_ID
NEW_EMPLOYEE_ID
Sequence Id: 1
Initial Value: 500
.
.
.
SQL>
12 – STORAGE_MAP
Changes an existing storage map. A storage map controls which
rows of a table are stored in which storage areas in a multifile
database.
In addition to changing storage maps, the ALTER STORAGE MAP
statement has options that change the following:
o Which index the database system uses when inserting rows in
the table
o Whether or not the rows of the table are stored in a
compressed format
o Whether or not the data is reorganized
o Whether partitioning keys can be modified
o Whether logging the transaction containing the ALTER statement
is journaled to the RUJ and AIJ files.
12.1 – Environment
You can use the ALTER STORAGE MAP statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
12.2 – Format
ALTER STORAGE MAP <map-name> -+
+-----------------------------+
++-+-++-> ENABLE --+-> COMPRESSION ---------------------------------+-+-+->
| | |+-> DISABLE -+ | | |
| | +-> COMPILE ---------------------------------------------------+ | |
| | +-> NO PLACEMENT VIA INDEX ------------------------------------+ | |
| | +-> PLACEMENT VIA INDEX <index-name> --------------------------+ | |
| | +-> RENAME PARTITION <partition-name> TO <new-partition-name> -+ | |
| | +-> REORGANIZE ---+-----------+--------------------------------+ | |
| | | +-> AREAS --+ | | |
| | | +-> PAGES --+ | | |
| | +-> NO REORGANIZE ---------------------------------------------+ | |
| | +-> store-clause ----------------------------------------------+ | |
| | +-> PARTITIONING IS UPDATABLE ---------------------------------+ | |
| | +-> PARTITIONING IS NOT UPDATABLE -----------------------------+ | |
| | +-> threshold-clause ------------------------------------------+ | |
| | +-> LOGGING ---------------------------------------------------+ | |
| | +-> NOLOGGING -------------------------------------------------+ | |
| | +-> COMMENT IS -+-> 'string' --+-------------------------------+ | |
| | +---- / <-----+ | |
| +------------------- <---------------------------------------------+ |
+-----------> store-list-clause ---------------------------------------+
store-clause =
--> STORE ---+-> IN area-spec ------+->
+-> across-clause -----+
+-> using-clause ------+
area-spec =
--> <area-name> -+---------------------------------------------------+->
+-> ( -++-> threshold-clause ---------------++-> ) -+
|+-> LOGGING ------------------------+|
|+-> NOLOGGING ----------------------+|
|+-> PARTITION <name> ---------------+|
|+-> COMMENT IS -+--> 'string' ---+--+|
| +------- / <-----+ |
+---------------- , -----------------+
across-clause =
---> RANDOMLY ACROSS ---+
+----------------------+
+-> ( -+> area-spec --+--> ) -->
+----- , <-----+
using-clause =
---> USING ---> ( -+-> <column-name> -+-> ) ------------------+
+-------- , <------+ |
+--------------------------------------------------------------+
++-> IN area-spec -> WITH LIMIT OF --> ( +-> <literal> -+> ) +-+
| +------ , <----+ | |
+--------------------------<--------------------------------+ |
+--------------------------<----------------------------------+
++------------------------------------------------+------------->
+> OTHERWISE IN area-spec -----------------------+
threshold-clause =
--+-> THRESHOLD -+-> IS -+-> ( --> <val1> --> ) ---------+->
| +-> OF -+ |
| |
+-> THRESHOLDS -+-> ARE -+-----------+ |
+-> OF -+ | |
+----------------------------------+ |
+-> ( --> <val1> -+---------------------------+-> ) -+
+-> , <val2> -+-------------+
+-> , <val3> -+
store-lists-clause =
---> STORE LISTS ---+
+------------------+
+-+-> IN -+-> area-spec ---------------+---+----------->
| +-> ( +> area-spec -> ) ----++ |
| +------ , <-----------+ |
| +---------------------------------------+
| +-+------------------------>-----------------------+
| +-> FOR -> ( -+-> <table-name> ---------+-> ) -+ |
| +-> <table-name.col-name> + | |
| +-------- , <-------------+ | |
| +--------------------------------------+ |
| +---+------------>----------+------------+
| +-> FILL RANDOMLY -----+ |
| +-> FILL SEQUENTIALLY --+ |
+----------------------------<-----------------------+
12.3 – Arguments
12.3.1 – AREAS
Specifies that the target of the data reorganization is storage
areas. All rows are checked to see if they are in the correct
storage area and if some are not, they are moved. This is the
default.
12.3.2 – COMMENT_IS
Adds or alters a comment about the storage map. SQL displays
the text of the comment when it executes a SHOW STORAGE MAPS
statement. Enclose the comment in single quotation marks (') and
separate multiple lines in a comment with a slash mark (/).
12.3.3 – COMPILE
Creates a SQL mapping routine that matches the WITH LIMIT OF
clause for the storage map. The routine is automatically created
in the system module RDB$STORAGE_MAPS (use SHOW SYSTEM MODULES to
view). The storage map name is used to name the mapping routine
(use SHOW SYSTEM FUNCTIONS to view).
NOTE
If a routine already exists with the same name as the
storage map, then the mapping routine will not be created.
If the storage map includes a STORE COLUMNS clause, that is,
a vertically partitioned map, then several routines will be
created and uniquely named by adding the vertical partition
number as a suffix.
The mapping routine returns the following values:
o Zero (0) if the storage map is defined as RANDOMLY ACROSS.
This routine is just a descriptive place holder.
o Positive value representing the storage map number (the
same value as stored in RDB$ORDINAL_POSITION column of the
RDB$STORAGE_MAP_AREAS table). These values can be used with
the PARTITION clause of the SET TRANSACTION...RESERVING clause
to reserve a specific partition prior to inserting the row.
o A value of -1 if the storage map has no OTHERWISE clause. This
indicates that the row cannot be inserted because it does not
match any of the WITH LIMIT OF clauses.
12.3.4 – COMPRESSION
Syntax options:
ENABLE COMPRESSION | DISABLE COMPRESSION
Changes whether the rows for the table are compressed or
uncompressed when stored. Enabling compression conserves disk
space, but it incurs additional CPU overhead for inserting and
retrieving compressed rows.
Changing the COMPRESSION clause causes the database system
to read all the rows in the table and write them back to the
table in the changed format. If compression is enabled and you
subsequently disable it, records may become fragmented because
the space allowed for the record is no longer large enough.
12.3.5 – FILL
Syntax options:
FILL RANDOMLY | FILL SEQUENTIALLY
Specifies whether to fill the area set randomly or sequentially.
Specifying FILL RANDOMLY or FILL SEQUENTIALLY requires a FOR
clause. When a storage area is filled, it is removed from the
list of available areas. Oracle Rdb does not attempt to store
any more lists in that area during the current database attach.
Instead, Oracle Rdb starts filling the next specified area.
When a set of areas is filled sequentially, Oracle Rdb stores
lists in the first specified area until that area is filled.
If the set of areas is filled randomly, lists are stored across
multiple areas. This is the default. Random filling benefits from
the I/O distribution across the storage areas.
The keywords FILL RANDOMLY and FILL SEQUENTIALLY can only be
applied to areas contained within an area list.
12.3.6 – FOR (table name)
Specifies the table or tables to which this storage map applies.
The named table must already be defined. If you want to store
lists of more than one table in the storage area, separate the
names of the tables with commas. For each area, you can specify
one FOR clause and a , do not use this statement unless all areas
specified list of table names.
12.3.7 – FOR (table name.col name)
Specifies the name of the table and column containing the list
to which this storage map applies. Separate the table name and
the column name with a period (.). The named table and column
must already be defined. If you want to store multiple lists
in the storage area, separate the table name and column name
combinations with commas. For each area, you can specify one FOR
clause and a list of column names.
12.3.8 – LOGGING
The LOGGING clause specifies that the ALTER STORAGE MAP statement
should be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij). The LOGGING clause is the
default.
12.3.9 – NOLOGGING
The NOLOGGING clause specifies that the ALTER STORAGE MAP
statement should not be logged in the recovery-unit journal file
(.ruj) and after-image journal file (.aij).
12.3.10 – NO_PLACEMENT_VIA_INDEX
Negates the PLACEMENT VIA INDEX clause so that subsequent
records stored are not stored by means of the index named in
the PLACEMENT VIA INDEX clause. If you specify the ALTER STORAGE
MAP statement without the PLACEMENT VIA INDEX argument or the
NO PLACEMENT VIA INDEX argument, the statement executes as if
the clause specified on the CREATE STORAGE MAP statement or last
ALTER STORAGE MAP statement was used.
12.3.11 – NO_REORGANIZE
Disables the reorganize action for PARTITIONING IS NOT UPDATABLE.
12.3.12 – PAGES
Specifies that the target of the data reorganization is database
pages. All rows are checked to determine whether they are in the
correct storage area and if some are not, they are moved. Then,
all rows are checked if any should be moved within each storage
area, and these rows are moved if there is space on or closer to
the new target page.
12.3.13 – PARTITION name
Names the partition. The name can be a delimited identifier
if the dialect is set to SQL99. Partition names must be unique
within the storage map. If you do not specify this clause, Oracle
Rdb generates a default name for the partition.
12.3.14 – PARTITIONING_IS_UPDATABLE
Specifies that the partitioning key can be modified. The
partitioning key is the column or list of columns specified in
the STORE USING clause.
See the Oracle Rdb Guide to Database Design and Definition for
more information regarding partitioning.
12.3.15 – PLACEMENT_VIA_INDEX
See the CREATE STORAGE_MAP statement for details of the PLACEMENT
VIA INDEX argument.
12.3.16 – RENAME_PARTITION
Syntax options:
RENAME PARTITION partition-name TO new-partition-name
Specifies a new name for an existing storage map partition.
12.3.17 – REORGANIZE
Causes new rows and rows previously stored in specified tables
to be moved according to the partitions specified in the STORE
clause of the ALTER STORAGE MAP statement. The REORGANIZE clause
works for one or more areas in the storage maps.
For details of how rows are moved or not moved among storage
areas depending on whether or not the REORGANIZE argument is
specified, see the Oracle Rdb Guide to Database Design and
Definition.
12.3.18 – STORAGE MAP map name
Specifies the name of the storage map you want to alter.
12.3.19 – store-clause
A new storage map definition that replaces the existing storage
map. The store-clause allows you to specify which storage area
files will be used to store rows from the table. Note that:
o All rows of a table can be associated with a single storage
area.
o Rows of a table can be distributed among several storage
areas.
o Rows of a table can be systematically distributed
(horizontally partitioned) among several storage areas by
specifying upper limits on the values for a column in a
particular storage area.
The store-clause specifies only how you want to associate rows
with areas and not the manner in which rows are assigned to pages
within an area.
See the CREATE STORAGE_MAP statement for a description of the
syntax for the store-clause. However, the effect of the clause
in the ALTER STORAGE MAP statement depends on how you change the
existing storage map.
12.3.20 – STORE LISTS IN area name
Directs the database system to store the lists from tables in a
specified storage area. You can store lists from different tables
in the same area. You can create only one storage map for lists
within each database.
You must specify the default storage area for lists. This should
be the LIST STORAGE AREA specified on CREATE DATABASE, or if
none, the DEFAULT STORAGE AREA, or if none, then it will be
RDB$SYSTEM.
For more information, see the CREATE STORAGE_MAP statement.
12.3.21 – threshold-clause
Specifies SPAM thresholds for logical areas with uniform format
pages.
When you specify the THRESHOLD clause without enclosing it in
parentheses, you are specifying the default threshold values
for all areas specified in the ALTER STORAGE MAP statement.
You cannot alter the thresholds for any storage areas which are
part of the storage map. Only specify this clause for storage
areas being added to the storage area by the ALTER STORAGE MAP
statement.
To specify threshold values for a particular storage area,
specify the clause as part of the STORE clause and enclose the
THRESHOLD clause in parentheses. You can only specify threshold
values for new areas, not existing ones.
For examples of specifying the THRESHOLD clause, see the Oracle
Rdb Guide to Database Design and Definition. See the CREATE
STORAGE_MAP statement for a description of the THRESHOLDS clause.
12.4 – Examples
Example 1: Reorganizing storage area data using the ALTER STORAGE
MAP statement
The following example defines a new storage area, EMPIDS_MID2, to
handle the employee ID numbers from 601 to 900 and to reorganize
the data from an existing storage area, EMPIDS_OVER. The current
data that is stored for employees with employee ID numbers from
601 to 900 is moved according to the new limits. Because no AREA
or PAGE option is specified, the default method of reorganization
is by storage areas.
SQL> ALTER DATABASE FILENAME mf_personnel ADD STORAGE AREA
cont> EMPIDS_MID2 PAGE FORMAT IS MIXED;
SQL> ATTACH 'FILENAME mf_personneL';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> STORE USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW WITH LIMIT OF ('00300')
cont> IN EMPIDS_MID WITH LIMIT OF ('00600')
cont> IN EMPIDS_MID2 WITH LIMIT OF ('00900')
cont> OTHERWISE IN EMPIDS_OVER
cont> REORGANIZE;
Example 2: Enabling compression with an ALTER STORAGE MAP
statement
The following example defines a new storage map, UNIFORM1_MAP,
and specifies thresholds for the logical area in the UNIFORM1
storage area. The ALTER STORAGE MAP statement is used to enable
row compression.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM1;
SQL> ATTACH 'FILENAME mf_personnel';
SQL> CREATE TABLE TEST (COL1 REAL);
SQL> CREATE STORAGE MAP UNIFORM1_MAP FOR TEST
cont> STORE IN UNIFORM1
cont> (THRESHOLDS ARE (80,90,95));
SQL> ALTER STORAGE MAP UNIFORM1_MAP
cont> STORE IN UNIFORM1
cont> ENABLE COMPRESSION;
Example 3: Changing an overflow partition to a WITH LIMIT OF
partition
To change the overflow partition to a partition defined with the
WITH LIMIT OF clause, you must use the REORGANIZE clause if you
want existing data that is stored in the overflow partition moved
to the appropriate storage area. For example, suppose the JOB_
HISTORY table contains a row with an EMPLOYEE_ID of 10001 and the
JH_MAP storage map is defined, as shown in the following example:
SQL> SHOW STORAGE MAP JH_MAP
JH_MAP
For Table: JOB_HISTORY
Compression is: ENABLED
Store clause: STORE USING (EMPLOYEE_ID)
IN PERSONNEL_1 WITH LIMIT OF ('00399')
IN PERSONNEL_2 WITH LIMIT OF ('00699')
OTHERWISE IN PERSONNEL_3
SQL>
If you want to change the PERSONNEL_3 storage area from an
overflow partition to a partition with a limit of 10,000 and
add the partition PERSONNEL_4, you must use the REORGANIZE clause
to ensure that Oracle Rdb moves existing rows to the new storage
area. The following example shows the ALTER STORAGE MAP statement
that accomplishes this change:
SQL> ALTER STORAGE MAP JH_MAP
cont> STORE USING (EMPLOYEE_ID)
cont> IN PERSONNEL_1 WITH LIMIT OF ('00399')
cont> IN PERSONNEL_2 WITH LIMIT OF ('00699')
cont> IN PERSONNEL_3 WITH LIMIT OF ('10000')
cont> IN PERSONNEL_4 WITH LIMIT OF ('10399')
cont> REORGANIZE;
SQL>
Example 4: Disabling Logging to the RUJ and AIJ files
SQL> ATTACH'FILENAME MF_PERSONNEL.RDB';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> STORE
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW
cont> WITH LIMIT OF ('00200')
cont> IN JOBS
cont> (NOLOGGING)
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN EMPIDS_OVER;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-DATACMIT, unjournaled changes made; database may not be recoverable
Example 5: Disabled Area Scan for PARTITIONING IS NOT UPDATABLE
When a storage map is altered to be NOT UPDATABLE a REORGANIZE
scan is implicitly executed to check that all rows are in the
correct storage area according to the WITH LIMIT OF clauses in
the storage map. This scan can be time consuming, and an informed
database administrator may know that the data already conforms
fully to the storage map. The NO REORGANIZE clause is used
in the following example to avoid the extra I/O. The database
administrator must understand that use of this clause might lead
to incorrect query results (for sequential scans) if the storage
map does not reflect the correct row mapping.
SQL> SET FLAGS 'stomap_stats';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> PARTITIONING IS NOT UPDATABLE
cont> NO REORGANIZE
cont> STORE
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW
cont> WITH LIMIT OF ('00200')
cont> IN EMPIDS_MID
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN EMPIDS_OVER;
~As: starting map restructure...
~As: REORGANIZE needed to preserve strict partitioning
~As: NO REORGANIZE was used to override scan
~As: reads: async 0 synch 21, writes: async 7 synch 3
SQL>
SQL> SHOW STORAGE MAPS EMPLOYEES_MAP
EMPLOYEES_MAP
For Table: EMPLOYEES
Placement Via Index: EMPLOYEES_HASH
Partitioning is: NOT UPDATABLE
Strict partitioning was not validated for this table
Comment: employees partitioned by "00200" "00400"
Store clause: STORE
using (EMPLOYEE_ID)
in EMPIDS_LOW
with limit of ('00200')
in EMPIDS_MID
with limit of ('00400')
otherwise in EMPIDS_OVER
Compression is: ENABLED
SQL>
A subsequent ALTER STORAGE MAP . . . REORGANIZE statement will
validate the partitioning, as shown in the following example:
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> PARTITIONING IS NOT UPDATABLE
cont> REORGANIZE;
~As: starting map restructure...
~As: starting REORGANIZE...
~As: reorganize AREAS...
~As: processing rows from area 69
~As: processing rows from area 70
~As: processing rows from area 71
~As: reads: async 408 synch 22, writes: async 3 synch 0
SQL>
Example 6: Redefining a SQL routine that matches the WITH LIMIT
OF clause for the storage map
The ALTER STORAGE MAP command removes any old mapping routine
and redefines it when either the STORE clause is used, or if the
COMPILE option is used.
SQL> alter storage map EMPLOYEES_MAP
cont> store
cont> using (EMPLOYEE_ID)
cont> in EMPIDS_LOW
cont> with limit of ('00200')
cont> in EMPIDS_MID
cont> with limit of ('00400')
cont> in EMPIDS_OVER
cont> with limit of ('00800');
SQL>
SQL> show system function (source) EMPLOYEES_MAP;
Information for function EMPLOYEES_MAP
Source:
return
case
when (:EMPLOYEE_ID <= '00200') then 1
when (:EMPLOYEE_ID <= '00400') then 2
when (:EMPLOYEE_ID <= '00800') then 3
else -1
end case;
13 – SYNONYM
Alters a synonym definition.
13.1 – Environment
You can use the ALTER SYNONYM statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
13.2 – Format
ALTER SYNONYM <synonym-name> -+----------------------+-+
+-> FOR <object-name> -+ |
+------------------------------------------------------+
++-------------------------------+--------------------->
+-> COMMENT IS -+-> 'string' -+-+
+----- / <----+
13.3 – Arguments
13.3.1 – COMMENT IS string
This clause can be used to add several lines of comment to the
synonym object. The SHOW SYNONYM statement displays the comment.
This clause is equivalent to the COMMENT ON SYNONYM statement.
13.3.2 – FOR object-name
You may change the synonym to reference a different database
object; however, it must be of the same type. Oracle Rdb assumes
that the object has the same or similar characteristics as
the referenced object. The referenced object must exist in the
database.
13.3.3 – synonym-name
The name of an existing synonym you want to alter.
13.4 – Examples
Example 1: Adding a Comment
SQL> ALTER SYNONYM CASH
cont> COMMENT IS 'use a different name to avoid confusion with'
cont> / 'the domain MONEY';
Example 2: Using Multiple Synonyms and Changing the Referenced
Table Using ALTER
The following example uses a synonym to reference a table. Later
an empty version of the table can be created and the synonym
altered to reference this new table. Although similar to using a
view definition, the use of synonyms avoid the usage locking of
a view. That is, to drop and create a new view requires that no
other user references that view, however, the alter synonym does
not require exclusive access to the table.
SQL> CREATE TABLE t_employees_0001 (...);
SQL> CREATE SYNONYM employees FOR t_employees_0001;
SQL> CREATE SYNONYM emps FOR employees;
SQL> CREATE TABLE t_employees_0002 LIKE t_employees_0001;
SQL> ALTER SYNONYM employees FOR t_employees_0002;
14 – TABLE
Changes an existing table definition. You can:
o Add columns
o Add constraints to tables or columns
o Modify columns
o Modify character sets
o Modify data types
o Delete columns
o Delete constraints
The ALTER TABLE statement can also add or delete table-specific
constraints. You can display the names for all constraints
currently associated with a table by using the SHOW TABLE
statement. Any number of constraints can be deleted and
declared at both the table and column levels. See also the ALTER
CONSTRAINT statement and the DROP CONSTRAINT. statement.
When you execute this statement, SQL modifies the named column
definitions in the table. All of the columns that you do not
mention remain unchanged. SQL defines new versions of columns
before defining constraints. Then, SQL defines and evaluates
constraints before storing them. Therefore, if columns and
constraints are defined in the same table definition, constraints
always apply to the latest version of a column.
When you change a table definition, other users see the revised
definition only when they connect to the database after you
commit the changes.
14.1 – Environment
You can use the ALTER TABLE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
14.2 – Format
ALTER TABLE --> <table-name> -+
+-----------------------------+
+-+-+-> ADD -+-> COLUMN col-definition ----------------+-+->
| | +-> CONSTRAINT table-constraint ----------+ |
| | +-> ( -+-> col-definition -+-> ) ---------+ |
| | +------- , <--------+ | |
| +-> ALTER COLUMN --> alter-col-definition ---------+ |
| +-> MODIFY -> ( -+-> alter-col-definition --+-> ) -+ |
| | +------------ , <----------+ | |
| +-> DROP -+-> COLUMN <column-name> ----------------+ |
| | +-> CONSTRAINT <constraint-name> --------+ |
| +-> enable-clause ---------------------------------+ |
| +-> disable-clause --------------------------------+ |
| +-> RENAME TO <new-table-name> --------------------+ |
| +-> COMMENT IS -+> '<quoted-string>' -+------------+ |
| +----- / -------------+ |
+-------------------------- <--------------------------+
col-definition =
--> <column-name> --+
+---------------+
+-> add-column-type -+---------------------------+-+-+
| +-> DEFAULT value-expr ----+ | |
| +-> column-identity ------+ | |
+---> COMPUTED BY value-expr ----------------------+ |
+----------------------------<-----------------------+
+--+-------------------++--------------------+-------+
+-> col-constraint -++-> position-clause -+ |
+----------------------------<-----------------------+
+--+------------------------+-------------------------->
+-> sql-and-dtr-clause --+
add-column-type =
--+-> data-type -----------------------------------+-->
+-> <domain-name> -------------------------------+
+-> <references-clause> -------------------------+
+-> AUTOMATIC --+-----------+--> AS value-expr --+
+-> INSERT -+
+-> UPDATE -+
column-identity =
--> IDENTITY --+--------------------------------------------------+-->
+- ( <start-with> --+----------------------+-> ) --+
+-> , <increment-by> --+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
col-constraint=
----+--------------------------------+-+
+> CONSTRAINT <constraint-name> -+ |
+---------------<--------------------+
+-> PRIMARY KEY -----------------+
+-> UNIQUE ----------------------+
+-> NOT NULL --------------------+
+-> NULL ------------------------+
+-> CHECK (predicate) -----------+
+-> references-clause -----------+
+--------------->----------------+
+---------------<----------------+
+----+----------------------------+-->
+--> constraint-attributes --+
references-clause =
--> REFERENCES <referenced-table-name> -+
+----------------------------------+
+-+-----------------------------------------+->
+-> ( -+> <referenced-column-name> +-> ) -+
+----------- , <------------+
constraint-attributes =
-+-> DEFERRABLE -------------+------------------------------+-+->
| +-> INITIALLY +-> IMMEDIATE --++ |
| +-> DEFERRED ---+ |
+-> NOT DEFERRABLE ---------+-------------------------+------+
| +-> INITIALLY IMMEDIATE --+ |
+-> INITIALLY IMMEDIATE ----+-------------------+------------+
| +-> DEFERRABLE -----+ |
| +-> NOT DEFERRABLE -+ |
+-> INITIALLY DEFERRED -----+-------------------+------------+
+-> DEFERRABLE -----+
position-clause =
-+-> AFTER --+--> COLUMN <column-name> ---->
+-> BEFORE -+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +-------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> -------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> -+
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS literal ----+
+-> DATATRIEVE -+
table-constraint =
---+-----------------------------------+----+
+-> CONSTRAINT <constraint-name> ---+ |
+------------------------------------------+
+--> table-constraint-clause --------------+
+------------------------------------------+
+---+---------------------------+------------->
+-> constraint-attributes --+
table-constraint-clause =
-+----------------------------------------------+-->
+-> PRIMARY KEY -> ( -+> <column-name> +-> ) --+
| +------- , <-----+ |
+-> UNIQUE -> ( -+> <column-name> +-> ) -------+
| +------- , <-----+ |
+-> CHECK (predicate) -------------------------+
+-> FOREIGN KEY -> ( -+> <column-name> +-> ) + |
+------- , <-----+ | |
+--------------------<---------------------+ |
+-> references-clause ---------->------------+
alter-col-definition =
-> <column-name> +----------------++-----------------------------++
+-> alt-col-type ++-> SET DEFAULT value-expr ---+|
+-> DEFAULT value-expr -------+|
+-> DROP DEFAULT value-expr --+|
+--------------------------- <------------------------------------+
+-+----------------------+---------+--------------------+--------+
++-> col-constraint -+-+ +-> position-clause -+ |
+-------- <---------+ |
+-------------------- <------------------------------------------+
+-+-+-----------------------------> ------------------+-+---------->
| +--> sql-and-dtr-clause --------------------------+ |
| +--> NO QUERY HEADER -----------------------------+ |
| +--> NO EDIT STRING ------------------------------+ |
| +--> NO QUERY NAME ----+-> FOR --+-> DTR --------++ |
| +--> NO DEFAULT VALUE -+ +-> DATATRIEVE -+ |
+------------------------- <--------------------------+
alt-col-type =
-+-> data-type -------------------+-+---------------------+-+-->
+-> <domain-name> --------------+ +-> column-identity --+ |
+-> COMPUTED BY value-expr --------------------------------+
+-> AUTOMATIC -+------------+----> AS value-expr ----------+
+-> INSERT --+
+-> UPDATE --+
enable-clause =
--> ENABLE ---+
+-------<-----+
+-+-> ALL TRIGGERS -----------------------------------------------+->
+-> TRIGGER <trigger-name> ------------------------------------++
+-+---------------+-+-> ALL CONSTRAINTS ----------------------++
+-> VALIDATE ---+ +-> CONSTRAINT <constraint-name> ---------+
+-> NOVALIDATE -+ +-> PRIMARY KEY --------------------------+
+-> UNIQUE ->( -+-> <column-name> -+-> ) -+
+------- , <-------+
disable-clause =
--> DISABLE ---+-> ALL TRIGGERS -------------------------+-->
+-> TRIGGER <trigger-name> ---------------+
+-> ALL CONSTRAINTS ----------------------+
+-> CONSTRAINT <constraint-name> ---------+
+-> PRIMARY KEY --------------------------+
+-> UNIQUE ->( -+-> <column-name> -+-> ) -+
+------- , <-------+
14.3 – Arguments
14.3.1 – ADD_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
14.3.2 – ADD_COLUMN
Creates an additional column in the table. SQL adds the column
after all existing columns in the table unless the position-
clause relocates the new column. The column definition specifies
a data type or domain name, optional default value, optional
column constraints, and optional formatting clauses.
The COLUMN keyword is optional.
14.3.3 – ADD_CONSTRAINT
Adds a table constraint definition. The four types of table
constraints are PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
14.3.4 – AFTER_COLUMN
Changes the normal field ordering of columns to make the
displayed column ordering more readable. Note that this does
not change the on-disk layout of the columns. By default, when
neither of these clauses is specified, columns are positioned at
the end of the table specified with the ALTER TABLE statement.
14.3.5 – ALTER COLUMN alter col definition
Modifies the column specified by the column name. The COLUMN
keyword is optional.
You can modify some elements of a column definition but not
others.
You cannot change an existing column constraint. However, you can
delete the existing constraint and add a new column constraint
using the alter-col-definition clause to achieve the same result.
14.3.6 – AUTOMATIC
Syntax options:
AUTOMATIC AS value-expr | AUTOMATIC INSERT AS value-expr |
AUTOMATIC UPDATE AS value-expr
These AUTOMATIC column clauses allow you to store special
information when data is inserted into a row or a row is updated.
For example, you can log application-specific information to
audit activity or provide essential values, such as time stamps
or unique identifiers for the data.
The assignment of values to these types of columns is managed by
Oracle Rdb. The AUTOMATIC INSERT clause can be used to provide
a complex default for the column when the row is inserted; it
cannot be changed by an UPDATE statement. The AUTOMATIC UPDATE
clause can be used to provide an updated value during an UPDATE
statement. The AUTOMATIC clause is the default and specifies that
the value expression should be applied during both INSERT and
UPDATE statements. The column type is derived from the AS value-
expr; using CAST allows a specific data type to be specified.
However, this is not required and is rarely necessary.
You can define an AUTOMATIC INSERT column to automatically
receive data during an insert operation. The data is stored like
any other column, but the column is read-only. Because AUTOMATIC
columns are treated as read-only columns, they cannot appear in
the column list for an insert operation nor be modified by an
update operation. AUTOMATIC UPDATE columns can have an associated
default value that will be used when the row is inserted.
Suppose that you want to store the current time stamp of a
transaction and supply a unique numeric value for an order
number. In addition, when the row is updated (the order is
altered), you want a new time stamp to be written to the LAST_
UPDATED column. You could write an application to supply this
information, but you could not guarantee the desired behavior.
For instance, a user with access to the table might update
the table with interactive SQL and forget to enter a new time
stamp to the LAST_UPDATED column. If you use an AUTOMATIC column
instead, it can be defined so that columns automatically receive
data during an insert operation. The data is stored like any
other column, but the column is read-only.
14.3.7 – BEFORE_COLUMN
Changes the normal field ordering of columns to make the
displayed column ordering more readable. Note that this does
not change the on-disk layout of the columns. By default, when
neither of these clauses is specified, columns are positioned at
the end of the table specified with the ALTER TABLE statement.
14.3.8 – char-data-types
A valid SQL character data type. See the Data_Types HELP topic
for more information on character data types.
14.3.9 – CHECK (predicate)
Specifies a predicate that column values inserted into the
table must satisfy. See Predicates for details on specifying
predicates.
Predicates in CHECK column constraints can only refer directly to
the column with which they are associated.
14.3.10 – col-constraint
Specifies a constraint that column values inserted into the table
must satisfy. You can specify more than one column constraint.
For example:
SQL> ALTER TABLE EMPLOYEE
cont> ADD ID_NUMBER INT NOT NULL UNIQUE;
You can name each constraint. For example:
SQL> ALTER TABLE EMPLOYEE
cont> ADD ID_NUMBER INT
cont> CONSTRAINT A NOT NULL
cont> CONSTRAINT B UNIQUE;
14.3.11 – column-name
The name of the column being added or modified.
14.3.12 – COMPUTED_BY
Specifies that the value of this column is calculated from values
in other columns and constant expressions. See the CREATE TABLE
statement for more information.
14.3.13 – constraint-attributes
Although the constraint attribute syntax provides 11 permutations
as required by the SQL99 standard, they equate to the following
three options:
o INITIALLY IMMEDIATE NOT DEFERRABLE
Specifies that evaluation of the constraint must take place
when the INSERT, DELETE, or UPDATE statement executes. If
you are using the SQL92, SQL99, MIA, ORACLE LEVEL1 or ORACLE
LEVEL2 dialect, this is the default.
This clause is the same as the NOT DEFERRABLE option provided
in previous releases of Oracle Rdb.
o INITIALLY DEFERRED DEFERRABLE
Specifies that evaluation of the constraint can take place
at any later time. Unless otherwise specified, evaluation of
the constraint takes place as the COMMIT statement executes.
You can use the SET ALL CONSTRAINTS statement to have all
constraints evaluated earlier. See the SET_ALL_CONSTRAINTS
Help topic for more information. If you are using the default
SQLV40 dialect, this is the default constraint attribute.
When using this default dialect, Oracle Rdb displays a
deprecated feature message for all constraints defined without
specification of one of the constraint attributes.
This clause is the same as the DEFERRABLE option provided in
previous releases of Oracle Rdb.
o INITIALLY IMMEDIATE DEFERRABLE
Specifies that evaluation of the constraint be deferred (using
the SET CONSTRAINT ALL statement or the SET TRANSACTION
statement with the EVALUATING clause) but by default it
is evaluated after the INSERT, DELETE, or UPDATE statement
executes.
14.3.14 – CONSTRAINT constraint name
The CONSTRAINT clause specifies a name for the table constraint.
The name is used for a variety of purposes:
o The INTEG_FAIL error message specifies the name when an
INSERT, UPDATE, or DELETE statement violates the constraint.
o The ALTER CONSTRAINT, DROP CONSTRAINT and ALTER TABLE DROP
CONSTRAINT statements specify the constraint name.
o The SHOW TABLE statements display the names of constraints.
o The EVALUATING clause of the SET and the DECLARE TRANSACTION
statements specifies constraint names.
The CONSTRAINT clause is optional. If you omit the constraint
name, SQL creates a name. However, Oracle Rdb recommends that
you always name column and table constraints. The constraint
names generated by SQL may be obscure. If you supply a constraint
name with the CONSTRAINT clause, the name must be unique in the
schema.
14.3.15 – data-type
A valid SQL data type. Specifying an explicit data type to
associate with a column is an alternative to specifying a domain
name.
See the Data_Types HELP topic for more information on data types.
Using the ALTER clause to change the data type of a column
(directly or indirectly by specifying a domain) requires caution:
o If you change a column to a character data type with a larger
capacity, or increase the scale factor for a column, or change
the character set, you may have to modify source programs that
refer to the column and precompile them again.
o If you change a column to a smaller capacity numeric data
type then overflow errors may result at run time as Oracle Rdb
attemps to convert the large value to the new data type.
o If you change a column to a data type with a smaller capacity,
SQL truncates values already stored in the database that
exceed the capacity of the new data type, but only when it
retrieves those values. (The values are not truncated in
the database, however, until they are updated. If you only
retrieve data, you can change the data type back to the
original, and SQL again retrieves the entire original value.)
o You can change a DATE column only to a character data type
(CHAR, VARCHAR, LONG VARCHAR, NCHAR, NATIONAL CHAR, NCHAR
VARYING, or NATIONAL CHAR VARYING, or date/time (DATE ANSI,
TIMESTAMP, TIME). If you attempt to change a DATE column to
anything else, SQL returns an error message.
14.3.16 – date-time-data-types
A valid SQL date-time data type. See the Data_Types HELP topic
for more information on date-time data types.
14.3.17 – disable-clause
Allows you to enable or disable all triggers, specified triggers,
all constraints, specified constraints, a primary key, or a
unique constraint, as described in the following list. By
default, table and column constraints added during an alter table
operation are enabled.
o DISABLE ALL TRIGGERS
All triggers defined for the table are disabled. (No error is
raised if no triggers are defined for this table.)
o ENABLE ALL TRIGGERS
All triggers defined for the table are enabled. (No error is
raised if no triggers are defined for this table.)
o DISABLE TRIGGER trigger-name
The named trigger for this table is disabled. The named
trigger must be defined on the table.
o ENABLE TRIGGER trigger-name
The named trigger for this table is enabled. The named trigger
must be defined on the table.
o DISABLE ALL CONSTRAINTS
All table and column constraints for this table are disabled.
(No error is raised if no constraints are defined on the
table.)
o ENABLE ALL CONSTRAINTS
All table and column constraints for this table are enabled.
(No error is raised if no constraints are defined on the
table.)
o DISABLE CONSTRAINT constraint-name
The named constraint is disabled. The named constraint must be
a table or column constraint for the table.
o ENABLE CONSTRAINT constraint-name
The named constraint is enabled. The named constraint must be
a table or column constraint for the table.
o DISABLE PRIMARY KEY
The primary key for the table is disabled.
o ENABLE PRIMARY KEY
The primary key for the table is enabled.
o DISABLE UNIQUE (column-name)
The matching UNIQUE constraint is disabled. The columns listed
must be columns in the table.
o ENABLE UNIQUE (column-name)
The matching UNIQUE constraint is enabled. The columns listed
must be columns in the table.
o VALIDATE and NOVALIDATE
These options are available only on the enable-clause. By
default, table and column constraints are enabled during an
ALTER TABLE statement. When a constraint is added or enabled
with the ALTER TABLE statement, the default is to validate
the table contents. The ENABLE NOVALIDATE option allows a
knowledgeable database administrator to avoid the time and I/O
resources required to revalidate the data when he or she knows
the data is valid.
NOTE
Oracle Corporation recommends that you use the RMU Verify
command with the Constraint qualifier periodically to
verify that your assumptions are correct if you use the
ENABLE NOVALIDATE option.
14.3.18 – DEFAULT value-expr
Provides a default value for a column if the row that is inserted
does not include a value for that column.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
You can add a default value to an existing column or alter
the existing default value of a column by altering the table.
However, doing so has no effect on the values stored in existing
rows.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a default value, a column inherits the
default value from the domain. If you do not specify a default
value for either the column or domain, SQL assigns NULL as the
default value.
If you specify a default value for either the column or domain
when a column is added, SQL propagates the default value from the
column or domain to all previously stored rows. Therefore, when
you add a column to a table and specify a default value for the
column, SQL stores the default value in the newly added column
of all the previously stored rows. Likewise, if the newly added
column is based upon a domain that specifies a default value, SQL
stores the default value in the column of all previously stored
rows.
Because SQL updates data when you add a column with a default
value other than NULL, the ALTER TABLE statement can take some
time to complete when the table contains many rows. (If you
specify a default value of NULL, SQL does not modify the data
because SQL automatically returns a null value for columns that
have no actual value stored in them.) If you want to add more
than one column with default values, add them in a single ALTER
TABLE statement. When you do so, SQL scans the table data once
instead of many times.
Because data is added to the rows, adding a column with a default
value may result in fragmented records. For information about
locating and correcting record fragmentation, see the Oracle Rdb7
Guide to Database Performance and Tuning.
14.3.19 – domain-name
The name of a domain created in a CREATE DOMAIN statement. SQL
gives the column the data type specified in the domain. For more
information on domains, see the CREATE DOMAIN. statement.
For most purposes, specify a domain instead of an explicit data
type.
o Domains ensure that columns in multiple tables that serve the
same purpose all have the same data type. For example, several
tables in the sample personnel database refer to the domain
ID_DOM.
o A domain lets you change the data type for all the columns
that refer to it in one operation by changing the domain
itself with an ALTER DOMAIN statement. For example, if you
want to change the data type for the column EMPLOYEE_ID from
CHAR(5) to CHAR(6), you need only alter the data type for ID_
DOM. You do not have to alter the data type for the column
EMPLOYEE_ID in the tables DEGREES, EMPLOYEES, JOB_HISTORY, and
SALARY_HISTORY, nor do you have to alter the column MANAGER_ID
in the DEPARTMENTS table.
However, you might not want to use domains when you create tables
if:
o Your application must be compatible with the Oracle RDBMS
language.
o You are creating tables that do not need the advantages of
domains.
14.3.20 – DROP_COLUMN
Deletes the specified column. The COLUMN keyword is optional.
14.3.21 – DROP_CONSTRAINT
Deletes the specified column constraint or table constraint from
the table definition.
14.3.22 – DROP_DEFAULT
Deletes (drops) the default value of a column in a table.
14.3.23 – enable-clause
Allows you to enable or disable all triggers, specified triggers,
all constraints, specified constraints, a primary key, or a
unique constraint, as described in the following list. By
default, table and column constraints added during an alter table
operation are enabled.
o DISABLE ALL TRIGGERS
All triggers defined for the table are disabled. (No error is
raised if no triggers are defined for this table.)
o ENABLE ALL TRIGGERS
All triggers defined for the table are enabled. (No error is
raised if no triggers are defined for this table.)
o DISABLE TRIGGER trigger-name
The named trigger for this table is disabled. The named
trigger must be defined on the table.
o ENABLE TRIGGER trigger-name
The named trigger for this table is enabled. The named trigger
must be defined on the table.
o DISABLE ALL CONSTRAINTS
All table and column constraints for this table are disabled.
(No error is raised if no constraints are defined on the
table.)
o ENABLE ALL CONSTRAINTS
All table and column constraints for this table are enabled.
(No error is raised if no constraints are defined on the
table.)
o DISABLE CONSTRAINT constraint-name
The named constraint is disabled. The named constraint must be
a table or column constraint for the table.
o ENABLE CONSTRAINT constraint-name
The named constraint is enabled. The named constraint must be
a table or column constraint for the table.
o DISABLE PRIMARY KEY
The primary key for the table is disabled.
o ENABLE PRIMARY KEY
The primary key for the table is enabled.
o DISABLE UNIQUE (column-name)
The matching UNIQUE constraint is disabled. The columns listed
must be columns in a unique constraint for the table.
o ENABLE UNIQUE (column-name)
The matching UNIQUE constraint is enabled. The columns listed
must be columns in a unique constraint for the table.
o VALIDATE and NOVALIDATE
When a constraint is added or enabled with the ALTER TABLE
statement, the default is to validate the table contents.
The ENABLE NOVALIDATE option allows a knowledgeable database
administrator to avoid the time and I/O resources required to
revalidate the data when they know the data is valid.
NOTE
Oracle Corporation recommends that you use the RMU Verify
command with the Constraint qualifier periodically to
verify that your assumptions are correct if you use the
ENABLE NOVALIDATE option.
14.3.24 – FOREIGN_KEY
The name of a column or columns that you want to declare as
a foreign key in the table you are altering (the referencing
table).
14.3.25 – IDENTITY
Specifies that the column is to be a special read-only identity
column. INSERT will evaluate this column and store a unique value
for each row inserted. Only one column of a table may have the
IDENTITY attribute. Oracle Rdb creates a sequence with the same
name as the current table.
See the ALTER SEQUENCE statement and the CREATE SEQUENCE
statement for more information.
14.3.26 – increment-by
An integer literal value that specifies the increment for the
sequence created for the IDENTITY column. A negative value
creates a descending sequence, and a positive value creates an
ascending sequence. A value of zero is not permitted. If omitted
the default is 1, that is, an ascending sequence.
14.3.27 – MODIFY_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
14.3.28 – NOT_NULL
Restricts values in the column to values that are not null.
14.3.29 – NULL
Specifies that NULL is permitted for the column. This is the
default behavior. A column with a NULL constraint cannot also
have a NOT NULL constraint within the same ALTER TABLE statement.
However, no checks are performed for CHECK constraints, which may
limit the column to non-null values.
The NULL constraint is not stored in the database and is provided
only as a syntactic alternative to NOT NULL.
When used on ALTER TABLE . . . ALTER COLUMN this clause drops any
NOT NULL constraints defined for the column.
14.3.30 – PRIMARY_KEY
A primary key constraint defines one or more columns whose values
make a row in a table different from all others. SQL requires
that values in a primary key column be unique and not null;
therefore, you need not specify the UNIQUE and NOT NULL column
constraints for primary key columns.
You cannot specify the primary key constraint for a computed
column.
When used as a table constraint this clause must be followed by
a list of column names. When used as a column constraint this
clause applies to the named column of the table.
14.3.31 – referenced-column-name
For a column constraint, the name of the column that is a
unique key or a primary key in the referenced table. For a table
constraint, the referenced column name is the name of the column
or columns that are a unique key or primary key in the referenced
table. If you omit the referenced-column-name clause, the primary
key is selected by default.
14.3.32 – references-clause
Specifies the name of the column or columns that are a unique
key or primary key or in the referenced table. When the
REFERENCES clause is used as a table constraint, the column names
specified in the FOREIGN KEY clause become a foreign key for the
referencing table.
When used as the column type clause, specifies that the type
of the column be inherited from the PRIMARY KEY or UNIQUE index
referenced. Both the data type and domain are inherited.
14.3.33 – RENAME_TO
Changes the name of the table being altered. See the RENAME
statement for further discussion. If the new name is the name
of a synonym then an error will be raised.
The new name must not exist as the name of an existing table,
synonym, sequence or view. You may not rename a system table.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
14.3.34 – SET_DEFAULT
Specifies a default value for the column.
14.3.35 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE
HELP topic for more information.
If you specify a formatting clause for a column that is based on
a domain that also specifies a formatting clause, the formatting
clause in the table definition overrides the one in the domain
definition.
14.3.36 – start-with
An integer literal value that specifies the starting value for
the sequence created for the IDENTITY column. If omitted the
default is 1.
14.3.37 – table-name
The name of the table whose definition you want to change.
14.3.38 – UNIQUE
Specifies that values in the associated column must be unique.
14.4 – Examples
Example 1: Adding a column to the EMPLOYEES table
SQL> ALTER TABLE EMPLOYEES ADD SALARY INTEGER(2);
Example 2: Adding a column and altering a column in the COLLEGES
table
The following example adds two columns, one with a query name to
the COLLEGES table. ALTER DOMAIN is also used to implicitly alter
the POSTAL_CODE column to accept 9 characters instead of 5.
SQL> SHOW TABLE COLLEGES;
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
COLLEGE_CODE CHAR(4) COLLEGE_CODE_DOM
Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
COLLEGE_NAME CHAR(25) COLLEGE_NAME_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
.
.
.
SQL> ALTER TABLE COLLEGES
cont> ADD RANKING INTEGER
cont> ADD NUMBER_ALUMS INTEGER
cont> QUERY_NAME IS 'ALUMS';
SQL> ALTER DOMAIN POSTAL_CODE_DOM CHAR(9);
SQL> SHOW TABLE COLLEGES;
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
COLLEGE_CODE CHAR(4) COLLEGE_CODE_DOM
Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
COLLEGE_NAME CHAR(25) COLLEGE_NAME_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(9) POSTAL_CODE_DOM
RANKING INTEGER
NUMBER_ALUMS INTEGER
Query Name: ALUMS
.
.
.
Example 3: Adding and modifying default values
SQL> /* Add a default value to the column HOURS_OVERTIME
***> */
SQL> create table DAILY_SALES
cont> (hours_overtime int
cont> ,hours_worked int default 0
cont> ,gross_sales int
cont> ,salesperson char(20)
cont> );
SQL>
SQL> /* Change the default value for the column HOURS_OVERTIME
***> */
SQL> alter table DAILY_SALES
cont> alter column HOURS_OVERTIME
cont> set default 0;
SQL>
SQL> /* Insert the days sales figures into the table,
***> accepting the default values for HOURS_WORKED, and
***> HOURS_OVERTIME
***> */
SQL> insert into DAILY_SALES (gross_sales, salesperson)
cont> values (2567, 'Bartlett');
1 row inserted
SQL>
SQL> table DAILY_SALES;
HOURS_OVERTIME HOURS_WORKED GROSS_SALES SALESPERSON
0 0 2567 Bartlett
1 row selected
SQL>
Example 4: Deleting a constraint from the EMPLOYEES table
To find out the name of a constraint, use the SHOW TABLES
statement. The SHOW TABLES statement shows all constraints that
refer to a table, not just those defined as part of the table's
definition. For that reason it is good practice to always use a
prefix to identify the table associated with a constraint when
you assign constraint names with the CONSTRAINT clause.
The constraint DEGREES_FOREIGN1 in this SHOW display follows that
convention to indicate that the constraint is associated with
the DEGREES, not the EMPLOYEES, table despite the constraint's
presence in the EMPLOYEES display.
SQL> SHOW TABLE EMPLOYEES
Information for table EMPLOYEES
Comment on table EMPLOYEES:
personal information about each employee
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_DOM
Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
LAST_NAME CHAR(14) LAST_NAME_DOM
FIRST_NAME CHAR(10) FIRST_NAME_DOM
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL_DOM
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1_DOM
ADDRESS_DATA_2 CHAR(20) ADDRESS_DATA_2_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
SEX CHAR(1) SEX_DOM
BIRTHDAY DATE DATE_DOM
STATUS_CODE CHAR(1) STATUS_CODE_DOM
Table constraints for EMPLOYEES:
EMPLOYEES_PRIMARY_EMPLOYEE_ID
Primary Key constraint
Column constraint for EMPLOYEES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
EMPLOYEES.EMPLOYEE_ID PRIMARY KEY
EMP_SEX_VALUES
Check constraint
Table constraint for EMPLOYEES
Evaluated on COMMIT
Source:
CHECK (
SEX IN ('M', 'F', '?')
)
EMP_STATUS_CODE_VALUES
Check constraint
Table constraint for EMPLOYEES
Evaluated on COMMIT
Source:
CHECK (
STATUS_CODE IN ('0', '1', '2', 'N')
)
Constraints referencing table EMPLOYEES:
DEGREES_FOREIGN1
Foreign Key constraint
Column constraint for DEGREES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
DEGREES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
JOB_HISTORY_FOREIGN1
Foreign Key constraint
Column constraint for JOB_HISTORY.EMPLOYEE_ID
Evaluated on COMMIT
Source:
JOB_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
RESUMES_FOREIGN1
Foreign Key constraint
Column constraint for RESUMES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
RESUMES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
SALARY_HISTORY_FOREIGN1
Foreign Key constraint
Column constraint for SALARY_HISTORY.EMPLOYEE_ID
Evaluated on COMMIT
Source:
SALARY_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
.
.
.
SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_SEX_VALUES;
Example 5: Adding a NOT NULL constraint to the EMPLOYEES table
SQL> ALTER TABLE EMPLOYEES
cont> ALTER BIRTHDAY
cont> CONSTRAINT E_BIRTHDAY_NOT_NULL
cont> NOT NULL;
If any rows in the EMPLOYEES table have a null BIRTHDAY column,
the ALTER statement fails and none of the changes described in it
will be made.
Example 6: Altering the character set of a table column
Assume the database was created specifying the database default
character set and identifier character set as DEC_KANJI and the
national character set as KANJI. Also assume the ROMAJI column
was created in the table COLOURS specifying the identifier
character set.
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> SHOW TABLE (COLUMNS) COLOURS;
Information for table COLOURS
Columns for table COLOURS:
Column Name Data Type Domain
----------- --------- ------
ENGLISH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
FRENCH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
JAPANESE CHAR(4) KANJI_DOM
KANJI 4 Characters, 8 Octets
ROMAJI CHAR(8) DEC_KANJI_DOM
KATAKANA CHAR(8) KATAKANA_DOM
KATAKANA 8 Characters, 8 Octets
HINDI CHAR(8) HINDI_DOM
DEVANAGARI 8 Characters, 8 Octets
GREEK CHAR(8) GREEK_DOM
ISOLATINGREEK 8 Characters, 8 Octets
ARABIC CHAR(8) ARABIC_DOM
ISOLATINARABIC 8 Characters, 8 Octets
RUSSIAN CHAR(8) RUSSIAN_DOM
ISOLATINCYRILLIC 8 Characters, 8 Octets
SQL> ALTER TABLE COLOURS ALTER ROMAJI NCHAR(8);
SQL> SHOW TABLE (COLUMNS) COLOURS;
Information for table COLOURS
Columns for table COLOURS:
Column Name Data Type Domain
----------- --------- ------
ENGLISH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
FRENCH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
JAPANESE CHAR(4) KANJI_DOM
KANJI 4 Characters, 8 Octets
ROMAJI CHAR(8)
KANJI 8 Characters, 16 Octets
KATAKANA CHAR(8) KATAKANA_DOM
KATAKANA 8 Characters, 8 Octets
HINDI CHAR(8) HINDI_DOM
DEVANAGARI 8 Characters, 8 Octets
GREEK CHAR(8) GREEK_DOM
ISOLATINGREEK 8 Characters, 8 Octets
ARABIC CHAR(8) ARABIC_DOM
ISOLATINARABIC 8 Characters, 8 Octets
RUSSIAN CHAR(8) RUSSIAN_DOM
ISOLATINCYRILLIC 8 Characters, 8 Octets
SQL>
Example 7: Error displayed if table COLOURS contains data
In the following example, the column ROMAJI is defined with the
DEC_KANJI character set. If the column ROMAJI contains data
before you alter the character set of the column, SQL displays
the following error when you try to retrieve data after altering
the table.
SQL> SELECT ROMAJI FROM COLOURS;
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-CSETBADASSIGN, incompatible character sets prohibits the requested
assignment
SQL> --
SQL> -- To recover, use the ROLLBACK statement or return the column to its
SQL> -- original character set.
SQL> --
SQL> ROLLBACK;
SQL> SELECT ROMAJI FROM COLOURS;
ROMAJI
kuro
shiro
ao
aka
ki
midori
6 rows selected
SQL>
Example 8: Using the Position Clause
SQL> SHOW TABLE (COL) EMPLOYEES
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
Missing Value:
LAST_NAME CHAR(14) LAST_NAME
FIRST_NAME CHAR(10) FIRST_NAME
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL
Missing Value:
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1
Missing Value:
ADDRESS_DATA_2 CHAR(25) ADDRESS_DATA_2
Missing Value:
CITY CHAR(20) CITY
Missing Value:
STATE CHAR(2) STATE
Missing Value:
POSTAL_CODE CHAR(5) POSTAL_CODE
Missing Value:
SEX CHAR(1) SEX
Missing Value: ?
BIRTHDAY DATE VMS STANDARD_DATE
Missing Value: 17-NOV-1858 00:00:00.00
STATUS_CODE CHAR(1) STATUS_CODE
Missing Value: N
SQL> -- Alter the table to rearrange the order in which columns
SQL> -- are displayed.
SQL> ALTER TABLE EMPLOYEES
cont> ALTER COLUMN SEX BEFORE COLUMN LAST_NAME
cont> ALTER COLUMN BIRTHDAY BEFORE COLUMN LAST_NAME
cont> ALTER COLUMN STATUS_CODE BEFORE COLUMN LAST_NAME;
SQL> COMMIT;
SQL> -- Show the table to demonstrate that the order in which
SQL> -- columns are displayed has changed.
SQL> SHOW TABLE (COL) EMPLOYEES;
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
Missing Value:
SEX CHAR(1) SEX
Missing Value: ?
BIRTHDAY DATE VMS STANDARD_DATE
Missing Value: 17-NOV-1858 00:00:00.00
STATUS_CODE CHAR(1) STATUS_CODE
Missing Value: N
LAST_NAME CHAR(14) LAST_NAME
FIRST_NAME CHAR(10) FIRST_NAME
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL
Missing Value:
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1
Missing Value:
ADDRESS_DATA_2 CHAR(25) ADDRESS_DATA_2
Missing Value:
CITY CHAR(20) CITY
Missing Value:
STATE CHAR(2) STATE
Missing Value:
POSTAL_CODE CHAR(5) POSTAL_CODE
Missing Value:
Example 9: Disabling a Trigger
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> -- Show that the EMPLOYEE_ID_CASCADE_DELETE trigger caused
SQL> -- records in the JOB_HISTORY table to be deleted for the
SQL> -- employee with EMPLOYEE_ID of 00164.
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
0 rows selected
SQL> -- Roll back the delete operation and alter the EMPLOYEES table
SQL> -- to disable the EMPLOYEE_ID_CASCADE_DELETE trigger.
SQL> ROLLBACK;
SQL> ALTER TABLE EMPLOYEES
cont> DISABLE TRIGGER EMPLOYEE_ID_CASCADE_DELETE;
SQL> -- Commit the alter operation and disconnect to ensure that
SQL> -- the next connection will have the trigger disabled.
SQL> COMMIT;
SQL> DISCONNECT DEFAULT;
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> -- Show that with the trigger disabled, a deletion of
SQL> -- employee 00164 from the EMPLOYEES table does not
SQL> -- trigger a deletion for that employee from the
SQL> -- JOB_HISTORY table.
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
Example 10: NOT NULL constraint is dropped
The following example shows that the NOT NULL constraint is
dropped by ALTER TABLE.
SQL> create table MY_TABLE (a integer not null);
SQL>
SQL> show table (constraint) MY_TABLE
Information for table MY_TABLE
Table constraints for MY_TABLE:
MY_TABLE_A_NOT_NULL
Not Null constraint
Column constraint for MY_TABLE.A
Evaluated on UPDATE, NOT DEFERRABLE
Source:
MY_TABLE.A NOT null
Constraints referencing table MY_TABLE:
No constraints found
SQL>
SQL> alter table MY_TABLE
cont> alter column A NULL;
SQL>
SQL> show table (constraint) MY_TABLE
Information for table MY_TABLE
Table constraints for MY_TABLE:
No constraints found
Constraints referencing table MY_TABLE:
No constraints found
SQL>
Example 11: Adding an identity column to an existing table
SQL> alter table EMPLOYEES
cont> add column SEQUENCE_ID integer identity (1000, 10)
cont> comment is 'Add unique sequence number for every employee';
SQL>
SQL> show table (column) EMPLOYEES
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
.
.
.
SEQUENCE_ID INTEGER
Computed: IDENTITY
Comment: Add unique sequence number for every employee
SQL> select EMPLOYEE_ID, SEQUENCE_ID from employees;
EMPLOYEE_ID SEQUENCE_ID
00164 1000
00165 1010
.
.
.
00418 1970
00435 1980
00471 1990
100 rows selected
SQL>
SQL> show sequence EMPLOYEES
EMPLOYEES
Sequence Id: 2
Initial Value: 1000
Minimum Value: 1000
Maximum Value: (none)
Next Sequence Value: 2000
Increment by: 10
Cache Size: 20
No Order
No Cycle
No Randomize
Wait
Comment: column IDENTITY sequence
SQL>
Example 12: Revising a COMPUTED BY column
SQL> create table ttt (a integer, c computed by CURRENT_USER);
SQL> insert into ttt (a) values (10);
1 row inserted
SQL> select * from ttt;
A C
10 SMITH
1 row selected
SQL>
SQL> show table (column) ttt
Information for table TTT
Columns for table TTT:
Column Name Data Type Domain
----------- --------- ------
A INTEGER
C CHAR(31)
UNSPECIFIED 31 Characters, 31 Octets
Computed: by CURRENT_USER
SQL>
SQL> alter table ttt
cont> alter c
cont> computed by upper (substring (current_user from 1 for 1))
cont> || lower (substring (current_user from 2));
SQL>
SQL> show table (column) ttt
Information for table TTT
Columns for table TTT:
Column Name Data Type Domain
----------- --------- ------
A INTEGER
C VARCHAR(31)
UNSPECIFIED 31 Characters, 31 Octets
Computed: by upper (substring (current_user from 1 for 1))
|| lower (substring (current_user from 2))
SQL>
SQL> select * from ttt;
A C
10 Smith
1 row selected
SQL>
15 – TRIGGER
Enables, disables, or renames an existing trigger. Changes
take place after the transaction containing the ALTER TRIGGER
statement is committed.
15.1 – Environment
You can use the ALTER TRIGGER statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
15.2 – Format
ALTER TRIGGER <trigger-name> --+--> DISABLE --+----------------+----+->
+--> ENABLE ---+ | |
+--> COMMENT IS -+-> 'string' -++ |
| +---- / ------+ |
+--> RENAME TO <new-trigger-name> ---+
+-------------------<----------------+
15.3 – Arguments
15.3.1 – COMMENT_IS
Adds a comment about the trigger. SQL displays the text of the
comment when it executes a SHOW statement. Enclose the comment
in single quotation marks ( ') and separate multiple lines in a
comment with a slash mark (/).
15.3.2 – DISABLE
Disables a previously enabled trigger.
15.3.3 – ENABLE
Enables a previously disabled trigger.
15.3.4 – RENAME_TO
Changes the name of the trigger being altered. See the RENAME for
further discussion. If the new name is the name of a synonym then
an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE statement SYNONYMS ARE
ENABLED clause. Note that these synonyms may be deleted if they
are no longer used by database definitions or applications.
15.3.5 – trigger-name
The name of an existing trigger.
15.4 – Examples
Example 1: Disabling a Trigger
The following example shows that while the EMPLOYEE_ID_CASCADE_
DELETE trigger is enabled, deleting a record from EMPLOYEES
causes the corresponding record in JOB_HISTORY to be deleted.
After the trigger is disabled, a deletion from EMPLOYEES does not
trigger a deletion from the JOB_HISTORY table.
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
0 rows selected
SQL> ROLLBACK;
SQL> ALTER TRIGGER EMPLOYEE_ID_CASCADE_DELETE DISABLE;
SQL> COMMIT;
SQL> DISCONNECT DEFAULT;
.
.
.
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
16 – USER
Modifies an entry for the specified user name. The modifications
take effect on the next database connection after the ALTER USER
statement is committed.
16.1 – Environment
You can use the ALTER USER statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
16.2 – Format
ALTER USER --+--> <username> --++------------------------+--->
+--> PUBLIC ------+++-> alter-user-opts --+-+
+----------<----------+
alter-user-opts =
-+-> ACCOUNT -----+--> LOCK ----+----------------------------------+->
| +--> UNLOCK --+ |
+-> IDENTIFIED EXTERNALLY ----------------------------------------+
+-> RENAME TO ------> <new-username> -----------------------------+
+-> COMMENT IS --+--> '<string>' ---------+-----------------------+
| +-------- / <------------+ |
+-> NO PROFILE ---------------------------------------------------+
+-> PROFILE <profile_name> ---------------------------------------+
16.3 – Arguments
16.3.1 – ACCOUNT
Syntax options:
ACCOUNT LOCK | ACCOUNT UNLOCK
The ACCOUNT LOCK clause disables access to the database by the
user for whom the ALTER USER statement is being applied. The
ACCOUNT UNLOCK clause allows the user access to the database.
16.3.2 – COMMENT_IS
Adds a comment about the user. SQL displays the text of the
comment when it executes a SHOW USERS statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
16.3.3 – IDENTIFIED_EXTERNALLY
Indicates that the user will be authenticated through the
operating system.
16.3.4 – PROFILE
Syntax options:
PROFILE | NOPROFILE
Identifies a new profile for assignment to the user and replaces
any previously assigned profile. The specified profile name must
be the name of an existing profile.
NOPROFILE removes any assigned profile from the user. No error is
returned if a profile is not currently assigned.
16.3.5 – PUBLIC
The PUBLIC user in the database. This entry gives you control
over anonymous users who access the database.
16.3.6 – RENAME_TO
Changes the user name and, if a security profile exists, assigns
the security profile associated with the old user name to the new
user name. This might be used, for example, when a person's name
changes (as through marriage), and, therefore, his or her account
on the operating system is changed accordingly. The new-username
must not currently exist in the database.
When the ALTER USER command is issued, the existing user name is
removed from the database and replaced with the new-username. If
SECURITY CHECKING is INTERNAL, then subsequent SHOW PROTECTION
statements will display the new name for the user, and all GRANT
and REVOKE statements will require the new-username. The new-
username is not visible to other sessions until the transaction
containing the ALTER USER command is committed.
See the RENAME for further discussion.
16.3.7 – username
An existing user name in the database.
16.4 – Examples
Example 1: Renaming a User
SQL> create user KELLYN
cont> identified externally
cont> comment is 'User: Edward "Ned" Kelly';
SQL>
SQL> -- The alternate name must exists at the operating system level
SQL> alter user KELLYN rename to N_KELLY;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-NOSUCHPRF, unknown profile user or role
SQL>
SQL> -- Use the new corporate user-id naming scheme
SQL> alter user KELLYN
cont> rename to NKELLY;
Example 2: Adding a profile to a user
This example creates a new profile that defines the DEFAULT
transaction and then assigns a profile to the user. The next time
the user attaches to the database, the START DEFAULT TRANSACTION
statement will use the defined profile instead of the standard
READ ONLY default.
SQL> create profile READ_COMMITTED
cont> default transaction read write isolation level read committed wait 30;
SQL> show profile READ_COMMITTED
READ_COMMITTED
Default transaction read write wait 30
Isolation level read committed
SQL> alter user JAIN profile READ_COMMITTED;
SQL> show user JAIN;
JAIN
Identified externally
Account is unlocked
Profile: READ_COMMITTED
No roles have been granted to this user
17 – VIEW
This statement allows the named view to be modified.
17.1 – Environment
You can use the ALTER VIEW statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in a SQL module
o In dynamic SQL as a statement to be dynamically executed
17.2 – Format
ALTER VIEW <view-name> -+-+--> AS <select-expr> -----------------+-+->
| +--> <check-option-clause> ------------+ |
| +--> COMMENT IS -+-> 'text-literal'--+-+ |
| | +--------- / <------+ | |
| +--> RENAME TO <new-view-name> -------+ |
| +--> WITH NO CHECK OPTION ------------+ |
+------------------- <---------------------+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
check-option-clause =
WITH CHECK OPTION --+----------------------------------+-->
+-> CONSTRAINT <check-option-name> +
17.3 – Arguments
17.3.1 – AS
Replaces the view select expression and the definitions of the
columns. The number of expressions in the select list must match
the original CREATE VIEW column list.
17.3.2 – COMMENT_IS
Replaces the comment currently defined for the view (if any).
The comment will be displayed by the SHOW VIEW statement in
Interactive SQL.
17.3.3 – CONSTRAINT
Specifies a name for the WITH CHECK OPTION constraint. If you
omit the name, SQL creates a name. However, Oracle Rdb recommends
that you always name constraints. If you supply a name for the
WITH CHECK OPTION constraint, the name must be unique in the
schema.
The name for the WITH CHECK OPTION constraint is used by the
INTEG_FAIL error message when an INSERT or UPDATE statement
violates the constraint.
17.3.4 – RENAME_TO
Renames the current view. The new view name must not exist as the
name of an existing view, table, sequence, or synonym.
17.3.5 – WITH_CHECK_OPTION
A constraint that places restrictions on update operations made
to a view. The check option clause ensures that any rows that are
inserted or updated in a view conform to the definition of the
view. Do not specify the WITH CHECK OPTION clause with views that
are read-only.
17.3.6 – WITH_NO_CHECK_OPTION
Removes any check option constraint currently defined for the
view.
17.4 – Examples
Example 1: Changing the comment on a view
A comment can be added or changed on a view using the COMMENT IS
clause as shown in this example.
SQL> show view (comment) current_job
Information for table CURRENT_JOB
SQL> alter view CURRENT_JOB
cont> comment is 'Select the most recent job for the employee';
SQL> show view (comment) current_job
Information for table CURRENT_JOB
Comment on table CURRENT_JOB:
Select the most recent job for the employee
SQL>
Example 2: Changing the column's results of a view definition
The following view uses a derived table and join to collect
the count of employees in each department. The view is used in
several reporting programs used by the department and company
managers.
SQL> create view DEPARTMENTS_SUMMARY
cont> as
cont> select department_code, d.department_name,
cont> d.manager_id, jh.employee_count
cont> from departments d inner join
cont> (select department_code, count (*)
cont> from job_history
cont> where job_end is null
cont> group by department_code)
cont> as jh (department_code, employee_count)
cont> using (department_code);
SQL>
SQL> show view DEPARTMENTS_SUMMARY;
Information for table DEPARTMENTS_SUMMARY
Columns for view DEPARTMENTS_SUMMARY:
Column Name Data Type Domain
----------- --------- ------
DEPARTMENT_CODE CHAR(4)
DEPARTMENT_NAME CHAR(30)
Missing Value: None
MANAGER_ID CHAR(5)
Missing Value:
EMPLOYEE_COUNT INTEGER
Source:
select department_code, d.department_name,
d.manager_id, jh.employee_count
from departments d inner join
(select department_code, count (*)
from job_history
where job_end is null
group by department_code) as jh (department_code, employee_count)
using (department_code)
SQL>
The database administrator decides to create a column in the
DEPARTMENTS table to hold the count of employees (rather than
using a query to gather the total) and to maintain the value
through triggers on EMPLOYEES and JOB_HISTORY (not shown here).
Now the view can be simplified without resorting to a DROP
VIEW and CREATE VIEW. The ALTER VIEW statement preserves the
dependencies on the view from other views, triggers, and routines
and so minimizes the work required to implement such a change.
SQL> alter table DEPARTMENTS
cont> add column EMPLOYEE_COUNT integer;
SQL>
SQL> alter view DEPARTMENTS_SUMMARY
cont> as
cont> select department_code, d.department_name,
cont> d.manager_id, d.employee_count
cont> from departments d;
SQL>
SQL> show view DEPARTMENTS_SUMMARY;
Information for table DEPARTMENTS_SUMMARY
Columns for view DEPARTMENTS_SUMMARY:
Column Name Data Type Domain
----------- --------- ------
DEPARTMENT_CODE CHAR(4)
Missing Value: None
DEPARTMENT_NAME CHAR(30)
Missing Value: None
MANAGER_ID CHAR(5)
Missing Value:
EMPLOYEE_COUNT INTEGER
Source:
select department_code, d.department_name,
d.manager_id, d.employee_count
from departments d
SQL>
Example 3: Changing the WITH CHECK OPTION constraint of a view
definition
This example shows that a WITH CHECK OPTION constraint restricts
the inserted data to the view's WHERE clause. Once the constraint
is removed, the INSERT is no longer constrained.
SQL> create view TOLIVER_EMPLOYEE
cont> as select * from EMPLOYEES where employee_id = '00164'
cont> with check option;
SQL> insert into TOLIVER_EMPLOYEE (employee_id) value ('00000');
%RDB-E-INTEG_FAIL, violation of constraint TOLIVER_EMPLOYEE_CHECKOPT1 caused operation to fail
-RDB-F-ON_DB, on database DISK1:[DATABASES]MF_PERSONNEL.RDB;1
SQL>
SQL> alter view TOLIVER_EMPLOYEE with no check option;
SQL>
SQL> insert into TOLIVER_EMPLOYEE (employee_id) value ('00000');
1 row inserted
SQL>