테스트 환경
| Source | Target |
| OS Ver : ol-7.9 DB Ver : 11.2.0.3 Chracter-set : AL32UTF8 |
OS Ver : ol-7.9 DB Ver : 19.15.0.0 Charaterset : AL32UTF8 |
Export 전 주의 사항
- Full,owner 및 tables 옵션 동시 사용 불가
- Direct=Y의 경우 consistent=Y 설정 불가
- Full=Y의 경우 exprot 시 exp_full_database 권한 필요
- 추출하고자 하는 테이블 크기 확인
- 해당 스키마가 포함된 tablespace 확인 필요 (dba_tables 나 dba_segments)
EXPDP
1. 추출하고자 하는 테이블 크기 확인
SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 AS TABLE_MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = ':V_TABLE_NAME' -- Table명 지정필요
GROUP BY SEGMENT_NAME
2. 기존 DATA PUMP 디렉토리 확인 및 추가
SELECT *
FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME='DATAPUMP_DIR'
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------------------
SYS DATAPUMP_DIR /home/ora11g/dump_exp
CREATE DIRECTORY DATAPUMP_DIR2 AS '/home/ora11g/dump_exp';
3. 디렉토리에 대한 권한 설정(SYS로 진행할 경우 무시)
GRANT READ, WRITE ON DIRECTORY dpump_dir2 to 사용자;
4. EXPDP 진행
[ora11g@oraclevm dump_exp]$ pwd
/home/ora11g/dump_exp
[ora11g@oraclevm dump_exp]$ expdp \"/ as sysdba \" DIRECTORY=datapump_dir2 DUMPFILE=HR_TABLE_%U.dmp LOGFILE=HR_TABLE.log TABLES=HR.JOBS,HR.EMPLOYEES,HR.EMP,HR.DEPT CONTENT=ALL FILESIZE=100MB PARALLEL=2
Export: Release 11.2.0.3.0 - Production on Wed Nov 23 10:57:47 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA"DIRECTORY=datapump_dir DUMPFILE=HR_TABLE_%U.dmp LOGFILE=HR_TABLE.log TABLES=HR.JOBS,HR.EMPLOYEES,HR.EMP,HR.DEPT CONTENT=ALL FILESIZE=100MB PARALLEL=2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
. . exported "HR"."DEPT" 5.921 KB 4 rows
. . exported "HR"."EMP" 8.554 KB 14 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/ora11g/dump_exp/HR_TABLE_01.dmp
/home/ora11g/dump_exp/HR_TABLE_02.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 10:57:54
5. 해당 경로 파일 확인
[ora11g@oraclevm dump_exp]$ ls -lrt
total 256
-rw-r-----. 1 ora11g dba 45056 Nov 23 10:57 HR_TABLE_02.dmp
-rw-r-----. 1 ora11g dba 212992 Nov 23 10:57 HR_TABLE_01.dmp
-rw-r--r--. 1 ora11g dba 1890 Nov 23 10:57 HR_TABLE.log- export 시 실제 테이블 크기보다 작게 생성된다. 위 SQL 수행하여 나온 크기만큼의 Disk 용량을 확보하면 export 작업을 수행하는데 충분하다. 또한, 추출 파일에는 해당 테이블의 인덱스를 생성할 수 있지만 정의만 지정하기에 별도의 디스크 공간은 필요하지 않다. 하지만 import 시에 실제 인덱스를 생성하므로 인덱스 테이블스페이스에 여유 공간을 확보해야한다.
EXPDP Option
| Option | Description | Default |
| scott/tiger | db user/password | |
| full | 해당 db 전체 데이터 추출 여부 | 기본값 N |
| buffer | 작업 단위의 크기 설정 | |
| owner | db 유저별 오브젝트 추출 설정 | |
| file | 추출한 데이터를 저장할 파일 이름 설정 | |
| tables | 데이터를 추출할 대상 테이블 설정 | |
| compress | 익스텐트 통합 여부를 지정. 필수로 N으로 지정하는 것이 좋다 | 기본값 Y |
| grants | 오브젝트 권한 설정에 대한 정보 추출 여부 | 기본값 Y |
| indexes | 인덱스 스크립트 추출 여부 | 기본값 Y |
| direct | 직접 경로로 export 수행 여부 | 기본값 N |
| triggers | 트리거 정보 추출 여부 | 기본값 Y |
| log | 로그를 저장할 파일 지정 | |
| rows | 테이블의 데이터 추출 여부 | 기본값 Y |
| consistent | 대상 테이블의 읽기 일관성 지정 | 기본값 N |
| constraints | 제약 조건의 추출 여부 | 기본값 Y |
| parfile | 파라메터 파일을 export시 적용 | |
| INCTYPE | 증분 엑스포트 유형 | |
| RECORD | 증분 엑스포트 추적 | 기본값 Y |
| OBJECT_CONSISTENT | 객체 엑스포트 동안 읽기 전용으로 설정된 트랜잭션(N) | |
| FEEDBACK | 매 x행마다 진행 상황 표시(0) | |
| FILESIZE | 각 덤프 파일의 최대 크기 | |
| FLASHBACK_SCN | 세션 스냅샷을 되돌리는 설정에 사용된 SCN | |
| FLASHBACK_TIME | 지정된 시간에 가장 가까운 SCN을 가져오는 데 사용된 시간 | |
| QUERY | 테이블의 부분 집합을 엑스포트하는 데 사용된 select 절 | |
| RESUMABLE | 공간 관련 오류가 발생하면 일시 중지됨(N) | |
| RESUMABLE_NAME | 재개 가능한 명령문을 식별하는 데 사용된 텍스트 문자열 | |
| RESUMABLE_TIMEOUT | RESUMABLE 대기 시간 | |
| TTS_FULL_CHECK | TTS에 대한 전체 또는 부분 종속성 검사 수행 | |
| TABLESPACES | 엑스포트할 테이블스페이스 목록 | |
| TRANSPORT_TABLESPACE | 이동 가능한 테이블스페이스 메타 데이터 엑스포트(N) | |
| TEMPLATE | iAS 모드 엑스포트를 호출하는 템플리트 이름 |
IMPDP
1. EXMDP 진행 한 파일 압축하여 scp/sftp로 IMPDP 할 디렉토리 또는 파일 이동
(동일한 서버지만 cp 보다는 scp를 이용해서 진행)
- tar로 압축 진행
[ora11g@oraclevm ~]$ tar -cvf dump_exp.tar dump_exp
dump_exp/
dump_exp/HR_TABLE.log
dump_exp/HR_TABLE_01.dmp
dump_exp/HR_TABLE_02.dmp
dump_exp/HR_SCHEMA_TABLE_CNT.log
dump_exp/schema_check.sh
dump_exp/result.log
- scp를 통해 파일 전송
[ora11g@oraclevm ~]$ scp dump_exp.tar ora19c@192.168.136.4:/home/ora19c
The authenticity of host '192.168.136.4 (192.168.136.4)' can't be established.
ECDSA key fingerprint is SHA256:U5MSbnCL/CYQKS296+iLEPYTbJzrnIKKpMUN0pMDYXY.
ECDSA key fingerprint is MD5:6c:dd:20:ae:d3:c4:56:f1:7f:12:82:67:0a:8e:38:4f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.136.4' (ECDSA) to the list of known hosts.
ora19c@192.168.136.4's password:
dump_exp.tar 100% 270KB 5.5MB/s 00:00
3. 파일 내용 확인
(동일한 서버로 switch로만 계정 전환)
[ora11g@oraclevm ~]$ su - ora19c
Password:
Last login: Wed Nov 23 10:38:07 KST 2022 on pts/0
[ora19c@oraclevm ~]$ ls -lrt
total 372
-rw-r--r--. 1 ora19c dba 570 Nov 8 13:14 bash_profile.sh
-rw-r--r--. 1 ora19c dba 38 Nov 8 15:12 afiedt.buf
-rw-r--r--. 1 ora19c dba 16688 Nov 8 17:12 check_DB19C_20221108.log
-rw-r--r--. 1 ora19c dba 16686 Nov 9 09:29 check_DB19C_20221109.log
-rw-r--r--. 1 ora19c dba 26628 Nov 9 10:07 script.sql
-rw-r--r--. 1 ora19c dba 6472 Nov 9 13:28 oracle_stress.zip
drwxr-xr-x. 9 ora19c dba 4096 Nov 9 13:29 oracle_stress
-rw-r--r--. 1 ora19c dba 2798 Nov 9 13:46 demo.sql
-rw-r--r--. 1 ora19c dba 276480 Nov 24 09:50 dump_exp.tar
-rw-r--r--. 1 ora19c dba 80 Nov 24 09:50 crontab2.log
-rw-r--r--. 1 ora19c dba 80 Nov 24 09:50 crontab.log
4. CTAS를 통해 EXPDP한 테이블 백업 진행(과정생략)
(예시문)
SQL> CREATE TABLE :V_TABLE_NAME_BK
AS
SELECT *
FROM :V_ONWER.:V_TABLE_NAME
5. TABLE DROP
(예시문)
DROP TABLE :V_ONWER.:V_TABLE_NAME CASCADE CONSTRAINT;
6. DataPump에 대한 디렉 토리 생성
CREATE DIRECTORY DATAPUMP_DIR2 AS '/home/ora19c/dump_exp';error no 1. table 존재로 에러 (분명삭제했는데 남아있음) ▶ 재삭제 후 import
[ora19c@oraclevm dump_exp]$ impdp \"/ as sysdba\" directory=DATAPUMP file=HR_TABLE_01.dmp,HR_TABLE_02.dmp log=HR_TABLE.log
Import: Release 19.0.0.0.0 - Production on Thu Nov 24 10:34:53 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=HR_TABLE_01.dmp,HR_TABLE_02.dmp" Location: Command Line, Replaced with: "dumpfile=DATAPUMP:HR_TABLE_01.dmp,DATAPUMP:HR_TABLE_02.dm p"
Database Directory Object "DATAPUMP" has been added to file specification: "HR_TABLE_01.dmp".
Database Directory Object "DATAPUMP" has been added to file specification: "HR_TABLE_02.dmp".
Legacy Mode Parameter: "log=HR_TABLE.log" Location: Command Line, Replaced with: "logfile=DATAPUMP:HR_TABLE.log"
Database Directory Object "DATAPUMP" has been added to file specification: "HR_TABLE.log".
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" dumpfile=DATAPUMP:HR_TABLE_01.dmp,DATAPUMP:HR_TABLE_02.dmp logfile=DATAPUMP:HR_TABLE.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39083: Object type TABLE:"HR"."EMPLOYEES" failed to create with error:
ORA-00959: tablespace 'EXAMPLE' does not exist
Failing sql is:
CREATE TABLE "HR"."EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20 BYTE), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" N OT NULL ENABLE, "JOB_ID" VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0 ), "DEPARTMENT_ID" NUMBER(4,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 10485 76 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"
ORA-39083: Object type TABLE:"HR"."JOBS" failed to create with error:
ORA-00959: tablespace 'EXAMPLE' does not exist
Failing sql is:
CREATE TABLE "HR"."JOBS" ("JOB_ID" VARCHAR2(10 BYTE), "JOB_TITLE" VARCHAR2(35 BYTE) CONSTRAINT "JOB_TITLE_NN" NOT NULL ENABLE, "MIN_SALARY" NUMBER(6,0), "MAX_S ALARY" NUMBER(6,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENT S 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"HR"."JOB_ID_PK" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_EMAIL_UK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_EMP_ID_PK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_DEPARTMENT_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_JOB_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_MANAGER_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_NAME_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"HR"."JOB_ID_PK" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_SALARY_MIN" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_EMAIL_UK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_EMP_ID_PK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_DEPT_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_MANAGER_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_JOB_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39112: Dependent object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type TRIGGER:"HR"."SECURE_EMPLOYEES" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 40 error(s) at Thu Nov 24 10:34:57 2022 elapsed 0 00:00:04
---------------------------------------------------------------------------------------------
error no 2. tablespace 없어서 에러
- 반드시 export tablespace와 동일한 tablespace 생성하거나 remap 필요
[ora19c@oraclevm dump_exp]$ impdp \"/ as sysdba\" directory=DATAPUMP content=all file=HR_TABLE_01.dmp,HR_TABLE_02.dmp log=HR_TABLE.log
Import: Release 19.0.0.0.0 - Production on Thu Nov 24 10:44:01 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=HR_TABLE_01.dmp,HR_TABLE_02.dmp" Location: Command Line, Replaced with: "dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp"
Legacy Mode Parameter: "log=HR_TABLE.log" Location: Command Line, Replaced with: "logfile=HR_TABLE.log"
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATAPUMP content=all dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp logfile=HR_TABLE.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"HR"."EMPLOYEES" failed to create with error:
ORA-00959: tablespace 'EXAMPLE' does not exist
Failing sql is:
CREATE TABLE "HR"."EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20 BYTE), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" N OT NULL ENABLE, "JOB_ID" VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0 ), "DEPARTMENT_ID" NUMBER(4,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 10485 76 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"
ORA-39083: Object type TABLE:"HR"."JOBS" failed to create with error:
ORA-00959: tablespace 'EXAMPLE' does not exist
Failing sql is:
CREATE TABLE "HR"."JOBS" ("JOB_ID" VARCHAR2(10 BYTE), "JOB_TITLE" VARCHAR2(35 BYTE) CONSTRAINT "JOB_TITLE_NN" NOT NULL ENABLE, "MIN_SALARY" NUMBER(6,0), "MAX_S ALARY" NUMBER(6,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENT S 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPT" 5.921 KB 4 rows
. . imported "HR"."EMP" 8.554 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"HR"."JOB_ID_PK" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_EMAIL_UK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_EMP_ID_PK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_DEPARTMENT_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_JOB_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_MANAGER_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"HR"."EMP_NAME_IX" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"HR"."JOB_ID_PK" skipped, base object type TABLE:"HR"."JOBS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_SALARY_MIN" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_EMAIL_UK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"HR"."EMP_EMP_ID_PK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_DEPT_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_MANAGER_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"HR"."EMP_JOB_FK" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39112: Dependent object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type TRIGGER:"HR"."SECURE_EMPLOYEES" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 38 error(s) at Thu Nov 24 10:44:05 2022 elapsed 0 00:00:03
--------------------------------------------------------------------------------------
tablespace 생성할 때 source tablespace 내용과 다를 수 있기 때문에
-export시 full로 뽑고 해당 덤프 파일을 sqlfile=(name).sql 옵션 설정해서 스키마 뽑아내는 것이 제일
impdp \'/ as sysdba\' directory=DATAPUMP_DIR2 sqlfile=script.sql dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp
--------------------------------------------------------------------------------------
error no 3. object grants error_source OE라는 유저가 HR 스키마와 Resource 걸려있어서 발생하는 에러
[ora19c@oraclevm dump_exp]$ impdp \"/ as sysdba\" directory=DATAPUMP content=all file=HR_TABLE_01.dmp,HR_TABLE_02.dmp log=HR_TABLE.log
Import: Release 19.0.0.0.0 - Production on Thu Nov 24 10:52:21 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=HR_TABLE_01.dmp,HR_TABLE_02.dmp" Location: Command Line, Replaced with: "dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp"
Legacy Mode Parameter: "log=HR_TABLE.log" Location: Command Line, Replaced with: "logfile=HR_TABLE.log"
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATAPUMP content=all dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp logfile=HR_TABLE.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.984 KB 19 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."JOBS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 5 error(s) at Thu Nov 24 10:52:28 2022 elapsed 0 00:00:07
--------------------------------------------------------------------------------------
[ora19c@oraclevm dump_exp]$ impdp \"/ as sysdba\" directory=DATAPUMP content=all file=HR_TABLE_01.dmp,HR_TABLE_02.dmp log=HR_TABLE.log grants=false 옵션 부여
--------------------------------------------------------------------------------------
Import: Release 19.0.0.0.0 - Production on Thu Nov 24 11:25:11 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=HR_TABLE_01.dmp,HR_TABLE_02.dmp" Location: Command Line, Replaced with: "dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp"
Legacy Mode Parameter: "grants=FALSE" Location: Command Line, Replaced with: "exclude=object_grant"
Legacy Mode Parameter: "log=HR_TABLE.log" Location: Command Line, Replaced with: "logfile=HR_TABLE.log"
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATAPUMP content=all dumpfile=HR_TABLE_01.dmp,HR_TABLE_02.dmp logfile=HR_TABLE.log exclud e=object_grant
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPT" 5.921 KB 4 rows
. . imported "HR"."EMP" 8.554 KB 14 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.984 KB 19 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 24 11:25:19 2022 elapsed 0 00:00:08
+추가사항
https://zzanggoo.tistory.com/97
Oracle expdp/impdp Utility
Data PumpOracle 10g의 기능인 Data Pump는 Oracle Database Data와 Meta Data의 이동을 위한 DBMS_DATAPUMP 패키지를 통하여 상당히 빠른 Data Pump Infrastructure를 제공하고 있다. 기존 Oracle 9i까지 사용되던 exp, imp 유틸
zzanggoo.tistory.com
'DB > Oracle' 카테고리의 다른 글
| [RAC][INSTALL] 3_Grid 엔진 설치 (0) | 2022.12.26 |
|---|---|
| [INSTALL][OL7.9 / Oracle 19.15.0.0] Oracle Slient 설치_생성 스크립트 추출 버전 (수정중) (0) | 2022.11.30 |
| [INSTALL][OL7.9 / Oracle 11.2.0.3] Oracle Slient mode 설치 (0) | 2022.11.22 |
| [ETC] 오라클 AWR 레포트 출력하는 방법 (0) | 2022.11.22 |
| [ETC] AWR Report 보는 방법 (0) | 2022.11.22 |
