oracle 資料庫匯入匯出方法教程

oracle11g資料庫匯入匯出： ①:傳統方式——exp(匯出)和(imp)匯入： ②:資料泵方式——expdp匯出和（impdp）匯入; ③:第三方工具——PL/sql Develpoer;

一、什麼是資料庫匯入匯出？

oracle11g資料庫的匯入/匯出，就是我們通常所說的oracle資料的還原/備份。 資料庫匯入:把.dmp 格式檔案從本地匯入到資料庫伺服器中(本地oracle測試資料庫中); 資料庫匯出:把資料庫伺服器中的資料(本地oracle測試資料庫中的資料),匯出到本地生成.dmp格式檔案。 .dmp 格式檔案：就是oracle資料的檔案格式(比如視訊是.mp4 格式,音樂是.mp3 格式);

二、二者優缺點描述：

1.exp/imp: 優點:程式碼書寫簡單易懂,從本地即可直接匯入，不用在伺服器中操作，降低難度，減少伺服器上的操作也就                                         保證了伺服器上資料檔案的安全性。 缺點：這種匯入匯出的速度相對較慢，合適資料庫資料較少的時候。如果檔案超過幾個G，大眾效能的電                                       腦，至少需要4~5個小時左右。 2.expdp/impdp: 優點:匯入匯出速度相對較快,幾個G的資料檔案一般在1~2小時左右。 缺點:程式碼相對不易理解,要想實現匯入匯出的操作，必須在伺服器上建立邏輯目錄(不是真正的目錄)。我們                                       都知道資料庫伺服器的重要性，所以在上面的操作必須慎重。所以這種方式一般由專業的程式人員來完                                           成(不一定是DBA(資料庫管理員)來幹,中小公司可能沒有DBA)。 3.PL/sql Develpoer: 優點：封裝了匯入匯出命令，無需每次都手動輸入命令。方便快捷，提高效率。 缺點：長時間應用會對其產生依賴，降低對程式碼執行原理的理解。

三、特別強調：

目標資料庫:資料即將匯入的資料庫(一般是專案上正式資料庫); 源資料庫:資料匯出的資料庫(一般是專案上的測試資料庫); 1.目標資料庫要與源資料庫有著名稱相同的表空間。 2.目標資料在進行匯入時，使用者名稱儘量相同(這樣保證使用者的許可權級別相同)。 3.目標資料庫每次在進行資料匯入前，應做好資料備份，以防資料丟失。 4.使用資料泵時,一定要現在伺服器端建立可用的邏輯目錄，並檢查是否可用。 5.弄清是匯入匯出到相同版本還是不同版本(oracle10g版本與oracle11g版本)。 6.目標資料匯入前,弄清楚是資料覆蓋(替換),還是僅插入新資料或替換部分資料表。 7.確定目標資料庫磁碟空間是否足夠容納新資料，是否需要擴充表空間。 8.匯入匯出時注意字符集是否相同，一般Oracle資料庫的字符集只有一個，並且固定，一般不改變。 9.匯出格式介紹:  　 Dmp格式：.dmp是二進位制檔案，可跨平臺，還能包含許可權，效率好；   　 Sql格式：.sql格式的檔案，可用文字編輯器檢視，通用性比較好，效率不如第一種， 適合小資料量匯入匯出。尤其注意的是表中不能有大欄位 （blob,clob,long），如果有，會報錯；   　 Pde格式：.pde格式的檔案，.pde為PL/SQL Developer自有的檔案格式，只能用PL/SQL Developer工具 匯入匯出，不能用文字編輯器檢視； 10.確定操作者的賬號許可權。

四、二者的匯入匯出方法：

1、傳統方法： 通用命令:exp(imp)  username/password@SERVICENAME:1521 file="e:/temp.dmp" full = y; 資料庫匯出舉例: exp xinxiaoyong/[email protected]:1521 file="e:/temp.dmp" full = y; exp:匯出命令，匯出時必寫。 imp:匯入命令，匯入時必寫,每次操作，二者只能選擇一個執行。 username:匯出資料的使用者名稱，必寫; password:匯出資料的密碼，必寫; @:地址符號，必寫; SERVICENAME:Oracle的服務名，必寫; 1521:埠號，1521是預設的可以不寫,非預設要寫; file="e:/temp.dmp" : 檔案存放路徑地址，必寫; full=y :表示全庫匯出。可以不寫，則預設為no,則只匯出使用者下的物件; 方法細分： 1.完全匯入匯出： exp(imp)  username/password@SERVICENAME:1521 file="e:/temp.dmp" full = y;

2.部分使用者表table匯入匯出： exp(imp)  username/password@SERVICENAME:1521 file="e:/temp.dmp"  tabels= (table1,table2,table3,...);

3.表空間tablespaces匯入匯出： //一個資料庫例項可以有N個表空間(tablespace)，一個表空間下可以有N張表(table)。 exp(imp)  username/password@SERVICENAME:1521 file="e:/temp.dmp"  tablespaces=   (tablespace1,tablespace2,tablespace3,...);

4.使用者名稱username物件匯入匯出： exp(imp)  username/password@SERVICENAME:1521 file="e:/temp.dmp"                                                                                 owner(username1,username2,username3);

2、資料泵方法： 建立directory: expdp(impdp) username/password@SERVICENAME:1521 schemas=username                                                                       dumpfile=file1.dmp logfile=file1.log directory=testdata1 remap_schema=test:test; 資料庫匯出舉例: expdp xinxiaoyong/[email protected]:1521 schemas=xinxiaoyong dumpfile=test.dmp  logfile=test.log directory=testdata1; exp:匯出命令，匯出時必寫。 imp:匯入命令，匯入時必寫,每次操作，二者只能選擇一個執行。 username:匯出資料的使用者名稱，必寫; password:匯出資料的密碼，必寫; @:地址符號，必寫; SERVICENAME:Oracle的服務名，必寫; 1521:埠號，1521是預設的可以不寫,非預設要寫; schemas：匯出操作的使用者名稱; dumpfile：匯出的檔案; logfile:匯出的日誌檔案,可以不寫； directory:建立的資料夾名稱; remap_schema=源資料庫使用者名稱:目標資料庫使用者名稱,二者不同時必寫，相同可以省略; 1.檢視錶空間： select ＊　from dba_tablespaces; 2.檢視管理理員目錄(同時檢視作業系統是否存在，因為Oracle並不關心該目錄是否存在，如果不存                                                    在，則出錯)。 select ＊　from dba_directories;

3.建立邏輯目錄，該命令不會在作業系統建立真正的目錄，最好以system等管理員建立。  create directory testdata1 as 'd:/test/dump'; 4.給xinxiaoyong使用者賦予在指定目錄的操作許可權，最好以system等管理員賦予。 //xinxiaoyong 是使用者名稱(123456是使用者密碼) grant read,write on directory testdata1 to xinxiaoyong;

5.匯出資料 1)按使用者導 expdp xinxiaoyong/123456@orcl schemas=xinxiaoyong                                                                                           dumpfile=expdp.dmp directory=testdata1; 2)並行程序parallel expdp xinxiaoyong/123456@orcl directory=testdata1                                                                                    dumpfile=xinxiaoyong3.dmp parallel=40 job_name=xinxiaoyong3 3)按表名導 expdp xinxiaoyong/123456@orcl tables=emp,dept dumpfile=expdp.dmp                                                                directory=testdata1; 4)按查詢條件導 expdp xinxiaoyong/123456@orcl directory=testdata1                                                                                            dumpfile=expdp.dmp tables=emp query='WHERE deptno=20'; 5)按表空間導 expdp system/manager directory=testdata1 dumpfile=tablespace.dmp                                                                    tablespaces=temp,example; 6)導整個資料庫 expdp system/manager directory=testdata1 dumpfile=full.dmp FULL=y; 6.還原資料 1)導到指定使用者下 impdp xinxiaoyong/123456 directory=testdata1 dumpfile=expdp.dmp                                                              schemas=xinxiaoyong; 2)改變表的owner impdp system/manager directory=testdata1 dumpfile=expdp.dmp                                                               tables=xinxiaoyong.dept remap_schema =xinxiaoyong:system; 3)匯入表空間 impdp system/manager directory=testdata1 dumpfile=tablespace.dmp                                                         tablespaces=example; 4)匯入資料庫 impdb system/manager directory=dump_dir dumpfile=full.dmp FULL=y; 5)追加資料 impdp system/manager directory=testdata1 dumpfile=expdp.dmp                                                         schemas=system  table_exists_action; 3、PLSQL方法： 登入plsql工具，所使用使用者為源資料庫有匯出許可權（exp_full_database,dba等）的使用者。    1.匯出建表語句（包括儲存結構）

匯出步驟tools ->export user object，選擇要匯出的物件，匯出.sql格式檔案並等待匯出完成，如 下圖：

匯出資料檔案 ;
   2.匯出步驟tools ->export tables，選擇要匯出的表及匯出的格式進行匯出。

匯出為dmp格式，如下圖：

匯出為sql格式，如下圖：

匯出為pde格式，如下圖：

提示說明：採用第三方工具匯出匯入整個資料庫的話，耗時較長，一定要有足夠的時間來操作(資料量大的話需要好幾個小時)。

3.匯入建表語句     匯入步驟tools->import tables->SQL Inserts 匯入.sql檔案  4.匯入資料；     tools->import talbes，然後再根據匯出的資料格式選擇匯入dmp檔案，或者sql檔案， 或者pde檔案。     提示說明：匯入之前最好把以前的表刪除，當然匯入另外資料庫除外。 另外匯入時當發現進度條一直卡在一個點，而且匯出的檔案不再增大時，甚至是提示程式 未響應，千萬不要以為程式卡死了，這個匯入匯出就是比較緩慢，只要沒有提示報錯，

由於水平有限，本文件僅提供參考。如程式碼有錯誤之處，請見諒。

另外附上Oracle exp匯出資料詳細介紹

Oracle exp匯出資料

Oracle exp工具可以選擇性的匯出資料庫的物件和表資料，匯出DMP檔案以備以後進行資料恢復。下面分別講解如何通過EXP匯出資料庫的表、方案、表空間、資料庫四個不同物件的資料。

使用EXP工具可以使用第三方工具PL/SQL DEVELOPER進行匯出，也可以直接通過DOS命令列視窗直接進行匯出。

EXP 匯出表

進入DOS命令列視窗，輸入以下命令：

exp username/password

使用使用者名稱和密碼登入，然後按照工具提示，匯出對應的表結構資料，具體步驟如下圖：

從上圖可以看出，我們要匯出的是student使用者的學生資訊表（stuinfo），其中匯出的資料檔案儲存的路徑是在d:/student.dmp。然後通過選擇要匯出的型別選擇匯出表（T），再然後按照提示一步一步的進行，就可以匯出想要的表結構資料。

但是在實際應用當中，我們一般是直接使用命令語句匯出想要的資料物件。不需要這樣子一步一步的選擇。

EXP匯出表資料命令：

exp student/123456@orcl file=d:/student.dmp tables= stuinfo buffer=4096 log=d:/student.log

結果如下：

命令解析：

1、file指定資料備份檔案的儲存地址。

2、tables指定要備份的表結構，可以匯出多個表，通過（table1，table2...,tablen）進行選擇。

3、buffer指定資料快取區的大小。

4、log指定進行匯出的日誌檔案的儲存地址。

EXP匯出帶條件的表資料：

exp student/123456@orcl file=d:/student.dmp tables= stuinfo buffer=4096 log=d:/student.log query="'where sex=1'"

命令解析：

1、query指定要新增的條件，把表中的資料進行過濾匯出。

結果如下：

EXP匯出使用者

EXP匯出使用者命令結構：

exp student/123456@orcl file=d:/student.dmp owner=(student) buffer=4096 log=d:/student.log

命令解析：

1、owner 指定要匯出的使用者的使用者名稱，但是前提條件是登入的使用者得具有訪問其它使用者物件的許可權。一般是使用DBA使用者進行登入。可以支援多使用者匯出，使用逗號“，”進行隔開。

結果如下：

EXP匯出表空間

EXP匯出表空間命令結構：

exp student/123456@orcl file=d:/student.dmp tablespaces=(student) buffer=4096 log=d:/student.log

命令解析：

1、tablespaces指定要匯出的表空間，但是前提條件是登入的使用者得是DBA使用者。可以支援多表空間一起匯出，使用逗號“，”進行隔開。

結果如下：

EXP匯出資料庫

EXP匯出資料庫命令結構：

exp student/123456@orcl file=d:/student.dmp FULL=Y buffer=4096 log=d:/student.log

命令解析：

1、full=Y指的是要匯出的是整個資料庫，但是前提條件是登入的使用者得是DBA使用者。

總結

Oracle使用EXP工具進行匯出資料庫資料進行備份是資料庫管理員經常要做的事情，經常用來做資料遷移和定期的資料備份。