What are the differences of v$sqltext, v$sqlarea, v$sql
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 2 Jul 2009 12:05:38 +0800
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
Other related posts:
