Error in the execution of Job

[mrcaetano]

Hey guys !

I am submitting a job for immediate execution and with execution interval for every Saturday. The statement is as follows:

Select all

VARIABLE jobno NUMBER  
BEGIN  
DBMS_JOB.SUBMIT (:jobno,  
'dbms_stats.gather_schema_stats(ownname=> ''ARBOR'' , estimate_percent=> 10 , cascade=> TRUE );',  
sysdate,  
'trunc(next_day(trunc(sysdate),7))');  
COMMIT;  
END;  
/  

The Job above is not running because the errors are occurring:

Select all

ORA-12011: execution of 1 jobs failed  
ORA-06512: at "SYS.DBMS_IJOB", line 406  
ORA-06512: at "SYS.DBMS_JOB", line 272  
ORA-06512: at line 1 

How do I solve this problem ????

I am using Oracle 9.2.0

thanks,

Márcio.

: Cry:

[dr_gori]

Your problem is not in Job, but in what it is running ...

Try:

Select all

VARIABLE jobno NUMBER 
BEGIN 
  DBMS_JOB.SUBMIT (:jobno, 
    'dbms_stats.gather_schema_stats(''ARBOR'');', 
    sysdate, 
    'trunc(next_day(trunc(sysdate),7))'); 
 
  COMMIT; 
END; 
/  

[mrcaetano]

It did not work DRA_GORI ....

The job was successfully submitted but the execution of it gave the same error.

See the log below:

Select all

SQL> VARIABLE jobno NUMBER  
SQL> BEGIN  
  2    DBMS_JOB.SUBMIT (:jobno,  
  3      'dbms_stats.gather_schema_stats(''ARBOR'');',  
  4      sysdate,  
  5      'trunc(next_day(trunc(sysdate),7))');  
  6   
  7    COMMIT;  
  8  END;  
  9  /  
 
Procedimento PL/SQL concluído com sucesso. 
 
SQL> SELECT JOB,  
  2  SUBSTR(WHAT,1,35),  
  3  NEXT_DATE,  
  4  NEXT_SEC, 
  5  BROKEN, 
  6  FAILURES 
  7  FROM DBA_JOBS;  
 
       JOB SUBSTR(WHAT,1,35)                   NEXT_DAT NEXT_SEC B   FAILURES 
---------- ----------------------------------- -------- -------- - ---------- 
        43 dbms_stats.gather_schema_stats('ARB 14/12/04 15:58:34 N          1 
 
SQL> dbms_job.run(43); 
SP2-0734: início de comando desconhecido "dbms_job.r..." - restante da linha ignorado. 
SQL> exec dbms_job.run(43); 
BEGIN dbms_job.run(43); END; 
 
* 
ERRO na linha 1: 
ORA-12011: execution of 1 jobs failed 
ORA-06512: at "SYS.DBMS_IJOB", line 406 
ORA-06512: at "SYS.DBMS_JOB", line 272 
ORA-06512: at line 1 

[dr_gori]

Oops .... Sorry, I put it wrong !!!
The command must have Begin and End.

Thus:

Select all

VARIABLE jobno NUMBER 
SQL> BEGIN 
  2    DBMS_JOB.SUBMIT (:jobno, 
  3      'BEGIN dbms_stats.gather_schema_stats(''ARBOR''); END;', 
  4      sysdate, 
  5      'trunc(next_day(trunc(sysdate),7))'); 
  6  
  7    COMMIT; 
  8  END; 
  9  / 

[mrcaetano]

DR_GORI

Thanks for the help but it did not work .....
continues with the same error:

The job is successfully submitted But there is failure to run ....

Select all

ERRO na linha 1:  
ORA-12011: execution of 1 jobs failed  
ORA-06512: at "SYS.DBMS_IJOB", line 406  
ORA-06512: at "SYS.DBMS_JOB", line 272  
ORA-06512: at line 1

[dr_gori]

If you can rotate the dbms_stat manually, then I do not know what can be ... I created exactly this job here and everything ran ok.

Try to create something like this:

Select all

VARIABLE jobno NUMBER 
BEGIN 
   DBMS_JOB.SUBMIT (:jobno, 
     'NULL;', 
     sysdate, 
     'trunc(next_day(trunc(sysdate),7))'); 
    COMMIT; 
 END; 
/ 

Turn this SQL and shows how the command was:

Select all

SELECT JOB,  SUBSTR(WHAT,1,35) from dba_jobs