Home » RDBMS Server » Server Administration » Oracle 9i statspack not scheduling (merged 3)
Oracle 9i statspack not scheduling (merged 3) [message #387317] Wed, 18 February 2009 17:39 Go to next message
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
Re: Oracle 9i statspack not scheduling [message #387324 is a reply to message #387317] Wed, 18 February 2009 18:20 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: Oracle 9i statspack not scheduling [message #387325 is a reply to message #387317] Wed, 18 February 2009 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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.
Re: Oracle 9i statspack not scheduling [message #387326 is a reply to message #387324] Wed, 18 February 2009 18:25 Go to previous messageGo to next message
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
Re: Oracle 9i statspack not scheduling [message #387328 is a reply to message #387317] Wed, 18 February 2009 18:31 Go to previous messageGo to next message
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?


Re: Oracle 9i statspack not scheduling [message #387329 is a reply to message #387317] Wed, 18 February 2009 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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?
Re: Oracle 9i statspack not scheduling [message #387331 is a reply to message #387317] Wed, 18 February 2009 19:39 Go to previous messageGo to next message
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 ?
Re: Oracle 9i statspack not scheduling [message #387332 is a reply to message #387317] Wed, 18 February 2009 19:48 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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.
Re: Oracle 9i statspack not scheduling [message #387335 is a reply to message #387317] Wed, 18 February 2009 20:11 Go to previous messageGo to next message
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.
Re: Oracle 9i statspack not scheduling [message #387336 is a reply to message #387317] Wed, 18 February 2009 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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 Go to previous message
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.

Previous Topic: Redolog
Next Topic: transportable tablespace
Goto Forum:
  


Current Time: Tue Dec 06 08:42:05 CST 2016

Total time taken to generate the page: 0.07979 seconds