Listers,
Ever been asked how much free space you have in the
tablespaces over time? I wrote a tool that tracks and
reports on free space per tablespace over time and
thought I would share it.
It consists of a simple stored procedure that stores
values in a holding table and a report that will tell
you how much each tablespace had over the past week
(you can change the coverage of this report.)
1) Here is the stored procedure:
CREATE OR REPLACE PROCEDURE
tablespace_proc AS
v_errmsg varchar2(100);
v_errcode varchar2(100);
BEGIN
delete dbmon.dbmon_tablespace_stats
where trunc(record_date) = trunc(sysdate);
INSERT INTO dbmon.dbmon_tablespace_stats
(tablespace_name,
total_space,
megs_free,
max_extent,
autoextend,
record_date)
SELECT
fs.tablespace_name,
round(df.total_bytes/1024/1024,0),
round(fs.bytes_free/1024/1024,0),
round(fs.max_bytes/1024/1024,0),
decode(a.tablespace_name,null,'No','Yes'),
sysdate
FROM
(SELECT
tablespace_name,
SUM(bytes) bytes_free,
max(bytes) max_bytes
FROM
dba_free_space fs
GROUP BY
tablespace_name) fs,
(SELECT
tablespace_name,
sum(bytes) total_bytes
FROM
dba_data_files
GROUP BY
tablespace_name) df,
(SELECT DISTINCT
tablespace_name
FROM
dba_data_files
WHERE
autoextensible = 'YES') a,
(select tablespace_name
from dba_tablespaces) ts
WHERE
df.tablespace_name = fs.tablespace_name(+)
AND
df.tablespace_name = a.tablespace_name(+)
AND
df.tablespace_name = ts.tablespace_name;
commit;
exception
when others then
v_errmsg := substr(SQLERRM,1,100);
v_errcode := SQLCODE;
insert into dbmon_activity_log
(activity_date,
activity_desc,
procedure_name,
error_code,
error_msg,
error_date)
values
(sysdate,
'tablespace_proc',
'tablespace_proc',
v_errcode,
v_errmsg,
sysdate);
commit;
end;
/
2) Here is the code to submit it to the job scheduler
(every day at 5AM):
variable jobno number
exec sys.dbms_job.submit(job=>:jobno, what=>'begin
dbmon.dbmon_tablespace_proc;end;',
next_date=>trunc(sysdate+1)+5/24,interval=>'trunc(sysdate+1)+5/24');
3) and here is the report:
@save_sqlplus_settings
set term off
set head off
spool temp.sql
select 'col c'||rownum ||' for 999,999,990 head "'||
to_char(sysdate+1-rownum,'dd-mon')||'"'
from dba_tablespaces where rownum<8;
select 'compute sum of c'||rownum ||' on report'
from dba_tablespaces where rownum<8;
select 'break on report' from dual;
spool off
@temp.sql
exec dbmon.dbmon_tablespace_proc;
set term on
set lines 135
prompt
prompt Free space per tablespace:
prompt
select tablespace_name,
sum(decode(old,7,value)) c7,
sum(decode(old,6,value)) c6,
sum(decode(old,5,value)) c5,
sum(decode(old,4,value)) c4,
sum(decode(old,3,value)) c3,
sum(decode(old,2,value)) c2,
sum(decode(old,1,value)) c1
from
(select tablespace_name,
megs_free value,
decode
(trunc(record_date),
trunc(sysdate),1,
trunc(sysdate)-1,2,
trunc(sysdate)-2,3,
trunc(sysdate)-3,4,
trunc(sysdate)-4,5,
trunc(sysdate)-5,6,
trunc(sysdate)-6,7) old
from
dbmon.dbmon_tablespace_stats)
group by tablespace_name
order by 1
/
good luck,
Jack
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
