Oracle job scheduling

In my project, the requirement is inserting records into main database from child databases.

I have created one Job in Oracle. It automatically inserting record which is not yet inserted into main database.

The status of each record which is not yet inserted or already inserted is maintained in one table called “update status” .

If “updaterecord” column is “True” then that record is not yet inserted in main database.

Otherwise that record is already inserted.

I have created one procedure which will checks and inserting the record. Created one Job (Oracle scheduler) and set the interval as 1 minute. That’s it. This job will be executed the procedure for every minute.

Step I :

The following is the procedure for inserting pending registrations of main database

CREATE OR REPLACE PROCEDURE updatejobproc IS

CURSOR x_cur IS

SELECT registrationno FROM abc.updatestatus@Child_Database WHERE updaterecord ='true';

BEGIN

FOR x_rec IN x_cur

LOOP

Insert into abc.contactdetails@Main_Database(aliasname,password,

hintquestion,hintanswer)

select userid,password,hintquestion,hintanswer from contactinformation@Child_Database where

registrationno=x_rec.registrationno;

update abc.updatestatus@Child_Database set updaterecord='false' where registrationno=x_rec.registrationno;

END LOOP;

COMMIT;

END updatejobproc;

/

Steps included in above procedure:

1) Getting all the registration numbers from child database which has updaterecord = true;

2) For each registration number from child database, corresponding record will be inserted into main database

3) Updating updaterecord flag in child database to “FALSE”

Step II:

Compile and run the procedure.

Step III:

Created one Job internally in child database to insert the record which has “updaterecord “ flag is “TRUE” for every minute.

SQL> declare 

2 job integer; 

3 begin 

4 dbms_job.submit(job, ‘updatejobproc;',sysdate,'sysdate+1/1440');

5 commit; 

6 end; 

7 /

Step IV:

Execute following queries.

1)ALTER SYSTEM DISABLE RESTRICTED SESSION;

2)ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10;