Oracle 11g查询前端工作流事务表及从V$sql/v$sqlarea提取表名方法

先说明确结论：不建议直接从V$SQL或V$SQLAREA的SQL_TEXT字段手动提取表名，误差和遗漏问题非常严重，优先用Oracle内置解析结果实现需求。

直接提取SQL_TEXT的缺陷 #

• V$SQL和V$SQLAREA的SQL_TEXT字段仅存储SQL前1000个字符，超过长度的长SQL会被截断，无法获取完整语句内容
• 手动字符串匹配无法处理SQL语法中的别名、嵌套子查询、关联查询、同义词、动态拼接、注释内容等场景，很容易出现误判、漏判
• 两个视图仅存储当前还在共享池缓存中的SQL，已经被缓存淘汰的历史执行SQL无法查询到，统计结果不完整

更可靠的实现方式 #

如果只需要统计当前缓存中留存的DML语句涉及的业务表，可以直接关联V$SQL_PLAN视图获取Oracle官方解析出的操作对象，准确率远高于自行解析SQL文本，参考查询语句如下：

SELECT DISTINCT 
  p.OBJECT_OWNER AS 表所属用户,
  p.OBJECT_NAME AS 涉及表名,
  s.SQL_TEXT AS 对应SQL片段
FROM V$SQL s
INNER JOIN V$SQL_PLAN p 
  ON s.SQL_ID = p.SQL_ID 
  AND s.CHILD_NUMBER = p.CHILD_NUMBER
WHERE 
  -- 过滤SELECT/INSERT/UPDATE/DELETE四类DML操作
  s.COMMAND_TYPE IN (2,3,6,7)
  -- 过滤表访问操作节点
  AND p.OPERATION = 'TABLE ACCESS'
  -- 替换为你的业务用户，过滤系统表
  AND p.OBJECT_OWNER = 'YOUR_BUSINESS_SCHEMA'
ORDER BY p.OBJECT_OWNER, p.OBJECT_NAME;

如果需要统计全量所有执行过的DML涉及的表，建议开启Oracle审计功能，或使用LOGMNR工具分析归档重做日志，可覆盖所有历史操作记录，不会出现遗漏。

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