--插件 kdb_schedule 目前是只支持Oracle版KingbaseES,不支持pg版本的
--1.创建表
CREATE TABLE dat_size AS
select sys_database.datname, sys_size_pretty
(sys_database_size(sys_database.datname)) AS size ,
to_char(NOW(),'YYYY-MM-DD') AS time
from sys_database;
--2.创建存储过程
CREATE OR REPLACE PROCEDURE p_dat_size()
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO dat_size
SELECT
sys_database.datname,
sys_size_pretty(sys_database_size(sys_database.datname)) AS SIZE ,
to_char(NOW(), 'YYYY-MM-DD') AS time
FROM
sys_database;
COMMIT;
END;
$$;
--3.添加kdb_schedule插件,kingbase.conf 文件修改shared_preload_libraries参数,把kdb_schedule添加到里面,重启数据库
shared_preload_libraries = 'kdb_schedule'
sys_ctl RESTART
--4.创建program
--begin ;
-- call dbms_scheduler.create_program(
-- program_name => 'program_dat_size',
-- program_type => 'PLSQL_BLOCK',
-- program_action => 'call public.p_dat_size()',
-- acconnstr => 'user=system dbname=test port=54321 password=kingbase',
-- acdbname => 'test',
-- number_of_arguments => 0,
-- enabled => true,
-- comments => 'test program');
--end;
begin
CALL DBMS_SCHEDULER.CREATE_PROGRAM(
'program_dat_size',
'PLSQL_BLOCK',
'call public.p_dat_size()',
'user=system dbname=test port=54321 password=kingbase',
'test',
0,
TRUE,
'this is test program'
);
END;
select * from kdb_schedule.kdb_action;
call dbms_scheduler.drop_program('program_dat_size', true);
--5.创建Schedule
/*
每天早上10点
FREQ =daily;BYHOUR=10;BYBYMINTUE=0;BYSECOND=0;
每隔7天的早上10点
FREQ =daily;INTERVAL=7;BYHOUR=10;BYBYMINTUE=0;BYSECOND=0;
每年10月1号早上10点
FREQ =yearly;BYMONTH=10;BYMONTHDAY=1;BYHOUR=10;BYBYMINTUE=0;BYSECOND=0; freq=minutely;interval=1表示从创建定时任务开始,每分钟执行一次,FREQ可以是YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY,INTERVAL
可指定的值的范围1-99,但目前金仓库FREQ =SECONDLY不生效,实际还是每分钟执行一次。
要实现按秒执行,可以改为'FREQ=MINUTELY;BYSECOND=0,10,20,30,40,50',这样便是每十秒执行一次,目前最快十秒一次,不能设置更短的时间。
其他情况也可以加上BY语句,可以精确控制定时任务执行的时间,例如'FREQ=MONTHLY;BYMONTHDAY=23;BYHOUR=0;BYMINUTE=10;BYSECOND=0'表示每月23日0时10分0秒执行定时任务
*/
*/
--begin ;
-- call dbms_scheduler.create_schedule(schedule_name => 'schedule_dat_size',
-- start_date => now(),
-- --repeat_interval => 'freq=daily;byhour=1;interval=1',
-- repeat_interval => 'freq=minutely;interval=1',
-- end_date => null,
-- comments => 'test schedule');
--end;
BEGIN
CALL DBMS_SCHEDULER.CREATE_SCHEDULE(
'schedule_dat_size',
now(),
'Freq=minutely;BYSECOND=10',
NULL,
'this is test schedule'
);
END;
select * from kdb_schedule.kdb_schedule;
call DBMS_SCHEDULER.DROP_SCHEDULE('schedule_dat_size', true);
--6创建Job
--begin
-- call dbms_scheduler.create_job(job_name => 'job_dat_size',
-- program_name => 'program_dat_size',
-- schedule_name => 'schedule_dat_size',
-- job_class => 'routine maintenance',
-- enabled => true,
-- auto_drop => true,
-- comments => 'test job',
-- credentail_name => null,
-- destination_name => null);
--end;
begin
CALL DBMS_SCHEDULER.CREATE_JOB(
'job_dat_size',
'program_dat_size',
'schedule_dat_size',
'Routine Maintenance',
FALSE,
TRUE,
'this is test job'
);
END;
select * from kdb_schedule.kdb_schedule_job;
select * from kdb_schedule.kdb_job_action;
call DBMS_SCHEDULER.DROP_JOB('job_dat_size', true);
select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes,NOW() from kdb_job;
begin
dbms_scheduler.run_job(job_name => 'job_dat_size');
end;
call p_dat_size();
SELECT count(1) FROM dat_size;
SELECT * FROM kdb_schedule.kdb_jobsteplog;
SELECT * FROM kdb_schedule.kdb_exception;