Oracle: how to find out storage space used by a table?

Via OEM 10g,

1. Connect to the database as usual
2. Click on the Schema tab
3. On the Tables link within Database Objects
4. Enter the schema name and object name (optional) and click Go
5. Use the radio button to select the table you want to look at and click on Edit (Don't click on the table name link)
6. Click on the Segments tab (and wait...)
7. You will see the size of the table data and the indexes used.

OK, that technically answered your question. But a better way is:

1. Logon using SQLPLUS
2. Run the script from Vincent.

I like to save the script as t.sql as a quick reference

COLUMN size_mb      FORMAT '999,999,990.0'
COLUMN num_rows     FORMAT '999,999,990'
COLUMN fmt_short    FORMAT A24


COLUMN owner        FORMAT A16
COLUMN table_name   LIKE fmt_short
COLUMN tablespace_name  LIKE fmt_short

SET LINESIZE 200
SET AUTOTRACE OFF

COMPUTE SUM OF size_mb ON REPORT
BREAK ON REPORT

SELECT 
    lower( owner )      AS owner
    ,lower(table_name)  AS table_name
    ,tablespace_name
    ,num_rows
    ,blocks*8/1024      AS size_mb
    ,pct_free
    ,compression 
    ,logging
FROM    all_tables 
WHERE   owner           LIKE UPPER('&1')
OR  owner           = USER
ORDER BY 1,2;

CLEAR COMPUTES
CLEAR BREAKS

The space used by a table is the space used by all its extents:

SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
    FROM dba_extents
    WHERE owner = :owner
    AND segment_name = :table_name;

SUM(BYTES)         MB
---------- ----------
3066429440   2924,375

Are you sure that the original approach using the all_tables view includes LOB extents? I would think not. Here is one that I am finding useful:

with da as (
 SELECT owner, segment_name, SUM(bytes)/1024/1024 size_mb
   FROM dba_extents
   group by rollup(owner, segment_name)
) select owner, segment_name, size_mb, round(size_mb/total_mb*100)
  from da 
    cross join (
      select size_mb as total_mb 
      from da t where owner is null and segment_name is null
    )
order by size_mb desc

It shows me what's using the most space.

After some google searches and experiments, I've built the following query, which provides to my mind the most accurate result. For an oracle user, it provides the free/used/total space by Table and segment type (TABLE *, INDEX *, LOB *). You can easily extend it in order to provide additional statistics like block counts.

Typical Output is:

table    ; segment type  ; used (mb) ; unused (mb) ;  total (mb)
user     ; INDEX         ;       0,78;         0,00;         0,78
user     ; LOBINDEX      ;       0,15;         0,00;         0,15
user     ; LOBSEGMENT    ;       3,48;         1,19;         4,67
user     ; TABLE         ;      12,11;         2,74;        14,85
address  ; INDEX         ;       0,12;         0,00;         0,12
(...)

Script:

DECLARE
  input_owner         NVARCHAR2(128) := 'MY_ORACLE_OWNER';
  segment_size_blocks NUMBER;
  segment_size_bytes  NUMBER;
  used_blocks         NUMBER;
  used_bytes          NUMBER;
  expired_blocks      NUMBER;
  expired_bytes       NUMBER;
  unexpired_blocks    NUMBER;
  unexpired_bytes     NUMBER;
  total_blocks        NUMBER;
  total_bytes         NUMBER;
  unused_blocks       NUMBER;
  unused_bytes        NUMBER;
  last_ext_file_id    NUMBER;
  last_ext_blk_id     NUMBER;
  last_used_blk       NUMBER;
  result_table        NVARCHAR2(128);
  result_segment_type NVARCHAR2(128);
  result_used_mb      NUMBER;
  result_unused_mb    NUMBER;
  result_total_mb     NUMBER;
  CURSOR cur
  IS
    SELECT
      s.segment_name   AS segment_name,
      s.owner          AS segment_owner,
      s.partition_name AS partition_name,
      s.segment_type   AS segment_type,
      CASE WHEN s.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
        THEN s.segment_name
      WHEN s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
        THEN (SELECT i.table_name
              FROM dba_indexes i
              WHERE s.segment_name = i.index_name AND s.owner = i.owner)
      WHEN s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
        THEN (SELECT l.table_name
              FROM dba_lobs l
              WHERE s.segment_name = l.segment_name AND s.owner = l.owner)
      WHEN s.segment_type IN ('LOBINDEX')
        THEN (SELECT l.table_name
              FROM dba_lobs l
              WHERE s.segment_name = l.index_name AND s.owner = l.owner)
      ELSE 'Unknown'
      END              AS table_name,
      s.bytes          AS segment_bytes
    FROM dba_segments s
    WHERE owner = input_owner
    ORDER BY table_name, segment_type;
BEGIN
  dbms_output.put_line('table                         ; segment type        ;   used (mb)     ; unused (mb)     ;  total (mb)');

  FOR ro IN cur
  LOOP

    result_table := ro.table_name;
    result_segment_type := ro.segment_type;

    IF ro.segment_type IN ('TABLE', 'INDEX')
    THEN
      dbms_space.unused_space(
          segment_owner             => ro.segment_owner,
          segment_name              => ro.segment_name,
          segment_type              => ro.segment_type,
          total_blocks              => total_blocks,
          total_bytes               => total_bytes,
          unused_blocks             => unused_blocks,
          unused_bytes              => unused_bytes,
          last_used_extent_file_id  => last_ext_file_id,
          last_used_extent_block_id => last_ext_blk_id,
          last_used_block           => last_used_blk);

      result_used_mb := (total_bytes - unused_bytes) / 1024 / 1024;
      result_unused_mb := unused_bytes / 1024 / 1024;
      result_total_mb := total_bytes / 1024 / 1024;

    ELSIF ro.segment_type IN ('LOBSEGMENT')
    THEN
      dbms_space.space_usage(
          segment_owner           => ro.segment_owner,
          segment_name            => ro.segment_name,
          segment_type            => 'LOB',
          partition_name          => ro.partition_name,
          segment_size_blocks     => segment_size_blocks,
          segment_size_bytes      => segment_size_bytes,
          used_blocks             => used_blocks,
          used_bytes              => used_bytes,
          expired_blocks          => expired_blocks,
          expired_bytes           => expired_bytes,
          unexpired_blocks        => unexpired_blocks,
          unexpired_bytes         => unexpired_bytes
      );
      result_used_mb := used_bytes / 1024 / 1024;
      result_unused_mb := (segment_size_bytes - used_bytes) / 1024 / 1024;
      result_total_mb := segment_size_bytes / 1024 / 1024;
    ELSE
      -- TODO ??
      result_used_mb := ro.segment_bytes / 1024 / 1024;
      result_unused_mb := 0;
      result_total_mb := result_used_mb + result_unused_mb;
    END IF;

    dbms_output.put_line(
        RPAD(result_table, 30) || '; ' ||
        RPAD(result_segment_type, 20)|| '; ' ||
        TO_CHAR(result_used_mb  / 1024 / 1024, '999999999990D00')|| '; ' ||
        TO_CHAR(result_unused_mb  / 1024 / 1024, '999999999990D00')|| '; ' ||
        TO_CHAR(result_total_mb / 1024 / 1024, '999999999990D00'));

  END LOOP;
END;