REM
/*
 * ContiguousSpace.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!
 */

PROMPT Look in DBA_FREE_SPACE and list contiguous space, a parameter can be used to specify a threshold of
PROMPT a number of "chunks" of contiguous to report on (ie reporting on single chunks is probably not very useful).
Prompt NB Requires/users DBMS_OUTPUT

SET PAGES 40 VER OFF
SET SERVEROUT ON SIZE 200000

ACCEPT Tablespace_name Prompt "Enter the name of the tablespace to report on: "
ACCEPT Chunk_Minimum Prompt "Enter the minimum number of contiguous chunks to report on: "

DECLARE
/*
 * Cursor to get the most commonly used extent size for a given tablespace
 */
	CURSOR CU_ts (P_tablespace_name dba_tablespaces.tablespace_name%TYPE)
	IS
	SELECT	 de.tablespace_name
		,de.blockS
		,de.bytes
		,de.cnt
	FROM	(SELECT tablespace_name,COUNT(*) cnt,blocks,bytes FROM dba_extents GROUP BY tablespace_name,blocks,bytes) de
	WHERE	de.tablespace_name = P_Tablespace_name
	ORDER BY de.cnt DESC, de.blocks DESC;

	
	CURSOR CU_fs IS
	SELECT	fs.tablespace_name,
		fs.block_id,
		fs.blocks,
		df.file_id,
		df.file_name
	FROM	dba_free_space fs,
		dba_data_files df
	WHERE	fs.file_id = df.file_id
	AND	fs.tablespace_name LIKE UPPER('&&Tablespace_name')
	ORDER BY fs.tablespace_name, fs.file_id, fs.block_id;
--
	R_fs		CU_fs%ROWTYPE;
	R_old_fs	CU_fs%ROWTYPE;
	R_ts		CU_ts%ROWTYPE;
	MT_ts		CU_ts%ROWTYPE;
--
	L_segm_file_id	dba_free_space.file_id%TYPE;
	L_segm_file_name	dba_data_files.file_name%TYPE;
	L_segm_ts	dba_free_space.tablespace_name%TYPE;
	L_segm_block_id	dba_free_space.block_id%TYPE;
	L_segm_blocks 	dba_free_space.blocks%TYPE;
	L_segm_chunks	NUMBER(9);
	L_segm_block_list VARCHAR2(32000);
--
BEGIN
	OPEN 	CU_fs;
	FETCH CU_fs INTO R_fs;
	R_old_fs := R_fs;
	L_segm_ts     := R_fs.tablespace_name;
	L_segm_file_NAME := R_fs.file_name;
	L_segm_file_id := R_fs.file_id;
	L_segm_block_id := R_fs.block_id;
	L_segm_blocks := R_fs.blocks;
	L_segm_chunks := 1;
	L_segm_block_list := R_fs.block_id||'('||R_fs.blocks||') ';
--
	WHILE CU_fs%FOUND LOOP
		FETCH CU_fs INTO R_fs;
		IF R_fs.file_id = R_old_fs.file_id AND
		   R_fs.block_id = R_old_fs.block_id + R_old_fs.blocks
		THEN
--DBMS_OUTPUT.PUT_LINE('contig FID='||R_fs.file_id||', BID='||R_fs.block_id||', END='||(R_old_fs.block_id + R_old_fs.blocks));
--
-- This segment is contiguous with the last so "coalesce"
--
			L_segm_blocks := L_segm_blocks + R_fs.blocks;
			L_segm_chunks := L_segm_chunks + 1;
			L_segm_block_list := L_segm_block_list||R_fs.block_id||'('||R_fs.blocks||') ';
		ELSE
--DBMS_OUTPUT.PUT_LINE('end FID='||R_fs.file_id||', BID='||R_fs.block_id||', END='||(R_old_fs.block_id + R_old_fs.blocks));
--
-- We have reached the end of a contiguous set of segments so report on it and set up for next fetch
--
			IF L_segm_chunks >= &&Chunk_minimum THEN
				DBMS_OUTPUT.PUT_LINE('Tablespace: '||L_segm_ts);
--				DBMS_OUTPUT.PUT_LINE('File ID: '||L_segm_file_id);
				DBMS_OUTPUT.PUT_LINE('File name: '||L_segm_file_name);
				DBMS_OUTPUT.PUT_LINE('Start->End blocks: '||L_segm_block_id||'->'||(L_segm_block_id+L_segm_blocks));
				DBMS_OUTPUT.PUT_LINE('Number of blocks: '||L_segm_blocks);
				DBMS_OUTPUT.PUT_LINE('Number of chunks: '||L_segm_chunks);
				DBMS_OUTPUT.PUT_LINE('Chunk details: '||L_segm_block_list);
--
-- Work out how many more extnts of the most common size will fit in this contiguous space
--
				IF CU_ts%ISOPEN THEN
					CLOSE CU_ts;
				END IF; -- CU_ts%ISOPEN

				R_ts := MT_ts;
				OPEN CU_ts(L_segm_ts);
				FETCH CU_ts INTO R_ts;
				CLOSE CU_ts;
				DBMS_OUTPUT.PUT_LINE('Most common extent size: '||R_ts.blocks||' blocks');
				DBMS_OUTPUT.PUT_LINE('# of '||R_ts.blocks||' block extents for this space: '||ROUND(L_segm_blocks/R_ts.blocks,4));
--
				DBMS_OUTPUT.PUT_LINE('.');
			END IF;
			L_segm_ts     := R_fs.tablespace_name;
			L_segm_file_NAME := R_fs.file_name;
			L_segm_file_id := R_fs.file_id;
			L_segm_block_id := R_fs.block_id;
			L_segm_blocks := R_fs.blocks;
			L_segm_chunks := 1;
			L_segm_block_list := R_fs.block_id||'('||R_fs.blocks||') ';

		END IF;
		R_old_fs := R_fs;
--
--		FETCH CU_fs INTO R_fs;
	END LOOP;
	CLOSE CU_fs;
END;
/

Undef Chunk_Minimum
Undef Tablespace_name

SET DOC off
/* SUMMARY - Report on free space in a tablespace, showing each chunk of contiguous space made up of one or more segments of unused 
 *           space giving an indication of how many extents of the most commonly used objects in the tablespace would fit.
 *           
 *
 * DETAIL - report on the free space in your tablespaces showing each chunk of contiguous space, how big it is (in blocks)
 * and how many extents of the most commonly used size will fit into the space. The report works out which areas of free space
 * are actually adjacent to each other to determine the total space that could be allocated as a contiguous chunk. This is where
 * it scores over other free space reports which show you either the overall total amount of space available but with no indication
 * as to whether or not it is usuable (it may be fragmented into sizes too small for allocation) or each piece of free space without
 * working out if one/two/many areas of free space can be allocated together as a single larger chunk.
 * 
 * The most commonly used extent size already allocated in the tablespace is used to show how many extents of that same size could
 * fit into the remaining free space.
 * Note that this script, although written for SQL*Plus, uses DBMS_OUTPUT to display the resulting data. If your tablespace is defined
 * to have a fixed extent size then this script offer little benefit. It is of most use when a variety of extent sizes have been used as this can
 * result in a variety of sizes of areas of free space which may, or may not, be useable.
 *          
 * (c) Tim Onions 2007
 * Script used at the database owners risk.
 */
REM