Example 1: Changing a read/write storage area to a read-only
storage area
This example changes the SALARY_HISTORY storage area from a
read/write storage area to a read-only storage area.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ALTER STORAGE AREA salary_history
cont> READ ONLY;
Example 2: Adding multiple, fixed-size journal files
This example demonstrates reserving slots for journal files,
enabling the journaling feature, and adding multiple, fixed-size
journal files.
SQL> CREATE DATABASE FILENAME test
cont> RESERVE 5 JOURNALS
cont> CREATE STORAGE AREA sa_one
cont> ALLOCATION IS 10 PAGES;
SQL> DISCONNECT ALL;
SQL>
SQL> ALTER DATABASE FILENAME test
cont> JOURNAL IS ENABLED
cont> ADD JOURNAL AIJ_ONE
cont> FILENAME aij_one
cont> BACKUP FILENAME aij_one
cont> ADD JOURNAL AIJ_TWO
cont> FILENAME aij_two
cont> BACKUP FILENAME aij_two
cont> ;
You should place journal files and backup files on disks other
than those that contain the database.
Example 3: Reserving and using slots for storage areas
This example demonstrates reserving slots for storage areas
and adding storage areas to the database that utilizes those
slots. Use the SHOW DATABASE statement to see changes made to the
database.
SQL> CREATE DATABASE FILENAME sample
cont> RESERVE 5 STORAGE AREAS
cont> CREATE STORAGE AREA RDB$SYSTEM
cont> FILENAME sample_system
cont> --
cont> -- Storage areas created when the database is created do not use
cont> -- the reserved storage area slots because this operation is being
cont> -- executed off line.
cont> --
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future
recovery
SQL> --
SQL> -- Reserving storage area slots is not a journaled activity.
SQL> --
SQL> -- To use the reserved slots, you must alter the database and
SQL> -- add storage areas.
SQL> --
SQL> DISCONNECT ALL;
SQL> ALTER DATABASE FILENAME sample
cont> ADD STORAGE AREA SAMPLE_1
cont> FILENAME sample_1
cont> ADD STORAGE AREA SAMPLE_2
cont> FILENAME sample_2;
Example 4: Reserving Slots for Sequences
This example shows that reserving extra sequences in the database
adds to the existing 32 that are provided by default, and the
count rounded up to the next multiple of 32 (that is, 64).
$ SQL$ ALTER DATABASE FILENAME MF_PERSONNEL RESERVE 10 SEQUENCES;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
$ RMU/DUMP/HEADER=SEQUENCE MF_PERSONNEL
*------------------------------------------------------------------------------
* Oracle Rdb V7.1-200 15-AUG-2003 14:54:26.55
*
* Dump of Database header
* Database: USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1
*
*------------------------------------------------------------------------------
Database Parameters:
Root filename is "USER2:[DOCS.WORK]MF_PERSONNEL.RDB;1"
Sequence Numbers...
.
.
.
Client sequences...
- 64 client sequences have been allocated
- 0 client sequences in use
Example 5: Adding and Enabling a Row Cache on OpenVMS
The MF_PERSONNEL database is altered to add a row cache, apply it
to several storage areas and enable row caching. The example
further assumes that after image journals have already been
defined for the database, they are required for the JOURNAL IS
ENABLED clause to succeed.
SQL> /*
***> Prepare the database for ROW CACHE, include extra
***> capacity for later additions
***> */
SQL> alter database
cont> filename MF_PERSONNEL
cont> number of cluster nodes is 1
cont> journal is ENABLED (fast commit is enabled)
cont> reserve 20 cache slots
cont> row cache is ENABLED
cont>
cont> /*
***> Create a physical cache for all the employee rows
***> */
cont> add cache EMPIDS_RCACHE
cont> shared memory is SYSTEM
cont> row length is 126 bytes
cont> cache size is 204 rows
cont> checkpoint updated rows to database
cont>
cont> /*
***> Apply the cache to each of the relevant storage areas
***> */
cont> alter storage area EMPIDS_LOW
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_MID
cont> cache using EMPIDS_RCACHE
cont> alter storage area EMPIDS_OVER
cont> cache using EMPIDS_RCACHE
cont> ;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
Example 6: Establishing a Timeout Value for Prestarted
Transactions
SQL> ALTER DATABASE
cont> FILENAME SAMPLE
cont> PRESTARTED TRANSACTIONS ARE ENABLED
cont> (WAIT 90 SECONDS FOR TIMEOUT)
cont> ;
Example 7: Altering a Database Specifying the SINGLE INSTANCE
Option
This example prepares a database to be run in a 4 node GALAXY
cluster. The SINGLE INSTANCE clause is used to enable special
optimizations that are available because of the galaxy shared
memory.
SQL> alter database
cont> filename MF_PERSONNEL
cont> galaxy support is ENABLED
cont> number of cluster nodes is 4 (single instance);
Example 8: Disabling storage if snapshot rows
The following example demonstrates using SQL to modify the "C1"
cache to disable storage of snapshot rows in cache and to modify
the "C5" cache to enable storage of snapshot rows in the cache
with a snapshot cache size of 12345 rows:
SQL> ALTER DATABASE FILE EXAMPLE_DB
cont> ALTER CACHE C1
cont> ROW SNAPSHOT IS DISABLED;
cont> ALTER CACHE C5
cont> ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12345 ROWS);
Example 9: Using the SWEEP INTERVAL clause
Here is an example of using the SWEEP INTERVAL clause.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ROW CACHE IS ENABLED (SWEEP INTERVAL IS 100 seconds)
cont> ;
SQL> attach 'filename MF_PERSONNEL';
SQL> show database rdb$dbhandle
Default alias:
Oracle Rdb database in file MF_PERSONNEL
.
.
.
Row Cache is Enabled
Row cache: sweep interval is 100 seconds
Row cache: No Location
Row cache: checkpoint updated rows to backing file
.
.
.