SELECT t.segment_name, round(SUM(t.bytes/1024/1024/1024),2)
  FROM user_segments t
 GROUP BY t.segment_name
 ORDER BY SUM(t.bytes/1024/1024/1024) DESC

SELECT * FROM (
SELECT A.TABLESPACE_NAME,ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "表空間大小(GB)"
      ,ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "表空間剩余大小(GB)"
      ,ROUND((A.BYTES - B.BYTES) / (1024 * 1024 * 1024), 2) AS "表空間使用大小(GB)"
      ,TO_CHAR((1 - B.BYTES / A.BYTES) * 100, '99.99999') || '%' AS "使用率"
 FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
      (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 
 UNION ALL 
SELECT C.TABLESPACE_NAME,ROUND(C.BYTES / (1024 * 1024 * 1024), 2) "表空間大小(GB)"
       ,ROUND((C.BYTES - D.BYTES_USED) / (1024 * 1024 * 1024), 2) "表空間剩余大小(GB)"
       ,ROUND(D.BYTES_USED / (1024 * 1024 * 1024), 2) "表空間使用大小(GB)"
       ,TO_CHAR(D.BYTES_USED * 100 / C.BYTES, '99.99999') || '%' "使用率"
  FROM(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) C,
      (SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) BYTES_USED FROM V$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) D 
 WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME
)
ORDER BY 5 desc
;

查看具體的表空間
SELECT TABLESPACE_NAME,OWNER,SEGMENT_NAME,/*PARTITION_NAME,*/SEGMENT_TYPE,ROUND(SUM(BYTES)/1024/1024/1024,2) SIZE_GB
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = UPPER('PCGSPDATA')
 GROUP BY TABLESPACE_NAME, OWNER, SEGMENT_NAME,/*PARTITION_NAME,*/ SEGMENT_TYPE
HAVING ROUND(SUM(BYTES)/1024/1024/1024,2) >= 0.1
 ORDER BY SIZE_GB DESC
;