如何在本地文件夹生成spool文件及批量删除有主外键约束的表

你可以直接在SQL*Plus、PL/SQL Developer命令窗口、SQLcl等支持spool功能的Oracle客户端中执行以下脚本，自行替换路径和文件名即可：

-- 关闭冗余输出，避免生成的SQL脚本混入无效内容
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
-- 此处替换为你预先指定的本地存储路径和自定义文件名，Windows路径格式示例：D:\sql_script\drop_scott_tables.sql
spool /opt/oracle/script/drop_scott_tables.sql

declare
 lv_str varchar2(1000);
begin
for c in(select distinct a.table_name as table_name, 
                b.table_name as parent_table_name ,
                a.owner
           from all_constraints a, 
                all_constraints b 
          where a.r_constraint_name = b.constraint_name 
            and a.constraint_type   = 'R'
            and b.constraint_type   = 'P'
            and a.owner             = b.owner
            and a.owner             = 'SCOTT'
       ) loop
 lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- 依赖父表：' || c.parent_table_name;
 dbms_output.put_Line (lv_str );
 end loop;
end;
/

spool off
-- 恢复默认输出设置
set feedback on
set heading on

注意：需确保运行客户端的系统用户对指定的存储路径有读写权限，否则会生成文件失败。

删除存在外键关联的表时，绝对不能先删除父表，直接删除父表会触发ORA-02449外键依赖报错，必须先删除所有引用该父表的子表，或者删除父表时加上CASCADE CONSTRAINTS参数自动删除关联外键约束后再删除父表。

如果需要基于原有代码同时生成子表和父表的删除语句，可以使用修改后的脚本：

set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
spool /opt/oracle/script/drop_all_scott_tables.sql

declare
 lv_str varchar2(1000);
 -- 定义集合存储已生成删除语句的表，避免重复生成删除命令
 type table_set is table of varchar2(100) index by varchar2(100);
 v_dropped_tables table_set;
begin
-- 先生成所有子表的删除语句
for c in(select distinct a.table_name as table_name, 
                b.table_name as parent_table_name ,
                a.owner
           from all_constraints a, 
                all_constraints b 
          where a.r_constraint_name = b.constraint_name 
            and a.constraint_type   = 'R'
            and b.constraint_type   = 'P'
            and a.owner             = b.owner
            and a.owner             = 'SCOTT'
       ) loop
  if not v_dropped_tables.exists(c.owner||'.'||c.table_name) then
    lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- 子表，依赖父表：' || c.parent_table_name;
    dbms_output.put_Line (lv_str );
    v_dropped_tables(c.owner||'.'||c.table_name) := 'Y';
  end if;
  -- 子表删除语句生成完成后，生成对应父表的删除语句
  if not v_dropped_tables.exists(c.owner||'.'||c.parent_table_name) then
    lv_str :='DROP TABLE ' || c.owner || '.' || c.parent_table_name || '; -- 父表，对应子表：' || c.table_name;
    dbms_output.put_Line (lv_str );
    v_dropped_tables(c.owner||'.'||c.parent_table_name) := 'Y';
  end if;
 end loop;
end;
/

spool off
set feedback on
set heading on

如果你不需要严格按照先子后父的顺序删除，也可以直接在DROP语句后加CASCADE CONSTRAINTS参数，示例：DROP TABLE SCOTT.PARENT_TABLE CASCADE CONSTRAINTS;，执行时会自动删除关联的外键约束，不会触发依赖报错，适合不需要保留外键约束的场景。

内容的提问来源于stack exchange，提问作者AlbertAlex