导出篇
linux-t9ub:~ # su - oracle
loracle@linux-t9ub:~> ls
Desktop Documents afiedt.buf dmp expdat.dmp gx190906261212.dmp log oracle sh software
oracle@linux-t9ub:~> cd dmp
oracle@linux-t9ub:~/dmp> ls
gx090624.dmp gx0906260900.dmp gx090628.dmp gx090630.dmp gx090703.dmp gx090705.tar.gz gx090708.dmp gx70090619.dmp
gx090625.dmp gx090627.dmp gx090629.dmp gx090701.dmp gx090704.dmp gx090706.dmp gx090709.dmp sys090629.dmp
gx090626.dmp gx0906271400.dmp gx090629.tar.gz gx090702.dmp gx090705.dmp gx090707.dmp gx190906260035.dmp sys090706.dmp
oracle@linux-t9ub:~/dmp> pwd
/oracle/dmp
oracle@linux-t9ub:~/dmp> exp gx/gx file=expdat_jzwx.dmp tables=SJY_DAT_SN_JZWX_JZWX grants=y;
注释：导出所有表可用：exp gx/gx file=expdat_jzwx.dmp grants=y;
Export: Release 10.2.0.1.0 - Production on 星期五 7月 10 11:07:35 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径…
. . 正在导出表 SJY_DAT_SN_JZWX_JZWX导出了 1240 行
成功终止导出, 没有出现警告。
oracle@linux-t9ub:~/dmp>
导入篇
[root@localhost ~]# su - oracle
-bash-3.00$ ls
CreateData.sh expdatgx_70_20090115.dmp flash_recovery_area
admin expdatgx_70_20090121.dmp gx_88_090526.log
afiedt.buf expdatgx_70_20090203.dmp insertdata.sql
arhiva expdatgx_70_20090210.dmp log
dmp expdatgx_70_20090211.dmp lost+found
expdatgx20081128.dmp expdatgx_70_200902111.dmp old
expdatgx20081202.dmp expdatgx_70_20090212.dmp oraInventory
expdatgx20081219.dmp expdatgx_70_20090223.dmp oracle
expdatgx_70_20081220.dmp expdatgx_70_20090228.dmp oradata
expdatgx_70_20090102.dmp expdatgx_70_20090301.dmp sh
expdatgx_70_200901021.dmp expdatgx_70_20090309.dmp sjy_create_tables.sql
expdatgx_70_20090105.dmp expdatgx_70_20090322.dmp
-bash-3.00$ cd dmp
-bash-3.00$ sqlplus gx/gx
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 10 11:24:44 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table SJY_DAT_SN_JZWX_JZWX;
Table dropped.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ imp gx/gx file=expdat_jzwx.dmp grants=y;
注：导入整个dmp文件需要加FULL=Y参数 还可以用fromuser参数指定要导入哪个用户下的表，touser指定导入到哪个用户表空间下面
Import: Release 10.2.0.1.0 - Production on Fri Jul 10 11:26:30 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing GX’s objects into GX
. importing GX’s objects into GX
. . importing table “SJY_DAT_SN_JZWX_JZWX” 1240 rows imported
Import terminated successfully without warnings.
数据泵：
查询directory：select * from dba_directories
将文件上传到directory的服务器目录
impdp system/********@192.168.1.230:1521/moepoc directory=bak_dir dumpfile=dci.dmp schemas=DCI
这个就行