How do I calculate tables size in Oracle

Question

Hello,

Being used to (and potentially spoiled by) MSSQL, I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that I may not have as easy an option as sp_spaceused. Still the potential answers I got are most of the time outdated or don't work. Probably because I'm no DBA on the schema I'm working with.

Would anyone have solutions and or recommendations?

Thanks, Rollo

Answer 1

IIRC the tables you need are DBA_TABLES, DBA_EXTENTS or DBA_SEGMENTS and DBA_DATA_FILES. There are also USER_ and ALL_ versions of these for tables you can see if you don't have administration permissions on the machine.

Answer 2

First, gather optimiser stats on the table (if you haven't already):

begin
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/

WARNING: As Justin says in his answer, gathering optimiser stats affects query optimisation and should not be done without due care and consideration!

Then find the number of blocks occupied by the table from the generated stats:

select blocks, empty_blocks, num_freelist_blocks
from   all_tables
where  owner = 'MYSCHEMA'
and    table_name = 'MYTABLE';

• The total number of blocks allocated to the table is blocks + empty_blocks + num_freelist_blocks.

• blocks is the number of blocks that actually contain data.

Multiply the number of blocks by the block size in use (usually 8KB) to get the space consumed - e.g. 17 blocks x 8KB = 136KB.

To do this for all tables in a schema at once:

begin
    dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/

select table_name, blocks, empty_blocks, num_freelist_blocks
from   user_tables;

Note: Changes made to the above after reading this AskTom thread

Answer 3

Thanks Nigel,

I've had a quick look and came up with the following. Is it correct?

select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) SIZE_KB from USER_TABLES order by TABLE_NAME

Thanks, Rollo

Answer 4

Thanks Tony.

So if I combine your answer with Nigel's, here's what I come up with

Refresh stats using

begin  
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');  
end;

Run the following query which gives the row and block sizes

select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) ROW_SIZE_KB, (BLOCKS * 8)  BLOCK_SIZE_KB  
from USER_TABLES  
order by TABLE_NAME

Is that correct?

Thanks, Rollo

Answer 5

First off, I would generally caution that gathering table statistics in order to do space analysis is a potentially dangerous thing to do. Gathering statistics may change query plans, particularly if the DBA has configured a statistics gathering job that uses non-default parameters that your call is not using, and will cause Oracle to re-parse queries that utilize the table in question which can be a performance hit. If the DBA has intentionally left some tables without statistics (common if your OPTIMIZER_MODE is CHOOSE), gathering statistics can cause Oracle to stop using the rule-based optimizer and start using the cost-based optimizer for a set of queries which can be a major performance headache if it is done unexpectedly in production. If your statistics are accurate, you can query USER_TABLES (or ALL_TABLES or DBA_TABLES) directly without calling GATHER_TABLE_STATS. If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo.

Second, the closest equivalent to the SQL Server sp_spaceused procedure is likely Oracle's DBMS_SPACE package. Tom Kyte has a nice show_space procedure that provides a simple interface to this package and prints out information similar to what sp_spaceused prints out.

Answer 6

Depends what you mean by "table's size". A table doesn't relate to a specific file on the file system. A table will reside on a tablespace (possibly multiple tablespaces if it is partitioned, and possibly multiple tablespaces if you also want to take into account indexes on the table). A tablespace will often have multiple tables in it, and may be spread across multiple files.

If you are estimating how much space you'll need for the table's future growth, then avg_row_len multiplied by the number of rows in the table (or number of rows you expect in the table) will be a good guide. But Oracle will leave some space free on each block, partly to allow for rows to 'grow' if they are updated, partly because it may not be possible to fit another entire row on that block (eg an 8K block would only fit 2 rows of 3K, though that would be an extreme example as 3K is a lot bigger than most row sizes). So BLOCKS (in USER_TABLES) might be a better guide.

But if you had 200,000 rows in a table, deleted half of them, then the table would still 'own' the same number of blocks. It doesn't release them up for other tables to use. Also, blocks are not added to a table individually, but in groups called an 'extent'. So there are generally going to be EMPTY_BLOCKS (also in USER_TABLES) in a table.

Answer 7

You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know "How much spaces the the Purchase Order table take, including any indexes" rather than just the table itself. You can always delve into the details. Note that this requires access to the DBA_* views.

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

Answer 8

Simple select that returns the raw sizes of the tables, based on the block size, also includes size with index

select table_name,(nvl (( select sum( blocks) from dba_indexes a,dba_segments b where a.index_name=b.segment_name and a.table_name=dba_tables.table_name ),0)+blocks)8192/1024 TotalSize,blocks8 tableSize from dba_tables order by 3