Ogg禁用job,外键,triger脚本

rem Before begin with replication, DBA should disable such Oracle objects as triggers, 
rem foreign key constraints, cascading deleting contraints, check constraints and so on 


set echo off
set verify off
set feedback off
set pagesize 10000
set heading off
set lines 100


spool disable_constraints1.sql


select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
  from dba_constraints
    where constraint_type in ('R','C') and owner='SIHANGYUAN'
  order by status,owner;


spool off


spool disable_triggers.sql


select 'alter trigger '||owner||'.'||trigger_name||' disable;'
  from dba_triggers where owner in ('TIANJIN');
  order by status,owner;


spool off


spool denies_users.sql


select 'alter user '||USERNAME||' account lock;'
  from splex.dr_users;


spool off


spool disable_job.sql


select 'execute DBMS_IJOB.BROKEN('||job||',TRUE); commit;'
  from dba_jobs
  where priv_user<>'SYS' and priv_user<>'system' and priv_user<>'splex' and  priv_user IN ('TIANJIN');


13002486632  任成




truncate table splex2300.dr_constraints;


insert into splex2300.dr_constraints
SELECT 
  owner,
  constraint_name,
  constraint_type,
  table_name,
  status
FROM 
  dba_constraints
where constraint_type= 'R' 
and   owner in ('TIANJIN');


commit;


insert into splex2300.dr_constraints
SELECT 
  owner,
  constraint_name,
  constraint_type,
  table_name,
  status
FROM 
  dba_constraints
where constraint_type in ('R','C')
and  owner in ('SIHANGYUAN') and STATUS='ENABLED';


commit;


sanhangju


truncate table splex2300.dr_triggers;


insert into splex2300.dr_triggers 
SELECT 
  owner,
  table_owner,
  trigger_name,
  trigger_type,
  table_name,
  status
FROM 
  dba_triggers
where owner in ('SIHANGYUAN');


commit;


truncate table splex2300.dr_jobs;


insert into splex2300.dr_jobs
select job,log_user,priv_user,schema_user,next_date,broken
from dba_jobs
where priv_user IN ('SIHANGYUAN');


commit;
hbetc


truncate table splex2300.dr_users;


insert into splex2300.dr_users
select username,account_status
from dba_users
where username IN ('LIS');


commit;

spool off