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

    Добрый день! Мы команда системных аналитиков одного из подразделений управления данными «Ростелекома». В нашей компании насчитывается более 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
                and a.owner = d.owner
        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
    ;
    

    Заключение


    Описанные выше скрипты помогают нашим системным аналитикам избавиться от многих рутинных действий по сбору информации о базе данных и сосредоточиться на более творческих вещах, таких как стратегия загрузки и структура таблиц-«приемников». Надеюсь, скрипты пригодятся и вам. Было бы интересно узнать, как вы автоматизируете эти и подобные задачи.
    • +12
    • 6,2k
    • 2
    Ростелеком
    102,00
    Компания
    Поделиться публикацией

    Комментарии 2

      0
      Вот поэтому я никогда и не показываю рабочие скрипты. Они обычно написаны наспех и годятся только для внутреннего пользования.

      В скрипте «Информация о таблицах»
      * таблица «d» джойнится только имени таблицы, когда надо джойнить по 2м полям.
      * запрос раз в 10 тяжелее чем можно было бы за счет джойнов с запросами с предварительной группировкой.

      Скрипт «Скрипт «Атрибутный состав таблиц»» не смог дождаться завершения при копипасте с сайте. Выключил через 2 минуты. Притом, что ожидаемый вывод 2 строки:
      SCHEMA_NAME_1 TABLE_NAME_1
      SCHEMA_NAME_1 TABLE_NAME_2

      Поведение вызвано тем же GROUP BY.

      PS. добавлю облегченный скрипт «Информация о таблицах».
      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.num_rows as height
              ,(SELECT   COUNT(1) AS width
                    FROM all_tab_columns x
                   WHERE x.owner = a.owner
                     AND x.table_name = a.table_name
                GROUP BY owner, table_name)
                  width
              ,(SELECT   LISTAGG(column_name || ' (' || data_type || ')', ', ') WITHIN GROUP (ORDER BY column_id)
                            AS datetime_columns
                    FROM all_tab_columns x
                   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%')
                     AND x.owner = a.owner
                     AND x.table_name = a.table_name
                GROUP BY owner, table_name)
                  datetime_columns
              ,b.avg_row_len
              ,(SELECT   LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_position) AS part_key
                    FROM all_part_key_columns x
                   WHERE object_type = 'TABLE'
                     AND x.owner = a.owner
                     AND x.name = a.table_name
                GROUP BY owner, name)
                  part_key
              ,(SELECT   LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_position) AS subpart_key
                    FROM all_subpart_key_columns x
                   WHERE object_type = 'TABLE'
                     AND x.owner = a.owner
                     AND x.name = a.table_name
                GROUP BY owner, name)
                  subpart_key
          FROM filter a
               LEFT JOIN all_tab_statistics b
                  ON a.table_name = b.table_name
                 AND a.owner = b.owner
                 AND b.object_type = 'TABLE'
               LEFT JOIN all_tab_comments e
                  ON a.table_name = e.table_name
                 AND a.owner = e.owner
                 AND table_type = 'TABLE'
      ORDER BY a.owner, a.table_name

        0
        Спасибо, koropovskiy, ошибку с join подправил. Над скоростью работаем.

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое