文档章节

导入导出 Oracle 分区表数据

代码cai
 代码cai
发布于 2016/08/23 15:11
字数 3271
阅读 50
收藏 1

 

--****************************

-- 导入导出 Oracle 分区表数据

--****************************

   

    导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考

虑到分区的特殊性,如分区索引,将分区迁移到普通表,或使用原始分区表导入到新的分区表。下面将描述使用imp/exp,impdp/expdp导入导出

分区表数据。

 

    有关分区表的特性请参考: 

        Oracle 分区表

        SQL server 2005 切换分区表

        SQL server 2005 基于已存在的表创建分区

 

    有关导入导出工具请参考:

        数据泵EXPDP 导出工具的使用

        数据泵IMPDP 导入工具的使用

 

    有关导入导出的官方文档请参考:

        Original Export and Import     

 

一、分区级别的导入导出

    可以导出一个或多个分区,也可以导出所有分区(即整个表)。

    可以导入所有分区(即整个表),一个或多个分区以及子分区。

    对于已经存在数据的表,使用imp导入时需要使用参数IGNORE=y,而使用impdp,加table_exists_action=append | replace 参数。

 

二、创建演示环境

    1.查看当前数据库的版本

        SQL> select * from v$version where rownum < 2;

 

        BANNER

        --------------------------------------------------------------------------------

        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

 

    2.创建一个分区表

        SQL> alter session set nls_date_format='yyyy-mm-dd';

 

        SQL> 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('2012-01-01','YYYY-MM-DD')) ,

        partition sal_12 values less than(to_date('2013-01-01','YYYY-MM-DD')) ,

        partition sal_13 values less than(to_date('2014-01-01','YYYY-MM-DD')) ,

        partition sal_14 values less than(to_date('2015-01-01','YYYY-MM-DD')) ,

        partition sal_15 values less than(to_date('2016-01-01','YYYY-MM-DD')) ,

        partition sal_16 values less than(to_date('2017-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.为分区表生成数据

        SQL> INSERT INTO tb_pt

        SELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUM

        FROM dual

        CONNECT BY LEVEL<=5000;

 

        SQL> commit;

 

        SQL> select count(1) from tb_pt partition(sal_11);

 

          COUNT(1)

        ----------

               300

 

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

 

          COUNT(1)

        ----------

              2873

 

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

 

        SAL_DATE      SAL_ID    SAL_ROW

        --------- ---------- ----------

        01-JAN-12 -1.356E+09        301

        02-JAN-12 -761530183        302

 

三、使用exp/imp导出导入分区表数据

    1.导出整个分区表

   

        [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt.dmp' log='/u02/dmp/tb_pt.log' tables=tb_pt

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:18 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 done in US7ASCII character set and AL16UTF16 NCHAR character set

        server uses ZHS16GBK character set (possible charset conversion)

        About to export specified tables via Conventional Path ...

        . . exporting table                          TB_PT

        . . exporting partition                         SAL_11        300 rows exported

        . . exporting partition                         SAL_12        366 rows exported

        . . exporting partition                         SAL_13        365 rows exported

        . . exporting partition                         SAL_14        365 rows exported

        . . exporting partition                         SAL_15        365 rows exported

        . . exporting partition                         SAL_16        366 rows exported

        . . exporting partition                      SAL_OTHER       2873 rows exported

        EXP-00091: Exporting questionable statistics.

        EXP-00091: Exporting questionable statistics.

        Export terminated successfully with warnings.

       

        [oracle@node1 ~]$ oerr exp 00091

        00091, 00000, "Exporting questionable statistics."

        // *Cause:  Export was able export statistics, but the statistics may not be

        //          usuable. The statistics are questionable because one or more of

        //          the following happened during export: a row error occurred, client

        //          character set or NCHARSET does not match with the server, a query

        //          clause was specified on export, only certain partitions or

        //          subpartitions were exported, or a fatal error occurred while

        //          processing a table.

        // *Action: To export non-questionable statistics, change the client character

        //          set or NCHARSET to match the server, export with no query clause,

        //          export complete tables. If desired, import parameters can be

        //          supplied so that only non-questionable statistics will be imported,

        //          and all questionable statistics will be recalculated.

 

        在上面的导出中出现了错误提示,即EXP-00091,该错误表明exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致

        尽管该错误对最终的数据并无影响,但调整该参数来避免异常还是有必要的。因此需要将其设置为一致即可解决上述的错误提示。

       

        SQL> select userenv('language') from dual;

 

        USERENV('LANGUAGE')

        ----------------------------------------------------

        AMERICAN_AMERICA.ZHS16GBK

 

        [oracle@node1 ~]$ export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'   

 

        经过上述设置之后再次导出正常,过程略。

 

    2.导出单个分区

 

        [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:38 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 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        366 rows exported

        EXP-00091: Exporting questionable statistics.

        EXP-00091: Exporting questionable statistics.

        Export terminated successfully with warnings

 

        在上面的导出过程中再次出现了统计信息错误的情况,因此采取了对该对象收集统计信息,但并不能解决该错误,但在exp命令行中增

        加statistics=none即可,如下:

        [oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/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语句

        [oracle@node1 ~]$ imp scott/tiger tables=tb_pt indexfile='/u02/dmp/cr_tb_pt.sql' /

        > file='/u02/dmp/tb_pt.dmp' ignore=y

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:54:38 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)

        . . skipping partition "TB_PT":"SAL_11"                  

        . . skipping partition "TB_PT":"SAL_12"                  

        . . skipping partition "TB_PT":"SAL_13"                  

        . . skipping partition "TB_PT":"SAL_14"                  

        . . skipping partition "TB_PT":"SAL_15"                  

        . . skipping partition "TB_PT":"SAL_16"                  

        . . skipping partition "TB_PT":"SAL_OTHER"               

        Import terminated successfully without warnings.

 

    4.导入单个分区(使用先前备份的单个分区导入文件)

        SQL> alter table tb_pt truncate partition sal_16;   --导入前先将分区实现truncate

 

        Table truncated.

 

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

 

          COUNT(1)

        ----------

                 0

 

        SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/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错误,下面查看索引的状态,并对其重建索引后再执行导入

        SQL> select index_name ,status from dba_indexes where table_name='TB_PT';  --查看索引的状态

 

        INDEX_NAME                     STATUS

        ------------------------------ --------

        TB_PT_IND1                     UNUSABLE

 

        SQL> alter index TB_PT_IND1 rebuild online;                --重建索引

 

        Index altered.

 

        SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=y  --再次导入成功

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:56:15 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"        366 rows imported

        Import terminated successfully without warnings.

       

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

 

          COUNT(*)

        ----------

               366

 

    5.导入整个表

        SQL> truncate table tb_pt;    --首先truncate 整个表

 

        Table truncated.

 

        SQL> ho imp scott/tiger tables=tb_pt file='/u02/dmp/tb_pt.dmp' ignore=y indexes=y

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:57:10 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_11"        298 rows imported

        . . importing partition               "TB_PT":"SAL_12"        366 rows imported

        . . importing partition               "TB_PT":"SAL_13"        365 rows imported

        . . importing partition               "TB_PT":"SAL_14"        365 rows imported

        . . importing partition               "TB_PT":"SAL_15"        365 rows imported

        . . importing partition               "TB_PT":"SAL_16"        366 rows imported

        . . importing partition            "TB_PT":"SAL_OTHER"       2875 rows imported

        Import terminated successfully without warnings.

       

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

 

          COUNT(1)

        ----------

              2875

 

四、使用expdp/impdb来实现分区表的导入导出

    1.查看导入导出的目录设置

        SQL> select directory_name,directory_path from dba_directories where directory_name='DMP';

 

        DIRECTORY_NAME                 DIRECTORY_PATH

        ------------------------------ ------------------------------------------------------------

        DMP                            /u02/dmp

 

    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.导出整个表

        [oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:04:28 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 options

        Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=

            tb_pt parallel=3                                                                  

        Estimate in progress using BLOCKS method...

        Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

        Total estimation using BLOCKS method: 512 KB

        . . exported "SCOTT"."TB_PT":"SAL_OTHER"                 71.63 KB    2875 rows

        . . exported "SCOTT"."TB_PT":"SAL_11"                    12.54 KB     298 rows

        . . exported "SCOTT"."TB_PT":"SAL_12"                    14.22 KB     366 rows

        . . exported "SCOTT"."TB_PT":"SAL_13"                    14.18 KB     365 rows

        . . exported "SCOTT"."TB_PT":"SAL_14"                    14.18 KB     365 rows

        . . exported "SCOTT"."TB_PT":"SAL_15"                    14.19 KB     365 rows

        . . exported "SCOTT"."TB_PT":"SAL_16"                    14.23 KB     366 rows

        Processing object type TABLE_EXPORT/TABLE/TABLE

        Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

        Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

        Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

        Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

        Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

        ******************************************************************************

        Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

          /u02/dmp/tb_pt.dmp

        Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:04:51

 

    4.导出多个分区

        [oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log /

        > tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2

        Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:08:06 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 options

        Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log

            tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2                        --*/

        Estimate in progress using BLOCKS method...

        Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

        Total estimation using BLOCKS method: 192 KB

        . . exported "SCOTT"."TB_PT":"SAL_OTHER"                 71.63 KB    2875 rows

        . . exported "SCOTT"."TB_PT":"SAL_16"                    14.23 KB     366 rows

        Processing object type TABLE_EXPORT/TABLE/TABLE

        Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

        Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

        Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

        Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

        Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

        ******************************************************************************

        Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

          /u02/dmp/tb_pts.dmp

        Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:08:17

 

    5.截断分区sal_other

        SQL> alter table tb_pt truncate partition(sal_other);

 

        Table truncated.

 

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

 

          COUNT(*)

        ----------

                 0

 

        SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT'; --查看索引的状态, TB_PT_IND1不可用

 

        INDEX_NAME                     STATUS   PAR

        ------------------------------ -------- ---

        TB_PT_IND1                     UNUSABLE NO

        TB_PT_LOCAL_IDX                N/A      YES

       

        SQL> 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.导入单个分区

        [oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log /

        > tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace

        Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:13:28 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 options

        Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

        Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log

            tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace             --*/

        Processing object type TABLE_EXPORT/TABLE/TABLE

        Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

        . . imported "SCOTT"."TB_PT":"SAL_OTHER"                 71.63 KB    2875 rows

        Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

        Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

        Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

        Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

        Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:13:33

 

        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.导入整个表

        [oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log /

        > tables=tb_pt skip_unusable_indexes=y table_exists_action=replace

        Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:17:35 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 options

        Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

        Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log

            tables=tb_pt skip_unusable_indexes=y table_exists_action=replace                     --*/

        Processing object type TABLE_EXPORT/TABLE/TABLE

        Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

        . . imported "SCOTT"."TB_PT":"SAL_OTHER"                 71.63 KB    2875 rows

        . . imported "SCOTT"."TB_PT":"SAL_11"                    12.54 KB     298 rows

        . . imported "SCOTT"."TB_PT":"SAL_12"                    14.22 KB     366 rows

        . . imported "SCOTT"."TB_PT":"SAL_13"                    14.18 KB     365 rows

        . . imported "SCOTT"."TB_PT":"SAL_14"                    14.18 KB     365 rows

        . . imported "SCOTT"."TB_PT":"SAL_15"                    14.19 KB     365 rows

        . . imported "SCOTT"."TB_PT":"SAL_16"                    14.23 KB     366 rows

        Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

        Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

        Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

        Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

        Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:17:40

 

五、参数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错误。

本文转载自:http://blog.itpub.net/22578826/viewspace-703466/

代码cai
粉丝 2
博文 14
码字总数 4169
作品 0
北京
后端工程师
私信 提问
oracle12C—RMAN表级恢复

Oracle数据库备份主要分为两类:逻辑和物理备份。每种备份类型都有其自身的优缺点。在之前的版本中,利用现有物理备份来恢复表或分区是不可行的。为了恢复特定对象,逻辑备份是必需的。 在O...

一个笨小孩
2017/07/24
0
0
风险预警·11g容易被忽略的导入性能问题

前言:某大型国有银行一套关键系统10g升级到11g,老K负责升级后第一天早上的运行保障;在升级前甲方客户已经先后做了各种测试,以保证升级后不会存在任何性能问题。然而,事与愿违,老K刚到现...

DBA小y
2017/07/28
0
0
风险预警·11g容易被忽略的导入性能问题

前言 某大型国有银行一套关键系统10g升级到11g,老K负责升级后第一天早上的运行保障;在升级前甲方客户已经先后做了各种测试,以保证升级后不会存在任何性能问题。然而,事与愿违,老K刚到现...

DBA小y
2017/07/28
0
0
oracle学习笔记----数据库导入导出

一、导入和导出 Oracle的备份是oracle操作中常见的工作,常见的备份方案包括有: 1、逻辑备份(IMP&EXP命令进行备份) 有如下四种模式: (1)表空间备份(tablespace) (2)表备份(table...

babyhanggege
2017/04/19
0
0
oracle 数据泵 EXPDP&IMPDP

导出/导入步骤: expdp system/pwd directory=sdirname dumpfile=mes120150810.dmp logfile=mes120150810.log schemas=mes1 CONTENT=METADATAONLY 1.在源资料库s中查看mes1与mes4中所有表和索......

---張榮---
2016/01/16
55
0

没有更多内容

加载失败,请刷新页面

加载更多

远程拷贝命令scp

命令scp 用途: secure copy,利用ssl安全复制(远程文件复制程序) 语法: ]# scp [选项] [用户名@ip]源文件 [用户@ip]目标文件 选项: -P port 指定远程主机的ssh端口 ...

迷失De挣扎
53分钟前
3
0
HTTP

作者:蛙课网 链接:https://zhuanlan.zhihu.com/p/70949908 来源:知乎 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。 HTTP协议简介 HTTP(超文本传输协议)是应用...

元歌
今天
2
0
总结使用tslint的一些代码规范

1.冒号后面,逗号后面要有空格,还有一般if语句大括号前面后面也都要有空格 2.使用for (let i = 0; i < arr.length; i++) {}循环数组报错,使用for (const i of arr) {} for (const i of arr...

大换子
今天
2
0
虚函数

虚函数作用: 基类中可以调用继承类中的函数。(相当于就是允许子类覆盖父类中的函数)

colin_86
今天
2
0
DisplayPort 迎来重大更新,数据带宽性能提高3倍

VESA宣布了他们对DisplayPort接口三年来的第一次重大更新。 与DP 1.4a相比,DisplayPort 2.0提供了三倍于DP 1.4a的数据带宽性能,支持超过8K的分辨率,更高的刷新率和更高分辨率的HDR,以及其...

linux-tao
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部