怎么導入導出Oracle分區表數據

這篇文章主要介紹“怎么導入導出Oracle分區表數據”，在日常操作中，相信很多人在怎么導入導出Oracle分區表數據問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么導入導出Oracle分區表數據”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

導入導入Oracle 分區表數據是Oracle DBA 經常完成的任務之一。分區表的導入導出同樣可以以普通表的導入導出方式，只不過導入導出需要考慮到分區的特殊性，如分區索引，將分區遷移到普通表，或使用原始分區表導入到新的分區表。下面將描述使用imp/exp，impdp/expdp導入導出

分區表數據
一、分區級別的導入導出
    可以導出一個或多個分區，也可以導出所有分區(即整個表)。
    可以導入所有分區(即整個表)，一個或多個分區以及子分區。
    對于已經存在數據的表，使用imp導入時需要使用參數IGNORE=y，而使用impdp，加table_exists_action=append | replace 參數。

二、創建演示環境
    1.查看當前數據庫的版本
    
        select * from v$version where rownum < 2;

        BANNER
        --------------------------------------------------------------------------------
        Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production


   2.創建一個分區表
        alter session set nls_date_format='yyyy-mm-dd';

        create table tb_pt (
        sal_date   date not null,
        sal_id number not null,
        sal_row    number(12) not null)
        partition by range(sal_date)
        (
        partition sal_11 values less than(to_date('2017-01-01','yyyy-mm-dd')) ,
        partition sal_12 values less than(to_date('2019-01-01','yyyy-mm-dd')) ,
        partition sal_13 values less than(to_date('2023-01-01','yyyy-mm-dd')) ,
        partition sal_14 values less than(to_date('2025-01-01','yyyy-mm-dd')) ,
        partition sal_15 values less than(to_date('2028-01-01','yyyy-mm-dd')) ,
        partition sal_16 values less than(to_date('2030-01-01','yyyy-mm-dd')) ,
        partition sal_other values less than (maxvalue)
        ) nologging;


    3.創建一個唯一索引
        create unique index tb_pt_ind1 on tb_pt(sal_date) nologging;

    4.為分區表生成數據
    ---插入數據
       insert into tb_pt select trunc(sysdate)+rownum, dbms_random.random, rownum from dual connect by level<=8000;

    ---查詢驗證
        select count(1) from tb_pt partition(sal_11);
 
          COUNT(1)
        ----------
               0
       
        select count(1) from tb_pt partition(sal_12);

            COUNT(1)
          ----------
              509

       select count(1) from tb_pt partition(sal_other);

          COUNT(1)
        ----------
              3473

        select * from tb_pt partition(sal_12) where rownum < 3;

        SAL_DATE      SAL_ID    SAL_ROW
        --------- ---------- ----------
        2017-08-10 -2.044E+09          1
        2017-08-11 -1.992E+09          2
               
       select count(1) from tb_pt;

         COUNT(1)
        ----------
           8000  
            
     ---收集分區表統計信息    
        exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TB_PT', estimate_percent => 100,method_opt=> 'for all indexed columns',cascade=>TRUE,granularity=>'ALL');
        
三、使用exp/imp導出導入分區表數據
    1.導出整個分區表
       exp scott/tiger file='/home/oracle/dmp/tb_pt.dmp' log='/home/oracle/dmp/tb_pt.log' tables=tb_pt
       
       注意：需保證數據庫字符集和操作系統環境變量指定的字符集一致。


    2.導出單個分區
        [oracle@slient dmp]$ exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16
        
        Export: Release 11.2.0.4.0 - Production on Wed Aug 9 19:15:06 2017
        
        Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
        
        
        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options
        Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
        
        About to export specified tables via Conventional Path ...
        . . exporting table                          TB_PT
        . . exporting partition                         SAL_16        731 rows exported
        EXP-00091: Exporting questionable statistics.
        EXP-00091: Exporting questionable statistics.
        Export terminated successfully with warnings.
        [oracle@slient dmp]$
        
        在上面的導出過程中再次出現了統計信息錯誤的情況，因此采取了對該對象收集統計信息，但并不能解決該錯誤，但在exp命令行中增加statistics=none即可，如下：

        exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16 statistics=none
        如果要導出多個分區，則在tables參數中增加分區數。如：tables="(tb_pt:sal_15,tb_pt:sal_16)"


    3.使用imp工具生成創建分區表的DDL語句
        imp scott/tiger tables=tb_pt indexfile='/home/oracle/dmp/cr_tb_pt.sql' file='/home/oracle/dmp/tb_pt.dmp' ignore=y

  
      這里我們在imp上加了個參數：indexfile='/home/oracle/dmp/cr_tb_pt.sql'，這條imp語句只會在對應的文件里生成分區表的ddl 語句。 然后編輯創建好就可以了。

    4.導入單個分區(使用先前備份的單個分區導入文件)
        --導入前先將分區實現truncate
        
        select count(1) from tb_pt partition(sal_16);

            COUNT(1)
          ----------
             731

        alter table tb_pt truncate partition sal_16;   

        Table truncated.

       select count(1) from tb_pt partition(sal_16);

          COUNT(1)
        ----------
                 0

        imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y
        
        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:55:39 2011
        Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
        With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
        Data Mining and Real Application Testing o

        Export file created by EXPORT:V11.02.00 via conventional path
        import done in US7ASCII character set and AL16UTF16 NCHAR character set
        import server uses ZHS16GBK character set (possible charset conversion)
        . importing SCOTT's objects into SCOTT
        . importing SCOTT's objects into SCOTT
        . . importing partition               "TB_PT":"SAL_16"
        IMP-00058: ORACLE error 1502 encountered
        ORA-01502: index 'SCOTT.TB_PT_IND1' or partition of such index is in unusable state

        Import terminated successfully with warnings.

        收到了ORA-01502錯誤，下面查看索引的狀態，并對其重建索引后再執行導入
               
       --查看索引的狀態
        select index_name ,status from dba_indexes where table_name='TB_PT';

       INDEX_NAME                     STATUS
       ------------------------------ --------
       TB_PT_IND1                     UNUSABLE
       
        --重建索引
       alter index TB_PT_IND1 rebuild online;               

        Index altered.
        
       --再次導入成功
       imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y


        select count(*) from tb_pt partition(sal_16);

          COUNT(*)
        ----------
               731
               
5.導入整個表

         --首先truncate 整個表
         truncate table tb_pt;

         Table truncated.

         imp scott/tiger tables=tb_pt file='/home/oracle/dmp/tb_pt.dmp' ignore=y indexes=y
         
         
         select count(1) from tb_pt partition(sal_other);

           COUNT(1)
         ----------
              3473          
              
四、使用expdp/impdb來實現分區表的導入導出

    1.查看導入導出的目錄設置

     SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
     
     OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
     ------------------------------ ------------------------------ --------------------------------------------
     SYS                            UTLFILE                        /home/oracle
     SYS                            XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
     SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/slient/state
     SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/test/dpdump/
     SYS                            ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state    
     
     SQL> grant read,write on directory DATA_PUMP_DIR to scott;

     Grant succeeded.
        
    2.為分區表創建一個本地索引

        create index tb_pt_local_idx
        on tb_pt(sal_id)
        local
        (partition local1,
        partition local2,
        partition local3,
        partition local4,
        partition local5,
        partition local6,
        partition local7)
        ;
        
     3.導出整個表
        expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3
        
     4.導出多個分區

       expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2
       -bash: syntax error near unexpected token `('
       
       檢查了腳本沒有錯誤：原來在linux 5中需要在括號加上雙引號才可以
       
       expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables="(tb_pt:sal_16,tb_pt:sal_other)" parallel=2

        
      5.截斷分區sal_other

        alter table tb_pt truncate partition(sal_other);

         Table truncated.

        SQL> select count(*) from tb_pt partition(sal_other);

          COUNT(*)
        ----------
                 0
        
        --查看索引的狀態, TB_PT_IND1不可用
       SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT';

        INDEX_NAME                     STATUS   PAR
        ------------------------------ -------- ---
        TB_PT_IND1                     UNUSABLE NO
        TB_PT_LOCAL_IDX                N/A      YES

        select index_name ,partition_name, status from dba_ind_partitions where index_owner='SCOTT';

        INDEX_NAME                     PARTITION_NAME                 STATUS
        ------------------------------ ------------------------------ --------
        TB_PT_LOCAL_IDX                LOCAL1                         USABLE
        TB_PT_LOCAL_IDX                LOCAL2                         USABLE
        TB_PT_LOCAL_IDX                LOCAL3                         USABLE
        TB_PT_LOCAL_IDX                LOCAL4                         USABLE
        TB_PT_LOCAL_IDX                LOCAL5                         USABLE
        TB_PT_LOCAL_IDX                LOCAL6                         USABLE
        TB_PT_LOCAL_IDX                LOCAL7                         USABLE
        
     6.導入單個分區

      impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace
        
      SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT';

        INDEX_NAME                     STATUS   PAR
        ------------------------------ -------- ---
        TB_PT_IND1                     VALID    NO
        TB_PT_LOCAL_IDX                N/A      YES

       
       從上面的導入情況可以看出，盡管執行了truncate partition，然而使用impdp導入工具，并且使用參數table_exists_action=replace可以避免使用imp導入時唯一和主鍵索引需要重建的問題。注意，如果沒有使用table_exists_action=replace參數，將會收到ORA-39151錯誤，如下:
     ORA-39151: Table "SCOTT"."TB_PT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

    7.導入整個表
     impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log tables=tb_pt skip_unusable_indexes=y table_exists_action=replace
        
五、參數skip_unusable_indexes的作用

    SQL> show parameter skip

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    skip_unusable_indexes                boolean     TRUE

    該參數允許在導入分區數據時延遲對索引的處理，即先將數據導入，導入后再來重建索引分區。
    在命令行導入中未指定導入參數skip_unusable_indexes時，則對于索引相關的問題，根據數據庫初始化參數的值來確定。
    在命令行導入中如果指定了參數skip_unusable_indexes時，則該參數的值優先于數據庫初始化參數的設定值。
    skip_unusable_indexes=y對unique index不起作用，因為此時的unique index扮演者constraint的作用，所以在insert數據時index必須被更新。
    對于單個分區導入時PK，unique index的處理，必須先重建索引然后進行導入。
    使用impdp數據泵實現導入并使用參數table_exists_action=replace可以解決上述問題，即ORA-01502錯誤。

到此，關于“怎么導入導出Oracle分區表數據”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！