Oracle 19c中按Schema分组的LISTAGG结果按固定批次拆分方法

核心逻辑 #

• 先对每个Schema下的表生成连续行号
• 用CEIL()函数按指定批次大小计算每个表所属的批次号
• 最终按Schema+批次号双重分组，调用LISTAGG拼接单批次内的表名

可复用SQL代码 #

如果你使用自己维护的SchemaInfo表，批次大小设置为5的写法如下：

SELECT 
  "Schema",
  batch_no,
  LISTAGG(Table_Name, ',') WITHIN GROUP (ORDER BY Table_Name) AS batch_tables
FROM (
  SELECT 
    "Schema",
    Table_Name,
    -- 行号按每5个一组划分批次，调整数字即可修改批次大小
    CEIL(ROW_NUMBER() OVER(PARTITION BY "Schema" ORDER BY Table_Name) / 5) AS batch_no
  FROM SchemaInfo
)
GROUP BY "Schema", batch_no
ORDER BY "Schema", batch_no;

如果是查询Oracle系统表中真实的表信息，替换子查询的表和字段即可：

SELECT 
  owner AS schema_name,
  batch_no,
  LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name) AS batch_tables
FROM (
  SELECT 
    owner,
    table_name,
    CEIL(ROW_NUMBER() OVER(PARTITION BY owner ORDER BY table_name) / 5) AS batch_no
  FROM all_tables
  -- 可加过滤条件，比如WHERE owner IN ('HR','SCOTT')
)
GROUP BY owner, batch_no
ORDER BY owner, batch_no;

输出示例（对应你提供的HR场景） #

常见调整说明 #

• 修改批次大小：将SQL中/5的数字替换为你需要的单批次表数量即可
• 调整分隔符：修改LISTAGG的第一个参数，比如用空格分隔就改为' '
• 处理超长拼接结果：如果单批次表名拼接后超出VARCHAR2长度限制，可改为LISTAGG(Table_Name, ',' ON OVERFLOW TRUNCATE '...')自动截断溢出内容

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