查看各表空间的使用率
select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%" from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name;
查看回滚段命中率情况
select rn.name,rs.gets "被访问次数",rs.waits "等待回退段块的次数",(rs.waits/rs.gets)*100 "命中率" from v$rollstat rs,v$rollname rn;
查看当前用户每个表占用空间的大小:
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes);
查看每个表空间占用空间的大小:
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name;