系统迁移时关于JOB,TRIGGER,CONSTRAINT,sequence的处理

做系统迁移的时候，系统中的一些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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1062481/