A Oracle DB2 Data Dictionary Views
The following section 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.
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
ALL_CATALOG
The ALL_CATALOG
view contains all tables, views, synonyms, and sequence accessible to the user.
Column name | Description |
---|---|
|
Owner of the object |
|
Name of the object |
|
Type of object |
ALL_COL_COMMENTS
The ALL_COL_COMMENTS
view contains comments on columns of accessible tables and views.
Column name | Description |
---|---|
|
Owner of the object |
|
Object name |
|
Column name |
|
Comments on column |
ALL_CONS_COLUMNS
The ALL_CONS_COLUMNS
view contains information about accessible columns in constraint definitions.
Column name | Description |
---|---|
|
Owner of the constraint definition |
|
Name of the constraint definition |
|
Name of the table with a constraint definition |
|
Name of the column specified in the constraint definition |
|
Original position of column in definition |
ALL_CONSTRAINTS
The ALL_CONSTRAINTS
view contains constraint definitions on accessible tables.
Column name | Description |
---|---|
|
Owner of the constraint definition |
|
Name of the constraint definition |
|
Type of the constraint definition |
|
Name of the table with constraint definition |
|
Text of the search condition for table check |
|
Owner of the table used in referential constraint |
|
Name of the unique constraint definition for referenced table |
|
Delete rule for a referential constraint |
|
Status of a constraint |
|
Whether the constraint is deferrable |
|
Whether the constraint was initially deferred |
|
Whether all data obeys the constraint |
|
Whether the name of the constraint is user or system generated |
|
Constraint specifies a century in an ambiguous manner |
|
Whether an enabled constraint is enforced or unenforced |
|
When the constraint was last enabled |
|
N/A |
|
N/A |
ALL_INDEXES
The ALL_INDEXES
view contains description of indexes on tables accessible to the user.
Column name | Description |
---|---|
|
Owner of the index |
|
Name of the index |
|
Type of the index |
|
Owner of the indexed object |
|
Name of the indexed object |
|
Type of the indexed object |
|
Uniqueness status of the index |
|
N/A |
|
0 |
|
Name of the tablespace containing the index |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Threshold percentage of block space allowed per index entry |
|
Column ID of the last column to be included in an index-organized table |
|
Number of process freelists allocated to this segment |
|
Number of freelist groups allocated to this segment |
|
N/A |
|
Logging information |
|
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. |
|
Number of leaf blocks in the index |
|
Number of distinct indexed values. For indexes that enforce |
|
N/A |
|
N/A |
|
N/A |
|
State of the index: |
|
Number of rows in the index |
|
Size of the sample used to analyze the index |
|
Date on which an index was most recently analyzed |
|
Number of threads per instance for scanning the index |
|
Number of instances across which the index is to be scanned |
|
Whether the index is partitioned |
|
Whether the index is on a temporary table |
|
Whether the name of the index is system generated |
|
N/A |
|
Whether the index is a secondary object |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
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 |
---|---|
|
Owner of the index |
|
Name of the index |
|
Owner of the table or cluster |
|
Name of the table or cluster |
|
Column name or attribute of object type column |
|
Position of a column or attribute within the index |
|
Indexed length of the column |
|
Maximum codepoint length of the column |
|
Whether the column is sorted in descending order (Y/N) |
ALL_OBJECTS
The ALL_OBJECTS
view contains objects accessible to the user.
Column name | Description |
---|---|
|
Owner of the object |
|
Name of object |
|
Name of the subobject |
|
Object number of the object |
|
Dictionary object number of the segment that contains the object |
|
Type of object |
|
N/A |
|
N/A |
|
N/A |
|
State of the object |
|
Whether the object is temporary |
|
Whether the name of this object system is generated |
|
N/A |
ALL_SYNONYMS
The ALL_SYNONYMS
view contains all synonyms accessible to the user.
Column name | Description |
---|---|
|
Owner of the synonym |
|
Name of the synonym |
|
Owner of the object referenced by the synonym |
|
Name of the object referenced by the synonym |
|
N/A |
ALL_TABLES
The ALL_TABLES
view contains description of tables accessible to the user.
Column name | Description |
---|---|
|
Owner of the table |
|
Name of the table |
|
Name of the tablespace containing the table |
|
N/A |
|
Name of the index organized table |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Number of process freelists allocated to this segment |
|
Number of freelist groups allocated to this segment |
|
Logging attribute |
|
N/A |
|
Number of rows in the table |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Average length of a row in the table in bytes |
|
Average freespace of all blocks on a freelist |
|
Number of blocks on the freelist |
|
Number of threads per instance for scanning the table |
|
Number of instances across which the table is to be scanned |
|
Whether the cluster is to be cached in the buffer cache |
|
Whether the table locking is enabled or disabled |
|
Sample size used in analyzing this table |
|
Date on which this table was most recently analyzed |
|
Whether this table is partitioned |
|
Whether the table is an index-organized table |
|
Can the current session only see data that it placed in this object itself? |
|
N/A |
|
Whether the table is a nested table |
|
Default buffer pool for the object |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
ALL_TAB_COLUMNS
The ALL_TAB_COLUMNS
view contains columns of all tables, views, and clusters accessible to the user.
Column name | Description |
---|---|
|
Owner of the table or view |
|
Table or view name |
|
Column name |
|
|
|
Data type modifier of the column |
|
Owner of the data type of the column |
|
Maximum length of the column in bytes |
|
N/A |
|
Digits to the right of decimal point in a number |
|
Whether the column permits nulls? Value is n if there is a |
|
Sequence number of the column as created |
|
N/A |
|
N/A |
|
Number of distinct values in each column of the table |
|
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. |
|
N/A |
|
N/A |
|
Number of nulls in the column |
|
Number of buckets in histogram for the column |
|
Date on which this column was most recently analyzed |
|
Sample size used in analyzing this column |
|
Name of the character set |
|
Length of the character set |
|
N/A |
|
N/A |
|
Average length of the column (in bytes) |
|
Displays the length of the column in characters |
|
N/A |
ALL_TAB_COMMENTS
The ALL_TAB_COMMENTS
view contains comments on tables and views accessible to the user.
Column name | Description |
---|---|
|
Owner of the object |
|
Name of the object |
|
Type of the object |
|
Comments on the object |
ALL_USERS
The ALL_USERS
contains information about all users of the database.
Column name | Description |
---|---|
|
Name of the user |
|
N/A |
|
N/A |
ALL_VIEWS
The ALL_VIEWS
view contains text of views accessible to the user.
Column name | Description |
---|---|
|
Owner of the view |
|
Name of the view |
|
|
|
|
|
Length of the type clause of the typed view |
|
Type clause of the typed view |
|
Length of the |
|
|
|
Owner of the type of the view, if the view is a typed view |
|
Type of the view, if the view is a typed view |
|
N/A |
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 |
---|---|
|
Name of the user to whom access was granted |
|
Username of the owner of the object |
|
Name of the object |
|
Name of the column |
|
Name of the user who performed the grant |
|
Permission to insert into the column |
|
Permission to update the column |
|
Permission to reference the column |
|
Timestamp for the grant |
DICTIONARY
The DICTIONARY
view contains list or data dictionary tables.
Column name | Description |
---|---|
|
Table name |
|
Description of the table |
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 |
---|---|
|
Name of the user to whom access is granted |
|
Owner of the object |
|
Name of the object |
|
Name of the user who performed the grant |
|
Permission to select data from an object |
|
Permission to insert data into an object |
|
Permission to delete data from an object |
|
Permission to update an object |
|
N/A |
|
Permission to alter an object |
|
Permission to create or drop an index on an object |
|
Timestamp for the grant |
USER_CATALOG
The USER_CATALOG
view contains tables, views, synonyms, and sequences owned by the user.
Column name | Description |
---|---|
|
Name of the object |
|
Type of the object |
USER_COL_COMMENTS
The USER_COL_COMMENTS
view contains comments on columns of tables and views owned by the user.
Column name | Description |
---|---|
|
Name of the object |
|
Name of the column |
|
Comments on the column |
USER_CONSTRAINTS
The USER_CONSTRAINTS
view contains constraint definitions on tables owned by the user.
Column name | Description |
---|---|
|
Owner of the constraint definition |
|
Name associated with the constraint definition |
|
Type of the constraint definition |
|
Name associated with the table with constraint definition |
|
Text of the search condition for table check |
|
Owner of table used in referential constraint |
|
Name of the unique constraint definition for referenced table |
|
Delete rule for referential constraint |
|
Status of a constraint |
|
Whether the constraint is deferrable |
|
Whether the constraint was initially deferred |
|
Whether all data obeys the constraint |
|
Whether the name of the constraint is user or system generated |
|
Constraint specifies a century in an ambiguous manner |
|
When the constraint was last enabled |
|
N/A |
|
N/A |
USER_CONS_COLUMNS
The USER_CONS_COLUMNS
contains information about columns in constraint definitions owned by the user.
Column name | Description |
---|---|
|
Owner of the constraint definition |
|
Name associated with the constraint definition |
|
Name associated with table with constraint definition |
|
Name associated with column specified in the constraint definition |
|
Original position of column in definition |
USER_INDEXES
The USER_INDEXES
view contains description of the user's indexes:
Column name | Description |
---|---|
|
Name of the index |
|
Type of index |
|
Owner of the indexed object |
|
Name of the indexed object |
|
Type of the indexed object |
|
Uniqueness status of the index |
|
N/A |
|
0 |
|
Name of the tablespace containing the index |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Threshold percentage of block space allowed per index entry |
|
Column ID of the last column to be included in index-organized table |
|
Number of process freelists allocated to a segment |
|
Number of freelist groups allocated to a segment |
|
N/A |
|
Logging information |
|
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. |
|
Number of leaf blocks in the index |
|
Number of distinct indexed values. For indexes that enforce |
|
N/A |
|
N/A |
|
N/A |
|
State of the indexes: |
|
Number of rows in the index |
|
Size of the sample used to analyze the index |
|
Date on which the index was most recently analyzed |
|
Number of threads per instance for scanning the index |
|
Number of instances across which the index is to be scanned |
|
Whether the index is partitioned |
|
Whether the index is on a temporary table |
|
Whether the name of the index is system generated |
|
N/A |
|
Whether the index is a secondary object |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
USER_OBJECTS
The USER_OBJECTS
view contains objects owned by the user.
Column name | Description |
---|---|
|
Name of the object |
|
Name of the subobject |
|
Object number of the object |
|
Dictionary object number of the segment that contains the object |
|
Type of object |
|
N/A |
|
N/A |
|
N/A |
|
State of the object: |
|
Whether the object is temporary |
|
Was the name of this object system generated? |
|
N/A |
USER_SYNONYMS
The USER_SYNONYMS
view contains the private synonyms of the user.
Column name | Description |
---|---|
|
Name of the synonym |
|
Owner of the object referenced by the synonym |
|
Name of the object referenced by the synonym |
|
N/A |
USER_TABLES
The USER_TABLES
view contains description of the tables owned by the user.
Column name | Description |
---|---|
|
Name of the table |
|
Name of the tablespace containing the table |
|
N/A |
|
Name of the index organized table |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Number of process freelists allocated to a segment |
|
Number of freelist groups allocated to a segment |
|
Logging information |
|
N/A |
|
Number of rows in the table |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
Average length of a row in the table in bytes |
|
Average freespace of all blocks on a freelist |
|
Number of blocks on the freelist |
|
Number of threads per instance for scanning the table |
|
Number of instances across which the table is to be scanned |
|
Whether the cluster is to be cached in the buffer cache |
|
Whether table locking is enabled or disabled |
|
Sample size used in analyzing this table |
|
Date on which this table was most recently analyzed |
|
Indicates whether this table is partitioned |
|
If this is an index organized table |
|
Can the current session only see data that it placed in this object itself? |
|
N/A |
|
If the table is a nested table |
|
The default buffer pool for the object |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
|
N/A |
USER_TAB_COLUMNS
The USER_TAB_COLUMNS
view contains columns of the tables, views, and clusters owned by the user.
Column name | Description |
---|---|
|
Name of the table, view, or cluster |
|
Name of the column |
|
|
|
Data type modifier of the column |
|
Owner of the data type of the column |
|
Maximum length of the column in bytes |
|
N/A |
|
Digits to the right of a decimal point in a number |
|
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. |
|
Sequence number of the column as created |
|
N/A |
|
N/A |
|
Number of distinct values in each column of the table |
|
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. |
|
N/A |
|
N/A |
|
Number of nulls in the column |
|
Number of buckets in histogram for the column |
|
Date on which this column was most recently analyzed |
|
Sample size used in analyzing this column |
|
Name of the character set |
|
Length of the character set |
|
N/A |
|
N/A |
|
Average length of the column (in bytes) |
|
Length of the column in characters |
|
N/A |
USER_TAB_COMMENTS
The USER_TAB_COMMENTS
view contains comments on the tables and views owned by the user.
Column name | Description |
---|---|
|
Name of the object |
|
Type of the object |
|
Comments on the object |
USER_VIEWS
The USER_VIEWS
view contains text of views owned by the user.
Column name | Description |
---|---|
|
Name of the view |
|
Length of the view text |
|
First line of the view text |
|
Length of the type clause of the typed view |
|
Type clause of the typed view |
|
Length of the |
|
|
|
Owner of the type of the view, if the view is a typed view |
|
Type of the view, if the view is a typed view |
|
N/A |
USER_USERS
The USER_USERS
view contains information about the current user.
Column name | Description |
---|---|
|
Name of the user |
|
N/A |
|
Indicates if the account is locked, expired or unlocked |
|
Date on which the account was locked |
|
Date of expiration of the account |
|
N/A |
|
N/A |
|
N/A |
|
Name of the external user |