PROMPT Snapshots segment statistics and reports difference between 2 snapshots.
PROMPT *** Only works on 9i+ ***

PROMPT Requires 'GRANT SELECT ON v_$segment_statistics TO <tascha_schema>;'

CONN &&tascha_schema/&&tascha_password

CREATE TABLE segstats_snap AS
SELECT  TO_NUMBER(1)	snap_no
,	OWNER
,	OBJECT_NAME
,	STATISTIC_NAME
,	VALUE
FROM	v$segment_statistics
WHERE	1 = 2;

CREATE OR REPLACE PROCEDURE segstats(	I_own		IN VARCHAR2
				,	I_obj		IN VARCHAR2 DEFAULT NULL
				,	I_statname	IN VARCHAR2 DEFAULT NULL
				,	I_which 	IN NUMBER DEFAULT 1)
AS
	PROCEDURE write_row(	I_col1	IN VARCHAR2
			,	I_col2	IN VARCHAR2
			,	I_col3	IN VARCHAR2
			,	I_col4	IN VARCHAR2)
	IS
	BEGIN
		tascha_logger.info(	RPAD(I_col1,30)
				||' '||	RPAD(I_col2,30)
				||' '||	RPAD(I_col3,15));
	END write_row;
BEGIN
	IF I_which = 1 THEN
		EXECUTE IMMEDIATE 'TRUNCATE TABLE segstats_snap';
	END IF;

	IF I_obj IS NULL THEN
		INSERT INTO segstats_snap
			(snap_no
		,	owner
		,	object_name
		,	statistic_name
		,	value)
		SELECT	I_which
		, 	owner
		,	object_name
		,	statistic_name
		,	value
		FROM	v$segment_statistics
		WHERE	owner = UPPER(I_own)
		AND	statistic_name = NVL(I_stat_name,statistic_name)
		AND	value <> 0;
	ELSE
		INSERT INTO segstats_snap
			(snap_no
		,	owner
		,	object_name
		,	statistic_name
		,	value)
		SELECT	I_which
		, 	owner
		,	object_name
		,	statistic_name
		,	value
		FROM	v$segment_statistics
		WHERE	object_name = UPPER(I_obj)
		AND	owner = UPPER(I_own)
		AND	statistic_name = NVL(I_stat_name,statistic_name)
		AND	value <> 0;
	END IF;

	IF I_which = 2 THEN
		tascha_logger.info('Segment statistic output for schema "'||I_own||'"');
		tascha_logger.info(' ');
		write_row(	'OBJECT NAME'
			,	'STATISTIC'
			,	'VALUE');
		write_row(	RPAD('=',25,'=')
			,	RPAD('=',35,'=')
			,	RPAD('=',15,'='));

		FOR R_stats IN (SELECT	owner
				,	object_name
				,	LAG(object_name) OVER (ORDER BY object_name, snap_delta DESC)	prev_object
				,	statistic_name
				,	snap_delta
				FROM    (
					SELECT  owner
					,       object_name
					,       statistic_name
					,       NVL(SUM(DECODE(snap_no,1,value,0)),0)			first_snap
					,       NVL(SUM(DECODE(snap_no,2,value,0)),0)			second_snap
					,       NVL(SUM(DECODE(snap_no,2,value,0)),0)
					-       NVL(SUM(DECODE(snap_no,1,value,0)),0)			snap_delta
					FROM    segstats_snap
					GROUP BY owner
					,	object_name
					,	statistic_name
				)
				WHERE   second_snap <> first_snap
				ORDER BY object_name
				,	snap_delta DESC)
		LOOP
			IF R_stats.object_name <> NVL(R_stats.prev_object,'-') THEN
				write_row(	R_stats.object_name
					,	R_stats.statistic_name
					,	TO_CHAR(R_stats.snap_delta));
			ELSE
				write_row(	TO_CHAR(NULL)
					,	R_stats.statistic_name
					,	TO_CHAR(R_stats.snap_delta));
			END IF;
		END LOOP;
	END IF;
END segstats;
/