Копирование из одной базы в другую с помощью SQL Plus : Oracle

OlegON · █ 10.08.2019 19:05

Начал клонировать базу Супермага, которая подвисает при обращении к системным таблицам даже в read only. В общем, сильно битая. Экспорт невозможен, как и подключение какими-либо другими утилитами.
К сожалению, для меня этот способ не подошел, поскольку выяснилось, что подвисает и часть супермажных таблиц. Но, если кому-то пригодится моя заготовка - пользуйтесь. Это сравнительно неплохой пример возможностей SQL Plus по копированию данных таблиц между двумя базами.

Сначала чистим базу-приемник и выключаем на ней триггеры

SQL код:

 DECLARE
sql_stmt   VARCHAR2 (1000);
BEGIN
FOR c IN (select table_name from (select r.table_name, count(t.table_name) FROM dba_constraints t, dba_constraints r WHERE t.r_constraint_name = r.constraint_name AND t.r_owner = r.owner and r.owner='SUPERMAG' group by r.table_name order by 2) union SELECT table_name FROM dba_tables WHERE owner = 'SUPERMAG' AND TEMPORARY = 'N')
LOOP
begin       
execute immediate 'alter table supermag.'||c.table_name||' disable all triggers';
execute immediate 'delete from supermag.'||c.table_name||' cascade';
commit;
exception when others then dbms_output.put_line(c.table_name);
end;
END LOOP;
END;
/

Затем на ней же готовим скрипт, который после небольших чисток от мусора запускаем на базе-источнике. supermag/qqq@ok, соответственно, не забудьте поменять на нужное

SQL код:

 set serveroutput on
set pagesize 0
set linesize 1000
spool fullc.sql
DECLARE
sql_stmt   VARCHAR2 (1000);
BEGIN
FOR c IN (select table_name from (select r.table_name, count(t.table_name) FROM dba_constraints t, dba_constraints r WHERE t.r_constraint_name = r.constraint_name AND t.r_owner = r.owner and r.owner='SUPERMAG' group by r.table_name order by 2 desc) union SELECT table_name FROM dba_tables WHERE owner = 'SUPERMAG' AND TEMPORARY = 'N')
LOOP
begin       
dbms_output.put_line('copy to supermag/qqq@ok insert supermag.'||c.table_name||' using select * from supermag.'||c.table_name||';');
end;
END LOOP;
END;
/
spool off

OlegON · █ 10.08.2019 19:46

Получение зависимостей таблицы перед копированием

SQL код:

 SELECT 
distinct 'copy to supermag/q@ok insert supermag.'||r.table_name||' using select * from supermag.'||r.table_name||';'
FROM dba_constraints t, dba_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.table_name = 'SMCARD';

пример копирования карточек в базу, прогенерированную для инициализации из офиса

SQL код:

 copy to supermag/q@ok insert supermag.sacardclass using select * from supermag.sacardclass where tree!='#';
copy to supermag/q@ok insert supermag.SALOSSESGROUPS using select * from supermag.SALOSSESGROUPS;
copy to supermag/q@ok insert supermag.SAMARKETINGGROUPS using select * from supermag.SAMARKETINGGROUPS;
copy to supermag/q@ok insert supermag.SAMEASUREMENT using select * from supermag.SAMEASUREMENT where id>2;
copy to supermag/q@ok insert supermag.SAONETORG using select * from supermag.SAONETORG;
copy to supermag/q@ok insert supermag.SASCALES using select * from supermag.SASCALES;
copy to supermag/q@ok insert supermag.SASPIRITCODE using select * from supermag.SASPIRITCODE;
copy to supermag/q@ok insert supermag.SATHREETORG using select * from supermag.SATHREETORG;
copy to supermag/q@ok insert supermag.SMPOSTLOCATIONS using select * from supermag.SMPOSTLOCATIONS;
copy to supermag/q@ok insert supermag.SMCARD using select * from supermag.SMCARD;

OlegON · █ 10.08.2019 19:55

штрихкоды

SQL код:

 SELECT 
distinct 'copy to supermag/q@ok insert supermag.'||r.table_name||' using select * from supermag.'||r.table_name||';'
FROM dba_constraints t, dba_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.table_name = 'SMSTOREUNITS';

SQL код:

 copy to supermag/q@ok insert supermag.SMSTOREBOX using select * from supermag.SMSTOREBOX;
copy to supermag/q@ok insert supermag.SABARCODES using select * from supermag.SABARCODES where id>15;
copy to supermag/q@ok insert supermag.SMARTICLEPACKS using select * from supermag.SMARTICLEPACKS;
copy to supermag/q@ok insert supermag.SMSTOREUNITS using select * from supermag.SMSTOREUNITS;

OlegON · █ 10.08.2019 20:11

Документы (до конца не проверял, CLIENTINFO битая оказалась)...

SQL код:

 SELECT 
distinct 'copy to supermag/q@ok insert supermag.'||r.table_name||' using select * from supermag.'||r.table_name||';'
FROM dba_constraints t, dba_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.table_name = 'SMDOCUMENTS';

copy to supermag/q@ok insert supermag.SMCLIENTINFO using select * from supermag.SMCLIENTINFO;
copy to supermag/q@ok insert supermag.SMCURRENCIES using select * from supermag.SMCURRENCIES;
copy to supermag/q@ok insert supermag.SMSTORELOCATIONS using select * from supermag.SMSTORELOCATIONS;
copy to supermag/q@ok insert supermag.SMUSEROP using select * from supermag.SMUSEROP;
copy to supermag/q@ok insert supermag.SSDOCTYPES using select * from supermag.SSDOCTYPES;

OlegON · █ 10.08.2019 21:30

SQL код:

 copy to supermag/q@ok insert supermag.SAEGAISMARKTYPES using select * from supermag.SAEGAISMARKTYPES;
copy to supermag/q@ok insert supermag.SMCONTROLTASKS using select * from supermag.SMCONTROLTASKS;
copy to supermag/q@ok insert supermag.SMEGAISACOHEADER using select * from supermag.SMEGAISACOHEADER;
copy to supermag/q@ok insert supermag.SMEGAISDOCHEADER using select * from supermag.SMEGAISDOCHEADER;
copy to supermag/q@ok insert supermag.SMEGAISDOCSPEC using select * from supermag.SMEGAISDOCSPEC;
copy to supermag/q@ok insert supermag.SMEGAISFORMAHEADER using select * from supermag.SMEGAISFORMAHEADER;
copy to supermag/q@ok insert supermag.SMEGAISINFORMAREGHEADER using select * from supermag.SMEGAISINFORMAREGHEADER;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABHEADER using select * from supermag.SMEGAISPROCESSEGOABHEADER;
copy to supermag/q@ok insert supermag.SMPROCESS using select * from supermag.SMPROCESS;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGPBHEADER using select * from supermag.SMEGAISPROCESSEGPBHEADER;
copy to supermag/q@ok insert supermag.SMEGAISRESTSHEADER using select * from supermag.SMEGAISRESTSHEADER;
copy to supermag/q@ok insert supermag.SMEGAISRESTSRG3HEADER using select * from supermag.SMEGAISRESTSRG3HEADER;
copy to supermag/q@ok insert supermag.SMEGAISACOSPEC using select * from supermag.SMEGAISACOSPEC;
copy to supermag/q@ok insert supermag.SMEGAISARTICLES using select * from supermag.SMEGAISARTICLES;
copy to supermag/q@ok insert supermag.SMEGAISDOCSPECA using select * from supermag.SMEGAISDOCSPECA;
copy to supermag/q@ok insert supermag.SMEGAISDOCSPECACT using select * from supermag.SMEGAISDOCSPECACT;
copy to supermag/q@ok insert supermag.SMEGAISDOCSPECB using select * from supermag.SMEGAISDOCSPECB;
copy to supermag/q@ok insert supermag.SMEGAISDOCSPECF2 using select * from supermag.SMEGAISDOCSPECF2;
copy to supermag/q@ok insert supermag.SMEGAISFORMAMARK using select * from supermag.SMEGAISFORMAMARK;
copy to supermag/q@ok insert supermag.SMEGAISINFORMAREGMARK using select * from supermag.SMEGAISINFORMAREGMARK;
copy to supermag/q@ok insert supermag.SMEGAISJOBQUERYRESTSPARAM using select * from supermag.SMEGAISJOBQUERYRESTSPARAM;
copy to supermag/q@ok insert supermag.SMEGAISPOSTLOG using select * from supermag.SMEGAISPOSTLOG;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABHEADER using select * from supermag.SMEGAISPROCESSEGOABHEADER;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABPIECERESTS using select * from supermag.SMEGAISPROCESSEGOABPIECERESTS;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABRESTS using select * from supermag.SMEGAISPROCESSEGOABRESTS;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABSMRESTS using select * from supermag.SMEGAISPROCESSEGOABSMRESTS;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGOABSPEC using select * from supermag.SMEGAISPROCESSEGOABSPEC;
copy to supermag/q@ok insert supermag.SMEGAISPROCESSEGPBSPEC using select * from supermag.SMEGAISPROCESSEGPBSPEC;
copy to supermag/q@ok insert supermag.SMEGAISRESTS using select * from supermag.SMEGAISRESTS;
copy to supermag/q@ok insert supermag.SMEGAISRESTSLOG using select * from supermag.SMEGAISRESTSLOG;
copy to supermag/q@ok insert supermag.SMEGAISRESTSPIECE using select * from supermag.SMEGAISRESTSPIECE;
copy to supermag/q@ok insert supermag.SMEGAISRESTSRG3 using select * from supermag.SMEGAISRESTSRG3;