客户端工具 EXP、IMP
上游系统给我们

  1. 文本格式（csv）
    逗号隔开字段的文本文件，camera splited values ,
    1,yan,”suzhou,yuanqu” qouted
  2. excel
  3. oracle ->imp/impdb/sqlldr2
    ；，| chr(6)
    1【用 exp 数 据 导 出】：
    1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
    exp system/manager@TEST   rows=y  indexes=y compress=n buffer=65536   feedback=100000full=y  file=d:\daochu.dmp  log=d:\daochulog.txt   owner=(ECCBIZ,ECCCUSTOMER)

记得导入数据库当中表名称或者字段有中午，
要在.bashrc 设置

export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

接下来可以导入中文表名称的数据了。
ssh oracle@192.168.10.3
oracle 是密码

exp yangang/123456 file=/tmp/yangang.dat 

imp yangang/123456 file=/tmp/yangang.dat  tables=A,B,C
cat expalluser.sh
#!/bin/bash
sqlplus -s system/oracle@orcl <<< "select username  from all_users;" >> user.lst
for user in ` cat user.lst | grep -v '\-\-' |grep -v 'USERNAME'  |sed '/^$/d' |grep -v SYSTEM |grep -v SYS
`
do
        exp $user/123456 file=/tmp/wangyu-${user}.dat
done

导出数据

imp system/oracle file=hr.dmp  ignore=yes touser=diguotao full=y

作业

每天晚上1点，从大连的数据库的本机 dalian01的用户，把数据导入192.168.10.3的DALIN01的用户里

1. 创建一个脚本文件

a. export NLSLANG="AMERICAN_AMERICA.AL32UTF8"
b. exp  DALIAN01/123456@DALIAN.WOOW.INK:15210/orcl file=/dalian01.dmp
c. imp  DALIAN01/123456@目标机器的IP地址/orcl  file=dalian01.dmp  full=y
d. rm -f  dalian01.dmp

2. 创建一个job（crontab -e)

关键字 说明 默认
USERID 用户名/口令
FULL 导出整个文件 (N)
BUFFER 数据缓冲区的大小
OWNER 导出指定的所有者用户名列表
FILE 输出文件 (EXPDAT.DMP)
TABLES 导出指定的表名列表
COMPRESS 是否压缩导出的文件 (Y)
RECORDLENGTH IO 记录的长度
GRANTS 导出权限 (Y)
INCTYPE 增量导出类型
INDEXES 导出索引 (Y)
RECORD 跟踪增量导出 (Y)
ROWS 导出数据行 (Y)
PARFILE 参数文件名
CONSTRAINTS 导出限制 (Y)
CONSISTENT 交叉表一致性
LOG 屏幕输出的日志文件
STATISTICS 分析对象(ESTIMATE)
DIRECT 直接路径 (N)
TRIGGERS 导出触发器 (Y)
FEEDBACK 显示每 x 行 (0) 的进度
FILESIZE 各转储文件的最大尺寸
QUERY 选定导出表子集的子句
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TABLESPACES 导出指定的表空间列表
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
linux tables后面带圆括号是不可以的。
4 将数据库中的表table1中的字段filed1以”00″打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\” where filed1 like ‘00% ‘\”
上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。不过在上面命令后面 加上 compress=y 就可以了。
【用 imp 数 据 导 入】：
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST ignore=y full=y file=d:\daochu.dmp log=d:\daoru.txt
关键字 说明 默认
USERID 用户名/口令
FULL 导入整个文件 (N)
BUFFER 数据缓冲区大小
FROMUSER 所有人用户名列表
FILE 输入文件 (EXPDAT.DMP)
TOUSER 用户名列表
SHOW 只列出文件内容 (N)
TABLES 表名列表
IGNORE 忽略创建错误 (N)
RECORDLENGTH IO记录的长度
GRANTS 导入权限 (Y)
INCTYPE 增量导入类型
INDEXES 导入索引 (Y)
COMMIT 提交数组插入 (N)
ROWS 导入数据行 (Y)
PARFILE 参数文件名
LOG 屏幕输出的日志文件
CONSTRAINTS 导入限制 (Y)
DESTROY 覆盖表空间数据文件 (N)
INDEXFILE 将表/索引信息写入指定的文件
SKIPUNUSABLEINDEXES 跳过不可用索引的维护 (N)
FEEDBACK 每 x 行显示进度
TOID_NOVALIDATE 跳过指定类型 ID 的验证
FILESIZE 每个转储文件的最大大小
STATISTICS 始终导入预计算的统计信息
RESUMABLE 在遇到有关空间的错误时挂起
RESUMABLE_NAME 用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT RESUMABLE 的等待时间
COMPILE 编译过程, 程序包和函数 (Y)
STREAMS_CONFIGURATION 导入 Streams 的一般元数据 (Y)
STREAMS_INSTANITATION 导入 Streams 的实例化元数据 (N)
TRANSPORT_TABLESPACE 导入可传输的表空间元数据
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户

  1. 获取帮助
    imp help=y
  2. 导入一个完整数据库
    imp system/manager file=bibledb log=dibledb full=y ignore=y
  3. 导入一个或一组指定用户所属的全部表、索引和其他对象
    imp system/manager file=seapark log=seapark fromuser=seapark
    imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
  4. 将一个用户所属的数据导入另一个用户
    imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
    imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
  5. 导入一个表
    imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
  6. 从多个文件导入
    imp system/manager file=(paycheck1,paycheck2,paycheck3,paycheck4) log=paycheck,filesize=1G full=y
  7. 使用参数文件
    imp system/manager parfile=bible_tables.par
    bible_tables.par参数文件：
    #Import the sample tables used for the Oracle8i Database Administrator’s
    #Bible.
    fromuser=seapark touser=seaparkcopy file=seapark log=seaparkimport
  8. 增量导入(9i中已经取消)
    imp system./manager@ inctype= RECTORE FULL=Y FILE=A

IMPDP/EXPDP (IMP/EXP)

EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

expdp或impdp命令时，可暂不指出用户名/密码@实例名 as 身份，然后根据提示再输入，如：

expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

一、创建逻辑目录，该命令不会在操作系统创建真正的目录，但必须是已经存在的目录。最好以system等管理员创建。在物理机器上实际上还是需要创建正真实的目录。

create directory dpdata1 as 'd:\test\dump';
如果用户每有权限
`grant create any directory to the_user;

二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;
三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。
grant read,write on directory dpdata1 to scott;
四、导出数据
1)按用户导

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1

只导出表数据

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1 CONTENT=DATA_ONLY

2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5)按表空间导
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导整个数据库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

五、还原数据
1)导到指定用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2) ** 改变表的owner **

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3)导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)导入数据库
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLEEXISTSACTION
cle/details/80243088
习题：
1、 删除自己服务器用户的所有表

  1. 导出192.168.10.3 HR里面的表 .expdp导出
  2. scp到本机，导入到自己的本地oracle 。（impdp)

    SQLLDR（imp，导入数据）

初始化环境变量

#!/bin/bash
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/client
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

umask 022
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD'

LC_CTYPE=zh_CN.GB2312
LC_ALL=zh_CN
LANG=zh_CN.GB2312
sqlldr userid=system/oracle@192.168.10.7/xe control=insertIntoTD_CORE_PUSH_DATA_TEST1.ctl data=sqlldrlog202110251554.txt log=sqlldrlog202110251554.log bad=sqlldrlog202110251554.bad errors=99999999

● 根据csv文件格式，里面包含字段，创建表。
● 创建表的时候默认都是varchar2（1024）

create table stock2 ( 日期 varchar2(256), 股票代码 varchar2(256), 名称 varchar2(256),
 收盘价 varchar2(256), 最高价 varchar2(256), 最低价 varchar2(256), 开盘价 varchar2(256), 前收盘 varchar2(256), 涨跌额 varchar2(256), 涨跌幅 varchar2(256), 换手率 varchar2(256), 
成交量 varchar2(256), 成交金额 varchar2(256), 总市值 varchar2(256), 流通市值 varchar2(256) );

● 创建control文件，包含了load data 命令

● options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
load data
CHARACTERSET utf8
infile 'e:\csv\000002.csv'
truncate  into table yanfeng.stock2
fields terminated by ',' 
(
日期,股票代码,名称,收盘价,最高价,最低价,开盘价,前收盘,涨跌额,涨跌幅,换手率,成交量,成交金额,总市值,流通市值
)

实操：
生成 student.txt

seq 1 1000000  |shuf > sid
seq 1 1000000  |shuf > cid
seq 1 1000000  |shuf > score
paste -d , sid cid score > student.txt

创建数据库

create table student ( sid int,cid int ,score int)

创建control文件

sid,cid,score
829395,254698,776586
173406,290064,395889
158942,4070,885404
257628,830152,779576
626277,630073,857675

运行

sqlldr userid=zhangxin05/123456 control=student.ctl

options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
load data
CHARACTERSET utf8
infile ‘/home/oracle/csv/student.txt’
truncate into table zhangxin05.student
fields terminated by ‘,’
(sid,cid,score)

● 使用sqlldr来导入
● sqlldr userid=yanfeng/123456 control=e:\csv\cvs.ctl
SQLLDR2 导出数据，要安装 （ exp 导出数据）
position 关键字用来指定列的开始和结束位置
position(m:n)：指从第 m 个字符开始截止到第 n 个字符作为列值
position(+2:15)：直接指定数值的方式叫做绝对偏移量，如果使用号，则为相对偏移量，表示上一个字段哪里结束，这次就哪里开始，相对便宜量也可以再做运算。
position(*) char(9)：这种相对偏移量+类型和长度的优势在于，你只需要为第一列指定开始位置，其他列只需要指定列长度就可以。
FILLER：控制文件中指定 FILLER，表示该列值不导入表中。
普通

  1. Load DATA
  2. INFILE *
  3. INTO TABLE BONUS
  4. FIELDS TERMINATED BY “,”
  5. (ENAME,JOB,SAL)
  6. BEGINDATA
  7. SMITH,CLEAK,3904
  8. ALLEN,SALESMAN,2891
  9. WARD,SALESMAN,3128
  10. KING,PRESIDENT,2523
    没有分隔符
  11. LOAD DATA
  12. INFILE *
  13. TRUNCATE INTO TABLE BONUS
  14. (
  15. ENAME position(1:5),
  16. JOB position(7:15),
  17. SAL position(17:20)
  18. )
  19. BEGINDATA
  20. SMITH CLEAK 2891
  21. ALLEN SALESMAN 2891
  22. WARD SALESMAN 3128
  23. KING PRESIDENT 2523
    比导入的表列少
  24. LOAD DATA
  25. INFILE *
  26. TRUNCATE INTO TABLE BONUS
  27. (
  28. ENAME position(1:5),
  29. JOB position(7:15),
  30. SAL position(17:20),
  31. comm “0”
  32. )
  33. BEGINDATA
  34. SMITH CLEAK 2891
  35. ALLEN SALESMAN 2891
  36. WARD SALESMAN 3128
  37. KING PRESIDENT 2523
    比导入的表列多
  38. LOAD DATA
  39. INFILE *
  40. TRUNCATE INTO TABLE BONUS
  41. (
  42. ENAME position(1:6),
  43. TCOL FILLER position(8:11),
  44. JOB position(13:21),
  45. SAL position(23:26)
  46. )
  47. BEGINDATA
  48. SMITH 7369 CLERK 800 20
  49. ALLEN 7499 SALESMAN 1600 30
  50. WARD 7521 SALESMAN 1250 30
  51. JONES 7566 MANAGER 2975 20
  52. MARTIN 7654 SALESMAN 1250 30
  53. BLAKE 7698 MANAGER 2850 30
  54. CLARK 7782 MANAGER 2450 10
  55. KING 7839 PRESIDENT 5000 10
  56. TURNER 7844 SALESMAN 1500 30
  57. JAMES 7900 CLERK 950 30
  58. FORD 7902 ANALYST 3000 20
  59. MILLER 7934 CLERK 1300 10
    导入不同表
  60. LOAD DATA
  61. INFILE *
  62. TRUNCATE
  63. INTO TABLE BONUS
  64. WHEN Tab=’BON’
  65. (
  66. Tab FILLER position(1:3),
  67. ENAME position(5:9),
  68. JOB position(11:19),
  69. SAL position(21:24)
  70. )
  71. INTO TABLE MANAGER
  72. WHEN Tab=’MGR’
  73. (
  74. Tab FILLER position(1:3),
  75. MGRNO position(5:6),
  76. MNAME position(8:14),
  77. JOB position(16:28)
  78. )
  79. BEGINDATA
  80. BON SMITH CLERK 3904
  81. BON ALLEN SALER,M 2891
  82. BON WARD SALER,”S” 3128
  83. BON KING PRESIDENT 2523
  84. MGR 10 SMITH SALES MANAGER
  85. MGR 11 ALLEN.W TECH MANAGER
  86. MGR 16 BLAKE HR MANAGER
  87. TMP SMITH 7369 CLERK 800 20
  88. TMP ALLEN 7499 SALESMAN 1600 30
  89. TMP WARD 7521 SALESMAN 1250 30
  90. TMP JONES 7566 MANAGER 2975 20
    换行符处理
  91. LOAD DATA
  92. INFILE *
  93. TRUNCATE INTO TABLE BONUS
  94. FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘“‘
  95. (ENAME,JOB,SAL)
  96. BEGINDATA
  97. SMITH,CLEAK,3904
  98. ALLEN,”SALER,M”,2891
  99. WARD,”SALER,””S”””,3182
  100. KING,PRESIDENT,2523
    函数使用
  101. LOAD DATA
  102. INFILE *
  103. TRUNCATE INTO TABLE BONUS
  104. (
  105. ENAME position(1:5),
  106. JOB position(7:15),
  107. SAL position(17:20),
  108. comm “substr(:sal,1,1)”
  109. )
  110. BEGINDATA
  111. SMITH CLEAK 3904
  112. ALLEN SALESMAN 2891
  113. WARD SALESMAN 3128
  114. KING PRESIDENT 2523
    大字段处理
  115. LOAD DATA
  116. INFILE * “str ‘\r\n’”
  117. TRUNCATE INTO TABLE MANAGER
  118. FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘“‘
  119. (MGRNO,MNAME,JOB,REMARK char(100000))
  120. BEGINDATA
  121. 10,SMITH,SALES MANAGER,This is SMITH.
  122. He is a Sales Manager.|
  123. 11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
  124. He is a Tech Manager.|
  125. 16,BLAKE,HR MANAGER,”This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
  126. \1. Ensure the effective local implementation of corporate level HRinitiatives and new programs.
  127. \2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing, rewarding and retaining talents for the sustainable development of company business.
  128. \3. Oversee stanard recruiting an procedures to ensure the company’s staffing requirements are met in a timely manner, and interview management level candidates
  129. \4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
  130. \5. Develop, implement and oversee the training and development programs to upgrade the skills of the employee and to enhance the company’s capabilities to met business goals and future challenges.”
    加载文件内容到大字段
  131. LOAD DATA
  132. INFILE *
  133. TRUNCATE INTO TABLE LOBTBL
  134. (
  135. CREATE_DATE POSITION(1：17) DATE ‘YYYY-MM-DD HH24：MI’,
  136. FILESIZE POSITION(*+1：25) “to_number(:FILESIZE, ‘99，999，999’)”,
  137. FILEOWNER POSITION(*+1：34),
  138. FILENAME POSITION(*+1) char(200) “substr(:FILENAME,instr(:FILENAME, ‘',-1)+1)”,
  139. FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF
  140. )
  141. BEGINDATA
  142. 2009-03-17 09:43 154 JUNSANSI F:\oracle\script\ldrcase111.ctl
  143. 2009-03-17 09:44 189 JUNSANSI F:\oracle\script\ldrcase111.dat
  144. 2009-03-17 09:44 2,369 JUNSANSI F:\oracle\script\ldrcase111.log
  145. 2009-03-16 16:50 173 JUNSANSI F:\oracle\script\ldrcase112.ctl
  146. 2009-03-16 16:49 204 JUNSANSI F:\oracle\script\ldrcase112.dat
  147. 2009-03-16 16:50 1,498 JUNSANSI F:\oracle\script\ldrcase112.log
  148. 2009-03-16 17:41 145 JUNSANSI F:\oracle\script\ldrcase113.ctl
  149. 2009-03-16 17:44 130 JUNSANSI F:\oracle\script\ldrcase113.dat
  150. 2009-03-16 17:44 1,743 JUNSANSI F:\oracle\script\ldrcase113.log
  151. 2009-03-17 11:01 132 JUNSANSI F:\oracle\script\ldrcase114.ctl
  152. 2009-03-17 11:02 188 JUNSANSI F:\oracle\script\ldrcase114.dat
  153. 2009-03-17 11:02 1,730 JUNSANSI F:\oracle\script\ldrcase114.log
    载入每行的行号
  154. load data
  155. infile *
  156. into table t
  157. replace
  158. (
  159. seqno RECNUM //载入每行的行号
  160. text Position(1:1024)
  161. )
  162. BEGINDATA
  163. testline1
  164. testline2
作者：严锋  创建时间：2024-07-04 09:13
最后编辑：严锋  更新时间：2024-07-04 11:18