Pular para o conteúdo
Visualizando 15 posts - 1 até 15 (de 19 do total)
  • Autor
    Posts
  • #87109
    mpvargas
    Participante

      Caros 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.

      #87112
      David Siqueira
      Participante

        E 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

        #87113
        mpvargas
        Participante

          Achei 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,4

          Mas como fazer para ler tabelas de schemas diferentes?

          #87115
          eversonpiza
          Participante

            mpvargas,

            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,
            Everson

            #87117
            mpvargas
            Participante

              Você pode me passar um exemplo de como criar um dblink.
              Para acessar uma Tabela X, por exemplo.
              Obrigado.

              #87119
              marlontk
              Participante

                Criaçã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 dados

                nos 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 b

                #87122
                Avatar photoRegis Araujo
                Participante

                  Opa..

                  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…

                  #87123
                  David Siqueira
                  Participante

                    Boa Regis.
                    o Esquema é esse mesmo.

                    Abraço.

                    #87125
                    mpvargas
                    Participante

                      OK
                      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.

                      #87128
                      marlontk
                      Participante

                        Realmente 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.

                        #87131
                        mpvargas
                        Participante

                          Eu tenho o Oracle SQL Developer, será que é o mesmo?
                          Onde tem a opção de comparar as tabelas?
                          Obrigado

                          #87132
                          mpvargas
                          Participante

                            Marlontk
                            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.
                            Obrigado

                            #87133
                            Ishii
                            Participante

                              Olá,

                              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

                              #87134
                              mpvargas
                              Participante

                                Valeu Ishii obrigado.
                                O que eu preciso fazer é um pouco mais complexo.
                                Na verdade preciso comparar tabelas com nomes diferentes.
                                EX: Comparar SRA010 com SRA020

                                #87135
                                David Siqueira
                                Participante

                                  Olá 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.
                                  PROMPT

                                  ACCEPT 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 OFF

                                  CREATE 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 1

                                  SET 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_VALUE

                                  PROMPT 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 SCHEMAS

                                  SELECT 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 a30

                                  PROMPT 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 &report

                                  SET FEEDBACK OFF

                                  DROP TABLE temp_schema_compare;
                                  DROP DATABASE LINK rem_schema;

                                  SET FEEDBACK 6
                                  SET PAGESIZE 20
                                  SET LINESIZE 80

                                  Abração á todos!!!

                                  David

                                Visualizando 15 posts - 1 até 15 (de 19 do total)
                                • Você deve fazer login para responder a este tópico.
                                plugins premium WordPress