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.