MONITORING TABLESPACE FOR GROWTH,USED and FREE SPACE

As a DBA one of the routine tasks we should do is keeping an eye on the growth of the tablespaces in our databases. It is also important to check which tablespace  has been using how much of the storage space allocated to it and how much is left for use out of it? The point here is to make sure that the database is not in danger of freezing from shortage of space as segments keep on increasing in size. I have posted the following scripts along with respective sample outputs. 

The following script is used to display the tablespace growth over a period of time

set linesize 175 
set pagesize 2000
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 
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname, days;

DAYS       TSNAME                         CUR_SIZE_GB     USEDSIZE_GB
----------       -------------------            -----------                  -----------
01-11-2012 EMPDATA                        1119.87                1117.33
17-10-2014 EMPDATA                        6145.67                6074.91
18-10-2014 EMPDATA                        6145.67                6078.53
19-10-2014 EMPDATA                        6145.67                6078.54
20-10-2014 EMPDATA                        6145.67                6078.54
21-10-2014 EMPDATA                        6145.67                6078.94
22-10-2014 EMPDATA                        6145.67                6079.16
23-10-2014 EMPDATA                        6145.67                6080.6
24-10-2014 EMPDATA                        6145.67                6086.11
25-09-2014 EMPDATA                        6129.67                6000.02
25-10-2014 EMPDATA                        6145.67                6086.11
26-09-2014 EMPDATA                        6133.67                6043.16
27-09-2014 EMPDATA                        6133.67                6001.93
28-09-2014 EMPDATA                        6133.67                6007.77
29-09-2014 EMPDATA                        6133.67                6018.81

30-09-2014 EMPDATA                        6145.67                6073.52


The following script checks tablespaces for percentage of used and free spaces.


SET LINESIZE 175
SET PAGESIZE 5000
SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
 FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Tablespace                       Size (MB)            Free (MB)            % Free          % Used
------------------------------            ----------                ----------             - ---------       ----------
UNDOTBS1                         32767.9844         21782.875             66             34
TEMP                                  391                        197                       50             50
SYSAUX                             8228                    4052.25                 49             51
SYSTEM                             6530                    2898.1875             44             56