Re: Re: how to notify when dbms_job completes?

I didn't see the other email. But the dbms alert approach will work fine. Just name the alerts in some manner like alert_for_index_1, alert_for_index_2 and so on and so forth. From the monitoring process, which will listen to the alerts, you can specify when _all_ these alerts are raised and then kick off the dbms_stats.gather_table_stats.

By the way, I follow a similar approach for my datawarehouse home grown ETL scripts in 9.2 where I use the dbms_pcxutil package to analyze the partitioned indexes which use jobs. A simple pl/sql job continuosly polls the partition creation time and when done, kicks off the other tasks. I found it to be much simpler compared to the dbms alert or the dbms AQ approach.

HTH. Arup Nanda
www.proligence.com
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 13, 2003 5:17 PM

> I have several indice and several constraints to create and enable. So if
I run them in parallel they will complete faster. So I need some form of 'lock' so that my analyze command runs after all that is complete.
>
> With one dbms_alert by itself is fine, however, I need to wait for
multiple alerts. I think the loop I used in another email will do it. What do you think?
> >
> > From: "Arup Nanda" <orarup_at_hotmail.com>
> > Date: 2003/05/13 Tue PM 04:46:57 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: Re: how to notify when dbms_job completes?
> >
> > I guess my first question is why do you need dbms_job to create index in
> > parallel. If you merely want to create index and then analyze from a job
> > interface, why not place both the create index and analyze, one after
the
> > other in the same job?
> >
> > If you have a partitioned index and you are using DBMS_PCXUTIL package
which
> > kicks off jobs, then you have no control over modifying those jobs
anyway.
> >
> > If you must use the create index and analyze in seprate jobs, the best
> > option is to use dbms_alerts. Your create index jobs will register a
> > predefined alert at the very end. A session listening to the alerts will
> > collect all the alerts and after all are collected, will kick off the
job to
> > analyze. Another option is to use Advanced Queues (AQ) where messages
can be
> > passed by the create index processes and then listened to be the analyze
> > process.
> >
> > HTH.
> >
> > Arup Nanda
> > www.proligence.com
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, May 13, 2003 12:11 PM
> >
> >
> > > I want to create indices in parallel so I want to use dbms_job. then
when
> > that is complete I want to analyze the table.
> > >
> > > I thought about using dbms_lock and a pl/sql table, but this is
> > inefficient and wastes CPUs because there is no 'lock monitor' like the
> > wait,notify,synchronize in java and POSIX pthreads, so I have to keep
> > looping and checking the pl/sql table.
> > >
> > > any better way?
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: <rgaffuri_at_cox.net
> > > INET: rgaffuri_at_cox.net
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> > >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Arup Nanda
> > INET: orarup_at_hotmail.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).