Hi,
DB Version:12 C
I have scheduled below SQL to run on weekly basis to capture db growth.
On a certain week (11feb), the result shows no growth ( USED GB) as shown below. Please advise why there is no growth as there is lot of Insert's' happening on daily basis on this Production database.
Also On 16th , Growth was double (29 GB) when there is no major change in data load (DML) volume.
SELECT SYSDATE,
ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024) TOTAL,
ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024) -
ROUND(FREE.P / 1024 / 1024 / 1024) USED,
ROUND(FREE.P / 1024 / 1024 / 1024) FREE
FROM (SELECT BYTES
FROM V$DATAFILE
WHERE TS# NOT IN (SELECT TS#
FROM V$TABLESPACE
WHERE NAME LIKE '%UNDO%')
UNION ALL
SELECT BYTES
FROM V$TEMPFILE
UNION ALL
SELECT BYTES
FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME NOT LIKE 'UNDO%') FREE
GROUP BY FREE.P;
RUN_DATE DB_GB USED_GB FREE_GB
----------- ------- ----------- -----------
31-Jan-19 2522 2023 499
04-Feb-19 2522 2037 485 -- 14 GB Increase
11-Feb-19 2522 2037 485 -- No Growth
16-Feb-19 2522 2066 456 -- 29 GB Increase.
Regards,
Veera