FAQ--Calculating Actual Space Used

From: Jean Anderson <jean_at_seismo.CSS.GOV>
Date: 19 Jul 92 22:11:59 GMT
Message-ID: <50975_at_seismo.CSS.GOV>


This FAQ consolidates several postings on how to calculate actual space used by tables and indexes. I have tried to reduce verbiage to lower the byte count.

The topics are:

  1. V6: How to determine actual bytes used by a table
  2. V6: actual_size.sql, SQL*Plus script that reports space used by a table
  3. V7: How to determine actual bytes used by a table
  4. V6: How to determine actual bytes used by an index
  5. V6: ind_stats.sql, SQL*Plus script that reports space used by an index
  6. V7: How to determine actual bytes used by an index

Each topic begins with the number flush against the left margin to make it easy to search down with an editor to a given topic.

I have included some sketchy details on V7 because space management changes for the better. So don't invest lots of effort in V6 space management routines if you anticipate upgrading to V7 as soon as the production release becomes available.

This posting is not the production of ORACLE Corp*. All errors, opinions, etc. are mine and I admit to having a Unix/rdbms/DBA bias. Feel free to email me corrections, additions or editorial comments and I'll do my best to incorporate them into a repost.

  • jean
 Jean Anderson, SAIC Open Systems Division      phone:  (619)458-2727
 10210 Campus Point Drive, MS A2-F              fax:    (619)458-4993
 San Diego, CA 92121                            email:   jean_at_esosun.css.gov
* Any opinions are mine, not my employer's.              jean_at_seismo.css.gov
==============================================================================


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  1. V6: How to determine actual bytes used by a table
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

From: jean_at_beno.CSS.GOV (Jean Anderson) Newsgroups: comp.databases
Subject: Re: Oracle Free Space or Space used - DBA Message-ID: <49911_at_seismo.CSS.GOV>
Date: 15 Aug 91 03:11:37 GMT

[... lots deleted ...]

ORACLE tags each record with a unique rowid that contains the datafile, the block number, and the rownumber. To find out how much space a table actually uses, count the number of unique blocks in that table's rowid. Since a single table could span multiple database files and the same block number could show up in both files, count the number of distinct block/file combinations (query is lifted from the ORACLE 6 DBA Guide, Chapter 6, page 10):

        select count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))
        from <table>;

Mind you, it doesn't tell you "how occupied" that block is so partially filled blocks will get counted the same as 100% filled blocks. But it gets me within 5% of reality, which is close enough for what I need.

I am including a script below that was distributed at the last IOUG. Maurice verbally told me at that time it could be freely distributed, so I assume it's ok to post it here. If not, my apologies in advance.

[ IOUG script moved down to Topic #2, rest deleted ... ]

>>>>>>>>>>>>>>>>>>>>>>

 2. V6: actual_size.sql
>>>>>>>>>>>>>>>>>>>>>>

REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM     %%%                                                             %%%
REM     %%%                     ACTUAL_SIZE.SQL                         %%%
REM     %%%                                                             %%%
REM     %%%             This SQL script uses the following SYS view     %%%
REM     %%%     USER_SEGMENTS. It also uses a table named               %%%
REM     %%%     TEMP_SIZE_TABLE which contains the table name and       %%%
REM     %%%     the number of blocks in use.  These views and table are %%%
REM     %%%     used to compare the number of blocks allocated to a     %%%
REM     %%%     table to the actual number of blocks used by the same   %%%
REM     %%%     table's data.                                           %%%
REM     %%%             This script creates a second SQL script which   %%%
REM     %%%     is made up of inserts.  These inserts select the        %%%
REM     %%%     tablename and block-in-use count to be used by the      %%%
REM     %%%     actual report select statement. This secondary file is  %%%
REM     %%%     named FILL_SIZE_TABLE.SQL and can be discarded as soon  %%%
REM     %%%     as the script has finished running.                     %%%
REM     %%%                                                             %%%
REM     %%%             This script is designed to be run in an account %%%
REM     %%%     with the RESOURCE privilege.                            %%%
REM     %%%                                                             %%%
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM     %%%                                                             %%%
REM     %%%     Oracle Version 6        IOUG Sept 1990                  %%%
REM     %%%             Author: Maurice C. Manton III                   %%%
REM     %%%                                                             %%%
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM
        SET TERM OFF;
        TTITLE OFF;
        BTITLE  OFF;
        SET PAGESIZE 0;
        SET VERIFY OFF;
        SET HEADING OFF;
        SET RECSEP OFF;
REM
        CREATE TABLE temp_size_table(
                table_name      CHAR(30),
                blocks          NUMBER);
REM
        SPOOL fill_size_table.sql
REM
REM     Note:
REM     Script corrected by Steve Lindeman in <1254_at_usdsd1.DaytonOH.NCR.COM>
REM     (slindemn_at_usdsd1.DaytonOH.NCR.COM) to report accurate results for
REM	tablespaces containing multiple datafiles.
REM	
        SELECT 'INSERT INTO temp_size_table',
                ' SELECT ','&'||'temp_var.' || segment_name ||'&'||'temp_var',
                ', COUNT( DISTINCT( SUBSTR( ROWID,1,8) || SUBSTR( ROWID,15,4))) blocks',
                ' FROM ', segment_name, ';'
        FROM user_segments
                WHERE segment_type = 'TABLE';
REM
        SPOOL OFF;
REM
        DEFINE temp_var = '''';
        START fill_size_table;

HOST rm fill_size_table.sql;
REM
        compute sum of blocks on report;
        compute sum of act_blocks on report;
        break on report;
REM     
        SET TERM ON;
        TTITLE ON;
        BTITLE  OFF;
REM
        SET VERIFY ON;
        SET HEADING ON;
        SET LINESIZE 80;
        SET PAGESIZE 60;
        SET NEWPAGE 0;
REM
        COLUMN t_date NOPRINT new_value t_date;
        COLUMN user_id NOPRINT new_value user_id;
REM
        SELECT sysdate t_date, user user_id
        FROM dual;
REM
        COLUMN segment_name FORMAT A20 HEADING "SEGMENT|NAME";
        COLUMN segment_type FORMAT A7 HEADING "SEGMENT|TYPE";
        COLUMN extents FORMAT 999 HEADING "EXTENTS";
        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
        SPOOL &user_id._actual_to_allocated_space
REM
        TTITLE LEFT t_date RIGHT 'PAGE: ' FORMAT 999 SQL.PNO SKIP 1 -
        CEN "&user_id ACTUAL VS ALLOCATED STORAGE REPORT" SKIP 2;
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.user_segments a, temp_size_table b
                WHERE segment_name = UPPER( b.table_name );
REM
        SPOOL OFF;
REM
REM     DROP TABLE temp_size_table;
REM
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
REM     %%%             END OF SCRIPT.                                  %%%
REM     %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
exit

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 3. V7: How to determine actual bytes used by a table
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

The ANALYZE TABLE command populates USER_TABLES with statistics about the table, including number of rows and number of bytes used. You have the option of calculating exact statistics or estimates.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 4. V6: How to determine actual bytes used by an index
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

The VALIDATE INDEX command populates the INDEX_STATS view with information about the index, including bytes allocated and used.

>>>>>>>>>>>>>>>>>>>

 5. 6: ind_stats.sql
>>>>>>>>>>>>>>>>>>>

Date: Tue, 20 Aug 91 14:08:19 EDT
From: tjr25%CAS.BITNET_at_CUNYVM.CUNY.EDU (Terri Roden x2410 4438B) Subject: Space used in indexes
To: jean_at_beno.CSS.GOV

Jean,

I saw your note on estimating space used within tables. That's the best way we've come up with estimating it also. Here is how we do indexes. I never found it documented very well but the "validate index" command can be very useful. Try describing the index_stats table and look at the comments on each column. Here's the script I use that I've found pretty reliable...I'd say your within 5% probably holds here too!

rem ind_stats.sql
rem
rem v1/l1 4/4/91 T. J. Roden
rem
rem Use the VALIDATE INDEX command to report on index usage and space. rem You can only run the VALIDATE command if you either own the index rem or are a DBA. VALIDATE creates a dynamic x$ table that holds the rem results of only the last validate command. Each person/session has rem it's onn x$ table.
rem
rem Creates a file in current directory, index_name.lst rem

prompt
PROMPT ~ Usage: _at_ind_stats index_name PROMPT define I = &1

rem Use next line if you want to be prompted rather than command line input rem accept I char prompt 'Enter index name:'

set verify off
column "% USED" format 99.99

rem spool &I

validate index &I;

select name "INDEX_NAME",
blocks * 2048 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space/(blocks * 2048)) * 100 "% USED" FROM INDEX_STATS; rem spool off

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 6. V7: How to determine actual bytes used by an index
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

The ANALYZE INDEX command populates USER_INDEXES with the information previously stored in INDEX_STATS, except for max/min values which are stored in USER_TAB_COLUMNS. Received on Mon Jul 20 1992 - 00:11:59 CEST

Original text of this message