Oracle技术问询：如何确定模式下表和物化视图的内外部数据源

场景说明 #

我为客户提供Oracle模式，客户可基于我另一模式中的表创建视图访问数据，同时还会使用第三方数据源/连接为其表、物化视图（MViews）等补充数据。

问题 #

是否有方法确定这些连接/数据源？能否提供相关查询语句，甚至获取数据使用量或仅识别连接？我曾检索网络未找到有效方案，目前尝试的如下查询并非合适解决方案：

select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

该查询仅能列出Oracle本地的活跃用户会话，无法识别第三方数据源或远程连接。

解决方案 #

针对不同类型的第三方数据源/连接，可使用以下查询语句：

1. 识别数据库链接（DB Links） #

数据库链接是Oracle访问远程数据库的核心方式，通过以下查询可查看已定义的链接：

-- 管理员权限：查看所有数据库链接
SELECT owner, db_link, username, host, created
FROM dba_db_links
ORDER BY owner, db_link;

-- 当前用户权限：查看自身拥有的数据库链接
SELECT db_link, username, host, created
FROM user_db_links;

host字段包含远程数据源的连接字符串，可直接识别目标地址。

2. 识别基于远程数据源的物化视图 #

若物化视图的数据来自远程数据源，其查询语句中会包含@数据库链接名，可通过以下语句筛选：

SELECT owner, mview_name, query, last_refresh_date
FROM dba_mviews
WHERE query LIKE '%@%';

3. 识别外部表（External Tables） #

外部表直接读取Oracle外部的文件系统或数据源，可通过以下查询获取其配置信息：

SELECT owner, table_name, type_owner, type_name, default_directory, access_parameters
FROM dba_external_tables
ORDER BY owner, table_name;

access_parameters字段包含外部数据源的具体配置（如文件路径、格式规则等）。

4. 监控正在使用的远程连接 #

如果需要追踪当前活跃的远程连接会话，可关联v$session和v$dblink视图：

SELECT s.sid, s.serial#, s.username, s.machine, d.db_link, d.owner_id
FROM v$session s
JOIN v$dblink d ON s.saddr = d.saddr
WHERE s.type = 'USER';

5. 获取远程数据源的数据使用量 #

通过v$sql视图可追踪包含远程访问的SQL语句，统计其执行次数和数据处理量：

SELECT sql_text, executions, rows_processed, cpu_time, elapsed_time
FROM v$sql
WHERE sql_text LIKE '%@%'
ORDER BY executions DESC;

若需要更精确的统计，可开启Oracle审计功能，或生成AWR/ASH报告分析长期访问情况。

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