查询数据字典基表

最新推荐文章于 2023-05-21 14:07:33 发布

bitko

最新推荐文章于 2023-05-21 14:07:33 发布

阅读量2k

点赞数

分类专栏： Oracle-Dict

本文链接：https://blog.csdn.net/gumengkai/article/details/53200301

版权

Oracle-Dict 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

查询动态性能视图基表(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#