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

--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';


未選定行