Oracle中表空间查询

1、查看表占的空间

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

2、查看表空间使用率(包含临时表空间)

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
;

3、查看表空间或者用户的表占用的空间

查看具体的表空间
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
;