Con este loop puedes hacer lo que buscas, espero te funcione.
Código:
declare
v_sql varchar2 (1000);
v_salida number;
begin
for c1 in (select * from all_tab_columns where owner ='OWNER_TABLA')
loop
if c1.data_type = 'VARCHAR2' then
v_sql := 'select count(*) from '||c1.table_name||' where '||c1.column_name||' like '||chr(39)||'%abcd%'||chr(39);
execute immediate v_sql into v_salida;
if v_salida > 0 then
dbms_output.put_line(c1.table_name);
end if;
end if;
end loop;
end;