计算某个用户下表的大小

---------统计某用户下对象占用大小-----------------------------------------

SELECT T.TOWNER, SUM(T.BYTES) / 1024 /1024/1024 SUMS_G

 FROM (SELECT C.owner TOWNER,C.segment_name TABLENAME, C.BYTES BYTES

         FROM DBA_SEGMENTS C

        WHERE C.owner='RMGZ'

          AND C.segment_type NOT IN ('LOBINDEX', 'INDEX','LOBSEGMENT')

       UNION

       SELECT B.owner, B.table_name, A.BYTES BYTES

         FROM (SELECT owner, SEGMENT_NAME, BYTES

                  FROM DBA_SEGMENTS

                 WHERE owner = 'RMGZ') A,

               DBA_INDEXES B

        WHERE A.SEGMENT_NAME = B.index_name

          AND B.owner = 'RMGZ') T

 --WHERE T.TABLENAME NOT LIKE 'BIN$%'

 GROUP BY T.TOWNER

 ORDER BY SUM(T.BYTES) / 1024 / 1024/1024 DESC

 

 

--------统计某用户下指定表的大小-------------------------------------------

SELECT SUM(T.BYTES) / 1024 / 1024 / 1024SUMS_G

 FROM (SELECT C.owner TOWNER, C.segment_name TABLENAME, C.BYTES BYTES

         FROM DBA_SEGMENTS C

        WHERE C.owner = 'RMGZ'

          AND C.segment_name = 'ADDR'

        UNION

       SELECT B.owner, B.table_name, A.BYTES BYTES

         FROM (SELECT owner, SEGMENT_NAME, BYTES

                  FROM DBA_SEGMENTS

                 WHERE owner = 'RMGZ') A,

               DBA_INDEXES B

        WHERE A.SEGMENT_NAME = B.index_name

          AND B.table_name = 'ADDR'

          AND B.owner = 'RMGZ') T

 

 

--------统计某用户下各个表占用空间大小-------------------------------------

SELECT T.TOWNER,t.TABLENAME,TRUNC(SUM(T.BYTES) / 1024 / 1024) SUMS_M

 FROM (SELECT C.owner TOWNER,C.segment_name TABLENAME, C.BYTES BYTES

         FROM DBA_SEGMENTS C

        WHERE C.owner='RMGZ'

          AND C.segment_type NOT IN ('LOBINDEX', 'INDEX','LOBSEGMENT')

       UNION

       SELECT B.owner, B.table_name, A.BYTES BYTES

         FROM (SELECT owner, SEGMENT_NAME, BYTES

                  FROM DBA_SEGMENTS

                 WHERE owner = 'RMGZ') A,

               DBA_INDEXES B

        WHERE A.SEGMENT_NAME = B.index_name

          AND B.owner = 'RMGZ') T

 --WHERE T.TABLENAME NOT LIKE 'BIN$%'

 GROUP BY T.TOWNER,t.TABLENAME

 ORDER BY SUM(T.BYTES) / 1024 / 1024 DESC