Oracle Database Daily Growth check Script

Oracle Database Daily Growth check

As a DBA, it was often asked me how the database grows and when we'll full up the disc space.
Now, when i create a database, i automatically add a job to follow the space occupation.

I give you my tip. It's in three parts:
1- a table, to record the statistics
2- a Procedure 
3- a job which execute the procedure, run every day at midnight.


1) the table
===========
CREATE TABLE watch_file (
heure DATE,
tablespace_name VARCHAR2(30),
file_name VARCHAR2(513),
total_bytes NUMBER,
used_bytes NUMBER,
max_bytes NUMBER,
ok NUMBER(1)
)
/
CREATE INDEX watch_file_idx1 ON watch_file (tablespace_name, heure)
/
CREATE INDEX watch_file_idx3 ON watch_file (heure)
/



grant select on dba_extents to halim
grant select on dba_tablespaces to halim
grant select on dba_data_files to halim



2) the procedure
=================

create or replace procedure DB_growth_check_daily is

CURSOR curs IS
SELECT ts.tablespace_name, f.file_name, f.BYTES,
NVL (ext.BYTES, 0) + f.BYTES - f.user_bytes used_bytes,
DECODE (f.autoextensible,
'YES', f.maxbytes,
f.BYTES
) max_bytes
FROM dba_tablespaces ts,
dba_data_files f,
(SELECT file_id, SUM (BYTES) BYTES
FROM dba_extents
GROUP BY file_id) ext
WHERE f.tablespace_name = ts.tablespace_name AND ext.file_id(+) =
f.file_id
ORDER BY 1, 2;

depart DATE;
s_ok number:=0;
BEGIN

depart := SYSDATE;

FOR rec IN curs LOOP
s_ok:=s_ok+1 ;
INSERT INTO watch_file
(heure, tablespace_name, file_name, total_bytes,
used_bytes, max_bytes, ok
)
VALUES (depart, rec.tablespace_name, rec.file_name, rec.BYTES,
rec.used_bytes, rec.max_bytes,s_ok
);
END LOOP;

COMMIT;

END;



4)   run or (make a job) with the procedure
================================

SQL> exec DB_growth_check_daily ;