2013-08-07

Oracle: Get bind variables inline'd

This should be run as a script (F5), in SQL Developer. The serveroutput enabling syntax may differ in other clients. Sorry. Run it as SYS.
This query tries to put inline the bind values, if they were captured.
EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE( BINDS => TRUE, WAITS => TRUE );
ALTER SYSTEM SET "_cursor_bind_capture_interval" = 3999;
ALTER SYSTEM SET "_xpl_peeked_binds_log_size" = 8192;

DECLARE
  V_CHILD_ADDRESS VARCHAR2(64) ;
  V_SQLTEXT       VARCHAR2(4000) ;
  V_BINDNAME      VARCHAR2(30) ;
  V_VALUE         VARCHAR2(4000) ;
  V_WASCAPTURED   VARCHAR2(3) ;
  V_DATATYPE      VARCHAR2(32) ;
  CURSOR C_BIND IS
    SELECT
      NAME        ,
      VALUE_STRING,
      WAS_CAPTURED,
      DATATYPE_STRING
    FROM GV$SQL_BIND_CAPTURE
    WHERE CHILD_ADDRESS = V_CHILD_ADDRESS
    ORDER BY NAME DESC;
BEGIN
  DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL) ;
  FOR ITEM IN
  (SELECT CHILD_ADDRESS, SQL_TEXT
    FROM
      (SELECT
          SQL_ID        ,
          CHILD_ADDRESS ,
          SQL_TEXT      ,
          LAST_LOAD_TIME,
          ROW_NUMBER() OVER(PARTITION BY SQL_ID ORDER BY LAST_LOAD_TIME DESC) AS RN
        FROM GV$SQL
          WHERE SERVICE = (SELECT DISTINCT LOWER(VALUE || '_user') FROM GV$PARAMETER WHERE NAME = 'db_unique_name')
          AND SQL_TEXT NOT LIKE '%I will be filtered out.%'
      ) SQ
    INNER JOIN GV$SESSION SS ON SS.PREV_SQL_ID = SQ.SQL_ID
    WHERE RN = 1
    ORDER BY PREV_EXEC_START DESC
  )
  LOOP
    V_CHILD_ADDRESS := ITEM.CHILD_ADDRESS;
    V_SQLTEXT       := ITEM.SQL_TEXT;
    OPEN C_BIND;
    LOOP
      FETCH C_BIND INTO V_BINDNAME, V_VALUE, V_WASCAPTURED, V_DATATYPE;
      EXIT WHEN C_BIND%NOTFOUND;
      IF V_WASCAPTURED = 'NO' THEN
        V_SQLTEXT := REPLACE(V_SQLTEXT, V_BINDNAME, '''(THE ' || V_DATATYPE || ' VALUE WAS NOT CAPTURED)''') ;
      ELSE
        V_SQLTEXT := REPLACE(V_SQLTEXT, V_BINDNAME, '''' || SUBSTR(V_VALUE, 0, 500) || '''') ;
      END IF;
    END LOOP;
    CLOSE C_BIND;
    DBMS_OUTPUT.PUT_LINE(V_SQLTEXT || ';') ;
  END LOOP;
END;

No comments :

Post a Comment