Calculating Actual Space Used

From: Jean Anderson <jean_at_seismo.CSS.GOV>
Date: 18 Jan 93 01:08:21 GMT
Message-ID: <51848_at_seismo.CSS.GOV>

Last August I posted a SQL*Plus script that reports actual space used by database objects. I started with Maurice Manton's ACTUAL_SIZE.SQL script from the 1990 IOUW, and added INDEX information.

Bill Pribyl (bill_at_datacraft.com) sent me a straight forward way to force linefeeds in SQL*Plus. And Garth DeCocq (uswnvg!gdecocq_at_uunet.UU.NET) has made an enormous contribution by adding support for clusters and trend analysis.

Here it is again. It has grown to three scripts:

   actsize       Extracts a list of tables and indexes from sys.dba_segments,
                 generates a temporary SQL script with the actual commands
                 necessary for calculating space, which it then executes.
                 Finally it executes actsize_rpt.
   actsize_rpt   Generates a report from the temporary table created by 
                 actsize.sql.

   actsize_arch Archives the data in the temporary table for trend analysis.

Enjoy. And my thanks to everyone who sent suggestions!

  -jean

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean_at_esosun.css.gov  |
| SAIC Open Systems Division, MS A2-F                                   |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
+-----------------------------------------------------------------------+

============================< actsize.sql >===================================
rem FILE
rem	actsize.sql

rem
rem DESCRIPTION

rem	ORACLE stores how much space has been allocated to objects in the
rem	SYS.USER_SEGMENTS and SYS.DBA_SEGMENTS data dictionary views.
rem	Actual usage, however, is not stored.
rem
rem	This script reports allocated and actual space used by tables and
rem	indexes belonging to a given user.  It has three parts:
rem
rem	actsize 	The main SQL*Plus script, this extracts a list of 
rem			tables and indexes from sys.dba_segments and generates
rem			a temporary SQL script, space_temp.sql, with the actual
rem			commands necessary for calculating space.
rem	actsize_rpt	Generates a report from the temporary table created
rem			by actsize.sql.
rem	actsize_arch	Archives the data in the temporary table for trend
rem			analysis.
rem
rem	This script is a hog. It must be run by an account with dba privileges
rem	and care should be taken not to run it on a large account during 
rem	database prime time usage.

rem
rem CONFIGURATION
rem

rem	Clusters:
rem	--------
rem     For cluster calculations to work, add a comment to the master table of
rem	the cluster identifying it as 'MASTER'.  
rem
rem	In sqlplus do 'comment on table <table_name> is 'MASTER';'.
rem
rem     This is inserted into space_temp with a segment type of 'TABLE'
rem     so it will sort ahead of the indexes in actsize_rpt.sql.
rem
rem	Data Archival:
rem	-------------
rem	Create a permanent archival table that is the structure of the
rem	space_temp table (see below), with the addition of a date field
rem	named 'extract_dt'.
rem
rem	Then uncomment the archive step at the end of this script.

rem
rem USAGE
rem sqlplus -s dba/password _at_actsize report_name acct_id report_program rem
rem (ie sqlplus -s dba_password _at_actsize actsize.rpt cwat0 actsize_rpt) rem
rem NOTES
rem This section describes how actual usage is calculated. rem

rem	CALCULATING SPACE USED BY TABLES
rem	--------------------------------
rem	ORACLE tags each record with a unique rowid that contains the datafile,
rem	the block number, and the rownumber:
rem
rem		SQL> select arid, time, rowid, substr(rowid,15,4) filedata,
rem		  2  substr(rowid,1,8) block, substr(rowid,10,4) rownumber
rem		  3  from arrival;
rem
rem             ARID            TIME ROWID              FILE BLOCK    ROWN
rem         -------- --------------- ------------------ ---- -------- ----
rem           448579   654362719.300 0000764D.0000.0004 0004 0000764D 0000
rem           448581   654362727.500 0000764D.0001.0004 0004 0000764D 0001
rem           448582   654362732.150 0000764D.0002.0004 0004 0000764D 0002
rem                                  -------- ---- ----
rem                                  block    row  file
rem
rem	To find out how much space a table actually uses, count the number of
rem	unique blocks in that table's rowid.  Since a single table could span
rem	multiple database files and the same block number could show up in
rem	both files, count the number of distinct block/file combinations:
rem
rem		select count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))
rem		from <table>;
rem
rem	This query is lifted from the ORACLE 6 DBA Guide, Chapter 6, page 10.
rem	Note this is imprecise; it tells how many blocks are occupied, but
rem	it won't say how much of each block is occupied.  A given block may
rem	have 1 row in it--and yet be counted as a full 2048 byte ORACLE block.
rem
rem	CALCULATING SPACE USED BY INDEXES
rem	---------------------------------
rem	The VALIDATE INDEX command populates the INDEX_STATS table and contains:
rem
rem		BLOCKS:       the blocks allocated to the INDEX
rem		BTREE_SPACE:  total bytes allocated to the btree
rem		USED_SPACE:   total bytes actually used
rem		PCT_USED:     % of the BTREE_SPACE used (USED_SPACE/BTREE_SPACE)
rem
rem	The bytes allocated output by this report is blocks_alloc*2048 instead 
rem	of btree_space.
rem	The % used figure in this report is based on USED_SPACE/BLOCKS.

rem
rem BUGS

rem	This report is exceptionally labor-intensive to change. Make changes at
rem	your own risk. Debug it by setting 'echo', 'termout', and 'verify' on.
rem
rem	The V7 version will be written entirely in PL/SQL, making it straight 
rem	forward to read and debug.

rem
rem ACKNOWLEDGEMENTS

rem	This bounces off a script and paper by Maurice C. Manton III titled
rem     "Space, The Great Unknown", presented at the 1990 International Oracle 
rem     User Group meetings.
rem
rem	Funding for the development of this implementation was provided by
rem	DARPA.

rem
rem AUTHOR
rem Jean T. Anderson, SAIC Open Systems Division, November 1991 rem
rem Modification history:
rem

rem	jta=jean_at_esosun.css.gov (Jean Anderson)
rem	grd=uswnvg!gdecocq_at_uunet.UU.NET (Garth DeCocq)
rem
rem	jta	11/05/91	Created -- works with V6.0.30.3.1.
rem	jta	11/14/91	Made space_temp.sql filename unique.
rem	jta	11/15/91	Made temp table and index name unique.
rem	jta	01/13/92	Expanded temp table to include data from
rem				INDEX_STATS view.
rem	jta	03/23/92	Certified for rdbms V6.0.33.2.2.
rem	jta	08/12/92	Certified for rdbms V7.0.9.3.1, but it sure
rem				ain't the right way to do it for V7!
rem     grd     09/05/92        modified this so that to remove 'dba_' from
rem                             the file names.  ie dba_actsize is now actsize.
rem                             added archiving program actsize_arch to this
rem    				program. 
rem     grd     09/10/92        map bytes_alloc on the space_usage table
rem                             to blocks_alloc*2048 instead of btree_space
rem                             in the section that builds the 'validate
rem                             index/insert into space_usage' statements.
rem     grd     11/24/92        added code to this and to actsize_rpt to deal
rem             		with clusters.
rem     jta     11/23/92        Forces linefeed after 'validate index' command
rem                             with chr(10)--tip from bill_at_datacraft.com.
rem  SEE ALSO
rem	Oracle 6 DBA Guide, Chapter 6
rem	RTSS article 99751.686 "INDEX STATISTICS"

WHENEVER SQLERROR EXIT FAILURE

define	filename = &1
define	user_id = &2
define	report_name = &3

rem	SESSIONID, the user's auditing identifier, is used for creating unique 
rem	names for the intermediate sql script, the results table, and the index.

column	uniq_id	new_value sid noprint
column	spfile	new_value spoolfile noprint
column	ttable	new_value space_temp noprint
column	tindex	new_value space_index noprint
column	tmesg	new_value plmesg noprint

rem	Some Oracle installations have pause set for interactive work
set	pause off

rem	Because of the "noprint", blank lines get output to stdout.
rem	Suppress output of these blank lines with 'termout off'
set	termout off

select	userenv('SESSIONID')	uniq_id
from	dual

/

select	&sid||'space_temp.sql'	spfile, 


	'space_temp'||&sid	ttable,

	'spacendx'||&sid	tindex,


	'plmesg'||&sid		tmesg


from	dual
/
rem	The SQL code generated by this script must contain single quotes.
rem	s_quote is a trick to surround character strings with single quotes in
rem	space_temp.sql.  But s_quote itself has to be separated from the &,
rem	otherwise it will get immediately expanded; hence the '&'||'s_quote.'
rem	syntax in the queries below.
define	s_quote = ''''

set	echo off
set     verify off
set     heading off
set     pagesize 0
set     feedback off

rem	-- Create the temp table.
create	table &space_temp (
	owner		varchar(30),
	table_name	varchar(30),
	segment_name	varchar(30),
	segment_type	varchar(8),
	recnum		number,
	blocks_alloc	number,
	bytes_alloc	number,
	bytes_used	number,
	pct_used	number,
	pctind		number);

create	table &plmesg (seqno number(4), mesgtext varchar(255), lddate date);

spool	&spoolfile

rem	--- First generate SQL to perform TABLE calculations --

select	'insert into &'||'space_temp ',


	'(owner,table_name,segment_name,segment_type,recnum,bytes_used)',

	' select ',

	'&'||'s_quote.' || owner ||'&'||'s_quote, ',

	'&'||'s_quote.' || segment_name ||'&'||'s_quote, ',

	'&'||'s_quote.' || segment_name ||'&'||'s_quote, ',

	'&'||'s_quote.TABLE&'||'s_quote, ',

	'count(*), ',

	'count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))*2048 blocks',


	' from ', owner||'.'||segment_name, ';'


from	sys.dba_segments
where	segment_type = 'TABLE'
and	owner=upper('&user_id');

rem	--- Now generate SQL to perform CLUSTER calculations --
rem     In order for this to work, add a comment to the master
rem     table of the cluster identifying it as 'MASTER'.
rem     In sqlplus do 'comment on table <table_name> is 'MASTER';'.
rem     This is inserted into space_temp with a segment type of 'TABLE'
rem     so it will sort ahead of the indexes in actsize_rpt.sql.
rem
rem     Although this is moderately hokey, dba_segments does not recognize
rem     individual tables in a cluster - it only recognizes the cluster 
rem     and its index.  It does recognize indexes for the individual
rem     tables.  The logic behind the following statement is first, find
rem     the master table in the cluster, then count how many blocks it
rem     occupies in the cluster.  Since detail rows appear in the same
rem     block (although there may be no details), you don't need to 
rem     count them.
rem
rem     When this is reported, The cluster name will appear along with
rem     rowcounts for the master table and the space used by the cluster.
rem     No counts for the detail rows will be given.  Sorry.  The index
rem     will appear below the cluster.  The individual tables in the
rem     cluster will not be identified anywhere in the report.
rem
rem     Indexes for the clustered tables will be reported, but will not
rem     be associated with a table.

rem

select	'insert into &'||'space_temp ',


	'(owner,table_name,segment_name,segment_type,recnum,bytes_used)',

	' select ',

	'&'||'s_quote.' || c.owner ||'&'||'s_quote, ',

	'&'||'s_quote.' || c.segment_name||'&'||'s_quote, ',

	'&'||'s_quote.' || c.segment_name||'&'||'s_quote, ',

	'&'||'s_quote.TABLE&'||'s_quote, ',

	'count(*), ',

	'count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))*2048 blocks',


	' from ', c.owner||'.'||b.table_name, ';'


from	 sys.dba_clu_columns  b, 
	 sys.dba_segments     c, 
	 sys.dba_tab_comments d
where	c.segment_type = 'CLUSTER'
and     d.comments     = 'MASTER'
and	b.owner        = upper('&user_id')
and     b.owner        = c.owner
and     b.owner        = d.owner
and     b.table_name   = d.table_name

/

rem	--- Now generate SQL to validate indexes ---
rem	Later updates to fill in the table_name from sys.dba_indexes are 
rem	remarkably expensive, so join in that information on the insert here.
rem	However, to also fill in extents requires a third join to
rem	sys.dba_segments--and that third join turns out to be costly.  Since
rem	extents doesn't figure in any other calculations, sys.dba_segments 
rem	will be joined in at the time the report is generated.
rem
rem	The chr(10) forces a linefeed after the semicolon--thanks to 
rem	Bill Pribyl (bill_at_datacraft.com) for this tip.

select 'validate index ', owner||'.'||segment_name||';'||chr(10),


	'insert into &'||'space_temp ',

	'(owner,table_name,segment_name,segment_type,blocks_alloc,bytes_alloc,bytes_used,pct_used)',

	'select ',

	'UPPER(&'||'s_quote.&user_id&'||'s_quote), ',

	'd.table_name, i.name, ',

	'&'||'s_quote.INDEX&'||'s_quote, ',

	'i.blocks, i.blocks*2048, i.used_space, i.pct_used ',

	'from sys.dba_indexes d, index_stats i ',

	'where d.index_name=i.name and d.owner=',


	'UPPER(&'||'s_quote.&user_id&'||'s_quote);'


from	sys.dba_segments
where	segment_type = 'INDEX'
and	owner=UPPER('&user_id');

spool off
/

rem -- Run the script --

start &spoolfile
host rm -f &spoolfile

rem	-- Set an index for updating statistics in temp table
rem	-- This will commit pending changes

create unique index &space_index on &space_temp (owner, segment_name) pctfree=0;

rem	-- Update TABLE information
rem	Correlated UPDATES are notoriously slow (see Tech Bulletin #99590.164).
rem	PL/SQL is a much faster way to do these updates, which now include: 
rem	   (1)	TABLE: get blocks_alloc from sys.dba_segments (INDEXES got 
rem		it from the INDEX_STATS table). segments_cursor does this.
rem	   (2)  TABLE: Update bytes_alloc and pct_used (space_cursor).
rem	   (3)  INDEX: Update size of index/size of base data (space_cursor).
rem
set	termout on
DECLARE
        CURSOR  segments_cursor is
	        SELECT  d.owner, d.segment_name, d.segment_type, d.blocks
	        FROM    sys.dba_segments d
	        WHERE   d.owner=UPPER('&user_id')
		  AND	d.segment_type in ('TABLE','CLUSTER');

        CURSOR  space_cursor IS
                SELECT  table_name, segment_type, blocks_alloc, bytes_alloc,
			bytes_used, pct_used, pctind
                FROM    &space_temp
                ORDER   by table_name asc, segment_type desc
                FOR	UPDATE OF bytes_alloc, pct_used, pctind;

	segments_ptr	segments_cursor%ROWTYPE;
	space_ptr	space_cursor%ROWTYPE;

	-- double the dot, otherwise it disappears
        tbytes          &space_temp..bytes_used%TYPE;
  	msg		&plmesg..mesgtext%TYPE;
	badcount	number:= 0;

BEGIN

• Update blocks allocated to tables. Owner is redundant in
  • this query, but is included so the space_temp index is used. OPEN segments_cursor; LOOP FETCH segments_cursor INTO segments_ptr; EXIT WHEN segments_cursor%NOTFOUND;

               	UPDATE  &space_temp
               	SET     blocks_alloc=segments_ptr.blocks
               	WHERE   owner=segments_ptr.owner
	  	AND	segment_name=segments_ptr.segment_name;

        END LOOP;
	CLOSE	segments_cursor;
        COMMIT;

		-- Update bytes_alloc and pct_used for TABLES.
		-- Update pctind for INDEXES.

	OPEN	space_cursor;
	LOOP
		FETCH space_cursor INTO space_ptr;
		EXIT WHEN space_cursor%NOTFOUND;

                IF      space_ptr.segment_type = 'TABLE'
                OR      space_ptr.segment_type = 'CLUSTER'
                THEN
			UPDATE	&space_temp
			SET	bytes_alloc=space_ptr.blocks_alloc*2048,
				pct_used=round(space_ptr.bytes_used/(space_ptr.blocks_alloc*2048)*100)
			WHERE   CURRENT OF space_cursor;

			tbytes := space_ptr.bytes_used;
                ELSE
			IF tbytes > 0
			THEN
	                        UPDATE  &space_temp 
				SET	pctind = round(space_ptr.bytes_used/tbytes*100)
	                        WHERE   CURRENT OF space_cursor;
			END IF;
                END IF;
        END LOOP;
	CLOSE	space_cursor;
        COMMIT;

EXCEPTION
	WHEN ZERO_DIVIDE THEN
		badcount := badcount+1;
		msg := 'Cannot divide by 0';
		insert into &plmesg(seqno, mesgtext, lddate)
		values (badcount, msg, sysdate);
		commit;
	WHEN OTHERS THEN
		badcount := badcount+1;
		msg := substr(SQLERRM, 1, 70);
		insert into &plmesg(seqno, mesgtext, lddate)
		values (badcount, msg, sysdate);
		commit;

END;
/
rem -- Output any errors from PL/SQL block

column	seqno format 999 heading x NOPRINT
column	mesgtext format A58 WORD_WRAPPED
column	lddate format A18
select	seqno, mesgtext, '  ', to_char(lddate,'DD-Mon-YY HH24:mi:ss') lddate
from	&plmesg
where	seqno > 0
order	by 1;

set termout off
rem	-- Output the space report. --

start &report_name &filename &space_temp &user_id

rem -- Archive the space_temp table --

rem start &actsize_arch &space_temp

drop table &space_temp;

drop table &plmesg;

exit SUCCESS

============================< actsize_rpt.sql >==============================
rem  FILE
rem	actsize_rpt.sql

rem
rem DESCRIPTION
rem This outputs a report from the temporary table created by actsize. rem
rem USAGE
rem (called by actsize)
rem
rem start actsize_rpt filename table_name account rem
rem (ie start actsize_rpt actsize space_temp44444 prod) rem
rem AUTHOR
rem Jean Anderson, SAIC Open Systems Operation, November 1991 rem
rem modification history:
rem

rem	grd	 9/5/92	changed name of output report to take the account name
rem  			as the qualifier after the dot.  ie actsize.cwat0.
rem			Added 1 position to each of three columns - blocks 
rem			alloc, bytes alloc, bytes used.  Removed the ','
rem			formatting from the bytes used column to make room for
rem			the above changes.  The report now handles numbers
rem			1gb and above.
rem
rem	grd	10/14/92 dropped a byte from column Ext

rem
define reportname="actsize_rpt.sql "

define	filename = &1
define	tablename = &2

define account = &3

rem	Some Oracle installations have pause set for interactive work
set	pause off

rem	-- Output report. --

set	pagesize 62
rem	newpage forces a formfeed
set	newpage 0
set	linesize 79
set	heading on
set	feedback off

column	segment_name	format A21 HEADING "Segment Name";
column	recnum		format 9,999,990 HEADING "Rows"
column	extents		format 90 HEADING "Xt";

rem	-- A table with 999,999 blocks would be 2 gigabytes, but 99999 blocks
rem	-- would only allow for 200 meg, so go ahead and give it the extra.
column	blocks_alloc	format 9999990 HEADING "Blocks|Alloc";
column	bytes_alloc	format 9999999990 HEADING "Bytes|Alloc";
column	bytes_used	format 9999999990 HEADING "Bytes|Used";
column	pct_used	format 990 HEADING "Pct|Used";
column	pctind		format 990 HEADING "% of|Data"

rem	-- break_column is used to suppress the output of ***** and 'sum'
column	break_column	noprint
column	today new_value rpt_date noprint
select	to_char(sysdate, 'dd-Mon-yy hh24:mi') today from dual;

rem	-- this for sorting

column sort_column noprint

break on table_name on break_column skip 1 on report; compute sum of blocks_alloc on break_column; compute sum of blocks_alloc on report;

compute sum of bytes_alloc on break_column;
compute sum of bytes_alloc on report;
compute sum of bytes_used on break_column;
compute sum of bytes_used on report;

compute sum of pctind on break_column;

ttitle skip center "Space Utilization Report for User '&account' on &rpt_date" -

        skip 2
/

btitle	skip left '&reportname' -
	right 'Page' FORMAT 99 sql.pno

/

rem	The decode statement below forces index names to be indented two
rem	characters below the parent table name.

select	b.table_name	break_column,
	b.segment_type	sort_column,
	decode(b.segment_type,
		'TABLE',
		rpad(substr(b.segment_name,1,15),16)||'DATA',
		'CLUSTER',
		rpad(substr(b.segment_name,1,15),16)||'DATA',
		'INDEX',
		'  '||rpad(substr(b.segment_name,1,13),14)||'INDEX') 
		segment_name,
	b.recnum,
	a.extents,
	b.blocks_alloc,
	b.bytes_alloc,
	b.bytes_used,
	b.pct_used,
	b.pctind
from	sys.dba_segments a, &tablename b
where	a.segment_name=b.segment_name 
  and	a.owner=b.owner
order	by b.table_name asc, b.segment_type desc, b.segment_name asc

spool	&filename..&account

/
spool off

============================< actsize_arch.sql >==============================
rem FILE
rem     actsize_arch.sql

rem
rem DESCRIPTION
rem archive information for trend analysis. rem
rem AUTHOR
rem Garth DeCocq, uswnvg!gdecocq_at_uunet.UU.NET rem
define tablename = &1
insert into space_usage (

	owner,
	table_name,
	segment_name	,
	segment_type,
	recnum	,
	blocks_alloc,
	bytes_alloc,
	bytes_used,
	pct_used,
	pctind,
	extract_dt)
select 
	owner,
	table_name,
	segment_name	,
	segment_type,
	recnum	,
	blocks_alloc,
	bytes_alloc,
	bytes_used,
	pct_used,
	pctind,
	trunc(sysdate)
	from  &tablename       

/

==================================< the end >=================================

Received on Mon Jan 18 1993 - 02:08:21 CET