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 Bug 16473783 导致,在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 Patch 16928674 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. 打Patch 16928674可以修复此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 http://www.oracle.com/support/policies.html 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
