Skip Headers

Oracle9i Lite Developer's Guide for Windows CE/Pocket PC
Release 5.0.2
Part No. B10100-01
Go To Table Of Contents
Contents
Go To Index
Index

Previous Next

A
System Catalog Views

This document is a reference to the system catalog views for the Oracle Lite database. This appendix covers the following sets of catalog views:

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

A.1.1 ALL_COL_COMMENTS

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

Table A-1 ALL_COL_COMMENTS Parameters

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

A.1.2 ALL_CONSTRAINTS

This view provides the following information about constraint definitions on accessible tables. The parameters for this view are listed in Table A-2:

Table A-2 ALL_CONSTRAINTS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No Owner of the constraint definition.
CONSTRAINT_NAME VARCHAR2(128) No Name associated with the constraint definition.
CONSTRAINT_TYPE VARCHAR2(128) No Type of constraint definition:

C (check constraint on a table)

P (primary key)

U (unique key)

R (referential integrity)

V (with check option, on a view)

TABLE_NAME VARCHAR2(128) No Name of table with constraint definition.
SEARCH_CONDITION VARCHAR2(1000) Yes Text of search condition for table check.
R_OWNER VARCHAR2(128) Yes Owner of table used in referential constraint.
R_CONSTRAINT_NAME VARCHAR2(128) Yes Name of unique constraint definition for referenced table.
DELETE_RULE VARCHAR2(128) Yes Delete rule for a referential constraint: "NO ACTION".
STATUS VARCHAR2(20) No Status of constraint: "ENABLED" or "DISABLED".

A.1.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 A-3:

Table A-3 ALL_CONS_COLUMNS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) Yes User name of owner of the constraint definition.
CONSTRAINT_NAME VARCHAR2(128) Yes Name associated with the constraint definition.
TABLE_NAME VARCHAR2(128) Yes Name of table with constraint definition.
COLUMN_NAME VARCHAR2(128) Yes Name associated with column specified in the constraint definition.
POSITION NUMBER(10) Yes Original position of column in definition.

A.1.4 ALL_INDEXES

This view contains descriptions of all indexes defined on tables. The parameters for this view are listed in Table A-4:

Table A-4 ALL_INDEXES Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No Owner of the INDEX definition.
INDEX_NAME VARCHAR2(128) No Name associated with the INDEX definition.
TABLE_OWNER VARCHAR2(128) No Owner of the table on which the INDEX is defined.
TABLE_NAME VARCHAR2(128) No Name of table with INDEX definition.
TABLE_TYPE VARCHAR2(10) Yes Type of the object.
UNIQUENESS VARCHAR2(128) No This is a string containing "UNIQUE" or "NONUNIQUE".

A.1.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 A-5:

Table A-5 ALL_IND_COLUMNS Parameters

Column Datatype NULL ALLOWED Description
INDEX_OWNER VARCHAR2(128) No Owner of the INDEX definition.
INDEX_NAME VARCHAR2(128) No Name associated with the INDEX definition.
TABLE_OWNER VARCHAR2(128) No Owner of the table on which the INDEX is defined.
TABLE_NAME VARCHAR2(128) No Name of table with INDEX definition.
COLUMN_NAME VARCHAR2(128) No Name associated with column specified in the INDEX definition.
COLUMN_POSITION NUMBER(10) No Position of the column in the index definition.

A.1.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 A-6:

Table A-6 ALL_OBJECTS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No Owner of the OBJECTS definition.
OBJECT_NAME VARCHAR2(128) No Name associated with the OBJECTS definition.
OBJECT_TYPE VARCHAR2(128) Yes Type of the object: TABLE, VIEW, INDEX, SEQUENCE, SYNONYM.
CREATED DATE Yes Timestamp for the creation of the OBJECTS.
STATUS VARCHAR2(128) Yes Status of the OBJECTS: VALID, INVALID, or N/A (always valid).

A.1.7 ALL_PARTITIONS

This view displays all map table partitions, partition IDs for use in dropping or merging partitions, and the distribution of clients across partitions. The parameters for this view are listed in Table A-7:

Table A-7 ALL_VIEWS Parameters

Column Datatype NULL ALLOWED Description
PUB_ITEM VARCHAR2(30) No Publication item name.
PARTITION_ID NUMBER No Partition ID.
CLID$$CS VARCHAR2(30) No Client ID.

Note:

This form of partitioning is not related to the partition functonality provided by Oracle Server, and is used exclusively by Oracle9i Lite.

A.1.8 ALL_SEQUENCES

This view lists descriptions of all sequences in the database. The parameters for this view are listed in Table A-8:

Table A-8 ALL_SEQUENCES Parameters

Column Datatype NULL ALLOWED Description
SEQUENCE_OWNER VARCHAR2(128) No Owner of the SEQUENCES definition.
SEQUENCE_NAME VARCHAR2(128) No Name associated with the SEQUENCES definition.
MIN_VALUE NUMBER(10) No Minimum value of the sequence.
MAX_VALUE NUMBER(10) No Maximum value of the sequence.
INCREMENT_BY NUMBER(10) No Increment amount for the sequence.

A.1.9 ALL_SYNONYMS

This view lists all synonyms in the database. The parameters for this view are listed in Table A-9:

Table A-9 ALL_SYNONYMS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) Yes Owner of the SYNONYMS definition.
SYNONYM_NAME VARCHAR2(128) Yes Name associated with the SYNONYMS definition.
TABLE_OWNER VARCHAR2(128) Yes Owner of the table on which the SYNONYMS is defined.
TABLE_NAME VARCHAR2(128) Yes Name of table with SYNONYMS definition.
DB_LINK VARCHAR2(128) Yes Reserved.

A.1.10 ALL_TABLES

This view provides the following information about tables accessible to the user. The parameters for this view are listed in Table A-10:

Table A-10 ALL_TABLES Parameter

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No User name of the owner of the table.
TABLE_NAME VARCHAR2(128) No Name of the table.
TABLESPACE_NAME VARCHAR2(128) Yes Name of the catalog or database file containing the table.
CLUSTER_NAME* VARCHAR2(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* VARCHAR2(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 contain 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.

A.1.11 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 A-11:

Table A-11 ALL_TAB_COLUMNS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No User name of owner of the table, view, or cluster.
TABLE_NAME VARCHAR2(128) No Table, view, or cluster name.
COLUMN_NAME VARCHAR2(128) No Column name.
DATA_TYPE VARCHAR2(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 VARCHAR2(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 VARCHAR2(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 See description in HIGH_VALUE.
HIGH_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.

A.1.12 ALL_TAB_COMMENTS

This view lists comments on tables and views entered by users. The parameters for this view are listed in Table A-12:

Table A-12 ALL_TAB_COMMENTS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No Owner of the TAB_COMMENTS definition.
TABLE_NAME VARCHAR2(128) No Name of table with TAB_COMMENTS definition.
TABLE_TYPE VARCHAR2(128) No Type of the object.
COMMENTS VARCHAR2(4096) No Comment text.

A.1.13 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 A-13:

Table A-13 ALL_USERS Parameters

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

A.1.14 ALL_VIEWS

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

Table A-14 ALL_VIEWS Parameters

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

A.1.15 CAT

This view provides the following information about tables and views accessible to the user. The parameters for this view are listed in Table A-15:

Table A-15 CAT Parameters

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

A.1.16 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 A-16:

Table A-16 COLUMN_PRIVILEGES Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No User name of the owner of the object.
TABLE_NAME VARCHAR2(128) No Name of the object.
COLUMN_NAME VARCHAR2(128) Yes Name of the column.
GRANTOR VARCHAR2(128) Yes Name of the user who performed the grant.
GRANTEE VARCHAR2(128) Yes Name of the user to whom access was granted.
GRANT_TYPE VARCHAR2(128) No Privilege on the object. The value can be SELECT, INSERT, or DELETE.
GRANTABLE VARCHAR2(128) Yes YES, if the privilege was granted with GRANT OPTION, otherwise NO.

A.1.17 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 A-17:

Table A-17 DATABASE_PARAMETERS Parameters

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

A.1.18 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 A-18:

Table A-18 DUAL Parameters

Column Datatype NULL ALLOWED Description
DUMMY VARCHAR2(1) No Always "X".

A.1.19 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 A-19:

Table A-19 TABLE_PRIVILEGES Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No User name of the owner of the object.
TABLE_NAME VARCHAR2(128) No Name of the object.
GRANTOR VARCHAR2(128) Yes Name of the user who performed the grant.
GRANTEE VARCHAR2(128) Yes Name of the user to whom access is granted.
GRANT_TYPE VARCHAR2(128) No Privilege on the object. The value can be one of the following: SELECT, INSERT, or DELETE.
GRANTABLE VARCHAR2(128) Yes YES, if the privilege was granted with GRANT OPTION, and NO, if it was not.

A.1.20 USER_OBJECTS

This view provides the following information about objects accessible to the user. The parameters for this view are listed in Table A-20:

Table A-20 USER_OBJECTS Parameters

Column Datatype NULL ALLOWED Description
OWNER VARCHAR2(128) No User name of the owner of the object.
OBJECT_NAME VARCHAR2(128) No Name of the object.
OBJECT_ID NUMBER(10) No Object identifier of the object.
OBJECT_TYPE VARCHAR2(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 grants and revokes).
CREATED_TIME VARCHAR2(128) Yes Timestamp for the creation of the object (character data).
STATUS* VARCHAR2(128) Yes Status of the object: VALID, INVALID, or N/A (always valid).


Previous Next
Oracle Logo
Copyright © 2002 Oracle Corporation

All rights reserved
Go To Table Of Contents
Contents
Go To Index
Index