How do I calculate tables size in Oracle

Question

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

User · Answer

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

User · Answer

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

User · Answer

select segment name segment type bytes 1024 1024 MB from dba segments where segment name  TABLENAME  and owner   OWNERNAME  order by mb desc

User · Answer

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

User · Answer

Correction for partitioned tables     SELECT owner  table name  ROUND sum bytes  1024 1024 1024  2  GB 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    LOB SUBPARTITION    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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    order by sum bytes  desc

User · Answer

I have the same variant as the last ones which calculates segments of table data  table indexes and blob-fields   CREATE OR REPLACE FUNCTION   SYS RAZMER TABLICY RAW pNazvanie in varchar  pOwner in varchar2  return number is   val number 16     sz number 16   begin   sz    0     --Calculate size of table data segments   select     sum t bytes  into val   from     sys dba segments t   where     t segment name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      --Calculate size of table indexes segments   select     sum s bytes  into val   from     all indexes t   inner join     dba segments s   on     t index name   s segment name   where     t table name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      --Calculate size of table blob segments   select     sum s bytes  into val   from     all lobs t   inner join     dba segments s on t segment name   s segment name   where     t table name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      return sz   end razmer tablicy raw    Source

User · Answer

select segment name as tablename  sum bytes   1024   1024   1024   as tablesize in GB From dba segments    if looking at tables not owned by you else use user segments    where segment name    TABLE WHOSE SIZE I WANT TO KNOW  and   OWNER    WHO OWNS THAT TABLE     if user segments is used delete this line     group by segment name

User · Answer

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    amp owner   GROUP BY owner  tablespace name --HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY tablespace name -- desc

User · Answer

I modified the WW s query to provide more detailed information   SELECT   FROM     SELECT     owner  object name  object type  table name  ROUND bytes  1024 1024 AS meg      tablespace name  extents  initial extent      ROUND Sum bytes 1024 1024  OVER  PARTITION BY table name   AS total table meg   FROM       -- Tables     SELECT owner  segment name AS object name   TABLE  AS object type            segment name AS table name  bytes            tablespace name  extents  initial extent     FROM   dba segments     WHERE  segment type IN   TABLE    TABLE PARTITION    TABLE SUBPARTITION       UNION ALL     -- Indexes     SELECT i owner  i index name AS object name   INDEX  AS object type            i table name  s bytes            s tablespace name  s extents  s initial extent     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       -- LOB Segments     UNION ALL     SELECT l owner  l column name AS object name   LOB COLUMN  AS object type            l table name  s bytes            s tablespace name  s extents  s initial extent     FROM   dba lobs l  dba segments s     WHERE  s segment name   l segment name     AND    s owner   l owner     AND    s segment type    LOBSEGMENT      -- LOB Indexes     UNION ALL     SELECT l owner  l column name AS object name   LOB INDEX  AS object type            l table name  s bytes            s tablespace name  s extents  s initial extent     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   UPPER   amp owner     WHERE total table meg  gt  10 ORDER BY total table meg DESC  meg DESC

User · Answer

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

User · Answer

select segment name segment type bytes 1024 1024 MB from dba segments where segment name  TABLENAME  and owner   OWNERNAME  order by mb desc

User · Answer

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

User · Answer

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

User · Answer

I modified the WW s query to provide more detailed information   SELECT   FROM     SELECT     owner  object name  object type  table name  ROUND bytes  1024 1024 AS meg      tablespace name  extents  initial extent      ROUND Sum bytes 1024 1024  OVER  PARTITION BY table name   AS total table meg   FROM       -- Tables     SELECT owner  segment name AS object name   TABLE  AS object type            segment name AS table name  bytes            tablespace name  extents  initial extent     FROM   dba segments     WHERE  segment type IN   TABLE    TABLE PARTITION    TABLE SUBPARTITION       UNION ALL     -- Indexes     SELECT i owner  i index name AS object name   INDEX  AS object type            i table name  s bytes            s tablespace name  s extents  s initial extent     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       -- LOB Segments     UNION ALL     SELECT l owner  l column name AS object name   LOB COLUMN  AS object type            l table name  s bytes            s tablespace name  s extents  s initial extent     FROM   dba lobs l  dba segments s     WHERE  s segment name   l segment name     AND    s owner   l owner     AND    s segment type    LOBSEGMENT      -- LOB Indexes     UNION ALL     SELECT l owner  l column name AS object name   LOB INDEX  AS object type            l table name  s bytes            s tablespace name  s extents  s initial extent     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   UPPER   amp owner     WHERE total table meg  gt  10 ORDER BY total table meg DESC  meg DESC

User · Answer

I have the same variant as the last ones which calculates segments of table data  table indexes and blob-fields   CREATE OR REPLACE FUNCTION   SYS RAZMER TABLICY RAW pNazvanie in varchar  pOwner in varchar2  return number is   val number 16     sz number 16   begin   sz    0     --Calculate size of table data segments   select     sum t bytes  into val   from     sys dba segments t   where     t segment name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      --Calculate size of table indexes segments   select     sum s bytes  into val   from     all indexes t   inner join     dba segments s   on     t index name   s segment name   where     t table name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      --Calculate size of table blob segments   select     sum s bytes  into val   from     all lobs t   inner join     dba segments s on t segment name   s segment name   where     t table name   upper pNazvanie    and     t owner   upper pOwner     sz    sz   nvl val 0      return sz   end razmer tablicy raw    Source

User · Answer

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

User · Answer

For sub partitioned tables and indexes we can use the following query        SELECT owner  table name  ROUND sum bytes  1024 1024 1024  2  GB     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    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

User · Answer

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

User · Answer

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

User · Answer

Correction for partitioned tables     SELECT owner  table name  ROUND sum bytes  1024 1024 1024  2  GB 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    LOB SUBPARTITION    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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    order by sum bytes  desc

User · Answer

I found this to be a little more accurate   SELECT    owner  table name  TRUNC sum bytes  1024 1024 1024  GB FROM  SELECT segment name table name  owner  bytes FROM dba segments WHERE segment type in    TABLE   TABLE PARTITION   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   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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

-- 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  gt  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  gt  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

User · Answer

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

User · Answer

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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

-- 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  gt  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  gt  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

User · Answer

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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

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

User · Answer

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

User · Answer

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    amp owner   GROUP BY owner  tablespace name --HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY tablespace name -- desc

User · Answer

Heres a variant on WWs answer  it includes partitions and sub-partitions as others above have suggested  plus a column to show the TYPE  Table Index LOB etc  SELECT    owner   Type   table name  Name   TRUNC sum bytes  1024 1024  Meg FROM    SELECT segment name table name  owner  bytes   Table  as  Type     FROM dba segments    WHERE segment type in    TABLE   TABLE PARTITION   TABLE SUBPARTITION    UNION ALL    SELECT i table name  i owner  s bytes   Index  as  Type     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   LOB  as  Type     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   LOB SUBPARTITION    UNION ALL    SELECT l table name  l owner  s bytes   LOB Index  as  Type     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   amp owner   GROUP BY table name  owner   Type  HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

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

User · Answer

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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

Heres a variant on WWs answer  it includes partitions and sub-partitions as others above have suggested  plus a column to show the TYPE  Table Index LOB etc  SELECT    owner   Type   table name  Name   TRUNC sum bytes  1024 1024  Meg FROM    SELECT segment name table name  owner  bytes   Table  as  Type     FROM dba segments    WHERE segment type in    TABLE   TABLE PARTITION   TABLE SUBPARTITION    UNION ALL    SELECT i table name  i owner  s bytes   Index  as  Type     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   LOB  as  Type     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   LOB SUBPARTITION    UNION ALL    SELECT l table name  l owner  s bytes   LOB Index  as  Type     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   amp owner   GROUP BY table name  owner   Type  HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

select segment name as tablename  sum bytes   1024   1024   1024   as tablesize in GB From dba segments    if looking at tables not owned by you else use user segments    where segment name    TABLE WHOSE SIZE I WANT TO KNOW  and   OWNER    WHO OWNS THAT TABLE     if user segments is used delete this line     group by segment name

User · Answer

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

User · Answer

I found this to be a little more accurate   SELECT    owner  table name  TRUNC sum bytes  1024 1024 1024  GB FROM  SELECT segment name table name  owner  bytes FROM dba segments WHERE segment type in    TABLE   TABLE PARTITION   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   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   amp owner   GROUP BY table name  owner HAVING SUM bytes  1024 1024  gt  10     Ignore really small tables    ORDER BY SUM bytes  desc

User · Answer

For sub partitioned tables and indexes we can use the following query        SELECT owner  table name  ROUND sum bytes  1024 1024 1024  2  GB     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    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

[oracle] How do I calculate tables size in Oracle

Examples related to oracle