function get_tablespace_tbl
RETURN t_table IS
l_table t_table := t_table ();
BEGIN
SELECT t_record (TABLE_SPACE, used_mb, free_mb, total_mb, max_mb, pct_free, max_pct_free)
BULK COLLECT INTO l_table
FROM (
SELECT
fs.tablespace_name AS TABLE_SPACE,
(DF.TOTALSPACE – FS.FREESPACE) as USED_MB,
FS.FREESPACE as FREE_MB,
DF.TOTALSPACE as TOTAL_MB,
DF.MAXSPACE as MAX_MB,
ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) as PCT_FREE,
case when DF.MAXSPACE > 0 then
ROUND(100 * ((DF.MAXSPACE – (df.totalspace – fs.freespace)) / DF.MAXSPACE))
else 0
end MAX_PCT_FREE
from
(select
TABLESPACE_NAME,
ROUND(SUM(BYTES) / 1048576) TOTALSPACE,
round(sum(maxbytes) / 1048576) MaxSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
order by
tablespace_name
) fs
WHERE
DF.TABLESPACE_NAME = FS.TABLESPACE_NAME
);
RETURN l_table;
end;
procedure get_tablespace_info(table_owner IN VARCHAR2 default ‚%‘)
IS
RC SYS_REFCURSOR;
begin
open rc for select
fs.tablespace_name „Tablespace“,
(df.totalspace – fs.freespace) „Used MB“,
fs.freespace „Free MB“,
DF.TOTALSPACE „Total MB“,
DF.MAXSPACE „Max MB“,
ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) „Pct. Free“,
case when DF.MAXSPACE > 0 then
ROUND(100 * ((DF.MAXSPACE – (df.totalspace – fs.freespace)) / DF.MAXSPACE))
else 0
end „Max Pct. Free“
from
(select
TABLESPACE_NAME,
ROUND(SUM(BYTES) / 1048576) TOTALSPACE,
round(sum(maxbytes) / 1048576) MaxSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
order by
tablespace_name
) fs
where
DF.TABLESPACE_NAME = FS.TABLESPACE_NAME
and FS.TABLESPACE_NAME like table_owner
;
dbms_sql.return_result(rc);
end get_tablespace_info;