[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表空間上有問題,細節我探究了.

[20190324]奇怪的GV$FILESPACE_USAGE檢視.txt

相關文章