oracle系統(tǒng)表詳解資料

Oracle常用系統(tǒng)表

dba_開頭

dba_users數(shù)據(jù)庫用戶信息

dba_segments表段信息

dba_objects數(shù)據(jù)庫對象信息

dba_extents數(shù)據(jù)區(qū)信息

dba_tablespaces數(shù)據(jù)庫表空間信息

dba_data_files數(shù)據(jù)文件設(shè)置信息

dba_temp_files臨時數(shù)據(jù)文件信息

dba_rollback_segs回滾段信息

dba_ts_quotas用戶表空間配額信息

dba_free_space數(shù)據(jù)庫空閑空間信息

dba_profiles數(shù)據(jù)庫用戶費源限制信息

dba_sys_privs用戶的系統(tǒng)權(quán)限信息

dba_tab_privs用戶具有的對象權(quán)限信息

dba_col_privs用戶具有的列對象權(quán)限信息

dba_role_privs用戶具有的角色信息

dba_audit_trail審計跟蹤記錄信息

dba_stmt_audit_opts審計設(shè)置信息

dba_audit_object對象審計結(jié)果信息

dba_audit_session會話審計結(jié)果信息

dbajndexes用戶模式的索引信息

user一開頭

user_objects用戶對象信息

user_source數(shù)據(jù)庫用戶的所有資源對象信息

user_segments用戶的表段信息

user_tables用戶的表對象信息

user_tab_columns用戶的表列信息

user_constraints用戶的對象約束信息

user_sys_privs當(dāng)前用戶的系統(tǒng)權(quán)限信息

user_tab_privs當(dāng)前用戶的對象權(quán)限信息

user_col_privs當(dāng)前用戶的表列權(quán)限信息

user_role_privs當(dāng)前用戶的角色權(quán)限信息

userjndexes用戶的索引信息

user_ind_columns用戶的索引對應(yīng)的表列信息

user_cons_columns用戶的約束對應(yīng)的表列信息

user__clusters用戶的所有簇信息

user_clu_columns用戶的簇所包含的內(nèi)容信息

user_cluster_hash_expressions散列簇的信息

V$開頭

v$database數(shù)據(jù)庫信息

v$datafile數(shù)據(jù)文件信息

v$controlfile控制文件信息

v$logfile重做日志信息

v$instance數(shù)據(jù)庫實例信息

V$log日志組信息

v$loghist日志歷史信息

v$sga數(shù)據(jù)庫SGA信息

v$parameter初始化參數(shù)信息

v$process數(shù)據(jù)庫服務(wù)器進程信息

v$bgprocess數(shù)據(jù)庫后臺進程信息

v$controlfile_record_section控制文件記載的各部分信息

v$thread線程信息

v$datafile_header數(shù)據(jù)文件頭所記載的信息

v$archived_log歸檔日志信息

v$archive_dest歸檔日志的設(shè)置信息

v$logmnr_contents歸檔日志分析的DMLDDL結(jié)果信息

v$logmnr_dictionary日志分析的字典文件信息

v$logmnr_logs日志分析的日志列表信息

v$tablespace表空間信息

v$tempfile臨時文件信息

v$filestat數(shù)據(jù)文件的I/O統(tǒng)計信息

v$undostatUndo數(shù)據(jù)信息

v$rollname在線回滾段信息

v$session會話信息

v$transaction事務(wù)信息

v$rollstat回滾段統(tǒng)計信息

v$pwfile_users特權(quán)用戶信息

v$sqlarea當(dāng)前查詢過的sql語句訪問過的資源及相關(guān)的信息

v$sql與v$sqlarea基本相同的相關(guān)信息

v$sysstat數(shù)據(jù)庫系統(tǒng)狀態(tài)信息

alL開頭

all_users數(shù)據(jù)庫所有用戶的信息

all_objects數(shù)據(jù)庫所有的對象的信息

all_def_audit_opts所有默認(rèn)的審計設(shè)置信息

all_tables所有的表對象信息

alljndexes所有的數(shù)據(jù)庫對象索引的信息

session.開頭

session_roles會話的角色信息

session_privs會話的權(quán)限信息

index.開頭

index_stats索引的設(shè)置和存儲信息

偽表

dual系統(tǒng)偽列表信息

dba_users

ColumnDatatypeNULLDescription

USERNAMEVARCHAR2(30)NOTNULLNameoftheuser

USER_IDNUMBERNOTNULLIDnumberoftheuser

PASSWORDVARCHAR2(30)Encryptedpassword(加密)

ACCOUNT_STATUSVARCHAR2(32)NOTNULLAccountstatus:

?OPEN

?EXPIRED(至期)

?EXPIRED(GRACE)

?LOCKED(TIMED)

?LOCKED

?EXPIRED&

LOCKED(TIMED)

?EXPIRED(GRACE)&

LOCKED(TIMED)

?EXPIRED&LOCKED

?EXPIRED(GRACE)&

LOCKED

LOCK_DATEDATEDatetheaccountwaslockedif

accountstatuswasLOCKED

EXPIRY_DATEDATEDateofexpirationofthe

account

DEFAULT_TABLESPACEVARCHAR2(30)NOTNULLDefaulttablespacefordata

TEMPORARY_TABLESPACEVARCHAR2(30)NOTNULLNameofthedefault

tablespacefortemporarytables

orthenameofatablespace

group

CREATEDDATENOTNULLUsercreationdate

PROFILEVARCHAR2(30)NOTNULLUserresourceprofilename

INITIAL_RSRC_CONSUMER_GROUPVARCHAR2(30)Initialresourceconsumer

groupfortheuser

EXTERNAL_NAMEVARCHAR2(4000)Userexternalname

dba_segments

ColumnDatatypeNULLDescription

OWNERVARCHAR2(30)Usernameofthesegment

owner

SEGMENT_NAMEVARCHAR2(81)Name,ifany,ofthesegment

PART工T工ON_NAMEVARCHAR2(30)ObjectPartitionName(Setto

NULLfornon-partitioned

objects)

SEGMENT_TYPEVARCHAR2(18)

Typeofsegment:INDEX

PARTITION,TABLE

PARTITION,TABLE,

CLUSTER,INDEX,

ROLLBACK,DEFERRED

ROLLBACK,TEMPORARY,

CACHE,LOBSEGMENTand

LOBINDEX

TABLESPACE_NAMEVARCHAR2(30)Nameofthetablespace

containingthesegment

HEADER_FILENUMBERIDofthefilecontainingthe

segmentheader

HEADER_BLOCKNUMBERIDoftheblockcontainingthe

segmentheader

BYTESNUMBERSize,inbytes,ofthesegment

BLOCKSNUMBERSize,inOracleblocks,ofthe

segment

EXTENTSNUMBERNumberofextentsallocatedto

thesegment

INITIAL_EXTENTNUMBERSizeinbytesrequestedforthe

initialextentofthesegmentat

createtime.(Oracleroundsthe

extentsizetomultiplesof5

blocksiftherequestedsizeis

greaterthan5blocks.)

NEXT_EXTENTNUMBERSizeinbytesofthenextextent

tobeallocatedtothesegment

MIN_EXTENTSNUMBERMinimumnumberofextents

allowedinthesegment

MAX_EXTENTSNUMBERMaximumnumberofextents

allowedinthesegment

PCT_工NCREASENUMBERPercentbywhichtoincrease

thesizeofthenextextentto

beallocated

FREELISTSNUMBERNumberofprocessfreelists

allocatedtothissegment

FREELIST_GROUPSNUMBERNumberoffreelistgroups

allocatedtothissegment

RELATIVE_FNONUMBERRelativefilenumberofthe

segmentheader

BUFFER_POOLVARCHAR2(7)Defaultbufferpoolforthe

object

dba_objects

ColumnDatatypeNULLDescription

OWNERVARCHAR2(30)NOTNULLOwneroftheobject

OBJECT_NAMEVARCHAR2(30)NOTNULLNameoftheobject

SUBOBJECT_NAMEVARCHAR2(30)Nameofthesubobject(for

example,partition)

OBJECT_IDNUMBERNOTNULLDictionaryobjectnumberofthe

object

DATA_OBJECT_IDNUMBERDictionaryobjectnumberofthe

segmentthatcontainsthe

object

Note：OBJECT_iDand

DATA_OBJECT—工Ddisplay

datadictionarymetadata.Do

notconfusethesenumbers

withtheunique16-byteobject

identifier{objectID)thatthe

OracleDatabaseassignsto

rowobjectsinobjecttablesin

thesystem.

OBJECT_TYPEVARCHAR2(19)Typeoftheobject(suchas

TABLE,INDEX)

CREATEDDATENOTNULLTimestampforthecreationof

theobject

LAST_DDL_TIMEDATENOTNULLTimestampforthelast

modificationoftheobject

resultingfromaDDL

statement(includinggrants

andrevokes)

TIMESTAMPVARCHAR2(20)Timestampforthespecification

oftheobject(characterdata)

STATUSVARCHAR2(7)

Statusoftheobject(VALID,

INVALID,OrN/A)

TEMPORARYVARCHAR2(1)Whethertheobjectis

temporary(thecurrent

sessioncanseeonlydatathat

itplacedinthisobjectitself)

GENERATEDVARCHAR2(1)Indicateswhetherthenameof

thisobjectwassystem

generated(Y)ornot(N)

SECONDARYVARCHAR2(1)Whetherthisisasecondary

objectcreatedbythe

ODCIIndexCreatemethod

oftheOracleDataCartridge

(YIN)

dba_extents

ColumnDatatypeNULLDescription

OWNERVARCHAR2(30)Ownerofthesegment

associatedwiththeextent

SEGMENT_NAMEVARCHAR2(81)Nameofthesegment

associatedwiththeextent

PART工T工ON_NAMEVARCHAR2(30)ObjectPartitionName(Setto

NULLfornon-partitioned

objects)

SEGMENT_TYPEVARCHAR2(18)

Typeofthesegment:INDEX

PARTITION,TABLE

PARTITION

TABLESPACE_NAMEVARCHAR2(30)Nameofthetablespace

containingtheextent

EXTENT_IDNUMBERExtentnumberinthesegment

FILE_IDNUMBERFileidentifiernumberofthefile

containingtheextent

BLOCK_IDNUMBERStartingblocknumberofthe

extent

BYTESNUMBERSizeoftheextentinbytes

BLOCKSNUMBERSizeoftheextentinOracle

blocks

RELATIVE_FNONUMBERRelativefilenumberofthefirst

extentblock

dba_tablespaces

ColumnDatatypeNULLDescription

TABLESPACE_NAMEVARCHAR2(30)NOTNULLNameofthetablespace

BLOCK_SIZENUMBERNOTNULLTablespaceblocksize

INITIAL_EXTENTNUMBERDefaultinitialextentsize

NEXT_EXTENTNUMBERDefaultincrementalextent

size

MIN_EXTENTSNUMBERNOTNULLDefaultminimumnumberof

extents

MAX_EXTENTSNUMBERDefaultmaximumnumberof

extents

PCT_工NCREASENUMBERDefaultpercentincreasefor

extentsize

MIN_EXTLENNUMBERMinimumextentsizeforthis

tablespace

STATUSVARCHAR2(9)Tablespacestatus:

?ONLINE

?OFFLINE

?READONLY

CONTENTSVARCHAR2(9)Tablespacecontents:

?UNDO

?PERMANENT

?TEMPORARY

LOGGINGVARCHAR2(9)Defaultloggingattribute:

?LOGGING

?NOLOGGING

FORCE_LOGGINGVARCHAR2(3)Indicateswhetherthe

tablespaceisunderforce

loggingmode(YES)ornot

(NO)

EXTENT_MANAGEMENTVARCHAR2(10)Indicateswhethertheextents

inthetablespaceare

dictio