Oracle Database Discussions

Hi,


I generate a database growth report manually every week, Is there any scripts or package i can use to generate automatic database growth trend report. The script/dbms package/procedures should compare allocated space, used space, free space SYSDATE and SYSDATE-7 to generate weekly database growth report.

I use following scripts to find space usage in database.

SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME,
mbytes_alloc mbytes, mbytes_alloc - NVL (mbytes_free, 0) used,
NVL (mbytes_free, 0) free,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc)
* 100 pct_used,
100
- (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100)
pct_free
FROM (SELECT SUM(BYTES) / 1024 / 1024 mbytes_free, tablespace_name
FROM SYS.dba_free_space
GROUP BY tablespace_name) a,
(SELECT SUM(BYTES) / 1024 / 1024 mbytes_alloc, tablespace_name
FROM SYS.dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT f.tablespace_name,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)
) "total MB",
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB",
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0)
)
/ 1024
/ 1024,
2
)
) "Free MB",
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
/ (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100
- (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
/ (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
FROM SYS.v_$temp_space_header f,
dba_temp_files d,
SYS.v_$temp_extent_pool p
WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id
AND p.file_id(+) = d.file_id
GROUP BY f.tablespace_name
ORDER BY 5 desc --&orderby

Or is there any other alternatives to generate database growth report weekly. I know dbms_space package to find space usage but it cover for objects only not for a whole database.

Thanks in Advance.

Happy Christmas and Prosperous New Year

Jafar