Hi Tom,
Well i executed that thing and it worked fine.
Thanks for the reply !!
Bhaskar

Tom, are there any risks with just passing in a simple statement?  

SQL> begin
  2  dbms_job.isubmit(40,'delete from ORDER_SUB where end_date <= sysdate;',
  3  trunc(sysdate),'TRUNC(SYSDATE+1)');
  4  end;
  5  /

It will commit?
It will break appropriately? 

Hi Tom,

I have a procedure sp_testproc which takes 2 input parameters. In order to execute the procedure I will do the foll

exec sp_testproc(input1, input2);

Now I am running this manually in my SQL* Plus sessions. Is it possible for me to just automate this exec proc?

Thanks.

Hi Tom,
I am calling stored procedures (bundled in a package) with different arguments (where procedure is same but argument is different) through a scheduling tool.I want to execute these procedures in parallel. Can I do this?
Call main procedure..
which in turn calls p(1) execute, p(2) execute....
If all successful then main is succussful. The idea is to execute p(1)... in parallel, but controlling through p(main)
Thanks

Hi Tom,
You are right that scheduling tool does all this. But when I was monitoring the job in unix, I found that processes are running but not doing much, and CPUs are idle 50% of the time. At that time, I thought to run straight from oracle procedure eliminating any other software in between. But on manually running outside, I found the same problem. What I am doing:
p(a) reads from temp table for a state and product loads data in target
p(b) does same thing with different state and other product
.......
Could it be bottleneck becuase I am inserting in the same target???
I thought that I can issue multiple inserts into the same target in Oracle efficiently or I am missing something?
I also tested that when I run procedures serially or in parralel, the loading time is almost same. Will partitioning in target table with state/product will help??? Is it locking issue etc???
Thanks

Thanks Tom.
Can you mention the specific Chapter(s) of your or Cary's books in this context?
Regards

Hi Tom,
I had a look at chapter 10 of your book, and Cary's book. I don't think it will be worth spending time on Cary's book to tune for the sake of saving few hours, as the Job is once a month, and takes 10 hours in all.
btw: For smaller set of products, the result is very quick. Does this give some indication to look for low hanging fruit?
Thanks

Hi Tom,
Thanks a lot. I fully agree with you that knowledge acquired will never be wasted (and also I admire your help and knowledge too).
The problem here is to deliver something also, which I think I won't be able to deliver if I start reading Cary's book (1...infinity). I will defintely read it (thanks for showing me the light), but unfortunately I have to weigh between either to delay the deliverables by making it in the best way, or deliver something as per target, and then re-visit. The reason I put here because I thought I found the problem and can easily be fixed with your magic help. The findings were:
When I run procedures in parallel
-- Parallel process A
exec P(for state S1, product p1)
product p1 (about couple of thousand rows) -- few minutes
exec P(for state S1, product p2)
product p2 (couple of million rows) -- about 2.5 hours
-- Parallel process B
exec P(for state S2, product p1)
product p1 (about couple of thousand rows) -- few minutes
exec P(for state S2, product p2)
product p2 (couple of million rows; more than s1,p2) -- about 4 hours
-- Parallel process C
exec P(for state S3, product p1)
product p1 (about couple of thousand rows) -- few minutes
exec P(for state S3, product p2)
product p2 (couple of miilion rows; more than s1, p2 and < s2,p2) -- about 3.5 hours
________________
Then, I tried to exec in parallel
p(s1,p2), p(s2,p2) and p(s3,p2), I thought if I am able to utilise parallel processing, it should not take T(s1,p2)+T(s2,p2)+T(s3,p3), but the whole process took around same time - around 10 hours!, and When I was monitoring through vmstat, I found that most of the time running processes were 1 (no blocked, no paging), cpu's 50% idle etc..
The oracle version is 9.2 on AIX 5.2L
Thanks

Hi Tom,
Thanks for your help.
I will try based upon chapter 10.
Regards

Hi Tom,

There is a task which should begin after commit & based on the keys that are updated.

My plan is to implement this using :
1. Create on update trigger
2. Create job in ON UPDATE TRIGGER using dynamic SQL and schedule it to run after say 15 seconds.
3. Delete that job( where ? ? still not clear)

Is there any better way of doing this? ?

Hi Tom,

I have implemented the same thing using autonomous transaction in triggers and things are working now.

Thanks for your time

I refered to metalink Note:208599.1 and followed the same :

Trigger :
create or replace trigger trig_rw_server_queue after update on rw_server_queue for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_seq_id number;
BEGIN
if :new.status_code = 3 then
v_seq_id := :new.rep_run_id;
p_run_job (v_seq_id);
end if;
END;
/

Procedure :
Create or replace procedure
p_run_job(p_report_id Number) as
Jobid Number := 0;
ProcNm Varchar2(200) := 'pkg_report.UPDATE_STATUS('||p_report_id||')';
Begin
ProcNm := 'Begin '||ProcNm||'; End;';
Dbms_job.Submit(Jobid,ProcNm,Sysdate+1/1440,null);
Commit;
End;
/

And the package.procedure

PROCEDURE UPDATE_STATUS (P_REP_KEY NUMBER) IS
v_job_Id SRW_ONE.Job_Ident;
V_rep_Status SRW_ONE.Status_Record;
P_JOB_ID NUMBER;
P_MESSAGE VARCHAR2(2000);

BEGIN
COMMIT;

BEGIN
SELECT job_id
INTO P_JOB_ID
FROM RW_SERVER_QUEUE
WHERE REP_RUN_ID = P_REP_KEY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_MESSAGE := P_REP_KEY||SQLERRM;
RAISE_APPLICATION_ERROR (-20002, sqlerrm);
insert into rw_server_queue ( REP_RUN_ID, JOB_ID, status_text )
values ( P_REP_KEY ,v_job_id.jobid, SUBSTR(P_MESSAGE,1,100));
commit;
END;

v_job_Id.JobID := P_JOB_ID;


SRW_ONE.DEBUGGING := TRUE;
-- myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));

v_job_Id.ServerName := F_MIC_LOOKUP('SERV_PARA','SERVER');
v_job_Id.GatewayURL := F_MIC_LOOKUP('SERV_PARA','GATEWAY');

V_rep_Status := SRW_ONE.Report_Status(v_job_Id);

UPDATE RW_SERVER_QUEUE
SET status_code = nvl(V_rep_Status.StatusCode,88),
status_text = nvl(V_rep_Status.StatusText,'null')
WHERE rep_run_id = p_rep_key;

dbms_output.put_line(V_rep_Status.JobIdent.JobID);
dbms_output.put_line(V_rep_Status.JobIdent.ServerName);
dbms_output.put_line(V_rep_Status.JobType);
dbms_output.put_line(V_rep_Status.OutputType);
dbms_output.put_line(V_rep_Status.OutputName);
dbms_output.put_line(V_rep_Status.QueuedAt);
dbms_output.put_line(V_rep_Status.StartedAt);
dbms_output.put_line(V_rep_Status.FinishedAt);
dbms_output.put_line(V_rep_Status.ParentJob);
exception when others then
RAISE_APPLICATION_ERROR (-20001, sqlerrm);
END;

The whole story behind this stuff is:
We are calling report using event based report.. from database which runs in background and creates file. After we create file, we need to send those file to users using unix 'uuencode' command and insert into blob. (we are using uuencode because of some security reasons otherwise we would have used rwservlet to send the mails directly from report server). So, in the after report trigger, we are updating the report status... since the status will be 'running report in afterreport trigger' we need something which will do an update after executing that report trigger. So, we have created the above job which will do an update after the job is finished (either successful or failure).

Thanks Tom... I have removed Autonomous Transaction part from trigger now... I have implemented code like yours.

Hi Tom,
I have scheduled some procedures to run at 02.45 AM, but, now I want to reschedule to run at different times, so I am giving the following command
dbms_job.change(jobid, null, trunc(sysdate)+1+1/24, 'trunc(sysdate)+1+1/24');
(To run daily at 01.00 AM)And, similarly, I have changed for other jobs also.
and when I query dba_jobs, I am able to see the changed time, but, the job runs only at 02.45 AM everyday. What could be the problem?

Please check the last_date, this_date....
JOB LAST_DATE THIS_DATE NEXT_DAT INTERVAL
45 2/8/2006 2:44:04 AM 2/9/2006 2:44:04 AM SYSDATE + 1
46 2/8/2006 2:44:04 AM 2/9/2006 2:44:04 AM SYSDATE + 1
44 2/8/2006 2:44:04 AM 2/9/2006 2:44:04 AM SYSDATE + 1
62 2/8/2006 2:44:04 AM 2/9/2006 2:44:04 AM SYSDATE + 1
61 2/8/2006 2:44:04 AM 2/9/2006 2:44:04 AM SYSDATE + 1

I would like to change the next_date to different timings

Regards,
Ram

it would appear to me, that you did not run the dbms_job.change (and that the interval was not correct in the past - it was sysdate+1 which would cause the job to slide over time)


if you do something like this:


ops$tkyte@ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
 
Session altered.
 
ops$tkyte@ORA10GR2> exec dbms_job.submit( :n, 'null;', trunc(sysdate)+1+1/24*2+1/24/60*45, 'trunc(sysdate)+1+1/24*2+1/24/60*45' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> commit;
 
Commit complete.
 
ops$tkyte@ORA10GR2> select last_date, this_date, next_date, interval from user_jobs;
 
LAST_DATE            THIS_DATE            NEXT_DATE            INTERVAL
-------------------- -------------------- -------------------- ---------------------
                                          11-feb-2006 02:45:00 trunc(sysdate)+1+1/24
                                                               *2+1/24/60*45
 
 
ops$tkyte@ORA10GR2> exec dbms_job.change(:n, null, trunc(sysdate)+1+1/24, 'trunc(sysdate)+1+1/24');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> commit;
 
Commit complete.
 
ops$tkyte@ORA10GR2> select last_date, this_date, next_date, interval from user_jobs;
 
LAST_DATE            THIS_DATE            NEXT_DATE            INTERVAL
-------------------- -------------------- -------------------- ---------------------
                                          11-feb-2006 01:00:00 trunc(sysdate)+1+1/24



what do you see (and please, make sure columns line up - I cannot tell what fields are which) 

Thanks, it is working now. I probably had forgotten to commit

Hi,
I want to schedule stats gathering to every Saturday. I have created a job like this:
 var jobno number;
 begin
  dbms_job.submit(:jobno, 'sys.dbms_stats.gather_schema_stats( ownname=>''SCOTT'', cascade=>TRUE);',next_day(trunc(sysdate),'SATURDAY')+22/24, 'next_day(trunc(sysdate),''SATURDAY'')+22/24');
 commit;
 end;

But, I am getting the error 
Errors in file /ford/app/oracle/admin/FLEETDV/bdump/fleetdv_j000_7835.trc:
ORA-12012: error on auto execute of job 3
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
and in the trace file
ORA-12012: error on auto execute of job 3
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
I have 'Analyze Any' privilege granted through a role.
Also, I am gathering statistics of another schema (SCOTT) through the job. I am able to execute this procedure directly
SQL> execute dbms_stats.gather_schema_stats( ownname=>'SCOTT', cascade=>TRUE);

PL/SQL procedure successfully completed.

What could be wrong ? 

Hi,
I want to schedule stats gathering to every Saturday. I have created a job like this:
 var jobno number;
 begin
  dbms_job.submit(:jobno, 'sys.dbms_stats.gather_schema_stats( ownname=>''SCOTT'', cascade=>TRUE);',next_day(trunc(sysdate),'SATURDAY')+22/24, 'next_day(trunc(sysdate),''SATURDAY'')+22/24');
 commit;
 end;

But, I am getting the error 
Errors in file /ford/app/oracle/admin/FLEETDV/bdump/fleetdv_j000_7835.trc:
ORA-12012: error on auto execute of job 3
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
and in the trace file
ORA-12012: error on auto execute of job 3
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
I have 'Analyze Any' privilege granted through a role.
Also, I am gathering statistics of another schema (SCOTT) through the job. I am able to execute this procedure directly
SQL> execute dbms_stats.gather_schema_stats( ownname=>'SCOTT', cascade=>TRUE);

PL/SQL procedure successfully completed.

What could be wrong ?