導(dǎo)入導(dǎo)出工具exp、imp、expdp、impdp

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

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

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

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

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

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

7、ct count(*) from emp;imp scott/oracle tables=emp file=emp.dmp ignore=y參數(shù)ignore=y 表示直接導(dǎo)入數(shù)據(jù)檢查結(jié)果select cont(*) from emp;將某用戶的表導(dǎo)入到另一個用戶下：交互式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 導(dǎo)入可能出現(xiàn)的問題a 數(shù)據(jù)對象已經(jīng)存在解決方法：使用參數(shù)ignore=y 會把exp文件的數(shù)據(jù)內(nèi)容直接導(dǎo)入如果表有唯一關(guān)鍵字約束，不符合條件的不導(dǎo)入如果表沒有唯一關(guān)鍵字約束，將引起重復(fù)記錄b 數(shù)據(jù)庫對象有主外鍵約束不符合主外鍵約束，數(shù)據(jù)導(dǎo)入失敗解決方法：

9、先導(dǎo)入主表，在導(dǎo)入從表      或者先禁用主外鍵，導(dǎo)入完成后在啟用c 權(quán)限不夠給予imp_full_database exp_full_databased 字符集問題如果字符集不同，導(dǎo)入失敗查看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如果檢查結(jié)果發(fā)現(xiàn)server端和client字符集不一樣，修改為同server端相同的字符集導(dǎo)出的過程轉(zhuǎn)換在 export過程中，源數(shù)據(jù)庫字符集與export用戶會話字符集不一致會發(fā)生字符集轉(zhuǎn)換，并在導(dǎo)出文件頭部幾個字節(jié)中存儲export用戶會話字符集ID號，在這個過程中肯可能發(fā)生

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

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

13、wner=scott file=soctt.dmp刪除用戶 drop user scott cascade ;導(dǎo)入用戶必須要先存在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使用腳本完成導(dǎo)

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í)行權(quán)限chmod u+x /u01/table_scott.sh-     Oracle 的導(dǎo)出導(dǎo)入是一個很常用的遷移工具。 在Oracle 10g中，Oracle 推出了數(shù)據(jù)泵(expdp/impdp). 它可以通過使用并行，從而在效率上要比exp/imp 要高。高速導(dǎo)入導(dǎo)出工具邏輯備份工具-數(shù)據(jù)泵使用專用api導(dǎo)入導(dǎo)出數(shù)據(jù)，速度快expdp只能用于服務(wù)器端，不能用戶客戶端工具目錄：$ORACLE_HOME/bin可以功過dbms_datapump調(diào)用可提供工具：e

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

29、時，要從源數(shù)據(jù)庫取樣和卸載的數(shù)據(jù)百分比。使用compression參數(shù)可以指示是否應(yīng)在導(dǎo)出轉(zhuǎn)存文件中壓縮元數(shù)據(jù)，以便占用更少的磁盤空間。使用數(shù)據(jù)泵之前必須創(chuàng)建目錄對象 ，導(dǎo)入到處都在目錄對象里數(shù)據(jù)庫泵是一個服務(wù)端工具。-expdp HELP=Y-對以 SYSDBA 角色登錄的用戶, 10g 數(shù)據(jù)庫不支持進(jìn)行導(dǎo)出和導(dǎo)入操作。請注銷, 之后使用其它角色登錄并重試。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維護(hù)-導(dǎo)出到文件->導(dǎo)出類型中選擇表->輸入

31、操作系統(tǒng)用戶名和口令4 添加-選擇表dp_test5 選擇目錄dp_dir6 完成。expdp system/oracle dumpfile=expdp01.dmp directory=dp_dir tables=system.dp_test logfile=expdp01.log一：導(dǎo)出，導(dǎo)入表先要創(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二 導(dǎo)出方案expdp scott/oracle directory=dump_dir dumpfile=scott.dmp schemas=scott三導(dǎo)出表空間expdp s

33、ystem/oracle directory=dump_dir dumpfile=users.dmp tablespaces=users四 導(dǎo)出全庫expdp system/oracle directory=dump_dir dumpfile=full.dmp full=y五 并行導(dǎo)出設(shè)置parallel 可以并行導(dǎo)出，導(dǎo)出每個線程創(chuàng)建一個單獨(dú)的導(dǎo)出文件，因此選項(xiàng)應(yī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導(dǎo)入impdp1 將emp表從scott用戶導(dǎo)入到system用戶下 impdp system/oracle directory=dump_dir dumpfile=e1.dmp tables=emp  remap_schema=scott:system2 將soctt方案導(dǎo)入到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 并行導(dǎo)入 expdp system/oracle directory=dump_dir schemas=scottdumpfile=scott_%U.dmp parallel=3先刪除用戶drop user scott cascade;然后導(dǎo)

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;然后導(dǎo)入impdp system/or

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

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

39、;grant read ,write on directory d3 to public;把文本文件拷貝到d3里1.txt 內(nèi)容1,a2,bc,c4,"d"目的建立外表去訪問文本文件的內(nèi)容？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可以查看表的內(nèi)容select * from "extend1"修改文本文件1.txt的內(nèi)容，你會看到表的內(nèi)容一起在變案例：使用外部表訪問警告日志文件對DBA來說，最常見一個例子是可以使用外部表來訪問警告日志文件alert_<sid>.log 或其他跟蹤文件，由于警告日志文件存儲在服務(wù)器上，有時候如果不能直接訪問操