I can query prod, but I cannot create code in prod without going through our release cycle. Normal stuff.
But I wanted to look for problematic sql that wasn't using bind variables. Thanks to 12c's introduction of pl/sql functions in the WITH clause I was able to cut/paste the following function from of a package and query away. Nice.
WITH FUNCTION normalize_scalars(p_sqltext IN VARCHAR2)
RETURN VARCHAR2
IS
l_normalized VARCHAR2(32000 CHAR);
l_char VARCHAR2(1 CHAR);
l_in_quotes BOOLEAN := FALSE;
BEGIN
IF (p_sqltext IS NULL)
THEN
RETURN NULL;
END IF;
-- replace quoted characters with x
FOR i IN 1 .. LENGTH(p_sqltext)
LOOP
l_char := SUBSTR(p_sqltext, i, 1);
IF l_in_quotes AND l_char != ''''
THEN
l_char := 'x';
END IF;
IF l_char = ''''
THEN
l_in_quotes := NOT l_in_quotes;
END IF;
l_normalized := l_normalized || l_char;
END LOOP;
-- replace digits with 1
l_normalized := TRANSLATE(l_normalized, '0123456789', '1111111111');
-- replace sequences of x or 1 with single instances of them
FOR i IN REVERSE 2 .. 10
LOOP
l_normalized := REPLACE(l_normalized, LPAD('1', i, '1'), '1');
l_normalized := REPLACE(l_normalized, LPAD('x', i, 'x'), 'x');
END LOOP;
RETURN l_normalized;
END normalize_scalars;
SELECT COUNT(*) AS dup_count, normalize_scalars(sql_text)
FROM (SELECT /*+ materialize */ sql_text FROM v$sql)
GROUP BY normalize_scalars(sql_text)
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;