[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表空间上有问题,细节我探究了.