This document is a reference to the system catalog views for the Oracle Lite database, which specifically covers the Oracle Lite database catalog views. The following views are available in the Oracle Lite database system catalog:
Note:
In the following tables, columns marked with an asterisk are not used by Oracle Lite, but are compatible with an Oracle database and generally returnNULL
or a default value.This view lists user comments for table columns. The parameters for this view are listed in Table 5-1:
This view provides the following information about constraint definitions on accessible tables. The parameters for this view are listed in Table 5-2:
Table 5-2 ALL_CONSTRAINTS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
Owner of the table. |
CONSTRAINT_NAME |
VARCHAR(128) |
No |
Name associated with the constraint definition. |
CONSTRAINT_TYPE |
VARCHAR(128) |
No |
Type of constraint definition, as follows:
|
TABLE_NAME |
VARCHAR(128) |
No |
Name of the table with the constraint definition. |
SEARCH_CONDITION |
VARCHAR(1000) |
Yes |
Text of search condition for table check. |
R_OWNER |
VARCHAR(128) |
Yes |
Owner of table used in referential constraint. |
R_CONSTRAINT_NAME |
VARCHAR(128) |
Yes |
Name of unique constraint definition for referenced table. |
DELETE_RULE |
VARCHAR(128) |
Yes |
Delete rule for a referential constraint: |
STATUS |
VARCHAR(20) |
No |
Status of constraint: |
This view provides the following information about accessible columns in constraint definitions. The parameters for this view are listed in Table 5-3:
Table 5-3 ALL_CONS_COLUMNS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
Yes |
User name of the owner of the constraint definition. |
CONSTRAINT_NAME |
VARCHAR(128) |
Yes |
Name associated with the constraint definition. |
TABLE_NAME |
VARCHAR(128) |
Yes |
Name of table with constraint definition. |
COLUMN_NAME |
VARCHAR(128) |
Yes |
Name of the column specified in constraint definition. |
POSITION |
VARCHAR(4096) |
Yes |
Original position of column in definition. |
This view contains descriptions of all indexes defined on tables. The parameters for this view are listed in Table 5-4:
Table 5-4 ALL_INDEXES Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
Owner of the INDEX definition. |
INDEX_NAME |
VARCHAR(128) |
No |
Name associated with the INDEX definition. |
TABLE_OWNER |
VARCHAR(128) |
No |
Owner of the table on which the INDEX is defined. |
TABLE_NAME |
VARCHAR(128) |
No |
Name of table with INDEX definition. |
TABLE_TYPE |
VARCHAR(10) |
Yes |
Type of the object. |
UNIQUENESS |
VARCHAR(128) |
No |
String containing |
This view lists index key columns for all indexes in the database. The parameters for this view are listed in Table 5-5:
Table 5-5 ALL_IND_COLUMNS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
INDEX_OWNER |
VARCHAR(128) |
No |
Owner of the INDEX definition. |
INDEX_NAME |
VARCHAR(128) |
No |
Name associated with the INDEX definition. |
TABLE_OWNER |
VARCHAR(128) |
No |
Owner of the table on which the INDEX is defined. |
TABLE_NAME |
VARCHAR(128) |
No |
Name of table with the INDEX definition. |
COLUMN_NAME |
VARCHAR(128) |
No |
Name of the column specified in the INDEX definition. |
COLUMN_POSITION |
NUMBER(10) |
No |
Position of the column in the index definition. |
This view contains descriptions of the objects (tables, views, synonyms, indexes, and sequences). The parameters for this view are listed in Table 5-6:
Table 5-6 ALL_OBJECTS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
Owner of the OBJECTS definition. |
OBJECT_NAME |
VARCHAR(128) |
No |
Name associated with the OBJECTS definition. |
OBJECT_TYPE |
VARCHAR(128) |
Yes |
Type of the object: TABLE, VIEW, INDEX, SEQUENCE, or SYNONYM. |
CREATED |
DATE |
Yes |
Timestamp for the creation of the OBJECTS. |
STATUS |
VARCHAR(128) |
Yes |
Status of the OBJECTS: VALID, INVALID, or N/A (always valid). |
This view lists descriptions of all sequences in the database. The parameters for this view are listed in Table 5-7:
Table 5-7 ALL_SEQUENCES Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
Owner of the OBJECTS definition. |
OBJECT_NAME |
VARCHAR(128) |
No |
Name associated with the OBJECTS definition. |
OBJECT_TYPE |
VARCHAR(128) |
Yes |
Type of the object: TABLE, VIEW, INDEX, SEQUENCE, or SYNONYM. |
CREATED |
DATE |
Yes |
Timestamp for the creation of the OBJECTS. |
STATUS |
VARCHAR(128) |
Yes |
Status of the OBJECTS: VALID, INVALID, or N/A (always valid). |
This view lists all synonyms in the database. The parameters for this view are listed in Table 5-8:
Table 5-8 ALL_SYNONYMS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
Yes |
Owner of the SYNONYMS definition. |
SYNONYM_NAME |
VARCHAR(128) |
Yes |
Name associated with the SYNONYMS definition. |
TABLE_OWNER |
VARCHAR(128) |
Yes |
Owner of the table on which the SYNONYMS is defined. |
TABLE_NAME |
VARCHAR(128) |
Yes |
Name of table with the SYNONYMS definition. |
DB_LINK |
VARCHAR(128) |
Yes |
Reserved. |
This view provides the following information on all tables accessible to the user. The parameters for this view are listed in Table 5-9:
Table 5-9 ALL_TABLES Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the table. |
TABLE_NAME |
VARCHAR(128) |
No |
Name of table. |
TABLESPACE_NAME |
VARCHAR(128) |
Yes |
Name of the catalog of database file containing the table. |
CLUSTER_NAME |
VARCHAR(128) |
Yes |
Name of the cluster, if any, to which the table belongs. |
PCT_FREE* |
NUMBER(10) |
Yes |
Minimum percentage of free space in a block. |
PCT_USED* |
NUMBER(10) |
Yes |
Minimum percentage of used space in a block. |
INI_TRANS* |
NUMBER(10) |
Yes |
Initial number of transactions. |
MAX_TRANS* |
NUMBER(10) |
Yes |
Maximum number of transactions. |
INITIAL_EXTENT* |
NUMBER(10) |
Yes |
Size of the initial extent in bytes. |
NEXT_EXTENT* |
NUMBER(10) |
Yes |
Size of secondary extents in bytes. |
MIN_EXTENTS* |
NUMBER(10) |
Yes |
Minimum number of extents allowed in the segment. |
MAX_EXTENTS* |
NUMBER(10) |
Yes |
Maximum number of extents allowed in the segment. |
PCT_INCREASE* |
NUMBER(10) |
Yes |
Percentage increase in extent size. |
BACKED_UP* |
VARCHAR(1) |
Yes |
If the table was backed up since last change. |
NUM_ROWS* |
NUMBER(10) |
Yes |
Number of rows in the table. |
BLOCKS* |
NUMBER(10) |
Yes |
Number of data blocks allocated to the table. |
EMPTY_BLOCKS* |
NUMBER(10) |
Yes |
Number of data blocks allocated to the table that contains no data. |
AVG_SPACE* |
NUMBER(10) |
Yes |
Average amount of free space (in bytes) in a data block allocated to the table. |
CHAIN_CNT* |
NUMBER(10) |
Yes |
Number of rows in the table that are chained from one data block to another, or that have migrated to a new block, requiring a link to preserve the old ROWID. |
AVG_ROW_LEN* |
NUMBER(10) |
Yes |
Average length of a row in the table in bytes. |
This view provides the following information about the columns of tables, views, and clusters accessible to the user. The parameters for this view are listed in Table 5-10:
Table 5-10 ALL_TAB_COLUMNS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the table, view or cluster. |
TABLE_NAME |
VARCHAR(128) |
No |
Table, view or cluster name. |
COLUMN_NAME |
VARCHAR(128) |
No |
Name of the column. |
DATA_TYPE |
VARCHAR(30) |
Yes |
Datatype of the column. |
DATA_LENGTH |
NUMBER(10) |
Yes |
Length of the column in bytes. |
DATA_PRECISION |
NUMBER(10) |
Yes |
Decimal precision for NUMERIC and DECIMAL datatype; binary precision for FLOAT, REAL, and DOUBLE datatype; NULL for all other datatypes. |
DATA_SCALE |
NUMBER(10) |
Yes |
Digits to the right of decimal point in a NUMERIC or DECIMAL. |
NULLABLE |
VARCHAR(1) |
Yes |
Indicates if the column allows 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 |
NUMBER(10) |
No |
Sequence number of the column as created. |
DEFAULT_LENGTH |
NUMBER(10) |
Yes |
Length of default value for the column. |
DATA_DEFAULT |
VARCHAR(4096) |
Yes |
Default value for the column. |
NUM_DISTINCT* |
NUMBER(10) |
Yes |
Number of distinct values in each column of the table. |
LOW_VALUE* |
NUMBER(10) |
Yes |
For tables with more than three rows, the second lowest and second highest values in the column. For tables with three rows or fewer, the lowest and highest values. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values. |
HIGH_VALUE* |
NUMBER(10) |
Yes |
See description in LOW_VALUE. |
This view lists comments on tables and views entered by users. The parameters for this view are listed in Table 5-11:
Table 5-11 ALL_TAB_COMMENTS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the TAB_COMMENTS definition. |
TABLE_NAME |
VARCHAR(128) |
No |
Table name for the TAB_COMMENTS definition. |
TABLE_TYPE |
VARCHAR(128) |
No |
Type of the object. |
COMMENTS |
VARCHAR(4096) |
No |
Comment text. |
This view provides the following information about all schemas created in the connected database. The parameters for this view are listed in Table 5-12:
This view provides the following information about views accessible to the user. The parameters for this view are listed in Table 5-13:
This view provides the following information about tables and views accessible to the user. The parameters for this view are listed in Table 5-14:
This view provides the following information about grants on columns for which the user is the grantor, grantee, or owner, or for which PUBLIC is the grantee. The parameters for this view are listed in Table 5-15:
Table 5-15 COLUMN_PRIVILEGES Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the object. |
TABLE_NAME |
VARCHAR(128) |
No |
Name of the object. |
COLUMN_NAME |
VARCHAR(128) |
Yes |
Name of the column. |
GRANTOR |
VARCHAR(128) |
Yes |
Name of the user who performed the grant. |
GRANTEE |
VARCHAR(128) |
Yes |
Name of the user to whom access was granted. |
GRANT_TYPE |
VARCHAR(128) |
No |
Privilege on the object. The value can be SELECT, INSERT, or DELETE. |
GRANTABLE |
VARCHAR(128) |
Yes |
YES, if the privilege was granted with GRANT OPTION; otherwise, NO. |
This view lists the value for the NLS_SORT parameter, which controls collation sequence. The parameters for this view are listed in Table 5-16:
This view is a dummy table which can be used in a query when you want to return a single row. For example, you could use DUAL to select CURRENT_TIMESTAMP. The parameters for this view are listed in Table 5-17:
This view provides the following information about grants on objects for which the user or PUBLIC is the grantee. The parameters for this view are listed in Table 5-18:
Table 5-18 TABLE_PRIVILEGES Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the object. |
TABLE_NAME |
VARCHAR(128) |
No |
Name of the object. |
GRANTOR |
VARCHAR(128) |
Yes |
Name of the user who performed the grant. |
GRANTEE |
VARCHAR(128) |
Yes |
Name of the user to whom access is granted. |
GRANT_TYPE |
VARCHAR(128) |
No |
Privilege on the object. The value can be one of the following: SELECT, INSERT, or DELETE. |
GRANTABLE |
VARCHAR(128) |
Yes |
YES, if the privilege was granted with GRANT OPTION; NO, if it was not. |
This view provides the following information about objects accessible to the user. The parameters for this view are listed in Table 5-19:
Table 5-19 USER_OBJECTS Parameters
Column | Datatype | NULL ALLOWED | Description |
---|---|---|---|
OWNER |
VARCHAR(128) |
No |
User name of the owner of the object. |
OBJECT_NAME |
VARCHAR(128) |
No |
Name of the object. |
OBJECT_ID |
NUMBER(10) |
No |
Object identifier of the object. |
OBJECT_TYPE |
VARCHAR(128) |
Yes |
Type of the object: TABLE, VIEW, INDEX, SEQUENCE, SYNONYM. |
CREATED |
DATE |
Yes |
Timestamp for the creation of the object. |
LAST_DDL_TIME |
DATE |
Yes |
Timestamp for the last modification of the object resulting from a DDL command (including grant and revoke). |
CREATED_TIME |
VARCHAR(128) |
Yes |
Timestamp for the creation of the object (character data). |
STATUS* |
VARCHAR(128) |
Yes |
Status of the object: VALID, INVALID, or N/A (always valid). |