SQL query to check table size in Oracle database Mikhail | April 30, 2014 In order to check size of table in Oracle database you may use following query: View the code on Gist.
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
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);