oracle表空间相关统计查询

部分转自 https://www.cnblogs.com/xwdreamer/p/3511047.html
--查询表空间使用情况
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 4 desc, 5 asc;

--查询表空间的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
order by 3 asc;

--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as mb
from dba_data_files
group by tablespace_name
order by 2 desc;

--查询表空间使用率
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
order by 4 desc;

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
order by 5 desc ;

select tablespace_name "表空间",
to_char(round(bytes / 1024, 2), '99990.00') || '' "实有",
to_char(round(free / 1024, 2), '99990.00') || 'g' "现有",
to_char(round((bytes - free) / 1024, 2), '99990.00') || 'g' "使用",
to_char(round(10000 * used / bytes) / 100, '99990.00') || '%' "使用比例"
from (select a.tablespace_name tablespace_name,
floor(a.bytes / (1024 * 1024)) bytes,
floor(b.free / (1024 * 1024)) free,
floor((a.bytes - b.free) / (1024 * 1024)) used
from (select tablespace_name tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name)
--where tablespace_name like 'cdr%' --这一句用于指定表空间名称
order by floor(10000 * used / bytes) desc;

select tablespace_name,
max_gb,
used_gb,
round(100 * used_gb / max_gb) pct_used
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
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 all
select h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
round(sum(decode(f.autoextensible,
'yes',
f.maxbytes,
'no',
f.bytes)) / power(2, 30),
2) max_gb
from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name)
order by 4;

--带获取数据文件及路径
select b.file_name 物理文件名,
b.tablespace_name 表空间名称,
b.bytes / 1024 / 1024 总大小m,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用m,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 使用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;

select a.tablespace_name tablespace_name,
total / 1048576 total_m,
free / 1048576 free_m,
(total - free) / 1048576 used_m,
round((total - free) / total, 4) * 100 "used%",
autoextensible autoextem
from (select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) total,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by 6, 5 desc;

--查看表空间的名字及文件所在位置
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;

--查询表空间使用情况
select a.tablespace_name as "表空间名",
a.bytes / 1024 / 1024 as "表空间大小(m)",
(a.bytes - b.bytes) / 1024 / 1024 as "已使用空间(m)",
b.bytes / 1024 / 1024 "空闲空间(m)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
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.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2 / 1024 / 1024 表空间大小m,
(b.b2 - a.a2) / 1024 / 1024 已使用m,
substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1
order by 6 desc;

---所有用户表使用大小的前三十名
select *
from (select segment_name, bytes
from dba_segments
where owner = user
order by bytes desc)
where rownum <= 30;

--有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：
select segment_name, bytes from user_segments where segment_type = 'table';
--or
select segment_name, sum(bytes) / 1024 / 1024
from user_extents
group by segment_name;

--另一种表实际使用的空间
analyze table emp compute statistics;

select num_rows * avg_row_len
from user_tables
where table_name = 't_test';
select num_rows * avg_row_len, t.*
from dba_tables t
where table_name = 't_test';