Guest User

sql MR

a guest
Aug 19th, 2019
97
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER TABLE MERCHANT_TRANSACTION ADD CONSTRAINT MERCHANT_TX_ID_UNIQUE UNIQUE(MERCHANT_TX_ID) ;
  2. ALTER TABLE MERCHANT_TRANSACTION ENABLE CONSTRAINT MERCHANT_TX_ID_UNIQUE;
  3.  
  4.  
  5. alter table process_entity_keys add unique_flag number(1) default 0;
  6. alter table process_entity_keys add unique_key as (decode(unique_flag,1,key_name||'::'||key_value,pid||'::'||key_name||'::'||key_value)) virtual;
  7. alter table process_entity_keys add CONSTRAINT UK_IDEMPOTENCY_KEY_PEK UNIQUE(unique_key);
  8.  
  9. --
  10. -- A hint submitted by a user: Oracle DB MUST be created as "shared" and the
  11. -- job_queue_processes parameter must be greater than 2
  12. -- However, these settings are pretty much standard after any
  13. -- Oracle install, so most users need not worry about this.
  14. --
  15. -- Many other users (including the primary author of Quartz) have had success
  16. -- runing in dedicated mode, so only consider the above as a hint ;-)
  17. --
  18.  
  19.  
  20. CREATE TABLE qrtz_job_details
  21. (
  22. SCHED_NAME VARCHAR2(120) NOT NULL,
  23. JOB_NAME VARCHAR2(200) NOT NULL,
  24. JOB_GROUP VARCHAR2(200) NOT NULL,
  25. DESCRIPTION VARCHAR2(250) NULL,
  26. JOB_CLASS_NAME VARCHAR2(250) NOT NULL,
  27. IS_DURABLE VARCHAR2(1) NOT NULL,
  28. IS_NONCONCURRENT VARCHAR2(1) NOT NULL,
  29. IS_UPDATE_DATA VARCHAR2(1) NOT NULL,
  30. REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,
  31. JOB_DATA BLOB NULL,
  32. CONSTRAINT QRTZ_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
  33. );
  34. CREATE TABLE qrtz_triggers
  35. (
  36. SCHED_NAME VARCHAR2(120) NOT NULL,
  37. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  38. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  39. JOB_NAME VARCHAR2(200) NOT NULL,
  40. JOB_GROUP VARCHAR2(200) NOT NULL,
  41. DESCRIPTION VARCHAR2(250) NULL,
  42. NEXT_FIRE_TIME NUMBER(13) NULL,
  43. PREV_FIRE_TIME NUMBER(13) NULL,
  44. PRIORITY NUMBER(13) NULL,
  45. TRIGGER_STATE VARCHAR2(16) NOT NULL,
  46. TRIGGER_TYPE VARCHAR2(8) NOT NULL,
  47. START_TIME NUMBER(13) NOT NULL,
  48. END_TIME NUMBER(13) NULL,
  49. CALENDAR_NAME VARCHAR2(200) NULL,
  50. MISFIRE_INSTR NUMBER(2) NULL,
  51. JOB_DATA BLOB NULL,
  52. CONSTRAINT QRTZ_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  53. CONSTRAINT QRTZ_TRIGGER_TO_JOBS_FK FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
  54. REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
  55. );
  56. CREATE TABLE qrtz_simple_triggers
  57. (
  58. SCHED_NAME VARCHAR2(120) NOT NULL,
  59. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  60. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  61. REPEAT_COUNT NUMBER(7) NOT NULL,
  62. REPEAT_INTERVAL NUMBER(12) NOT NULL,
  63. TIMES_TRIGGERED NUMBER(10) NOT NULL,
  64. CONSTRAINT QRTZ_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  65. CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  66. REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  67. );
  68. CREATE TABLE qrtz_cron_triggers
  69. (
  70. SCHED_NAME VARCHAR2(120) NOT NULL,
  71. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  72. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  73. CRON_EXPRESSION VARCHAR2(120) NOT NULL,
  74. TIME_ZONE_ID VARCHAR2(80),
  75. CONSTRAINT QRTZ_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  76. CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  77. REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  78. );
  79. CREATE TABLE qrtz_simprop_triggers
  80. (
  81. SCHED_NAME VARCHAR2(120) NOT NULL,
  82. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  83. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  84. STR_PROP_1 VARCHAR2(512) NULL,
  85. STR_PROP_2 VARCHAR2(512) NULL,
  86. STR_PROP_3 VARCHAR2(512) NULL,
  87. INT_PROP_1 NUMBER(10) NULL,
  88. INT_PROP_2 NUMBER(10) NULL,
  89. LONG_PROP_1 NUMBER(13) NULL,
  90. LONG_PROP_2 NUMBER(13) NULL,
  91. DEC_PROP_1 NUMERIC(13,4) NULL,
  92. DEC_PROP_2 NUMERIC(13,4) NULL,
  93. BOOL_PROP_1 VARCHAR2(1) NULL,
  94. BOOL_PROP_2 VARCHAR2(1) NULL,
  95. CONSTRAINT QRTZ_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  96. CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  97. REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  98. );
  99. CREATE TABLE qrtz_blob_triggers
  100. (
  101. SCHED_NAME VARCHAR2(120) NOT NULL,
  102. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  103. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  104. BLOB_DATA BLOB NULL,
  105. CONSTRAINT QRTZ_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  106. CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  107. REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  108. );
  109. CREATE TABLE qrtz_calendars
  110. (
  111. SCHED_NAME VARCHAR2(120) NOT NULL,
  112. CALENDAR_NAME VARCHAR2(200) NOT NULL,
  113. CALENDAR BLOB NOT NULL,
  114. CONSTRAINT QRTZ_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
  115. );
  116. CREATE TABLE qrtz_paused_trigger_grps
  117. (
  118. SCHED_NAME VARCHAR2(120) NOT NULL,
  119. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  120. CONSTRAINT QRTZ_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
  121. );
  122. CREATE TABLE qrtz_fired_triggers
  123. (
  124. SCHED_NAME VARCHAR2(120) NOT NULL,
  125. ENTRY_ID VARCHAR2(95) NOT NULL,
  126. TRIGGER_NAME VARCHAR2(200) NOT NULL,
  127. TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  128. INSTANCE_NAME VARCHAR2(200) NOT NULL,
  129. FIRED_TIME NUMBER(13) NOT NULL,
  130. SCHED_TIME NUMBER(13) NOT NULL,
  131. PRIORITY NUMBER(13) NOT NULL,
  132. STATE VARCHAR2(16) NOT NULL,
  133. JOB_NAME VARCHAR2(200) NULL,
  134. JOB_GROUP VARCHAR2(200) NULL,
  135. IS_NONCONCURRENT VARCHAR2(1) NULL,
  136. REQUESTS_RECOVERY VARCHAR2(1) NULL,
  137. CONSTRAINT QRTZ_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID)
  138. );
  139. CREATE TABLE qrtz_scheduler_state
  140. (
  141. SCHED_NAME VARCHAR2(120) NOT NULL,
  142. INSTANCE_NAME VARCHAR2(200) NOT NULL,
  143. LAST_CHECKIN_TIME NUMBER(13) NOT NULL,
  144. CHECKIN_INTERVAL NUMBER(13) NOT NULL,
  145. CONSTRAINT QRTZ_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
  146. );
  147. CREATE TABLE qrtz_locks
  148. (
  149. SCHED_NAME VARCHAR2(120) NOT NULL,
  150. LOCK_NAME VARCHAR2(40) NOT NULL,
  151. CONSTRAINT QRTZ_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME)
  152. );
  153.  
  154. create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY);
  155. create index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP);
  156.  
  157. create index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
  158. create index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP);
  159. create index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME);
  160. create index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP);
  161. create index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE);
  162. create index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
  163. create index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
  164. create index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME);
  165. create index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
  166. create index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
  167. create index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
  168. create index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
  169.  
  170. create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME);
  171. create index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
  172. create index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
  173. create index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP);
  174. create index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
  175. create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP);
  176.  
  177. COMMIT;
RAW Paste Data