查看oracle 分区表大小,求统计包含大字段和分区的表大小 - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗...

本帖最后由 repentance 于 2013-5-30 16:21 编辑

按用户统计用户数据空间使用情况

SELECT SYSDATE GATHER_TIME, OWNER,

TRUNC(SUM(BYTES) / 1024 / 1024) TOTAL_MB,

TRUNC(SUM(DECODE(A.SEGMENT_TYPE,'TABLE',A.BYTES,'TABLE PARTITION',A.BYTES,0)) / 1024 / 1024) TABLE_MB,

TRUNC(SUM(DECODE(A.SEGMENT_TYPE,'INDEX',A.BYTES,'INDEX PARTITION',A.BYTES,0)) / 1024 / 1024) INDEX_MB,

TRUNC(SUM(DECODE(A.SEGMENT_TYPE,

'LOBSEGMENT',

A.BYTES,

'LOBINDEX',

A.BYTES,

'LOB PARTITION',

A.BYTES,

0)) / 1024 / 1024) LOB_MB

FROM DBA_SEGMENTS A, DBA_USERS B

WHERE A.OWNER = B.USERNAME

AND B.ACCOUNT_STATUS = 'OPEN'

GROUP BY OWNER

ORDER BY 3 DESC;