Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 5 System Catalog

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:

- Section 5.1, "ALL_COL_COMMENTS"

- Section 5.2, "ALL_CONSTRAINTS"

- Section 5.3, "ALL_CONS_COLUMNS"

- Section 5.4, "ALL_INDEXES"

- Section 5.5, "ALL_IND_COLUMNS"

- Section 5.6, "ALL_OBJECTS"

- Section 5.7, "ALL_SEQUENCES"

- Section 5.8, "ALL_SYNONYMS"

- Section 5.9, "ALL_TABLES"

- Section 5.10, "ALL_TAB_COLUMNS"

- Section 5.11, "ALL_TAB_COMMENTS"

- Section 5.12, "ALL_USERS"

- Section 5.13, "ALL_VIEWS"

- Section 5.14, "CAT"

- Section 5.15, "COLUMN_PRIVILEGES"

- Section 5.16, "DATABASE_PARAMETERS"

- Section 5.17, "DUAL"

- Section 5.18, "TABLE_PRIVILEGES"

- Section 5.19, "USER_OBJECTS"

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 return `NULL` or a default value.

## 5.1 ALL_COL_COMMENTS

This view lists user comments for table columns. The parameters for this view are listed in Table 5-1:

Table 5-1 ALL_COL_COMMENTS Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
OWNER | VARCHAR(128) | No | Owner of the table.
TABLE_NAME | VARCHAR(128) | No | Name of the object.
COLUMN_NAME | VARCHAR(128) | No | Name of the column.
COMMENTS | VARCHAR(4096) | Yes | Text of the column comment.

## 5.2 ALL_CONSTRAINTS

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: C—check constraint on a table P—primary key U—unique key R—referential integrity V—with the check option, checks the constraint on a view
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: `NO ACTION`.
STATUS | VARCHAR(20) | No | Status of constraint: `ENABLED` or `DISABLED`.

## 5.3 ALL_CONS_COLUMNS

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.

## 5.4 ALL_INDEXES

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 `UNIQUE` or `NONUNIQUE`.

## 5.5 ALL_IND_COLUMNS

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.

## 5.6 ALL_OBJECTS

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).

## 5.7 ALL_SEQUENCES

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).

## 5.8 ALL_SYNONYMS

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.

## 5.9 ALL_TABLES

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.

## 5.10 ALL_TAB_COLUMNS

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.

## 5.11 ALL_TAB_COMMENTS

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.

## 5.12 ALL_USERS

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:

Table 5-12 ALL_USERS Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
USERNAME | VARCHAR(30) | No | Name of the user.
USER_ID* | NUMBER | No | ID number of the user.
CREATED | DATE | No | User creation date.

## 5.13 ALL_VIEWS

This view provides the following information about views accessible to the user. The parameters for this view are listed in Table 5-13:

Table 5-13 ALL_TAB_COMMENTS Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
OWNER | VARCHAR(128) | No | User name of the owner of the view.
VIEW_NAME | VARCHAR(128) | No | Name of the view.
TEXT_LENGTH | NUMBER(10) | No | Length of the view text.
TEXT | VARCHAR(1000) | No | View text.

## 5.14 CAT

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:

Table 5-14 CAT Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
TABLE_NAME | VARCHAR(128) | No | Name of the object.
TABLE_TYPE | VARCHAR(128) | No | Type of the object: TABLE or VIEW.

## 5.15 COLUMN_PRIVILEGES

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.

## 5.16 DATABASE_PARAMETERS

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:

Table 5-16 DATABASE_PARAMETERS Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
PARAMETER | VARCHAR(30) | No | NLS_SORT
VALUE | VARCHAR(128) | Yes | Collation sequence string constant. The value can be BINARY, FRENCH, GERMAN, CZECH, or XCZECH.

## 5.17 DUAL

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:

Table 5-17 DUAL Parameters

Column | Datatype | NULL ALLOWED | Description
--- | --- | --- | ---
DUMMY | VARCHAR(1) | No | Always "X".

## 5.18 TABLE_PRIVILEGES

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.

## 5.19 USER_OBJECTS

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).