Created
April 5, 2020 10:25
-
-
Save mehdip2007/cde3c8cb454a98341cb0f9f05d95d818 to your computer and use it in GitHub Desktop.
You can use the following snippet to check how much your tablespace is growing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT to_char(creation_time, | |
'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth IN GB" | |
FROM sys.v_$datafile | |
WHERE to_char(creation_time,'RRRR')='2014' | |
GROUP BY to_char(creation_time, 'MM-RRRR') | |
ORDER BY to_char(creation_time, 'MM-RRRR'); -----SELECT b.tsname tablespace_name, | |
MAX (b.used_size_mb) cur_used_size_mb, | |
ROUND (AVG (inc_used_size_mb), | |
2) avg_increas_mb | |
FROM | |
(SELECT a.days, | |
a.tsname, | |
used_size_mb, | |
used_size_mb - LAG (used_size_mb, | |
1) | |
OVER (PARTITION BY a.tsname | |
ORDER BY a.tsname, a.days) inc_used_size_mb | |
FROM | |
(SELECT TO_CHAR (sp.begin_interval_time, | |
'MM-DD-YYYY') days, ts.tsname, MAX ( ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024), 2)) used_size_mb | |
FROM dba_hist_tbspc_space_usage tsu, dba_hist_tablespace_stat ts, dba_hist_snapshot sp, dba_tablespaces dt | |
WHERE tsu.tablespace_id = ts.ts# | |
AND tsu.snap_id = sp.snap_id | |
AND ts.tsname = dt.tablespace_name | |
AND sp.begin_interval_time > SYSDATE - 7 | |
GROUP BY TO_CHAR (sp.begin_interval_time, 'MM-DD-YYYY'), ts.tsname | |
ORDER BY ts.tsname, days) a) b | |
GROUP BY b.tsname | |
ORDER BY b.tsname; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment