Oracle-L: RE: report showing free space per tablespace over time

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Thu, 27 Jun 2002 09:53:32 -0800
Message-ID: <F001.0048A9C7.20020627095332@fatcity.com>

Can't recall the name of the graphing tool I used. I selected it because my client was already using it for something else. I don't remember being thrilled with it, but it did the job.

As I said before, I used the Kevin Loney scripts originally poached from the Oracle 7 DBA Handbook. Column headers are not fancy:

column week4 format 999 heading "1Wk|Ago"
column week3 format 999 heading "2Wks|Ago"
column week2 format 999 heading "3Wks|Ago"
column week1 format 999 heading "4Wks|Ago"

and neither is data selection (one example):

select

        spaces.db_nm,
        spaces.ts,
        max(decode(spaces.check_date, trunc(sysdate-28),
                round(100*sum_free_blocks/sum_file_blocks),0)) week1,
        max(decode(spaces.check_date, trunc(sysdate-21),
                round(100*sum_free_blocks/sum_file_blocks),0)) week2,
        max(decode(spaces.check_date, trunc(sysdate-14),
                round(100*sum_free_blocks/sum_file_blocks),0)) week3,
        max(decode(spaces.check_date, trunc(sysdate-7),
                round(100*sum_free_blocks/sum_file_blocks),0)) week4,
        max(decode(spaces.check_date, trunc(sysdate),
                round(100*sum_free_blocks/sum_file_blocks),0)) today,
        max(decode(spaces.check_date, trunc(sysdate),
                round(100*sum_free_blocks/sum_file_blocks),0)) -
        max(decode(spaces.check_date, trunc(sysdate-28),
                round(100*sum_free_blocks/sum_file_blocks),0)) change
from spaces, files_ts_view ftv
where spaces.db_nm = ftv.db_nm          /*same database name*/
and spaces.ts = ftv.ts                  /*same tablespace name*/
and spaces.check_date = ftv.check_date  /*same check date*/
and exists                              /*does ts still exist?*/
        (select 'x' from spaces x
        where x.db_nm = spaces.db_nm
        and x.ts = spaces.ts
        and x.check_date = trunc(sysdate))
group by
        spaces.db_nm,
        spaces.ts
order by spaces.db_nm,
        decode(spaces.ts,'RBS',1,'TEMP',2,0),
        decode(
        max(decode(spaces.check_date,trunc(sysdate),
                round(100*sum_free_blocks/sum_file_blocks),0)) -
        max(decode(spaces.check_date, trunc(sysdate-28),
                round(100*sum_free_blocks/sum_file_blocks),0)),0,9999,
        max(decode(spaces.check_date,trunc(sysdate),
                round(100*sum_free_blocks/sum_file_blocks),0)) -
        max(decode(spaces.check_date, trunc(sysdate-28),
                round(100*sum_free_blocks/sum_file_blocks),0))),
        max(decode(spaces.check_date,trunc(sysdate),
                round(100*sum_free_blocks/sum_file_blocks),0))

Ok, so it's pretty obvious that there is room for code clarification and/or improvement. But, it works fine and I've got other fish to fry. Besides, the only documentation I have to write is to refer people to the Oracle Press/Osborne/McGraw Hill bookshelf 8-)

Kevin Kennedy
First Point Energy Corporation

-----Original Message-----
Sent: Wednesday, June 26, 2002 5:23 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kkennedy
  INET: kkennedy_at_firstpoint.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 Thu Jun 27 2002 - 12:53:32 CDT