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

봄·2025년 9월 10일
0

오라클 관리

목록 보기
126/163

1. 제약을 생성합니다.

ALTER TABLE HR2.COUNTRIES
ADD CONSTRAINT COUNTRY_ID_NN CHECK ("COUNTRY_ID" IS NOT NULL);

ALTER TABLE HR2.DEPARTMENTS
ADD CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_EMAIL_NN CHECK ("EMAIL" IS NOT NULL);

ALTER TABLE HR2.COUNTRIES
ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID);

ALTER TABLE HR2.DEPARTMENTS
ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID);

ALTER TABLE HR2.COUNTRIES
ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY (REGION_ID) REFERENCES HR2.REGIONS (REGION_ID);

ALTER TABLE HR2.DEPARTMENTS
ADD CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES HR2.LOCATIONS (LOCATION_ID);

ALTER TABLE HR2.DEPARTMENTS
ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES HR2.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES HR2.DEPARTMENTS (DEPARTMENT_ID);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_HIRE_DATE_NN CHECK ("HIRE_DATE" IS NOT NULL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_JOB_NN CHECK ("JOB_ID" IS NOT NULL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_LAST_NAME_NN CHECK ("LAST_NAME" IS NOT NULL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_DATE_INTERVAL CHECK (end_date > start_date);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_EMPLOYEE_NN CHECK ("EMPLOYEE_ID" IS NOT NULL);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES HR2.JOBS (JOB_ID);

ALTER TABLE HR2.EMPLOYEES
ADD CONSTRAINT EMP_MANAGER_FK FOREIGN KEY (MANAGER_ID) REFERENCES HR2.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES HR2.DEPARTMENTS (DEPARTMENT_ID);

ALTER TABLE HR2.JOBS
ADD CONSTRAINT JOB_TITLE_NN CHECK ("JOB_TITLE" IS NOT NULL);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_END_DATE_NN CHECK ("END_DATE" IS NOT NULL);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_JOB_NN CHECK ("JOB_ID" IS NOT NULL);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_START_DATE_NN CHECK ("START_DATE" IS NOT NULL);

ALTER TABLE HR2.LOCATIONS
ADD CONSTRAINT LOC_CITY_NN CHECK ("CITY" IS NOT NULL);

ALTER TABLE HR2.JOBS
ADD CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY (EMPLOYEE_ID,START_DATE);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_EMP_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES HR2.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE HR2.JOB_HISTORY
ADD CONSTRAINT JHIST_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES HR2.JOBS (JOB_ID);

ALTER TABLE HR2.LOCATIONS
ADD CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID) REFERENCES HR2.COUNTRIES (COUNTRY_ID);

ALTER TABLE HR2.REGIONS
ADD CONSTRAINT REGION_ID_NN CHECK ("REGION_ID" IS NOT NULL);

ALTER TABLE HR2.LOCATIONS
ADD CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID);

ALTER TABLE HR2.REGIONS
ADD CONSTRAINT REG_ID_PK PRIMARY KEY (REGION_ID);


select count(*)   -- 46
 from user_constraints;
 
SELECT 34+17 FROM DUAL;
 
select count(*)  --34
 from user_constraints@asis_hr_link;

나머지 시퀀스, 뷰, 트리거를 생성합니다.


CREATE SEQUENCE HR2.DEPARTMENTS_SEQ
START WITH 280
INCREMENT BY 10
MINVALUE 1
MAXVALUE 9990
NOCACHE;

CREATE SEQUENCE HR2.EMPLOYEES_SEQ
START WITH 207
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCACHE;

CREATE SEQUENCE HR2.LOCATIONS_SEQ
START WITH 3300
INCREMENT BY 100
MINVALUE 1
MAXVALUE 9900
NOCACHE;

CREATE OR REPLACE VIEW HR2.EMP_DETAILS_VIEW
(
    EMPLOYEE_ID,
    JOB_ID,
    MANAGER_ID,
    DEPARTMENT_ID,
    LOCATION_ID,
    COUNTRY_ID,
    FIRST_NAME,
    LAST_NAME,
    SALARY,
    COMMISSION_PCT,
    DEPARTMENT_NAME,
    JOB_TITLE,
    CITY,
    STATE_PROVINCE,
    COUNTRY_NAME,
    REGION_NAME
)
AS
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY;

CREATE OR REPLACE PROCEDURE HR2.add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/

CREATE OR REPLACE PROCEDURE HR2.secure_dml
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
	RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END secure_dml;
/


CREATE OR REPLACE TRIGGER HR2.secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/

.

select count(*)
 from user_sequences;
 

select count(*)
 from user_sequences@asis_hr_link;
 

select count(*)
 from user_views;
 
select count(*)
from user_views@asis_hr_link;


select count(*)
 from user_triggers;
 
select count(*)
 from user_triggers@asis_hr_link;
 
select count(*)
 from user_procedures;

select count(*)
 from user_procedures@asis_hr_link;
  1. 데이터의 갯수가 정확한지 확인 (★중요)
  2. 인덱스의 갯수가 정확한지 확인 (★중요)
  3. 제약의 갯수가 정확한지 확인 (★중요)
  4. 뷰의 갯수가 정확한지 확인
  5. 시퀀스의 갯수가 정확한지 확인
  6. 프로시져의 갯수가 정확한지 확인

위의 방법으로 데이터 이행을 했는데 아주 깔끔하게 전부 이행하는 방법
--> pump를 이용한 방법(뒷장에)
--> 더 빠르고 갯수까지 똑같이 완벽하게 이행됨


개인정보가 들어가져 있는 데이터를 암호화해서 이행할 때 서브쿼리를 사용한 insert 로 옮김

insert into emp(empno,ename,sal, job,mgr, comm, hiredate, deptno)
select empno, ename, 0 as sal, job, mgr, comm, hiredate, deptno 
 from emp@asis_hr_link;

삭제해야할 제약 리스트

with aaa as (
select TABLE_NAME,SEARCH_CONDITION_VC, count(*)
from user_constraints
group by TABLE_NAME,SEARCH_CONDITION_VC having count(*) > 1)

select a.constraint_name, a.table_name, a.SEARCH_CONDITION_VC
from user_constraints a, aaa b
where a.TABLE_NAME = b.TABLE_NAME and a.SEARCH_CONDITION_VC = b.SEARCH_CONDITION_VC 
and a.constraint_name like 'SYS%';

.

with aaa as (
select TABLE_NAME,SEARCH_CONDITION_VC, count(*)
from user_constraints
group by TABLE_NAME,SEARCH_CONDITION_VC 
having count(*) > 1)

select 'alter table ' || a.table_name || ' drop constraint ' || a.constraint_name ||';'
from user_constraints a, aaa b
where a.TABLE_NAME = b.TABLE_NAME and a.SEARCH_CONDITION_VC = b.SEARCH_CONDITION_VC 
and a.constraint_name like 'SYS%';

제약 삭제 후 갯수 비교하기

select count(*)   
 from user_constraints;

select count(*)  
 from user_constraints@asis_hr_link;

🔸 [문제] DW 쪽에서 KBM 유져를 생성하고 as-is 의 hr 계정의 데이터를 이행하시오

검증 스크립트 돌려서 갯수가 맞는지 확인하기

-- 1. 테이블 확인
select o.table_name, o.num_rows, n.num_rows, o.last_analyzed
  from  user_tables@asis_yys_link  o , user_tables  n
  where o.table_name = n.table_name;
  
--2. 제약 확인
select count(*)   
 from user_constraints;
 
select count(*)  
 from user_constraints@asis_yys_link;
 
--3. 인덱스 확인 
select count(*)   
 from user_indexes;
 
select count(*)  
 from user_indexes@asis_yys_link;

1) [DW] kbm 계정을 dw 에서 생성합니다.

SYS @ ora19dw > create user kbm
  2             identified by kbm;

사용자가 생성되었습니다.

SYS @ ora19dw > grant dba to kbm;

권한이 부여되었습니다.

SYS @ ora19dw >

as-is(hr) 는 하얀색, to-be(kbm) 는 파란색으로 색깔을 변경해놓기 (혼동방지)

2) as-is 쪽의 테이블 생성 스크립트를 추출합니다.

스크립트를 전면적으로 수정합니다.

3) HR을 KBM으로 변경합니다.

편집 - 바꾸기 - 모두 바꾸기

4) HR이 가지고 있는 테이블의 데이터 크기를 확인합니다.

select table_name, num_rows, avg_row_len, num_rows*avg_row_len /1024
 from user_tables
 order by num_rows desc;
 
 select  sum(num_rows*avg_row_len /1024 )
 from user_tables;

5) DW 쪽 테이블을 담을 TABLESPACE 를 생성합니다.

to-be 창에서

create tablespace kbm_tables
  datafile '/home/oracle/kbm_tables01.dbf' size 1024m;

6) DW 쪽에 테이블을 생성하기 위해서 테이블 생성 스크립트를 kbm_tables 테이블스페이스로 저장하도록 수정합니다.

SYSAUX를 --> KBM_TABLES로

-- 1. 테이블 생성 스크립트

CREATE TABLE KBM.COUNTRIES
(
    COUNTRY_ID      CHAR(2) NOT NULL,
    COUNTRY_NAME    VARCHAR2(40),
    REGION_ID       NUMBER,
    CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID)
)
ORGANIZATION INDEX 
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOMAPPING;

COMMENT ON TABLE KBM.COUNTRIES IS 'country table. Contains 25 rows. References with locations table.';
COMMENT ON COLUMN KBM.COUNTRIES.COUNTRY_ID IS 'Primary key of countries table.';
COMMENT ON COLUMN KBM.COUNTRIES.COUNTRY_NAME IS 'Country name';
COMMENT ON COLUMN KBM.COUNTRIES.REGION_ID IS 'Region ID for the country. Foreign key to region_id column in the departments table.';

CREATE TABLE KBM.DEPARTMENTS
(
    DEPARTMENT_ID   NUMBER(4) NOT NULL,
    DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
    MANAGER_ID      NUMBER(6),
    LOCATION_ID     NUMBER(4)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

COMMENT ON TABLE KBM.DEPARTMENTS IS 'Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.';
COMMENT ON COLUMN KBM.DEPARTMENTS.DEPARTMENT_ID IS 'Primary key column of departments table.';
COMMENT ON COLUMN KBM.DEPARTMENTS.DEPARTMENT_NAME IS 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ';
COMMENT ON COLUMN KBM.DEPARTMENTS.MANAGER_ID IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';
COMMENT ON COLUMN KBM.DEPARTMENTS.LOCATION_ID IS 'Location id where a department is located. Foreign key to location_id column of locations table.';

CREATE TABLE KBM.EMPLOYEES
(
    EMPLOYEE_ID     NUMBER(6) NOT NULL,
    FIRST_NAME      VARCHAR2(20),
    LAST_NAME       VARCHAR2(25) NOT NULL,
    EMAIL           VARCHAR2(25) NOT NULL,
    PHONE_NUMBER    VARCHAR2(20),
    HIRE_DATE       DATE NOT NULL,
    JOB_ID          VARCHAR2(10) NOT NULL,
    SALARY          NUMBER(8,2),
    COMMISSION_PCT  NUMBER(2,2),
    MANAGER_ID      NUMBER(6),
    DEPARTMENT_ID   NUMBER(4)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

COMMENT ON TABLE KBM.EMPLOYEES IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';
COMMENT ON COLUMN KBM.EMPLOYEES.EMPLOYEE_ID IS 'Primary key of employees table.';
COMMENT ON COLUMN KBM.EMPLOYEES.FIRST_NAME IS 'First name of the employee. A not null column.';
COMMENT ON COLUMN KBM.EMPLOYEES.LAST_NAME IS 'Last name of the employee. A not null column.';
COMMENT ON COLUMN KBM.EMPLOYEES.EMAIL IS 'Email id of the employee';
COMMENT ON COLUMN KBM.EMPLOYEES.PHONE_NUMBER IS 'Phone number of the employee; includes country code and area code';
COMMENT ON COLUMN KBM.EMPLOYEES.HIRE_DATE IS 'Date when the employee started on this job. A not null column.';
COMMENT ON COLUMN KBM.EMPLOYEES.JOB_ID IS 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.';
COMMENT ON COLUMN KBM.EMPLOYEES.SALARY IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';
COMMENT ON COLUMN KBM.EMPLOYEES.COMMISSION_PCT IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';
COMMENT ON COLUMN KBM.EMPLOYEES.MANAGER_ID IS 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)';
COMMENT ON COLUMN KBM.EMPLOYEES.DEPARTMENT_ID IS 'Department id where employee works; foreign key to department_id
column of the departments table';

CREATE TABLE KBM.JOBS
(
    JOB_ID      VARCHAR2(10) NOT NULL,
    JOB_TITLE   VARCHAR2(35) NOT NULL,
    MIN_SALARY  NUMBER(6),
    MAX_SALARY  NUMBER(6)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

COMMENT ON TABLE KBM.JOBS IS 'jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.';
COMMENT ON COLUMN KBM.JOBS.JOB_ID IS 'Primary key of jobs table.';
COMMENT ON COLUMN KBM.JOBS.JOB_TITLE IS 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';
COMMENT ON COLUMN KBM.JOBS.MIN_SALARY IS 'Minimum salary for a job title.';
COMMENT ON COLUMN KBM.JOBS.MAX_SALARY IS 'Maximum salary for a job title';

CREATE TABLE KBM.JOB_HISTORY
(
    EMPLOYEE_ID     NUMBER(6) NOT NULL,
    START_DATE      DATE NOT NULL,
    END_DATE        DATE NOT NULL,
    JOB_ID          VARCHAR2(10) NOT NULL,
    DEPARTMENT_ID   NUMBER(4)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

COMMENT ON TABLE KBM.JOB_HISTORY IS 'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.';
COMMENT ON COLUMN KBM.JOB_HISTORY.EMPLOYEE_ID IS 'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table';
COMMENT ON COLUMN KBM.JOB_HISTORY.START_DATE IS 'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)';
COMMENT ON COLUMN KBM.JOB_HISTORY.END_DATE IS 'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)';
COMMENT ON COLUMN KBM.JOB_HISTORY.JOB_ID IS 'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.';
COMMENT ON COLUMN KBM.JOB_HISTORY.DEPARTMENT_ID IS 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table';

CREATE TABLE KBM.LOCATIONS
(
    LOCATION_ID     NUMBER(4) NOT NULL,
    STREET_ADDRESS  VARCHAR2(40),
    POSTAL_CODE     VARCHAR2(12),
    CITY            VARCHAR2(30) NOT NULL,
    STATE_PROVINCE  VARCHAR2(25),
    COUNTRY_ID      CHAR(2)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

COMMENT ON TABLE KBM.LOCATIONS IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';
COMMENT ON COLUMN KBM.LOCATIONS.LOCATION_ID IS 'Primary key of locations table';
COMMENT ON COLUMN KBM.LOCATIONS.STREET_ADDRESS IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
COMMENT ON COLUMN KBM.LOCATIONS.POSTAL_CODE IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';
COMMENT ON COLUMN KBM.LOCATIONS.CITY IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
COMMENT ON COLUMN KBM.LOCATIONS.STATE_PROVINCE IS 'State or Province where an office, warehouse, or production site of a
company is located.';
COMMENT ON COLUMN KBM.LOCATIONS.COUNTRY_ID IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';

CREATE TABLE KBM.REGIONS
(
    REGION_ID   NUMBER NOT NULL,
    REGION_NAME VARCHAR2(25)
)
TABLESPACE KBM_TABLES
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

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

drop public database link asis_kbm_link;

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

select table_name
 from user_tables@asis_kbm_link;

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

select table_name
 from user_tables@asis_kbm_link;

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


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;


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('KBM');

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

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

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

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

SYSAUX --> HR_INDEX_TS로 변경

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

11) 갯수가 맞는지 확인

select count(*)
 from user_indexes;
 
 
select count(*)
 from user_indexes@asis_kbm_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;

12) 제약을 생성합니다.

ALTER TABLE KBM.COUNTRIES
ADD CONSTRAINT COUNTRY_ID_NN CHECK ("COUNTRY_ID" IS NOT NULL);

ALTER TABLE KBM.DEPARTMENTS
ADD CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_EMAIL_NN CHECK ("EMAIL" IS NOT NULL);

ALTER TABLE KBM.COUNTRIES
ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID);

ALTER TABLE KBM.DEPARTMENTS
ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID);

ALTER TABLE KBM.COUNTRIES
ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY (REGION_ID) REFERENCES KBM.REGIONS (REGION_ID);

ALTER TABLE KBM.DEPARTMENTS
ADD CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES KBM.LOCATIONS (LOCATION_ID);

ALTER TABLE KBM.DEPARTMENTS
ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES KBM.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES KBM.DEPARTMENTS (DEPARTMENT_ID);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_HIRE_DATE_NN CHECK ("HIRE_DATE" IS NOT NULL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_JOB_NN CHECK ("JOB_ID" IS NOT NULL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_LAST_NAME_NN CHECK ("LAST_NAME" IS NOT NULL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_DATE_INTERVAL CHECK (end_date > start_date);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_EMPLOYEE_NN CHECK ("EMPLOYEE_ID" IS NOT NULL);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES KBM.JOBS (JOB_ID);

ALTER TABLE KBM.EMPLOYEES
ADD CONSTRAINT EMP_MANAGER_FK FOREIGN KEY (MANAGER_ID) REFERENCES KBM.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES KBM.DEPARTMENTS (DEPARTMENT_ID);

ALTER TABLE KBM.JOBS
ADD CONSTRAINT JOB_TITLE_NN CHECK ("JOB_TITLE" IS NOT NULL);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_END_DATE_NN CHECK ("END_DATE" IS NOT NULL);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_JOB_NN CHECK ("JOB_ID" IS NOT NULL);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_START_DATE_NN CHECK ("START_DATE" IS NOT NULL);

ALTER TABLE KBM.LOCATIONS
ADD CONSTRAINT LOC_CITY_NN CHECK ("CITY" IS NOT NULL);

ALTER TABLE KBM.JOBS
ADD CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY (EMPLOYEE_ID,START_DATE);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_EMP_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES KBM.EMPLOYEES (EMPLOYEE_ID);

ALTER TABLE KBM.JOB_HISTORY
ADD CONSTRAINT JHIST_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES KBM.JOBS (JOB_ID);

ALTER TABLE KBM.LOCATIONS
ADD CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID) REFERENCES KBM.COUNTRIES (COUNTRY_ID);

ALTER TABLE KBM.REGIONS
ADD CONSTRAINT REGION_ID_NN CHECK ("REGION_ID" IS NOT NULL);

ALTER TABLE KBM.LOCATIONS
ADD CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID);

ALTER TABLE KBM.REGIONS
ADD CONSTRAINT REG_ID_PK PRIMARY KEY (REGION_ID);

select count(*)   -- 24
 from user_constraints;
 
select count(*)  -- 34
 from user_constraints@asis_kbm_link;

13) 나머지 시퀀스와 뷰와 트리거를 생성합니다.

CREATE SEQUENCE KBM.DEPARTMENTS_SEQ
START WITH 280
INCREMENT BY 10
MINVALUE 1
MAXVALUE 9990
NOCACHE;

CREATE SEQUENCE KBM.EMPLOYEES_SEQ
START WITH 207
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCACHE;

CREATE SEQUENCE KBM.LOCATIONS_SEQ
START WITH 3300
INCREMENT BY 100
MINVALUE 1
MAXVALUE 9900
NOCACHE;

CREATE OR REPLACE VIEW KBM.EMP_DETAILS_VIEW
(
    EMPLOYEE_ID,
    JOB_ID,
    MANAGER_ID,
    DEPARTMENT_ID,
    LOCATION_ID,
    COUNTRY_ID,
    FIRST_NAME,
    LAST_NAME,
    SALARY,
    COMMISSION_PCT,
    DEPARTMENT_NAME,
    JOB_TITLE,
    CITY,
    STATE_PROVINCE,
    COUNTRY_NAME,
    REGION_NAME
)
AS
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY;

CREATE OR REPLACE PROCEDURE KBM.add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/

CREATE OR REPLACE PROCEDURE KBM.secure_dml
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
	RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END secure_dml;
/

CREATE OR REPLACE TRIGGER KBM.secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/

.

select count(*)
 from user_sequences;
 

select count(*)
 from user_sequences@asis_kbm_link;
 

select count(*)
 from user_views;
 
select count(*)
from user_views@asis_kbm_link;

select count(*)
 from user_triggers;
 
select count(*)
 from user_triggers@asis_kbm_link;
 
select count(*)
from user_procedures;

select count(*)
from user_procedures@asis_kbm_link;
profile
봄
이전 포스트

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

다음 포스트

[Oracle] Dump를 이용한 데이터 이행1 (TABLE 레벨)

0개의 댓글