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.
1 – Environment
You can use the ALTER DATABASE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – 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
3.1 – ADD CACHE row-cache-name
Adds a new row cache. For information regarding the row-cache-
params-1 and row-cache-params-2, see the descriptions under the
CREATE CACHE clause.
3.2 – ADD_JOURNAL
Creates a new journal file.
3.3 – ADD_STORAGE_AREA
Specifies the name and file specification for a storage area you
want to add to the database. You can use the ADD STORAGE AREA
clause only on multifile databases. The storage area name cannot
be the same as any other storage area name in the database.
The ADD STORAGE AREA clause creates two files: a storage area
file with an .rda file extension and a snapshot file with an
.snp file extension. If you omit the FILENAME argument, the file
specification uses the following defaults:
o Device-the current device for the process
o Directory-the current directory for the process
o File name-the name specified for the storage area
The file specification is used for the storage area and snapshot
files that comprise the storage area (unless you use the SNAPSHOT
FILENAME argument to specify a different file for the snapshot
file, which you can only specify with a multifile database).
Because the ADD STORAGE AREA clause creates two files with
different file extensions, do not specify a file extension with
the file specification.
If you use the ALTER DATABASE statement to add a storage area,
the change is journaled, however, you should back up your
database before making such a change.
3.4 – ADJUSTABLE_LOCK_GRANULARITY
Syntax options:
ADJUSTABLE LOCK GRANULARITY IS ENABLED | ADJUSTABLE LOCK
GRANULARITY IS DISABLED
Enables or disables whether or not the database system
automatically maintains as few locks as possible on database
resources. The default, ENABLED, results in fewer locks against
the database. However, if contention for database resources is
high, the automatic adjustment of locks can become a CPU drain.
You can trade more restrictive locking for less CPU usage in such
databases by disabling adjustable lock granularity.
3.5 – ALERT_OPERATOR
Specifies which operator will be notified of the occurrence of
a database system event. You can specify the following operator
classes:
Operator
Class Meaning
ALL The ALL operator class broadcasts a message to
all terminals that are enabled as operators and
that are attached to the system or cluster. These
terminals must be turned on and have broadcast-
message reception enabled.
NONE The NONE operator class inhibits the display of
messages to the entire system or cluster.
[NO] CENTRAL The CENTRAL operator class broadcasts messages
sent to the central system operator. The NO
CENTRAL operator class inhibits the display of
messages sent to the central system operator.
[NO] DISKS The DISKS operator class broadcasts messages
pertaining to mounting and dismounting disk
volumes. The NO DISKS operator class inhibits
the display of messages pertaining to mounting and
dismounting disk volumes.
[NO] CLUSTER The CLUSTER operator class broadcasts messages
from the connection manager pertaining to
cluster state changes. The NO CLUSTER operator
class inhibits the display of messages from the
connection manager pertaining to cluster state
changes.
[NO] CONSOLE The CONSOLE class broadcasts messages to the
Oracle Enterprise Manager (OEM). NO CONSOLE
inhibits broadcast to OEM.
[NO] The SECURITY operator class displays messages
SECURITY pertaining to security events. The NO SECURITY
operator class inhibits the display of messages
pertaining to security events.
[NO] OPER1 The OPER1 through OPER12 operator classes display
through [NO] messages to operators identified as OPER1 through
OPER12 OPER12. The NO OPER1 through NO OPER12 operator
classes inhibit messages from being sent to the
specified operator.
3.6 – ALLOCATION blocks for aij
Syntax option:
ALLOCATION IS n BLOCKS
Specifies the number of blocks allocated for the .aij file. The
default and minimum is 512 blocks. Even if you specify a value
less than 512 blocks, the .aij file is allocated 512 blocks.
For information on determining the allocation value, see the
Oracle Rdb Guide to Database Design and Definition.
3.7 – ALLOCATION IS n PAGES
Specifies the number of database pages allocated to the storage
area. The initial allocation never changes and is used for
the hash algorithm. The new allocation becomes the current
allocation. If you execute the RMU Dump/Header command, you see
the initial and the current allocation.
SQL automatically extends the allocation to handle the storage
requirements. Pages are allocated in groups of three (known as a
clump). An ALLOCATION of 25 pages actually provides for 27 pages
of data and subsequent expansion. The default is 700 pages.
The altered area is extended if the specified value exceeds
the current area allocation. Otherwise the specified value is
ignored.
3.8 – ALTER_CACHE
Alters an existing row cache.
For more information, see the following arguments:
o ALLOCATION_blocks_for_rdc
o CACHE_SIZE_IS_n_ROWS
o EXTENT_blocks_for_rdc
o LARGE_MEMORY
o LOCATION
o NO_LOCATION
o NUMBER_OF_RESERVED_ROWS
o SHARED_MEMORY
o ROW_REPLACEMENT
o ROW_LENGTH
o WINDOW_COUNT
3.9 – ALTER_JOURNAL
Alters existing journal files. RDB$JOURNAL is the default journal
name if no name is specified.
3.10 – alter-root-file-params
Parameters that control the characteristics of the database
root file associated with the database or that control the
characteristics that apply to the entire database. You can
specify these parameters for either single-file or multifile
databases except as noted in the individual parameter
descriptions. For more information about database parameters
and details about how they affect performance, see the Oracle
Rdb7 Guide to Database Performance and Tuning.
The ALTER DATABASE statement does not let you change all database
root file parameters that you can specify in the CREATE DATABASE
statement. You must use the EXPORT and IMPORT statements to
change a number of storage area parameters. For more information
on changing storage area parameters, see the IMPORT statement.
3.11 – alter-storage-area-params
Parameters that change the characteristics of database storage
area files. You can specify the same storage area parameters for
either single-file or multifile databases, but the effect of the
clauses in this part of an ALTER DATABASE statement differs.
o For single-file databases, the storage area parameters
change the characteristics for the single storage area in
the database.
o For multifile databases, the storage area parameters change
the characteristics of the RDB$SYSTEM storage area.
You can also change some of the characteristics of the
RDB$SYSTEM storage area using the ALTER STORAGE AREA clause.
However, you can only change the read-only and read/write
parameters in this part of the ALTER DATABASE statement. See
the ALTER_STORAGE_AREA topic in this Arguments list for more
information about the RDB$SYSTEM characteristics that you are
allowed to alter.
The ALTER DATABASE statement does not let you change all storage
area parameters you can specify in the CREATE DATABASE statement.
You must use the EXPORT and IMPORT statements to change the
following database root file parameters:
o INTERVAL
o PAGE FORMAT
o PAGE SIZE
o SNAPSHOT FILENAME
o THRESHOLDS
3.12 – ALTER_STORAGE_AREA
Specifies the name of an existing storage area in the database
that you want to alter. You can use the ALTER STORAGE AREA clause
only on multifile databases.
You can specify RDB$SYSTEM for the area-name if you are altering
the following clauses:
o ALLOCATION IS number-pages PAGES
o extent-params
o CACHE USING row-cache-name
o NO ROW CACHE
o SNAPSHOT ALLOCATION IS snp-pages PAGES
o SHAPSHOT EXTENT
o CHECKSUM CALCULTION
o SNAPSHOT CHECKSUM CALCULATION
Oracle Rdb generates an error if you specify RDB$SYSTEM or the
DEFAULT storage area as the area-name when altering the following
clauses:
o LOCKING IS PAGE LEVEL
o READ WRITE
o READ ONLY
If you want to change the read-only and read/write parameters of
the RDB$SYSTEM storage area using the ALTER DATABASE statement,
you must specify these parameters outside of the ALTER STORAGE
AREA clause.
3.13 – ALTER_TRANSACTION_MODES
Enables or disables the modes specified leaving the previously
defined or default modes enabled. This is an offline operation
and requires exclusive database access.
If the current transaction modes are SHARED and READ ONLY and you
want to add the EXCLUSIVE mode, use the following statement:
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER TRANSACTION MODES (EXCLUSIVE);
3.14 – ASYNC_BATCH_WRITES
Syntax options:
ASYNC BATCH WRITES ARE ENABLED | ASYNC BATCH WRITES ARE DISABLED
Specifies whether asynchronous batch-writes are enabled or
disabled.
Asynchronous batch-writes allow a process to write batches of
modified data pages to disk asynchronously (the process does not
stall while waiting for the batch-write operation to complete).
Asynchronous batch-writes improve the performance of update
applications without the loss of data integrity.
By default, batch-writes are enabled.
For more information about when to use asynchronous batch-writes,
see the Oracle Rdb7 Guide to Database Performance and Tuning.
You can enable asynchronous batch-writes by defining the logical
name RDM$BIND_ABW_ENABLED.
3.15 – ASYNC_PREFETCH
Syntax options:
ASYNC PREFETCH IS ENABLED | ASYNC PREFETCH IS DISABLED
Specifies whether or not Oracle Rdb reduces the amount of time
that a process waits for pages to be read from disk by fetching
pages before a process actually requests the pages.
Prefetch can significantly improve performance, but it may
cause excessive resource usage if it is used inappropriately.
Asynchronous prefetch is enabled by default. For more information
about asynchronous prefetch, see the Oracle Rdb7 Guide to
Database Performance and Tuning.
You can enable asynchronous prefetch by defining the logical name
RDM$BIND_APF_ENABLED.
3.16 – BACKUP_FILENAME
Syntax option:
BACKUP FILENAME backup-file-spec
Specifies the default file specification to be used by the backup
server.
During execution, the backup server and the RMU Backup After_
Journal command use this file specification as the name of the
backup file. You can override this value by specifying a file
name for the journal file using the RMU Backup After_Journal
command.
3.17 – backup-filename-options
Specifies whether or not the backup file name includes an edit
string. When the EDIT STRING clause is used, the specified backup
file name is edited by appending any or all of the edit string
options listed in the following table.
Edit String
Option Meaning
SEQUENCE The journal sequence number of the first journal
file in the backup operation.
YEAR The current year expressed as a 4-digit integer.
MONTH The current month expressed as a 2-digit integer
(01-12).
DAY The current day of the month expressed as a 2-
digit integer (00-31).
HOUR The current hour of the day expressed as a 2-digit
integer (00-23).
MINUTE The current minute of the hour expressed as a
2-digit integer (00-59).
JULIAN The current day of the year expressed as a 3-digit
integer (001-366).
WEEKDAY The current day of the week expressed as a 1-digit
integer (1-7) where 1 is Sunday and 7 is Saturday.
literal Any string literal. This string literal is copied
to the file specification. See Quoted Character
String for more information about string literals.
Use a plus sign (+) between multiple edit string options. The
edit string should be 32 characters or less in length.
The default is NO EDIT STRING which means the BACKUP FILENAME
supplied is all that is used to name the backup file.
3.18 – BACKUP_SERVER
Syntax options:
BACKUP SERVER IS AUTOMATIC backup-file-spec | BACKUP SERVER IS
MANUAL backup-file-spec
Specifies whether the backup server runs automatically or
manually.
If BACKUP SERVER IS MANUAL is specified, you must execute the
RMU Backup After_Journal command manually. If BACKUP SERVER
IS AUTOMATIC is specified, a special backup server runs when
a journal file in the set is full and causes a switch over to
another journal file.
The default is MANUAL.
3.19 – BUFFER_SIZE
Syntax option:
BUFFER SIZE IS buffer-blocks BLOCKS
Specifies the number of blocks Oracle Rdb allocates per buffer.
You need to specify an unsigned integer greater than zero. The
default buffer size is 3 times the PAGE SIZE value (6 blocks for
the default PAGE SIZE of 2).
The buffer size is a global parameter and the number of blocks
per page (or buffer) is constrained to 64 blocks per page. The
page size can vary by storage area for multifile databases, and
the page size should be determined by the sizes of the records
that will be stored in each storage area.
When choosing the number of blocks per buffer, choose a number so
that a round number of pages fits in the buffer. In other words,
the buffer size is wholly divisible by all page sizes for all
storage areas in your multifile database. For example, if you
have three storage areas with page sizes of 2, 3, and 4 blocks
each respectively, choosing a buffer size of 12 blocks ensures
optimal buffer utilization. In contrast, choosing a buffer size
of 8 wastes 2 blocks per buffer for the storage area with a page
size of 3 pages. Oracle Rdb reads as many pages as fit into the
buffer; in this instance it reads two 3-block pages into the
buffer, leaving 2 wasted blocks.
The altered buffer size must allow for existing page sizes. You
cannot specify a buffer size smaller than the largest existing
page size.
3.20 – CACHE_USING
Specifies that the named row cache is the default physical row
cache for all storage areas in the database. All rows stored in
each storage area are cached, regardless of whether they consist
of table data, segemented string data, or are special rows such
as index nodes.
You must either add the specified cache before completing the
ALTER DATABASE statement, or it must already exist.
Alter the database and storage area to asign a new physical
area row cache that overrides the database default physical area
row cache. Only one physical area row cache is allowed for each
storage area.
You can have multiple row caches that contain rows for a single
storage area by defining logical area row caches, where the row
cache name matches the name of a table or index.
If you do not specify the CACHE USING clause or the NO ROW CACHE
clause, then the NO ROW CACHE clause is the default.
3.21 – CARDINALITY_COLLECTION
Syntax options:
CARDINALITY COLLECTION IS ENABLED | CARDINALITY COLLECTION IS
DISABLED
Specifies whether or not the optimizer records cardinality
updates in the system tables. When enabled, the optimizer
collects cardinalities for tables and indexes as rows are
inserted or deleted from tables. The update of the cardinalities
is performed at commit time, if sufficient changes have
accumulated, or at disconnect time.
In high update environments, it may be more convenient to
disable cardinality updates. If you disable this feature, you
should manually maintain the cardinalities using the RMU Collect
Optimizer_Statistics command so that the optimizer is given the
most accurate values for estimation purposes.
Cardinality collection is enabled by default.
3.22 – CARRY_OVER_LOCKS
Syntax options:
CARRY OVER LOCKS ARE ENABLED | CARRY OVER LOCKS ARE DISABLED
Enables or disables carry-over lock optimization. Carry-over lock
optimization holds logical area locks (table and index) across
transactions. Carry-over locks are enabled by default and are
available as an online database modification.
For more information on carry-over lock optimization, see the
CREATE DATABASEstatement.
3.23 – CHECKPOINT EVERY n TRANSACTIONS
A FAST COMMIT option which allows the checkpoint to be generated
after a set number of transactions.
See the following example.
SQL> alter database
cont> filename db$:scratch
cont>
cont> journal is enabled
cont> (fast commit is enabled
cont> (checkpoint every 20 transactions,
cont> checkpoint timed every 20 seconds
cont> )
cont> )
cont> add journal rdb$journal
cont> filename db$:scratch_aij
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
3.24 – CHECKPOINT_INTERVAL
Syntax option:
CHECKPOINT INTERVAL IS n BLOCKS
You can limit how many transactions the database recovery process
(DBR) must redo by setting a checkpoint interval. Setting a
checkpoint interval instructs Oracle Rdb to periodically write
modified pages to disk. This shortens recovery time.
The value you assign to the checkpoint interval specifies the
number of blocks the .aij file is allowed to increase to before
updated pages are transferred. For example, if you set the
checkpoint interval value equal to 100, all processes transfer
updated pages to the disk when 100 blocks were written to
the .aij file since the last checkpoint. Thus all processes
contribute to .aij growth.
If no checkpoint interval is established and a process completes
1000 transactions but fails during number 1001, the DBR must redo
transactions 1 through 1000 and undo number 1001.
When a process attaches to the database, it writes a checkpoint
record to the .aij file and notes the virtual block number (VBN)
of the .aij file at which the checkpoint record is located. If
the checkpoint is located at VBN 120 and the checkpoint interval
is 100 blocks, the process checkpoints again when VBN 220 is
reached.
Because all processes contribute to .aij file growth, a process
may be able to commit many transactions before checkpointing
if update activity by other processes is low. Conversely, if a
process' first transaction is long and if update activity by
other processes is high, the process may be forced to checkpoint
when it commits its first transaction.
When the database checkpoint interval value is reached, Oracle
Rdb executes the following steps:
1. Writes updated pages to the disk.
2. Writes a checkpoint record to the .aij file.
3. Updates the run-time user process block (RTUPB) for each
process to indicate where the checkpoint record is stored
in the .aij file.
The RTUPB is a data structure in the database root file that
maintains information on each process accessing the database.
The database recovery process (DBR) uses the RTUPB checkpoint
entry to determine where in the .aij file recovery must start.
3.25 – CHECKPOINT TIMED for fast commit
Assigns a value to the checkpoint interval specifying the number
of seconds that can pass before updated pages are written. When
the specified number of seconds elapsed, Oracle Rdb executes the
checkpoint steps.
For example, if you specify TIMED EVERY 100 SECONDS, each process
checkpoints after at least 100 seconds have passed since its last
checkpoint.
You can set both a checkpoint based on time and a checkpoint
based on .aij file growth; Oracle Rdb performs a checkpoint
operation at whichever checkpoint it reaches first.
The following statement enables fast commit processing and
specifies checkpoint intervals of 512 blocks and 12 seconds:
SQL> ALTER DATABASE FILENAME test1
cont> JOURNAL IS ENABLED
cont> (FAST COMMIT ENABLED
cont> (CHECKPOINT INTERVAL IS 512 BLOCKS,
cont> CHECKPOINT TIMED EVERY 12 SECONDS)
cont> );
3.26 – Checkpoint Rows
Syntax options:
CHECKPOINT UPDATED ROWS TO BACKING FILE
CHECKPOINT UPDATED ROWS TO DATABASE
CHECKPOINT ALL ROWS TO BACKING FILE
Specifies the default source and target during checkpoint
operations for all row caches. If ALL ROWS is specified, then the
source records written during each checkpoint operation are both
the modified and the unmodified rows in a row cache. If UPDATED
ROWS is specified, then just the modified rows in a row cache are
checkpointed each time.
If the target of the checkpoint operation is BACKING FILE,
then the RCS process writes the source row cache entries to
the backing (.rdc) files. The row cache LOCATION, ALLOCATION,
and EXTENT clauses are used to create the backing files. Upon
recovery from a node failure, the database recovery process is
able to repopulate the row caches in memory from the rows found
in the backing files.
If the target is DATABASE, then updated row cache entries
are written back to the database. The row cache LOCATION,
ALLOCATION, and EXTENT clauses are ignored. Upon recovery from
a node failure, the database recovery process has no data on the
contents of the row cache. Therefore, it does not repopulate the
row caches in memory.
The CHECKPOINT clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this database-level CHECKPOINT clause.
3.27 – CHECKSUM_CALCULATION
Syntax options:
CHECKSUM CALCULATION
SNAPSHOT CHECKSUM CALCULATION
This option allows you to enable or disable calculations of page
checksums when pages are read from or written to the storage area or
snapshot files.
The default is ENABLED.
NOTE
Oracle Corporation recommends that you leave checksum
calculations enabled, which is the default.
With current technology, it is possible that errors may occur
that the checksum calculation can detect but that may not
be detected by either the hardware, firmware, or software.
Unexpected application results and database corruption may occur
if corrupt pages exist in memory or on disk but are not detected.
Oracle Corporation recommends performing checksum calculations,
except in the following specific circumstances:
o Your application is stable and has run without errors on the
current hardware and software configuration for an extended
period of time.
o You have reached maximum CPU utilization in your current
configuration. Actual CPU utilization by the checksum
calculation depends primarily on the size of the database
pages in your database. The larger the database page, the
more noticeable the CPU usage by the checksum calculation may
become.
NOTE
Oracle Corporation recommends that you carefully evaluate
the trade-off between reducing CPU usage by the checksum
calculation and the potential for loss of database integrity
if checksum calculations are disabled.
Oracle Corporation allows you to disable and, subsequently,
re-enable checksum calculation without error. However, once
checksum calculations have been disabled, corrupt pages may
not be detected even if checksum calculations are subsequently
re-enabled.
3.28 – CLEAN_BUFFER_COUNT
Syntax option:
CLEAN BUFFER COUNT IS buffer-count BUFFERS
Specifies the number of buffers to be kept available for
immediate reuse.
The default is five buffers. The minimum value is one; the
maximum value can be as large as the buffer pool size.
You can override the number of clean buffers by defining the
logical name RDM$BIND_CLEAN_BUF_CNT. For information about how to
set the values, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
3.29 – COMMIT_TO_JOURNAL_OPTIMIZATION
Syntax options:
COMMIT TO JOURNAL OPTIMIZATION | NO COMMIT TO JOURNAL
OPTIMIZATION
If you enable COMMIT TO JOURNAL OPTIMIZATION when you enable fast
commit processing, Oracle Rdb does not write commit information
to the database root file. This option enhances performance
in database environments that are update-intensive. Because
of the prerequisites for enabling the journal optimization
option, general-use databases or databases that have many read-
only transactions may not benefit from this feature. For more
information, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
NOTE
If you specify COMMIT TO JOURNAL OPTIMIZATION, you must
disable or defer snapshots.
If you change snapshots to ENABLED IMMEDIATE, then you must
specify NO COMMIT TO JOURNAL OPTIMIZATION.
3.30 – COUNT IS n
Specifies the number of levels on the page lock tree used to
manage locks. For example, if you specify COUNT IS 3, the fanout
factor is (10, 100, 1000). Oracle Rdb locks a range of 1000 pages
and adjusts downward to 100 and then to 10 and then to 1 page
when necessary.
If the COUNT IS clause is omitted, the default is 3. The value of
n can range from 1 through 8.
3.31 – DEPTH buffers option
Syntax option:
DEPTH IS number-buffers BUFFERS
Specifies the number of buffers to prefetch for a process.
The default is one-quarter of the buffer pool, but not more than
eight buffers. You can override the number of buffers specified
in the CREATE or ALTER DATABASE statements by using the logical
name RDM$BIND_APF_DEPTH.
You can also specify this option with the DETECTED ASYNC PREFETCH
clause.
3.32 – DETECTED_ASYNC_PREFETCH
Syntax options:
DETECTED ASYNC PREFETCH IS ENABLED | DETECTED ASYNC PREFETCH IS
DISABLED
Specifies whether or not Oracle Rdb reduces the amount of time
that a process waits for pages to be read from disk.
By using heuristics, detected asynchronous prefetch determines if
an I/O pattern is sequential in behavior even if sequential I/O
is not actually executing at the time. For example, when a LIST
OF BYTE VARYING column is fetched, the heuristics detect that
the pages being fetched are sequential and asynchronously fetches
pages further in the sequence. This avoids wait times when the
page is really needed.
Detected asynchronous prefetch is enabled by default.
3.33 – DICTIONARY required option
Syntax options:
DICTIONARY IS REQUIRED | DICTIONARY IS NOT REQUIRED
Specifies whether or not definition statements issued for the
database must also be stored in the repository. If you specify
the REQUIRED option, any data definition statements issued after
an ATTACH or DECLARE ALIAS statement that does not specify the
PATHNAME argument fails.
If you specify the DICTIONARY argument in an ALTER DATABASE
statement, you cannot specify any other database root file or
storage area parameters.
If you omitted the PATHNAME clause from the database root file
parameters in the CREATE DATABASE statement that created the
database, SQL generates an error if you specify DICTIONARY IS
REQUIRED in an ALTER DATABASE statement for the same database.
This is not true if you use the INTEGRATE statement with the
CREATE PATHNAME clause to copy database definitions to the
repository before specifying the DICTIONARY IS REQUIRED clause
in an ALTER DATABASE statement for that database.
3.34 – DICTIONARY used option
Syntax options:
DICTIONARY IS USED | DICTIONARY IS NOT USED
Specifies whether or not to remove the link between the
repository and the database. If you specify the DICTIONARY IS NOT
USED clause, the definitions in both the repository and database
are still maintained. After removing the links, you can integrate
the database to a new repository.
The DICTIONARY IS USED clause is the default.
3.35 – DROP_CACHE
Syntax options:
DROP CACHE row-cache-name CASCADE | DROP CACHE row-cache-name
RESTRICT
Deletes the specified row cache from the database. If the mode is
RESTRICT, then an exeption is raised if the row cache is assigned
to a storage area. If the mode is CASCADE, then the row cache is
removed from all referencing storage areas.
The default is RESTRICT if no mode is specified.
3.36 – DROP_JOURNAL
Deletes the specified journal file from the database.
You can only delete an .aij file that is not current and that has
been backed up.
3.37 – DROP_STORAGE_AREA
Syntax options:
DROP STORAGE AREA area-name CASCADE | DROP STORAGE AREA area-name
RESTRICT
Deletes the specified storage area definition and the associated
storage area and snapshot files. You can use the DROP STORAGE
AREA clause only on multifile databases.
If you use the RESTRICT keyword, you cannot delete a storage area
if any database object, such as a storage map, refers to the area
or if there is data in the storage area.
If you use the CASCADE keyword, Oracle Rdb modifies all objects
that refer to the storage area so that they no longer refer to
it. However, Oracle Rdb does not delete objects if doing so makes
the database inconsistent.
If you use the ALTER DATABASE statement to delete a storage
area, the change is journaled, however, you should back up your
database before making such a change.
3.38 – EXTENT blocks for aij
Specifies the number of blocks of each .aij file extent. The
default and minimum extent for .aij files is 512 blocks.
3.39 – EXTENT clause
Syntax options:
EXTENT ENABLED | EXTENT DISABLED
Enables or disables extents. Extents are ENABLED by default
and can be changed on line; however, the new extents are not
immediately effective on all nodes of a cluster. On the node on
which you have changed extents, the new storage area extents are
immediately effective for all users. The new storage area extents
become effective as the database is attached on each node of the
cluster.
You can encounter performance problems when creating hashed
indexes in storage areas with the mixed page format if the
storage area was created specifying the wrong size for the area
and if extents are enabled. By disabling extents, this problem
can be diagnosed early and corrected to improve performance.
3.40 – EXTENT pages
Syntax options:
EXTENT IS extent-pages PAGES | EXTENT IS (extension-options)
Changes the number of pages of each storage area file extent. See
the description under the SNAPSHOT EXTENT argument.
3.41 – FAST_COMMIT
Syntax options:
FAST COMMIT IS ENABLED | FAST COMMIT IS DISABLED
By default, Oracle Rdb writes updated database pages to the
disk each time a transaction executes the COMMIT statement. If
a transaction fails before committing, Oracle Rdb only needs to
roll back (undo) the current failed transaction; it never has to
redo previous successful transactions.
You can change the commit processing method by enabling journal
fast commit processing. With journal fast commit enabled, Oracle
Rdb keeps updated pages in the buffer pool (in memory) and does
not write the pages to the disk when a transaction commits. The
updated pages remain in the buffer pool until the process meets a
condition specified by the database administrator or applications
programmer. At the moment the condition is met (the checkpoint),
all the pages the process updated for multiple transactions are
written to the disk.
You can set a checkpoint for your process when:
o A fixed number of transactions are committed or aborted. You
set this by specifying CHECKPOINT EVERY n TRANSACTIONS.
o A specified time interval elapsed. You set this by specifying
the CHECKPOINT TIMED EVERY n SECONDS clause.
o The after-image journal (.aij) file increased by a specified
number of blocks. You set this by specifying the CHECKPOINT
INTERVAL IS n BLOCKS clause.
If a transaction fails, Oracle Rdb must undo the current, failed
transaction and redo all the committed transactions since the
last checkpoint. Redoing updates involves reading the .aij file
and reapplying the changes to the relevant data pages.
Fast commit processing applies only to data updates: erase,
modify, and store operations. Transactions that include
data definition statements, such as create logical area or
create index operations, force a checkpoint at the end of the
transaction. If you do not specify values with the FAST COMMIT
clause, the default values are applied.
NOTE
To enable FAST COMMIT, you must first enable after-image
journaling.
3.42 – FILENAME file spec
Identifies the database root file associated with the database.
If you specify a repository path name, the path name indirectly
specifies the database root file. The ALTER DATABASE statement
does not change any definitions in the repository, so there is no
difference in the effect of the PATHNAME and FILENAME arguments.
If you specify PATHNAME, SQL does not use the repository's fully
qualified name. Instead, SQL uses the name stored as the user-
supplied name in the repository. In the following example, SQL
uses the name TEST as the file name, not DB$DISK:[DBDIR]TEST.RDB.
As a result, the database root file must be located in your
present working directory or the database name must be a logical
name when you use the PATHNAME clause.
$ REPOSITORY OPERATOR
.
.
.
CDO> show database/full test
Definition of database TEST
| database uses RDB database TEST
| database in file TEST
| | fully qualified file DB$DISK:[DBDIR]TEST.RDB;
| | user-specified file DB$DISK:[DBDIR]test.rdb
If the database referred to in the PATHNAME or FILENAME argument
has been attached, the ALTER DATABASE statement will fail with a
file access conflict error.
3.43 – FILENAME journal file spec
Specifies the journal file specification with the default file
extension .aij.
3.44 – GALAXY_SUPPORT
Syntax options:
GALAXY SUPPORT IS ENABLED|GALAXY SUPPORT IS DISABLED
Allows global memory to be shared in an OpenVMS Galaxy
configuration. Galaxy support is disabled by default.
OpenVMS Galaxy is a software architecture for the OpenVMS Alpha
operating system that enables multiple instances of OpenVMS to
execute cooperatively in a single computer. An instance refers
to a copy of the OpenVMS Alpha operating system. As an extension
of the existing OpenVMS cluster support within Oracle Rdb, Oracle
Rdb provides support for databases opened on multiple instances
(or nodes) within a Galaxy system to share data structures in
memory. Within an Oracle Rdb Galaxy environment, all instances
with an open database share:
o Database root objects (for example, TSN blocks and SEQ blocks)
o Global buffers (if enabled)
o Row caches and Row Cache Server process (RCS) (if enabled)
3.45 – GLOBAL_BUFFERS
Syntax options:
GLOBAL BUFFERS ARE ENABLED | GLOBAL BUFFERS ARE DISABLED
Specifies whether or not Oracle Rdb maintains one global buffer
pool per VMScluster node for each database. By default, Oracle
Rdb maintains a local buffer pool for each attach (GLOBAL BUFFERS
ARE DISABLED). For more than one attach to use the same page,
each must read it from the disk into their local buffer pool.
A page in the global buffer pool can be read by more than one
attach at the same time, although only one attach reads the page
from the disk into the global buffer pool. Global buffers improve
performance because the I/O is reduced, and memory is better
utilized.
NOTE
If GALAXY SUPPORT is enabled, then a single global buffer
pool is shared by all Galaxy nodes.
3.46 – INCREMENTAL_BACKUP_SCAN_OPTIMIZATION
Syntax options:
INCREMENTAL BACKUP SCAN OPTIMIZATION | NO INCREMENTAL BACKUP SCAN
OPTIMIZATION
Specifies whether Oracle Rdb checks each area's SPAM pages or
each database page to find changes during incremental backup.
If you specify INCREMENTAL BACKUP SCAN OPTIMIZATION, Oracle Rdb
checks each area's SPAM pages and scans the SPAM interval of
pages only if the SPAM transaction number (TSN) is higher than
the last full backup TSN, which indicates that a page in the SPAM
interval has been updated since the last full backup operation.
Specify INCREMENTAL BACKUP SCAN OPTIMIZATION if your database has
large SPAM intervals or infrequently occurring updates, and you
want to increase the speed of incremental backups. If you disable
the attribute (using the NO INCREMENTAL BACKUP SCAN OPTIMIZATION
clause), you cannot enable it until immediately after the next
full backup.
If you specify NO INCREMENTAL BACKUP SCAN OPTIMIZATION, Oracle
Rdb checks each page to find changes during incremental backup.
Specify the NO INCREMENTAL BACKUP SCAN OPTIMIZATION clause if
your database has frequently occurring updates, uses bulk-load
operations, or does not use incremental backups, or if you want
to improve run-time performance.
The default is INCREMENTAL BACKUP SCAN OPTIMIZATION.
3.47 – JOURNAL clause for aij
Syntax options:
JOURNAL IS ENABLED | JOURNAL IS DISABLED
Specifies whether or not journaling is enabled.
If journal files already exist, the JOURNAL IS ENABLED clause
simply restarts the journaling feature.
If no journal files exist when the ALTER DATABASE . . . JOURNAL IS
ENABLED statement completes, an exception is raised. For example:
SQL> ALTER DATABASE FILENAME sample
cont> JOURNAL IS ENABLED;
%RDMS-F-NOAIJENB, cannot enable after-image journaling without any AIJ journals
Use the ADD JOURNAL clause to create journal files.
The ENABLED option can be followed by a list of database journal
options.
All journal files remain unchanged but become inaccessible when
you disable them. You cannot specify database journal options
with the DISABLED option.
3.48 – JOURNAL_IS_UNSUPPRESSED
If a journal file becomes inaccessible, it is disabled by the
journaling system. It remains in that state until you correct the
problem and manually unsuppress that journal file.
3.49 – literal-user-auth
Specifies the user name and password for access to databases,
particularly remote database.
This literal lets you explicitly provide user name and password
information in the ALTER DATABASE statement.
3.50 – LOCATION IS directory-spec
Specifies the name of the default directory to which row
cache backing file information is written. The database system
generates a file name (row-cache-name.rdc) automatically for each
row cache backing file it creates when the RCS process starts up.
Specify a device name and directory name enclosed within single
quotation marks ('); do not include a file specification. The
file name is the row-cache-name specified when creating the row
cache. By default, the location is the directory of the database
root file.
The LOCATION clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this location, which is the default for
the database.
This clause is ignored if the row cache is defined to checkpoint
to the database.
3.51 – LOCK_PARTITIONING
Syntax options:
LOCK PARTITIONING IS ENABLED | LOCK PARTITIONING IS DISABLED
Specifies whether more than one lock tree is used for the
database or all lock trees for a database are mastered by one
database resource tree.
When partitioned lock trees are enabled for a database, locks for
storage areas are separated from the database resource tree and
all locks for each storage area are independently mastered on the
VMScluster node that has the highest traffic for that resource.
OpenVMS determines the node that is using each resource the most
and moves the resource hierarchy to that node.
You cannot enable lock partitioning for single-file databases.
You should not enable lock partitioning for single-node systems,
because all lock requests are local on single-node systems.
By default, lock partitioning is disabled.
3.52 – LOCK_TIMEOUT_INTERVAL
Syntax option:
LOCK TIMEOUT INTERVAL IS number-seconds SECONDS
Specifies the number of seconds for processes to wait during a
lock conflict before timing out. The number can be between 1 and
65,000 seconds.
Specifying 0 is interpreted as no lock timeout interval being
set. It is not interpreted as 0 seconds.
The lock timeout interval is database-wide; it is used as
the default and the upper limit when determining the timeout
interval. For example, if the database definer specified LOCK
TIMEOUT INTERVAL IS 25 SECONDS in the ALTER DATABASE statement,
and a user of that database specified SET TRANSACTION WAIT 30 or
changed the logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL to 30,
SQL still uses the interval 25. For more information on timeout
intervals, see the Oracle Rdb7 Guide to Distributed Transactions.
3.53 – LOCKING level
Syntax options:
LOCKING IS ROW LEVEL | LOCKING IS PAGE LEVEL
Specifies if locking is at the page or row level. This clause
provides an alternative to requesting locks on records. The
default is ROW LEVEL.
When many records are accessed in the same area and on the same
page, the LOCKING IS PAGE LEVEL clause reduces the number of lock
operations perfomed to process a transaction; however, this is at
the expense of reduced concurrency because these page locks are
held until COMMIT/ROLLBACK time. Transactions that benefit most
with page-level locking are of short duration and also access
several database records on the same page.
Use the LOCKING IS ROW LEVEL clause if transactions are long in
duration and lock many rows.
The LOCKING IS PAGE LEVEL clause causes fewer blocking
asynchronous system traps and provides better response time
and utilization of system resources. However, there is a higher
contention for pages and increased potential for deadlocks and
long transactions may use excessive locks.
Page-level locking is never applied to RDB$SYSTEM or the DEFAULT
storage area, either implicitly or explicitly, because the
locking protocol can stall metadata users.
You cannot specify page-level locking on single-file databases.
3.54 – LOG_SERVER
Syntax options:
LOG SERVER IS MANUAL | LOG SERVER IS AUTOMATIC
Specifies if the AIJ log server (ALS) is activated manually or
automatically. The default is manual.
Multiple-user databases with medium to high update activity
can experience after-image journal (.aij) file bottlenecks.
To alleviate these bottlenecks, you can specify the LOG SERVER
clause to transfer log data to the .aij file either automatically
or manually. On a single node with ALS, there is no AIJ locking.
If the log server is set to MANUAL, you must execute the RMU
Server After_Journal command with the Start qualifier to start
the log server. In this case, the database must already be open.
If the OPEN IS MANUAL clause was specified, an explicit RMU Open
command needs to be executed before the log server is started.
If the OPEN IS AUTOMATIC clause was specified, at least one
user should be attached to the database before the log server
is started.
If the log server is set to AUTOMATIC, the log server starts when
the database is opened, automatically or manually, and is shut
down when the database is closed.
For more information on setting log servers, see the Oracle Rdb7
Guide to Database Performance and Tuning.
3.55 – LOGMINER_SUPPORT
Syntax options:
LOGMINER SUPPORT IS ENABLED|LOGMINER SUPPORT IS DISABLED
Allows additional information to be written to the after-image
journal file to allow the use of the RMU Unload After_Image
command. See the Oracle RMU Reference Manual for more details.
Logminer support is disabled by default.
The LOGMINER SUPPORT clause allows the continuous mode for
LogMiner to be enabled and disabled.
o LOGMINER SUPPORT IS ENABLED (CONTINUOUS)
Enables continuous LogMiner.
o LOGMINER SUPPORT IS ENABLED (NOT CONTINUOUS)
Disables continuous LogMiner, but leaves LogMiner enabled.
o LOGMINER SUPPORT IS DISABLED
Disables LogMiner, including disabling continuous LogMiner.
3.56 – MAXIMUM_BUFFER_COUNT
Syntax option:
MAXIMUM BUFFER COUNT IS buffer-count
Specifies the number of buffers a process will write
asynchronously.
The default is one-fifth of the buffer pool, but not more than 10
buffers. The minimum value is 2 buffers; the maximum value can be
as large as the buffer pool.
You can override the number of buffers to be written
asynchronously by defining the logical name RDM$BIND_BATCH_MAX.
For information about how to set the values, see the Oracle Rdb7
Guide to Database Performance and Tuning.
3.57 – MAXIMUM_PAGES
Syntax option:
MAXIMUM OF max-pages PAGES
Specifies the maximum number of pages of each extent. The default
is 9999 pages.
3.58 – METADATA_CHANGES
Syntax options:
METADATA CHANGES ARE ENABLED | METADATA CHANGES ARE DISABLED
Specifies whether or not data definition changes are allowed
to the database. This attribute becomes effective at the
next database attach and affects all ALTER, CREATE, and DROP
statements (except ALTER DATABASE which is needed for database
tuning) and the GRANT, REVOKE, and TRUNCATE TABLE statements. For
example:
SQL> CREATE DATABASE FILENAME sample;
SQL> CREATE TABLE t (a INTEGER);
SQL> DISCONNECT ALL;
SQL> ALTER DATABASE FILENAME sample
cont> METADATA CHANGES ARE DISABLED;
SQL> ATTACH 'FILENAME sample';
SQL> CREATE TABLE s (b INTEGER);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-NOMETADATA, metadata operations are disabled
The METADATA CHANGES ARE DISABLED clause prevents data definition
changes to the database.
The METADATA CHANGES ARE ENABLED clause allows data definition
changes to the database by users granted the DBADMIN privilege.
METADATA CHANGES ARE ENABLED is the default.
3.59 – MINIMUM_PAGES
Syntax option:
MINIMUM OF min-pages PAGES
Specifies the minimum number of pages of each extent. The default
is 99 pages.
3.60 – MULTISCHEMA
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
Specifies the multischema attribute for the database. If a
database has the multischema attribute, you can create multiple
schemas in that database and group them within catalogs. The
MULTISCHEMA IS ON option is the default for databases created
with the multischema attribute. MULTISCHEMA IS OFF is the default
for databases created without the multischema attribute.
You can create a database using the CREATE DATABASE MULTISCHEMA
IS ON clause, but you cannot use ALTER DATABASE MULTISCHEMA IS
OFF to take away the multischema attribute. Once a database has
the multischema attribute, you cannot change it.
3.61 – NO_BACKUP_FILENAME
Removes a previously established backup file specification.
3.62 – NO_LOCATION
This is a subclause of other clauses and has different effects,
depending upon the clause in which it is used, as follows:
o In the row-cache-options clause
Removes the location previously specified in a LOCATION IS
clause for the row cache. If you specify NO LOCATION, the
row cache location becomes the directory of the database root
file.
The LOCATION clause of the CREATE CACHE, ADD CACHE, or ALTER
CACHE clause overrides this location, which is the default for
the database.
o In a CREATE CACHE, ADD CACHE, or ALTER CACHE clause (row-
cache-params1 clause)
Removes the location previously specified in a LOCATION IS
clause for the row cache backing file. If you specify NO
LOCATION, the row cache location becomes the directory of
the database root file.
This clause is ignored if the row cache is defined to
checkpoint to the database.
3.63 – NO_ROW_CACHE
Specifies that the database default is to not assign a row cache
to all storage areas in the database. You cannot specify the NO
ROW CACHE clause if you specify the CACHE USING clause.
Alter the storage area and name a row cache to override the
database default. Only one row cache is allowed for each storage
area.
If you do not specify the NO ROW CACHE clause or the CACHE USING
clause, then the NO ROW CACHE clause is the default.
3.64 – NO_SWEEP_INTERVAL
NO SWEEP INTERVAL disables periodic timed sweeps.
3.65 – NOTIFY
Syntax options:
NOTIFY IS ENABLED | NOTIFY IS DISABLED
Specifies whether system notification is enabled or disabled.
When the system notification is enabled, the system is notified
(using the OpenVMS OPCOM facility) in the event of events such as
running out of disk space for a journal.
If you specify the NOTIFY IS ENABLED clause and do not specify
the ALERT OPERATOR clause, the operator classes used are CENTRAL
and CLUSTER. To specify other operator classes, use the ALERT
OPERATOR clause.
The NOTIFY IS ENABLED clause replaces any operator classes set by
the RMU Set After_Journal Notify command.
The default is disabled.
3.66 – NUMBER global buffers
Syntax option:
NUMBER IS number-glo-buffers
Specifies the total number of buffers in the global buffer pool.
This number appears as "global buffer count" in RMU Dump command
output. Base this value on the database users' needs and the
number of attachments. The default is the maximum number of
attachments multiplied by 5.
NOTE
Do not confuse the NUMBER IS parameter with the NUMBER OF
BUFFERS IS parameter. The NUMBER OF BUFFERS IS parameter
determines the default number of buffers Oracle Rdb
allocates to each user's process that attaches to the
database. The NUMBER OF BUFFERS IS parameter applies to,
and has the same meaning for, local and global buffering.
The NUMBER IS parameter has meaning only within the context
of global buffering.
You can override the default number of user-allocated buffers by
defining a value for the logical name RDM$BIND_BUFFERS. For more
information on user-allocated buffers, see Oracle Rdb7 Guide to
Database Performance and Tuning.
Although you can change the NUMBER IS parameter on line, the
change does not take effect until the next time the database is
opened.
3.67 – NUMBER_OF_BUFFERS
Syntax option:
NUMBER OF BUFFERS IS number-buffers
The number of buffers SQL allocates for each process using this
database. Specify an unsigned integer with a value greater than
or equal to 2 and less than or equal to 32,767. The default is 20
buffers.
3.68 – NUMBER_OF_CLUSTER_NODES
Syntax option:
NUMBER OF CLUSTER NODES IS number-nodes
Sets the upper limit on the maximum number of VMS cluster nodes
from which users can access the shared database. Specify this
clause only if the database named in the ALTER DATABASE statement
refers to a multifile database. The default is 16 nodes. The
range is 1 to 96 nodes. The actual maximum limit is the current
VMS cluster node limit set by your system administrator.
The Oracle Rdb root file data structures (.rdb) are mapped to
shared memory, each such shared memory copy is known as an Rdb
instance. When there is only one copy of shared memory containing
root file information, several optimizations are enabled to
reduce locking and root file I/O. activity. Specify NUMBER OF
CLUSTER NODES is set to 1, or use the SINGLE INSTANCE clause to
enable these optimizations.
MULTIPLE INSTANCE means that the Oracle Rdb root file data
structures are mapped on different system and are kept consistent
through disk I/O. Such systems can not benefit from single
instance optimizations. MULTIPLE INSTANCE is the default.
3.69 – NUMBER_OF_RECOVERY_BUFFERS
Syntax option:
NUMBER OF RECOVERY BUFFERS IS number-buffers
Specifies the number of buffers allocated to the automatic
recovery process that Oracle Rdb initiates after a system or
process failure. This recovery process uses the recovery-unit
journal file (.ruj file extension).
You can specify any number greater than or equal to 2 and less
than or equal to 32,767. The default value for the NUMBER OF
RECOVERY BUFFERS parameter is 20. If you have a large, multifile
database and you work on a system with a large amount of memory,
specify a large number of buffers. The result is faster recovery
time. However, make sure your buffer pool does not exceed the
amount of memory you can allocate for the pool.
Use the NUMBER OF RECOVERY BUFFERS option to increase the number
of buffers allocated to the recovery process.
SQL> ALTER DATABASE FILENAME personnel
cont> NUMBER OF RECOVERY BUFFERS IS 150;
This option is used only if the NUMBER OF RECOVERY BUFFERS value
is larger than the NUMBER OF BUFFERS value. For more information
on allocating recovery buffers, see the Oracle Rdb Guide to
Database Maintenance.
3.70 – NUMBER_OF_SWEEP_ROWS
Syntax option:
NUMBER OF SWEEP ROWS IS n
Specifies the number of modified rows that will be written from
the row cache back to the database by the row cache server (RCS)
process during a sweep operation. When the RCS is notified that
a cache is "full" of modified data, the RCS starts a sweep to
make space available in the cache for subsequent transactions
to be able to insert rows into the cache. Oracle Corporation
recommends that you initially specify the number of sweep rows
to be approximately 5 percent of the total number of rows in
the cache. Then monitor performance and adjust the number of
sweep rows, if necessary. Allowable values must be in the range 2
through 524288. If not specified, the default is 3,000 rows.
3.71 – NUMBER_OF_USERS
Syntax option:
NUMBER OF USERS IS number-users
Limits the maximum number of users allowed to access the database
at one time. Specify this clause only if the database named in
the ALTER DATABASE statement refers to a multifile database.
The default is 50 users. After the maximum is reached, the next
user who tries to invoke the database receives an error message
and must wait. The maximum number of users you can specify is
16368 and the minimum is 1 user.
Note that number of users is defined as the number of active
attachments to the database. Therefore, if a single process
is running one program but that program performs 12 attach
operations, the process is responsible for 12 active users.
If you use the ALTER DATABASE statement to change the current
number of users, the change is not journaled. Therefore, back up
your database before making such a change.
3.72 – OPEN
Syntax options:
OPEN IS AUTOMATIC | OPEN IS MANUAL
Specifies whether or not the database must be explicitly opened
before users can attach to it. The default, OPEN IS AUTOMATIC,
means that any user can open a previously unopened or a closed
database by attaching to it and executing a statement. The
OPEN IS MANUAL option means that a privileged user must issue
an explicit OPEN statement through Oracle RMU, the Oracle
Rdb management utility, before other users can attach to the
database.
To issue the RMU Open command, you must have the RMU$OPEN
privilege for the database.
The OPEN IS MANUAL option limits access to databases.
You will receive an error message if you specify both OPEN IS
AUTOMATIC and OPEN IS MANUAL options.
3.73 – OVERWRITE
Syntax options:
OVERWRITE IS ENABLED | OVERWRITE IS DISABLED
Specifies whether the overwrite option is enabled or disabled.
After-image journal files are used for database recovery in case
of media failure and for transaction recovery as part of the
fast commit feature. In some environments, only the fast commit
feature is of interest and a small set of journal files can be
used as a circular fast commit log with no backup of the contents
required. The OVERWRITE option instructs Oracle Rdb to write over
journal records that would normally be used for media recovery.
The resulting set of journal files is unable to be used by the
RMU Recover command for media recovery.
The OVERWRITE option is ignored when only one after-image journal
(.aij) file exists. Adding subsequent journal files activates the
OVERWRITE option.
The default is DISABLED.
3.74 – PAGE_TRANSFER
Syntax options:
PAGE TRANSFER VIA DISK | PAGE TRANSFER VIA MEMORY
Specifies whether Oracle Rdb transfers (flushes) pages to disk or
to memory.
When you specify PAGE TRANSFER VIA MEMORY, processes on a single
node can share and update database pages in memory without
transferring the pages to disk. It is not necessary for a process
to write a modified page to disk before another process accesses
the page.
The default is to DISK. If you specify PAGE TRANSFER VIA MEMORY,
the database must have the following characteristics:
o The NUMBER OF NODES must be one, or SINGLE INSTANCE must be
specified in the NUMBER OF CLUSTER NODES clause.
o GLOBAL BUFFERS must be enabled.
o After-image journaling must be enabled.
o FAST COMMIT must be enabled.
If the database does not have these characteristics, Oracle Rdb
will perform page transfers via disk.
For more information about page transfers, see the Oracle Rdb7
Guide to Database Performance and Tuning.
3.75 – PATHNAME path name
Identifies the database root file associated with the database.
If you specify a repository path name, the path name indirectly
specifies the database root file. The ALTER DATABASE statement
does not change any definitions in the repository, so there is no
difference in the effect of the PATHNAME and FILENAME arguments.
If the database referred to in the PATHNAME or FILENAME argument
has been attached, the ALTER DATABASE statement will fail with a
file access conflict error.
3.76 – PERCENT_GROWTH
Syntax option:
PERCENT GROWTH IS growth
Specifies the percent growth of each extent. The default is 20
percent growth.
3.77 – PRESTARTED_TRANSACTIONS
Syntax options:
PRESTARTED TRANSACTIONS ARE ENABLED (prestart-trans-options)
PRESTARTED TRANSACTIONS ARE DISABLED
Enables or disables the prestarting of transactions.
Note that the keyword OFF, available in previous versions, is
synonymous with DISABLED.
3.78 – READ_WRITE,READ_ONLY
The READ options of the alter-storage-area-params clause permit
you to change existing storage area access as follows:
o Select the READ WRITE option to change any storage area to
read/write access.
o Select the READ ONLY option to change any storage area to
read-only access.
If you want to change the read-only and read/write parameters of
the RDB$SYSTEM storage area, you must specify these parameters at
this point of your ALTER DATABASE statement and not in the ALTER
STORAGE AREA clause. For example:
SQL> -- You can change the RDB$SYSTEM storage area by altering
SQL> -- the database.
SQL> --
SQL> ALTER DATABASE FILENAME mf_personnel
cont> READ ONLY;
SQL> --
SQL> -- An error is returned if you try to change the RDB$SYSTEM storage
SQL> -- area to read-only using the ALTER STORAGE AREA clause.
SQL> --
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER STORAGE AREA RDB$SYSTEM
cont> READ ONLY;
%RDB-E-BAD_DPB_CONTENT, invalid database parameters in the database
parameter block (DPB)
-RDMS-E-NOCHGRDBSYS, cannot change RDB$SYSTEM storage area explicitly
SQL provides support for read-only databases and databases with
one or more read-only storage areas.
You can take advantage of read-only support if you have a
stable body of data that is never (or rarely) updated. When the
RDB$SYSTEM storage area is changed to read-only, lock conflicts
occur less frequently, and the automatic updating of index and
table cardinality is inhibited.
Read-only databases consist of:
o A read/write database root file
o One or more read-only storage areas and no read/write storage
areas
Read-only databases can be published and distributed on CD-ROM.
Read-only storage areas:
o Have snapshot files but do not use them. (Data in a read-only
storage area is not updated; specify a small number for the
initial snapshot file size for a read-only storage area.)
o Eliminate page and record locking in the read-only storage
areas.
o Are backed up by the RMU Backup command by default unless you
explicitly state the Noread_Only qualifier, which excludes
read-only areas without naming them.
o Are restored by the RMU Restore command if they were
previously backed up.
o Are recovered by the RMU Recover command. However, unless the
read-only attribute was modified, the read-only area does not
change.
o Are not recovered by the RMU Recover command with the Area=*
qualifier, in which you are not explicitly naming the areas
needing recovery, unless they are inconsistent.
You use the READ ONLY option to change a storage area from
read/write to read-only access. If you wanted to facilitate
batch-update transactions to infrequently changed data, you would
use the READ WRITE option to change a read-only storage area back
to read/write.
If you change a read/write storage area to read-only, you cannot
specify the EXTENT, SNAPSHOT ALLOCATION, and SNAPSHOT EXTENT
clauses.
A database with both read/write and read-only storage areas can
be fully recovered after a system failure only if after-image
journaling is enabled on the database. If your database has
both read/write and read-only storage areas but does not have
after-image journaling enabled, perform full backup operations
(including read-only areas) at all times. Doing full backup
operations enables you to recover the entire database to its
condition at the time of the previous backup operation.
For a complete description of read-only databases and read-only
storage areas, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
3.79 – RECOVERY_JOURNAL_(BUFFER_MEMORY)
Syntax options:
BUFFER MEMORY IS LOCAL
BUFFER MEMORY IS GLOBAL
Specifies whether RUJ buffers will be allocated in global or
local memory.
The RUJ buffers used by each process are normally allocated in
local virtual memory. With the introduction of row caching, these
buffers now can be assigned to a shared global section (global
memory) on OpenVMS, so that the recovery process can process this
in-memory buffer and possibly avoid a disk access.
You can define this buffer memory to be global to improve row
caching performance for recovery. If row caching is disabled,
then buffer memory is always local.
3.80 – RECOVERY_JOURNAL_(LOCATION)
Syntax options:
RECOVERY JOURNAL (LOCATION IS directory-spec)
Specifies the location, including device and directory, in which
the recovery-unit journal (.ruj) file is written. Do not include
network node names, file names or process-concealed logical
names. The default is the current user's login device.
See the Oracle Rdb Guide to Database Maintenance for more
information on recovery-unit journal files.
Following is an example using this clause:
SQL> ALTER DATABASE FILENAME SAMPLE
cont> RECOVERY JOURNAL (LOCATION IS 'SQL_USER1:[DBDIR.RECOVER]');
3.81 – RECOVERY_JOURNAL_(NO_LOCATION)
Removes a location previously defined by a RECOVERY JOURNAL
LOCATION IS clause. This causes the recovery journal to revert
to the default location.
3.82 – RESERVE n CACHE SLOTS
Specifies the number of row caches for which slots are reserved
in the database.
You can use the RESERVE CACHE SLOTS clause to reserve slots in
the database root file for furture use by the ADD CACHE clause.
You can only add row caches if row cache slots are available.
Slots become available after you issue a DROP CACHE clause or a
RESERVE CACHE SLOTS clause.
You cannot reduce the number of reserved slots for row caching.
If you reserve 10 slots and later reserve 5 slots, a total of 15
slots are reserved for row caches.
3.83 – RESERVE n JOURNALS
Specifies the number of journal files for which slots are to
reserve in the database. The number of slots for journal files
must be a positive number greater than zero.
This feature is additive in nature. In other words, the number
of reserved slots for journal files cannot be decreased once
the RESERVE clause has been issued. If you reserve 10 slots and
later reserve 5 slots, you have a total of 15 reserved slots for
journal files plus 1 slot (totaling 16 reserved slots) because
you initially get 1 pre-reserved slot.
You must reserve slots or delete an existing journal file before
you can add new journal files to the database.
You cannot reserve journal files for a single-file database.
3.84 – RESERVE n SEQUENCES
Specifies the number of sequences for which slots are reserved in
the database. Sequences are reserved in multiples of 32. Thus, if
you specify a value less than 32 for n, 32 slots are reserved. If
you specify a value of 33, 64 slots are reserved, and so on.
You can use the RESERVE SEQUENCES clause to reserve slots in
the database root file for future use by the CREATE SEQUENCE
statement. Sequences can be created only if sequence slots are
available. Slots become available after a DROP SEQUENCE statement
or a RESERVE SEQUENCES clause of the ALTER DATABASE statement is
executed.
3.85 – RESERVE n STORAGE AREAS
Specifies the number of storage areas for which slots are to
reserve in the database. The number of slots for storage areas
must be a positive number greater than zero.
You can use the RESERVE STORAGE AREA clause to reserve slots
in the database root file for future use by the ADD STORAGE
AREA clause of the ALTER DATABASE statement. Storage areas can
be added only if there are storage area slots available. Slots
become available after a DROP STORAGE AREA clause or a RESERVE
STORAGE AREA clause is issued.
This feature is additive in nature. In other words, the number
of reserved slots for storage areas cannot be decreased once
the RESERVE clause is issued. If you reserve 10 slots and later
reserve 5 slots, you have a total of 15 reserved slots for
storage areas.
You must reserve slots or delete an existing storage area before
you can add new storage areas to the database.
If you do not specify the RESERVE STORAGE AREA clause, the
default number of reserved storage areas is zero.
3.86 – ROW_CACHE
Syntax options:
ROW CACHE IS ENABLED | ROW CACHE IS DISABLED
Specifies whether or not the row caching feature is enabled.
Enabling row caching does not affect database operations until a
cache is created and assigned to one or more storage areas.
When row caching is disabled, all previously created and assigned
caches remain and will be available if row caching is enabled
again.
The following conditions must be true in order to use row caches:
o The number of cluster nodes is one
o After-image journaling is enabled
o Fast commit is enabled
o One or more cache slots are reserved
o Row caching is enabled
Use the RMU Dump Header command to check if you have met the
requirements for using row caches. The following command output
displays a warning for every requirement that is not met:
.
.
.
Row Caches...
- Active row cache count is 0
- Reserved row cache count is 1
- Sweep interval is 1 second
- Default cache file directory is ""
- WARNING: Maximum node count is 16 instead of 1
- WARNING: After-image journaling is disabled
- WARNING: Fast commit is disabled
.
.
3.87 – SAME_BACKUP_FILENAME_AS_JOURNAL
During execution, the backup server assigns the same name to the
backup file as it does to the journal file. This is a quick form
of backup as a new file is created.
NOTE
Oracle Corporation recommends that you save the old journal
file on tape or other media to prevent accidental purging of
these files.
3.88 – SECURITY_CHECKING
Traditionally Oracle Rdb has performed security checking using
the operating system security layer (for example, the UIC and
rights identifiers of the OpenVMS operating system).
The access control list (ACL) information stored in the database
contains a granted privilege mask and a set of users represented
by a unique integer (for example, a UIC).
There are two modes of security checking:
1. SECURITY CHECKING IS EXTERNAL
This is the default. External security checking recognizes
database users as operating system user identification codes
(UICs) and roles as special rights identifiers or groups.
PERSONA support is enabled or disabled as follows:
o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS ENABLED)
Enables the full impersonation of an OpenVMS user. This
means the UIC and the granted right identifiers are used to
check access control list permissions.
o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS DISABLED)
Disables the full impersonation of an OpenVMS user. Only
the UIC is used to check access control list permissions.
This is the default for a new database, or for a database
converted from a prior version of Oracle Rdb.
2. SECURITY CHECKING IS INTERNAL
In this mode, Oracle Rdb records users (username and UIC) and
roles (rights identifiers) in the database. The CREATE USER
and CREATE ROLE statements perform this action explicitly, and
GRANT will perform this implicitly. This type of database can
now be moved to another system and is only dependent on the
names of the users and roles.
o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS ENABLED)
The ACCOUNT CHECK clause ensures that Oracle Rdb validates
the current database user with the user name (such as
defined with a CREATE USER statement) stored in the
database. This prevents different users with the same name
from accessing the database. Therefore, this clause might
prevent a breach in security.
The ACCOUNT CHECK IS ENABLED clause on OpenVMS forces the
user session to have the same user name and UIC as recorded
in the database.
o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS DISABLED)
If you specify the ACCOUNT CHECK IS DISABLED clause, then
a user with a matching UIC (also called a profile-id) is
considered the same as the user even if his or her user
name is different. This allows support for multiple OpenVMS
users with the same UIC.
3.89 – SET_TRANSACTION_MODES
Enables only the modes specified, disabling all other previously
defined modes. This is an offline operation and requires
exclusive database access. For example, if a database is used for
read-only access and you want to disable all other transaction
modes, specify the following statement:
SQL> ALTER DATABASE FILENAME mf_personnel
cont> SET TRANSACTION MODES (READ ONLY);
Specifying a negated txn-mode or specifying NONE disables all
transaction usage. Disabling all transaction usage would be
useful when, for example, you want to perform major restructuring
of the physical database. Execute the ALTER DATABASE statement to
re-enable transaction modes.
3.90 – SHARED_MEMORY
Syntax options:
SHARED MEMORY IS SYSTEM
SHARED MEMORY IS PROCESS
SHARED MEMORY IS PROCESS RESIDENT
Determines whether database root global sections (including global
buffers when enabled) are created in system space or process space.
The default is PROCESS.
When you use global sections created in the process space, you
and other users share physical memory and the OpenVMS operating
system maps a row cache to a private address space for each user.
As a result, all users are limited by the free virtual address
range and each use a percentage of memory in overhead. If many
users are accessing the database, the overhead can be high.
When many users are accessing the database, consider using SHARED
MEMORY IS SYSTEM. This gives users more physical memory because
they share the system space of memory and there is none of the
overhead associated with the process space of memory.
The default is SHARED MEMORY IS PROCESS.
When you use this clause as a cache attribute, it controls
whether Oracle Rdb creates cache global sections in system space
or process space. The default is PROCESS.
The SHARED MEMORY clause determines whether database root global
sections (including global buffers when enabled) or whether the
cache global sections are created in system space or process
space. The RESIDENT option extends the PROCESS option by making
the global section memory resident.
To enable or disable SHARED MEMORY IS PROCESS RESIDENT, the
process executing the command must be granted the VMS$MEM_
RESIDENT_USER rights identifier. When this feature is enabled,
the process that opens the database must also be granted the
VMS$MEM_RESIDENT_USER rights identifier. Oracle Corporation
recommends using the RMU Open command when utilizing this
feature.
3.91 – SHUTDOWN_TIME
Syntax option:
SHUTDOWN TIME IS n MINUTES
Specifies the number of minutes the database system will wait
after a catastrophic event before it shuts down the database.
The shutdown time is the period, in minutes, between the point
when the after-image journaling subsystem becomes unavailable
and the point when the database is shut down. During the after-
image journaling shutdown period, all database update activity is
stalled.
If notification is enabled with the NOTIFY IS clause, operator
messages will be broadcast to all enabled operator classes.
To recover from the after-image journaling shutdown state and
to resume normal database operations, you must make an .aij file
available for use. You can do this by backing up an existing
modified journal file, or, if you have a journal file reservation
available, by adding a new journal file to the after-image
journaling subsystem. If you do not make a journal file available
before the after-image journal shutdown time expires, the
database will be shut down and all active database attachments
will be terminated.
The after-image journaling shutdown period is only in effect when
a fixed-size .aij file is used. When a single extensible .aij
file is used, the default action is to shut down all database
operations when the .aij file becomes unavailable.
The default is 60 minutes. The minimum value is 1 minute; the
maximum value is 4320 minutes (3 days).
3.92 – SNAPSHOT_ALLOCATION
Syntax option:
SNAPSHOT ALLOCATION IS snp-pages PAGES
Changes the number of pages allocated for the snapshot file. The
default is 100 pages. If you have disabled the snapshot file, you
can set the snapshot allocation to 0 pages.
3.93 – SNAPSHOT_EXTENT
Syntax options:
SNAPSHOT EXTENT IS extent-pages PAGES | SNAPSHOT EXTENT IS
(extension-options)
Changes the number of pages of each snapshot or storage area file
extent. The default extent for storage area files is 100 pages.
Specify a number of pages for simple control over the file
extent. For greater control, and particularly for multivolume
databases, use the MINIMUM, MAXIMUM, and PERCENT GROWTH extension
options instead.
If you use the MINIMUM, MAXIMUM, and PERCENT GROWTH parameters,
you must enclose them in parentheses.
3.94 – SNAPSHOT_IS_ENABLED
Syntax options:
SNAPSHOT IS IMMEDIATE | SNAPSHOT IS DEFERRED
Specifies when read/write transactions write database changes to
the snapshot file used by read-only transactions.
The ENABLED IMMEDIATE option is the default and causes read/write
transactions to write copies of rows they modify to the snapshot
file, regardless of whether or not a read-only transaction is
active. Although ENABLED IMMEDIATE is the default, if you set
snapshots ENABLED DEFERRED, you must specify both ENABLED and
IMMEDIATE options to return the database to the default setting.
The ENABLED DEFERRED option lets read/write transactions
avoid writing copies of rows they modify to the snapshot file
(unless a read-only transaction is already active). Deferring
snapshot writing in this manner improves the performance for
the read/write transaction. However, read-only transactions that
start after an active read/write transaction starts must wait for
all active read/write users to complete their transactions.
3.95 – SNAPSHOT_IS_DISABLED
Specifies that snapshot writing be disabled. Snapshot writing is
enabled by default.
In this mode any READ ONLY transaction will be converted to READ
WRITE mode automatically.
3.96 – STATISTICS_COLLECTION
Syntax options:
STATISTICS COLLECTION IS ENABLED | STATISTICS COLLECTION IS
DISABLED
Specifies whether the collection of statistics for the database
is enabled or disabled. When you disable statistics for the
database, statistics are not displayed for any of the processes
attached to the database. Statistics are displayed using the RMU
Show Statistics command.
The default is STATISTICS COLLECTION IS ENABLED. You can disable
statistics using the ALTER DATABASE and IMPORT statements.
For more information on the RMU Show Statistics command, see the
Oracle RMU Reference Manual.
You can enable statistics collection by defining the logical
name RDM$BIND_STATS_ENABLED. For more information about when to
use statistics collection, see the Oracle Rdb7 Guide to Database
Performance and Tuning.
3.97 – storage-area-params
Parameters that control the characteristics of the storage area.
For more information on the parameters, see the CREATE STORAGE_
AREA statement.
3.98 – SWEEP_INTERVAL
Syntax option:
SWEEP INTERVAL IS n SECONDS
Specifies the interval, in seconds, between each Record Cache
Server (RCS) sweep. Allowable values must be in the range from 1
second to 3600 seconds (1 hour). The default is 1.
The Record Cache Server (RCS) is a detached server process
automatically invoked by the monitor when row caching is active.
A sweep is one full pass through all active row cache areas to
write modified rows back to the database storage areas.
3.99 – SYNONYMS_ARE_ENABLED
Adds the optional system table RDB$OBJECT_SYNONYMS that is
used for the CREATE SYNONYM, ALTER . . . RENAME TO and RENAME
statements. The default if omitted is disabled.
3.100 – THRESHOLD buffers option
Syntax option:
THRESHOLD IS number-buffers PAGES
This number represents the number of sequential buffer accesses
that must be detected before prefetching is started. The default
is four buffers.
If you specify the THRESHOLD option, you must have also specified
the DETECTED ASYNC PREFETCH clause. You receive an error if you
attempt to specify the THRESHOLD option with the ASYNC PREFETCH
clause.
3.101 – TRANSACTION_INTERVAL
Syntax option:
TRANSACTION INTERVAL IS number-txns
The TRANSACTION INTERVAL IS clause specifies the size of the
transaction sequence number (TSN) range where number-txns equals
the number of TSNs. Oracle Rdb uses transaction sequence numbers
to ensure database integrity. When you specify NO COMMIT TO
JOURNAL OPTIMIZATION, Oracle Rdb assigns TSNs to users one at
a time. When you enable the journal optimization option, Oracle
Rdb preassigns a range of TSNs to each user. Assigning a range
of TSNs means that commit information need not be written to
the database root for each transaction. Oracle Rdb writes all
transaction information to the .aij file except for each user's
allocated TSN range, which it writes to the root file.
The transaction interval value (the TSN range) must be a number
between 8 and 1024. The default value is 256.
In general, if your database has few users or if all user
sessions are long, select a large transaction interval. If your
database has many users or if user sessions are short, select a
smaller transaction interval.
3.102 – txn-modes
Specifies the transaction modes for the database.
Mode Description
Transaction Types
[NO]READ Allows read-only transactions on the database.
ONLY
[NO]READ Allows read/write transactions on the database.
WRITE
[NO] BATCH Allows batch-update transactions on the database.
UPDATE This mode executes without the overhead, or
security, or a recovery-unit journal file.
The batch-update transaction is intended for
the initial loading of a database. Oracle Rdb
recommends that this mode be disabled.
Reserving Modes
[NO] SHARED Allows tables to be reserved for shared mode. That
[READ | is, other users can work with those tables.
WRITE]
[NO] Allows tables to be reserved for protected mode.
PROTECTED That is, other users can read from those tables.
[READ |
WRITE]
[NO] Allows tables to be reserved for exclusive access.
EXCLUSIVE That is, other users are prevented access to those
[READ | tables, even in READ ONLY transactions.
WRITE]
ALL Allows other users to work with all tables.
NONE Allows no access to tables.
For detailed information about the txn-modes, see the SET_
TRANSACTION.
3.103 – USER_LIMIT
Syntax option:
USER LIMIT IS max-glo-buffers
Specifies the maximum number of global buffers each user
allocates. Because global buffer pools are shared by all users,
you must define an upper limit on how many global buffers a
single user can allocate. This limit prevents a user from
defining RDM$BIND_BUFFERS to use all the buffers in the global
buffer pool. The user limit cannot be greater than the total
number of global buffers. The default is 5 global buffers.
Decide the maximum number of global buffers a user can allocate
by dividing the total number of global buffers by the total
number of users for whom you want to guarantee access to the
database. For example, if the total number of global buffers is
200 and you want to guarantee access to the database for at least
10 users, set the maximum number of global buffers per user to
20.
For maximum performance on a VMScluster system, tune the two
global buffer parameters on each node in the cluster using the
RMU Open command with the Global_Buffers qualifier.
Although you can change the USER LIMIT IS parameter on line, the
change does not take effect until the next time the database is
opened.
The NUMBER IS and USER LIMIT IS parameters are the only two
buffer parameters specific to global buffers. They are in effect
on a per node basis rather than a per process basis.
3.104 – USER username
Syntax options:
USER 'username'
A character string literal that specifies the operating system
user name that the database system uses for privilege checking.
This clause also sets the value of the SYSTEM_USER value
expression.
3.105 – USING password
Syntax options:
USING 'password'
A character string literal that specifies the user's password for
the user name specified in the USER clause.
3.106 – WAIT option
Syntax option:
WAIT n MINUTES FOR CLOSE
Specifies the amount of time that Oracle Rdb waits before
automatically closing a database. If anyone attaches during that
wait time, the database is not closed.
The default value for n is zero (0) if the WAIT clause is
not specified. The value for n can range from zero (0) to
35,791,394. However, Oracle Rdb does not recommend using large
values.
3.107 – WORKLOAD_COLLECTION
Syntax options:
WORKLOAD COLLECTION IS ENABLED | WORKLOAD COLLECTION IS DISABLED
Specifies whether or not the optimizer records workload
information in the system table RDB$WORKLOAD. The WORKLOAD
COLLECTION IS ENABLED clause creates this system table if
it does not exist. If you later disable workload collection,
the RDB$WORKLOAD system table is not deleted, nor is the data
deleted.
A workload profile is a description of the interesting table
and column references used by queries in a database work load.
When workload collection is enabled, the optimizer collects
and records these references in the RDB$WORKLOAD system table.
This work load is then processed by the RMU Collect Optimizer-
Statistics command which records useful statistics about the work
load. These workload statistics are used by the optimizer at run
time to deliver more accurate access strategies.
Workload collection is disabled by default.
4 – Examples
Example 1: Changing a read/write storage area to a read-only
storage area
This example changes the SALARY_HISTORY storage area from a
read/write storage area to a read-only storage area.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER STORAGE AREA salary_history
cont> READ ONLY;
Example 2: Adding multiple, fixed-size journal files
This example demonstrates reserving slots for journal files,
enabling the journaling feature, and adding multiple, fixed-size
journal files.
SQL> CREATE DATABASE FILENAME test
cont> RESERVE 5 JOURNALS
cont> CREATE STORAGE AREA sa_one
cont> ALLOCATION IS 10 PAGES;
SQL> DISCONNECT ALL;
SQL>
SQL> ALTER DATABASE FILENAME test
cont> JOURNAL IS ENABLED
cont> ADD JOURNAL AIJ_ONE
cont> FILENAME aij_one
cont> BACKUP FILENAME aij_one
cont> ADD JOURNAL AIJ_TWO
cont> FILENAME aij_two
cont> BACKUP FILENAME aij_two
cont> ;
You should place journal files and backup files on disks other
than those that contain the database.
Example 3: Reserving and using slots for storage areas
This example demonstrates reserving slots for storage areas
and adding storage areas to the database that utilizes those
slots. Use the SHOW DATABASE statement to see changes made to the
database.
SQL> CREATE DATABASE FILENAME sample
cont> RESERVE 5 STORAGE AREAS
cont> CREATE STORAGE AREA RDB$SYSTEM
cont> FILENAME sample_system
cont> --
cont> -- Storage areas created when the database is created do not use
cont> -- the reserved storage area slots because this operation is being
cont> -- executed off line.
cont> --
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future
recovery
SQL> --
SQL> -- Reserving storage area slots is not a journaled activity.
SQL> --
SQL> -- To use the reserved slots, you must alter the database and
SQL> -- add storage areas.
SQL> --
SQL> DISCONNECT ALL;
SQL> ALTER DATABASE FILENAME sample
cont> ADD STORAGE AREA SAMPLE_1
cont> FILENAME sample_1
cont> ADD STORAGE AREA SAMPLE_2
cont> FILENAME sample_2;
Example 4: Reserving Slots for Sequences
This example shows that reserving extra sequences in the database
adds to the existing 32 that are provided by default, and the
count rounded up to the next multiple of 32 (that is, 64).
$ SQL$ ALTER DATABASE FILENAME MF_PERSONNEL RESERVE 10 SEQUENCES;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
$ RMU/DUMP/HEADER=SEQUENCE MF_PERSONNEL
*------------------------------------------------------------------------------
* Oracle Rdb V7.1-200 15-AUG-2003 14:54:26.55
*
* Dump of Database header
* Database: USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1
*
*------------------------------------------------------------------------------
Database Parameters:
Root filename is "USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1"
Sequence Numbers...
.
.
.
Client sequences...
- 64 client sequences have been allocated
- 0 client sequences in use
Example 5: Adding and Enabling a Row Cache on OpenVMS
The MF_PERSONNEL database is altered to add a row cache, apply it
to several storage areas and enable row caching. The example
further assumes that after image journals have already been
defined for the database, they are required for the JOURNAL IS
ENABLED clause to succeed.
SQL> /*
***> Prepare the database for ROW CACHE, include extra
***> capacity for later additions
***> */
SQL> alter database
cont> filename MF_PERSONNEL
cont> number of cluster nodes is 1
cont> journal is ENABLED (fast commit is enabled)
cont> reserve 20 cache slots
cont> row cache is ENABLED
cont>
cont> /*
***> Create a physical cache for all the employee rows
***> */
cont> add cache EMPIDS_RCACHE
cont> shared memory is SYSTEM
cont> row length is 126 bytes
cont> cache size is 204 rows
cont> checkpoint updated rows to database
cont>
cont> /*
***> Apply the cache to each of the relevant storage areas
***> */
cont> alter storage area EMPIDS_LOW
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_MID
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_OVER
cont> cache using EMPIDS_RCACHE
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
Example 6: Establishing a Timeout Value for Prestarted
Transactions
SQL> ALTER DATABASE
cont> FILENAME SAMPLE
cont> PRESTARTED TRANSACTIONS ARE ENABLED
cont> (WAIT 90 SECONDS FOR TIMEOUT)
cont> ;
Example 7: Altering a Database Specifying the SINGLE INSTANCE
Option
This example prepares a database to be run in a 4 node GALAXY
cluster. The SINGLE INSTANCE clause is used to enable special
optimizations that are available because of the galaxy shared
memory.
SQL> alter database
cont> filename MF_PERSONNEL
cont> galaxy support is ENABLED
cont> number of cluster nodes is 4 (single instance);
Example 8: Disabling storage if snapshot rows
The following example demonstrates using SQL to modify the "C1"
cache to disable storage of snapshot rows in cache and to modify
the "C5" cache to enable storage of snapshot rows in the cache
with a snapshot cache size of 12345 rows:
SQL> ALTER DATABASE FILE EXAMPLE_DB
cont> ALTER CACHE C1
cont> ROW SNAPSHOT IS DISABLED;
cont> ALTER CACHE C5
cont> ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12345 ROWS);
Example 9: Using the SWEEP INTERVAL clause
Here is an example of using the SWEEP INTERVAL clause.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ROW CACHE IS ENABLED (SWEEP INTERVAL IS 100 seconds)
cont> ;
SQL> attach 'filename MF_PERSONNEL';
SQL> show database rdb$dbhandle
Default alias:
Oracle Rdb database in file MF_PERSONNEL
.
.
.
Row Cache is Enabled
Row cache: sweep interval is 100 seconds
Row cache: No Location
Row cache: checkpoint updated rows to backing file
.
.
.