To check the size of table or segments in a schema in Oracle

In an Oracle database, a segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. To be precise, segments contain the real data and they usually remain in the owner’s default tablespace and usually take most of the disk space. Segments comprise tables, indexes, large objects, etc.

NOTE: Segments and Schema objects are not the same things. A segment is just an independent storage piece that belongs to a schema object, e.g. table or index. It indicates that segments not only contain the metadata but real data. Schema objects like procedure, view, database link, and synonym which contain no real data shall not have segments.

The following query will be useful when sometimes we require to find out what is the allocated size of a table before proceeding with any activity.

Below is the SQL query to check the size of segments (segment type “TABLE” in a schema. Click here for sample output.

For Size in MB :

SET LINES 333 PAGES 1000
COL OWNER FOR A30
COL TABLESPACE_NAME FOR A30
COL SEGMENT_NAME FOR A30
SELECT
OWNER,
TABLESPACE_NAME,
SEGMENT_NAME,SUM(BYTES)/1024/1024 AS "SIZE IN MB"
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE='TABLE' AND OWNER LIKE 'TEST%'
GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME
ORDER BY OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES) DESC;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

NOTE: In the above command, you can change your desire segment name or column fields as required.

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.