Information tables display internal information about storage
areas, after-image journals, row caches, database users, the
database root, and database character sets. Once the information
tables are created, you can use the INFO_TABLES.SQL script to
query the tables with the SQL interface.
Information tables are special read-only tables that can be
created in an Oracle Rdb database and used to retrieve database
attributes that are not stored in the existing relational tables.
Information tables allow interesting database information, which
is currently stored in an internal format, to be displayed as a
relational table.
The script, INFO_TABLES.SQL, is supplied as a part of the Oracle
Rdb kit in the SQL$SAMPLE directory.
1 – All Information Tables
The following information tables are supported:
Table Name Description
RDB$STORAGE_AREAS Displays information about the
database storage areas.
RDB$DATABASE_JOURNAL Displays information about the
default journal information.
RDB$CACHES Displays information about the
database row caches.
RDB$DATABASE_ROOT Displays information about the
database root.
RDB$JOURNALS Displays information about the
database journal files.
RDB$DATABASE_USERS Displays information about the
database users.
RDB$LOGICAL_AREAS Displays information about the
logical areas.
RDB$CHARACTER_SETS Displays information about the Oracle
Rdb character sets.
RDB$NLS_CHARACTER_SETS Displays the mapping of Oracle
NLS character sets to Oracle Rdb
character sets.
2 – Example
The following example shows how to query one of the information
tables created by the INFO_TABLES.SQL script:
SQL> SELECT * FROM RDB$LOGICAL_AREAS WHERE RDB$LOGICAL_AREA_NAME='JOBS';
RDB$LOGICAL_AREA_ID RDB$AREA_ID RDB$RECORD_LENGTH RDB$THRESHOLD1_PERCENT
RDB$THRESHOLD2_PERCENT RDB$THRESHOLD3_PERCENT RDB$ORDERED_HASH_OFFSET
RDB$RECORD_TYPE RDB$LOGICAL_AREA_NAME
95 7 41 0
0 0 0
1 JOBS
1 row selected
3 – RDB$STORAGE_AREAS
Displays information about the database storage areas.
Data
Column Name Type Description
RDB$LAST_BACKUP date vms Date of last backup
RDB$LAST_INCREMENT_ date vms Date of last restore
RESTORE
RDB$INITIAL_ALLOCATION integer Allocation
RDB$CURRENT_ALLOCATION integer Maximum page number
RDB$AREA_ACCESS_MODE integer Access mode
RDB$THRESHOLD1_BYTES integer First threshold value
RDB$THRESHOLD2_BYTES integer Second threshold value
RDB$THRESHOLD3_BYTES integer Third threshold value
RDB$PAGE_SIZE integer Page size
RDB$AREA_MIN_EXTENT integer Minimum page extent
RDB$AREA_MAX_EXTENT integer Maximum page extent
RDB$AREA_PERCENT_EXTENT integer Percent growth
RDB$EXTEND_COUNT integer Number of times area has
been extended
RDB$SPAM_INTERVAL integer Number of pages per SPAM
page
RDB$JOURNAL_SEQUENCE integer AIJ recovery version number
RDB$MAX_PAGE_FREE_SPACE integer Maximum free space on new
page
RDB$MAX_ROW_LENGTH integer Largest segment that can be
stored on a page
RDB$SEQUENCE integer Sequence number
RDB$AREA_ID integer Area ID
RDB$LINKED_AREA_ID integer For live storage area,
ID of snapshot area. For
snapshot area, ID of live
storage area
RDB$FLAGS integer Flags
RDB$THRESHOLD1_PERCENT integer First threshold percent
value
RDB$THRESHOLD2_PERCENT integer Second threshold percent
value
RDB$THRESHOLD3_PERCENT integer Third threshold percent
value
RDB$AREA_FILE char(255) Storage area file name
RDB$CACHE_ID integer Row cache ID
RDB$AREA_NAME char(31) Storage area name
RDB$CLUMP_PAGE_COUNT integer The number of pages per
logical area clump (UNIFORM
area)
RDB$PAGE_LENGTH integer Page length in bytes
RDB$PAGES_PER_SPAM_PAGE_ integer Number of data pages per
P1 SPAM page + 1
RDB$SPAM_PAGE_VECTOR_ integer SPAM page vector length in
LENGTH bytes
RDB$PAGE_PAD_LENGTH integer Page padding length in
bytes
RDB$MAX_SEGMENT_LENGTH integer Largest length to which an
existing segment can grow
RDB$BACKUP_STATISTICS bigint Backup-specific statistics
RDB$SNAPS_ENABLED_TSN bigint TSN of oldest snapshots. If
this is a live storage area
that has snapshots allowed,
this field contains the
TSN of the most recent
transaction that enabled
snapshots for or performed
exclusive updates to this
area
RDB$COMMIT_TSN bigint TSN to which this area is
consistent. If the area
is marked inconsistent,
this is the TSN level to
which the area is currently
consistent
RDB$BACKUP_TSN bigint TSN of last full area
backup. If this field
contains zero, this area
has not been backed up
RDB$INCR_BACKUP_TSN bigint TSN of last incremental
area backup. If this field
contains zero, this area
has not been incrementally
backed up
RDB$LAST_EXTEND date vms Date of last area extend
3.1 – RDB$FLAGS
Represents flags for RDB$STORAGE_AREAS information table.
Bit
Position Description
0 Page format is mixed
1 Checksum calculation is enabled
2 This is a snapshot area
3 Area has snapshots
4 Snapshots are enabled
5 Area has space management pages
6 SPAM pages are enabled
7 Allow multi-volume disk extents
8 Extent is enabled
9 Area is corrupt
10 Area is inconsistent
11 WORM device is full
12 Page level locking
13 Write once
14 WORM area is corrupt
15 Row cache is enabled
16 Area has been restructured. If TRUE, this area has been
restructured and may only be readied by a restructuring
process
17 Roll forward quiet-point is enabled
18 Area is corrupt due to Hot Standby. If TRUE, this
storage area may have been corrupted by an aborted
batch update transaction
4 – RDB$JOURNALS
Displays information about the database journal files.
Column Name Data Type Description
RDB$LAST_BACKUP date vms Date of last AIJ
backup
RDB$STATUS integer AIJ file is
inaccessible
RDB$ALLOCATION integer Allocation
RDB$EXTENT integer Extent
RDB$FLAGS integer Flags
RDB$JOURNAL_NAME char(31) Journal name
RDB$LAST_BACKUP_SEQUENCE integer Last version number
backed up
RDB$LAST_ACTIVATED date vms Date last activated
RDB$BACKUP_EDIT_STRING varchar(255) Backup editname file
RDB$DEFAULT_FILENAME char(255) Default journal name
RDB$FILENAME char(255) Journal name
RDB$BACKUP_FILENAME char(255) Backup file name
RDB$SEQUENCE_NUMBER integer Current AIJ sequence
number
RDB$STATE char(31) State ("Current" or
"Latent")
4.1 – RDB$FLAGS
Represents flags for RDB$JOURNALS information table.
Bit
Position Description
0 Initialization in progress
1 Backup in progress
2 AIJ file has been modified
3 AIJ restored from existing file
4 Hard data loss resulted from fail over
5 Soft data loss resulted from fail over
6 New version of journal created
7 Journal has been overwritten
8 Backup failed prematurely
9 Journal created due to switch-over suspension
10 AIJ file block has been assigned
11 Journal created for recovery synchronization
5 – RDB$CACHES
Displays information about the database row caches.
Data
Column Name Type Description
RDB$ALLOCATION integer Allocation
RDB$EXTENT integer Extent
RDB$CACHE_ID integer Cache ID
RDB$FLAGS integer Flags
RDB$ROW_LENGTH integer Row length
RDB$CACHE_SIZE integer Cache size
RDB$WINDOW_COUNT integer Window count
RDB$NUM_RESERVED_ROWS integer Number of reserved rows
RDB$NUM_SWEEP_ROWS integer Number of sweep rows
RDB$CACHE_NAME char(31) Cache name
RDB$LOCATION char(255) Cache location
RDB$SNAP_CACHE_SIZE integer Number of snapshot record
slots in cache
RDB$PHYSICAL_MEMORY bigint Physical memory in bytes
5.1 – RDB$FLAGS
Represents flags for RDB$CACHES information table.
Bit
Position Description
0 Shared memory is system
1 Large memory is enabled
2 Row replacement is enabled
3 Device/directory is defined
4 Updated rows to database
5 Updated rows to backing file
6 All rows to backing file
7 Snapshots are enabled
6 – RDB$DATABASE_ROOT
Displays information about the database root.
Data
Column Name Type Description
RDB$CREATED date vms Date of database
creation
RDB$LAST_FULL_BACKUP_TIME date vms Date of last complete
full backup
RDB$MAJOR_VERSION integer Major software version
RDB$MINOR_VERSION integer Minor software version
RDB$PRIOR_MAJOR_VERSION integer Major software version
for database converted
with /nocommit
RDB$PRIOR_MINOR_VERSION integer Minor software version
for database converted
with /nocommit
RDB$FLAGS integer Flags
RDB$MAX_USERS integer Number of users
RDB$MAX_CLUSTER_NODES integer Number of cluster
nodes
RDB$DEFAULT_NUM_OF_BUFFERS integer Number of buffers
RDB$MAX_RECOVERY_BUFFERS integer Number of recover
buffers
RDB$BUFFER_SIZE integer Buffer size
RDB$GLOBAL_BUFFER_COUNT integer Number of global
buffers
RDB$ALG_COUNT integer Adjustable lock
granularity count
RDB$LOCK_TIMEOUT_INTERVAL integer Lock timeout interval
in seconds
RDB$CHECKPOINT_INTERVAL_BLKS integer Checkpoint block
interval
RDB$CHECKPOINT_TIMED_SECONDS integer Checkpoint time
interval
RDB$TRANSACTION_INTERVAL integer Commit transaction
interval
RDB$CLOSE_MODE integer Close mode
RDB$CLOSE_INTERVAL integer Close interval
RDB$ASYNC_PREFETCH_DEPTH integer Async prefetch depth
RDB$D_ASYNC_PREFETCH_DEPTH integer Detected async
prefetch depth
RDB$D_ASYNC_PREFETCH_THRESHOLD integer Detected async
prefetch threshold
RDB$CLEAN_BUFFER_COUNT integer Clean buffer count
RDB$MAX_BUFFER_COUNT integer Maximum buffer count
RDB$MIN_PAGE_SIZE_BLOCKS integer Minimum area page
block count
RDB$MAX_PAGE_SIZE_BLOCKS integer Maximum area page
block count
RDB$TRANSACTION_MODE_FLAGS integer Transaction mode
RDB$ALG_FACTOR_0 integer Adjustable locking
granularity factor 0
RDB$ALG_FACTOR_1 integer Adjustable locking
granularity factor 1
RDB$ALG_FACTOR_2 integer Adjustable locking
granularity factor 2
RDB$ALG_FACTOR_3 integer Adjustable locking
granularity factor 3
RDB$ALG_FACTOR_4 integer Adjustable locking
granularity factor 4
RDB$ALG_FACTOR_5 integer Adjustable locking
granularity factor 5
RDB$ALG_FACTOR_6 integer Adjustable locking
granularity factor 6
RDB$ALG_FACTOR_7 integer Adjustable locking
granularity factor 7
RDB$AUDIT_FILENAME char(255) Audit journal file
name
RDB$ROOT_FILENAME char(255) Database root file
name
RDB$RUJ_LOCATION char(255) Default recovery-unit
journal file name
RDB$CACHE_LOCATION char(255) Default
device/directory
specification for
record cache files
RDB$MAX_PAGES_IN_BUFFER integer Maximum number of
pages in a buffer
RDB$RCS_SWEEP_INTERVAL integer Row cache server (RCS)
sweep interval (in
seconds)
RDB$RCS_CKPT_TIME integer Time interval to force
row cache server (RCS)
to checkpoint. This
field contains the
number of seconds
that pass before RCS
is forced to perform
another checkpoint
RDB$LAST_FULL_RESTORE date vms Date of last complete
full restore
RDB$AIJ_ACTIVATION_ID bigint AIJ journaling
activation identifier
RDB$RCVR_ACTIVATION_ID bigint RCVR journaling
activation identifier
RDB$OPER_CLASS integer Operator notification
classes
RDB$PRESTART_TXN_TIMEOUT integer Seconds until
prestarted transaction
is abandoned. Zero
means no abandon timer
RDB$DB_REPLICATED integer AIJ log roll forward
server started
6.1 – RDB$FLAGS
Represents flags for RDB$DATABASE_ROOT information table.
Bit
Position Description
0 Single file database
1 Open mode
2 Log server mode
3 Snapshots are deferred
4 Global buffers are enabled
5 Carryover locks are enabled
6 Statistics collection is enabled
7 Fast commit is enabled
8 AIJ commit optimization is enabled
9 RUJ is corrupt
10 Database is corrupt
11 Fast incremental backup is enabled
12 Async prefetch is enabled
13 Async batch writes are enabled
14 Lock partitioning is enabled
15 Page transfer via memory
16 Detected async prefetch is enabled
17 Shared memory is system
18 Database has been modified (TSN allocated)
19 Database conversion has been committed
20 Row cache server (RCS) checkpoints to database, by
default
21 RCS checkpoints to backing store files, by default
22 RCS checkpoints marked and unmarked to RDC
23 Global buffers should be in VLM
24 Row cache RUJ global buffers are disabled
25 LogMiner feature is enabled
26 Prestarted transactions are enabled
27 VMS Galaxy shared memory is enabled
7 – RDB$DATABASE_JOURNAL
Displays information about the default journal information.
Data
Column Name Type Description
RDB$CONDITION integer AIJ status
RDB$DEFAULT_ALLOCATION integer Default allocation in
blocks
RDB$DEFAULT_EXTENT integer Default extension in blocks
RDB$CURRENT_BACKUP_ integer Backup sequence number
SEQUENCE
RDB$CURR_RECOVERY_ integer Recovery sequence number
SEQUENCE
RDB$DATABASE_BACKUP_ integer Database backup sequence
SEQUENCE number
RDB$ALLOCATION integer Number of allocated AIJ
file blocks
RDB$SHUTDOWN_TIME_MIN integer Shutdown time in minutes
RDB$OPERATOR_CLASSES integer Operator class
RDB$FLAGS integer Flags
RDB$DEFAULT_BACKUP_ char(255) Default backup file name
FILENAME
RDB$CACHE_FILENAME char(255) Cache file name
RDB$STANDBY_FILENAME char(255) Standby database file name
RDB$SERVER_NAME char(31) Server name
RDB$BACKUP_EDIT_STRING varchar(25Backup editname file
RDB$REMOTE_NODE_NAME char(31) Remote node name
RDB$CUR_ACTIVE_AIJ integer Current active AIJ journal
index
RDB$MASTER_FILENAME char(255) When database replication
is active on the standby
database, this field
contains the file name
of the master database
7.1 – RDB$FLAGS
Represents flags for RDB$DATABASE_JOURNAL information table.
Bit
Position Description
0 Journaling is enabled
1 Overwrite is enabled
2 Backup mode
3 New journal version
4 ABS uses quiet-point AIJ backup
5 Replicated as master
6 Replicated as standby
7 Master replication database
8 Database replication online
9 Hot Standby quiet-point
10 Hot Standby is enabled
11 Database changes made when AIJ disabled
12 One or more journals overwritten
13 Hard data loss resulted from fail over
14 Full quiet-point AIJ backup required
8 – RDB$DATABASE_USERS
Displays information about the database users.
Data
Column Name Type Description
RDB$PROCESS_ID integer Process ID number
RDB$STREAM_ID integer Stream ID number
RDB$MONITOR_ID integer Monitor ID number
RDB$ATTACH_ID integer Attach ID number
RDB$FLAGS integer Flags
8.1 – RDB$FLAGS
Represents flags for RDB$DATABASE_USERS information table.
Bit
Position Description
0 Client server process
1 AIJ log server
2 Process is being recovered
3 Database server process
4 Database utility process
5 Catch-up server
6 AIJ roll forward server
7 Row cache server
8 Log shipping server
9 Backup server
9 – RDB$LOGICAL_AREAS
Displays information about the logical areas.
Data
Column Name Type Description
RDB$LOGICAL_AREA_ID integer Logical area ID
RDB$AREA_ID integer Physical area ID
RDB$FLAGS integer Flags
RDB$RECORD_LENGTH integer Record length
RDB$THRESHOLD1_PERCENT integer First threshold percent
value
RDB$THRESHOLD2_PERCENT integer Second threshold percent
value
RDB$THRESHOLD3_PERCENT integer Third threshold percent
value
RDB$ORDERED_HASH_OFFSET integer Ordered hash offset
RDB$RECORD_TYPE integer AIP record type
RDB$LOGICAL_AREA_NAME char(31) Logical area name
9.1 – RDB$FLAGS
Represents flags for RDB$LOGICAL_AREAS information table.
Bit
Position Description
0 Logical area uses hash ordered index
1 Logical area modified with unjournaled records
2 Nologging is enabled
9.2 – RDB$RECORD_TYPE
Represents AIP record types for RDB$LOGICAL_AREAS information
table.
AIP Record
Types Description
0 Unknown
1 Table
2 Sorted index
3 Hashed index
4 System record
5 Segmented string
10 – RDB$CHARACTER_SETS
Displays information about the Oracle Rdb character sets.
Data
Column Name Type Description
RDB$LOWCASE_MAPPING varbyte Segmented string containing
list a 256 byte table used for
lowercasing characters
RDB$UPCASE_MAPPING varbyte Segmented string containing
list a 256 byte table used for
uppercasing characters
RDB$ASSOCIATED_ integer Identifier of the associated
CHARACTER_SET character set
RDB$CHARACTER_SET_ID integer Character set identifier
RDB$CHARACTER_SET_NAME char(31) Character set name
RDB$CHARACTER_WILDCARD integer Character used as wildcard
character
RDB$FLAGS integer Character set flags
RDB$FORM_OF_USE integer Character set form-of-use
indicator
RDB$IDENTIFIER_ integer Character set ID of the
CHARACTER_SET indentifier character set
RDB$MAXIMUM_OCTETS integer Maximum number of octets per
character
RDB$MINIMUM_OCTETS integer Minimum number of octets per
character
RDB$REPERTOIRE integer Character set repertoire
RDB$SPACE_CHARACTER integer Character used as space
RDB$STRING_WILDCARD integer Character used as string
wildcard
RDB$VERSION integer Version number of character
set entry
10.1 – RDB$REPERTOIRE
Represents the repertoire values for RDB$CHARACTER_SETS
information table.
Value Name Description
0 OTHER Non-specific repertoire
1 LATIN Contains mainly Latin characters
2 JAPANESE Contains mainly Japanese characters
3 SIMPLE_ Contains mainly simplified Chinese
CHINESE characters
4 KOREAN Contains mainly Korean characters
5 OLD_CHINESE Contains mainly traditional Chinese
characters
6 UNIVERSAL Contains universal characters; for
example, UNICODE
7 INDIAN Contains mainly Indian characters
8 ARABIC Contains mainly Arabic characters
9 GREEK Contains mainly Greek characters
10 CYRILLIC Contains mainly Cyrillic characters
11 HEBREW Contains mainly Hebrew characters
10.2 – RDB$FORM_OF_USE
Represents the form-of-use values for RDB$CHARACTER_SETS
information table.
Value Name Description
0 FIXED_OCTET Fixed octet
1 MIXED_OCTET Mixed octet with DEC_KANJI style
encoding
2 FIXED_NO_UP Fixed octet, no uppercasing allowed
3 MIXED_SS2 As in MIXED_OCTET plus <SS2> as
introducer to alternate single octet
encoding
4 FIXED_UP_G1 Fixed octet, uppercasing only 7-bit
characters
5 MIXED_SS3 As in MIXED_SS2 plus <SS3> as introducer
to alternate double octet encoding
6 FIXED_OTHER Fixed octet, other
7 MIXED_C2CB As in MIXED_OCTET plus hex 'C2CB' as
introducer to alternate double octet
encoding
9 MIXED_TAG Mixed octet with leading tag, compound
string
11 MIXED_SHIFT Mixed octet with coding table shifted;
for example, SHIFT_JIS
10.3 – RDB$FLAGS
Represents flags for RDB$CHARACTER_SETS information table.
Bit
Position Name Description
0 CONTAINS_ASCII Character set contains 7-bit
ASCII characters
1 SPACE_OCTET_REPEATS All octets of the multi-octet
space character are the same
value
11 – RDB$NLS_CHARACTER_SETS
Represents the mapping of Oracle NLS character sets to Oracle Rdb
character sets.
Data
Column Name Type Description
RDB$CHARACTER_SET_ integer Character set identifier
ID
RDB$NLS_ID integer Oracle NLS identifier of
character set
RDB$NLS_NAME char(31) Oracle NLS character set name