/**********************************************************************
 * File:        space_usage.sql
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        03Sep00
 *
 * Description:
 *      SQL*Plus script to display segment space usage using the
 *	DBMS_SPACE procedures.  Accomodates non-partitioned, partitioned,
 *	and sub-partitioned objects.  Also accomodates both types of
 *	segment-space management:  MANUAL and AUTO.
 *
 * Modifications:
 *********************************************************************/
set echo on feedback on timing on

spool space_usage

create or replace procedure space_usage (p_in_owner in varchar2, p_in_segment in varchar2)
as
        --
	v_FreeBlocks		number;
	v_TotalBlocks		number;
	v_TotalBytes		number;
	v_UnusedBytes		number;
	v_UnusedBlocks		number;
	v_LastUsedBlock		number;
	v_LastUsedExtFileId	number;
	v_LastUsedExtBlockId	number;
	v_UnformattedBlocks	number;
	v_UnformattedBytes	number;
	v_FS1_Blocks		number;
	v_FS1_Bytes		number;
	v_FS2_Blocks		number;
	v_FS2_Bytes		number;
	v_FS3_Blocks		number;
	v_FS3_Bytes		number;
	v_FS4_Blocks		number;
	v_FS4_Bytes		number;
	v_FullBlocks		number;
	v_FullBytes		number;
	v_TimeStamp		date;
	v_SSM			varchar2(30);
	v_BytesUsed		number := 0;
	--
	v_ErrContext		varchar2(80);
	v_ErrMsg		varchar2(1000);
	v_SaveModule		varchar2(48);
	v_SaveAction		varchar2(32);
	--
	cursor get_segments (in_owner in varchar2, in_segment in varchar2)
	is
	select	partition_name,
		segment_type,
		tablespace_name,
		extents,
		buffer_pool
	from	dba_segments
	where	owner = in_owner
	and	segment_name = in_segment;
	--
	procedure p(p_label in varchar2, p_num in number )
	is
	begin
		dbms_output.put_line(rpad(p_label,40,'.')||p_num);
	end;
	--
begin
	--
	v_ErrContext := 'read/set DBMS_APINFO';
	dbms_application_info.read_module(v_SaveModule, v_SaveAction);
	dbms_application_info.set_module('Procedure SCHEMA_SPACE', v_ErrContext);
	--
	v_ErrContext := 'open/fetch get_segments('||p_in_owner||','||p_in_segment||')';
	dbms_application_info.set_action(v_ErrContext);
	for x in get_segments(p_in_owner, p_in_segment) loop
		--
		v_TimeStamp := sysdate;
		--
		select	t.segment_space_management
		into	v_SSM
		from	dba_segments		s,
			dba_tablespaces		t
		where	s.owner = p_in_owner
		and	s.segment_name = p_in_segment
		and	s.segment_type = x.segment_type
		and	nvl(s.partition_name,'~') = nvl(x.partition_name,'~')
		and	t.tablespace_name = s.tablespace_name;
		--
		dbms_output.put_line('.');
		dbms_output.put_line(rpad('Owner:',40,'.')||p_in_owner);
		dbms_output.put_line(rpad('Segment Name:',40,'.')||p_in_segment);
		dbms_output.put_line(rpad('Partition Name:',40,'.')||x.partition_name);
		dbms_output.put_line(rpad('Segment Type:',40,'.')||x.segment_type);
		dbms_output.put_line(rpad('# of extents:',40,'.')||x.extents);
		dbms_output.put_line(rpad('Buffer Pool:',40,'.')||x.buffer_pool);
		dbms_output.put_line(rpad('Tablespace Segment Spc Mgmt:',40,'.')||v_SSM);
		--
		if v_SSM = 'MANUAL' then
			--
			v_ErrContext := 'dbms_space.free_blocks';
			dbms_space.free_blocks (
				segment_owner			=> p_in_owner,
				segment_name			=> p_in_segment,
				segment_type			=> x.segment_type,
				partition_name			=> x.partition_name,
				freelist_group_id		=> 0,
				free_blks			=> v_FreeBlocks);
			--
			v_ErrContext := 'dbms_space.unused_space';
			dbms_space.unused_space (
				segment_owner			=> p_in_owner,
				segment_name			=> p_in_segment,
				segment_type			=> x.segment_type,
				partition_name			=> x.partition_name,
				total_blocks			=> v_TotalBlocks,
				total_bytes			=> v_TotalBytes,
				unused_blocks			=> v_UnusedBlocks,
				unused_bytes			=> v_UnusedBytes,
				last_used_block			=> v_LastUsedBlock,
				last_used_extent_file_id	=> v_LastUsedExtFileId,
				last_used_extent_block_id	=> v_LastUsedExtBlockId);
			--
			p('Total blocks', v_TotalBlocks);
			p('Total bytes', v_TotalBytes);
			p('Unused blocks', v_UnusedBlocks);
			p('Unused bytes', v_UnusedBytes);
			p('Free blocks', v_FreeBlocks);
			p('Last used block', v_LastUsedBlock);
			p('Last used ExtFileID', v_LastUsedExtFileId);
			p('Last used ExtBlockID', v_LastUsedExtBlockId);
			--
			v_BytesUsed := v_BytesUsed + v_TotalBytes;
			--
		else
			--
			dbms_space.space_usage(
				segment_owner			=> p_in_owner
				, segment_name			=> p_in_segment
				, segment_type			=> x.segment_type
				, partition_name		=> x.partition_name
				, unformatted_blocks		=> v_UnformattedBlocks
				, unformatted_bytes		=> v_UnformattedBytes
				, fs1_blocks			=> v_FS1_Blocks
				, fs1_bytes			=> v_FS1_Bytes
				, fs2_blocks			=> v_FS2_Blocks
				, fs2_bytes			=> v_FS2_Bytes
				, fs3_blocks			=> v_FS3_Blocks
				, fs3_bytes			=> v_FS3_Bytes
				, fs4_blocks			=> v_FS4_Blocks
				, fs4_bytes			=> v_FS4_Bytes
				, full_blocks			=> v_FullBlocks
				, full_bytes			=> v_FullBytes);
			--
			p('Unformatted blocks', v_UnformattedBlocks);
			p('Unformatted bytes', v_UnformattedBytes);
			p('FS1 blocks', v_FS1_Blocks);
			p('FS1 bytes', v_FS1_Bytes);
			p('FS2 blocks', v_FS2_Blocks);
			p('FS2 bytes', v_FS2_Bytes);
			p('FS3 blocks', v_FS3_Blocks);
			p('FS3 bytes', v_FS3_Bytes);
			p('FS4 blocks', v_FS4_Blocks);
			p('FS4 bytes', v_FS4_Bytes);
			p('Full blocks', v_FullBlocks);
			p('Full bytes', v_FullBytes);
			--
			v_BytesUsed := v_BytesUsed + v_UnformattedBytes +
					v_FS1_Bytes + v_FS2_Bytes +
					v_FS3_Bytes + v_FS4_Bytes + v_FullBytes;
			--
		end if;
		p('Total Bytes Used', v_BytesUsed);
		--
		v_ErrContext := 'fetch get_segments('||p_in_owner||','||p_in_segment||')';
		dbms_application_info.set_action(v_ErrContext);
		--
	end loop;
	--
	dbms_application_info.set_module(v_SaveModule, v_SaveAction);
	--
exception
	when others then
		v_ErrMsg := sqlerrm;
		dbms_application_info.set_module(v_SaveModule, v_SaveAction);
		raise_application_error(-20000, v_ErrContext || ': ' || v_ErrMsg);
end space_usage;
/
show error

spool off