如何修正SQL查询筛选表名后缀数字小于16284961并排除回收站表

问题原因 #

• 原有正则[0-9]+会匹配表名任意位置的连续数字，Oracle回收站表命名格式为BIN$xxx==$0，名称中包含数字，因此会被误判纳入结果
• 原有正则没有校验「数字必须位于表名末尾、且前面紧跟下划线」的规则，匹配逻辑不严谨

修正后的查询语句 #

SELECT segment_name, bytes/1024/1024 AS "SIZE in MB" 
FROM user_segments 
WHERE segment_type='TABLE' 
  -- 校验表名规则：末尾为下划线加连续数字
  AND REGEXP_LIKE(segment_name, '_[0-9]+$')
  -- 仅提取表名末尾的数字做大小判断
  AND TO_NUMBER(REGEXP_SUBSTR(segment_name, '[0-9]+$')) < 16284961;

如果需要更保险，可主动添加条件排除回收站表：

SELECT segment_name, bytes/1024/1024 AS "SIZE in MB" 
FROM user_segments 
WHERE segment_type='TABLE' 
  AND REGEXP_LIKE(segment_name, '_[0-9]+$')
  AND TO_NUMBER(REGEXP_SUBSTR(segment_name, '[0-9]+$')) < 16284961
  -- 直接过滤Oracle回收站生成的临时表
  AND segment_name NOT LIKE 'BIN$%';

逻辑说明 #

• REGEXP_LIKE(segment_name, '_[0-9]+$') 严格匹配你要求的表名规则，确保数字部分一定在表名末尾且前缀为下划线
• REGEXP_SUBSTR(segment_name, '[0-9]+$') 仅提取表名末尾的连续数字，避免匹配到表名中间的数字导致判断错误
• 可选的NOT LIKE 'BIN$%' 直接过滤回收站临时表，进一步降低误判概率

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