[Oracle] Pump를 이용한 데이터 이행3 (USER 레벨 + 인덱스 생성을 나중에 따로 하기)

봄·2025년 9월 12일

오라클 관리

목록 보기

134/163

[실습1] 오렌지에서 hr 계정의 인덱스 생성 스크립트를 추출합니다.

asis_create_hr_index_pump.sql

[실습2] 유져 레벨로 export pump 를 수행해서 hr 계정을 export 하시오

expdp system/oracle_4U directory=datapump_dir schemas=hr dumpfile=hr_pump2.dmp exclude=index,constraint

[oracle@ora19c pump_ora19]$ ora19
ORA19

[oracle@ora19c pump_ora19]$ expdp system/oracle_4U directory=datapump_dir schemas=hr  dumpfile=hr_pump2.dmp exclude=index,constraint

Export: Release 19.0.0.0.0 - Production on 금 9월 12 14:01:22 2025
Version 19.3.0.0.0
.
.
.
SYSTEM.SYS_EXPORT_SCHEMA_01에 대해 설정된 덤프 파일:
  /home/oracle/pump_ora19/hr_pump2.dmp
"SYSTEM"."SYS_EXPORT_SCHEMA_01" 작업이 금 9월 12 14:01:47 2025 elapsed 0 00:00:24에서 성공적으로 완료됨

[실습3] hr_pump2.dmp 파일을 dw 쪽으로 copy 합니다

cp /home/oracle/pump_ora19/hr_pump2.dmp /home/oracle/pump_ora19dw/hr_pump2.dmp

[oracle@ora19c pump_ora19]$ cp /home/oracle/pump_ora19/hr_pump2.dmp /home/oracle/pump_ora19dw/hr_pump2.dmp

[실습4] dw쪽에 hr4 에 임폴트 pump 를 수행합니다.

impdp system/oracle_4U directory=ora19dw_dir dumpfile=hr_pump2.dmp remap_schema=hr:hr4 remap_tablespace=hrts:ts450 exclude=index,constraint

--> hr4 유저 생성 안해도 자동으로 만들어짐
--> (패스워드는 hr에 따라서 hr로 자동 지정됨)
--> (패스워드 따로 지정해주기)

[oracle@ora19c ~]$ impdp system/oracle_4U directory=ora19dw_dir dumpfile=hr_pump2.dmp remap_schema=hr:hr4 remap_tablespace=hrts:ts450  exclude=index,constraint

Import: Release 19.0.0.0.0 - Production on 금 9월 12 14:14:24 2025
Version 19.3.0.0.0
.
.
.
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 금 9월 12 14:14:37 2025 elapsed 0 00:00:12에서 성공적으로 완료됨


[oracle@ora19c ~]$ sqlplus hr4/hr

SQL*Plus: Release 19.0.0.0.0 - Production on 금 9월 12 14:15:12 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

HR4 @ ora19dw >
HR4 @ ora19dw > password
HR4에 대한 비밀번호를 변경합니다.
이전 비밀번호:
새 비밀번호:
새 비밀번호 다시 입력:
비밀번호가 변경되었습니다.

HR4 @ ora19dw > connect hr4/hr4
연결되었습니다.
HR4 @ ora19dw >

[실습5] hr4 로 접속해서 인덱스를 생성하시오

alter  session set workarea_size_policy=manual;
alter  session set sort_area_size=1000000000;
alter  session set hash_area_size=1000000000;


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

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

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

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

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

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

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

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

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

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

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


alter  session set workarea_size_policy=auto;


select ' alter index ' || index_name || ' logging  parallel 1 ;'
 from user_indexes;

 alter index COUNTRY_C_ID_PK logging  parallel 1 ;
 alter index DEPT_LOCATION_IX logging  parallel 1 ;
 alter index EMP_DEPARTMENT_IX 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 LOC_CITY_IX logging  parallel 1 ;
 alter index LOC_COUNTRY_IX logging  parallel 1 ;
 alter index LOC_STATE_PROVINCE_IX logging  parallel 1 ;
 alter index JHIST_DEPARTMENT_IX logging  parallel 1 ;
 alter index JHIST_EMPLOYEE_IX logging  parallel 1 ;
 alter index JHIST_JOB_IX logging  parallel 1 ;

[실습6] 제약을 생성하시오


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


select count(*)
 from user_indexes;  -- 19

select count(*)
 from user_constraints;  --34

봄

이전 포스트

[Oracle] Pump를 이용한 데이터 이행2 (USER 레벨)

다음 포스트