How to analyze Tablespace growth?

In DBA life, there would be times where you got stuck with queries like,

What was the size of the tablespace ABC yesterday?
What is the average growth of the tablespce YYY for the last week?
Did the tablespace ZZZ got extended day before yesterday?
How much free space were there in the tablespace XTZ yesterday?
How to analyze the growth of a tablespace for last week?

Here is the answer for all these type of quries,

Just run the following sql in your DB.


SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_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 ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


The red line

AND ts.tsname NOT IN ('SYSAUX','SYSTEM')

is JUST AN EXAMPLE.

You can give any conditions here.

Hope it would be useful.

Thank you,

alimajaf