Kevin,
thanks for the input. Gotta have space over time.
I toyed with the idea of putting this in a webpage so
people would look instead of ask too - just haven't
had time yet.
what did you use for your graphing tool?
And you can see how I assigned the dates to the column
headers - kind of clunky, but works. Do you have
another take on how to do this?
thx,
jack
- kkennedy <kkennedy_at_firstpoint.com> wrote:
> 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,
>
=== message truncated ===
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: jack_silvey_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed Jun 26 2002 - 19:23:04 CDT
