|
View previous topic :: View next topic |
Author |
Message |
mos-eisley
Joined: 21 Mar 2007 Posts: 76 Location: Klarup (AAlborg), Demark
|
Posted: Wed Mar 21, 2007 6:19 am Post subject: Oracle Tablespace filling degree check |
|
|
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 |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12801 Location: USA
|
Posted: Wed Mar 21, 2007 8:04 pm Post subject: |
|
|
Thank you for contribution
Regards
Alex |
|
Back to top |
|
|
mos-eisley
Joined: 21 Mar 2007 Posts: 76 Location: Klarup (AAlborg), Demark
|
Posted: Tue Apr 24, 2007 6:37 am Post subject: |
|
|
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 |
|
|
mos-eisley
Joined: 21 Mar 2007 Posts: 76 Location: Klarup (AAlborg), Demark
|
Posted: Wed May 02, 2007 1:39 am Post subject: |
|
|
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 |
|
|
mos-eisley
Joined: 21 Mar 2007 Posts: 76 Location: Klarup (AAlborg), Demark
|
Posted: Tue May 08, 2007 2:08 am Post subject: |
|
|
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 |
|
|
|
|
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
|