oracle10g_v$sqltext之对等视图v$sqltext_with_newlines
--v$sqltext_with_newlines是v$sqltext等同体,较之易读性强(由sql_address及sql_hash_value唯一确认一个cached cursor中的sql)
SQL> select b.sql_text from v$session a,v$sqltext_with_newlines b where a.sql_address=b.address and a.sql_hash_value=b.hash_value;
SQL_TEXT
----------------------------------------------------------------
re a.sql_address=b.address and a.sql_hash_value=b.hash_value
select b.sql_text from v$session a,v$sqltext_with_newlines b whe
SQL> select b.sql_text from v$session a,v$sqltext_with_newlines b where a.prev_sql_addr=b.address and a.prev_hash_value=b.hash_value and a.sid=145;--用prev找到刚刚执行过的sql
SQL_TEXT
----------------------------------------------------------------
select deptno,dname from test where deptno in(10,20) for update
SQL> /
SQL_TEXT
----------------------------------------------------------------
delete from test
小结
以后关联查sql就用v$session连接v$sqltext_with_newlines吧,方便快速
v$sqltext_with_newlines各列含义:address --sql(cached cursor)的在共享池(library cache)地址,
hash_value --连同上列唯一确定一个cached cursor
sql_id --cached cursor的标识
command_type --sql语句类型(select ,insert 等)
piece --一个大sql会分为好多行,可理解为行号
sql_text --每个piece的sql内容