What are the differences of v$sqltext, v$sqlarea, v$sql

From: Karl Arao <karlarao_at_gmail.com>
Date: Thu, 2 Jul 2009 12:05:38 +0800
Message-ID: <12ee65600907012105u7c893483w299551a4e32d48bc_at_mail.gmail.com>

Hi Guys,

Weird scenario, when I'm looking for TRUNCATE statement, I can see it in V$SQLTEXT .... but I can't see it in V$SQLAREA and V$SQL

select * from v$sqltext where upper(sql_text) like '%TRUNCATE%TEST3%';* *-- returns the truncate statement

select * from v$sqlarea
where sql_id = 'dfwz4grz83d6a'
where upper(sql_text) like '%TRUNCATE%';* *-- no rows

select * from v$sql
where sql_id = 'dfwz4grz83d6a'
where upper(sql_text) like '%TRUNCATE%'; -- no rows

So to have a more readable ASH... I joined it with SQL_TEXT so I can clearly see the TRUNCATE statement on the samples...

set lines 3000
select substr(sa.sql_text,1,500) txt, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.user_id, a.sql_id,

       a.sql_child_number, a.sql_plan_hash_value,
       a.sql_opcode, a.plsql_object_id, a.service_hash, a.session_type,
       a.session_state, a.qc_session_id, a.blocking_session,
       a.blocking_session_status, a.blocking_session_serial#, a.event,
a.event_id,
       a.seq#, a.p1, a.p2, a.p3, a.wait_class,
       a.wait_time, a.time_waited, a.program, a.module, a.action,

a.client_id
from gv$active_session_history a, gv$sqltext sa where a.sql_id = sa.sql_id
and session_id = 126

So my question is, what are the differences of v$sqltext, v$sqlarea, v$sql? Any ideas? :)

• Karl Arao http://karlarao.wordpress.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 01 2009 - 23:05:38 CDT