Oracle export and import data

Oracle support 2 types of data export/import

1. Utilityexp/imp

-- But data format is binary,people cannot read and write them.

2. Using sqlplus/sqlldr

-- Export data as text file,and import as text file also.

Following is a sample using sqlplus/sqlldr to export and import a table.

Suppose table is defined as:

SQL> desc KSDS09;

NameNull? Type

------------------------------------------------- ----------------------------

KSDS09_SEQ_NUM NOT NULL NUMBER(8)

K_NUM NOT NULLNUMBER(6)

K_NUM02 NOT NULL NUMBER(2)

K_X NOT NULL CHAR(22)

D01VARCHAR2(30)

REC0_END0CHAR(4)

(e.g.,export DB_LOGIN=scott/tiger@orcl)

-- Export script:

sqlplus -s ${DB_LOGIN} >/dev/null<<EOF

set heading off;

set echo off;

set feedback off;

set verify off;

set wrap off;

set pagesize 0;

set linesize 2500;

set trimout on;

set trimspool on;

set trims on;

spoolKSDS09.txt;

selectKSDS09_SEQ_NUM || ',' || K_NUM || ',' || K_NUM02 || ',' || K_X || ',' || D01 ||',' || REC0_END0 from KSDS09;

set define on;

set heading on;

set echo on;

set feedback on;

spool off;

quit;

EOF

-- Import script:

step 1: define sqlldr control file

$ catKSDS09.ctl

load data

infile'KSDS09.txt'

appendinto table KSDS09H

fields terminated by ","

(KSDS09_SEQ_NUM,K_NUM,K_NUM02,K_X,D01,REC0_END0)

step 2: execute sqlldr command

sqlldr userid=${DB_LOGIN}control=KSDS09.ctl

-- Appendix: data in KSDS09.text

$ head KSDS09.txt

1,10,1,AAAA,ABCD,1234

2,2,BBBB,1234

3,3,CCCC,1234

...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!