查看物理size和实际size
select name,sum(dbsz)/1024/1024 "Physical siZe MB" from
(select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log),v$database
group by name
OR
select name,sum(a.dbsz)/1024/1024 "Physical siZe MB",b.actsz/1024/1024 "Actual Size in Mb"
from (select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log) a ,
(select sum(bytes) actsz from dba_extents) b ,v$database
group by name,b.actsz
NAME Physical siZe MB Actual Size in Mb
--------- ---------------- -----------------
ORA11G 28348.8125 22796.625
(select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log),v$database
group by name
OR
select name,sum(a.dbsz)/1024/1024 "Physical siZe MB",b.actsz/1024/1024 "Actual Size in Mb"
from (select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log) a ,
(select sum(bytes) actsz from dba_extents) b ,v$database
group by name,b.actsz
NAME Physical siZe MB Actual Size in Mb
--------- ---------------- -----------------
ORA11G 28348.8125 22796.625
查询表空间使用情况
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
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,
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 tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查询表空间使用率
SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
round(b.bytes / 1024 / 1024) "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
round(b.bytes / 1024 / 1024) "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27000195/viewspace-731847/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27000195/viewspace-731847/