select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
round(maxbytes/1048576,2) Max
from (
select f.tablespace_name, sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name
) a,(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name (+)
union
select tablespace_name,
round(sum(bytes_used + bytes_free) / 1048576, 2),
round(sum(bytes_free) / 1048576,2),
round(sum(bytes_used) / 1048576,2),
round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_Free,
100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_used,
round(max(bytes_used + bytes_free) / 1048576, 2)
from sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
round(maxbytes/1048576,2) Max
from (
select f.tablespace_name, sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name
) a,(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name (+)
union
select tablespace_name,
round(sum(bytes_used + bytes_free) / 1048576, 2),
round(sum(bytes_free) / 1048576,2),
round(sum(bytes_used) / 1048576,2),
round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_Free,
100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_used,
round(max(bytes_used + bytes_free) / 1048576, 2)
from sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1
解决方案 »
- sql查询的问题(a or b加a and b加ab)?
- 在win7中oracle10g数据库安装问题?
- 如何用SQL语句把一个表里的数据全部复制到另外一张不存在的表里?
- ora-01157 suse Linux 下安装的oracle11g,请问dbwr跟踪文件路径是多少啊
- 算库存问题
- oracle 建表时 提示 SP2-0640: 未连接 在线等..
- 请问Oracle如何创建表名为User的数据表
- 多个select网一个表里的多个字段里插值,SQL语句怎么写?
- 关于日期查询的问题,牛人请进,在线等,答对马上给分!!!
- 高分求解! RedHat7.3上如何安装apache_1.3.27?
- oracle的plusql中的设置
- 关于罐头过期的SQL查询问题
也可以几个用户共用表空间
select * from dba_users
group by tablespace_name;
group by tablespace_name;ERROR at line 1:
ORA-00942: table or view does not exist没有dba_free_space这个表啊
或者你以system用户登陆查看吧
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE ,
MAX(BYTES/1024/1024) MAX,
MIN(BYTES/1024/1024) MIN
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE,'',''
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)