查询表空间剩余空间百分比的错误方法纠正:
查询表空间剩余空间百分比的两种方法:
方法1:
SQL> select a.tablespace_name,100*sum(a.bytes)/sum(b.bytes)
2 from dba_free_space a,dba_data_files b
3 where a.tablespace_name=b.tablespace_name
4 group by a.tablespace_name;
TABLESPACE_NAME 100*SUM(A.BYTES)/SUM(B.BYTES)
------------------------------ -----------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 65.4270833333333
INDX 99.75
ODM 53.125
SYSTEM 0.81875
TEST 46.25
TOOLS 39.375
UNDOTBS1 12.0025143678161
USERS 99.75
XDB 12.2731356693621
12 rows selected
方法2:
SQL> select a.tablespace_name,100*sum(a.bytes)/sum(b.bytes)
2 from sys.sm$ts_free a,sys.sm$ts_avail b
3 where a.tablespace_name=b.tablespace_name
4 group by a.tablespace_name;
TABLESPACE_NAME 100*SUM(A.BYTES)/SUM(B.BYTES)
------------------------------ -----------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 98.140625
INDX 99.75
ODM 53.125
SYSTEM 9.825
TEST 92.5
TOOLS 39.375
UNDOTBS1 96.0201149425287
USERS 99.75
XDB 49.0925426774483
12 rows selected
SQL>
比较以上两个查询结果,发现system表空间剩余空间百分比不同,我们来看看dba_free_space
和dba_data_files中的数据:
SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM 1 54953 786432 96 1
SYSTEM 1 55177 3145728 384 1
SYSTEM 1 55817 327680 40 1
SYSTEM 1 55865 655360 80 1
SYSTEM 1 56073 2097152 256 1
SYSTEM 1 56841 5242880 640 1
SYSTEM 1 57737 1048576 128 1
SYSTEM 1 57993 1048576 128 1
SYSTEM 1 58249 1048576 128 1
SYSTEM 1 58505 1048576 128 1
SYSTEM 1 59017 1572864 192 1
SYSTEM 1 59913 33488896 4088 1
UNDOTBS1 2 129 65536 8 2
UNDOTBS1 2 145 65536 8 2
UNDOTBS1 2 169 786432 96 2
UNDOTBS1 2 905 1048576 128 2
UNDOTBS1 2 1673 1179648 144 2
UNDOTBS1 2 1833 786432 96 2
UNDOTBS1 2 2185 2097152 256 2
UNDOTBS1 2 2953 431947776 52728 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
CWMLITE 3 2273 2359296 288 3
DRSYS 4 1241 10813440 1320 4
EXAMPLE 5 19057 524288 64 5
INDX 6 9 26148864 3192 6
ODM 7 1201 11141120 1360 7
TOOLS 8 777 4128768 504 8
USERS 9 9 26148864 3192 9
XDB 10 1353 2031616 248 10
XDB 10 3929 65536 8 10
XDB 10 4353 262144 32 10
XDB 10 5953 42401792 5176 10
FORP 12 17 35586048 4344 12
FORP 12 4489 1011810304 123512 12
FORP 11 4617 1010761728 123384 11
TEST 13 41 262144 32 13
TEST 13 81 4587520 560 13
36 rows selected
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
Y:/ORACLE/ORADATA/FORP/SYSTEM01.DBF 1 SYSTEM 524288000 64000 AVAILABLE 1 YES 3435972198 4194302 1280 524222464 63992
Y:/ORACLE/ORADATA/FORP/UNDOTBS01.DBF 2 UNDOTBS1 456130560 55680 AVAILABLE 2 YES 3435972198 4194302 640 456065024 55672
Y:/ORACLE/ORADATA/FORP/CWMLITE01.DBF 3 CWMLITE 20971520 2560 AVAILABLE 3 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/DRSYS01.DBF 4 DRSYS 20971520 2560 AVAILABLE 4 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/EXAMPLE01.DBF 5 EXAMPLE 156631040 19120 AVAILABLE 5 YES 3435972198 4194302 80 156565504 19112
Y:/ORACLE/ORADATA/FORP/INDX01.DBF 6 INDX 26214400 3200 AVAILABLE 6 YES 3435972198 4194302 160 26148864 3192
Y:/ORACLE/ORADATA/FORP/ODM01.DBF 7 ODM 20971520 2560 AVAILABLE 7 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/TOOLS01.DBF 8 TOOLS 10485760 1280 AVAILABLE 8 YES 3435972198 4194302 40 10420224 1272
Y:/ORACLE/ORADATA/FORP/USERS01.DBF 9 USERS 26214400 3200 AVAILABLE 9 YES 3435972198 4194302 160 26148864 3192
Y:/ORACLE/ORADATA/FORP/XDB01.DBF 10 XDB 91176960 11130 AVAILABLE 10 YES 3435972198 4194302 80 91095040 11120
Y:/ORACLE/ORADATA/FORP/FORP1.ORA 11 FORP 1048576000 128000 AVAILABLE 11 NO 0 0 0 1048510464 127992
Y:/ORACLE/ORADATA/FORP/FORP2.ORA 12 FORP 1048576000 128000 AVAILABLE 12 NO 0 0 0 1048510464 127992
Y:/ORACLE/ORADATA/FORP/TEST.ORA 13 TEST 5242880 640 AVAILABLE 13 YES 3435972198 4194302 1 5177344 632
13 rows selected
对比发现:
dba_free_space 中针对某一表空间的记录条数不一定是1条(将影响sum(b.bytes)的值(几条就是几倍)),同理dba_data_files 中针对某一表空间的记录条数不一定是1条(将影响sum(a.bytes)的值(几条就是几倍))
所以使用第一种方法得到的结果是不正确的。
如果一定要用dba_free_space 和dba_data_files 来监控空闲空间情况,就用最笨的方法,如下:
SQL> select a.tablespace_name,100*a.sum_bytes/b.sum_bytes
2 from (select distinct tablespace_name,sum(bytes) sum_bytes
3 from dba_free_space
4 group by tablespace_name) a,
5 (select distinct tablespace_name,sum(bytes) sum_bytes
6 from dba_data_files
7 group by tablespace_name) b
8 where a.tablespace_name=b.tablespace_name
9 ;
TABLESPACE_NAME 100*A.SUM_BYTES/B.SUM_BYTES
------------------------------ ---------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 98.140625
INDX 99.75
ODM 53.125
SYSTEM 9.825
TEST 92.5
TOOLS 39.375
UNDOTBS1 96.0201149425287
USERS 99.75
XDB 49.0925426774483
12 rows selected
SQL>
查询表空间剩余空间百分比的两种方法:
方法1:
SQL> select a.tablespace_name,100*sum(a.bytes)/sum(b.bytes)
2 from dba_free_space a,dba_data_files b
3 where a.tablespace_name=b.tablespace_name
4 group by a.tablespace_name;
TABLESPACE_NAME 100*SUM(A.BYTES)/SUM(B.BYTES)
------------------------------ -----------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 65.4270833333333
INDX 99.75
ODM 53.125
SYSTEM 0.81875
TEST 46.25
TOOLS 39.375
UNDOTBS1 12.0025143678161
USERS 99.75
XDB 12.2731356693621
12 rows selected
方法2:
SQL> select a.tablespace_name,100*sum(a.bytes)/sum(b.bytes)
2 from sys.sm$ts_free a,sys.sm$ts_avail b
3 where a.tablespace_name=b.tablespace_name
4 group by a.tablespace_name;
TABLESPACE_NAME 100*SUM(A.BYTES)/SUM(B.BYTES)
------------------------------ -----------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 98.140625
INDX 99.75
ODM 53.125
SYSTEM 9.825
TEST 92.5
TOOLS 39.375
UNDOTBS1 96.0201149425287
USERS 99.75
XDB 49.0925426774483
12 rows selected
SQL>
比较以上两个查询结果,发现system表空间剩余空间百分比不同,我们来看看dba_free_space
和dba_data_files中的数据:
SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM 1 54953 786432 96 1
SYSTEM 1 55177 3145728 384 1
SYSTEM 1 55817 327680 40 1
SYSTEM 1 55865 655360 80 1
SYSTEM 1 56073 2097152 256 1
SYSTEM 1 56841 5242880 640 1
SYSTEM 1 57737 1048576 128 1
SYSTEM 1 57993 1048576 128 1
SYSTEM 1 58249 1048576 128 1
SYSTEM 1 58505 1048576 128 1
SYSTEM 1 59017 1572864 192 1
SYSTEM 1 59913 33488896 4088 1
UNDOTBS1 2 129 65536 8 2
UNDOTBS1 2 145 65536 8 2
UNDOTBS1 2 169 786432 96 2
UNDOTBS1 2 905 1048576 128 2
UNDOTBS1 2 1673 1179648 144 2
UNDOTBS1 2 1833 786432 96 2
UNDOTBS1 2 2185 2097152 256 2
UNDOTBS1 2 2953 431947776 52728 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
CWMLITE 3 2273 2359296 288 3
DRSYS 4 1241 10813440 1320 4
EXAMPLE 5 19057 524288 64 5
INDX 6 9 26148864 3192 6
ODM 7 1201 11141120 1360 7
TOOLS 8 777 4128768 504 8
USERS 9 9 26148864 3192 9
XDB 10 1353 2031616 248 10
XDB 10 3929 65536 8 10
XDB 10 4353 262144 32 10
XDB 10 5953 42401792 5176 10
FORP 12 17 35586048 4344 12
FORP 12 4489 1011810304 123512 12
FORP 11 4617 1010761728 123384 11
TEST 13 41 262144 32 13
TEST 13 81 4587520 560 13
36 rows selected
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
Y:/ORACLE/ORADATA/FORP/SYSTEM01.DBF 1 SYSTEM 524288000 64000 AVAILABLE 1 YES 3435972198 4194302 1280 524222464 63992
Y:/ORACLE/ORADATA/FORP/UNDOTBS01.DBF 2 UNDOTBS1 456130560 55680 AVAILABLE 2 YES 3435972198 4194302 640 456065024 55672
Y:/ORACLE/ORADATA/FORP/CWMLITE01.DBF 3 CWMLITE 20971520 2560 AVAILABLE 3 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/DRSYS01.DBF 4 DRSYS 20971520 2560 AVAILABLE 4 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/EXAMPLE01.DBF 5 EXAMPLE 156631040 19120 AVAILABLE 5 YES 3435972198 4194302 80 156565504 19112
Y:/ORACLE/ORADATA/FORP/INDX01.DBF 6 INDX 26214400 3200 AVAILABLE 6 YES 3435972198 4194302 160 26148864 3192
Y:/ORACLE/ORADATA/FORP/ODM01.DBF 7 ODM 20971520 2560 AVAILABLE 7 YES 3435972198 4194302 80 20905984 2552
Y:/ORACLE/ORADATA/FORP/TOOLS01.DBF 8 TOOLS 10485760 1280 AVAILABLE 8 YES 3435972198 4194302 40 10420224 1272
Y:/ORACLE/ORADATA/FORP/USERS01.DBF 9 USERS 26214400 3200 AVAILABLE 9 YES 3435972198 4194302 160 26148864 3192
Y:/ORACLE/ORADATA/FORP/XDB01.DBF 10 XDB 91176960 11130 AVAILABLE 10 YES 3435972198 4194302 80 91095040 11120
Y:/ORACLE/ORADATA/FORP/FORP1.ORA 11 FORP 1048576000 128000 AVAILABLE 11 NO 0 0 0 1048510464 127992
Y:/ORACLE/ORADATA/FORP/FORP2.ORA 12 FORP 1048576000 128000 AVAILABLE 12 NO 0 0 0 1048510464 127992
Y:/ORACLE/ORADATA/FORP/TEST.ORA 13 TEST 5242880 640 AVAILABLE 13 YES 3435972198 4194302 1 5177344 632
13 rows selected
对比发现:
dba_free_space 中针对某一表空间的记录条数不一定是1条(将影响sum(b.bytes)的值(几条就是几倍)),同理dba_data_files 中针对某一表空间的记录条数不一定是1条(将影响sum(a.bytes)的值(几条就是几倍))
所以使用第一种方法得到的结果是不正确的。
如果一定要用dba_free_space 和dba_data_files 来监控空闲空间情况,就用最笨的方法,如下:
SQL> select a.tablespace_name,100*a.sum_bytes/b.sum_bytes
2 from (select distinct tablespace_name,sum(bytes) sum_bytes
3 from dba_free_space
4 group by tablespace_name) a,
5 (select distinct tablespace_name,sum(bytes) sum_bytes
6 from dba_data_files
7 group by tablespace_name) b
8 where a.tablespace_name=b.tablespace_name
9 ;
TABLESPACE_NAME 100*A.SUM_BYTES/B.SUM_BYTES
------------------------------ ---------------------------
CWMLITE 11.25
DRSYS 51.5625
EXAMPLE 0.334728033472803
FORP 98.140625
INDX 99.75
ODM 53.125
SYSTEM 9.825
TEST 92.5
TOOLS 39.375
UNDOTBS1 96.0201149425287
USERS 99.75
XDB 49.0925426774483
12 rows selected
SQL>