關於oracle的空間查詢

甲骨文技術支援發表於2018-03-15


~~~~~~~~~~~~~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  

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/20893244/viewspace-2151882/,如需轉載,請註明出處,否則將追究法律責任。

相關文章