SQL query to check table size in Oracle database

In order to check size of table in Oracle database you may use following query:

Please follow and like us:

6 thoughts on “SQL query to check table size in Oracle database

  1. dchitac

    You should better use :”dba_segments” view instead of “user_segments”, otherwise you might loose some information, see user authorizations & rights.

    Reply
    1. Mikhail

      Thanks for the tip. I guess I used “user_segments” back then because of limited access rights, but thanks for pointing this out anyway.

      Reply
  2. Jfk

    This does not apply to tables with partitions and subpartitions.If you want to query for tables with partitions and subpartitions, leave out the segment_type.

    Reply
  3. Fernando

    Based on the example above I’m using the following:

    WITH tbls AS (
    SELECT DISTINCT OWNER, OBJECT_NAME
    FROM ALL_OBJECTS
    WHERE OBJECT_TYPE = ‘TABLE’
    AND OWNER = ‘SCHEMA_NAME’
    ) SELECT segment_name, bytes/1024/1024 MB FROM user_segments
    WHERE segment_type=’TABLE’ AND segment_name in (SELECT OBJECT_NAME FROM tbls);

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *