Hi
Repost from SQL forum, wher noone answered - so now i'm trying here:
I am trying to extract BIND vars from the current or last SQL a session has produced:
The reasin we try to do in in pure sql is that we are outsourced and the DBAs sits in another country. We dont have access to the actual servers (AIX) - thus we cannon run TKRPOF, 10046 traces etc. So we are kind of stuck with pure SQL, and maybe a proxy
select distinct name, value_string, datatype_string, last_captured, sql_text
from (
select rownum rn, DECODE (s.sql_id, NULL, s.prev_sql_id, s.sql_id) sql_id, sql_text
from v$session s, v$sqltext sq
where s.sid = :no and
DECODE (s.sql_hash_value, 0, s.prev_hash_value, s.sql_hash_value) = sq.hash_value and
DECODE (s.sql_id, NULL, s.prev_sql_id, s.sql_id) = sq.sql_id
union
select rownum rn, o.sql_id, sql_text
FROM v$open_cursor o, v$session s
WHERE o.saddr = s.saddr AND
o.SID = s.SID AND
s.sid = :no
) a, v$sql_bind_capture b
where a.sql_id = b.sql_id
order by last_captured desc nulls last;
This gives me a "better" result, since it takes the past executions from open cursors along, but I cant figure out if it takes too much if other sessions have executed the same SQL (same hash).
And since we have only sampled values it seems that it only takes the first execution of a statmets and its binds?
So ideas appreciated :-)
Best regards
Mette
PS rownum stuff is there, because it rewrites the query when we run it from the report in sql developer otherwise giving us terrible performance (2-3 minutes contra less 1 sec).