How to get name of owners, their object names and sizes of this object in one query?

Question

I want to check which of user's objects are the biggest. So I made query gives me total size of objects every user:

select
  owner,
  tablespace_name,
  round((sum(bytes)/1024/1024/1024),2) as size_in_gb,
  (select round((sum(bytes)/1024/1024/1024),2) from v$datafile) as total_space_in_gb
from
   dba_segments
where
   owner like 'OPS$%'
group by
 owner, tablespace_name
order by
  size_in_gb desc
fetch first 55 rows only;

But it gives me only information about total size of all objects of particular user. How to check everys user objects and its size?

Answer 1

Add segment_name to your select list and your group by

select
  segment_name,
  owner,
  tablespace_name,
  round((sum(bytes)/1024/1024/1024),2) as size_in_gb,
  (select round((sum(bytes)/1024/1024/1024),2) from v$datafile) as total_space_in_gb
from
   dba_segments
where
   owner like 'OPS$%'
group by
 segment_name, owner, tablespace_name
order by
  size_in_gb desc
fetch first 55 rows only;

It is not obvious, though, what your v$datafile query is doing. That is summing the size of every data file, not just those associated with the tablespace(s) where the OPS$ user has objects. It isn't obvious that produces a meaningful number let alone a number that you'd want to duplicate on every line of your result. But if you're getting segment-level data in your result, it isn't obvious what value you'd want in that column.