Oracle Tablespace filling degree check

[mos-eisley]

I am now using this litte SQL to check for tablespaces ( not SYSTEM, TEMP etc) with a filling degree above 80%

SELECT cast(count(*) as varchar2(30)) FROM (SELECT ((total_space-Free_space)/total_space)*100 Pct_Used FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space where tablespace_name not in ('SYSTEM','TEMP','RBS','PERFSTAT_DATA') group by tablespace_name ) Free,(select b.name,sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name(+) = Total.name ORDER BY Total.name) where Pct_used>80

The script returns number of tablespaces (as a varchar2) with a filling above 80%

The script is used through ODBC.

[KS-Soft]

Thank you for contribution

Regards
Alex

[mos-eisley]

Just a small rewrite:

SELECT cast(count(*) as varchar2(30)) FROM (SELECT ((total_space-Free_space)/total_space)*100 Pct_Used FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space where tablespace_name not in ('SYSTEM','TEMP','RBS','PERFSTAT_DATA') group by tablespace_name ) Free,(select b.name,sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name = Total.name ORDER BY Total.name) where Pct_used>80

This eliminate some NULL rows that the first script gave.

[mos-eisley]

Here is next version, which returns the name(s) of the filled tablespaces:

First, compile this function onto the oracle server:

CREATE OR REPLACE FUNCTION join(
sysref_cursor_in sys_refcursor,
s_delimiter_in VARCHAR2 := ',' )
RETURN VARCHAR2
IS
s_value VARCHAR2(32767) := Null;
s_result VARCHAR2(32767) := Null;
s_delimiter VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN

LOOP
FETCH sysref_cursor_in
INTO s_value;

EXIT WHEN sysref_cursor_in%NOTFOUND;

IF (s_result IS NOT NULL) THEN
s_result := s_result||s_delimiter;
END IF;

s_result := s_result||s_value;

END LOOP;

RETURN s_result;

END join;

Secondly, use this SQL via ODBC now:

select join(cursor(select tablespace_name from (SELECT ((total_space-Free_space)/total_space)*100 Pct_Used,tablespace_name,Free_Space,Total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space where tablespace_name not in ('SYSTEM','TEMP','RBS','PERFSTAT_DATA') group by tablespace_name ) Free,(select b.name,sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name = Total.name ORDER BY Total.name) where pct_used>80)) from dual

[mos-eisley]

Next version, includes filling degree in the reply:

select join(cursor(select tablespace_name || ' (' || Cast(pct_used as varchar2(5)) || '%)' from (SELECT ((total_space-Free_space)/total_space)*100 Pct_Used,tablespace_name,Free_Space,Total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space where tablespace_name not in ('SYSTEM','TEMP','RBS','PERFSTAT_DATA') group by tablespace_name ) Free,(select b.name,sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name = Total.name ORDER BY Total.name) where pct_used>80)) from dual