/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        ACTUAL VS ALLOCATED STORAGE REPORT        */
/*                                                  */
/* name:    @c:\oracle\sql\allvsact.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas                           */ 
/*                                                  */
/* ************************************************ */


ACCEPT Username CHAR PROMPT 'Enter Username: ';

 	SET TERM OFF;
	TTITLE OFF;
	BTITLE  OFF;
	SET PAGESIZE 0;
	SET VERIFY OFF;
	SET HEADING OFF;
	SET RECSEP OFF;

  	CREATE TABLE temp_size_table(
  		table_name	VARCHAR2(30),
  		blocks		NUMBER)
	tablespace tstemp1
  	STORAGE( INITIAL 10K NEXT 10K
  		MINEXTENTS 1 MAXEXTENTS 4);
REM
	SPOOL allvsact_fill.sql
REM
	SELECT 'INSERT INTO temp_size_table',
		' SELECT ','&'||'temp_var.' || segment_name ||'&'||'temp_var',
		', COUNT( DISTINCT( SUBSTR( ROWID,1,8))) blocks',
		' FROM ', '&&Username' || '.' || segment_name, ';'
	FROM dba_segments
		WHERE segment_type = 'TABLE'
		and owner = '&&Username';
REM	SPOOL OFF;
REM	DEFINE temp_var = '''';
	START allvsact_fill;
REM
	SET TERM ON;
	TTITLE ON;
	BTITLE  OFF;
REM	SET VERIFY ON;
	SET HEADING ON;
	SET LINESIZE 79;
	SET PAGESIZE 58;
	SET NEWPAGE 0;
REM	COLUMN segment_name FORMAT A25 HEADING "SEGMENT|NAME";
	COLUMN segment_type FORMAT A7 HEADING "SEGMENT|TYPE";
	COLUMN extents FORMAT 999 HEADING "EXT";
	COLUMN bytes FORMAT A6 HEADING "BYTES";
	COLUMN blocks FORMAT 9,999,999 HEADING "ORACLE|BLOCKS";
	COLUMN act_blocks FORMAT 9,999,999 HEADING "ACTUAL|USED|BLOCKS";
	COLUMN pct_block FORMAT 999.99 HEADING "PCT|BLOCKS|USED";
REM
REM
	TTITLE "ACTUAL VS ALLOCATED STORAGE REPORT"
	define output = 'allvsact.lis'
	SPOOL &output
REM	SELECT segment_name, segment_type,
		extents, to_char( bytes/1024)||'K' bytes, a.blocks,
		b.blocks act_blocks, b.blocks/a.blocks*100 pct_block
	FROM sys.dba_segments a, temp_size_table b
		WHERE segment_name = UPPER( b.table_name )
		and a.owner = '&&Username';
REM
	SPOOL OFF;
REM
	  DROP TABLE temp_size_table;
REM
    	EXIT;