2015-02-10

Free space in tablespace

Script to calculate free space in oracle tablespace.

SELECT a.tablespace_name,
a.file_name,
a.file_id,
used_gb "Used Size GB",
a.max_gb "Max Size Gb",
a.max_gb - a.used_gb + NVL(c.free_gb, 0) free_space
FROM
(SELECT tablespace_name, file_name, file_id, a.bytes /1024/1024/1024 used_gb,
CASE WHEN a.maxbytes = 0 THEN bytes ELSE a.maxbytes END /1024/1024/1024 max_gb
FROM dba_data_files a) a,
(
SELECT tablespace_name, file_id, SUM(BYTES) / 1024/1024/1024 free_gb
FROM DBA_FREE_SPACE c
GROUP BY tablespace_name, file_id
) c
WHERE a.tablespace_name = c.tablespace_name(+)
AND a.file_id = c.file_id(+);

1 комментарий:

Andrey Zaytsev комментирует...

Или более просто
select * from V$FILESPACE_USAGE;