explanations of v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines

[jlandzpa]

due to Tom.

v$sql the details -- if you have multiple copies of the query:

"select * from T"

in your shared pool, v$sql will have a row per query. This can happen if user
U1 and user U2 both have a table T and both issue "select * from T". Those are
entirely different queries with different plans and so on. v$sql will have 2
rows.

v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from
T" will appear there.

It is not clear to me how you are joing v$session to v$sql to get more then one
row. If you wish to see the queries a session has open (maybe open, we cache
cursors so you might see some queries that are closed) use v$open_cursor by sid.

v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea
views only show the first 1000 bytes. newlines and other control characters are
replace with whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

[biti_rainy]

:)

[esunny]

use out join.

[llm06]

thanks