xxl_job_oracle/doc/db/tables_xxl_job.sql

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;