RAC中指定job运行实例

统计RAC库晚上运行了很多job，由于没给job指定固定的实例。部分job会

同时在一个实例上运行。导致job不能按时完成。通过dbms_job.instance()

将job固定在某个实例上，避免多个job同时被分配到一个实例上。

 

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as careport

--1.建表
SQL> DROP TABLE T_JOB;
Table dropped

 

--2建表t_job.分别存储实例编号，实例名，系统时间
SQL> CREATE TABLE t_job as
  2  SELECT INSTANCE_NUMBER,INSTANCE_NAME ,sysdate  c_date
  3  FROM v$instance;
Table created

 

 

--3建存储过程
SQL> CREATE OR REPLACE PROCEDURE   job_inst IS
  2  BEGIN
  3     execute immediate('truncate table t_job');
  4     execute immediate ('insert into t_job
  5      select INSTANCE_NUMBER,INSTANCE_NAME ,sysdate  from v$instance');
  6     commit;
  7  END job_inst;
  8  /
Procedure created

 

--4建job

SQL> variable jobno number;
SQL> begin
  2  sys.dbms_job.submit(job => :jobno,
  3                      what => 'job_inst;',
  4                      next_date => to_date('2010-12-01 15:07:00', 'yyyy-mm-dd hh24:mi:ss'),
  5                      interval => 'TRUNC(SYSDATE+1)+15/24');
  6    commit;
  7  end;
  8  /
PL/SQL procedure successfully completed
jobno
---------
292

 

--5.查询job建好后默认分在那个实例。默认instance是0
SQL> select job ,what ,instance  from dba_jobs where job=292;

       JOB WHAT         INSTANCE
---------- ---------- ----------
       292 job_inst;           0

 

--6.指定292号job在实例3上执行
SQL> exec dbms_job.instance(292,3);
PL/SQL procedure successfully completed

 

--7.已经指定成功
SQL> select job ,what ,instance  from dba_jobs where job=292;

       JOB WHAT         INSTANCE
---------- ---------- ----------
       292 job_inst;           3

 

--8. 等job运行后，查询该t_job表发现292号job已经在实例3上运行  
SQL> select * from t_job;

INSTANCE_NUMBER INSTANCE_NAME    C_DATE
--------------- ---------------- --------------------
              3 caprod3          2010-12-01 15:07:00