오라클 테이블 복사 프로시져
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 테이블 간 데이터 복사 프로시져[편집]
1.1 sp_dbm_copy[편집]
CREATE OR REPLACE PROCEDURE sp_dbm_copy
( p_tname in varchar2 default null
, p_dblink in varchar2 default null
, p_option in varchar2 default 'I' -- I:insert, R:replace(truncate), A:append
, p_exec in number default 0
, p_owner in varchar2 default ''
, p_srcowner in varchar2 default null
, p_srctname in varchar2 default null
) authid current_user
is
/* =============================================================================
XXX_DBA 계정에 select any table 권한 필요: 없을 경우 다계정에 대해 NO_COLUMN_LIST 발생
create table XXX_DBA.tb_dbm_copy_log
(
logdt date not null, -- 작업일자
owner varchar2 (100) not null, -- 테이블 소유자
tname varchar2 (100) not null, -- 테이블명
dblink varchar2 (100), -- DB LINK
sowner varchar2 (100), -- 원본 테이블 소유자
stname varchar2 (100), -- 원본 테이블
coption varchar2 (10), -- 옵션(I:Insert, A:Append, R:Truncate and Insert)
result varchar2 (5), -- 결과(S:Sucess, F:Fail)
rowcount number, -- 건수
errmsg varchar2 (300), -- 에러메시지
constraint pk_dbm_copy_log primary key (logdt, owner, tname)
);
============================================================================= */
NO_COLUMN_LIST EXCEPTION;
NO_PRIMARYKEY EXCEPTION;
v_tgt_tname varchar2(50) default null;
v_tgt_owner varchar2(30) default null;
v_src_owner varchar2(30) default null;
v_src_tname varchar2(50) default null;
v_option varchar2(2);
type typ_rec_cols is record
(colid user_tab_columns.column_id%TYPE, colname all_tab_columns.column_name%TYPE);
type typ_tab_cols is table of typ_rec_cols;
tab_cols typ_tab_cols;
v_dblink varchar2(100) default ' ';
v_sqltxt long;
v_sqlwhere varchar2(1000);
v_colist long;
v_msg long;
v_rowcnt number(10) default 0;
V_NL char(1) default chr(10);
V_DELIM varchar(10) default ',';
begin
if p_tname is null then
dbms_output.enable(100);
dbms_output.put_line('usage: sp_dbm_copy( ');
dbms_output.put_line(' p_tname => null ');
dbms_output.put_line(' , p_dblink => null ');
dbms_output.put_line(' , p_option => ''i'' -- I:insert, R:replace(truncate), A:append');
dbms_output.put_line(' , p_exec => 0 ');
dbms_output.put_line(' , p_owner => null ');
dbms_output.put_line(' , p_srcowner => null ');
dbms_output.put_line(' , p_srctname => null)');
dbms_output.put_line(' p_option=I:Insert, R:Replace(truncate and insert), A:Append');
return;
end if;
v_tgt_owner := upper(p_owner);
v_tgt_tname := upper(p_tname);
v_option := upper(p_option);
if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if;
if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if;
if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if;
------------------------------------------------------------------
v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'(';
if p_exec <> 1 then
dbms_output.enable(1000);
if v_option = 'R' then dbms_output.put_line('truncate table '||v_tgt_owner||'.'||v_tgt_tname||';'); end if;
dbms_output.put_line(v_sqltxt);
end if;
-- column list
execute immediate
'select t.column_id, t.column_name
from all_tab_columns t, all_tab_columns'||v_dblink|| ' s '||
' where t.owner = :1
and t.table_name = :2
and s.owner = :3
and s.table_name = :4
and t.column_name=s.column_name
order by t.column_id'
bulk collect into tab_cols
using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname;
if SQL%ROWCOUNT<1 then raise NO_COLUMN_LIST; end if;
for i in tab_cols.first..tab_cols.last
loop
if p_exec=1 then
if i=tab_cols.first then
v_colist := trim(tab_cols(i).colname);
else
v_colist := v_colist ||V_NL||V_DELIM|| trim(tab_cols(i).colname);
end if;
else
if i=tab_cols.first then
dbms_output.enable(1000); dbms_output.put_line(' '||tab_cols(i).colname);
else
dbms_output.enable(1000); dbms_output.put_line(' ,'||tab_cols(i).colname);
end if;
end if;
end loop;
if v_option = 'A' then
for icur in (select l.column_name, l.position from dba_cons_columns l, dba_constraints c
where l.owner=c.owner and l.constraint_name=c.constraint_name
and c.constraint_type='P'
and c.owner=v_tgt_owner and c.table_name=v_tgt_tname
order by position)
loop
if icur.position = 1 then
-- v_sqlwhere := icur.column_name;
v_sqlwhere := 'B.'||icur.column_name||' = A.'||icur.column_name;
else
--v_sqlwhere := v_sqlwhere||', '||icur.column_name;
v_sqlwhere := v_sqlwhere||' AND B.'||icur.column_name||' = A.'||icur.column_name;
end if;
end loop;
if v_sqlwhere is null then raise NO_PRIMARYKEY; end if;
-- v_sqlwhere := 'WHERE ('||v_sqlwhere||') NOT IN ( SELECT '||v_sqlwhere||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')';
v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' B WHERE '||v_sqlwhere||')';
end if;
if p_exec=1 then
if v_option = 'R' then execute immediate 'truncate table '||v_tgt_owner||'.'||v_tgt_tname; end if;
v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('|| v_colist ||')'||V_NL
||'SELECT '||v_colist||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A ';
if v_option = 'A' then v_sqltxt := v_sqltxt ||chr(10)||v_sqlwhere; end if;
begin
execute immediate v_sqltxt;
v_rowcnt := to_char(SQL%ROWCOUNT);
v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(v_rowcnt,10,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink;
commit;
--(--------------------------------------------------------------------------------------------------------
execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, rowcount)'
||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)'
using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'S', to_number(v_rowcnt);
commit;
--)--------------------------------------------------------------------------------------------------------
exception
when others then
rollback;
--(--------------------------------------------------------------------------------------------------------
execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, errmsg)'
||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)'
using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'F', substr(SQLERRM,1,250) ;
commit;
--)--------------------------------------------------------------------------------------------------------
v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
dbms_output.enable(100);
dbms_output.put_line(v_msg);
end;
dbms_output.enable(2000);
dbms_output.put_line(v_msg);
else
dbms_output.enable(1000);
dbms_output.put_line(' )');
dbms_output.put_line(' SELECT ');
for i in tab_cols.first..tab_cols.last
loop
if i=tab_cols.first then
dbms_output.enable(1000); dbms_output.put_line(' '||tab_cols(i).colname);
else
dbms_output.enable(1000); dbms_output.put_line(' ,'||tab_cols(i).colname);
end if;
end loop;
dbms_output.put_line(' FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A ');
if v_option = 'A' then dbms_output.put_line(v_sqlwhere); end if;
dbms_output.put_line(';');
end if;
exception
when NO_COLUMN_LIST then
v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname;
dbms_output.enable(100);
dbms_output.put_line(v_msg);
when NO_PRIMARYKEY then
v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname;
dbms_output.enable(100);
dbms_output.put_line(v_msg);
when others then
v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
dbms_output.enable(100);
dbms_output.put_line(v_msg);
end;1.2 sp_dbw_copy[편집]
CREATE OR REPLACE PROCEDURE sp_dbw_copy
( p_tname in varchar2 default null
, p_dblink in varchar2 default null
, p_option in varchar2 default 'I' -- I,R,DI,M,AI,AX
, p_exec in number default 0
, p_owner in varchar2 default null
, p_srcowner in varchar2 default null
, p_srctname in varchar2 default null
)
is
NO_COLUMN_LIST EXCEPTION;
NO_PRIMARYKEY EXCEPTION;
v_tgt_tname varchar2(50) default null;
v_tgt_owner varchar2(30) default null;
v_src_owner varchar2(30) default null;
v_src_tname varchar2(50) default null;
v_option varchar2(2);
type typ_rec_cols is record
(colid user_tab_columns.column_id%TYPE, colname all_tab_cols.column_name%TYPE, pno number);
type typ_tab_cols is table of typ_rec_cols;
tab_cols typ_tab_cols;
v_dblink varchar2(100) default ' ';
v_sql_select varchar2(1000) default ' ';
v_sql_dataRemove varchar2(1000) default ' ';
v_sqlwhere varchar2(4000);
v_sql_copyData long;
v_colist long;
v_keycols varchar2(4000);
v_merge_join varchar2(4000);
v_merge_update varchar2(4000);
v_merge_insert varchar2(4000);
v_msg varchar(4000);
v_rowcnt number(15) default 0;
v_start_dtm date;
v_secs varchar2(10);
V_NL char(1) default chr(10); /* new line */
V_TAB char(4) default ' '; /* tab */
V_DELIM varchar2(10) default ','; /* delimter */
begin
v_tgt_owner := upper(p_owner);
v_tgt_tname := upper(p_tname);
v_option := upper(p_option);
if p_tname is null or v_option not in ('I','R','DI','M','AI','AX')
then
v_msg :=
V_NL||'usage: '
||V_NL||' exec sp_dbw_copy('
||V_NL||' p_tname => ''table_name'' '
||V_NL||' , p_dblink => null '
||V_NL||' , p_option => ''i'' -- I/R/DI/M/AI/AX '
||V_NL||' , p_exec => 0 '
||V_NL||' , p_owner => '''' '
||V_NL||' , p_srcowner => null '
||V_NL||' , p_srctname => null); '
||V_NL||' p_option=> I:Insert, R:Replace(truncate and insert), DI:Delete Insert '
||V_NL||' , M:Merge, AI:Append(not In), AX:Append(not eXists) '
;
dbms_output.enable(500);
dbms_output.put_line(v_msg);
return;
end if;
if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if;
if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if;
if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if;
if v_option = 'R' then
v_sql_dataRemove := 'truncate table '||v_tgt_owner||'.'||v_tgt_tname ;
elsif v_option = 'DI' then
v_sql_dataRemove := 'delete from '||v_tgt_owner||'.'||v_tgt_tname;
end if;
-- column list
v_sql_select := 'select tgt.column_id, tgt.column_name, p.position pno '
||V_NL||' from all_tab_cols tgt '
||V_NL||' join all_tab_cols'||v_dblink||' src '
||V_NL||' on (src.column_name = tgt.column_name) '
||V_NL||' left join all_constraints c '
||V_NL||' on (c.owner = tgt.owner and c.table_name = tgt.table_name and c.constraint_type = ''P'') '
||V_NL||' left join all_cons_columns p '
||V_NL||' on (p.owner = c.owner and p.table_name = c.table_name and p.constraint_name = c.constraint_name '
||V_NL||' and p.owner = tgt.owner and p.table_name = tgt.table_name and p.column_name = tgt.column_name) '
||V_NL||' where tgt.owner = :1 '
||V_NL||' and tgt.table_name = :2 '
||V_NL||' and src.owner = :3 '
||V_NL||' and src.table_name = :4 '
||V_NL||' and tgt.virtual_column <> ''YES'' '
||V_NL||' order by tgt.column_id '
;
execute immediate v_sql_select
bulk collect into tab_cols
using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname;
if SQL%ROWCOUNT < 1 then
dbms_output.enable(10000);
dbms_output.put_line(v_sql_select);
raise NO_COLUMN_LIST;
end if;
for i in tab_cols.first..tab_cols.last
loop
/* insert column list */
if i=tab_cols.first then
v_colist := V_TAB||' SRC.'||trim(tab_cols(i).colname);
else
v_colist := v_colist ||V_NL||V_TAB||V_DELIM||' SRC.'|| trim(tab_cols(i).colname);
end if;
/* merge on clause, update set clause */
if tab_cols(i).pno is not null then
v_merge_join := v_merge_join||' AND SRC.'||tab_cols(i).colname||' = TGT.'||tab_cols(i).colname;
v_keycols := v_keycols ||', '||tab_cols(i).colname;
else
v_merge_update := v_merge_update||', TGT.'||tab_cols(i).colname||' = SRC.'||tab_cols(i).colname||V_NL||V_TAB;
end if;
end loop;
v_merge_join := substr(v_merge_join, 5, 10000);
v_keycols := substr(v_keycols, 2,10000);
v_merge_update := substr(v_merge_update, 2, 10000);
if v_option in ('M','AI', 'AX') then
if v_merge_join is null then raise NO_PRIMARYKEY; end if;
if v_option = 'AI' then
v_sqlwhere := 'WHERE ('||v_keycols||') NOT IN ( SELECT '||v_keycols||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')';
elsif v_option = 'AX' then
v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' TGT WHERE '||v_merge_join||')';
end if;
end if;
if v_option = 'M' then
v_sql_copyData := 'MERGE INTO '||v_tgt_owner||'.'||v_tgt_tname||' TGT '
||V_NL||'USING '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC '
||V_NL||' ON ('||v_merge_join||' ) '
||V_NL||'WHEN MATCHED THEN UPDATE '
||V_NL||' SET '
||V_NL||' '||v_merge_update
||V_NL||'WHEN NOT MATCHED THEN '
||V_NL||' INSERT ('
||V_NL|| replace(replace(v_colist, ' SRC.',' '), V_TAB, V_TAB||V_TAB)||') '
||V_NL||' VALUES ('
||V_NL||replace(v_colist, V_TAB, V_TAB||V_TAB)||')'
;
else
v_sql_copyData := 'INSERT '|| case when v_option in ('R','AI','AX') then '/*+ APPEND */' end
||V_NL||' INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('
||V_NL|| replace(v_colist , ' SRC.', ' ')
||V_NL||')'
||V_NL||'SELECT /*+ PARALLEL(SRC 1) */'
||V_NL||v_colist
||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC ';
if v_option IN ('AI', 'AX') then v_sql_copyData := v_sql_copyData ||chr(10)||v_sqlwhere; end if;
end if;
if p_exec=1 then
begin
select sysdate into v_start_dtm from dual;
if v_option in ('R','DI') then execute immediate v_sql_dataRemove; end if;
execute immediate v_sql_copyData;
v_rowcnt := SQL%ROWCOUNT;
v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(to_char(v_rowcnt, 'fm999,999,999,999'),15,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink;
commit;
--(--------------------------------------------------------------------------------------------------------
select to_char(trunc(tm /3600), 'fm00')
||':'||to_char(mod(trunc(tm/60),60),'fm00')
||':'||to_char(mod(tm,60), 'fm00')
into v_secs
from (select (sysdate - v_start_dtm) * 24 * 60 * 60 tm from dual)
;
insert into XXX_DBA.tb_dbw_copy_log
(logid, owner, table_name, result, rowcount
, src_owner, src_table_name, dblink, copyopt, start_time, end_time, errmsg)
values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', to_number(v_rowcnt)
, v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate, v_secs);
commit;
--)--------------------------------------------------------------------------------------------------------
exception when others then
v_msg := SQLERRM;
rollback;
--(--------------------------------------------------------------------------------------------------------
insert into XXX_DBA.tb_dbw_copy_log
(logid, owner, table_name, result, errmsg
, src_owner, src_table_name, dblink, copyopt, start_time, end_time)
values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', substr(v_msg, 1, 250)
, v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate);
commit;
--)--------------------------------------------------------------------------------------------------------
v_msg := 'ERROR : '|| substr(v_msg,1,500);
dbms_output.enable(1000);
dbms_output.put_line(v_msg);
end;
dbms_output.enable(1000);
dbms_output.put_line(v_msg);
else
dbms_output.enable(10000);
dbms_output.put_line('');
if v_option in ('R','DI') then dbms_output.put_line(v_sql_dataRemove||';'); end if;
dbms_output.put_line(v_sql_copyData||';');
end if;
exception
when NO_COLUMN_LIST then
v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname;
dbms_output.enable(100);
dbms_output.put_line(v_msg);
when NO_PRIMARYKEY then
v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname;
dbms_output.enable(100);
dbms_output.put_line(v_msg);
when others then
v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
dbms_output.enable(100);
dbms_output.put_line(v_msg);
end;
/