1  ALTER
 

2  CONSTRAINT
   Alters a constraint.
 

3  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
 

3  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 -----+

 

3  Arguments
 

4  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 (/).
 

4  constraint-attributes

   See the ALTER TABLE statement.
 

4  constraint-name

   The name of the table whose definition you want to change.
 

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.
 

3  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.
 

3  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
 

3  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> ---------------------+
 

 

3  Arguments
 

4  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.
 

4  ADD_JOURNAL

   Creates a new journal file.
 

4  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.
 

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.
 

4  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.
 

4  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.
 

4  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.
 

4  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
 

4  ALTER_JOURNAL

   Alters existing journal files. RDB$JOURNAL is the default journal
   name if no name is specified.
 

4  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.
 

4  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
 

4  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.
 

4  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);
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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
 

4  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.
 

4  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>    );
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  FILENAME_journal_file_spec

   Specifies the journal file specification with the default file
   extension .aij.
 

4  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)
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  MAXIMUM_PAGES

   Syntax option:

   MAXIMUM OF max-pages PAGES

   Specifies the maximum number of pages of each extent. The default
   is 9999 pages.
 

4  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.
 

4  MINIMUM_PAGES

   Syntax option:

   MINIMUM OF min-pages PAGES

   Specifies the minimum number of pages of each extent. The default
   is 99 pages.
 

4  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.
 

4  NO_BACKUP_FILENAME

   Removes a previously established backup file specification.
 

4  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.
 

4  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.
 

4  NO_SWEEP_INTERVAL

   NO SWEEP INTERVAL disables periodic timed sweeps.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  PERCENT_GROWTH

   Syntax option:

   PERCENT GROWTH IS growth

   Specifies the percent growth of each extent. The default is 20
   percent growth.
 

4  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.
 

4  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.
 

4  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.
 

4  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]');
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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

      .
      .
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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).
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  storage-area-params

   Parameters that control the characteristics of the storage area.
   For more information on the parameters, see the CREATE STORAGE_
   AREA statement.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

3  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
   .
   .
   .
 

2  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  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  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  Arguments
 

4  char-data-types

   A valid SQL character data type. For more information on
   character data types, see the Data_Types HELP topic.
 

4  character-set-name

   A valid character set name. For a list of allowable character set
   names, see the Supported_Character_Sets HELP topic.
 

4  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.
 

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 (/).
 

4  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.
 

4  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.
 

4  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
 

4  domain-name

   The name of a domain you want to alter. The domain name must be
   unique among domain names in the database.
 

4  DROP_DEFAULT

   Deletes (drops) the default value of a domain.
 

4  IS_datatype

   A valid SQL data type. For more information on data types, see
   the Data_Types HELP topic.
 

4  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.
 

4  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.
 

4  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.
 

4  sql-and-dtr-clause

   Optional SQL and DATATRIEVE formatting clause. For more
   information on the formatting clauses, see the DATATRIEVE HELP
   topic.
 

4  value-expr

   Specifies the default value of a domain.
 

3  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
 

2  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
 

3  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
 

3  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 --+
 

 

3  Arguments
 

4  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  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  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  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  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  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  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  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  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  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  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  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  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.
 

3  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
 

2  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
 

3  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
 

3  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 ----------------------------------+

 

3  Arguments
 

4  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.
 

4  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.
 

4  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.
 

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 (/).
 

4  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.
 

4  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.
 

4  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.
 

4  index-name

   The name of the index.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

3  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
 

2  MODULE
   Alters a module to add or drop routines, change a comment, or
   compile stored routines.
 

3  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
 

3  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 --+ |
                                         +----------<--------+

 

3  Arguments
 

4  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.
 

4  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.
 

4  COMPILE

   Recompiles stored routines in the module. Any that were
   marked invalid will have this flag cleared if the compile was
   successful.
 

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.
 

4  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.
 

4  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.
 

3  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
 

2  OUTLINE
   Alters an outline definition.
 

3  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
 

3  Format

 ALTER OUTLINE  <outline-name>  ----+
   +--------------------------------+
   +-+-+-> COMMENT IS --+-------> '<string>'  ---------+--+-+->
     | |                +------------ / <--------------+  | |
     | +-> COMPILE ---------------------------------------+ |
     | +-> MOVE TO  <new-schema-name>    -----------------+ |
     | +-> RENAME TO <new-outline-name>  -----------------+ |
     +-------------------------- <--------------------------+

 

3  Arguments
 

4  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.
 

4  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.
 

4  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.
 

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.
 

3  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;
 

2  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
 

3  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
 

3  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 --+
 

 

3  Arguments
 

4  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  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  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.
 

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.
 

4  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  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  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  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  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  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.
 

3  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>
 

2  PROFILE
   Alters a profile definition.
 

3  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
 

3  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  ----+

 

3  Arguments
 

4  ALIAS_aliasname

   When attached to multiple databases, the aliasname is required to
   direct the ALTER command to the appropriate database.
 

4  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 (/).
 

4  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.
 

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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  RENAME_TO

   Changes the name of the profile being altered. See the RENAME
   statement for further discussion.
 

4  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.
 

3  Examples

   The following example changes a default transaction for an
   existing profile.

   SQL> ALTER PROFILE DECISION_SUPPORT
   cont>   DEFAULT TRANSACTION READ ONLY;
 

2  ROLE
   Allows you to change the role name or add a comment to a role.
 

3  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
 

3  Format

 ALTER ROLE ----> <role-name> --+----------------------+------>
                                +--> alter-role-opts --+



 alter-role-opts =
 
 --+--> IDENTIFIED EXTERNALLY -------------+---->
   +--> NOT IDENTIFIED --------------------+
   +--> RENAME TO <new-role-name> ---------+
   +--> COMMENT IS --+--> 'string' --+-----+
                     +--<--- / ------+

 

3  Arguments
 

4  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 (/).
 

4  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.
 

4  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.
 

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.
 

4  role-name

   The name of an existing role (such as one created with the CREATE
   ROLE statement).
 

3  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
 

2  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.
 

3  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
 

3  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 --------------------------+

 

3  Arguments
 

4  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.
 

4  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 (/).
 

4  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.
 

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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  sequence-name

   The name of the sequence whose definition you want to change.
 

4  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.
 

3  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>
 

2  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.
 

3  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
 

3  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 --+            |
     +----------------------------<-----------------------+
 

 

3  Arguments
 

4  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.
 

4  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 (/).
 

4  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.
 

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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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).
 

4  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.
 

4  NO_REORGANIZE

   Disables the reorganize action for PARTITIONING IS NOT UPDATABLE.
 

4  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.
 

4  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.
 

4  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.
 

4  PLACEMENT_VIA_INDEX

   See the CREATE STORAGE_MAP statement for details of the PLACEMENT
   VIA INDEX argument.
 

4  RENAME_PARTITION

Syntax options:

   RENAME PARTITION partition-name TO new-partition-name


   Specifies a new name for an existing storage map partition.
 

4  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.
 

4  STORAGE_MAP_map_name

   Specifies the name of the storage map you want to alter.
 

4  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.
 

4  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.
 

4  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.
 

3  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;
 

2  SYNONYM
   Alters a synonym definition.
 

3  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
 

3  Format

 ALTER SYNONYM <synonym-name> -+----------------------+-+
                               +-> FOR <object-name> -+ |
 +------------------------------------------------------+
 ++-------------------------------+--------------------->
  +-> COMMENT IS -+-> 'string' -+-+
                  +----- / <----+

 

3  Arguments
 

4  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.
 

4  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.
 

4  synonym-name

   The name of an existing synonym you want to alter.
 

3  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;
 

2  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.
 

3  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
 

3  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> -+-> ) -+
                                +------- , <-------+

 

3  Arguments
 

4  ADD_(...)__

   This alternate syntax is added for compatibility with Oracle
   RDBMS.
 

4  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.
 

4  ADD_CONSTRAINT

   Adds a table constraint definition. The four types of table
   constraints are PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
 

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.
 

4  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.
 

4  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.
 

4  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.
 

4  char-data-types

   A valid SQL character data type. See the Data_Types HELP topic
   for more information on character data types.
 

4  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.
 

4  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;
 

4  column-name

   The name of the column being added or modified.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  DROP_COLUMN

   Deletes the specified column. The COLUMN keyword is optional.
 

4  DROP_CONSTRAINT

   Deletes the specified column constraint or table constraint from
   the table definition.
 

4  DROP_DEFAULT

   Deletes (drops) the default value of a column in a table.
 

4  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.
 

4  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).
 

4  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.
 

4  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.
 

4  MODIFY_(...)__

   This alternate syntax is added for compatibility with Oracle
   RDBMS.
 

4  NOT_NULL

   Restricts values in the column to values that are not null.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  SET_DEFAULT

   Specifies a default value for the column.
 

4  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.
 

4  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.
 

4  table-name

   The name of the table whose definition you want to change.
 

4  UNIQUE

   Specifies that values in the associated column must be unique.
 

3  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>
 

2  TRIGGER
   Enables, disables, or renames an existing trigger. Changes
   take place after the transaction containing the ALTER TRIGGER
   statement is committed.
 

3  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
 

3  Format

 ALTER TRIGGER <trigger-name> --+--> DISABLE --+----------------+----+->
                                +--> ENABLE ---+                |    |
                                +--> COMMENT IS -+-> 'string' -++    |
                                |                +---- / ------+     |
                                +--> RENAME TO <new-trigger-name> ---+
                                +-------------------<----------------+

 

3  Arguments
 

4  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 (/).
 

4  DISABLE

   Disables a previously enabled trigger.
 

4  ENABLE

   Enables a previously disabled trigger.
 

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.
 

4  trigger-name

   The name of an existing trigger.
 

3  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
 

2  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.
 

3  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
 

3  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> ---------------------------------------+

 

3  Arguments
 

4  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.
 

4  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 (/).
 

4  IDENTIFIED_EXTERNALLY

   Indicates that the user will be authenticated through the
   operating system.
 

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.
 

4  PUBLIC

   The PUBLIC user in the database. This entry gives you control
   over anonymous users who access the database.
 

4  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.
 

4  username

   An existing user name in the database.
 

3  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
 

2  VIEW
   This statement allows the named view to be modified.
 

3  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
 

3  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> +
 

 

3  Arguments
 

4  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.
 

4  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.
 

4  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.
 

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.
 

4  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.
 

4  WITH_NO_CHECK_OPTION

   Removes any check option constraint currently defined for the
   view.
 

3  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>