REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_unused.sql
REM
REM <SQLDIR_GRP>STORAGE SEGMENT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script used to get the space usage (High-water mark)
REM	for TABLE, INDEX or CLUSTER
REM	The high-water mark for a table is the difference between the
REM	TOTAL_BLOCKS value and UNUSED_BYTES value returned by this procedure call.
REM	The UNUSED_BLOCKS value represents the number of blocks above
REM	the high-water mark; the TOTAL_BLOCKS value reflects the total
REM	number of blocks allocated to the table
REM	After executing this script you can reclaim space from a TABLE
REM	For example:
REM	TOTAL_BLOCKS = 200
REM	UNUSED_BLOCKS = 100
REM	if the database BLOCK size = 4K, then 100 blocks--400K--could be reclaimed
REM	if you want to leave 20 blocks within the table as unused space above the
REM	high-water mark, you can alter the table, specifying that the database
REM	keep 20 blocks--80K:
REM	SQL> alter table <TABLE_NAME> deallocate unused keep 80K;
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_unused.sql
REM 
REM Example:
REM	c_unused.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set term on
set serveroutput on
set feedback on
set echo off

declare
var1			number;
var2			number;
var3			number;
var4			number;
var5			number;
var6			number;
var7			number;

begin
dbms_space.unused_space(upper('&owner'),upper('&&object_name'),upper('&object_type'),
				VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);

	dbms_output.put_line('OBJECT_NAME               = &&object_name');
	dbms_output.put_line('--------------------------------------');
	dbms_output.put_line('TOTAL_BLOCKS              = '||VAR1);
	dbms_output.put_line('TOTAL_BYTES               = '||VAR2);
	dbms_output.put_line('UNUSED_BLOCKS             = '||VAR3);
	dbms_output.put_line('UNUSED_BYTES              = '||VAR4);
	dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||VAR5);
	dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
	dbms_output.put_line('LAST_USED_BLOCK           = '||VAR7);
end;
/
undefine object_name
undefine owner
undefine object_type