Check Oracle Schema Size

To estimate the space used by a schema, sum the bytes allocated to that schema’s segments in DBA_SEGMENTS.

SELECT owner,
       ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb
FROM   dba_segments
WHERE  owner = 'SCOTT'
GROUP  BY owner;

Use the schema name in uppercase unless it was created with quoted mixed-case identifiers.

If you do not have DBA_SEGMENTS access

A schema owner can usually query USER_SEGMENTS for its own objects:

SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb
FROM   user_segments;

This reports allocated segment space. It is not the same as logical row data size or total tablespace consumption.