Oracle表空间利用率

表空间利用率：

SELECT a.tablespace_name,
  --total "表空间大小",
  -- free "表空间剩余大小",
  -- (total - free) "表空间使用大小",
  total        / (1024 * 1024 * 1024) "表空间大小(G)",
  (total       - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
  free         / (1024 * 1024 * 1024) "表空间剩余大小(G)",
  ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM
  (SELECT tablespace_name,
    SUM(bytes) free
  FROM dba_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,
    SUM(bytes) total
  FROM dba_data_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name = b.tablespace_name;

临时表空间使用情况：

--临时表空间物理文件以及大小
SELECT
    TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024/1024 "CURR_SIZE(GB)", MAXBYTES/1024/1024/1024 "MAX_SIZE(GB)",AUTOEXTENSIBLE
FROM
    DBA_TEMP_FILES;
 
 
  --临时表空间使用情况
SELECT c.tablespace_name,
  TO_CHAR(c.bytes     /1024/1024/1024,'99,999.999') total_gb,
  TO_CHAR( (c.bytes   -d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
  TO_CHAR(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
  TO_CHAR(d.bytes_used*100/c.bytes,'99.99')
  || '%'use
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;

大表排行：

 SELECT *
    FROM
      (SELECT owner,
        segment_name,
        segment_type,
        partition_name,
        SUM(bytes)/1024/1024 MB,
        SUM(bytes)/1024/1024/1024 GB
      FROM dba_segments
      GROUP BY owner,
        segment_name,
        segment_type,
        partition_name
      ORDER BY SUM(extents) DESC
      )
    WHERE rownum <=10;