Re: Is there a way to get byte counts of actual data stored in a table?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 17 Jul 2001 12:14:12 -0700
Message-ID: <178d2795.0107171114.6927ac15@posting.google.com>

"Michel Cadot" <micadot_at_netcourrier.com> wrote in message news:<9j0o3c$5oh$1_at_s1.read.news.oleane.net>...
> "g vose" <g_vose_at_yahoo.com> a écrit dans le message news:
> 985a9e76.0107161612.466fbf10_at_posting.google.com...
> > I am looking for a way to see the actual size of a table in bytes
> > used.
> > I can see the size being used for the DataBase and I can see the
> > allocated space for the table but I can not tell the actual space
> > being used by a table.
> > Does anyone have any suggestions?
>
> Have a look at dbms_space.unused_space procedure:
>
> procedure unused_space(segment_owner IN varchar2,
> segment_name IN varchar2,
> segment_type IN varchar2,
> total_blocks OUT number,
> total_bytes OUT number,
> unused_blocks OUT number,
> unused_bytes OUT number,
> last_used_extent_file_id OUT number,
> last_used_extent_block_id OUT number,
> last_used_block OUT number,
> partition_name IN varchar2 DEFAULT NULL
> );
> pragma restrict_references(unused_space,WNDS);
>
> -- Returns information about unused space in an object (table, index,
> -- or cluster).
> -- Input arguments:
> -- segment_owner
> -- schema name of the segment to be analyzed
> -- segment_name
> -- object name of the segment to be analyzed
> -- partition_name
> -- partition name of the segment to be analyzed
> -- segment_type
> -- type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
> -- Output arguments:
> -- total_blocks
> -- total number of blocks in the segment
> -- total_bytes
> -- the same as above, expressed in bytes
> -- unused_blocks
> -- number of blocks which are not used
> -- unused_bytes
> -- the same as above, expressed in bytes
> -- last_used_extent_file_id
> -- the file ID of the last extent which contains data
> -- last_used_extent_block_id
> -- the block ID of the last extent which contains data
> -- last_used_block
> -- the last block within this extent which contains data
>
> total_bytes - unused_bytes gives you the number of bytes used
> in the table.

Another way to get this information is to look at the dictionary view sys.dba_segments for the total bytes allocated to the table. The actual bytes used can be calculated as the dba_table column blocks X oracle block size. You have to analzye the table first to update the HWM but based on the following this should work:

UT1> @dbmsspace

Enter table/index owner                > jit 
Enter table/index name                 > item_master
Enter table or index                   > table

Enter free list group to analyze 0 - n > 0

'----------------------------------------'
Total Blocks            ==> 210
Unused Blocks           ==> 35
Percent Obj Space Free  ==> 16.7%
Total Bytes             ==> 860160
Unused Bytes            ==> 143360
Last Used Ext. File ID  ==> 9
Last Used Ext. Block ID ==> 762
Last Used Block         ==> 5
Blocks free             ==> 7

PL/SQL procedure successfully completed.

UT1> select 860160 - 143360 from dual;

860160-143360

---

       716800

  1* select blocks * 4096 from dba_tables where table_name = 'ITEM_MASTER'
UT1> /

BLOCKS*4096

---

     712704

My figures are off by 1 block, probably the table header block.

UT1> l
  1 select bytes, blocks, blocks * 4096   2 from sys.dba_segments
  3* where segment_name = 'ITEM_MASTER'
UT1> /

     BYTES BLOCKS BLOCKS*4096
---------- ---------- -----------

    860160 210 860160

I hope this is of some use to you.

• Mark D Powell --

Received on Tue Jul 17 2001 - 14:14:12 CDT