oracle系统表详解(中文)

1、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_

2、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用户的对象约束信息us

3、er_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$con

4、trolfile控制文件信息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

5、$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数

6、据库所有用户的信息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

7、number of the userPASSWORDVARCHAR2(30)Encrypted password（加密）ACCOUNT_STATUSVARCHAR2(32)NOT NULLAccount status: OPENEXPIRED（到期）EXPIRED(GRACE)LOCKED(TIMED)LOCKEDEXPIRED & LOCKED(TIMED)EXPIRED(GRACE) & LOCKED(TIMED)EXPIRED & LOCKEDEXPIRED(GRACE) & LOCKEDLOCK_DATEDATEDate the account was locked if accoun

8、t 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 datePROFIL

9、EVARCHAR2(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,

10、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)Na

11、me 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 t

12、o 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_E

13、XTENTSNUMBERMinimum 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_GROUPSNU

14、MBERNumber 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 ob

15、jectSUBOBJECT_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 con

16、fuse 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 NULL

17、Timestamp 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 te

18、mporary (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 Cartrid

19、ge (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)Typ

20、e 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

21、 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_EXTENTNUMBERDef

22、ault 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 tablespaceSTATUSV

23、ARCHAR2(9)Tablespace status: ONLINEOFFLINEREAD ONLYCONTENTSVARCHAR2(9)Tablespace contents: UNDOPERMANENTTEMPORARYLOGGINGVARCHAR2(9)Default logging attribute: LOGGINGNOLOGGINGFORCE_LOGGINGVARCHAR2(3)Indicates whether the tablespace is under force logging mode (YES) or not (NO)EXTENT_MANAGEMENTVARCHAR

24、2(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: SYSTEMUNIFORMUSERPLUGGED_INVARCHAR2(3)Indicates whether the tablespace is plugged in (YES) or not (NO)S

25、EGMENT_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 co

26、mpression 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 t

27、hat 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 NOGUARANTEENOT APPLY - Tablespace is not an un

28、do 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_NAMEVARCHA

29、R2(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 dr

30、opped)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 colu

31、mn 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:

32、 SYSOFFSYSTEMOFFLINEONLINERECOVERdba_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

33、(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

34、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

35、 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 segme

36、nt 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

37、 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 NUL

38、LTablespace 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 -

39、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 exte

40、ntBYTESNUMBERSize 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_TY

41、PEVARCHAR2(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 c

42、urrent 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

43、 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

44、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 columnGRAN

45、TORVARCHAR2(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

46、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_trailColumn

47、DatatypeNULLDescriptionOS_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 terminalTIMEST

48、AMPDATEDate 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 actionACTIONNUM

49、BERNOT 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)N

50、ew 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 th

51、e 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 i

52、n 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: - - NoneS - SuccessF - FailureB - BothLOGOFF_TIMEDATEDate and time of user log offLOGOFF_LREADNUMBERLogica

53、l 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 entry, providing more information about the statement audited

54、 Also indicates how the user was authenticated. The method can be one of the following:DATABASE - Authentication was done by passwordNETWORK - Authentication was done by Oracle Net Services or the Advanced Security optionPROXY - Client was authenticated by another user; the name of the proxy user fo

55、llows the method typeSESSIONIDNUMBERNOT NULLNumeric ID for each Oracle sessionENTRYIDNUMBERNOT NULLNumeric ID for each audit trail entry in the sessionSTATEMENTIDNUMBERNOT NULLNumeric ID for each statement runRETURNCODENUMBERNOT NULLOracle error code generated by the action. Some useful values: 0 -

56、Action succeeded2004 - Security violationPRIV_USEDVARCHAR2(40)System privilege used to execute the actionCLIENT_IDVARCHAR2(64)Client identifier in each Oracle sessionECONTEXT_IDVARCHAR2(64)Application execution context identifierSESSION_CPUNUMBERAmount of CPU time used by each Oracle sessionEXTENDED

57、_TIMESTAMPTIMESTAMP(6) WITH TIME ZONETimestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zonePROXY_SESSIONIDNUMBERProxy session serial number, if an enterprise user has logged in through the proxy

58、mechanismGLOBAL_UIDVARCHAR2(32)Global user identifier for the user, if the user has logged in as an enterprise userINSTANCE_NUMBERNUMBERInstance number as specified by the INSTANCE_NUMBER initialization parameterOS_PROCESSVARCHAR2(16)Operating System process identifier of the Oracle processTRANSACTI

59、ONIDRAW(8)Transaction identifier of the transaction in which the object is accessed or modifiedSCNNUMBERSystem change number (SCN) of the querySQL_BINDNVARCHAR2(2000)Bind variable data of the querySQL_TEXTNVARCHAR2(2000)SQL text of the querydba_stmt_audit_optsColumnDatatypeNULLDescriptionUSER_NAMEVA

60、RCHAR2(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 auditingPROXY_NAMEVARCHAR2(30)The name of the proxy user which is performing an operation for the client. NULL if the client is performing the operation directly.AUDI