<think>我们有两个视图:v$session 和 v$active_session_history。用户想要在查询这些视图时同时显示SQL的文本内容。
在Oracle中,SQL文本存储在v$sql或v$sqlarea等视图中。我们可以通过SQL_ID(在v$session和v$active_session_history中都有)来关联这些视图,从而获取SQL文本。
注意:v$active_session_history中存储的是历史活动会话信息,而v$session是当前会话信息。
对于v$session,我们可以这样写:
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.module, s.client_info,
q.sql_text
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE';
但是注意,一个SQL_ID可能对应多个子游标(child cursor),但通常我们取其中一个即可(比如通过sql_id和child_number),或者使用v$sqlarea(它按sql_id聚合,但只显示一个子游标的sql_text)。不过,v$sqlarea中sql_text可能是截断的(只显示前1000字符),而v$sql中sql_fulltext是完整的(CLOB类型,但显示时可能受限于工具)。
另外,v$active_session_history中,每个活动会话每秒采样一次,可能同一个会话在多次采样中执行不同的SQL,所以我们需要根据SQL_ID来关联。
对于v$active_session_history的查询:
SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id,
ash.sql_exec_id, ash.event, ash.wait_class,
q.sql_text
FROM v$active_session_history ash
LEFT JOIN v$sql q ON ash.sql_id = q.sql_id
WHERE ash.sample_time BETWEEN ... AND ...;
但是,v$active_session_history中可能包含历史数据,而v$sql中可能已经没有对应的SQL(因为SQL游标被刷出共享池)。为了获取更久远的SQL文本,我们可以关联dba_hist_sqltext(需要AWR许可,并且已经做过快照)。
因此,如果我们想要查询v$active_session_history并且显示SQL文本,有两种情况:
1. 如果SQL还在共享池中(v$sql中还存在),则从v$sql中获取。
2. 如果SQL已经不在共享池中,则从dba_hist_sqltext中获取(前提是AWR快照已经捕获)。
但是,由于用户没有特别说明历史数据,我们先提供当前共享池中能关联到的SQL文本。
另外,注意v$sql中的sql_text是前1000个字符,而sql_fulltext是完整的SQL文本(CLOB)。我们可以使用DBMS_LOB.SUBSTR来截取,或者根据工具支持直接显示。
以下提供两个查询示例:
1. 查询v$session并显示SQL文本(当前正在执行的SQL):
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.module, s.client_info,
s.sql_id,
q.sql_text, -- 前1000个字符
DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS sql_fulltext -- 显示最多4000字符
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE';
2. 查询v$active_session_history并显示SQL文本(注意:v$active_session_history中的SQL_ID可能为null,表示当时没有执行SQL):
SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id,
ash.sql_exec_id, ash.event, ash.wait_class,
q.sql_text,
DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS sql_fulltext
FROM v$active_session_history ash
LEFT JOIN v$sql q ON ash.sql_id = q.sql_id
WHERE ash.sample_time > SYSDATE - INTERVAL '1' HOUR -- 最近一小时
ORDER BY ash.sample_time DESC;
但是,如果v$sql中已经没有了,我们可以尝试关联dba_hist_sqltext(需要AWR许可):
SELECT ash.sample_id, ash.sample_time, ash.session_id, ash.session_serial#, ash.sql_id,
ash.sql_exec_id, ash.event, ash.wait_class,
COALESCE(q.sql_text, h.sql_text) AS sql_text
FROM v$active_session_history ash
LEFT JOIN v$sql q ON ash.sql_id = q.sql_id
LEFT JOIN dba_hist_sqltext h ON ash.sql_id = h.sql_id
WHERE ash.sample_time > SYSDATE - INTERVAL '1' HOUR
ORDER BY ash.sample_time DESC;
注意:dba_hist_sqltext中的sql_text是完整的SQL文本(VARCHAR2类型,最多4000字符),如果SQL文本超过4000字符,则会被截断。而v$sql中的sql_fulltext是CLOB,可以显示更长的文本(但我们在查询时通过DBMS_LOB.SUBSTR转换为4000字符)。
另外,如果用户没有AWR许可,则不能使用dba_hist_sqltext。
因此,我们给出一个通用的,只使用v$sql的查询。
最后,注意权限:用户需要被授予SELECT_CATALOG_ROLE,或者直接授予对v$session, v$sql, v$active_session_history, dba_hist_sqltext的查询权限。
由于用户的问题中提到了“现有的Oracle性能视图查询”,我们假设用户已经有查询这些视图的权限。
现在,根据用户提供的引用,我们知道v$active_session_history是内存中的,重启会消失,而dba_hist_active_sess_history是持久化的。但是,我们这里只需要关联SQL文本,所以用dba_hist_sqltext就可以。
总结:在查询v$session和v$active_session_history时,通过关联v$sql或dba_hist_sqltext来获取SQL文本。</think>### Oracle 查询添加 SQL 文本内容的方法
在 Oracle 中,可以通过关联 `v$sql` 或 `dba_hist_sqltext` 视图来获取 SQL 文本内容。以下是具体实现方法:
#### 1. 查询 `v$active_session_history` + SQL 文本
```sql
SELECT
ash.sample_id,
ash.sample_time,
ash.session_id,
ash.sql_id,
ash.event,
ash.wait_class,
ash.machine AS "客户端主机",
ash.program AS "客户端程序",
s.sql_text AS "SQL文本",
DBMS_LOB.SUBSTR(s.sql_fulltext, 4000, 1) AS "完整SQL文本"
FROM
v$active_session_history ash
LEFT JOIN
v$sql s ON ash.sql_id = s.sql_id
WHERE
ash.sample_time > SYSDATE - INTERVAL '1' HOUR
AND ash.session_state = 'ON CPU' -- 过滤活动会话
ORDER BY
ash.sample_time DESC;
```
#### 2. 查询 `v$session` + SQL 文本(实时会话)
```sql
SELECT
s.sid,
s.serial#,
s.username,
s.machine AS "客户端主机",
s.program AS "客户端程序",
s.module AS "应用模块",
q.sql_text AS "当前SQL文本",
DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS "完整SQL文本"
FROM
v$session s
LEFT JOIN
v$sql q ON s.sql_id = q.sql_id
WHERE
s.status = 'ACTIVE';
```
#### 3. 历史 SQL 文本查询(需 AWR 许可)
```sql
SELECT
ash.sample_time,
ash.session_id,
ash.sql_id,
ash.machine,
t.sql_text AS "历史SQL文本"
FROM
dba_hist_active_sess_history ash
JOIN
dba_hist_sqltext t ON ash.sql_id = t.sql_id
WHERE
ash.sample_time BETWEEN TO_DATE('2023-01-01','YYYY-MM-DD')
AND TO_DATE('2023-01-02','YYYY-MM-DD');
```
### 关键说明:
1. **SQL文本获取原理**
- 通过 `sql_id` 关联 `v$sql` 或 `dba_hist_sqltext` 视图[^1][^3]
- `v$sql.sql_text`:显示前 1000 字符
- `DBMS_LOB.SUBSTR(sql_fulltext)`:显示完整 SQL(CLOB 转 VARCHAR2)
2. **性能优化建议**
```sql
-- 创建 SQL_ID 索引加速关联查询
CREATE INDEX idx_sql_id ON v$sql(sql_id);
```
3. **权限要求**:
```sql
GRANT SELECT ON v_$sql TO your_user;
GRANT SELECT ON dba_hist_sqltext TO your_user;
```
### 注意事项:
1. `v$active_session_history` 数据保留约 1 小时(内存中),重启后丢失[^1][^2]
2. `dba_hist_sqltext` 需要 AWR 许可且已启用快照[^2][^3]
3. 超过 1000 字符的 SQL 需使用 `sql_fulltext` 字段
4. 空闲会话(IDLE)不会出现在 ASH 采样中[^4][^5]
> **示例输出**:
> 
> *显示客户端主机、程序和对应 SQL 文本的查询结果*
---