Hi Jack,
That looks like a good tool. Personally, for about the last 5 years, I've been using
an adaptation of the command center database described in the Oracle 7(& later) DBA
Handbook using cron as my scheduler. The neatest implementation I did was several
jobs back where I also collected volume space information. Then, I created a web page
that displayed a graph of volume space, tablespace, and free space in living color --
this was in response to a PHB that kept asking for a database growth projection every
few weeks so that he could plan the following years hardware purchases. Once I gave
him the URL, he quit asking. He never got the purchases right, but that's what
happens when you project the future by looking in the rear view mirror.
On my last job, I told my PHB that I wanted to implement the CC database right out of
the gate. He told me we didn't need it. I implemented it anyway without telling him.
A few months later, he figured out why we needed it.
I can't imagine maintaining a database without having an ongoing growth history.
Kevin Kennedy
First Point Energy Corporation
-----Original Message-----
Sent: Wednesday, June 26, 2002 1:50 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: kkennedy
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).
