系統遷移時關於JOB,TRIGGER,CONSTRAINT,sequence的處理

wzz123snow發表於2013-12-06
AI
做系統遷移的時候，系統中的一些JOB會自動產生資料，為保證資料同步，會將JOB先停下來。待完全遷移過後，再將JOB開啟。
這裡將停JOB,TRIGGER,CONSTRAINT,sequence的指令碼保留下來，作為記錄。
另外，之前使用GOLDENGATE做資料同步的時候，也需要做這個操作。
--disable trigger
SQL> select count(1),status from dba_triggers where owner='S8_ARCADE' group by status;


  COUNT(1) STATUS
---------- ----------------
        35 DISABLED
       118 ENABLED


SQL> declare
  2  v_sql varchar2(2000);
  3  cursor c_trigger is
  4  select 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers where owner in ('S8_ARCADE');
  5  begin
  6  OPEN c_trigger;
  7  loop
  8  fetch c_trigger into v_sql;
  9  exit when c_trigger%NOTFOUND;
 10  execute immediate v_sql;
 11  end loop;
 12  end;
 13  /


PL/SQL 過程已成功完成。



SQL> select count(1),status from dba_triggers where owner='S8_ARCADE' group by status;


  COUNT(1) STATUS
---------- ----------------
       153 DISABLED
--disable constraints


SQL> select count(1),status   from dba_constraints
  2   where constraint_type = 'R'
  3     and owner in ('S8_ARCADE') group by status;


  COUNT(1) STATUS
---------- ----------------
        15 DISABLED
         5 ENABLED


SQL> declare
  2  v_sql varchar2(2000);
  3  cursor c_ref is
  4  select 'alter table ' || owner || '.' || table_name ||' disable constraint '||constraint_name from dba_constraints where constraint_type = 'R' and owner in ('S8_ARCADE');
  5  begin
  6  open c_ref;
  7  loop
  8  fetch c_ref into v_sql;
  9  exit when c_ref%NOTFOUND;
 10  execute immediate v_sql;
 11  end loop;
 12  close c_ref;
 13  end;
 14  /


PL/SQL procedure successfully completed.

SQL> select count(1),status   from dba_constraints
  2   where constraint_type = 'R'
  3     and owner in ('S8_ARCADE') group by status;


  COUNT(1) STATUS
---------- ----------------
        20 DISABLED




--disable job
SQL> select count(1),broken from dba_jobs where schema_user='S8_ARCADE' group by broken;


  COUNT(1) BR
---------- --
        19 Y
         2 N

declare 
  cursor jl is select * from dba_jobs where log_user='S8_ARCADE';
begin
  for r in jl loop
    dbms_job.broken(r.job,true);   
  end loop;
  commit;
end;


SQL> select count(1),broken from dba_jobs where schema_user='S8_ARCADE' group by broken;


  COUNT(1) BR
---------- --
        21 Y


--關於JOB的過程需要以S8_ARCADE使用者執行，否則會提示如下錯誤：


ORA-23421: job number 1 is not a job in the job queue

--drop sequence
SQL> select sequence_name from dba_sequences where sequence_owner='WZZ';


SEQUENCE_NAME
------------------------------------------------------------
SEQ1
SEQ2

SQL> declare
  2  v_sql varchar2(200);
  3  begin
  4  for c in (select sequence_name from dba_sequences where sequence_owner='WZZ')
  5  loop
  6  v_sql:='drop sequence wzz.'||c.sequence_name||'';
  7  execute immediate v_sql;
  8  end loop;
  9  end;
 10  /


PL/SQL 過程已成功完成。


SQL> select sequence_name from dba_sequences where sequence_owner='WZZ';


未選定行





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26084062/viewspace-1062481/，如需轉載，請註明出處，否則將追究法律責任。

相關文章