oracle spool导出数据和sqlldr导入数据

一.利用spool技术导出数据

1.在pl/sql或者sqlplus中执行

 -- Oracle批量导出数据  //设置列分隔符    SQL> set colsep '
'  //设置不分页　    SQL> set pageSize  0  //保存导出的文件路径　    SQL> spool C:\Users\Administrator\Desktop\backup\account.txt  //导出数据的查询语句      SQL> select * from user  //关闭spool     SQL> spool off

导出刚开始文件会卡顿一下然后会迅速的导出

2.或者将执行的语句放在文件中 通过 @c:user\a.sql去执行

二.利用sqlldr技术导入数据

1.创建account.ctl文件

LOAD DATA  INFILE 'C:\Users\Administrator\Desktop\backup\account.txt'TRUNCATE INTO TABLE BOK_KEEPACCOUNTDETAILFIELDS TERMINATED BY '
'OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS(seqno            ,pseqno            ,subject                ,coreaccount          ,crdrfg    ,damount        ,camount              ,bookno          ,redmark               ,accountid                ,balance        ,subjectbalancedir                ,effectflag          ,lstmntuser         ,lstmntdate         "to_date(SUBSTR(:lstmntdate,0,19),'yyyy-mm-dd-hh24.mi.ss')"  ,deptcode        )

2.cmd中执行以下语句 运行导入的ctl文件地址

sqlldr userid=zcgldb/zcgldb@192.168.30.91:1521/orcl control=C:\Users\Administrator\ACCOUNT.CTL errors=100 direct=y