Источник: 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;Ссылки по теме