[20190324]奇怪的GV$FILESPACE_USAGE视图.txt

[20190324]奇怪的GV$FILESPACE_USAGE视图.txt

--//发现GV$FILESPACE_USAGE定义很奇怪,做一个记录.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//GV$FILESPACE_USAGE的定义如下:
SCOTT@book> select VIEW_NAME,VIEW_DEFINITION c100  from V$FIXED_VIEW_DEFINITION  where view_name='GV$FILESPACE_USAGE';
VIEW_NAME                      C100
------------------------------ ----------------------------------------------------------------------------------------------------
GV$FILESPACE_USAGE             SELECT inst_id, KTTEFINFOTSN, KTTEFINFOFNO, KTTEFINFOUSP,          KTTEFINFOSIZE, KTTEFINFOMSIZE, KT
                               TEFINFOSCNB,          KTTEFINFOSCNW, KTTEFINFOFLAG          FROM   X$KTTEFINFO
--//整理如下:
SELECT inst_id
      ,KTTEFINFOTSN
      ,KTTEFINFOFNO
      ,KTTEFINFOUSP
      ,KTTEFINFOSIZE
      ,KTTEFINFOMSIZE
      ,KTTEFINFOSCNB
      ,KTTEFINFOSCNW
      ,KTTEFINFOFLAG
  FROM X$KTTEFINFO;

2.增加数据文件:
ALTER TABLESPACE UNDOTBS1
  ADD DATAFILE '/mnt/ramdisk/book/undotbs02.dbf'
  SIZE 16M
  AUTOEXTEND OFF;

SCOTT@book> SELECT file#,ts#,rfile#,name,blocks,bytes FROM v$datafile;
FILE# TS# RFILE# NAME                             BLOCKS        BYTES
----- --- ------ -------------------------------- ------ ------------
    1   0      1 /mnt/ramdisk/book/system01.dbf    97280    796917760
    2   1      2 /mnt/ramdisk/book/sysaux01.dbf   120320    985661440
    3   2      3 /mnt/ramdisk/book/undotbs01.dbf  110720    907018240
    4   4      4 /mnt/ramdisk/book/users01.dbf     16384    134217728
    5   6      5 /mnt/ramdisk/book/example01.dbf   44320    363069440
    6   7      6 /mnt/ramdisk/book/tea01.dbf        5120     41943040
    7   2      7 /mnt/ramdisk/book/undotbs02.dbf    2048     16777216
7 rows selected.

SCOTT@book> select * from GV$FILESPACE_USAGE;
INST_ID TABLESPACE_ID         RFNO ALLOCATED_SPACE    FILE_SIZE FILE_MAXSIZE CHANGESCN_BASE CHANGESCN_WRAP FLAG
------- ------------- ------------ --------------- ------------ ------------ -------------- -------------- ----
      1             0            1           96112        97280      4194302      395636096              3    2
      1             1            2          107392       120320      4194302      395638024              3    2
      1             2            1            2936       110720       664925      395639290              3    6
      1             3            1             256        52992        52992      395639290              3    6
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      1             4            4            1904        16384      4194302      395637154              3    2
      1             6            5           39704        44320      4194302      395636096              3    2
      1             7            6             128         5120      4194302      395636096              3    2
7 rows selected.

--//RFNO根本没有7.视乎表示的含义与rfile#不同.奇怪?视乎相同的表空间RFNO一样.
--//TABLESPACE_ID到底表示什么.乱.

--//GV$FILESPACE_USAGE.FILE_SIZE应该与datafile.BLOCKS一致,但是看下划线这行应该对应/mnt/ramdisk/book/undotbs02.dbf
--//但是大小对不上很奇怪,单位是块不是字节,这个视图很奇怪,不建议使用.
--//视乎flga=6表示undo表空间的数据文件.

--//再增加数据文件看看:
ALTER TABLESPACE UNDOTBS1
  ADD DATAFILE '/mnt/ramdisk/book/undotbs03.dbf'
  SIZE 16M
  AUTOEXTEND OFF;

SCOTT@book> select * from GV$FILESPACE_USAGE;
INST_ID TABLESPACE_ID RFNO ALLOCATED_SPACE    FILE_SIZE FILE_MAXSIZE CHANGESCN_BASE CHANGESCN_WRAP FLAG
------- ------------- ---- --------------- ------------ ------------ -------------- -------------- ----
      1             0    1           96112        97280      4194302      395636096              3    2
      1             1    2          107392       120320      4194302      395640371              3    2
      1             2    1            2560       112768       664919      395643160              3    6
      1             3    1             256        52992        52992      395643160              3    6
      1             4    4            1904        16384      4194302      395637154              3    2
      1             6    5           39704        44320      4194302      395636096              3    2
      1             7    6             128         5120      4194302      395636096              3    2
7 rows selected.
--//还是7条记录

SCOTT@book> SELECT file#,ts#,rfile#,name,blocks,bytes FROM v$datafile;
FILE# TS# RFILE# NAME                             BLOCKS        BYTES
----- --- ------ -------------------------------- ------ ------------
    1   0      1 /mnt/ramdisk/book/system01.dbf    97280    796917760
    2   1      2 /mnt/ramdisk/book/sysaux01.dbf   120320    985661440
    3   2      3 /mnt/ramdisk/book/undotbs01.dbf  110720    907018240
    4   4      4 /mnt/ramdisk/book/users01.dbf     16384    134217728
    5   6      5 /mnt/ramdisk/book/example01.dbf   44320    363069440
    6   7      6 /mnt/ramdisk/book/tea01.dbf        5120     41943040
    7   2      7 /mnt/ramdisk/book/undotbs02.dbf    2048     16777216
    8   2      8 /mnt/ramdisk/book/undotbs03.dbf    2048     16777216
8 rows selected.
--//重启数据库看看:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> select * from GV$FILESPACE_USAGE;
     INST_ID TABLESPACE_ID         RFNO ALLOCATED_SPACE    FILE_SIZE FILE_MAXSIZE CHANGESCN_BASE CHANGESCN_WRAP         FLAG
------------ ------------- ------------ --------------- ------------ ------------ -------------- -------------- ------------
           1             0            1           96112        97280      4194302      395645171              3            2
           1             1            2          107392       120320      4194302      395645171              3            2
           1             2            1            2720       114816       664914      395645171              3            6
           1             3            1             128        52992        52992      395645171              3            6
           1             4            4            1904        16384      4194302      395645171              3            2
           1             6            5           39704        44320      4194302      395645171              3            2
           1             7            6             128         5120      4194302      395645171              3            2
7 rows selected.
--//依旧是7行.

3.再看看看CHANGESCN_WRAP:
--//CHANGESCN_WRAP有表示什么?表示scn的wrap吗?我查询生产系统如下:
set linesize 200
select * from GV$FILESPACE_USAGE where rownum<=10;
   INST_ID TABLESPACE_ID       RFNO ALLOCATED_SPACE  FILE_SIZE FILE_MAXSIZE CHANGESCN_BASE CHANGESCN_WRAP       FLAG
---------- ------------- ---------- --------------- ---------- ------------ -------------- -------------- ----------
         1             0          1           96000      96000      4194302     2895231817              3          2
         1             0         29            9848     512000       512000     1524748330              6          2
         1             1          2           66560      66560      4194302     1658426615              6          2
         1             1         28          412112     512000       512000     1682050392              6          2
         1             2          1           85232    5760000     10948604     1682775853              6          6
         1             3          1          107520    4194176      4194302     1682775853              6          6
         1             4          4          473656    2439840      4194302     1459217743              6          2
         1             5          1           17456    5760000     10948604     1682654360              6          6
         1             6          6         1541808    1622016      4194302     1608398721              6          2
         1             7          7         4194303    4194303      4194303     1190175374              5          2
--//CHANGESCN_WRAP有6,3,5,表示什么??

总结:
--//总之这个视图很奇怪,不建议使用,视乎在查询undo表空间上有问题,细节我探究了.

原文地址:https://www.cnblogs.com/lfree/p/10592101.html