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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20893244/viewspace-2151882/，如需转载，请注明出处，否则将追究法律责任。