Skip Headers
Oracle® Transparent Gateway for DRDA Installation and User's Guide
10g Release 2 (10.2) for Microsoft Windows

Part Number B16218-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

A Oracle DB2 Data Dictionary Views

This appendix includes the Oracle Transparent Gateway for DRDA data dictionary views accessible to all users of an Oracle server. Most views can be accessed by any user with SELECT privileges for DB2 catalog tables.

N/A is used in the following tables to mean that the column is not valid for the gateway.

This appendix contains the following sections:

Supported Views

The following is a list of Oracle data dictionary views that are supported by the gateway for DB2/OS390, DB2/400, and DB2/UDB DRDA Servers. This release of the gateway does not have data dictionary view support for DB2/VM servers.

Data Dictionary View Tables

The remainder of this chapter contains tables describing data dictionary views. In the following descriptions, all are supported for DB2/OS390 and DB2/400.

ALL_CATALOG

All tables, views, synonyms, and sequences accessible to the user:

Column Name Description
OWNER Owner of the object
TABLE_NAME Name of the object
TABLE_TYPE Type of object

ALL_COL_COMMENTS

Comments on columns of accessible tables and views:

Column Name Description
OWNER Owner of the object
TABLE_NAME Object name
COLUMN_NAME Column name
COMMENTS Comments on column

ALL_CONS_COLUMNS

Information about accessible columns in constraint definitions:

Column Name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
TABLE_NAME Name associated with table with constraint definition
COLUMN_NAME Name associated with column specified in the constraint definition
POSITION Original position of column in definition

ALL_CONSTRAINTS

Constraint definitions on accessible tables:

Column Name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
CONSTRAINT_TYPE Type of constraint definition
TABLE_NAME Name associated with table with constraint definition
SEARCH_CONDITION Text of search condition for table check
R_OWNER Owner of table used in referential constraint
R_CONSTRAINT_NAME Name of unique constraint definition for referenced table
DELETE_RULE Delete rule for referential constraint
STATUS Status of constraint
DEFERRABLE Whether the constraint is deferrable
DEFERRED Whether the constraint was initially deferred
VALIDATED Whether all data obeys the constraint
GENERATED Whether the name of the constraint is user or system generated
BAD Constraint specifies a century in an ambiguous manner
RELY Whether an enabled constraint is enforced or unenforced
LAST_CHANGE When the constraint was last enabled or disabled
INDEX_OWNER N/A
INDEX_NAME N/A

ALL_INDEXES

Description of indexes on tables accessible to the user:

Column Name Description
OWNER Owner of the index
INDEX_NAME Name of the index
INDEX_TYPE Type of index
TABLE_OWNER Owner of the indexed object
TABLE_NAME Name of the indexed object
TABLE_TYPE Type of the indexed object
UNIQUENESS Uniqueness status of the index
COMPRESSION N/A
PREFIX_LENGTH 0
TABLESPACE_NAME Name of the tablespace containing the index
INI_TRANS N/A
MAX_TRANS N/A
INITIAL_EXTENT N/A
NEXT_EXTENT N/A
MIN_EXTENTS N/A
MAX_EXTENTS N/A
PCT_INCREASE N/A
PCT_THRESHOLD Threshold percentage of block space permitted per index entry
INCLUDE_COLUMN Column ID of the last column to be included in index-organized table
FREELISTS Number of process free lists allocated to this segment
FREELIST_GROUPS Number of free list groups allocated to this segment
PCT_FREE N/A
LOGGING Logging information
BLEVEL Depth of the index from its root block to its leaf blocks. A depth of 1 indicates that the root block and the leaf block are the same.
LEAF_BLOCKS Number of leaf blocks in the index
DISTINCT_KEYS Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table.
AVG_LEAF_BLOCKS_PER_KEY N/A
AVG_DATA_BLOCKS_PER_KEY N/A
CLUSTERING_FACTOR N/A
STATUS State of the index: VALID
NUM_ROWS Number of rows in the index
SAMPLE_SIZE Size of the sample used to analyze the index
LAST_ANALYZED Date on which this index was most recently analyzed
DEGREE Number of threads per instance for scanning the index
INSTANCES Number of instances across which the index is to be scanned
PARTITIONED Whether this index is partitioned
TEMPORARY Whether the index is on a temporary table
GENERATED Whether the name of the index is system generated
SECONDARY N/A
BUFFER_POOL Whether the index is a secondary object
USER_STATS N/A
DURATION N/A
PCT_DIRECT_ACCESS N/A
ITYP_OWNER N/A
ITYP_NAME N/A
PARAMETERS N/A
GLOBAL_STATS N/A
DOMIDX_STATUS N/A
DOMIDX_OPSTATUS N/A
FUNCIDX_STATUS N/A
JOIN_INDEX N/A
IOT_REDUNDANT_PKEY_ELIM N/A

ALL_IND_COLUMNS

ALL_IND_COLUMNS describes the columns of indexes on all tables that are accessible to the current user.

Column Names Description
INDEX_OWNER Owner of the index
INDEX_NAME Name of the index
TABLE_OWNER Owner of the table or cluster
TABLE_NAME Name of the table or cluster
COLUMN_NAME Column name or attribute of object type column
COLUMN_POSITION Position of column or attribute within the index
COLUMN_LENGTH Indexed length of the column
CHAR_LENGTH Maximum code point length of the column
DESCEND Whether the column is sorted in descending order (Y/N)

ALL_OBJECTS

Objects accessible to the user:

Column Name Description
OWNER Owner of the object
OBJECT_NAME Name of object
SUBOBJECT_NAME Name of the subobject
OBJECT_ID Object number of the object
DATA_OBJECT_ID Dictionary object number of the segment that contains the object
OBJECT_TYPE Type of object
CREATED N/A
LAST_DDL_TIME N/A
TIMESTAMP N/A
STATUS State of the object
TEMPORARY Whether the object is temporary
GENERATED Was the name of this object system-generated?
SECONDARY N/A

ALL_SYNONYMS

All synonyms accessible to the user:

Column Name Description
OWNER Owner of the synonym
SYNONYM_NAME Name of the synonym
TABLE_OWNER Owner of the object referenced by the synonym
TABLE_NAME Name of the object referenced by the synonym
DB_LINK N/A

ALL_TABLES

Description of tables accessible to the user:

Column Name Description
OWNER Owner of the table
TABLE_NAME Name of the table
TABLESPACE_NAME Name of the tablespace containing the table
CLUSTER_NAME N/A
IOT_NAME Name of the index-organized table
PCT_FREE N/A
PCT_USED N/A
INI_TRANS N/A
MAX_TRANS N/A
INITIAL_EXTENT N/A
NEXT_EXTENT N/A
MIN_EXTENTS N/A
MAX_EXTENTS N/A
PCT_INCREASE N/A
FREELISTS Number of process free lists allocated to this segment
FREELIST_GROUPS Number of free list groups allocated to this segment
LOGGING Logging attribute
BACKED_UP N/A
NUM_ROWS Number of rows in the table
BLOCKS N/A
EMPTY_BLOCKS N/A
AVG_SPACE N/A
CHAIN_CNT N/A
AVG_ROW_LEN Average length of a row in the table in bytes
AVG_SPACE_FREELIST_BLOCKS The average free space of all blocks on a free list
NUM_FREELIST_BLOCKS The number of blocks on the free list
DEGREE The number of threads per instance for scanning the table
INSTANCES The number of instances across which the table is to be scanned
CACHE Whether the cluster is to be cached in the buffer cache
TABLE_LOCK Whether table locking is enabled or disabled
SAMPLE_SIZE Sample size used in analyzing this table
LAST_ANALYZED Date on which this table was most recently analyzed
PARTITIONED Indicates whether this table is partitioned
IOT_TYPE Whether this is an index-organized table
TEMPORARY Can the current session only see data that it placed in this object itself?
SECONDARY N/A
NESTED Whether the table is a nested table
BUFFER_POOL The default buffer pool for the object
ROW_MOVEMENT N/A
GLOBAL_STATS N/A
USER_STATS N/A
DURATION N/A
SKIP_CORRUPT N/A
MONITORING N/A
CLUSTER_OWNER N/A
DEPENDENCIES N/A
COMPRESSION N/A

ALL_TAB_COLUMNS

Columns of all tables, views, and clusters accessible to the user:

Column Name Description
OWNER Owner of the table or view
TABLE_NAME Table or view name
COLUMN_NAME Column name
DATA_TYPE Data type of column
DATA_TYPE_MOD Data type modifier of the column
DATA_TYPE_OWNER Owner of the data type of the column
DATA_LENGTH Maximum length of the column in bytes
DATA_PRECISION N/A
DATA_SCALE Digits to the right of decimal point in a number
NULLABLE Does the column permit nulls? Value is n if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key.
COLUMN_ID Sequence number of the column as created
DEFAULT_LENGTH N/A
DATA_DEFAULT N/A
NUM_DISTINCT Number of distinct values in each column of the table
LOW_VALUE For tables with more than three rows, the second lowest and second highest values. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE N/A
DENSITY N/A
NUM_NULLS The number of nulls in the column
NUM_BUCKETS The number of buckets in histogram for the column
LAST_ANALYZED The date on which this column was most recently analyzed
SAMPLE_SIZE The sample size used in analyzing this column
CHARACTER_SET_NAME The name of the character set
CHAR_COL_DECL_LENGTH The length of the character set
GLOBAL_STATS N/A
USER_STATS N/A
AVG_COL_LEN Average length of the column (in bytes)
CHAR_LENGTH Displays the length of the column in characters
CHAR_USED N/A

ALL_TAB_COMMENTS

Comments on tables and views accessible to the user:

Column Name Description
OWNER Owner of the object
TABLE_NAME Name of the object
TABLE_TYPE Type of object
COMMENTS Comments on the object

ALL_USERS

Information about all users of the database:

Column Name Description
USERNAME Name of the user
USER_ID N/A
CREATED N/A

ALL_VIEWS

Text of views accessible to the user:

Column Name Description
OWNER Owner of the view
VIEW_NAME Name of the view
TEXT_LENGTH Length of the view text
TEXT View text. Only the first row of text is returned, even if multiple rows exist.
TYPE_TEXT_LENGTH Length of the type clause of the typed view
TYPE_TEXT Type clause of the typed view
OID_TEXT_LENGTH Length of the WITH OID clause of the typed view
OID_TEXT WITH OID clause of the typed view
VIEW_TYPE_OWNER Owner of the type of the view if the view is a typed view
VIEW_TYPE Type of the view if the view is a typed view
SUPERVIEW_NAME N/A

COLUMN_PRIVILEGES

Grants on columns for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:

Column Name Description
GRANTEE Name of the user to whom access was granted
OWNER Username of the object's owner
TABLE_NAME Name of the object
COLUMN_NAME Name of the column
GRANTOR Name of the user who performed the grant
INSERT_PRIV Permission to insert into the column
UPDATE_PRIV Permission to update the column
REFERENCES_PRIV Permission to reference the column
CREATED Timestamp for the grant

DICTIONARY

List or data dictionary tables:

Column Name Description
TABLE_NAME Table name
COMMENTS Description of table

DUAL

Column Name Description
DUMMY A dummy column

TABLE_PRIVILEGES

Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:

Column Name Description
GRANTEE Name of the user to whom access is granted
OWNER Owner of the object
TABLE_NAME Name of the object
GRANTOR Name of the user who performed the grant
SELECT_PRIV Permission to select from an object
INSERT_PRIV Permission to insert into an object
DELETE_PRIV Permission to delete from an object
UPDATE_PRIV Permission to update an object
REFERENCES_PRIV N/A
ALTER_PRIV Permission to alter an object
INDEX_PRIV Permission to create or drop an index on an object
CREATED Timestamp for the grant

USER_CATALOG

Tables, views, synonyms, and sequences owned by the use:

Column Name Description
TABLE_NAME Name of the object
TABLE_TYPE Type of object

USER_COL_COMMENTS

Comments on columns of user's tables and views:

Column Name Description
TABLE_NAME Object name
COLUMN_NAME Column name
COMMENTS Comments on column

USER_CONSTRAINTS

Constraint definitions on user's tables:

Column Name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
CONSTRAINT_TYPE Type of constraint definition
TABLE_NAME Name associated with table with constraint definition
SEARCH_CONDITION Text of search condition for table check
R_OWNER Owner of table used in referential constraint
R_CONSTRAINT_NAME Name of unique constraint definition for referenced table
DELETE_RULE Delete rule for referential constraint
STATUS Status of constraint
DEFERRABLE Whether the constraint is deferrable
DEFERRED Whether the constraint was initially deferred
VALIDATED Whether all data obeys the constraint
GENERATED Whether the name of the constraint is user or system generated
BAD Constraint specifies a century in an ambiguous manner
LAST_CHANGE When the constraint was las enabled or disabled
INDEX_OWNER N/A
INDEX_NAME N/A

USER_CONS_COLUMNS

Information about columns in constraint definitions owned by the user:

Column Name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
TABLE_NAME Name associated with table with constraint definition
COLUMN_NAME Name associated with column specified in the constraint definition
POSITION Original position of column in definition

USER_INDEXES

Description of the user's own indexes:

Column Name Description
INDEX_NAME Name of the index
INDEX_TYPE Type of index
TABLE_OWNER Owner of the indexed object
TABLE_NAME Name of the indexed object
TABLE_TYPE Type of the indexed object
UNIQUENESS Uniqueness status of the index
COMPRESSION N/A
PREFIX_LENGTH 0
TABLESPACE_NAME Name of the tablespace containing the index
INI_TRANS N/A
MAX_TRANS N/A
INITIAL_EXTENT N/A
NEXT_EXTENT N/A
MIN_EXTENTS N/A
MAX_EXTENTS N/A
PCT_INCREASE N/A
PCT_THRESHOLD Threshold percentage of block space permitted per index entry
INCLUDE_COLUMN Column ID of the last column to be included in index-organized table
FREELISTS Number of process freelists allocated to this segment
FREELIST_GROUPS Number of freelist groups allocated to this segment
PCT_FREE N/A
LOGGING Logging information
BLEVEL Depth of the index from its root block to its leaf blocks. A depth of 1 indicates that the root and leaf block are the same.
LEAF_BLOCKS Number of leaf blocks in the index
DISTINCT_KEYS Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table.
AVG_LEAF_BLOCKS_PER_KEY N/A
AVG_DATA_BLOCKS_PER_KEY N/A
CLUSTERING_FACTOR N/A
STATUS State of the indexes: VALID
NUM_ROWS Number of rows in the index
SAMPLE_SIZE Size of the sample used to analyze the index
LAST_ANALYZED Date on which this index was most recently analyzed
DEGREE Number of threads per instance for scanning the index
INSTANCES Number of instances across which the index is to be scanned
PARTITIONED Whether this index is partitioned
TEMPORARY Whether the index is on a temporary table
GENERATED Whether the name of the index is system generated
SECONDARY N/A
BUFFER_POOL Whether the index is a secondary object
USER_STATS N/A
DURATION N/A
PCT_DIRECT_ACCESS N/A
ITYP_OWNER N/A
ITYP_NAME N/A
PARAMETERS N/A
GLOBAL_STATS N/A
DOMIDX_STATUS N/A
DOMIDX_OPSTATUS N/A
FUNCIDX_STATUS N/A
JOIN_INDEX N/A
IOT_REDUNDANT_PKEY_ELIM N/A

USER_OBJECTS

Objects owned by the user:

Column Name Description
OBJECT_NAME Name of object
SUBOBJECT_NAME Name of the subobject
OBJECT_ID Object number of the object
DATA_OBJECT_ID Dictionary object number of the segment that contains the object
OBJECT_TYPE Type of object
CREATED N/A
LAST_DDL_TIME N/A
TIMESTAMP N/A
STATUS State of the object: VALID
TEMPORARY Whether the object is temporary
GENERATED Was the name of this object system generated?
SECONDARY N/A

USER_SYNONYMS

The user's private synonyms:

Column Name Description
SYNONYM_NAME Name of the synonym
TABLE_OWNER Owner of the object referenced by the synonym
TABLE_NAME Name of the object referenced by the synonym
DB_LINK N/A

USER_TABLES

Description of the user's own tables:

Column Name Description
TABLE_NAME Name of the table
TABLESPACE_NAME Name of the tablespace containing the table
CLUSTER_NAME N/A
IOT_NAME Name of the index organized table
PCT_FREE N/A
PCT_USED N/A
INI_TRANS N/A
MAX_TRANS N/A
INITIAL_EXTENT N/A
NEXT_EXTENT N/A
MIN_EXTENTS N/A
MAX_EXTENTS N/A
PCT_INCREASE N/A
FREELISTS Number of process freelists allocated to this segment
FREELIST_GROUPS Number of freelist groups allocated to this segment
LOGGING Logging information
BACKED_UP N/A
NUM_ROWS Number of rows in the table
BLOCKS N/A
EMPTY_BLOCKS N/A
AVG_SPACE N/A
CHAIN_CNT N/A
AVG_ROW_LEN Average length of a row in the table in bytes
AVG_SPACE_FREELIST_BLOCKS The average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS The number of blocks on the freelist
DEGREE The number of threads per instance for scanning the table
INSTANCES The number of instances across which the table is to be scanned
CACHE Whether the cluster is to be cached in the buffer cache
TABLE_LOCK Whether table locking is enabled or disabled
SAMPLE_SIZE Sample size used in analyzing this table
LAST_ANALYZED Date on which this table was most recently analyzed
PARTITIONED Indicates whether this table is partitioned
IOT_TYPE If this is an index organized table
TEMPORARY Can the current session only see data that it placed in this object itself?
SECONDARY N/A
NESTED If the table is a nested table
BUFFER_POOL The default buffer pool for the object
ROW_MOVEMENT N/A
GLOBAL_STATS N/A
USER_STATS N/A
DURATION N/A
SKIP_CORRUPT N/A
MONITORING N/A
CLUSTER_OWNER N/A
DEPENDENCIES N/A
COMPRESSION N/A

USER_TAB_COLUMNS

Columns of user's tables, views, and clusters:

Column Name Description
TABLE_NAME Table, view, or cluster name
COLUMN_NAME Column name
DATA_TYPE data type of column
DATA_TYPE_MOD data type modifier of the column
DATA_TYPE_OWNER Owner of the data type of the column
DATA_LENGTH Maximum length of the column in bytes
DATA_PRECISION N/A
DATA_SCALE Digits to the right of decimal point in a number
NULLABLE Does the column permit nulls? Value is n if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key.
COLUMN_ID Sequence number of the column as created
DEFAULT_LENGTH N/A
DATA_DEFAULT N/A
NUM_DISTINCT Number of distinct values in each column of the table
LOW_VALUE For tables with more than three rows, the second lowest and second highest values. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE N/A
DENSITY N/A
NUM_NULLS The number of nulls in the column
NUM_BUCKETS The number of buckets in histogram for the column
LAST_ANALYZED The date on which this column was most recently analyzed
SAMPLE_SIZE The sample size used in analyzing this column
CHARACTER_SET_NAME The name of the character set
CHAR_COL_DECL_LENGTH The length of the character set
GLOBAL_STATS N/A
USER_STATS N/A
AVG_COL_LEN Average length of the column (in bytes)
CHAR_LENGTH Displays the length of the column in characters
CHAR_USED N/A

USER_TAB_COMMENTS

Comments on the tables and views owned by the user:

Column Name Description
TABLE_NAME Name of the object
TABLE_TYPE Type of object
COMMENTS Comments on the object

USER_USERS

Information about the current user:

Column Name Description
USERNAME Name of the user
USER_ID N/A
ACCOUNT_STATUS Indicates if the account is locked, expired or unlocked
LOCK_DATE Date the account was locked
EXPIRE_DATE Date of expiration of the account
DEFAULT_TABLESPACE N/A
TEMPORARY_TABLESPACE N/A
CREATED N/A
EXTERNAL_NAME User external name

USER_VIEWS

Text of views owned by the user:

Column Name Description
VIEW_NAME Name of the view
TEXT_LENGTH Length of the view text
TEXT First line of the view text
TYPE_TEXT_LENGTH Length of the type clause of the typed view
TYPE_TEXT Type clause of the typed view
OID_TEXT_LENGTH Length of the WITH OID clause of the typed view
OID_TEXT WITH OID clause of the typed view
VIEW_TYPE_OWNER Owner of the type of the view if the view is a typed view
VIEW_TYPE Type of the view if the view is a typed view
SUPERVIEW_NAME N/A