表領域とディスクグループの使用量確認

表領域の使用量確認

SELECT d.tablespace_name "Tablespace Name",
  TO_CHAR(a.bytes                     / 1024 / 1024, '9,999,990.900') "Total(MB)",
  TO_CHAR((a.bytes - NVL(f.bytes, 0)) / 1024 / 1024, '9,999,990.900') "Usage(MB)",
  '  ' || TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Usage(%)",
  a.autoextensible "Autoextensible"
FROM sys.dba_tablespaces d,
  (SELECT tablespace_name,
    SUM(bytes) bytes,
    autoextensible
  FROM dba_data_files
  GROUP BY tablespace_name, autoextensible
  ) a,
  (SELECT tablespace_name,
    SUM(bytes) bytes
  FROM dba_free_space
  GROUP BY tablespace_name
  ) f
WHERE d.tablespace_name = a.tablespace_name
AND d.tablespace_name   = f.tablespace_name
AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name "Tablespace Name",
  TO_CHAR(a.bytes          / 1024 / 1024, '9,999,990.900') "Total(MB)",
  TO_CHAR(NVL(t.bytes , 0) / 1024 / 1024, '9,999,990.900') "Usage(MB)",
  '  ' || TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Usage(%)",
  a.autoextensible "Autoextensible"
FROM sys.dba_tablespaces d,
  (SELECT tablespace_name,
    SUM(bytes) bytes,
    autoextensible
  FROM dba_temp_files
  GROUP BY tablespace_name, autoextensible
  ) a,
  (SELECT tablespace_name,
    SUM(bytes_used) bytes
  FROM v$temp_space_header
  GROUP BY tablespace_name
  ) t
WHERE d.tablespace_name = a.tablespace_name
AND d.tablespace_name   = t.tablespace_name
AND (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
ORDER BY 5, 4 DESC;

ディスクグループの使用量確認

SELECT name "Diskgroup Name",
  type "Redundancy",
  TO_CHAR(total_mb, '999,999,990') "Total(MB)",
  TO_CHAR(total_mb - free_mb, '999,999,990') "Usage(MB)",
  ' ' || TO_CHAR((total_mb - free_mb) / total_mb * 100, '990.00') "Usage(%)",
  TO_CHAR(usable_file_mb, '999,999,990') "Usable(MB)"
FROM v$asm_diskgroup
ORDER BY group_number;