Oracle Replication Tips by Burleson

Creating a Oracle Job

  DBMS_JOB.REMOVE(job => 1);
END;
/

This will remove a job with a job identifier of 1. You can extract the job identifier from the dba_jobs view, which we will discuss later in this chapter.  If you receive an error that the job is not in the job queue, you are not logged on as the job’s creator or the job does not exist. 

Changing a Job

If you want to change a table that is refreshing every 5 minutes to refresh every 15 minutes, adjust the interval in the supporting job.

BEGIN
  DBMS_JOB.CHANGE(job => 1,
   next_date => SYSDATE,
   interval => '/*15:Mins*/ sysdate +
                15/(60*24)',
   what => NULL);
END;
/ 

By changing the next_date to SYSDATE, the job will execute immediately, and then every 15 minutes after that. Setting the WHAT field to null in the above example will cause the field to retain its current value.

Stopping a Job

Stopping a job is also easy.  Each job has a field called BROKEN.  If this field is true, the job is broken and will not execute until it is unbroken.

BEGIN

This is an excerpt from Oracle Replication By Rampant TechPress (only $19.95).  You can click here to order a copy and get instant access to the code depot:

http://www.rampant-books.com/book_2003_2_replication.htm

Get a Personal Oracle Replication Mentor

The author is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle replication question. 

You can get me personally, or any Oracle Certified replication DBA with more than 20 years of full-time IT experience.