Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DBMS_JOBS

Re: DBMS_JOBS

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 22 Nov 2006 07:49:18 -0800
Message-ID: <1164210558.211578.196490@b28g2000cwb.googlegroups.com>

Gowree ha escrito:

> Any idea why this auto job for every 10 minutes not working?
>
> SQL> SET SERVEROUTPUT ON
> SQL>
> SQL> Create or replace PROCEDURE DatabaseStatus is
> 2 v_job_number BINARY_INTEGER;
> 3 v_instance_number NUMBER(10);
> 4 v_database_status varchar2(20);
> 5 v_logins varchar2(20);
> 6 v_status varchar2(20);
> 7 v_hostName varchar2(20);
> 8 v_instanceName varchar2(20);
> 9
> 10 BEGIN
> 11
> 12 -- Get the instance number for use with DBMS_JOB.SUBMIT
> 13 SELECT
> instance_number,database_status,logins,status,host_name,instance_name
> 14 INTO
> v_instance_number,v_database_status,v_logins,v_status,v_hostName,v_instanceName
> 15 FROM v$instance;
> 16
> 17 DBMS_JOB.SUBMIT (JOB => v_job_number,
> 18 WHAT => 'job1;',
> 19 NEXT_DATE => TRUNC(SYSDATE+1/144,'MI'),
> 20 INTERVAL => 'trunc(sysdate+1/144,''MI'')',
> 21 NO_PARSE => TRUE,
> 22 INSTANCE => v_instance_number,
> 23 FORCE => NULL);
> 24 COMMIT;
> 25
> 26 DBMS_OUTPUT.PUT_LINE('The job number is:
> '||v_job_number);
> 27 DBMS_OUTPUT.PUT_LINE('The Database instance number is:
> '||v_instance_number);
> 28 DBMS_OUTPUT.PUT_LINE('The Database status:
> '||v_database_status);
> 29 DBMS_OUTPUT.PUT_LINE('The Database logins:
> '||v_logins);
> 30 DBMS_OUTPUT.PUT_LINE('Status is:
> '||v_status);
> 31 DBMS_OUTPUT.PUT_LINE('Host Name is:
> '||v_hostName);
> 32 DBMS_OUTPUT.PUT_LINE('Instance Name is:
> '||v_instanceName);
> 33
> 34 END;
> 35 /
>
> Procedure created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> Create or replace PROCEDURE job1 as
> 2 begin
> 3 insert into temp_Table values(sysdate,'Active');
> 4 commit;
> 5 end;
> 6 /
>
> Procedure created.
>
> SQL> exec DatabaseStatus;
> The job number is: 7
>
>
>
> The Database instance number is: 1
>
>
>
> The Database status: ACTIVE
>
>
>
> The Database logins: ALLOWED
>
>
>
> Status is: OPEN
>
>
>
>
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> spool off

What are the values for JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL?

Cheers.

Carlos. Received on Wed Nov 22 2006 - 09:49:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US