1 使用expdp匯出資料，報如下錯誤

[oracle@HOST_A expdir]$ expdp \'/ as sysdba\' JOB_NAME=xsc1 directory=FKFQ_EXP tables=USER_A.TABLE_A:A09_20220616 dumpfile=TABLE_A0616_1.dmp

logfile=TABLE_A0616_1.log CLUSTER=N FLASHBACK_SCN=15905125660849

Export: Release 11.2.0.4.0 - Production on Thu Jun 16 19:50:02 2022

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."XSC1": "/******** AS SYSDBA" JOB_NAME=xsc1 directory=FKFQ_EXP tables=USER_A.TABLE_A:A09_20220616 dumpfile=TABLE_A0616_1.dmp

logfile=TABLE_A0616_1.log CLUSTER=N FLASHBACK_SCN=15905125660849

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 234 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "USER_A"."TABLE_A":"A09"."A09_20220616" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01466: unable to read data - table definition has changed

Master table "SYS"."XSC1" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.XSC1 is:

/backup/expdir/TABLE_A0616_1.dmp

2 檢視官方文件，說是由於表定義發生變化導致的，可以忽略。

3 使用如下語句，查詢表是否發生變化，經查，確實發生變化

SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

SQL> set linesize 300

SQL> col OWNER for a20

SQL> col OBJECT_NAME for a20

SQL> select a.OWNER,a.OBJECT_NAME,a.CREATED,a.LAST_DDL_TIME from dba_objects a

where a.OBJECT_NAME='TABLE_A' and a.LAST_DDL_TIME > sysdate -1 ;

OWNER OBJECT_NAME CREATED LAST_DDL_TIME

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

USER_A TABLE_A 2021-04-28 21:51:28 2022-06-16 17:41:41

4 採取官方文件的第二個建議，不要新增scn，因為匯出的scn值為2022-06-16 9:10分的，進行匯出，沒有發生報錯，如下：

[oracle@HOST_A expdir]$ expdp \'/ as sysdba\' JOB_NAME=xsc1 directory=FKFQ_EXP tables=USER_A.TABLE_A:A09_20220616,

USER_A.TABLE_A:A08_20220616,USER_A.TABLE_A:A06_20220616,USER_A.TABLE_A:A12_20220616,USER_A.TABLE_A:A10_20220616,

USER_A.TABLE_A:A07_20220616,USER_A.TABLE_A:A11_20220616 dumpfile=TABLE_A0616_2.dmp logfile=TABLE_A0616_2.log CLUSTER=N

Export: Release 11.2.0.4.0 - Production on Fri Jun 17 08:57:13 2022

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."XSC1": "/******** AS SYSDBA" JOB_NAME=xsc1 directory=FKFQ_EXP tables=USER_A.TABLE_A:A09_20220616,

USER_A.TABLE_A:A08_20220616,USER_A.TABLE_A:A06_20220616,USER_A.TABLE_A:A12_20220616,USER_A.TABLE_A:A10_20220616,

USER_A.TABLE_A:A07_20220616,USER_A.TABLE_A:A11_20220616 dumpfile=TABLE_A0616_2.dmp logfile=TABLE_A0616_2.log CLUSTER=N

Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.091 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "USER_A"."TABLE_A":"A08"."A08_20220616" 145.0 MB 646132 rows

. . exported "USER_A"."TABLE_A":"A09"."A09_20220616" 151.9 MB 670802 rows

. . exported "USER_A"."TABLE_A":"A06"."A06_20220616" 137.8 MB 603644 rows

. . exported "USER_A"."TABLE_A":"A12"."A12_20220616" 90.18 MB 393310 rows

. . exported "USER_A"."TABLE_A":"A07"."A07_20220616" 78.79 MB 339139 rows

. . exported "USER_A"."TABLE_A":"A10"."A10_20220616" 82.03 MB 367017 rows

. . exported "USER_A"."TABLE_A":"A11"."A11_20220616" 19.86 MB 80143 rows

Master table "SYS"."XSC1" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.XSC1 is: /backup/expdir/TABLE_A0616_2.dmp

Job "SYS"."XSC1" successfully completed at Fri Jun 17 09:02:14 2022 elapsed 0 00:04:56

expdp匯出報ORA-31693、ORA-02354、ORA-01466

相關文章