3种方式导出导入数据/备份表(都可以查看Log):
1:Oracel Import(exp/imp.exe)
生成dmp文件,注意导入和导出的参数,是同名表导入导出。
2:SQL Inserts(sqlplus.exe/command window)
生成sql文件,修改sql文件,可以在不同名表导入导出。
3:PL/SQL Developer
生成pde文件,生成和sqlplus一样的sql(不能修改,pde是二进制文件),有多个参数可选择,选择性地
执行sql中的命令。
exp.exe dac_etl_daily/\""Oracle.123\""@ETLDMO file=d:\dac_daily.dmp owner=(dac_etl_daily)
imp.exe dac_etl_daily_q/oracle@DMOETLNEW file=d:\dac_daily.dmp fromuser=dac_etl_daily touser=dac_etl_daily_q
有些表有blob类型的字段,导致这些表导入失败,
exp.exe dac_etl_daily/\""Oracle.123\""@ETLDMO file=d:\dac_daily1.dmp TABLES=(W_ETL_SYSPROP,W_ETL_DBCONN,W_ETL_SERVER,TMP_SYSPROP,TMP_DBCONN)
imp.exe dac_etl_daily_q/oracle@DMOETLNEW file=d:\dac_daily2.dmp fromuser=dac_etl_daily touser=dac_etl_daily_q IGNORE=Y
ignore=y:如果表存在,则只导入数据
ignore=n:如果表存在,则中止导入操作
*********************************************************************
C:\app\product\11.2.0\client_2\BIN\sqlldr.exe可用于向Oracle数据库中导入纯文本
excel文件可以先另存为txt(以制表符分隔), csv(以逗号分隔)文本,再用sqlldr导入数据库
cd C:\app\product\11.2.0\client_2\BIN
sqlldr userid=system/manager@oracle control='C:\Test\input.ctl' log='C:\Test\hh_input.log'
--sqlldr userid=ulap_i/\""o566730@larghjfi.com\""@BIEE12Ccontrol='C:\QYQ\input_hotel.ctl' log='C:\QYQ\hh_input1.log'
a)input.ctl,内容如下:
LOAD DATA
INFILE 'C:\Test\rre.csv'
APPEND INTO TABLE wc_coach_det_f
FIELDS TERMINATED BY ","
(ROW_WID,
MONTH_WID,
COACH_DT_WID,
DSM_POSTN_WID,
SR_POSTN_WID,
VIS_BU_ID,
VIS_PR_POST_ID,
SCORE_TYPE_WID,
POINTS,
KPI_FLG_WID,
COACH_TYPE_CD,
INTEGRATION_ID,
PAR_INTEGRATION_ID,
W_INSERT_DT DATE "MM/DD/YYYY HH24:MI:SS",
W_UPDATE_DT DATE "MM/DD/YYYY HH24:MI:SS",
DATASOURCE_NUM_ID,
ETL_PROC_WID,
DSM_EMP_WID,
SR_EMP_WID,
STATUS_WID
)
b)input.ctl,内容如下:
load data --1、控制文件标识
infile 'test.txt' --2、要输入的数据文件名为test.txt
append into table test --3、向表test中追加记录
fields terminated by X'09' --4、字段终止于X'09',是一个制表符(TAB)
(id,username,password,sj) -----定义列对应顺序
a、insert,为缺省方式,在数据装载开始时要求表为空
b、append,在表中追加新记录
c、replace,删除旧记录,替换成新装载的记录
d、truncate,同上
c)在控制文件中直接导入数据
-- The format for executing this file with SQL Loader is:
-- SQLLDR control=<filename> Be sure to substitute your
-- version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'
DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
COUNTRYID NULLIF (COUNTRYID="NULL"),
COUNTRYCODE,
COUNTRYNAME,
CONTINENTID NULLIF (CONTINENTID="NULL"),
MAPID NULLIF (MAPID="NULL"),
CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL
3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL
4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL
5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL
6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL
7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL
8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL
9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL
10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL
11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL
12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL
13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL
14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL
15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL
16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL
17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL
18;"BR";"Brazil";5;85;"