1] Replication with COPY FROM
This old
utility helps me to copy small tables data_only , using just SQLPLUS
connections :
- First, a REPLACE Version
$ cat
copy_from_sql_COMMUN_REPLACE.sql
-- NO
DATABASE LINK necessary
--
source &1 system/<psw>@<db_name_s>
--
cible &2 system/<psw>@<db_name_c>
--
owner source &3 SCH_S
--
table source &4 <SCH_S>_TABLE01
--
owner target &5 SCH_T
--
table target &6 <SCH_T>_TABLE01
conn
&2
set
serveroutput on
SET
ARRAYSIZE 1000;
SET
COPYCOMMIT 500;
execute
DBMS_APPLICATION_INFO.set_module('COMMUN Replication','Debut copie &4');
execute
DBMS_APPLICATION_INFO.set_client_info('Debut : '||to_char(Sysdate,'DD/MM/YYYY
HH24:MI:SS'));
--
-------------------------------------------
--
COPY FROM Truncate Mode / APPEND vs REPLACE
--
-------------------------------------------
alter
session set current_schema = &5 ;
execute
DBMS_APPLICATION_INFO.set_module('COMMUN Replication','COPY FROM &4');
COPY FROM &1 REPLACE &6 USING select *
from "&3"."&4" ;
--
-----------------
--
CALCUL STATS
--
-----------------
execute
DBMS_APPLICATION_INFO.set_module('COMMUN Replication','CALCUL STATS &6');
--
Error ORA-00600 [15735] While Running A Query In Parallel [ID 1298042.1]
execute
DBMS_STATS.GATHER_TABLE_STATS(
'&5','&6',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED
COLUMNS SIZE AUTO',1,'ALL',TRUE) ;
create
or replace public synonym &6 for &6 ;
grant
select on &6 to PUBLIC ;
execute
DBMS_APPLICATION_INFO.set_client_info('Fin : '||to_char(Sysdate,'DD/MM/YYYY
HH24:MI:SS'));
exit
- Then, a Truncate/Append Version
$ cat copy_from_sql_COMMUN_APPEND.sql
-- NO DATABASE LINK necessary
-- source &1 system/<psw>@<db_name_s>
-- cible &2
system/<psw>@<db_name_c>
-- owner source &3 SCH_S
-- table source &4 <SCH_S>_TABLE01
-- owner target
&5 SCH_T
-- table target
&6 <SCH_T>_TABLE01
conn &2
set serveroutput on
SET ARRAYSIZE
1000;
SET COPYCOMMIT 500;
execute DBMS_APPLICATION_INFO.set_module('COMMUN
Replication','Debut copie &4');
execute DBMS_APPLICATION_INFO.set_client_info('Debut :
'||to_char(Sysdate,'DD/MM/YYYY HH24:MI:SS'));
-- -------------------------------------------
-- COPY FROM Truncate Mode / APPEND vs REPLACE
-- -------------------------------------------
alter session set
current_schema = &5 ;
truncate table
&5..&6 ;
execute
DBMS_APPLICATION_INFO.set_module('COMMUN Replication','COPY FROM &4');
COPY FROM &1 APPEND &6 USING select *
from "&3"."&4" ;
-- -----------------
-- CALCUL STATS
-- -----------------
execute DBMS_APPLICATION_INFO.set_module('COMMUN
Replication','CALCUL STATS &6');
-- Error ORA-00600 [15735] While Running A Query In
Parallel [ID 1298042.1]
execute DBMS_STATS.GATHER_TABLE_STATS(
'&5','&6',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED
COLUMNS SIZE AUTO',1,'ALL',TRUE) ;
create or replace public synonym &6 for &6 ;
grant select on &6 to PUBLIC ;
execute DBMS_APPLICATION_INFO.set_client_info('Fin :
'||to_char(Sysdate,'DD/MM/YYYY HH24:MI:SS'));
exit
- SQLPLUS calling
sqlplus
-s /nolog @${usr_sql_dir}/copy_from_sql_${USc}_${Mod}.sql
system/${system_psw_bds}@${baseS} system/${system_psw_bdc}@${baseC} ${ownerS}
${tableS} ${ownerC} ${tableC} >> ${log}
2] SQL Loader
2.1 How to generate sqlldr Controlfile ?
Get the
Controlfile automatically for a given Table, to load a .csv flat file for
instance.
Taken from dbasupport, thx.
/* GenerateControlFile.sql Generates SQL Loader control file from data
dictionary
>> just remove the command from last but one line << */
select
'LOAD DATA
APPEND
INTO TABLE ' || 'MASTERUSR' ||
' FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(' "Column Name",' ' "sql_loader_type" from dual
union all
select
COLUMN_NAME,
DECODE(DATA_TYPE,
'TIMESTAMP(6)','TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF",',
'NUMBER','DECIMAL EXTERNAL,',
'VARCHAR2','CHAR,',
'CHAR','CHAR',
'DATE','"TO_DATE(SUBSTR(:' || column_name || ',1,19),''YYYY-MM-DD
HH24:MI:SS'')",'
) "sql_loader_type"
from all_tab_cols
where owner=UPPER('OWNER') AND TABLE_NAME = UPPER('MASTERUSR')
union all
select ')' "Column Name" , '' "sql_loader_type" from
dual
