--删除外键约束  

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 '/';
--------------------------下面更好一点 --------
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;