job.sql
7.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
--
-- A hint submitted by a user: Oracle DB MUST be created as "shared" and the
-- job_queue_processes parameter must be greater than 2
-- However, these settings are pretty much standard after any
-- Oracle install, so most users need not worry about this.
--
-- Many other users (including the primary author of Quartz) have had success
-- runing in dedicated mode, so only consider the above as a hint ;-)
--
delete from js_job_fired_triggers;
delete from js_job_simple_triggers;
delete from js_job_simprop_triggers;
delete from js_job_cron_triggers;
delete from js_job_blob_triggers;
delete from js_job_triggers;
delete from js_job_job_details;
delete from js_job_calendars;
delete from js_job_paused_trigger_grps;
delete from js_job_locks;
delete from js_job_scheduler_state;
drop table js_job_calendars;
drop table js_job_fired_triggers;
drop table js_job_blob_triggers;
drop table js_job_cron_triggers;
drop table js_job_simple_triggers;
drop table js_job_simprop_triggers;
drop table js_job_triggers;
drop table js_job_job_details;
drop table js_job_paused_trigger_grps;
drop table js_job_locks;
drop table js_job_scheduler_state;
CREATE TABLE js_job_job_details
(
SCHED_NAME VARCHAR2(120) NOT NULL,
JOB_NAME VARCHAR2(200) NOT NULL,
JOB_GROUP VARCHAR2(200) NOT NULL,
DESCRIPTION VARCHAR2(250) NULL,
JOB_CLASS_NAME VARCHAR2(250) NOT NULL,
IS_DURABLE VARCHAR2(1) NOT NULL,
IS_NONCONCURRENT VARCHAR2(1) NOT NULL,
IS_UPDATE_DATA VARCHAR2(1) NOT NULL,
REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,
JOB_DATA BLOB NULL,
CONSTRAINT js_job_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE js_job_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
JOB_NAME VARCHAR2(200) NOT NULL,
JOB_GROUP VARCHAR2(200) NOT NULL,
DESCRIPTION VARCHAR2(250) NULL,
NEXT_FIRE_TIME NUMBER(13) NULL,
PREV_FIRE_TIME NUMBER(13) NULL,
PRIORITY NUMBER(13) NULL,
TRIGGER_STATE VARCHAR2(16) NOT NULL,
TRIGGER_TYPE VARCHAR2(8) NOT NULL,
START_TIME NUMBER(13) NOT NULL,
END_TIME NUMBER(13) NULL,
CALENDAR_NAME VARCHAR2(200) NULL,
MISFIRE_INSTR NUMBER(2) NULL,
JOB_DATA BLOB NULL,
CONSTRAINT js_job_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
CONSTRAINT js_job_TRIGGER_TO_JOBS_FK FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
REFERENCES js_job_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE js_job_simple_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
REPEAT_COUNT NUMBER(7) NOT NULL,
REPEAT_INTERVAL NUMBER(12) NOT NULL,
TIMES_TRIGGERED NUMBER(10) NOT NULL,
CONSTRAINT js_job_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
CONSTRAINT js_job_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES js_job_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE js_job_cron_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
CRON_EXPRESSION VARCHAR2(120) NOT NULL,
TIME_ZONE_ID VARCHAR2(80),
CONSTRAINT js_job_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
CONSTRAINT js_job_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES js_job_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE js_job_simprop_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
STR_PROP_1 VARCHAR2(512) NULL,
STR_PROP_2 VARCHAR2(512) NULL,
STR_PROP_3 VARCHAR2(512) NULL,
INT_PROP_1 NUMBER(10) NULL,
INT_PROP_2 NUMBER(10) NULL,
LONG_PROP_1 NUMBER(13) NULL,
LONG_PROP_2 NUMBER(13) NULL,
DEC_PROP_1 NUMERIC(13,4) NULL,
DEC_PROP_2 NUMERIC(13,4) NULL,
BOOL_PROP_1 VARCHAR2(1) NULL,
BOOL_PROP_2 VARCHAR2(1) NULL,
CONSTRAINT js_job_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
CONSTRAINT js_job_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES js_job_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE js_job_blob_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
BLOB_DATA BLOB NULL,
CONSTRAINT js_job_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
CONSTRAINT js_job_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES js_job_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE js_job_calendars
(
SCHED_NAME VARCHAR2(120) NOT NULL,
CALENDAR_NAME VARCHAR2(200) NOT NULL,
CALENDAR BLOB NOT NULL,
CONSTRAINT js_job_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
);
CREATE TABLE js_job_paused_trigger_grps
(
SCHED_NAME VARCHAR2(120) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
CONSTRAINT js_job_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
);
CREATE TABLE js_job_fired_triggers
(
SCHED_NAME VARCHAR2(120) NOT NULL,
ENTRY_ID VARCHAR2(95) NOT NULL,
TRIGGER_NAME VARCHAR2(200) NOT NULL,
TRIGGER_GROUP VARCHAR2(200) NOT NULL,
INSTANCE_NAME VARCHAR2(200) NOT NULL,
FIRED_TIME NUMBER(13) NOT NULL,
SCHED_TIME NUMBER(13) NOT NULL,
PRIORITY NUMBER(13) NOT NULL,
STATE VARCHAR2(16) NOT NULL,
JOB_NAME VARCHAR2(200) NULL,
JOB_GROUP VARCHAR2(200) NULL,
IS_NONCONCURRENT VARCHAR2(1) NULL,
REQUESTS_RECOVERY VARCHAR2(1) NULL,
CONSTRAINT js_job_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID)
);
CREATE TABLE js_job_scheduler_state
(
SCHED_NAME VARCHAR2(120) NOT NULL,
INSTANCE_NAME VARCHAR2(200) NOT NULL,
LAST_CHECKIN_TIME NUMBER(13) NOT NULL,
CHECKIN_INTERVAL NUMBER(13) NOT NULL,
CONSTRAINT js_job_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
);
CREATE TABLE js_job_locks
(
SCHED_NAME VARCHAR2(120) NOT NULL,
LOCK_NAME VARCHAR2(40) NOT NULL,
CONSTRAINT js_job_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME)
);
create index idx_qrtz_j_req_recovery on js_job_job_details(SCHED_NAME,REQUESTS_RECOVERY);
create index idx_qrtz_j_grp on js_job_job_details(SCHED_NAME,JOB_GROUP);
create index idx_qrtz_t_j on js_job_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create index idx_qrtz_t_jg on js_job_triggers(SCHED_NAME,JOB_GROUP);
create index idx_qrtz_t_c on js_job_triggers(SCHED_NAME,CALENDAR_NAME);
create index idx_qrtz_t_g on js_job_triggers(SCHED_NAME,TRIGGER_GROUP);
create index idx_qrtz_t_state on js_job_triggers(SCHED_NAME,TRIGGER_STATE);
create index idx_qrtz_t_n_state on js_job_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create index idx_qrtz_t_n_g_state on js_job_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create index idx_qrtz_t_next_fire_time on js_job_triggers(SCHED_NAME,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_st on js_job_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_misfire on js_job_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_st_misfire on js_job_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
create index idx_qrtz_t_nft_st_misfire_grp on js_job_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
create index idx_qrtz_ft_trig_inst_name on js_job_fired_triggers(SCHED_NAME,INSTANCE_NAME);
create index idx_qrtz_ft_inst_job_req_rcvry on js_job_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
create index idx_qrtz_ft_j_g on js_job_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create index idx_qrtz_ft_jg on js_job_fired_triggers(SCHED_NAME,JOB_GROUP);
create index idx_qrtz_ft_t_g on js_job_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
create index idx_qrtz_ft_tg on js_job_fired_triggers(SCHED_NAME,TRIGGER_GROUP);