SQL SCRIPT FOR UPDATING SNAPSHOTS EVERY 2 HRS

[mesheena]

Hello,
i am new to sql scripting....i am working with a database related software and i need to execute a certain set of statements in oracle after every 2 hrs......i have written the statements in a .sql file and when i run the file using START command, it runs fine...but how do i automatically run the file after every 2 hrs....working on a solaris workstation.....
your help would be appreciated

 

[carp]

Mesheena -
There are a couple of ways to do this:
1. Write a stored procedure that does what your script is doing, then schedule it via the DBMS_JOB package.

2. Schedule the script to run via the Unix chron command.

3. If you're using Oracle Apps, set your script up as a concurrent program and schedule it as a concurrent request.

 

[mesheena]

thanks for the prompt reply......but it would be more clear to me if we take an example...lets suppose we want to run this command after every 2 hrs....just an example:
select * from users
i put it in a file named try.sql and run it using START command.....now i want to run this file try.sql automatically after every 2 hr.?how do i do it via DBMS_JOB package ot the unix cron command as u mentioned....
could u elaborate

 

[kjnorman]

To illustrate (1) suggested by carp, here is an example script to run in SqlPlus.

-- start script
variable jn number;
create or replace procedure update_every_2hr
is
DECLARE
<< enter you declare statements here >>
BEGIN
<< enter your sql routine here >>
END;
/
begin
dbms_job.submitjn
, 'BEGIN
update_every_2hr;
END;'
, SYSDATE
, 'SYSDATE + 1/12');
COMMIT;
END;
/
print jn
-- end script

Note, this assumes you are logged in as a user that has rights to run the dbms_job procedure. The output print jn tells you the job number that was created.

It should also be noted that your SNP processes will need to be configured correctly to support jobs, but if you are running snapshots then I would assume this to be the case. Anyway, look at the Administrators Guide, chapter 7 Managing Job Queues for more information.

Kerry