How do I calculate tables size in Oracle

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,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 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 IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 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 IN ('LOBSEGMENT', 'LOB PARTITION')
 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
;

-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;


--Tables + Rows
select owner, table_name, num_rows
 from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;

Note: These are estimates, made more accurate with gather statistics:

exec dbms_utility.analyze_schema(user,'COMPUTE');

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.

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

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.

I modified the query to get the schema size per tablespace ..

SELECT owner,
     tablespace_name,
     TRUNC (SUM (bytes) / 1024 / 1024)   Meg,
     ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent
FROM (SELECT tablespace_name, owner, bytes
        FROM dba_segments
       WHERE segment_type IN
                 ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
      UNION ALL
      SELECT i.tablespace_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 IN
                     ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
      UNION ALL
      SELECT l.tablespace_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 IN ('LOBSEGMENT', 'LOB PARTITION')
      UNION ALL
      SELECT l.tablespace_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 owner, tablespace_name
--HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY tablespace_name -- desc
;

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.

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,blocks*8 tableSize from dba_tables order by 3

there one more option that allows to get "select" size with joins, and table size as option too

-- 1
EXPLAIN PLAN
   FOR
      SELECT
            Scheme.Table_name.table_column1 AS "column1",
            Scheme.Table_name.table_column2 AS "column2",
            Scheme.Table_name.table_column3 AS "column3",
            FROM Scheme.Table_name
       WHERE ;

SELECT * FROM TABLE (DBMS_XPLAN.display);