导入导出 Oracle 分区表数据

发布时间：2020-12-12 16:05:42 所属栏目：百科来源：网络整理

导读：--**************************** --导入导出Oracle分区表数据 --**************************** 导入导入Oracle分区表数据是Oracle DBA经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，

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

--导入导出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*fromv$versionwhererownum<2;

BANNER

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

OracleDatabase11g Enterprise Edition Release 11.2.0.1.0-Production

2.创建一个分区表

SQL>altersessionsetnls_date_format='yyyy-mm-dd';

SQL>CREATETABLEtb_pt(

sal_dateDATENOTNULL,

sal_id NUMBERNOTNULL,

sal_rowNUMBER(12)NOTNULL)

partitionbyrange(sal_date)

(

partitionsal_11valuesless than(to_date('2012-01-01','YYYY-MM-DD')),

partitionsal_12valuesless than(to_date('2013-01-01',

partitionsal_13valuesless than(to_date('2014-01-01',

partitionsal_14valuesless than(to_date('2015-01-01',

partitionsal_15valuesless than(to_date('2016-01-01',

partitionsal_16valuesless than(to_date('2017-01-01',

partitionsal_othervaluesless than(maxvalue)

)nologging;

3.创建一个唯一索引

CREATEUNIQUEINDEXtb_pt_ind1

ONtb_pt(sal_date)nologging;

4.为分区表生成数据

SQL>INSERTINTOtb_pt

SELECTTRUNC(SYSDATE)+ROWNUM,dbms_random.random,ROWNUM

FROMdual

CONNECTBYLEVEL<=5000;

SQL>commit;

SQL>selectcount(1)fromtb_ptpartition(sal_11);

COUNT(1)

----------

300

SQL>selectcount(1)fromtb_ptpartition(sal_other);

COUNT(1)

----------

2873

SQL>select*fromtb_ptpartition(sal_12)whererownum<3;

SAL_DATESAL_IDSAL_ROW

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

01-JAN-12-1.356E+09301

02-JAN-12-761530183302

三、使用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

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 tableTB_PT

. . exporting partitionSAL_11300 rows exported

. . exporting partitionSAL_12366 rows exported

. . exporting partitionSAL_13365 rows exported

. . exporting partitionSAL_14365 rows exported

. . exporting partitionSAL_15365 rows exported

. . exporting partitionSAL_16366 rows exported

. . exporting partitionSAL_OTHER2873 rows exported

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>selectuserenv('language')fromdual;

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

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 tableTB_PT

. . exporting partitionSAL_16366 rows exported

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

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>altertabletb_pttruncatepartitionsal_16;--导入前先将分区实现truncate

Tabletruncated.

SQL>selectcount(1)fromtb_ptpartition(sal_16);

COUNT(1)

----------

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

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 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>selectindex_name,statusfromdba_indexeswheretable_name='TB_PT';--查看索引的状态

INDEX_NAMESTATUS

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

TB_PT_IND1UNUSABLE

SQL>alterindexTB_PT_IND1rebuildonline;--重建索引

Indexaltered.

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

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 partition"TB_PT":"SAL_16"366 rows imported

Import terminated successfully without warnings.

SQL>selectcount(*)fromtb_ptpartition(sal_16);

COUNT(*)

----------

366

5.导入整个表

SQL>truncatetabletb_pt;--首先truncate整个表

Tabletruncated.

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

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 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>selectcount(1)fromtb_ptpartition(sal_other);

COUNT(1)

----------

2875

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

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

SQL>selectdirectory_name,directory_pathfromdba_directorieswheredirectory_name='DMP';

DIRECTORY_NAMEDIRECTORY_PATH

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

DMP/u02/dmp

2.为分区表创建一个本地索引

createindextb_pt_local_idx

ontb_pt(sal_id)

local

(partitionlocal1,

partitionlocal2,

partitionlocal3,

partitionlocal4,

partitionlocal5,

partitionlocal6,

partitionlocal7)

;

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

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

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>altertabletb_pttruncatepartition(sal_other);

Tabletruncated.

SQL>selectcount(*)fromtb_ptpartition(sal_other);

COUNT(*)

----------

SQL>selectindex_name,status,partitionedfromdba_indexeswheretable_name='TB_PT';--查看索引的状态,TB_PT_IND1不可用

INDEX_NAMESTATUSPAR

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

TB_PT_IND1UNUSABLENO

TB_PT_LOCAL_IDXN/AYES

SQL>selectindex_name,partition_name,statusfromdba_ind_partitionswhereindex_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

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>selectindex_name,partitionedfromdba_indexeswheretable_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

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错误。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!