OraFAQ Forum: Server Administration » Oracle 9i statspack not scheduling (merged 3)

Home » RDBMS Server » Server Administration » Oracle 9i statspack not scheduling (merged 3)

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

Oracle 9i statspack not scheduling (merged 3) [message #387317]

Wed, 18 February 2009 17:39

jrmtl
Messages: 27
Registered: February 2006

Junior Member

Hi all,

I have an oracle 9.2.0.8 db where statspack is installed and had the spauto.sql script schedule to run every hour.
I removed the schedule with dbms_job.remove and recreated for every 30 min,
now i see that the job does not run anymore and even when I put back the original hourly job ?

I do see the job through dba_jobs

any ideas?
Thx

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387324 is a reply to message #387317]

Wed, 18 February 2009 18:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do not cross/multi-post

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387325 is a reply to message #387317]

Wed, 18 February 2009 18:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Here is what we know.
1) the DB was OK.
2) You made a change, but we don't know exactly what was changed.
3) Now you report 1 (or more?) DBMS_JOB entry "does not run anymore"
4) you provide no meaningful details.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

Both my crystal ball & OUIJA board are broken, so I can't provide any solution.

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387326 is a reply to message #387324]

Wed, 18 February 2009 18:25

jrmtl
Messages: 27
Registered: February 2006

Junior Member

Sorry about the posts ... My screen froze up and I clicked too fast on the POST Sad

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387328 is a reply to message #387317]

Wed, 18 February 2009 18:31

jrmtl
Messages: 27
Registered: February 2006

Junior Member

Thanks for the humor Smile

What I did was add another job with spauto.sql with a different scheduled interval ( 30 mins ) along with the original.

Now two jobs existed,, so I deleted the hourly and kept the 30 min one. When I noticed it wasn't running I deleted that one also.
Then, I just cleaned them all with exec dbms_jobs.remove( process)

I reran original spauto.sql and I see the job in dbms_jobs but it's taking a snapshot at those given times when I check with spreport?

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387329 is a reply to message #387317]

Wed, 18 February 2009 18:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The Posting Guidelines clearly state -
Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session),
including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!

What do you observe within the DB from a separate session?

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387331 is a reply to message #387317]

Wed, 18 February 2009 19:39

jrmtl
Messages: 27
Registered: February 2006

Junior Member

This is what i did and it worked.
SQL> @spauto.sql;

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
----------
338

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
job_queue_processes integer 3

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- --------- --------
338 18-FEB-09 21:04:00

SQL>
==============
But come run time the job doesn't execute ?

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387332 is a reply to message #387317]

Wed, 18 February 2009 19:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

When I post a response, you really should actually do ALL as requested.

What do you observe within the DB from a separate session?
What do you observe within the DB from a separate session?
What do you observe within the DB from a separate session?
What do you observe within the DB from a separate session?

You somewhat I confirmed what I suspected.

Do you understand how Oracle data integrity operates?

Changes are only "visible" to other sessions after a COMMIT!!!!!! (changes like a "new" job available to background session to run it)

Unless/until you issue COMMIT, the "new" job is only visible to YOUR current process.

Unless/until you issue COMMIT, it will NOT be scheduled to run.

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387335 is a reply to message #387317]

Wed, 18 February 2009 20:11

jrmtl
Messages: 27
Registered: February 2006

Junior Member

I am looking at it from two diffrent sessions.
And YES i see it from that other session.

I know I was probably not clear but don't jump to conclusions either buddy.

Report message to a moderator

Re: Oracle 9i statspack not scheduling [message #387336 is a reply to message #387317]

Wed, 18 February 2009 20:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am looking at it from two diffrent sessions.
Please explain how this is true when you do NOT show COMMIT in previously posted session.

from sqlplus post results in a whole new session::

set term on echo on time on
column next format a20
column interval format a24
column what format a32
select to_char(next_date,'YYYY-MM-DD:HH24:MI') next, interval, what, broken
from dba_jobs
/

[Updated on: Wed, 18 February 2009 20:47]

Report message to a moderator

Re: Oracle 9i statspack not scheduling (merged 3) [message #387485 is a reply to message #387317]

Thu, 19 February 2009 07:58

jrmtl
Messages: 27
Registered: February 2006

Junior Member

It's working now at 30 min intervals... thanks for your help!
I apologize for not being clearer.

I think the "commit" did it rather exiting from the session.

Report message to a moderator

Previous Topic:	Redolog
Next Topic:	transportable tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 05:55:44 CDT 2024