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

봄·2025년 9월 11일

오라클 관리

목록 보기
128/163

user level = schema level
user level을 가장 많이 씀

💡 export / import 방법 4가지

  1. table level : 특정 테이블만 export / import 수행하기
  2. user level : scott과 같이 유져가 가지고 있는 모든 객체를 전부 export / import 수행하기
  3. tablespace level : 특정 테이블 스페이스를 통째로 export /import 하기
  4. database level : database를 통째로 export / import

[실습1] ora19dw 쪽에 scottdw 로 유져를 생성하시오

[oracle@ora19c ~]$ sysdw

SQL*Plus: Release 19.0.0.0.0 - Production on 목 9월 11 11:21:08 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

SYS @ ora19dw > create user scottdw identified by tiger;

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

SYS @ ora19dw > grant dba to scottdw;

권한이 부여되었습니다.

SYS @ ora19dw >

[실습2] ora19쪽에서 scott의 모든 객체들을 유져레벨로 export 하시오

[oracle@ora19c ~]$ exp scott/tiger owner=scott file=scott.dmp

[실습3] ora19dw 쪽에 scottdw에 임폴트를 하시오

[oracle@ora19c ~]$ sqlplus system/oracle_4U

SQL*Plus: Release 19.0.0.0.0 - Production on 목 9월 11 11:24:29 2025
Version 19.3.0.0.0

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

마지막 성공한 로그인 시간: 수 8월  27 2025 15:29:50 +09:00

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

SYSTEM @ ora19dw > exit;

[oracle@ora19c ~]$ imp system/oracle_4U file=scott.dmp fromuser=scott touser=scottdw

[실습4] ora19 쪽에 hr 계정의 모든 객체들을 유져레벨로 export 하시오

[oracle@ora19c ~]$ exp hr/hr file=hr.dmp owner=hr

Export: Release 19.0.0.0.0 - Production on 목 9월 11 11:28:52 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


다음에 접속됨: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
AL32UTF8 문자 설정과 AL16UTF16 NCHAR 문자 설정에서 익스포트가 종료되었습니다
. 스키마 이전 단계의 객체와 작업을 엑스포트합니다\r

. HR 사용자를 위해 외래 함수 라이브러리 이름을 엑스포트합니다
. PUBLIC 유형 동의어 익스포트 중
. 전용 유형 동의어 익스포트 중
. HR 사용자의 객체 유형 정의를 익스포트합니다.
HR의 객체를 익스포트하려고 합니다 ...
. 데이터베이스 링크 익스포트 중
. 순차 번호 익스포트 중
. 클러스터 정의 익스포트 중
. HR의 테이블을 익스포트하려고 합니다 via 규정 경로...
. .                       COUNTRIES 테이블 익스포트 중         25 행이 엑스포트됨
. .                     DEPARTMENTS 테이블 익스포트 중         27 행이 엑스포트됨
. .                       EMPLOYEES 테이블 익스포트 중        107 행이 엑스포트됨
. .                            JOBS 테이블 익스포트 중         19 행이 엑스포트됨
. .                     JOB_HISTORY 테이블 익스포트 중         10 행이 엑스포트됨
. .                       LOCATIONS 테이블 익스포트 중         23 행이 엑스포트됨
. .                         REGIONS 테이블 익스포트 중          4 행이 엑스포트됨
. 동의어 익스포트 중
. 뷰 익스포트 중
. 저장 프로시저 익스포트 중
. 작업을 엑스포트합니다
. 참조 무결성 제약조건 익스포트 중
. 트리거 익스포트 중
. 인덱스유형을 엑스포팅합니다
. 비트맵, 함수, 기능과 확장 가능한 인덱스들을 엑스포트합니다
. 이후 테이블 처리 익스포트 중
. 구체화된 뷰 익스포트 중
. 스냅샷 로그 익스포트 중
. 작업 대기열 익스포트 중
. 새로고침 그룹과 하위 그룹 익스포트 중
. 차원을 엑스포트합니다
. 스키마 이후 단계의 객체와 작업을 엑스포트합니다\r

. 통계를 엑스포트합니다
익스포트가 경고 없이 정상적으로 종료되었습니다.


[oracle@ora19c ~]$ rm hr.dmp
[oracle@ora19c ~]$
[oracle@ora19c ~]$
[oracle@ora19c ~]$ exp hr/hr file=hr.dmp owner=hr feedback=1

💡 feedback을 이용하면 중간에 export 되는 과정을 볼 수 있음
exp 아이디/패스워드 file=테이블명.dmp owner=유저명 feedback=1

[실습5] ora19dw 쪽에 hrdw 라는 유져를 생성하시오

[oracle@ora19c ~]$ sysdw

SQL*Plus: Release 19.0.0.0.0 - Production on 목 9월 11 11:48:48 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

SYS @ ora19dw > create user hrdw identified by hrdw;

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

SYS @ ora19dw > grant dba to hrdw;

권한이 부여되었습니다.

SYS @ ora19dw >

[실습6] hrdw 유져로 dump 파일을 임폴트 하시오

[oracle@ora19c ~]$ imp system/oracle_4U file=hr.dmp fromuser=hr touser=hrdw


drop public database link asis_hr_link;

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


select 'as-is', count(*)
 from user_tables@asis_hr_link
union all
select 'to-be', count(*)
 from user_tables;
 
select 'as-is', count(*)
 from user_indexes@asis_hr_link
union all
select 'to-be', count(*)
 from user_indexes;
 
 select 'as-is', count(*)
 from user_views@asis_hr_link
union all
select 'to-be', count(*)
 from user_views;
 
 select 'as-is', count(*)
 from user_sequences@asis_hr_link
union all
select 'to-be', count(*)
 from user_sequences;
 
 select 'as-is', count(*)
 from user_procedures@asis_hr_link
union all
select 'to-be', count(*)
 from user_procedures;
 
 -- 위의 모든것을 한번에 확인하는 스크립트
 select 'as-is', count(*)
 from user_objects@asis_hr_link
union all
select 'to-be', count(*)
 from user_objects;

[실습7] 대용량 데이터베이스 환경이라고 가정하고 인덱스를 따로 생성하는 순서로 유져레벨 export/import 를 하시오

1. 오렌지에서 ORA19쪽 hr 계정의 인덱스 생성 스크립트만 따로 생성하시오


2. ora19에서 hr 계정을 유져 레벨로 export 합니다.

[oracle@ora19c ~]$ exp hr/hr file=hr_no_index2.dmp owner=hr indexes=no constraints=no

3. ora19dw 에서 hrdw2 유져를 생성하세요

SYS @ ora19dw > create user hrdw2 identified by hrdw2;

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

SYS @ ora19dw > grant dba to hrdw2;

권한이 부여되었습니다.

4. ora19dw 에서 hrdw2 유져로 import 하시오

[oracle@ora19c ~]$ imp system/oracle_4U file=hr_no_index.dmp fromuser=hr touser=hrdw2 indexes=no

Import: Release 19.0.0.0.0 - Production on 목 9월 11 13:56:05 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


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

익스포트 파일은 규정 경로를 거쳐 EXPORT:V19.00.00 에 의해 생성되었습니다

경고: 객체는 다른 사용자 HR(이)가 엑스포트한 것입니다.

AL32UTF8 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
IMP-00403:

경고: 이 임포트는 권한 문제로 인해 실패한 DDL이 포함된 개별 SQL 파일 "import_sys"을(를) 생성했습니다.

. HR 객체를 HRDW2(으)로 임포트하는 중입니다
. . 테이블                    "COUNTRIES"(를)을 임포트 중         25 행이 임포트되었습니다
. . 테이블                  "DEPARTMENTS"(를)을 임포트 중         27 행이 임포트되었습니다
. . 테이블                    "EMPLOYEES"(를)을 임포트 중        107 행이 임포트되었습니다
. . 테이블                         "JOBS"(를)을 임포트 중         19 행이 임포트되었습니다
. . 테이블                  "JOB_HISTORY"(를)을 임포트 중         10 행이 임포트되었습니다
. . 테이블                    "LOCATIONS"(를)을 임포트 중         23 행이 임포트되었습니다
. . 테이블                      "REGIONS"(를)을 임포트 중          4 행이 임포트되었습니다
제약 조건을 사용으로 설정에 대한 정보...
경고와 함께 임포트가 정상 종료되었습니다.
[oracle@ora19c ~]$

5. 인덱스를 생성합니다.

--- 인덱스 생성 자체가 정렬을 과도하게 일으키므로 
-- 정렬작업을 하는 메모리 영역을 자동으로 할당받지않고
--  수동으로 크게잡고 하겠다.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


alter session set workarea_size_policy = auto;

select index_name from user_indexes;

select 'alter index ' || 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;

7. 제약을 걸어줍니다.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.


select count(*)
 from user_tables@asis_hr_link
union all
select count(*)
 from user_tables;


select count(*)
 from user_indexes@asis_hr_link
union all
select count(*)
 from user_indexes;
 

select count(*)
 from user_constraints@asis_hr_link
union all
select count(*)
 from user_constraints;
profile
봄
이전 포스트

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

다음 포스트

[Oracle] Dump를 이용한 데이터 이행3 (USER 레벨 + 오렌지만 사용)

0개의 댓글