Oracle关于导入导出

exp/imp

/**yhm-用户名  yhmm-用户密码  dbname=数据库名称**/

exp yhm/yhmm@dbname file=D:\expdb.dmp tables=(table1) query=\" where filed1 like '00%'\"

exp yhm/yhmm@dbname file=D:\expdb.dmp tables=(table1,table2,table3)  log=D:\explog.log

imp  yhm/yhmm@dbname file=D:\expdb.dmp  tables=(table1)

imp yhm/yhmm@dbname file=D:\expdb.dmp log=D:\implog.log indexes=y grants=y buffer=1024000 fromuser=yhm touser=yhm ignore=y

expdp/impdp

导出yhm/yhmm@127.0.0.1:1521/dbname 

/**创建逻辑目录**/

select * from dba_directories;(查看管理员目录)

create directory migrateas ‘D:\migrate’

/**给yhm用户赋予指定目录的操作权限(最好以system等管理员赋予)**/

grant read,write on directory migrate to yhm

/**按用户导出**/

expdp yhm/yhmm@127.0.0.1:1521/dbname schemas=yhm dumpfile=expdp.dmp DIRECTORY=migrate

/**按用户导入**/

impdp yhm/yhmm@127.0.0.1:1521/dbname  directory=migrate dumpfile=expdp.dmp logfile=expdp.log remap_tablespace=yhmtablespace:yhmtablespace

/**expdp导出指定的表**/

expdp yhm/yhmm@127.0.0.1:1521/dbname dumpfile=expdp.dmp DIRECTORY=migrate tables=table1,table2,table3

/**impdp导入指定表**/

impdp yhm/yhmm@127.0.0.1:1521/dbname directory=migrate dumpfile=expdp.DMP logfile=impdp.log remap_schema=yhm:yhm remap_tablespace=yhmtablespace:yhmtablespace

expdp yhm/yhm@dbname  tables=table1 dumpfile=table1.dmp logfile=table1.log query=\"where rownum \< 5\"

sqlldr bras/bras@jsnx control=D:\.ctl data=D:\.del log=D:\.log

 sqlldr/ctl文件

sqlldr bras/bras@jsnx control=D:\.ctl data=D:\.del log=D:\,log

Sqlldr bras/bras@jsnx control=D:/cbod_mir_saacnacn.ctl log=d:/cbod_mir_saacnacn.log errors=50000 rows=10000;

Sqlldr bras/bras@jsnx control=D:/cbod_mir_saacnacn.ctl data=D:/cbod_mir_saacnacn.del log=d:/cbod_mir_saacnacn.txt;

/**ctl文件格式**/

load  data

infile ‘d:\table1.del’

truncateinto table table1 fields terminated by x’1D’ optionally enclosed by X’1E’ Trailing nullcols(字段名);

load data

 CHARACTERSET AL32UTF8  /**指定字符**/

infile ‘d:\table2.del’

truncate into table table2 fields terminated by ‘,’ optionally enclosed by ‘”’ Trailing nullcols(字段名);

Load  data

Truncate into table table3 fields terminated by ‘,’ optionally enclosed by ‘”’ Trailing nullcols(字段名);