Whats the difference between the v$sql* views

What is the diff between:

v$sql 
v$sqlarea 
v$sqltext 
v$sqltext_with_newlines 

when I query v$sql joining with v$session and filter by a SID I get more than 
one SQL (expected) but if I join v$sqltext with v$session with same conditions I 
get one sql statement only (last SQL issued) why is this?

Thanks
Alan 

and we said...

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.

 

[@more@]