()

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.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Sruti Banerjee

Hello and welcome to DBsGuru, I am Sruti Banerjee, having experience of 3 years as an Oracle DBA, I have good interpersonal skills that can help in attaining the desired level of team coordination, self-starter & quick learner. I am having rich experience in Oracle database management, Backup and Recovery, Security, Installation, Up-gradation, Patching, Migration, Cloning of databases, Dataguard (Physical), and a wide range of other database administration activities.Thanks for the visits!Share Learn Grow!