[Oracle] DB Link를 이용한 데이터 이행2 (HR 계정)

1. dw 쪽에서 db 링크를 생성합니다.

create public database link asis_hr_link
connect  to hr 
identified by hr
using '192.168.13.69:21533/ORA19';

select table_name
 from user_tables@asis_hr_link;

데이터 이행 스크립트를 생성합니다.

insert /*+ parallel(new_table,4) */ into  REGIONS      new_table  select /*+ parallel(old_table,4) */ * from    REGIONS@asis_hr_link       old_table;
insert /*+ parallel(new_table,4) */ into  LOCATIONS    new_table  select /*+ parallel(old_table,4) */ * from 	LOCATIONS@asis_hr_link     old_table;
insert /*+ parallel(new_table,4) */ into  DEPARTMENTS  new_table  select /*+ parallel(old_table,4) */ * from 	DEPARTMENTS@asis_hr_link   old_table;
insert /*+ parallel(new_table,4) */ into  JOBS         new_table  select /*+ parallel(old_table,4) */ * from 	JOBS@asis_hr_link          old_table;
insert /*+ parallel(new_table,4) */ into  EMPLOYEES    new_table  select /*+ parallel(old_table,4) */ * from 	EMPLOYEES@asis_hr_link     old_table;
insert /*+ parallel(new_table,4) */ into  JOB_HISTORY  new_table  select /*+ parallel(old_table,4) */ * from 	JOB_HISTORY@asis_hr_link   old_table;
insert /*+ parallel(new_table,4) */ into  COUNTRIES    new_table  select /*+ parallel(old_table,4) */ * from 	COUNTRIES@asis_hr_link     old_table;

.

select 'insert /*+ parallel(new_table,4) */ into ' || table_name
       || ' new_table select /*+ parallel(old_table,4) */ * from  ' || table_name 
       || '@asis_hr_link old_table;'
   from user_tables;

.

truncate table DEPARTMENTS;
truncate table EMPLOYEES;
truncate table JOBS;
truncate table JOB_HISTORY;
truncate table LOCATIONS;
truncate table REGIONS;
truncate table COUNTRIES;

alter session enable  parallel dml;

insert /*+ parallel(new_table,4) */ into DEPARTMENTS new_table select /*+ parallel(old_table,4) */ * from  DEPARTMENTS@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into EMPLOYEES new_table select /*+ parallel(old_table,4) */ * from  EMPLOYEES@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into JOBS new_table select /*+ parallel(old_table,4) */ * from  JOBS@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into JOB_HISTORY new_table select /*+ parallel(old_table,4) */ * from  JOB_HISTORY@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into LOCATIONS new_table select /*+ parallel(old_table,4) */ * from  LOCATIONS@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into REGIONS new_table select /*+ parallel(old_table,4) */ * from  REGIONS@asis_hr_link old_table;
commit;

insert /*+ parallel(new_table,4) */ into COUNTRIES new_table select /*+ parallel(old_table,4) */ * from  COUNTRIES@asis_hr_link old_table;
commit;

exec dbms_stats.gather_schema_stats('HR2');

select table_name, num_rows, last_analyzed
 from user_tables
 order by num_rows desc;

---

인덱스를 생성합니다.

1. 먼저 인덱스를 저장하기 위한 테이블 스페이스를 생성합니다.

create  tablespace  hr_index_ts
  datafile  '/home/oracle/hr_index_ts01.dbf' size 1024m;

2. 인덱스 생성 스크립트를 수정합니다.

메모장 열고

--- 2. 인덱스 생성 스크립트 

CREATE UNIQUE INDEX HR2.COUNTRY_C_ID_PK
ON HR2.COUNTRIES (COUNTRY_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.DEPT_ID_PK
ON HR2.DEPARTMENTS (DEPARTMENT_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.DEPT_LOCATION_IX
ON HR2.DEPARTMENTS (LOCATION_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.EMP_DEPARTMENT_IX
ON HR2.EMPLOYEES (DEPARTMENT_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.EMP_EMAIL_UK
ON HR2.EMPLOYEES (EMAIL) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.EMP_EMP_ID_PK
ON HR2.EMPLOYEES (EMPLOYEE_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.EMP_JOB_IX
ON HR2.EMPLOYEES (JOB_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.EMP_MANAGER_IX
ON HR2.EMPLOYEES (MANAGER_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.EMP_NAME_IX
ON HR2.EMPLOYEES (LAST_NAME,FIRST_NAME) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.JHIST_DEPARTMENT_IX
ON HR2.JOB_HISTORY (DEPARTMENT_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.JHIST_EMPLOYEE_IX
ON HR2.JOB_HISTORY (EMPLOYEE_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.JHIST_EMP_ID_ST_DATE_PK
ON HR2.JOB_HISTORY (EMPLOYEE_ID,START_DATE) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.JHIST_JOB_IX
ON HR2.JOB_HISTORY (JOB_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.JOB_ID_PK
ON HR2.JOBS (JOB_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.LOC_CITY_IX
ON HR2.LOCATIONS (CITY) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.LOC_COUNTRY_IX
ON HR2.LOCATIONS (COUNTRY_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.LOC_ID_PK
ON HR2.LOCATIONS (LOCATION_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE INDEX HR2.LOC_STATE_PROVINCE_IX
ON HR2.LOCATIONS (STATE_PROVINCE) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

CREATE UNIQUE INDEX HR2.REG_ID_PK
ON HR2.REGIONS (REGION_ID) 
TABLESPACE HR_INDEX_TS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)  nologging parallel 4 ;

갯수 맞는지 확인

select count(*)
 from user_indexes;
 
 
select count(*)
 from user_indexes@asis_hr_link;
 
 
select 'alter index  ' || lower(index_name) || ' logging parallel 1;'
  from  user_indexes;

.

alter index  country_c_id_pk logging parallel 1;
alter index  dept_id_pk logging parallel 1;
alter index  dept_location_ix logging parallel 1;
alter index  emp_department_ix logging parallel 1;
alter index  emp_email_uk logging parallel 1;
alter index  emp_emp_id_pk logging parallel 1;
alter index  emp_job_ix logging parallel 1;
alter index  emp_manager_ix logging parallel 1;
alter index  emp_name_ix logging parallel 1;
alter index  job_id_pk logging parallel 1;
alter index  jhist_department_ix logging parallel 1;
alter index  jhist_employee_ix logging parallel 1;
alter index  jhist_emp_id_st_date_pk logging parallel 1;
alter index  jhist_job_ix logging parallel 1;
alter index  loc_city_ix logging parallel 1;
alter index  loc_country_ix logging parallel 1;
alter index  loc_id_pk logging parallel 1;
alter index  loc_state_province_ix logging parallel 1;
alter index  reg_id_pk logging parallel 1;

select index_name, degree
 from user_indexes;