Oracle计划任务

最新推荐文章于 2025-02-21 10:26:57 发布

ynchyong

最新推荐文章于 2025-02-21 10:26:57 发布

阅读量846

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.youkuaiyun.com/ynchyong/article/details/109050658

版权

数据库专栏收录该内容

13 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中设置计划任务，包括重建索引以优化查询性能和定期释放存储空间以保持数据库高效运行。虽然作者记不清重建索引的任务是否成功执行，但这两个维护操作对于数据库管理至关重要。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

计划任务

 begin  
       dbms_scheduler.create_job (
        job_name            => 'DELETE_Tbl_Log',
        job_type            => 'PLSQL_BLOCK',
        job_action          => 'delete tbl_sys_log where logdate<(to_timestamp( sysdate)-7 );
        commit;',
        start_date          => to_date('01-02-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
        repeat_interval     => 'Freq=Daily;Interval=1;ByHour=00;ByMinute=00;BySecond=00',
        end_date            => to_date(null),
        job_class           => 'DEFAULT_JOB_CLASS',
        enabled             => true,
        auto_drop           => false,
        comments            => '定期删除日志' 
         );
 end;

重建索引（不记得这个有没有成功执行了）

 begin
 dbms_scheduler.create_job (
  job_name => 'Auto_Rebuild_Indexs_JOB', 
  job_type => 'STORED_PROCEDURE',
  job_action => 'auto_rebuild_indexs_unusable', 
  start_date => sysdate,  
  repeat_interval => 'sysdate+10/1440', 
  enabled             => true,
  auto_drop           => false,
  comments => 'Rebuild Indexes件' 
 );
 end;

定期释放存储空间

begin  
       dbms_scheduler.create_job (
        job_name            => 'freeSpace',
        job_type            => 'PLSQL_BLOCK',
        job_action          => 'declare i number; 
  f number:=1;
  begin 
    while f=1 LOOP
      BEGIN  
      select count(session_id) into i  from v$locked_object; 
      if i=0 then 
        begin 
        --执行时间太长
        execute immediate ''ALTER TABLE TBL_REC_RED_LIGHT_AUTO_CAPTURE SHRINK SPACE CASCADE'';
        execute immediate ''ALTER TABLE tbl_rec_custom_manual_capture SHRINK SPACE CASCADE'';
        --execute immediate ''ALTER TABLE TBL_REC_RED_LIGHT_AUTO_CAPTURE MOVE'';
        --execute immediate ''alter index PK_RED_LIGHT_RECORD_ID rebuild'';

        --execute immediate ''ALTER TABLE tbl_rec_custom_manual_capture MOVE'';
        --execute immediate ''alter index PK_CUSTOM_CAPTURE_ID rebuild'';
        --execute immediate ''alter index UN_CUSTOM_CAPTURE_RECORD rebuild'';
        f:=0;  
        --EXCEPTION 
        --   WHEN OTHERS THEN
        --      dbms_output.put_line(sqlcode||sqlerrm);
        --      f:=0;
        end; 
       end if;
      END;
    END LOOP;
end;',
        start_date          => to_date('01-02-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
         -- repeat_interval     => 'Freq=Weekly;Interval=1;ByDay=Sun;ByHour=05;ByMinute=00;BySecond=00',
        repeat_interval     => 'trunc(sysdate)+3+5/24', --每天5点运行 
        --repeat_interval     => 'sysdate+1/1440',  
        end_date            => to_date(null),
        job_class           => 'DEFAULT_JOB_CLASS',
        enabled             => true,
        auto_drop           => false,
        comments            => '释放存储空间，但只能本表空间使用' 
         );
 end;