《oracle系统表详解.docx》由会员分享，可在线阅读，更多相关《oracle系统表详解.docx（232页珍藏版）》请在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_col_privs用
2、户具有的列对象权限信息dba_role_privs用户具有的角色信息dba_audit_trail审计跟踪记录信息dba_stmt_audit_opts审计设置信息dba_audit_object对象审计结果信息dba_audit_session会话审计结果信息dbajndexes用户模式的索引信息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 (
3、N)SECONDARYVARCHAR2(1)Whether this is a secondary object created by theODCIIndexCreate methodof the Oracle Data Cartridge (Y1 N)dba_extentsColumnDatatypeNULLDescriptionOWNERVARCHAR2(30)Owner of the segment associated with the extentSEGMENT_NAMEVARCHAR2(81)Name of the segment associated with the exte
4、ntPART工T工ON_NAMEVARCHAR2(30)Object Partition Name (Set to NULL for non-partitioned LOB_WRITE - Change was caused by an invocation of DBMS_LOB.WRITE LOB_TRIM - Change was caused by an invocation of DBMS_LOB.TRIM LOB_ERASE - Change was caused by an invocation of DBMS_LOB.ERASE SELtCI_FOR_UPDATE - Oper
5、ation was a SELECT FOR UPDATE statement SEL_LOB_LOCAT0R - Operation was a SELECT statement that returned a LOB locator MISSING_SCN - LogMiner encountered a gap in the redo records. This is most likely because not all redo logs were registered with LogMiner. INTERNAL - Change was caused by internal o
6、perations initiated by the database XML DOC BEGIN - Beginning of a change to an XMLType column or table XML DOC WRITE - Data for an XML document XML DOC END - End of the Data for an XML document UNSUPPORTED - Change was caused by operations not currently supported by LogMiner (for example, changes m
7、ade to tables with ADT columns)OPERATION_CODENUMBERNumber of the operation code: 0 - INTERNAL 1 - INSERT 2 - DELETE 3 - UPDATE 5 - DDL 6 - START 7-COMMIT 9 - SELtC l_LOB_LOCATOR 10 - LOB_WRITE 11 - LOB_TRIM 25 - SELECT_FOR_UPDATE 28 - LOB_ERASE 34 - MISSING_SCN 68 - XML DOC BEGIN 70 = XML DOC WRITE
8、71 = XML DOC END 36 - ROLLBACK 255 - UNSUPPORTEDROLLBACKNUMBER1 = if the redo record was generated because of a partial or a full rollback of the associated transaction0 = otherwiseSEG_OWNERVARCHAR2(32)Owner of the modified data segmentSEG_NAMEVARCHAR2(256)Name of the modified data segmentTABLE_NAME
9、VARCHAR2(32)Name of the modified table (in case the redo pertains to a table modification)SEG_TYPENUMBERType of the modified data segment: 0 - UNKNOWN.1 - INDEX.2 - TABLE 19 - TABLE PARTITION 20 - INDEX PARTITION 34 - TABLE SUBPARTITION All other values - UNSUPPORTEDSEG_TYPE_NAMEVARCHAR2(32)Segment
10、type name: UNKNOWN INDEX TABLE TABLE PARTITION INDEX PARTITION TABLE SUBPARTITION UNSUPPORTEDTABLE_SPACEVARCHAR2(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 mo
11、re than one tablespace.ROW_IDVARCHAR2(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.USERNAMEVARCHAR2(30)Name of the user who executed the transactionOSJJSERNAMEVARCHAR2(4000)Name of the
12、 operating system userMACHINE_NAMEVARCHAR2(4000)Machine from which the user connected to the databaseAUD工T_SESS工ON工DNUMBERAudit session ID associated with the user session making the changeSESSION#NUMBERSession number of the session that made the changeSERIAL#NUMBERSerial number of the session that
13、made the changeSESSION_INFOVARCHAR2(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: login_username = HR clientjnfo = OS_username =
14、 jkundu Machine_name = nirvan OS_terminal = pts/31 OS_program_name = sqlplusnirvan (TNS V1-V3)THREAD#NUMBERNumber of the thread that made the change to the databaseSEQUENCE#NUMBERSequence number of the SQL statement within the transaction. If you are mining without the commited_data_onlY option set,
15、 then this value is 1.RBASQNNUMBERSequence# associated with the Redo Block Address (RBA) of the redo record associated with the changeRBABLKNUMBERRBA block number within the log fileRBABYTENUMBERRBA byte offset within the blockUBAFILNUMBERUndo Block Address (UBA) file number identifying the file con
16、taining the undo blockUBABLKNUMBERUBA block number for the undo blockUBARECNUMBERUBA record index within the undo blockUBASQNNUMBERUBA undo block sequence numberABS_FILE#NUMBERData block absolute file number of the block changed by the transactionREL_FILE#NUMBERData block relative file number. The f
17、ile number is relative to the tablespace of the object.DATA_BLK#NUMBERData block number within the fileDATA_OBJ#NUMBERData block object number identifying the objectDATA_OBJV#NUMBERVersion number of the table being modifiedDATA_OBJD#NUMBERData block data object number identifying the object within t
18、he tablespaceSQL_REDOVARCHAR2(4000)LogMiner does not generate SQL redo for temporary tables. In such a case, this column will contain the string H/* No SQL_REDO fortemporary tables */.SQL_UNDOVARCHAR2(4000)LogMiner does not generate SQL undo for temporary tables. In such a case, this column will con
19、tain the string /* No SQL_UNDO fortemporary tables */H.RS_IDVARCHAR2(32)Record set ID. The tuple(RS_工D, 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
20、would be too large to fitwithin the respective columns SQL_UNDO or SQL_REDO. RS_ID uniquely identifies the redo record that generated the row.SSNNUMBERSQL sequence number. Used in conjunction with RS_工D, this uniquely identifies a logical row change, shown as one or more rows from the V$LOGMNR_CONTE
21、NTS view.CSFNUMBERContinuation SQL flag. Possible values are: 0 - Indicates SQL_REDO and SQL_UNDO is contained within the same row 1 - Indicates that either SQL_REDO or SQL_UNDO is greaterthan 4000 bytes in size and is continued in the next row returned by the viewINFOVARCHAR2(32)Informational messa
22、ge 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.STATUSNUMB
23、ERA value of 0 indicates that the reconstructed SQL statements as shownin the SQL_REDO and SQL_UNDO columns are valid executable SQLstatements. 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 analysi
24、s, 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 XMLTypecolumn or table and the XML document must be assembled before being applied.REDO_VALUENUMBERUsed as input to the dbms_logmnr . mine_valu
25、e () andDBMS_LOGMNR. COLUMN_PRESENT () functionsUNDO_VALUENUMBERUsed as input to the dbms_logmnr . mine_value () andDBMS_LOGMNR. COLUMN_PRESENT () functionsSAFE_RESUME_SCNNUMBERReserved for future useCSCNNUMBERThis column is deprecated in favor of the COMM工T_SCN columnOBJECT_IDRAW(16)Object identifi
26、er for DMLs to XMLType tables. For changes to non-typed tables, this column is NULL.EDITION_NAMEVARCHAR2(30)Identifies the edition in which a DDL statement was executedCL工ENT_工DVARCHAR2(64)Client identifier in the session that performed the operation, if available.v$logmnr_dictionaryColumnDatatypeDe
27、scriptionDB_NAMEVARCHAR2(9)Name of the databaseDB_IDNUMBERDatabase IDDB_CREATEDDATECreation date of the source database (corresponds to the CREATED column in the V$DATABASE view)TIMESTAMPDATEDate when the dictionary was createdRESET_SCNNUMBERReset log SCN when the dictionary was createdRESET_SCN_TIM
28、EDATETimestamp of the reset log SCN when the dictionary was createdDB_VERSION_TIMEDATEVersion time for the source database (corresponds to the VERSION_TIME column in the V$DATABASE view)DB_CHARACTER_SETVARCHAR2(30)Character set of the source databaseDB_VERS工ONVARCHAR2(64)This column is deprecated.DB
29、_STATUSVARCHAR2(64)This column is deprecated.DICTIONARY_SCNNUMBERDatabase checkpoint SCN at which the dictionary was createdENABLED_THREAD_MAPRAW(16)This column is deprecated.DB_TXN_SCNNUMBERSCN at which the dictionary was createdFILENAMEVARCHAR2(512)Dictionary file nameINFOVARCHAR2(32)Informational
30、/Status messageBAD_DATE indicates that the SCN of the dictionary file does not match the SCN range of the log filesSTATUSNUMBERA 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_
31、logsColumnDatatypeDescriptionLOG_IDNUMBERThis column is deprecated.FILENAMEVARCHAR2(512)Name of the log fileLOW_TIMEDATEOldest date of any records in the fileH工GH_T工MEDATEMost recent date of any records in the fileDB_IDNUMBERDatabase IDDB_NAMEVARCHAR2(8)Name of the databaseRESET_SCNNUMBERResetlogs S
32、CN of the database incarnation that generated the log fileRESET_SCN_TIMEDATEResetlogs timestamp of the database incarnation that generated the log fileCOMPATIBLEVARCHAR2(17)The setting of the database COMPATIBLE initialization parameter at the time the log file was generatedTHREAD_IDNUMBERThread num
33、berTHREAD_SQNNUMBERThread sequence numberLOW_SCNNUMBERSCN allocated when log switched intoNEXT_SCNNUMBERSCN after this log. Low SCN of the next log.DICTIONARY_BEGINVARCHAR2(3)Indicates whether dictionary dumped to redo logs starts in this redo log (YES) or not (NO)DICTIONARY_ENDVARCHAR2(3)Indicates
34、whether dictionary dumped to redo logs ends in this redo log (YES) or not (NO)TYPEVARCHAR2(7)Redo log file type: ARCHIVED ONLINEBLOCKSIZENUMBERDatabase block sizeFILESIZENUMBERSize of the redo file (in bytes)INFOVARCHAR2(32)Informational message. A value of MISSING_LOGFILE will be assigned to a row
35、entry where a needed log file is missing from the list of log files.STATUSNUMBERStatus of the redo log file: 0 - Will be read 1 - First to be read 2 - Not needed 4 - Missing log filev$tablespaceColumnDatatypeDescriptionTS#NUMBERTablespace numberNAMEVARCHAR2(30)Tablespace nameobjects)SEGMENT_TYPEVARC
36、HAR2(18)Type of the segment: INDEXPARTITION, TABLE PARTITIONTABLESPACE_NAMEVARCHAR2(30)Name of the tablespace containing the extentEXTENT.工DNUMBERExtent number in the segmentFILE_IDNUMBERFile identifier number of the file containing the extentBLOCK_IDNUMBERStarting block number of the extentBYTESNUM
37、BERSize 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_S工ZENUMBERNOT NULLTablespace block sizeINITIAL_EXTEN
38、TNUMBERDefault initial extent sizeINCLUDED_IN_DATABASE_BACKUPVARCHAR2(3)Indicates whether the tablespace is included in full database backups using the backup database RMAN command (yes) ornot (NO); NO only if the CONFIGURE EXCLUDE RMAN command was used for this tablespaceBIGFILEVARCHAR2(3)Indicates
39、 whether the tablespace is a bigfile tablespace (yes) or asmallfile tablespace (NO)FLASHBACK_ONVARCHAR2(3)Indicates whether the tablespace participates in flashback database operations (YES) or not (NO)ENCRYPT_工N_BACKUPVARCHAR2(3)Indicates whether encryption is turned ON or off at the tablespace lev
40、el: ON - Encryption is turned ON at the tablespace level OFF - Encryption is turned OFF at the tablespace level NULL - Encryption is neither explicitly turned on nor off at the tablespace level (default or when cleared)v$tempfileColumnDatatypeDescriptionFILE#NUMBERAbsolute file numberCREATION_CHANGE
41、 #NUMBERCreation System Change Number (SCN)CREAT工ON_T工MEDATECreation timeTS#NUMBERTablespace numberRFILE#NUMBERRelative file number in the tablespaceSTATUSVARCHAR2(7)Status of the file (OFFLINE|ONLINE)ENABLEDVARCHAR2(10)Enabled for read and/or writeBYTESNUMBERSize of the file in bytes (from the file
42、 header)BLOCKSNUMBERSize of the file in blocks (from the file header)CREATE_BYTESNUMBERCreation size of the file (in bytes)BLOCK_SIZENUMBERBlock size for the fileNAMEVARCHAR2(513)Name of the filev$filestatColumnDatatypeDescriptionFILE#NUMBERNumber of the filePHYRDSNUMBERNumber of physical reads done
43、PHYWRTSNUMBERNumber of times DBWR is required to writePHYBLKRDNUMBERNumber of physical blocks readOPTIMIZED_PHYBLKRDNUMBERNumber of physical reads from Database Smart Flash Cache blocksPHYBLKWRTNUMBERNumber of blocks written to disk, which may be the same as PHYWRTS if all writes are single blocksSI
44、NGLEBLKRDSNUMBERNumber of single block readsREADT工MNUMBERTime (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is true; 0 if falseWRITETIMNUMBERTime (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is true; 0 if falseSINGLEBLKRDTIMNUMBER
45、Cumulative single block read time (in hundredths of a second)AVGIOTIMNUMBERAverage time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if falseLSTIOTIMNUMBERTime (in hundredths of a second) spent doing the last I/O, if the T工MED_STAT工ST工CS parameter is true; 0 if falseMINIOTIMNUMBERMinimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is true; 0 if falseMAXIORTMNUMBERMaximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is true; 0 if falseMAXIOW