Saturday, April 2, 2016

Month wise Space growth

To check growth month wise:
==============================
set linesize 80 
set pagesize 200
col year for a10
col month for a10 compute sum of SPACE_ALLOCATED_GB on tablespace_name
SELECT TO_CHAR(vdf.CREATION_TIME,'YYYY') "YEAR",
TO_CHAR(vdf.CREATION_TIME,'MON') "MONTH",ROUND(sum(vdf.BYTES)/1024/1024/1024) "SPACE_ALLOCATED_GB"
FROM V$DATAFILE vdf, dba_data_files ddf
where vdf.file#=ddf.file_id
GROUP BY TO_CHAR(vdf.CREATION_TIME,'YYYY'),TO_CHAR(vdf.CREATION_TIME,'MON')
order by TO_CHAR(vdf.CREATION_TIME,'YYYY') ,
decode(TO_CHAR(vdf.CREATION_TIME,'MON'),'JAN',1,'FEB',2,'MAR',3,'APR',4,'MAY',5,'JUN',6,'JUL',7,'AUG',8,'SEP',9,'OCT',10,'NOV',11,'DEC',12)
/

No comments :

Post a Comment