oracle dataexport,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<

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

...

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。