如何确定使用外部表时最新BADFILE的位置与文件名？

我来帮你一步步拆解这个问题，从BADFILE的位置、变量取值获取到竞争条件的规避，都给你理清楚：

一、确定最新BADFILE的存储位置 #

外部表的BADFILE存储位置由两部分决定：

• 默认路径：对应外部表关联的DIRECTORY对象指向的操作系统路径。你可以通过以下SQL查询这个路径：

  SELECT directory_path 
  FROM all_directories 
  WHERE directory_name = (SELECT directory_name FROM user_external_tables WHERE table_name = 'MYTABLE');
  

• 文件名模板：你在创建外部表时指定的BADFILE参数（比如mytable_%a_%p.bad）会直接作为文件名的生成规则，替换变量后就会生成实际的文件名。

如果创建外部表时指定了绝对路径（比如/data/badfiles/mytable_%a_%p.bad），那路径就是你指定的绝对路径，无需结合DIRECTORY对象。

二、获取%a和%p的具体取值 #

%a和%p是Oracle外部表的内置替换变量，含义分别是：

• %a：当前会话的审计ID（Audit ID）
• %p：执行外部表查询的操作系统进程ID（Process ID）

你可以通过以下几种方式获取它们的实际值：

1. 从当前会话直接查询 #

如果你是在执行外部表查询的会话中操作，直接查v$session视图就能拿到：

SELECT 
  auditid AS "%a的值",
  -- 注意：Windows系统中process格式是"进程ID:线程ID"，Linux下是纯进程ID，按需截取
  SUBSTR(process, 1, INSTR(process, ':') - 1) AS "%p的值"
FROM v$session 
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

拿到这两个值后，直接替换模板里的%a和%p，就能得到完整的BAD文件名。

2. 查看已生成的BADFILE #

如果查询已经执行且生成了BADFILE，直接去DIRECTORY对应的操作系统路径下查看文件列表，就能看到替换后的完整文件名（比如mytable_12345_6789.bad），其中数字部分就是%a和%p的取值。

3. 提前预测文件名（自动化场景） #

如果是脚本或自动化任务，可以在执行外部表查询前，先通过PL/SQL获取当前会话的变量值，提前构造出文件名：

DECLARE
  v_auditid NUMBER;
  v_process VARCHAR2(50);
  v_badfile VARCHAR2(200);
BEGIN
  -- 获取当前会话的审计ID和进程ID
  SELECT auditid, process INTO v_auditid, v_process 
  FROM v$session WHERE sid = SYS_CONTEXT('USERENV', 'SID');
  
  -- 处理Windows系统的进程ID格式（去掉线程ID部分）
  IF INSTR(v_process, ':') > 0 THEN
    v_process := SUBSTR(v_process, 1, INSTR(v_process, ':') - 1);
  END IF;
  
  -- 构造完整BAD文件名
  v_badfile := 'mytable_' || v_auditid || '_' || v_process || '.bad';
  DBMS_OUTPUT.PUT_LINE('即将生成的BADFILE: ' || v_badfile);
END;
/

三、关于竞争条件的疑问 #

完全不建议使用固定的mytable.bad！当多个会话同时查询同一个外部表时，多个进程会尝试写入同一个BADFILE，必然会导致文件覆盖、数据损坏等问题，竞争条件是无法避免的。

使用带%a和%p的模板才是正确的做法——每个会话/进程都会生成独立的BADFILE，从根本上避免了竞争。如果你需要找到最新生成的BADFILE，可以通过操作系统的文件修改时间来筛选：

• Linux系统：ls -lt /path/to/directory/mytable_*.bad | head -1
• Windows系统：dir /o-d /b C:\path\to\directory\mytable_*.bad | findstr /v /c:"."

这样就能快速定位到最新的那个BADFILE。

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