Как упростить исследование БД Oracle: «джентльменский набор» скриптов04.03.2019 15:40

Добрый день! Мы команда системных аналитиков одного из подразделений управления данными «Ростелекома». В нашей компании насчитывается более 300 неоднородных источников данных — такое многообразие необходимо для поддержки работы Ростелекома по всем многочисленным направлениям. Мы изучаем источники данных и по необходимости частично выгружаем в контур хранилища.

Ровер Curiosity на поверхности. У него тоже много неоднородных источников данных. Картинка позаимствована с therahnuma.com.

В этом процессе выделяется две подзадачи: определение стратегии сбора данных из таблиц источника в зависимости от их свойств и подготовка таблиц-«приемников» хранилища данных. Для этого мы используем различные GUI и средства реверс-инжиниринга. Кроме того, при сборе информации системный аналитик начинает обрастать пулом вспомогательных запросов к информационным таблицам СУБД (преимущественно Oracle). В этой статье я поделюсь «джентльменским набором» таких скриптов, используемых нашей командой.
Для начала небольшое пояснение ко всем приведенным скриптам:

Во многих скриптах для агрегации строк используется xmlagg, так как listagg не может обработать слишком длинные строки, получающиеся в результате конкатенации.
Во всех скриптах кроме «Процедуры, функции и пакеты» целевые таблицы задаются через таблицу filter в блоке «with». Заполняется наименование схемы и наименование таблицы.
К каждому скрипту прилагается один или несколько сценариев использования, описание спецификации (результирующего набора), а также список используемых системных таблиц (для оценки возможности использования на конкретной БД).

Скрипт «Информация о таблицах»

Спецификация:

Наименование колонки	Комментарий
SCHEMA_NAME	Наименование схемы данных (OWNER)
TABLE_NAME	Наименование таблицы
COMMENTS	Комментарий к таблице
HEIGHT	Количество строк в таблице (приблизительно)
WIDTH	Количество столбцов
DATETIME_COLUMNS	Столбцы с временнЫми типами данных и столбцы, исходя из наименования, предположительно являющиеся временнЫми метками (паттерны — %period%, %date%, %time%)
AVG_ROW_LEN	Средняя длина строки в байтах
PART_KEY	Столбцы по которым осуществлено партиционирование
SUBPART_KEY	Столбцы по которым осуществлено субпартиционирование

Используемые системные таблицы: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.

Запрос полезен для определения стратегии выгрузки данных из системы источника. Если на рассматриваемой таблице построен первичный ключ, то можно организовать выгрузку с последующим выделением «инкремента» по нему. При наличии метки времени — например, в технических полях с информацией о вставке данных или об обновлении — можно организовать выгрузку только измененных/добавленных записей за период времени. Информация о структуре партиций может пригодиться при создании аналогичной таблицы-«приемника».

Тело запроса:

with
filter (owner, table_name) as (
    select 'SCHEMA_NAME_1', t.*
    from table(
        sys.odcivarchar2list(
            'TABLE_NAME_1'
            , 'TABLE_NAME_2'
        )
    ) t
    union all
    select
        owner
        , table_name
    from
        all_tables
    where owner = 'SCHEMA_NAME_2'
)
select 
    a.owner as schema_name
    , a.table_name
    , e.comments
    , b.height
    , c.width
    , d.datetime_columns
    , b.avg_row_len
    , p.part_key
    , s.subpart_key
from
    filter a
    left join (
        select 
            owner
            , table_name
            , num_rows as height
            , avg_row_len
        from all_tab_statistics
        where object_type = 'TABLE'
    ) b
        on
            a.table_name = b.table_name
            and a.owner = b.owner
    left join (
        select
            owner
            , table_name
            , count(1) as width
        from all_tab_columns
        group by
            owner
            , table_name
    ) c
        on
            a.table_name = c.table_name
            and a.owner = c.owner
    left join (
        select
            owner
            , table_name
            , listagg(
                column_name || ' (' || data_type || ')'
                , ', '
            ) within group (order by column_id) as datetime_columns
        from all_tab_columns 
        where
            data_type = 'DATE'
            or data_type like 'TIMESTAMP%'
            or data_type like 'INTERVAL%'
            or lower(column_name) like '%period%'
            or lower(column_name) like '%date%'
            or lower(column_name) like '%time%'
        group by
            owner
            , table_name
    ) d
        on
            a.table_name = d.table_name
    left join (
        select
            owner
            , table_name
            , comments
        from all_tab_comments
        where table_type = 'TABLE'
    ) e
        on
            a.table_name = e.table_name
            and a.owner = e.owner
    left join (
        select
            owner
            , name as table_name
            , listagg(
                column_name
                , ', '
            ) within group (order by column_position) as part_key
        from all_part_key_columns
        where object_type = 'TABLE'
        group by
            owner
            , name
    ) p
        on 
            a.owner = p.owner
            and a.table_name = p.table_name
    left join (
        select
            owner
            , name as table_name
            , listagg(
                column_name
                , ', '
            ) within group (order by column_position) as subpart_key
        from all_subpart_key_columns
        where object_type = 'TABLE'
        group by
            owner
            , name
    ) s
        on
            a.owner = s.owner
            and a.table_name = s.table_name
order by
    e.owner
    , e.table_name
;

Скрипт «Партиции и субпартиции»

Спецификация:

Наименование колонки	Комментарий
SCHEMA_NAME	Наименование схемы данных (OWNER)
TABLE_NAME	Наименование таблицы
PART_KEY	Столбцы по которым осуществлено партиционирование
PARTITION_NAME	Наименование партиции
PARTITION_POSITION	Номер партиции
PARTITION_HEIGHT	Количество строк в партиции
SUBPART_KEY	Столбцы по которым осуществлено субпартиционирование
SUBPARTITION_NAME	Наименование субпартиции
SUBPARTITION_POSITION	Номер субпартиции
SUBPARTITION_HEIGHT	Количество строк в субпартиции

Используемые системные таблицы: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.

Скрипт будет полезен для получения характеристик (наименование, размеры) партиций при их непосредственном использовании в качестве источников данных.

Тело запроса:

with
filter (owner, table_name) as (
    select 'SCHEMA_NAME_1', t.*
    from table(
        sys.odcivarchar2list(
            'TABLE_NAME_1'
            , 'TABLE_NAME_2'
        )
    ) t
    union all
    select
        owner
        , table_name
    from
        all_tables
    where owner = 'SCHEMA_NAME_2'
)
select
    f.owner as schema_name
    , f.table_name
    , p.part_key
    , pc.partition_name
    , pc.partition_position
    , pc.num_rows as partition_height
    , s.subpart_key
    , sc.subpartition_name
    , sc.subpartition_position
    , sc.num_rows as subpartition_height
from
    filter f
    join (
        select
            owner
            , name as table_name
            , listagg(
                column_name
                , ', '
            ) within group (order by column_position) as part_key
        from all_part_key_columns
        where object_type = 'TABLE'
        group by
            owner
            , name
    ) p
        on 
            f.owner = p.owner
            and f.table_name = p.table_name
    left join all_tab_partitions pc
        on
            p.table_name = pc.table_name
            and p.owner = pc.table_owner
    left join (
        select
            owner
            , name as table_name
            , listagg(
                column_name
                , ', '
            ) within group (order by column_position) as subpart_key
        from all_subpart_key_columns
        where object_type = 'TABLE'
        group by
            owner
            , name
    ) s
        on
            p.owner = s.owner
            and p.table_name = s.table_name
    left join all_tab_subpartitions sc
        on
            f.owner = sc.table_owner
            and f.table_name = sc.table_name
            and pc.partition_name = sc.partition_name
    order by
        f.owner
        , f.table_name
;

Скрипт «Атрибутный состав таблиц»

Спецификация:

Наименование колонки	Комментарий
SCHEMA_NAME	Наименование схемы данных (OWNER)
TABLE_NAME	Наименование таблицы
COLUMN_ID	Порядковый номер столбца
COLUMN_NAME	Наименование столбца
DATA_TYPE	Тип данных
COLUMN_COMMENT	Комментарий к столбцу
SCALE	Масштаб (для типа данных NUMBER)
PRECISION	Точность (для типа данных NUMBER)
BYTE_LENGTH	Длина поля в байтах
ENCODING	Кодировка для текстовых типов данных (CHAR, VARCHAR2, NCHAR, NVARCHAR2)
CHAR_LENGTH	Максимальная длина строки для текстовых типов данных (CHAR, VARCHAR2, NCHAR, NVARCHAR2)
NOT_NULL	Флаг «Обязательно для заполнения»
IS_PRIMARY	Флаг «Входит в состав первичного ключа»
DEFAULT_VALUE	Значение «по умолчанию»
COLUMN_IMPACT	Список столбцов других таблиц, ссылающихся на столбец.
COLUMN_DEPEND	Список столбцов других таблиц, на которые ссылается столбец.

Используемые системные таблицы: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v$nls_parameters.

Этот скрипт будет полезен для подготовки таблиц-«приемников» в хранилище данных, когда нужна подробная информация о таблице, ее взаимосвязях с другими таблицами, а также полном атрибутном составе. Через вспомогательную таблицу filter2 задается фильтрация таблиц, для которых осуществляется поиск ссылок (от и к). По умолчанию берутся таблицы из всех схем, кроме системных.

Тело запроса:

with
filter (owner, table_name) as (
    select 'SCHEMA_NAME_1', t.*
    from table(
        sys.odcivarchar2list(
            'TABLE_NAME_1'
            , 'TABLE_NAME_2'
        )
    ) t
    union all
    select
        owner
        , table_name
    from
        all_tables
    where owner = 'SCHEMA_NAME_2'
)
, filter2 (owner, table_name) as (
    select owner, table_name 
    from all_tables
    where owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS')
)
, refs as (
    select
        b.constraint_type as from_constraint_type
        , b.constraint_name as from_constraint_name
        , d.position as from_position
        , d.column_name as from_column_name
        , b.table_name as from_table_name
        , b.owner as from_owner
        , a.owner as to_owner
        , a.table_name as to_table_name
        , c.column_name as to_column_name
        , c.position as to_position
        , a.constraint_name as to_constraint_name
        , a.constraint_type as to_constraint_type
    from
        all_constraints a
        left join all_constraints b
            on
                a.r_constraint_name = b.constraint_name
                and a.r_owner = b.owner
        left join all_cons_columns c
            on
                a.constraint_name = c.constraint_name
                and a.table_name = c.table_name
                and a.owner = c.owner
        left join all_cons_columns d
            on
                b.constraint_name = d.constraint_name
                and b.table_name = d.table_name
                and b.owner = d.owner
        where
            a.constraint_type = 'R'
            and b.constraint_type in ('P', 'U')
            and c.position = d.position
)
, depends as (
    select
        rtrim(
            xmlagg(
                xmlelement(
                    e
                    , to_owner || '.' || to_table_name || '.' || to_column_name
                    , ', '
                ).extract('//text()')
                order by to_owner
            ).getclobval()
            , ', '
        )  as val
        , from_owner as owner
        , from_table_name as table_name
        , from_column_name as column_name
    from refs
    where (to_owner, to_table_name) in (select * from filter2)
    group by
        from_table_name
        , from_column_name
        , from_owner
)
, impacts as (
    select
        rtrim(
            xmlagg(
                xmlelement(
                    e 
                    , from_owner || '.' || from_table_name || '.' || from_column_name
                    , ', '
                ).extract('//text()')
                order by from_owner
            ).getclobval()
            , ', '
        ) as val
        , to_owner as owner
        , to_table_name as table_name
        , to_column_name as column_name
    from refs
    where (from_owner, from_table_name) in (select * from filter2)
    group by
        to_table_name
        , to_column_name
        , to_owner
)
select
    f.owner as schema_name
    , f.table_name
    , a.column_id
    , a.column_name
    , a.data_type
    , b.comments as column_comment
    /*
        Если показатель precision не заполнен, то берется значение 38
        (максимально возможная точность в соответствии с документацией)
        , если не задан scale, то выводится значение 0 (масштаб не задан). */
    , decode (
        a.data_type
        , 'NUMBER', nvl(a.data_scale, 0)
        , ''
    ) as scale
    , decode (
        a.data_type
        , 'NUMBER', nvl(a.data_precision, 38)
        , ''
    ) as precision
    /*
        По умолчанию длина строки для типов CHAR, VARCHAR2 и их псевдонимов
        в DDL-скриптах задается в байтах, а для типов NCHAR or NVARCHAR2
        в символах.*/
    , a.data_length as byte_length
    , case
        when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
        then d.value
    end as encoding
    , case
        when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
        then a.char_length --a.char_col_decl_length
    end as char_length
    , decode(a.nullable, 'Y', 'N', 'Y') as not_null
    , decode(c.is_primary, 1, 'Y', 'N') as is_primary
    , a.data_default as default_value
    , impacts.val as column_impact
    , depends.val as column_depend
from
    filter f
    left join all_tab_columns a
        on
            f.owner = a.owner
            and f.table_name = a.table_name
    left join all_col_comments b
        on
            a.owner = b.owner
            and a.table_name = b.table_name
            and a.column_name = b.column_name
    left join (
        select
            1 as is_primary
            , owner
            , table_name
            , column_name
        from all_cons_columns
        where (owner, constraint_name) in (
            select owner, constraint_name
            from all_constraints
            where constraint_type = 'P'
        )
    ) c
        on
            a.owner = c.owner
            and a.table_name = c.table_name
            and a.column_name = c.column_name
    left join v$nls_parameters d
        on decode (
            a.character_set_name
            , 'CHAR_CS', 'NLS_CHARACTERSET'
            , 'NCHAR_CS', 'NLS_NCHAR_CHARACTERSET'
            , a.character_set_name
        ) = d.parameter
    left join depends
        on
            a.owner = depends.owner
            and a.table_name = depends.table_name
            and a.column_name = depends.column_name
    left join impacts
        on
            a.owner = impacts.owner
            and a.table_name = impacts.table_name
            and a.column_name = impacts.column_name
order by
    f.owner
    , f.table_name
    , a.column_id
;

Скрипт «Процедуры, функции и пакеты»

Спецификация:

Наименование колонки	Комментарий
SCHEMA_NAME	Наименование схемы данных (OWNER)
NAME	Наименование процедуры/функции/пакета/заголовка пакета
BODY	Тело
TYPE	Тип (PACKAGE BODY, PACKAGE, FUNCTION, PROCEDURE)
WRAPPED	Флаг «Закодировано тело или нет (wrapped)»

Используемые системные таблицы: all_source

При анализе источника может возникнуть задача по изучению потоков данных, циркулирующих в системе. Это практически невозможно сделать без кодовой базы пакетов, функций и процедур, особенно с неполной или отсутствующей документацией. Для удобства перечисленные объекты через скрипт можно представить в виде таблицы. Результат запроса с помощью консольной утилиты можно вывести в поток и перенаправить простейшим обработчиком (bash script) в файлы для дальнейшего изучения любимым редактором. Кроме того, на выходной поток можно «навесить» различные обработчики — beautifying, unwrapping и др.

Тело запроса:

select
    t.owner as schema_name
    , t.name as name
    , xmlagg(
        xmlelement(
            e 
            , t.text
            , ''
        ).extract('//text()')
        order by t.line asc
    ).getclobval() as body
    , f.wrapped
    , t.type as type
from (
    select
        owner, name, type
        , case
            when lower(text) like '%wrapped%' then 1 
            else 0
        end as wrapped
    from all_source
    where type in (
        'PACKAGE BODY'
        , 'PACKAGE'
        , 'FUNCTION'
        , 'PROCEDURE'
    )
    and line = 1 
    and owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS')
) f
join all_source t
on
    f.owner = t.owner
    and f.name = t.name
    and f.type = t.type
group by
    t.owner
    , t.name
    , t.type
    , f.wrapped
order by
    t.owner
    , t.name
    , t.type
;

Заключение

Описанные выше скрипты помогают нашим системным аналитикам избавиться от многих рутинных действий по сбору информации о базе данных и сосредоточиться на более творческих вещах, таких как стратегия загрузки и структура таблиц-«приемников». Надеюсь, скрипты пригодятся и вам. Было бы интересно узнать, как вы автоматизируете эти и подобные задачи.