Re: table size in bytes

From: Murray Kaiser <ae873_at_cfn.cs.dal.ca>
Date: 1995/03/30
Message-ID: <D68EyH.5LM_at_cs.dal.ca>#1/1


Charles Thompson (thompsoc_at_groupwest.ca) wrote:
: There are a few ways. I've used a query like the following :
:
: select blocks * 512 /* assuming 512-byte block size ... see init.ora */
: from dba_tables
: where table_name = 'YOUR_TABLE';

But this would tell you the size of the allocated blocks not how big the actual table was! Remember, he wanted to be able to predict how close the table was to grabbing another extent.

How about

SELECT COUNT ( DISTINCT ( SUBSTR ( ROWID,1,8 ))) FROM YOUR_TABLE; This would give you the count of all blocks that were actually used by the table rather than the allocated blocks. You could then compare that to allocated blocks via

SELECT BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'YOUR_TABLE'; The actual blocks / allocated blocks would give you a percentage full

:
: You could also use the num_rows column in dba_tables and multiply the size
: in bytes per row.
:

But you would have to ANALYZE COMPUTE STATISTICS on the table first wouldn't you?

--
Murray Kaiser                  | Usual gutless disclaimer.. 
Nova Scotia Power              | Opinions are mine and not necessarily
(Murray.Kaiser_at_NSPower.NS.CA)  | shared by my employer
Received on Thu Mar 30 1995 - 00:00:00 CEST

Original text of this message