OraFAQ Forum: Server Administration » DBMS

Home » RDBMS Server » Server Administration » DBMS_JOB restarting (oracle 10g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_JOB restarting [message #470141]

Sun, 08 August 2010 19:26

sahadba
Messages: 59
Registered: September 2009
Location: Pune

Member

Hello Experts,

My Apologies for duplicating my Post...

The Issue is as below: -
Hello Experts,

Apologies for Duplicating the message....
Please look into my problem,

I was monitoring a database job to collect statistics, it was scheduled using DBMS_JOBS...
I found that it was running during business hours so i got the session ID of the job using;

select sid from dba_jobs_running where job=11;

I then i killed the job using;

select serial# from v$session where sid =232;
alter system kill session '232, 10852';
select sid from dba_jobs_running where job=11;

no rows selected

After some time i again fired the same command

select sid from dba_jobs_running where job=11;

       SID
----------
       232

and found that the same job is again running..

This behavior was repeated again N again. i have attached the spool file for the same...

Please let me know what could be the reason that the job is starting all over again even after killing the session and what should be done to stop it..

I understand that once the database shuts down and if the job is still running then it will restart once the database is up..
In this case, Should i remove the job and re submit it again..

Thank You

Attachment: Statistics_Job.txt
(Size: 4.95KB, Downloaded 1486 times)

Report message to a moderator

Re: DBMS_JOB restarting [message #470143 is a reply to message #470141]

Sun, 08 August 2010 19:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select job_name from dba_scheduler_jobs order by 1;

post results from SQL above

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: DBMS_JOB restarting [message #470387 is a reply to message #470143]

Mon, 09 August 2010 13:40

sahadba
Messages: 59
Registered: September 2009
Location: Pune

Member

Thank You BlackSwan,

As you had instructed;

SQL> select job_name from dba_scheduler_jobs order by 1;

JOB_NAME
------------------------------
AUTO_SPACE_ADVISOR_JOB
FGR$AUTOPURGE_JOB
GATHER_STATS_JOB
MGMT_CONFIG_JOB
MGMT_STATS_CONFIG_JOB
PURGE_LOG

6 rows selected.

Report message to a moderator

Re: DBMS_JOB restarting [message #470388 is a reply to message #470387]

Mon, 09 August 2010 13:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

By default in V10+ Oracle collects statistics once a day via SCHEDULER job.
notice "GATHER_STATS_JOB" above
So why are you duplicating this effort with DBA_JOBS task of your own?

Report message to a moderator

Re: DBMS_JOB restarting [message #470390 is a reply to message #470388]

Mon, 09 August 2010 14:12

sahadba
Messages: 59
Registered: September 2009
Location: Pune

Member

It is a Uniform process set for all the databases in one of our Client Environments..
This JOB runs for all the databases successfully...

Job Name : - BERKE_ANALYZE_TABLES

If Oracle collects statistics once a day, will the last_analyzed reflect a change daily for all tables??

Report message to a moderator

Re: DBMS_JOB restarting [message #470391 is a reply to message #470390]

Mon, 09 August 2010 14:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> will the last_analyzed reflect a change daily for all tables??
NO
Oracle is smart & does not waste resources.
If a table's content does NOT change, Oracle does not bother to "collect new" statistics; which will be identical to old/current statistics.
When data remains the same, statistics remain the same.
New statistics are gathered only after about 10% of data has changed.

Report message to a moderator

Re: DBMS_JOB restarting [message #470392 is a reply to message #470391]

Mon, 09 August 2010 14:25

sahadba
Messages: 59
Registered: September 2009
Location: Pune

Member

Thank You BlackSwan,

I think i will suggest this to my Client..

But what should i do about my current issue, why does the job start again N again even after killing the session??
As i had inquired, should i remove and re submit if required??

Report message to a moderator

Re: DBMS_JOB restarting [message #470395 is a reply to message #470392]

Mon, 09 August 2010 14:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>why does the job start again N again even after killing the session??
When the DBMS_JOB codes gets invoked & needs to decide if any job needs to be started.
It compares the "NEXT" time for this job & concludes it is past due to be run, so it starts it.
the code does not know or care, what transpired in the past.
It just runs the job because it was directed to run the job since it knows the job still needs to be run.

Report message to a moderator

Re: DBMS_JOB restarting [message #470402 is a reply to message #470395]

Mon, 09 August 2010 14:58

sahadba
Messages: 59
Registered: September 2009
Location: Pune

Member

I have re scheduled the job to run at 13/08/2010 00:00 hrs and killed the current session..

SQL> select job, next_date, interval, what from dba_jobs where job=11;

       JOB NEXT_DATE            INTERVAL             WHAT
---------- -------------------- -------------------- --------------------------------------------------
        11 13.08.2010-00:00:00  TRUNC(SYSDATE)+7     berka_analyze_tables;

SQL> select sid from dba_jobs_running where job=11;

       SID
----------
       311

SQL> select serial# from v$session where sid=311;

   SERIAL#
----------
       241

SQL> alter system kill session '311, 241';

System altered.

SQL> select sid from dba_jobs_running where job=11;

no rows selected

Will discuss about the necessity of this job with Client..

Thank You BlanSwan for the support Smile

Report message to a moderator

Previous Topic:	Which way can I choose?
Next Topic:	Oracle XE problem. Can't create user and ORA-01034: ORACLE not available.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 22:06:13 CDT 2024