Why uses Export Import Export Export Command Line

Why uses Export / Import ?

Export

호출 유형에 따른 Export Ÿ Command Line에서 Export 하기 Ÿ Parameter File을 통해 Command Line에서 Export 하기 Ÿ 문답식 다이얼로그를 통해 Export하기

Command Line에서 Export 하기 Syntax EXP keyword = value or keyword =value 1, value 2, . . . value n Example exp scott/tiger grants=yes tables=emp, dept

exp_param File을 통해 Export 하기 exp_param (parameter file) USERID=SCOTT/TIGER TABLES=(EMP) FILE=EMP_EXP. DMP GRANTS=Y INDEXES=Y DIRECT=Y Syntax EXP PARFILE = filename EXP username/password PARFILE = filename Example exp parfile = exp_param

대화형 다이얼로그를 통한 Export $ exp Export: Release 8. 1. 5. 0. 0 - Production on Wed May 3 17: 02: 54 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Username: scott/tiger Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Enter array fetch buffer size: 4096 > Export file: expdat. dmp > (2)U(sers), or (3)T(ables): (2)U > 3 Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in KO 16 KSC 5601 character set and KO 16 KSC 5601 FIXED NCHAR character set About to export specified tables via Conventional Path. . . Table(T) or Partition(T: P) to be exported: (RETURN to quit) > emp. . exporting table EMP 14 rows exported Table(T) or Partition(T: P) to be exported: (RETURN to quit) > Export terminated successfully without warnings.

Exporting Data Table Mode User Mode Full Database Mode Table definitions Table data Owner’s table grants Owner’s grants Grants Owner’s table indexes Owner’s indexes Indexes Table constraints

Exporting Data Table Mode User Mode Full Database Mode Table triggers Clusters Database links Sequences Snapshot logs Stored procedures Private synonyms Views Profiles Rollback segment Definitions System audit options System privileges Tablespace definitions Tablespace quotas User definitions

Exporting Data Keyword Default Value Description USERID BUFFER COMPRESS FILE GRANTS ROWS FULL OWNER TABLES INDEXES CONSTRAINTS RECORDLENGTH DIRECT 미정의 시스템 종속적 yes expdat. dmp yes no 사용자 이름과 비밀번호 데이터 버퍼의 크기 Extents의 압축 미정의 yes 출력 파일 Grant의 Export 데이터 행의 Export 전체 데이터 베이스 Export 할 사용자 Export 할 테이블 인덱스의 정의 Export 테이블의 제약조건 Export 시스템 종속적 yes 파일 레코드의 바이트 길이 Direct Mode export

Using Export for Backup Incremental Export Type Description Complete Incremental 모든 테이블과 데이터 정의 어떤 유형에 관계 없이 가장 최근에 Export 된 이후에 갱신된 객체만 최근에 Cumulative 나 Full Export 한 이후에 갱신된 모든 테이블 Cumulative

Backup을 위한 Export의 사용 Syntax EXP username/password INCTYPE = INCREMENTAL CUMULATIVE COMPLETE Example exp system/manager inctype = incremental Ÿ Benefits – – 공간 절약 : 작은 Export 파일 시간 절약 : 짧은 Export 시간

Export session in Full Database mode Ÿ Interactive mode method $ exp system/manager Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production Enter array fetch buffer size : 4096 > (RETURN) Export file : expdat. dmp > (1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > e Export grants (yes/no) : yes > yes Export table data (yes/no) : yes > yes Compress extents (yes/no) : yes > yes About to export the entire database. . . exporting tablespace definitions. exporting profiles. exporting user definitions. exporting role. exporting rollback segment definitions. exporting database links. exporting sequence numbers. exporting cluster definitions. exporting stored procedures. about to export SYSTEM's tables. . about to export SCOTT's tables. . exporting synonyms. exporting views. exporting referential integrity constraints. exporting triggers Export terminated successfully without warnings.

Full Database mode에서의 Export Ÿ Command line method $ exp userid=system/manager full=y compress=y grants=y rows=y Ÿ Dynamic method – EXPORT 매개변수를 다음과 같은 파일(tusc. par) 형태로 만 든다 Userid=system/manager full=yes compress=yes grants=yes rows=yes $ exp parfile=tusc. par

User mode에서의 Export Ÿ Interactive mode method $ exp scott/tiger Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Enter array fetch buffer size : 4096 > (RETURN) Export file : expdat. dmp > (1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > u Export grants (yes/no) : yes > yes Export table data (yes/no) : yes > yes Compress extents (yes/no) : yes > yes About to export SCOTT’s objects. exporting snapshot log. exporting database links. exporting sequence numbers. exporting cluster definitions. exporting stored procedures. about to export SCOTT's tables. . exporting synonyms. exporting views. exporting referential integrity constraints. exporting triggers Export terminated successfully without warnings.

Export session in User mode Ÿ Command line method $ exp userid=scott/manager grants=yes rows=yes Ÿ Dynamic method – EXPORT 매개변수를 다음과 같은 파일(tusc. par) 형태로 만든다 userid=scott/tiger grants=yes rows=yes $ exp parfile=tusc. par

Table mode에서의 Export 일반 사용자가 할 경우 Ÿ 대화형 방법 $ exp scott/tiger Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Enter array fetch buffer size : 4096 > (RETURN) Export file : expdat. dmp > scott. dmp (1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > T Export grants (yes/no) : yes > yes Export table data (yes/no) : yes > yes Compress extents (yes/no) : yes > yes About to export specified tables Table Name : emp. Export table emp 423 rows exported Table Name : dept. Export table dept 20 rows exported Export terminated successfully without warnings.

Table mode에서의 Export 일반 사용자가 할 경우 Ÿ 커맨드라인 방법 $ exp userid=soctt/tiger file=scott. dmp tables=emp, dept compress=yes grants=yes rows=yes

Table mode에서의 Export EXP_FULL_DATABASE role 소유자가 할 경우 Ÿ 대화형 방법 $ exp system/manager Export: Release 8. 1. 5. 0. 0 - Production on Sat May 6 17: 08: 18 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Enter array fetch buffer size : 4096 > (RETURN) Export file : expdat. dmp > (1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > T Export grants (yes/no) : yes > yes Export table data (yes/no) : yes > yes Compress extents (yes/no) : yes > yes About to export specified tables via Conventional Path. . . Table(T) or Partition(T: P) to be exported: (RETURN to quit) > scott. emp Current user changed to SCOTT. Export table emp 423 rows exported Table(T) or Partition(T: P) to be exported: (RETURN to quit) > mary. app Current user changed to MARY. Export table app 420 rows exported Export terminated successfully without warnings.

Table mode에서의 Export EXP_FULL_DATABASE role 소유자가 할 경우 Ÿ 커맨드라인 방법 $ exp userid=system/manager tables=scott. emp, mary. app compress=yes grants=yes rows=yes indexes=yes

Direct Path Concepts Oracle Server Export Generate SQL Commands Two-Task Common (TTC) SQL Buffer Command Cache Processing Manager Write Blocks Export Dump File TTC Buffer Direct Path Evaluating Buffer Cache Read Blocks Oracle Server

User Interface in Direct Path Direct parameter 의 사용법 • 파라미터 파일에서 키워드 이용 : EXP PARFILE=filename • EXP 명령에 대한 명령어 라인 옵션의 이용: EXP USERID=scott/tiger FULL=YES DIRECT=YES

User Interface in Direct Path exp_param (parameter file) USERID=SCOTT/TIGER TABLES=EMP FILE=EMP_EXP. DMP GRANTS=Y INDEXES=Y DIRECT=Y Example exp parfile = exp_param

Import

명령어 라인에서의 import Syntax IMP keyword = value or keyword = (value 1, value 2, . . . value n Example imp scott/tiger grants=yes tables=emp, dept

imp_param 파일을 통한 Import Imp_param (parameter file) USERID=SCOTT/TIGER TABLES=EMP FILE=EMP_EXP. DMP GRANTS=YES INDEXES=YES Syntax IMP PARFILE=filename IMP username/password PARFILE=filename Example imp parfile=imp_param

Importing Data in interactive dialog $ imp scott/tiger Import: Release 8. 1. 5. 0. 0 - Production on Sat May 6 20: 50: 38 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Import file: expdat. dmp > Enter insert buffer size (wminimum is 4096) 30720> Export file created by EXPORT: V 08. 01. 05 via conventional path List contents of import file only (yes/no): no > Ignore create error due to object existence (yes/no): no > y Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no > Username: scott Enter table names. Null list means all tables for user Enter table name or. if dons: emp Enter table name or. if done: . . importing SCOTT’s objects into SCOTT. . importing table “EMP” 14 rows imported Import terminated successfully without warnings.

Importing Data Table Mode User Mode (Table) Full Database Mode Table definitions Table data Table definitions Tablespace definitions Profile Owner’s table grants Table data Table grants Table indexes Owner’s table indexes Analyze table Column comments Audit Private synonyms User views Stored procedures Analyze cluster Referential constraints Trigger Table constraints Referential constraints Table Trigger Postable actions User definition Roles System privilege grants Role grant Default roles Tablespace quotas Resource cost Rollback segment definitions Database links Sequence number Snapshots Snapshot logs

Importing Data Table Mode User Mode (User) Full Database Mode Snapshots Job sequence Cluster definitions Table definition Table Data Table constraints Table grants Table indexes Analyze table Column comments Audit Referential constraint Postable actions Synonyms Views Stored procedure Triggers analyze cluster Snapshot logs Job queues Database Links Sequence Number Cluster Definition

Importing Data Keyword USERID BUFFER FILE SHOW GRANTS ROWS IGNORE FULL TABLES Default Value 30720 expdat. dmp NO YES NO NO Description 사용자 이름과 비밀번호. 데이터 버퍼의 크기. 출력 파일. contents 만 나열. grant(허가)를 Import. 데이터 행을 Import. 오브젝트의 존재로 인한 create errors 를 무시. 전체 파일을 Import 할 테이블 나열.

Importing Data Keyword Default Value Description INDEXES COMMIT RECORDLENGTH PARFILE TOUSER FROMUSER HELP INDEXFILE INCTYPE LOG DESTROY YES NO System dependent Undefined 인덱스의 정의 Import 각 배열 삽입 후 COMMIT 파일 I/O record의 Byte 길이 Parameter specifications 데이터가 Import 될 사용자 이름 나열 Object가 Import 될 사용자 이름 나열 Import parameters 나열 인덱스 정의가 저장될 파일 나열 RESTORE 또는 SYSTEM 로그 메시지를 위한 파일 이름 현존 데이터의 이름 재사용 N Undefined

Using Import for Recovery Syntax IMP username/password INCTYPE = SYSTEM RESTORE SYSTEM : 시스템 오브젝트를 import RESTORE : 사용자 오브젝트를 import Example imp system/manager inctype = restore

특정 사용자의 선택된 테이블의 Import Ÿ Full Database Export 파일을 사용 Ÿ 관리자가 특정 스키마의 특정 오브젝트만 Import 함 Ÿ Command line method imp system/manager file=dba. dmp fromuser=scott tables=emp, dept

특정 사용자의 선택된 테이블의 Import Ÿ Interactive mode method $ imp system/manager Import: Release 8. 1. 5. 0. 0 - Production on Sat May 6 21: 08: 03 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Import file: expdat. dmp > (RETURN) Enter insert buffer size (minimum is 4096) 30720 > (RETURN) Export file created by EXPORT: V 08. 01. 05 List contents of import file only (yes/no) : no > Ignore create errors due to object existence (yes/no) : yes > Import grants (yes/no) : yes > Import table data (yes/no) : yes > Import entire export file (yes/no) : yes > no User name : SCOTT Enter table name. Null list name all tables for user Enter table name or. if done : EMP importing SCOTT's objects into system. importing table EMP 900 rows imported Import terminated successfully.

다른 사용자가 Export 한 테이블의 Import Ÿ Interactive mode method $ imp scott/tiger Import: Release 8. 1. 5. 0. 0 - Production on Sat May 6 21: 08: 03 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle 8 i Release 8. 1. 5. 0. 2 - Production With the Java option PL/SQL Release 8. 1. 5. 0. 0 - Production Import file: expdat. dmp > jane. dmp Enter insert buffer size (minimum is 4096) 30720 > (RETURN) Export file created by EXPORT: V 08. 01. 05 List contents of import file only (yes/no) : no > Ignore create errors due to object existence (yes/no) : yes > Import grants (yes/no) : yes > no Import table data (yes/no) : yes > Import entire export file (yes/no) : yes > no User name : jane Enter table name. Null list name all tables for user Enter table name or. if done : EMP Enter table name or. if done : DEPT importing JANE's objects into SCOTT. importing table EMP 900 rows imported. importing table DEPT 20 rows imported Import terminated successfully.

SQL*Loader

SQL*Loader Control File Datafile Records SQL*Loader Discard File (optional) Oracle Server Inserted Database Files Bad File Log File

SQL*Loader Files Ÿ Control files Ÿ Data files Ÿ Log file Ÿ Bad file Ÿ Discard file

SQL*Loader Ÿ Conventional Path Loading (전통적인 경로) Ÿ Direct Path Loading (직접적인 경로)

Uses of SQL*Loader Control File Datafile Records SQL*Loader Field Processing Discarded Rejected Accepted WHEN Clause Evaluation Discard File (optional) Selected Bad File Oracle Server Inserted Database Files Log File

Control File Example 1 The control file is ULCASE. CTL: 1) LOAD DATA 2) INFILE * 3) 4) 5) 6) INTO TABLE dept FIELDS TERMINATED BY ', ' OPTIONALLY ENCLOSED BY '"' (deptno, dname, loc) BEGINDATA 12, RESEARCH, "SARATOGA" 10, "ACCOUNTING", CLEVELAND 11, "ART", SALEM 13, FINANCE, "BOSTON" 21, "SALES", ROCHESTER 42, "INT’L", "SAN FRAN"

Control File Example 2 <ulcase 2. ctl> LOAD DATA INFILE 'ulcase 2. dat' INTO TABLE EMP ( EMPNO POSITION(01: 04) INTEGER EXTERNAL, ENAME POSITION(06: 15) CHAR, JOB POSITION(17: 25) CHAR, MGR POSITION(27: 30) INTEGER EXTERNAL, SAL POSITION(32: 39) DECIMAL EXTERNAL, COMM POSITION(41: 48) DECIMAL EXTERNAL, DEPTNO POSITION(50: 51) INTEGER EXTERNAL) <ulcase. dat> 7782 CLARK 7839 KING 7499 ALLEN MANAGER 7839 2572. 50 PRESIDENT 5500. 00 SALESMAN 7698 1600. 00 300. 00 10 10 30

Invoking SQL*Loader Ÿ Syntax SQLLDR keyword = value or keyword = value 1, value 2, . . . value n Ÿ Example sqlldr scott/tiger control=sample. ctl data=sample. dat

Example Ÿ Insert option : 비어 있는 table에 넣을 때 load data infile * insert into table ldr_option (a position (1: 5), b position (5: 10 )) begindata aaaaa 11111 bbbbb 22222 Ÿ replace option : table의 기존 행을 모두 삭제(delete)하고 insert Ÿ Ÿ append option : 새로운 행을 기존의 data에 추가 truncate option : table의 기존 행을 모두 truncate하고 insert

Conventional과 Direct Data Path Oracle Server SQL*Loader Generate SQL Commands SQL Space Buffer Command Management Cache Processing Manager Write Blocks SQL*Loader Write Database Get New Block Extent Direct Path Buffer Cache Read Blocks Oracle Server

Loading Data Using Direct Path Ÿ Direct Load 를 명시하기 위해서 명령 라인에 DIRECT = TRUE 옵션을 사용한다. $ sqlldr USERID=scott/tiger CONTROL = test. ctl LOG = test. log Direct = TRUE

Additional Resources Ÿ Oracle Manual – Oracle Server Utilities Ÿ http: //technet. oracle. co. kr Ÿ http: //docs. oracle. com

#1 in e-business