- Este tópico contém 18 respostas, 7 vozes e foi atualizado pela última vez 15 anos, 11 meses atrás por
beneti.
-
AutorPosts
-
3 de junho de 2009 às 5:32 pm #87109
mpvargas
ParticipanteCaros Amigos,
Preciso criar alguns scripts de exportação e necessito comparar as tabelas de duas instancias diferentes.
Tenho o script pronto, que exporta os dados da tabela para arquivos .txt, mas gostaria de usar esse mesmo script para a outra instancia, as tabelas tem o mesmo nome, mas a estrutura de algumas é diferente.
Tem alguma forma de saber quais campos são diferentes nas tabelas?
Obrigado.3 de junho de 2009 às 7:10 pm #87112David Siqueira
ParticipanteE ai MP , beleza?
Então cara, algumas ferramentas como TOAD, PLDEVELOPER e etc tem uma opção de COMPARE SCHEMA, onde eles mostram as diferenças existentes, no EM da Oracle na parte de de Administração também tinha uma opção de comparar objetos , inclusive ele gerava até um relatório com as diferenças etc e tal. Tudo isso é possivel, ou se vc quiser crie um db link entre as instancias e desenvolva scripts manuais mesmo pra fazer as diferenciações e trazer as informações que vocÊ deseja.Abração
David
3 de junho de 2009 às 7:33 pm #87113mpvargas
ParticipanteAchei um script que compara a estrutura de duas tabelas
select a.table_name tabela,a.column_name coluna,
a.data_type tipo_a,
a.data_length tamanho_a,
a.data_precision precisao_a,
b.data_type tipo_b,
b.data_length tamanho_b,
b.data_precision precisao_b
from dba_tab_cols a inner join dba_tab_cols b
on a.table_name=b.table_name and a.column_name=b.column_name
where (a.data_type b.data_type
or a.data_length b.data_length
or a.data_precision b.data_precision)
and a.owner = ‘BASE_OFICIAL’ –Informe aqui o nome da base oficial
and b.owner=’BASE_TESTES’ –informe aqui o nome da base de testes
order by 1,2,3,4Mas como fazer para ler tabelas de schemas diferentes?
3 de junho de 2009 às 7:48 pm #87115eversonpiza
Participantempvargas,
Esse script que vc passou já lê dois schemas diferentes ‘BASE_OFICIAL’ e ‘BASE_TESTES’.
Agora se vc quiser comparar duas instâncias diferentes vai ter que criar db link.
Atn,
Everson3 de junho de 2009 às 9:03 pm #87117mpvargas
ParticipanteVocê pode me passar um exemplo de como criar um dblink.
Para acessar uma Tabela X, por exemplo.
Obrigado.3 de junho de 2009 às 9:11 pm #87119marlontk
ParticipanteCriação de um dblink
create database link LINK_instancia_esquema
connect to esquema identified by senha
using ‘SID’;
Instancia = nome do banco ao qual tu vai te conectar
esquema = usuario que possui os dados a serem comparados
senha = senha do usuario
‘SID’ = nome do banco de dadosnos selects na hora de referenciar a table que esta no dblink
Uitliza esquema.table@link_instancia_esquema
Obs.:
Voce precisa configurar o arquivo tns.ora para o banco a enchergar o banco b3 de junho de 2009 às 9:45 pm #87122Regis Araujo
ParticipanteOpa..
Desculpa a entromissão..
Mas só uma coisa.. o DBLINK é para conectar instâncias diferentes, Vargas se vc quiser comparar schemas dentro da mesma instância, basta vc dar GRANT de SELECT da tabela usuário do schema A para o usuário B e quando vc for rodar seu select..
Basta colocar o nome do OWNER(SCHEMA) antes do nome da tabela..
OWNER.TABELA
Abraços…
3 de junho de 2009 às 9:46 pm #87123David Siqueira
ParticipanteBoa Regis.
o Esquema é esse mesmo.Abraço.
3 de junho de 2009 às 9:52 pm #87125mpvargas
ParticipanteOK
Acho que preciso do DBLink mesmo.
Preciso comparar a Tabela X no Banco 1 com a Tabela X do Banco 2.
São 2 instancias diferentes.3 de junho de 2009 às 10:05 pm #87128marlontk
ParticipanteRealmente se o banco fosse o mesmo bastaria ter um usuario ter privilegios para ver os objetos do outro e utilizar a referencia OWNER.TABELA.
Mas entendo que é em bancos distintos(instancias distintas).
Neste caso se utiliza um DBLINK.Eu utilizo para fazer comparações o PL/SQL Developer.
Compara tables,triggers,procedures e functions entre bancos distintos.3 de junho de 2009 às 10:37 pm #87131mpvargas
ParticipanteEu tenho o Oracle SQL Developer, será que é o mesmo?
Onde tem a opção de comparar as tabelas?
Obrigado3 de junho de 2009 às 10:57 pm #87132mpvargas
ParticipanteMarlontk
Instalei o PL/SQL Developer
Vc pode me dar uma dica de como fazer comparação de tabelas em instancias diferentes?
Eu nunca usei este programa e estou com algumas dificuldades.
Obrigado3 de junho de 2009 às 11:28 pm #87133Ishii
ParticipanteOlá,
PL/SQL Developer: Conecte na Instância que tem a estrutura correta
Vá em Tools >> Compare User Object
Na janela aberta com os objetos, selecione as tabelas ou objetos que deseja comparar. Abaixo há um botão Target Session, clique nele e conecte na Instância que deseja comparar, depois aperte o Botão Compare nesta mesma tela.
Na aba Differences no canto direito há um botão de Show Differences que você pode visualizar as diferenças.Qualquer dúvida coloque aqui.
[]s Ishii
3 de junho de 2009 às 11:40 pm #87134mpvargas
ParticipanteValeu Ishii obrigado.
O que eu preciso fazer é um pouco mais complexo.
Na verdade preciso comparar tabelas com nomes diferentes.
EX: Comparar SRA010 com SRA0203 de junho de 2009 às 11:41 pm #87135David Siqueira
ParticipanteOlá rapaziada, achei isso aqui perdido nas minhas pastas de scripts se alguem achar util e quiser, segue, é um script de comparação de schemas, NA MUNHECA, hehehehe :
PROMPT
PROMPT Schema Comparison
PROMPT =================
PROMPT
PROMPT Run this script while connected to one Oracle schema. Enter the Oracle
PROMPT username, password, and SQL*Net / Net8 service name of a second schema.
PROMPT This script will compare the two schemas and generate a report of
PROMPT differences.
PROMPT
PROMPT A temporary database link and table will be created and dropped by
PROMPT this script.
PROMPTACCEPT schema CHAR PROMPT "Enter username for remote schema: "
ACCEPT passwd CHAR PROMPT "Enter password for remote schema: " HIDE
ACCEPT tnssvc CHAR PROMPT "Enter SQL*Net / Net8 service for remote schema: "PROMPT
ACCEPT report CHAR PROMPT "Enter filename for report output: "
SET FEEDBACK OFF
SET VERIFY OFFCREATE DATABASE LINK rem_schema CONNECT TO &schema IDENTIFIED BY &passwd
USING '&tnssvc';SET TRIMSPOOL ON
SPOOL &report
SELECT SUBSTR (RPAD (TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss'), 25), 1, 25)
"REPORT DATE AND TIME"
FROM SYS.dual;COL local_schema FORMAT a35 TRUNC HEADING "LOCAL SCHEMA"
COL remote_schema FORMAT a35 TRUNC HEADING "REMOTE SCHEMA"SELECT USER || '@' || C.global_name local_schema,
A.username || '@' || B.global_name remote_schema
FROM user_users@rem_schema A, global_name@rem_schema B, global_name C
WHERE ROWNUM = 1;SET PAGESIZE 9999
SET LINESIZE 250
SET FEEDBACK 1SET TERMOUT OFF
PROMPT
REM Object differences
REM ==================COL object_name FORMAT a30
PROMPT SUMMARY OF OBJECTS MISSING FROM LOCAL SCHEMA
SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name)
FROM user_objects@rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name)
FROM user_objects
)
GROUP BY object_type
ORDER BY object_type;PROMPT SUMMARY OF EXTRANEOUS OBJECTS IN LOCAL SCHEMA
SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name)
FROM user_objects
WHERE object_type != 'DATABASE LINK'
OR object_name NOT LIKE 'REM_SCHEMA.%'
MINUS
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name)
FROM user_objects@rem_schema
)
GROUP BY object_type
ORDER BY object_type;PROMPT OBJECTS MISSING FROM LOCAL SCHEMA
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name) object_name
FROM user_objects@rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name) object_name
FROM user_objects
ORDER BY object_type, object_name;PROMPT EXTRANEOUS OBJECTS IN LOCAL SCHEMA
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name) object_name
FROM user_objects
WHERE object_type != 'DATABASE LINK'
OR object_name NOT LIKE 'REM_SCHEMA.%'
MINUS
SELECT object_type,
DECODE (object_type,
'INDEX', DECODE (SUBSTR (object_name, 1, 5),
'SYS_C', 'SYS_C', object_name),
'LOB', DECODE (SUBSTR (object_name, 1, 7),
'SYS_LOB', 'SYS_LOB', object_name),
object_name) object_name
FROM user_objects@rem_schema
ORDER BY object_type, object_name;PROMPT OBJECTS IN LOCAL SCHEMA THAT ARE NOT VALID
SELECT object_name, object_type, status
FROM user_objects
WHERE status != 'VALID'
ORDER BY object_name, object_type;REM Table differences
REM =================PROMPT TABLE COLUMNS MISSING FROM ONE SCHEMA
PROMPT (NOTE THAT THIS REPORT DOES NOT LIST DISCREPENCIES IN COLUMN ORDER)(
SELECT table_name, column_name, 'Local' "MISSING IN SCHEMA"
FROM user_tab_columns@rem_schema
WHERE table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT table_name, column_name, 'Local' "MISSING IN SCHEMA"
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, 'Remote' "MISSING IN SCHEMA"
FROM user_tab_columns
WHERE table_name IN
(
SELECT table_name
FROM user_tables@rem_schema
)
MINUS
SELECT table_name, column_name, 'Remote' "MISSING IN SCHEMA"
FROM user_tab_columns@rem_schema
)
ORDER BY 1, 2;COL schema FORMAT a15
COL nullable FORMAT a8
COL data_type FORMAT a9
COL data_length FORMAT 9999 HEADING LENGTH
COL data_precision FORMAT 9999 HEADING PRECISION
COL data_scale FORMAT 9999 HEADING SCALE
COL default_length FORMAT 9999 HEADING LENGTH_OF_DEFAULT_VALUEPROMPT DATATYPE DISCREPENCIES FOR TABLE COLUMNS THAT EXIST IN BOTH SCHEMAS
(
SELECT table_name, column_name, 'Remote' schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@rem_schema
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns
)
MINUS
SELECT table_name, column_name, 'Remote' schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, 'Local' schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns@rem_schema
)
MINUS
SELECT table_name, column_name, 'Local' schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@rem_schema
)
ORDER BY 1, 2, 3;REM Index differences
REM =================COL column_position FORMAT 999 HEADING ORDER
PROMPT INDEX DISCREPENCIES FOR INDEXES THAT EXIST IN BOTH SCHEMAS
(
SELECT A.index_name, 'Remote' schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@rem_schema A, user_ind_columns@rem_schema B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, 'Remote' schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
UNION ALL
(
SELECT A.index_name, 'Local' schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes@rem_schema
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, 'Local' schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@rem_schema A, user_ind_columns@rem_schema B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
ORDER BY 1, 2, 6;REM Constraint differences
REM ======================PROMPT CONSTRAINT DISCREPENCIES FOR TABLES THAT EXIST IN BOTH SCHEMAS
SET FEEDBACK OFF
CREATE TABLE temp_schema_compare
(
database NUMBER(1),
object_name VARCHAR2(30),
object_text VARCHAR2(2000),
hash_value NUMBER
);DECLARE
CURSOR c1 IS
SELECT constraint_name, search_condition
FROM user_constraints
WHERE search_condition IS NOT NULL;
CURSOR c2 IS
SELECT constraint_name, search_condition
FROM user_constraints@rem_schema
WHERE search_condition IS NOT NULL;
v_constraint_name VARCHAR2(30);
v_search_condition VARCHAR2(32767);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_constraint_name, v_search_condition;
EXIT WHEN c1%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
1, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_constraint_name, v_search_condition;
EXIT WHEN c2%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
2, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c2;
COMMIT;
END;
/SET FEEDBACK 1
(
SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
'9', NULL) constraint_name,
'Remote' schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@rem_schema A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables
)
AND B.database (+) = 2
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
'9', NULL) constraint_name,
'Remote' schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE B.database (+) = 1
AND B.object_name (+) = A.constraint_name
)
UNION ALL
(
SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
'9', NULL) constraint_name,
'Local' schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables@rem_schema
)
AND B.database (+) = 1
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,'012345678','999999999'),
'9', NULL) constraint_name,
'Local' schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@rem_schema A, temp_schema_compare B
WHERE B.database (+) = 2
AND B.object_name (+) = A.constraint_name
)
ORDER BY 1, 4, 2;REM Database link differences
REM =========================PROMPT DATABASE LINK DISCREPENCIES
COL db_link FORMAT a40
(
SELECT db_link, 'Remote' schema, username, host
FROM user_db_links@rem_schema
MINUS
SELECT db_link, 'Remote' schema, username, host
FROM user_db_links
)
UNION ALL
(
SELECT db_link, 'Local' schema, username, host
FROM user_db_links
WHERE db_link NOT LIKE 'REM_SCHEMA.%'
MINUS
SELECT db_link, 'Local' schema, username, host
FROM user_db_links@rem_schema
)
ORDER BY 1, 2;REM Sequence differences
REM ====================PROMPT SEQUENCE DISCREPENCIES
(
SELECT sequence_name, 'Remote' schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@rem_schema
MINUS
SELECT sequence_name, 'Remote' schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
)
UNION ALL
(
SELECT sequence_name, 'Local' schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
MINUS
SELECT sequence_name, 'Local' schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@rem_schema
)
ORDER BY 1, 2;REM Private synonym differences
REM ===========================PROMPT PRIVATE SYNONYM DISCREPENCIES
(
SELECT synonym_name, 'Remote' schema, table_owner, table_name, db_link
FROM user_synonyms@rem_schema
MINUS
SELECT synonym_name, 'Remote' schema, table_owner, table_name, db_link
FROM user_synonyms
)
UNION ALL
(
SELECT synonym_name, 'Local' schema, table_owner, table_name, db_link
FROM user_synonyms
MINUS
SELECT synonym_name, 'Local' schema, table_owner, table_name, db_link
FROM user_synonyms@rem_schema
)
ORDER BY 1, 2;REM PL/SQL differences
REM ==================PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMASSELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, text
FROM user_source@rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, text
FROM user_source
)
UNION ALL
(
SELECT name, type, line, text
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@rem_schema
)
MINUS
SELECT name, type, line, text
FROM user_source@rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS (CASE INSENSITIVE COMPARISON)SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, UPPER (text)
FROM user_source@rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source
)
UNION ALL
(
SELECT name, type, line, UPPER (text)
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@rem_schema
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source@rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;REM Trigger differences
REM ===================PROMPT TRIGGER DISCREPENCIES
SET FEEDBACK OFF
TRUNCATE TABLE temp_schema_compare;
DECLARE
CURSOR c1 IS
SELECT trigger_name, trigger_body
FROM user_triggers;
CURSOR c2 IS
SELECT trigger_name, trigger_body
FROM user_triggers@rem_schema;
v_trigger_name VARCHAR2(30);
v_trigger_body VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c1%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ' ', NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c2%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ' ', NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/SET FEEDBACK 1
(
SELECT A.trigger_name, 'Local' schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
AND A.table_name IN
(
SELECT table_name
FROM user_tables@rem_schema
)
MINUS
SELECT A.trigger_name, 'Local' schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.trigger_name, 'Remote' schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
AND A.table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT A.trigger_name, 'Remote' schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
)
ORDER BY 1, 2, 5, 3;REM View differences
REM ================PROMPT VIEW DISCREPENCIES
SET FEEDBACK OFF
TRUNCATE TABLE temp_schema_compare;
DECLARE
CURSOR c1 IS
SELECT view_name, text
FROM user_views;
CURSOR c2 IS
SELECT view_name, text
FROM user_views@rem_schema;
v_view_name VARCHAR2(30);
v_text VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_view_name, v_text;
EXIT WHEN c1%NOTFOUND;
v_text := REPLACE (v_text, ' ', NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_view_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_view_name, v_text;
EXIT WHEN c2%NOTFOUND;
v_text := REPLACE (v_text, ' ', NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_view_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/SET FEEDBACK 1
(
SELECT A.view_name, 'Local' schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
AND A.view_name IN
(
SELECT view_name
FROM user_views@rem_schema
)
MINUS
SELECT A.view_name, 'Local' schema, B.hash_value
FROM user_views@rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.view_name, 'Remote' schema, B.hash_value
FROM user_views@rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
AND A.view_name IN
(
SELECT view_name
FROM user_views
)
MINUS
SELECT A.view_name, 'Remote' schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
)
ORDER BY 1, 2;REM Job queue differences
REM =====================COL what FORMAT a30
COL interval FORMAT a30PROMPT JOB QUEUE DISCREPENCIES
(
SELECT what, interval, 'Remote' schema
FROM user_jobs@rem_schema
MINUS
SELECT what, interval, 'Remote' schema
FROM user_jobs
)
UNION ALL
(
SELECT what, interval, 'Local' schema
FROM user_jobs
MINUS
SELECT what, interval, 'Local' schema
FROM user_jobs@rem_schema
)
ORDER BY 1, 2, 3;REM Privilege differences
REM =====================PROMPT OBJECT-LEVEL GRANT DISCREPENCIES
(
SELECT owner, table_name, 'Remote' schema, grantee, privilege, grantable
FROM user_tab_privs@rem_schema
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects
)
MINUS
SELECT owner, table_name, 'Remote' schema, grantee, privilege, grantable
FROM user_tab_privs
)
UNION ALL
(
SELECT owner, table_name, 'Local' schema, grantee, privilege, grantable
FROM user_tab_privs
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects@rem_schema
)
MINUS
SELECT owner, table_name, 'Local' schema, grantee, privilege, grantable
FROM user_tab_privs@rem_schema
)
ORDER BY 1, 2, 3;PROMPT SYSTEM PRIVILEGE DISCREPENCIES
(
SELECT privilege, 'Remote' schema, admin_option
FROM user_sys_privs@rem_schema
MINUS
SELECT privilege, 'Remote' schema, admin_option
FROM user_sys_privs
)
UNION ALL
(
SELECT privilege, 'Local' schema, admin_option
FROM user_sys_privs
MINUS
SELECT privilege, 'Local' schema, admin_option
FROM user_sys_privs@rem_schema
)
ORDER BY 1, 2;PROMPT ROLE PRIVILEGE DISCREPENCIES
(
SELECT granted_role, 'Remote' schema, admin_option, default_role, os_granted
FROM user_role_privs@rem_schema
MINUS
SELECT granted_role, 'Remote' schema, admin_option, default_role, os_granted
FROM user_role_privs
)
UNION ALL
(
SELECT granted_role, 'Local' schema, admin_option, default_role, os_granted
FROM user_role_privs
MINUS
SELECT granted_role, 'Local' schema, admin_option, default_role, os_granted
FROM user_role_privs@rem_schema
)
ORDER BY 1, 2;SPOOL OFF
SET TERMOUT ON
PROMPT
PROMPT Report output written to &reportSET FEEDBACK OFF
DROP TABLE temp_schema_compare;
DROP DATABASE LINK rem_schema;SET FEEDBACK 6
SET PAGESIZE 20
SET LINESIZE 80Abração á todos!!!
David
-
AutorPosts
- Você deve fazer login para responder a este tópico.