//这个好点
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",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 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 F.TABLESPACE_NAME;
或者
SELECT
tablespace_name,
100*(sum_max-sum_alloc+nvl(sum_free,0))/sum_max AS capa_per,
(sum_max-sum_alloc+nvl(sum_free,0))/1024/1024 AS capa_free,
(sum_alloc - nvl(sum_free,0))/1024/1024 as capa_used,
sum_max/1024/1024 as capa_max,
100*nvl(sum_free,0)/sum_alloc As per,
nvl(sum_free,0)/1024/1024 as free,
(sum_alloc - nvl(sum_free,0))/1024/1024 as used,
sum_alloc/1024/1024 as max
FROM ( SELECT tablespace_name
, sum(bytes) AS sum_alloc
, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
FROM dba_data_files
GROUP BY tablespace_name
)
,( SELECT tablespace_name AS fs_ts_name
, sum(bytes) AS sum_free
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by 2,3;
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 F.TABLESPACE_NAME;
//这个很烂
select
b.file_id file_id,
b.tablespace_name,
b.bytes,
b.maxbytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) unused,
sum(nvl(a.bytes,0))/(b.bytes)*100 percentage
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes,b.maxbytes
order by b.file_id;
//这个很烂 ,似乎是表空间的总大小
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;
//查看数据文件表空间的对应关系
select t.name,t1.name
from v$tablespace t,
v$datafile t1
where t.ts# = t1.ts#
oracle查询表和索引所占的表空间容量
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",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 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 F.TABLESPACE_NAME;
或者
SELECT
tablespace_name,
100*(sum_max-sum_alloc+nvl(sum_free,0))/sum_max AS capa_per,
(sum_max-sum_alloc+nvl(sum_free,0))/1024/1024 AS capa_free,
(sum_alloc - nvl(sum_free,0))/1024/1024 as capa_used,
sum_max/1024/1024 as capa_max,
100*nvl(sum_free,0)/sum_alloc As per,
nvl(sum_free,0)/1024/1024 as free,
(sum_alloc - nvl(sum_free,0))/1024/1024 as used,
sum_alloc/1024/1024 as max
FROM ( SELECT tablespace_name
, sum(bytes) AS sum_alloc
, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
FROM dba_data_files
GROUP BY tablespace_name
)
,( SELECT tablespace_name AS fs_ts_name
, sum(bytes) AS sum_free
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by 2,3;
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 F.TABLESPACE_NAME;
//这个很烂
select
b.file_id file_id,
b.tablespace_name,
b.bytes,
b.maxbytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) unused,
sum(nvl(a.bytes,0))/(b.bytes)*100 percentage
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes,b.maxbytes
order by b.file_id;
//这个很烂 ,似乎是表空间的总大小
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;
//查看数据文件表空间的对应关系
select t.name,t1.name
from v$tablespace t,
v$datafile t1
where t.ts# = t1.ts#
oracle查询表和索引所占的表空间容量
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;