KS-Soft. Network Management Solutions
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister    ProfileProfile    Log inLog in 

Oracle Tablespace filling degree check

 
Post new topic   Reply to topic    KS-Soft Forum Index -> Library
View previous topic :: View next topic  
Author Message
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Wed Mar 21, 2007 6:19 am    Post subject: Oracle Tablespace filling degree check Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
KS-Soft



Joined: 03 Apr 2002
Posts: 12792
Location: USA

PostPosted: Wed Mar 21, 2007 8:04 pm    Post subject: Reply with quote

Thank you for contribution

Regards
Alex
Back to top
View user's profile Send private message Visit poster's website
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Tue Apr 24, 2007 6:37 am    Post subject: Reply with quote

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.


Last edited by mos-eisley on Wed May 02, 2007 1:39 am; edited 1 time in total
Back to top
View user's profile Send private message MSN Messenger
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Wed May 02, 2007 1:39 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message MSN Messenger
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Tue May 08, 2007 2:08 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    KS-Soft Forum Index -> Library All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group

KS-Soft Forum Index