ORACLE_系統字典常用表整理
dba_ 開頭 |
|
dba_users |
資料庫使用者資訊 |
dba_segments |
表段資訊 |
dba_objects |
資料庫物件資訊 |
dba_extents |
資料區資訊 |
dba_tablespaces |
資料庫表空間資訊 |
dba_data_files |
資料檔案設定資訊 |
dba_temp_files |
臨時資料檔案資訊 |
dba_rollback_segs |
回滾段資訊 |
dba_ts_quotas |
使用者表空間配額資訊 |
dba_free_space |
資料庫空閒空間資訊 |
dba_profiles |
資料庫使用者資源限制資訊 |
dba_sys_privs |
使用者的系統許可權資訊 |
dba_tab_privs |
使用者具有的物件許可權資訊 |
dba_col_privs |
使用者具有的列物件許可權資訊 |
dba_role_privs |
使用者具有的角色資訊 |
dba_audit_trail |
審計跟蹤記錄資訊 |
dba_stmt_audit_opts |
審計設定資訊 |
dba_audit_object |
物件審計結果資訊 |
dba_audit_session |
會話審計結果資訊 |
dba_indexes |
使用者模式的索引資訊 |
user_開頭 |
|
user_objects |
使用者物件資訊 |
user_source |
資料庫使用者的所有資源物件資訊 |
user_segments |
使用者的表段資訊 |
user_tables |
使用者的表物件資訊 |
user_tab_columns |
使用者的表列資訊 |
user_constraints |
使用者的物件約束資訊 |
user_sys_privs |
當前使用者的系統許可權資訊 |
user_tab_privs |
當前使用者的物件許可權資訊 |
user_col_privs |
當前使用者的表列許可權資訊 |
user_role_privs |
當前使用者的角色許可權資訊 |
user_indexes |
使用者的索引資訊 |
user_ind_columns |
使用者的索引對應的表列資訊 |
user_cons_columns |
使用者的約束對應的表列資訊 |
user_clusters |
使用者的所有簇資訊 |
user_clu_columns |
使用者的簇所包含的內容資訊 |
user_cluster_hash_expressions |
雜湊簇的資訊 |
v$ 開頭 |
|
v$database |
資料庫資訊 |
v$datafile |
資料檔案資訊 |
v$controlfile |
控制檔案資訊 |
v$logfile |
重做日誌資訊 |
v$instance |
資料庫例項資訊 |
v$log |
日誌組資訊 |
v$loghist |
日誌歷史資訊 |
v$sga |
資料庫SGA資訊 |
v$parameter |
初始化引數資訊 |
v$process |
資料庫伺服器程式資訊 |
v$bgprocess |
資料庫後臺程式資訊 |
v$controlfile_record_section |
控制檔案記載的各部分資訊 |
v$thread |
執行緒資訊 |
v$datafile_header |
資料檔案頭所記載的資訊 |
v$archived_log |
歸檔日誌資訊 |
v$archive_dest |
歸檔日誌的設定資訊 |
v$logmnr_contents |
歸檔日誌分析的DML DDL結果資訊 |
v$logmnr_dictionary |
日誌分析的字典檔案資訊 |
v$logmnr_logs |
日誌分析的日誌列表資訊 |
v$tablespace |
表空間資訊 |
v$tempfile |
臨時檔案資訊 |
v$filestat |
資料檔案的I/O統計資訊 |
v$undostat |
Undo資料資訊 |
v$rollname |
線上回滾段資訊 |
v$session |
會話資訊 |
v$transaction |
事務資訊 |
v$rollstat |
回滾段統計資訊 |
v$pwfile_users |
特權使用者資訊 |
v$sqlarea |
當前查詢過的sql語句訪問過的資源及相關的資訊 |
v$sql |
與v$sqlarea基本相同的相關資訊 |
v$sysstat |
資料庫系統狀態資訊 |
all_開頭 |
|
all_users |
資料庫所有使用者的資訊 |
all_objects |
資料庫所有的物件的資訊 |
all_def_audit_opts |
所有預設的審計設定資訊 |
all_tables |
所有的表物件資訊 |
all_indexes |
所有的資料庫物件索引的資訊 |
session_開頭 |
|
session_roles |
會話的角色資訊 |
session_privs |
會話的許可權資訊 |
index_開頭 |
|
index_stats |
索引的設定和儲存資訊 |
偽表 |
|
dual |
系統偽列表資訊 |
dba_users
Column |
Datatype |
NULL |
Description |
USERNAME |
VARCHAR2(30) |
NOT NULL |
Name of the user |
USER_ID |
NUMBER |
NOT NULL |
ID number of the user |
PASSWORD |
VARCHAR2(30) |
|
Encrypted password(加密) |
ACCOUNT_STATUS |
VARCHAR2(32) |
NOT NULL |
Account status:
|
LOCK_DATE |
DATE |
|
Date the account was locked if account status was LOCKED |
EXPIRY_DATE |
DATE |
|
Date of expiration of the account |
DEFAULT_TABLESPACE |
VARCHAR2(30) |
NOT NULL |
Default tablespace for data |
TEMPORARY_TABLESPACE |
VARCHAR2(30) |
NOT NULL |
Name of the default tablespace for temporary tables or the name of a tablespace group |
CREATED |
DATE |
NOT NULL |
User creation date |
PROFILE |
VARCHAR2(30) |
NOT NULL |
User resource profile name |
INITIAL_RSRC_CONSUMER_GROUP |
VARCHAR2(30) |
|
Initial resource consumer group for the user |
EXTERNAL_NAME |
VARCHAR2(4000) |
|
User external name |
dba_segments
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
|
Username of the segment owner |
SEGMENT_NAME |
VARCHAR2(81) |
|
Name, if any, of the segment |
PARTITION_NAME |
VARCHAR2(30) |
|
Object Partition Name (Set to NULL for non-partitioned objects) |
SEGMENT_TYPE |
VARCHAR2(18) |
|
Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX |
TABLESPACE_NAME |
VARCHAR2(30) |
|
Name of the tablespace containing the segment |
HEADER_FILE |
NUMBER |
|
ID of the file containing the segment header |
HEADER_BLOCK |
NUMBER |
|
ID of the block containing the segment header |
BYTES |
NUMBER |
|
Size, in bytes, of the segment |
BLOCKS |
NUMBER |
|
Size, in Oracle blocks, of the segment |
EXTENTS |
NUMBER |
|
Number of extents allocated to the segment |
INITIAL_EXTENT |
NUMBER |
|
Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.) |
NEXT_EXTENT |
NUMBER |
|
Size in bytes of the next extent to be allocated to the segment |
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 |
|
Percent by which to increase the size of the next extent to be allocated |
FREELISTS |
NUMBER |
|
Number of process freelists allocated to this segment |
FREELIST_GROUPS |
NUMBER |
|
Number of freelist groups allocated to this segment |
RELATIVE_FNO |
NUMBER |
|
Relative file number of the segment header |
BUFFER_POOL |
VARCHAR2(7) |
|
Default buffer pool for the object |
dba_objects
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the object |
OBJECT_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the object |
SUBOBJECT_NAME |
VARCHAR2(30) |
|
Name of the subobject (for example, partition) |
OBJECT_ID |
NUMBER |
NOT NULL |
Dictionary object number of the object |
DATA_OBJECT_ID |
NUMBER |
|
Dictionary object number of the segment that contains the object |
|
|
|
Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system. |
OBJECT_TYPE |
VARCHAR2(19) |
|
Type of the object (such as 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 statement (including grants and revokes) |
TIMESTAMP |
VARCHAR2(20) |
|
Timestamp for the specification of the object (character data) |
STATUS |
VARCHAR2(7) |
|
Status of the object (VALID, INVALID, or N/A) |
TEMPORARY |
VARCHAR2(1) |
|
Whether the object is temporary (the current session can see only data that it placed in this object itself) |
GENERATED |
VARCHAR2(1) |
|
Indicates whether the name of this object was system generated (Y) or not (N) |
SECONDARY |
VARCHAR2(1) |
|
Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N) |
dba_extents
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 |
PARTITION_NAME |
VARCHAR2(30) |
|
Object Partition Name (Set to NULL for non-partitioned objects) |
SEGMENT_TYPE |
VARCHAR2(18) |
|
Type of the segment: INDEX PARTITION, TABLE PARTITION |
TABLESPACE_NAME |
VARCHAR2(30) |
|
Name of the tablespace containing the extent |
EXTENT_ID |
NUMBER |
|
Extent number in the segment |
FILE_ID |
NUMBER |
|
File identifier number of the file containing the extent |
BLOCK_ID |
NUMBER |
|
Starting block number of the extent |
BYTES |
NUMBER |
|
Size of the extent in bytes |
BLOCKS |
NUMBER |
|
Size of the extent in Oracle blocks |
RELATIVE_FNO |
NUMBER |
|
Relative file number of the first extent block |
dba_tablespaces
Column |
Datatype |
NULL |
Description |
TABLESPACE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the tablespace |
BLOCK_SIZE |
NUMBER |
NOT NULL |
Tablespace block size |
INITIAL_EXTENT |
NUMBER |
|
Default initial extent size |
NEXT_EXTENT |
NUMBER |
|
Default incremental extent size |
MIN_EXTENTS |
NUMBER |
NOT NULL |
Default minimum number of extents |
MAX_EXTENTS |
NUMBER |
|
Default maximum number of extents |
PCT_INCREASE |
NUMBER |
|
Default percent increase for extent size |
MIN_EXTLEN |
NUMBER |
|
Minimum extent size for this tablespace |
STATUS |
VARCHAR2(9) |
|
Tablespace status:
|
CONTENTS |
VARCHAR2(9) |
|
Tablespace contents:
|
LOGGING |
VARCHAR2(9) |
|
Default logging attribute:
|
FORCE_LOGGING |
VARCHAR2(3) |
|
Indicates whether the tablespace is under force logging mode (YES) or not (NO) |
EXTENT_MANAGEMENT |
VARCHAR2(10) |
|
Indicates whether the extents in the tablespace are dictionary managed (DICTIONARY) or locally managed (LOCAL) |
ALLOCATION_TYPE |
VARCHAR2(9) |
|
Type of extent allocation in effect for the tablespace:
|
PLUGGED_IN |
VARCHAR2(3) |
|
Indicates whether the tablespace is plugged in (YES) or not (NO) |
SEGMENT_SPACE_MANAGEMENT |
VARCHAR2(6) |
|
Indicates whether the free and used segment space in the tablespace is managed using free lists (MANUAL) or bitmaps (AUTO) |
DEF_TAB_COMPRESSION |
VARCHAR2(8) |
|
Indicates whether default table compression is enabled (ENABLED) or not (DISABLED) Note: Enabling default table compression indicates that all tables in the tablespace will be created with table compression enabled unless otherwise specified. |
RETENTION |
VARCHAR2(11) |
|
Undo tablespace retention:
A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.
|
BIGFILE |
VARCHAR2(3) |
|
Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO) |
dba_data_files
Column |
Datatype |
NULL |
Description |
FILE_NAME |
VARCHAR2(513) |
|
Name of the database file |
FILE_ID |
NUMBER |
NOT NULL |
File identifier number 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 |
|
Number of tablespace blocks used as autoextension increment. Block size is contained in the BLOCK_SIZE column of the DBA_TABLESPACES view. |
USER_BYTES |
NUMBER |
|
The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata. |
USER_BLOCKS |
NUMBER |
|
Number of blocks which can be used by the data |
ONLINE_STATUS |
VARCHAR2(7) |
|
Online status of the file:
|
dba_temp_files
Column |
Datatype |
NULL |
Description |
FILE_NAME |
VARCHAR2(513) |
|
Name of the database temp file |
FILE_ID |
NUMBER |
|
File identifier number of the database temp 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 |
|
Size of the file (in Oracle blocks) |
STATUS |
CHAR(9) |
|
File status:
|
RELATIVE_FNO |
NUMBER |
|
Tablespace-relative file number |
AUTOEXTENSIBLE |
VARCHAR2(3) |
|
Indicates whether the file is autoextensible (YES) or not (NO) |
MAXBYTES |
NUMBER |
|
maximum size of the file (in bytes) |
MAXBLOCKS |
NUMBER |
|
Maximum size of the file (in Oracle blocks) |
INCREMENT_BY |
NUMBER |
|
Default increment for autoextension |
USER_BYTES |
NUMBER |
|
Size of the useful portion of the file (in bytes) |
USER_BLOCKS |
NUMBER |
|
Size of the useful portion of the file (in Oracle blocks) |
dba_rollback_segs
Column |
Datatype |
NULL |
Description |
SEGMENT_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the rollback segment |
OWNER |
VARCHAR2(6) |
|
Owner of the rollback segment |
TABLESPACE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the tablespace containing the rollback segment |
SEGMENT_ID |
NUMBER |
NOT NULL |
ID number of the rollback segment |
FILE_ID |
NUMBER |
NOT NULL |
File identifier number of the file containing the segment head |
BLOCK_ID |
NUMBER |
NOT NULL |
ID number of the block containing the segment header |
INITIAL_EXTENT |
NUMBER |
|
Initial extent size in bytes |
NEXT_EXTENT |
NUMBER |
|
Secondary extent size in bytes |
MIN_EXTENTS |
NUMBER |
NOT NULL |
Minimum number of extents |
MAX_EXTENTS |
NUMBER |
NOT NULL |
Maximum number of extent |
PCT_INCREASE |
NUMBER |
|
Percent increase for extent size |
STATUS |
VARCHAR2(16) |
|
Rollback segment status |
INSTANCE_NUM |
VARCHAR2(40) |
|
Rollback segment owning Real Application Clusters instance number |
RELATIVE_FNO |
NUMBER |
NOT NULL |
Relative file number of the segment header |
dba_ts_quotas
Column |
Datatype |
NULL |
Description |
TABLESPACE_NAME |
VARCHAR2(30) |
NOT NULL |
Tablespace name |
USERNAME |
VARCHAR2(30) |
NOT NULL |
User with resource rights on the tablespace |
BYTES |
NUMBER |
|
Number of bytes charged to the user |
MAX_BYTES |
NUMBER |
|
User's quota in bytes, or -1 if no limit |
BLOCKS |
NUMBER |
NOT NULL |
Number of Oracle blocks charged to the user |
MAX_BLOCKS |
NUMBER |
|
User's quota in Oracle blocks, or -1 if no limit |
DROPPED |
VARCHAR2(3) |
|
Whether the tablespace has been dropped |
dba_free_space
Column |
Datatype |
NULL |
Description |
TABLESPACE_NAME |
VARCHAR2(30) |
|
Name of the tablespace containing the extent |
FILE_ID |
NUMBER |
|
File identifier number of the file containing the extent |
BLOCK_ID |
NUMBER |
|
Starting block number of the extent |
BYTES |
NUMBER |
|
Size of the extent (in bytes) |
BLOCKS |
NUMBER |
|
Size of the extent (in Oracle blocks) |
RELATIVE_FNO |
NUMBER |
|
Relative file number of the file containing the extent |
dba_profiles
Column |
Datatype |
NULL |
Description |
PROFILE |
VARCHAR2(30) |
NOT NULL |
Profile name |
RESOURCE_NAME |
VARCHAR2(32) |
NOT NULL |
Resource name |
RESOURCE_TYPE |
VARCHAR2(8) |
|
Indicates whether the resource profile is a KERNEL or a PASSWORD parameter |
LIMIT |
VARCHAR2(40) |
|
Limit placed on this resource for this profile |
dba_sys_privs
Column |
Datatype |
NULL |
Description |
GRANTEE |
VARCHAR2(30) |
NOT NULL |
Grantee name, user, or role receiving the grant |
USERNAME |
VARCHAR2(30) |
|
Name of the current user |
PRIVILEGE |
VARCHAR2(40) |
NOT NULL |
System privilege |
ADMIN_OPTION |
VARCHAR2(3) |
|
Grant was with the ADMIN option |
dba_tab_privs
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) |
|
Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO) |
HIERARCHY |
VARCHAR2(3) |
|
Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO) |
dba_col_privs
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 |
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) |
|
Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO) |
dba_role_privs
Column |
Datatype |
NULL |
Description |
GRANTEE |
VARCHAR2(30) |
|
Name of the user or role receiving the grant |
GRANTED_ROLE |
VARCHAR2(30) |
NOT NULL |
Granted role name |
ADMIN_OPTION |
VARCHAR2(3) |
|
Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO) |
DEFAULT_ROLE |
VARCHAR2(3) |
|
Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) |
dba_audit_trail
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(128) |
|
Client host machine name |
TERMINAL |
VARCHAR2(255) |
|
Identifier of the user's terminal |
TIMESTAMP |
DATE |
|
Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone |
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 action type code. The corresponding name of the action type is in the ACTION_NAME column. |
ACTION_NAME |
VARCHAR2(28) |
|
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 the 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) |
|
Indicates whether the role or system privilege was granted with the ADMIN option |
GRANTEE |
VARCHAR2(30) |
|
Name of the 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:
|
LOGOFF_TIME |
DATE |
|
Date and time of 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:
|
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 error code generated by the action. Some useful values:
|
PRIV_USED |
VARCHAR2(40) |
|
System privilege used to execute the action |
CLIENT_ID |
VARCHAR2(64) |
|
Client identifier in each Oracle session |
ECONTEXT_ID |
VARCHAR2(64) |
|
Application execution context identifier |
SESSION_CPU |
NUMBER |
|
Amount of CPU time used by each Oracle session |
EXTENDED_TIMESTAMP |
TIMESTAMP(6) WITH TIME ZONE |
|
Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone |
PROXY_SESSIONID |
NUMBER |
|
Proxy session serial number, if an enterprise user has logged in through the proxy mechanism |
GLOBAL_UID |
VARCHAR2(32) |
|
Global user identifier for the user, if the user has logged in as an enterprise user |
INSTANCE_NUMBER |
NUMBER |
|
Instance number as specified by the INSTANCE_NUMBER initialization parameter |
OS_PROCESS |
VARCHAR2(16) |
|
Operating System process identifier of the Oracle process |
TRANSACTIONID |
RAW(8) |
|
Transaction identifier of the transaction in which the object is accessed or modified |
SCN |
NUMBER |
|
System change number (SCN) of the query |
SQL_BIND |
NVARCHAR2(2000) |
|
Bind variable data of the query |
SQL_TEXT |
NVARCHAR2(2000) |
|
SQL text of the query |
dba_stmt_audit_opts
Column |
Datatype |
NULL |
Description |
USER_NAME |
VARCHAR2(30) |
|
User name if by user auditing. ANY CLIENT if access by a proxy on behalf of a client is being audited. NULL for system-wide auditing |
PROXY_NAME |
VARCHAR2(30) |
|
The name of the proxy user which is performing an operation for the client. NULL if the client is performing the operation directly. |
AUDIT_OPTION |
VARCHAR2(40) |
NOT NULL |
Name of the system auditing option |
SUCCESS |
VARCHAR2(10) |
|
Mode for WHENEVER SUCCESSFUL system auditing |
FAILURE |
VARCHAR2(10) |
|
Mode for WHENEVER NOT SUCCESSFUL system auditing |
dba_audit_object
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(128) |
|
Client host machine name |
TERMINAL |
VARCHAR2(255) |
|
Identifier of the user's terminal |
TIMESTAMP |
DATE |
|
Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone |
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(28) |
|
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 error code generated by the action. Some useful values: · 0 - Action succeeded · 2004 - Security violation |
PRIV_USED |
VARCHAR2(40) |
|
System privilege used to execute the action |
CLIENT_ID |
VARCHAR2(64) |
|
Client identifier in each Oracle session |
SESSION_CPU |
NUMBER |
|
Amount of CPU time used by each Oracle session |
EXTENDED_TIMESTAMP |
TIMESTAMP(6) WITH TIME ZONE |
|
Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone |
PROXY_SESSIONID |
NUMBER |
|
Proxy session serial number, if an enterprise user has logged in through the proxy mechanism |
GLOBAL_UID |
VARCHAR2(32) |
|
Global user identifier for the user, if the user has logged in as an enterprise user |
INSTANCE_NUMBER |
NUMBER |
|
Instance number as specified by the INSTANCE_NUMBER initialization parameter |
OS_PROCESS |
VARCHAR2(16) |
|
Operating System process identifier of the Oracle process |
TRANSACTIONID |
RAW(8) |
|
Transaction identifier of the transaction in which the object is accessed or modified |
SCN |
NUMBER |
|
System change number (SCN) of the query |
SQL_BIND |
NVARCHAR2(2000) |
|
Bind variable data of the query |
SQL_TEXT |
NVARCHAR2(2000) |
|
SQL text of the query |
dba_audit_session
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(128) |
|
Client host machine name |
TERMINAL |
VARCHAR2(255) |
|
Identifier of the user's terminal |
TIMESTAMP |
DATE |
|
Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone |
ACTION_NAME |
VARCHAR2(28) |
|
Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL |
LOGOFF_TIME |
DATE |
|
Date and time of 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 error code generated by the action. Some useful values: · 0 - Action succeeded · 2004 - Security violation |
CLIENT_ID |
VARCHAR2(64) |
|
Client identifier in each Oracle session |
SESSION_CPU |
NUMBER |
|
Amount of CPU time used by each Oracle session |
EXTENDED_TIMESTAMP |
TIMESTAMP(6) WITH TIME ZONE |
|
Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone |
PROXY_SESSIONID |
NUMBER |
|
Proxy session serial number, if an enterprise user has logged in through the proxy mechanism |
GLOBAL_UID |
VARCHAR2(32) |
|
Global user identifier for the user, if the user has logged in as an enterprise user |
INSTANCE_NUMBER |
NUMBER |
|
Instance number as specified by the INSTANCE_NUMBER initialization parameter |
OS_PROCESS |
VARCHAR2(16) |
|
Operating System process identifier of the Oracle process |
dba_indexes
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the index |
INDEX_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the index |
INDEX_TYPE |
VARCHAR2(27) |
|
Type of the 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 (for example, TABLE, CLUSTER) |
UNIQUENESS |
VARCHAR2(9) |
|
Indicates whether the index is UNIQUE or NONUNIQUE |
COMPRESSION |
VARCHAR2(8) |
|
Indicates whether index compression is enabled (ENABLED) or not (DISABLED) |
PREFIX_LENGTH |
NUMBER |
|
Number of columns in the prefix of the compression key |
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 |
|
Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to the COLUMN_ID column of the *_TAB_COLUMNS data dictionary views. |
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 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, 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 rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns. |
CLUSTERING_FACTOR* |
NUMBER |
|
Indicates the amount of order of the rows in the table based on the values of the index.
For bitmap indexes, this column is not applicable and is not used. |
STATUS |
VARCHAR2(8) |
|
Indicates whether a nonpartitioned index is VALID or UNUSABLE |
NUM_ROWS |
NUMBER |
|
Number of rows in the index |
SAMPLE_SIZE |
NUMBER |
|
Size of the sample used to analyze the index |
LAST_ANALYZED |
DATE |
|
Date on which this index was most recently analyzed |
DEGREE |
VARCHAR2(40) |
|
Number of threads per instance for scanning the index |
INSTANCES |
VARCHAR2(40) |
|
Number of instances across which the indexes to be scanned |
PARTITIONED |
VARCHAR2(3) |
|
Indicates whether the index is partitioned (YES) or not (NO) |
TEMPORARY |
VARCHAR2(1) |
|
Indicates whether the index is on a temporary table |
GENERATED |
VARCHAR2(1) |
|
Indicates whether the name of the index is system generated (Y) or not (N) |
SECONDARY |
VARCHAR2(1) |
|
Indicates whether the index is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) |
BUFFER_POOL |
VARCHAR2(7) |
|
Name of the default buffer pool to be used for the index blocks |
USER_STATS |
VARCHAR2(3) |
|
Indicates whether statistics were entered directly by the user (YES) or not (NO) |
DURATION |
VARCHAR2(15) |
|
Indicates the duration of a temporary table:
Null for a permanent table |
PCT_DIRECT_ACCESS |
NUMBER |
|
For a secondary index on an index-organized table, the percentage of rows with VALID guess |
ITYP_OWNER |
VARCHAR2(30) |
|
For a domain index, the owner of the indextype |
ITYP_NAME |
VARCHAR2(30) |
|
For a domain index, the name of the indextype |
PARAMETERS |
VARCHAR2(1000) |
|
For a domain index, the parameter string |
GLOBAL_STATS |
VARCHAR2(3) |
|
For partitioned indexes, indicates whether statistics were collected by analyzing the index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO) |
DOMIDX_STATUS |
VARCHAR2(12) |
|
Status of the domain index:
|
DOMIDX_OPSTATUS |
VARCHAR2(6) |
|
Status of the operation on the domain index:
|
FUNCIDX_STATUS |
VARCHAR2(8) |
|
Status of a function-based index:
|
JOIN_INDEX |
VARCHAR2(3) |
|
Indicates whether the index is a join index (YES) or not (NO) |
IOT_REDUNDANT_PKEY_ELIM |
VARCHAR2(3) |
|
Indicates whether redundant primary key columns are eliminated from secondary indexes on index-organized tables (YES) or not (NO) |
DROPPED |
VARCHAR2(3) |
|
Indicates whether the index has been dropped and is in the recycle bin (YES) or not (NO); null for partitioned tables |
user_objects
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the object |
OBJECT_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the object |
SUBOBJECT_NAME |
VARCHAR2(30) |
|
Name of the subobject (for example, partition) |
OBJECT_ID |
NUMBER |
NOT NULL |
Dictionary object number of the object |
DATA_OBJECT_ID |
NUMBER |
|
Dictionary object number of the segment that contains the object |
|
|
|
Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system. |
OBJECT_TYPE |
VARCHAR2(19) |
|
Type of the object (such as 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 statement (including grants and revokes) |
TIMESTAMP |
VARCHAR2(20) |
|
Timestamp for the specification of the object (character data) |
STATUS |
VARCHAR2(7) |
|
Status of the object (VALID, INVALID, or N/A) |
TEMPORARY |
VARCHAR2(1) |
|
Whether the object is temporary (the current session can see only data that it placed in this object itself) |
GENERATED |
VARCHAR2(1) |
|
Was the name of this object system generated? (Y|N) |
SECONDARY |
VARCHAR2(1) |
|
Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N) |
user_source
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: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY |
LINE |
NUMBER |
NOT NULL |
Line number of this line of source |
TEXT |
VARCHAR2(4000) |
|
Text source of the stored object |
user_segments
USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. Its columns (except for OWNER, HEADER_FILE, HEADER_BLOCK, and RELATIVE_FNO) are the same as those in DBA_SEGMENTS.
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
|
Username of the segment owner |
SEGMENT_NAME |
VARCHAR2(81) |
|
Name, if any, of the segment |
PARTITION_NAME |
VARCHAR2(30) |
|
Object Partition Name (Set to NULL for non-partitioned objects) |
SEGMENT_TYPE |
VARCHAR2(18) |
|
Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX |
TABLESPACE_NAME |
VARCHAR2(30) |
|
Name of the tablespace containing the segment |
HEADER_FILE |
NUMBER |
|
ID of the file containing the segment header |
HEADER_BLOCK |
NUMBER |
|
ID of the block containing the segment header |
BYTES |
NUMBER |
|
Size in bytes, of the segment |
BLOCKS |
NUMBER |
|
Size, in Oracle blocks, of the segment |
EXTENTS |
NUMBER |
|
Number of extents allocated to the segment |
INITIAL_EXTENT |
NUMBER |
|
Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.) |
NEXT_EXTENT |
NUMBER |
|
Size in bytes of the next extent to be allocated to the segment |
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 |
|
Percent by which to increase the size of the next extent to be allocated |
FREELISTS |
NUMBER |
|
Number of process freelists allocated to this segment |
FREELIST_GROUPS |
NUMBER |
|
Number of freelist groups allocated to this segment |
RELATIVE_FNO |
NUMBER |
|
Relative file number of the segment header |
BUFFER_POOL |
VARCHAR2(7) |
|
Default buffer pool for the object |
user_tables
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) |
|
Name of the tablespace containing the table; null for partitioned, temporary, and index-organized 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 or mapping table entry belongs. If the IOT_TYPE column is not null, then this column contains the base table name. |
STATUS |
VARCHAR2(8) |
|
If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID) |
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 secondary extents (in 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 the segment; null for partitioned tables |
FREELIST_GROUPS |
NUMBER |
|
Number of freelist groups allocated to the 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 |
|
Average freespace of all blocks on a freelist |
NUM_FREELIST_BLOCKS |
NUMBER |
|
Number of blocks on the freelist |
DEGREE |
VARCHAR2(10) |
|
Number of threads per instance for scanning the table |
INSTANCES |
VARCHAR2(10) |
|
Number of instances across which the table is to be scanned |
CACHE |
VARCHAR2(5) |
|
Indicates whether the table is to be cached in the buffer cache (Y) or not (N) |
TABLE_LOCK |
VARCHAR2(8) |
|
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) |
SAMPLE_SIZE |
NUMBER |
|
Sample size used in analyzing this table |
LAST_ANALYZED |
DATE |
|
Date on which this table was most recently 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, IOT_OVERFLOW, or IOT_MAPPING. 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) |
|
Whether the trigger is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y |N) |
NESTED |
VARCHAR2(3) |
|
Indicates whether the table is a nested table (YES) or not (NO) |
BUFFER_POOL |
VARCHAR2(7) |
|
The default buffer pool for the object. NULL for partitioned tables |
ROW_MOVEMENT |
VARCHAR2(8) |
|
Whether partitioned row movement is enabled or disabled |
GLOBAL_STATS |
VARCHAR2(3) |
|
For partitioned tables, indicates whether statistics were collected for the table as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (NO) |
USER_STATS |
VARCHAR2(3) |
|
Indicates whether statistics were entered directly by the user (YES) or not (NO) |
DURATION |
VARCHAR2(15) |
|
Indicates the duration of a temporary table:
Null for a permanent table |
SKIP_CORRUPT |
VARCHAR2(8) |
|
Whether the Oracle Database ignores blocks marked corrupt during table and index scans (ENABLED) or raises an error (DISABLED). To enable this feature, run the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure. |
MONITORING |
VARCHAR2(3) |
|
Whether the table has the MONITORING attribute set |
CLUSTER_OWNER |
VARCHAR2(30) |
|
Owner of the cluster, if any, to which the table belongs |
DEPENDENCIES |
VARCHAR2(8) |
|
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) |
COMPRESSION |
VARCHAR2(8) |
|
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); null for partitioned tables |
DROPPED |
VARCHAR2(3) |
|
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); null for partitioned tables |
user_tab_columns
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the table, view, or cluster |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the table, view, or cluster |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Column name |
DATA_TYPE |
VARCHAR2(106) |
|
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 |
|
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 |
|
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 the histogram for the column Note: The number of buckets in a histogram is specified in the SIZE parameter of the SQL statement ANALYZE. However, the Oracle Database does not create a histogram with more buckets than the number of rows in the sample. Also, if the sample contains any values that are very repetitious, the Oracle Database creates the specified number of buckets, but the value indicated by this column may be smaller because of an internal compression algorithm. |
LAST_ANALYZED |
DATE |
|
Date on which this column was most recently analyzed |
SAMPLE_SIZE |
NUMBER |
|
Sample size used in analyzing this column |
CHARACTER_SET_NAME |
VARCHAR2(44) |
|
Name of the character set: CHAR_CS or NCHAR_CS |
CHAR_COL_DECL_LENGTH |
NUMBER |
|
Length |
GLOBAL_STATS |
VARCHAR2(3) |
|
For partitioned tables, indicates whether column statistics were collected for the table as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (NO) |
USER_STATS |
VARCHAR2(3) |
|
Indicates whether statistics were entered directly by the user (YES) or not (NO) |
AVG_COL_LEN |
NUMBER |
|
Average length of the column (in bytes) |
CHAR_LENGTH |
NUMBER |
|
Displays the length of the column in characters. This value only applies to the following datatypes: · CHAR · VARCHAR2 · NCHAR · NVARCHAR |
CHAR_USED |
VARCHAR2(1) |
|
B | C. B indicates that the column uses BYTE length semantics. C indicates that the column uses CHAR length semantics. NULL indicates the datatype is not any of the following: · CHAR · VARCHAR2 · NCHAR · NVARCHAR2 |
V80_FMT_IMAGE |
VARCHAR2(3) |
|
Indicates whether the column data is in release 8.0 image format (YES) or not (NO) |
DATA_UPGRADED |
VARCHAR2(3) |
|
Indicates whether the column data has been upgraded to the latest type version format (YES) or not (NO) |
HISTOGRAM |
VARCHAR2(15) |
|
Indicates existence/type of histogram: · NONE · FREQUENCY · HEIGHT BALANCED |
user_constraints
USER_CONSTRAINTS describes all constraint definitions on tables owned by the current user. Its columns are the same as those in .
user_sys_privs
USER_SYS_PRIVS lists system privileges granted to the current user. Its columns are the same as those in .
user_tab_privs
USER_TAB_PRIVS describes the object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.
user_col_privs
USER_COL_PRIVS describes the column object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_COL_PRIVS.
user_role_privs
Column |
Datatype |
NULL |
Description |
USERNAME |
VARCHAR2(30) |
|
Name of the user, or PUBLIC |
GRANTED_ROLE |
VARCHAR2(30) |
|
Name of the role granted to the user |
ADMIN_OPTION |
VARCHAR2(3) |
|
Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO) |
DEFAULT_ROLE |
VARCHAR2(3) |
|
Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) |
OS_GRANTED |
VARCHAR2(3) |
|
Granted by the operating system (YES) or not (NO); occurs if configuration parameter OS_ROLES = true. |
user_indexes
USER_INDEXES describes indexes owned by the current user. To gather statistics for this view, use the SQL ANALYZE statement. This view supports parallel partitioned index scans. Its columns (except for OWNER) are the same as those in .
user_ind_columns
USER_IND_COLUMNS describes the columns of the indexes owned by the current user and columns of indexes on tables owned by the current user. Its columns are the same as those in .
user_cons_columns
USER_CONS_COLUMNS describes columns that are owned by the current user and that are specified in constraint definitions. Its columns are the same as those in .
user_clusters
USER_CLUSTERS describes all the clusters owned by the current user. Its columns are the same as those in .
user_clu_columns
USER_CLU_COLUMNS maps columns in the current user's tables to cluster columns. Its columns are the same as those in .
user_cluster_hash_expressions
USER_CLUSTER_HASH_EXPRESSIONS lists hash functions for the hash clusters owned by the current user. Its columns are the same as those in .
v$database
Column |
Datatype |
Description |
DBID |
NUMBER |
Database identifier calculated when the database is created and stored in all file headers |
NAME |
VARCHAR2(9) |
Name of the database |
CREATED |
DATE |
Creation date of the database |
RESETLOGS_CHANGE# |
NUMBER |
System change number (SCN) at open resetlogs |
RESETLOGS_TIME |
DATE |
Timestamp of open resetlogs |
PRIOR_RESETLOGS_CHANGE# |
NUMBER |
SCN at prior resetlogs |
PRIOR_RESETLOGS_TIME |
DATE |
Timestamp of prior resetlogs |
LOG_MODE |
VARCHAR2(12) |
Archive log mode:
|
CHECKPOINT_CHANGE# |
NUMBER |
Last SCN checkpointed |
ARCHIVE_CHANGE# |
NUMBER |
Database force archiving SCN. Any redo log with a start SCN below this will be forced to archive out. |
CONTROLFILE_TYPE |
VARCHAR2(7) |
Type of control file:
|
CONTROLFILE_CREATED |
DATE |
Creation date of the control file |
CONTROLFILE_SEQUENCE# |
NUMBER |
Control file sequence number incremented by control file transactions |
CONTROLFILE_CHANGE# |
NUMBER |
Last SCN in backup control file; null if the control file is not a backup |
CONTROLFILE_TIME |
DATE |
Last timestamp in backup control file; null if the control file is not a backup |
OPEN_RESETLOGS |
VARCHAR2(11) |
(NOT ALLOWED | ALLOWED | REQUIRED) Indicates whether the next database open allows or requires the resetlogs option |
VERSION_TIME |
DATE |
Version time |
OPEN_MODE |
VARCHAR2(10) |
Open mode information:
|
PROTECTION_MODE |
VARCHAR2(20) |
Protection mode currently in effect for the database:
|
PROTECTION_LEVEL |
VARCHAR2(20) |
Aggregated protection mode currently in effect for the database:
Note: This column is an aggregation of the PROTECTION_MODE of all standby archive log destinations. |
REMOTE_ARCHIVE |
VARCHAR2(8) |
Value of the REMOTE_ARCHIVE_ENABLE initialization parameter |
ACTIVATION# |
NUMBER |
Number assigned to the database instantiation |
SWITCHOVER# |
NUMBER |
Number assigned to the database switchover |
DATABASE_ROLE |
VARCHAR2(16) |
Current role of the database:
|
ARCHIVELOG_CHANGE# |
NUMBER |
Highest NEXT_CHANGE# (from the V$ARCHIVED_LOG view) for an archive log |
ARCHIVELOG_COMPRESSION |
VARCHAR2(8) |
Status of the archive log compression (ENABLED) or (DISABLED) |
SWITCHOVER_STATUS |
VARCHAR2(20) |
Indicates whether switchover is allowed:
|
DATAGUARD_BROKER |
VARCHAR2(8) |
Indicates whether the Data Guard configuration is being managed by the broker (ENABLED) or not (DISABLED) |
GUARD_STATUS |
VARCHAR2(7) |
Protects data from being changed:
|
SUPPLEMENTAL_LOG_DATA_MIN |
VARCHAR2(8) |
Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables:
See Also: for additional information about the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement |
SUPPLEMENTAL_LOG_DATA_PK |
VARCHAR2(3) |
For all tables with a primary key, indicates whether all columns of the primary key are placed into the redo log whenever an update is performed (YES) or not (NO) See Also: for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement |
SUPPLEMENTAL_LOG_DATA_UI |
VARCHAR2(3) |
For all tables with a unique key, indicates whether all other columns belonging to the unique key are placed into the redo log if any of the unique key columns are modified (YES) or not (NO) See Also: for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement |
FORCE_LOGGING |
VARCHAR2(3) |
Indicates whether the database is under force logging mode (YES) or not (NO) |
PLATFORM_ID |
NUMBER |
Platform identification number of the database |
PLATFORM_NAME |
VARCHAR2(101) |
Platform name of the database |
RECOVERY_TARGET_INCARNATION# |
NUMBER |
Incarnation number where all datafiles are recovered by the RECOVER DATABASE command |
LAST_OPEN_INCARNATION# |
NUMBER |
Record number of the incarnation in V$DATABASE_INCARNATION that was last opened successfully |
CURRENT_SCN |
NUMBER |
Current SCN; null if the database is not currently open. For a standby database, the current standby redo application SCN. |
FLASHBACK_ON |
VARCHAR2(18) |
Possible values are as follows:
|
SUPPLEMENTAL_LOG_DATA_FK |
VARCHAR2(3) |
For all tables with a foreign key, indicates whether all other columns belonging to the foreign key are placed into the redo log if any foreign key columns are modified (YES) or not (NO) See Also: for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement |
SUPPLEMENTAL_LOG_DATA_ALL |
VARCHAR2(3) |
For all columns, indicates whether all the fixed-length maximum size columns of that row are placed into the redo log (YES) or not (NO) See Also: for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement |
DB_UNIQUE_NAME |
VARCHAR2(30) |
Unique database name |
STANDBY_BECAME_PRIMARY_SCN |
NUMBER |
SCN at which a physical standby database became a primary database. This SCN is useful for converting a failed primary database into a physical standby database after a forced failover. See Also: |
FS_FAILOVER_STATUS |
VARCHAR2(21) |
Fast-start failover status:
See Also: for detailed descriptions of these values |
FS_FAILOVER_CURRENT_TARGET |
VARCHAR2(30) |
DB_UNIQUE_NAME of the standby that is the current FSFO target standby for the Data Guard configuration |
FS_FAILOVER_THRESHOLD |
NUMBER |
Time (in seconds) that the observer will attempt to reconnect with a disconnected primary before attempting FSFO with the target standby |
FS_FAILOVER_OBSERVER_PRESENT |
VARCHAR2(7) |
Indicates whether the observer is currently connected to the local database (YES) or not (NO) Note: This column is consistent throughout an Oracle RAC environment; that is, if the observer is connected to any instance, then all instances will show a value of YES. |
FS_FAILOVER_OBSERVER_HOST |
VARCHAR2(512) |
Machine name that is currently hosting the observer process |
CONTROLFILE_CONVERTED |
VARCHAR2(3) |
??? (YES) or (NO) |
PRIMARY_DB_UNIQUE_NAME |
VARCHAR2(30) |
For any Standby database (Physical, Logical, or Reporting), this column will contain the DB_UNIQUE_NAME of the Primary database that this Standby last received current redo from. If this standby has not received any current redo since last being started, then this column will be null. For a Primary database that had previously been a Standby, this column will contain the DB_UNIQUE_NAME of the last Primary that this database received current redo from while acting as a Standby. For a Primary database that has never been a Standby, this column will be null. |
SUPPLEMENTAL_LOG_DATA_PL |
VARCHAR2(3) |
??? (YES) or (NO) |
MIN_REQUIRED_CAPTURE_CHANGE# |
VARCHAR2(40) |
Minimum REQUIRED_CHECKPOINT_SCN for all local capture processes on the database |
v$datafile
Column |
Datatype |
Description |
FILE# |
NUMBER |
File identification number |
CREATION_CHANGE# |
NUMBER |
Change number at which the datafile was created |
CREATION_TIME |
DATE |
Timestamp of the datafile creation |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Tablespace relative datafile number |
STATUS |
VARCHAR2(7) |
Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace) |
ENABLED |
VARCHAR2(10) |
Describes how accessible the file is from SQL:
|
CHECKPOINT_CHANGE# |
NUMBER |
SCN at last checkpoint |
CHECKPOINT_TIME |
DATE |
Timestamp of the checkpoint# |
UNRECOVERABLE_CHANGE# |
NUMBER |
Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated. |
UNRECOVERABLE_TIME |
DATE |
Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode. |
LAST_CHANGE# |
NUMBER |
Last change number made to this datafile (null if the datafile is being changed) |
LAST_TIME |
DATE |
Timestamp of the last change |
OFFLINE_CHANGE# |
NUMBER |
Offline change number of the last offline range. This column is updated only when the datafile is brought online. |
ONLINE_CHANGE# |
NUMBER |
Online change number of the last offline range |
ONLINE_TIME |
DATE |
Online timestamp of the last offline range |
BYTES |
NUMBER |
Current datafile size (in bytes); 0 if inaccessible |
BLOCKS |
NUMBER |
Current datafile size (in blocks); 0 if inaccessible |
CREATE_BYTES |
NUMBER |
Size when created (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size of the datafile |
NAME |
VARCHAR2(513) |
Name of the datafile |
PLUGGED_IN |
NUMBER |
Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write, 0 if not. |
BLOCK1_OFFSET |
NUMBER |
Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows: BYTES + BLOCK1_OFFSET. |
AUX_NAME |
VARCHAR2(513) |
Auxiliary name that has been set for this file via CONFIGURE AUXNAME |
FIRST_NONLOGGED_SCN |
NUMBER |
First nonlogged SCN |
FIRST_NONLOGGED_TIME |
DATE |
First nonlogged time |
v$controlfile
Column |
Datatype |
Description |
STATUS |
VARCHAR2(7) |
INVALID if the name cannot be determined (which should not occur); NULL if the name can be determined |
NAME |
VARCHAR2(513) |
Name of the control file |
IS_RECOVERY_DEST_FILE |
VARCHAR2(3) |
Indicates whether the file was created in the flash recovery area (YES) or not (NO) |
BLOCK_SIZE |
NUMBER |
Control file block size |
FILE_SIZE_BLKS |
NUMBER |
Control file size (in blocks) |
v$logfile
Column |
Datatype |
Description |
GROUP# |
NUMBER |
Redo log group identifier number |
STATUS |
VARCHAR2(7) |
Status of the log member: · INVALID - File is inaccessible · STALE - File's contents are incomplete · DELETED - File is no longer used · null - File is in use |
TYPE |
VARCHAR2(7) |
Type of the logfile: · ONLINE · STANDBY |
MEMBER |
VARCHAR2(513) |
Redo log member name |
IS_RECOVERY_DEST_FILE |
VARCHAR2(3) |
Indicates whether the file was created in the flash recovery area (YES) or not (NO) |
v$instance
Column |
Datatype |
Description |
INSTANCE_NUMBER |
NUMBER |
Instance number used for instance registration (corresponds to the INSTANCE_NUMBER initialization parameter) See Also: |
INSTANCE_NAME |
VARCHAR2(16) |
Name of the instance |
HOST_NAME |
VARCHAR2(64) |
Name of the host machine |
VERSION |
VARCHAR2(17) |
Database version |
STARTUP_TIME |
DATE |
Time when the instance was started |
STATUS |
VARCHAR2(12) |
Status of the instance:
|
PARALLEL |
VARCHAR2(3) |
Indicates whether the instance is mounted in cluster database mode (YES) or not (NO) |
THREAD# |
NUMBER |
Redo thread opened by the instance |
ARCHIVER |
VARCHAR2(7) |
(STOPPED | STARTED | FAILED) FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes |
LOG_SWITCH_WAIT |
VARCHAR2(11) |
The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then value is NULL |
LOGINS |
VARCHAR2(10) |
ALLOWED | RESTRICTED |
SHUTDOWN_PENDING |
VARCHAR2(3) |
YES | NO |
DATABASE_STATUS |
VARCHAR2(17) |
Status of the database |
INSTANCE_ROLE |
VARCHAR2(18) |
Describes whether the instance is an active instance (PRIMARY_INSTANCE) or an inactive secondary instance (SECONDARY_INSTANCE), or UNKNOWN if the instance has been started but not mounted |
ACTIVE_STATE |
VARCHAR2(9) |
(NORMAL|QUIESCING|QUIESCED). NORMAL indicates the database is in a normal state. QUIESCING indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected. QUIESCED indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed. Note that a single ALTER SYSTEM QUIESCE RESTRICTED statement quiesces all instances in a Real Application Clusters environment. After this statement has been issued, some instances may enter into a quiesced state before other instances; the system is quiesced when all instances enter the quiesced state. |
v$log
Column |
Datatype |
Description |
GROUP# |
NUMBER |
Log group number |
THREAD# |
NUMBER |
Log thread number |
SEQUENCE# |
NUMBER |
Log sequence number |
BYTES |
NUMBER |
Size of the log (in bytes) |
MEMBERS |
NUMBER |
Number of members in the log group |
ARCHIVED |
VARCHAR2(3) |
Archive status (YES or NO) |
STATUS |
VARCHAR2(16) |
Log status: · UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log. · CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed. · ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. · CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED. · CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. · INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. · INVALIDATED - Archived the current redo log without a log switch. |
FIRST_CHANGE# |
NUMBER |
Lowest system change number (SCN) in the log |
FIRST_TIME |
DATE |
Time of the first SCN in the log |
v$loghist
Column |
Datatype |
Description |
THREAD# |
NUMBER |
Log thread number |
SEQUENCE# |
NUMBER |
Log sequence number |
FIRST_CHANGE# |
NUMBER |
Lowest SCN in the log |
FIRST_TIME |
DATE |
Time of first SCN in the log |
SWITCH_CHANGE# |
NUMBER |
SCN at which the log switch occurred; one more than highest SCN in the log |
v$sga
Column |
Datatype |
Description |
NAME |
VARCHAR2(20) |
SGA component group |
VALUE |
NUMBER |
Memory size (in bytes) |
v$parameter
Column |
Datatype |
Description |
NUM |
NUMBER |
Parameter number |
NAME |
VARCHAR2(80) |
Name of the parameter |
TYPE |
NUMBER |
Parameter type:
|
VALUE |
VARCHAR2(4000) |
Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value |
DISPLAY_VALUE |
VARCHAR2(4000) |
Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K. |
ISDEFAULT |
VARCHAR2(9) |
Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE) |
ISSES_MODIFIABLE |
VARCHAR2(5) |
Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE) |
ISSYS_MODIFIABLE |
VARCHAR2(9) |
Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
|
ISINSTANCE_MODIFIABLE |
VARCHAR2(5) |
For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE. |
ISMODIFIED |
VARCHAR2(10) |
Indicates whether the parameter has been modified after instance startup:
|
ISADJUSTED |
VARCHAR2(5) |
Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number) |
ISDEPRECATED |
VARCHAR2(5) |
Indicates whether the parameter has been deprecated (TRUE) or not (FALSE) |
ISBASIC |
VARCHAR2(5) |
Indicates whether the parameter is a basic parameter (TRUE) or not (FALSE) |
DESCRIPTION |
VARCHAR2(255) |
Description of the parameter |
UPDATE_COMMENT |
VARCHAR2(255) |
Comments associated with the most recent update |
HASH |
NUMBER |
Hash value for the parameter name |
v$process
Column |
Datatype |
Description |
ADDR |
RAW(4 | 8) |
Address of the process state object |
PID |
NUMBER |
Oracle process identifier |
SPID |
VARCHAR2(24) |
Operating system process identifier |
PNAME |
VARCHAR2(5) |
Name of this process |
USERNAME |
VARCHAR2(15) |
Operating system process username Note: Any two-task user coming across the network has "-T" appended to the username. |
SERIAL# |
NUMBER |
Process serial number |
TERMINAL |
VARCHAR2(30) |
Operating system terminal identifier |
PROGRAM |
VARCHAR2(48) |
Program in progress |
TRACEID |
VARCHAR2(255) |
Trace file identifier |
TRACEFILE |
VARCHAR2(513) |
Trace file name of the process |
BACKGROUND |
VARCHAR2(1) |
1 for a background process; NULL for a normal process |
LATCHWAIT |
VARCHAR2(8) |
Address of the latch the process is waiting for; NULL if none |
LATCHSPIN |
VARCHAR2(8) |
Address of the latch the process is spinning on; NULL if none |
PGA_USED_MEM |
NUMBER |
PGA memory currently used by the process |
PGA_ALLOC_MEM |
NUMBER |
PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process) |
PGA_FREEABLE_MEM |
NUMBER |
Allocated PGA memory which can be freed |
PGA_MAX_MEM |
NUMBER |
Maximum PGA memory ever allocated by the process |
v$bgprocess
Column |
Datatype |
Description |
PADDR |
RAW(4 | 8) |
Address of the process state object |
PSERIAL# |
NUMBER |
Process state object serial number |
NAME |
VARCHAR2(5) |
Name of this background process |
DESCRIPTION |
VARCHAR2(64) |
Description of the background process |
ERROR |
NUMBER |
Error encountered |
v$controlfile_record_section
Column |
Datatype |
Description |
TYPE |
VARCHAR2(28) |
Identifies the type of record section:
|
RECORD_SIZE |
NUMBER |
Record size in bytes |
RECORDS_TOTAL |
NUMBER |
Number of records allocated for the section |
RECORDS_USED |
NUMBER |
Number of records used in the section |
FIRST_INDEX |
NUMBER |
Index (position) of the first record |
LAST_INDEX |
NUMBER |
Index of the last record |
LAST_RECID |
NUMBER |
Record ID of the last record |
v$thread
Column |
Datatype |
Description |
THREAD# |
NUMBER |
Thread number |
STATUS |
VARCHAR2(6) |
Thread status (OPEN| CLOSED) |
ENABLED |
VARCHAR2(8) |
Enabled status: DISABLED, (enabled) PRIVATE, or (enabled) PUBLIC |
GROUPS |
NUMBER |
Number of log groups assigned to this thread |
INSTANCE |
VARCHAR2(16) |
Instance name, if available |
OPEN_TIME |
DATE |
Last time the thread was opened |
CURRENT_GROUP# |
NUMBER |
Current log group |
SEQUENCE# |
NUMBER |
Sequence number of current log |
CHECKPOINT_CHANGE# |
NUMBER |
SCN at last checkpoint |
CHECKPOINT_TIME |
DATE |
Time of last checkpoint |
ENABLE_CHANGE# |
NUMBER |
SCN at which thread was enabled |
ENABLE_TIME |
DATE |
Time of enable SCN |
DISABLE_CHANGE# |
NUMBER |
SCN at which thread was disabled |
DISABLE_TIME |
DATE |
Time of disable SCN |
LAST_REDO_SEQUENCE# |
NUMBER |
Last redo sequence number written by LGWR |
LAST_REDO_BLOCK |
NUMBER |
Last redo block written by LGWR |
LAST_REDO_CHANGE# |
NUMBER |
SCN of last redo for the thread |
LAST_REDO_TIME |
DATE |
Time of last redo for the thread |
v$datafile_header
Column |
Datatype |
Description |
FILE# |
NUMBER |
Datafile number (from control file) |
STATUS |
VARCHAR2(7) |
ONLINE | OFFLINE (from control file) |
ERROR |
VARCHAR2(18) |
NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used. |
FORMAT |
NUMBER |
Indicates the format for the header block. The possible values are 6, 7, 8, 10 or 0. 6 - indicates Oracle Version 6 7 - indicates Oracle Version 7 8 - indicates Oracle Version 8 10 - indicates Oracle Version 10 0 - indicates the format could not be determined (for example, the header could not be read) |
RECOVER |
VARCHAR2(3) |
File needs media recovery (YES | NO) |
FUZZY |
VARCHAR2(3) |
File is fuzzy (YES | NO) |
CREATION_CHANGE# |
NUMBER |
Datafile creation change# |
CREATION_TIME |
DATE |
Datafile creation timestamp |
TABLESPACE_NAME |
VARCHAR2(30) |
Tablespace name |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Tablespace relative datafile number |
RESETLOGS_CHANGE# |
NUMBER |
Resetlogs change# |
RESETLOGS_TIME |
DATE |
Resetlogs timestamp |
CHECKPOINT_CHANGE# |
NUMBER |
Datafile checkpoint change# |
CHECKPOINT_TIME |
DATE |
Datafile checkpoint timestamp |
CHECKPOINT_COUNT |
NUMBER |
Datafile checkpoint count |
BYTES |
NUMBER |
Current datafile size in bytes |
BLOCKS |
NUMBER |
Current datafile size in blocks |
NAME |
VARCHAR2(513) |
Datafile name |
SPACE_HEADER |
VARCHAR2(40) |
Represents the block address of a space file header block of a locally managed datafile |
LAST_DEALLOC_SCN |
VARCHAR2(16) |
Last deallocated SCN |
UNDO_OPT_CURRENT_CHANGE# |
VARCHAR2(40) |
For internal use only |
v$archived_log
Column |
Datatype |
Description |
RECID |
NUMBER |
Archived log record ID |
STAMP |
NUMBER |
Archived log record stamp |
NAME |
VARCHAR2(513) |
Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;). |
DEST_ID |
NUMBER |
Original destination from which the archive log was generated. The value is 0 if the destination identifier is not available. |
THREAD# |
NUMBER |
Redo thread number |
SEQUENCE# |
NUMBER |
Redo log sequence number |
RESETLOGS_CHANGE# |
NUMBER |
Resetlogs change number of the database when the log was written |
RESETLOGS_TIME |
DATE |
Resetlogs time of the database when the log was written |
RESETLOGS_ID |
NUMBER |
Resetlogs identifier associated with the archived redo log |
FIRST_CHANGE# |
NUMBER |
First change number in the archived log |
FIRST_TIME |
DATE |
Timestamp of the first change |
NEXT_CHANGE# |
NUMBER |
First change in the next log |
NEXT_TIME |
DATE |
Timestamp of the next change |
BLOCKS |
NUMBER |
Size of the archived log (in blocks) |
BLOCK_SIZE |
NUMBER |
Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user. |
CREATOR |
VARCHAR2(7) |
Creator of the archivelog:
|
REGISTRAR |
VARCHAR2(7) |
Registrar of the entry:
|
STANDBY_DEST |
VARCHAR2(3) |
Indicates whether the entry is an archivelog destination (YES) or not (NO) |
ARCHIVED |
VARCHAR2(3) |
Indicates whether the online redo log was archived (YES) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (NO). See Also: . |
APPLIED |
VARCHAR2(9) |
Indicates whether an archived redo log file has been applied to the corresponding physical standby database. The value is always NO for local destinations. This column is meaningful on a physical standby database for rows where REGISTRAR = RFS:
This column can be used to identify log files that can be backed up and deleted. When used for this purpose, the value IN-MEMORY should be treated as if it were NO. |
DELETED |
VARCHAR2(3) |
Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (YES) or not (NO) |
STATUS |
VARCHAR2(1) |
Status of the archived log: A - Available D - Deleted U - Unavailable X - Expired |
COMPLETION_TIME |
DATE |
Time when the archiving completed |
DICTIONARY_BEGIN |
VARCHAR2(3) |
Indicates whether the log contains the start of a LogMiner dictionary (YES) or not (NO) |
DICTIONARY_END |
VARCHAR2(3) |
Indicates whether the log contains the end of a LogMiner dictionary (YES) or not (NO) |
END_OF_REDO |
VARCHAR2(3) |
Indicates whether the archived redo log contains the end of all redo information from the primary database (YES) or not (NO) |
BACKUP_COUNT |
NUMBER |
Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15. |
ARCHIVAL_THREAD# |
NUMBER |
Redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance. |
ACTIVATION# |
NUMBER |
Number assigned to the database instantiation |
IS_RECOVERY_DEST_FILE |
VARCHAR2(3) |
Indicates whether the file was created in the fast recovery area (YES) or not (NO) |
COMPRESSED |
VARCHAR2(3) |
Reserved for internal use |
FAL |
VARCHAR2(3) |
Indicates whether the archive log was generated as the result of a FAL request (YES) or not (NO) |
END_OF_REDO_TYPE |
VARCHAR2(10) |
Possible values are as follows:
|
BACKED_BY_VSS |
VARCHAR2(3) |
Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is reserved for internal use. |
v$archive_dest
Column |
Datatype |
Description |
DEST_ID |
NUMBER |
Log archive destination parameter identifier (1 to 31) |
DEST_NAME |
VARCHAR2(256) |
Log archive destination parameter name |
STATUS |
VARCHAR2(9) |
Identifies the current status of the destination:
|
BINDING |
VARCHAR2(9) |
Specifies how failure will affect the archival operation:
|
NAME_SPACE |
VARCHAR2(7) |
Identifies the scope of parameter setting:
|
TARGET |
VARCHAR2(7) |
Specifies whether the archive destination is local or remote to the primary database:
|
ARCHIVER |
VARCHAR2(10) |
Identifies the archiver process relative to the database where the query is issued:
|
SCHEDULE |
VARCHAR2(8) |
Indicates whether the archival of this destination is INACTIVE, PENDING, ACTIVE, or LATENT |
DESTINATION |
VARCHAR2(256) |
Specifies the location where the archived redo logs are to be archived |
LOG_SEQUENCE |
NUMBER |
Identifies the sequence number of the last archived redo log to be archived |
REOPEN_SECS |
NUMBER |
Identifies the retry time (in seconds) after error |
DELAY_MINS |
NUMBER |
Identifies the delay interval (in minutes) before the archived redo log is automatically applied to a standby database |
MAX_CONNECTIONS |
NUMBER |
Maximum number of connections |
NET_TIMEOUT |
NUMBER |
Number of seconds the log writer process will wait for status from the network server of a network operation issued by the log writer process |
PROCESS |
VARCHAR2(10) |
Identifies the archiver process relative to the primary database, even if the query is issued on the standby database:
|
REGISTER |
VARCHAR2(3) |
Indicates whether the archived redo log is registered in the remote destination control file (YES) or not (NO). If the archived redo log is registered, it is available to log apply services. |
FAIL_DATE |
DATE |
Date and time of last error |
FAIL_SEQUENCE |
NUMBER |
Sequence number of the archived redo log being archived when the last error occurred |
FAIL_BLOCK |
NUMBER |
Block number of the archived redo log being archived when the last error occurred |
FAILURE_COUNT |
NUMBER |
Current number of contiguous archival operation failures that have occurred for the destination |
MAX_FAILURE |
NUMBER |
Allows you to control the number of times log transport services will attempt to reestablish communication and resume archival operations with a failed destination |
ERROR |
VARCHAR2(256) |
Displays the error text |
ALTERNATE |
VARCHAR2(256) |
Alternate destination, if any |
DEPENDENCY |
VARCHAR2(256) |
Reserved for future use |
REMOTE_TEMPLATE |
VARCHAR2(256) |
Specifies the template to be used to derive the location to be recorded |
QUOTA_SIZE |
NUMBER |
Destination quotas, expressed in bytes |
QUOTA_USED |
NUMBER |
Size of all the archived redo logs currently residing on the specified destination |
MOUNTID |
NUMBER |
Instance mount identifier |
TRANSMIT_MODE |
VARCHAR2(12) |
Specifies network transmission mode:
|
ASYNC_BLOCKS |
NUMBER |
Number of blocks specified for the ASYNC attribute |
AFFIRM |
VARCHAR2(3) |
Specifies disk I/O mode |
TYPE |
VARCHAR2(7) |
Indicates whether the archived log destination definition is PUBLIC or PRIVATE. Only PUBLIC destinations can be modified at runtime using the ALTER SYSTEM SET or ALTER SESSION SET statements. By default, all archived log destinations are PUBLIC. |
VALID_NOW |
VARCHAR2(16) |
Indicates whether the destination is valid right now for archival operations:
|
VALID_TYPE |
VARCHAR2(15) |
Redo log type or types that are valid for the destination:
|
VALID_ROLE |
VARCHAR2(12) |
Database role or roles that are valid for the destination:
|
DB_UNIQUE_NAME |
VARCHAR2(30) |
Unique database name |
VERIFY |
VARCHAR2(3) |
Indicates whether the value of the VERIFY attribute on the LOG_ARCHIVE_DEST_n parameter is verified (YES) or not verified (NO) |
COMPRESSION |
VARCHAR2(7) |
Indicates whether network compression is ENABLED or DISABLED. |
APPLIED_SCN |
NUMBER |
For a destination that corresponds to a physical or logical standby database, the SCN of the last applied redo. For a destination that corresponds to a snapshot standby database, the SCN of the last redo applied before conversion to a snapshot standby database. This column is only valid for enabled and active standby database destinations on a primary or cascading standby database. |
v$logmnr_contents
Column |
Datatype |
Description |
SCN |
NUMBER |
System change number (SCN) when the database change was made |
START_SCN |
NUMBER |
System change number (SCN) when the transaction that contains this change started; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation, NULL otherwise. This column may also be NULL if the query is run over a time/SCN range that does not contain the start of the transaction. |
COMMIT_SCN |
NUMBER |
System change number (SCN) when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation |
TIMESTAMP |
DATE |
Timestamp when the database change was made |
START_TIMESTAMP |
DATE |
Timestamp when the transaction that contains this change started; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation, NULL otherwise. This column may also be NULL if the query is run over a time/SCN range that does not contain the start of the transaction. |
COMMIT_TIMESTAMP |
DATE |
Timestamp when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation |
XIDUSN |
NUMBER |
Transaction ID undo segment number of the transaction that generated the change |
XIDSLT |
NUMBER |
Transaction ID slot number of the transaction that generated the change |
XIDSQN |
NUMBER |
Transaction ID sequence number of the transaction that generated the change |
XID |
RAW(8) |
Raw representation of the transaction identifier |
PXIDUSN |
NUMBER |
Parent transaction ID undo segment number of a parallel transaction |
PXIDSLT |
NUMBER |
Parent transaction ID slot number of a parallel transaction |
PXIDSQN |
NUMBER |
Parent transaction ID sequence number of a parallel transaction |
PXID |
RAW(8) |
Raw representation of the parent transaction identifier |
TX_NAME |
VARCHAR2(256) |
Name of the transaction that made the change; only meaningful if the transaction is a named transaction |
OPERATION |
VARCHAR2(32) |
User level SQL operation that made the change:
|
OPERATION_CODE |
NUMBER |
Number of the operation code:
|
ROLLBACK |
NUMBER |
1 = if the redo record was generated because of a partial or a full rollback of the associated transaction 0 = otherwise |
SEG_OWNER |
VARCHAR2(32) |
Owner of the modified data segment |
SEG_NAME |
VARCHAR2(256) |
Name of the modified data segment |
TABLE_NAME |
VARCHAR2(32) |
Name of the modified table (in case the redo pertains to a table modification) |
SEG_TYPE |
NUMBER |
Type of the modified data segment:
|
SEG_TYPE_NAME |
VARCHAR2(32) |
Segment type name:
|
TABLE_SPACE |
VARCHAR2(32) |
Name of the tablespace containing the modified data segment. This column is not populated for rows where the value of the OPERATION column is DDL. This is because DDL may operate on more than one tablespace. |
ROW_ID |
VARCHAR2(18) |
Row ID of the row modified by the change (only meaningful if the change pertains to a DML). This will be NULL if the redo record is not associated with a DML. |
USERNAME |
VARCHAR2(30) |
Name of the user who executed the transaction |
OS_USERNAME |
VARCHAR2(4000) |
Name of the operating system user |
MACHINE_NAME |
VARCHAR2(4000) |
Machine from which the user connected to the database |
AUDIT_SESSIONID |
NUMBER |
Audit session ID associated with the user session making the change |
SESSION# |
NUMBER |
Session number of the session that made the change |
SERIAL# |
NUMBER |
Serial number of the session that made the change |
SESSION_INFO |
VARCHAR2(4000) |
Information about the database session that executed the transaction. Contains process information, machine name from which the user logged in, and so on. A possible SESSION_INFO column may contain the following:
|
THREAD# |
NUMBER |
Number of the thread that made the change to the database |
SEQUENCE# |
NUMBER |
Sequence number of the SQL statement within the transaction. If you are mining without the COMMITED_DATA_ONLY option set, then this value is 1. |
RBASQN |
NUMBER |
Sequence# associated with the Redo Block Address (RBA) of the redo record associated with the change |
RBABLK |
NUMBER |
RBA block number within the log file |
RBABYTE |
NUMBER |
RBA byte offset within the block |
UBAFIL |
NUMBER |
Undo Block Address (UBA) file number identifying the file containing the undo block |
UBABLK |
NUMBER |
UBA block number for the undo block |
UBAREC |
NUMBER |
UBA record index within the undo block |
UBASQN |
NUMBER |
UBA undo block sequence number |
ABS_FILE# |
NUMBER |
Data block absolute file number of the block changed by the transaction |
REL_FILE# |
NUMBER |
Data block relative file number. The file number is relative to the tablespace of the object. |
DATA_BLK# |
NUMBER |
Data block number within the file |
DATA_OBJ# |
NUMBER |
Data block object number identifying the object |
DATA_OBJV# |
NUMBER |
Version number of the table being modified |
DATA_OBJD# |
NUMBER |
Data block data object number identifying the object within the tablespace |
SQL_REDO |
VARCHAR2(4000) |
Reconstructed SQL statement that is equivalent to the original SQL statement that made the change. Refer to before executing SQL_REDO to your database. LogMiner does not generate SQL redo for temporary tables. In such a case, this column will contain the string "/* No SQL_REDO for temporary tables */". |
SQL_UNDO |
VARCHAR2(4000) |
Reconstructed SQL statement that can be used to undo the effect of the original statement that made the change. DDL statements have no corresponding SQL_UNDO. Refer to before executing SQL_UNDO to your database. LogMiner does not generate SQL undo for temporary tables. In such a case, this column will contain the string "/* No SQL_UNDO for temporary tables */". |
RS_ID |
VARCHAR2(32) |
Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a logical row change. This will usually mean one row from V$LOGMNR_CONTENTS, but could be more than one row if a single SQL statement for either the Redo or Undo would be too large to fit within the respective columns SQL_UNDO or SQL_REDO. RS_ID uniquely identifies the redo record that generated the row. |
SSN |
NUMBER |
SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a logical row change, shown as one or more rows from the V$LOGMNR_CONTENTS view. |
CSF |
NUMBER |
Continuation SQL flag. Possible values are:
|
INFO |
VARCHAR2(32) |
Informational message about the row. For instance, the string "USER DDL" indicates that the DDL statement returned in the SQL_REDO column was the top-level DDL executed by the user and the string "INTERNAL DDL" indicates that the DDL statement returned in the SQL_REDO column was executed internally by the RDBMS. |
STATUS |
NUMBER |
A value of 0 indicates that the reconstructed SQL statements as shown in the SQL_REDO and SQL_UNDO columns are valid executable SQL statements. Otherwise, the reconstructed SQL statements are not executable. This may be due to the fact that no data dictionary was provided to LogMiner for the analysis, or that the data dictionary provided did not have the definition of the object being mined. A value of 5 indicates that this row is part of a change to an XMLType column or table and the XML document must be assembled before being applied. |
REDO_VALUE |
NUMBER |
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions |
UNDO_VALUE |
NUMBER |
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions |
SAFE_RESUME_SCN |
NUMBER |
Reserved for future use |
CSCN |
NUMBER |
This column is deprecated in favor of the COMMIT_SCN column |
OBJECT_ID |
RAW(16) |
Object identifier for DMLs to XMLType tables. For changes to non-typed tables, this column is NULL. |
EDITION_NAME |
VARCHAR2(30) |
Identifies the edition in which a DDL statement was executed |
CLIENT_ID |
VARCHAR2(64) |
Client identifier in the session that performed the operation, if available. |
v$logmnr_dictionary
Column |
Datatype |
Description |
DB_NAME |
VARCHAR2(9) |
Name of the database |
DB_ID |
NUMBER |
Database ID |
DB_CREATED |
DATE |
Creation date of the source database (corresponds to the CREATED column in the V$DATABASE view) |
TIMESTAMP |
DATE |
Date when the dictionary was created |
RESET_SCN |
NUMBER |
Reset log SCN when the dictionary was created |
RESET_SCN_TIME |
DATE |
Timestamp of the reset log SCN when the dictionary was created |
DB_VERSION_TIME |
DATE |
Version time for the source database (corresponds to the VERSION_TIME column in the V$DATABASE view) |
DB_CHARACTER_SET |
VARCHAR2(30) |
Character set of the source database |
DB_VERSION |
VARCHAR2(64) |
This column is deprecated. |
DB_STATUS |
VARCHAR2(64) |
This column is deprecated. |
DICTIONARY_SCN |
NUMBER |
Database checkpoint SCN at which the dictionary was created |
ENABLED_THREAD_MAP |
RAW(16) |
This column is deprecated. |
DB_TXN_SCN |
NUMBER |
SCN at which the dictionary was created |
FILENAME |
VARCHAR2(512) |
Dictionary file name |
INFO |
VARCHAR2(32) |
Informational/Status message BAD_DATE indicates that the SCN of the dictionary file does not match the SCN range of the log files |
STATUS |
NUMBER |
A NULL indicates a valid dictionary file for the list of log files. A non-NULL value indicates further information is contained in the INFO column as a text string. |
v$logmnr_logs
Column |
Datatype |
Description |
LOG_ID |
NUMBER |
This column is deprecated. |
FILENAME |
VARCHAR2(512) |
Name of the log file |
LOW_TIME |
DATE |
Oldest date of any records in the file |
HIGH_TIME |
DATE |
Most recent date of any records in the file |
DB_ID |
NUMBER |
Database ID |
DB_NAME |
VARCHAR2(8) |
Name of the database |
RESET_SCN |
NUMBER |
Resetlogs SCN of the database incarnation that generated the log file |
RESET_SCN_TIME |
DATE |
Resetlogs timestamp of the database incarnation that generated the log file |
COMPATIBLE |
VARCHAR2(17) |
The setting of the database COMPATIBLE initialization parameter at the time the log file was generated |
THREAD_ID |
NUMBER |
Thread number |
THREAD_SQN |
NUMBER |
Thread sequence number |
LOW_SCN |
NUMBER |
SCN allocated when log switched into |
NEXT_SCN |
NUMBER |
SCN after this log. Low SCN of the next log. |
DICTIONARY_BEGIN |
VARCHAR2(3) |
Indicates whether dictionary dumped to redo logs starts in this redo log (YES) or not (NO) |
DICTIONARY_END |
VARCHAR2(3) |
Indicates whether dictionary dumped to redo logs ends in this redo log (YES) or not (NO) |
TYPE |
VARCHAR2(7) |
Redo log file type:
|
BLOCKSIZE |
NUMBER |
Database block size |
FILESIZE |
NUMBER |
Size of the redo file (in bytes) |
INFO |
VARCHAR2(32) |
Informational message. A value of MISSING_LOGFILE will be assigned to a row entry where a needed log file is missing from the list of log files. |
STATUS |
NUMBER |
Status of the redo log file:
|
v$tablespace
Column |
Datatype |
Description |
TS# |
NUMBER |
Tablespace number |
NAME |
VARCHAR2(30) |
Tablespace name |
INCLUDED_IN_DATABASE_BACKUP |
VARCHAR2(3) |
Indicates whether the tablespace is included in full database backups using the BACKUP DATABASE RMAN command (YES) or not (NO); NO only if the CONFIGURE EXCLUDE RMAN command was used for this tablespace |
BIGFILE |
VARCHAR2(3) |
Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO) |
FLASHBACK_ON |
VARCHAR2(3) |
Indicates whether the tablespace participates in FLASHBACK DATABASE operations (YES) or not (NO) |
ENCRYPT_IN_BACKUP |
VARCHAR2(3) |
Indicates whether encryption is turned ON or off at the tablespace level:
|
v$tempfile
Column |
Datatype |
Description |
FILE# |
NUMBER |
Absolute file number |
CREATION_CHANGE# |
NUMBER |
Creation System Change Number (SCN) |
CREATION_TIME |
DATE |
Creation time |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Relative file number in the tablespace |
STATUS |
VARCHAR2(7) |
Status of the file (OFFLINE|ONLINE) |
ENABLED |
VARCHAR2(10) |
Enabled for read and/or write |
BYTES |
NUMBER |
Size of the file in bytes (from the file header) |
BLOCKS |
NUMBER |
Size of the file in blocks (from the file header) |
CREATE_BYTES |
NUMBER |
Creation size of the file (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size for the file |
NAME |
VARCHAR2(513) |
Name of the file |
v$filestat
Column |
Datatype |
Description |
FILE# |
NUMBER |
Number of the file |
PHYRDS |
NUMBER |
Number of physical reads done |
PHYWRTS |
NUMBER |
Number of times DBWR is required to write |
PHYBLKRD |
NUMBER |
Number of physical blocks read |
OPTIMIZED_PHYBLKRD |
NUMBER |
Number of physical reads from Database Smart Flash Cache blocks |
PHYBLKWRT |
NUMBER |
Number of blocks written to disk, which may be the same as PHYWRTS if all writes are single blocks |
SINGLEBLKRDS |
NUMBER |
Number of single block reads |
READTIM |
NUMBER |
Time (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is true; 0 if false |
WRITETIM |
NUMBER |
Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is true; 0 if false |
SINGLEBLKRDTIM |
NUMBER |
Cumulative single block read time (in hundredths of a second) |
AVGIOTIM |
NUMBER |
Average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if false |
LSTIOTIM |
NUMBER |
Time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is true; 0 if false |
MINIOTIM |
NUMBER |
Minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is true; 0 if false |
MAXIORTM |
NUMBER |
Maximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is true; 0 if false |
MAXIOWTM |
NUMBER |
Maximum time (in hundredths of a second) spent doing a single write, if the TIMED_STATISTICS parameter is true; 0 if false |
v$undostat
Column |
Datatype |
Description |
BEGIN_TIME |
DATE |
Identifies the beginning of the time interval |
END_TIME |
DATE |
Identifies the end of the time interval |
UNDOTSN |
NUMBER |
Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported. |
UNDOBLKS |
NUMBER |
Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system. |
TXNCOUNT |
NUMBER |
Identifies the total number of transactions executed within the period |
MAXQUERYLEN |
NUMBER |
Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view. |
MAXQUERYID |
VARCHAR2(13) |
SQL identifier of the longest running SQL statement in the period |
MAXCONCURRENCY |
NUMBER |
Identifies the highest number of transactions executed concurrently within the period |
UNXPSTEALCNT |
NUMBER |
Number of attempts to obtain undo space by stealing unexpired extents from other transactions |
UNXPBLKRELCNT |
NUMBER |
Number of unexpired blocks removed from certain undo segments so they can be used by other transactions |
UNXPBLKREUCNT |
NUMBER |
Number of unexpired undo blocks reused by transactions |
EXPSTEALCNT |
NUMBER |
Number of attempts to steal expired undo blocks from other undo segments |
EXPBLKRELCNT |
NUMBER |
Number of expired undo blocks stolen from other undo segments |
EXPBLKREUCNT |
NUMBER |
Number of expired undo blocks reused within the same undo segments |
SSOLDERRCNT |
NUMBER |
Identifies the number of times the error occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error. |
NOSPACEERRCNT |
NUMBER |
Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace. |
ACTIVEBLKS |
NUMBER |
Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period |
UNEXPIREDBLKS |
NUMBER |
Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period |
EXPIREDBLKS |
NUMBER |
Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period |
TUNED_UNDORETENTION |
NUMBER |
Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed at a particular time in the past can be recycled. |
v$rollname
Column |
Datatype |
NULL |
USN |
NUMBER |
|
NAME |
VARCHAR2(30) |
NOT NULL |
v$session
Column |
Datatype |
Description |
SADDR |
RAW(4 | 8) |
Session address |
SID |
NUMBER |
Session identifier |
SERIAL# |
NUMBER |
Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. |
AUDSID |
NUMBER |
Auditing session ID |
PADDR |
RAW(4 | 8) |
Address of the process that owns the session |
USER# |
NUMBER |
Oracle user identifier |
USERNAME |
VARCHAR2(30) |
Oracle username |
COMMAND |
NUMBER |
Command in progress (last statement parsed); for a list of values, see . These values also appear in the AUDIT_ACTIONS table. |
OWNERID |
NUMBER |
Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644 For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator. |
TADDR |
VARCHAR2(8) |
Address of the transaction state object |
LOCKWAIT |
VARCHAR2(8) |
Address of the lock the session is waiting for; NULL if none |
STATUS |
VARCHAR2(8) |
Status of the session:
|
SERVER |
VARCHAR2(9) |
Server type:
|
SCHEMA# |
NUMBER |
Schema user identifier |
SCHEMANAME |
VARCHAR2(30) |
Schema user name |
OSUSER |
VARCHAR2(30) |
Operating system client user name |
PROCESS |
VARCHAR2(24) |
Operating system client process ID |
MACHINE |
VARCHAR2(64) |
Operating system machine name |
PORT |
NUMBER |
Client port number |
TERMINAL |
VARCHAR2(30) |
Operating system terminal name |
PROGRAM |
VARCHAR2(48) |
Operating system program name |
TYPE |
VARCHAR2(10) |
Session type |
SQL_ADDRESS |
RAW(4 | 8) |
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed |
SQL_HASH_VALUE |
NUMBER |
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed |
SQL_ID |
VARCHAR2(13) |
SQL identifier of the SQL statement that is currently being executed |
SQL_CHILD_NUMBER |
NUMBER |
Child number of the SQL statement that is currently being executed |
SQL_EXEC_START |
DATE |
Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL |
SQL_EXEC_ID |
NUMBER |
SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR) |
PREV_SQL_ADDR |
RAW(4 | 8) |
Used with PREV_HASH_VALUE to identify the last SQL statement executed |
PREV_HASH_VALUE |
NUMBER |
Used with SQL_HASH_VALUE to identify the last SQL statement executed |
PREV_SQL_ID |
VARCHAR2(13) |
SQL identifier of the last SQL statement executed |
PREV_CHILD_NUMBER |
NUMBER |
Child number of the last SQL statement executed |
PREV_EXEC_START |
DATE |
SQL execution start of the last executed SQL statement |
PREV_EXEC_ID |
NUMBER |
SQL execution identifier of the last executed SQL statement |
PLSQL_ENTRY_OBJECT_ID |
NUMBER |
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack |
PLSQL_ENTRY_SUBPROGRAM_ID |
NUMBER |
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack |
PLSQL_OBJECT_ID |
NUMBER |
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL |
PLSQL_SUBPROGRAM_ID |
NUMBER |
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL |
MODULE |
VARCHAR2(48) |
Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure |
MODULE_HASH |
NUMBER |
Hash value of the MODULE column |
ACTION |
VARCHAR2(32) |
Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure |
ACTION_HASH |
NUMBER |
Hash value of the ACTION column |
CLIENT_INFO |
VARCHAR2(64) |
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure |
FIXED_TABLE_SEQUENCE |
NUMBER |
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database. |
ROW_WAIT_OBJ# |
NUMBER |
Object ID for the table containing the row specified in ROW_WAIT_ROW# |
ROW_WAIT_FILE# |
NUMBER |
Identifier for the datafile containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1. |
ROW_WAIT_BLOCK# |
NUMBER |
Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1. |
ROW_WAIT_ROW# |
NUMBER |
Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1. |
TOP_LEVEL_CALL# |
NUMBER |
Oracle top level call number |
LOGON_TIME |
DATE |
Time of logon |
LAST_CALL_ET |
NUMBER |
If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive. |
PDML_ENABLED |
VARCHAR2(3) |
This column has been replaced by the PDML_STATUS column |
FAILOVER_TYPE |
VARCHAR2(13) |
Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:
See Also:
|
FAILOVER_METHOD |
VARCHAR2(10) |
Indicates the transparent application failover method for the session:
|
FAILED_OVER |
VARCHAR2(3) |
Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO) |
RESOURCE_CONSUMER_GROUP |
VARCHAR2(32) |
Name of the session's current resource consumer group |
PDML_STATUS |
VARCHAR2(8) |
If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML. |
PDDL_STATUS |
VARCHAR2(8) |
If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL. |
PQ_STATUS |
VARCHAR2(8) |
If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL QUERY. |
CURRENT_QUEUE_DURATION |
NUMBER |
If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0. |
CLIENT_IDENTIFIER |
VARCHAR2(64) |
Client identifier of the session |
BLOCKING_SESSION_STATUS |
VARCHAR2(11) |
This column provides details on whether there is a blocking session:
|
BLOCKING_INSTANCE |
NUMBER |
Instance identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID. |
BLOCKING_SESSION |
NUMBER |
Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID. |
FINAL_BLOCKING_SESSION_STATUS |
VARCHAR2(11) |
The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker. This column provides details on whether there is a final blocking session:
|
FINAL_BLOCKING_INSTANCE |
NUMBER |
Instance identifier of the final blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. |
FINAL_BLOCKING_SESSION |
NUMBER |
Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. |
SEQ# |
NUMBER |
A number that uniquely identifies the current or last wait (incremented for each wait) |
EVENT# |
NUMBER |
Event number |
EVENT |
VARCHAR2(64) |
Resource or event for which the session is waiting See Also: |
P1TEXT |
VARCHAR2(64) |
Description of the first wait event parameter |
P1 |
NUMBER |
First wait event parameter (in decimal) |
P1RAW |
RAW(8) |
First wait event parameter (in hexadecimal) |
P2TEXT |
VARCHAR2(64) |
Description of the second wait event parameter |
P2 |
NUMBER |
Second wait event parameter (in decimal) |
P2RAW |
RAW(8) |
Second wait event parameter (in hexadecimal) |
P3TEXT |
VARCHAR2(64) |
Description of the third wait event parameter |
P3 |
NUMBER |
Third wait event parameter (in decimal) |
P3RAW |
RAW(8) |
Third wait event parameter (in hexadecimal) |
WAIT_CLASS_ID |
NUMBER |
Identifier of the class of the wait event |
WAIT_CLASS# |
NUMBER |
Number of the class of the wait event |
WAIT_CLASS |
VARCHAR2(64) |
Name of the class of the wait event |
WAIT_TIME |
NUMBER |
If the session is currently waiting, then the value is 0. If the session is not in a wait, then the value is as follows:
This column has been deprecated in favor of the columns WAIT_TIME_MICRO and STATE. |
SECONDS_IN_WAIT |
NUMBER |
If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait. This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO. |
STATE |
VARCHAR2(19) |
Wait state:
|
WAIT_TIME_MICRO |
NUMBER |
Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait. |
TIME_REMAINING_MICRO |
NUMBER |
Value is interpreted as follows:
|
TIME_SINCE_LAST_WAIT_MICRO |
NUMBER |
Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0. |
SERVICE_NAME |
VARCHAR2(64) |
Service name of the session |
SQL_TRACE |
VARCHAR2(8) |
Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED) |
SQL_TRACE_WAITS |
VARCHAR2(5) |
Indicates whether wait tracing is enabled (TRUE) or not (FALSE) |
SQL_TRACE_BINDS |
VARCHAR2(5) |
Indicates whether bind tracing is enabled (TRUE) or not (FALSE) |
SQL_TRACE_PLAN_STATS |
VARCHAR2(10) |
Frequency at which row source statistics are dumped in the trace files for each cursor:
|
SESSION_EDITION_ID |
NUMBER |
Shows the value that, in the session, would be reported by sys_context('USERENV', 'SESSION_EDITION_ID') |
CREATOR_ADDR |
RAW(4 | 8) |
Address of the creating process or circuit |
CREATOR_SERIAL# |
NUMBER |
Serial number of the creating process or circuit |
ECID |
VARCHAR2(64) |
Execution context identifier (sent by Application Server) |
v$transaction
Column |
Datatype |
Description |
ADDR |
RAW(4 | 8) |
Address of the transaction state object |
XIDUSN |
NUMBER |
Undo segment number |
XIDSLOT |
NUMBER |
Slot number |
XIDSQN |
NUMBER |
Sequence number |
UBAFIL |
NUMBER |
Undo block address (UBA) filenum |
UBABLK |
NUMBER |
UBA block number |
UBASQN |
NUMBER |
UBA sequence number |
UBAREC |
NUMBER |
UBA record number |
STATUS |
VARCHAR2(16) |
Status |
START_TIME |
VARCHAR2(20) |
Start time (wall clock) |
START_SCNB |
NUMBER |
Start system change number (SCN) base |
START_SCNW |
NUMBER |
Start SCN wrap |
START_UEXT |
NUMBER |
Start extent number |
START_UBAFIL |
NUMBER |
Start UBA file number |
START_UBABLK |
NUMBER |
Start UBA block number |
START_UBASQN |
NUMBER |
Start UBA sequence number |
START_UBAREC |
NUMBER |
Start UBA record number |
SES_ADDR |
RAW(4 | 8) |
User session object address |
FLAG |
NUMBER |
Flag |
SPACE |
VARCHAR2(3) |
YES if a space transaction |
RECURSIVE |
VARCHAR2(3) |
YES if a recursive transaction |
NOUNDO |
VARCHAR2(3) |
YES if a no undo transaction |
PTX |
VARCHAR2(3) |
YES if parallel transaction |
NAME |
VARCHAR2(256) |
Name of a named transaction |
PRV_XIDUSN |
NUMBER |
Previous transaction undo segment number |
PRV_XIDSLT |
NUMBER |
Previous transaction slot number |
PRV_XIDSQN |
NUMBER |
Previous transaction sequence number |
PTX_XIDUSN |
NUMBER |
Rollback segment number of the parent XID |
PTX_XIDSLT |
NUMBER |
Slot number of the parent XID |
PTX_XIDSQN |
NUMBER |
Sequence number of the parent XID |
DSCN-B |
NUMBER |
This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE. |
DSCN-W |
NUMBER |
This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP. |
USED_UBLK |
NUMBER |
Number of undo blocks used |
USED_UREC |
NUMBER |
Number of undo records used |
LOG_IO |
NUMBER |
Logical I/O |
PHY_IO |
NUMBER |
Physical I/O |
CR_GET |
NUMBER |
Consistent gets |
CR_CHANGE |
NUMBER |
Consistent changes |
START_DATE |
DATE |
Start time (wall clock) |
DSCN_BASE |
NUMBER |
Dependent SCN base |
DSCN_WRAP |
NUMBER |
Dependent SCN wrap |
START_SCN |
NUMBER |
Start SCN |
DEPENDENT_SCN |
NUMBER |
Dependent SCN |
XID |
RAW(8) |
Transaction XID |
PRV_XID |
RAW(8) |
Previous transaction XID |
PTX_XID |
RAW(8) |
Parent transaction XID |
v$rollstat
Column |
Datatype |
Description |
USN |
NUMBER |
Rollback segment number |
LATCH |
NUMBER |
Latch for the rollback segment |
EXTENTS |
NUMBER |
Number of extents in the rollback segment |
RSSIZE |
NUMBER |
Size (in bytes) of the rollback segment. This value differs by the number of bytes in one database block from the value of the BYTES column of the *_SEGMENTS view. See Also: |
WRITES |
NUMBER |
Number of bytes written to the rollback segment |
XACTS |
NUMBER |
Number of active transactions |
GETS |
NUMBER |
Number of header gets |
WAITS |
NUMBER |
Number of header waits |
OPTSIZE |
NUMBER |
Optimal size of the rollback segment |
HWMSIZE |
NUMBER |
High watermark of the rollback segment size |
SHRINKS |
NUMBER |
Number of times the size of a rollback segment decreases |
WRAPS |
NUMBER |
Number of times rollback segment is wrapped |
EXTENDS |
NUMBER |
Number of times rollback segment size is extended |
AVESHRINK |
NUMBER |
Average shrink size |
AVEACTIVE |
NUMBER |
Current size of active extents, averaged over time. |
STATUS |
VARCHAR2(15) |
Rollback segment status:
|
CUREXT |
NUMBER |
Current extent |
CURBLK |
NUMBER |
Current block |
v$pwfile_users
Column |
Datatype |
Description |
USERNAME |
VARCHAR2(30) |
Name of the user that is contained in the password file |
SYSDBA |
VARCHAR2(5) |
Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE) |
SYSOPER |
VARCHAR2(5) |
Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE) |
SYSASM |
VARCHAR2(5) |
Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE) |
v$sqlarea
Column |
Datatype |
Description |
SQL_TEXT |
VARCHAR2(1000) |
First thousand characters of the SQL text for the current cursor |
SQL_FULLTEXT |
CLOB |
All characters of the SQL text for the current cursor |
SQL_ID |
VARCHAR2(13) |
SQL identifier of the parent cursor in the library cache |
SHARABLE_MEM |
NUMBER |
Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors. |
PERSISTENT_MEM |
NUMBER |
Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, then the fixed sum of memory used for the lifetime of all the child cursors. |
RUNTIME_MEM |
NUMBER |
Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, then the fixed sum of all memory required during execution of all the child cursors. |
SORTS |
NUMBER |
Sum of the number of sorts that were done for all the child cursors |
VERSION_COUNT |
NUMBER |
Number of child cursors that are present in the cache under this parent |
LOADED_VERSIONS |
NUMBER |
Number of child cursors that are present in the cache and have their context heap loaded |
OPEN_VERSIONS |
NUMBER |
Number of child cursors that are currently open under this current parent |
USERS_OPENING |
NUMBER |
Number of users that have any of the child cursors open |
FETCHES |
NUMBER |
Number of fetches associated with the SQL statement |
EXECUTIONS |
NUMBER |
Total number of executions, totalled over all the child cursors |
PX_SERVERS_EXECUTIONS |
NUMBER |
Total number of executions performed by parallel execution servers (0 when the statement has never been executed in parallel) |
END_OF_FETCH_COUNT |
NUMBER |
Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column. |
USERS_EXECUTING |
NUMBER |
Total number of users executing the statement over all child cursors |
LOADS |
NUMBER |
Number of times the object was loaded or reloaded |
FIRST_LOAD_TIME |
VARCHAR2(19) |
Timestamp of the parent creation time |
INVALIDATIONS |
NUMBER |
Total number of invalidations over all the child cursors |
PARSE_CALLS |
NUMBER |
Sum of all parse calls to all the child cursors under this parent |
DISK_READS |
NUMBER |
Sum of the number of disk reads over all child cursors |
DIRECT_WRITES |
NUMBER |
Sum of the number of direct writes over all child cursors |
BUFFER_GETS |
NUMBER |
Sum of buffer gets over all child cursors |
APPLICATION_WAIT_TIME |
NUMBER |
Application wait time (in microseconds) |
CONCURRENCY_WAIT_TIME |
NUMBER |
Concurrency wait time (in microseconds) |
CLUSTER_WAIT_TIME |
NUMBER |
Cluster wait time (in microseconds) |
USER_IO_WAIT_TIME |
NUMBER |
User I/O Wait Time (in microseconds) |
PLSQL_EXEC_TIME |
NUMBER |
PL/SQL execution time (in microseconds) |
JAVA_EXEC_TIME |
NUMBER |
Java execution time (in microseconds) |
ROWS_PROCESSED |
NUMBER |
Total number of rows processed on behalf of this SQL statement |
COMMAND_TYPE |
NUMBER |
Oracle command type definition |
OPTIMIZER_MODE |
VARCHAR2(10) |
Mode under which the SQL statement was executed |
OPTIMIZER_COST |
NUMBER |
Cost of this query given by the optimizer |
OPTIMIZER_ENV |
RAW(2000) |
Optimizer environment |
OPTIMIZER_ENV_HASH_VALUE |
NUMBER |
Hash value for the optimizer environment |
PARSING_USER_ID |
NUMBER |
User ID of the user that has parsed the very first cursor under this parent |
PARSING_SCHEMA_ID |
NUMBER |
Schema ID that was used to parse this child cursor |
PARSING_SCHEMA_NAME |
VARCHAR2(30) |
Schema name that was used to parse this child cursor |
KEPT_VERSIONS |
NUMBER |
Number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package |
ADDRESS |
RAW(4 | 8) |
Address of the handle to the parent for this cursor |
HASH_VALUE |
NUMBER |
Hash value of the parent statement in the library cache |
OLD_HASH_VALUE |
NUMBER |
Old SQL hash value |
PLAN_HASH_VALUE |
NUMBER |
Numeric representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line). |
MODULE |
VARCHAR2(64) |
Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE |
MODULE_HASH |
NUMBER |
Hash value of the module that is named in the MODULE column |
ACTION |
VARCHAR2(64) |
Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION |
ACTION_HASH |
NUMBER |
Hash value of the action that is named in the ACTION column |
SERIALIZABLE_ABORTS |
NUMBER |
Number of times the transaction failed to serialize, producing errors, totalled over all the child cursors |
OUTLINE_CATEGORY |
VARCHAR2(64) |
If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank. |
CPU_TIME |
NUMBER |
CPU time (in microseconds) used by this cursor for parsing, executing, and fetching |
ELAPSED_TIME |
NUMBER |
Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching |
OUTLINE_SID |
VARCHAR2(40) |
Outline session identifier |
LAST_ACTIVE_CHILD_ADDRESS |
RAW(4 | 8) |
Address (identifier) of the child cursor that was the last to be active in the group (that is, the child cursor on behalf of which statistics in V$SQL were updated) |
REMOTE |
VARCHAR2(1) |
Indicates whether the cursor is remote mapped (Y) or not (N) |
OBJECT_STATUS |
VARCHAR2(19) |
Status of the cursor:
|
LITERAL_HASH_VALUE |
NUMBER |
Hash value of the literals which are replaced with system-generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, then the value is 0. |
LAST_LOAD_TIME |
DATE |
Time at which the query plan was loaded into the library cache |
IS_OBSOLETE |
VARCHAR2(1) |
Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large. |
IS_BIND_SENSITIVE |
VARCHAR2(1) |
Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan. |
IS_BIND_AWARE |
VARCHAR2(1) |
Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive. |
CHILD_LATCH |
NUMBER |
Child latch number that is protecting the cursor. This column is obsolete and maintained for backward compatibility. |
SQL_PROFILE |
VARCHAR2(64) |
SQL profile used for this statement, if any |
SQL_PATCH |
VARCHAR2(30) |
SQL patch used for this statement, if any |
SQL_PLAN_BASELINE |
VARCHAR2(30) |
SQL plan baseline used for this statement, if any |
PROGRAM_ID |
NUMBER |
Program identifier |
PROGRAM_LINE# |
NUMBER |
Program line number |
EXACT_MATCHING_SIGNATURE |
NUMBER |
Signature used when the CURSOR_SHARING parameter is set to EXACT |
FORCE_MATCHING_SIGNATURE |
NUMBER |
Signature used when the CURSOR_SHARING parameter is set to FORCE |
LAST_ACTIVE_TIME |
DATE |
Time at which the query plan was last active |
BIND_DATA |
RAW(2000) |
Bind data |
TYPECHECK_MEM |
NUMBER |
Typecheck memory |
IO_CELL_OFFLOAD_ELIGIBLE_BYTES |
NUMBER |
Number of I/O bytes which can be filtered by the Exadata storage system See Also: Oracle Exadata Storage Server Software documentation for more information |
IO_INTERCONNECT_BYTES |
NUMBER |
Number of I/O bytes exchanged between Oracle Database and the storage system |
PHYSICAL_READ_REQUESTS |
NUMBER |
Number of physical read I/O requests issued by the monitored SQL |
PHYSICAL_READ_BYTES |
NUMBER |
Number of bytes read from disks by the monitored SQL |
PHYSICAL_WRITE_REQUESTS |
NUMBER |
Number of physical write I/O requests issued by the monitored SQL |
PHYSICAL_WRITE_BYTES |
NUMBER |
Number of bytes written to disks by the monitored SQL |
OPTIMIZED_PHY_READ_REQUESTS |
NUMBER |
Number of physical read I/O requests from Database Smart Flash Cache issued by the monitored SQL |
LOCKED_TOTAL |
NUMBER |
Total number of times the child cursor has been locked |
PINNED_TOTAL |
NUMBER |
Total number of times the child cursor has been pinned |
IO_CELL_UNCOMPRESSED_BYTES |
NUMBER |
Number of uncompressed bytes (that is, size after decompression) that are offloaded to the Exadata cells See Also: Oracle Exadata Storage Server Software documentation for more information |
IO_CELL_OFFLOAD_RETURNED_BYTES |
NUMBER |
Number of bytes that are returned by Exadata cell through the regular I/O path See Also: Oracle Exadata Storage Server Software documentation for more information |
v$sql
Column |
Datatype |
Description |
SQL_TEXT |
VARCHAR2(1000) |
First thousand characters of the SQL text for the current cursor |
SQL_FULLTEXT |
CLOB |
Full text for the SQL statement exposed as a CLOB column. The full text of a SQL statement can be retrieved using this column instead of joining with the V$SQLTEXT dynamic performance view. |
SQL_ID |
VARCHAR2(13) |
SQL identifier of the parent cursor in the library cache |
SHARABLE_MEM |
NUMBER |
Amount of shared memory used by the child cursor (in bytes) |
PERSISTENT_MEM |
NUMBER |
Fixed amount of memory used for the lifetime of the child cursor (in bytes) |
RUNTIME_MEM |
NUMBER |
Fixed amount of memory required during the execution of the child cursor |
SORTS |
NUMBER |
Number of sorts that were done for the child cursor |
LOADED_VERSIONS |
NUMBER |
Indicates whether the context heap is loaded (1) or not (0) |
OPEN_VERSIONS |
NUMBER |
Indicates whether the child cursor is locked (1) or not (0) |
USERS_OPENING |
NUMBER |
Number of users executing the statement |
FETCHES |
NUMBER |
Number of fetches associated with the SQL statement |
EXECUTIONS |
NUMBER |
Number of executions that took place on this object since it was brought into the library cache |
PX_SERVERS_EXECUTIONS |
NUMBER |
Total number of executions performed by parallel execution servers (0 when the statement has never been executed in parallel) |
END_OF_FETCH_COUNT |
NUMBER |
Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column. |
USERS_EXECUTING |
NUMBER |
Number of users executing the statement |
LOADS |
NUMBER |
Number of times the object was either loaded or reloaded |
FIRST_LOAD_TIME |
VARCHAR2(19) |
Timestamp of the parent creation time |
INVALIDATIONS |
NUMBER |
Number of times this child cursor has been invalidated |
PARSE_CALLS |
NUMBER |
Number of parse calls for this child cursor |
DISK_READS |
NUMBER |
Number of disk reads for this child cursor |
DIRECT_WRITES |
NUMBER |
Number of direct writes for this child cursor |
BUFFER_GETS |
NUMBER |
Number of buffer gets for this child cursor |
APPLICATION_WAIT_TIME |
NUMBER |
Application wait time (in microseconds) |
CONCURRENCY_WAIT_TIME |
NUMBER |
Concurrency wait time (in microseconds) |
CLUSTER_WAIT_TIME |
NUMBER |
Cluster wait time (in microseconds) |
USER_IO_WAIT_TIME |
NUMBER |
User I/O Wait Time (in microseconds) |
PLSQL_EXEC_TIME |
NUMBER |
PL/SQL execution time (in microseconds) |
JAVA_EXEC_TIME |
NUMBER |
Java execution time (in microseconds) |
ROWS_PROCESSED |
NUMBER |
Total number of rows the parsed SQL statement returns |
COMMAND_TYPE |
NUMBER |
Oracle command type definition |
OPTIMIZER_MODE |
VARCHAR2(10) |
Mode under which the SQL statement was executed |
OPTIMIZER_COST |
NUMBER |
Cost of this query given by the optimizer |
OPTIMIZER_ENV |
RAW(2000) |
Optimizer environment |
OPTIMIZER_ENV_HASH_VALUE |
NUMBER |
Hash value for the optimizer environment |
PARSING_USER_ID |
NUMBER |
User ID of the user who originally built this child cursor |
PARSING_SCHEMA_ID |
NUMBER |
Schema ID that was used to originally build this child cursor |
PARSING_SCHEMA_NAME |
VARCHAR2(30) |
Schema name that was used to originally build this child cursor |
KEPT_VERSIONS |
NUMBER |
Indicates whether this child cursor has been marked to be kept pinned in the cache using the DBMS_SHARED_POOL package |
ADDRESS |
RAW(4 | 8) |
Address of the handle to the parent for this cursor |
TYPE_CHK_HEAP |
RAW(4) |
Descriptor of the type check heap for this child cursor |
HASH_VALUE |
NUMBER |
Hash value of the parent statement in the library cache |
OLD_HASH_VALUE |
NUMBER |
Old SQL hash value |
PLAN_HASH_VALUE |
NUMBER |
Numeric representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line). |
CHILD_NUMBER |
NUMBER |
Number of this child cursor |
SERVICE |
VARCHAR2(64) |
Service name |
SERVICE_HASH |
NUMBER |
Hash value for the name listed in the SERVICEcolumn |
MODULE |
VARCHAR2(64) |
Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_MODULE |
MODULE_HASH |
NUMBER |
Hash value of the module listed in the MODULE column |
ACTION |
VARCHAR2(64) |
Contains the name of the action that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_ACTION |
ACTION_HASH |
NUMBER |
Hash value of the action listed in the ACTION column |
SERIALIZABLE_ABORTS |
NUMBER |
Number of times the transaction failed to serialize, producing errors, per cursor |
OUTLINE_CATEGORY |
VARCHAR2(64) |
If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank. |
CPU_TIME |
NUMBER |
CPU time (in microseconds) used by this cursor for parsing, executing, and fetching |
ELAPSED_TIME |
NUMBER |
Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching |
OUTLINE_SID |
NUMBER |
Outline session identifier |
CHILD_ADDRESS |
RAW(4 | 8) |
Address of the child cursor |
SQLTYPE |
NUMBER |
Denotes the version of the SQL language used for this statement |
REMOTE |
VARCHAR2(1) |
Indicates whether the cursor is remote mapped (Y) or not (N) |
OBJECT_STATUS |
VARCHAR2(19) |
Status of the cursor:
|
LITERAL_HASH_VALUE |
NUMBER |
Hash value of the literals which are replaced with system-generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, then the value is 0. |
LAST_LOAD_TIME |
VARCHAR2(19) |
Time at which the query plan was loaded into the library cache |
IS_OBSOLETE |
VARCHAR2(1) |
Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large. |
IS_BIND_SENSITIVE |
VARCHAR2(1) |
Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan. |
IS_BIND_AWARE |
VARCHAR2(1) |
Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive. |
IS_SHAREABLE |
VARCHAR2(1) |
Indicates whether the cursor can be shared (Y) or not (N) |
CHILD_LATCH |
NUMBER |
Child latch number that is protecting the cursor. This column is obsolete and maintained for backward compatibility. |
SQL_PROFILE |
VARCHAR2(64) |
SQL profile used for this statement, if any |
SQL_PATCH |
VARCHAR2(30) |
SQL patch used for this statement, if any |
SQL_PLAN_BASELINE |
VARCHAR2(30) |
SQL plan baseline used for this statement, if any |
PROGRAM_ID |
NUMBER |
Program identifier |
PROGRAM_LINE# |
NUMBER |
Program line number |
EXACT_MATCHING_SIGNATURE |
NUMBER |
Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings. |
FORCE_MATCHING_SIGNATURE |
NUMBER |
Signature used when the CURSOR_SHARING parameter is set to FORCE |
LAST_ACTIVE_TIME |
DATE |
TIme at which the query plan was last active |
BIND_DATA |
RAW(2000) |
Bind data |
TYPECHECK_MEM |
NUMBER |
Typecheck memory |
IO_CELL_OFFLOAD_ELIGIBLE_BYTES |
NUMBER |
Number of I/O bytes which can be filtered by the Exadata storage system See Also: Oracle Exadata Storage Server Software documentation for more information |
IO_INTERCONNECT_BYTES |
NUMBER |
Number of I/O bytes exchanged between Oracle Database and the storage system |
PHYSICAL_READ_REQUESTS |
NUMBER |
Number of physical read I/O requests issued by the monitored SQL |
PHYSICAL_READ_BYTES |
NUMBER |
Number of bytes read from disks by the monitored SQL |
PHYSICAL_WRITE_REQUESTS |
NUMBER |
Number of physical write I/O requests issued by the monitored SQL |
PHYSICAL_WRITE_BYTES |
NUMBER |
Number of bytes written to disks by the monitored SQL |
OPTIMIZED_PHY_READ_REQUESTS |
NUMBER |
Number of physical read I/O requests from Database Smart Flash Cache issued by the monitored SQL |
LOCKED_TOTAL |
NUMBER |
Total number of times the child cursor has been locked |
PINNED_TOTAL |
NUMBER |
Total number of times the child cursor has been pinned |
IO_CELL_UNCOMPRESSED_BYTES |
NUMBER |
Number of uncompressed bytes (that is, size after decompression) that are offloaded to the Exadata cells See Also: Oracle Exadata Storage Server Software documentation for more information |
IO_CELL_OFFLOAD_RETURNED_BYTES |
NUMBER |
Number of bytes that are returned by Exadata cell through the regular I/O path See Also: Oracle Exadata Storage Server Software documentation for more information |
v$sysstat
Column |
Datatype |
Description |
STATISTIC# |
NUMBER |
Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications. |
NAME |
VARCHAR2(64) |
Statistic name. You can get a complete listing of statistic names by querying the V$STATNAME view. |
CLASS |
NUMBER |
A number representing one or more statistics class. The following class numbers are additive:
|
VALUE |
NUMBER |
Statistic value |
all_users
ALL_USERS lists all users of the database visible to the current user. This view does not describe the users (see the related views).
Related Views
· DBA_USERS describes all users of the database, and contains more columns than ALL_USERS.
· USER_USERS describes the current user, and contains more columns than ALL_USERS.
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_objects
ALL_OBJECTS describes all objects accessible to the current user.
Related Views
· DBA_OBJECTS describes all objects in the database.
· USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.
Column |
Datatype |
NULL |
Description |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the object |
OBJECT_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the object |
SUBOBJECT_NAME |
VARCHAR2(30) |
|
Name of the subobject (for example, partition) |
OBJECT_ID |
NUMBER |
NOT NULL |
Dictionary object number of the object |
DATA_OBJECT_ID |
NUMBER |
|
Dictionary object number of the segment that contains the object |
|
|
|
Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle Database assigns to row objects in object tables in the system. |
OBJECT_TYPE |
VARCHAR2(19) |
|
Type of the object (such as 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 statement (including grants and revokes) |
TIMESTAMP |
VARCHAR2(19) |
|
Timestamp for the specification of the object (character data) |
STATUS |
VARCHAR2(7) |
|
Status of the object:
|
TEMPORARY |
VARCHAR2(1) |
|
Indicates whether the object is temporary (the current session can see only data that it placed in this object itself) (Y) or not (N) |
GENERATED |
VARCHAR2(1) |
|
Indicates whether the name of this object was system-generated (Y) or not (N) |
SECONDARY |
VARCHAR2(1) |
|
Indicates whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) |
NAMESPACE |
NUMBER |
NOT NULL |
Namespace for the object |
EDITION_NAME |
VARCHAR2(30) |
|
Name of the edition in which the object is actual |
all_def_audit_opts
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 |
CHAR(3) |
|
This column is obsolete and maintained for backward compatibility. The value of this column is always -/- |
EXE |
VARCHAR2(3) |
|
Auditing EXECUTE WHENEVER SUCCESSFUL / UNSUCCESSFUL |
FBK |
VARCHAR2(3) |
|
Auditing FLASHBACK WHENEVER SUCCESSFUL / UNSUCCESSFUL |
REA |
VARCHAR2(3) |
|
Auditing READ WHENEVER SUCCESSFUL / UNSUCCESSFUL |
all_tables
ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the ANALYZE SQL statement.
Related Views
· DBA_TABLES describes all relational tables in the database.
· USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
Note:
Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package.
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) |
|
Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized 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 or mapping table entry belongs. If the IOT_TYPE column is not NULL, then this column contains the base table name. |
STATUS |
VARCHAR2(8) |
|
If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID) |
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 secondary extents (in 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 the segment; NULL for partitioned tables |
FREELIST_GROUPS |
NUMBER |
|
Number of freelist groups allocated to the segment; NULL for partitioned tables |
LOGGING |
VARCHAR2(3) |
|
Indicates whether or not changes to the table are logged; NULL for partitioned tables:
|
BACKED_UP |
VARCHAR2(1) |
|
Indicates whether the table has been backed up since the last modification (Y) or not (N) |
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. This column is populated only if you collect statistics on the table using the ANALYZE statement. |
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 |
|
Average freespace of all blocks on a freelist |
NUM_FREELIST_BLOCKS |
NUMBER |
|
Number of blocks on the freelist |
DEGREE |
VARCHAR2(10) |
|
Number of threads per instance for scanning the table, or DEFAULT |
INSTANCES |
VARCHAR2(10) |
|
Number of instances across which the table is to be scanned, or DEFAULT |
CACHE |
VARCHAR2(5) |
|
Indicates whether the table is to be cached in the buffer cache (Y) or not (N) |
TABLE_LOCK |
VARCHAR2(8) |
|
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) |
SAMPLE_SIZE |
NUMBER |
|
Sample size used in analyzing this table |
LAST_ANALYZED |
DATE |
|
Date on which this table was most recently analyzed |
PARTITIONED |
VARCHAR2(3) |
|
Indicates whether the table is partitioned (YES) or not (NO) |
IOT_TYPE |
VARCHAR2(12) |
|
If the table is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If the table is not an index-organized table, then IOT_TYPE is NULL. |
TEMPORARY |
VARCHAR2(1) |
|
Indicates whether the table is temporary (Y) or not (N) |
SECONDARY |
VARCHAR2(1) |
|
Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) |
NESTED |
VARCHAR2(3) |
|
Indicates whether the table is a nested table (YES) or not (NO) |
BUFFER_POOL |
VARCHAR2(7) |
|
Buffer pool for the table; NULL for partitioned tables:
|
FLASH_CACHE |
VARCHAR2(7) |
|
Database Smart Flash Cache hint to be used for table blocks:
Solaris and Oracle Linux functionality only. |
CELL_FLASH_CACHE |
VARCHAR2(7) |
|
Cell flash cache hint to be used for table blocks:
See Also: Oracle Exadata Storage Server Software documentation for more information |
ROW_MOVEMENT |
VARCHAR2(8) |
|
Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED) |
GLOBAL_STATS |
VARCHAR2(3) |
|
For partitioned tables, indicates whether statistics for the table as a whole (global statistics) are accurate (YES) or whether they were not collected and have to be estimated from statistics on underlying partitions and subpartitions (NO) |
USER_STATS |
VARCHAR2(3) |
|
Indicates whether statistics were entered directly by the user (YES) or not (NO) |
DURATION |
VARCHAR2(15) |
|
Indicates the duration of a temporary table:
Null - Permanent table |
SKIP_CORRUPT |
VARCHAR2(8) |
|
Indicates whether Oracle Database ignores blocks marked corrupt during table and index scans (ENABLED) or raises an error (DISABLED). To enable this feature, run the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure. |
MONITORING |
VARCHAR2(3) |
|
Indicates whether the table has the MONITORING attribute set (YES) or not (NO) |
CLUSTER_OWNER |
VARCHAR2(30) |
|
Owner of the cluster, if any, to which the table belongs |
DEPENDENCIES |
VARCHAR2(8) |
|
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) |
COMPRESSION |
VARCHAR2(8) |
|
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables |
COMPRESS_FOR |
VARCHAR2(12) |
|
Default compression for what kind of operations:
|
DROPPED |
VARCHAR2(3) |
|
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables |
READ_ONLY |
VARCHAR2(3) |
|
Indicates whether the table IS READ-ONLY (YES) or not (NO) |
SEGMENT_CREATED |
VARCHAR2(3) |
|
Indicates whether the table segment is created (YES) or not (NO) |
RESULT_CACHE |
VARCHAR2(7) |
|
Result cache mode annotation for the table:
|
all_indexes
ALL_INDEXES describes the indexes on the tables accessible to the current user. To gather statistics for this view and the related views DBA_INDEXES and USER_INDEXES, use the SQL ANALYZE statement.
Related Views
· DBA_INDEXES describes all indexes in the database.
· USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.
Note:
Column names followed by an asterisk are populated only if you collect statistics on the index using the ANALYZE statement or the DBMS_STATS package.
session_roles
Column |
Datatype |
NULL |
Description |
ROLE |
VARCHAR2(30) |
NOT NULL |
Name of the role |
session_privs
Column |
Datatype |
NULL |
Description |
PRIVILEGE |
VARCHAR2(40) |
NOT NULL |
Name of the privilege |
index_stats
Column |
Datatype |
NULL |
Description |
HEIGHT |
NUMBER |
|
Height of the B-Tree |
BLOCKS |
NUMBER |
NOT NULL |
Blocks allocated to the segment |
NAME |
VARCHAR2(30) |
NOT NULL |
Name of the index |
PARTITION_NAME |
VARCHAR2(30) |
|
Name of the partition of the index which was analyzed. If the index is not partitioned, NULL is returned. |
LF_ROWS |
NUMBER |
|
Number of leaf rows (values in the index) |
LF_BLKS |
NUMBER |
|
Number of leaf blocks in the B-Tree |
LF_ROWS_LEN |
NUMBER |
|
Sum of the lengths of all the leaf rows |
LF_BLK_LEN |
NUMBER |
|
Usable space in a leaf block |
BR_ROWS |
NUMBER |
|
Number of branch rows in the B-Tree |
BR_BLKS |
NUMBER |
|
Number of branch blocks in the B-Tree |
BR_ROWS_LEN |
NUMBER |
|
Sum of the lengths of all the branch blocks in the B-Tree |
BR_BLK_LEN |
NUMBER |
|
Usable space in a branch block |
DEL_LF_ROWS |
NUMBER |
|
Number of deleted leaf rows in the index |
DEL_LF_ROWS_LEN |
NUMBER |
|
Total length of all deleted rows in the index |
DISTINCT_KEYS |
NUMBER |
|
Number of distinct keys in the index (may include rows that have been deleted) |
MOST_REPEATED_KEY |
NUMBER |
|
How many times the most repeated key is repeated (may include rows that have been deleted) |
BTREE_SPACE |
NUMBER |
|
Total space currently allocated in the B-Tree |
USED_SPACE |
NUMBER |
|
Total space that is currently being used in the B-Tree |
PCT_USED |
NUMBER |
|
Percent of space allocated in the B-Tree that is being used |
ROWS_PER_KEY |
NUMBER |
|
Average number of rows per distinct key (this figure is calculated without consideration of deleted rows) |
BLKS_GETS_PER_ACCESS |
NUMBER |
|
Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan. |
PRE_ROWS |
NUMBER |
|
Number of prefix rows (values in the index) |
PRE_ROWS_LEN |
NUMBER |
|
Sum of lengths of all prefix rows |
OPT_CMPR_COUNT |
NUMBER |
|
Optimal key compression length |
OPT_CMPR_PCTSAVE |
NUMBER |
|
Corresponding space savings after an ANALYZE |
Dual
DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X. The following example queries DUAL to perform an arithmetical operation:
SQL> SELECT ((3*4)+5)/3 FROM DUAL;
((3*4)+5)/3
-----------
5.66666667
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23490154/viewspace-1068701/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 整理的一些常用系統表 (轉)
- Oracle常用資料字典表Oracle
- Python 常用系統模組整理Python
- ORACLE常用系統檢視(整理) .Oracle
- InnoDB資料字典詳解-系統表
- 系統表和資料字典檢視
- Oracle常用的系統查詢語句整理Oracle
- InnoDB 層系統字典表 | 全方位認識 information_schemaORM
- 探索ORACLE_之表空間02_管理Oracle
- Oracle 常用資料字典表、檢視的總結Oracle
- Oracle 常用資料字典檢視、表的總結Oracle
- HTML5中常用表單整理HTML
- Oracle常用資料字典Oracle
- dict(字典)的常用方法
- 【Mysql】flushprivilges重新整理系統許可權相關表MySql
- InnoDB資料字典--字典表載入
- 以身試水Linux系統一些常用命令整理Linux
- 資料字典和固定表統計資訊更新
- 分享之前整理的EBS常用表關聯圖 TRM
- 【Mysql】flush privilges 重新整理系統許可權相關表MySql
- dict字典常用操作(python)Python
- oracle常用的資料字典Oracle
- python輸出字典的方法整理Python
- 常用系統io
- 對系統快捷生成字典進行修改
- oracle常用資料字典.檢視Oracle
- ORACLE常用資料字典介紹Oracle
- 常用JS方法整理JS
- js常用方法整理JS
- 常用函式整理函式
- PHPmemcached常用指令整理PHP
- python-字典方法(dist)知識整理Python
- oracle_備份指令碼Oracle指令碼
- ORACLE EBS常用表及查詢語句(最終整理版)Oracle
- 系統、角色、物件相關許可權字典物件
- 系統許可權相關資料字典
- unix常用系統命令
- Oracle_勒索病毒解決方案Oracle