Hi Masters,
I used the below script for find out tablespace growth for 3 / 6 months .. But I need db growth using same table. Please advise ..
SQL> select tablespace_name,
log_date,
size_in_mb,
used,
free,
used - first_value(used) over(partition by tablespace_name order by log_date) diff
from DBMON.tbs_growth_trend
where LOG_DATE >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-5) and to_char(log_date,'dd') = '01'
and tablespace_name='SMP_DBUSER_BR_DATA'
order by tablespace_name, log_date;
TABLESPACE_NAME LOG_DATE SIZE_IN_MB USED FREE DIFF
---------------------------------------------------- --------- ---------- ---------- ---------- ----------
SMP_DBUSER_BR_DATA 01-AUG-18 19496 16832 2664 0
SMP_DBUSER_BR_DATA 01-SEP-18 23592 20667 2925 3835
SMP_DBUSER_BR_DATA 01-OCT-18 26664 22731 3933 5899
SMP_DBUSER_BR_DATA 01-NOV-18 28712 24874 3838 8042
SMP_DBUSER_BR_DATA 01-DEC-18 30760 26571 4189 9739
SMP_DBUSER_BR_DATA 01-JAN-19 30760 26917 3843 10085
6 rows selected.
SQL> desc DBMON.tbs_growth_trend;
Name Null? Type
------------------------------------------------
LOG_DATE NOT NULL DATE
TABLESPACE_NAME VARCHAR2(100)
FILE_COUNT NUMBER
SIZE_IN_MB NUMBER
FREE NUMBER
USED NUMBER
MAX_EXTENT NUMBER
PERCENT_FREE NUMBER
GRAPH VARCHAR2(10)
instead of table space growth .. I need db growth like same format. Please advise ..
select
decode
(
grouping_id (to_char(creation_time, 'YYYY MM'))
, 1
, 'Total'
, to_char(creation_time, 'YYYY MM')
) mnth
, sum(bytes)/1024/1024/1024 growth_gb
from v$datafile
where creation_time > SYSDATE - 365
group by rollup (to_char(creation_time, 'YYYY MM')); --- instead of using v$datafile .. shall I use my table? I replaced creation_time with log_date, but for bytes... which column I can used?
BR
Frd