查看表空间的利用情况

--表空间
select tablespace_name, sum(bytes/1024/1024) from dba_data_files
group by tablespace_name;
--空闲空间
select tablespace_name,sum(bytes/1024/1024)
from dba_free_space
group by tablespace_name
--表空间利用率
select a.tablespace_name,total,free,total-free,round((total-free)/total,4)*100 || '%'
from
(select tablespace_name,sum(bytes/1024/1024) total from dba_data_files group by tablespace_name)a,
(select tablespace_name,sum(bytes/1024/1024) free from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name;

--segment
select segment_name,sum(bytes/1024/1024) from user_extents
group by segment_name;
--tablespace_name ,user
select tablespace_name,owner,sum(bytes/1024/1024) from
dba_segments
group by tablespace_name,owner;

--正在使用的临时表

select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as space,
       tablespace,
       segtype,
       sql_text
       from v$sort_usage su,
       v$parameter p,
       v$session se,
       v$sql s
       where lower(p.name)='db_block_size'
       and su.SESSION_ADDR=se.SADDR
       and s.hash_value=su.SQLHASH
       and s.ADDRESS=su.SQLADDR
       order by se.USERNAME,se.SID;