我想定时让 oracle 执行一段sql语句 怎么做
发布网友
发布时间：2022-04-29 15:03
共3个回答
热心网友
时间：2022-04-13 07:46
如：定时删除三分钟前的数据
### 1.创建存储过程SQL> create or replace procere pro_delete_a as
2 begin
3 delete from a t where t.a < (sysdate-3/1440); ###一天1440分钟。即一分钟是 1/1440
4 end;
5 /
Procere created
### 2创建job
SQL> variable job_pro_delete_a number;
SQL> begin
2 dbms_job.submit(:job_pro_delete_a,'pro_delete_a;',sysdate,'sysdate+3/1440');
3 end;
4 /
PL/SQL procere successfully completed
job_pro_delete_a
---------
127
### 3.调用job
SQL> begin
2 dbms_job.run(:job_pro_delete_a);
3 end;
4 /
PL/SQL procere successfully completed
job_pro_delete_a
---------
127
### 4.删除job
SQL> begin
2 dbms_job.remove(:job_pro_delete_a);
3 end;
4 /
Ps：select * from user_jobs;
dbms_job.remove(ID);
热心网友
时间：2022-04-13 09:04
在oracle用户下创建一个脚本cc.sh
vim cc.sh
#!/bin/bash
./home/oracle/.bash_profile
sqlplus scott/tiger<<!
create table t1 as select * from emp where 1=2;
exit;
!
2
将脚本cc.sh加载到计划任务中(比如：每天8:30执行脚本，创建表t1)
crontab -e
30 8 * * * /home/oracle/cc.sh
热心网友
时间：2022-04-13 10:38
