站長(zhǎng)資訊網(wǎng)本篇文章給大家?guī)?lái)了關(guān)于Oracle的相關(guān)知識(shí)，其中主要整理了數(shù)據(jù)字典、數(shù)據(jù)字典視圖及動(dòng)態(tài)性能視圖的相關(guān)問(wèn)題，數(shù)據(jù)字典記錄數(shù)據(jù)庫(kù)最基本的信息，包括數(shù)據(jù)字典基本表和數(shù)據(jù)字典視圖，下面一起來(lái)看一下，希望對(duì)大家有幫助。
推薦教程：《Oracle視頻教程》
1.數(shù)據(jù)字典
1.1 概念
??數(shù)據(jù)字典記錄數(shù)據(jù)庫(kù)最基本的信息，包括數(shù)據(jù)字典基本表和數(shù)據(jù)字典視圖；數(shù)據(jù)字典基本表由 $ORACLE_HOMERDBMSADMINsql.bsq創(chuàng)建
??數(shù)據(jù)字典基本表，屬于sys用戶，存放在system表空間，用戶不能手動(dòng)去修改數(shù)據(jù)字典基本表；
??為了簡(jiǎn)化對(duì)數(shù)據(jù)字典基本表的使用，oracle提供了數(shù)據(jù)字典視圖，還為數(shù)據(jù)字典視圖創(chuàng)建了公有同義詞，方便用戶使用，數(shù)據(jù)字典視圖和同義詞的創(chuàng)建通過(guò)catalog.sql
1.2 數(shù)據(jù)字典視圖
1.2.1 三種類型
??USER_類型的視圖表示當(dāng)前登錄用戶擁有的信息；
??ALL_類型的視圖表示當(dāng)前登錄用戶有權(quán)限看到的信息；
??DBA_類型的視圖表示數(shù)據(jù)庫(kù)管理員能夠看到的信息
如：
（1）USER_類型 (user_tables)
對(duì)應(yīng)了2個(gè)數(shù)據(jù)庫(kù)對(duì)象，一個(gè)是數(shù)據(jù)字典視圖，一個(gè)是同義詞，而且是先基于數(shù)據(jù)字典表創(chuàng)建數(shù)據(jù)字典視圖，然后為視圖創(chuàng)建了一個(gè)同名的同義詞
查詢USER_TABLES對(duì)應(yīng)的數(shù)據(jù)庫(kù)對(duì)象：
select * from dba_objects d where d.OBJECT_NAME='USER_TABLES';
查詢USER_TABLES對(duì)應(yīng)的視圖：
select * from dba_views d where d.view_name='USER_TABLES';
查詢USER_TABLES對(duì)應(yīng)視圖的創(chuàng)建語(yǔ)句：
SQL> SET LONG 10000;SQL> select d.text from dba_views d where d.view_name='USER_TABLES';TEXT--------------------------------------------------------------------------------select o.name, decode(bitand(t.property,2151678048), 0, ts.name, decode(t.ts#, 0, null, ts.name)), decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), decode(bitand(t.property, 17179869184), 17179869184, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(t.property, 17179869184), 17179869184, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(t.property, 17179869184), 17179869184, ds.minext_stg, s.minexts), decode(bitand(t.property, 17179869184), 17179869184, ds.maxext_stg, s.maxexts), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(t.property, 17179869184), 17179869184, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(bitand(t.property, 17179869184), 17179869184, ds.frlins_stg, decode(s.lists, 0, 1, s.lists)))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(bitand(t.property, 17179869184), 17179869184, ds.maxins_stg, decode(s.groups, 0, 1, s.groups)))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null), decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5), decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, 'YES', 'NO'), decode(bitand(t.property, 64), 64, 'IOT', decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW', decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))), decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'), decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'), decode(bitand(t.property, 8192), 8192, 'YES', decode(bitand(t.property, 1), 0, 'NO', 'YES')), decode(bitand(o.flags, 2), 2, 'DEFAULT', decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')), decode(bitand(o.flags, 2), 2, 'DEFAULT', decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT')), decode(bitand(o.flags, 2), 2, 'DEFAULT', decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT')), decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'), decode(bitand(t.flags, 512), 0, 'NO', 'YES'), decode(bitand(t.flags, 256), 0, 'NO', 'YES'), decode(bitand(o.flags, 2), 0, NULL, decode(bitand(t.property, 8388608), 8388608, 'SYS$SESSION', 'SYS$TRANSACTION')), decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'), decode(bitand(o.flags, 2), 2, 'NO', decode(bitand(t.property, 2147483648), 2147483648, 'NO', decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))), decode(bitand(t.property, 1024), 0, null, cu.name), decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'), case when (bitand(t.property, 32) = 32) then null when (bitand(t.property, 17179869184) = 17179869184) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(t.property, 32) = 32) then null when (bitand(t.property, 17179869184) = 17179869184) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 -- 0x1000000 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, decode(bitand(o.flags, 128), 128, 'YES', 'NO'), decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'), decode(bitand(t.property, 17179869184), 17179869184, 'NO', decode(bitand(t.property, 32), 32, 'N/A', 'YES')), decode(bitand(t.property,16492674416640),2199023255552,'FORCE', 4398046511104,'MANUAL','DEFAULT')from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o, sys.deferred_stg$ ds, sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppiwhere o.owner# = userenv('SCHEMAID') and o.obj# = t.obj# and bitand(t.property, 1) = 0 and bitand(o.flags, 128) = 0 and t.bobj# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.obj# = ds.obj# (+) and t.dataobj# = cx.obj# (+) and cx.owner# = cu.user# (+) and ksppi.indx = ksppcv.indx and ksppi.ksppinm = '_dml_monitoring_enabled'
查詢同義詞：
select * from dba_synonyms d where d.synonym_name='USER_TABLES'
查詢當(dāng)前登錄用戶擁有的表
（2）ALL_類型(all_tables)
（3）DBA_類型(dba_tables)
2.動(dòng)態(tài)性能視圖
2.1 概念
??存儲(chǔ)數(shù)據(jù)庫(kù)中每時(shí)每刻都在變化的信息，主要是數(shù)據(jù)庫(kù)的活動(dòng)狀態(tài)信息。把存儲(chǔ)在內(nèi)存里的信息，和存儲(chǔ)在控制文件里的信息，以視圖的形式展現(xiàn)出來(lái)；
2.2 常見(jiàn)的動(dòng)態(tài)性能視圖
v$parameter
初始化參數(shù)文件中所有項(xiàng)的值
v$process
當(dāng)前進(jìn)程的信息
v$session
有關(guān)會(huì)話的信息
v$sysstat
基于當(dāng)前操作會(huì)話進(jìn)行的系統(tǒng)統(tǒng)計(jì)
v$log
從控制文件中提取有關(guān)重做日志組的信息
v$logfile
有關(guān)實(shí)例重做日志組文件名及其位置的信息
v$lock
當(dāng)前進(jìn)程已獲得和正在請(qǐng)求的鎖信息
v$transaction
數(shù)據(jù)庫(kù)事務(wù)信息
v$fixed_view_definition
記錄所有動(dòng)態(tài)性能視圖的定義信息
推薦教程：《Oracle視頻教程》
