查询动态性能视图基表(V$PROCESS)
注:动态性能视图的基表是不能通过DBA_VIEWS查看的,而是V$FIXED_VIEW_DEFINITION
SQL> select * from v$fixed_table where name='V$PROCESS';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
V$PROCESS 4294950917 VIEW 65537
SQL> select view_definition from v$fixed_view_definition where view_name='V$PROCESS'
2 ;
VIEW_DEFINITION
--------------------------------------------------------------------------------
select addr, pid, spid, pname, username, serial#, terminal, program, traceid, tr
acefile, background, latchwait,latchspin,pga_used_mem,pga_alloc_mem,pga_freeable
_mem,pga_max_mem from gv$process where inst_id = USERENV('Instance')
SQL> select * from v$fixed_table where name='GV$PROCESS';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
GV$PROCESS 4294951256 VIEW 65537
SQL> select view_definition from v$fixed_view_definition where view_name='GV$PROCESS';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id, addr, indx, ksuprpid, ksuprpname, ksuprunm, ksuprser, ksuprtid,
ksuprpnm, ksuprtfi, ksuprtfn, decode(bitand(ksuprflg,2),0,null,1), decode(ksllaw
at,hextoraw('00'),null,ksllawat), decode(ksllaspn,hextoraw('00'),null,ksllaspn),
ksuprpum,ksuprpnam+ksuprpram,ksuprpfm, case when ksuprpnam+ksuprpram > ksuprpmm
then ksuprpnam+ksuprpram else ksuprpmm end from x$ksupr where bita
VIEW_DEFINITION
--------------------------------------------------------------------------------
nd(ksspaflg,1)!=0
可以看出数据库在启动过程中创建动态性能视图的步骤为:动态创建X$(表),在X$基础上创建GV$(视图),在GV$基础上创建V$(视图)
查询数据字典视图基表(DBA_DATA_FILES)
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_DATA_FILES';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS DBA_DATA_FILES VIEW
PUBLIC DBA_DATA_FILES SYNONYM
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='DBA_DATA_FILES'
2 ;
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC DBA_DATA_FILES SYS DBA_DATA_FILES
SQL> select text from dba_views where view_name='DBA_DATA_FILES';
TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#