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

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

Разбиение строки на токены (JSON версия)

Мне очень нравится реализация встроенной поддержки JSON в ORACLE, что я даже уже не хочу вспоминать те времена, когда ее не было и каждый пытался реализовать JSON-парсеры по-своему. Некоторые так к ним привыкли (например на моей текущей работе), что пользуются ими даже на 12c.

Набившую оскомину задачу разбиения строки на токены можно решить и с помощью JSON, обрамив список элементов через запятую квадратными скобками и передав на вход JSON-парсеру:

-- можно не обрамлять элементы двойными кавычками, например для целых чисел

with q_json as (select '[1, 2, 3, 4, 0.24, .2, 5e4]' arrayList from dual)

SELECT arrayItem FROM json_table( (select arrayList from q_json) , '$[*]' COLUMNS (arrayItem PATH '$'))

 

-- со строковыми значениями надо обрамлять элементы списка двойными кавычками

with q_json as (select '["кактус", "утром", "на","окне", "вел", "старушку","на","ремне"]' arrayList from dual)

SELECT arrayItem FROM json_table( (select arrayList from q_json) , '$[*]' COLUMNS (arrayItem PATH '$'))

Разбиение строки на токены

При использовании списков часто возникает необходимость в разбиении получившейся строки на токены по разделителю. Привожу пример, позволяющий это сделать.

with

    q_item_list as (

        select 'a,b,cv,d,r,e,,df,f,' list, ',' delimiter, '''' quotation 

        from dual),

    q_item_list_prepared as (

        select quotation||replace(list, delimiter, quotation||delimiter||quotation)||quotation list, delimiter, quotation 

        from q_item_list)

select

    regexp_substr(list, '['||quotation||']([^'||delimiter||']*)['||quotation||']+',1,level,'mi',1)

from

    q_item_list_prepared

connect by

    regexp_substr(list, '[^'||delimiter||']+',1,level) is not null

В данном случае строкой, имитирующей список является строка  'a,b,cv,d,r,e,,df,f,' (см q_item_list), разделителем я выбрал запятую, а обрамляющим символом принимается одиночный апостроф.
Первым делом квотируем все элементы списка апострофами с обеих сторон, чтобы не потерять при разбиении пустые элементы (см. q_item_list_prepared). После этого спокойно нарезаем все элементы по разделителю и после этого удаляем обрамляющие символы.

Если перед нами стоит задача получить из этой строки только непустые элементы, то все резко упрощается:

with

    q_item_list as (

        select 

            'a,b,cv,d,r,e,,df,f,' list, ',' delimiter, '''' quotation 

        from 

            dual)

    select 

        regexp_substr(list, '[^'||delimiter||']+',1,level) 

    from 

        q_item_list 

    connect by 

        regexp_substr(list, '[^'||delimiter||']+',1,level) is not null

В данном запросе мы ничего не подготавливаем к нарезке, а просто режем строку по разделителю.