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