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