《oracle系统表详解(中文)8575.docx》由会员分享，可在线阅读，更多相关《oracle系统表详解(中文)8575.docx（169页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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: OPEN EXPIRED（到期） EXPIRED(GRACE) LOCKED(TIMED) LOCKED EXPIRED & LOCKED(TIMED) EXPIRED(GRACE) & LOCKED(TIMED) EXPIRED & LOCKED EXPIRED(GRACE) & LOCKEDLOCK_DATEDATEDate the account was locked i
8、f 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 da
9、tePROFILEVARCHAR2(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,
10、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_NAMEVARCHA
11、R2(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 all
12、ocated 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 segm
13、entMIN_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_
14、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 o
15、f 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
16、 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_TIMEDATE
17、NOT 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 obje
18、ct 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
19、 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_TYPEVARCHAR
20、2(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 extentBYTESNUMBE
21、RSize 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_EXTENTN
22、UMBERDefault 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 tablespac
23、eSTATUSVARCHAR2(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_MANA
24、GEMENTVARCHAR2(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 (YE
25、S) 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 d
26、efault 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 GUARAN
27、TEE 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 - Tables
28、pace 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 fileTABL
29、ESPACE_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 table
30、space 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 th
31、e 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 st
32、atus 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 belongsBYTESNU
33、MBERSize 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)MA
34、XBLOCKSNUMBERMaximum 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_segsColumnDatatypeNULLDescriptionSEGMEN
35、T_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
36、 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_
37、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_NA
38、MEVARCHAR2(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 i
39、n 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 blo
40、ck 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 NULLRes
41、ource 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 grantUSERNAMEVARC
42、HAR2(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_NAMEVAR
43、CHAR2(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 NULLOwne