导入导出 Oracle 分区表数据

系统 1865 0

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

-- 导入导出 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 错误。

 

六、更多参考

 

    有关性能优化请参考

        Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化( 一)

Oracle 表缓存(caching table) 的使用

 

有关闪回特性请参考

        Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query 、Flashback Table)

Oracle 闪回特性(Flashback Version 、Flashback Transaction)

 

有关基于用户管理的备份和备份恢复的概念请参考:

        Oracle 冷备份

        Oracle 热备份

        Oracle 备份恢复概

        Oracle 实例恢复

        Oracle 基于用户管理恢复的处理 ( 详细描述了介质恢复及其处理 )

       

    有关 RMAN 的恢复与管理请参考:

        RMAN 概述及其体系结构

        RMAN 配置、监控与管理

        RMAN 备份详解

        RMAN 还原与恢复

       

    有关 Oracle 体系结构请参考:

        Oracle 实例和Oracle 数据库(Oracle 系结构)

        Oracle 表空间与数据文件

        Oracle 密码文件

        Oracle 参数文件

Oracle 数据库实例启动关闭过程

        Oracle 联机重做日志文件(ONLINE LOG FILE)

        Oracle 控制文件(CONTROLFILE)

        Oracle 归档日志

 

导入导出 Oracle 分区表数据


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧,狠狠点击下面给点支持吧,站长非常感激您!手机微信长按不能支付解决办法:请将微信支付二维码保存到相册,切换到微信,然后点击微信右上角扫一扫功能,选择支付二维码完成支付。

【本文对您有帮助就好】

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论