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_KEEPACCOUNTDETAIL
FIELDS 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