DECLARE
sql_str VARCHAR2(20000);
sql_del VARCHAR2(20000);
cadena_buscar VARCHAR(200);
total_val number;
CURSOR c1 IS
SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE
FROM all_tab_columns
WHERE owner='[OWNER]' AND DATA_TYPE IN ('CHAR','VARCHAR2');
BEGIN
cadena_buscar:='CADENA_A_BUSCAR';
FOR fila IN c1
LOOP
sql_str := 'SELECT COUNT(*) FROM '||fila.OWNER||'.'||fila.TABLE_NAME||' where '||fila.COLUMN_NAME||' like ''%'||cadena_buscar||'%''';
EXECUTE IMMEDIATE sql_str INTO total_val;
IF total_val > 0 then
dbms_output.put_line(sql_str);
dbms_output.put_line(fila.OWNER||'.'||fila.TABLE_NAME||'.'||fila.COLUMN_NAME||' Aciertos '||total_val);
dbms_output.put_line(sql_del);
dbms_output.put_line('*******************************************');
END IF;
END LOOP;
END;
Deberás sustituir el valor de la variable cadena_buscar por el valor que necesites
