step by step 查询v$tablespace 的基表

通过这个实验，可以发现动态性能视图最终建立在基表上。而基表很不友好，很难读懂，所以要在基表上建立动态性能视图，方便DBA们去识别读取。

SQL> select OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$TABLESPACE';

OBJECT_NAME    OBJECT_TYPE
-------------- -------------------
V$TABLESPACE   SYNONYM

 

SQL> select SYNONYM_NAME,TABLE_NAME from dba_synonyms where SYNONYM_NAME='V$TABLESPACE';

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$TABLESPACE                   V_$TABLESPACE

SQL> select OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$TABLESPACE';

OBJECT_NAME    OBJECT_TYPE
-------------- -------------------
V_$TABLESPACE  VIEW

 

SQL> select VIEW_NAME,TEXT from dba_views where VIEW_NAME='V_$TABLESPACE';
VIEW_NAME          TEXT
------------------ ----------------------------------------
V_$TABLESPACE      select "TS#","NAME","INCLUDED_IN_DATABAS
                   E_BACKUP","BIGFILE","FLASHBACK_ON","ENCR
                   YPT_IN_BACKUP" from v$tablespace

 

 

下面很重要，因为上面一步查出来的结果发现是v$tablespace,等于转了一圈又回来了，但是这个v$tablespace不是最初查询的v$tablespace。

怎么解决这个问题呢？ 
那就要用到v$fixed_table这个动态性能视图来确定这个动态性能视图是基于哪个对象建立的。

 

SQL> select NAME,TYPE from v$fixed_table where NAME='V$TABLESPACE';
NAME                           TYPE
------------------------------ -----
V$TABLESPACE                   VIEW

 

看到了吧，V$TABLESPACE 其实是个视图，接着V$FIXED_VIEW_DEFINITION这个动态性能视图挖掘

SQL> select * from V$FIXED_VIEW_DEFINITION where  VIEW_NAME='V$TABLESPACE';
VIEW_NAME          VIEW_DEFINITION
------------------ ------------------------------------------------------------
V$TABLESPACE       select  TS# , NAME, INCLUDED_IN_DATABASE_BACKUP, BIGFILE, FL
                   ASHBACK_ON, ENCRYPT_IN_BACKUP from GV$TABLESPACE where inst_
                   id = USERENV('Instance')

SQL> select OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='GV$TABLESPACE';
OBJECT_NAME    OBJECT_TYPE
-------------- -------------------
GV$TABLESPACE  SYNONYM

SQL> select SYNONYM_NAME,TABLE_NAME from dba_synonyms where SYNONYM_NAME='GV$TABLESPACE';
SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
GV$TABLESPACE                  GV_$TABLESPACE

 

SQL> select OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='GV_$TABLESPACE';

OBJECT_NAME    OBJECT_TYPE
-------------- -------------------
GV_$TABLESPACE VIEW

SQL> select VIEW_NAME,TEXT from dba_views where VIEW_NAME='GV_$TABLESPACE';
VIEW_NAME          TEXT
------------------ ----------------------------------------
GV_$TABLESPACE     select "INST_ID","TS#","NAME","INCLUDED_
                   IN_DATABASE_BACKUP","BIGFILE","FLASHBACK
                   _ON","ENCRYPT_IN_BACKUP" from gv$tablespace

回到了gv$tablespace，又开始转圈了；接着使用v$fixed_table视图查询

 

SQL> select NAME,TYPE from v$fixed_table where NAME='GV$TABLESPACE';
NAME                           TYPE
------------------------------ -----
GV$TABLESPACE                  VIEW

 

SQL> select * from V$FIXED_VIEW_DEFINITION where VIEW_NAME='GV$TABLESPACE';

VIEW_NAME          VIEW_DEFINITION
------------------ ------------------------------------------------------------
GV$TABLESPACE      select inst_id,tstsn,tsnam,  decode(bitand(tsflg, 1+2),
                    1, 'NO', 2,'NO','YES'),  decode(bitand(tsflg, 4), 4,'YES','
                   NO'),  decode(bitand(tsflg, 8), 8,'NO','YES'),  decode(bitan
                   d(tsflg, 16+32), 16, 'ON', 32, 'OFF', to_char(null)) from x$kccts where tstsn != -1

 

通过一系列的追踪，发现v$tablespace是基于x$kccts建立的，那么x$kccts中到底是什么呢？

下面来看看，我唯一能看懂的就是TSNAM列，其他的都看不懂。

所以，在基表上建立动态性能视图就是为了增强可读性。

下面是截图，点击能看全图，也可以看下面的文字，不过比较乱。

SQL> select * from x$kccts;

ADDR           INDX    INST_ID      TSRNO      TSTSN TSNAM         TSFLG      TSDFP TSPSS            TSPST     TSPCS            TSPCT
-------- ---------- ---------- ---------- ---------- -------- ---------- ---------- ---------------- --------- ---------------- --------------------
B7EEF290          0          1          1          0 SYSTEM            0          1 0                          0
B7EEF290          1          1          2          1 UNDOTBS1          0          2 0                          0
B7EEF290          2          1          3          2 SYSAUX            0          3 0                          0
B7EEF290          3          1          4          4 USERS             0          4 0                          0
B7EEF290          4          1          5          3 TEMP              1          1 0                          0
B7EEF290          5          1          6          6 EXAMPLE           0          5 0                          0
B7EEF290          6          1          7          7 TEST              0          6 0                          0
B7EEF290          7          1          8          8 T6                0          7 0                          0