oracle學習筆記----數(shù)據庫導入導出

一、導入和導出  

    Oracle的備份是oracle操作中常見的工作，常見的備份方案包括有：

        1、邏輯備份（IMP&EXP命令進行備份）

            有如下四種模式：

               （1）表空間備份（tablespace）

               （2）表備份（table）

               （3）用戶備份（user）

               （4）完全備份（full）

        2、物理文件備份（脫機及聯(lián)機備份

        3、利用RMAN（Recovery Manager）的增量物理文件系統(tǒng)備份

    oracle的邏輯備份是使用IMP&EXP命令進行數(shù)據導入導出操作。使用EXP命令導出或者使用IMP命令導入時，需要Create Session系統(tǒng)權限，但是如果要導出其他的表，必須擁有權限：EXP_FULL_DATABASE.

    調用導入導出命令時，首先要估計所需的空間。EXP命令導出的文件時二進制文件（*.dmp）只能由對應的IMP命令進行讀取恢復。導入導出的用途是：

        ·備份與恢復

        ·Oracle平臺更換：可以在相同版本之間進行備份與恢復，Oracle較低版本的export數(shù)據文件可以import到高版本的Oracle數(shù)據庫中，但是Oracle的版本只能是相鄰的，不能跨版本。

        ·重組表

        ·在不同數(shù)據庫用戶間移動數(shù)據

        ·在不同數(shù)據庫之間移動數(shù)據表空間

        ·將表的定義存入二進制的操作系統(tǒng)文件以防止用戶操作失誤造成數(shù)據丟失。

    需要指出的是邏輯備份是不能對數(shù)據庫進行完全恢復的，即數(shù)據的丟失時在所難免的。那么邏輯備份會丟失多少數(shù)據呢？從導出開始到導入為止，這段時間之內的數(shù)據將全部丟失，這對銀行、證券交易所和電信等行業(yè)的數(shù)據庫是絕對不能接受的。所以對于絕大多數(shù)真正的生產或商業(yè)數(shù)據庫，邏輯備份永遠是不能作為備份和恢復策略的基石，它們必須要有物理備份以保證全恢復，而邏輯備份只能作為輔助手段。

二、EXP導出數(shù)據

    EXP命令可以在交互環(huán)境下導出數(shù)據庫中的數(shù)據，也可以在非交互環(huán)境下執(zhí)行命令。交互環(huán)境下的命令執(zhí)行，是一步一步執(zhí)行的過程。

    1.交互式導出

    圖中一些代碼的解析：（1）Exp是導出命令，該命令后面緊跟“用戶名/密碼@服務器網絡連接”
    （2）Exp程序導出時使用的緩沖區(qū)大小，緩沖區(qū)越大，導出速度越快。直接回車代表使用默認值4096B。

    （3）Exp命令會把所有要導出的數(shù)據導出到一個Dmp文件中，該步驟是Exp詢問導出的數(shù)據文件名稱。

    （4）Exp程序詢問導出整個用戶還是導出某個表。默認導出整個用戶。

    （5）Exp程序詢問是否導出每張表的訪問權限。默認導出訪問權限。

    （6）Exp程序詢問是否導出表中的數(shù)據。默認導出數(shù)據庫表中的數(shù)據。

    （7）Oracle表中的數(shù)據可能來自不同的分區(qū)中的數(shù)據塊，默認導出時會把所有的數(shù)據壓縮在一個數(shù)據塊上，IMP導入時，如果不存在連續(xù)一個大數(shù)據塊，則會導入失敗。

    2.非交互式導出

    也可以在使用Exp命令時，一次性設置好各種參數(shù)，是準備就緒的Exp命令不需要與用戶交互，按照參數(shù)的要求，Exp命令會一次性執(zhí)行導出工作。要指定參數(shù)，您可以使用關鍵字

    EXP KEYWORD=value 或 KEYWORD=（value1,value2,...valueN）

    例如：EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

    參數(shù)名                                        說明

    USERID                    表示“用戶名/密碼”

    BUFFER                    數(shù)據緩沖區(qū)大小。以字節(jié)為單位，一般在64000以上

    FILE                      指定輸出文件的路徑和文件名，一般以.dmp為后綴名，注意該文件包

                              括完整路徑，但是路徑必須存在，導出命令不能自動創(chuàng)建路徑。

    COMPRESS                  是否壓縮導出，默認yes

    GRANTS                    是否導出權限，默認yes

    INDEXES                   是否導出索引，默認yes

    DIRECT                    是否直接導出，默認情況，數(shù)據先經過Oracle的數(shù)據緩沖區(qū)，然后再

                              導出數(shù)據。

    LOG                       指定導出命令的日志所在的日志文件的位置

    ROWS                      是否導出數(shù)據行，默認導出所有數(shù)據

    CONSTRAINTS               是否導出表的約束條件，默認yes

    PARFILE                   可以把各種參數(shù)配置為一個文本鍵值形式的文件，該參數(shù)可以指定參                                數(shù)文件的位置

    TRIGGERS                  是否導出觸發(fā)器，默認值是yes

    TABLES                    表的名稱列表，導出多個表可以使用逗號隔開

    TABLESPACES               導出某個表空間的數(shù)據

    Owner                     導出某一個用戶的數(shù)據

    Full                      導出數(shù)據庫的所有數(shù)據。默認值是no

    QUERY                     把查詢的結果導出

三、導入數(shù)據

    IMP程序導入就是把exp導出的文件重新導入到數(shù)據庫的過程。導入時也有一些重要的參數(shù)。

    >>>Fromuser:指出導出時dmp文件中記載的用戶信息。

    >>>Touser:dmp文件導入到什么目標用戶中。

    >>>Commit:默認是N,在緩沖區(qū)滿時是否需要commit，如果設為N，需要較大的回滾段。

    >>>Ignore：Oracle在恢復數(shù)據的過程中，當恢復某個表時，該表已經存在，就要根據ignore參數(shù)的設置來覺得如何操作。若ignore=y,Oracle不執(zhí)行CREATE TABLE語句，直接將數(shù)據插入到表中，如果插入的記錄違背了約束條件，比如主鍵約束，則出錯的記錄不會插入，但合法記錄會添加到表中。如ignore=n,Oracle不執(zhí)行CREATE TABLE語句，同時也不會將數(shù)據插入到表中，而是忽略該表的錯誤，繼續(xù)回復下一個表。

    D:\>imp system/manager file=employee.dmp fromuser=scott touser=employee commit=y

四、常見問題

    ·數(shù)據對象已經存在

    一般情況，導入數(shù)據前應該徹底刪除目標數(shù)據下的表，序列，函數(shù)/過程，觸發(fā)器等。

    數(shù)據庫對象已經存在，按缺省的imp參數(shù)，則會導入失敗。

    如果用了參數(shù)ignore=y,會把exp文件內的數(shù)據內容導入。

    如果表沒有唯一關鍵字的約束條件，將引起記錄重復。

    ·數(shù)據庫對象有主外鍵約束

    不符合主外鍵約束時，數(shù)據會導入失敗。

    解決辦法：先導入主表，再導入依存表。

    disable目標導入對象的主外鍵約束，導入數(shù)據后，再enable他們。

    ·權限不夠

    如果要把A用戶的數(shù)據導入B用戶下，A用戶需要由imp_full_datebase權限。

    ·導入大表（大于80M）時，存儲分配失敗

    默認的EXP時，compress=Y，也就是把所有的數(shù)據壓縮在一個數(shù)據塊上。

    導入時，如果不存在連續(xù)一個大數(shù)據塊，則會導入失敗。

    導出80M以上的大表時，記得compress=N，則不會引起這種錯誤。

    ·imp和exp使用的字符集不同

    如果字符集不同，導入會失敗，可以改變unix環(huán)境變量或者NT注冊表里NLS_LANG相關信息。

    ·imp和exp版本上不能往上兼容

    imp可以成功導入低版本exp生成的文件，不能導入高版本exp生成的文件根據情況我們使用。

五、導入和導出實例   

set line 120;    //格式化顯示，更清晰
set pagesize 30;    //格式化顯示，更清晰
create table emp_dump as select * from emp;    /創(chuàng)建名為emp_dump的表
create table dept_dump as select * from dept;    /創(chuàng)建名為emp_dump的表
select count(*) from emp_dump;    /驗證
select count(*) from dept_dump;    /驗證

 當確認這兩個表創(chuàng)建成功后還應創(chuàng)建一個存放邏輯備份文件的目錄f:\export，現(xiàn)在就可以開始做邏輯備份的實驗了。在具體操作之前，先簡單介紹實驗的原理和目的。首先，對剛剛創(chuàng)建的兩個表做一個邏輯備份，之后對其中的一個表進行DML操作并立即提交這些操作。接下來，將這兩個表破壞掉。最后，再用所做的邏輯備份進行數(shù)據恢復以檢驗究竟能恢復多少數(shù)據。

exp scott/tiger file=f:\export\scott.dmp tables=(emp_dump,dept_dump)
select * from emp_dump;
update emp_dump set job='CEO';
commit;
select * from emp_dump;檢查修改是否成功
drop table emp_dump;
drop table dept_dump;
select * from emp_dump;
select * from dept_dump; 此時出現(xiàn)錯誤
imp scott/tiger file=f:\export\scott.dmp;  導入操作
select * from dept_dump;發(fā)現(xiàn)職位并沒有改變，仍是提職之前的。

六、數(shù)據泵介紹

    數(shù)據泵是Oracle 10g引入的一個新工具。它不但包括了所有的以前導入和導出工具的功能，而且還進行了不少擴充和加強。另外，其速度也更快，而且操作也更安全，總之好處多多。其功能與導入導出工具類似。

    數(shù)據泵工具也是一種對操作系統(tǒng)下的應用程序，包括expdp和impdp，其中expdp負責導出，而impdp負責導入。exp導出命令中，我們使用了物理文件的全路徑（絕對路徑），這樣為了將來的管理和維護方便。但是在oracle 10g中，為了系統(tǒng)的安全，在expdpd和impdp應用程序中已經不允許使用絕對路徑，取而代之的是在expdp和impdp應用程序中使用數(shù)據庫的目錄對象。目錄對象一般是由DBA或有相應系統(tǒng)權限的用戶創(chuàng)建，之后再將目錄的讀或寫權限授予所需用戶。為了簡化問題，我們暫時不創(chuàng)建目錄對象，而是使用Oracle數(shù)據庫系統(tǒng)自動創(chuàng)建一個叫DATA——PUMP_DIR目錄對象，該目錄就是expdp和impdp應用程序默認的工作目錄。

    那么如何才能找到該數(shù)據庫目錄對象所對象的操作系統(tǒng)目錄呢？Oracle提供了一個叫dba_directories數(shù)據字典，利用它就可以方便地找到所需信息。但是，為了使顯示輸出清晰。首先是要SQL*Plus格式化命令（要以DBA用戶登錄數(shù)據庫系統(tǒng)）。

set line 120;///我們工作常用200
col OWNER for a6;
col DIRECTORY_NAME for a20;
col DIRECTORY_PATH for a65;
select * from dba_directories;
GARNT READ,WRITE ON DIRECTORY DATA_PUMP_DIR to scott;//賦予scott用戶對DATA_PUMP_DIR的權限
select * from emp_dump order by job,sql; 切換SCOTT用戶后，查詢相關信息

    參數(shù)介紹：

    ·DIRECTORY=DATA_PUMP_DIR,存放導出文件的目錄為DATA_PUMP_DIR所定義的操作系統(tǒng)目錄。

    ·tables=(emp_dump,dept_dump),要導出的表為emp_dump和dept_dump

    ·DUMPFILE=SCOTT.dmp,導出操作系統(tǒng)文件的名為SCOTT.dmp。

    ·QUERY=scott.emp_dump:"WHERE job<>'ANALYST' AND sal>1250",在scott的emp_dump表中只有job(職位)不是ANALYST（分析員），并且sal（工資）高于1250的數(shù)據才導出到SCOTT.dmp文件中。

    由于這個實力的單數(shù)很多，我么將上面的參數(shù)保存在一個名為scott_par.txt的正文參數(shù)文件中，之后expdp應用程序再調用這個正文參數(shù)文件。如果導出工作是經常的工作，這回極大地簡化例行的操作。因此啟動記事本程序并將上面所介紹的參數(shù)寫入，最后以scott_par.txt為文件名存入F:\oracle\mgt目錄中（該目錄時自己創(chuàng)建，也可以使用其他的目錄名）。

    啟動DOS窗口，

F：\>cd oracle\mgt
F：\>cd oracle\mgt>dir
F：\>cd oracle\mgt>more scott_par.txt

DIRECTORY=DATA_PUMP_DIR
tables=(emp_dump,dept_dump)
DUMPFILE=SCOTT.dmp
QUERY=scott.emp_dump:"WHERE job<>'ANALYST' AND sql>1250"

F：\>cd oracle\mgt>expdp scott/tiger parfile=scott_par.txt 查看是否該文件創(chuàng)建生成

drop table emp_dump; 以scott用戶
select * from emp_dump;//驗證是否真的被刪除

    現(xiàn)在就可以利用剛剛做的邏輯備份（用數(shù)據泵導出的文件）進行邏輯恢復了，可以使用如下命令將用數(shù)據泵將SCOTT.DMP文件中的數(shù)據重新導入回SCOTT用戶中（邏輯恢復）。

F：\>cd oracle\mgt> impdp scott/tiger parfile=scott_par.txt

    顯示輸出標明，SCOTT用戶下的DEPT_DUMP已經存在，這是因為之前只刪除了emp_dump表，但是這并不影響邏輯恢復的結果，impdp程序只是跳過了已經存在的表。接下來，使用SQL語句檢查emp_dump表是否已經被成功恢復。

    select * from emp_dump;

    上述語句，顯示出的結果表明：impdp只恢復了滿足條件（job不是ANALYST并且sal高于1250）的數(shù)據，即該表的子集。最后，使用語句檢查一下dept_dump表是否完好無損。經查看dept_dump表中的數(shù)據確實完好無損，這也正是我們期望看到的結果到此為止可以確信所做的邏輯恢復已經成功。