데이터 이동의 필요성
일반적으로 운영서버와 복구서버가 분리되어 있고,
복구서버에서 데이터 복구 후 운영서버로 이동하기 때문에 꼭 익혀둬야 한다.
8.1 Export / Import
- 개요
- Export / Import 데이터를 이동시키는 용도로 개발된 툴이나, 사용법이 쉽고 기능이 막강하여 백업 및 복구 툴로 많이 사용
| 구분 | 내용 |
|---|
| export | 데이터를 DB에서 OS파일로 저장할 때 사용하는 논리적인 백업 툴 |
| import | export로 백업받은 파일 서버로 입력하는 툴 |
- 사용 예: DB가 No Archive Log Mode이고, Open상태에서 백업 받아야 하는 경우
- OS 기종이나 오라클 버전이 달라도 사용 가능(이식성이 좋음)
- DB가 Open된 상태에서만 사용 가능
- 데이터가 많을 경우 물리적인 백업보다 시간이 많이 소요
- export는 데이터는 복사하지만 데이터의 위치는 기록하지 않음(내용은 가져오지만 데이터파일에 대한 정보는 가져오지 않음)
- export가 진행 중일 때 데이터가 추가되거나 변경되어도 그 내용은 현재 export 작업에 반영 되지 않음(가급적 서버 사용량이 적은 시간에 작업)
- Conventional Path export와 Direct Path export
- export 수행하기
- Conventional Path로 Full export 받기(기본모드)
$ time exp system/PW full=y file=/data/exp/full01.dmp log=/data/exp/full_log01.log
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 05:40:34 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
(중간 생략)
. about to export OE's tables via Conventional Path ...
. . exporting table CATEGORIES_TAB 22 rows exported
. . exporting table PRODUCT_REF_LIST_NESTEDTAB 288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB 21 rows exported
. . exporting table CUSTOMERS 319 rows exported
. . exporting table INVENTORIES 1112 rows exported
. . exporting table ORDERS 105 rows exported
. . exporting table ORDER_ITEMS 665 rows exported
. . exporting table PRODUCT_DESCRIPTIONS 8640 rows exported
. . exporting table PRODUCT_INFORMATION 288 rows exported
. . exporting table PROMOTIONS 2 rows exported
. . exporting table PURCHASEORDER 132 rows exported
. . exporting table WAREHOUSES
EXP-00107: Feature (BINARY XML) of column WAREHOUSE_SPEC in table OE.WAREHOUSES is not supported. The table will not be exported. <<< XML 데이터로 인한 오류(11g): Datapump 지원
. about to export IX's tables via Conventional Path ...
. . exporting table AQ$_ORDERS_QUEUETABLE_G 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_H 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_I 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_L 0 rows exported
(중간 생략)
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
real 1m38.236s
user 0m16.041s
sys 0m3.844s
- Direct Path로 Full export 받기
$ time exp ID/PW full=y file=/data/exp/full02.dmp log=/data/exp/full_log02.log direct=y
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 05:56:22 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Direct Path ...
Table DEF$_AQCALL will be exported in conventional path.
. . exporting table DEF$_AQCALL 0 rows exported
Table DEF$_AQERROR will be exported in conventional path.
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 0 rows exported
. . exporting table DEF$_ERROR 0 rows exported
(중간 생략)
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
real 1m36.738s
user 0m14.720s
sys 0m3.741s
- export를 저장하는 파일을 분할해서 받기
$ time exp ID/PW full=y file=/data/exp/full04_1.dmp, /data/exp/full04_2.dmp,
/data/exp/full04_3.dmp filesize=10M
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 06:10:10 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
(중간 생략)
continuing export into file /data/exp/full04_2.dmp <<< 다음 파일 사용 로그
23311 rows exported
. . exporting table MGMT_METADATA_SETS 75 rows exported
. . exporting table MGMT_METRICS 12635 rows exported
(중간 생략)
Export file: expdat.dmp > /data/exp/full04_4.dump <<< 파일 부족 시 추가 파일명 입력
continuing export into file /data/exp/full04_4.dmp
7416 rows exported
. . exporting table WWV_FLOW_PAGE_PLUG_TEMPLATES 166 rows exported
. . exporting table WWV_FLOW_PAGE_SUBMISSIONS 0 rows exported
. . exporting table WWV_FLOW_PASSWORD_HISTORY 1 rows exported
(이하 생략)
- 특정 Tablespace만 export하기
$ time exp ID/PW file=/data/exp/ex_users.dmp tablespaces=example,users
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 06:32:03 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export selected tablespaces ...
For tablespace EXAMPLE ... <<< EXAMPLE Tablesapce Export
. exporting cluster definitions
. exporting table definitions
. . exporting table COUNTRIES 25 rows exported
. . exporting table DEPARTMENTS 27 rows exported
. . exporting table EMPLOYEES 107 rows exported
(중간 생략)
For tablespace USERS ... <<< USER Tablesapce Export
. exporting cluster definitions
. exporting table definitions
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table CATEGORIES_TAB 22 rows exported
. . exporting table PRODUCT_REF_LIST_NESTEDTAB 288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB 21 rows exported
. . exporting table PURCHASEORDER 132 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully with warnings.
real 0m16.971s
user 0m1.764s
sys 0m0.133s
1 ^C^CiEXPORT:V11.02.00 <<< export 버전
2 DIMDBA <<< export를 수행한 계정
3 RTABLES <<< table export
4 8192 <<< 블록 사이즈
5 0
6 72
7 0
(중간 생략)
11 CONNECT HR <<< export tablespace의 table schema로 접속
12 TABLE "COUNTRIES" <<< export table명
13 CREATE TABLE "COUNTRIES" ( <<< create문
"COUNTRY_ID" CHAR(2) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE
, "COUNTRY_NAME" VARCHAR2(40)
, "REGION_ID" N UMBER
, CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE
) ORGANIZATION INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EXAMPLE" NOLOGGING NOCOMPRESS PCTTHRESHOLD 50
14 INSERT INTO "COUNTRIES" ( <<< insert문
"COUNTRY_ID"
, "COUNTRY_NAME"
, "REGION_ID"
) VALUES (:1, :2, :3)
(이하 생략)
Import 시 Tablespace
A 서버에서 export 하고, B 서버로 import 할 때
| 구분 | A 서버 | B 서버 | Import 결과(B 서버) | 비고 |
| Schema | HR | HR | HR |
| Default Tablespace | EXAMPLE | USERS | USERS | B 서버의 EXAMPLE tablespace 없을 시에만 |
※ B 서버에 EXAMPLE tablespace가 있다면 B 서버의 EXAMPLE tablespace로 import
※ AS-IS(A) 서버와 TO-BE(B) 서버의 사용자 계정과 각 사용자의 default tablespace를 동일하게 설정하고 Privilege와 Role을 동일하게 설정한 후에 Schema별로 exp를 수행해서 imp를 수행해라.
- 특정 Table만 export하기
$time exp ID/PW file=/data/exp/emp_dept.dmp tables=scott.emp,scott.dept
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 06:41:25 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 14 rows exported <<< EMP Table Export
. . exporting table DEPT 4 rows exported <<< DEPT Table Export
Export terminated successfully without warnings.
real 0m1.083s
user 0m0.017s
sys 0m0.003s
$ vi emp_dept.dmp
1 ^C^CiEXPORT:V11.02.00 <<< export 버전
2 DIMDBA <<< export를 수행한 계정
3 RTABLES <<< table을 export
4 8192 <<< 블록 사이즈
5 0
6 72
7 0
(중간 생략)
12 CONNECT SCOTT <<< table 스키마 유저
13 TABLE "EMP" <<< export table명
14 CREATE TABLE "EMP" ( <<< create문
"EMPNO" NUMBER(4, 0)
, "ENAME" VARCHAR2(10)
, "JOB" VARCHAR2(9)
, "MGR" NUMBER(4, 0)
, "HIREDATE" DATE
, "SAL" NUMBER( 7, 2)
, "COMM" NUMBER(7, 2)
, "DEPTNO" NUMBER(2, 0
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
15 INSERT INTO "EMP" ( <<< insert문
"EMPNO"
, "ENAME"
, "JOB"
, "MGR"
, "HIREDATE"
, "SAL"
, "COMM"
, "DEPTNO"
) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)
(중간 생략)
19 CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ( <<< index 생성
"EMPNO"
) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
20 ANALSTATS IS "EMP"
21 Y^@BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"PK_EMP"',NULL,NULL,NULL,14,1,14,1,1,1,0,6); END;
22 ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ( <<< constraint 추가
"EMPNO"
) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE
23 ANALSTATS TS "EMP"
(이하 생략)
- 여러 사용자를 동시에 export하기
$ time exp ID/PW file=/data/exp/scott_hr.dmp owner=scott,hr
Export: Release 11.2.0.4.0 - Production on Thu Nov 12 06:44:47 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
. exporting object type definitions for user HR
About to export SCOTT's objects ... <<< SCOTT User Export
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export HR's objects ... <<< HR User Export
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table COUNTRIES 25 rows exported
. . exporting table DEPARTMENTS 27 rows exported
. . exporting table EMPLOYEES 107 rows exported
. . exporting table JOBS 19 rows exported
. . exporting table JOB_HISTORY 10 rows exported
(이하 생략)
- evaluation buffer 값이 export에 주는 영향 테스트하기
- evaluation buffer 값을 설정하지 않고 export 수행
$ time exp ID/PW file=/data/exp/test01_1.dmp tables=scott.test01
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 21:17:18 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST01 5000000 rows exported
Export terminated successfully without warnings.
real 0m12.492s
user 0m3.484s
sys 0m0.551s
- evaluation buffer 값을 1M로 설정 후 export 수행
$ time exp ID/PW file=/data/exp/test01_2.dmp tables=scott.test01 buffer=1024000
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 21:20:23 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST01 5000000 rows exported
Export terminated successfully without warnings.
real 0m9.421s
user 0m3.689s
sys 0m0.343s
- evaluation buffer 값을 10M로 설정 후 export 수행
$ time exp ID/PW file=/data/exp/test01_3.dmp tables=scott.test01 buffer=10240000
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 21:22:40 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST01 5000000 rows exported
Export terminated successfully without warnings.
real 0m9.025s
user 0m3.652s
sys 0m0.415s
- evaluation buffer 값을 20M로 설정 후 export 수행
$ time exp ID/PW file=/data/exp/test01_4.dmp tables=scott.test01 buffer=20480000
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 21:28:31 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST01 5000000 rows exported
Export terminated successfully without warnings.
real 0m9.065s
user 0m3.717s
sys 0m0.368s
- direct path로 export 수행
$ time exp ID/PW file=/data/exp/test01_5.dmp tables=scott.test01 direct=y
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 21:30:56 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
Current user changed to SCOTT
. . exporting table TEST01 5000000 rows exported
Export terminated successfully without warnings.
real 0m8.486s
user 0m0.479s
sys 0m0.640s
※ 결과 비교
| 구분 | 설정 없이 | 1MB | 10MB | 20MB | Direct |
|---|
| 수행시간 | 12.492s | 9.421s | 9.025s | 9.065s | 8.486s |
- parameter file을 이용한 export 수행하기
$vi full.dat
file=/data/exp/full02.dmp
full=y
direct=y
$ time exp ID/PW parfile=full.dat
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 23:11:52 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
(이하 생략)
- 특정 조건만 export 받기 - query옵션 사용하기
- emp테이블에서 이름 처 글자가 F인 사람만 export 받기
$ exp ID/PW query=\"where ename like \'F%\'\"tables=scott.emp file=/data/exp/test06.dmp
Export: Release 11.2.0.4.0 - Production on Sun Nov 15 23:49:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 1 rows exported
EXP-00091: Exporting questionable statistics. <<< query 옵션 시 발생 statistics='none'
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
- emp테이블에서 job이 CLERK이고 급여가 1000 이상인 사람만 export 받기
$ exp ID/PW query=\"where job=\'CLERK\' and sal\>1000\" file=/data/exp/scott2.dmp tables=scott.emp statistics='none'
Export: Release 11.2.0.4.0 - Production on Mon Nov 16 00:05:29 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 2 rows exported
Export terminated successfully without warnings.
- parameter file에서 query옵션 사용하기 - escape문자 안 써도 됨.
$vi par2.dat
tables=scott.emp
query="where job='CLERK' and sal>1000" <<< escape 문자 안 써도 됨.
file=/data/exp/scott3.dmp
statistics='none'
$exp ID/PW parfile=par2.dat
Export: Release 11.2.0.4.0 - Production on Mon Nov 16 00:08:37 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 2 rows exported
Export terminated successfully without warnings.
- schema별로 자동 export백업 받는 스크립트
export LANG=C
export ORACLE_BASE=/sw/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11gR2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=TEST
sqlplus /nolog<<EOF3
conn /as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /sw/oracle/exp.tmp
select 'mkdir -p /data/backup/exp/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS')
from dual;
select distinct 'exp system/PW'||' owner='||lower(owner)||' file=/data/backup/exp/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS')||'/'||lower(owner)||'.dmp'||' filesize=100m direct=y'
from dba_tables where owner not in ('SYS','DBNMP','WMSYS','IX','SYSTEM','OE','PM','EXESYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS');
spool off
!cat /sw/oracle/exp.tmp | egrep -v SQL | egrep -v SYS > /sw/oracle/exp.sh
!sh /sw/oracle/exp.sh
exit
EOF3
- Import 수행하기
{warning:title=Import 시 주의사항}
Import 작업은 DDL과 DML을 수행하는 것이므로 Redo log와 Undo Segment를 사용
대량의 데이터를 import할 때는 충분한 용량의 Undo Tablespace를 준비(용량 부족 시 전체 rollback)
전체 rollback을 방지하기 위해서는 commit=y 사용으로 array 단위로 commit을 수행해야 한다.
{warning}- 전체 데이터 import 수행하기
$ imp system/PW file=/data/exp/full11.dmp ignore=y full=y
Full Import 사용 용도
DB전체를 이동할 때 사용
만약 A서버의 데이터를 B서버로 import 시 B서버에 같은 테이블이나 데이터가 존재한다면 추가
그러나 B서버에 제약조건이나 index(Primary Key나 Unique Index 등)가 있다면 데이터가 추가되지 않고 에러 발생
※ 특별한 경우 외는 잘 사용하지 않는다.
- 특정 사용자의 데이터만 import 수행하기
$ imp system/PW file=/data/exp/full11.dmp fromuser=scott tables=test01 ignore=y <<< full11.dmp: Full Export 파일
Import: Release 11.2.0.4.0 - Production on Mon Nov 16 01:14:20 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "TEST01" 5000000 rows imported
Import terminated successfully without warnings.
- scott 사용자의 test02 테이블을 hr 사용자 소유로 변경하기
$ exp ID/PW file=/data/exp/test02.dmp tables=scott.test02 <<< scott.test02 Export
$ imp ID/PW file=/data/exp/test02.dmp fromuser=scott touser=hr ignore=y <<< scott.test02을 hr.test02로 Import
Import: Release 11.2.0.4.0 - Production on Mon Nov 16 01:29:13 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into HR
. . importing table "TEST02" 1000 rows imported
Import terminated successfully without warnings.
- 실제 데이터는 Import하지 않고 DDL 문장만 추출하기
$ imp ID/PW file=/data/exp/test02.dmp show=y log=test02.log fromuser=scott touser=scott
Import: Release 11.2.0.4.0 - Production on Mon Nov 16 01:39:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "TEST02" ("NO" NUMBER, "ADDR" VARCHAR2(10)) PCTFREE 10 PCTUSE"
"D 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS "
"1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOG"
"GING NOCOMPRESS"
. . skipping table "TEST02"
Import terminated successfully without warnings.
$ vi test02.log <<< 로그 파일 편집
8.2 Datapump 살펴보기
- Datapump의 장점
- 작업 관리의 편의성
- 필요한 디스크 공간의 예측
- 원격지 DB에 작업 수행 가능(DB Link)
- remapping 기능 지원
- dump 작업하면서 압축을 동시에 진행
- 아주 빨라진 작업 속도 (exp/imp의 평균적으로 약 20배)
- 사용 전 환경 설정하기
- Datapump는 export/import와 다르게 유틸리티가 직접 OS 파일에 I/O를 할 수 없고 오라클에 directroy라는 객체를 통해서 간접적으로 접근이 가능함
- directroy를 생성하고 datapump를 수행하는 사용자에게 그 directory에 접근할 권한 부여
$ mkdir /data/dp
SQL> create directory datapump as '/data/dp/ ';
Directory created.
SQL> grant read, write on directory datapump to scott;
Grant succeeded.
SQL> grant create any directory to scott;
Grant succeeded.
- expdp 실행모드
- Full 모드
- Schema 모드
- Tablespace 모드
- Table 모드
- expdp 파라미터 정리
- expdp 실습
- scott계정의 emp, dept 테이블만 백업 받기
$ expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 16 20:59:21 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."T1": scott/******** tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."T1" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.T1 is:
/data/dp/emp_dept.dmp
Job "SCOTT"."T1" successfully completed at Mon Nov 16 20:59:32 2015 elapsed 0 00:00:06
- scott schema 전부 백업 받기
$ expdp scott/tiger schemas=scott directory=datapump dumpfile=scott.dmp
Export: Release 11.2.0.4.0 - Production on Mon Nov 16 23:04:16 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** schemas=scott directory=datapump dumpfile=scott.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.189 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
(이하생략)
- DB전체를 백업 받기
$ expdp system/PW full=y directory=datapump dumpfile=full01.dmp job_name=a
- 일시중단 후 다시 작업하기
$ expdp system/PW full=y directory=datapump dumpfile=full01.dmp job_name=a
Export: Release 11.2.0.4.0 - Production on Tue Nov 17 01:40:50 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."A": system/******** full=y directory=datapump dumpfile=full01.dmp job_name=a
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.542 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
^C <<< Ctrl+C 작업 취소
Export> stop_job <<< 일시 중단
Are you sure you wish to stop this job ([yes]/no): <<< Enter 입력
$ vi dp.sql <<< 중단된 작업 찾기
col owner_name for a10
col job_name for a10
col operation for a10
col job_mode for a10
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;
SQL> @dp
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- ---------- ---------- ---------- ------------------------------
SYSTEM A EXPORT FULL NOT RUNNING
$ expdp system/PW attach=system.a <<< 일시 중단된 작업에 재접속
Export: Release 11.2.0.4.0 - Production on Tue Nov 17 01:42:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: A
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 24B7CE812F713CDFE053EE131FAC8732
Start Time: Tuesday, 17 November, 2015 1:42:40
Mode: FULL
Instance: TEST
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y directory=datapump dumpfile=full01.dmp job_name=a
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /data/dp/full01.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Export> start_job <<< 재시작
Export> exit
SQL> @dp <<< 작업 상태 확인
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- ---------- ---------- ---------- ------------------------------
SYSTEM A EXPORT FULL EXECUTING
- 비정상적으로 종료된 job 취소하기
$ expdp system/PW full=y directory=datapump dumfile=full01.dmp job_name=a
Export: Release 11.2.0.4.0 - Production on Mon Nov 16 23:08:11 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."A": system/******** full=y directory=datapump dumpfile=full01.dmp job_name=a
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.542 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
(중간 생략)
ORA-31693: Table data object "SCOTT"."TEST01" failed to load/unload and is being skipped due to error: <<< 서버 disk full로 error & oracle 종료
ORA-19502: write error on file "/data/dp/full01.dmp", block number 262607 (block size=4096)
ORA-27072: File I/O error
Additional information: 4
Additional information: 262607
Additional information: 172032
ORA-31693: Table data object "SH"."CUSTOMERS" failed to load/unload and is being skipped due to error:
ORA-19502: write error on file "/data/dp/full01.dmp", block number 262671 (block size=4096)
ORA-27072: File I/O error
(중간 생략)
SQL> @dp
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- ---------- ---------- ---------- ------------------------------
SYSTEM A EXPORT FULL NOT RUNNING
$ expdp system/PW attach=system.a <<< job에 다시 접속 시도
Export: Release 11.2.0.4.0 - Production on Tue Nov 17 01:22:16 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp/full01.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
$ vi dpm.sql <<< 마스터 테이블 검색
set line 200
col owner.object for a15
SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name
AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
SQL> @dpm
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------- ---------------
VALID 88762 TABLE SYSTEM.A
SQL> drop table system.a; <<< 마스터 테이블 Drop
Table dropped.
SQL> @dp
no rows selected
- 여러 사용자의 테이블 한꺼번에 expdp 받기
$ expdp system/PW full=y directory=datapump dumpfile=scott16.dmp tables=scott.emp,hr.department
- 병럴로 expdb 작업하기
$ expdp system/PW full=y directory=datapump dumpfile=full04.dmp job_name=a parallel=4
- 병렬로 위치 분산하여 expdp 작업하기
expdp system/PW full=y job_name=a parallel=4 dumpfile=dp1:full1%U.dat,dp2:full2%U.dat,dp3:full3%U.dat,dp4:full4%U.dat filesize=100M
(중간 생략)
Master table "SYSTEM"."A" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.A is:
/data/dp1/full101.dat
/data/dp2/full201.dat
/data/dp3/full301.dat
/data/dp4/full401.dat
/data/dp1/full102.dat
/data/dp2/full202.dat
/data/dp3/full302.dat
/data/dp4/full402.dat
/data/dp1/full103.dat
/data/dp2/full203.dat
/data/dp3/full303.dat
/data/dp4/full403.dat
/data/dp1/full104.dat
Job "SYSTEM"."A" completed with 1 error(s) at Tue Nov 17 02:10:56 2015 elapsed 0 00:03:12
- 파라미터 파일 사용해서 expdp 수행 - 여러 개의 파일로 분할
$ vi expdp_pump.par
userid=system/PW
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y
$ expdp parfile=expdp_pump.par
(이하 생략)
- 상위 버전에서 하위 버전으로 데이터 이동하기 (11g → 10g)
$ expdp scott/tiger tables=emp directory=datapump dumfile=emp2.dmp version=10.2
- impdp 실습
- parameter 파일 이용해서 impdp 작업하기
vi impdp.par
userid=system/PW
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append
$ impdp parfile=impdp.par
- impdp 병렬 작업하기
$ impdp system/PW parallel=4 dumpfile=dp1:full1%U.dat,dp2:full2%U.dat,dp3:full3%U.dat,dp4:full4%U.dat Table_exists_action=append
- import 수행하지 않고 DDL 문장만 추출하기
$ impdp system/PW directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat
(이하 생략)
$ vi datapump.dat
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/sw/oracle/oradata/TEST/undotbs01.dbf' SIZE 26214400
(이하 생략)
- 설정된 Directory 경로 확인하기
vi dir.sql
set line 200
col owner for a10
col directory_name for a25
col directory_path for a60
select * from dba_directories
order by 1,2;
SQL> @dir
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------- ------------------------------------------------------------
SYS DATAPUMP /data/dp
SYS DATA_FILE_DIR /sw/oracle/product/11gR2/demo/schema/sales_history/
SYS DATA_PUMP_DIR /sw/oracle/admin/TEST/dpdump/
SYS DP1 /data/dp1
SYS DP2 /data/dp2
SYS DP3 /data/dp3
SYS DP4 /data/dp4
SYS LOG_FILE_DIR /sw/oracle/product/11gR2/demo/schema/log/
SYS MEDIA_DIR /sw/oracle/product/11gR2/demo/schema/product_media/
SYS ORACLE_OCM_CONFIG_DIR /sw/oracle/product/11gR2/ccr/hosts/ip-172-31-19-238.ap-north
east-1.compute.internal/state
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------- ------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR2 /sw/oracle/product/11gR2/ccr/state
SYS SS_OE_XMLDIR /sw/oracle/product/11gR2/demo/schema/order_entry/
SYS SUBDIR /sw/oracle/product/11gR2/demo/schema/order_entry//2002/Sep
SYS XMLDIR /sw/oracle/product/11gR2/rdbms/xml
14 rows selected.
- 일자별 schema별로 자동 백업받는 스크립트
$ vi expdp_script.sh
sqlplus /nolog << EOF3
conn "/as sysdba"
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool/home/oracle/expdp.tmp
SELECT '!mkdir -p /data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24')
FROM dual
;
SELECT 'create or replace directory datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||'as '||'"'||'/data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24')||'"'||';'
FROM dual
;
SELECT distinct 'grant read, write on directory '||'datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' to '||lower(owner)
FROM dba_tables
WHERE owner not in ('SYS'，'DBSNMP'，'WMSYS', 'IX'，'SYSTEM'，'OE'，'PM'，'EXFSYS'，'CTXSYS'，'OLAPSYS'，'MDSYS'，'SYSMAN', 'LOADER', 'XDB','ORDSYS','OUTLN','TSMSYS','DMSYS')
;
SELECT distinct '!expdp system/PW'||' schemas='||lower(owner)||' job_name='||lower(owner)||'_datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' directory=datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' dumpfile='||lower(owner)||'_%U.dmp'||' logfile='||lower(owner)||'.log '||'filesize=100M '
FROM dba_tables
WHERE owner not in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE', 'PM', 'EXFSYS','CTXSYS','OLAPSYS','MDSYS', 'SYSMAN', 'LOADER', 'XDB', 'ORDSYS', 'OUTLN', 'TSMSYS', 'DMSYS')
;
spool off
!cat /home/oracle/expdp.tmp | grep -v SOL | grep -v SYS > /home/oracle/expdp.sh
@/home/oracle/expdp.sh
exit
EOF3
- Datapump 작업 관리 및 모니터링하기
DBA_DATAPUMP_JOBS 딕셔너리를 조회
| 컬럼명 | 내용 |
|---|
| owner_name | 작업 계정 |
| job_name | 작업 명칭 |
| job_mode | full, table, index, tablespace 등 |
| state | executing(수행 중), defining, undefined, not running 등 |
- 12c Datapump New Feature
- LOGTIME 지원
- VIEW_AS_TABLE 옵션
8.3 SQL*Loader로 외부데이터 입력하기
- Convetional Path Method와 Direct Path Method
- High Water Mark란 데이터 파일에서 사용한 블록을 표시하는 마크
- Conventional Path 방법에서는 High Water Mark(HWM) 왼쪽에 있는 Free block에 데이터를 입력한다.
- 데이터를 입력할 때 HWM 왼쪽부터 입력하고 만약 HWM 왼쪽 블록을 다 사용하게 되면 입력 작업을 일시 중지하고 HWM을 오른쪽으로 이동(HWM Bump Up)하여 빈 블록들(Extent)을 확보한 후 다시 데이터를 입력한다.
- HWM Bump Up을 하면서 소요되는 시간 때문에 대량의 데이터를 입력할 때는 문제가 발생한다.
- Direct Path 방식은 처음부터 HWM 오른쪽에 있는 비어 있는 블록에 한꺼번에 데이터를 입력하고 commit을 한 번만 하는 방식을 사용한다.
- 다른 세션과 공유도 되지 않기에 Undo 데이터도 필요 없고, 롤백 시 실제 블록은 그대로 두고 딕셔너리 정보만 롤백하기에 롤백시간도 굉장히 짧다.
- Redo를 사용하지 않기 때문에 중간에 장애가 생길 경우 처음부터 다시 입력해야 한다.
- Direct Path 방식으로 작업 시 해당 테이블에 Exclusive Lock을 설정한다.
- 특정 테이블에서 이 기능을 사용하려면 해당 테이블에서 nologging 옵션이 활성화 되어야 한다.
- SQL*Loader
- SQL*Loader 수행 전 미리 확인해야 할 사항
- NLS_LANG 환경변수를 먼저 체크해서 서버의 Character set과 동일하게 설정
- ORACLE_HOME 환경변수 설정: ORA-12560: TNS:protocol adapter error 발생
- LD_LIBRARY_PATH 환경변수 설정(Unix만): libwtc8 library cannot be found error 발생
- ORA_NLS10 환경변수 설정(10g 이상): ORA_NLS10=$ORACLE_HOME/nls/data
- SQL*Loader의 구조와 특징
 |
- OS에서 다른 응용프로그램에서 생성된 특정 규칙을 가진 텍스트파일을 메모리에 거치지 않고 데이터파일로 직접 대량 입력한다.
- 입력 파일을 여러 개 가질 수 있다.
- 입력 파일의 여러 개의 레코드를 입력 시 하나의 논리적 레코드로 가공하여 한꺼번에 입력이 가능하다.
- 입력 필드가 고정길이나 가변길이로 가능하다.
- 입력 데이터가 문자, binary, 날짜, packed 십진 데이터, zone 십진 데이터 등의 여러가지 형태가 가능하다.
- 하나의 입력 데이터로 여러 테이블에 동시 입력이 가능하다.
- 테이블의 기존 데이터를 변경하거나 지우고 다시 입력하거나 추가하는 등의 작업도 가능하다.
- SQL함수를 사용하여 입력이 가능하다.
- Sequnce를 사용하여 데이터를 순차적으로 입력할 수 있다.
- SQL*Loader를 구성하는 주요 파일들
※ Oracle의 control file이 아니라 SQL*Loader를 사용하기 위한 정보를 가진 control file이다. 확장자는 ctl로 다음과 같은 정보를 포함한다.
- SQL*Loader 실습 1 - 입력할 데이터 외부 프로그램 자료일 경우
$vi ani.csv <<< 엑셀 csv 파일
F_Animal,, <<< 데이터 외 레코드 삭제
No,Name,Loc <<< 데이터 외 레코드 삭제
1,Cat,room1
2,Dog,room2
3,Monkey,room3
SQL> create table f_animal (
2 no number
3 , name varchar2(10)
4 , loc varchar2(10)
5 )
6 ;
Table created.
$vi f_ani.ctl
load data
infile "/home/oracle/ani.csv"
into table f_animal
fields terminated by ','
(no,name,loc)
$sqlldr scott/tiger control='/home/oracle/f_ani.ctl'
SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 19 19:31:51 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
SQL> select *
2 from f_animal;
NO NAME LOC
---------- ---------- ----------
1 Cat room1
2 Dog room2
3 Monkey room3
$vi f_ani.log
SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 19 19:35:43 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: /home/oracle/f_ani.ctl
Data File: /home/oracle/ani.csv
Bad File: /home/oracle/ani.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table F_ANIMAL, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NO FIRST * , CHARACTER
NAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
Table F_ANIMAL:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Thu Nov 19 19:35:43 2015
Run ended on Thu Nov 19 19:35:43 2015
Elapsed time was: 00:00:00.03
CPU time was: 00:00:00.00
- SQL*Loader 실습 2 - 입력 데이터가 control file 내부에 있을 경우
$ vi f_ani2.ctl
load data
infile *
replace
into table f_animal
fields terminated by ','
optionally enclosed by '"'
(no, name, loc)
begindata
11,"앵무새","11번방"
22,"기린","22번방"
33,"코뿔소","33번방"
$ sqlldr scott/tiger control='/home/oracle/f_ani2.ctl'
SQL> select * from f_animal;
NO NAME LOC
---------- ---------- ----------
11 앵무새 11번방
22 기린 22번방
33 코뿔소 33번방
- SQL*Loader 실습 3 - 각종 파일 사용하기(BAD File, DISCARD File 사용하기)
$ vi f_ani2.ctl
load data
infile *
append
into table f_animal
fields terminated by ','
optionally enclosed by '"'
(no, name, loc)
begindata
44,"대머리독수리","44번방"
55,"티라노사우루스","55번방"
$ sqlldr scott/tiger control='/home/oracle/f_ani2.ctl'
$ vi f_ani2.log
SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 19 20:06:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: /home/oracle/f_ani2.ctl
Data File: /home/oracle/f_ani2.ctl
Bad File: /home/oracle/f_ani2.bad <<< 문제가 되는 내용이 저장된 파일
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table F_ANIMAL, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NO FIRST * , O(") CHARACTER
NAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table F_ANIMAL, column NAME. <<< Error 메시지
ORA-12899: value too large for column "SCOTT"."F_ANIMAL"."NAME" (actual: 18, maximum: 10)
Record 2: Rejected - Error on table F_ANIMAL, column NAME.
ORA-12899: value too large for column "SCOTT"."F_ANIMAL"."NAME" (actual: 21, maximum: 10)
Table F_ANIMAL:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 2 <<< Error 건 수
Total logical records discarded: 0
$ vi f_ani2.bad
44,"대머리독수리","44번방"
55,"티라노사우루스","55번방"
SQL> alter table f_animal modify (name varchar(21));
Table altered.
$ vi f_ani2.ctl
load data
infile /home/oracle/f_ani2.bad
append
into table f_animal
fields terminated by ','
optionally enclosed by '"'
(no, name, loc)
$ sqlldr scott/tiger control='/home/oracle/f_ani2.ctl'
SQL> select * from f_animal;
NO NAME LOC
---------- --------------------- ----------
11 앵무새 11번방
22 기린 22번방
33 코뿔소 33번방
55 티라노사우루스 55번방
44 대머리독수리 44번방
- SQL*Loader 실습 4 - control file에서 position 사용하기
$ vi test3.prn
1000 나사장 사장 1000 0 10
1001 나상무 상무 700 0 20
1002 전부장 부장 500 500 30
1003 기왕애 대리 200 300 40
$ vi test3.ctl
load data
infile '/home/oracle/test3.prn'
into table test3
trailing nullcols
(
empno position(1:4) integer external,
name position(6:14) char,
position position(16:21) char,
sal position(23:26) integer external,
comm position(28:30) integer external,
deptno position(32:33) integer external
)
SQL> create table scott.test3 (
2 empno number,
3 name varchar2(10),
4 position varchar2(10),
5 sal number,
6 comm number,
7 deptno number
8 );
Table created.
$ sqlldr scott/tiger control='/home/oracle/test3.ctl'
SQL> select * from test3;
EMPNO NAME POSITION SAL COMM DEPTNO
---------- ---------- ---------- ---------- ---------- ----------
1000 나사장 사장 1000 0 10
1001 나상무 상무 700 0 20
1002 전부장 부장 500 500 30
1003 기왕애 대리 200 300 40
- SQL*Loader 실습 5 - Null 값을 0으로 표시하기
$ vi test4.csv
0001,홍길동,300,50,
0002,이순신,500,,
0003,강감찬,250,,
0004,유관순,630,,
$ vi test4.ctl
load data
infile '/home/oracle/test4.csv'
replace
into table test4
fields terminated by ','
(
번호 char,
이름 char,
급여 integer external,
상여금 integer external "nvl(:상여금,0)"
)
SQL> create table scott.test4 (
2 번호 number(4)
3 , 이름 varchar2(10)
4 , 급여 number(4)
5 , 상여금 number(4)
6 );
Table created.
$ sqlldr scott/tiger control='/home/oracle/test4.ctl'
SQL> select * from test4;
번호 이름 급여 상여금
---------- ---------- ---------- ----------
1 홍길동 300 50
2 이순신 500 0
3 강감찬 250 0
4 유관순 630 0
- SQL*Loader Express - 12c New Feature
: Control File 없이 입력할 데이터 파일만 가지고 SQL*Loader를 실행할 수 있는 Express 기능 새로 추가됨.
