Oracle 11g跨Schema扫描ETL_SOURCE_SYSTEM列的统计实现

• 基于dba_tab_cols定位目标列的思路：核心逻辑完全可行，仅需修正两个细节即可使用：
  • 原查询使用column_name like '%ETL_SOURCE_SYSTEM%'会误匹配带前后缀的非目标列（如ETL_SOURCE_SYSTEM_OLD、TEST_ETL_SOURCE_SYSTEM），需改为精确等值匹配column_name = 'ETL_SOURCE_SYSTEM'
  • 需补充过滤非业务系统Schema、指定表类型（排除视图、临时表等非目标对象），避免统计无关内容
• 拼接全量UNION ALL汇总取值的思路：在数千表规模下不可行，原因是Oracle 11g单条SQL语句最大长度约128KB，数千张表拼接的UNION ALL语句会直接触发长度限制报错；同时一次性扫描所有大表会产生极高IO开销，执行效率极低

步骤1：获取全量目标业务表清单 #

先通过修正后的数据字典查询，导出所有含目标列的业务表基础信息，示例代码：

SELECT 
  owner AS schema_name,
  table_name
FROM dba_tab_cols
WHERE column_name = 'ETL_SOURCE_SYSTEM'
  -- 补充需要排除的非业务系统Schema，可根据实际环境增减
  AND owner NOT IN (
    'SYS','PUBLIC','SYSTEM','OUTLN','DBSNMP','WMSYS',
    'XDB','ORDSYS','MDSYS','APEX_030200','APPQOSSYS',
    'DIP','ORACLE_OCM','SI_INFORMTN_SCHEMA'
  )
  AND object_type = 'TABLE' -- 仅统计普通业务表，排除视图、物化视图等
ORDER BY owner, table_name;

注意：执行该查询需账号拥有SELECT ANY DICTIONARY权限或DBA权限，无权限时可替换为all_tab_cols，仅能统计当前账号有权限访问的表

步骤2：分场景实现统计逻辑 #

场景A：需统计实际数据中出现过该系统编号的关联表数 #

即仅当某张表的ETL_SOURCE_SYSTEM字段实际存储过某个系统编号时，才计入该系统的关联表数量，此时逐表执行统计，避免全量拼UNION ALL：

1. 先建中间汇总表，存储每张表实际存在的系统编号

CREATE TABLE dwh_ss_table_stat (
  schema_name VARCHAR2(30),
  table_name VARCHAR2(30),
  source_system_id NUMBER(3),
  CONSTRAINT pk_dwh_ss_table_stat PRIMARY KEY (schema_name, table_name, source_system_id)
);

2. 用PL/SQL动态块遍历所有目标表，逐表抽取非空的系统编号插入中间表，示例代码：

DECLARE
  v_sql CLOB;
BEGIN
  -- 清空历史统计数据
  EXECUTE IMMEDIATE 'TRUNCATE TABLE dwh_ss_table_stat';

  FOR rec IN (
    SELECT owner, table_name
    FROM dba_tab_cols
    WHERE column_name = 'ETL_SOURCE_SYSTEM'
      AND owner NOT IN (
        'SYS','PUBLIC','SYSTEM','OUTLN','DBSNMP','WMSYS',
        'XDB','ORDSYS','MDSYS','APEX_030200','APPQOSSYS',
        'DIP','ORACLE_OCM','SI_INFORMTN_SCHEMA'
      )
      AND object_type = 'TABLE'
  ) LOOP
    BEGIN
      -- 拼接动态SQL，插入当前表存在的所有非空系统编号
      v_sql := '
        INSERT INTO dwh_ss_table_stat (schema_name, table_name, source_system_id)
        SELECT DISTINCT :1, :2, ETL_SOURCE_SYSTEM
        FROM "'||rec.owner||'"."'||rec.table_name||'"
        WHERE ETL_SOURCE_SYSTEM IS NOT NULL
      ';
      EXECUTE IMMEDIATE v_sql USING rec.owner, rec.table_name;
      COMMIT; -- 可调整为每N张表提交一次，减少事务日志占用
    EXCEPTION
      WHEN OTHERS THEN
        -- 捕获单表异常，避免中断全量统计
        DBMS_OUTPUT.PUT_LINE('跳过异常表：'||rec.owner||'.'||rec.table_name||'，错误原因：'||SQLERRM);
    END;
  END LOOP;
END;
/

3. 关联系统映射lookup表（假设表名为dim_source_system，字段为system_id、system_name）输出最终统计结果：

SELECT 
  s.schema_name,
  COUNT(DISTINCT s.table_name) AS related_table_cnt,
  d.system_name,
  s.source_system_id
FROM dwh_ss_table_stat s
JOIN dim_source_system d 
  ON s.source_system_id = d.system_id
GROUP BY s.schema_name, d.system_name, s.source_system_id
ORDER BY s.schema_name, s.source_system_id;

场景B：仅需统计含目标列的表关联的系统数 #

即只要表存在ETL_SOURCE_SYSTEM列，即默认关联所有1-100的系统编号（无需扫描实际数据），可直接通过数据字典关联lookup表输出，无需扫表，性能极高：

SELECT 
  c.owner AS schema_name,
  COUNT(*) AS related_table_cnt,
  d.system_name,
  d.system_id
FROM dba_tab_cols c
CROSS JOIN dim_source_system d
WHERE c.column_name = 'ETL_SOURCE_SYSTEM'
  AND c.owner NOT IN (
    'SYS','PUBLIC','SYSTEM','OUTLN','DBSNMP','WMSYS',
    'XDB','ORDSYS','MDSYS','APEX_030200','APPQOSSYS',
    'DIP','ORACLE_OCM','SI_INFORMTN_SCHEMA'
  )
  AND c.object_type = 'TABLE'
GROUP BY c.owner, d.system_name, d.system_id
ORDER BY c.owner, d.system_id;

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