表、空间相关 SQL

表空间

select t.tablespace_name, round(sum(b.bytes / 1024 / 1024))
from dba_tablespaces t, dba_data_files b 
where t.tablespace_name = b.tablespace_name 
group by t.tablespace_name;

临时表空间

select t.tablespace_name, t.file_name, t.bytes / 1024 / 1024
from dba_temp_files t;

剩余表空间

select t.tablespace_name, round(sum(t.bytes / 1024 / 1024)) 
from dba_free_space t
group by t.tablespace_name;

表空间占比

select
dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks "总块数",
dfs.freespace "剩余总量",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例" 
from
(
select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name
) dbf,
(
select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name
) dfs
where
trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
and
dbf.tablespace_name
in
(
select tablespace_name from user_tablespaces
);

数据表占比

select t.owner, t.segment_name,
t.bytes / 1024 / 1024 used_space,
t.bytes / 1024 /1024 / b.table_space
from dba_segments t,
(
select t.tablespace_name,
round(sum(b.bytes / 1024 / 1024)) table_space
from dba_tablespaces t, dba_data_files b
where t.tablespace_name = b.tablespace_name
group by t.tablespace_name
) b
where t.tablespace_name = b.tablespace_name
and t.segment_type = 'talbe'
and t.tablespace_name = 'users'
order by 3 desc;