| Pause long running query / job (merged 3) [message #426240] |
Wed, 14 October 2009 09:45  |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Hi,
We have a large load occurring on our test system at the moment, which is impacting a different production system (same SAN) at a set time each night. Does anyone have any ideas on how to pause this load for a given set time each night? We can perform the load as an Oracle job if that makes things easier
Thanks
Firefly.
|
|
|
|
|
|
|
|
|
|
| Re: Pause long running query / job (merged 3) [message #426262 is a reply to message #426259] |
Wed, 14 October 2009 10:45  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The only ways of pausing the job that I can think of rely on uit having some form of loop in it.
You could write code to check for a specific value in a table once per loop, and if it doesn't find the value, then it waits 30 seconds and looks again. That way you could pause the job by deleting a value, and restart the job by re-inserting it.
Or, you could have your package use DBMS_LOCK to try to get a named lock once per loop, and then release the lock.
That way, if you get the lock from another session then the job will pause until it can get the lock.
|
|
|
|
| Re: Pause long running query / job (merged 3) [message #426263 is a reply to message #426261] |
Wed, 14 October 2009 10:45 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
One possible option would be to create a resource consumer group, and then limit the I/O of the user that is running the job on test in the times in question. It wouldn't "pause" the job, just limit it's I/O.
I haven't done that myself yet, but the documentation is here, and more detailed here.
|
|
|
|
|
|
| Re: Pause long running query / job (merged 3) [message #426420 is a reply to message #426267] |
Thu, 15 October 2009 06:14 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good point - if whatever you're pausing the job for does a lot of DML, then your chances of getting an ORA-01555 will go up a lot.
Although if the job that's being paused is just a data load, then this shouldn't be a problem.
Really, I think we need more details before we can go much further with this one.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
