| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: report showing free space per tablespace over time
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.)
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);
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)
good luck,
Jack
-- 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 Wed Jun 26 2002 - 17:03:14 CDT
 |
 |