對于Oracle的數(shù)據(jù)導出和導入，我們之前一直使用exp、imp來處理， 但用imp在導入時經(jīng)常會要求表空間一致，而博主今天恰恰就遇到了需要變更表空間和用戶的情況，這在我們平常的業(yè)務場景中也會遇到，那么該如何處理呢？
我們采用Oralce的 “數(shù)據(jù)泵” Data Dump來處理。
測試環(huán)境：Windows Server 2008 R2、Oracle11g
①. 比imp/exp更加靈活，支持多種元數(shù)據(jù)過濾策略，多種導入、導出模式，如將A表空間B用戶數(shù)據(jù)導入C表空間D用戶下，則只需REMAP_SCHEMA即可
②. 表空間占用和表空間變更問題，可使用REMAP_TABLESPACE來解決
③. 空表問題，Oracle11g新的表并且表中無數(shù)據(jù)也為使用過則表的segment空間是不會分配的，這樣在使用exp導出時空表便不會被導出，解決這個問題要么給空分配segment或直接使用expdp。
④. 效率問題。普通情況下expdp/impdp比exp/imp效率要高，高多少博主沒有測試，有興趣的不妨驗證下，@博主。
使用expdp、impdp有條件限制
一是遠程使用時必須配置好Database Link，因為expdp、impdp只能在服務端使用，不能再客戶端使用；
二是試用前要解決路徑配置問題。
創(chuàng)建源用戶和表空間，為了便于操作我們給新創(chuàng)建用戶授權(quán)的了dba權(quán)限，大家在本地使用時注意
- /*第1步：創(chuàng)建臨時表空間 */
- create temporary tablespace test_source_temp
- tempfile 'D:\Oracle11g\oradata\test_source_temp.dbf'
- size 10m
- autoextend on
- next 1m maxsize unlimited
- extent management local;
- /*第2步：創(chuàng)建數(shù)據(jù)表空間 */
- create tablespace test_source
- logging
- datafile 'D:\Oracle11g\oradata\test_source.dbf'
- size 10m
- autoextend on
- next 1m maxsize unlimited
- extent management local;
- /*第3步：創(chuàng)建用戶并指定表空間 */
- create user test_source identified by a123456
- default tablespace test_source
- temporary tablespace test_source_temp;
- /*第4步：給用戶授予權(quán)限 */
- grant connect,resource,dba to test_source;
創(chuàng)建兩張測試表，一張基礎數(shù)據(jù)類型，一張含clob、blob、date等復雜數(shù)據(jù)類型
- --表1
- create table tab_1
- (
- tab1_field1 number,
- tab1_field2 varchar2(20),
- tab1_field3 varchar2(10)
- )
- --插入測試數(shù)據(jù)
- insert into TAB_1 (tab1_field1, tab1_field2, tab1_field3) values (1, 'a12', 'a13');
- insert into TAB_1 (tab1_field1, tab1_field2, tab1_field3) values (2, '張22', '李23');
- --表2
- create table TAB_2
- (
- tab2_field1 NUMBER,
- tab2_field2 VARCHAR2(20),
- tab2_field3 DATE,
- tab2_field4 CLOB,
- tab2_field5 BLOB
- )
- --插入測試數(shù)據(jù)
- insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','A12',SYSDATE,'測試1','C1');
- insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','測試22',SYSDATE,'測試2','C2');
- insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','嘿嘿32',SYSDATE,'測試3','C3');
創(chuàng)建目標用戶和表空間
- /*第1步：創(chuàng)建臨時表空間 */
- create temporary tablespace test_target_temp
- tempfile 'D:\Oracle11g\oradata\test_target_temp.dbf'
- size 10m
- autoextend on
- next 1m maxsize unlimited
- extent management local;
- /*第2步：創(chuàng)建數(shù)據(jù)表空間 */
- create tablespace test_target
- logging
- datafile 'D:\Oracle11g\oradata\test_target.dbf'
- size 10m
- autoextend on
- next 1m maxsize unlimited
- extent management local;
- /*第3步：創(chuàng)建用戶并指定表空間 */
- create user test_target identified by a123456
- default tablespace test_target
- temporary tablespace test_target_temp;
- /*第4步：給用戶授予權(quán)限 */
- grant connect,resource,dba to test_target;
這里需要注意：
EXP和IMP是客戶端工具程序，它們既可以在客戶端使用，也可以在服務端使用。
而EXPDP和IMPDP是服務端的工具程序，他們只能在ORACLE服務端使用，不能在客戶端使用。
IMP只適用于EXP導出的文件，不適用于EXPDP導出文件；IMPDP只適用于EXPDP導出的文件，而不適用于EXP導出文件。
我們這里需要使用expdp導出，需在服務端執(zhí)行。
①.創(chuàng)建邏輯目錄
- --創(chuàng)建一個dump路徑的對象
- create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';
當然我們這里也可以使用dump默認目錄，查詢默認目錄，然后執(zhí)行創(chuàng)建
- --查詢使用默認dump路徑
- select * from dba_directories where directory_name='DATA_PUMP_DIR';
- --創(chuàng)建一個dump路徑的對象
- create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';
- --查詢已創(chuàng)建的dump路徑對象
- select * from dba_directories where directory_name='DUMP_DIR1';
但正常情況下這個目錄只有dba 和system高級用戶可以使用，所以我們需要給用戶授權(quán)，這里我們同時給源數(shù)據(jù)庫用戶和目標數(shù)據(jù)庫用戶均加上目錄授權(quán)。
- --用戶地址讀寫授權(quán)
- grant read,write on directory dump_dir1 to test_source;
- grant read,write on directory dump_dir1 to test_target;
②.導出源數(shù)據(jù)庫
在數(shù)據(jù)庫服務器上，點擊Win+R彈出命令框，輸入“cmd”，進入命令操作框，輸入以下語句，導出數(shù)據(jù)文件dmp。
- 1)按用戶導
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_source;
- 2)并行進程parallel
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp parallel=40 job_name=test_source
- 3)按表名導
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1,tab_2;
- 4)按查詢條件導
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1 query='where deptno=20';
- 5)按表空間導
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_source;
- 6)導整個數(shù)據(jù)庫
- expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp full=y;
我采用按用戶導出測試，發(fā)現(xiàn)報以下錯誤
然后打開目錄“”發(fā)現(xiàn)該目錄為只讀目錄，去掉只讀選項，確認，同時增加日志文件指向
再次嘗試，成功！
上面我們已經(jīng)給目標數(shù)據(jù)庫用戶加上了目錄授權(quán)，可以通過下面語句進行導入：
- 1)導到指定用戶下
- impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_target;
- 2)改變表的owner
- impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=test_target.tab_1,test_target.tab_2 remap_schema=test_target;
- 3)導入表空間
- impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_target;
- 4)導入數(shù)據(jù)庫
- impdb test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp full=y;
- 5)追加數(shù)據(jù)
- impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_target table_exists_action
我們采用導入指定用戶下，同時改變他的owner和表空間
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp remap_schema=test_source:test_target
remap_tablespace=test_source:test_target
導入完成！
我們驗證下導入后的表所屬用戶和表空間是否有變化
所屬用戶和表空間已發(fā)生改變。
為了確保blob、clob等字段所屬表空間已改變，我們刪除源數(shù)據(jù)庫用戶和表空間，測試目標數(shù)據(jù)庫是否正常
- --刪除源數(shù)據(jù)庫用戶
- drop user test_source;
- --因為所刪表空間非空，所以我們加上including contents進行表空間刪除
- drop tablespace test_source including contents and datafiles;
然后我們查詢下帶有clob和blob字段的表數(shù)據(jù)是否正常
成功，完美！
