DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Value of bind variables ?

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Value of bind variables ?

    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 :
    Code:
    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 :
    Code:
    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    trace the query and get the binds from the trace file - you cannot get them from the database

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe you could infer it from the result set, if the query completed.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you can only get bind values for your own session

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by davey23uk
    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

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    i mean from the database itself

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tamilselvan
    ...enable level 12 trace.
    ... or more succinctly, level 4 of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Sep 2006
    Posts
    2
    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

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    http://www.oracle-base.com/articles/...gForOracle.php

    found through Google in about 3 seconds
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width