REM
/*
 * TablespaceFreeSpace.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

SET TERMOUT off
REM Get block size from V$PARAMETER and store for use in other utilities
undef _db_block_size
column xpvx new_value _db_block_size heading "Block size|(bytes)" Format A10
select	p.Value xpvx
from 	V$PARAMETER p
WHERE	name = 'db_block_size'
/

SET TERMOUT on
CLEAR COLUMNS
ACCEPT TablespaceName PROMPT "Enter the name of the tablespace to report space on: "
SET VER off ECHO off LINES 200

column Pct_Free format 999.99
column mbb format 999,999,999 Heading "Max Contig|Space|(blocks)"
column nf format 999 Heading "No.|of|files"
column count_blocks format 999,999,999
column sum_free_blocks format 999,999,999 
column mbk format 999,999,999 Heading "Max Contig|Space|(k)"
column sfb format 999,999,999 Heading "Total Free|Space|(blocks)"
column sfk format 999,999,999 Heading "Total Free|Space|(k)"
column sab format 999,999,999 Heading "Total Alloc|Space|(blocks)"
column sak format 999,999,999 Heading "Total Alloc|Space|(k)"
column sub format 999,999,999 Heading "Total Used|Space|(blocks)"
column suk format 999,999,999 Heading "Total Used|Space|(k)"
column tn format A20 Heading "Tablespace Name"
column chunks format 999,999 Heading "#free|chunks"
column pct_free format 999.99 Heading "%free"

select Tablespace_Name tn,no_of_files nf,
Max_Blocks mbb,
(Max_blocks * &&_db_block_size)/1024 mbk,
Count_Blocks chunks,
Sum_Free_Blocks sfb,
(Sum_Free_Blocks*&&_db_block_size)/1024 sfk,
NVL(sum_alloc_blocks,0)-NVL(Sum_Free_Blocks,0) sub,
((NVL(sum_alloc_blocks,0)-NVL(Sum_Free_Blocks,0))*&&_db_block_size)/1024 suk,
sum_alloc_blocks sab,
(NVL(sum_alloc_blocks,0)*&&_db_block_size)/1024 sak,
100*NVL(Sum_Free_Blocks,0)/Sum_Alloc_Blocks AS Pct_Free
,Tablespace_Name tn
from
(select Tablespace_Name, count(*) no_of_files,SUM(Blocks) Sum_Alloc_Blocks
from DBA_DATA_FILES 
group by Tablespace_Name
UNION ALL
select Tablespace_Name, count(*) no_of_files,SUM(Blocks) Sum_Alloc_Blocks
from DBA_temp_FILES 
group by Tablespace_Name
), 
   (select Tablespace_Name FS_TS_NAME,
   MAX(Blocks)  AS Max_Blocks,
   COUNT(Blocks)  AS Count_Blocks,
   SUM(Blocks) AS Sum_Free_Blocks
   from DBA_FREE_SPACE
   group by Tablespace_Name)
where Tablespace_Name = FS_TS_NAME(+)
And	Tablespace_Name LIKE UPPER('&&TablespaceName')
order by 1;

SET DOC off
/* SUMMARY - Show free, allocated, used space for a tablespaces with count of the number of datafiles, extents, free chunks.
 *
 * DETAIL - A quick and easy way to see the essential space details for any or all tablespaces. It shows what is used, what 
 *          was allocated and how much is left (all in blocks as well as bytes). The percentage of space left free is calculated
 *          and an indicate of fragmentation given by the number of chunks that make up the total free space and the largest single
 *          chunk of free space (maximum contiguous space). Having been first written for Oracle7 it still works all the way
 *          up to 10g (and RAC). Some of the derived data is available as data dictionary columns in the later versions of Oracle
 *          but the script retains its original Oracle7 format for backwards compatability.
 *
 * Sample usage of this script might result in the following output:
 *
 * SQL>start d:/timonionscom/tablespacefreespace.sql
 * Enter the name of the tablespace to report space on: TS%BABA%
 *
 *                       No.   Max Contig   Max Contig            Total Free    Total Free   Total Used   Total Used  Total Alloc  Total Alloc
 *                       of        Space        Space     #free        Space         Space        Space        Space        Space        Space
 * Tablespace Name      files     (blocks)          (k)   chunks     (blocks)          (k)     (blocks)          (k)     (blocks)          (k)   %free Tablespace Name
 * -------------------- ----- ------------ ------------ -------- ------------ ------------ ------------ ------------ ------------ ------------ ------- --------------------
 * TS_DATA_BABA             2       98,304      786,432        1       98,304      786,432      163,968    1,311,744      262,272    2,098,176   37.48 TS_DATA_BABA
 * TS_DATA_BABABARAN        1       65,536      524,288        1       65,536      524,288       65,664      525,312      131,200    1,049,600   49.95 TS_DATA_BABABARAN
 *
 * (c) Tim Onions 2007
 * Script used at the database owners risk.
 */
REM