Oracle Replication Tips by Burleson

Creating a Oracle Job

In both one-way and updatable materialized view replication, the required jobs are automatically created.  However, creating a job is relatively easy.  The code below will create a job that executes a STATSPACK snapshot every 5 min.

DECLARE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(job => jobno,
                 what => ‘statspack.snap',
                 next_date => SYSDATE,
                 interval => '/*5:Mins*/ sysdate + 5/(60*24)'); END;
/ 

Note that dbms_job.submit returns the job number, so you will need to define a variable to catch it.  You can do the same thing in Oracle Enterprise Manager.  Navigate to the Distributed, Advanced Replication, Administration tab and select the DBMS Jobs tab at the top to display any jobs already defined.  Select the NEW button at the bottom to define a new job.

Figure 5.1 The Create Job Screen

Define the Next Date, Interval, and a PL/SQL procedure, function, or text to execute and select OK. 

Deleting a Job

To delete a job, you need to know the job number and will need to be logged in as the user who created the job.

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.