--删除外键约束 select 'Alter table '||TABLE_NAME||' '||'drop constraint'||' '|| CONSTRAINT_NAME||';' from user_constraints where CONSTRAINT_TYPE ='R' and TABLE_NAME like 'TA/_%' escape '/'; --删除表名 select 'drop table '||TABLE_NAME||' '||';' from user_tables where TABLE_NAME like 'TA/_%' escape '/';--执行上面两个查询出的sql语句就ok了。
1楼的ID让我想起来2年前刚接触oracle的时候,在百度知道上遇到好心人。呵呵 不知道是不是同一个人哦 在1楼的基础上:declare v_sql varchar2(4000); cursor cur1 is select 'Alter table ' || TABLE_NAME || ' ' || 'drop constraint' || ' ' || CONSTRAINT_NAME || ';' from user_constraints where CONSTRAINT_TYPE = 'R' and TABLE_NAME like 'TA/_%' escape '/'; cursor cur2 is select 'drop table ' || TABLE_NAME || ' ' || ';' from user_tables where TABLE_NAME like 'TA/_%' escape '/';begin open cur1; loop fetch cur1 into v_sql; exit when cur1%notfound; execute immediate v_sql; end loop; close cur1; open cur2; loop fetch cur2 into v_sql; exit when cur2%notfound; execute immediate v_sql; end loop; close cur2;end;
select 'Alter table '||TABLE_NAME||' '||'drop constraint'||' '|| CONSTRAINT_NAME||';'
from user_constraints
where CONSTRAINT_TYPE ='R'
and TABLE_NAME like 'TA/_%' escape '/';
--删除表名
select 'drop table '||TABLE_NAME||' '||';'
from user_tables
where TABLE_NAME like 'TA/_%' escape '/';--执行上面两个查询出的sql语句就ok了。
在1楼的基础上:declare
v_sql varchar2(4000);
cursor cur1 is
select 'Alter table ' || TABLE_NAME || ' ' || 'drop constraint' || ' ' ||
CONSTRAINT_NAME || ';'
from user_constraints
where CONSTRAINT_TYPE = 'R'
and TABLE_NAME like 'TA/_%' escape '/'; cursor cur2 is
select 'drop table ' || TABLE_NAME || ' ' || ';'
from user_tables
where TABLE_NAME like 'TA/_%' escape '/';begin
open cur1;
loop
fetch cur1
into v_sql;
exit when cur1%notfound;
execute immediate v_sql;
end loop;
close cur1;
open cur2;
loop
fetch cur2
into v_sql;
exit when cur2%notfound;
execute immediate v_sql;
end loop;
close cur2;end;
在删除表中如果表中有约束条件一定要先删除约束条件,然后才能删除表,否则会提示楼主的错误信息。
user_tables
别搞那么复杂,还先删除啥约束。