ORA-31623： a job is not attached to this session via the specified handle

在使用Oracel Datapump API时碰到ORA-31623(a job is not attached to this session via the specified handle)错误，从故障描述来
看提示 job并没有成功附加到当前session指定的handle。该package的导入导出一直正常运行究竟是什么原因导致的呢？我们拭目以待......

1、故障环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

2、异常方式与异常现象
	当前package的使用情况是使用schema为goex_admin执行时正常，而使用goex_webuser执行时收到错误提示。
	即使用不同的用户来执行时存储过程异常.而在此之前类似的问题并不存在，即无论用哪个schema来实现datapump都成功执行。
	被调用的存储过程为:goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump  

-->下面是调用时出现的异常现象
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'TEST_EXP';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    DBMS_OUTPUT.Put_Line('HANDLE_OUT = ' || TO_CHAR(handle_out));
 17    DBMS_OUTPUT.Put_Line('ERR_NUM = ' || TO_CHAR(err_num));
 18    DBMS_OUTPUT.Put_Line('ERR_MSG = ' || err_msg);
 19  
 20    DBMS_OUTPUT.Put_Line('');
 21    COMMIT; 
 22  END; 
 23  /
DECLARE
*
ERROR at line 1:
ORA-20001: FNO_SYS_DATAPUMP_PKG.export_dump debugpos=210 (dumpname = TEST_EXP, -->这段信息给出了代码中错误所在的位置为210
handle = null)
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 738
ORA-06512: at line 15

3、分析异常
-->初步判断有可能是存在权限问题，但以前适用goex_webuser也一直正常，我们来看看代码中的210处位置
BEGIN
   -- Drop table  'Go_Data_Dumping_Job '
   debugpos    := 200;
   drop_db_table( tbl_in => c_db_dump_job_name );    -->此为同一package的过程用于删除已经存在的job
   debugpos    := 210;
   -- Create Datapump Job
   hand        :=                                    -->从210位置来判断，应该是job没有被open,然后就跳到exception的others部分
      DBMS_DATAPUMP.open( operation   => 'EXPORT'    -->由于给出的error 信息不够详细很难以发现到底问题出在哪里
                        , job_mode    => 'SCHEMA'
                        , job_name    => c_db_dump_job_name
                        , version     => 'LATEST' );
EXCEPTION
   WHEN DBMS_DATAPUMP.job_exists THEN
      debugpos    := 220;

      EXECUTE IMMEDIATE   'drop table '
                       || c_db_dump_job_name;

      debugpos    := 230;
      hand        :=
         DBMS_DATAPUMP.open( operation   => 'EXPORT'
                           , job_mode    => 'SCHEMA'
                           , job_name    => c_db_dump_job_name
                           , version     => 'LATEST' );
   WHEN OTHERS THEN
      raise_application_error( -20001,    myspname            
                                       || ' debugpos='       
                                       || TO_CHAR( debugpos )
                                       || ' (dumpname = '
                                       || dump_name_in
                                       || ', handle = '
                                       || NVL( TO_CHAR( hand ), 'null' )
                                       || ')' );
END;
-->修改EXCEPTION的WHEN OTHERS THEN部分，以便能抛出未知异常.使用下面的代码来替换(v_err_code,v_err_msg声明部分略)
   v_err_code  := SQLCODE;
   v_err_msg   := SUBSTR( SQLERRM, 1, 200 );
   DBMS_OUTPUT.put_line( 'Error code: '|| v_err_code );
   DBMS_OUTPUT.put_line( 'Error message: '|| v_err_msg );
     
SQL> show user;
USER is "GOEX_WEBUSER"
SQL> alter package goex_admin.FNO_SYS_DATAPUMP_PKG compile;

Package altered.

SQL> alter package goex_admin.FNO_SYS_DATAPUMP_PKG compile body;

Package body altered.

-->再次执行则ORA-31623错误出现,为故障的解决提供了线索
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'X';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle   
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> ho oerr ora 31623
31623, 00000, "a job is not attached to this session via the specified handle"
// *Cause:  An attempt to reference a job using a handle which is invalid or
//          no longer valid for the current session.
// *Action: Select a handle corresponding to a valid active job or start a
//          new job.

-->分析上面的ORA描述job不能附加到指定的handle.其Action是选择一个有效的handle或启动一个新的job.
-->Google了一下该ORA,有个关于这个问题的描述是由于当前schema(goex_webuser)没有创建表的权限.
-->于是按下面的方式授予goex_webuser建表权限,当时并没有检查该权限是否授予,而是直接为其授予.
SQL> conn sys / as sysdba
Enter password: 
Connected.
SQL> grant create any table to goex_webuser;

Grant succeeded.

SQL> conn goex_webuser/goex_webuser;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  err_num NUMBER;
  5    6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'X';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /

PL/SQL procedure successfully completed.   -->授予create any table权限之后存储过程成功执行了

4、故障再现
-->后续再次使用GOEX_WEBUSER执行时故障依旧
SQL> show user;
USER is "GOEX_WEBUSER"
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'N';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

5、进一步分析
-->检查alert log file发现下列提示信息,且不论是使用GOEX_WEBUSER还是使用GOEX_ADMIN都出现该提示
kupprdp: master process DM00 started with pid=28, OS id=6858
         to execute - SYS.KUPM$MCP.MAIN('Go_Data_Dumping_Job', 'GOEX_WEBUSER', 
             'KUPC$C_1_20120321131105', 'KUPC$S_1_20120321131105', 0);
kupprdp: worker process DW01 started with worker id=1, pid=29, OS id=6862
         to execute - SYS.KUPW$WORKER.MAIN('Go_Data_Dumping_Job', 'GOEX_WEBUSER');
Wed Mar 21 13:13:00 2012
The value (30) of MAXTRANS parameter ignored.
Wed Mar 21 13:13:10 2012
The value (30) of MAXTRANS parameter ignored. -->这个提示每执行一次就提示一次,对应上面执行语句时使用 “/”来再次执行PL/SQL
The value (30) of MAXTRANS parameter ignored.
The value (30) of MAXTRANS parameter ignored.

Wed Mar 21 14:13:46 2012
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=30, OS id=12040
         to execute - SYS.KUPM$MCP.MAIN('Go_Data_Dumping_Job', 'GOEX_ADMIN', 'KUPC$C_1_20120321141346',
              'KUPC$S_1_20120321141346', 0);
kupprdp: worker process DW01 started with worker id=1, pid=31, OS id=12042
         to execute - SYS.KUPW$WORKER.MAIN('Go_Data_Dumping_Job', 'GOEX_ADMIN');

-->关于从alert log file得到的错误信息，Metalink给出了BUG号为：6347775
-->此提示信息是由于在创建备份主表时使用了如下语句：
-->CREATE TABLE <table_name> (<columns_list>) INITRANS 10 MAXTRANS 30
-->由于Maxtrans参数在10gR1时已经废弃，所以在告警日志中出现提示，不影响数据的导入导出，仅仅是增大告警日志文件的大小
-->由此可知告警日志获得的信息无助于当前故障的解决
-->Metalink上[ID 308388.1]描述ORA-31623为由于流池和Java池导致该故障发生，然使用GOEX_ADMIN导出正常,因此排除这个原因.
                  
6、彻底解决
-->使用sys登陆到数据库,查看视图DBA_DATAPUMP_JOBS 

DBA_DATAPUMP_JOBS：
  Identifies all active Data Pump jobs in the database, regardless of their state, on an instance (or on all instances
for Real Application Clusters). It also show all Data Pump master tables not currently associated with an active job.

-->视图DBA_DATAPUMP_JOBS给出了当前活动JOB相关的一些信息

SQL> conn / as sysdba
Connected.

-->下面的查询发现有一个名为Go_Data_Dumping_Job的job_name且其owner为GOEX_WEBUSER
-->实际上该job_name对应于一个以job_name命名的临时表名
SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME
------------------------------ ------------------------------
GOEX_WEBUSER                   Go_Data_Dumping_Job

SQL> drop table GOEX_WEBUSER.Go_Data_Dumping_Job purge;
drop table GOEX_WEBUSER.Go_Data_Dumping_Job purge
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table GOEX_WEBUSER."Go_Data_Dumping_Job" purge;   -->删除该临时表名

Table dropped.

SQL> conn goex_webuser/goex_webuser;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> DECLARE                                   -->再次调用datapump存储过程被成功执行
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  handle_out NUMBER;
  4    5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := NULL;
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /

PL/SQL procedure successfully completed.

-->Author：Robinson Cheng
-->Blog: http://blog.csdn.net/robinson_0612

6、修正代码段
	 -->为原来的procedure添加如下代码段以生成schema的名字,以避免在删除由于job产生的表名时错误(v_schema应先声明)
	 SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) INTO v_schema FROM dual;
	 -->原来的schema能够导出自身的所有对象，且能够删除导出故障时生成的临时表，因此没有报错
   WHEN DBMS_DATAPUMP.job_exists THEN
            debugpos    := 220;
            EXECUTE IMMEDIATE   'drop table '
                             || v_schema
                             || '.'
                             || c_db_dump_job_name;		
   -->同理procedure drop_db_table也应当添加一个schema参数传入,此处不再演示
   
7、总结
a、在使用Oracle Datapump API时应注意如果定义了job_name则任意导致job失败或挂起的情形都将导致以job_name命名的table存在.
b、对于当前失败的job,再次执行时会碰到job(ORA-31634)已经存在的提示,此时应删除对应表名再使用原来的job_name再次实现Datapump.
c、对于出现的故障一般的建议是不要使用自定义的Job_name,由Oracle自动生成Job_name以自动销毁异常的job.
d、本文的例子得到的是ORA-31623错误而不是ORA-31634，貌似与ORA-31634 毫不相干，但删除job_name后一切正常.
e、在PL/SQL代码中,应尽可能的使用SQLCODE,SQLERRM来抛出不可预知的故障.

更多参考:

数据泵 EXPDP 导出工具的使用

数据泵 IMPDP 导入工具的使用

expdp impdp中 exclude/include 的使用

使用 Oracle Datapump API 实现数据导出

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

PL/SQL 联合数组与嵌套表