Creates database system files, metadata definitions, and user
data that comprise a database. The CREATE DATABASE statement
lets you specify in a single SQL statement all data and privilege
definitions for a new database. (You can also add definitions to
the database later.) For information about ways to ensure good
performance and data consistency, see the Oracle Rdb7 Guide to
Database Performance and Tuning.
The many optional elements of the CREATE DATABASE statement
make it very flexible. In its simplest form, the CREATE DATABASE
statement creates database system files, specifies their names,
and determines the physical characteristics of the database.
Using the optional elements of the CREATE DATABASE statement, you
can also specify:
o Whether the database created with CREATE DATABASE is multifile
(separate database root file and storage area data file) or
single file (combined database root file and storage area
data file). Multifile databases can have many storage areas
for user data, all separate from the database root file
created by the CREATE DATABASE statement. Multifile databases
include CREATE STORAGE AREA clauses in the CREATE DATABASE
statement to create multiple storage area files for enhanced
performance.
The presence or absence of a CREATE STORAGE AREA clause in
a CREATE DATABASE statement determines whether the database
is single file or multifile. To create a multifile database,
you must include a CREATE STORAGE AREA clause in the CREATE
DATABASE statement. To create a single-file database, do not
include a CREATE STORAGE AREA clause in the CREATE DATABASE
statement.
o Values for various database root file parameters that override
the system defaults. Database root file (.rdb) parameters
describe characteristics of the database root file. Database
root file parameters affect the entire database, whether it is
a single-file or a multifile database.
o Values for storage area parameters that override system
defaults. Storage area parameters describe characteristics of
the database storage area files. In a single-file database,
because the storage area data file is combined with the
database root file, storage area parameters apply to a single
storage area and affect the entire database. In a multifile
database, storage area parameters specify defaults for the
main storage area, RDB$SYSTEM, and for any subsequent CREATE
STORAGE AREA clauses within the CREATE DATABASE statement.
o Any number of database elements. Database elements are a
CREATE CATALOG statement, a CREATE STORAGE AREA clause, or
a GRANT statement. The CREATE DATABASE statements that create
single-file databases cannot include a CREATE STORAGE AREA
clause because this is specific to multifile databases. The
CREATE DATABASE statements that create multifile databases
must include at least one CREATE STORAGE AREA clause.
Unlike the same statements outside a CREATE DATABASE
statement, database elements do not use statement terminators.
The first statement terminator that SQL encounters ends the
CREATE DATABASE statement. Later CREATE or GRANT statements
are not within the scope of the CREATE DATABASE statement.
o The database default character set and national character set.
For information regarding identifier character sets, database
default character sets, and national character sets, see the
Character_Sets HELP topic.
1 – Environment
You can use the CREATE 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
CREATE DATABASE -+------------------++
+-> ALIAS <alias> -+|
+------------ <-----------------------+
++-----------------------------++------------------------------+-+
+++-> root-file-params-1 ---++++++-> storage-area-params-1 -+++ |
|+-> root-file-params-2 ---+| |+-> storage-area-params-2 -+| |
|+-> root-file-params-3 ---+| +-------------- <------------+ |
|+-> root-file-params-4 ---+| |
+----------- <--------------+ |
+-------------------------- <------------------------------------+
++--------------------+--+-----------------------+--------->
+-> character-sets --+ ++-> database-element -++
+-------- <-----------+
root-file-params-1 =
-+-> FILENAME <db-attach-spec> -++----------------------++->
+-> PATHNAME <path-name> ----++-> literal-user-auth -+|
+-> attach-options -------------------------------------+
+-> COLLATING SEQUENCE <collation-name> -----+ |
| +-------------------<---------------------+ |
| ++-----------------------------------+----+ |
| +-> COMMENT IS ---+-> '<string>' --++ | |
| +------- / <-----+ | |
| +-------------------<---------------------+ |
| +--> <ncs-name> -+-----------------------+-----------+
| +-> FROM <library-name> + |
+-> NUMBER OF USERS -------> <number-users> ------------+
+-> NUMBER OF BUFFERS -----> <number-buffers> ----------+
+-> NUMBER OF CLUSTER NODES ----> <number-nodes> --+ |
| +--------------------------------------------+ |
| ++----------------------------------------+-------+
| +> ( -+-> SINGLE ---+-> INSTANCE --> ) --+ |
| +-> MULTIPLE -+ |
+-> NUMBER OF RECOVERY BUFFERS -> <number-buffers> -----+
+-> BUFFER SIZE IS -> <buffer-blocks> --> BLOCKS -------+
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>' -+
attach-options =
-+-> DBKEY -+-> SCOPE IS -+-> ATTACH -------+-----------------------+->
+-> ROWID -+ +-> TRANSACTION --+ |
+-> MULTISCHEMA IS -+-> ON --+-------------------------------------+
| +-> OFF -+ |
+-> PRESTARTED TRANSACTIONS ARE -+-> ON --+------------------------+
| +-> OFF -+ |
+-+-------+-> RESTRICTED ACCESS -----------------------------------+
+-> NO -+
root-file-params-2 =
-+-> global-buffer-params -----------------------------------------+->
+-> SNAPSHOT IS ----+-----> ENABLED -+-> IMMEDIATE +-+-----------+
| | +-> DEFERRED -+ | |
| +-----> DISABLED -----------------+ |
+-> DICTIONARY IS ---+---> REQUIRED -------+----------------------+
| +---> NOT REQUIRED ---+ |
+-> ADJUSTABLE LOCK GRANULARITY IS -+-> ENABLED -> alg-options -+-+
| +-> DISABLED ---------------+ |
+-> LOCK TIMEOUT INTERVAL IS <number-seconds> SECONDS ------------+
+-> SEGMENTED STRING -+-> STORAGE AREA IS <area-name> ------------+
+-> LIST -------------+ |
+-> DEFAULT ----------+ |
+-> PROTECTION IS ---+---> ANSI --+-------------------------------+
| +---> ACLS --+ |
+-> RESERVE <n> +-> CACHE SLOTS ----+-----------------------------+
| +-> JOURNALS -------+ |
| +-> STORAGE AREAS --+ |
| +-> SEQUENCES ------+ |
+-+-> 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 -+ |
+------------------- , <----------------+
alg-options =
--+------------------------------+-->
+-> ( --> COUNT IS <n> --> ) --+
txn-modes =
-+-+-------+-+-> READ ONLY -----------------+-->
| +-> NO -+ +-> READ WRITE ----------------+
| +-> BATCH UPDATE --------------+
| +-> SHARED ----+--+----------+-+
| +-> PROTECTED -+ +-> READ --+ |
| +-> EXCLUSIVE -+ +-> WRITE -+ |
+-------------> ALL -----------------------+
+-------------> NONE ----------------------+
root-file-params-3 =
-+> 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 ----------------------------+ |
+> SYSTEM INDEX -+-> COMPRESSION IS -+-> ENABLED ---+-----+----------+
| | +-> DISABLED --+ | |
| +-> ( -+-> system-index-options -+-> ) --+ |
| +---------- , <-----------+ |
+> prestarted-transaction-params ------------------------------------+
+> SECURITY CHECKING IS --> security-checking-options ---------------+
+> SYNONYMS ARE ENABLED ---------------------------------------------+
+> NOTIFY IS -+-> ENABLED --> notify-options -+---------------------+
+-> DISABLED --------------------+
system-index-options =
-+-> COMPRESSION IS --------------------------------++- ENABLED -++->
+-> PREFIX CARDINALITY COLLECTION IS --------------++> DISABLED +|
+-> PREFIX CARDINALITY COLLECTION IS ENABLED FULL ---------------+
+-> TYPE IS SORTED --+-----------+-------------------------------+
+-> RANKED -+
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 -------------------+
security-checking-options =
-+-> EXTERNAL -+------------------------------------------------++->
| +-> ( -> PERSONA SUPPORT IS -+-> ENABLED --+> ) -+|
| +-> DISABLED -+ |
+-> INTERNAL -+-----------------------------------------------+-+
+-> ( -> ACCOUNT CHECK IS -+---> ENABLED --+> ) +
+---> DISABLED -+
notify-options =
--+-----------------------------------------------------+-->
+--> ( ALERT OPERATOR -+--> operator-class -+-- ) --+
+--------- + <---------+
root-file-params-4 =
-+-> ASYNC BATCH WRITES ARE +> ENABLED -> async-bat-wr-options -+---+->
| +> DISABLED ------------------------+ |
++------------+-> ASYNC PREFETCH IS --+ |
|+> DETECTED -+ +-------------------+ |
| +-+-> ENABLED ---> async-prefetch-options +------+
| +-> DISABLED ---------------------------+ |
+-> ROW CACHE IS -+-> ENABLED --+-+----------------------+---------+
| +-> DISABLED -+ +-> row-cache-options -+ |
+-+------+-> INCREMENTAL BACKUP SCAN OPTIMIZATION -----------------+
| +> NO -+ |
+-> MULTITHREAD AREA ADDITIONS ----> multithread-options ----------+
+-> RECOVERY JOURNAL -> ( -> ruj-options -> ) ---------------------+
+-> OPEN IS -+> MANUAL --------------------------------------------+
| +> AUTOMATIC -+--------------------------------------++
| +->(WAIT <n> -> MINUTES -> FOR CLOSE) -+|
+-> SHARED MEMORY IS -+-> SYSTEM -------------------+--------------+
+-> PROCESS -+-------------+--+
+-> RESIDENT -+
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 -+ |
+----------------- , <--------------------------+
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 ------------------------+|
+-------------------------- , <-----------------------------+
multithread-options =
---+-----------------------------------------+-->
+-> ( --+-> ALL AREAS -----------+--> ) --+
+-> LIMIT TO <n> AREAS --+
ruj-options =
-+-> LOCATION IS --> <directory-spec> -+-->
+-> NO LOCATION ----------------------+
+-> BUFFER MEMORY IS -+-> LOCAL ------+
+-> GLOBAL -----+
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 -----+
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> ------->
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> + |
+------------------------------<--------------------------+
character-sets =
-+-+--> DEFAULT CHARACTER SET <support-char-set> --+-+->
| +--> NATIONAL CHARACTER SET <support-char-set> -+ |
| +--> IDENTIFIER CHARACTER SET <names-char-set> -+ |
| +--> DISPLAY CHARACTER SET <support-char-set> ----+
+---------------------- <---------------------------+
database-element =
-+-> create-cache-clause -----------------+-->
+-> create-catalog-statement ------------+
+-> create-collating-sequence-statement -+
+-> create-domain-statement -------------+
+-> create-function-statement -----------+
+-> create-index-statement --------------+
+-> create-module-statement -------------+
+-> create-procedure-statement ----------+
+-> create-sequence-statement -----------+
+-> create-schema-statement -------------+
+-> create-storage-area-clause ----------+
+-> create-storage-map-statement --------+
+-> create-table-statement --------------+
+-> create-trigger-statement ------------+
+-> create-view-statement ---------------+
+-> grant-statement ---------------------+
3 – Arguments
3.1 – 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 is ENABLED and 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. Such databases can trade
more restrictive locking for less CPU usage by disabling adjustable
lock granularity.
3.2 – alias
Specifies the alias for the implicit database declaration
executed by the CREATE DATABASE statement. An alias is a name for
a particular attach to a database that identifies that database
in subsequent SQL statements.
NOTE
If you attach to a database using an alias, you must use
that alias in subsequent statements to qualify the names of
elements in that database.
If you omit the FILENAME argument from the database root file
parameters, SQL also uses the alias as the file name for the
database root file and creates the root file in the current
default directory. (SQL generates a syntax error if you include
a disk or directory specification in the alias clause.) You must
specify either the FILENAME or alias argument.
Schema elements in the CREATE DATABASE statement do not need to
use the alias, however, they cannot specify any other alias.
The alias clause is optional. The default alias in interactive
SQL and in precompiled programs is RDB$DBHANDLE. In the SQL
module language, the default is the alias specified in the
module header. Using the default alias (either by specifying
it explicitly in the ALIAS clause or omitting the ALIAS clause)
declares the database as the default database. Specifying a
default database means that statements outside the CREATE
DATABASE statement that refer to the default database do not
need to use an alias.
If a default database was already declared, and you specify the
default alias in the ALIAS clause (or specify any alias that was
already declared), the results depend on the environment in which
you issue the CREATE DATABASE statement.
o In interactive SQL, you receive a prompt asking if you want
to override the default database declaration. Unless you
explicitly override the default declaration, the CREATE
DATABASE statement fails.
SQL> -- Assume a default database has been declared:
SQL> --
SQL> -- Now create a database without an alias.
SQL> -- SQL asks if you want to override the default:
SQL> CREATE DATABASE FILENAME test;
This alias has already been declared.
Would you like to override this declaration (No)? NO
%SQL-F-DEFDBDEC, A database has already been declared with the default
alias
o In embedded SQL or in the SQL module language, specifying
an already-declared alias in the CREATE DATABASE statement
generates an error when you precompile the program or compile
the module.
o In dynamic SQL, specifying an already-declared alias overrides
the earlier declaration.
For more information about default databases, see the User_
Supplied_Names HELP topic.
3.3 – ALL_AREAS
Specifies that all storage areas be created and initialized in
parallel.
All storage areas are created asynchronously. If you are creating
a large number of storage areas, you may exceed process quotas,
resulting in the database creation failing.
3.4 – ALLOCATION n pages
The number of database pages allocated to the database initially.
SQL automatically extends the allocation to handle the loading of
data and subsequent expansion. Pages are allocated in groups of
3. An ALLOCATION of 25 pages would actually provide for 27 pages.
The default is 700 pages. If you are loading a large database, a
large allocation helps to prevent fragmented files.
3.5 – ALTER_TRANSACTION_MODES
Enables the modes specified, leaving the previously defined or
default modes enabled. For example, if the only transaction
mode you want to disable are batch updates, use the following
statement:
SQL> CREATE DATABASE FILENAME mf_personnel
cont> ALTER TRANSACTION MODES (NO BATCH UPDATE);
If not specified, the default transaction mode is ALL.
3.6 – 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.7 – 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.8 – BUFFER_SIZE
Specifies the number of blocks SQL 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 less than 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.
3.9 – CACHE_USING
Assigns the named row cache as the default for all storage areas
in the database. All rows stored in an area, whether they consist
of table data, segmented string data, or special rows such as
index nodes, are cached.
You must create the row cache before terminating the CREATE
DATABASE statement. For example:
SQL> CREATE DATABASE FILENAME test_db
cont> ROW CACHE IS ENABLED
cont> CACHE USING test1
cont> CREATE CACHE test1
cont> CACHE SIZE IS 100 ROWS
cont> CREATE STORAGE AREA area1;
You can override the database default row cache by either
specifying the CACHE USING clause after the CREATE STORAGE AREA
clause or by later altering the database and storage area to
assign a new row cache. Only one row cache is allowed for each
storage area.
If you do not specify the CACHE USING clause or the NO ROW CACHE
clause, NO ROW CACHE is the default for the database.
3.10 – CARDINALITY_COLLECTION
Syntax options:
CARDINALITY COLLECTION IS ENABLED
CARDINALITY COLLECTION IS DISABLED
Specifies whether or not the optimizer records cardinality
updates in the system table. When enabled, the optimizer collects
cardinalities for the table and non-unique 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 Analyze
Cardinality command so the optimizer is given the most accurate
values for estimation purposes.
Cardinality collection is enabled by default.
3.11 – CARRY_OVER_LOCKS
Syntax options:
CARRY OVER LOCKS ARE ENABLED | CARRY OVER LOCKS ARE DISABLED
Enables or disables carry-over lock optimization. Carry-over
locks are enabled by default.
While attached to the database, a process can have some active
locks (locks attached to the database) and some carry-over
locks (locks requested in earlier transactions that have not
been demoted). If a transaction needs a lock it has currently
marked as carry-over, it can reuse the lock by changing it to an
active lock. The same lock can go from active to carry-over to
active multiple times without paying the cost of lock request and
demotion. This substantially reduces the number of lock requests
if a process accesses the same areas repeatedly.
As part of the carry-over lock optimization, a NOWAIT transaction
requests, acquires, and holds a NOWAIT lock. This signals other
processes accessing the database that a NOWAIT transaction exists
and causes Oracle Rdb to release all carry-over locks. If NOWAIT
transactions are noticeably slow when executing, you can specify
CARRY OVER LOCKS ARE DISABLED with the ALTER DATABASE or CREATE
DATABASE statement.
This feature is available as an online database modification.
3.12 – CHECKPOINT_TIMED
Syntax option:
CHECKPOINT TIMED EVERY n SECONDS
For the row-cache-options clause, specifies the frequency with
which the row-cache server (RCS) process checkpoints the contents
of the row caches back to disk. The RCS process does not use the
checkpoint frequency options of the FAST COMMIT clause.
The frequency of RCS checkpointing is important in determining
how much of an .aij file must be read during a recovery operation
following a node failure. It also affects the frequency with
which marked records get flushed back to the database for those
row caches that checkpoint to the database. The default is every
15 minutes (900 seconds).
3.13 – 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.14 – CHECKSUM_CALCULATION
Syntax options:
CHECKSUM CALCULATION IS ENABLED
CHECKSUM CALCULATION IS DISABLED
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 Rdb 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.15 – CLEAN_BUFFER_COUNT
Syntax options:
CLEAN BUFFER COUNT IS buffer-count BUFFERS
Specifies the number of buffers to be kept available for
immediate reuse.
Oracle Rdb maintains the number of buffers at the end of a
process' least recently used queue of buffers for replacement.
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.16 – COLLATING_SEQUENCE
Specifies a default collating sequence to be used for all CHAR
and VARCHAR columns in the database. SQL uses the default
collating sequence if you do not specify a collating sequence
in subsequent CREATE DOMAIN statements.
Collation-name is a name of your choosing; you must use this name
in any COLLATING SEQUENCE clauses that refer to this collating
sequence for operations on this database.
3.17 – COMMENT
Syntax option:
COMMENT IS 'string'
Adds a comment about the collating sequence. SQL displays the
text when it executes a SHOW COLLATING SEQUENCE statement in
interactive SQL. Enclose the comment in single quotation marks
(') and separate multiple lines in a comment with a slash mark
(/).
3.18 – 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.19 – create-cache-clause
See the CREATE CACHE clause for more details.
3.20 – create-catalog-statement
See the CREATE CATALOG statement for details.
If you want to specify a CREATE CATALOG statement in a CREATE
DATABASE statement, you must first specify a MULTISCHEMA IS ON
clause in the same CREATE DATABASE statement.
The CREATE CATALOG statement is committed immediately and cannot
be rolled back. Before you specify the CREATE CATALOG statement,
the following conditions must be true:
o The database is enabled for multischema.
o No transactions are active.
o The catalog alias must be the same as the database alias.
For information about enabling the database for multischema, see
the User_Supplied_Names HELP topic.
3.21 – create-collating-sequence-statement
See the CREATE COLLATING_SEQUENCE statement for details.
If you want to specify a collating sequence in a CREATE DOMAIN
statement embedded in a CREATE DATABASE statement, you must first
specify a CREATE COLLATING SEQUENCE statement in the same CREATE
DATABASE statement.
3.22 – create-domain-statement
See the CREATE DOMAIN statement for details.
You cannot use the FROM path-name clause when embedding a
CREATE DOMAIN statement in a CREATE DATABASE statement. You
can, however, issue a separate CREATE DOMAIN statement following
the CREATE DATABASE statement. You can also describe the domain
directly in the CREATE DATABASE statement.
If you want to specify a collating sequence in your embedded
CREATE DOMAIN statement, you must first specify a CREATE
COLLATING SEQUENCE statement in the same CREATE DATABASE
statement.
3.23 – create-function-statement
A CREATE FUNCTION statement. See the CREATE Routine statement for
details.
3.24 – create-index-statement
See the CREATE INDEX statement for details.
3.25 – create-module-statement
See the CREATE MODULE statement for details.
3.26 – create-procedure-statement
A CREATE PROCEDURE statement. See the CREATE Routine statement
for details.
3.27 – create-schema-statement
See the CREATE SCHEMA statement for details.
The schema you create must have the same alias as the catalog and
database that contain the schema, or they must share the default
alias.
3.28 – create-sequence-statement
See the CREATE SEQUENCE statement for details.
3.29 – create-storage-area-clause
See the CREATE STORAGE_AREA statement for more details.
3.30 – create-storage-map-statement
See the CREATE STORAGE_MAP statement for details.
3.31 – create-table-statement
See the CREATE TABLE statement for details.
You cannot use the FROM path-name clause when embedding a CREATE
TABLE statement in a CREATE DATABASE statement. You can, however,
issue a separate CREATE TABLE statement following the CREATE
DATABASE statement. You can also describe the table directly in
the CREATE DATABASE statement.
The CREATE TABLE statements in a CREATE DATABASE statement can
refer to domains not yet created, provided that CREATE DOMAIN
statements for the domains are in the same CREATE DATABASE
statement.
3.32 – create-trigger-statement
See the CREATE TRIGGER statement for details.
3.33 – create-view-statement
See the CREATE VIEW statement for details.
3.34 – database-element
Database elements are a CREATE STORAGE AREA clause, any of the
CREATE statements (except CREATE DOMAIN . . . FROM path-name and
CREATE TABLE . . . FROM path-name), or a GRANT statement.
3.35 – DBKEY_SCOPE
Syntax options:
DBKEY SCOPE IS ATTACH | DBKEY SCOPE IS TRANSACTION
Controls when the database key of a deleted row can be used again
by SQL. This setting is not a database root file parameter, but
a characteristic of the implicit database attach executed by
the CREATE DATABASE statement. Thus, the DBKEY SCOPE clause in a
CREATE DATABASE statement takes effect only for the duration of
the session of the user who entered the statement.
o The default DBKEY SCOPE IS TRANSACTION means that SQL can
reuse the database key of a deleted table row (to refer to a
newly inserted row) as soon as the transaction that deleted
the original row completes with a COMMIT statement. (If the
user who deleted the original row enters a ROLLBACK statement,
then the database key for that row cannot be used again by
SQL.)
During the connection of the user who entered the CREATE
DATABASE statement, the DBKEY SCOPE IS TRANSACTION clause
specifies that a database key is guaranteed to refer to the
same row only within a particular transaction.
o The DBKEY SCOPE IS ATTACH clause means that SQL cannot use the
database key again (to refer to a newly inserted row) until
all users who have attached with DBKEY SCOPE IS ATTACH have
detached from the database.
Also it only requires one process to attach with DBKEY
SCOPE IS ATTACH to force all database users to assume this
characteristic.
o Oracle Corporation recommends using DBKEY SCOPE IS TRANSACTION
to prevent excessive consumption of storage area space by
overhead needed to support DBKEY SCOPE IS ATTACH, and to
prevent performance problems when storing new rows.
During the connection of the user who entered the CREATE
DATABASE statement, the DBKEY SCOPE IS ATTACH clause specifies
that a database key is guaranteed to refer to the same row
until the user detaches from the database.
For more information, see the DBKEY HELP topic.
3.36 – DEFAULT_CHARACTER_SET
Specifies the database default character set for this database.
For a list of allowable character set names, see Supported
Character Sets.
3.37 – DEFAULT_STORAGE_AREA
Specifies a default storage area to which all user data and
unmapped indexes are stored. The DEFAULT STORAGE AREA parameter
separates user data from the system data, such as system tables.
RDB$SYSTEM is the default area if you do not specify a default
storage area.
In addition to user data, Oracle Rdb stores the following system
tables in the default storage area:
o RDB$INTERRELATIONS
o RDB$MODULES
o RDB$ROUTINES
o RDB$PARAMETERS
o RDB$QUERY_OUTLINES
o RDB$SEQUENCES
o RDB$PROFILES
o RDB$GRANTED_PROFILES
o RDB$TYPES
o RDB$TYPE_FIELDS
o RDB$WORKLOAD
o RDB$OBJECT_SYNONYMS
o RDB$SYNONYMS
o RDB$CATALOG_SCHEMA
For information on moving these system tables to other
storage areas, see the Oracle Rdb Guide to Database Design and
Definition.
The DEFAULT STORAGE AREA parameter must reference an existing
storage area. You must create the storage area using the CREATE
STORAGE AREA clause in the same CREATE DATABASE statement as the
DEFAULT STORAGE AREA parameter.
3.38 – 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.39 – 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.40 – DICTIONARY
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
REQUIRED, any data definition statements issued after a DECLARE
DATABASE statement that does not use the PATHNAME argument fails.
If you omit the PATHNAME clause from the database root file
parameters in the CREATE DATABASE statement, SQL generates an
error if you also specify DICTIONARY IS REQUIRED.
The default is DICTIONARY IS NOT REQUIRED.
3.41 – DISPLAY_CHARACTER_SET
Specifies the character set encoding and characteristics expected
of text strings returned back to SQL from Oracle Rdb.
3.42 – EXTENT clause
Syntax options:
EXTENT IS ENABLED | EXTENT IS DISABLED
Enables or disables extents. Extents are enabled by default.
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.43 – EXTENT pages
Syntax options:
EXTENT IS extent-pages PAGES| EXTENT IS (extension-options)
Specifies the number of pages of each storage area file extent.
For more information, see the SNAPSHOT EXTENT argument.
3.44 – FILENAME file spec
The file specification associated with the database.
You can omit the FILENAME clause if you specify the ALIAS clause.
If you omit the FILENAME clause, 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 alias, if any was specified; otherwise omitting
the FILENAME clause generates an error
Use either a full file specification or a partial file
specification.
You can use a logical name for all or part of a file
specification.
If you use a simple file name, SQL creates the database in the
current default directory. Because the CREATE DATABASE statement
may create more than one file with different file extensions, do
not specify a file extension with the file specification.
The file specification may contain an OpenVMS remote node
specification. Oracle Rdb must be installed on that remote node
so that the CREATE DATABASE statement can be executed remotely.
Note that all other file specifications in the command (storage
areas, snapshot files, recovery journal location, and so on) must
be specified using the logical names and device names relative to
that remote node.
The number and type of files created using the file specification
in the FILENAME clause depend on whether you create a multifile
or single-file database.
o In multifile CREATE DATABASE statements (any that include
CREATE STORAGE AREA clauses), SQL uses the file specification
to create up to three files:
- A database root file with an .rdb file extension
- A storage area file, with an .rda file extension, for the
main storage area, RDB$SYSTEM, (unless the CREATE DATABASE
statement contains a CREATE STORAGE AREA RDB$SYSTEM clause,
which overrides this file specification)
- A snapshot file, with an .snp file extension, for the
main storage area, RDB$SYSTEM (unless the CREATE DATABASE
statement contains a CREATE STORAGE AREA RDB$SYSTEM clause,
which overrides this file specification)
o In single-file CREATE DATABASE statements (any that omit the
CREATE STORAGE AREA clause), SQL uses the file specification
to create two files:
- A combined root and data file with an .rdb file extension
- A snapshot file with an .snp file extension
If you create a single-file database, you cannot later create
additional data and snapshot files with ALTER DATABASE . . .
ADD STORAGE AREA statements. If you want to change a database
from a single-file to a multifile database, you must use the
EXPORT and IMPORT statements.
3.45 – FROM library name
Specifies the name of an NCS library other than the default
library. The default NCS library is SYS$LIBRARY:NCS$LIBRARY.
3.46 – 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.47 – GLOBAL_BUFFERS
Syntax options:
GLOBAL BUFFERS ARE ENABLED | GLOBAL BUFFERS ARE DISABLED
Specifies that 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. For more than one
attach to use the same page, each must read it from disk into
its local buffer pool. A page in the global buffer pool may be
read by more than one attach at the same time, although only one
process reads the page from the disk into the global buffer pool.
Global buffering provides improved performance because 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.48 – grant-statement
See the GRANT statement for details.
3.49 – IDENTIFIER_CHARACTER_SET
Specifies the identifier character set for user-supplied database
object names, such as table names and column names. The character
set must contain ASCII characters. See the Oracle Rdb SQL
Reference Manual for a list of allowable character sets.
3.50 – INCREMENTAL_BACKUP_SCAN_OPTIMIZATION
Syntax option:
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 root file backup TSN, which indicates that a page in the SPAM
interval has been updated since the last full backup operation.
Updates in the SPAM interval result in an extra I/O.
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 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.51 – INTERVAL
Specifies the number of data pages between space area management
(SPAM) pages in the storage area file, and therefore the maximum
number of data pages each space area management page will manage.
The default, and also the minimum interval, is 216 data pages.
The first page of each storage area is a space area management
page. The interval you specify determines where subsequent space
area management pages are to be inserted, provided there are
enough data pages in the storage file to require more space area
management pages.
You cannot specify the INTERVAL storage area parameter for
single-file databases, and you cannot specify INTERVAL unless
you also explicitly specify PAGE FORMAT IS MIXED.
Oracle Rdb calculates the maximum interval size based on the
number of blocks per page and returns an error message if you
exceed this value. For example, when the page size is 2 blocks,
the maximum interval is 4008 pages. If you try to create a
storage area with the interval set to 4009, Oracle Rdb returns
the following error message:
%RDB-E-BAD_DPB_CONTENT, invalid database parameters in the database parameter
block (DPB)
-RDMS-F-SPIMAX, spam interval of 4009 is more than the Rdb maximum of 4008
-RDMS-F-AREA_NAME, area NEW
For more information about setting space area management
parameters, see the Oracle Rdb Guide to Database Maintenance.
3.52 – LARGE_MEMORY
Syntax options:
LARGE MEMORY IS ENABLED | LARGE MEMORY IS DISABLED
Specifies whether or not large memory is used to manage the row
cache. Very large memory (VLM) allows Oracle Rdb to use as much
physical memory as is available.
Use the LARGE MEMORY IS ENABLED clause only when both of the
following are true:
o You have enabled row caching.
o You want to cache large amounts of data, but the cache does
not fit in the virtual address space.
The default is the LARGE MEMORY IS DISABLED clause.
3.53 – LIMIT TO n AREAS
Specifies the number of storage areas to be created in parallel.
The number of areas should be smaller than the current process
file open quota. The number of areas can range from between 1 and
the number of storage areas being created.
3.54 – LIST_STORAGE_AREA
Specifies the name of the storage area to be used for table
columns defined through SQL with the LIST OF BYTE VARYING data
type.
You can specify the LIST STORAGE AREA parameter for multifile
databases only.
By default, columns with the LIST OF BYTE VARYING data type are
stored in the RDB$SYSTEM storage area. If you specify a different
storage area in this clause, the CREATE DATABASE statement must
include a CREATE STORAGE AREA clause defining that area. For
information about creating multiple list storage areas for a
table, see the CREATE STORAGE_AREA clause.
NOTE
If you plan to store lists with segments of widely varying
sizes, you should specify a MIXED page format area just
for list storage. (Do not assign tables and indexes to the
area.)
The database system looks for free space in an area when it
stores each segment of a segmented string. If size varies
significantly among the different segments of the lists
that you plan to store, the interval and threshold values
that the database system automatically sets for page format
areas you specify as UNIFORM can make storing lists time-
consuming. For a mixed page format area, you can customize
interval and thresholds values to reduce the amount of time
that the database system spends looking for free space when
it stores different segments of the same segmented string.
The following example shows valid syntax for the LIST STORAGE
AREA clause:
SQL> CREATE DATABASE FILENAME test
cont> LIST STORAGE AREA IS registry_area
cont> CREATE STORAGE AREA RDB$SYSTEM FILENAME maintenance_area
cont> CREATE STORAGE AREA registry_area FILENAME registry_area;
SQL> CREATE STORAGE MAP registry_map
cont> STORE LISTS IN registry_area;
3.55 – 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 CREATE DATABASE statement.
3.56 – LOCATION IS directory spec
Specifies the name of the backing store directory to which row
cache information is written. The database system generates a
file name (row-cache-name.rdc) automatically for each row cache
at checkpoint time. Specify a device name and directory name
only, enclosed within single quotation marks. 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.
These .rdc files are permanent database backing store files.
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.57 – 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.58 – 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 of seconds 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 as well as the upper limit for determining the timeout
interval. For example, if the database definer specified LOCK
TIMEOUT INTERVAL IS 25 SECONDS in the CREATE 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 uses the interval of 25 seconds. For more information,
see the SET_TRANSACTION statement and the Oracle Rdb7 Guide to
Distributed Transactions.
3.59 – LOCKING level
Syntax options:
LOCKING IS ROW LEVEL | LOCKING IS PAGE LEVEL
Specifies page-level or row-level locking as the default for the
database. This clause provides an alternative to requesting locks
on records. You can override the database default lock level
at the storage area level. The default is ROW LEVEL, which is
compatible with previous versions of Oracle Rdb.
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. 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 ASTs
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 lock
protocol can stall metadata users.
You cannot specify page-level locking on single-file databases.
3.60 – 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 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.61 – MAXIMUM_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.62 – MAXIMUM extent pages
Syntax option:
MAXIMUM OF max-pages PAGES
Specifies the maximum number of pages of each extent. The default
is 9999 pages.
3.63 – 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, TRUNCATE TABLE, COMMENT ON and
RENAME statements. For example:
SQL> CREATE DATABASE FILENAME sample
cont> METADATA CHANGES ARE DISABLED;
SQL> CREATE TABLE t (a INTEGER);
SQL> DISCONNECT ALL;
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. If you specify this clause in the
CREATE DATABASE statement, system index compression is implicitly
enabled.
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.64 – MINIMUM extent pages
Syntax option:
MINIMUM OF min-pages PAGES
Specifies the minimum number of pages of each extent. The default
is 99 pages.
3.65 – MULTISCHEMA
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
Specifies the multischema attribute for the database. You must
specify the multischema attribute for your database to create
multiple schemas and store them in catalogs. Each time you attach
to a database created with the multischema attribute, you can
specify whether you want multischema naming enabled or disabled
for subsequent statements. For more information on multischema
naming, see the User_Supplied_Names HELP topic.
If you prefer to access a database created with the multischema
attribute as though it were single-schema database, you can turn
off multischema naming using the MULTISCHEMA IS OFF clause in the
ATTACH or DECLARE ALIAS statement.
If you have turned off the multischema attribute, you can enable
it again using the MULTISCHEMA IS ON clause in the ATTACH or
DECLARE ALIAS statement. You can use multischema naming only
when you are attached to a database that was created with the
multischema attribute. For more information, see the ATTACH
statement.
Multischema naming is disabled by default.
3.66 – MULTITHREAD_AREA_ADDITIONS
Specifies whether Oracle Rdb creates all storage areas in
parallel, creates a specified number in parallel, or creates
areas serially.
This clause lets you determine the number of storage areas
to be created in parallel, possibly saving time during the
initial database creation. However, if you specify a large
number of storage areas and many areas share the same device,
multithreading may cause excessive disk head movement, which may
result in the storage area creation taking longer than if the
areas were created serially. In addition, if you specify a large
number of storage areas, you may exceed process quotas, resulting
in the database creation failing.
This setting is not saved as a permanent database attribute. It
is used only during the execution of the CREATE DATABASE, ALTER
DATABASE, or IMPORT statements.
If you do not specify the MULTITHREAD AREA ADDITIONS clause, the
default is to create one storage area at a time. If you specify
the MULTITHREAD AREA ADDITIONS clause, but do not specify an
option, the default is all areas are created in parallel.
3.67 – NATIONAL_CHARACTER_SET
Specifies the database national character set when you create a
database. For a list of allowable national character set names,
see Supported Character Sets.
3.68 – ncs-name
The OpenVMS National Character Set (NCS) utility provides a
set of predefined collating sequences and also lets you define
collating sequences of your own. In the default NCS library,
SYS$LIBRARY:NCS$LIBRARY, ncs-name is the name of a collating
sequence or ncs-name is the name of the collating sequence in
the NCS library specified by the library-name argument. (In most
cases, it is simplest to make the collating sequence name the
same as the ncs-name, for example, CREATE DATABASE . . . COLLATING
SEQUENCE IS SPANISH SPANISH.) The COLLATING SEQUENCE clause
accepts both predefined and user-defined NCS collating sequences.
If you omit the COLLATING SEQUENCE clause in the CREATE DATABASE
statement at database definition time, the default sequence is
the DEC Multinational Character Set (MCS).
3.69 – 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.70 – NO_ROW_CACHE
Specifies that the database default is not to 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 CACHE USING clause or the NO ROW CACHE
clause, NO ROW CACHE is the default for the database.
3.71 – 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.72 – NUMBER global buffers
Syntax option:
NUMBER IS number-glo-buffers
Specifies the default number of global buffers to be used on
one node when global buffers are enabled. 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 process that attaches to the
database. The NUMBER OF BUFFERS IS parameter applies to, and
has the same meaning for, both 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, see the 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.73 – NUMBER_OF_BUFFERS
Specifies the number of buffers SQL allocates for each attach
to this database. This number is displayed as the "default
database buffer count" in the output from the RMU Dump command.
The default buffer count applies to local and global buffers.
Specify an unsigned integer greater than or equal to 2 and less
than or equal to 32,767. The default is 20 buffers.
3.74 – NUMBER_OF_CLUSTER_NODES
Syntax option:
NUMBER OF CLUSTER NODES number-nodes
Sets the upper limit on the maximum number of VMS cluster nodes
from which users can access the shared 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 during database activity. To
enable these optimizations, specify NUMBER OF CLUSTER NODES 1, or
use the SINGLE INSTANCE clause.
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.75 – NUMBER_OF_RECOVERY_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 (.ruj) file.
Specify an unsigned integer 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 buffers. If you have a large,
multifile database and you are working on a system with a large
amount of memory, specify a large number of buffers. This result
is faster recovery time. However, make sure your buffer pool does
not exceed the amount of memory you can allocate for the pool.
if the number of buffers is too large for the amount of memory on
your system, the system may be forced to perform virtual paging
of the buffer pool. This can slow performance time because the
operating system must perform the virtual paging of the buffer
pool in addition to reading database pages. You may want to
experiment to determine the optimal number of buffers for your
database.
Use the NUMBER OF RECOVERY BUFFERS option to increase the number
of buffers allocated to the recovery process.
SQL> CREATE DATABASE FILENAME personnel
cont> NUMBER OF RECOVERY BUFFERS 150;
This option is used only if the NUMBER OF RECOVERY BUFFERS value
is larger than the NUMBER OF BUFFERS value. For more information,
see the Oracle Rdb Guide to Database Maintenance.
3.76 – 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.77 – NUMBER_OF_USERS
Specifies the maximum number of users allowed to access the
database at one time. 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. Thus, if a single process runs
one program but that program performs 12 attach operations, the
process is responsible for 12 active users as defined by this
argument.
3.78 – OPEN
Syntax options:
OPEN IS MANUAL | OPEN IS AUTOMATIC
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.
The OPEN IS MANUAL option limits access to databases. You must
have the DBADM privilege to attach to the database.
You receive an error message if you specify both OPEN IS
AUTOMATIC and OPEN IS MANUAL options.
You can modify the OPEN IS option through the ALTER DATABASE
statement.
3.79 – PAGE_FORMAT
Syntax options:
PAGE FORMAT IS UNIFORM | PAGE FORMAT IS MIXED
Specifies the on-disk structure for the storage area.
o The default is PAGE FORMAT IS UNIFORM and creates a storage
area data file that is divided into clumps. Clump size, which
is derived from buffer size, is 3 pages by default. A set
of clumps forms a logical area that can contain rows from
a single table only. For more information on uniform page
formats, see the Oracle Rdb7 Guide to Database Performance and
Tuning.
Uniform page format storage areas generally give the best
performance if the tables in the storage area are subject to a
wide range of queries.
o The PAGE FORMAT IS MIXED clause creates a storage area with
a format that lets rows from more than one table reside on
or near a particular page of the storage area data file. This
is useful for storing related rows from different tables on
the same page of the data file. For storage areas subject to
repeated queries that retrieve those related rows, a mixed
page format can greatly reduce I/O overhead if the mix of
rows on the page is carefully controlled. However, mixed page
format storage areas degrade performance if the mix of rows
on the page is not suited for the queries made against the
storage area.
NOTE
The main storage area created by the CREATE DATABASE
statement, called RDB$SYSTEM, must have uniform pages. If
you specify PAGE FORMAT IS MIXED as a default storage area
parameter, SQL generates a warning message and overrides
that default when it creates the RDB$SYSTEM storage area.
3.80 – PAGE_SIZE
Syntax option:
PAGE SIZE IS page-blocks BLOCKS
The size in blocks of each database page. Page size is allocated
in 512-byte blocks. The default is 2 blocks (1024 bytes). If your
largest row is larger than approximately 950 bytes, allocate more
blocks per page to prevent fragmented rows. If you specify a page
size larger than the buffer size, an error message is returned.
3.81 – 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 CLUSTER NODES must equal 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.82 – PATHNAME path name
The repository path name for the repository directory where the
database definition is stored.
Specify one of the following:
o A full repository path name, such as CDD$TOP.SQL.DEPT3
o A relative repository path name, such as DEPT3
o A logical name that refers to a full or relative repository
path name
If you use a relative path name, CDD$DEFAULT must be defined
as all the path name segments preceding the relative path name.
For example, define CDD$DEFAULT as CDD$TOP.SQL, and then use the
relative path name DEPT3.
SQL> SHOW DICTIONARY
The current data dictionary is CDD$TOP.SQL
SQL> CREATE DATABASE ALIAS PERSONNEL PATHNAME DEPT3;
There is no default path name. If you do not specify a repository
path name for the database, SQL does not store database
definitions in the repository. Subsequent data definitions cannot
use the repository. However, Oracle Rdb recommends that you do
specify a repository path name when you create a database. For
more information, see the Oracle Rdb SQL Reference Manual.
If you use the PATHNAME argument and your system does not have
the repository, SQL ignores the argument.
When you use the PATHNAME argument, the repository associates
the path name with the file specification exactly as given in the
CREATE DATABASE statement. If that file specification is a file
name, not a logical name, you cannot alter or delete the database
by specifying the path name unless the database root file is in
the current, default working directory.
3.83 – PERCENT extent growth
Syntax option:
PERCENT GROWTH IS growth
Specifies the percent growth of each extent. The default is 20
percent growth.
3.84 – PRESTARTED_TRANSACTIONS
Syntax options:
PRESTARTED TRANSACTIONS ARE ENABLED
PRESTARTED TRANSACTIONS ARE DISABLED
Enables or disables the prestarting of transactions.
Note that the keyword ON, available in previous versions, is
synonymous with ENABLED, and the OFF keyword is synonymous with
the DISABLED keyword.
This clause is used to establish a permanent database setting for
prestarted transactions. In prior versions, this clause was only
used to temporarily set the mode for prestarted transaction for
the implicit attach performed by the CREATE DATABASE and IMPORT
DATABASE statements.
The prestart-trans-options can be one of the following clauses:
o WAIT n SECONDS FOR TIMEOUT
The n represents the number of seconds to wait before
aborting the prestarted transaction. Timing out the prestarted
transaction may prevent snapshot file growth in environments
where servers stay attached to the database with long periods
of inactivity.
o WAIT n MINUTES FOR TIMEOUT
The n represents the number of minutes to wait before aborting
the prestarted transaction.
o NO TIMEOUT
This is the default for a prestarted transaction.
Syntax options:
PRESTARTED TRANSACTIONS ARE ON | PRESTARTED TRANSACTIONS ARE OFF
3.85 – PROTECTION
Syntax options:
PROTECTION IS ANSI | PROTECTION IS ACLS
Specifies whether the database root file will be invoked with
ACL-style or ANSI/ISO-style privileges. If no protection clause
is specified, the default is ACL-style privileges.
For ACL-style databases, the access privilege set is order-
dependent. When a user tries to perform an operation on a
database, SQL reads the associated access privilege set, called
the access control list (ACL), from top to bottom, comparing
the identifier of the user with each entry. As soon as SQL finds
a match, it grants the rights listed in that entry and stops
the search. All identifiers that do not match a previous entry
"fall through" to the entry [ *,*] (equivalent to the SQL keyword
PUBLIC). The default access for PUBLIC is NONE.
See the GRANT statement and the REVOKE statement for more
information on ACL-style privileges.
For ANSI/ISO-style databases, the access privilege set is
not order-dependent. The user matches the entry in the access
privilege set; gets whatever privileges have been granted on the
database, table, or column; and gets the privileges defined for
PUBLIC. A user without an entry in the access privilege set gets
only the privileges defined for PUBLIC. There is always an access
privilege entry for PUBLIC, even if that entry has no access to
the database, table, or column.
ANSI/ISO-style databases grant access to the creator when an
object is created. Because only the creator is granted access
to the newly created object, additional access must be granted
explicitly.
See the GRANT_ANSI statement and the REVOKE_ANSI statement for
more information on ANSI/ISO-style privileges.
You can change the PROTECTION IS parameter by using the IMPORT
statement. See the IMPORT statement for more information.
3.86 – 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.87 – RECOVERY_JOURNAL_(LOCATION)
Syntax options:
RECOVERY JOURNAL (LOCATION IS directory-spec)
Specifies the location in which the recovery-unit journal (.ruj)
file is written. Do not include node names, file names, or
process-concealed logical names in the directory-spec. Single
quotation marks are required around the directory-spec. This
clause overrides the RDMS$RUJ logical name.
If this clause is omitted, then NO LOCATION is assumed.
Following is an example using this clause:
SQL> ALTER DATABASE FILENAME SAMPLE
cont> RECOVERY JOURNAL (LOCATION IS 'SQL_USER1:[DBDIR.RECOVER]')
See the Oracle Rdb Guide to Database Maintenance for more
information on recovery-unit journal files.
3.88 – RECOVERY_JOURNAL_(NO_LOCATION)
If you specify NO LOCATION, the recovery journal uses the
current user's login device and the directory [RDM$RUJ]. See
the Oracle Rdb Guide to Database Maintenance for more information
on recovery-unit journal files.
3.89 – 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 future use by the ADD CACHE clause
of the ALTER DATABASE statement. You can only add row caches if
row cache slots are available. Slots become available after a
DROP CACHE clause or a RESERVE CACHE SLOTS clause of the ALTER
DATABASE statement.
The number of reserved slots for row caches 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
row caches
If you do not specify the RESERVE CACHE SLOTS clause, the default
number of row caches is one.
Reserving row cache slots is an offline operation (requiring
exclusive database access). See the CREATE CACHE clause for more
information.
3.90 – RESERVE n JOURNALS
Specifies the number of journal files for which slots are
reserved in the database. If your database is not a multifile
database, you cannot reserve additional slots later using the
ALTER DATABASE statement.
You must reserve slots before you can add journal files to the
database.
See the ALTER DATABASE statement for more information about
adding journal files and enabling the journaling feature.
The following SQL statements create a multifile database and
reserve 5 slots for future journal files.
SQL> CREATE DATABASE FILENAME test
cont> RESERVE 5 JOURNALS
cont> CREATE STORAGE AREA sa_one
cont> ALLOCATION IS 10 PAGES;
3.91 – 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.
The number of reserved slots for sequences cannot be decreased.
If you do not specify the RESERVED SEQUENCES clause, the default
number of sequence slots is 32.
3.92 – RESERVE n STORAGE AREAS
Specifies the number of storage areas for which slots are
reserved 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.
The number of reserved slots for storage areas cannot be
decreased once the RESERVE clause is issued. If you reserve 5
slots and later reserve 10 slots, you have a total of 15 reserved
slots for storage areas.
If you do not specify the RESERVE STORAGE AREA clause, the
default number of storage areas is zero.
3.93 – RESTRICTED_ACCESS
Syntax option:
RESTRICTED ACCESS | NO RESTRICTED ACCESS
Restricts access to the database. This allows you to access the
database but locks out all other users until you disconnect from
the database. Setting restricted access to the database requires
DBADM privileges.
The default is NO RESTRICTED ACCESS.
3.94 – root-file-params
Parameters that control the characteristics of the database root
file or characteristics stored in the database root file that
apply to the entire database. You can specify these parameters
for either single-file or multifile databases.
Some database root file parameters specified in the CREATE
DATABASE statement cannot be changed with the ALTER DATABASE
statement. To change these database root file parameters,
you must use the EXPORT and IMPORT statements. See the EXPORT
statement and the IMPORT statement for information on exporting
and importing your database.
3.95 – ROW_CACHE
Syntax options:
ROW CACHE IS ENABLED | ROW CACHE IS DISABLED
Specifies whether or not you want Oracle Rdb to enable the row
caching feature.
When a database is created or is converted from a previous
version of Oracle Rdb without specifying row cache support, the
default is ROW CACHE IS DISABLED. Enabling row cache support does
not affect database operations until a row cache is created and
assigned to one or more storage areas.
When the row caching feature is disabled, all previously created
and assigned row cache definitions remain in existence for future
use when the row caching feature is enabled.
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.96 – ROWID_SCOPE
Syntax options:
ROWID SCOPE IS ATTACH | ROWID SCOPE IS TRANSACTION
The ROWID keyword is a synonym for the DBKEY keyword. See the
DBKEY_SCOPE argument for more information.
3.97 – 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 (created with the SQL CREATE USER statement)
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 an SQL 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.98 – SEGMENTED_STRING_STORAGE_AREA
Another name for LIST STORAGE AREA.
3.99 – SET_TRANSACTION_MODES
Enables only the modes specified, disabling all other previously
defined modes. For example, if a database is to be used for read-
only access and you want to disable all other transaction modes,
use the following statement:
SQL> CREATE DATABASE FILENAME mf_personnel
cont> SET TRANSACTION MODES (READ ONLY);
If not specified, the default transaction mode is ALL.
Specifying a negated transaction 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 or use Oracle RMU, the Oracle Rdb
management utility.
3.100 – SHARED_MEMORY_IS_PROCESS_RESIDENT
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.
3.101 – SHARED_MEMORY
Syntax options:
SHARED MEMORY IS SYSTEM | SHARED MEMORY IS PROCESS
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.
3.102 – 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.103 – SNAPSHOT_CHECKSUM_ALLOCATION
Syntax option:
SNAPSHOT CHECKSUM ALLOCATION IS ENABLED
SNAPSHOT CHECKSUM ALLOCATION IS DISABLED
See the CHECKSUM ALLOCATION clause for details.
3.104 – SNAPSHOT_IS_ENABLED
Syntax options:
SNAPSHOT IS ENABLED IMMEDIATE | SNAPSHOT IS ENABLED DEFERRED
Specifies when read/write transactions write database changes
they make to the snapshot file used by read-only transactions.
The default is ENABLED IMMEDIATE 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.
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
attempt to start after an active read/write transaction starts
must wait for all active read/write users to complete their
transactions.
3.105 – SNAPSHOT_EXTENT
Syntax options:
SNAPSHOT EXTENT IS extent-pages
SNAPSHOT EXTENT IS (extension-options)
Specifies the number of pages of each snapshot or storage area file
extent. The default extent for storage area files is 99 pages.
Specify a number of pages for simple control over the extension.
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.106 – SNAPSHOT_FILENAME
Provides a separate file specification for the storage area
snapshot file. The SNAPSHOT FILENAME argument can only be used
with a multifile database.
In a multifile database, the file specification is used for the
RDB$SYSTEM storage area snapshot file, unless the CREATE DATABASE
statement contains a CREATE STORAGE AREA RDB$SYSTEM clause that
contains its own SNAPSHOT FILENAME clause.
Do not specify a file extension other than .snp to the snapshot
file specification. Oracle Rdb will assign the extension .snp
to the file specification, even if you specify an alternate
extension.
If you omit the SNAPSHOT FILENAME argument, the .snp file gets
the same device, directory, and file name as the database root
file.
3.107 – SNAPSHOT_IS_DISABLED
Specifies that snapshot writing is disabled. Snapshot writing
is enabled by default. If you specify the SNAPSHOT IS DISABLED
option, you cannot specify either of the SNAPSHOT IS ENABLED
options, and you cannot back up the database on line. You can,
however, continue to set snapshot options in the event that you
will enable snapshots in the future. SQL warns you of a possible
conflict in the setting of snapshot options while snapshots are
disabled, but SQL will execute the statement.
3.108 – SNAPSHOT_IS_ENABLED
Syntax options:
SNAPSHOT IS ENABLED IMMEDIATE | SNAPSHOT IS ENABLED DEFERRED
Specifies when read/write transactions write database changes
they make to the snapshot file used by read-only transactions.
The default is ENABLED IMMEDIATE 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.
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
attempt to start after an active read/write transaction starts
must wait for all active read/write users to complete their
transactions.
3.109 – 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.110 – storage-area-params
Parameters that control the characteristics of database storage
area files. You can specify most storage area parameters for
either single-file or multifile databases, but the effect of the
clauses differs.
o For single-file databases, the storage area parameters
specify the characteristics for the single storage area in
the database.
o For multifile databases, the storage area parameters specify
a set of default values for any storage areas created by
the CREATE DATABASE statement that do not specify their own
values for the same parameters. The default values apply to
the RDB$SYSTEM storage area, plus any others named in CREATE
STORAGE AREA database elements.
The CREATE STORAGE AREA clauses in a CREATE DATABASE statement
can override these default values. The default values do
not apply to any storage areas created later with the ALTER
DATABASE statement.
3.111 – 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.112 – SYSTEM_INDEX_(COMPRESSION_IS_._._._)
This clause allows the database creator choose compressed system
indexes. The default is SYSTEM INDEX (COMPRESSION IS DISABLED).
If enabled Oracle Rdb uses run-length compression, which
compresses any sequences of two or more spaces from text data
types or two or more binary zeros from non-character data
types. Compressing system indexes results in reduced storage
and improved I/O. Unless your applications frequently perform
concurrent data definition, you should compress system indexes.
Once you create a database specifying the SYSTEM INDEX
(COMPRESSION IS . . . ) clause, you only can change it using the
EXPORT and IMPORT statements. You cannot alter the database to
change the compression mode.
The clause SYSTEM INDEX COMPRESSION IS is identical to this
clause and is retained for compatibility with older versions
of Oracle Rdb.
3.113 – SYSTEM_INDEX_(PREFIX_CARDINALITY_COLLECTION_IS_._._._)
This clause allows the database creator to adjust the prefix
cardinality collection for system indices. Refer to the CREATE
INDEX Statement for more details on these clauses. The default is
PREFIX CARDINALITY COLLECTION IS ENABLED.
3.114 – SYSTEM_INDEX_(TYPE_IS_._._._)
This clause allows the database creator choose between SORTED or
SORTED RANKED indices for system table. The default is SORTED.
SORTED RANKED indices have advantages in space usage and reduced
CPU during DDL operations for those system indices with many
duplicates.
3.115 – 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.116 – THRESHOLD pages option
Syntax option:
THRESHOLD IS number-pages 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.117 – THRESHOLDS values
Syntax options
THRESHOLDS ARE ( val1 [,val2 [,val3] ] )
Specifies one, two, or three threshold values. The threshold
values represent a fullness percentage on a data page and
establish four possible ranges of guaranteed free space on the
data pages. When a data page reaches the percentage defined by a
given threshold value, the space area management (SPAM) entry for
the data page is updated to reflect the new fullness percentage
and its remaining free space.
The default thresholds are 70, 85, and 95 percent. If you
specify only one or two values, unspecified values default to
100 percent.
You cannot specify the THRESHOLDS storage area parameter for
single-file databases, and you cannot specify THRESHOLDS unless
you also explicitly specify PAGE FORMAT IS MIXED. To specify
thresholds for uniform storage areas, use the CREATE STORAGE MAP
statement.
For more information about setting space area management
parameters, see the Oracle Rdb Guide to Database Maintenance.
3.118 – USER username
Syntax option:
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.119 – USER_LIMIT
Syntax option:
USER LIMIT IS max-glo-buffers
Specifies the maximum number of global buffers each attach
allocates. Because global buffer pools are shared by all
attachments, you must define an upper limit on how many global
buffers a single attach can allocate. This limit prevents a user
from defining the RDM$BIND_BUFFERS logical name to use all the
buffers in the global buffer pool. (The behavior of RDM$BIND_
BUFFERS which depends on whether you are using local or global
buffers, is explained in the Oracle Rdb7 Guide to Database
Performance and Tuning.)
The user limit cannot be greater than the total number of global
buffers. The default is 5 buffers. The user limit appears as
"maximum global buffer count per user" in RMU Dump command
output.
Decide the maximum number of global buffers a process can
allocate per attach by dividing the total number of global
buffers set by the NUMBER IS clause by the total number of
attachments for which you want to guarantee access to the
database. For example, if the total number of global buffers is
200 and you want to guarantee at least 10 attachments access to
the database, set the maximum number of global buffers per attach
to 20.
In general, when you use global buffers, you should set the
maximum global buffer count per user higher than the default
database buffer count. 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,
therefore, in effect on a per node rather than a per process
basis.
3.120 – USING password
Syntax option:
USING 'password'
A character string literal that specifies the user's password for
the user name specified in the USER clause.
3.121 – txn-modes
Specifies the transaction modes for the database.
Mode Description
ALL All modes are enabled.
NONE No modes are enabled.
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 statement.
3.122 – 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.123 – 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.
A workload profile is a description of the interesting table
and column references used by queries in a database workload.
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 Analyze 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: Creating a single-file database
This command file example creates a single-file database that
contains one table, EMPLOYEES, made up of domains defined within
the CREATE DATABASE statement. The EMPLOYEES table has the same
definition as that in the sample personnel database.
For an example that creates a multifile version of the personnel
database, see the CREATE STORAGE_AREA.
SQL> -- By omitting a FILENAME clause, the database root file
SQL> -- takes the file name from the alias:
SQL> CREATE DATABASE ALIAS personnel
cont> --
cont> -- This CREATE DATABASE statement takes default
cont> -- database root file and storage area parameter values.
cont> --
cont> -- Create domains.
cont> -- Note that database elements do not terminate with semicolons.
cont> --
cont> CREATE DOMAIN ID_DOM CHAR(5)
cont> --
cont> CREATE DOMAIN LAST_NAME_DOM CHAR(14)
cont> --
cont> CREATE DOMAIN FIRST_NAME_DOM CHAR(10)
cont> --
cont> CREATE DOMAIN MIDDLE_INITIAL_DOM CHAR(1)
cont> --
cont> CREATE DOMAIN ADDRESS_DATA_1_DOM CHAR(25)
cont> --
cont> CREATE DOMAIN ADDRESS_DATA_2_DOM CHAR(20)
cont> --
cont> CREATE DOMAIN CITY_DOM CHAR(20)
cont> --
cont> CREATE DOMAIN STATE_DOM CHAR(2)
cont> --
cont> CREATE DOMAIN POSTAL_CODE_DOM CHAR(5)
cont> --
cont> CREATE DOMAIN SEX_DOM CHAR(1)
cont> --
cont> CREATE DOMAIN DATE_DOM DATE
cont> --
cont> CREATE DOMAIN STATUS_CODE_DOM CHAR(1)
cont> --
cont> -- Create a table:
cont> --
cont> CREATE TABLE EMPLOYEES
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT EMP_EMPLOYEE_ID_NOT_NULL
cont> NOT NULL
cont> NOT DEFERRABLE,
cont> LAST_NAME LAST_NAME_DOM,
cont> FIRST_NAME FIRST_NAME_DOM,
cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
cont> CITY CITY_DOM,
cont> STATE STATE_DOM,
cont> POSTAL_CODE POSTAL_CODE_DOM,
cont> SEX SEX_DOM,
cont> CONSTRAINT EMP_SEX_VALUES
cont> CHECK (
cont> SEX IN ('M', 'F') OR SEX IS NULL
cont> )
cont> NOT DEFERRABLE,
cont> BIRTHDAY DATE_DOM,
cont> STATUS_CODE STATUS_CODE_DOM,
cont> CONSTRAINT EMP_STATUS_CODE_VALUES
cont> CHECK (
cont> STATUS_CODE IN ('0', '1', '2')
cont> OR STATUS_CODE IS NULL
cont> )
cont> NOT DEFERRABLE
cont> )
cont> --
cont> -- End CREATE DATABASE by specifying a semicolon:
cont> ;
Example 2: Creating a database not using the repository
The following example:
o Creates the database root file acct.rdb in the default working
directory
o Creates the snapshot file acct.snp in the default working
directory
o Does not store the database definition in the repository
o Enables writing to the snapshot file
o Sets the allocation of the snapshot file to 200 pages
SQL> CREATE DATABASE ALIAS acct
cont> FILENAME acct
cont> SNAPSHOT IS ENABLED IMMEDIATE
cont> SNAPSHOT ALLOCATION IS 200 PAGES;
Example 3: Creating a database with the snapshot file disabled
This statement creates a database root file and, to save disk
space, disables snapshot writing and sets the initial allocation
size to 1.
SQL> CREATE DATABASE ALIAS PERS
cont> FILENAME personnel
cont> SNAPSHOT IS DISABLED
cont> SNAPSHOT ALLOCATION IS 1 PAGES;
Example 4: Creating a database with ANSI/ISO-style privileges
This statement creates a database in which all ANSI/ISO-style
privileges are granted to the creator of the database, WARRING,
and no privileges are granted to the identifier [*,*], the PUBLIC
identifier.
SQL> CREATE DATABASE ALIAS EXAMPLE
cont> FILENAME ansi_test
cont> PROTECTION IS ANSI;
SQL>
SQL> SHOW PROTECTION ON DATABASE EXAMPLE;
Protection on Alias EXAMPLE
[SQL,WARRING]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
Example 5: Creating a database with a German collating sequence
This statement creates a database named LITERATURE and specifies
a collating sequence named GERMAN (based on the GERMAN collating
sequence defined in the NCS library).
SQL> CREATE DATABASE FILENAME literature
cont> COLLATING SEQUENCE GERMAN GERMAN;
SQL> SHOW COLLATING SEQUENCE
User collating sequences in schema with filename LITERATURE
GERMAN
Example 6: Creating a database with global buffers
This statement creates a database named parts.rdb.
SQL> CREATE DATABASE ALIAS PARTS FILENAME parts
cont> GLOBAL BUFFERS ARE ENABLED (NUMBER IS 110, USER LIMIT IS 17);
Example 7: Creating a database specifying the database default
and national character sets
The following SQL statements create a database specifying the
database default character set of DEC_KANJI and the national
character set of KANJI. Use the SHOW DATABASE statement to see
the database settings.
SQL> SET DIALECT 'SQL99';
SQL> CREATE DATABASE FILENAME mia_char_set
cont> DEFAULT CHARACTER SET DEC_KANJI
cont> NATIONAL CHARACTER SET KANJI
cont> IDENTIFIER CHARACTER SET DEC_KANJI;
SQL> --
SQL> SHOW CHARACTER SET;
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
See the SHOW for information on the SHOW CHARACTER SETS
statement.
Example 8: This example demonstrates how to:
o Create a multifile database
o Reserve slots for journal files, storage areas, and row caches
o Restrict access to the database for the current session
o Enable system index compression, row caching, and workload
collection
o Disable statistics and cardinality collection
o Specify a default storage area
o Specify ROW as the lock-level default for the database
o Delay closing the database
o Create and assign a row cache to a storage area
o Specify the location of the recovery-unit journal file
SQL> CREATE DATABASE FILENAME sample
cont> SNAPSHOT IS DISABLED
cont> RESERVE 10 JOURNALS
cont> RESERVE 10 STORAGE AREAS
cont> RESERVE 5 CACHE SLOTS
cont> SYSTEM INDEX COMPRESSION IS ENABLED
cont> ROW CACHE IS ENABLED
cont> WORKLOAD COLLECTION IS ENABLED
cont> RESTRICTED ACCESS
cont> STATISTICS COLLECTION IS DISABLED
cont> CARDINALITY COLLECTION IS DISABLED
cont> LOCKING IS ROW LEVEL
cont> DEFAULT STORAGE AREA IS area1
cont> OPEN IS AUTOMATIC (WAIT 5 MINUTES FOR CLOSE)
cont> RECOVERY JOURNAL (LOCATION IS 'SQL_USER1:[DAY]')
cont> CREATE CACHE cache1
cont> CACHE SIZE IS 1000 ROWS
cont> ROW LENGTH IS 1000 BYTES
cont> CREATE STORAGE AREA area1
cont> CACHE USING cache1;
SQL>
SQL> SHOW DATABASE *;
Default alias:
Oracle Rdb database in file sample
Multischema mode is disabled
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Disabled
Carry over locks are enabled
Lock timeout interval is 0 seconds
Adjustable lock granularity is enabled (count is 3)
Global buffers are disabled (number is 250, user limit is 5,
page transfer via disk)
Journal fast commit is disabled
( checkpoint interval is 0 blocks,
checkpoint timed every 0 seconds,
no commit to journal optimization,
transaction interval is 256 )
AIJ File Allocation: 512
AIJ File Extent: 512
Statistics Collection is DISABLED
Unused Storage Areas: 10
Unused Journals: 10
System Index Compression is ENABLED
Restricted Access
Journal is Disabled
Backup Server: Manual
Log Server: Manual
Overwrite: Disabled
Notification: Disabled
Asynchronous Prefetch is Enabled (depth is 5)
Asynchronous Batch Write is Enabled (clean buffers 5, max buffers 4)
Lock Partitioning is DISABLED
Incremental Backup Scan Optim uses SPAM pages
Shutdown Time is 60 minutes
Unused Cache Slots: 5
Workload Collection is Enabled
Cardinality Collection is Disabled
Metadata Changes are Enabled
Row Cache is Enabled (Sweep interval is 1 second,
No Location)
Detected Asynch Prefetch is Enabled (depth is 4, threshold is 4)
Default Storage Area AREA1
Mode is Open Automatic (Wait 5 minutes for close)
RUJ File Location SQL_USER1:[DAY]
Database Transaction Mode(s) Enabled:
ALL
Dictionary Not Required
ACL based protections
Storage Areas in database with filename sample
RDB$SYSTEM List storage area.
AREA1 Default storage area.
Journals in database with filename sample
No Journals Found
Cache Objects in database with filename sample
CACHE1
SQL> SHOW CACHE cache1;
CACHE1
Cache Size: 1000 rows
Row Length: 1000 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Reserved Rows: 20
Sweep Rows: 3000
Reserving Slots for Sequences
No Sweep Thresholds
Allocation: 100 blocks
Extent: 100 blocks
Example 9: Reserving Slots for Sequences
SQL> CREATE DATABASE FILENAME many_sequences
cont> RESERVE 320 SEQUENCES;
Example 10: Creating a Database with a Row Cache
SQL> create database
cont> filename SAMPLE
cont> snapshot is disabled
cont> reserve 10 journals
cont> reserve 10 storage areas
cont> reserve 5 cache slots
cont> system index (compression is enabled, type sorted ranked)
cont> row cache is enabled
cont> workload collection is enabled
cont> restricted access
cont> default storage area is AREA1
cont> open is automatic (wait 5 minutes for close)
cont>
cont> create cache CACHE_AREA1
cont> shared memory is process
cont> row length is 1000 bytes
cont> cache size is 204 rows
cont> checkpoint all rows to backing file
cont>
cont> create storage area AREA1
cont> page format is UNIFORM
cont> cache using CACHE_AREA1
cont> ;
SQL>
SQL> show database *
Default alias:
Oracle Rdb database in file SAMPLE
Multischema mode is disabled
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Disabled
Carry over locks are enabled
Lock timeout interval is 0 seconds
Adjustable lock granularity is enabled (count is 3)
Global buffers are disabled (number is 250, user limit is 5,
page transfer via disk)
Journal fast commit is disabled
( checkpoint interval is 0 blocks,
checkpoint timed every 0 seconds,
no commit to journal optimization,
transaction interval is 256 )
AIJ File Allocation: 512
AIJ File Extent: 512
Statistics Collection is ENABLED
Unused Storage Areas: 10
Unused Journals: 10
Unused Cache Slots: 5
Unused Sequences: 32
Restricted Access
Journal is Disabled
Backup Server: Manual
Log Server: Manual
Overwrite: Disabled
Notification: Disabled
Asynchronous Prefetch is Enabled (depth is 5)
Asynchronous Batch Write is Enabled (clean buffers 5, max buffers 4)
Lock Partitioning is DISABLED
Incremental Backup Scan Optim uses SPAM pages
Shutdown Time is 60 minutes
Workload Collection is Enabled
Cardinality Collection is Enabled
Metadata Changes are Enabled
Row Cache is Enabled
Row cache: No Location
Row cache: checkpoint updated rows to backing file
Detected Asynch Prefetch is Enabled (depth is 4, threshold is 4)
Default Storage Area AREA1
Mode is Open Automatic (Wait 5 minutes for close)
No RUJ File Location
recovery journal buffers are in local memory
Database Transaction Mode(s) Enabled:
ALL
Shared Memory: Process
Large Memory: Disabled
Security Checking is External
System Index Compression is ENABLED
System Index:
Type is sorted ranked
Prefix cardinality collection is enabled
Logminer support is disabled
Galaxy support is disabled
Prestarted transactions are enabled
Dictionary Not Required
ACL based protections
Storage Areas in database with filename SAMPLE
AREA1 Default storage area
RDB$SYSTEM List storage area.
Journals in database with filename SAMPLE
No Journals Found
Cache Objects in database with filename SAMPLE
CACHE_AREA1