一、背景介紹

?Oracle針對一些超大表，我們通常都會使用分區(qū)表來存放數(shù)據(jù)，便于優(yōu)化、歸檔等，但是分區(qū)表也需要定期維護(hù)，增加新的分區(qū)，否則可能會發(fā)生如下錯誤:

ORA-14400: 插入的分區(qū)關(guān)鍵字未映射到任何分區(qū)

?在oracle11g出現(xiàn)之前，實際工作中經(jīng)常會遇到這種情況，而處理這種情況，通常是由DBA手動進(jìn)行分區(qū)或者直接定義maxvalue。

?11g之后推出新增特性Interval分區(qū)可以解決這個問題，它是針對Range類型分區(qū)的一種功能拓展，支持自動按年、月、日分區(qū)。對連續(xù)數(shù)據(jù)類型的Range分區(qū)，如果插入的新數(shù)據(jù)值與當(dāng)前分區(qū)均不匹配，Interval-Partition特性可以實現(xiàn)自動的分區(qū)創(chuàng)建。

二、問題描述

? 客戶反饋每日exp備份任務(wù)，執(zhí)行報錯，檢查導(dǎo)出log，發(fā)現(xiàn)在導(dǎo)出一部分表之后，會拋出EXP-00006: 出現(xiàn)內(nèi)部不一致的錯誤，然后導(dǎo)出結(jié)束。備份log如下：

連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將導(dǎo)出指定的用戶...
. 正在導(dǎo)出 pre-schema 過程對象和操作
. 正在導(dǎo)出用戶 XXXX 的外部函數(shù)庫名
. 導(dǎo)出 PUBLIC 類型同義詞
. 正在導(dǎo)出專用類型同義詞
. 正在導(dǎo)出用戶 XXXX 的對象類型定義
即將導(dǎo)出 XXXX 的對象...
. 正在導(dǎo)出數(shù)據(jù)庫鏈接
. 正在導(dǎo)出序號
. 正在導(dǎo)出簇定義
. 即將導(dǎo)出 XXXX 的表通過常規(guī)路徑...
.................
................
. . 正在導(dǎo)出表                         WCXSSCB導(dǎo)出了           0 行
. . 正在導(dǎo)出表                        WEBCDGNB導(dǎo)出了          89 行
EXP-00006: 出現(xiàn)內(nèi)部不一致的錯誤
EXP-00000: 導(dǎo)出終止失敗

?通過導(dǎo)出log只能看到exp-6錯誤信息，不夠清晰，無法判斷是哪個具體的表導(dǎo)出失敗的，經(jīng)查詢mos，看到文檔Doc ID 762774.1描述，11g之后，exp不支持Interval-Partition導(dǎo)出。?

三、問題復(fù)現(xiàn)

1) 沒有自動分區(qū)表情況下導(dǎo)出

[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:42:19 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

?結(jié)論：exp導(dǎo)出正常，無任何報錯。

2) 有自動分區(qū)表情況下導(dǎo)出

SQL> conn sxc/sxc
Connected.
SQL> 
SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  5    create_time DATE
  6  )
  7  PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
  8  (partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

Table created.

SQL> insert into test values(1,'test1',sysdate);

1 row created.

SQL> commit;

Commit complete.

[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:43:29 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

結(jié)論：exp導(dǎo)出報錯，拋出EXP-6 和 EXP-0錯誤信息，和客戶反饋問題一致。

四、解決辦法

方法1：使用數(shù)據(jù)泵導(dǎo)出

[oracle@ora11204 ~]$ expdp sxc/sxc directory=SOURCEDIR dumpfile=test_expdp.dmp logfile=test_expdp.log   
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:44:30 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SXC"."SYS_EXPORT_SCHEMA_01":  sxc/******** directory=SOURCEDIR dumpfile=test_expdp.dmp logfile=test_expdp.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SXC"."T"                                   7.651 MB   78920 rows
. . exported "SXC"."TEST":"SYS_P101"                     5.867 KB       1 rows
. . exported "SXC"."TEST":"PART_T01"                         0 KB       0 rows
Master table "SXC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/oradata/test_expdp.dmp
Job "SXC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 14 10:45:03 2022 elapsed 0 00:00:33

結(jié)論：使用expdp導(dǎo)出正常，無任何報錯。

方法2：使用普通分區(qū)表

-- 1）轉(zhuǎn)化為普通分區(qū)表
SQL> alter table test rename to test_bak;

Table altered.

SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  create_time DATE
)
partition by range (create_time)
(
  partition part_t01 values less than (TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition part_tmax values less than (MAXVALUE)                         
 11  );

Table created.

SQL> insert into test select * from test_bak;

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_bak;

Table dropped.


-- 2）再次使用exp導(dǎo)出：正常，無任何報錯
[oracle@ora11204 ~]$  exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 11:26:24 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
. . exporting table                           TEST
. . exporting partition                       PART_T01          0 rows exported
. . exporting partition                      PART_TMAX          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

五、延申測試

1) 新建自動分區(qū)表，不插入數(shù)據(jù)

SQL> conn sxc/sxc
Connected.
SQL> drop table test;
SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  5    create_time DATE
  6  )
  7  PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
  8  (partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

Table created.

-- 新建自動分區(qū)表之后，直接導(dǎo)出
[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:51:23 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00113: Feature New Composite Partitioning Method is unsupported. Table SXC.TEST could not be exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

結(jié)論：exp導(dǎo)出同樣報錯，但是拋出了 EXP-00113: Feature New Composite Partitioning Method is unsupported. Table SXC.TEST could not be exported錯誤信息，提示了是 SXC.TEST 表導(dǎo)出失敗，比之前的錯誤信息詳細(xì)。

2) 自動分區(qū)表插入數(shù)據(jù)

SQL> insert into test values(1,'test1',sysdate);

1 row created.

SQL> commit;

Commit complete.


-- 插入數(shù)據(jù)后，再導(dǎo)出
[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 11:09:02 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

結(jié)論：exp導(dǎo)出同樣報錯，但是并沒有拋出了 EXP-00113錯誤信息，只拋出了EXP-6 和 EXP-0錯誤信息，不夠清晰。