oracle資料庫表的匯出與匯入 筆記

匯出篇

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參數

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.