A Oracle DB2 Data Dictionary Views

This appendix covers the Oracle Database Gateway for DRDA data dictionary views accessible to all users of Oracle database. Any user with SELECT privileges for DB2 catalog tables can access most of the views.

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

This appendix contains the following sections:

A.1 Supported Views

The following is a list of Oracle data dictionary views that are supported by the gateway for DB2 UDB for z/OS, DB2 UDB for iSeries, and DB2/UDB DRDA servers.

  • ALL_CATALOG

  • ALL_COL_COMMENTS

  • ALL_CONS_COLUMNS

  • ALL_CONSTRAINTS

  • ALL_INDEXES

  • ALL_IND_COLUMNS

  • ALL_OBJECTS

  • ALL_SYNONYMS

  • ALL_TAB_COMMENTS

  • ALL_TABLES

  • ALL_TAB_COLUMNS

  • ALL_USERS

  • ALL_VIEWS

  • COL_PRIVILEGES

  • DICTIONARY

  • DUAL

  • TABLE_PRIVILEGES

  • USER_CATALOG

  • USER_COL_COMMENTS

  • USER_CONSTRAINTS

  • USER_CONS_COLUMNS

  • USER_INDEXES

  • USER_OBJECTS

  • USER_SYNONYMS

  • USER_TABLES

  • USER_TAB_COLUMNS

  • USER_TAB_COMMENTS

  • USER_USERS

  • USER_VIEWS

A.2 Data Dictionary View Tables

This section contains tables that describe data dictionary views. DB2 UDB for z/OS and DB2 UDB for iSeries supports all views in the following sections.

A.2.1 ALL_CATALOG

The ALL_CATALOG view contains all tables, views, synonyms, and sequence accessible to the user.

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

A.2.2 ALL_COL_COMMENTS

The ALL_COL_COMMENTS view contains 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

A.2.3 ALL_CONS_COLUMNS

The ALL_CONS_COLUMNS view contains information about accessible columns in constraint definitions.

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

A.2.4 ALL_CONSTRAINTS

The ALL_CONSTRAINTS view contains constraint definitions on accessible tables.

Column name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name of the constraint definition
CONSTRAINT_TYPE Type of the constraint definition
TABLE_NAME Name of the table with constraint definition
SEARCH_CONDITION Text of the search condition for table check
R_OWNER Owner of the table used in referential constraint
R_CONSTRAINT_NAME Name of the unique constraint definition for referenced table
DELETE_RULE Delete rule for a referential constraint
STATUS Status of a 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
INDEX_OWNER N/A
INDEX_NAME N/A

A.2.5 ALL_INDEXES

The ALL_INDEXES view contains 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 the 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 allowed per index entry
INCLUDE_COLUMN Column ID of the last column to be included in an 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 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 an 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 the 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

A.2.6 ALL_IND_COLUMNS

The ALL_IND_COLUMNS view contains 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 a column or attribute within the index
COLUMN_LENGTH Indexed length of the column
CHAR_LENGTH Maximum codepoint length of the column
DESCEND Whether the column is sorted in descending order (Y/N)

A.2.7 ALL_OBJECTS

The ALL_OBJECTS view contains 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 Whether the name of this object system is generated
SECONDARY N/A

A.2.8 ALL_SYNONYMS

The ALL_SYNONYMS view contains 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

A.2.9 ALL_TABLES

The ALL_TABLES view contains 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 freelists allocated to this segment
FREELIST_GROUPS Number of freelist 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 Average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS Number of blocks on the freelist
DEGREE Number of threads per instance for scanning the table
INSTANCES 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 the 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 Whether this table is partitioned
IOT_TYPE Whether the table 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 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

A.2.10 ALL_TAB_COLUMNS

The ALL_TAB_COLUMNS view contains 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 the 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 Whether the column permits 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 Number of nulls in the column
NUM_BUCKETS Number of buckets in histogram for the column
LAST_ANALYZED Date on which this column was most recently analyzed
SAMPLE_SIZE Sample size used in analyzing this column
CHARACTER_SET_NAME Name of the character set
CHAR_COL_DECL_LENGTH 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

A.2.11 ALL_TAB_COMMENTS

The ALL_TAB_COMMENTS view contains 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 the object
COMMENTS Comments on the object

A.2.12 ALL_USERS

The ALL_USERS contains information about all users of the database.

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

A.2.13 ALL_VIEWS

The ALL_VIEWS view contains 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

A.2.14 COLUMN_PRIVILEGES

The COLUMN_PRIVILEGES view contains grants on columns for which the user is the grantor, grantee, or owner, or, the grantee is PULBLIC.

Column name Description
GRANTEE Name of the user to whom access was granted
OWNER Username of the owner of the object
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

A.2.15 DICTIONARY

The DICTIONARY view contains list or data dictionary tables.

Column name Description
TABLE_NAME Table name
COMMENTS Description of the table

A.2.16 DUAL

The DUAL view contains list of dual tables.

Column name Description
DUMMY A dummy column

A.2.17 TABLE_PRIVILEGES

The TABLE_PRIVILEGES view contains grants on objects for which the user is the grantor, grantee, or owner, or, the grantee is PUBLIC.

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 data from an object
INSERT_PRIV Permission to insert data into an object
DELETE_PRIV Permission to delete data 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

A.2.18 USER_CATALOG

The USER_CATALOG view contains tables, views, synonyms, and sequences owned by the user.

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

A.2.19 USER_COL_COMMENTS

The USER_COL_COMMENTS view contains comments on columns of tables and views owned by the user.

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

A.2.20 USER_CONSTRAINTS

The USER_CONSTRAINTS view contains constraint definitions on tables owned by the user.

Column name Description
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
CONSTRAINT_TYPE Type of the constraint definition
TABLE_NAME Name associated with the table with constraint definition
SEARCH_CONDITION Text of the search condition for table check
R_OWNER Owner of table used in referential constraint
R_CONSTRAINT_NAME Name of the unique constraint definition for referenced table
DELETE_RULE Delete rule for referential constraint
STATUS Status of a 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 last enabled
INDEX_OWNER N/A
INDEX_NAME N/A

A.2.21 USER_CONS_COLUMNS

The USER_CONS_COLUMNS contains 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

A.2.22 USER_INDEXES

The USER_INDEXES view contains description of the user's 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 allowed 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 a segment
FREELIST_GROUPS Number of freelist groups allocated to a 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 the 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 the 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

A.2.23 USER_OBJECTS

The USER_OBJECTS view contains objects owned by the user.

Column name Description
OBJECT_NAME Name of the 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

A.2.24 USER_SYNONYMS

The USER_SYNONYMS view contains the private synonyms of the user.

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

A.2.25 USER_TABLES

The USER_TABLES view contains description of the tables owned by the user.

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 a segment
FREELIST_GROUPS Number of freelist groups allocated to a 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 Average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS Number of blocks on the freelist
DEGREE Number of threads per instance for scanning the table
INSTANCES 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

A.2.26 USER_TAB_COLUMNS

The USER_TAB_COLUMNS view contains columns of the tables, views, and clusters owned by the user.

Column name Description
TABLE_NAME Name of the table, view, or cluster
COLUMN_NAME Name of the column
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 a decimal point in a number
NULLABLE Whether the column permits 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 Number of nulls in the column
NUM_BUCKETS Number of buckets in histogram for the column
LAST_ANALYZED Date on which this column was most recently analyzed
SAMPLE_SIZE Sample size used in analyzing this column
CHARACTER_SET_NAME Name of the character set
CHAR_COL_DECL_LENGTH 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 Length of the column in characters
CHAR_USED N/A

A.2.27 USER_TAB_COMMENTS

The USER_TAB_COMMENTS view contains comments on the tables and views owned by the user.

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

A.2.28 USER_USERS

The USER_USERS view contains 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 on which 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 Name of the external user

A.2.29 USER_VIEWS

The USER_VIEWS view contains 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