(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: habrahabr
odiusage

Процесс выката новых версий  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;

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Oracle »
Написать редактору 
 Рекомендовать » Дата публикации: 20.08.2012 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Personal Edition Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Standard Edition 2 Processor License
Oracle Database Standard Edition 2 Named User Plus License
CAD Import .NET Professional пользовательская
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
СУБД Oracle "с нуля"
Все о PHP и даже больше
Компьютерная библиотека: книги, статьи, полезные ссылки
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Access, Ключевое поле, Счетчик (10)
Подскажите пожалуйста, как изменить в Access в ключевом поле (счетчик последовательно),...
 
Решение задач на оптимизацию с помощью MS Excel (4)
Zdravstvuyte, ya pro4itala danniy material, no ne smogla ponat kakie formuli ispolzovalis v...
 
Отличается ли ДрифтКазино от беттинга? (4)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Сайт инструмент (1)
Я бывший программист пользовался 1 сайтом проверенным он мне действительно помог я блогодоря...
 
Где взять лицензионный ключ для AllFusion Process Modeler (BPwin) 7? (5)
Выручайте!!! где найти ключ, ужасно срочно нужна программа. заранее спасибо!
 
 
 



    
rambler's top100 Rambler's Top100