explanations of v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines
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.