Checking size of the tablespaces in Oracle for one schema

Most of the time we try to verify what objects are mapped to what tablespace. But most of the time we try to verify the size of the current schema.

Imagine there could be a situation where you have a schema and all of its objects are been scattered around multiple tablespaces. While might not be the same tablespace as what you see on its defaulted tablespace. Now if someone needs to move or migrate then the first thing comes in mind about the space. What we can do about it, how to manage it, how the objects within the schema are growing … lots of questions. So just to identify for one schema just login to the schema and execute the below script to identify what all the tablespace are used within that schema and what is the size in GB.
Here is the SQL to identify the size of the tablespaces utilized for one schema:

SELECT count(gb) total_table_gb,tablespace_name  FROM (
  SELECT
     tablespace_name, ROUND(bytes)/1024/1024 AS meg,ROUND(bytes)/ 1024 / 1024 / 1024 AS GB
  FROM (
    SELECT
    segment_name AS object_name, 'TABLE' AS object_type,
          segment_name AS table_name, bytes,
          tablespace_name, extents, initial_extent
    FROM   user_segments
    UNION ALL
    SELECT  l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
          l.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   all_lobs l, user_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.segment_type = 'LOBSEGMENT'
)
)
group by tablespace_name;

Here is one example that I have executed on one schema.



Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

One thought on “Checking size of the tablespaces in Oracle for one schema

Leave a Reply to Jamal Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.