These views are based on oracle 11g, but some views include some fields from the new version.
Views are created under oracle and sys schema.
In order to be compatible with oracle, the following views have the same fields as the corresponding views in oracle , but return NULL for those fields whose field values cannot be obtained or are difficult to obtain.
The supported fields are listed below. Some fields have different meanings from oracle.
DBA_SEQUENCES describes all sequences in the database.
The ALL_SEQUENCES view is currently the same as the DBA_SEQUENCES view, and no permission verification is done like oracle.
USER_SEQUENCES describes all sequences owned by the current user.
Table 48.107. ALL_SEQUENCES
|
Column |
Description |
|---|---|
|
SEQUENCE_OWNER |
Owner of the sequence |
|
SEQUENCE_NAME |
Sequence name |
|
MIN_VALUE |
Minimum value of the sequence |
|
MAX_VALUE |
Maximum value of the sequence |
|
INCREMENT_BY |
Value by which sequence is incremented |
|
CYCLE_FLAG |
Indicates whether the sequence wraps around on reaching the limit (Y) or not (N) |
|
ORDER_FLAG |
Indicates whether sequence numbers are generated in order (Y) or not (N), Under lightdb this is always Y |
|
CACHE_SIZE |
Number of sequence numbers to cache |
|
LAST_NUMBER |
Last sequence number written to disk. |
SYNONYMS is not supported in LightDB yet, so these view are empty now.
DBA_TAB_COLS describes the columns of all tables, views in the database.
The ALL_TAB_COLS view is currently the same as the DBA_TAB_COLS view, and no permission verification is done like oracle.
USER_TAB_COLS describes the columns of the tables and views owned by the current user. This view does not display the OWNER column.
COLS is same with USER_TAB_COLS.
DBA_TAB_COLUMNS describes the columns of all tables and views in the database.
The ALL_TAB_COLUMNS view is currently the same as the DBA_TAB_COLUMNS view, and no permission verification is done like oracle.
USER_TAB_COLUMNS describes the columns of the tables and views owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TAB_COLS view differs from [DBA/ALL/USER]_TAB_COLUMNS in that hidden columns are not filtered out(HIDDEN_COLUMN,VIRTUAL_COLUMN,SEGMENT_COLUMN_ID,INTERNAL_COLUMN_ID).
Table 48.108. ALL_TAB_COLS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the table, view |
|
TABLE_NAME |
Name of the table, view |
|
COLUMN_NAME |
Column name |
|
DATA_TYPE |
Datatype of the column |
|
DATA_TYPE_OWNER |
Owner of the datatype of the column |
|
DATA_LENGTH |
Length of the column (in char for character Types; in byte for other types, for number/numeric it is always 22) |
|
DATA_PRECISION |
Decimal precision for NUMBER datatype; binary precision for FLOAT datatype; NULL for all other datatypes |
|
DATA_SCALE |
Digits to the right of the decimal point in a number |
|
NULLABLE |
Indicates whether a column allows NULLs. The 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 |
Sequence number of the column as created |
|
DEFAULT_LENGTH |
Length of the default value for the column |
|
DATA_DEFAULT |
Default value for the column |
|
NUM_DISTINCT |
Number of distinct values in the column |
|
NUM_NULLS |
Number of NULLs in the column |
|
AVG_COL_LEN |
Average length of the column (in bytes) |
|
CHAR_LENGTH |
Displays the length of the column in characters |
|
CHAR_USED |
Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C), or whether the datatype is not any of the following (NULL) |
DBA_TAB_COL_STATISTICS contains column statistics and histogram information extracted from "DBA_TAB_COLUMNS". Its columns are the same as those in "ALL_TAB_COL_STATISTICS".
The ALL_TAB_COL_STATISTICS view is currently the same as the DBA_SEQUENCES view, and no permission verification is done like oracle.
USER_TAB_COL_STATISTICS displays such information extracted from "USER_TAB_COLUMNS". This view does not display the OWNER column.
Table 48.109. ALL_TAB_COL_STATISTICS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
COLUMN_NAME |
Column name |
|
NUM_DISTINCT |
Number of distinct values in the column |
|
NUM_NULLS |
Number of NULLs in the column |
|
AVG_COL_LEN |
Average length of the column (in bytes) |
DBA_OBJECTS describes all objects in the database. Its columns are the same as those in "ALL_OBJECTS".
The ALL_OBJECTS view is currently the same as the DBA_OBJECTS view, and no permission verification is done like oracle.
USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.
OBJ is same with USER_OBJECTS.
Table 48.110. ALL_OBJECTS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
OBJECT_NAME |
Name of the object |
|
SUBOBJECT_NAME |
Name of the subobject (for example, partition), NULL if not exist |
|
OBJECT_ID |
Dictionary object number of the object |
|
DATA_OBJECT_ID |
Dictionary object number of the segment that contains the object |
|
OBJECT_TYPE |
Type of the object |
|
CREATED |
Create time of the object(The start time of the transaction in which the object was created) |
|
STATUS |
Status of the object: VALID, INVALID |
|
TEMPORARY |
Indicates whether the object is temporary (the current session can see only data that it placed in this object itself) (Y) or not (N) |
DBA_CATALOG lists all tables, views, and sequences in the database(no cluster and synonyms).
The ALL_CATALOG view is currently the same as the DBA_CATALOG view, and no permission verification is done like oracle.
USER_OCATALOG displays the tables, views, and sequences in the current user's schema.
All column in oracle view is supported.
Table 48.111. ALL_CATALOG
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the TABLE, VIEW, SEQUENCE |
|
TABLE_NAME |
Name of the TABLE, VIEW, SEQUENCE |
|
TABLE_TYPE |
Type of the TABLE, VIEW, SEQUENCE |
DICTIONARY/DICT contains descriptions of data dictionary tables and views.
Table 48.112. DICTIONARY
|
Column |
Description |
|---|---|
|
TABLE_NAME |
Name of the object |
|
COMMENTS |
Text comment on the object |
DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links. This view does not display the SCHEMAID column.
The ALL_DEPENDENCIES view is currently the same as the DBA_DEPENDENCIES view, and no permission verification is done like oracle.
USER_DEPENDENCIES describes dependencies between objects in the current user's schema. This view does not display the OWNER column.
Table 48.113. ALL_DEPENDENCIES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
NAME |
Name of the object |
|
TYPE |
Type of the object |
|
REFERENCED_OWNER |
Owner of the referenced object (remote owner if remote object) |
|
REFERENCED_NAME |
Name of the referenced object |
|
REFERENCED_TYPE |
Type of the referenced object |
|
DEPENDENCY_TYPE |
Type of the dependency (oracle: Indicates whether the dependency is a REF dependency (REF) or not (HARD)) |
DBA_SOURCE describes the text source of all stored objects in the database.
The ALL_SOURCE view is currently the same as the DBA_SOURCE view, and no permission verification is done like oracle.
USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.
Table 48.114. ALL_SOURCE
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
NAME |
Name of the object |
|
TYPE |
Type of object: FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER |
|
LINE |
Line number of this line of source |
|
TEXT |
Text source of the stored object |
DBA_PROCEDURES lists all functions and procedures, along with associated properties.
The ALL_PROCEDURES view is currently the same as the DBA_PROCEDURES view, and no permission verification is done like oracle.
USER_PROCEDURES lists all functions and procedures owned by the current user, along with associated properties. It does not contain the OWNER column.
These views indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified.
Table 48.115. ALL_PROCEDURES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the procedure |
|
OBJECT_NAME |
Name of the object: top-level function, procedure, or package name |
|
OBJECT_ID |
Object number of the object |
|
OBJECT_TYPE |
The typename of the object |
|
AGGREGATE |
Indicates whether the procedure is an aggregate function (YES) or not (NO) |
|
PARALLEL |
Indicates whether the procedure or function is parallel-enabled (YES) or not (NO) |
|
DETERMINISTIC |
YES, if the procedure/function is declared to be deterministic; otherwise NO |
DBA_TRIGGERS describes all triggers in the database.
The ALL_TRIGGERS view is currently the same as the DBA_TRIGGERS view, and no permission verification is done like oracle.
USER_TRIGGERS describes the triggers owned by the current user
Table 48.116. ALL_TRIGGERS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the trigger |
|
TRIGGER_NAME |
Name of the trigger |
|
TRIGGER_TYPE |
When the trigger fires: BEFORE STATEMENT, BEFORE EACH ROW, AFTER STATEMENT, AFTER EACH ROW, INSTEAD OF EACH ROW |
|
TRIGGERING_EVENT |
DML that fires the trigger |
|
TABLE_OWNER |
Owner of the table on which the trigger is defined |
|
BASE_OBJECT_TYPE |
Base object on which the trigger is defined: TABLE, VIEW |
|
TABLE_NAME |
Indicates the table or view name on which the trigger is defined |
|
REFERENCING_NAMES |
Names used for referencing OLD and NEW column values from within the trigger |
|
WHEN_CLAUSE |
Must evaluate to TRUE for TRIGGER_BODY to execute |
|
STATUS |
Indicates whether the trigger is enabled (ENABLED) or disabled (DISABLED); a disabled trigger will not fire |
|
ACTION_TYPE |
Action type of the trigger body: 'PL/SQL' |
|
TRIGGER_BODY |
Statements executed by the trigger when it fires |
|
BEFORE_STATEMENT |
Indicates whether the trigger has a BEFORE STATEMENT section (YES) or not (NO) |
|
BEFORE_ROW |
Indicates whether the trigger has a BEFORE EACH ROW section (YES) or not (NO) |
|
AFTER_ROW |
Indicates whether the trigger has an AFTER EACH ROW section (YES) or not (NO) |
|
AFTER_STATEMENT |
Indicates whether the trigger has an AFTER STATEMENT section (YES) or not (NO) |
|
INSTEAD_OF_ROW |
Indicates whether the trigger has an INSTEAD OF section (YES) or not (NO) |
DBA_TRIGGER_COLS describes the use of columns in all triggers in the database.
The ALL_TRIGGER_COLS view is currently the same as the DBA_TRIGGER_COLS view, and no permission verification is done like oracle.
USER_TRIGGER_COLS describes the use of columns in the triggers owned by the current user and in triggers on tables owned by the current user.
Table 48.117. ALL_TRIGGER_COLS
|
Column |
Description |
|---|---|
|
TRIGGER_OWNER |
Owner of the trigger |
|
TRIGGER_NAME |
Name of the trigger |
|
TABLE_OWNER |
Owner of the table on which the trigger is defined |
|
TABLE_NAME |
Table on which the trigger is defined |
|
COLUMN_NAME |
Name of the column used in the trigger |
DBA_TYPES describes all object types in the database.
The ALL_TYPES view is currently the same as the DBA_TYPES view, and no permission verification is done like oracle.
USER_TYPES describes the object types owned by the current user. This view does not display the OWNER column.
Table 48.118. ALL_TYPES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the type |
|
TYPE_NAME |
Name of the type |
|
TYPE_OID |
Object identifier (OID) of the type |
|
TYPECODE |
Typecode of the type |
|
PREDEFINED |
Indicates whether the type is a predefined type (YES) or not (NO) |
DBA_CONSTRAINTS describes all constraint definitions in the database.
The ALL_CONSTRAINTS view is currently the same as the DBA_CONSTRAINTS view, and no permission verification is done like oracle.
USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.
Table 48.119. ALL_CONSTRAINTS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the constraint definition |
|
CONSTRAINT_NAME |
Name of the constraint definition |
|
CONSTRAINT_TYPE |
Type of the constraint definition c - Check constraint on a table p - Primary key u - Unique key f - Constraint that involves a REF column t - Constraint triggers t - exclusivity Constraint |
|
TABLE_NAME |
Name associated with the table (or view) with the constraint definition |
|
SEARCH_CONDITION |
Text of search condition for a check constraint |
|
R_OWNER |
Owner of the table referred to in a referential constraint |
|
DELETE_RULE |
Delete rule for a referential constraint: 'NO ACTION', 'RESTRICT', 'CASCADE', 'SET NULL', 'SET DEFAULT' |
|
STATUS |
Enforcement status of the constraint: 'ENABLED', 'DISABLED' |
|
DEFERRABLE |
Indicates whether the constraint is deferrable (DEFERRABLE) or not (NOT DEFERRABLE) |
|
DEFERRED |
Indicates whether the constraint was initially deferred (DEFERRED) or not (IMMEDIATE) |
|
VALIDATED |
Has the constraint been validated? Currently, can be false only for foreign keys and CHECK constraints |
|
INDEX_OWNER |
Name of the user owning the index |
|
INDEX_NAME |
Name of the index (only shown for unique, primary key, foreign key, or exclusion constraint) |
|
INVALID |
Indicates whether the constraint is invalid (INVALID) or not (NULL) |
DBA_CONS_COLUMNS describes all columns in the database that are specified in constraints.
The ALL_CONS_COLUMNS view is currently the same as the DBA_CONS_COLUMNS view, and no permission verification is done like oracle.
USER_CONS_COLUMNS describes columns that are owned by the current user and that are specified in constraints.
All column in oracle are supported
Table 48.120. ALL_CONS_COLUMNS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the constraint definition |
|
CONSTRAINT_NAME |
Name of the constraint definition |
|
TABLE_NAME |
Name of the table with the constraint definition |
|
COLUMN_NAME |
Name of the column or attribute of the object type column specified in the constraint definition |
|
POSITION |
Original position of the column or attribute in the definition of the object |
DBA_VIEWS describes all views in the database.
The ALL_VIEWS view is currently the same as the DBA_VIEWS view, and no permission verification is done like oracle.
USER_VIEWS describes the views owned by the current user. This view does not display the OWNER column.
Table 48.121. ALL_VIEWS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the view |
|
VIEW_NAME |
Name of the view |
|
TEXT_LENGTH |
Length of the view text |
|
TEXT |
View text |
DBA_ALL_TABLES describes all object tables and relational tables in the database.
The ALL_ALL_TABLES view is currently the same as the DBA_ALL_TABLES view, and no permission verification is done like oracle.
USER_ALL_TABLES describes the object tables and relational tables owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TABLES does not display the OBJECT_ID_TYPE, TABLE_TYPE_OWNER and TABLE_TYPE column.
[DBA/ALL/USER]_TABLES has RESULT_CACHE column, but [DBA/ALL/USER]_ALL_TABLES does not.
TABS is same with USER_TABLES.
Table 48.122. ALL_ALL_TABLES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
TABLESPACE_NAME |
Name of the tablespace containing the table |
|
LOGGING |
Indicates whether or not changes to the table are logged |
|
NUM_ROWS |
Number of rows in the table |
|
BLOCKS |
Number of used blocks in the table |
|
AVG_ROW_LEN |
Average row length, including row overhead |
|
LAST_ANALYZED |
Date on which the table was most recently analyzed |
|
PARTITIONED |
Indicates whether the table is partitioned (YES) or not (NO) |
|
TEMPORARY |
Indicates whether the table is temporary (Y) or not (N) |
|
ROW_MOVEMENT |
If a partitioned table, indicates whether row movement is enabled (ENABLED) or disabled (DISABLED) Always is 'ENABLE' |
DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
The ALL_TAB_STATISTICS view is currently the same as the DBA_TAB_STATISTICS view, and no permission verification is done like oracle.
USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.
[DBA/ALL/USER]_TABLES does not display the OBJECT_ID_TYPE, TABLE_TYPE_OWNER and TABLE_TYPE column.
Except for the fields listed in the table below, all other fields will only return default values with no practical significance.
Table 48.123. ALL_TAB_STATISTICS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
TABLE_NAME |
Name of the table |
|
TABLESPACE_NAME |
Name of the tablespace containing the table |
|
PARTITION_NAME |
Name of the partition |
|
PARTITION_POSITION |
Position of the partition within the table |
|
OBJECT_TYPE |
Type of the object：TABLE, PARTIOTION, SUBPARTITION |
|
NUM_ROWS |
Number of rows in the object |
|
BLOCKS |
Number of used blocks in the object |
|
AVG_ROW_LEN |
Average row length, including row overhead |
|
LAST_ANALYZED |
Date of the most recent time the table was analyzed |
DBA_TAB_COMMENTS displays comments on all tables and views in the database.
The ALL_TAB_COMMENTS view is currently the same as the DBA_TAB_COMMENTS view, and no permission verification is done like oracle.
USER_TAB_COMMENTS displays comments on the tables and views owned by the current user. This view does not display the OWNER column.
Table 48.124. ALL_TAB_COMMENTS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
TABLE_NAME |
Name of the object |
|
TABLE_TYPE |
Type of the object |
|
COMMENTS |
Comment on the object |
DBA_COL_COMMENTS displays comments on the columns of all tables and views in the database.
The ALL_COL_COMMENTS view is currently the same as the DBA_COL_COMMENTS view, and no permission verification is done like oracle.
USER_COL_COMMENTS displays comments on the columns of the tables and views owned by the current user. This view does not display the OWNER column.
Table 48.125. ALL_COL_COMMENTS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the object |
|
TABLE_NAME |
Name of the object |
|
COLUMN_NAME |
Name of the column |
|
COMMENTS |
Name of the column |
DBA_TAB_MODIFICATIONS describes such information for all tables in the database.
The ALL_TAB_MODIFICATIONS view is currently the same as the DBA_TAB_MODIFICATIONS view, and no permission verification is done like oracle.
USER_TAB_MODIFICATIONS describes such information for tables owned by the current user. This view does not display the TABLE_OWNER column.
Table 48.126. ALL_TAB_MODIFICATIONS
|
Column |
Description |
|---|---|
|
TABLE_OWNER |
Owner of the modified table |
|
TABLE_NAME |
Name of the modified table |
|
INSERTS |
Approximate number of inserts since the last time statistics were gathered |
|
UPDATES |
Approximate number of updates since the last time statistics were gathered |
|
DELETES |
Approximate number of deletes since the last time statistics were gathered |
|
TIMESTAMP |
Indicates the last time the table was modified |
DBA_INDEXES describes all indexes in the database.
The ALL_INDEXES view is currently the same as the DBA_INDEXES view, and no permission verification is done like oracle.
USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.
IND is same with USER_INDEXES.
Table 48.127. ALL_INDEXES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the index |
|
INDEX_NAME |
Name of the index |
|
INDEX_TYPE |
Type of the index, always NORMAL |
|
TABLE_OWNER |
Owner of the indexed object |
|
TABLE_NAME |
Name of the indexed object |
|
TABLE_TYPE |
Type of the indexed object: TABLE, VIEW, INDEX, SEQUENCE |
|
UNIQUENESS |
Indicates whether the index is unique (UNIQUE) or nonunique (NONUNIQUE) |
|
TABLESPACE_NAME |
Name of the tablespace containing the index |
|
LOGGING |
Indicates whether or not changes to the index are logged: 'YES', 'NO' |
|
DISTINCT_KEYS |
Number of distinct indexed values |
|
STATUS |
Indicates whether a nonpartitioned index is VALID or UNUSABLE |
|
NUM_ROWS |
Number of rows in the index |
|
LAST_ANALYZED |
Date on which this index was most recently analyzed |
|
PARTITIONED |
Indicates whether the index is partitioned (YES) or not (NO) |
|
TEMPORARY |
Indicates whether the index is on a temporary table (Y) or not (N) |
DBA_INDEX_USAGE displays cumulative statistics for each index.
The ALL_INDEX_USAGE view is same with DBA_INDEX_USAGE(this view is not exist in oracle).
USER_INDEX_USAGE describes cumulative statistics for each index owned by the current user(this view is not exist in oracle). This view does not display the OWNER column.
Table 48.128. DBA_INDEX_USAGE
|
Column |
Description |
|---|---|
|
OBJECT_ID |
Object ID for the index |
|
NAME |
Index name |
|
OWNER |
Index owner |
|
TOTAL_ACCESS_COUNT |
Total number of times the index has been accessed |
|
TOTAL_ROWS_RETURNED |
Total rows returned by the index |
DBA_IND_COLUMNS describes the columns of indexes on all tables in the database.
The ALL_IND_COLUMNS view is currently the same as the DBA_IND_COLUMNS view, and no permission verification is done like oracle.
USER_IND_COLUMNS describes the columns of indexes owned by the current user and columns of indexes on tables owned by the current user. This view does not display the INDEX_OWNER or TABLE_OWNER columns.
Table 48.129. ALL_IND_COLUMNS
|
Column |
Description |
|---|---|
|
INDEX_OWNER |
Owner of the index |
|
INDEX_NAME |
Name of the index |
|
TABLE_OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
COLUMN_NAME |
Column name or attribute of the object type column |
|
COLUMN_POSITION |
Position of the column or attribute within the index |
|
COLUMN_LENGTH |
Indexed length of the column, for number/numeric it is always 22 |
|
CHAR_LENGTH |
Maximum codepoint length of the column |
|
DESCEND |
Indicates whether the column is sorted in descending order (DESC) or ascending order (ASC) |
DBA_IND_EXPRESSIONS describes the expressions of all function-based indexes in the database.
The ALL_IND_EXPRESSIONS view is currently the same as the DBA_IND_COLUMNS view, and no permission verification is done like oracle.
USER_IND_EXPRESSIONS describes the expressions of function-based indexes on tables owned by the current user. This view does not display the INDEX_OWNER or TABLE_OWNER columns.
Table 48.130. ALL_IND_EXPRESSIONS
|
Column |
Description |
|---|---|
|
INDEX_OWNER |
Owner of the index |
|
INDEX_NAME |
Name of the index |
|
TABLE_OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
COLUMN_EXPRESSION |
Function-based index expression defining the column |
|
COLUMN_POSITION |
Position of the column or attribute within the index |
DBA_IND_STATISTICS displays optimizer statistics for all indexes in the database.
The ALL_IND_STATISTICS view is currently the same as the DBA_IND_STATISTICS view, and no permission verification is done like oracle.
USER_IND_STATISTICS displays optimizer statistics for the indexes on the tables owned by the current user. This view does not display the OWNER column.
Table 48.131. ALL_IND_STATISTICS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the index |
|
INDEX_NAME |
Name of the index |
|
TABLE_OWNER |
Owner of the indexed object |
|
TABLE_NAME |
Name of the indexed object |
|
PARTITION_NAME |
Function-based index expression defining the column |
|
PARTITION_POSITION |
Position of the column or attribute within the index |
|
OBJECT_TYPE |
Type of the object:INDEX, PARTITION Always 'INDEX' now, because this view will not to get partition info |
|
DISTINCT_KEYS |
Number of distinct keys in the index |
|
NUM_ROWS |
Number of rows in the index |
|
LAST_ANALYZED |
Date of the most recent time the index was analyzed |
DBA_USERS describes all users of the database, and contains more columns than ALL_USERS.
ALL_USERS lists all users of the database(USERNAME, USER_ID, CREATED), but no permission verification is done like oracle.
USER_USERS describes the current user, and contains more columns than ALL_USERS.
Table 48.132. DBA_USERS
|
Column |
Description |
|---|---|
|
USERNAME |
Name of the user |
|
USER_ID |
ID number of the user |
|
PASSWORD |
This column is deprecated in favor of the AUTHENTICATION_TYPE column |
|
ACCOUNT_STATUS |
Account status, always 'NORMAL' |
|
EXPIRY_DATE |
Date of expiration of the account |
Table 48.133. ALL_USERS
|
Column |
Description |
|---|---|
|
USERNAME |
Name of the user |
|
USER_ID |
ID number of the user |
Describes all roles in the database
Table 48.134. DBA_ROLES
|
Column |
Description |
|---|---|
|
ROLE |
Name of the role |
|
PASSWORD_REQUIRED |
This column is deprecated in favor of the AUTHENTICATION_TYPE column, always 'NO' |
|
AUTHENTICATION_TYPE |
Indicates the authentication mechanism for the role, always 'NONE' NONE - CREATE ROLE role1; |
|
COMMON |
Indicates whether a given role is common. always 'YES', does not exist in oracle 11g |
DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
USER_ROLE_PRIVS describes the roles granted to the current user.
Table 48.135. DBA_ROLE_PRIVS
|
Column |
Description |
|---|---|
|
GRANTEE |
Name of the user or role receiving the grant |
|
GRANTED_ROLE |
Granted role name |
|
ADMIN_OPTION |
Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO) |
|
DEFAULT_ROLE |
Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) Always 'NO' |
|
COMMON |
Indicates how the grant was made always 'YES', does not exist in oracle 11g |
Contains version and status information for component products.
Table 48.136. PRODUCT_COMPONENT_VERSION
|
Column |
Description |
|---|---|
|
PRODUCT |
Product name |
|
VERSION |
Version number |
|
STATUS |
Status of release |
PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users.
it is not supported by LightDB, so this view is empty for compatibility.
Describes database files
Table 48.137. DBA_DATA_FILES
|
Column |
Description |
|---|---|
|
FILE_NAME |
Name of the database file |
|
FILE_ID |
File identifier number of the database file |
|
TABLESPACE_NAME |
Name of the tablespace to which the file belongs |
|
BYTES |
Size of the file in bytes |
|
BLOCKS |
Size of the file in Oracle blocks |
|
STATUS |
File status: AVAILABLE or INVALID Always 'AVAILABLE' |
|
RELATIVE_FNO |
Relative file number |
|
AUTOEXTENSIBLE |
Autoextensible indicator Always 'YES' |
|
INCREMENT_BY |
Number of Oracle blocks used as autoextension increment |
|
ONLINE_STATUS |
Online status of the file Always 'ONLINE' |
Get info from lt_cron
DBA_JOBS describes all jobs in the database.
USER_JOBS describes the jobs owned by the current user.
ALL_JOBS is same with USER_JOBS.
Table 48.138. DBA_JOBS
|
Column |
Description |
|---|---|
|
JOB |
Identifier of job |
|
LOG_USER |
Login user when the job was submitted |
|
PRIV_USER |
User whose default privileges apply to this job |
|
SCHEMA_USER |
Default schema used to parse the job Always 'CRON' |
|
LAST_DATE |
Date on which this job last successfully executed |
|
LAST_SEC |
Same as LAST_DATE. This is when the last successful execution started 'HH24:MI:SS' format |
|
THIS_DATE |
Date that this job started executing (usually null if not executing) |
|
THIS_SEC |
Same as THIS_DATE. This is when the last successful execution started 'HH24:MI:SS' format |
|
TOTAL_TIME |
Total wall clock time spent by the system on this job in the last ececuted It is not same with oracle. In oracle, it means total wall clock time spent by the system on this job (in seconds) since the first time this job executed, and this value is cumulative |
|
BROKEN |
Y: no attempt is made to run this job, N: an attempt is made to run this job Always 'N' |
|
INTERVAL |
String uses the standard cron syntax, ref to lt_cron Different from oracle |
|
WHAT |
LightDB commands Different from oracle |
Get info from lt_cron
Lists all jobs that are currently running in the instance.
Table 48.139. DBA_JOBS_RUNNING
|
Column |
Description |
|---|---|
|
SID |
Identifier of process that is executing the job |
|
JOB |
Identifier of job. This job is currently executing |
|
LAST_DATE |
Date on which this job last successfully executed |
|
LAST_SEC |
Same as LAST_DATE. This is when the last successful execution started 'HH24:MI:SS' format |
|
THIS_DATE |
Date that this job started executing (usually null if not executing) |
|
THIS_SEC |
Same as THIS_DATE. This is when the last successful execution started 'HH24:MI:SS' format |
DBA_TABLESPACES describes all tablespaces in the database.
USER_TABLESPACES describes the tablespaces accessible to the current user. This view does not display the PLUGGED_IN column.
Table 48.140. DBA_TABLESPACES
|
Column |
Description |
|---|---|
|
TABLESPACE_NAME |
Name of the tablespace |
|
BLOCK_SIZE |
Tablespace block size (in bytes) |
|
STATUS |
Tablespace status Always 'ONLINE' |
|
CONTENTS |
Tablespace contents Always 'PERMANENT' |
NLS_DATABASE_PARAMETERS lists permanent NLS parameters of the database.
NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
NLS_SESSION_PARAMETERS lists NLS parameters of the user session.
Table 48.141. NLS_DATABASE_PARAMETERS
|
Column |
Description |
|---|---|
|
PARAMETER |
Parameter name |
|
VALUE |
Parameter value |
DBA_SEGMENTS describes the storage allocated for all segments in the database.
USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.
Table 48.142. DBA_SEGMENTS
|
Column |
Description |
|---|---|
|
OWNER |
Username of the segment owner |
|
SEGMENT_NAME |
Name, if any, of the segment |
|
PARTITION_NAME |
Object Partition Name (Set to NULL for non-partitioned objects) |
|
SEGMENT_TYPE |
Type of segment:'TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION' |
|
TABLESPACE_NAME |
Name of the tablespace containing the segment |
|
HEADER_FILE |
Name of the tablespace containing the segment |
|
BYTES |
Size, in bytes, of the segments |
|
BLOCKS |
Size, in LightDB blocks, of the segment |
DBA_PART_TABLES displays the object-level partitioning information for all partitioned tables in the database.
The ALL_PART_TABLES view is currently the same as the DBA_PART_TABLES view, and no permission verification is done like oracle.
USER_PART_TABLES displays the object-level partitioning information for the partitioned tables owned by the current user. This view does not display the OWNER column.
The supported fields are listed below. unsupported fields are filed with NULL.
Table 48.143. ALL_PART_TABLES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the partitioned table |
|
TABLE_NAME |
Name of the partitioned table |
|
PARTITIONING_TYPE |
Type of the partitioning method: RANGE, HASH, LIST |
|
SUBPARTITIONING_TYPE |
Type of the composite partitioning method: RANGE, HASH, LIST |
|
PARTITION_COUNT |
Number of partitions in the table |
|
PARTITIONING_KEY_COUNT |
Number of columns in the partitioning key |
|
SUBPARTITIONING_KEY_COUNT |
For a composite-partitioned table, the number of columns in the subpartitioning key |
|
DEF_TABLESPACE_NAME |
Default tablespace to be used when adding a partition |
|
DEF_LOGGING |
Default LOGGING attribute to be used when adding a partition |
DBA_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics for all partitions in the database.
The ALL_TAB_PARTITIONS view is currently the same as the DBA_TAB_PARTITIONS view, and no permission verification is done like oracle.
USER_TAB_PARTITIONS displays such information for the partitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.
When a new partition is created, the partition name is identical to the name specified in ADD/CREATE/MODIFY statement, for range interval partition we prefix the partition name with "ap", for hash partition that did not has a explicit name, we prefix the partition name with "p".
Table 48.144. ALL_TAB_PARTITIONS
|
Column |
Description |
|---|---|
|
TABLE_OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
COMPOSITE |
Indicates whether the table is composite-partitioned (YES) or not (NO) |
|
PARTITION_NAME |
Name of the partition |
|
SUBPARTITION_COUNT |
If this is a composite partitioned table, the number of subpartitions in the partition |
|
PARTITION_POSITION |
Position of the partition within the table |
|
TABLESPACE_NAME |
Name of the tablespace containing the partition |
|
LOGGING |
Indicates whether or not changes to the table are logged |
|
NUM_ROWS |
Number of rows in the partition |
|
BLOCKS |
Number of used data blocks in the partition |
|
LAST_ANALYZED |
Date on which this partition was most recently analyzed |
DBA_TAB_SUBPARTITIONS displays the subpartition name, name of the table and partition to which it belongs, its storage attributes, and statistics for all subpartitions in the database.
The ALL_TAB_SUBPARTITIONS view is currently the same as the DBA_TAB_SUBPARTITIONS view, and no permission verification is done like oracle.
USER_TAB_SUBPARTITIONS displays such information for subpartitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.
When a new subpartition is created, the subpartition name is identical to the name specified in the ADD statement, and prefixed with its partition name while created in CREATE statement. When dealing with hash partitions that do not have an explicit name, we prepend the partition name with "p".
Table 48.145. ALL_TAB_SUBPARTITIONS
|
Column |
Description |
|---|---|
|
TABLE_OWNER |
Owner of the table |
|
TABLE_NAME |
Name of the table |
|
PARTITION_NAME |
Name of the partition |
|
SUBPARTITION_NAME |
Name of the subpartition |
|
PARTITION_POSITION |
Position of the partition within the table |
|
SUBPARTITION_POSITION |
Position of the subpartition within the partition |
|
TABLESPACE_NAME |
Name of the tablespace containing the partition |
|
LOGGING |
Indicates whether or not changes to the table are logged |
|
NUM_ROWS |
Number of rows in the subpartition |
|
BLOCKS |
Number of blocks in the subpartition |
|
LAST_ANALYZED |
Date on which this table was most recently analyzed |
DBA_PART_KEY_COLUMNS describes the partitioning key columns for all partitioned objects in the database.
The ALL_PART_KEY_COLUMNS view is currently the same as the DBA_PART_KEY_COLUMNS view, and no permission verification is done like oracle.
USER_PART_KEY_COLUMNS describes the partitioning key columns for the partitioned objects owned by the current user. This view does not display the OWNER column.
Table 48.146. ALL_PART_KEY_COLUMNS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the partitioned table or index |
|
NAME |
Name of the partitioned table or index |
|
OBJECT_TYPE |
Object type: TABLE, INDEX |
|
COLUMN_NAME |
Name of the column |
|
COLUMN_POSITION |
Position of the column within the partitioning key |
DBA_SUBPART_KEY_COLUMNS displays subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables) for all subpartitions in the database.
The ALL_SUBPART_KEY_COLUMNS view is currently the same as the DBA_SUBPART_KEY_COLUMNS view, and no permission verification is done like oracle.
USER_SUBPART_KEY_COLUMNS displays this information for subpartitions of all partitioned objects owned by the current user. This view does not display the OWNER column.
Table 48.147. ALL_SUBPART_KEY_COLUMNS
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the partitioned table or index |
|
NAME |
Name of the partitioned table or index |
|
OBJECT_TYPE |
Object type: TABLE, INDEX |
|
COLUMN_NAME |
Name of the column |
|
COLUMN_POSITION |
Position of the column within the subpartitioning key |
DBA_IND_PARTITIONS describes all index partitions in the database.
The ALL_IND_PARTITIONS view is currently the same as the DBA_IND_PARTITIONS view, and no permission verification is done like oracle.
USER_IND_PARTITIONS describes the index partitions owned by the current user. This view does not display the INDEX_OWNER column.
Table 48.148. ALL_IND_PARTITIONS
|
Column |
Description |
|---|---|
|
INDEX_OWNER |
Owner of the index |
|
INDEX_NAME |
Name of the index |
|
COMPOSITE |
Indicates whether the partition belongs to a local index on a composite-partitioned table (YES) or not (NO) |
|
PARTITION_NAME |
Name of the partition |
|
SUBPARTITION_COUNT |
If a local index on a composite-partitioned table, the number of subpartitions in the partition |
|
PARTITION_POSITION |
Position of the partition within the index |
|
TABLESPACE_NAME |
Name of the tablespace containing the partition |
|
LOGGING |
Indicates whether or not changes to the index are logged |
|
NUM_ROWS |
Number of rows returned |
|
LAST_ANALYZED |
Date on which this partition was most recently analyzed |
DBA_PART_INDEXES displays the object-level partitioning information for all partitioned indexes in the database.
The ALL_PART_INDEXES view is currently the same as the DBA_PART_INDEXES view, and no permission verification is done like oracle.
USER_PART_INDEXES displays the object-level partitioning information for the partitioned indexes owned by the current user. This view does not display the OWNER column.
Table 48.149. ALL_PART_INDEXES
|
Column |
Description |
|---|---|
|
OWNER |
Owner of the partitioned index |
|
INDEX_NAME |
Name of the partitioned index |
|
TABLE_NAME |
Name of the partitioned table |
|
PARTITIONING_TYPE |
Type of the partitioning method: RANGE, HASH, LIST |
|
SUBPARTITIONING_TYPE |
Type of the composite partitioning method: RANGE, HASH, LIST |
|
PARTITION_COUNT |
Number of partitions in the index |
|
PARTITIONING_KEY_COUNT |
Number of columns in the partitioning key |
|
SUBPARTITIONING_KEY_COUNT |
For a composite-partitioned table, the number of columns in the subpartitioning key |
|
LOCALITY |
Indicates whether the partitioned index is local (LOCAL) or global (GLOBAL), always 'LOCAL' |
|
ALIGNMENT |
Indicates whether the partitioned index is prefixed (PREFIXED) or non-prefixed (NON_PREFIXED) |
|
DEF_TABLESPACE_NAME |
For a local index, the default tablespace to be used when adding or splitting a table partition |
|
DEF_LOGGING |
For a local index, the default LOGGING attribute to be used when adding a table partition |
COL describes the columns of the tables and views owned by the current user. This view does not display the OWNER column.
Table 48.150. COL
|
Column |
Description |
|---|---|
|
TNAME |
Name of the table, view |
|
COLNO |
Sequence number of the column as created |
|
CNAME |
Column name |
|
COLTYPE |
Datatype of the column |
|
WIDTH |
Length of the column(in char for character Types; in byte for other types, for number/numeric it is always 22) |
|
SCALE |
Digits to the right of the decimal point in a number |
|
PRECISION |
Decimal precision for NUMBER datatype; binary precision for FLOAT datatype; NULL for all other datatypes |
|
NULLS |
Indicates whether a column allows NULLs. The value is 'NOT NULL' if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY |
|
DEFAULTVAL |
Default value for the column |
DBA_RECYCLEBIN describes all user dropped objects by command DROP TABLE
USER_RECYCLEBIN describes current user dropped objects by command DROP TABLE
Table 48.151. DBA_RECYCLEBIN
|
Column |
Description |
|---|---|
|
OWNER |
name of the original owner of the object |
|
OBJECT_NAME |
new name of the object |
|
ORIGINAL_NAME |
original name of the object |
|
NAMESPACE |
name of the schema to which the object belongs |
|
OPERATION |
operation carried out on the object |
|
type |
type of the object |
|
TS_NAME |
name of the tablespace to which the object belongs |
|
DROPTIME |
timestamp for the deletion of the object |
|
DROPSCN |
unused in lightdb |
|
CNA_UNDROP |
unused in lightdb |
|
CNA_PURGE |
unused in lightdb |
|
RELATED |
object number of the parent object |
|
BASE_OBJECT |
object number of the base object |
|
PURGE_OBJECT |
object number for the object which gets purged |
|
SPACE |
unused in lightdb |