I will show, how to assign a job to window..
We will create a new window. It will start every day at 2:20 and will be 30 minutes long:
Here, the SQL for creating the window:
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"TEST_WINDOW"',
resource_plan=>'INTERNAL_PLAN',
start_date=>systimestamp at time zone 'Europe/Prague',
duration=>numtodsinterval(30, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=14;BYMINUTE=20;BYSECOND=0',
end_date=>null,
window_priority=>'LOW',
comments=>'just a test window');
END;
Here I will create the job and assign it to this new window:
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"TEST_WINDOW"',
resource_plan=>'INTERNAL_PLAN',
start_date=>systimestamp at time zone 'Europe/Prague',
duration=>numtodsinterval(30, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=14;BYMINUTE=20;BYSECOND=0',
end_date=>null,
window_priority=>'LOW',
comments=>'just a test window');
END;
Here I will create the job and assign it to this new window:
Here the SQL for creating the job:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
schedule_name => '"SYS"."TEST_WINDOW"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'just a test job',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"TEST"."TEST_JOB"', attribute => 'stop_on_window_close', value => FALSE);
sys.dbms_scheduler.enable( '"TEST"."TEST_JOB"' );
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
schedule_name => '"SYS"."TEST_WINDOW"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'just a test job',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"TEST"."TEST_JOB"', attribute => 'stop_on_window_close', value => FALSE);
sys.dbms_scheduler.enable( '"TEST"."TEST_JOB"' );
END;
I have truncated the table, so we will see, if the job will work:
SQL> truncate table test_table;
Table truncated.
SQL> select * from test_table;
no rows selected
Here the explanation of the parameter which have to be passed to dbms_scheduler.create_window:
START_DATE Time when the Window will open.
REPEAT_INTERVAL The next time the window will open again.
RESOURCE_PLAN Tells us that while this window is open, resource allocation to all the jobs that run in this window will be
guided by the resource plan directives in the resource plan TEST_RESOURCEPLAN.
DURATION Window will remain open for a period of 60 minutes, after which it will close.
END_DATE Window will open for the last time on December 31, 2005, after which it will be disabled and closed.
WINDOW_PRIORITY Possible values are: LOW, HIGH.
When two Windows overlap, the high-priority window will open and the lower-priority window does not open.
You can open window manually:
DBMS_SCHEDULER.OPEN_WINDOW ( WINDOW_NAME =>'TEST_WINDOW',DURATION => '0 12:00:00')
You can open an already open window. If you do this, the duration of the window will last a time period equal to its duration attribute.
Closing window:
DBMS_SCHEDULER.CLOSE_WINDOW('TEST_WINDOW');
A running job may close upon the closing of its window, if you create a job with the attribute STOP_ON_WINDOW_CLOSE set to TRUE. I have used this attribute during the creation of my test window.
SQL> truncate table test_table;
Table truncated.
SQL> select * from test_table;
no rows selected
Here the explanation of the parameter which have to be passed to dbms_scheduler.create_window:
START_DATE Time when the Window will open.
REPEAT_INTERVAL The next time the window will open again.
RESOURCE_PLAN Tells us that while this window is open, resource allocation to all the jobs that run in this window will be
guided by the resource plan directives in the resource plan TEST_RESOURCEPLAN.
DURATION Window will remain open for a period of 60 minutes, after which it will close.
END_DATE Window will open for the last time on December 31, 2005, after which it will be disabled and closed.
WINDOW_PRIORITY Possible values are: LOW, HIGH.
When two Windows overlap, the high-priority window will open and the lower-priority window does not open.
You can open window manually:
DBMS_SCHEDULER.OPEN_WINDOW ( WINDOW_NAME =>'TEST_WINDOW',DURATION => '0 12:00:00')
You can open an already open window. If you do this, the duration of the window will last a time period equal to its duration attribute.
Closing window:
DBMS_SCHEDULER.CLOSE_WINDOW('TEST_WINDOW');
A running job may close upon the closing of its window, if you create a job with the attribute STOP_ON_WINDOW_CLOSE set to TRUE. I have used this attribute during the creation of my test window.