Oracle 排除非业务表:一份能直接抄的"全量过滤"SQL(最新推荐)_MsSqlserver

在日常的 oracle 开发与运维中，我们经常需要列出某个 schema 下的“真实业务表”。但现实往往很骨感：只要库里启用过全文索引、物化视图、高级队列，甚至只是删过几张表，user_tables 里就会混入一堆系统自动生成的辅助表。

笔者在整理清单时发现，仅靠过滤常见的 bin$%（回收站）和 sys_% 并不够。像外部表临时表 et$%、空间索引 mdxt_% 这类前缀，仅在特定场景下出现，容易被遗漏。为此，笔者将新旧版本中所有系统辅助表前缀做了全量整合，整理出下面这份“最全排除 sql”。

01 | 查 user_tables（最全推荐版）

如果只需要当前用户下、且排除官方标记的二级表和嵌套表的业务表，推荐直接查 user_tables：

select table_name 
from user_tables
where 
  -- 1. 过滤官方标记的二级表与嵌套表
  (secondary is null or secondary = 'n')
  and (nested is null or nested = 'no')
  -- 2. 索引与内部特性辅助表
  and table_name not like 'dr$%'           -- 全文索引 (oracle text)
  and table_name not like 'vector$%'      -- 23ai 向量索引 (hnsw/ivf)
  and table_name not like 'mdxt_%'         -- 空间索引 (spatial index)
  and table_name not like 'sys_%'          -- 系统临时表 / lob 表
  -- 3. 数据同步与高级特性表
  and table_name not like 'aq$%'           -- 高级队列 (advanced queuing)
  and table_name not like 'mlog$%'         -- 物化视图日志
  and table_name not like 'rupd$%'         -- 物化视图更新日志
  and table_name not like 'et$%'           -- 外部表 (external table) 临时表
  and table_name not like 'dm$%'           -- data mining 数据挖掘模型表
  and table_name not like 'annotations_%'  -- 23ai 数据注解表
  -- 4. 开发工具与管理平台辅助表
  and table_name not like 'dbtools$%'      -- database actions / ords 执行历史表
  and table_name not like 'sqldev$%'       -- sql developer 辅助表
  -- 5. 系统垃圾与回收站
  and table_name not like 'bin$%'         -- 回收站表 (recycle bin)
  order by table_name;

02 | 查 user_objects（全量过滤版）

如果想覆盖当前用户可见的所有对象，可以改用 user_objects 视图，比如最常见的，需要过滤出业务的表以及视图：

select object_name as table_name, object_type as table_type
from user_objects
where object_type in ('table', 'view')
  -- 1. 过滤所有系统/后台标记为自动生成的对象
  and generated = 'n'
  -- 2. 索引与内部特性辅助表（以防某些版本或场景下 generated 为 n）
  and object_name not like 'dr$%'           -- 全文索引 (oracle text)
  and object_name not like 'vector$%'      -- 23ai 向量索引 (hnsw/ivf)
  and object_name not like 'mdxt_%'         -- 空间索引 (spatial index)
  and object_name not like 'sys_%'          -- 系统临时表 / lob 表
  -- 3. 数据同步、工具与高级特性表
  and object_name not like 'aq$%'           -- 高级队列 (advanced queuing)
  and object_name not like 'mlog$%'         -- 物化视图日志
  and object_name not like 'rupd$%'         -- 物化视图更新日志
  and object_name not like 'et$%'           -- 外部表 (external table) 临时表
  and object_name not like 'dm$%'           -- data mining 数据挖掘模型表
  and object_name not like 'annotations_%'  -- 23ai 数据注解表
  -- 4. 开发工具与管理平台辅助表
  and object_name not like 'dbtools$%'      -- database actions / ords 执行历史表
  and object_name not like 'sqldev$%'       -- sql developer 辅助表
  -- 5. 系统视图 & 回收站
  and object_name not like 'metadata_%'     -- 23ai 元数据注解相关视图
  and object_name not like 'mview_%'        -- 物化视图系统视图
  and object_name not like 'logmnr_%'       -- logminer 系统视图
  and object_name not like 'olap_%'         -- olap 特性系统视图
  and object_name not like 'bin$%'         -- 数据库回收站 (recycle bin)
  order by table_type, table_name;

03 | 核心汇总对照清单

这份对照表可以直接保存为开发规范，方便日后排查：

过滤前缀	对应功能/模块	产生场景
dr$%	全文索引 (oracle text)	创建 context 类型的全文索引
vector$%	ai 向量检索 (23ai)	创建 vector 类型字段的 hnsw/ivf 索引
mdxt_%	空间索引 (spatial index)	创建 sdo_geometry 空间索引
sys_%	系统底层/lob段/临时表	表中包含 clob/blob 或在线重定义
aq$%	高级队列 (advanced queuing)	使用 oracle 内部消息队列
mlog$% / rupd$%	物化视图日志 (mview log)	为表创建了增量刷新的物化视图日志
et$%	外部表 (external table)	访问外部文件时生成的临时控制表
dm$%	数据挖掘 / 机器学习 (oml)	在数据库内训练/运行 ml 模型
annotations_%	数据注解 (23ai)	给表或列添加元数据 annotation
dbtools$% / sqldev$%	web/客户端工具控制表	使用 ords、sql developer web 等工具
bin$%	数据库回收站	删表（drop table）后留在回收站的数据