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.
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);
6 Comments
You should better use :”dba_segments” view instead of “user_segments”, otherwise you might loose some information, see user authorizations & rights.
Thanks for the tip. I guess I used “user_segments” back then because of limited access rights, but thanks for pointing this out anyway.
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.
Great !!! Thanks! This query helps, all the other ones I tried didn’t work.\r\nThank you!
Glad that it worked for you.
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);