oracle中空间查询的示例分析

这篇文章主要介绍oracle中空间查询的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

~~~~~~~~~~~~~ORACLE查看某用户占用了多少空间~~~~~~~~~~~~~~~~

SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) "USED(M)"
  FROM DBA_SEGMENTS
 GROUP BY OWNER, TABLESPACE_NAME
 ORDER BY SUM(BYTES) DESC;

~~~~~~~~~~~~~~~~~~~~查询某个用户下表所占用的空间~~~~~~~~~~~~~~~~~~~
Select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
 from dba_segments t
 where t.owner = 'xxx' 
 and t.segment_type='TABLE'
 group by OWNER, t.segment_name, t.segment_type
 order by mmm desc;

~~~~~~~~~~~~~~~~~数据文件的高水位线~~~~~~~~~~~~~~~~~···

select a.file#,  
       a.name,  
       a.bytes / 1024 / 1024 CurrentMB,  
       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,  
       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,  
       'alter database datafile ''' || a.name || ''' resize ' ||  
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD  
  from v$datafile a,  
       (select file_id, max(block_id + blocks - 1) HWM  
          from dba_extents  
         group by file_id) b  
 where a.file# = b.file_id(+)  
   and (a.bytes - HWM * block_size) > 0  
 order by 5  

以上是“oracle中空间查询的示例分析”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！