CREATE TABLESPACE JZX_XXL_JOB DATAFILE '/opt/oracle/19c/oradata/ORCL/etlp_pdb/jzx_xxl_job.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M; CREATE USER SMART_ETLP_XXL_JOB IDENTIFIED BY "huadong_2025" DEFAULT TABLESPACE JZX_XXL_JOB; GRANT ALL PRIVILEGES TO SMART_ETLP_XXL_JOB; REVOKE CREATE USER FROM SMART_ETLP_JNPF; REVOKE ALTER USER FROM SMART_ETLP_JNPF; REVOKE DROP USER FROM SMART_ETLP_JNPF; REVOKE GRANT ANY ROLE FROM SMART_ETLP_JNPF; REVOKE GRANT ANY PRIVILEGE FROM SMART_ETLP_JNPF; REVOKE CREATE TABLESPACE FROM SMART_ETLP_JNPF; REVOKE ALTER TABLESPACE FROM SMART_ETLP_JNPF; REVOKE DROP TABLESPACE FROM SMART_ETLP_JNPF; REVOKE SELECT ANY TABLE FROM SMART_ETLP; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_INFO ( ID NUMBER(11) not null constraint PK_XXL_JOB_INFO primary key, JOB_GROUP NUMBER(11) not null, JOB_DESC VARCHAR2(255) not null, ADD_TIME TIMESTAMP(6) default NULL, UPDATE_TIME TIMESTAMP(6) default NULL, AUTHOR VARCHAR2(64) default NULL, ALARM_EMAIL VARCHAR2(255) default NULL, SCHEDULE_TYPE VARCHAR2(50) default 'NONE', SCHEDULE_CONF VARCHAR2(128) default NULL, MISFIRE_STRATEGY VARCHAR2(50) default 'DO_NOTHING', EXECUTOR_ROUTE_STRATEGY VARCHAR2(50) default NULL, EXECUTOR_HANDLER VARCHAR2(255) default NULL, EXECUTOR_PARAM VARCHAR2(512) default NULL, EXECUTOR_BLOCK_STRATEGY VARCHAR2(50) default NULL, EXECUTOR_TIMEOUT NUMBER(11) default 0, EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) default 0, GLUE_TYPE VARCHAR2(50) not null, GLUE_SOURCE CLOB default NULL, GLUE_REMARK VARCHAR2(128) default NULL, GLUE_UPDATETIME TIMESTAMP(6) default NULL, CHILD_JOBID VARCHAR2(255) default NULL, TRIGGER_STATUS NUMBER(1) default 0, TRIGGER_LAST_TIME NUMBER(13) default 0, TRIGGER_NEXT_TIME NUMBER(13) default 0 ); COMMENT ON COLUMN xxl_job_info.job_group IS '执行器主键ID'; COMMENT ON COLUMN xxl_job_info.job_desc IS '任务描述'; COMMENT ON COLUMN xxl_job_info.add_time IS '添加时间'; COMMENT ON COLUMN xxl_job_info.update_time IS '更新时间'; COMMENT ON COLUMN xxl_job_info.author IS '作者'; COMMENT ON COLUMN xxl_job_info.alarm_email IS '报警邮件'; COMMENT ON COLUMN xxl_job_info.schedule_type IS '调度类型'; COMMENT ON COLUMN xxl_job_info.schedule_conf IS '调度配置，值含义取决于调度类型'; COMMENT ON COLUMN xxl_job_info.misfire_strategy IS '调度过期策略'; COMMENT ON COLUMN xxl_job_info.executor_route_strategy IS '执行器路由策略'; COMMENT ON COLUMN xxl_job_info.executor_handler IS '执行器任务handler'; COMMENT ON COLUMN xxl_job_info.executor_param IS '执行器任务参数'; COMMENT ON COLUMN xxl_job_info.executor_block_strategy IS '阻塞处理策略'; COMMENT ON COLUMN xxl_job_info.executor_timeout IS '任务执行超时时间，单位秒'; COMMENT ON COLUMN xxl_job_info.executor_fail_retry_count IS '失败重试次数'; COMMENT ON COLUMN xxl_job_info.glue_type IS 'GLUE类型'; COMMENT ON COLUMN xxl_job_info.glue_source IS 'GLUE源代码'; COMMENT ON COLUMN xxl_job_info.glue_remark IS 'GLUE备注'; COMMENT ON COLUMN xxl_job_info.glue_updatetime IS 'GLUE更新时间'; COMMENT ON COLUMN xxl_job_info.child_jobid IS '子任务ID，多个逗号分隔'; COMMENT ON COLUMN xxl_job_info.trigger_status IS '调度状态：0-停止，1-运行'; COMMENT ON COLUMN xxl_job_info.trigger_last_time IS '上次调度时间'; COMMENT ON COLUMN xxl_job_info.trigger_next_time IS '下次调度时间'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_LOG ( ID NUMBER(20) not null constraint PK_XXL_JOB_LOG primary key, JOB_GROUP NUMBER(11) not null, JOB_ID NUMBER(11) not null, EXECUTOR_ADDRESS VARCHAR2(255) default NULL, EXECUTOR_HANDLER VARCHAR2(255) default NULL, EXECUTOR_PARAM VARCHAR2(512) default NULL, EXECUTOR_SHARDING_PARAM VARCHAR2(20) default NULL, EXECUTOR_FAIL_RETRY_COUNT NUMBER(11) default 0, TRIGGER_TIME TIMESTAMP(6) default NULL, TRIGGER_CODE NUMBER(11) not null, TRIGGER_MSG CLOB default NULL, HANDLE_TIME TIMESTAMP(6) default NULL, HANDLE_CODE NUMBER(11) not null, HANDLE_MSG CLOB default NULL, ALARM_STATUS NUMBER(1) default 0 ); COMMENT ON COLUMN xxl_job_log.job_group IS '执行器主键ID'; COMMENT ON COLUMN xxl_job_log.job_id IS '任务，主键ID'; COMMENT ON COLUMN xxl_job_log.executor_address IS '执行器地址，本次执行的地址'; COMMENT ON COLUMN xxl_job_log.executor_handler IS '执行器任务handler'; COMMENT ON COLUMN xxl_job_log.executor_param IS '执行器任务参数'; COMMENT ON COLUMN xxl_job_log.executor_sharding_param IS '执行器任务分片参数，格式如 1/2'; COMMENT ON COLUMN xxl_job_log.executor_fail_retry_count IS '失败重试次数'; COMMENT ON COLUMN xxl_job_log.trigger_time IS '调度-时间'; COMMENT ON COLUMN xxl_job_log.trigger_code IS '调度-结果'; COMMENT ON COLUMN xxl_job_log.trigger_msg IS '调度-日志'; COMMENT ON COLUMN xxl_job_log.handle_time IS '执行-时间'; COMMENT ON COLUMN xxl_job_log.handle_code IS '执行-状态'; COMMENT ON COLUMN xxl_job_log.handle_msg IS '执行-日志'; COMMENT ON COLUMN xxl_job_log.alarm_status IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_LOG_REPORT ( ID NUMBER(11) not null constraint PK_XXL_JOB_LOG_REPORT primary key, TRIGGER_DAY TIMESTAMP(6) default NULL constraint UQ_TRIGGER_DAY unique, RUNNING_COUNT NUMBER(11) default 0 not null, SUC_COUNT NUMBER(11) default 0 not null, FAIL_COUNT NUMBER(11) default 0 not null, UPDATE_TIME TIMESTAMP(6) default NULL ); COMMENT ON COLUMN xxl_job_log_report.id IS '主键ID'; COMMENT ON COLUMN xxl_job_log_report.trigger_day IS '调度-时间'; COMMENT ON COLUMN xxl_job_log_report.running_count IS '运行中-日志数量'; COMMENT ON COLUMN xxl_job_log_report.suc_count IS '执行成功-日志数量'; COMMENT ON COLUMN xxl_job_log_report.fail_count IS '执行失败-日志数量'; COMMENT ON COLUMN xxl_job_log_report.update_time IS '更新时间'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_LOGGLUE ( ID NUMBER(11) not null constraint PK_XXL_JOB_LOGGLUE primary key, JOB_ID NUMBER(11) not null, GLUE_TYPE VARCHAR2(50) default NULL, GLUE_SOURCE CLOB default NULL, GLUE_REMARK VARCHAR2(128) not null, ADD_TIME TIMESTAMP(6) default NULL, UPDATE_TIME TIMESTAMP(6) default NULL ); COMMENT ON COLUMN xxl_job_logglue.job_id IS '任务，主键ID'; COMMENT ON COLUMN xxl_job_logglue.glue_type IS 'GLUE类型'; COMMENT ON COLUMN xxl_job_logglue.glue_source IS 'GLUE源代码'; COMMENT ON COLUMN xxl_job_logglue.glue_remark IS 'GLUE备注'; COMMENT ON COLUMN xxl_job_logglue.add_time IS '添加时间'; COMMENT ON COLUMN xxl_job_logglue.update_time IS '更新时间'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_REGISTRY ( ID NUMBER(11) not null constraint PK_XXL_JOB_REGISTRY primary key, REGISTRY_GROUP VARCHAR2(50) not null, REGISTRY_KEY VARCHAR2(255) not null, REGISTRY_VALUE VARCHAR2(255) not null, UPDATE_TIME TIMESTAMP(6) default NULL, constraint UQ_G_K_V unique (REGISTRY_GROUP, REGISTRY_KEY, REGISTRY_VALUE) ); COMMENT ON COLUMN xxl_job_registry.id IS '主键ID'; COMMENT ON COLUMN xxl_job_registry.registry_group IS '注册组'; COMMENT ON COLUMN xxl_job_registry.registry_key IS '注册键'; COMMENT ON COLUMN xxl_job_registry.registry_value IS '注册值'; COMMENT ON COLUMN xxl_job_registry.update_time IS '更新时间'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_GROUP ( ID NUMBER(11) not null constraint PK_XXL_JOB_GROUP primary key, APP_NAME VARCHAR2(255) not null, TITLE VARCHAR2(50) not null, ADDRESS_TYPE NUMBER(3) default 0 not null, ADDRESS_LIST CLOB default NULL, UPDATE_TIME TIMESTAMP(6) default NULL ); COMMENT ON COLUMN xxl_job_group.id IS '主键ID'; COMMENT ON COLUMN xxl_job_group.app_name IS '执行器AppName'; COMMENT ON COLUMN xxl_job_group.title IS '执行器名称'; COMMENT ON COLUMN xxl_job_group.address_type IS '执行器地址类型：0=自动注册、1=手动录入'; COMMENT ON COLUMN xxl_job_group.address_list IS '执行器地址列表，多地址逗号分隔'; COMMENT ON COLUMN xxl_job_group.update_time IS '更新时间'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_USER ( ID NUMBER(11) not null constraint PK_XXL_JOB_USER primary key, USERNAME VARCHAR2(50) not null constraint UQ_USERNAME unique, PASSWORD VARCHAR2(50) not null, ROLE NUMBER(3) not null, PERMISSION VARCHAR2(255) default NULL ); COMMENT ON COLUMN xxl_job_user.id IS '主键ID'; COMMENT ON COLUMN xxl_job_user.username IS '账号'; COMMENT ON COLUMN xxl_job_user.password IS '密码'; COMMENT ON COLUMN xxl_job_user.role IS '角色：0-普通用户、1-管理员'; COMMENT ON COLUMN xxl_job_user.permission IS '权限：执行器ID列表，多个逗号分割'; ------------------------------------------------------------------------------------------------------------------------ create table XXL_JOB_LOCK ( LOCK_NAME VARCHAR2(50) not null constraint PK_XXL_JOB_LOCK primary key ); COMMENT ON COLUMN xxl_job_lock.lock_name IS '锁名称'; ------------------------------------------------------------------------------------------------------------------------ CREATE SEQUENCE group_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE info_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE lock_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE log_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE log_report_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE logglue_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE registry_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; CREATE SEQUENCE user_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CYCLE NOCACHE; ------------------------------------------------------------------------------------------------------------------------ INSERT INTO SMART_ETLP_XXL_JOB.XXL_JOB_USER (ID, USERNAME, PASSWORD, ROLE, PERMISSION) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, null); INSERT INTO XXL_JOB_LOCK (LOCK_NAME) VALUES ('schedule_lock'); INSERT INTO SMART_ETLP_XXL_JOB.XXL_JOB_GROUP (ID, APP_NAME, TITLE, ADDRESS_TYPE, ADDRESS_LIST, UPDATE_TIME) VALUES (10, 'xxl-job-executor-jnpf', 'ips系统', 0, null, TIMESTAMP '2025-06-18 09:29:05.052000'); commit;