오라클 테이블 복사 프로시져
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
오라클 테이블 간 데이터 복사 프로시져[편집]
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
/* =============================================================================
RTIS_DBA 계정에 select any table 권한 필요: 없을 경우 다계정에 대해 NO_COLUMN_LIST 발생
create table RTIS_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 RTIS_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 RTIS_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;