oracle分区表导入数据,Oracle 分区表数据的导入与导出（2）

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_NAMEDIRECTORY_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 KB2875 rows

. . exported "SCOTT"."TB_PT":"SAL_11"12.54 KB298 rows

. . exported "SCOTT"."TB_PT":"SAL_12"14.22 KB366 rows

. . exported "SCOTT"."TB_PT":"SAL_13"14.18 KB365 rows

. . exported "SCOTT"."TB_PT":"SAL_14"14.18 KB365 rows

. . exported "SCOTT"."TB_PT":"SAL_15"14.19 KB365 rows

. . exported "SCOTT"."TB_PT":"SAL_16"14.23 KB366 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 KB2875 rows

. . exported "SCOTT"."TB_PT":"SAL_16"14.23 KB366 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_NAMESTATUSPAR

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

TB_PT_IND1UNUSABLE NO

TB_PT_LOCAL_IDXN/AYES

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

INDEX_NAMEPARTITION_NAMESTATUS

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

TB_PT_LOCAL_IDXLOCAL1USABLE

TB_PT_LOCAL_IDXLOCAL2USABLE

TB_PT_LOCAL_IDXLOCAL3USABLE

TB_PT_LOCAL_IDXLOCAL4USABLE

TB_PT_LOCAL_IDXLOCAL5USABLE

TB_PT_LOCAL_IDXLOCAL6USABLE

TB_PT_LOCAL_IDXLOCAL7USABLE

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 KB2875 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_NAMESTATUSPAR

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

TB_PT_IND1VALIDNO

TB_PT_LOCAL_IDXN/AYES

从上面的导入情况可以看出，尽管执行了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 KB2875 rows

. . imported "SCOTT"."TB_PT":"SAL_11"12.54 KB298 rows

. . imported "SCOTT"."TB_PT":"SAL_12"14.22 KB366 rows

. . imported "SCOTT"."TB_PT":"SAL_13"14.18 KB365 rows

. . imported "SCOTT"."TB_PT":"SAL_14"14.18 KB365 rows

. . imported "SCOTT"."TB_PT":"SAL_15"14.19 KB365 rows

. . imported "SCOTT"."TB_PT":"SAL_16"14.23 KB366 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

NAMETYPEVALUE

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

skip_unusable_indexesbooleanTRUE

该参数允许在导入分区数据时延迟对索引的处理，即先将数据导入，导入后再来重建索引分区。

在命令行导入中未指定导入参数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错误。