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 <[EMAIL PROTECTED]> 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: [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).
