Skip to content

Instantly share code, notes, and snippets.

@mehdip2007
Created April 5, 2020 10:25
Show Gist options
  • Save mehdip2007/cde3c8cb454a98341cb0f9f05d95d818 to your computer and use it in GitHub Desktop.
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
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