【expdp/impdp】 ORA-06502、ORA-39077 錯誤分析與解決方案
expdp/impdp操作報錯資訊如下：
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
根據報錯資訊初步分析，可能是datapump元件中sequence數值大於6位數或sequence失效導致。參考mos 文件1550344.1分析，此錯誤由oracle 導致,在Oracle 12.2版本中被修復，如果執行指令碼重新安裝datapump元件可能會引起其它問題。
錯誤模擬
1.測試匯出操作sequence值是否會增加
1.檢視datapump元件包含的sequence
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where owner='SYS' and object_name like '%DATAPUMP%' and object_type='SEQUENCE';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
AQ$_KUPC$DATAPUMP_QUETAB_N SEQUENCE VALID
AQ$_KUPC$DATAPUMP_QUETAB_1_N SEQUENCE VALID
2.檢視兩個sequence的值
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;
NEXTVAL
----------
1
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
361
3.執行匯出
[oracle@ENMOEDU
~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp
tables=test.T_TASKDONE
4.檢視sequence值
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;
NEXTVAL
----------
2
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
368
說明：匯出操作datapump元件中的sequence值會增加
2.測試sequence值超過6位數是否出現此錯誤
1.透過Increment By來實現修改初始值。序列名稱是AQ$_KUPC$DATAPUMP_QUETAB_1_N，初始值是368，而現在要設定初始值為999999，Increment By值為:999619(999999-380)
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 999619;
Sequence altered.
SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;
NEXTVAL
----------
999987
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 7;
Sequence altered.
SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;
NEXTVAL
----------
999994
2.執行expdp匯出操作檢視是否報錯
[oracle@ENMOEDU ~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp logfile=test.log tables=test.T_TASKDONE
Export: Release 11.2.0.3.0 - Production on Thu Aug 7 19:11:36 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
說明：成功模擬錯誤。
手動修復
1.重建sequence
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;
Sequence dropped.
SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;
Sequence created.
2.驗證sequence
1.驗證sequence值超過6位時是否報錯
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N;
Sequence dropped.
SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 999997 increment by 1 cache 20 cycle;
Sequence created.
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
999998
2.執行expdp匯入操作
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:10 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test02.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:21
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test03.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:30 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test03.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test03.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:36
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test04.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:44 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test04.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test04.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:50
說明：三次expdp匯出操作都成功
4.查詢sequence已經cycle到40
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
40
說明：問題可以解決。需要在生產庫執行兩條sql:
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;
SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N
minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;
MOS解決方案分析
MOS文件1550344.1提供的解決方案
SOLUTION
To address the
issue, use any of below alternatives:
o Apply interim for the generic platform if available for your Oracle version.
- OR -
o As a workround, execute next scripts to recreate the datapump objects:
@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Please refer to
Note
16473783.8 - Bug 16473783 -
expdp encounters ORA-39077 and ORA-31638 - withdrawn
Generally speaking, we can recreate the datapump objects in 11g by calling;
1. Catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
2. To recompile invalid objects, if any
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
This is described
in
Note
430221.1 - How To Reload
Datapump Utility EXPDP/IMPDP
- OR -
o As an alternative to a re-installation of datapump, which would need an
instance shutdown/restart, you could recreate the queue table, e.g:
connect / as sysdba
exec dbms_aqadm.drop_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
force => TRUE);
dbms_aqadm.create_queue_table(
queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
multiple_consumers => TRUE,
queue_payload_type => 'SYS.KUPC$_MESSAGE',
comment => 'DataPump Queue Table',
compatible => '8.1.3',
storage_clause=>'TABLESPACE
SYSAUX');
Note that this will cause any running Data Pump jobs on the instance to fail with queue errors. However, they should be restartable.
分析：
1. 打可以修復此bug，可以回退，不用停機風險小。
2. 執行如下指令碼，但此方法會引起其它問題，被官方撤回。
@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
或者
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
3. 執行儲存過程重新安裝datapump元件，需要重啟例項。
connect / as sysdba
exec dbms_aqadm.drop_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
force => TRUE);
dbms_aqadm.create_queue_table(
queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
multiple_consumers => TRUE,
queue_payload_type => 'SYS.KUPC$_MESSAGE',
comment => 'DataPump Queue Table',
compatible => '8.1.3',
storage_clause=>'TABLESPACE SYSAUX');
驗證打path 16928674修復bug
1.錯誤再現
1.將sequence值設定為999987
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
321
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 999666;
Sequence
altered.
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
999987
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 1;
Sequence
altered.
2.執行expdp匯出
[oracle@ENMOEDU dmp]$ expdp test/test directory=MY_DIR dumpfile=d2.dmp tables=T_TASKDONE
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 11:07:03 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user TEST
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_110703612385000 to queue "KUPC$C_1_20140808110703"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-06502: PL/SQL: numeric or value error: character
string buffer too small
3.執行impdp匯入
[oracle@ENMOEDU dmp]$ impdp test/test directory=MY_DIR dumpfile=d2.dmp tables=T_TASKDONE
Import: Release 11.2.0.3.0 - Production on Fri Aug 8 11:07:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_IMPORT_TABLE_01 for user TEST
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_110742848594000 to queue "KUPC$C_1_20140808110742"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
說明：expdp匯出與impdp匯入錯誤原因相同
2.安裝補丁
1.檢視opatch版本
[oracle@ENMOEDU dmp]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/
[oracle@ENMOEDU OPatch]$ ./opatch version
Invoking OPatch 11.2.0.1.7
OPatch Version: 11.2.0.1.7
OPatch
succeeded.
2.上傳最新Opath到$ORACLE_HOME目錄
[oracle@ENMOEDU dbhome_1]$ ls -trl
-rw-r--r-- 1 root
root 32995358 Aug 8 11:17 p6880880_112000_Linux-x86-64.zip
3.備份原Opath目錄
[oracle@ENMOEDU
dbhome_1]$ mv OPatch/ OPatch.bak
4.解壓最新Opath
[oracle@ENMOEDU
dbhome_1]$ unzip p6880880_112000_Linux-x86-64.zip
5.檢視opatch版本
[oracle@ENMOEDU dbhome_1]$ cd OPatch
[oracle@ENMOEDU OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.5
OPatch
succeeded.
6.上傳補丁
[oracle@ENMOEDU tmp]$ cd /u01/
[oracle@ENMOEDU u01]$ mkdir patch
[oracle@ENMOEDU u01]$ cd patch
[oracle@ENMOEDU patch]$ ls
p16928674_112030_Generic.zip
7.解壓補丁
[oracle@ENMOEDU
patch]$ unzip p16928674_112030_Generic.zip
8.檢查補丁
[oracle@ENMOEDU patch]$ ls
16928674 p16928674_112030_Generic.zip
[oracle@ENMOEDU patch]$ cd 16928674/
[oracle@ENMOEDU 16928674]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_11-26-35AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch
succeeded.
9.安裝補丁
[oracle@ENMOEDU 16928674]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_11_28_27/apply2014-08-08_11-28-27AM_1.log
Applying interim patch '16928674' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Backing up files...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Verifying the update...
Patch 16928674 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_11_28_27/apply2014-08-08_11-28-27AM_1.log
OPatch succeeded.
10.檢查安裝結果
[oracle@ENMOEDU 16928674]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_11-30-08AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-08-08_11-30-08AM.txt
----------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 16928674 : applied on Fri Aug 08 11:28:51 CST 2014
Unique Patch ID: 17483843
Created on 2 Apr 2014, 04:20:57 hrs PST8PDT
Bugs fixed:
16928674
----------------------------------------------------------------------------
OPatch succeeded.
11.檢視補丁執行指令碼路徑
[oracle@ENMOEDU 16928674]$ ls
etc files postinstall.sql README.txt
[oracle@ENMOEDU 16928674]$ pwd
/u01/patch/16928674
12.執行補丁指令碼
SQL> @/u01/patch/16928674/postinstall
Calling rdbms/admin/prvtbpci.plb on 08-AUG-14 11.34.01.504642 AM +08:00
Package body created.
Package
body created.
3.測試修復效果
1.測試expdp和impdp是否可以正常執行
[oracle@ENMOEDU ~]$ expdp test/test directory=MY_DIR dumpfile=d3.dmp tables=T_TASKDONE
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 11:34:14 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=MY_DIR dumpfile=d3.dmp tables=T_TASKDONE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 280 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
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 "TEST"."T_TASKDONE" 26.65 MB 233428 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/d3.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 11:34:27
[oracle@ENMOEDU ~]$ impdp test/test directory=MY_DIR dumpfile=d3.dmp tables=T_TASKDONE table_exists_action=replace
Import: Release 11.2.0.3.0 - Production on Fri Aug 8 11:41:55 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=MY_DIR dumpfile=d3.dmp tables=T_TASKDONE table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_TASKDONE" 26.65 MB 233428 rows
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
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 11:42:20
2.檢視sequence值
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
14
說明：安裝patch後在執行expdp和impdp操作前會驗證sequece，如果值大於1000000時，sequence會自動重建。
------------end---------------
DBA_建瑾
2014.8.8
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2057141/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【expdp/impdp】 ORA-06502、ORA-39077 修復補丁安裝與回退操作
- impdp 遇到 ORA-07445 錯誤。重新EXPDP，IMPDP解決了。
- exp/imp出現錯誤通過expdp/impdp來解決
- 【故障解決】ORA-06502錯誤解決
- expdp/impdp來解決exp/imp出現的錯誤並匯入指定表空間
- Expdp Impdp詳解
- ORA-39125 ORA-06502 錯誤解決
- expdp/impdp 用法詳解
- ORACLE expdp/impdp詳解Oracle
- expdp/impdp用法詳解
- ORA-00845的錯誤與解決方案
- latex 錯誤以及解決方案
- [zt] expdp / impdp 用法詳解
- ORA-03113錯誤分析與解決
- Linux下錯誤解決方案Linux
- exp/imp與expdp/impdp區別
- expdp\impdp及exp\imp 詳解
- impdp/expdp 示例
- oracle expdp and impdpOracle
- MySQL常見錯誤分析與解決方法總結MySql
- RMAN-06026 錯誤分析與解決
- [Oracle] ORA-03113錯誤分析與解決Oracle
- ora-12523錯誤分析與解決
- mysql與php錯誤解決MySqlPHP
- Ubuntu Cannot allocate memory 錯誤解決方案Ubuntu
- ORA-28000錯誤解決方案
- ORA-07445: 錯誤解決方案
- ORA-00054錯誤解決方案（摘）
- exp/expdp與imp/impdp的區別
- 案例分析：ora-04031與ora-04030錯誤分析與解決
- zt_RMAN-06026 錯誤分析與解決
- ORACLE常見錯誤程式碼的分析與解決(轉)Oracle
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- 解決MySQL server has gone away錯誤的解決方案MySqlServerGo
- oracle expdp/impdp用法Oracle
- Hadoop常見錯誤及解決方案Hadoop
- HTTP 錯誤 500.21 - Internal Server Error 解決方案HTTPServerError
- MySQL插入資料1366錯誤解決方案MySql