6 Comments

  • dchitac says:

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

    • Mikhail says:

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

  • Jfk says:

    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.

  • okyereis says:

    Great !!! Thanks! This query helps, all the other ones I tried didn’t work.\r\nThank you!

  • Fernando says:

    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);

Leave a Reply

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