Value of bind variables ?

[rshivagami]

I have this huge query running in production that is using up all the temp tablespace. I put the complete query together using the v$sqltext view and it is using bind valirables. How do I get the values of these bind variables ? v$sql_cursor does not have that sql address anymore, so I am not able to retrive the data from v$sql_bind_data. And this is on Oracle 9.2.0.5. Any suggestions appreciated.

Here is the query I ran to figure out what is using the temp tablespace :

SELECT a.TABLESPACE, b.SID, b.status, b.serial#, c.address, SUM(a.blocks) ,c.sql_text, c.piece FROM
v$sort_usage a,v$session b,v$sqltext c,  v$sql_cursor sqlc ,     v$sql_bind_data sqlbd
WHERE a.session_addr = b.saddr
AND b.sql_address = c.address
AND c.address = sqlc.parent_handle AND
sqlbd.cursor_num = sqlc.curno
GROUP BY a.TABLESPACE, b.SID, b.status, b.serial#, c.address, c.sql_text, c.piece
ORDER BY a.TABLESPACE,b.SID,b.serial#,c.address, c.piece;

and here is the query I was trying to run to get the values for the bind variables :

SELECT     SQL.sql_text line,   sqlbd.cursor_num,  sqlbd.position,   sqlbd.datatype,    'xx' || sqlbd.VALUE || 'xx'
FROM     v$sql SQL,    v$sql_cursor sqlc ,     v$sql_bind_data sqlbd
WHERE  SQL.address = '3E601000' AND
    SQL.address=sqlc.parent_handle AND
    sqlbd.cursor_num = sqlc.curno

Thanks.

[davey23uk]

trace the query and get the binds from the trace file - you cannot get them from the database

[rshivagami]

This query was executed about 5 hours before via the application. So I do not know who or what the user did to be able to recreate the user activity to get the trace file.

Is there any other way to get this data for a past session ?

Thanks.

[slimdave]

Maybe you could infer it from the result set, if the query completed.

[davey23uk]

you can only get bind values for your own session

[tamilselvan]

you can only get bind values for your own session

Not true.

You can enable level 12 extended trace for another session also.

If the query is completed, then you can't get bind variable values.
How ever, if the query is still running, it is easy enable level 12 trace.

Tamil

[davey23uk]

i mean from the database itself

[slimdave]

...enable level 12 trace.

... or more succinctly, level 4 of course.

[jijujoy]

How do I implement bind variables on the SQL statements implemented on ASP pages?If anyone can give me an example of how to do that it would be helpful

[slimdave]

http://www.oracle-base.com/articles/...gForOracle.php

found through Google in about 3 seconds