Проверка правильности переноса дампа

Очень часто сталкиваюсь с проблемой, когда администраторы разворачивают дамп с неконсистентными данными, при этом теряются констрейнты, форенкеи.......
Для проверки о том что был ли дамп перенесен консистентно, я создал таблицу a$schema_statistics:

declare

  v_table_name   varchar2(30)   := 'a$schema_statistics';

  v_create_table varchar2(2000) := 'create table ' || v_table_name || ' '

                                || '(object_type varchar2(30)'

                                || ',object_name varchar2(4000)'

                                || ',status      varchar2(20))'

                                || 'tablespace itap_data01';

  v_trunc_table  varchar2(200)  := 'truncate table ' || v_table_name;

  v_cnt integer;

begin

  select count(*)

    into v_cnt

    from user_tables

    where table_name = upper(v_table_name);

  execute immediate case v_cnt

                      when 0 then v_create_table

                      else v_trunc_table

                    end;

end;

/

И два представления:

CREATE OR REPLACE FORCE VIEW v_schema_statistics

AS

WITH cons_columns AS

  (SELECT table_name

       || '#'

       || listagg(column_name, ':') WITHIN GROUP (ORDER BY position) cons_name, constraint_name

     FROM user_cons_columns uccs

    GROUP BY table_name

            , constraint_name)

SELECT 'CONSTRAINTS' AS object_type

     , CASE

         WHEN constraint_name LIKE 'SYS%'

         THEN CASE urcs.constraint_type

                 WHEN 'R' THEN urcs.constraint_type

                            || '='

                            || (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

                            || '&'

                            || (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.r_constraint_name)

                 WHEN 'P' THEN urcs.constraint_type

                            || '='

                            || (SELECT cons_name FROM cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

                 WHEN 'U' THEN urcs.constraint_type

                            || '='

                            || (SELECT cons_name from cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

                 WHEN 'C' THEN urcs.constraint_type

                            || '='

                            || (SELECT cons_name from cons_columns cscs WHERE cscs.constraint_name = urcs.constraint_name)

               END

         ELSE urcs.constraint_name

       END object_name

     , urcs.status

  FROM user_constraints urcs

UNION ALL

SELECT 'INDEX' as object_type

     , CASE

         WHEN index_name LIKE 'SYS%'

         THEN CASE index_type

                WHEN 'LOB' then index_type || '=' || uris.table_name

                ELSE (SELECT table_name

                          || '#'

                          || LISTAGG(column_name || '&' || descend, ':') WITHIN GROUP (ORDER BY column_position) index_name

                        FROM user_ind_columns uics

                       WHERE uics.index_name = uris.index_name

                       GROUP BY table_name)

              END

         ELSE index_name

       END object_name

     , uris.status

  FROM user_indexes uris

WHERE uris.index_type <> 'LOB'

UNION ALL

SELECT object_type

     , object_name

     , status

  FROM user_objects

WHERE object_type not in ('LOB', 'INDEX')

   AND ((object_type <> 'TYPE') OR (object_name not LIKE 'SYS%'))

/

CREATE OR REPLACE FORCE VIEW v_schema_statistics_compare

AS

SELECT *

  FROM (SELECT CASE

                 WHEN sass.object_name IS NULL

                   OR vsss.object_name IS NULL   THEN 'NOT MATCHED'

                 WHEN sass.status <> vsss.status THEN 'DIFF STATUS'

                 ELSE 'OK'

               END compare_result

             , sass.object_type AS a$_object_type

             , sass.object_name AS a$_object_name

             , sass.status      AS a$_status

             , vsss.object_type AS sh_object_type

             , vsss.object_name AS sh_object_name

             , vsss.status      AS sh_status

          FROM a$schema_statistics sass

          FULL OUTER JOIN

               v_schema_statistics vsss

            ON sass.object_type = vsss.object_type

           AND sass.object_name = vsss.object_name)

WHERE compare_result <> 'OK'

/

Первое представление v_schema_statistics отображает статистику по текущей схеме, а второе v_schema_statistics_compare сравнивает сохраненную статистику в таблице  a$schema_statistics с текущей статистикой. При каждом обновлении схемы, я запускаю следующий скрипт;

truncate table a$schema_statistics

/

insert

  into a$schema_statistics

     ( object_type

     , object_name

     , status

     )

select object_type

     , object_name

     , status

  from v_schema_statistics

/

commit

/

Он собирает статистику по схеме и сохраняет в таблице a$schema_statistics.

Если мне нужно узнать, целостность перенесенного дампа, я выполняю запрос 

select * from v_schema_statistics_compare

Колонки с префиксом A$ отображают сохраненную статистику, а с префиксом SH текущую статистику по схеме.