Script for checking tablespace growth
with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and su.tablespace_id = ts.ts#
and ts.name =upper('USERS')
and ts.name = dt.tablespace_name )
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,
b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb
then to_char(e.used_size_gb - b.used_size_gb)
when e.used_size_gb = b.used_size_gb
then '***NO DATA GROWTH'
when e.used_size_gb < b.used_size_gb
then '******DATA PURGED' end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1
Tags :
About the Author
debasis maity
12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA
I tried to run for all tablespaces from a given base and received this error below, please see.
SQL> with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and su.tablespace_id = ts.ts#)
and ts.name = dt.tablespace_name )
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb – b.used_size_gb)
when e.used_size_gb = b.used_size_gb then ‘***NO DATA GROWTH’
when e.used_size_gb < b.used_size_gb then ‘******DATA PURGED’ end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 and ts.name = dt.tablespace_name )
*
ERROR at line 12:
ORA-00928: missing SELECT keyword
It seems some notepad to wordpress character conversion problem..I am checking if I can attach text files.Thanks for informing.
generic modafinil modafinil 100mg without prescription buy lopressor pills
Your comment is awaiting moderation.