Скрипт для сравнения двух БД Oracle

    Процесс выката новых версий ETL на продуктив всегда процесс волнующий. Редко когда среда разработки полностью соответствует среде эксплуатации, в моем предыдущем проекте различались в том числе ОС и железо, на которых велась разработка и эксплуатация ХД.

    Хорошо хоть база данных использовалась одна и та же — Oracle. Для того, чтобы убрать максимальное количество различий между настройками и содержимым продуктивной и разработческой БД мой коллега подготовил скрипт, собирающий, и, что очень важно, правильно форматирующий вывод в файл, скрипт, позволяющий сравнивать две БД.

    После такого сравнения и унификации многие, зачастую неожиданные, проблемы при деплое должны будут найтись немного раньше, на этапе тестирования, т.е. при деплое с ДЕВа на СИТ. А определенное количество устаревших маппингов OWB или таблиц можно будет удалить с ПРОД системы.

    Возможности сравнения нескольких БД присутствуют в некоторых утилитах разработки и администрирования БД, в частности данный набор скриптов получен из Toad. Мой коллега просто выбрал необходимые параметры для сравнения, которых, я думаю, будет достаточно для начала и вам, а уж если вы используете кластеризацию таблиц или что-то более сложное добавить вывод этих объектов для сравнительного анализа сможете сами.

    Сформированные файлы (db_info.txt с ДЕВа и такой же с ПРОДа) можно, в дальнейшем, сравнивать утилитами типа WinDiff и решить, где значение вернее и какое из них оставить.


    -- Настройка параметров вывода для sqlplus
    Set Echo Off Heading On Underline On Recsep Off Feedback off;
    Set Linesize 600
    Set pagesize 50000
    Set Pause Off
    
    -- Выводим в этот файл
    spool db_info.txt
    
    col name format a80
    col isdefault format a9
    col type format a18
    col value format a300
    
    -- Список параметров БД
    prompt List of database parameters
    SELECT name,isdefault, value FROM v$parameter order by num;
    
    -- Список объектов и типов
    prompt List of objects and types
    SELECT o.object_name, o.object_type, o.status, t.typecode, t.attributes, t.methods
    FROM  SYS.ALL_OBJECTS o, SYS.ALL_TYPES t
    WHERE o.owner in (<перечень необходимых схем>)
    and   o.owner = t.owner
    and   o.object_type = 'TYPE'
    and   o.object_name = t.type_name
    and   o.subobject_name is null
    and   t.type_name not like 'SYS@_PLSQL@_%' escape '@' order by 1,2,3,4;
    
    -- Список директорий
    prompt List of directories
    -- В моей БД нет схем, с наименованием длиннее 20 символов.
    col owner format a20
    col directory_name format a30
    col directory_path format a250
    SELECT owner, directory_name, directory_path FROM SYS.ALL_DIRECTORIES
    WHERE 1=1 order by 1,2,3;
    
    -- Список пользовательских типов
    prompt List of user types
    SELECT owner, type_name, typecode
    FROM
     SYS.ALL_TYPES WHERE owner in (<перечень необходимых схем>) order by 1,2,3;
     
    -- Список пользовательских сиквенсов
    prompt List of user sequences
    SELECT sequence_owner owner,SEQUENCE_NAME, to_char(MIN_VALUE) min_value, to_char(MAX_VALUE) max_value, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE
    FROM SYS.ALL_SEQUENCES
    WHERE sequence_owner in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список некластеризованных таблиц  
    col table_name format a30
    col tablespace_name format a30
    prompt List of non-cluster tables
    SELECT owner,
      TABLE_NAME,
      TABLESPACE_NAME,  
      TEMPORARY, LOGGING, PARTITIONED, NESTED, ROW_MOVEMENT,
      MONITORING, DEPENDENCIES, COMPRESSION, 
      'NO' READ_ONLY, CACHE
    FROM ALL_OBJECT_TABLES t
    WHERE owner in (<перечень необходимых схем>)
    and ((iot_type is null) or (iot_type <> 'IOT_MAPPING'))
    and cluster_name is null
    
    union all
    
    SELECT owner,
      TABLE_NAME,
      TABLESPACE_NAME,
      TEMPORARY, LOGGING, PARTITIONED, NESTED, ROW_MOVEMENT,
      MONITORING,  DEPENDENCIES, COMPRESSION, 
      READ_ONLY, CACHE
    FROM ALL_TABLES t
    WHERE owner in (<перечень необходимых схем>)
    and ((iot_type is null) or (iot_type <> 'IOT_MAPPING'))
    and cluster_name is null order by 1,2,3,4;
    
    -- Список колонок таблиц
    col column_name format a30
    prompt List of table columns
    SELECT owner,table_name, column_name, data_type,
           decode(data_type, 'CHAR', char_length,
                             'VARCHAR', char_length,
                             'VARCHAR2', char_length,
                             'NCHAR', char_length,
                             'NVARCHAR', char_length,
                             'NVARCHAR2', char_length,
                             data_length) data_length,
           data_precision, data_scale, nullable, char_used
           , virtual_column
    FROM SYS.ALL_TAB_COLS c
    WHERE OWNER  in (<перечень необходимых схем>)
    and   HIDDEN_COLUMN = 'NO'
    and exists (SELECT 'x'
                FROM  sys.ALL_ALL_TABLES t
                WHERE t.table_name = c.table_name
                and   t.owner = c.owner)
    order by owner,table_name, column_id;
    
    -- Список колонок со значениями по-умолчанию
    prompt List of columns with default value 
    SELECT owner,TABLE_NAME, COLUMN_NAME, DEFAULT_LENGTH, DATA_DEFAULT
    FROM SYS.ALL_TAB_COLUMNS C WHERE OWNER  in (<перечень необходимых схем>)
    and default_length is not null order by 1,2,3;
    
    -- Список колонок, являющихся ссылками на объекты
    prompt List of REF columns or attributes
    SELECT * FROM SYS.ALL_REFS WHERE owner in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список настроек для логирования изменений в таблицах через Streams
    prompt List of log group definitions on users table
    SELECT c.owner,c.TABLE_NAME, l.LOG_GROUP_NAME, c.COLUMN_NAME, l.ALWAYS
    FROM   SYS.ALL_LOG_GROUPS l, SYS.ALL_LOG_GROUP_COLUMNS c
    WHERE  l.OWNER = c.OWNER
    and    l.owner in (<перечень необходимых схем>)
    and    l.LOG_GROUP_NAME = c.LOG_GROUP_NAME
    and    l.TABLE_NAME = c.TABLE_NAME
    order by c.TABLE_NAME, l.LOG_GROUP_NAME, c.POSITION;
    
    -- Список индексов
    col index_name format a30
    prompt List of indexes
    SELECT OWNER,
       INDEX_NAME,
       INDEX_TYPE,
       TABLE_OWNER,
       TABLE_NAME,
       TABLE_TYPE,
       UNIQUENESS,
       COMPRESSION,
       TABLESPACE_NAME,
       LOGGING,
       STATUS,
       INSTANCES,
       PARTITIONED,
       TEMPORARY,
       GLOBAL_STATS,
       JOIN_INDEX,
       SEGMENT_CREATED FROM SYS.ALL_INDEXES i WHERE owner  in (<перечень необходимых схем>)
    and index_type <> 'LOB' order by 1,2,3,4;
    
    -- Список дблинков (общих или в конкретных схемах)
    col db_link format a50
    col host format a200
    prompt List of database links for SELECTed users or public database links
    SELECT B.NAME OWNER, A.NAME DB_LINK, A.USERID USERNAME, A.HOST, decode(bitand(a.flag, 1), 1, 'YES', 'NO') shared, a.authusr
    FROM SYS.LINK$ A, SYS.USER$ B
    WHERE A.OWNER# = B.USER#
    and (B.NAME in (<перечень необходимых схем>) OR B.NAME = 'PUBLIC')
    order by 1,2,3,4;
    
    -- Список заголовков пакетов
    col object_name format a30
    col object_type format a20
    col status format a10
    prompt List of packages
    SELECT  owner, object_name, object_type, status
    FROM SYS.ALL_OBJECTS WHERE owner in (<перечень необходимых схем>) and OBJECT_TYPE = 'PACKAGE' order by 1,2,3,4;
    
    -- Список тел пакетов
    prompt List of package bodies
    SELECT owner, object_name, object_type, status FROM SYS.ALL_OBJECTS WHERE owner in (<перечень необходимых схем>) and OBJECT_TYPE = 'PACKAGE BODY'
    union all
    SELECT distinct owner, s.name, 'PACKAGE BODY', 'VALID'
    FROM   sys.all_source s
    WHERE  s.type = 'PACKAGE BODY'
    and    s.owner in (<перечень необходимых схем>)
    and    not exists (SELECT 'x'
                       FROM all_objects o
                       WHERE o.owner = s.owner
                       and o.object_name = s.name
                       and o.object_type = 'PACKAGE BODY') order by 1,2,3,4;
    
    -- Список процедур
    prompt List of procedures
    SELECT object_name, object_type, status, owner
    FROM SYS.ALL_OBJECTS WHERE owner in (<перечень необходимых схем>) and OBJECT_TYPE = 'PROCEDURE' order by 1,2,3,4;
    
    -- Список функций
    prompt List of functions
    SELECT object_name, object_type, status, owner
    FROM SYS.ALL_OBJECTS WHERE owner in (<перечень необходимых схем>) and OBJECT_TYPE = 'FUNCTION' order by 1,2,3,4;
    
    -- Список snapshot логов для материализованных представлений
    prompt List of snapshot logs
    SELECT *
    FROM SYS.ALL_SNAPSHOT_LOGS 
    WHERE LOG_OWNER in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список представлений
    col view_name format a30
    prompt List of views
    SELECT v.owner, v.view_name,  o.status, v.view_type_owner, v.view_type, superview_name
    FROM SYS.ALL_VIEWS v, SYS.ALL_OBJECTS o
    WHERE v.owner = o.owner
    and o.object_type = 'VIEW'
    and v.view_name = o.object_name
    and o.owner in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список колонок представлений
    col data_type format a10
    col data_type_mod format a10
    col data_type_owner format a10
    prompt List of view columns
    SELECT owner,COLUMN_NAME, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, TABLE_NAME, DATA_TYPE
    FROM SYS.ALL_TAB_COLUMNS C
    WHERE OWNER in (<перечень необходимых схем>)
    and exists (SELECT 'x'
                FROM   sys.ALL_VIEWS v
                WHERE  v.view_name = c.table_name
                and    v.owner = c.owner)
               and TABLE_NAME not like 'BIN$%'
    order by table_name, column_id;
    
    -- Список констрейнтов для представлений
    col constraint_name format a30
    prompt List of view check constraints
    SELECT owner,TABLE_NAME, CONSTRAINT_NAME
    FROM   SYS.ALL_CONSTRAINTS
    WHERE  owner in (<перечень необходимых схем>)
    and    constraint_type = 'V'
    and GENERATED = 'USER NAME' order by 1,2;
    
    -- Список триггеров
    prompt List of triggers
    col trigger_name format a30
    col trigger_type format a16
    col TRIGGERING_EVENT format a100
    SELECT t.owner,T.TRIGGER_NAME, T.TABLE_NAME, T.TRIGGER_TYPE, T.TRIGGERING_EVENT, T.STATUS
    FROM SYS.ALL_TRIGGERS t
    WHERE t.owner in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список синонимов
    col synonym_name format a30
    prompt List of synonyms
    SELECT * FROM SYS.ALL_SYNONYMS
    WHERE ((OWNER in (<перечень необходимых схем>)) OR ((TABLE_OWNER in (<перечень необходимых схем>)) and (OWNER = 'PUBLIC'))) order by 1,2,3,4;
    
    -- Список дименшинов
    col dimension_name format a30
    prompt List of dimensions
    SELECT * FROM SYS.ALL_DIMENSIONS WHERE owner in (<перечень необходимых схем>) order by 1,2,3,4;
    
    -- Список индексированных колонок
    prompt List of indexed columns
    SELECT INDEX_OWNER OWNER, INDEX_NAME, column_name, COLUMN_LENGTH, TABLE_OWNER, TABLE_NAME, COLUMN_POSITION, DESCEND
    FROM SYS.ALL_IND_COLUMNS
    WHERE INDEX_OWNER in (<перечень необходимых схем>)
    and INDEX_NAME not like 'BIN$%'
    ORDER BY INDEX_OWNER, INDEX_NAME, COLUMN_POSITION;
    
    -- Список колонок участвующих в условных битмап индексах
    prompt List of join conditions for bitmap indexes
    SELECT *
    FROM   SYS.ALL_JOIN_IND_COLUMNS
    WHERE  INDEX_OWNER in (<перечень необходимых схем>)
    order by index_owner, index_name;
    
    -- Список FBI индексов
    prompt List of  function based indexes
    SELECT IE.INDEX_OWNER OWNER, IE.INDEX_NAME, IE.COLUMN_EXPRESSION, IC.DESCEND,  case when ic.column_name like 'SYS_NC%' THEN 'AUTO GENERATED' ELSE  ic.column_name END column_name
    FROM   SYS.ALL_IND_EXPRESSIONS IE, SYS.ALL_IND_COLUMNS IC
    WHERE  IE.INDEX_OWNER = IC.INDEX_OWNER
    and    IE.INDEX_NAME = IC.INDEX_NAME
    and    IE.TABLE_OWNER = IC.TABLE_OWNER
    and    IE.TABLE_NAME = IC.TABLE_NAME
    and    IE.COLUMN_POSITION = IC.COLUMN_POSITION
    and    IC.INDEX_OWNER in (<перечень необходимых схем>)
    and    IC.COLUMN_NAME LIKE 'SYS_NC%'
    and    IE.INDEX_NAME not like 'BIN$%'
    Order by IE.Index_name, IC.column_position;
    
    -- Список комментариев к таблицам
    col comments format a300
    prompt List of table comments
    SELECT OWNER,TABLE_NAME, NULL COLUMN_NAME, comments
    FROM   SYS.ALL_TAB_COMMENTS
    WHERE  OWNER in (<перечень необходимых схем>)
    and    COMMENTS IS NOT NULL and TABLE_NAME not like 'BIN$%'
    UNION ALL
    SELECT owner,TABLE_NAME, COLUMN_NAME, comments
    FROM   SYS.ALL_COL_COMMENTS
    WHERE  OWNER in (<перечень необходимых схем>)
    and    COMMENTS IS NOT NULL and TABLE_NAME not like 'BIN$%'
    UNION ALL
    SELECT owner,MVIEW_NAME, NULL COLUMN_NAME,  comments
    FROM   SYS.ALL_MVIEW_COMMENTS
    WHERE  OWNER in (<перечень необходимых схем>)
    and    COMMENTS IS NOT NULL and MVIEW_NAME not like 'BIN$%' order by 1,2;
    
    -- Список условных констрейнтов. Для безыменных добавляется дефолтное имя AUTO GENERATED
    prompt List of  check constraints
    SELECT c.owner,case when c.constraint_name like 'SYS_C%' THEN 'AUTO GENERATED' ELSE  c.constraint_name END constraint_name, C.TABLE_NAME, CC.COLUMN_NAME, C.SEARCH_CONDITION
    FROM   SYS.ALL_CONSTRAINTS c, SYS.ALL_CONS_COLUMNS cc
    WHERE  c.OWNER = cc.OWNER
    and    c.table_name = cc.TABLE_NAME
    and    c.CONSTRAINT_NAME = cc.constraint_name
    and    c.constraint_type in ('C', '?')
    and    cc.column_name NOT LIKE 'SYS_NC%'
    and    c.TABLE_NAME not like 'BIN$%'
    and    c.owner in (<перечень необходимых схем>)
    and    exists (SELECT owner, table_name, constraint_name  
                   FROM   ALL_CONS_COLUMNS cc2
                   WHERE  cc2.owner = c.owner
                   and    cc2.constraint_name = c.constraint_name
                   and    cc2.table_name = c.table_name
                   and    cc2.column_name NOT LIKE 'SYS_NC%' -- Без констрейнтов для виртуальных столбцов
                   group  by owner, table_name, constraint_name
                   having count(*) = 1) order by 1,2,3;
    
    -- Список всех констрейнтов. Для безыменных добавляется дефолтное имя AUTO GENERATED
    prompt List of all constraints
    SELECT owner,case when constraint_name like 'SYS_C%' THEN 'AUTO GENERATED' ELSE  constraint_name END constraint_name, OWNER, TABLE_NAME, CONSTRAINT_TYPE, SUBSTRB(STATUS, 1, 1) STATUS
    , SUBSTRB(DEFERRABLE, 1, 1) deferrable, SUBSTRB(DEFERRED, 1, 1) deferred, SUBSTRB(GENERATED, 1, 1) generated
    , RELY
    , VALIDATED
    , VIEW_RELATED
    FROM SYS.ALL_CONSTRAINTS
    WHERE OWNER in (<перечень необходимых схем>)
    and TABLE_NAME not like 'BIN$%'
    and table_name not in (SELECT table_name FROM SYS.ALL_TABLES WHERE owner in (<перечень необходимых схем>) and cluster_name is not null) order by 1,2,3,4;
    
    
    -- Список колонок, используемых в констрейнтах
    prompt List of columns specified in constraints
    SELECT owner,case when constraint_name like 'SYS_C%' THEN 'AUTO GENERATED' ELSE  constraint_name END constraint_name, TABLE_NAME, COLUMN_NAME
    FROM SYS.ALL_CONS_COLUMNS A
    WHERE OWNER in (<перечень необходимых схем>)
    and TABLE_NAME not like 'BIN$%'
    ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION, COLUMN_NAME;
    
    -- Список груп политик безопасности
    prompt List of policy groups
    SELECT  *
    FROM SYS.ALL_POLICY_GROUPS
    WHERE object_owner in (<перечень необходимых схем>) order by 1,2,3;
    
    -- Список политик безопасности
    col policy_group format a30
    col policy_name format a30
    col pf_owner format a30
    col package format a30
    col function format a30
    col static_policy format a30
    col policy_type format a30
    prompt List of policies
    SELECT  object_owner owner, object_name, policy_group, policy_name, pf_owner, package, function,sel,ins,upd,del,idx,chk_option,enable,static_policy,policy_type
    FROM SYS.ALL_POLICIES
    WHERE object_owner in (<перечень необходимых схем>) order by 1,2,3;
    
    -- Список колонок, участвующих в политиках безопасности
    prompt List of security relevant columns
    SELECT  *
    FROM SYS.ALL_SEC_RELEVANT_COLS
    WHERE object_owner in (<перечень необходимых схем>) order by 1,2,3;
    
    -- Список привилегий на объекты
    prompt List of object grants
    SELECT ue.name GRANTEE, u.name OWNER, o.name TABLE_NAME, ur.name GRANTOR, tpm.name PRIVILEGE,
           decode(mod(oa.option$,2), 1, 'YES', 'NO') GRANTABLE,
           decode(bitand(oa.option$,2), 2, 'YES', 'NO') HIERARCHY,
           decode(o.TYPE#, 2, 'TABLE',        4, 'VIEW',
                           6, 'SEQUENCE',     7, 'PROCEDURE',
                           8, 'FUNCTION',     9, 'PACKAGE',
                           13, 'TYPE',       22, 'LIBRARY',
                           23, 'DIRECTORY',  24, 'QUEUE',
                           28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                           32, 'INDEXTYPE',  33, 'OPERATOR',
                           42, 'MATERIALIZED VIEW',  'UNDEFINED') object_type
    FROM sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
         sys.table_privilege_map tpm
    WHERE oa.obj# = o.obj#
      and oa.grantor# = ur.user#
      and oa.grantee# = ue.user#
      and oa.col# is null
      and oa.privilege# = tpm.privilege
      and u.user# = o.owner#
      and o.TYPE# in (2,4,6,7,8,9,13,22,24,28,29,30,32,33,42)
      and u.name in (<перечень необходимых схем>)
      and o.name not like 'BIN$%'
    ORDER BY 1, 2, 3, 5;
    
    -- Список привилегий на колонки
    prompt List of column privileges
    SELECT * FROM SYS.ALL_COL_PRIVS WHERE TABLE_SCHEMA  in (<перечень необходимых схем>)
    ORDER BY grantee, TABLE_SCHEMA, table_name, column_name, privilege;
    
    col REFERENCED_OWNER format a30
    col REFERENCED_TYPE format a30
    col REFERENCED_NAME format a30
    col REFERENCED_LINK_NAME format a30
    
    -- Список зависимостей между объектами. Генерирует очень много строк!!!
    prompt List of dependencies between objects
    SELECT  owner,NAME object_name, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_LINK_NAME
    FROM   SYS.ALL_DEPENDENCIES
    WHERE  OWNER  in (<перечень необходимых схем>)
    and    OWNER || NAME || TYPE <> REFERENCED_OWNER || REFERENCED_NAME || TYPE
    and REFERENCED_NAME not like 'BIN$%'
    and   TYPE in ('DIMENSION','FUNCTION','INDEX','MATERIALIZED VIEW','SNAPSHOT','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','TABLE','TYPE','TYPE BODY','VIEW') order by 1,2,3,4;
    
    spool off
    
    exit;
    
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 8

      0
      Спасибо коллеге за скрипт!
      Статью в запоминальню…
      Мне как раз достался в наследство проект, где тестовый сервер в плачевном состоянии после множества экспериментов и тяжело понять какие эксперименты были удачными.
        0
        Я бы предложил выгрузить схему БД с помощью scheme2ddl из комплекта code.google.com/p/oracle-ddl2svn/ и сравнивать например, с помощью diff мли Araxis Merge.
          0
          в частности данный набор скриптов получен из Toad

          Отсюда — Program Files\Quest Software\Toad for Oracle 10.6\ClientFiles\ScriptMgr\Schema Objects?

          Для меня тоад до сих пор есть эталон среди всех средств сравнения.

          Пользуясь случаем, залинкую сюда связную задачка, когда исходники хранимого кода на деве были отформачены (обычный построчный диф не катит) а фактических изменений всего-ничего — тынц
          Должно помочь, когда код схемы грабается и диффается из консоли / по джобу, когда нет / ограничен доступ к средствам автоформатирования.

            0
            Рекомендую Вам не изобретать велосипед а использовать утилиту которая поставляется со всеми версиями базы данных начиная в 11g (в 10g возможно тоже есть), но в любом случае доступной для скачивания: отсюда совершенно бесплатно.

            Более того данная утилита не только сравнивает две схемы, но и предлагает скрипт для синхронизации баз!!! Одназжды данная функциональность спасла мою шкуру во время одного очень сложного переноса, типерь я всем ее советую :))

            Все что хотели знать о синхранизации баз с помошью данной утилиты но боялись спросить можно найти по данному адресу
              0
              Я в свою очередь рекомендую Вам сравнить возможности сравнения (тафталогия :) Oracle SQL Developer и TOAD'а. Использование скриптов из набора последнего, не самый худший из велосипедов.
                0
                «тавтология» конечно же
                  0
                  ТОАД отличный инструмент — жаль не все функиональность доступна в эклипс плагине.
                  Но ТОАД же платный?
                0
                Я наверное слеп, но что-то сравнения как такового я не увидел.
                Печаль…

                Only users with full accounts can post comments. Log in, please.