Удаление дубликатов с перебивкой айдишников

Очень часто, при удалении дубликатов из таблицы, оказывается, что на нее уже ссылаются записи из других таблиц. Поэтому перед удалением. нам нужно заменить айдишники в дочерних таблицах, а потом удалять дубликаты. Для этого, я использую следующий скрипт:

DECLARE
   l_id   NUMBER;

   PROCEDURE sp_merge_ids (p_old          IN INTEGER,
                           p_new          IN INTEGER,
                           p_table_name   IN VARCHAR2)
   IS
      v_str   VARCHAR2 (2000)
                 := 'update table set column = :new where column  = :old';
   BEGIN
      FOR rw
         IN (SELECT urcs2.table_name, uccs.column_name
               FROM user_constraints urcs1
               JOIN user_constraints urcs2
                 ON urcs1.constraint_name = urcs2.r_constraint_name
                AND urcs1.table_name = UPPER (p_table_name)
                AND urcs1.constraint_type = 'P'
               JOIN user_cons_columns uccs
                 ON uccs.constraint_name = urcs2.constraint_name)
      LOOP
         EXECUTE IMMEDIATE REPLACE (REPLACE (v_str, 'table', rw.table_name),
                                    'column',
                                    rw.column_name)
            USING p_new, p_old;
      END LOOP;
   END;
BEGIN
   FOR r IN (SELECT employee_id, employee_name
               FROM tbl_employee
              WHERE ROWID NOT IN (SELECT MAX (ROWID)
                                    FROM tbl_employee
                                   GROUP BY employee_name))
   LOOP
      SELECT employee_id
        INTO l_id
        FROM tbl_employee
       WHERE employee_name = r.employee_name
         AND employee_id <> r.employee_id
         AND ROWNUM = 1;

      sp_merge_ids (r.employee_id, l_id, 'tbl_employee');

      DELETE FROM tbl_employee
            WHERE employee_id = r.employee_id;
   END LOOP;
END;
/

В этом скрипте, удаляются дубликаты из таблицы tbl_employee. Сначала в цикле мы определяем записи для удаления. С помощью селекта находим дубликат, который должен остаться и при помощи процедуры sp_merge_ids перебиваем старые айдишники в подчиненных таблицах, на новые. И в заключении удаляем запись.

Ключевое слово PRAGMA

Ключевое слово PRAGMA является директивой, которая передает некоторую управляющую информацию компилятору, но не преобразуется непосредственно в исполняемый код.

PL/SQL поддерживает следующие директивы:

AUTONOMOUS_TRANSACTION — приказывает исполнительному ядру PL/SQL выполнить
сохранение или откат любых изменений, внесенных в базу данных в текущем блоке, без воздействия на главную или внешнюю транзакцию.

EXCEPTION_INIT — приказывает компилятору связать конкретный номер ошибки
с идентификатором, объявленным в программе как исключение. Идентификатор должен соответствовать правилам объявления исключений.

DECLARE
  no_such_sequence EXCEPTION;
  PRAGMA EXCEPTION_INIT (no_such_sequence, –2289);
BEGIN
...
EXCEPTION
  WHEN no_such_sequence
   THEN
      q$error_manager.raise_error ('Sequence not defined');
END;


RESTRICT_REFERENCES — задает для компилятора уровень чистоты программного пакета (отсутствия действий, вызывающих побочные эффекты).

SERIALLY_REUSABLE — сообщает исполнительному ядру PL/SQL, что данные уровни
пакета не должны сохраняться между обращениями к ним.

Команды SQL*Plus

show all -- отображение значений всех переменных
show pagesize --отображение значения переменной pagesize
define x=10 -- присваивание пользовательской переменной Х строкового значения 10
define x -- отображение значения пользовательской переменной Х
select '&x' from dual; -- использование строкового значения переменной Х в запросе
variable x varchar2(10) -- объявление переменной привязки
begin :x := 'hello!'; end; -- присваивание переменной привязки значения
print :x -- вывод значения переменной привязки
spool report -- выходные данные сохраняются в файл report.lst
spool off -- отключение вывода данных в файл
edit -- сохраняет текущее содержимое буфера в файле afiedt.buf и открывает его на редактирование
define _editor = /bin/vi -- установка редактора по умолчанию
SQLPATH -- переменная окружения, в которой можно указать каталоги, где нужно искать скрипты
login.sql -- можно сохранить в нем настройки пользователя и он будет выполняться при каждом подключении к базе, главное чтобы он лежал или в текущем каталоге или в каталоге на который указывает переменная SQLPATH
WHENEVER SQLERROR EXIT SQL.SQLCODE - SQL*Plus прекратит работу, если после выполнения команды сервер базы данных в ответ на команду SQL или PL/SQL вернет сообщение об ошибке
WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK - означает, что перед завершением SQL*Plus будет произведен откат всех не сохраненных изменений данных.

С помощью SQL*Plus можно запускать пакетные программы, задавая в командной
строке аргументы и обращаясь к ним по ссылкам вида &1 (первый аргумент), &2
(второй аргумент) и т. д.

show errors -- отображает список последних ошибок
SHOW ERRORS категория [схема.]объект -- отображение списка последних ошибок по объекту. Например, чтобы просмотреть информацию о последних ошибках в процедуре
wordcount, выполните такую команду: SHOW ERRORS FUNCTION wordcount

Полный список категорий, поддерживаемых этой командой, зависит от версии СУБД,
но в него как минимум входят следующие категории:
DIMENSION
FUNCTION
JAVA SOURCE
JAVA CLASS
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY
VIEW

Вызов функции и присваивание значения переменной привязки:

VARIABLE words NUMBER
CALL wordcount('некоторый_ текст') INTO :words;
PRINT :words

DESCRIBE wordcount -- вывод описания объекта. В нашем случае, это функция wordcount 

Удаление дубликатов строк

Рассмотрим случай, когда у нас есть таблица T_EMP с дубликатами в поле EMP_NO.
Для удаления дубликатов из нее, нужно выполнить следующий запрос:

DELETE
    FROM t_emp
     WHERE rowid NOT IN (SELECT MAX(rowid)
                                                 FROM t_emp
                                               GROUP BY emp_no)

Создание ридонли админского аккаунта

В больших компаниях существует разделение между девелоперами и админами Оракла и девелоперам выдаются очень ограниченные права, что затрудняет поиска проблем.
Если ваше полиси позволяет создание аккаунта для чтения данных из системных таблиц, то его можно создать с помощью следующего скрипта:

CREATE ROLE USER_ADMIN_ROLE
/
GRANT CREATE SESSION TO USER_ADMIN_ROLE
/
GRANT SELECT_CATALOG_ROLE TO USER_ADMIN_ROLE
/
CREATE USER USER_ADMIN IDENTIFIED BY user_admin
/
GRANT USER_ADMIN_ROLE TO USER_ADMIN
/

После этого, можно настроить админский коннекшен в SQL Developer и мониторить состояние базы данных:

Проверка переноса всех строк при экспорте/импорте дампа

Если у вас есть возможность перед сворачиванием дампа, подсчитать количество строк в каждой таблице и типов объектов, то можно использовать следующий скрипт:

DECLARE
  l_cnt NUMBER;
BEGIN
  FOR rw IN (SELECT object_type, count(*) cnt
               FROM user_objects
              GROUP BY object_type
              ORDER BY object_type)
  LOOP
    dbms_output.put_line(rw.object_type || '=' || rw.cnt);
  END LOOP;
  FOR rw IN (SELECT table_name
               FROM user_tables
              ORDER BY table_name)
  LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) l_cnt FROM ' || rw.table_name INTO l_cnt;
    dbms_output.put_line(rw.table_name || '=' || l_cnt);
  END LOOP;
END;
/

После импорта дампа, запускаем данный скрипт на схеме и сверяем с предыдущем запуском.

Конечно результаты можно было бы сохранить в таблицы и проводить сравнение с помощью SQL, но на данном этапе я остановился на текущем варианте и сохраняю результаты в текстовые файлы, а потом сравниваю их обычным компаратором файлов.

Сбор статистики по таблицам

После разворачивания дампа, я использую следующий скрипт для сбора статистики по таблицам:

begin

  for r in (select table_name from user_tables)

  loop

        dbms_stats.unlock_table_stats(user, r.table_name);

        dbms_stats.gather_table_stats(

          ownname => user,

          tabname => r.table_name,

          estimate_percent => 100);

  end loop;

end;

/