drop table ls_tables / drop table ls_refers / create table ls_tables as select t.table_name, pk.constraint_name pkey, 9999 as listorder from user_tables t, (select constraint_name,table_name from user_constraints where constraint_type='P') pk where t.temporary='N' and t.table_name = pk.table_name (+) / create table ls_refers as select fk.table_name, fk.r_constraint_name from user_constraints fk where fk.constraint_type='R' / create index idx_ls_tables_1 on ls_tables(table_name) / create index idx_ls_refers_1 on ls_refers(r_constraint_name) /declare i number(3):=0; begin loop i := i+1; update ls_tables t set listorder=i where t.listorder=9999 and not exists(select 1 from ls_refers r where r.r_constraint_name=t.pkey); exit when SQL%ROWCOUNT=0; delete ls_refers r where exists(select 1 from ls_tables t where t.listorder=i and t.table_name=r.table_name); end loop; end; /--按listorder的逆序,就是要写入的顺序;按listorder的顺序是要删除的顺序 select * from ls_tables order by listorder desc,table_name;
/
drop table ls_refers
/
create table ls_tables as
select t.table_name, pk.constraint_name pkey, 9999 as listorder
from user_tables t,
(select constraint_name,table_name from user_constraints where constraint_type='P') pk
where t.temporary='N'
and t.table_name = pk.table_name (+)
/
create table ls_refers as
select fk.table_name, fk.r_constraint_name
from user_constraints fk
where fk.constraint_type='R'
/
create index idx_ls_tables_1 on ls_tables(table_name)
/
create index idx_ls_refers_1 on ls_refers(r_constraint_name)
/declare
i number(3):=0;
begin
loop
i := i+1;
update ls_tables t set listorder=i
where t.listorder=9999
and not exists(select 1 from ls_refers r where r.r_constraint_name=t.pkey);
exit when SQL%ROWCOUNT=0;
delete ls_refers r
where exists(select 1 from ls_tables t where t.listorder=i and t.table_name=r.table_name);
end loop;
end;
/--按listorder的逆序,就是要写入的顺序;按listorder的顺序是要删除的顺序
select * from ls_tables order by listorder desc,table_name;