DBA_TABLESPACE_USAGE_METRICS describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces.
Column | Datatype | NULL | Description |
---|---|---|---|
TABLESPACE_NAME | VARCHAR2(30) | Tablespace name | |
USED_SPACE | NUMBER | Total space consumed by the tablespace | |
TABLESPACE_SIZE | NUMBER | Total size of the tablespace | |
USED_PERCENT | NUMBER | Percentage of used space, as a function of the maximum possible tablespace size |
以上是Oracle对该视图的定义,本以为Oracle提供的视图会很准,所以平时查看表空间的使用情况,为了图方便,就直接查看该视图。
今天一个偶然的机会,发现这个视图是相当的不准确了。
我用imp方式还原一个数据库的时候,控制台已经报错:
ORA-01658: 无法为表空间 TEST_DB 中的段创建 INITIAL 区
这个错误明显表示表空间的空间不够了,可我查看dba_tablespace_usage_metrics视图,已用空间的比率确很低:
SQL> select * from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TEST_DB 255984 4194302 6.1031370654
------------------------------ ---------- --------------- ------------
TEST_DB 255984 4194302 6.1031370654
这里看到已用空间之占了百分之六多点。
我再通过 DBA_DATA_FILE 和 DBA_FREE_SPACE 视图查看,结果如下:
SQL> SELECT D.TABLESPACE_NAME,
2 FILE_NAME "FILE_NAME",
3 SPACE "SUM_SPACE(M)",
4 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
5 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
6 AUTOEXTENSIBLE
7 FROM (SELECT FILE_ID,
8 FILE_NAME,
9 TABLESPACE_NAME,
10 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
11 SUM(BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME, FILE_ID, FILE_NAME) D,
14 (SELECT FILE_ID,
15 TABLESPACE_NAME,
16 ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
17 FROM DBA_FREE_SPACE
18 GROUP BY TABLESPACE_NAME, FILE_ID) E,
19 (SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES) F
20 WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
21 AND D.FILE_ID = E.FILE_ID(+)
22 AND D.FILE_ID = F.FILE_ID(+)
23 UNION ALL --if have tempfile
24 SELECT D.TABLESPACE_NAME,
25 FILE_NAME "FILE_NAME",
26 SPACE "SUM_SPACE(M)",
27 USED_SPACE "USED_SPACE(M)",
28 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
29 AUTOEXTENSIBLE
30 FROM (SELECT FILE_ID,
31 FILE_NAME,
32 TABLESPACE_NAME,
33 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
34 SUM(BLOCKS) BLOCKS
35 FROM DBA_TEMP_FILES
36 GROUP BY TABLESPACE_NAME, FILE_ID, FILE_NAME) D,
37 (SELECT FILE_ID,
38 TABLESPACE_NAME,
39 ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
40 ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
41 FROM V$TEMP_SPACE_HEADER
42 GROUP BY TABLESPACE_NAME, FILE_ID) E,
43 (SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
44 WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
45 AND D.FILE_ID = E.FILE_ID(+)
46 AND D.FILE_ID = F.FILE_ID(+)
47 ORDER BY TABLESPACE_NAME, FILE_NAME;
2 FILE_NAME "FILE_NAME",
3 SPACE "SUM_SPACE(M)",
4 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
5 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
6 AUTOEXTENSIBLE
7 FROM (SELECT FILE_ID,
8 FILE_NAME,
9 TABLESPACE_NAME,
10 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
11 SUM(BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME, FILE_ID, FILE_NAME) D,
14 (SELECT FILE_ID,
15 TABLESPACE_NAME,
16 ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
17 FROM DBA_FREE_SPACE
18 GROUP BY TABLESPACE_NAME, FILE_ID) E,
19 (SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES) F
20 WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
21 AND D.FILE_ID = E.FILE_ID(+)
22 AND D.FILE_ID = F.FILE_ID(+)
23 UNION ALL --if have tempfile
24 SELECT D.TABLESPACE_NAME,
25 FILE_NAME "FILE_NAME",
26 SPACE "SUM_SPACE(M)",
27 USED_SPACE "USED_SPACE(M)",
28 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
29 AUTOEXTENSIBLE
30 FROM (SELECT FILE_ID,
31 FILE_NAME,
32 TABLESPACE_NAME,
33 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
34 SUM(BLOCKS) BLOCKS
35 FROM DBA_TEMP_FILES
36 GROUP BY TABLESPACE_NAME, FILE_ID, FILE_NAME) D,
37 (SELECT FILE_ID,
38 TABLESPACE_NAME,
39 ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
40 ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
41 FROM V$TEMP_SPACE_HEADER
42 GROUP BY TABLESPACE_NAME, FILE_ID) E,
43 (SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
44 WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
45 AND D.FILE_ID = E.FILE_ID(+)
46 AND D.FILE_ID = F.FILE_ID(+)
47 ORDER BY TABLESPACE_NAME, FILE_NAME;
TABLESPACE_NAME FILE_NAME SUM_SPACE(M) USED_SPACE(M) USED_RATE(%) AUTOEXTENSIBLE
------------------------------ -------------------------------------------------------------------------------- ------------ ------------- ------------ --------------
TEST_DB /oracle/database/orcl/TEST_DB 2000 1976.31 98.82 YES
------------------------------ -------------------------------------------------------------------------------- ------------ ------------- ------------ --------------
TEST_DB /oracle/database/orcl/TEST_DB 2000 1976.31 98.82 YES
在此,我省略掉了其他无关的信息。
通过这个可以得出一个结论,dba_tablespace_usage_metrics视图提供的信息不可靠,不能嫌麻烦,还得从DBA_DATA_FILE 和 DBA_FREE_SPACE 视图查询。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-687702/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-687702/