深入理解V$视图

平常大家做为DBA经常用到v$视图，但不知道到底有多少v$视图。下面做如下讲解。

1，查看v$fixed_table表的视图总和

SQL> SELECT COUNT(*) FROM v$fixed_table;

  COUNT(*)
----------
      1383

 

2，分类统计视图总和
SQL> SELECT (SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'V$%') V$_VIEW,
  2         (SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'GV$%') GV$_VIEW,
  3         (SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'X$%') X$1_VIEW,
  4         ((SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'V$%') +
  5         (SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'GV$%') +
  6         (SELECT COUNT(*) FROM V$FIXED_TABLE WHERE NAME LIKE 'X$%')) sum_view_count
  7    FROM DUAL;

   V$_VIEW   GV$_VIEW   X$1_VIEW SUM_VIEW_COUNT
---------- ---------- ---------- --------------
       396        372        613           1381

 

 

3，还有两个视图如下。

SQL> SELECT NAME
  2    FROM V$FIXED_TABLE
  3   WHERE NAME NOT LIKE 'V_%'
  4     AND NAME NOT LIKE 'GV_%'
  5     AND NAME NOT LIKE 'X_%';

NAME
------------------------------
GO$SQL_BIND_CAPTURE
O$SQL_BIND_CAPTURE

 

 

讨论下dba_视图是从oracle底层数据库的表中得到.

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#
上述说明DBA_视图不是从X_表或者V_视图中派生。