select a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes 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我用上面的sql查看表空间使用情况,查询本机的时候,还正常,%used和%free的和基本等于100%,但在服务器上查的,有几个表空间的used和free的和要比100%大很多,什么原因?
sql有问题?还是其他,另外,服务器使用的磁盘阵列还要提一下,使用下面的sql,查出来的却是正常的:
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 哪种方式是正确的?两者什么区别?为什么会有这种情况?哪位朋友指点一下。
a.bytes total,
b.bytes used,
c.bytes 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我用上面的sql查看表空间使用情况,查询本机的时候,还正常,%used和%free的和基本等于100%,但在服务器上查的,有几个表空间的used和free的和要比100%大很多,什么原因?
sql有问题?还是其他,另外,服务器使用的磁盘阵列还要提一下,使用下面的sql,查出来的却是正常的:
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 哪种方式是正确的?两者什么区别?为什么会有这种情况?哪位朋友指点一下。
监控表空间就是防止因表空间满了,数据库出现问题,只要使用率没有超过95%就好了. 如果超过95%,就要加表空间的数据文件了。 这个是我用的一个脚本。
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
你试试上面这几个方法
查查看结果如何
sys.sm$ts_used
sys.sm$ts_free
我对这三个表不太了解 所以没法回答你不过从语句来看
你的两条语句计算USED时候的方法好像不太一样