怎么导入导出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分区表数据”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！