TB worker 任务设置相关sql脚本

最新推荐文章于 2021-03-19 11:30:07 发布

qinggreen

最新推荐文章于 2021-03-19 11:30:07 发布

阅读量1.4k

点赞数

分类专栏： orther kj

orther kj 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

http://rongqiang1992.blog.163.com/blog/static/21727620920146180021789/

CREATE SMALLFILE TABLESPACE "TMS_WORK" DATAFILE 'D:\APP\YFLIJIE\ORADATA\ORCL\MS_WORK' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

create table PAMIRS_SCHEDULE_QUEUE

(

id NUMBER not null,

task_type VARCHAR2(50) not null,

queue_id VARCHAR2(50) not null,

own_sign VARCHAR2(50) not null,

base_task_type VARCHAR2(50),

cur_server VARCHAR2(100),

req_server VARCHAR2(100),

gmt_create DATE not null,

gmt_modified DATE not null

)

tablespace TMS_WORK

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

comment on table PAMIRS_SCHEDULE_QUEUE

is '调度队列';

comment on column PAMIRS_SCHEDULE_QUEUE.id

is '主键ID';

comment on column PAMIRS_SCHEDULE_QUEUE.task_type

is '任务类型,由BASE_TASK_TYPE-OWN_SIGN构成，如果是BASE，则是BASE_TASK_TYPE';

comment on column PAMIRS_SCHEDULE_QUEUE.queue_id

is '任务队列编号';

comment on column PAMIRS_SCHEDULE_QUEUE.own_sign

is '环境，例如开发、测试、预发、线上。缺省BASE，2.0版本新增';

comment on column PAMIRS_SCHEDULE_QUEUE.base_task_type

is '基础任务类型，2.0版本新增';

comment on column PAMIRS_SCHEDULE_QUEUE.cur_server

is '当前持有的调度服务器';

comment on column PAMIRS_SCHEDULE_QUEUE.req_server

is '请求调度的服务器';

comment on column PAMIRS_SCHEDULE_QUEUE.gmt_create

is '创建时间';

comment on column PAMIRS_SCHEDULE_QUEUE.gmt_modified

is '修改时间';

create unique index IND_PAMIRS_SCHEDULE_QUEUEID on PAMIRS_SCHEDULE_QUEUE (TASK_TYPE, QUEUE_ID, OWN_SIGN)

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table PAMIRS_SCHEDULE_QUEUE

add constraint PK_PAMIRS_SCHEDULE_QUEUE primary key (ID)

using index

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

create table PAMIRS_SCHEDULE_SERVER

(

id NUMBER not null,

uuid VARCHAR2(100) not null,

task_type VARCHAR2(50) not null,

own_sign VARCHAR2(50) not null,

base_task_type VARCHAR2(50) not null,

ip VARCHAR2(50) not null,

host_name VARCHAR2(50) not null,

manager_port NUMBER not null,

thread_num NUMBER not null,

register_time DATE not null,

heartbeat_time DATE not null,

version NUMBER not null,

jmx_url VARCHAR2(200),

dealinfo_desc VARCHAR2(1000),

next_run_start_time VARCHAR2(100),

next_run_end_time VARCHAR2(100),

gmt_create DATE not null,

gmt_modified DATE not null

)

tablespace TMS_WORK

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

comment on table PAMIRS_SCHEDULE_SERVER

is '调度服务器';

comment on column PAMIRS_SCHEDULE_SERVER.id

is '主键ID';

comment on column PAMIRS_SCHEDULE_SERVER.uuid

is '调度服务器唯一编号';

comment on column PAMIRS_SCHEDULE_SERVER.task_type

is '任务类型。由BASE_TASK_TYPE-OWN_SIGN构成，如果是BASE，则是BASE_TASK_TYPE';

comment on column PAMIRS_SCHEDULE_SERVER.own_sign

is '环境，例如开发、测试、预发、线上。缺省BASE，2.0版本新增';

comment on column PAMIRS_SCHEDULE_SERVER.base_task_type

is '基础任务类型，2.0版本新增';

comment on column PAMIRS_SCHEDULE_SERVER.ip

is 'IP地址';

comment on column PAMIRS_SCHEDULE_SERVER.host_name

is '主机名称';

comment on column PAMIRS_SCHEDULE_SERVER.manager_port

is 'JMX的HTTP协议远程管理端口';

comment on column PAMIRS_SCHEDULE_SERVER.thread_num

is '线程数量';

comment on column PAMIRS_SCHEDULE_SERVER.register_time

is '注册时间';

comment on column PAMIRS_SCHEDULE_SERVER.heartbeat_time

is '最后一次心跳时间';

comment on column PAMIRS_SCHEDULE_SERVER.version

is '版本号';

comment on column PAMIRS_SCHEDULE_SERVER.jmx_url

is 'JMX的连接串';

comment on column PAMIRS_SCHEDULE_SERVER.dealinfo_desc

is '调度处理情况描述';

comment on column PAMIRS_SCHEDULE_SERVER.next_run_start_time

is '当限制直接时间段的时候，下一次启动时间';

comment on column PAMIRS_SCHEDULE_SERVER.next_run_end_time

is '当限制直接时间段的时候，下一次必须终止的时间';

comment on column PAMIRS_SCHEDULE_SERVER.gmt_create

is '创建时间';

comment on column PAMIRS_SCHEDULE_SERVER.gmt_modified

is '修改时间';

create index IND_PAMIRS_SCHEDULE_UUID on PAMIRS_SCHEDULE_SERVER (UUID)

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table PAMIRS_SCHEDULE_SERVER

add constraint PK_PAMIRS_SCHEDULE_SERVER primary key (ID)

using index

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

create table PAMIRS_SCHEDULE_SERVER_HIS

(

id NUMBER not null,

uuid VARCHAR2(100) not null,

task_type VARCHAR2(50) not null,

own_sign VARCHAR2(50) not null,

base_task_type VARCHAR2(50) not null,

ip VARCHAR2(50) not null,

host_name VARCHAR2(50) not null,

manager_port NUMBER not null,

thread_num NUMBER not null,

register_time DATE not null,

heartbeat_time DATE not null,

version NUMBER not null,

jmx_url VARCHAR2(200),

dealinfo_desc VARCHAR2(1000),

next_run_start_time VARCHAR2(100),

next_run_end_time VARCHAR2(100),

gmt_create DATE not null,

gmt_modified DATE not null

)

tablespace TMS_WORK

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

create table PAMIRS_SCHEDULE_TASKTRUN

(

id NUMBER not null,

task_type VARCHAR2(100) not null,

own_sign VARCHAR2(50),

base_task_type VARCHAR2(50),

last_assign_time DATE,

last_assign_uuid VARCHAR2(100),

gmt_create DATE not null,

gmt_modified DATE not null

)

tablespace TMS_WORK

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

comment on table PAMIRS_SCHEDULE_TASKTRUN

is '运行期任务表，2.0版本新增';

comment on column PAMIRS_SCHEDULE_TASKTRUN.id

is '主键ID';

comment on column PAMIRS_SCHEDULE_TASKTRUN.task_type

is '任务类型，由BASE_TASK_TYPE-OWN_SIGN构成，如果是BASE，则是BASE_TASK_TYPE';

comment on column PAMIRS_SCHEDULE_TASKTRUN.own_sign

is '环境，例如开发、测试、预发、线上。缺省BASE';

comment on column PAMIRS_SCHEDULE_TASKTRUN.base_task_type

is '基础任务类型';

comment on column PAMIRS_SCHEDULE_TASKTRUN.last_assign_time

is '最近一次任务分配时间';

comment on column PAMIRS_SCHEDULE_TASKTRUN.last_assign_uuid

is '最近一次进行任务分配的服务器';

comment on column PAMIRS_SCHEDULE_TASKTRUN.gmt_create

is '创建时间';

comment on column PAMIRS_SCHEDULE_TASKTRUN.gmt_modified

is '修改时间';

create index IND_PAMIRS_SCHEDULE_RUN_TASK on PAMIRS_SCHEDULE_TASKTRUN (TASK_TYPE, OWN_SIGN)

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table PAMIRS_SCHEDULE_TASKTRUN

add constraint PK_PAMIRS_SCHEDULE_RUN primary key (ID)

using index

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

create table PAMIRS_SCHEDULE_TASKTYPE

(

id NUMBER not null,

task_type VARCHAR2(100) not null,

deal_bean_name VARCHAR2(100) not null,

heartbeat_rate NUMBER not null,

judge_dead_interval NUMBER not null,

thread_number NUMBER not null,

execute_number NUMBER,

fetch_number NUMBER,

sleep_time_nodata NUMBER,

sleep_time_interval NUMBER,

processor_type VARCHAR2(20),

permit_run_start_time VARCHAR2(100),

permit_run_end_time VARCHAR2(100),

last_assign_time DATE,

last_assign_uuid VARCHAR2(100),

expire_own_sign_interval NUMBER,

gmt_create DATE not null,

gmt_modified DATE not null

)

tablespace TMS_WORK

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

comment on table PAMIRS_SCHEDULE_TASKTYPE

is '调度处理的任务类型';

comment on column PAMIRS_SCHEDULE_TASKTYPE.id

is '唯一编号，是数据库管理的需要';

comment on column PAMIRS_SCHEDULE_TASKTYPE.task_type

is '任务类型，要求唯一性，建立唯一索引';

comment on column PAMIRS_SCHEDULE_TASKTYPE.deal_bean_name

is '处理任务Bean的名称，Spring中的配置，2.0版本新增';

comment on column PAMIRS_SCHEDULE_TASKTYPE.heartbeat_rate

is '心跳频率';

comment on column PAMIRS_SCHEDULE_TASKTYPE.judge_dead_interval

is '判断服务器死亡的心跳间隔';

comment on column PAMIRS_SCHEDULE_TASKTYPE.thread_number

is '每一个调度服务器的线程数量';

comment on column PAMIRS_SCHEDULE_TASKTYPE.execute_number

is '批处理的时候，每次执行的数据量';

comment on column PAMIRS_SCHEDULE_TASKTYPE.fetch_number

is '每次查询获取的数据量';

comment on column PAMIRS_SCHEDULE_TASKTYPE.sleep_time_nodata

is '当没有取到数据时候，休眠的时间,缺省是0，不休眠';

comment on column PAMIRS_SCHEDULE_TASKTYPE.sleep_time_interval

is '休眠时间，每一批数据处理完成后，缺省是0，不休眠';

comment on column PAMIRS_SCHEDULE_TASKTYPE.processor_type

is '处理器类型：SLEEP 或者 NOTSLEEP,缺省是SLEEP';

comment on column PAMIRS_SCHEDULE_TASKTYPE.permit_run_start_time

is '允许执行时段的开始时间crontab的时间格式.以startrun:开始，则表示开机立即启动调度';

comment on column PAMIRS_SCHEDULE_TASKTYPE.permit_run_end_time

is '允许执行时段的结束时间crontab的时间格式,如果不设置，表示取不到数据就停止';

comment on column PAMIRS_SCHEDULE_TASKTYPE.last_assign_time

is '最近进行环境处理时间';

comment on column PAMIRS_SCHEDULE_TASKTYPE.last_assign_uuid

is '最近进行环境处理的Server';

comment on column PAMIRS_SCHEDULE_TASKTYPE.expire_own_sign_interval

is '清除过期环境数据的时间间隔,单位是天，2.0版本新增';

comment on column PAMIRS_SCHEDULE_TASKTYPE.gmt_create

is '创建时间';

comment on column PAMIRS_SCHEDULE_TASKTYPE.gmt_modified

is '修改时间';

create unique index IND_PAMIRS_TASKTYPE_TASKTYPE on PAMIRS_SCHEDULE_TASKTYPE (TASK_TYPE)

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

alter table PAMIRS_SCHEDULE_TASKTYPE

add constraint PK_PAMIRS_SCHEDULE_TASKTYPE primary key (ID)

using index

tablespace TMS_WORK

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

-- Create sequence

create sequence SEQ_PAMIRS_SCHEDULE_TASKTYPE

minvalue 1

maxvalue 99999999999999999999999999

start with 41

increment by 1

cache 20;

create sequence SEQ_PAMIRS_SCHEDULE_QUEUE

minvalue 1

maxvalue 99999999999999999999999999

start with 41

increment by 1

cache 20;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

insert into PAMIRS_SCHEDULE_TASKTYPE

values(SEQ_PAMIRS_SCHEDULE_TASKTYPE.Nextval,'doSelfCompleteTask','doSelfCompleteTask',10,60,3,10,500,10,5,'NOTSLEEP',null,null,null,null,null,sysdate,sysdate);