如何批量脚本停用（启用）SQL Server 和 ORACLE 数据库用户作业

关于如何批量脚本停用（启用）SQL Server数据库用户作业：

（执行脚本之后的到的文本结果，拿到查询分析器上运行。如果需要针对原来已经停用的作业批量启用仅需要调整一下脚本enabled字段的值即可）

DECLARE @job_name varchar(1000)
DECLARE cv_sysjobs CURSOR FOR  
select j.name from msdb.dbo.sysjobs j
where j.enabled = 1
ORDER BY j.name;

OPEN cv_sysjobs
FETCH NEXT FROM cv_sysjobs INTO @job_name
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'msdb..sp_update_job @job_name = N''' + @job_name + ''' , @enabled = 0;'
    PRINT 'GO'
    FETCH NEXT FROM cv_sysjobs INTO @job_name;
END
CLOSE cv_sysjobs  
DEALLOCATE cv_sysjobs

ORACLE的批量停用（启用）数据库调度作业：

注：如下面脚本执行没有输出的话，先打开SERVEROUTPUT选项：

set serveroutput on;

declare
  row_job ALL_SCHEDULER_JOBS%rowtype;
  cursor cur_job
  is select * from ALL_SCHEDULER_JOBS where "ENABLED"='TRUE' AND OWNER in ('APPS','CONCEPT','CONFIG','DATA','ESB','FIAB','GZRHIN','POOR','PORX','PRPA');
begin
  open cur_job;
  loop
    fetch cur_job into row_job;
    exit when cur_job%notfound;
    dbms_output.put_line('begin');
    dbms_output.put_line('  sys.dbms_scheduler.disable(name => ''' || row_job.OWNER || '.' || row_job.JOB_NAME || ''');');
    dbms_output.put_line('end;');
    dbms_output.put_line('/');
  end loop;
  close cur_job;
end;
/

-- 查看job的运行日志
SELECT T.OWNER, T.JOB_NAME, T.STATUS, T.ERROR#, T.ERRORS, T.REQ_START_DATE, T.ACTUAL_START_DATE, T.RUN_DURATION, T.*
FROM DBA_SCHEDULER_JOB_RUN_DETAILS T
--WHERE T.JOB_NAME = 'JOB_BOOK1' 
WHERE T.ACTUAL_START_DATE > SYSDATE-0.01 
ORDER BY T.LOG_DATE DESC;

-- 查看job的最后运行时间
SELECT OWNER, JOB_NAME, STATUS, ERROR#, ERRORS, MAX(LOG_DATE) 最后运行时间
FROM DBA_SCHEDULER_JOB_RUN_DETAILS T
WHERE ACTUAL_START_DATE >TO_DATE('2023-04-01 00:00:00','yyyy-mm-dd HH24:mi:ss')  
GROUP BY OWNER,JOB_NAME,STATUS,ERROR#,ERRORS
ORDER BY MAX(LOG_DATE) DESC;

-- 有什么作业在跑
SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS;