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

Issue:
Few Jobs are failing

Error Message:
Errors in file /orahome/oracle/product/10.2.0/admin/orcl/bdump/orcl1_j001_2694.trc:
ORA-12012: error on auto execute of job 716137
ORA-31623: a job is not attached to this session via the specified handle

Findings:
All these jobs are found to be scheduled to run at the same time (4AM daily)

The problem in running these jobs all at the same time is due to the fact that all these jobs are using the same Table 'DAYJOB'
So, each job tries to create a new table 'DAYJOB' and hence each job fails.

Solution:
1.) Schedule these jobs at different interval of time. Make sure that none of the jobs overlap with each other.
or
2.) Use a different table name for each of the jobs (DAYJOB1,DAYJOB2...etc).
     Please check with the application team the impact of using a different table name for each job. 

How to backup and restore statistics using dbms_stats

1)Check last analyzed date of scott schema tables
ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04


2)Create stat table in users tablespace
ORCL>>exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'stats_bkp_scott', tblspace => 'USERS');
PL/SQL procedure successfully completed.


3)Take the statistics backup of scott schema in stat table
ORCL>>exec dbms_stats.export_schema_stats(ownname => 'SCOTT', stattab => 'stats_bkp_scott');
PL/SQL procedure successfully completed.


4)Take the export backup of scott schema or the owner of stats table
$ exp scott/scott1 file=scott_stat_bkp_09122010.dmp tables=scott.STATS_BKP_SCOTT
Export: Release 10.2.0.4.0 - Production on Thu Dec 9 14:37:42 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                STATS_BKP_SCOTT         24 rows exported
Export terminated successfully without warnings.


5)Gather the statistics
14:41:39 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'DEPT', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14

14:41:51 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'EMP', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.

14:42:58 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'BONUS', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.

14:43:19 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'SALGRADE', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.


6)Check the last analyzed date of tables
14:43:41 ORCL>> select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-DEC-2010 02:42:27
DEPT                           09-DEC-2010 02:41:50


7)Import/Revert the statistics of one/two table from the backup
15:07:22 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'emp', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30

15:10:28 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'dept', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26


8)Check the last analyzed date of the tables
15:120:31 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.54


9)Revert the statistics of whole schema from the backup
15:40:38 ORCL>>EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS ('SCOTT','stats_bkp_scott');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.34


10)Check the last analyzed date of the tables
15:45:53 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.27