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

1.重建sequence使用cycle参数限制最大值不超过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 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

时间： 2024-09-19 21:11:08

【expdp/impdp】 ORA-06502、ORA-39077 错误分析与解决方案的相关文章

exp/imp 与 expdp/impdp 对比

原文转自:http://blog.csdn.net/tianlesoftware/article/details/6093973 一. exp/imp 与 expdp/impdp 对比  1.1 expdp/impdp调用Server端的API在执行操作,是数据库内部的job任务.可以远程使用,但是生成的dump 文件存在于服务器上的directory里.   1.2  exp/imp 与 expdp/impdp 的默认模式和原理不一样   1.2.1 exp/imp 不同模式原理 在metal

Oracle datapump expdp/impdp 导入导出数据库时hang住

   最近在导出schema级别的数据时被hang住,不得不停止当前的导出作业,如果你有类似的问题,请继续往下看.  1.问题描述    导出整个schema时数据库被hang住,如下所示    符号">"是由SecureCRT设定的每300秒发送一次    oracle@Dev-DB-04:~> expdp goex_admin/xxx directory=db_dump_dir dumpfile=gobo2.dmp logfile=gobo2.log schemas=g

exp/imp与expdp/impdp的用法区别

exp/imp与expdp/impdp的用法区别 1:把用户usera的对象导到用户userb,用法区别在于fromuser=usera touser=userb ,remap_schema='usera':'usera' .例如 imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log; impdp system/passwd directory=expdp dumpfile=e

oracle-mariadb imp/dmp问题,mariadb是否支持expdp/impdp

问题描述 mariadb imp/dmp问题,mariadb是否支持expdp/impdp mariadb,是否可以导出expdp,impdp,想请教一下装什么mariadb版本数据库比较好 解决方案 version is 10

数据泵expdp/impdp工具的使用：

数据泵expdp/impdp工具的使用: 建立数据泵目录 使用数据泵需要先建directory create directory dump_scott as'/home/oracle/dump/scott' 查看建立的目录 Select * from dba_directories 赋权 Grant read,write on directory dump_scott toscott   导出案例1,按表导出 expdp scott/tiger directory=dump_scottdumpf

Oracle expdp/impdp 用法例子 详解 举例 例子 他的这篇文章没有办法写出来 要这么改

 步骤:   导出端: 建立导出目录. grant create any directory to qbjmes;  create directory backup as '/tmp/backup'; grant read, write on directory backup to public;   导入端:  grant create any directory to qbjmes;  create directory backup as '/tmp/backup'; grant read,

EXP/IMP EXPDP/IMPDP 时间比较

EXP/IMP EXPDP/IMPDP 时间比较 EXP: C:\Documents and Settings\Administrator>exp awen/oracle@orcl buffer=65536 file=d :\awen.dmp  Export: Release 10.2.0.1.0 - Production on 星期四 3月 8 12:49:30 2012  Copyright (c) 1982, 2005, Oracle.  All rights reserved.  连接到

ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)

        不论是单实例还是RAC,对于非缺省端口下(1521)的监听器,pmon进程不会将service/instance注册到监听器,即不会实现动态注册.与单实例相同,RAC非缺省端口的监听器也是通过设置参数local_listener来达到目的.除此之外,还可以对实例进行远程注册,以达到负载均衡的目的.这是通过一个参数remote_listener来实现.   有关Oracle 网络配置相关基础以及概念性的问题请参考:      配置ORACLE 客户端连接到数据库   配置非默认端口

oracle imp/dmp问题,是否支持expdp/impdp

问题描述 oracle imp/dmp问题,是否支持expdp/impdp oracle9i,10g,是否可以导出expdp,impdp,想请教一下装什么版本比较好 解决方案 Oracle9i 也支持EXP 但是有一些 break-up features 具体参见http://www.cnblogs.com/mfryf/archive/2013/06/16/3138466.html 至于装什么版本,如果你有既有项目,最好和原先的版本一致.如果没有既有项目,建议用新版本. 解决方案二: oracl