日期：2015-06-28 00:00:00 来源: IT猫扑网
declare
towner varchar2(30);
begin
towner := '&user';
/*
先禁用表上所有的外键约束
*/
for c in (select 'alter table '||owner||'.'||t.table_name || ' disable CONSTRAINT '||t.constraint_name as csql from all_constraints t where t.owner = towner and t.constraint_type='R')
loop
begin
execute immediate c.csql;
dbms_output.put_line(c.csql|| ' ...........√');
exception
when others then
dbms_output.put_line(c.csql||' ..........ⅹ');
end;
end loop;
/*
开始清理表数据
*/
for c in (select owner,object_name,'truncate table '|| owner||'.'||object_name as dsql
from all_objects where object_type='TABLE' and owner = towner) loop
begin
execute immediate c.dsql;
dbms_output.put_line(c.dsql || ' ...........√');
exception
when others then
dbms_output.put_line(c.dsql||' ..........ⅹ');
end;
end loop;
/*
启用表上的外键约束
*/
for c in (select 'alter table '||owner||'.'||table_name || ' enable CONSTRAINT '||t.constraint_name as csql from all_constraints t where t.owner = towner and t.constraint_type='R')
loop
begin
execute immediate c.csql;
dbms_output.put_line(c.csql|| ' ...........√');
exception
when others then
dbms_output.put_line(c.csql||' ..........ⅹ');
end;
end loop;
end;
相关文章
相关下载
网友评论