This is easily done with the Oracle 10g interface, there is a nice article that explains that in at this address
However sometimes you don't have access to the Administrative UI. Is there another way to create or schedule jobs?
Sure just use the he DBMS_SCHEDULER package.
There are several things you should do:
1. GRANT CREATE JOB TO MYUSER;
2. ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
3. Create Scheduler Program:
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'MYUSER.PROGRAM1',
program_action=>'begin
INSERT INTO TABLE1
SELECT * FROM TABLE2;
end;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'Loads a table from another',
enabled=>TRUE);
END;
4. Create a schedule program:
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval =>
'FREQ=DAILY;INTERVAL=2;BYHOUR=18;BYMINUTE=0;BYSECOND=0',
start_date =>
to_timestamp_tz('2006-11-22 US/Eastern', 'YYYY-MM-DD TZR'),
comments =>
'Schedule for periodic loads',
schedule_name => '"MYUSER"."DOLOADS"');
END;
5. And finally link both together to create a JOB:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYUSER"."JOB1"',
program_name => 'MYUYSER.PROGRAM1',
schedule_name => 'MYUSER.DOLOADS',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Loads a table',
auto_drop => FALSE,
enabled => TRUE);
END;
At least this is how I did it. This is just a quick summary of the following article:http://www.oracle.com/technology/oramag/oracle/04-jul/o44tech_dba.html