Monitoring Tablespace growth from sql

set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10


select * from dba_data_files;


select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files;

select
        file_name "Filename",
        tablespace_name "Tablespace",
        round(bytes/1024/1024 ,2) "Current Size (MB)",
        autoextensible "Autoextend",
        round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
        round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'SYSTEM'; 

The following will show you the total size and percentage usage of each tablespace, order by percentage:

select T1.TABLESPACE_NAME,
       round(T1.BYTES/1024/1024 ,2) "Used Size (MB)",
       round(T2.BYTES/1024/1024 ,2) "Free Size (MB)",
       round(T2.largest/1024/1024 ,2) "Largest Datafile (MB)",
       round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) "Used %"
  from
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES
   from dba_data_files
   group by TABLESPACE_NAME
  )
   T1,
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES ,
   max(BYTES) largest
   from dba_free_space
   group by TABLESPACE_NAME
  )
   T2
   where T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
   order by ((T1.BYTES-T2.BYTES)/T1.BYTES) desc;
 
 
If you do not want to convert the bytes into MB or whatever, use the following. Otherwise you will get the high values as power of 10 (eg. 3.4360E+10):

column maxbytes format 9,999,999,999,999
column bytes format 9,999,999,999,999
column user_bytes format 9,999,999,999,999



select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB" from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size

from v$controlfile) d;