一、Oracle數(shù)據(jù)庫(kù)數(shù)據(jù)遷移:
A、數(shù)據(jù)遷移方法
1、導(dǎo)入導(dǎo)出方法exp.exe/imp.exe;
優(yōu)點(diǎn):兼容性強(qiáng),可以導(dǎo)出對(duì)象可以自定義;
2、數(shù)據(jù)泵導(dǎo)入導(dǎo)出(Oracle10g版本以上)expdp.exe/impdp.exe;
優(yōu)點(diǎn):速度較exp.exe/imp.exe快;
-
缺點(diǎn):導(dǎo)入導(dǎo)出需指定Directory(需新建Directory,可能權(quán)限要求較高);
3、SQLLODER導(dǎo)入導(dǎo)出;
優(yōu)點(diǎn):能從文本文件導(dǎo)入;
4、通過(guò)ODBC導(dǎo)入導(dǎo)出;
5、通過(guò)DBLINK用INSERT INTO方式;
二、異構(gòu)數(shù)據(jù)庫(kù)之間的數(shù)據(jù)遷移:
A、Oracle透明網(wǎng)關(guān)技術(shù):
1、安裝10201_gateways_win32透明網(wǎng)管組件;
2、在文件listener.ora中SID_LIST_LISTENER添加:
(SID_DESC =
(PROGRAM = tg4sybs)
(SID_NAME = tg4sybs)
(Oracle_HOME = E:\oracle\product\10.2.0\db_1)
)
tg4sybs為sybase組件;tg4msql為sql server組件;
3、在文件tnsnames.ora中添加:
SYBS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = renx)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4sybs)
)
(HS=OK) (此處通知Oracle使用異構(gòu)服務(wù))
)
4、重啟監(jiān)聽(tīng)器:
lsnrctl stop;
lsnrctl start;
B、通過(guò)ODBC進(jìn)行數(shù)據(jù)遷移:
三、部分?jǐn)?shù)據(jù)庫(kù)軟件對(duì)應(yīng)的遷移功能:
A、PL/SQL Developer:
1、Tools->Export Tables <> 對(duì)應(yīng)導(dǎo)入exp.exe/export sql語(yǔ)句(insert);
2、Tools->Import Tables <> 對(duì)應(yīng)導(dǎo)出imp.exe/import from sql語(yǔ)句(insert);
3、Tools->ODBC Importer <> 對(duì)應(yīng)ODBC導(dǎo)入;
4、Tools->Text Importer <> 對(duì)應(yīng)SQLLODER數(shù)據(jù)導(dǎo)入;
5、Tools->Export User Object <> 導(dǎo)出DDL腳本;
6、Tools->Data Generator <> 生成測(cè)試數(shù)據(jù);
B、Toad?for Oracle:
1、Database ->EXPORT->Data Pump Export Wizard <> 對(duì)應(yīng) 數(shù)據(jù)泵導(dǎo)出expdp.exe;
2、Database ->EXPORT->Data Subset Wizard <> 模式復(fù)制(把一個(gè)用戶(hù)中的對(duì)應(yīng)完全賦值到另一用戶(hù)模式);
3、Database ->EXPORT->Export DDL <> 導(dǎo)出選定對(duì)象DDL語(yǔ)句(數(shù)據(jù)割接常用);
4、Database ->EXPORT->Export Utility Wizard <> 對(duì)應(yīng) 導(dǎo)出exp.exe;
5、Database ->EXPORT->Export File Browser <> 瀏覽導(dǎo)出的DMP文件;
6、Database ->EXPORT->Generate Database Script <> 導(dǎo)出數(shù)據(jù)庫(kù)腳本(可用于新建相似數(shù)據(jù)庫(kù))
7、Database ->EXPORT-> Generate Schema Script <> 導(dǎo)出整個(gè)用戶(hù)模式DDL腳本;
8、Database ->EXPORT->Export Table as Flat File <> 按SQLLODER格式導(dǎo)出數(shù)據(jù);
9、Database ->IMPORT->Data Pump Import Wizard <> 對(duì)應(yīng)數(shù)據(jù)泵導(dǎo)入impdp.exe
10、Database ->IMPORT->Import Source Files <> 導(dǎo)入腳本文件(*.sql.,*.pkb…………)
11、Database ->IMPORT->Import Table Data <> 從txt\xls\dbf\mdb文件導(dǎo)入數(shù)據(jù)到指定TABLE;
12、Database ->IMPORT->Import Utility Wizard <> 對(duì)應(yīng) 導(dǎo)入imp.exe;
13、Database ->IMPORT->SQL*Loader Wizard <> 對(duì)應(yīng)SQLLODER導(dǎo)入;
C、Embarcadero DBArtisan(Oracle\SYBASE\SQL SERVER)
1、Utilities -> Data Unload Wizard <> 導(dǎo)出數(shù)據(jù)到文本格式文件;
2、Utilities ->Data Load Wizard <> 從文本文件導(dǎo)入數(shù)據(jù)庫(kù)(SQLLODER);
3、Utilities ->Data Export Wizard for Oracle <> 對(duì)應(yīng)導(dǎo)出exp.exe;(ORACLE)
4、Utilities ->Data Import Wizard for Oracle <>對(duì)應(yīng)導(dǎo)入imp.exe;(ORACLE)
5、Utilities ->schema extraction wizard <>導(dǎo)出整個(gè)用戶(hù)模式DDL腳本;
6、Utilities ->schema migration wizard <> 轉(zhuǎn)移整個(gè)用戶(hù)模式到另一數(shù)據(jù)庫(kù)用戶(hù);
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)!虛擬主機(jī)域名注冊(cè)頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|