Oracle8i Reference
Release 8.1.5
A67790-01

Library
Product
Contents
Index

Prev Next

2
Static Data Dictionary Views

This chapter contains descriptions of data dictionary tables and views. To see the data dictionary views available to you, query the view DICTIONARY.

See Chapter 3, "Dynamic Performance (V$) Views" for descriptions of the V$ views.

In Trusted Oracle Server, each of the dictionary tables and views contains a column that indicates the label of each row in the table or view. Trusted Oracle also provides some additional dictionary tables and views, and some Oracle dictionary tables and views contain columns that support compatibility with Trusted Oracle applications.

See your Trusted Oracle documentation for more information about Trusted Oracle dictionary tables and views.

Data Dictionary Views

The following is an alphabetical reference of the data dictionary views accessible to all users of an Oracle Server. Most views can be accessed by any user with the CREATE_SESSION privilege.

The data dictionary views that begin with DBA_ are restricted. These views can be accessed only by users with the SELECT_ANY_TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.

ALL_ALL_TABLES

This view describes all of the tables (object tables and relational tables) accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the table  

TABLE_NAME  

VARCHAR2(30)  

 

Name of the table  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the table  

CLUSTER_NAME  

VARCHAR2(30)  

 

Name of the cluster, if any, to which the table belongs  

IOT_NAME  

VARCHAR2(30)  

 

Name of the index organized table, if any, to which the overflow entry belongs  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a bloc  

PCT_USED  

NUMBER  

 

Minimum percentage of used space in a block  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute  

BACKED_UP  

VARCHAR2(1)  

 

Has table been backed up since last modification?  

NUM_ROWS  

NUMBER  

 

The number of rows in the table  

BLOCKS  

NUMBER  

 

The number of used blocks in the table  

EMPTY_BLOCKS  

NUMBER  

 

The number of empty (never used) blocks in the table  

AVG_SPACE  

NUMBER  

 

The average available free space in the table  

CHAIN_CNT  

NUMBER  

 

The number of chained rows in the table  

AVG_ROW_LEN  

NUMBER  

 

The average row length, including row overhead  

AVG_SPACE_FREELIST
_BLOCKS  

NUMBER  

 

The average freespace of all blocks on a freelist  

NUM_FREELIST_BLOCKS  

NUMBER  

 

The number of blocks on the freelist  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the table  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the table is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the table is to be cached in the buffer cache  

TABLE_LOCK  

VARCHAR2(8)  

 

Whether table locking is enabled or disabled  

SAMPLE_SIZE  

NUMBER  

 

The sample size used in analyzing this table  

LAST_ANALYZED  

DATE  

 

The date of the most recent time this table was analyzed  

PARTITIONED  

VARCHAR2(3)  

 

Is this table partitioned? YES or NO  

IOT_TYPE  

VARCHAR2(12)  

 

If an index organized table, then IOT_TYPE is IOT or IOT_OVERFLOW else NULL  

TABLE_TYPE_OWNER  

VARCHAR2(0)  

 

Owner of the type of the table if the table is a typed table  

TABLE_TYPE  

VARCHAR2(0)  

 

Type of the table if the table is a typed table  

PACKED  

VARCHAR2(0)  

 

If the table is a typed table, does it store objects in packed format?  

OBJECT_ID_TYPE  

VARCHAR2(16)  

 

If user-defined OID, then USER-DEFINED, else if system generated OID, then SYSTEM GENERATED  

TABLE_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the table if the table is a typed table  

TABLE_TYPE  

VARCHAR2(30)  

 

Type of the table if the table is a typed table  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it place in this object itself?  

NESTED  

VARCHAR2(3)  

 

Is the table a nested table?  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object  

ROW_MOVEMENT  

VARCHAR2(8)  

 

Whether partitioned row movement is enabled or disabled  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

If temporary table, then duration is sys$session or sys$transaction, else NULL  

SKIP_CORRUPT  

VARCHAR2(8)  

 

Whether skip corrupt blocks is enabled or disabled  

ALL_ARGUMENTS

This view lists all of the arguments in the object which are accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the object  

OBJECT_NAME  

VARCHAR2(30)  

 

Procedure or function name  

OVERLOAD  

VARCHAR2(40)  

 

Overload unique identifier  

PACKAGE_NAME  

VARCHAR2(30)  

 

Package name  

OBJECT_ID  

NUMBER  

NOT NULL  

Object number of the object  

ARGUMENT_NAME  

VARCHAR2(30)  

 

Argument name  

POSITION  

NUMBER  

NOT NULL  

Position in argument list, or NULL for function return value  

SEQUENCE  

NUMBER  

NOT NULL  

Argument sequence, including all nesting levels  

DATA_LEVEL  

NUMBER  

NOT NULL  

Nesting depth of argument for composite types  

DATA_TYPE  

VARCHAR2(14)  

 

Datatype of the argument  

DEFAULT_VALUE  

LONG  

 

Default value for the argument  

DEFAULT_LENGTH  

NUMBER  

 

Length of default value for the argument  

IN_OUT  

VARCHAR2(9)  

 

Argument direction (IN, OUT, or IN/OUT)  

DATA_LENGTH  

NUMBER  

 

Length of the column in bytes  

DATA_PRECISION  

NUMBER  

 

Length: decimal digits (NUMBER) or binary digits (FLOAT)  

DATA_SCALE  

NUMBER  

 

Digits to right of decimal point in a number  

RADIX  

NUMBER  

 

Argument radix for a number  

CHARACTER_SET_NAME  

VARCHAR2(44)  

 

Character set name for the argument  

TYPE_OWNER  

VARCHAR2(30)  

 

Owner name of the type  

TYPE_NAME  

VARCHAR2(30)  

 

Name  

TYPE_SUBNAME  

VARCHAR2(30)  

 

This is valid only in case of package local types; in such cases, the package name is the name and the type name is the subname  

TYPE_LINK  

VARCHAR2(128)  

 

Database link valid only in case of package local types, in case the package is remote  

ALL_ASSOCIATIONS

This view lists user-defined statistics information.

Column   Datatype   NULL   Description  

OBJECT_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object for which the association is being defined  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

Object name for which the association is being defined  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name in the object for which the association is being defined  

OBJECT_TYPE  

VARCHAR2(9)  

 

Schema type of the object - column, type, package or function  

STATSTYPE_SCHEMA  

VARCHAR2(30)  

 

Owner of the statistics type  

STATSTYPE_NAME  

VARCHAR2(30)  

 

Name of Statistics type which contains the cost, selectivity or stats funcs  

DEF_SELECTIVITY  

NUMBER  

 

Default Selectivity if any of the object  

DEF_CPU_COST  

NUMBER  

 

Default CPU cost if any of the object  

DEF_IO_COST  

NUMBER  

 

Default I/O cost if any of the object  

DEF_NET_COST  

NUMBER  

 

Default Networking cost if any of the object  

ALL_CATALOG

This view lists all tables, views, synonyms, and sequences accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

TABLE_TYPE  

VARCHAR2(11)  

 

Type of the object  

ALL_CLUSTERS

This view list all clusters accessible to the user.

Column   Datatype   NULL   Description  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the cluster  

OWNER  

VARCHAR2(30)  

NOT NULL  

The owner of the cluster  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

The name of the cluster  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the cluster  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

NOT NULL  

Minimum percentage of used space in a block  

KEY_SIZE  

NUMBER  

 

Estimated size of cluster key plus associated rows  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENTS  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated to this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated to this segment  

AVG_BLOCKS_PER
_KEY  

NUMBER  

 

Number of blocks in the table divided by number of hash keys  

CLUSTER_TYPE  

VARCHAR2(5)  

 

Type of cluster: B-Tree index or hash  

FUNCTION  

VARCHAR2(15)  

 

If a hash cluster, the hash function  

HASHKEYS  

NUMBER  

 

If a hash cluster, the number of hash keys (hash buckets)  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the cluster  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the cluster is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the cluster is to be cached in the buffer cache  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object  

SINGLE_TABLE  

VARCHAR2(5)  

 

Y if the cluster is single table; N if not  

ALL_CLUSTER_HASH_EXPRESSIONS

This view list hash functions for all accessible clusters.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner of the cluster  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

The name of the cluster  

HASH_EXPRESSION  

LONG  

 

Text of hash function of cluster  

ALL_COL_COMMENTS

This view lists comments on columns of accessible tables and views.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the column  

COMMENTS  

VARCHAR2(4000)  

 

Comment on the column  

ALL_COL_PRIVS

This view lists grants on columns for which the user or PUBLIC is the grantee.

Column   Datatype   NULL   Description  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

TABLE_SCHEMA  

VARCHAR2(30)  

NOT NULL  

Schema of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the column  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the column  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privileges were granted with ADMIN OPTION; otherwise NO  

ALL_COL_PRIVS_MADE

This view lists grants on columns for which the user is owner or grantor.

Column   Datatype   NULL   Description  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the column  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the column  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privilege was granted with ADMIN OPTION; otherwise NO  

ALL_COL_PRIVS_RECD

This view lists grants on columns for which the user or PUBLIC is the grantee.

Column   Datatype   NULL   Description  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

OWNER  

VARCHAR2(30)  

NOT NUL  

Username of the owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the column  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privilege was granted with ADMIN OPTION; otherwise NO  

ALL_COLL_TYPES

This view displays the named collection types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

COLL_TYPE  

VARCHAR2(30)  

NOT NULL  

Collection type  

UPPER_BOUND  

NUMBER  

 

Maximum size of the VARRAY type  

ELEM_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the element  

ELEM_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the element  

ELEM_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the element  

LENGTH  

NUMBER  

 

Length of the CHAR element or maximum length of the VARCHAR or VARCHAR2 element  

PRECISION  

NUMBER  

 

Decimal precision of the NUMBER or DECIMAL element or binary precision of the FLOAT element  

SCALE  

NUMBER  

 

Scale of the NUMBER or DECIMAL element  

CHARACTER_SET
_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS NCHAR_CS  

ELEM_STORAGE  

VARCHAR2(7)  

 

Storage optimization specification for VARRAY of numeric elements  

NULLS_STORED  

VARCHAR2(3)  

 

Is null information stored with each VARRAY element?  

ALL_CONS_COLUMNS

This view contains information about accessible columns in constraint definitions.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the constraint definition  

CONSTRAINT_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with the constraint definition  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with table with constraint definition  

COLUMN_NAME  

VARCHAR2(4000)  

 

Name associated with column or attribute of the object type column specified in the constraint definition  

POSITION  

NUMBER  

 

Original position of column or attribute in definition  

Note: If you create a constraint on a user-defined REF column, the system creates the constraint on the exploded columns that make up the REF column and hence column names of the constraint will be the column names of the exploded columns. These names will however have as their prefix the REF column's name.

ALL_CONSTRAINTS

This view lists constraint definitions on accessible tables.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the constraint definition  

CONSTRAINT_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with the constraint definition  

CONSTRAINT_TYPE  

VARCHAR2(1)  

 

Type of constraint definition: C (check constraint on a table), P (primary key), U (unique key), R (referential integrity), or V (with check option, on a view), or O (with read only, on a view)  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with table with constraint definition  

SEARCH_CONDITION  

LONG  

 

Text of search condition for table check  

R_OWNER  

VARCHAR2(30)  

 

Owner of table used in referential constraint  

R_CONSTRAINT
_NAME  

VARCHAR2(30)  

 

Name of unique constraint definition for referenced table  

DELETE_RULE  

VARCHAR2(9)  

 

Delete rule for a referential constraint: CASCADE / NO ACTION  

STATUS  

VARCHAR2(8)  

 

Enforcement status of constraint: ENABLED or DISABLED  

DEFERRABLE  

VARCHAR2(14)  

 

Indicates whether the constraint is deferrable  

DEFERRED  

VARCHAR2(9)  

 

Indicates whether the constraint was initially deferred  

VALIDATED  

VARCHAR2(13)  

 

Indicates whether all data obeys the constraint: VALIDATED, NOT VALIDATED  

GENERATED  

VARCHAR2(14)  

 

Indicates whether the name system is generated  

BAD  

VARCHAR2(3)  

 

Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.  

RELY  

VARCHAR2(4)  

 

If set, this flag will be used in the optimizer  

LAST_CHANGE  

DATE  

 

Indicates when the constraint was last enabled or disabled  

ALL_CONTEXT

This view lists active context information.

Column   Datatype   NULL   Description  

NAMESPACE  

VARCHAR2(30)  

NOT NULL  

The name of the context namespace  

SCHEMA  

VARCHAR2(30)  

NOT NULL  

The schema name of the designated package which can set attributes using this namespace  

PACKAGE  

VARCHAR2(30)  

NOT NULL  

The package name of the designated package which can set attributes using this namespace  

ALL_DB_LINKS

This view lists database links accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the database link  

DB_LINK  

VARCHAR2(12)  

NOT NULL  

Name of the database link  

USERNAME  

VARCHAR2(30)  

 

Name of user when logging in  

HOST  

VARCHAR2(200)  

 

Net8 string for connect  

CREATED  

DATE  

NOT NULL  

Creation time of the database link  

ALL_DEF_AUDIT_OPTS

This view contains default object-auditing options that will be applied when objects are created.

Column   Datatype   NULL   Description  

ALT  

VARCHAR2(3)  

 

Auditing ALTER WHENEVER SUCCESSFUL / UNSUCCESSFUL  

AUD  

VARCHAR2(3)  

 

Auditing AUDIT WHENEVER SUCCESSFUL / UNSUCCESSFUL  

COM  

VARCHAR2(3)  

 

Auditing COMMENT WHENEVER SUCCESSFUL / UNSUCCESSFUL  

DEL  

VARCHAR2(3)  

 

Auditing DELETE WHENEVER SUCCESSFUL / UNSUCCESSFUL  

GRA  

VARCHAR2(3)  

 

Auditing GRANT WHENEVER SUCCESSFUL / UNSUCCESSFUL  

IND  

VARCHAR2(3)  

 

Auditing INDEX WHENEVER SUCCESSFUL / UNSUCCESSFUL  

INS  

VARCHAR2(3)  

 

Auditing INSERT WHENEVER SUCCESSFUL / UNSUCCESSFUL  

LOC  

VARCHAR2(3)  

 

Auditing LOCK WHENEVER SUCCESSFUL / UNSUCCESSFUL  

REN  

VARCHAR2(3)  

 

Auditing RENAME WHENEVER SUCCESSFUL / UNSUCCESSFUL  

SEL  

VARCHAR2(3)  

 

Auditing SELECT WHENEVER SUCCESSFUL / UNSUCCESSFUL  

UPD  

VARCHAR2(3)  

 

Auditing UPDATE WHENEVER SUCCESSFUL / UNSUCCESSFUL  

REF  

VARCHAR2(3)  

 

Auditing REFERENCES WHENEVER SUCCESSFUL / UNSUCCESSFU
(not used)  

EXE  

VARCHAR2(3)  

 

Auditing EXECUTE WHENEVER SUCCESSFUL / UNSUCCESSFUL  

ALL_DEPENDENCIES

This view lists dependencies between objects accessible to the user. Dependencies on views created without any database links are also available.

.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of object  

TYPE  

VARCHAR2(12)  

 

Type of object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY, TRIGGER  

REFERENCED
_OWNER  

VARCHAR2(30)  

 

Owner of the parent object  

REFERENCED
_NAME  

VARCHAR2(64)  

 

Type of parent object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY, TRIGGER  

REFERENCED_TYPE  

VARCHAR2(12)  

 

Type of referenced object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY, TRIGGER  

REFERENCED_
LINK_NAME  

VARCHAR2(128)  

 

Name of the link to the parent object (if remote)  

DEPENDENCY_TYPE  

VARCHAR2(4)  

 

Two values: REF when the dependency is a REF dependency; HARD otherwise  

ALL_DIMENSIONS

This view represents dimension objects.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

INVALID  

VARCHAR2(1)  

 

'Y' if this dimension is in an invalid state, else 'N'  

REVISION  

NUMBER  

 

Dimension revision level  

ALL_DIM_ATTRIBUTES

This view represents the relationship between a dimension level and a functionally dependent column. The table that the level columns are in must match the table of the dependent column.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Dependent column name  

INFERRED  

CHAR(1)  

 

Whether inferred  

ALL_DIM_CHILD_OF

This view represents a 1:n hierarchical relationship between a pair of levels in a dimension.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Hierarchy name  

POSITION  

NUMBER  

NOT NULL  

Hierarchical position within this hierarchy, position 1 being the most detailed  

CHILD_LEVEL_NAME  

VARCHAR2(30)  

 

Child side of 1:n relationship  

JOIN_KEY_ID  

VARCHAR2(40)  

 

If non-null, then the child joins to the parent  

PARENT_LEVEL_NAME  

VARCHAR2(30)  

 

Parent side of 1:n relationship  

ALL_DIM_HIERARCHIES

This view represents a dimension hierarchy.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Hierarchy name  

ALL_DIM_JOIN_KEY

This view represents a join between two dimension tables. The join is always specified between a parent dimension level column and a child column.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

DIM_KEY_ID  

NUMBER  

NOT NULL  

Unique within a dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

KEY_POSITION  

NUMBER  

NOT NULL  

Ordinal position of the key column within the level  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Name of the key column  

CHILD_JOIN_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of the join column  

ALL_DIM_LEVELS

This view represents a dimension level. All columns of a dimension level must come from the same relation.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Unique within a dimension  

NUM_COLUMNS  

NUMBER  

 

Number of columns in the level definition  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the detail object that the keys of this level come from  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table that the keys of this level come from  

ALL_DIM_LEVEL_KEY

This view represents a column of a dimension level. The position of a column within a level is specified by KEY_POSITION.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

KEY_POSITION  

NUMBER  

NOT NULL  

Ordinal position of the key column within the level  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the key column  

ALL_DIRECTORIES

This view contains the description of all directories accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the directory (always SYS)  

DIRECTORY_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the directory  

DIRECTORY_PATH  

VARCHAR2(4000)  

 

Operating system pathname for the directory  

ALL_ERRORS

This view lists current errors on all objects accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of object  

TYPE  

VARCHAR2(12)  

 

Type of object: VIEW, PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY  

SEQUENCE  

NUMBER  

NOT NULL  

Sequence number, for ordering  

LINE  

NUMBER  

NOT NULL  

Line number at which this error occurs  

POSITION  

NUMBER  

NOT NULL  

Position in the line at which this error occurs  

TEXT  

VARCHAR2(4000)  

NOT NULL  

Text of the error  

ALL_IND_COLUMNS

This view lists columns of the indexes on accessible tables.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

Index owner  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table or cluster owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table or cluster name  

COLUMN_NAME  

VARCHAR2(4000)  

 

Column name or attribute of object type column  

COLUMN_POSITION  

NUMBER  

NOT NULL  

Position of column or attribute within index  

COLUMN_LENGTH  

NUMBER  

NOT NULL  

Indexed length of the column  

DESCEND  

VARCHAR2(4)  

 

Y/N, Y if this column is sorted in descending order  

Note: If you create an index on a user-defined REF column, the system creates the index on the exploded columns that make up the REF column and hence column names of the index will be the column names of the exploded columns. These names will however have as their prefix the REF column's name.

ALL_IND_EXPRESSIONS

This view lists functional index expressions on accessible tables.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

Index owner  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table or cluster owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table or cluster name  

COLUMN_EXPRESSION  

LONG  

 

Functional index expression defining the column  

COLUMN_POSITION  

NUMBER  

NOT NULL  

Position of column or attribute within index  

ALL_IND_PARTITIONS

This view describes, for each index partition, the partition level partitioning information, the storage parameters for the partition, and various partition statistics determined by ANALYZE that the current user can access.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

 

Index owner  

INDEX_NAME  

VARCHAR2(30)  

 

Index name  

COMPOSITE  

VARCHAR2(3)  

 

'YES' if the partition belongs to a Local index on a table partitioned using Composite method; 'NO' otherwise  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_COUNT  

NUMBER  

 

If this is a Local index on a table partitioned using a Composite method, the number of subpartitions in the partition  

HIGH_VALUE  

LONG  

 

Partition bound value expression  

HIGH_VALUE_LENGTH  

NUMBER  

 

Length of partition bound value expression  

PARTITION_POSITION  

NUMBER  

 

Position of the partition within the index  

STATUS  

VARCHAR2(8)  

 

Indicates whether index partition is usable or not  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the partition  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of process freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of partition  

BLEVEL  

NUMBER  

 

B-Tree level  

LEAF_BLOCKS  

NUMBER  

 

Number of leaf blocks in the index partition  

DISTINCT_KEYS  

NUMBER  

 

Number of distinct keys in the index partition  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of leaf blocks per key  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of data blocks per key  

CLUSTERING_FACTOR  

NUMBER  

 

Measurement of the amount of (dis)order of the table this index partition is for  

NUM_ROWS  

NUMBER  

 

Number of rows returned by the ANALYZE command  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this partition  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this partition was analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The actual buffer pool for the partition  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

PCT_DIRECT_ACCESS  

NUMBER  

 

If index on IOT, then this is percentage of rows with Valid guess  

ALL_IND_SUBPARTITIONS

This view describes, for each index subpartition that the current user owns, the partition level partitioning information, the storage parameters for the subpartition, and various partition statistics determined by ANALYZE.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

The owner of the index  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Subpartition name  

SUBPARTITION_POSITION  

NUMBER  

NOT NULL  

Position of a subpartition within a partition  

STATUS  

VARCHAR2(8)  

 

Indicates whether index partition is usable or not  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the partition  

PCT_FREE  

NUMBER  

NOT NULL  

Minimum percentage of free space in a block  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of process freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of partition  

BLEVEL  

NUMBER  

 

B-Tree level  

LEAF_BLOCKS  

NUMBER  

 

Number of leaf blocks in the index partition  

DISTINCT_KEYS  

NUMBER  

 

Number of distinct keys in the index partition  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of leaf blocks per key  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of data blocks per key  

CLUSTERING_FACTOR  

NUMBER  

 

Measurement of the amount of (dis)order of the table this index partition is for  

NUM_ROWS  

NUMBER  

 

Number of rows in this index partition  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this partition  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this partition was analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The buffer pool for the partition  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

ALL_INDEXES

This view contains descriptions of indexes on tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE. This view supports parallel partitioned index scans.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the index  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the index  

INDEX_TYPE  

VARCHAR2(12)  

 

Type of index  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the indexed object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indexed object  

TABLE_TYPE  

CHAR(5)  

 

Type of the indexed object  

UNIQUENESS  

VARCHAR2(9)  

 

Uniqueness status of the index: UNIQUE or NONUNIQUE  

COMPRESSION  

VARCHAR2(11)  

 

Enabled or disabled  

PREFIX_LENGTH  

NUMBER  

 

The number of columns in the prefix of the key used for compression  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the index  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

PCT_THRESHOLD  

NUMBER  

 

Threshold percentage of block space allowed per index entry  

INCLUDE_COLUMN  

NUMBER  

 

User column-id for last column to be included in index organized table top index  

FREELISTS  

NUMBER  

 

Number of process freelists allocated to this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated to this segment  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

LOGGING  

VARCHAR(2(3)  

 

Logging information  

BLEVEL  

NUMBER  

 

B-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.  

LEAF_BLOCKS  

NUMBER  

 

Number of leaf blocks in the index  

DISTINCT_KEYS  

NUMBER  

 

Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of leaf blocks in which each distinct value in the index appears. This statistic is rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of data blocks in the table that are pointed to by a distinct value in the index. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns. This statistic is rounded to the nearest integer.  

CLUSTERING_FACTOR  

NUMBER  

 

Statistic that represents the amount of order of the rows in the table based on the values of the index. If its value is near the number of blocks, then the table is very well ordered. In such a case, the index entries in a single leaf block tend to point to rows in the same data blocks. If its value is near the number of rows, then the table is very randomly ordered. In such a case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.  

STATUS  

VARCHAR2(8)  

 

State of the index: DIRECT LOAD, VALID or INPROGS (a DDL operation on the domain index is in progress).  

DOMIDX_STATUS  

VARCHAR  

 

Reflects the status of the domain index. A NULL value means that the specified index is not a domain index. A Value of VALID means that the index is a domain index and the index does not have any errors. If the value of this column is IDXTYP_INVLD it means that the indextype corresponding to this domain index is invalid.  

DOMIDX_OPSTATUS  

VARCHAR  

 

Reflects the status of an operation that was performed on the domain index. A value of NULL indicates that the specified index is not a domain index. A value of VALID specifies that the index does not have any errors. A value of FAILED indicates that the operation that was performed on the domain index failed with an error.  

FUNCIDX_STATUS  

VARCHAR  

 

A value of NULL indicates a non function-based index. ENABLED indicates the function-based index is enabled. DISABLED indicates the function-based index is disabled.  

NUM_ROWS  

NUMBER  

 

Number of rows in this index  

SAMPLE_SIZE  

NUMBER  

 

Size of the sample used to analyze this index  

LAST_ANALYZED  

DATE  

 

Timestamp for when this index was last analyzed  

DEGREE  

VARCHAR2(40)  

 

Number of threads per instance for scanning the index, NULL if PARTITIONED=NO.  

INSTANCES  

VARCHAR2(40)  

 

Number of instances across which the indexes to be scanned. NULL if PARTITIONED=NO.  

PARTITIONED  

VARCHAR2(3)  

 

Indicates whether this index is partitioned. Set to 'YES' if it is partitioned  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it places in this object itself?  

GENERATED  

VARCHAR2(1)  

 

Was the name of this index system generated?  

SECONDARY  

VARCHAR2(1)  

 

Is the index object created as part of icreate for domain indexes?  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

The duration  

PCT_DIRECT_ACCESS  

NUMBER  

 

If index on IOT, then this is percentage of rows with Valid guess  

ALL_INDEXTYPES

This view lists all the indextypes.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the indextype  

INDEXTYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indextype  

IMPLEMENTATION
_SCHEMA  

VARCHAR2(30)  

NOT NULL  

Name of the schema for indextype implementation  

IMPLEMENTATION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of indextype implementation  

IMPLEMENTATION_VERSION  

NUMBER  

NOT NULL  

Version of indextype implementation  

NUMBER_OF_OPERATORS  

NUMBER  

 

Number of operators associated with the indextype  

ALL_INDEXTYPE_OPERATORS

This view lists all the operators supported by indextypes.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the indextype  

INDEXTYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indextype  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the operator for which the indextype is defined  

BINDING#  

NUMBER  

NOT NULL  

Binding# associated with the operator  

ALL_JOBS

This view lists all jobs in the database.

Column   Datatype   NULL   Description  

JOB  

NUMBER  

NOT NULL  

Identifier of job. Neither import/export nor repeated executions change it  

LOG_USER  

VARCHAR2(30)  

NOT NULL  

USER who was logged in when the job was submitted  

PRIV_USER  

VARCHAR2(30)  

NOT NULL  

USER whose default privileges apply to this job  

SCHEMA_USER  

VARCHAR2(30)  

NOT NULL  

Default schema used to parse the job

For example, if the SCHEMA_USER is SCOTT and you submit the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP  

LAST_DATE  

DATE  

 

Date that this job last successfully executed  

LAST_SEC  

VARCHAR2(8)  

 

Same as LAST_DATE. This is when the last successful execution started  

THIS_DATE  

DATE  

 

Date that this job started executing (usually NULL if not executing)  

THIS_SEC  

VARCHAR2(8)  

 

Same as THIS_DATE  

NEXT_DATE  

DATE  

NOT NULL  

Date that this job will next be executed  

NEXT_SEC  

VARCHAR2(8))  

 

Same as NEXT_DATE. The job becomes due for execution at this time  

TOTAL_TIME  

NUMBER  

 

Total wall clock time spent by the system on this job, in seconds  

BROKEN  

VARCHAR2(1)  

 

If Y, no attempt is made to run this job  

INTERVAL  

VARCHAR2(200)  

NOT NULL  

A date function, evaluated at the start of execution, becomes next NEXT_DATE  

FAILURES  

NUMBER  

 

How many times has this job started and failed since its last success?  

WHAT  

VARCHAR2(4000)  

 

Body of the anonymous PL/SQL block that this job executes  

NLS_ENV  

VARCHAR2(4000)  

 

ALTER SESSION parameters describing the NLS environment of the job  

MISC_ENV  

RAW(32)  

 

Other session parameters that apply to this job  

INSTANCE  

NUMBER  

 

Indicates which instance can execute or is executing the job; the default is 0  

ALL_LABELS

This is a Trusted Oracle Server view that lists system labels. For more information, see your Trusted Oracle documentation.

ALL_LIBRARIES

This new data view lists all the libraries that a user can access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the library  

LIBRARY_NAME  

VARCHAR2(30)  

NOT NULL  

Library name  

FILE_SPEC  

VARCHAR2(2000)  

 

Operating system file specification associated with the library  

DYNAMIC  

VARCHAR2(1)  

 

Is the library dynamically loadable? (YES or NO)  

STATUS  

VARCHAR2(7)  

 

Status of the library  

ALL_LOBS

This view displays the LOBs contained in tables accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table containing the LOB  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table containing the LOB  

COLUMN_NAME  

VARCHAR2(4000)  

 

Name of the LOB column or attribute  

SEGMENT_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the LOB segment  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the LOB index  

CHUNK  

NUMBER  

 

Size of the LOB chunk as a unit of allocation/manipulation in bytes  

PCTVERSION  

NUMBER  

NOT NULL  

Maximum percentage of the LOB space used for versioning  

CACHE  

VARCHAR2(3)  

 

Indicates whether the LOB is accessed through the buffer cache  

LOGGING  

VARCHAR2(3)  

 

Indicates whether the changes to the LOB are logged  

IN_ROW  

VARCHAR2(3)  

 

Are some of the LOBs stored with the base row?  

ALL_LOB_PARTITIONS

This view displays the LOBs contained in tables accessible to the user.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

 

Table owner  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of a LOB column  

LOB_NAME  

VARCHAR2(30)  

 

Name of a partitioned LOB item  

PARTITION_NAME  

VARCHAR2(30)  

 

Name of a table partition  

LOB_PARTITION_NAME  

VARCHAR2(30)  

 

Name of LOB data partition  

LOB_INDPART_NAME  

VARCHAR2(30)  

 

Name of corresponding LOB index partition  

PARTITION_POSITION  

NUMBER  

 

Position of the LOB data partition within the lOB item  

COMPOSITE  

VARCHAR2(3)  

 

Is it a Composite partition (one of YES, NO)?  

CHUNK  

NUMBER  

 

CHUNK attribute of a LOB data partition  

PCTVERSION  

NUMBER  

 

PCTVERSION attribute of a LOB data partition  

CACHE  

VARCHAR2(3)  

 

CACHE attribute of a LOB data partition  

IN_ROW  

VARCHAR2(3)  

 

IN_ROW attribute of a LOB data partition  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the LOB data partition  

INITIAL_EXTENT  

VARCHAR2(40)  

 

Size of the initial extent in bytes for a LOB data partition  

NEXT_EXTENT  

VARCHAR2(40)  

 

Size of secondary extents in bytes for a LOB data partition  

MIN_EXTENTS  

VARCHAR2(40)  

 

Minimum number of extents allowed in the segment of a LOB data partition  

MAX_EXTENTS  

VARCHAR2(40)  

 

Maximum number of extents allowed in the segment of a LOB data partition  

PCT_INCREASE  

VARCHAR2(40)  

 

Percentage increase in extent size for a LOB data partition  

FREELISTS  

VARCHAR2(40)  

 

Number of process freelists allocated in the segment of a LOB data partition  

FREELIST_GROUPS  

VARCHAR2(40)  

 

Number of freelist groups allocated in the segment of a LOB data partition  

LOGGING  

VARCHAR2(7)  

 

The logging attribute of a LOB data partition  

BUFFER_POOL  

VARCHAR2(7)  

 

Buffer pool of a LOB data partition  

ALL_LOB_SUBPARTITIONS

This view displays partition-level attributes of LOB data subpartitions.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

 

Table owner  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of a LOB column  

LOB_NAME  

VARCHAR2(30)  

 

Name of a partitioned LOB item  

LOB_PARTITION_NAME  

VARCHAR2(30)  

 

Name of LOB data partition to which this LOB data subpartition belongs  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Name of a table subpartition to which this LOB subpartition corresponds  

LOB_INDSUBPART
_NAME  

VARCHAR2(30)  

 

Name of corresponding LOB index subpartition  

SUBPARTITION
_POSITION  

NUMBER  

 

Position of the LOB data partition within the LOB item  

CHUNK  

NUMBER  

 

CHUNK attribute of a LOB data partition  

PCTVERSION  

NUMBER  

 

PCTVERSION attribute of a LOB data partition  

CACHE  

VARCHAR2(3)  

 

CACHE attribute of a LOB data partition  

IN_ROW  

VARCHAR2(3)  

 

IN_ROW attribute of a LOB data partition  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the LOB data partition  

INITIAL_EXTENT  

VARCHAR2(40)  

 

Size of the initial extent in bytes for a LOB data partition  

NEXT_EXTENT  

VARCHAR2(40)  

 

Size of secondary extents in bytes for a LOB data partition  

MIN_EXTENTS  

VARCHAR2(40)  

 

Minimum number of extents allowed in the segment of a LOB data partition  

MAX_EXTENTS  

VARCHAR2(40)  

 

Maximum number of extents allowed in the segment of a LOB data partition  

PCT_INCREASE  

VARCHAR2(40)  

 

Percentage increase in extent size for a LOB data partition  

FREELISTS  

VARCHAR2(40)  

 

Number of process freelists allocated in the segment of a LOB data partition  

FREELIST_GROUPS  

VARCHAR2(40)  

 

Number of freelist groups allocated in the segment of a LOB data partition  

LOGGING  

VARCHAR2(7)  

 

The logging attribute of a LOB data partition  

BUFFER_POOL  

VARCHAR2(7)  

 

Buffer pool of a LOB data partition  

ALL_METHOD_PARAMS

This view is a description view of method parameters of types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

METHOD_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the method  

METHOD_NO  

NUMBER  

NOT NULL  

Method number for distinguishing overloaded method (not to be used as ID number)  

PARAM_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the parameter  

PARAM_NO  

NUMBER  

NOT NULL  

Parameter number or position  

PARAM_MODE  

VARCHAR2(6)  

 

Mode of the parameter  

PARAM_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the parameter  

PARAM_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the parameter  

PARAM_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the parameter  

CHARACTER_SET_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS or NCHAR_CS  

ALL_METHOD_RESULTS

This view is a description view of method results of types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

METHOD_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the method  

METHOD_NO  

NUMBER  

NOT NULL  

Method number for distinguishing overloaded method (not to be used as ID number)  

RESULT_TYPE_MOD  

VARCHAR2(7)  

NOT NULL  

Type modifier of the result  

RESULT_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the result  

RESULT_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the result  

CHARACTER_SET_NAME  

VARCHAR2(44  

 

The name of the character set: CHAR_CS or NCHAR_CS  

ALL_MVIEW_AGGREGATES

This view represents the grouping functions (aggregated measures) that appear in the SELECT list of an aggregated materialized view.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view index  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

POSITION_IN_SELECT  

NUMBER  

NOT NULL  

Position of this measure within the SELECT list  

CONTAINER_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of this column in the container table  

AGG_FUNCTION  

VARCHAR2(8)  

 

Aggregation function  

DISTINCTFLAG  

VARCHAR2(1)  

 

Set to Y if DISTINCT aggregation  

MEASURE  

LONG  

 

Contains the SQL text of the measure, excluding the aggregation function. Equal to * for COUNT(*)  

ALL_MVIEW_ANALYSIS

This view represents the materialized views that potentially support query rewrite and that have additional information that is available for analysis by applications. This view excludes any materialized view that references a remote table or that includes a reference to a non-static value such as SYSDATE or USER. It also excludes any materialized view that was created as a snapshot prior to Oracle8.1 and that was never altered to enable query rewrite.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

MVIEW_TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view table object  

CONTAINER_NAME  

VARCHAR2(30)  

 

Name of the snapshot that the materialized view data is contained in  

LAST_REFRESH_SCN  

NUMBER  

 

The System Change Number  

LAST_REFRESH_DATE  

DATE  

 

SYSDATE of the last refresh  

REFRESH_METHOD  

VARCHAR2(8)  

 

FORCE, FAST, COMPLETE, or NONE  

SUMMARY  

VARCHAR2(1)  

 

Y if this materialized view includes a GROUP BY clause or aggregation, else N  

FULLREFRESHTIM  

NUMBER  

 

Approximate refresh time, in seconds (defined only when summary = Y for full refresh  

INCREFRESHTIM  

NUMBER  

 

Approximate refresh time, in seconds (defined only when summary = Y for full refresh  

CONTAINS_VIEWS  

VARCHAR2(1)  

 

Y if this materialized view contains a view in its definition, else N  

UNUSABLE  

VARCHAR2(1)  

 

Y if this materialized view is in an unusable state (inconsistent data), else N. A materialized view can be in an unusable state if a system failure occurs during a full refresh  

RESTRICTED_SYNTAX  

VARCHAR2(1)  

 

Y if this materialized view uses restricted materialized view syntax, else N  

INC_REFRESHABLE  

VARCHAR2(1)  

 

Y if this materialized view can be incrementally refreshed, else N  

KNOWN_STALE  

VARCHAR2(1)  

 

Y if the data contained in the materialized view is known to be inconsistent with the detail data, else N  

INVALID  

VARCHAR2(1)  

 

Y if this materialized view is in an invalid state (inconsistent metadata), else N  

QUERY_LEN  

NUMBER  

NOT NULL  

The length of the query field  

QUERY  

LONG  

NOT NULL  

SELECT expression of the materialized view definition  

REVISION  

NUMBER  

 

The materialized view metadata revision level  

ALL_MVIEW_DETAIL_RELATIONS

This view represents the named detail relations that are either in the FROM list of a materialized view, or that are indirectly referenced through views in the FROM list. Inline views in the materialized view definition are not represented in this table.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Detail object owner  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Detail object name, eg, the name of a table or view  

DETAILOBJ_TYPE  

VARCHAR2(9)  

 

TABLE, VIEW, MATERIALIZED VIEW, or SNAPSHOT  

DETAILOBJ_ALIAS  

VARCHAR2(30)  

 

Implicit or explicit alias for detail relation  

ALL_MVIEW_JOINS

This view represents a join between two columns in the WHERE clause of a materialized view.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

POSITION_IN_SELECT  

NUMBER  

NOT NULL  

Position of this key within the SELECT list  

CONTAINER_COLUMN  

VARCHAR2(30)  

NOT NULL  

The name of the column in the container table  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Detail object owner  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Detail object name, eg, the name of a table or view  

DETAILOBJ_TYPE  

VARCHAR2(5)  

 

VIEW or TABLE  

DETAILOBJ_ALIAS  

VARCHAR2(30)  

 

Implicit or explicit alias for detail relation  

DETAILOBJ_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of the detail relation column  

ALL_MVIEW_KEYS

This view represents the named detail relations that are either in the FROM list of a materialized view, or that are indirectly referenced through views in the FROM list. Inline views in the materialized view definition are not represented in this table.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

POSITION_IN_SELECT  

NUMBER  

NOT NULL  

Position of this key within the SELECT list  

CONTAINER_COLUMN  

VARCHAR2(30)  

NOT NULL  

The name of the column in the container table  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Detail object owner  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Detail object name, eg, the name of a table or view  

DETAILOBJ_TYPE  

VARCHAR2(5)  

 

VIEW or TABLE  

DETAILOBJ_ALIAS  

VARCHAR2(30)  

 

Implicit or explicit alias for detail relation  

DETAILOBJ_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of the detail relation column  

ALL_NESTED_TABLES

This view describes the nested tables in tables accessible to the user.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the nested table  

TABLE_NAME  

VARCHAR2(30)  

 

Name of the nested table  

TABLE_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of which the nested table was created  

TABLE_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the nested table  

PARENT_TABLE
_NAME  

VARCHAR2(30)  

 

Name of the parent table containing the nested table  

PARENT_TABLE
_COLUMN  

VARCHAR2(4000)  

 

Column name of the parent table that corresponds to the nested table  

STORAGE_SPEC  

VARCHAR2(30)  

 

Indication of default or user-specified storage for the varray  

RETURN_TYPE  

VARCHAR2(20)  

 

Return type of the varray column locator or value  

ALL_OBJECT_TABLES

This view contains descriptions of the object tables accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the table  

CLUSTER_NAME  

VARCHAR2(30)  

 

Name of the cluster, if any, to which the table belongs  

IOT_NAME  

VARCHAR2(30)  

 

Name of the index organized table, if any, to which the overflow entry belongs  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

 

Minimum percentage of used space in a block  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute  

BACKED_UP  

VARCHAR2(1)  

 

Has table been backed up since last modification?  

NUM_ROWS  

NUMBER  

 

The number of rows in the table  

BLOCKS  

NUMBER  

 

The number of used blocks in the table  

EMPTY_BLOCKS  

NUMBER  

 

The number of empty (never used) blocks in the table  

AVG_SPACE  

NUMBER  

 

The average available free space in the table  

CHAIN_CNT  

NUMBER  

 

The number of chained rows in the table  

AVG_ROW_LEN  

NUMBER  

 

The average row length, including row overhead  

AVG_SPACE_FREELIST
_BLOCKS  

NUMBER  

 

The average freespace of all blocks on a freelist  

NUM_FREELIST_BLOCKS  

NUMBER  

 

The number of blocks on the freelist  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the table  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the table is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the table is to be cached in the buffer cache  

TABLE_LOCK  

VARCHAR2(8)  

 

Whether table locking is enabled or disabled  

SAMPLE_SIZE  

NUMBER  

 

The sample size used in analyzing this table  

LAST_ANALYZED  

DATE  

 

The date of the most recent time this table was analyzed  

PARTITIONED  

VARCHAR2(3)  

 

Is this table partitioned? YES or NO  

IOT_TYPE  

VARCHAR2(12)  

 

If index organized table, then IOT_TYPE is IOT or IOT_OVERFLOW else NULL  

OBJECT_ID_TYPE  

VARCHAR2(16)  

 

If user-defined OID, then USER-DEFINED, else if system generated OID, then SYSTEM GENERATED  

TABLE_TYPE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type of the table if the table is a typed table  

TABLE_TYPE  

VARCHAR2(30)  

NOT NULL  

Type of the table if the table is a typed table  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it place in this object itself?  

SECONDARY  

VARCHAR2(1)  

 

Is this table object created as part of icreate for domain indexes?  

NESTED  

VARCHAR2(3)  

 

Is the table a nested table?  

BUFFER_POOL  

VARCHAR2(7)  

 

The default buffer pool to be used for table blocks  

ROW_MOVEMENT  

VARCHAR2(8)  

 

Whether partitioned row movement is enabled or disabled  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

If temporary table, then duration is sys$session or sys$transaction else NULL  

SKIP_CORRUPT  

VARCHAR2(8)  

 

Whether skip corrupt blocks is enabled or disabled  

ALL_OBJECTS

This view lists objects accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the object  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

SUBOBJECT_NAME  

VARCHAR2(30)  

 

Name of the sub-object (for example, partition)  

OBJECT_ID  

NUMBER  

NOT NULL  

Object number of the object  

DATA_OBJECT_ID  

NUMBER  

 

Object number of the segment which contains the object  

OBJECT_TYPE  

VARCHAR2(15)  

 

Type of the object (eg, TABLE, INDEX)  

CREATED  

DATE  

NOT NULL  

Timestamp for the creation of the object  

LAST_DDL_TIME  

DATE  

NOT NULL  

Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes)  

TIMESTAMP  

VARCHAR2(20)  

 

Timestamp for the creation of the object (character data)  

STATUS  

VARCHAR2(7)  

 

Status of the object: VALID, INVALID, or N/A  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it placed in this object itself?  

GENERATED  

VARCHAR2(1)  

 

Was the name of this object system generated?  

SECONDARY  

VARCHAR2(1)  

 

Is this a secondary object created as part of create for domain indexes?  

ALL_OPANCILLARY

This view lists ancillary information for operators.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of ancillary operator  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of ancillary operator  

BINDING#  

NUMBER  

NOT NULL  

Binding number of ancillary operator  

PRIMOP_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of primary operator  

PRIMOP_NAME  

VARCHAR2(30)  

NOT NULL  

Name of primary operator  

PRIMOP_BIND#  

NUMBER  

NOT NULL  

Binding number of primary operator  

ALL_OPARGUMENTS

This view lists argument information for operator bindings.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the operator argument  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the operator argument  

BINDING#  

NUMBER  

NOT NULL  

Binding number of the operator argument  

POSITION  

NUMBER  

NOT NULL  

Position of the operator argument  

ARGUMENT_TYPE  

VARCHAR2(61)  

 

Datatype of the operator argument  

ALL_OPBINDINGS

This view lists operator bindings.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the operator  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the operator  

BINDING#  

NUMBER  

NOT NULL  

Binding number of the operator  

FUNCTION_NAME  

VARCHAR2(92)  

 

Name of the binding function or method as specified by the user  

RETURN_SCHEMA  

VARCHAR2(30)  

 

Name of the schema of the return type - not null only for ADTs  

RETURN_TYPE  

VARCHAR2(30)  

 

Name of the return type  

IMPLEMENTATION_TYPE_SCHEMA  

VARCHAR2(30)  

 

Schema of the implementation type of the indextype  

IMPLEMENTATION_TYPE  

VARCHAR2(30)  

 

Implementation type of the indextype  

ALL_OPERATORS

This view lists operators.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the operator  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the operator  

NUMBER_OF_BINDS  

NUMBER  

NOT NULL  

Number of bindings associated with the operator  

ALL_OUTLINE_HINTS

This view lists the set of hints which make up the outlines.

Column   Datatype   NULL   Description  

NAME  

VARCHAR2(30)  

 

Name of the outline  

NODE  

NUMBER  

 

I.D. of the query or subquery to which the hint applies. The top-level query is labelled 1. Subqueries are assigned sequentially numbered labels, starting with 2  

JOIN_POS  

NUMBER  

 

Position of the table in the join order. The JOIN_POS column is 0 for all hints except the access method hints. The access method hints identify a table to which the hint and the join position apply  

HINT  

VARCHAR2(512)  

 

Text of the hint  

ALL_OUTLINES

This view lists information about outlines.

Column   Datatype   NULL   Description  

NAME  

VARCHAR2(30)  

 

The user-specified or generated name of the stored outline. The name must be of a form that can be expressed in SQL  

CATEGORY  

VARCHAR2(30)  

 

A user-defined name used to group outlines into collections  

USED  

VARCHAR2(9)  

 

Flag indicating whether the outline has ever been used  

TIMESTAMP  

DATE  

 

Timestamp at which the outline was created  

VERSION  

VARCHAR2(64)  

 

Oracle Version that created the outline  

SQL_TEXT  

LONG  

 

SQL text of the query-including any hints that were a part of the original statement. If bind variables are included, the variable names are stored as SQL text, not the values that are assigned to the variables  

ALL_PART_COL_STATISTICS

This view contains column statistics and histogram information for table partitions that the current user can access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner name  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

PARTITION_NAME  

VARCHAR2(30)  

 

Table partition name  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Column name  

NUM_DISTINCT  

NUMBER  

 

Number of distinct values in the column  

LOW_VALUE  

RAW(32)  

 

Low value in the column  

HIGH_VALUE  

RAW(32)  

 

High value in the column  

DENSITY  

NUMBER  

 

Density of the column  

NUM_NULLS  

NUMBER  

 

Number of nulls in the column  

NUM_BUCKETS  

NUMBER  

 

Number of buckets in histogram for the column  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this column  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this column was analyzed  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

AVG_COL_LEN  

NUMBER  

 

The average length of the column, in bytes  

ALL_PART_HISTOGRAMS

This view contains the histogram data (end-points per histogram) for histograms on table partitions that the current user can access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner name  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

PARTITION_NAME  

VARCHAR2(30)  

 

Table partition name  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name  

BUCKET_NUMBER  

NUMBER  

 

Bucket number  

ENDPOINT_VALUE  

NUMBER  

 

Normalized endpoint values for this bucket  

ALL_PART_INDEXES

This view lists the object level partitioning information for all partitioned indexes that the current user can access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of this partitioned index  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of this partitioned index  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of this partitioned table  

PARTITIONING_TYPE  

VARCHAR2(7)  

 

In addition to RANGE, may specify HASH  

SUBPARTITIONING_TYPE  

VARCHAR2(7)  

 

May specify HASH or NONE  

PARTITION_COUNT  

NUMBER  

NOT NULL  

Number of partitions in this index  

DEF_SUBPARTITION
_COUNT  

NUMBER  

NOT NULL  

If the index is partitioned using a Composite method, the default number of subpartitions, if any  

PARTITIONING_KEY
_COUNT  

NUMBER  

NOT NULL  

Number of columns in the partitioning key  

SUBPARTITIONING_KEY
_COUNT  

NUMBER  

NOT NULL  

If the index is partitioned using a Composite method with Hash subpartitioning, this column will contain a number of columns in the subpartitioning key  

LOCALITY  

VARCHAR2(6)  

 

Indicates whether this partitioned index is LOCAL or GLOBAL  

ALIGNMENT  

VARCHAR2(12)  

 

Indicates whether this partitioned index is PREFIXED or NON-PREFIXED  

DEF_TABLESPACE
_NAME  

VARCHAR2(30)  

 

Default TABLESPACE, for LOCAL index, for ADD/SPLIT TABLE partition  

DEF_PCT_FREE  

NUMBER  

NOT NULL  

Default PCTFREE, for LOCAL index, for ADD TABLE partition  

DEF_INI_TRANS  

NUMBER  

NOT NULL  

Default INITRANS, for LOCAL index, for ADD TABLE partition  

DEF_MAX_TRANS  

NUMBER  

NOT NULL  

Default MAXTRANS, for LOCAL index, for ADD TABLE partition  

DEF_INITIAL_EXTENT  

VARCHAR2(40)  

NOT NULL  

Default INITIAL, for LOCAL index, for ADD TABLE partition, 'DEFAULT' if attribute was not specified (in Oracle blocks)  

DEF_NEXT_EXTENT  

VARCHAR2(40)  

NOT NULL  

Default NEXT, for LOCAL index, for ADD TABLE partition, 'DEFAULT' if attribute was not specified (in Oracle blocks)  

DEF_MIN_EXTENTS  

VARCHAR2(40)  

NOT NULL  

Default MINEXTENTS, for LOCAL index, for ADD TABLE partition, 'DEFAULT' if attribute was not specified  

DEF_MAX_EXTENTS  

VARCHAR2(40)  

NOT NULL  

Default MAXEXTENTS, for LOCAL index, for ADD TABLE partition, 'DEFAULT' if attribute was not specified  

DEF_PCT_INCREASE  

VARCHAR2(40)  

NOT NULL  

Default PCTINCREASE, for LOCAL index, for ADD TABLE partition, 'DEFAULT' if attribute was not specified  

DEF_FREELISTS  

NUMBER  

NOT NULL  

Default FREELISTS, for LOCAL index, for ADD TABLE partition  

DEF_FREELIST_GROUPS  

NUMBER  

NOT NULL  

Default FREELIST GROUPS, for LOCAL index, for ADD TABLE partition  

DEF_LOGGING  

VARCHAR2(7)  

 

Default LOGGING, for LOCAL index, for ADD TABLE PARTITION  

DEF_BUFFER_POOL  

VARCHAR2(7)  

 

Default buffer pool for LOCAL index, for ADD TABLE PARTITION  

ALL_PART_KEY_COLUMNS

This view describes the partitioning key columns for partitioned objects that the current user access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Partitioned table or index owner  

NAME  

VARCHAR2(30)  

 

Partitioned table or index name  

OBJECT_TYPE  

VARCHAR2(11)  

 

Object type ('TABLE' or 'INDEX)  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name  

COLUMN_POSITION  

NUMBER  

 

Position of the column within the partitioning key  

ALL_PART_LOBS

This view describes table-level information for partitioned LOBs, including default attributes for LOB data partitions.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of a partitioned table containing LOB(s)  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of a partitioned table containing LOB(s)  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of a LOB column  

LOB_NAME  

VARCHAR2(30)  

NOT NULL  

Name of a partitioned LOB  

LOB_INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of a partitioned LOB index  

DEF_CHUNK  

NUMBER  

NOT NULL  

Default CHUNK for a LOB data partition, used for ADD PARTITION  

DEF_PCTVERSION  

NUMBER  

NOT NULL  

Default PCTVERSION for a LOB data partition, used for ADD PARTITION  

DEF_CACHE  

VARCHAR2(3)  

 

Default CACHE for a LOB data partition, used for ADD PARTITION  

DEF_IN_ROW  

VARCHAR2(3)  

 

Default "IN ROW" for a LOB data partition, used for ADD PARTITION  

DEF_TABLESPACE_NAME  

VARCHAR2(30)  

 

Default TABLESPACE for a LOB data partition, used for ADD PARTITION  

DEF_INITIAL_EXTENT  

VARCHAR2(40)  

 

Default INITIAL for a LOB data partition, used for ADD PARTITION  

DEF_NEXT_EXTENT  

VARCHAR2(40)  

 

Default NEXT for a LOB data partition, used for ADD PARTITION  

DEF_MIN_EXTENTS  

VARCHAR2(40)  

 

Default MINEXTENT for a LOB data partition, used for ADD PARTITION  

DEF_MAX_EXTENTS  

VARCHAR2(40)  

 

Default MAXEXTENTS for a LOB data partition, used for ADD PARTITION  

DEF_PCT_INCREASE  

VARCHAR2(40)  

 

Default PCTINCREASE for a LOB data partition, used for ADD PARTITION  

DEF_FREELISTS  

VARCHAR2(40)  

 

Default FREELISTS for a LOB data partition, used for ADD PARTITION  

DEF_FREELIST_GROUPS  

VARCHAR2(40)  

 

Default FREELIST GROUPS for a LOB data partition, used for ADD PARTITION  

DEF_LOGGING  

VARCHAR2(7)  

 

Default LOGGING attribute for a LOB data partition, used for ADD PARTITION  

DEF_BUFFER_POOL  

VARCHAR2(7)  

 

Default BUFFER POOL for a LOB data partition, used for ADD PARTITION  

ALL_PART_TABLES

This view lists the object level partitioning information for partitioned tables the current user access.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of this partitioned table  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of this partitioned table  

PARTITIONING_TYPE  

VARCHAR2(7)  

 

In addition to RANGE, may specify HASH  

SUBPARTITIONING_TYPE  

VARCHAR2(7)  

 

May specify HASH or NONE  

PARTITION_COUNT  

NUMBER  

NOT NULL  

Number of partitions in this table  

DEF_SUBPARTITION
_COUNT  

NUMBER  

NOT NULL  

If the index is partitioned using a Composite method, the default number of subpartitions, if any  

PARTITIONING_KEY_COUNT  

NUMBER  

NOT NULL  

Number of columns in the partitioning key  

SUBPARTITIONING_KEY
_COUNT  

NUMBER  

NOT NULL  

If the index is partitioned using a Composite method with Hash subpartitioning, this column will contain a number of columns in the subpartitioning key  

DEF_TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Default TABLESPACE, used for add partition  

DEF_PCT_FREE  

NUMBER  

NOT NULL  

Default PCTFREE, used for add partition  

DEF_PCT_USED  

NUMBER  

NOT NULL  

Default PCTUSED, used for add partition  

DEF_INI_TRANS  

NUMBER  

NOT NULL  

Default INITRANS, used for add partition  

DEF_MAX_TRANS  

NUMBER  

NOT NULL  

Default MAXTRANS, used for add partition  

DEF_INITIAL_EXTENT  

VARCHAR2(40)  

 

Default INITIAL, used for add partition, 'DEFAULT' if attribute was not specified (in Oracle blocks)  

DEF_NEXT_EXTENT  

VARCHAR2(40)  

 

Default NEXT, used for add partition, 'DEFAULT' if attribute was not specified (in Oracle blocks)  

DEF_MIN_EXTENTS  

VARCHAR2(40)  

 

Default MINEXTENTS, used for add partition, 'DEFAULT' if attribute was not specified  

DEF_MAX_EXTENTS  

VARCHAR2(40)  

 

Default MAXEXTENTS, used for add partition,
'DEFAULT' if attribute was not specified  

DEF_PCT_INCREASE  

VARCHAR2(40)  

 

Default PCTINCREASE, used for add partition,
'DEFAULT' if attribute was not specified  

DEF_FREELISTS  

NUMBER  

NOT NULL  

Default FREELISTS, used for add partition  

DEF_FREELIST_GROUPS  

NUMBER  

NOT NULL  

Default FREELIST GROUPS, used for add partition  

DEF_LOGGING  

VARCHAR2(7)  

 

Default LOGGING attribute, used for add partition  

DEF_BUFFER_POOL  

VARCHAR2(7)  

 

Default buffer pool for the given object, used for add partition  

ALL_PARTIAL_DROP_TABS

This view describes tables which have partially dropped tables.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table  

ALL_POLICIES

This view is a list of all policies on all tables and views that the user has access to.

Column   Datatype   NULL   Description  

OBJECT_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table or view  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table or view  

POLICY_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the policy  

PF_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the policy function  

PACKAGE  

VARCHAR2(30)  

 

Name of the package containing the policy function  

FUNCTION  

VARCHAR2(30)  

NOT NULL  

Name of the policy function  

SEL  

VARCHAR2(3)  

 

If YES, policy is applied to query on the object  

INS  

VARCHAR2(3)  

 

If YES, policy is applied to insert on the object  

UPD  

VARCHAR2(3)  

 

If YES, policy is applied to update on the object  

DEL  

VARCHAR2(3)  

 

If YES, policy is applied to delete on the object  

CHK_OPTION  

VARCHAR2(3)  

 

Is check option enforced for this policy?  

ENABLE  

VARCHAR2(3)  

 

Is this policy enabled?  

ALL_QUEUES

This view displays information of the queues the user has enqueue or dequeue privilege on. If the user has any of the Advanced Queuing system privileges, like MANAGE ANY QUEUE, ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE, then all queues in the database will be displayed.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the queue  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the queue  

QUEUE_TABLE  

VARCHAR2(30)  

NOT NULL  

Name of the table the queue data resides in  

QID  

NUMBER  

NOT NULL  

Object number of the queue  

QUEUE_TYPE  

VARCHAR2(15)  

 

Type of the queue  

MAX_RETRIES  

NUMBER  

 

Maximum number of retries allowed when dequeuing from the queue  

RETRY_DELAY  

NUMBER  

 

Time interval between retries  

ENQUEUE_ENABLED  

VARCHAR2(7)  

 

Queue is enabled for enqueue  

DEQUEUE_ENABLED  

VARCHAR2(7)  

 

Queue is enabled for dequeue  

RETENTION  

VARCHAR2(40)  

 

Time interval processed messages retained in the queue  

USER_COMMENT  

VARCHAR2(50)  

 

User specified comment  

ALL_REFRESH

This view lists all the refresh groups that the user can access.

Column   Datatype   NULL   Description  

ROWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner of the refresh group  

RNAME  

VARCHAR2(30)  

NOT NULL  

Name of the refresh group  

REFGROUP  

NUMBER  

 

Internal identifier of refresh group  

IMPLICIT_DESTROY  

VARCHAR2(1)  

 

Y or N; if Y, then destroy the refresh group when its last item is subtracted  

PUSH_DEFERRED_RPC  

VARCHAR2(1)  

 

Y or N; if Y then push changes from snapshot to master before refresh  

REFRESH_AFTER
_ERRORS  

VARCHAR2(1)  

 

If Y, proceed with refresh despite error when pushing deferred RPCs  

ROLLBACK_SEG  

VARCHAR2(30)  

 

Name of the rollback segment to use while refreshing  

JOB  

NUMBER  

 

Identifier of job used to refresh the group automatically  

NEXT_DATE  

DATE  

 

Date that this job will next be refreshed automatically, if not broken  

INTERVAL  

VARCHAR2(200)  

 

A date function used to compute the next NEXT_DATE  

BROKEN  

VARCHAR2(1)  

 

Y or N; Y means the job is broken and will never be run  

PURGE_OPTION  

NUMBER(38)  

 

The method for purging the transaction queue after each push. 1=quick purge option; 2=precise purge option  

PARALLELISM  

NUMBER(38)  

 

The level of parallelism for transaction propagation  

HEAP_SIZE  

NUMBER(38)  

 

The size of the heap  

ALL_REFRESH_CHILDREN

This view lists all the objects in refresh groups, where the user can access the group.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object in the refresh group  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object in the refresh group  

TYPE  

VARCHAR2(30)  

 

Type of the object in the refresh group  

ROWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner of the refresh group  

RNAME  

VARCHAR2(30)  

NOT NULL  

Name of the refresh group  

REFGROUP  

NUMBER  

 

Internal identifier of refresh group  

IMPLICIT_DESTROY  

VARCHAR2(1)  

 

Y or N; if Y, then destroy the refresh group when its last item is subtracted  

PUSH_DEFERRED_RPC  

VARCHAR2(1)  

 

Y or N; if Y then push changes from snapshot to master before refresh  

REFRESH_AFTER
_ERRORS  

VARCHAR2(1)  

 

If Y, proceed with refresh despite error when pushing deferred RPCs  

ROLLBACK_SEG  

VARCHAR2(30)  

 

Name of the rollback segment to use while refreshing  

JOB  

NUMBER  

 

Identifier of job used to refresh the group automatically  

NEXT_DATE  

DATE  

 

Date that this job will next be refreshed automatically, if not broken  

INTERVAL  

VARCHAR2(200)  

 

A date function used to compute the next NEXT_DATE  

BROKEN  

VARCHAR2(1)  

 

Y or N; Y means the job is broken and will never be run  

PURGE_OPTION  

NUMBER(38)  

 

The method for purging the transaction queue after each push. 1=quick purge option; 2=precise purge option  

PARALLELISM  

NUMBER(38)  

 

The level of parallelism for transaction propagation  

HEAP_SIZE  

NUMBER(38)  

 

The size of the heap.  

ALL_REFRESH_DEPENDENCIES

This view lists the names of the dependent detail or container tables of all the summaries or snapshots in the current schema.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name, unique within this schema  

PARENT_OBJECT_TYPE  

CHAR(7)  

 

'SNAPSHOT' or 'SUMMARY'  

OLDEST_REFRESH_SCN  

NUMBER  

 

The minimum SCN of any summary or snapshot that has TABLE_NAME as a detail table  

OLDEST_REFRESH_DATE  

DATE  

 

SYSDATE when last refreshed  

ALL_REFS

This view describes the REF columns and REF attributes in object type columns accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table  

COLUMN_NAME  

VARCHAR2(4000)  

 

Name of the REF column or attribute. If it is not a top-level attribute, the value of COLUMN_NAME should be a path name starting with the column name  

WITH_ROWID  

VARCHAR2(3)  

 

Is the REF value stored with ROWID (YES or NO)?  

IS_SCOPED  

VARCHAR2(3)  

 

Is the REF column scoped (YES or NO)?  

SCOPE_TABLE_OWNER  

VARCHAR2(30)  

 

Name of the owner of the scope table, if it exists and is accessible by the user  

SCOPE_TABLE_NAME  

VARCHAR2(30)  

 

Name of the scope table, if it exists and is accessible by the user  

OBJECT_ID_TYPE  

VARCHAR2(16)  

 

If user-defined OID, then USER-DEFINED, else if system generated OID, then SYSTEM GENERATED  

ALL_REGISTERED_SNAPSHOTS

This view lists all registered snapshots.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the snapshot  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the snapshot  

SNAPSHOT_SITE  

VARCHAR2(128)  

NOT NULL  

Global name of the snapshot site.  

CAN_USE_LOG  

VARCHAR2(3)  

 

YES if this snapshot can use a snapshot log, NO if this snapshot is too complex to use a log  

UPDATABLE  

VARCHAR2(3)  

 

Specifies whether the snapshot is updatable. YES if it is, NO if it is not. If set to NO, the snapshot is read only  

REFRESH_METHOD  

VARCHAR2(11)  

 

Whether the snapshot uses rowids or primary key for fast refresh  

SNAPSHOT_ID  

NUMBER(38)  

 

Identifier for the snapshot used by the master for fast refresh  

VERSION  

VARCHAR2(17)  

 

Version of snapshot  

QUERY_TXT  

LONG  

 

Original query of which this snapshot is an instantiation  

ALL_REPCATLOG

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPCOLUMN

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPCOLUMN_GROUP

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPCONFLICT

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPDDL

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPGENERATED

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPGENOBJECTS

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPGROUP

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPGROUPED_COLUMN

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPKEY_COLUMNS

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPOBJECT

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPPARAMETER_COLUMN

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPPRIORITY

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPPRIORITY_GROUP

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPPROP

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPRESOLUTION

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPRESOL_STATS_CONTROL

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPRESOLUTION_METHOD

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPRESOLUTION_STATISTICS

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_REPSITES

This view is used with Advanced Replication. For more information, see Oracle8i Replication.

ALL_SEQUENCES

This view lists descriptions of sequences accessible to the user.

Column   Datatype   NULL   Description  

SEQUENCE_OWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner of the sequence  

SEQUENCE_NAME  

VARCHAR2(30)  

NOT NULL  

Sequence name  

MIN_VALUE  

NUMBER  

 

Minimum value of the sequence  

MAX_VALUE  

NUMBER  

 

Maximum value of the sequence  

INCREMENT_BY  

NUMBER  

NOT NULL  

Value by which sequence is incremented  

CYCLE_FLAG  

VARCHAR2(1)  

 

Does sequence wrap around on reaching limit  

ORDER_FLAG  

VARCHAR2(1)  

 

Are sequence numbers generated in order  

CACHE_SIZE  

NUMBER  

NOT NULL  

Number of sequence numbers to cache  

LAST_NUMBER  

NUMBER  

NOT NULL  

Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used  

ALL_SNAPSHOT_LOGS

This view lists all snapshot logs.

Column   Datatype   NULL   Description  

LOG_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the log  

MASTER  

VARCHAR2(30)  

NOT NULL  

Name of the master table whose changes are logged  

LOG_TABLE  

VARCHAR2(30)  

NOT NULL  

Name of the table where the changes to the master table are recorded  

LOG_TRIGGER  

VARCHAR2(30)  

 

Obsolete with the release of Oracle8i Server. Set to NULL. Formerly, this parameter was an after-row trigger on the master which inserted rows into the log  

ROWIDS  

VARCHAR2(3)  

 

If YES, records ROWID information  

PRIMARY_KEY  

VARCHAR2(3)  

 

If YES, records primary key information  

FILTER_COLUMNS  

VARCHAR2(3)  

 

If YES, snapshot log records filter columns  

CURRENT_SNAPSHOTS  

DATE  

 

One date per snapshot; the date the snapshot of the master was last refreshed  

SNAPSHOT_ID  

NUMBER(38)  

 

Unique identifier of the snapshot  

ALL_SNAPSHOT_REFRESH_TIMES

This view lists snapshot refresh times.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the snapshot  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the snapshot view  

MASTER_OWNER  

VARCHAR2(30)  

 

Owner of the master table  

MASTER  

VARCHAR2(30)  

 

Name of the master table  

LAST_REFRESH  

DATE  

 

The last refresh  

ALL_SNAPSHOTS

This view lists all snapshots accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the snapshot  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the view used by users and applications for viewing the snapshot  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table the snapshot is stored in. This table may have additional columns  

MASTER_VIEW  

VARCHAR2(30)  

 

View of the master table, owned by the snapshot owner, used for refreshes. This is obsolete in Oracle8i and is set to NULL  

MASTER_OWNER  

VARCHAR2(30)  

 

Owner of the master table  

MASTER  

VARCHAR2(30)  

 

Name of the master table of which this snapshot is a copy  

MASTER_LINK  

VARCHAR2(128)  

 

Database link name to the master site  

CAN_USE_LOG  

VARCHAR2(3)  

 

YES if this snapshot can use a snapshot log, NO if this snapshot is too complex to use a log  

UPDATABLE  

VARCHAR2(3)  

 

Specifies whether the snapshot is updatable. YES if it is, NO if it is not. If set to YES, the snapshot is read only  

REFRESH_METHOD  

VARCHAR2(11)  

 

Values used to drive a refresh of the snapshot
(PRIMARY KEY/ROWID/COMPLEX). If PRIMARY KEY, then the snapshot uses primary keys to drive a fast refresh. If ROWID, then it uses RowIDs to drive a fast refresh. If COMPLEX, then fast refreshes are not allowed and the snapshot can only perform complete refreshes  

LAST_REFRESH  

DATE  

 

Date and time at the master site of the last refresh  

ERROR  

NUMBER  

 

The number of failed automatic refreshes since last successful refresh  

FR_OPERATIONS  

VARCHAR2(10)  

 

Status of generated fast refresh operations: (REGENERATE, VALID)  

CR_OPERATIONS  

VARCHAR2(10)  

 

Status of generated complete refresh operations: (REGENERATE, VALID)  

TYPE  

VARCHAR2(8)  

 

Type of refresh for all automatic refreshes: COMPLETE, FAST, FORCE  

NEXT  

VARCHAR2(200)  

 

Date function used to compute next refresh dates  

START_WITH  

DATE  

 

Date function used to compute next refresh dates  

REFRESH_GROUP  

NUMBER  

 

All snapshots in a given refresh group get refreshed in the same transaction  

UPDATE_TRIG  

VARCHAR2(30)  

 

Obsolete. It is NULL for Oracle8i snapshots. Formerly, the name of the trigger that fills the UPDATE_LOG  

UPDATE_LOG  

VARCHAR2(30)  

 

The table that logs changes made to an updatable snapshots  

QUERY  

LONG  

 

Original query of which this snapshot is an instantiation  

MASTER_ROLLBACK_SEG  

VARCHAR2(30)  

 

Rollback segment to use at the master site  

STATUS  

VARCHAR2(7)  

 

The status of the contents of the snapshot  

REFRESH_MODE  

VARCHAR2(8)  

 

This indicates how and when the snapshot will be refreshed  

PREBUILT  

VARCHAR2(3)  

 

If YES, this snapshot uses a prebuilt table as the base table  

ALL_SOURCE

This view lists the text source of all stored objects accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

TYPE  

VARCHAR2(12)  

 

Type of object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY  

LINE  

NUMBER  

NOT NULL  

Line number of this line of source  

TEXT  

VARCHAR2(4000)  

 

Text source of the stored object  

ALL_SUBPART_COL_STATISTICS

This view lists column statistics and histogram information for table subpartitions.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner name  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Table subpartition name  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Column name  

NUM_DISTINCT  

NUMBER  

 

Number of distinct values in the column  

LOW_VALUE  

RAW(32)  

 

Low value in the column  

HIGH_VALUE  

RAW(32)  

 

High value in the column  

DENSITY  

NUMBER  

 

Density of the column  

NUM_NULLS  

NUMBER  

 

Number of nulls in the column  

NUM_BUCKETS  

NUMBER  

 

Number of buckets in histogram for the column  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this column  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this column was analyzed  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying subpartitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

AVG_COL_LEN  

NUMBER  

 

The average length of the column, in bytes  

ALL_SUBPART_HISTOGRAMS

This view lists the actual histogram data (end-points per histogram) for histograms on table subpartitions.

SUB

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner name  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Table subpartition name  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name  

BUCKET_NUMBER  

NUMBER  

 

Bucket number  

ENDPOINT_VALUE  

NUMBER  

 

Normalized endpoint values for this bucket  

ALL_SUBPART_KEY_COLUMNS

This view lists subpartitioning key columns for tables (and Local indexes on tables) partitioned using the Composite Range/Hash method.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

The partitioned table or index owner  

NAME  

VARCHAR2(30)  

 

The partitioned table or index name  

OBJECT_TYPE  

VARCHAR2(11)  

 

The object type (TABLE or INDEX)  

COLUMN_NAME  

VARCHAR2(30)  

 

The column name  

COLUMN_POSITION  

NUMBER  

 

The position of the column within the subpartitioning key  

ALL_SUMDELTA

This view lists direct path load entries accessible to the user.

Column   Datatype   NULL   Description  

TABLEOBJ#  

NUMBER  

NOT NULL  

Object number of the table  

PARTITIONOBJ#  

NUMBER  

NOT NULL  

Object number of table partitions (if the table is partitioned)  

DMLOPERATION  

VARCHAR2(1)  

 

Type of DML operation applied to the table  

SCN  

NUMBER  

NOT NULL  

SCN when the bulk DML occurred  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp of log entry  

LOWROWID  

ROWID  

NOT NULL  

The start ROWID in the loaded rowid range  

HIGHROWID  

ROWID  

NOT NULL  

The end ROWID in the loaded rowid range  

ALL_SUMMARIES

This view represents a summary object.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the synonym  

SUMMARY_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the synonym  

CONTAINER_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the container object  

CONTAINER_NAME  

VARCHAR2(30)  

 

Name of the table or snapshot that the summary data is contained in  

CONTAINER_TYPE  

VARCHAR2(9)  

 

'TABLE' or 'SNAPSHOT'  

LAST_REFRESH_SCN  

NUMBER  

 

System Change Number  

LAST_REFRESH_DATE  

DATE  

 

SYSDATE of last refresh  

REFRESH_METHOD  

VARCHAR2(11)  

 

'ANY', 'INCREMENTAL', 'FULL', or 'NONE'  

FULLREFRESHTIM  

NUMBER  

 

The time that it took to fully refresh the summary  

INCREFRESHTIM  

NUMBER  

 

The time that it took to incrementally refresh the summary  

CONTAINS_VIEWS  

VARCHAR2(1)  

 

This summary contains views in the FROM clause  

UNUSABLE  

VARCHAR2(1)  

 

This summary is unusable, the build was deferred  

RESTRICTED_SYNTAX  

VARCHAR2(1)  

 

Y if this summary uses restricted syntax, else 'N'  

INC_REFRESHABLE  

VARCHAR2(1)  

 

Y if this summary can be incrementally refreshed, else 'N  

KNOWN_STALE  

VARCHAR2(1)  

 

Y if the data contained in this summary is known to be inconsistent with the detail data, and 'N' otherwise  

QUERY_LEN  

NUMBER  

NOT NULL  

Length of the query field  

QUERY  

LONG  

NOT NULL  

SELECT expression of the summary definition  

ALL_SYNONYMS

This view lists all synonyms accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the synonym  

SYNONYM_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the synonym  

TABLE_OWNER  

VARCHAR2(30)  

 

Owner of the object referenced by the synonym  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object referenced by the synonym  

DB_LINK  

VARCHAR2(128)  

 

Name of the database link referenced, if any  

ALL_TAB_COL_STATISTICS

This view contains column statistics and histogram information which is in the USER_TAB_COLUMNS view. For more information, see "USER_TAB_COLUMNS".

Column   Datatype   NULL   Description  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Column name  

NUM_DISTINCT  

NUMBER  

 

Number of distinct values in the column  

LOW_VALUE  

RAW(32)  

 

Low value in the column  

HIGH_VALUE  

RAW(32)  

 

High value in the column  

DENSITY  

NUMBER  

 

Density of the column  

NUM_NULLS  

NUMBER  

 

Number of nulls in the column  

NUM_BUCKETS  

NUMBER  

 

Number of buckets in histogram for the column  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this column was analyzed  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this column  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

AVG_COL_LEN  

NUMBER  

 

The average length of the column, in bytes  

ALL_TAB_COLUMNS

This view lists the columns of all tables, views, and clusters accessible to the user. To gather statistics for this view, use the SQL command ANALYZE.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table, view or cluster  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table, view, or cluster name  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Column name  

DATA_TYPE  

VARCHAR2(30)  

 

Datatype of the column  

DATA_TYPE_MOD  

VARCHAR2(3)  

 

Datatype modifier of the column  

DATA_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the datatype of the column  

DATA_LENGTH  

NUMBER  

NOT NULL  

Length of the column in bytes  

DATA_PRECISION  

NUMBER  

 

Decimal precision for NUMBER datatype; binary precision for FLOAT datatype, NULL for all other datatypes  

DATA_SCALE  

NUMBER  

 

Digits to right of decimal point in a number  

NULLABLE  

VARCHAR2(1)  

 

Specifies whether a 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  

NOT NULL  

Sequence number of the column as created  

DEFAULT_LENGTH  

NUMBER  

 

Length of default value for the column  

DATA_DEFAULT  

LONG  

 

Default value for the column  

NUM_DISTINCT  

NUMBER  

 

These columns remain for backward
compatibility with Oracle7. This
information is now in the
{TAB|PART}_COL_STATISTICS
views. This view now picks up these values
from HIST_HEAD$ rather than COL$.  

LOW_VALUE  

RAW(32)  

 

HIGH_VALUE  

RAW(32)  

 

DENSITY  

NUMBER  

 

NUM_NULLS  

NUMBER  

 

The number of nulls in the column  

NUM_BUCKETS  

NUMBER  

 

The number of buckets in histogram for the column  

LAST_ANALYZED  

DATE  

 

The date of the most recent time this column was analyzed  

SAMPLE_SIZE  

 

 

The sample size used in analyzing this column  

CHARACTER_SET
_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS or NCHAR_CS  

CHAR_COL_DECL
_LENGTH  

NUMBER  

 

The length  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

AVG_COL_LEN  

NUMBER  

 

The average length of the column, in bytes  

ALL_TAB_COMMENTS

This view lists comments on tables and views accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

TABLE_TYPE  

VARCHAR2(11)  

 

Type of the object  

COMMENTS  

VARCHAR2(4000)  

 

Comment on the object  

ALL_TAB_HISTOGRAMS

This view lists histograms on tables and views accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of table  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

COLUMN_NAME  

VARCHAR2(4000)  

 

Column name or attribute of the object type column  

BUCKET_NUMBER  

NUMBER  

 

Bucket number  

ENDPOINT_VALUE  

NUMBER  

 

Normalized endpoint values for this bucket  

ALL_TAB_PARTITIONS

This view describes, for each table partition, the partition level partitioning information, the storage parameters for the partition, and various partition statistics determined by ANALYZE that the current user can access.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

COMPOSITE  

VARCHAR2(3)  

 

'YES' if the partition belongs to a Local index on a table partitioned using Composite method; 'NO' otherwise  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_COUNT  

NUMBER  

 

If this is a Local index on a table partitioned using a Composite method, the number of subpartitions in the partition  

HIGH_VALUE  

LONG  

 

Partition bound value expression  

HIGH_VALUE_LENGTH  

NUMBER  

NOT NULL  

Length of partition bound value expression  

PARTITION_POSITION  

NUMBER  

NOT NULL  

Position of the partition within the table  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the partition  

PCT_FREE  

NUMBER  

NOT NULL  

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

NOT NULL  

Minimum percentage of used space in a block  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of partition  

NUM_ROWS  

NUMBER  

 

Number of rows in the partition  

BLOCKS  

NUMBER  

 

Number of used blocks in the partition  

EMPTY_BLOCKS  

NUMBER  

 

Number of empty (never used) blocks in the partition  

AVG_SPACE  

NUMBER  

 

Average available free space in the partition  

CHAIN_CNT  

NUMBER  

 

Number of chained rows in the partition  

AVG_ROW_LEN  

NUMBER  

 

Average row length, including row overhead  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this partition  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this partition was analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The actual buffer pool for this partition  

GLOBAL_STATS  

VARCHAR2(3)  

 

Global statistics  

USER_STATS  

VARCHAR2(3)  

 

User statistics  

ALL_TAB_PRIVS

This view lists the grants on objects for which the user or PUBLIC is the grantee.

Column   Datatype   NULL   Description  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access is granted  

TABLE_SCHEMA  

VARCHAR2(30)  

NOT NULL  

Schema of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the object  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privilege was granted with ADMIN OPTION; otherwise NO  

ALL_TAB_PRIVS_MADE

This view lists the user's grants and grants on the user's objects.

Column   Datatype   NULL   Description  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the object  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privilege was granted with ADMIN OPTION; otherwise NO  

ALL_TAB_PRIVS_RECD

This view lists grants on objects for which the user or PUBLIC is the grantee.

Column   Datatype   NULL   Description  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Privilege on the object  

GRANTABLE  

VARCHAR2(3)  

 

YES if the privilege was granted with ADMIN OPTION; otherwise NO  

ALL_TAB_SUBPARTITIONS

This view describes, for each table subpartition, its name, name of the table and partition to which it belongs, and its storage attributes. Note that statistics will not be collected on a per-subpartition basis.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Subpartition name  

SUBPARTITION_POSITION  

NUMBER  

NOT NULL  

Position of a subpartition within a partition  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the subpartition  

PCT_FREE  

NUMBER  

NOT NULL  

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

NOT NULL  

Minimum percentage of used space in a block  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of freelist groups allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of subpartition  

NUM_ROWS  

NUMBER  

 

The number of rows  

BLOCKS  

NUMBER  

 

The number of blocks  

EMPTY_BLOCKS  

NUMBER  

 

The number of empty blocks  

AVG_SPACE  

NUMBER  

 

The average space  

CHAIN_CNT  

NUMBER  

 

The chain count  

AVG_ROW_LEN  

NUMBER  

 

The average row length  

SAMPLE_SIZE  

NUMBER  

 

The sample size  

LAST_ANALYZED  

DATE  

 

The date when last analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The actual buffer pool for this subpartition  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

ALL_TABLES

This view contains descriptions of relational tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the table  

TABLE_NAME  

VARCHAR2(30)  

 

Name of the table  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the table; NULL for partitioned tables  

CLUSTER_NAME  

VARCHAR2(30)  

 

Name of the cluster, if any, to which the table belongs  

IOT_NAME  

VARCHAR2(30)  

 

Name of the index organized table, if any, to which the overflow entry belongs  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block; NULL for partitioned tables  

PCT_USED  

NUMBER  

 

Minimum percentage of used space in a block; NULL for partitioned tables  

INI_TRANS  

NUMBER  

 

Initial number of transactions; NULL for partitioned tables  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions; NULL for partitioned tables  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes; NULL for partitioned tables  

NEXT_EXTENT  

NUMBER  

 

Size of the secondary extension bytes; NULL for partitioned tables  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment; NULL for partitioned tables  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment; NULL for partitioned tables  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size; NULL for partitioned tables  

FREELISTS  

NUMBER  

 

Number of process freelists allocated to this segment; NULL for partitioned tables  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated to this segment; NULL for partitioned tables  

LOGGING  

VARCHAR2(3)  

 

Logging attribute; NULL for partitioned tables  

BACKED_UP  

VARCHAR2(1)  

 

Has table been backed up since last change  

NUM_ROWS  

NUMBER  

 

Number of rows in the table  

BLOCKS  

NUMBER  

 

Number of used data blocks in the table  

EMPTY_BLOCKS  

NUMBER  

 

Number of empty (never used) data blocks in the table  

AVG_SPACE  

NUMBER  

 

Average amount of free space, in bytes, in a data block allocated to the table  

CHAIN_CNT  

NUMBER  

 

Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID  

AVG_ROW_LEN  

NUMBER  

 

Average length of a row in the table in bytes  

AVG_SPACE_FREELIST
_BLOCKS  

NUMBER  

 

The average freespace of all blocks on a freelist  

NUM_FREELIST_BLOCKS  

NUMBER  

 

The number of blocks on the freelist  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the table  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the table is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the table is to be cached in the buffer cache  

TABLE_LOCK  

VARCHAR2(8)  

 

Whether table locking is enabled or disabled  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this table  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this table was analyzed  

PARTITIONED  

VARCHAR2(3)  

 

Indicates whether this table is partitioned. Set to YES if it is partitioned  

IOT_TYPE  

VARCHAR2(12)  

 

If this is an index organized table, then IOT_TYPE is IOT or IOT_OVERFLOW. If this is not an index organized table, then IOT_TYPE is NULL  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it place in this object itself?  

SECONDARY  

VARCHAR2(1)  

 

Is the object created as part of icreate for domain indexes?  

NESTED  

VARCHAR2(3)  

 

Is the table a nested table?  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object; NULL for partitioned tables  

ROW_MOVEMENT  

VARCHAR2(8)  

 

Whether partitioned row movement is enabled or disabled  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

If temporary table, then duration is sys$session or sys$transaction else NULL  

SKIP_CORRUPT  

VARCHAR2(8)  

 

Whether skip corrupt blocks is enabled or disabled  

ALL_TRIGGERS

This view lists trigger information for triggers owned by the user, triggers on tables owned by the user, or all triggers if the user has the CREATE ANY TRIGGER privilege.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the trigger  

TRIGGER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the trigger  

TRIGGER_TYPE  

VARCHAR2(16)  

 

When the trigger fires: BEFORE STATEMENT, BEFORE EACH ROW, BEFORE EVENT, AFTER STATEMENT, AFTER EACH ROW, and AFTER EVENT  

TRIGGERING_EVENT  

VARCHAR2(75)  

 

Events that fire the trigger: INSERT, UPDATE, DELETE, STARTUP, SHUTDOWN, ERROR, LOGON, LOGOFF, CREATE, ALTER, DROP  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table on which the trigger is defined  

BASE_OBJECT_TYPE  

VARCHAR2(16)  

 

The base object on which the trigger is defined: TABLE, VIEW, SCHEMA, or DATABASE  

TABLE_NAME  

VARCHAR2(30)  

 

If the base object type of the trigger is SCHEMA or DATABASE, then this column is NULL; if the base object type of the trigger is TABLE or VIEW, this columns indicates the table/view name on which the trigger is defined  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of the nested table column (if nested table trigger), else null  

REFERENCING_NAMES  

VARCHAR2(87)  

 

Names used for referencing OLD and NEW column values from within the trigger  

WHEN_CLAUSE  

VARCHAR2(4000)  

 

Must evaluate to TRUE for TRIGGER_BODY to execute  

STATUS  

VARCHAR2(8)  

 

Whether the trigger is enabled: ENABLED or DISABLED  

DESCRIPTION  

VARCHAR2(4000)  

 

Trigger description. Useful for re-creating a trigger creation statement.  

ACTION_TYPE  

VARCHAR2(11)  

 

The action type of the trigger body: CALL or PL/SQL  

TRIGGER_BODY  

LONG  

 

Statement(s) executed by the trigger when it fires  

ALL_TRIGGER_COLS

This view displays the usage of columns in triggers owned by user, on tables owned by user, or on all triggers if the user has the CREATE ANY TRIGGER privilege.

Column   Datatype   NULL   Description  

TRIGGER_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the triggers  

TRIGGER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the trigger  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table on which the trigger is defined  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table on which the trigger is defined  

COLUMN_NAME  

VARCHAR2(4000)  

 

Name of the column used in the trigger  

COLUMN_LIST  

VARCHAR2(3)  

 

Column specified in UPDATE clause: Y/N  

COLUMN_USAGE  

VARCHAR2(17)  

 

How the column is used in the trigger. All applicable combinations of NEW, OLD, IN, OUT, and IN OUT.  

ALL_TYPE_ATTRS

This view displays the attributes of types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

ATTR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the attribute  

ATTR_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the attribute  

ATTR_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the attribute  

ATTR_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the attribute  

LENGTH  

NUMBER  

 

Length of the CHAR attribute or maximum length of the VARCHAR or VARCHAR2 attribute  

PRECISION  

NUMBER  

 

Decimal precision of the NUMBER or DECIMAL attribute or binary precision of the FLOAT attribute  

SCALE  

NUMBER  

 

Scale of the NUMBER or DECIMAL attribute  

CHARACTER_SET
_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS or NCHAR_CS  

ATTR_NO  

NUMBER  

NOT NULL  

Syntactical order number or position of the attribute as specified in the type specification or CREATE TYPE statement (not to be used as ID number)  

ALL_TYPE_METHODS

This view is a description of methods of types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

METHOD_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the method  

METHOD_NO  

NUMBER  

NOT NULL  

Method number for distinguishing overloaded method (not to be used as ID number)  

METHOD_TYPE  

VARCHAR2(6)  

 

Type of the method  

PARAMETERS  

NUMBER  

NOT NULL  

Number of parameters to the method  

RESULTS  

NUMBER  

NOT NULL  

Number of results returned by the method  

ALL_TYPES

This view displays the types accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

TYPE_OID  

RAW(16)  

NOT NULL  

Object identifier (OID) of the type  

TYPECODE  

VARCHAR2(30)  

 

Typecode of the type  

ATTRIBUTES  

NUMBER  

 

Number of attributes in the type  

METHODS  

NUMBER  

 

Number of methods in the type  

PREDEFINED  

VARCHAR2(3)  

 

Indicates whether the type is a predefined type  

INCOMPLETE  

VARCHAR2(3)  

 

Indicates whether the type is an incomplete type  

ALL_UNUSED_COL_TABS

This view contains a description of all tables containing unused columns.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

The owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

The name of the table  

COUNT  

NUMBER  

 

The number of unused columns  

ALL_UPDATABLE_COLUMNS

This view contains a description of all columns that are updatable in a join view.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Table owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table name  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Column name  

UPDATABLE  

VARCHAR2(3)  

 

Indicates whether the column is updatable  

INSERTABLE  

VARCHAR2(3)  

 

Indicates whether the column is insertable  

DELETABLE  

VARCHAR2(3)  

 

Indicates whether the column is deletable  

ALL_USERS

This view contains information about all users of the database.

Column   Datatype   NULL   Description  

USERNAME  

VARCHAR2(30)  

NOT NULL  

Name of the user  

USER_ID  

NUMBER  

NOT NULL  

ID number of the user  

CREATED  

DATE  

NOT NULL  

User creation date  

ALL_USTATS

This view contains information about the current user.

Column   Datatype   NULL   Description  

OBJECT_OWNER  

VARCHAR2(30)  

 

Owner of the table or index for which the statistics have been collected  

OBJECT_NAME  

VARCHAR2(30)  

 

Name of the table or index for which the statistics have been collected  

PROPERTY  

VARCHAR2(6)  

 

Property of the object - column or index  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name, if property is column for which statistics have been collected  

STATSTYPE_SCHEMA  

VARCHAR2(30)  

 

Schema of statistics type which was used to collect the statistics  

STATSTYPE_NAME  

VARCHAR2(30)  

 

Name of statistics type which was used to collect statistics  

STATISTICS  

RAW(2000)  

 

User collected statistics for the object  

ALL_VARRAYS

This view lists the text of views accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the table containing the varray  

PARENT_TABLE_NAME  

VARCHAR2(30)  

 

Name of the containing table  

PARENT_TABLE_COLUMN  

VARCHAR2(4000)  

 

Name of the varray column or attribute  

TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the varray type  

TYPE_NAME  

VARCHAR2(30)  

 

Name of the varray type  

LOB_NAME  

VARCHAR2(30)  

 

Name of the LOB if the varray is stored in a LOB  

STORAGE_SPEC  

VARCHAR2(30)  

 

DEFAULT value indicates that the storage was defaulted. USER_SPECIFIED value indicates that the storage was user-specified  

RETURN_TYPE  

VARCHAR2(20)  

 

Return type of the column  

ALL_VIEWS

This view lists the text of views accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the view  

VIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the view  

TEXT_LENGTH  

NUMBER  

 

Length of the view text  

TEXT  

LONG  

 

View text  

TYPE_TEXT_LENGTH  

NUMBER  

 

Length of the type clause of the typed view  

TYPE_TEXT  

VARCHAR2(4000)  

 

Type clause of the typed view  

OID_TEXT_LENGTH  

NUMBER  

 

Length of the WITH OID clause of the typed view  

OID_TEXT  

VARCHAR2(4000)  

 

WITH OID clause of the typed view  

VIEW_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the view if the view is a typed view  

VIEW_TYPE  

VARCHAR2(30)  

 

Type of the view if the view is a typed view  

AUDIT_ACTIONS

This view contains descriptions for audit trail action type codes.

Column   Datatype   NULL   Description  

ACTION  

NUMBER  

NOT NULL  

Numeric audit trail action type code  

NAME  

VARCHAR2(27)  

NOT NULL  

Name of the type of audit trail action  

CATALOG

This view is included for compatibility with Oracle version 5. Use of this view is not recommended.

CAT

This is a synonym for USER_CATALOG. For more information, see "USER_CATALOG".

CHAINED_ROWS

Stores the output for the ANALYZE command with the CHAINED ROWS option.

Column   Description  

OWNER_NAME  

Table owner  

TABLE_NAME  

Table name  

CLUSTER_NAME  

Cluster the table is in, if any  

PARTITION_NAME  

The name of the partition  

SUBPARTITION_NAME  

The name of the subpartition  

HEAD_ROWID  

ROWID the chained row is accessed by  

ANALYZE_TIMESTAMP  

Date/time that the ANALYZE command was issued  

CLU

This is a synonym for USER_CLUSTERS. For more information, see "USER_CLUSTERS".

CODE_PIECES

This view is accessed to create the DBA_OBJECT_SIZE and USER_OBJECT_SIZE views. For more information, see "DBA_OBJECT_SIZE" and "USER_OBJECT_SIZE".

CODE_SIZE

This view is accessed to create the DBA_OBJECT_SIZE and USER_OBJECT_SIZE views. For more information, see "DBA_OBJECT_SIZE" and "USER_OBJECT_SIZE".

COL

This view is included for compatibility with Oracle version 5. Use of this view is not recommended.

COLS

This is a synonym for USER_TAB_COLUMNS. For more information, see "USER_TAB_COLUMNS".

COLUMN_PRIVILEGES

This view lists grants on columns for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee.

This view is included for compatibility with Oracle version 6. Use of this view is not recommended.

Column   Description  

GRANTEE  

Name of the user to whom access was granted.  

OWNER  

Username of the object's owner.  

TABLE_NAME  

Name of the object.  

COLUMN_NAME  

Name of the column.  

GRANTOR  

Name of the user who performed the grant.  

INSERT_PRIV  

Permission to insert into the column.  

UPDATE_PRIV  

Permission to update the column.  

REFERENCES_PRIV  

Permission to reference the column.  

CREATED  

Timestamp for the grant.  

Column   Description  

GRANTEE  

Name of the user to whom access was granted  

OWNER  

Username of the object's owner  

TABLE_NAME  

Name of the object  

COLUMN_NAME  

Name of the column  

GRANTOR  

Name of the user who performed the grant  

INSERT_PRIV  

Permission to insert into the column  

UPDATE_PRIV  

Permission to update the column  

REFERENCES_PRIV  

Permission to reference the column  

CREATED  

Timestamp for the grant  

DBA_2PC_NEIGHBORS

This view contains information about incoming and outgoing connections for pending transactions.

Column   Datatype   NULL   Description  

LOCAL_TRAN_ID  

VARCHAR2(22)  

 

Local identifier of a transaction  

IN_OUT  

VARCHAR2(3)  

 

IN for incoming connections, OUT for outgoing  

DATABASE  

VARCHAR2(128)  

 

IN: client database name; OUT: outgoing database link  

DBUSER_OWNER  

VARCHAR2(30)  

 

IN: name of local user; OUT: owner of database link  

INTERFACE  

VARCHAR2(1)  

 

"C" for request commit, otherwise "N" for prepare or request readonly commit  

DBID  

VARCHAR2(16)  

 

The database ID at the other end of the connection  

SESS#  

NUMBER  

 

Session number of the connection at this database  

BRANCH  

VARCHAR2(128)  

 

Transaction branch ID of the connection at this database  

DBA_2PC_PENDING

This view contains information about distributed transactions awaiting recovery.

Column   Datatype   NULL   Description  

LOCAL_TRAN_ID  

VARCHAR2(22)  

NOT NULL  

String of form: n.n.n; n is a number  

GLOBAL_TRAN_ID  

VARCHAR2(169)  

 

Globally unique transaction ID  

STATE  

VARCHAR2(16)  

NOT NULL  

Collecting, prepared, committed, forced commit, or forced rollback  

MIXED  

VARCHAR2(3)  

 

YES = part of the transaction committed and part rolled back  

ADVICE  

VARCHAR2(1)  

 

C for commit, R for rollback, else NULL  

TRAN_COMMENT  

VARCHAR2(2000)  

 

Text for commit work comment text  

FAIL_TIME  

DATE  

NOT NULL  

Value of SYSDATE when the row was inserted (tx or system recovery)  

FORCE_TIME  

DATE  

 

Time of manual force decision (null if not forced locally)  

RETRY_TIME  

DATE  

NOT NULL  

Time automatic recovery (RECO) last tried to recover the transaction  

OS_USER  

VARCHAR2(2000)  

 

Time automatic recovery (RECO) last tried to recover the transaction  

OS_TERMINAL  

VARCHAR2(2000)  

 

Time automatic recovery (RECO) last tried to recover the transaction  

HOST  

VARCHAR2(2000)  

 

Name of the host machine for the end-user  

DB_USER  

VARCHAR2(30)  

 

Name of the host machine for the end-user  

COMMIT#  

VARCHAR2(16)  

 

Name of the host machine for the end-user  

DBA_ALL_TABLES

This view displays descriptions of all tables (object tables and relational tables) in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the table  

TABLE_NAME  

VARCHAR2(30)  

 

Name of the table  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the table  

CLUSTER_NAME  

VARCHAR2(30)  

 

Name of the cluster, if any, to which the table belongs  

IOT_NAME  

VARCHAR2(30)  

 

Name of the index organized table, if any, to which the overflow entry belongs  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

 

Minimum percentage of used space in a block  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute  

BACKED_UP  

VARCHAR2(1)  

 

Has table been backed up since last modification?  

NUM_ROWS  

NUMBER  

 

The number of rows in the table  

BLOCKS  

NUMBER  

 

The number of used blocks in the table  

EMPTY_BLOCKS  

NUMBER  

 

The number of empty (never used) blocks in the table  

AVG_SPACE  

NUMBER  

 

The average available free space in the table  

CHAIN_CNT  

NUMBER  

 

The number of chained rows in the table  

AVG_ROW_LEN  

NUMBER  

 

The average row length, including row overhead  

AVG_SPACE_FREELIST
_BLOCKS  

NUMBER  

 

The average freespace of all blocks on a freelist  

NUM_FREELIST
_BLOCKS  

NUMBER  

 

The number of blocks on the freelist  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the table  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the table is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the table is to be cached in the buffer cache  

TABLE_LOCK  

VARCHAR2(8)  

 

Whether table locking is enabled or disabled  

SAMPLE_SIZE  

NUMBER  

 

The sample size used in analyzing this table  

LAST_ANALYZED  

DATE  

 

The date of the most recent time this table was analyzed  

PARTITIONED  

VARCHAR2(3)  

 

Is this table partitioned? YES or NO  

IOT_TYPE  

VARCHAR2(12)  

 

If an index organized table, then IOT_TYPE is IOT or IOT_OVERFLOW else NULL  

OBJECT_ID_TYPE  

VARCHAR2(16)  

 

If user-defined OID, then USER-DEFINED, else if system generated OID, then SYSTEM GENERATED  

TABLE_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the table if the table is a typed table  

TABLE_TYPE  

VARCHAR2(30)  

 

Type of the table if the table is a typed table  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it place in this object itself?  

SECONDARY  

VARCHAR2(1)  

 

Is the index object created as part of icreate for domain indexes?  

NESTED  

VARCHAR2(3)  

 

Is the table a nested table?  

BUFFER_POOL  

VARCHAR2(7)  

 

The default buffer pool to be used for table blocks  

ROW_MOVEMENT  

VARCHAR2(8)  

 

The movement of the row  

GLOBAL_STATS  

VARCHAR2(3)  

 

Are the statistics calculated without merging underlying partitions?  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

If temporary table, then duration is sys$session or sys$transaction else NULL  

SKIP_CORRUPT  

VARCHAR2(8)  

 

Whether skip corrupt blocks is enabled or disabled  

DBA_ANALYZE_OBJECTS

This view lists analyze objects.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

The owner  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

The object name  

OBJECT_TYPE  

VARCHAR2(7)  

 

The object type  

DBA_ASSOCIATIONS

This view lists user-defined statistics information.

Column   Datatype   NULL   Description  

OBJECT_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object for which the association is being defined  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

Object name for which the association is being defined  

COLUMN_NAME  

VARCHAR2(30)  

 

Column name in the object for which the association is being defined  

OBJECT_TYPE  

VARCHAR2(9)  

 

Schema type of the object - column, type, package or function  

STATSTYPE_SCHEMA  

VARCHAR2(30)  

 

Owner of the statistics type  

STATSTYPE_NAME  

VARCHAR2(30)  

 

Name of Statistics type which contains the cost, selectivity or stats funcs  

DEF_SELECTIVITY  

NUMBER  

 

Default Selectivity if any of the object  

DEF_CPU_COST  

NUMBER  

 

Default CPU cost if any of the object  

DEF_IO_COST  

NUMBER  

 

Default I/O cost if any of the object  

DEF_NET_COST  

NUMBER  

 

Default Networking cost if any of the object  

DBA_AUDIT_EXISTS

This view lists audit trail entries produced by AUDIT NOT EXISTS and AUDIT EXISTS.

Column   Datatype   NULL   Description  

OS_USERNAME  

VARCHAR2(255)  

 

Operating system login username of the user whose actions were audited  

USERNAME  

VARCHAR2(30)  

 

Name (not ID number) of the user whose actions were audited  

USERHOST  

VARCHAR2(2000)  

 

Numeric instance ID for the Oracle instance from which the user is accessing the database  

TERMINAL  

VARCHAR2(2000)  

 

Identifier of the user's terminal  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp for the creation of the audit trail entry  

OWNER  

VARCHAR2(30)  

 

Intended creator of the non-existent object  

OBJ_NAME  

VARCHAR2(128)  

 

Name of the object affected by the action  

ACTION_NAME  

VARCHAR2(27)  

 

Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL  

NEW_OWNER  

VARCHAR2(30)  

 

Owner of the object named in the NEW_NAME column  

NEW_NAME  

VARCHAR2(128)  

 

New name of an object after a RENAME or the name of the underlying object  

OBJ_PRIVILEGE  

VARCHAR2(16)  

 

Object privileges granted or revoked by a GRANT or REVOKE statement  

SYS_PRIVILEGE  

VARCHAR2(40)  

 

System privileges granted or revoked by a GRANT or REVOKE statement  

GRANTEE  

VARCHAR2(30)  

 

Name of grantee specified in a GRANT or REVOKE statement  

SESSIONID  

NUMBER  

NOT NULL  

Numeric ID for each Oracle session  

ENTRYID  

NUMBER  

NOT NULL  

Numeric ID for each audit trail entry in the session  

STATEMENTID  

NUMBER  

NOT NULL  

Numeric ID for each statement run  

RETURNCODE  

NUMBER  

NOT NULL  

Oracle Server message code generated by the action. Some useful values:

  • zero: the action succeeded

  • 2004: security violation

 

DBA_AUDIT_OBJECT

This view contains audit trail records for all objects in the system.

Column   Datatype   NULL   Description  

OS_USERNAME  

VARCHAR2(255)  

 

Operating system login username of the user whose actions were audited  

USERNAME  

VARCHAR2(30)  

 

Name (not ID number) of the user whose actions were audited  

USERHOST  

VARCHAR2(2000)  

 

Numeric instance ID for the Oracle instance from which the user is accessing the database  

TERMINAL  

VARCHAR2(2000)  

 

Identifier of the user's terminal  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp for the creation of the audit trail entry or login time for the CONNECT statement  

OWNER  

VARCHAR2(30)  

 

Creator of the object affected by the action  

OBJ_NAME  

VARCHAR2(128)  

 

Name of the object affected by the action  

ACTION_NAME  

VARCHAR2(27)  

 

Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL  

NEW_OWNER  

VARCHAR2(30)  

 

Owner of the object named in the NEW_NAME column  

NEW_NAME  

VARCHAR2(128)  

 

New name of an object after a RENAME or the name of the underlying object  

SES_ACTIONS  

VARCHAR2(19)  

 

Session summary (a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved for future use. The characters are: - for none, S for success, F for failure, and B for both)  

COMMENT_TEXT  

VARCHAR2(4000  

 

Text comment on the audit trail  

SESSIONID  

NUMBER  

NOT NULL  

Numeric ID for each Oracle session  

ENTRYID  

NUMBER  

NOT NULL  

Numeric ID for each audit trail entry in the session  

STATEMENTID  

NUMBER  

NOT NULL  

Numeric ID for each statement run  

RETURNCODE  

NUMBER  

NOT NULL  

Oracle Server message code generated by the action. Some useful values:

  • zero: the action succeeded

  • 2004: security violation

 

PRIV_USED  

VARCHAR2(40)  

 

System privilege used to execute the action  

DBA_AUDIT_SESSION

This view lists all audit trail records concerning CONNECT and DISCONNECT.

Column   Datatype   NULL   Description  

OS_USERNAME  

VARCHAR2(255)  

 

Operating system login username of the user whose actions were audited  

USERNAME  

VARCHAR2(30)  

 

Name (not ID number) of the user whose actions were audited  

USERHOST  

VARCHAR2(2000)  

 

Numeric instance ID for the Oracle instance from which the user is accessing the database  

TERMINAL  

VARCHAR2(2000)  

 

Identifier of the user's terminal  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp for the creation of the audit trail entry or login time for the CONNECT statement  

ACTION_NAME  

VARCHAR2(27)  

 

Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL  

LOGOFF_TIME  

DATE  

 

Timestamp for user log off  

LOGOFF_LREAD  

NUMBER  

 

Logical reads for the session  

LOGOFF_PREAD  

NUMBER  

 

Physical reads for the session  

LOGOFF_LWRITE  

NUMBER  

 

Logical writes for the session  

LOGOFF_DLOCK  

VARCHAR2(40)  

 

Deadlocks detected during the session  

SESSIONID  

NUMBER  

NOT NULL  

Numeric ID for each Oracle session  

RETURNCODE  

NUMBER  

NOT NULL  

Oracle Server message code generated by the action. Some useful values:

  • zero: the action succeeded

  • 2004: security violation

 

DBA_AUDIT_STATEMENT

This view lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements.

Column   Datatype   NULL   Description  

OS_USERNAME  

VARCHAR2(255)  

 

Operating system login username of the user whose actions were audited  

USERNAME  

VARCHAR2(30)  

 

Name (not ID number) of the user whose actions were audited  

USERHOST  

VARCHAR2(2000)  

 

Numeric instance ID for the Oracle instance from which the user is accessing the database  

TERMINAL  

VARCHAR2(2000)  

 

Identifier of the user's terminal  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp for the creation of the audit trail entry or login time for the CONNECT statement  

OWNER  

VARCHAR2(30)  

 

Creator of the object affected by the action  

OBJ_NAME  

VARCHAR2(128)  

 

Name of object affected by the action  

ACTION_NAME  

VARCHAR2(27)  

 

Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL  

NEW_NAME  

VARCHAR2(128)  

 

New name of an object after a RENAME or the name of the underlying object  

OBJ_PRIVILEGE  

VARCHAR2(16)  

 

Object privileges granted or revoked by a GRANT or REVOKE statement  

SYS_PRIVILEGE  

VARCHAR2(40)  

 

System privileges granted or revoked by a GRANT or REVOKE statement  

ADMIN_OPTION  

VARCHAR2(1)  

 

Signifies the role or system privilege was granted with ADMIN option  

GRANTEE  

VARCHAR2(30)  

 

Name of grantee specified in a GRANT or REVOKE statement  

AUDIT_OPTION  

VARCHAR2(40)  

 

Auditing option set with the AUDIT statement  

SES_ACTIONS  

VARCHAR2(19)  

 

Session summary (a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved for future use. The characters are: - for none, S for success, F for failure, and B for both)  

COMMENT_TEXT  

VARCHAR2(4000)  

 

Text comment on the audit trail, inserted by the application  

SESSIONID  

NUMBER  

NOT NULL  

Numeric ID for each Oracle session  

ENTRYID  

NUMBER  

NOT NULL  

Numeric ID for each audit trail entry in the session  

STATEMENTID  

NUMBER  

NOT NULL  

Numeric ID for each statement run  

RETURNCODE  

NUMBER  

NOT NULL  

Oracle Server message code generated by the action. Some useful values:

  • zero: the action succeeded

  • 2004: security violation

 

PRIV_USED  

VARCHAR2(40)  

 

System privilege used to execute the action  

DBA_AUDIT_TRAIL

This view lists all audit trail entries.

Column   Datatype   NULL   Description  

OS_USERNAME  

VARCHAR2(255)  

 

Operating system login username of the user whose actions were audited  

USERNAME  

VARCHAR2(30)  

 

Name (not ID number) of the user whose actions were audited  

USERHOST  

VARCHAR2(2000)  

 

Numeric instance ID for the Oracle instance from which the user is accessing the database  

TERMINAL  

VARCHAR2(2000)  

 

Identifier of the user's terminal  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp for the creation of the audit trail entry or login time for the CONNECT statement  

OWNER  

VARCHAR2(30)  

 

Creator of the object affected by the action  

OBJ_NAME  

VARCHAR2(128)  

 

Name of the object affected by the action  

ACTION  

NUMBER  

NOT NULL  

Numeric type code corresponding to the action  

ACTION_NAME  

VARCHAR2(27)  

 

Name of the action type corresponding to the numeric code in the ACTION column  

NEW_OWNER  

VARCHAR2(30)  

 

Owner of the object named in the NEW_NAME column  

NEW_NAME  

VARCHAR2(128)  

 

New name of an object after a RENAME or the name of the underlying object  

OBJ_PRIVILEGE  

VARCHAR2(16)  

 

Object privileges granted or revoked by a GRANT or REVOKE statement  

SYS_PRIVILEGE  

VARCHAR2(40)  

 

System privileges granted or revoked by a GRANT or REVOKE statement  

ADMIN_OPTION  

VARCHAR2(1)  

 

Signifies the role or system privilege was granted with ADMIN option  

GRANTEE  

VARCHAR2(30)  

 

Name of grantee specified in a GRANT or REVOKE statement  

AUDIT_OPTION  

VARCHAR2(40)  

 

Auditing option set with the AUDIT statement  

SES_ACTIONS  

VARCHAR2(19)  

 

Session summary (a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved for future use. The characters are: - for none, S for success, F for failure, and B for both)  

LOGOFF_TIME  

DATE  

 

Timestamp for user log off  

LOGOFF_LREAD  

NUMBER  

 

Logical reads for the session  

LOGOFF_PREAD  

NUMBER  

 

Physical reads for the session  

LOGOFF_LWRITE  

NUMBER  

 

Logical writes for the session  

LOGOFF_DLOCK  

VARCHAR2(40)  

 

Deadlocks detected during the session  

COMMENT_TEXT  

VARCHAR2(4000)  

 

Text comment on the audit trail entry, providing more information about the statement audited

Also indicates how the user was authenticated. The method can be one of the following:

DATABASE - authentication was done by password
NETWORK - authentication was done by Net8 or the Advanced Security option
PROXY - the client was authenticated by another user. The name of the proxy user follows the method type  

SESSIONID  

NUMBER  

NOT NULL  

Numeric ID for each Oracle session  

ENTRYID  

NUMBER  

NOT NULL  

Numeric ID for each audit trail entry in the session  

STATEMENTID  

NUMBER  

NOT NULL  

Numeric ID for each statement run  

RETURNCODE  

NUMBER  

NOT NULL  

Oracle Server message code generated by the action. Some useful values:

  • zero: the action succeeded

  • 2004: security violation

 

PRIV_USED  

VARCHAR2(40)  

 

System privilege used to execute the action  

DBA_BLOCKERS

This view lists all sessions that have someone waiting on a lock they hold that are not themselves waiting on a lock.

Column   Datatype   NULL   Description  

HOLDING_SESSION  

NUMBER  

 

Session holding a lock  

DBA_CATALOG

This view lists all database tables, views, synonyms, and sequences.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

TABLE_TYPE  

VARCHAR2(11)  

 

Type of the object  

DBA_CLU_COLUMNS

This view lists mappings of table columns to cluster columns.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the cluster  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

Cluster name  

CLU_COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Key column in the cluster  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Clustered table name  

TAB_COLUMN_NAME  

VARCHAR2(4000)  

 

Key column or attribute of the object type column  

DBA_CLUSTERS

This view contains description of all clusters in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the cluster  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the cluster  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the cluster  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

PCT_USED  

NUMBER  

NOT NULL  

Minimum percentage of used space in a block  

KEY_SIZE  

NUMBER  

 

Estimated size of cluster key plus associated rows  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENTS  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated to this segment  

AVG_BLOCKS_PER_KEY  

NUMBER  

 

Average number of blocks containing rows with a given cluster key  

CLUSTER_TYPE  

VARCHAR2(5)  

 

Type of cluster: B-Tree index or hash  

FUNCTION  

VARCHAR2(15)  

 

If a hash cluster, the hash function  

HASHKEYS  

NUMBER  

 

If a hash cluster, the number of hash keys (hash buckets)  

DEGREE  

VARCHAR2(10)  

 

The number of threads per instance for scanning the table  

INSTANCES  

VARCHAR2(10)  

 

The number of instances across which the table is to be scanned  

CACHE  

VARCHAR2(5)  

 

Whether the table is to be cached in the buffer cache  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object  

SINGLE_TABLE  

VARCHAR2(5)  

 

Y if the cluster is single table; N if not  

DBA_CLUSTER_HASH_EXPRESSIONS

This view list hash functions for all clusters.

This view list all clusters accessible to the user.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Name of owner of cluster  

CLUSTER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of cluster  

HASH_EXPRESSION  

LONG  

 

Text of hash function of cluster  

DBA_COL_COMMENTS

This view lists comments on columns of all tables and views.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Name of the owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the column  

COMMENTS  

VARCHAR2(4000)  

 

Comment on the object  

DBA_COL_PRIVS

This view lists all grants on columns in the database.

Column   Datatype   NULL   Description  

GRANTEE  

VARCHAR2(30)  

NOT NULL  

Name of the user to whom access was granted  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the column  

GRANTOR  

VARCHAR2(30)  

NOT NULL  

Name of the user who performed the grant  

PRIVILEGE  

VARCHAR2(40)  

NOT NULL  

Column privilege  

GRANTABLE  

VARCHAR2(3)  

 

Privilege is Grantable  

DBA_COLL_TYPES

This view displays all named collection types in the database such as VARRAYs, nested tables, object tables, and so on.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

COLL_TYPE  

VARCHAR2(30)  

NOT NULL  

Collection type  

UPPER_BOUND  

NUMBER  

 

Maximum size of the VARRAY type  

ELEM_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the element  

ELEM_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the element  

ELEM_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the element  

LENGTH  

NUMBER  

 

Length of the CHAR element or maximum length of the VARCHAR or VARCHAR2 element  

PRECISION  

NUMBER  

 

Decimal precision of the NUMBER or DECIMAL element or binary precision of the FLOAT element  

SCALE  

NUMBER  

 

Scale of the NUMBER or DECIMAL element  

CHARACTER_SET_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS NCHAR_CS  

ELEM_STORAGE  

VARCHAR2(7)  

 

Storage optimization specification for VARRAY of numeric elements  

NULLS_STORED  

VARCHAR2(3)  

 

Is null information stored with each VARRAY element?  

DBA_CONSTRAINTS

This view contains constraint definitions on all tables.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table  

CONSTRAINT_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with constraint definition  

CONSTRAINT_TYPE  

VARCHAR2(1)  

 

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)
O (with read only, on a view)  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with table with constraint definition  

SEARCH_CONDITION  

LONG  

 

Text of search condition for table check  

R_OWNER  

VARCHAR2(30)  

 

Owner of table used in referential constraint  

R_CONSTRAINT_NAME  

VARCHAR2(30)  

 

Name of unique constraint definition for referenced table  

DELETE_RULE  

VARCHAR2(9)  

 

Delete rule for a referential constraint: CASCADE / NO ACTION  

STATUS  

VARCHAR2(8)  

 

Enforcement status of constraint: ENABLED or DISABLED  

DEFERRABLE  

VARCHAR2(14)  

 

Indicates whether the constraint is deferrable  

DEFERRED  

VARCHAR2(9)  

 

Indicates whether the constraint was initially deferred  

VALIDATED  

VARCHAR2(13)  

 

Indicates whether all data obeys the constraint: VALIDATED, NOT VALIDATED  

GENERATED  

VARCHAR2(14)  

 

Indicates whether the name system is generated  

BAD  

VARCHAR2(3)  

 

Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.  

RELY  

VARCHAR2(4)  

 

If set, this flag will be used in the optimizer  

LAST_CHANGE  

DATE  

 

Indicates when the constraint was last enabled or disabled  

DBA_CONS_COLUMNS

This view contains information about accessible columns in constraint definitions.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the constraint definition  

CONSTRAINT_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with the constraint definition  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name associated with table with constraint definition  

COLUMN_NAME  

VARCHAR2(4000)  

 

Name associated with column or attribute of the object type column specified in the constraint definition  

POSITION  

NUMBER  

 

Original position of column or attribute in definition  

Note: If you create a constraint on a user-defined REF column, the system creates the constraint on the exploded columns that make up the REF column and hence column names of the constraint will be the column names of the exploded columns. These names will however have as their prefix the REF column's name.

DBA_CONTEXT

This view lists all context namespaces information.

Column   Datatype   NULL   Description  

NAMESPACE  

VARCHAR2(30)  

NOT NULL  

The name of the context namespace  

SCHEMA  

VARCHAR2(30)  

NOT NULL  

The schema name of the designated package which can set attributes using this namespace  

PACKAGE  

VARCHAR2(30)  

NOT NULL  

The package name of the designated package which can set attributes using this namespace  

DBA_DATA_FILES

This view contains information about database files.

Column   Datatype   NULL   Description  

FILE_NAME  

VARCHAR2(513)  

 

Name of the database file  

FILE_ID  

NUMBER  

NOT NULL  

ID of the database file  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace to which the file belongs  

BYTES  

NUMBER  

 

Size of the file in bytes  

BLOCKS  

NUMBER  

NOT NULL  

Size of the file in Oracle blocks  

STATUS  

VARCHAR2(9)  

 

File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)  

RELATIVE_FNO  

NUMBER  

 

Relative file number  

AUTOEXTENSIBLE  

VARCHAR2(3)  

 

Autoextensible indicator  

MAXBYTES  

NUMBER  

 

Maximum file size in bytes  

MAXBLOCKS  

NUMBER  

 

Maximum file size in blocks  

INCREMENT_BY  

NUMBER  

 

Autoextension increment  

USER_BYTES  

NUMBER  

 

Corresponding number of bytes  

USER_BLOCKS  

NUMBER  

 

Number of blocks which can be used by the data  

DBA_DB_LINKS

This view lists all database links in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the database link  

DB_LINK  

VARCHAR2(128)  

NOT NULL  

Name of the database link  

USERNAME  

VARCHAR2(3)  

 

Name of user to log in as  

HOST  

VARCHAR2(2000)  

 

Connect string  

CREATED  

DATE  

NOT NULL  

Creation time of the database link  

DBA_DDL_LOCKS

This view lists all DDL locks held in the database and all outstanding requests for a DDL lock.

Column   Datatype   NULL   Description  

SESSION_ID  

NUMBER  

 

Session identifier  

OWNER  

VARCHAR2(30)  

 

Owner of the lock  

NAME  

VARCHAR2(30)  

 

Name of the lock  

TYPE  

VARCHAR2(40)  

 

Lock type: CURSOR, TABLE/PROCEDURE/TYPE, BODY, TRIGGER, INDEX, CLUSTER  

MODE_HELD  

VARCHAR2(9)  

 

Lock mode: NONE, NULL, SHARE, EXCLUSIVE  

MODE_REQUESTED  

VARCHAR2(9)  

 

Lock request type: NONE, NULL, SHARE, EXCLUSIVE  

DBA_DEPENDENCIES

This view lists dependencies to and from objects. Dependencies on views created without any database links are also available.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the object  

NAME  

VARCHAR2(3)  

NOT NULL  

Name of the object  

TYPE  

VARCHAR2(12)  

 

Type of the object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY.  

REFERENCED_OWNER  

VARCHAR2(30)  

 

Owner of referenced object (remote owner if remote object)  

REFERENCED_NAME  

VARCHAR2(64)  

 

Type of parent object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY  

REFERENCED_TYPE  

VARCHAR2(12)  

 

Type of referenced object: PROCEDURE, PACKAGE, FUNCTION, PACKAGE BODY.  

REFERENCED_LINK
_NAME  

VARCHAR2(128)  

 

Name of dblink if this is a remote object  

DEPENDENCY_TYPE  

VARCHAR2(4)  

 

Two values: REF when the dependency is a REF dependency; HARD otherwise  

DBA_DIMENSIONS

This view represents dimension objects.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

INVALID  

VARCHAR2(1)  

 

'Y' if this dimension is in an invalid state, else 'N'  

REVISION  

NUMBER  

 

Dimension revision level  

DBA_DIM_ATTRIBUTES

This view represents the relationship between a dimension level and a functionally dependent column. The table that the level columns are in must match the table of the dependent column.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Dependent column name  

INFERRED  

CHAR(1)  

 

Whether inferred  

DBA_DIM_CHILD_OF

This view represents a 1:n hierarchical relationship between a pair of levels in a dimension.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Hierarchy name  

POSITION  

NUMBER  

NOT NULL  

Hierarchical position within this hierarchy, position 1 being the most detailed  

CHILD_LEVEL_NAME  

VARCHAR2(30)  

 

Child side of 1:n relationship  

JOIN_KEY_ID  

VARCHAR2(40)  

 

If non-null, then the child joins to the parent  

PARENT_LEVEL_NAME  

VARCHAR2(30)  

 

Parent side of 1:n relationship  

DBA_DIM_JOIN_KEY

This view represents a join between two dimension tables. The join is always specified between a parent dimension level column and a child column.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

DIM_KEY_ID  

NUMBER  

NOT NULL  

Unique within a dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

KEY_POSITION  

NUMBER  

NOT NULL  

Ordinal position of the key column within the level  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Name of the key column  

CHILD_JOIN_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of the join column  

DBA_DIM_HIERARCHIES

This view represents a dimension hierarchy.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

HIERARCHY_NAME  

VARCHAR2(30)  

 

Hierarchy name  

DBA_DIM_LEVELS

This view represents a dimension level. All columns of a dimension level must come from the same relation.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Unique within a dimension  

NUM_COLUMNS  

NUMBER  

 

Number of columns in the level definition  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the detail object that the keys of this level come from  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table that the keys of this level come from  

DBA_DIM_LEVEL_KEY

This view represents a column of a dimension level. The position of a column within a level is specified by KEY_POSITION.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the dimension  

DIMENSION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the dimension  

LEVEL_NAME  

VARCHAR2(30)  

 

Name of the hierarchy level  

KEY_POSITION  

NUMBER  

NOT NULL  

Ordinal position of the key column within the level  

COLUMN_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the key column  

DBA_DIRECTORIES

This view provides information on all directory objects in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the directory (always SYS)  

DIRECTORY_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the directory  

DIRECTORY_PATH  

VARCHAR2(4000)  

 

Operating system pathname for the directory  

DBA_DML_LOCKS

This view lists all DML locks held in the database and all outstanding requests for a DML lock.

Column   Datatype   NULL   Description  

SESSION_ID  

NUMBER  

 

Session holding or acquiring the lock  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the lock  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the lock  

MODE_HELD  

VARCHAR2(13)  

 

Lock mode: see Table 2-1, "Lock Modes for the DBA_DML_LOCKS View"  

MODE_REQUESTED  

VARCHAR2(13)  

 

Lock request type: see Table 2-1, "Lock Modes for the DBA_DML_LOCKS View"  

LAST_CONVERT  

NUMBER  

 

The last convert  

BLOCKING_OTHERS  

VARCHAR2(40)  

 

Blocking others  

Table 2-1 describes DML lock mode values that are valid for the MODE_HELD column.

Table 2-1 Lock Modes for the DBA_DML_LOCKS View
Lock Mode  Description 

ROWS-S (SS)  

Row share  

ROW-X (SX)  

Row exclusive  

SHARE (S)  

Share  

S/ROW-X (SSX)  

Exclusive  

NONE  

MODE_HELD: Lock requested, not yet obtained
MODE_REQUESTED: Lock identifier obtained, lock not held or requested  

DBA_ERRORS

This view lists current errors on all stored objects in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

The owner of the object  

NAME  

VARCHAR2(30)  

NOT NULL  

Name of the object  

TYPE  

VARCHAR2(12)  

 

Type of object: VIEW, PROCEDURE, FUNCTION, PACKAGE, TYPE, TYPE BODY, PACKAGE BODY, or TRIGGER  

SEQUENCE  

NUMBER  

NOT NULL  

Sequence number used for ordering purposes  

LINE  

NUMBER  

NOT NULL  

Line number at which this error occurs  

POSITION  

NUMBER  

NOT NULL  

Position in the line at which this error occurs  

TEXT  

VARCHAR2(4000)  

NOT NULL  

Text of the error  

DBA_EXP_FILES

This view contains a description of export files.

Column   Datatype   NULL   Description  

EXP_VERSION  

NUMBER(3)  

NOT NULL  

Version number of the export session  

EXP_TYPE  

VARCHAR2(11)  

 

Type of export file: complete, cumulative, or incremental  

FILE_NAME  

VARCHAR2(100)  

NOT NULL  

Name of the export file  

USER_NAME  

VARCHAR2(30)  

NOT NULL  

Name of user who executed export  

TIMESTAMP  

DATE  

NOT NULL  

Timestamp of the export session  

DBA_EXP_OBJECTS

This view lists objects that have been incrementally exported.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of exported object  

OBJECT_NAME  

VARCHAR2(30)  

NOT NULL  

Name of exported object  

OBJECT_TYPE  

VARCHAR2(12)  

 

Type of exported object  

CUMULATIVE  

DATE  

 

Timestamp of last cumulative export  

INCREMENTAL  

DATE  

NOT NULL  

Timestamp of last incremental export  

EXPORT_VERSION  

NUMBER(3)  

NOT NULL  

The ID of the export session  

DBA_EXP_VERSION

This view contains the version number of the last export session.

Column   Datatypes   NULL   Description  

EXP_VERSION  

NUMBER(3)  

NOT NULL  

Version number of the last export session  

DBA_EXTENTS

This view lists the extents comprising all segments in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

 

Owner of the segment associated with the extent  

SEGMENT_NAME  

VARCHAR2(81)  

 

Name of the segment associated with the extent  

SEGMENT_TYPE  

VARCHAR2(17)  

 

Type of the segment: INDEX PARTITION, TABLE PARTITION  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the extent  

EXTENT_ID  

NUMBER  

NOT NULL  

Extent number in the segment  

FILE_ID  

NUMBER  

NOT NULL  

Name of the file containing the extent  

BLOCK_ID  

NUMBER  

NOT NULL  

Starting block number of the extent  

BYTES  

NUMBER  

 

Size of the extent in bytes  

BLOCKS  

NUMBER  

NOT NULL  

Size of the extent in Oracle blocks  

RELATIVE_FNO  

NUMBER  

NOT NULL  

Relative file number of the first extent block  

PARTITION_NAME  

VARCHAR2(30)  

 

Object Partition Name (Set to NULL for non-partitioned objects)  

DBA_FREE_SPACE

This view lists the free extents in all tablespaces.

Column   Datatype   NULL   Description  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the extent  

FILE_ID  

NUMBER  

NOT NULL  

ID number of the file containing the extent  

BLOCK_ID  

NUMBER  

NOT NULL  

Starting block number of the extent  

BYTES  

NUMBER  

 

Size of the extent in bytes  

BLOCKS  

NUMBER  

NOT NULL  

Size of the extent in Oracle blocks  

RELATIVE_FNO  

NUMBER  

NOT NULL  

Relative file number of the first extent block  

DBA_FREE_SPACE_COALESCED

This view contains statistics on coalesced space in tablespaces.

Column   Datatype   NULL   Description  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of tablespace  

TOTAL_EXTENTS  

NUMBER  

 

Total number of free extents in tablespace  

EXTENTS_COALESCED  

NUMBER  

 

Total number of coalesced free extents in tablespace  

PERCENT_EXTENTS
_COALESCED  

NUMBER  

 

Percentage of coalesced free extents in tablespace  

TOTAL_BYTES  

NUMBER  

 

Total number of free bytes in tablespace  

BYTES_COALESCED  

NUMBER  

 

Total number of coalesced free bytes in tablespace  

TOTAL_BLOCKS  

NUMBER  

 

Total number of free Oracle blocks in tablespace  

BLOCKS_COALESCED  

NUMBER  

 

Total number of coalesced free Oracle blocks in tablespace  

PERCENT_BLOCKS
_COALESCED  

NUMBER  

 

Percentage of coalesced free Oracle blocks in tablespace  

DBA_INDEXES

This view contains descriptions for all indexes in the database. To gather statistics for this view, use the SQL command ANALYZE. This view supports parallel partitioned index scans.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Username of the owner of the index  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the index  

INDEX_TYPE  

VARCHAR2(12)  

 

Type of index  

TABLE_OWNER  

VARCHAR2(30)  

 

Owner of the indexed object  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indexed object  

TABLE_TYPE  

VARCHAR2(11)  

NOT NULL  

Type of the indexed object  

UNIQUENESS  

VARCHAR2(9)  

 

Uniqueness status of the index: UNIQUE or NONUNIQUE  

COMPRESSION  

VARCHAR2(11)  

 

Enabled or disabled  

PREFIX_LENGTH  

NUMBER  

 

The number of columns in the prefix of the key used for compression  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the index  

INI_TRANS  

NUMBER  

 

Initial number of transactions  

MAX_TRANS  

NUMBER  

 

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of initial extent  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size  

PCT_THRESHOLD  

NUMBER  

 

Threshold percentage of block space allowed per index entry  

INCLUDE_COLUMN  

NUMBER  

 

User column-id for last column to be included in index organized table top index  

FREELISTS  

NUMBER  

 

Number of process freelists allocated to this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated to this segment  

PCT_FREE  

NUMBER  

 

Minimum percentage of free space in a block  

LOGGING  

VARCHAR2(3)  

 

Logging attribute  

BLEVEL  

NUMBER  

 

B-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.  

LEAF_BLOCKS  

NUMBER  

 

The number of leaf blocks in the index  

DISTINCT_KEYS  

NUMBER  

 

The number of distinct keys in the index  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

The average number of leaf blocks per key  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

The average number of data blocks per key  

CLUSTERING_FACTOR  

NUMBER  

 

A measurement of the amount of (dis)order of the table this index is for  

STATUS  

VARCHAR2(8)  

 

State of the index: DIRECT LOAD, VALID or INPROGS (a DDL operation on the domain index is in progress).  

DOMIDX_STATUS  

VARCHAR  

 

Reflects the status of the domain index. A NULL value means that the specified index is not a domain index. A Value of VALID means that the index is a domain index and the index does not have any errors. If the value of this column is IDXTYP_INVLD it means that the indextype corresponding to this domain index is invalid.  

DOMIDX_OPSTATUS  

VARCHAR  

 

Reflects the status of an operation that was performed on the domain index. A value of NULL indicates that the specified index is not a domain index. A value of VALID specifies that the index does not have any errors. A value of FAILED indicates that the operation that was performed on the domain index failed with an error.  

FUNCIDX_STATUS  

VARCHAR  

 

A value of NULL indicates a non function-based index. ENABLED indicates the function-based index is enabled. DISABLED indicates the function-based index is disabled.  

NUM_ROWS  

NUMBER  

 

Number of rows in this index  

SAMPLE_SIZE  

NUMBER  

 

Size of the sample used to analyze this index  

LAST_ANALYZED  

DATE  

 

Timestamp for when this index was last analyzed  

DEGREE  

VARCHAR2(40)  

 

Number of threads per instance for scanning the index. NULL if PARTITIONED=NO.  

INSTANCES  

VARCHAR2(40)  

 

Number of instances across which the indexes are to be scanned. NULL if PARTITIONED=NO.  

PARTITIONED  

VARCHAR2(3)  

 

Indicates whether this index is partitioned. Set to YES if it is partitioned  

TEMPORARY  

VARCHAR2(1)  

 

Can the current session only see data that it place in this object itself?  

GENERATED  

VARCHAR2(1)  

 

Was the name of this index system generated?  

SECONDARY  

VARCHAR2(1)  

 

Is the index object created as part of icreate for domain indexes?  

BUFFER_POOL  

VARCHAR2(7)  

 

Name of the default buffer pool for the appropriate object  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DURATION  

VARCHAR2(15)  

 

The duration  

PCT_DIRECT_ACCESS  

NUMBER  

 

If index on IOT, then this is percentage of rows with Valid guess  

DBA_IND_COLUMNS

This view contains descriptions of the columns comprising the indexes on all tables and clusters.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

Index owner  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table or cluster owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table or cluster name  

COLUMN_NAME  

VARCHAR2(4000)  

 

Column name or attribute of the object type column  

COLUMN_POSITION  

NUMBER  

NOT NULL  

Position of column or attribute within index  

COLUMN_LENGTH  

NUMBER  

NOT NULL  

Indexed length of the column or attribute  

DESCEND  

VARCHAR2(4)  

 

Y/N, Y if this column is sorted in descending order  

Note: If you create an index on a user-defined REF column, the system creates the index on the exploded columns that make up the REF column and hence column names of the index will be the column names of the exploded columns. These names will however have as their prefix the REF column's name.

DBA_IND_EXPRESSIONS

This view lists functional index expressions on all tables and clusters.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

Index owner  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Table or cluster owner  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Table or cluster name  

COLUMN_EXPRESSION  

LONG  

 

Functional index expression defining the column  

COLUMN_POSITION  

NUMBER  

NOT NULL  

Position of column or attribute within index  

DBA_IND_PARTITIONS

This view describes, for each index partition, the partition level partitioning information, the storage parameters for the partition, and various partition statistics determined by ANALYZE.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

Index owner  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

COMPOSITE  

VARCHAR2(3)  

 

'YES' if the partition belongs to a Local index on a table partitioned using Composite method; 'NO' otherwise  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_COUNT  

NUMBER  

 

If this is a Local index on a table partitioned using a Composite method, the number of subpartitions in the partition  

HIGH_VALUE  

LONG  

 

Partition bound value expression  

HIGH_VALUE_LENGTH  

NUMBER  

NOT NULL  

Length of partition bound value expression  

PARTITION_POSITION  

NUMBER  

NOT NULL  

Position of the partition within the index  

STATUS  

VARCHAR2(8)  

 

Indicates whether index partition is usable or not  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the partition  

PCT_FREE  

NUMBER  

NOT NULL  

Minimum percentage of free space in a block  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of process freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of partition  

BLEVEL  

NUMBER  

 

B-Tree level  

LEAF_BLOCKS  

NUMBER  

 

Number of leaf blocks in the index partition  

DISTINCT_KEYS  

NUMBER  

 

Number of distinct keys in the index partition  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of leaf blocks per key  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of data blocks per key  

CLUSTERING_FACTOR  

NUMBER  

 

Measurement of the amount of (dis)order of the table this index partition is for  

NUM_ROWS  

NUMBER  

 

Number of rows in this index partition  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this partition  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this partition was analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The buffer pool for this partition  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

PCT_DIRECT_ACCESS  

NUMBER  

 

If index on IOT, then this is percentage of rows with Valid guess  

DBA_IND_SUBPARTITIONS

This view describes, for each index subpartition that the current user owns, the partition level partitioning information, the storage parameters for the subpartition, and various partition statistics determined by ANALYZE.

Column   Datatype   NULL   Description  

INDEX_OWNER  

VARCHAR2(30)  

NOT NULL  

The owner of the index  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Index name  

PARTITION_NAME  

VARCHAR2(30)  

 

Partition name  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Subpartition name  

SUBPARTITION_POSITION  

NUMBER  

NOT NULL  

Position of a subpartition within a partition  

STATUS  

VARCHAR2(8)  

 

Indicates whether index partition is usable or not  

TABLESPACE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the tablespace containing the partition  

PCT_FREE  

NUMBER  

NOT NULL  

Minimum percentage of free space in a block  

INI_TRANS  

NUMBER  

NOT NULL  

Initial number of transactions  

MAX_TRANS  

NUMBER  

NOT NULL  

Maximum number of transactions  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes  

MIN_EXTENT  

NUMBER  

NOT NULL  

Minimum number of extents allowed in the segment  

MAX_EXTENT  

NUMBER  

NOT NULL  

Maximum number of extents allowed in the segment  

PCT_INCREASE  

NUMBER  

NOT NULL  

Percentage increase in extent size  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in this segment  

FREELIST_GROUPS  

NUMBER  

 

Number of process freelist groups allocated in this segment  

LOGGING  

VARCHAR2(3)  

 

Logging attribute of partition  

BLEVEL  

NUMBER  

 

B-Tree level  

LEAF_BLOCKS  

NUMBER  

 

Number of leaf blocks in the index partition  

DISTINCT_KEYS  

NUMBER  

 

Number of distinct keys in the index partition  

AVG_LEAF_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of leaf blocks per key  

AVG_DATA_BLOCKS
_PER_KEY  

NUMBER  

 

Average number of data blocks per key  

CLUSTERING_FACTOR  

NUMBER  

 

Measurement of the amount of (dis)order of the table this index partition is for  

NUM_ROWS  

NUMBER  

 

Number of rows in this index partition  

SAMPLE_SIZE  

NUMBER  

 

Sample size used in analyzing this partition  

LAST_ANALYZED  

DATE  

 

Date of the most recent time this partition was analyzed  

BUFFER_POOL  

VARCHAR2(7)  

 

The buffer pool for the partition  

USER_STATS  

VARCHAR2(3)  

 

Were the statistics entered directly by the user?  

DBA_INDEXTYPES

This view lists all the indextypes.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the indextype  

INDEXTYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indextype  

IMPLEMENTATION
_SCHEMA  

VARCHAR2(30)  

NOT NULL  

Name of the schema for indextype implementation  

IMPLEMENTATION_NAME  

VARCHAR2(30)  

NOT NULL  

Name of indextype implementation  

IMPLEMENTATION_VERSION  

NUMBER  

NOT NULL  

Version of indextype implementation  

NUMBER_OF_OPERATORS  

NUMBER  

 

Number of operators associated with the indextype  

DBA_INDEXTYPE_OPERATORS

This view lists all the operators supported by indextypes.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the indextype  

INDEXTYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the indextype  

OPERATOR_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the operator for which the indextype is defined  

BINDING#  

NUMBER  

NOT NULL  

Binding# associated with the operator  

DBA_JOBS

This view lists all jobs in the database.

Column   Datatype   NULL   Description  

JOB  

NUMBER  

NOT NULL  

Identifier of job. Neither import/export nor repeated executions change it.  

LOG_USER  

VARCHAR2(30)  

NOT NULL  

USER who was logged in when the job was submitted  

PRIV_USER  

VARCHAR2(30)  

NOT NULL  

USER whose default privileges apply to this job  

SCHEMA_USER  

VARCHAR2(30)  

NOT NULL  

Default schema used to parse the job.

For example, if the SCHEMA_USER is SCOTT and you submit the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP.  

LAST_DATE  

DATE  

 

Date that this job last successfully executed  

LAST_SEC  

VARCHAR2(8)  

 

Same as LAST_DATE. This is when the last successful execution started.  

THIS_DATE  

DATE  

 

Date that this job started executing (usually NULL if not executing)  

THIS_SEC  

VARCHAR2(8)  

 

Same as THIS_DATE.  

NEXT_DATE  

DATE  

NOT NULL  

Date that this job will next be executed  

NEXT_SEC  

VARCHAR2(8))  

 

Same as NEXT_DATE. The job becomes due for execution at this time.  

TOTAL_TIME  

NUMBER  

 

Total wall clock time spent by the system on this job, in seconds  

BROKEN  

VARCHAR2(1)  

 

If Y, no attempt is made to run this job.  

INTERVAL  

VARCHAR2(200)  

NOT NULL  

A date function, evaluated at the start of execution, becomes next NEXT_DATE  

FAILURES  

NUMBER  

 

How many times has this job started and failed since its last success?  

WHAT  

VARCHAR2(4000)  

 

Body of the anonymous PL/SQL block that this job executes  

NLS_ENV  

VARCHAR2(4000)  

 

ALTER SESSION parameters describing the NLS environment of the job  

MISC_ENV  

RAW(32)  

 

Other session parameters that apply to this job  

INSTANCE  

NUMBER  

 

Indicates which instance can execute or is executing the job; the default is 0  

DBA_JOBS_RUNNING

This view lists all jobs in the database that are currently running.

Column   Datatype   NULL   Description  

SID  

NUMBER  

 

Identifier of process that is executing the job. See "V$LOCK".  

JOB  

NUMBER  

 

Identifier of job. This job is currently executing.  

FAILURES  

NUMBER  

 

Number of times this job started and failed since its last success  

LAST_DATE  

DATE  

 

Date that this job last successfully executed  

LAST_SEC  

VARCHAR2(8)  

 

Same as LAST_DATE. This is when the last successful execution started.  

THIS_DATE  

DATE  

 

Date that this job started executing  

THIS_SEC  

VARCHAR2(8)  

 

Same as THIS_DATE. This is when the job started executing.  

INSTANCE  

NUMBER  

 

Indicates which instance can execute or is executing the job; the default is 0  

DBA_LIBRARIES

This view lists all the libraries in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the library  

LIBRARY_NAME  

VARCHAR2(30)  

NOT NULL  

Library name  

FILE_SPEC  

VARCHAR2(2000)  

 

Operating system file specification associated with the library  

DYNAMIC  

VARCHAR2(1)  

 

Is the library dynamically loadable? (YES or NO)  

STATUS  

VARCHAR2(7)  

 

Status of the library  

DBA_LOBS

This view displays the LOBs contained in all tables.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the table containing the LOB  

TABLE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the table containing the LOB  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of the LOB column or attribute  

SEGMENT_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the LOB segment  

INDEX_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the LOB index  

CHUNK  

NUMBER  

 

Size of the LOB chunk as a unit of allocation/manipulation in bytes  

PCTVERSION  

NUMBER  

NOT NULL  

Maximum percentage of the LOB space used for versioning  

CACHE  

VARCHAR2(3)  

 

Indicates whether the LOB is accessed through the buffer cache  

LOGGING  

VARCHAR2(3)  

 

Indicates whether the changes to the LOB are logged  

IN_ROW  

VARCHAR2(3)  

 

Are some of the LOBs stored with the base row?  

DBA_LOB_PARTITIONS

This view displays the LOBs contained in tables accessible to the user.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

 

Table owner  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of a LOB column  

LOB_NAME  

VARCHAR2(30)  

 

Name of a partitioned LOB item  

PARTITION_NAME  

VARCHAR2(30)  

 

Name of a table partition  

LOB_PARTITION_NAME  

VARCHAR2(30)  

 

Name of LOB data partition  

LOB_INDPART_NAME  

VARCHAR2(30)  

 

Name of corresponding LOB index partition  

PARTITION_POSITION  

NUMBER  

 

Position of the LOB data partition within the lOB item  

COMPOSITE  

VARCHAR2(3)  

 

Is it a Composite partition (one of YES, NO)?  

CHUNK  

NUMBER  

 

CHUNK attribute of a LOB data partition  

PCTVERSION  

NUMBER  

 

PCTVERSION attribute of a LOB data partition  

CACHE  

VARCHAR2(3)  

 

CACHE attribute of a LOB data partition  

IN_ROW  

VARCHAR2(3)  

 

IN_ROW attribute of a LOB data partition  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the LOB data partition  

INITIAL_EXTENT  

VARCHAR2(40)  

 

Size of the initial extent in bytes for a non-composite LOB data partition or size of the initial extent in blocks for a composite LOB data partition (to ascertain the number of bytes, query DBA_LOB_SUBPARTITIONS)  

NEXT_EXTENT  

VARCHAR2(40)  

 

Size of secondary extents in bytes for a LOB data partition or size of the secondary extents in blocks for a composite LOB data partition (to ascertain the number of bytes, query DBA_LOB_SUBPARTITIONS)  

MIN_EXTENTS  

VARCHAR2(40)  

 

Minimum number of extents allowed in the segment of a LOB data partition  

MAX_EXTENTS  

VARCHAR2(40)  

 

Maximum number of extents allowed in the segment of a LOB data partition  

PCT_INCREASE  

VARCHAR2(40)  

 

Percentage increase in extent size for a LOB data partition  

FREELISTS  

VARCHAR2(40)  

 

Number of process freelists allocated in the segment of a LOB data partition  

FREELIST_GROUPS  

VARCHAR2(40)  

 

Number of freelist groups allocated in the segment of a LOB data partition  

LOGGING  

VARCHAR2(7)  

 

The logging attribute of a LOB data partition  

BUFFER_POOL  

VARCHAR2(7)  

 

Buffer pool of a LOB data partition  

DBA_LOB_SUBPARTITIONS

This view displays partition-level attributes of LOB data subpartitions.

Column   Datatype   NULL   Description  

TABLE_OWNER  

VARCHAR2(30)  

 

Table owner  

TABLE_NAME  

VARCHAR2(30)  

 

Table name  

COLUMN_NAME  

VARCHAR2(30)  

 

Name of a LOB column  

LOB_NAME  

VARCHAR2(30)  

 

Name of a partitioned LOB item  

LOB_PARTITION_NAME  

VARCHAR2(30)  

 

Name of LOB data partition to which this LOB data subpartition belongs  

SUBPARTITION_NAME  

VARCHAR2(30)  

 

Name of a table subpartition to which this LOB subpartition corresponds  

LOB_INDSUBPART
_NAME  

VARCHAR2(30)  

 

Name of corresponding LOB index subpartition  

SUBPARTITION_POSITION  

NUMBER  

 

Position of the LOB data partition within the lOB item  

CHUNK  

NUMBER  

 

CHUNK attribute of a LOB data partition  

PCTVERSION  

NUMBER  

 

PCTVERSION attribute of a LOB data partition  

CACHE  

VARCHAR2(3)  

 

CACHE attribute of a LOB data partition  

IN_ROW  

VARCHAR2(3)  

 

IN_ROW attribute of a LOB data partition  

TABLESPACE_NAME  

VARCHAR2(30)  

 

Name of the tablespace containing the LOB data partition  

INITIAL_EXTENT  

NUMBER  

 

Size of the initial extent in bytes for a LOB data partition  

NEXT_EXTENT  

NUMBER  

 

Size of secondary extents in bytes for a LOB data partition  

MIN_EXTENTS  

NUMBER  

 

Minimum number of extents allowed in the segment of a LOB data partition  

MAX_EXTENTS  

NUMBER  

 

Maximum number of extents allowed in the segment of a LOB data partition  

PCT_INCREASE  

NUMBER  

 

Percentage increase in extent size for a LOB data partition  

FREELISTS  

NUMBER  

 

Number of process freelists allocated in the segment of a LOB data partition  

FREELIST_GROUPS  

NUMBER  

 

Number of freelist groups allocated in the segment of a LOB data partition  

LOGGING  

VARCHAR2(7)  

 

The logging attribute of a LOB data partition  

BUFFER_POOL  

VARCHAR2(7)  

 

Buffer pool of a LOB data partition  

DBA_LOCKS

This view lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

Column   Datatype   NULL   Description  

SESSION_ID  

NUMBER  

 

Session holding or acquiring the lock  

LOCK_TYPE  

VARCHAR2(26)  

 

Lock type  

MODE HELD  

VARCHAR2(40)  

 

Lock mode  

MODE REQUESTED  

VARCHAR2(40)  

 

Lock mode requested  

LOCK_ID1  

VARCHAR2(40)  

 

Type-specific lock identifier, part 1  

LOCK_ID2  

VARCHAR2(40)  

 

Type-specific lock identifier, part 2  

LAST_CONVERT  

NUMBER  

 

The last convert  

BLOCKING_OTHERS  

VARCHAR2(40)  

 

Blocking others  

DBA_LOCK_INTERNAL

This view has a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

Column   Datatype   NULL   Description  

SESSION_ID  

NUMBER  

 

Session holding or acquiring the lock  

LOCK_TYPE  

VARCHAR2(56)  

 

Lock type  

MODE HELD  

VARCHAR2(40)  

 

Lock mode  

MODE REQUESTED  

VARCHAR2(40)  

 

Lock mode requested  

LOCK_ID1  

VARCHAR2(1130)  

 

Type-specific lock identifier, part 1  

LOCK_ID2  

VARCHAR2(40)  

 

Type-specific lock identifier, part 2  

DBA_METHOD_PARAMS

This view is a description of method parameters of types in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

METHOD_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the method  

METHOD_NO  

NUMBER  

NOT NULL  

Method number for distinguishing overloaded method (not to be used as ID number)  

PARAM_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the parameter  

PARAM_NO  

NUMBER  

NOT NULL  

Parameter number or position  

PARAM_MODE  

VARCHAR2(6)  

 

Mode of the parameter  

PARAM_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the parameter  

PARAM_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the parameter  

PARAM_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the parameter  

CHARACTER_SET
_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS NCHAR_CS  

DBA_METHOD_RESULTS

This view is a description of method results of all types in the database.

Column   Datatype   NULL   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the type  

TYPE_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the type  

METHOD_NAME  

VARCHAR2(30)  

NOT NULL  

Name of the method  

METHOD_NO  

NUMBER  

NOT NULL  

The method number  

RESULT_TYPE_MOD  

VARCHAR2(7)  

 

Type modifier of the result  

RESULT_TYPE_OWNER  

VARCHAR2(30)  

 

Owner of the type of the result  

RESULT_TYPE_NAME  

VARCHAR2(30)  

 

Name of the type of the result  

CHARACTER_SET_NAME  

VARCHAR2(44)  

 

The name of the character set: CHAR_CS, NCHAR_CS  

DBA_MVIEW_AGGREGATES

This view represents the grouping functions (aggregated measures) that appear in the SELECT list of an aggregated materialized view.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view index  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

POSITION_IN_SELECT  

NUMBER  

NOT NULL  

Position of this measure within the SELECT list  

CONTAINER_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of this column in the container table  

AGG_FUNCTION  

VARCHAR2(8)  

 

Aggregation function  

DISTINCTFLAG  

VARCHAR2(1)  

 

Set to Y if DISTINCT aggregation  

MEASURE  

LONG  

 

Contains the SQL text of the measure, excluding the aggregation function. Equal to * for COUNT(*)  

DBA_MVIEW_ANALYSIS

This view represents the materialized views that potentially support query rewriting and that have additional information that is available for analysis by applications. This view excludes any materialized view that references a remote table or that includes a reference to a non-static value such as SYSDATE or USER. It does show all materialized views upon which Oracle would allow ENABLE QUERY REWRITE, not just those that are actually enabled for query rewrite.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

MVIEW_TABLE_OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view table object  

CONTAINER_NAME  

VARCHAR2(30)  

 

Name of the snapshot that the materialized view data is contained in  

LAST_REFRESH_SCN  

NUMBER  

 

The System Change Number  

LAST_REFRESH_DATE  

DATE  

 

SYSDATE of the last refresh  

REFRESH_METHOD  

VARCHAR2(8)  

 

FORCE, FAST, COMPLETE, or NONE  

SUMMARY  

VARCHAR2(1)  

 

Y if this materialized view includes a GROUP BY clause or aggregation, else N  

FULLREFRESHTIM  

NUMBER  

 

Approximate refresh time, in seconds (defined only when summary = Y for full refresh  

INCREFRESHTIM  

NUMBER  

 

Approximate refresh time, in seconds (defined only when summary = Y for full refresh  

CONTAINS_VIEWS  

VARCHAR2(1)  

 

Y if this materialized view contains a view in its definition, else N  

UNUSABLE  

VARCHAR2(1)  

 

Y if this materialized view is in an unusable state (inconsistent data), else N. A materialized view can be in an unusable state if a system failure occurs during a full refresh  

RESTRICTED_SYNTAX  

VARCHAR2(1)  

 

Y if this materialized view uses restricted materialized view syntax, else N  

INC_REFRESHABLE  

VARCHAR2(1)  

 

Y if this materialized view can be incrementally refreshed, else N  

KNOWN_STALE  

VARCHAR2(1)  

 

Y if the data contained in the materialized view is known to be inconsistent with the detail data, else N  

INVALID  

VARCHAR2(1)  

 

Y if this materialized view is in an invalid state (inconsistent metadata), else N  

QUERY_LEN  

NUMBER  

NOT NULL  

The length of the query field  

QUERY  

LONG  

NOT NULL  

SELECT expression of the materialized view definition  

REVISION  

NUMBER  

 

The materialized view metadata revision level  

DBA_MVIEW_DETAIL_RELATIONS

This view represents the named detail relations that are either in the FROM list of a materialized view, or that are indirectly referenced through views in the FROM list. Inline views in the materialized view definition are not represented in this table.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Detail object owner  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Detail object name, eg, the name of a table or view  

DETAILOBJ_TYPE  

VARCHAR2(9)  

 

TABLE, VIEW, MATERIALIZED VIEW, or SNAPSHOT  

DETAILOBJ_ALIAS  

VARCHAR2(30)  

 

Implicit or explicit alias for detail relation  

DBA_MVIEW_JOINS

This view represents a join between two columns in the WHERE clause of a materialized view.

Column   Datatype   Null   Description  

OWNER  

VARCHAR2(30)  

NOT NULL  

Owner of the materialized view  

MVIEW_NAME  

VARCHAR2(30)  

NOT NULL  

Materialized view name  

POSITION_IN_SELECT  

NUMBER  

NOT NULL  

Position of this key within the SELECT list  

CONTAINER_COLUMN  

VARCHAR2(30)  

NOT NULL  

The name of the column in the container table  

DETAILOBJ_OWNER  

VARCHAR2(30)  

NOT NULL  

Detail object owner  

DETAILOBJ_NAME  

VARCHAR2(30)  

NOT NULL  

Detail object name, eg, the name of a table or view  

DETAILOBJ_TYPE  

VARCHAR2(5)  

 

VIEW or TABLE  

DETAILOBJ_ALIAS  

VARCHAR2(30)  

 

Implicit or explicit alias for detail relation  

DETAILOBJ_COLUMN  

VARCHAR2(30)  

NOT NULL  

Name of the detail relation column  

DBA_MVIEW_KEYS

This view represents the named detail relations that are either in the FROM list of a materialized view, or that are indirectly referenced through views in the FROM list. Inline views in the materialized view definition are not represented in this table.

Column   Datatype   Null