Re: 오늘의 마지막 문제. prod 쪽에 hr2 계정을 생성하고 orcl 쪽의 hr 계정의...

[18기_오운학]

create user hr2

identified by hr2;

grant connect, resource to hr2;

create public database link hr_link

connect to hr

identified by hr

using '192.168.19.199:1521/orcl.us.oracle.com';

select  'CREATE TABLE ' || TABLE_NAME || ' as select * from '|| TABLE_NAME || '@hr_link where 1=2'

from user_tables;

CREATE TABLE COUNTRIES as select * from COUNTRIES@hr_link where 1=2;

CREATE TABLE EMPLOYEES as select * from EMPLOYEES@hr_link where 1=2;

CREATE TABLE REGIONS as select * from REGIONS@hr_link where 1=2;

CREATE TABLE DEPARTMENTS as select * from DEPARTMENTS@hr_link where 1=2;

CREATE TABLE LOCATIONS as select * from LOCATIONS@hr_link where 1=2;

CREATE TABLE JOB_HISTORY as select * from JOB_HISTORY@hr_link where 1=2;

CREATE TABLE JOBS as select * from JOBS@hr_link where 1=2;

select

'insert /*+ append */into '|| TABLE_NAME || ' select * from ' ||TABLE_NAME|| '@hr_link;'

from user_tables;

insert /*+ append */into COUNTRIES select * from COUNTRIES@hr_link;

commit;

insert /*+ append */into EMPLOYEES select * from EMPLOYEES@hr_link;

insert /*+ append */into REGIONS select * from REGIONS@hr_link;

insert /*+ append */into DEPARTMENTS select * from DEPARTMENTS@hr_link;

insert /*+ append */into LOCATIONS select * from LOCATIONS@hr_link;

insert /*+ append */into JOB_HISTORY select * from JOB_HISTORY@hr_link;

insert /*+ append */into JOBS select * from JOBS@hr_link;