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

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 17 Jul 2001 09:05:15 +0200
Message-ID: <9j0o3c$5oh$1@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.

--
Hope this helps
Michel

Received on Tue Jul 17 2001 - 02:05:15 CDT