[獨孤九劍]Oracle知識點梳理（三）匯入、匯出

標籤：

3、資料庫的匯入匯出（在命令提示字元cmd下直接執行）

　　Oracle 9i之前，使用imp/exp命令完成匯入/匯出操作。

3.1、資料匯出

1   --將資料庫orcl完全匯出到D盤指定目錄2   exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y3   --將 資料庫中userName使用者和userName2使用者的相關內容匯出4   exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log owner=(userName,userName2) 5   --將表t1和t2匯出6   exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1,t2)7   --將表t1中以00開頭的資料匯出8   exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1) query=\" where field1 like ‘00%‘\"

 3.2、資料匯入

　　匯入的前提：資料庫中用於即將匯入的資料的一致的資料表空間同名使用者見的匯入： 

1　　--將備份檔案全部匯入 2　　imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y 3　　--其中ignore=y表示忽略已存在的表，不加，遇到同名的表，對該表就不匯入4　　imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y ignore=y5　　--將表t1 匯入6　　imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1)

 　　不同名使用者間的匯入：

imp userName/password@orcl file=d:\backup\userName01.dmp 
　　log=d:\backup\userName01.log 
　　fromuser=userNameA touser=userNameB --注意fromuser和touser

　　Oracle 10i之後，Oracle推薦使用資料泵方式匯入/匯出資料【impdp/expdp】；使用資料泵方式，只能在伺服器上進行匯入/匯出操作。

3.3、資料泵匯出步驟：

a) 建立Directory

create directory dir_dp as ‘D:\backup\dir_dp‘;

b) 授權

create read,write on directory dir_dp on userName;

b.1) 查看目錄及許可權

select privilege,directory_name,directory_path 
　　from user_tab_privs t,all_directories d　　　　where t.table_name(+)=d.directory_name;

c) 執行匯出

expdp userName/password@orcl schemas=userName 
　　directory=dir_dp dmpfile=expdp_userName.dmp logfile=expdp_userName.log

注意：

　　1、directory=dir_dp必須放在前面，如果將其放置最後，會提示 ORA-39002: 操作無效
　　　　ORA-39070: 無法開啟記錄檔。
　　　　ORA-39087: 目錄名 DATA_PUMP_DIR; 無效
　　2、在匯出過程中，DATA DUMP 建立並使用了一個名為SYS_EXPORT_SCHEMA_01的對象，此對象就是DATA DUMP匯出過程中所用的JOB名字，如果在執行這個命令時如果沒有指定匯出的JOB名字那麼就會產生一個預設的JOB名字，如果在匯出過程中指定JOB名字就為以指定名字出現。
如下改成：

expdp userName/password@orcl schemas=userName 
　　directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;

　　3、匯出語句後面不要有分號，否則如上的匯出語句中的job表名為‘my_job1;’，而不是my_job1。因此導致expdp userName/password attach=userName.my_job1執行該命令時一直提示找不到job表

　　4、建立的目錄一定要在資料庫所在的機器上。否則也是提示:
　　　　ORA-39002: 操作無效
　　　　ORA-39070: 無法開啟記錄檔。
　　　　ORA-39087: 目錄名 DATA_PUMP_DIR; 無效

資料泵匯出的各種模式：

• 按表模式匯出：

expdp userName/password@orcl tables=userName.b$i_exch_info,userName.b$i_manhole_info 
　　dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job

• 按查詢條件匯出：

expdp userName/password@orcl tables=userName.b$i_exch_info 
　　dumpfile=expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query=‘"where rownum<11"‘

• 按資料表空間匯出：

Expdp userName/password@orcl dumpfile=expdp_tablespace.dmp 
　　tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job

• 匯出方案

Expdp userName/password DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=userName,gwm

• 匯出整個資料庫：

expdp userName/password@orcl dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job

3.4、資料泵匯入步驟:

準備：1.建庫2.建資料表空間3.建使用者並授權4.將aa.dmp拷貝到dpdump目錄下（如果不修改並指定備份檔案目錄的話）

• 按表匯入（p_street_area.dmp檔案中的表，此檔案是以gwm使用者按schemas=gwm匯出的：）

impdp gwm/gwm@fgisdb dumpfile =p_street_area.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job

• 按使用者匯入（可以將使用者資訊直接匯入，即如果使用者資訊不存在的情況下也可以直接匯入）

impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job

• 不通過expdp的步驟產生dmp檔案而直接匯入的方法：（從來源資料庫中向目標資料庫匯入表p_street_area）

impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log job_name=my_job-- igisdb是目的資料庫與來源資料的連結名，dir_dp是目的資料庫上的目錄

• 更換資料表空間（採用remap_tablespace參數 ，--匯出gwm使用者下的所有資料）

expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm

注意：

　　如果是用sys使用者匯出的使用者資料，包括使用者建立、授權部分，用自身使用者匯出則不含這些內容

--以下是將gwm使用者下的資料全部匯入到資料表空間gcomm（原來為gmapdata資料表空間下）下impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcomm

 3.5、不同版本資料庫間的資料匯入/匯出3.5.1、Oracle 10g與Oracle 11g之間的匯入匯出

• 匯出

1 create directory dir_dp as ‘D:\backup\dir_dp‘;2 create read,write on directory dir_dp on userName;3 expdp userName/password@orcl schemas=userName directory=dir_dp 
　　　　dumpfile=expdp_userName.dmp logfile=expdp_userName.log version=10.2.0.1.0 --帶上版本號碼

• 匯入

impdp userName/password@orcl schemas=userName directory=dir_dp 
　　　　dumpfile=expdp_userName.dmp logfile=expdp_userName.log version=10.2.0.1.0 --帶上版本號碼

3.5.2、其他方法

　　用Notepad++或其它文本編輯工具軟體查看dmp檔案，將頭部修改成你將匯入目標資料庫的版本號碼
　　以下對應的版本號碼： 11g R2：V11.02.00 　　11g R1：V11.01.00 　　10g：V10.02.01

　　這樣，ORACLE10g就可以匯入此檔案了，當然也可能考驗人品。

[獨孤九劍]Oracle知識點梳理（三）匯入、匯出