SM$TS



在表空间维护管理的时候，一直在使用

SELECT A.TABLESPACE_NAME,A.BYTES/1024/1024 TOTAL,

B.BYTES/1024/1024 USED, C.BYTES/1024/1024 FREE,

(B.BYTES*100)/A.BYTES "% USED",

(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME

AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

和

SELECT a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",
(total-free) "表空间使用大小",
ROUND((total-free)/total,4)*100 "使用率 %"
FROM  (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name 

该查询语句，但是最近在工作中，发现在同事所管理的一台数据库上使用第一条语句查询出来的结果却有些问题，在free和used加起来时却大大的超过了100%，但是使用第二条查询语句一切正常。疑问较大。

First，we can analyze how did those 3 views which were SYS.SM$TS_USED，SYS.SM$TS_FREE，SYS.SM$TS_AVAIL  worked.

1）

SQL>select text from dba_views where view_name = upper('sm$ts_used');

TEXT

--------------------------------------------------------------------------------

select tablespace_name, sum(bytes) bytes from dba_segments

    group by tablespace_name

2）

SQL>select text from dba_views where view_name = upper('sm$ts_avail');

TEXT

--------------------------------------------------------------------------------

select tablespace_name, sum(bytes) bytes from dba_data_files

    group by tablespace_name

3）

SQL>select text from dba_views where view_name = upper('sm$ts_free');

TEXT

--------------------------------------------------------------------------------

select tablespace_name, sum(bytes) bytes from dba_free_space

    group by tablespace_name