1，在执行expdp的时候，报错ORA-31637

在plsq界面，启动sql导入的方式是可行的，但是速度非常慢，同事以及客户着急这部分数据导入，尝试去服务器上面执行expdp导出，但是报错ora-31626了@H_301_6@

[oracle@orct~]$ time expdp 'dw/"pw1817"'@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=dw_20170622.dmp tables=ads_pd_zjsals_proj_m,ads_pd_sjyswc_proj_m LOGFILE=zhanghaoxiang.log;

Export: Release 11.2.0.4.0 - Production on Thu Jun 22 17:01:39 2017

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
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user DW
ORA-06512: at "SYS.DBMS_SYS_ERROR",line 95
ORA-06512: at "SYS.KUPV$FT",line 1587
ORA-39062: error creating master process DM00
ORA-39105: Master process DM00 Failed during startup. Master error:
ORA-01427: single-row subquery returns more than one row



real    0m1.784s
user    0m0.011s
sys 0m0.014s
[oracle@orct~]$ 
[oracle@orct~]$

@H_301_6@

2，赋予DBA权限，尽快导出数据

google了下，查到是权限不足的问题，尝试赋予dba权限，试试看
[oracle@orct~]$ rlwrap sqlplus / as sysdba

sql*Plus: Release 11.2.0.4.0 Production on Thu Jun 22 17:02:27 2017

Copyright (c) 1982,2013,Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining and Real Application Testing options

sql> grant dba to dw;

Grant succeeded.

sql> exit
ERROR:
ORA-04088: error during execution of trigger 'TIMDBA.TRIG_logoFF_AUDIT'
ORA-01403: no data found
ORA-06512: at line 5


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining and Real Application Testing options (with complications)
[oracle@orct~]$ time expdp 'dw/"pw1817"'@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=dw_20170622.dmp tables=ads_pd_zjsals_proj_m,ads_pd_sjyswc_proj_m LOGFILE=zhanghaoxiang.log;

Export: Release 11.2.0.4.0 - Production on Thu Jun 22 17:02:37 2017

Copyright (c) 1982,Data Mining and Real Application Testing options
Starting "DW"."SYS_EXPORT_TABLE_01":  dw/********@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=dw_20170622.dmp tables=ads_pd_zjsals_proj_m,ads_pd_sjyswc_proj_m LOGFILE=zhanghaoxiang.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
>>> DBMS_AW_EXP: BIN$TAsGFYCiGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCkGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCmGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCoGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCqGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCsGqfgUwwD+woJNg==$0 not AW$
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
>>> DBMS_AW_EXP: BIN$TAsGFYCiGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCkGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCmGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCoGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCqGqfgUwwD+woJNg==$0 not AW$
>>> DBMS_AW_EXP: BIN$TAsGFYCsGqfgUwwD+woJNg==$0 not AW$
. . exported "DW"."ADS_PD_ZJSALS_PROJ_M" 3.654 MB   39695 rows
. . exported "DW"."ADS_PD_SJYSWC_PROJ_M" 931.3 KB   10983 rows
Master table "DW"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DW.SYS_EXPORT_TABLE_01 is:
  /oracle/expdir_tmp/dw_20170622.dmp
Job "DW"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jun 22 17:02:42 2017 elapsed 0 00:00:04


real    0m5.643s
user    0m0.019s
sys 0m0.014s
[oracle@orct~]$ 
[oracle@orct~]$

@H_301_6@

blog来源地址：http://www.jb51.cc/article/p-agrbrkdr-bav.html，博主黄杉，谢绝转载。@H_301_6@

3，收回DBA权限

[oracle@orct~]$ rlwrap sqlplus / as sysdba@H_301_6@

sql*Plus: Release 11.2.0.4.0 Production on Thu Jun 22 17:02:46 2017@H_301_6@

Copyright (c) 1982,Oracle. All rights reserved.@H_301_6@

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining and Real Application Testing options@H_301_6@

sql> revoke dba from dw;@H_301_6@

Revoke succeeded.@H_301_6@

sql> @H_301_6@

@H_301_6@

4，比较完善的办法

查看官方文档http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm
@H_301_6@

里面有关于expdp的权限问题描述，需要DATAPUMP_EXP_FULL_DATABASE、DATAPUMP_IMP_FULL_DATABASE操作权限，一会可以采取赋予以下权限给普通用户，这样普通用户也可以通过expdp/impdp来导出导入数据了，如下所示：@H_301_6@

sql> grant DATAPUMP_EXP_FULL_DATABASE  to dw;

Grant succeeded.

sql> 
sql> grant DATAPUMP_IMP_FULL_DATABASE   to dw;

Grant succeeded.

sql>