Подсчет места, занятого объектами схем(ы) на дисках

with
    -- список схем через запятую и шаблон поиска
    q_schema_names_list as (select '&schema_owners' list, '\s*([^,]+)\s*' pattern from dual),
    -- таблица со схемами
    q_schema_names as (select upper(regexp_substr(list,pattern,1,level,'i',1)) schema_name from q_schema_names_list connect by regexp_substr(list,pattern,1,level,'i',1) is not null )
-- результат
select
    owner$ "Владелец",
    tablespace_name "Табличное пространство",
    segment_name "Сегмент",
    segment_type "Тип сегмента",
    master_object "Объект владедец",
    sum_size$ "Размер объекта"
from (
    -- приводим значения в удобочитаемый вид
    select
        case when grouping$type = '1111111' then 'ИТОГО по запросу' when grouping$type = '0111111' then 'Итого по схеме '|| owner else owner end owner$,
        tablespace_name,
        segment_name,
        segment_type,
        master_object,
        case
            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024 / 1024 ) || ' PB'

            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024) || ' TB'
            when (tt.sum_size / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024) || ' GB'
            when (tt.sum_size / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024) || ' MB'
            when (tt.sum_size / 1024) > 1 then to_char(tt.sum_size / 1024) || ' KB'
            else to_char(tt.sum_size) || ' B'
        end sum_size$,
        case when grouping$type = '1111111' then 10E6 when grouping$type = '0111111' then 10E6-1 else rn end rn$
    from (
        -- rollup запрос с подсчетом суммарных размеров
        select
            owner,
            tablespace_name,
            segment_name,
            segment_type,
            master_object,
            bytes,
            rn,
            grouping(owner)||
            grouping(tablespace_name)||
            grouping(segment_name)||
            grouping(segment_type)||
            grouping(master_object)||
            grouping(bytes) ||
            grouping(rn)grouping$type,
            sum(bytes) sum_size
        from (
            -- перечень интересующих нас сегментов по списку схем, сортируем список по убыванию занимаемого объектами размера
            select
                s.owner,
                s.tablespace_name,
                s.segment_name,
                s.segment_type,
                case
                    when s.segment_name like 'BIN$%' then '<Recyclebin> => ' || (select distinct original_name from dba_recyclebin r where r.owner = s.owner and r.object_name = s.segment_name)
                    when s.segment_type = 'TABLE' then s.segment_name
                    when s.segment_type in ('INDEX', 'LOBINDEX') then (select t.table_name from dba_indexes t where t.owner = s.owner and t.index_name = s.segment_name)
                    when s.segment_type = 'LOBSEGMENT' then (select t.table_name from dba_lobs t where t.owner = s.owner and t.segment_name = s.segment_name)
                    when s.segment_type = 'LOB PARTITION' then (select distinct t.table_name from dba_lob_partitions t where t.table_owner = s.owner and t.lob_name = s.segment_name)
                    when s.segment_type = 'TABLE PARTITION' then nvl((select distinct t.table_name from dba_tab_partitions t where t.table_owner = s.owner and t.partition_name = s.segment_name),s.segment_name)
                    when s.segment_type = 'INDEX PARTITION' then nvl((select distinct t.table_name from dba_indexes t where t.table_owner = s.owner and t.index_name = s.segment_name),s.segment_name)
                end master_object,
                s.bytes,
                row_number() over (order by owner, bytes desc) rn
            from
                dba_segments s
            where
                s.owner in (select schema_name  from q_schema_names)
        ) t group by rollup (owner, tablespace_name, segment_name, segment_type, master_object,bytes, rn)
    ) tt where grouping$type in ('0000000', '0111111', '1111111')
    order by
        tt.owner, rn$
) z