導入導出工具exp、imp、expdp、impdp

1、導入導出工具exp、imp、expdp、impdpexp和imp是在數(shù)據(jù)泵之前的邏輯備份工具。一：導出工具exp1.$ORACLE_HOME/bin 操作系統(tǒng)下可執(zhí)行文件導出工具將數(shù)據(jù)庫中的數(shù)據(jù)壓縮在一個二進制文件三中模式：a.表模式：導出用戶所有的表或者指定的表b.用戶模式：導出用戶下的所有內容c.整個數(shù)據(jù)庫：導出數(shù)據(jù)庫中的所有對象c查看幫助命令exp help =y練習導出前1）查詢數(shù)據(jù)庫的字符集（方法很多只用一種）SQL> select userenv('language') from dual;USERE

2、NV('LANGUAGE')-AMERICAN_AMERICA.WE8ISO8859P12）設置Linux操作系統(tǒng)的NLS_LANG環(huán)境變量 export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1開始導出使用exp把scott.emp表導出來 導出文件名scott.empexp help=y  imp help=y  查看幫助exp scott/oracle  TABLES=emp file=scott.emp然后刪除scott.emp表sqlplus 

3、scott/oracledrop table emp;最后使用導出的文件恢復原來的emp表imp scott/oracle  TABLES=emp file=scott.emp檢查是否成功select count(*) from scott.emp;例如：表模式exp scott/oracle tables=emp file=emp.dmp導出多張表exp scott/oracle tables=emp,dept file=ed.dmp使用sys

4、用戶導出exp tables=scott.emp file=emp1.dmpusername:sys as sysdbapassword: oracle或者exp 'sys/oracle as sysdba'  tables=scott.emp  file=emp2.dmp用戶模式導出用戶所有的對象exp scott/oracle owner=scott file=soctt.dmp全庫導出exp system/orac

5、le full=y file=full.dmp指定導出片的大小，限制為1gexp scott/oracle tables=emp file=emp2.dmpfilesize=1g參數(shù)filesize 指定了二進制備份文件的最大字節(jié)數(shù)目的是解決某些操作系統(tǒng)下2g物理文件限制以及可以加快壓縮和方便刻錄光盤等也可以遠程導出到本地exp system/oracleorcl tables=scott.emp file=emp168.dmp導入工具：imp1 該工具是$ORACLE_HOME/bin下一個可執(zhí)行文件2 imp將

6、exp生成的二進制文件導入數(shù)據(jù)庫中  只有exp_full_database 和dba權限的用戶才能 做整個數(shù)據(jù)庫的導入grant exp_full_database to scott;3 imp操作步驟先create table ->insert data ->create index ->create trigger ,constriants例子練習：先刪除 scott.emp 表的記錄，使用備份文件恢復記錄sqlplus scott/oracletruncate table emp;sele

7、ct count(*) from emp;imp scott/oracle tables=emp file=emp.dmp ignore=y參數(shù)ignore=y 表示直接導入數(shù)據(jù)檢查結果select cont(*) from emp;將某用戶的表導入到另一個用戶下：交互式imp   tables=emp file=emp.dmp fromuser=scott touser=hr輸入用戶名： sys as sysdba輸入密碼:   &#

8、160; oracle非交互式imp 'sys/oracle as sysdba'  tables=emp file=emp.dmp fromuser=scott touser=hr約束報錯，可以不需要創(chuàng)建約束，加入?yún)?shù)CONSTRAINTS=N4 導入可能出現(xiàn)的問題a 數(shù)據(jù)對象已經(jīng)存在解決方法：使用參數(shù)ignore=y 會把exp文件的數(shù)據(jù)內容直接導入如果表有唯一關鍵字約束，不符合條件的不導入如果表沒有唯一關鍵字約束，將引起重復記錄b 數(shù)據(jù)庫對象有主外鍵約束不符合主外鍵約束，數(shù)據(jù)導入失敗解決方法：

9、先導入主表，在導入從表      或者先禁用主外鍵，導入完成后在啟用c 權限不夠給予imp_full_database exp_full_databased 字符集問題如果字符集不同，導入失敗查看oracle server端的字符集select userenv('language') from dual;查看dump文件字符集cat scott.dmp |od -x|head -1|awk 'print $2 $

10、3'|cut -c 3-60345select nls_charset_name(to_number('0354','xxxx') from dual;查看oracle client端字符集echo $NLS_LANG如果檢查結果發(fā)現(xiàn)server端和client字符集不一樣，修改為同server端相同的字符集導出的過程轉換在 export過程中，源數(shù)據(jù)庫字符集與export用戶會話字符集不一致會發(fā)生字符集轉換，并在導出文件頭部幾個字節(jié)中存儲export用戶會話字符集ID號，在這個過程中肯可能發(fā)生

11、數(shù)據(jù)丟失。要正確導出源數(shù)據(jù)庫數(shù)據(jù)，要求export過程的用戶會話字符集應等于源數(shù)據(jù)庫字符集或是源數(shù)據(jù)庫字符集的超集導入的過程字符集轉換確定導出數(shù)據(jù)庫字符集環(huán)境（通過讀取導出文件頭獲?。┐_定session字符集（通過nls_lang獲得）imp讀取導出的文件，讀取文件集ID于導入進程所在的會話nls_lang比較如果相同不做轉換，不過不同要把數(shù)據(jù)文件字符集轉為session字符集總結：先去檢查導入文件字符集于導入session使用字符集之間的轉換，如果轉換不成功，則imp就不能完成然后導入session字符集與數(shù)據(jù)庫字符集之間轉換f imp,exp版本不能向上兼容imp可以導入低版本exp生成的

12、文件，不能導入高版本exp生成的文件g 導入大表（大于80M),存儲失敗默認的exp時，compress=y,也就是數(shù)據(jù)將壓縮在一個數(shù)據(jù)塊上導入時，如果不存在連續(xù)一個大數(shù)據(jù)塊，導入失敗導出80m以上大表時，將compress=n5 exp imp參數(shù)buffer=4096000 (數(shù)據(jù)緩沖區(qū)代銷哦啊，以字節(jié)為單位，自行調整）對性能有很大影響compress=y(將在導出時候合并碎片）direct=y(以direct 方式導出，告訴exp直接讀取數(shù)據(jù)，速度快）feedback(每x行顯示進度）例如：將用戶的所有內容刪除后恢復用戶的所有內容1 導出用戶所有的對象exp scott/oracle o

13、wner=scott file=soctt.dmp刪除用戶 drop user scott cascade ;導入用戶必須要先存在create user scott identified by oracledefault tablespace userstemporary tablespace tempquota unlimited on users;grant connect，resource to scott ;imp system/oracle file=scott.dmp fromuser=scott touser=scott ignore=y buffer=200000使用腳本完成導

14、出某個用戶的所有表set feedback off heading off  verify off trimspool offset pagesize 0 linesize 200spool /u01/table_scott.shselect 'exp scott/oracle tables=' | table_name | ' file=/u01/' | table_name |'.dmp' from user_tables;spool offvim /u01/table_scott.sh#! /bin/bashexp scott/o

15、racle  tables=BT file=/u01/BT.dmp                                           

16、0;                                                 

17、0;                     exp scott/oracle  tables=DEPT file=/u01/DEPT.dmp                    

18、;                                                  

19、;                                         exp scott/oracle  tables=DT file=/u01/DT.dmp

20、0;                                                 

21、0;                                                 

22、0;              exp scott/oracle  tables=EMP file=/u01/EMP.dmp                           &

23、#160;                                                 &

24、#160;                                   exp scott/oracle  tables=MANAGER file=/u01/MANAGER.dmp    &#

25、160;                                                 &#

26、160;                                              exp scott/oracle

27、0; tables=SALGRADE file=/u01/SALGRADE.dmp給予執(zhí)行權限chmod u+x /u01/table_scott.sh-     Oracle 的導出導入是一個很常用的遷移工具。 在Oracle 10g中，Oracle 推出了數(shù)據(jù)泵(expdp/impdp). 它可以通過使用并行，從而在效率上要比exp/imp 要高。高速導入導出工具邏輯備份工具-數(shù)據(jù)泵使用專用api導入導出數(shù)據(jù)，速度快expdp只能用于服務器端，不能用戶客戶端工具目錄：$ORACLE_HOME/bin可以功過dbms_datapump調用可提供工具：e

28、xpdp impdp  基于web界面可提供數(shù)據(jù)訪問方法：直接路徑 外部表作用：oracle數(shù)據(jù)庫之間導入導出數(shù)據(jù)泵是oracle database 10g的一個組成功能數(shù)據(jù)泵優(yōu)點：exclude ,include 和content 參數(shù)用于選擇細粒度級對象和數(shù)據(jù)version參數(shù)指定要移動的對象的數(shù)據(jù)庫版本parallel參數(shù)指定代表導出作業(yè)運行的活動執(zhí)行服務器的最大線程數(shù)estimate_only參數(shù)可估計導出作業(yè)占用的空間量（實際上并不執(zhí)行導出）使用網(wǎng)絡模式可從遠程數(shù)據(jù)庫直接導出到轉存文件集在導入過程中，可以更改目標數(shù)據(jù)文件名、方案和表空間此外10g還允許您指定在執(zhí)行數(shù)據(jù)泵導出

29、時，要從源數(shù)據(jù)庫取樣和卸載的數(shù)據(jù)百分比。使用compression參數(shù)可以指示是否應在導出轉存文件中壓縮元數(shù)據(jù)，以便占用更少的磁盤空間。使用數(shù)據(jù)泵之前必須創(chuàng)建目錄對象 ，導入到處都在目錄對象里數(shù)據(jù)庫泵是一個服務端工具。-expdp HELP=Y-對以 SYSDBA 角色登錄的用戶, 10g 數(shù)據(jù)庫不支持進行導出和導入操作。請注銷, 之后使用其它角色登錄并重試。1創(chuàng)建data pump 使用的一個表conn system/oraclecreate table dp_test as select * from 

30、;all_users;select count(*) from dp_test;2 創(chuàng)建oracle目錄mkdir /u01/dp_dircreate directory dp_dir as '/u01/dp_dir'grant all on directory dp_dir to public;可以通過dba_directories 查看3 使用system賬戶打開Dbconsole維護-導出到文件->導出類型中選擇表->輸入

31、操作系統(tǒng)用戶名和口令4 添加-選擇表dp_test5 選擇目錄dp_dir6 完成。expdp system/oracle dumpfile=expdp01.dmp directory=dp_dir tables=system.dp_test logfile=expdp01.log一：導出，導入表先要創(chuàng)建目錄對象 mkdir /u01/backupcreate directory dump_dir as '/u01/backup'grant read 

32、;,write  on directory dump_dir to scott;expdp scott/oracle tables=emp directory=dump_dir dumpfile=e1.dmp  logfile =e1.log二 導出方案expdp scott/oracle directory=dump_dir dumpfile=scott.dmp schemas=scott三導出表空間expdp s

33、ystem/oracle directory=dump_dir dumpfile=users.dmp tablespaces=users四 導出全庫expdp system/oracle directory=dump_dir dumpfile=full.dmp full=y五 并行導出設置parallel 可以并行導出，導出每個線程創(chuàng)建一個單獨的導出文件，因此選項應該擁有和并行線程一樣多的文件通配符 %U 文件格式full_%U.dmp(full_01,full_02,.)例如：expdp system/oracle dir

34、ectory=dump_dir dumpfile=users_%U.dmp tablespaces=users parallel=3導入impdp1 將emp表從scott用戶導入到system用戶下 impdp system/oracle directory=dump_dir dumpfile=e1.dmp tables=emp  remap_schema=scott:system2 將soctt方案導入到system用戶下expdp scott/oracle directory=dump_dir

35、60;dumpfile=scott.dmp schemas=scottimpdp system/oracle directory=dump_dir dumpfile=scott.dmpremap_schema=scott:system3 并行導入 expdp system/oracle directory=dump_dir schemas=scottdumpfile=scott_%U.dmp parallel=3先刪除用戶drop user scott cascade;然后導

36、入impdp system/oracle directory=dump_dir  schemas=scott dumpfile=scott_%U.dmp parallel=3對與hr schema expdp system/oracle directory=dump_dir schemas=hrdumpfile=hr_%U.dmp parallel=3先刪除用戶drop user scott cascade;然后導入impdp system/or

37、acle directory=dump_dir  schemas=hr dumpfile=hr_%U.dmp parallel=3-數(shù)據(jù)泵：可以高速移動數(shù)據(jù)與元數(shù)據(jù)的，基于服務器的設備數(shù)據(jù)泵具有以下特點可通過DBMS_DATAPUMP調用PL/SQL程序包調用數(shù)據(jù)泵基礎結構。因此數(shù)據(jù)泵可以構建自定義的移動數(shù)據(jù)實用程序?？商峁┕ぞ撸?expdp impdp            基于web界面數(shù)據(jù)泵自動確定要使用的數(shù)據(jù)訪問方法直

38、接路徑或外部表。如果表結構允許實現(xiàn)思路：sqlldr 的會生成一個日志，日志記錄所有操作，會包含創(chuàng)建外部表的語法執(zhí)行sqlldr 必須有控制文件借助EM幫我生成控制文件然后通過控制文件生成日志，在日志文件找出創(chuàng)建外部表的語法，然后copy執(zhí)行一次，外部表就創(chuàng)建好效果：可以通過建立外部表，通過sqlloader去查看外部的文件，外部文件的更新會適時的通過外部表查看。實現(xiàn)過程：首先要創(chuàng)建外部表，外部表的語法復雜，所以想辦法復制過來首先使用命令建立目錄對象（建議手工建立） create directory d3 as '/u01/d3'

39、;grant read ,write on directory d3 to public;把文本文件拷貝到d3里1.txt 內容1,a2,bc,c4,"d"目的建立外表去訪問文本文件的內容？sqlldr system/oracle data=a.txt control=1.ctl log=1.log bad=1.bad direct=yes通過這個語法可以創(chuàng)建日志文件，在日志文件里有創(chuàng)建外部表的語法這個命令需要控制文件，可以通過em來生成控制文件

40、data movement->load data from user files-> 一路下去，最后的時候選擇控制文件信息保留然后把控制文件拷貝到d3里執(zhí)行如下命令，生成日志文件d3.logsqlldr system/oracle data=a.txt control=1.ctl log=d3.log bad=1.bad external_table =GENERATE_ONLY查看d3.log修改表名，這個表就是外部表CREATE TABLE "extend1"

41、("A" NUMBER(38),"B" CHAR(10)ORGANIZATION external(TYPE oracle_loaderDEFAULT DIRECTORY D3ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE 'D3':'1.bad'LOGFILE 'd3.lo

42、g_xt'READSIZE 1048576FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIMREJECT ROWS WITH ALL NULL FIELDS("A" CHAR(255)TERMINATED BY "," OPTIONALLY ENC

43、LOSED BY '"',"B" CHAR(255)TERMINATED BY "," OPTIONALLY ENCLOSED BY '"')location('1.txt')REJECT LIMIT UNLIMITED可以查看表的內容select * from "extend1"修改文本文件1.txt的內容，你會看到表的內容一起在變案例：使用外部表訪問警告日志文件對DBA來說，最常見一個例子是可以使用外部表來訪問警告日志文件alert_<sid>.log 或其他跟蹤文件，由于警告日志文件存儲在服務器上，有時候如果不能直接訪問操