Oracle使用expdb、impdb解決導(dǎo)出、導(dǎo)入時(shí)的用戶修改、表空間修改變更問(wèn)題

愛(ài)吃魚(yú)的俊懶貓 2019-09-27

展開(kāi)全文

對(duì)于Oracle的數(shù)據(jù)導(dǎo)出和導(dǎo)入，我們之前一直使用exp、imp來(lái)處理，但用imp在導(dǎo)入時(shí)經(jīng)常會(huì)要求表空間一致，而博主今天恰恰就遇到了需要變更表空間和用戶的情況，這在我們平常的業(yè)務(wù)場(chǎng)景中也會(huì)遇到，那么該如何處理呢？

我們采用Oralce的 “數(shù)據(jù)泵” Data Dump來(lái)處理。

測(cè)試環(huán)境：Windows Server 2008 R2、Oracle11g

1、數(shù)據(jù)泵(Data Dump)主要解決問(wèn)題

①. 比imp/exp更加靈活，支持多種元數(shù)據(jù)過(guò)濾策略，多種導(dǎo)入、導(dǎo)出模式，如將A表空間B用戶數(shù)據(jù)導(dǎo)入C表空間D用戶下，則只需REMAP_SCHEMA即可

②. 表空間占用和表空間變更問(wèn)題，可使用REMAP_TABLESPACE來(lái)解決

③. 空表問(wèn)題，Oracle11g新的表并且表中無(wú)數(shù)據(jù)也為使用過(guò)則表的segment空間是不會(huì)分配的，這樣在使用exp導(dǎo)出時(shí)空表便不會(huì)被導(dǎo)出，解決這個(gè)問(wèn)題要么給空分配segment或直接使用expdp。

④. 效率問(wèn)題。普通情況下expdp/impdp比exp/imp效率要高，高多少博主沒(méi)有測(cè)試，有興趣的不妨驗(yàn)證下，@博主。

2、數(shù)據(jù)泵使用限制

使用expdp、impdp有條件限制

一是遠(yuǎn)程使用時(shí)必須配置好Database Link，因?yàn)閑xpdp、impdp只能在服務(wù)端使用，不能再客戶端使用；

二是試用前要解決路徑配置問(wèn)題。

3、創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)

創(chuàng)建源用戶和表空間，為了便于操作我們給新創(chuàng)建用戶授權(quán)的了dba權(quán)限，大家在本地使用時(shí)注意

/*第1步：創(chuàng)建臨時(shí)表空間  */
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)建兩張測(cè)試表，一張基礎(chǔ)數(shù)據(jù)類(lèi)型，一張含clob、blob、date等復(fù)雜數(shù)據(jù)類(lèi)型

--表1
create table tab_1
(
  tab1_field1 number,
  tab1_field2 varchar2(20),
  tab1_field3 varchar2(10)
)
--插入測(cè)試數(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
)
--插入測(cè)試數(shù)據(jù)
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','A12',SYSDATE,'測(cè)試1','C1');
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','測(cè)試22',SYSDATE,'測(cè)試2','C2');
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','嘿嘿32',SYSDATE,'測(cè)試3','C3');

創(chuàng)建目標(biāo)用戶和表空間

/*第1步：創(chuàng)建臨時(shí)表空間  */
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;

4、導(dǎo)出源數(shù)據(jù)庫(kù)

這里需要注意：

EXP和IMP是客戶端工具程序，它們既可以在客戶端使用，也可以在服務(wù)端使用。

而EXPDP和IMPDP是服務(wù)端的工具程序，他們只能在ORACLE服務(wù)端使用，不能在客戶端使用。

IMP只適用于EXP導(dǎo)出的文件，不適用于EXPDP導(dǎo)出文件；IMPDP只適用于EXPDP導(dǎo)出的文件，而不適用于EXP導(dǎo)出文件。

我們這里需要使用expdp導(dǎo)出，需在服務(wù)端執(zhí)行。

①.創(chuàng)建邏輯目錄

--創(chuàng)建一個(gè)dump路徑的對(duì)象
create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';

當(dāng)然我們這里也可以使用dump默認(rèn)目錄，查詢(xún)默認(rèn)目錄，然后執(zhí)行創(chuàng)建

--查詢(xún)使用默認(rèn)dump路徑
select * from dba_directories where directory_name='DATA_PUMP_DIR';
--創(chuàng)建一個(gè)dump路徑的對(duì)象
create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';
--查詢(xún)已創(chuàng)建的dump路徑對(duì)象
select * from dba_directories where directory_name='DUMP_DIR1';

但正常情況下這個(gè)目錄只有dba 和system高級(jí)用戶可以使用，所以我們需要給用戶授權(quán)，這里我們同時(shí)給源數(shù)據(jù)庫(kù)用戶和目標(biāo)數(shù)據(jù)庫(kù)用戶均加上目錄授權(quán)。

--用戶地址讀寫(xiě)授權(quán)
grant read,write on directory dump_dir1 to test_source;
grant read,write on directory dump_dir1 to test_target;

②.導(dǎo)出源數(shù)據(jù)庫(kù)

在數(shù)據(jù)庫(kù)服務(wù)器上，點(diǎn)擊Win+R彈出命令框，輸入“cmd”，進(jìn)入命令操作框，輸入以下語(yǔ)句，導(dǎo)出數(shù)據(jù)文件dmp。

1)按用戶導(dǎo)
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_source;
2)并行進(jìn)程parallel
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp parallel=40 job_name=test_source
3)按表名導(dǎo)
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1,tab_2;
4)按查詢(xún)條件導(dǎo)
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1 query='where deptno=20';
5)按表空間導(dǎo)
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_source;
6)導(dǎo)整個(gè)數(shù)據(jù)庫(kù)
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp full=y;

我采用按用戶導(dǎo)出測(cè)試，發(fā)現(xiàn)報(bào)以下錯(cuò)誤

然后打開(kāi)目錄“”發(fā)現(xiàn)該目錄為只讀目錄，去掉只讀選項(xiàng)，確認(rèn)，同時(shí)增加日志文件指向

再次嘗試，成功！

5、導(dǎo)入目標(biāo)數(shù)據(jù)庫(kù)

上面我們已經(jīng)給目標(biāo)數(shù)據(jù)庫(kù)用戶加上了目錄授權(quán)，可以通過(guò)下面語(yǔ)句進(jìn)行導(dǎo)入：

1)導(dǎo)到指定用戶下
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)導(dǎo)入表空間
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_target;
4)導(dǎo)入數(shù)據(jù)庫(kù)
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

我們采用導(dǎo)入指定用戶下，同時(shí)改變他的owner和表空間

impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp remap_schema=test_source:test_target

remap_tablespace=test_source:test_target

導(dǎo)入完成！

6、驗(yàn)證導(dǎo)入后表空間

我們驗(yàn)證下導(dǎo)入后的表所屬用戶和表空間是否有變化

所屬用戶和表空間已發(fā)生改變。

為了確保blob、clob等字段所屬表空間已改變，我們刪除源數(shù)據(jù)庫(kù)用戶和表空間，測(cè)試目標(biāo)數(shù)據(jù)庫(kù)是否正常

--刪除源數(shù)據(jù)庫(kù)用戶
drop user test_source;
--因?yàn)樗鶆h表空間非空，所以我們加上including contents進(jìn)行表空間刪除
drop tablespace test_source including contents and datafiles;

然后我們查詢(xún)下帶有clob和blob字段的表數(shù)據(jù)是否正常

成功，完美！

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自：愛(ài)吃魚(yú)的俊懶貓 > 《IT》

舉報(bào)/認(rèn)領(lǐng)