导入导出 Oracle 分区表数据

逃离我推掉我的手 2022-05-15 05:12 361阅读 0赞

\--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

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

\--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

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

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

分区表数据。

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

[Oracle 分区表][Oracle]

[SQL server 2005 切换分区表][SQL server 2005]

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

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

[数据泵EXPDP 导出工具的使用][EXPDP]

[数据泵IMPDP 导入工具的使用][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

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.

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

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.

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

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)

\----------

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

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

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

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

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

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(\*)

\----------

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

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

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 硬解析与软解析][Oracle 1]

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

[Buffer cache 的调整与优化(一)][Buffer cache]

[Oracle 表缓存(caching table)的使用][Oracle _caching table]

有关闪回特性请参考

[Oracle 闪回特性(FLASHBACK DATABASE)][Oracle _FLASHBACK DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

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

[Oracle 冷备份][Oracle 2]

[Oracle 热备份][Oracle 3]

[Oracle 备份恢复概念][Oracle 4]

[Oracle 实例恢复][Oracle 5]

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

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

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 7]

[Oracle 密码文件][Oracle 8]

[Oracle 参数文件][Oracle 9]

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

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

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 11]

[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/10/07/5925572.aspx
[SQL server 2005]: http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx
[SQL server 2005 1]: http://blog.csdn.net/robinson_0612/archive/2009/11/07/4783702.aspx
[EXPDP]: http://blog.csdn.net/robinson_0612/archive/2010/10/07/5925592.aspx
[IMPDP]: http://blog.csdn.net/robinson_0612/archive/2010/10/08/5926276.aspx
[Original Export and Import]: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx
[Shared pool Tuning]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208268.aspx
[Buffer cache]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208594.aspx
[Oracle _caching table]: http://blog.csdn.net/robinson_0612/archive/2011/03/06/6226767.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 10]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 11]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx