sqlplus dumping data 博客分类： sqlplus spoolload data sqlplussqlldrdataDumping

sqlplus dumping data

startDumping4Dev.bat

 sqlplus wc/wc@pro_server @%1\table_manipulate\template_db_export.sql

 sqlplus wc/wc@dev_server @%1\table_manipulate\instance_db_cleanup.sql

 sqlldr wc/wc@dev_server control=%1\table_import\MSCP_T_CHECKLIST.ctl log=log1.log bad=bad1.log rows=500 parallel=true

 sqlplus wc/wc@dev_server @%1\table_manipulate\template_db_copy.sql

 

Please note: 

just support for five type: Char, Date, Long, Number,Varchar2

we use different seprated sqlplus 'COPY' and 'spool' because the oracle database cannot support the timestamp with using copy if a table contains a column whose type is timestamp...!!!

 CPY-0012: Datatype cannot be copied

 

1.cleanup.sql

 delete ...

 

2.export.sql:

set line 10000

set pagesize 0

set feedback off

 

set heading off

set trimspool on

set trims on

set echo on

set colsep '<EOFD>'

set termout OFF

 

spool C:\opt\Template.txt

select TRIM(ID)||'<EOFD>'|| TRIM(NAME) 

from table;

 

3.template.ctl

load data

infile 'C:\opt\Template.txt'

REPLACE 

INTO TABLE table

FIELDS TERMINATED BY '<EOFD>'

trailing nullcols

(ID,name)

 

4.copy.sql

COPY FROM wc/wc@PRO_SERVER INSERT TABLE USING SELECT * FROM TABLE;