oracle系统表详解.docx

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$undostatUndo数据信息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_usersColumnDatatypeNULLDescriptionUSERNAMEVARCHAR2(30)NOT NULLName of the userUSER_IDNUMBERNOT NULLID number of the userPASSWORDVARCHAR2(30)Encrypted password（加密）ACCOUNT_STATUSVARCHAR2(32)NOT NULLAccount status: OPEN EXPIRED（到期） EXPIRED(GRACE) LOCKED(TIMED) LOCKED EXPIRED & LOCKED(TIMED) EXPIRED(GRACE) & LOCKED(TIMED) EXPIRED & LOCKED EXPIRED(GRACE) & LOCKEDLOCK_DATEDATEDate the account was locked if account status was LOCKEDEXPIRY_DATEDATEDate of expiration of the accountDEFAULT_TABLESPACEVARCHAR2(30)NOT NULLDefault tablespace for dataTEMPORARY_TABLESPACEVARCHAR2(30)NOT NULLName of the default tablespace for temporary tables or the name of a tablespace groupCREATEDDATENOT NULLUser creation datePROFILEVARCHAR2(30)NOT NULLUser resource profile nameINITIAL_RSRC_CONSUMER_GROUPVARCHAR2(30)Initial resource consumer group for the userEXTERNAL_NAMEVARCHAR2(4000)User external namedba_segmentsColumnDatatypeNULLDescriptionOWNERVARCHAR2(30)Username of the segment ownerSEGMENT_NAMEVARCHAR2(81)Name, if any, of the segmentPARTITION_NAMEVARCHAR2(30)Object Partition Name (Set to NULL for non-partitioned objects)SEGMENT_TYPEVARCHAR2(18)Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEXTABLESPACE_NAMEVARCHAR2(30)Name of the tablespace containing the segmentHEADER_FILENUMBERID of the file containing the segment headerHEADER_BLOCKNUMBERID of the block containing the segment headerBYTESNUMBERSize, in bytes, of the segmentBLOCKSNUMBERSize, in Oracle blocks, of the segmentEXTENTSNUMBERNumber of extents allocated to the segmentINITIAL_EXTENTNUMBERSize 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_EXTENTNUMBERSize in bytes of the next extent to be allocated to the segmentMIN_EXTENTSNUMBERMinimum number of extents allowed in the segmentMAX_EXTENTSNUMBERMaximum number of extents allowed in the segmentPCT_INCREASENUMBERPercent by which to increase the size of the next extent to be allocatedFREELISTSNUMBERNumber of process freelists allocated to this segmentFREELIST_GROUPSNUMBERNumber of freelist groups allocated to this segmentRELATIVE_FNONUMBERRelative file number of the segment headerBUFFER_POOLVARCHAR2(7)Default buffer pool for the objectdba_objectsColumnDatatypeNULLDescriptionOWNERVARCHAR2(30)NOT NULLOwner of the objectOBJECT_NAMEVARCHAR2(30)NOT NULLName of the objectSUBOBJECT_NAMEVARCHAR2(30)Name of the subobject (for example, partition)OBJECT_IDNUMBERNOT NULLDictionary object number of the objectDATA_OBJECT_IDNUMBERDictionary object number of the segment that contains the objectNote: 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_TYPEVARCHAR2(19)Type of the object (such as TABLE, INDEX)CREATEDDATENOT NULLTimestamp for the creation of the objectLAST_DDL_TIMEDATENOT NULLTimestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)TIMESTAMPVARCHAR2(20)Timestamp for the specification of the object (character data)STATUSVARCHAR2(7)Status of the object (VALID, INVALID, or N/A)TEMPORARYVARCHAR2(1)Whether the object is temporary (the current session can see only data that it placed in this object itself)GENERATEDVARCHAR2(1)Indicates whether the name of this object was system generated (Y) or not (N)SECONDARYVARCHAR2(1)Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N)dba_extentsColumnDatatypeNULLDescriptionOWNERVARCHAR2(30)Owner of the segment associated with the extentSEGMENT_NAMEVARCHAR2(81)Name of the segment associated with the extentPARTITION_NAMEVARCHAR2(30)Object Partition Name (Set to NULL for non-partitioned objects)SEGMENT_TYPEVARCHAR2(18)Type of the segment: INDEX PARTITION, TABLE PARTITIONTABLESPACE_NAMEVARCHAR2(30)Name of the tablespace containing the extentEXTENT_IDNUMBERExtent number in the segmentFILE_IDNUMBERFile identifier number of the file containing the extentBLOCK_IDNUMBERStarting block number of the extentBYTESNUMBERSize of the extent in bytesBLOCKSNUMBERSize of the extent in Oracle blocksRELATIVE_FNONUMBERRelative file number of the first extent blockdba_tablespacesColumnDatatypeNULLDescriptionTABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the tablespaceBLOCK_SIZENUMBERNOT NULLTablespace block sizeINITIAL_EXTENTNUMBERDefault initial extent sizeNEXT_EXTENTNUMBERDefault incremental extent sizeMIN_EXTENTSNUMBERNOT NULLDefault minimum number of extentsMAX_EXTENTSNUMBERDefault maximum number of extentsPCT_INCREASENUMBERDefault percent increase for extent sizeMIN_EXTLENNUMBERMinimum extent size for this tablespaceSTATUSVARCHAR2(9)Tablespace status: ONLINE OFFLINE READ ONLYCONTENTSVARCHAR2(9)Tablespace contents: UNDO PERMANENT TEMPORARYLOGGINGVARCHAR2(9)Default logging attribute: LOGGING NOLOGGINGFORCE_LOGGINGVARCHAR2(3)Indicates whether the tablespace is under force logging mode (YES) or not (NO)EXTENT_MANAGEMENTVARCHAR2(10)Indicates whether the extents in the tablespace are dictionary managed (DICTIONARY) or locally managed (LOCAL)ALLOCATION_TYPEVARCHAR2(9)Type of extent allocation in effect for the tablespace: SYSTEM UNIFORM USERPLUGGED_INVARCHAR2(3)Indicates whether the tablespace is plugged in (YES) or not (NO)SEGMENT_SPACE_MANAGEMENTVARCHAR2(6)Indicates whether the free and used segment space in the tablespace is managed using free lists (MANUAL) or bitmaps (AUTO)DEF_TAB_COMPRESSIONVARCHAR2(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.RETENTIONVARCHAR2(11)Undo tablespace retention: GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEEA 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. NOGUARANTEE - Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE NOT APPLY - Tablespace is not an undo tablespaceBIGFILEVARCHAR2(3)Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO)dba_data_files ColumnDatatypeNULLDescriptionFILE_NAMEVARCHAR2(513)Name of the database fileFILE_IDNUMBERNOT NULLFile identifier number of the database fileTABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the tablespace to which the file belongsBYTESNUMBERSize of the file in bytesBLOCKSNUMBERNOT NULLSize of the file in Oracle blocksSTATUSVARCHAR2(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_FNONUMBERRelative file numberAUTOEXTENSIBLEVARCHAR2(3)Autoextensible indicatorMAXBYTESNUMBERMaximum file size in bytesMAXBLOCKSNUMBERMaximum file size in blocksINCREMENT_BYNUMBERNumber of tablespace blocks used as autoextension increment. Block size is contained in the BLOCK_SIZE column of the DBA_TABLESPACES view.USER_BYTESNUMBERThe 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_BLOCKSNUMBERNumber of blocks which can be used by the dataONLINE_STATUSVARCHAR2(7)Online status of the file: SYSOFF SYSTEM OFFLINE ONLINE RECOVERdba_temp_filesColumnDatatypeNULLDescriptionFILE_NAMEVARCHAR2(513)Name of the database temp fileFILE_IDNUMBERFile identifier number of the database temp fileTABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the tablespace to which the file belongsBYTESNUMBERSize of the file (in bytes)BLOCKSNUMBERSize of the file (in Oracle blocks)STATUSCHAR(9)File status: AVAILABLERELATIVE_FNONUMBERTablespace-relative file numberAUTOEXTENSIBLEVARCHAR2(3)Indicates whether the file is autoextensible (YES) or not (NO)MAXBYTESNUMBERmaximum size of the file (in bytes)MAXBLOCKSNUMBERMaximum size of the file (in Oracle blocks)INCREMENT_BYNUMBERDefault increment for autoextensionUSER_BYTESNUMBERSize of the useful portion of the file (in bytes)USER_BLOCKSNUMBERSize of the useful portion of the file (in Oracle blocks)dba_rollback_segsColumnDatatypeNULLDescriptionSEGMENT_NAMEVARCHAR2(30)NOT NULLName of the rollback segmentOWNERVARCHAR2(6)Owner of the rollback segmentTABLESPACE_NAMEVARCHAR2(30)NOT NULLName of the tablespace containing the rollback segmentSEGMENT_IDNUMBERNOT NULLID number of the rollback segmentFILE_IDNUMBERNOT NULLFile identifier number of the file containing the segment headBLOCK_IDNUMBERNOT NULLID number of the block containing the segment headerINITIAL_EXTENTNUMBERInitial extent size in bytesNEXT_EXTENTNUMBERSecondary extent size in bytesMIN_EXTENTSNUMBERNOT NULLMinimum number of extentsMAX_EXTENTSNUMBERNOT NULLMaximum number of extentPCT_INCREASENUMBERPercent increase for extent sizeSTATUSVARCHAR2(16)Rollback segment statusINSTANCE_NUMVARCHAR2(40)Rollback segment owning Real Application Clusters instance numberRELATIVE_FNONUMBERNOT NULLRelative file number of the segment headerdba_ts_quotas ColumnDatatypeNULLDescriptionTABLESPACE_NAMEVARCHAR2(30)NOT NULLTablespace nameUSERNAMEVARCHAR2(30)NOT NULLUser with resource rights on the tablespaceBYTESNUMBERNumber of bytes charged to the userMAX_BYTESNUMBERUsers quota in bytes, or -1 if no limitBLOCKSNUMBERNOT NULLNumber of Oracle blocks charged to the userMAX_BLOCKSNUMBERUsers quota in Oracle blocks, or -1 if no limitDROPPEDVARCHAR2(3)Whether the tablespace has been droppeddba_free_spaceColumnDatatypeNULLDescriptionTABLESPACE_NAMEVARCHAR2(30)Name of the tablespace containing the extentFILE_IDNUMBERFile identifier number of the file containing the extentBLOCK_IDNUMBERStarting block number of the extentBYTESNUMBERSize of the extent (in bytes)BLOCKSNUMBERSize of the extent (in Oracle blocks)RELATIVE_FNONUMBERRelative file number of the file containing the extentdba_profilesColumnDatatypeNULLDescriptionPROFILEVARCHAR2(30)NOT NULLProfile nameRESOURCE_NAMEVARCHAR2(32)NOT NULLResource nameRESOURCE_TYPEVARCHAR2(8)Indicates whether the resource profile is a KERNEL or a PASSWORD parameterLIMITVARCHAR2(40)Limit placed on this resource for this profiledba_sys_privs ColumnDatatypeNULLDescriptionGRANTEEVARCHAR2(30)NOT NULLGrantee name, user, or role receiving the grantUSERNAMEVARCHAR2(30)Name of the current userPRIVILEGEVARCHAR2(40)NOT NULLSystem privilegeADMIN_OPTIONVARCHAR2(3)Grant was with the ADMIN optiondba_tab_privsColumnDatatypeNULLDescriptionGRANTEEVARCHAR2(30)NOT NULLName of the user to whom access was grantedOWNERVARCHAR2(30)NOT NULLOwner of the objectTABLE_NAMEVARCHAR2(30)NOT NULLName of the objectGRANTORVARCHAR2(30)NOT NULLName of the user who performed the grantPRIVILEGEVARCHAR2(40)NOT NULLPrivilege on the objectGRANTABLEVARCHAR2(3)Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO)HIERARCHYVARCHAR2(3)Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO)dba_col_privsColumnDatatypeNULLDescriptionGRANTEEVARCHAR2(30)NOT NULLName of the user to whom access was grantedOWNERVARCHAR2(30)NOT NULLOwner of the objectTABLE_NAMEVARCHAR2(30)NOT NULLName of the objectCOLUMN_NAMEVARCHAR2(30)NOT NULLName of the columnGRANTORVARCHAR2(30)NOT NULLName of the user who performed the grantPRIVILEGEVARCHAR2(40)NOT NULLPrivilege on the columnGRANTABLEVARCHAR2(3)Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO)dba_role_privsColumnDatatypeNULLDescriptionGRANTEEVARCHAR2(30)Name of the user or role receiving the grantGRANTED_ROLEVARCHAR2(30)NOT NULLGranted role nameADMIN_OPTIONVARCHAR2(3)Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO)DEFAULT_ROLEVARCHAR2(3)Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO)dba_audit_trailColumnDatatypeNULLDescriptionOS_USERNAMEVARCHAR2(255)Operating system login username of the user whose actions were auditedUSERNAMEVARCHAR2(30)Name (not ID number) of the user whose actions were auditedUSERHOSTVARCHAR2(128)Client host machine nameTERMINALVARCHAR2(255)Identifier of the users terminalTIMESTAMPDATEDate 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 zoneOWNERVARCHAR2(30)Creator of the object affected by the actionOBJ_NAMEVARCHAR2(128)Name of the object affected by the actionACTIONNUMBERNOT NULLNumeric action type code. The corresponding name of the action type is in the ACTION_NAME column.ACTION_NAMEVARCHAR2(28)Name of the action type corresponding to the numeric code in the ACTION columnNEW_OWNERVARCHAR2(30)Owner of the object named in the NEW_NAME columnNEW_NAMEVARCHAR2(128)New name of the object after a RENAME or the name of the underlying objectOBJ_PRIVILEGEVARCHAR2(16)Object privileges granted or revoked by a GRANT or REVOKE statementSYS_PRIVILEGEVARCHAR2(40)System privileges granted or revoked by a GRANT or REVOKE statementADMIN_OPTIONVARCHAR2(1)Indicates whether the role or system privilege was granted with the ADMIN optionGRANTEEVARCHAR2(30)Name of the grantee specified in a GRANT or REVOKE statementAUDIT_OPTIONVARCHAR2(40)Auditing option set with the AUDIT statementSES_ACTIONSVARCHAR2(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: - - None S - Success F - Failure B - BothLOGOFF_TIMEDATEDate and time of user log offLOGOFF_LREADNUMBERLogical reads for the sessionLOGOFF_PREADNUMBERPhysical reads for the sessionLOGOFF_LWRITENUMBERLogical writes for the sessionLOGOFF_DLOCKVARCHAR2(40)Deadlocks detected during the sessionCOMMENT_TEXTVARCHAR2(4000)Text comment on the audit trail