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