Tablespace usage report (w/ autoextend)

If you use auto extending table-spaces in your Oracle environment and use Grid Control to monitor them, you will know that it does not take the auto extension into account. The following script will give you a breakout of your TS usage including the auto extension.

============================================================
set pagesize 1000
set lines 200

col tablespace_name format a32 head 'Tablespace|Name'
col total format 999.99 head 'Total(GB)'
col used format 999.99 head 'Used(GB)'
col free format 999.99 head 'Free(GB)'
col pct format 999 head 'Pct|Used'
col next_extent format 999,999 head 'Next|Extent(KB)'
col Contents format a4 head 'Cont'
col Logging format a3 head 'Log'
col Extent_Management format a3 head 'Ext|Mgt'
col Allocation_Type format a5 head 'Alloc|Type'

SELECT inn1.tablespace_name tbs_name
 ,ROUND(inn1.total/1024,2) TOTAL_SPACE_GB
 ,ROUND((inn1.total-inn1.allocated+NVL(inn2.free,0))/1024,2) FREE_SPACE_GB
,ROUND((inn1.allocated-NVL(inn2.free,0))/1024,2) USED_SPACE_GB
,ROUND(((inn1.allocated-NVL(inn2.free,0))/inn1.total)*100) USED_PERCENT
FROM
(select tablespace_name,SUM(bytes)/1024/1024 allocated,SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1024/1024 total
    FROM dba_data_files
    GROUP BY tablespace_name) inn1,
(SELECT tablespace_name, NVL(SUM(bytes)/1024/1024,0) free
FROM dba_free_space
GROUP BY tablespace_name) inn2
WHERE inn1.tablespace_name= inn2.tablespace_name(+) 
and inn1.tablespace_name = '&tbsname' ;


SELECT file_name, tablespace_name, round(bytes / 1073741842,2) SIZE_GB, autoextensible, (increment_by * 32768) / 1048576 increment_MB, maxbytes / 1073741824 max_size_GB FROM dba_data_files WHERE tablespace_name = '&tbsname';