Searching Oracle tables for particular values
This script constructs one select statement per table for all CHAR and VARCHAR2 columns.
It will give error ORA-01489 if some of the tables have many columns with long names.
It uses LIKE and equality search, in hope of an index.
The result is the table name and exact row, where the first occurrence of string has happened. Run resulted statement to quickly get this row.
If you don't have access to DBA_ views, and want to search in schema you are connected to, then change DBA_ to USER_ and remove rows 11 and 12.
For large schemas (many gigabytes or terabytes) it is slow to do such search.
HR@mydb&gt; declare
  2   l_owner varchar2(30):= 'HR';
  3   l_search_string varchar2(200):= 'King';
  4   l_sql varchar2(30000);
  5   l_rowid rowid;
  6  begin
  7    l_search_string := upper(l_search_string);
  8    for c in (select t.table_name, listagg ('upper('||column_name||')',',') within group ( order by column_id )col
  9                from DBA_tables t, DBA_tab_columns c
 10               where t.table_name = c.table_name
 11                 and t.owner = c.owner
 12                 and t.owner = L_OWNER
 13                 and c.data_type in ('VARCHAR2','CHAR')
 14               group by t.table_name
 15               order by t.table_name )
 16    loop
 17      begin
 18        dbms_application_info.set_action('table: '||c.table_name);
 19        l_sql := 'select rowid from '|| l_owner||'.' ||c.table_name||
 20          ' where (' || replace(c.col,',',' like ''%'||L_SEARCH_STRING||'%'' or ' )|| '='''||L_SEARCH_STRING||'''' ||
 21          ') and rownum = 1';
 22        --DBMS_OUTPUT.PUT_LINE ( 'l_sql = ' || l_sql );
 23        --
 24        execute immediate l_sql  into l_rowid;
 25        --
 26        DBMS_OUTPUT.PUT_LINE ( 'select * from  '|| l_owner||'.' || c.table_name||' where rowid = '''||l_rowid|| ''';'  );
 27      exception when no_data_found then null;
 28      end;
 29    end loop;
 30    dbms_application_info.set_action(null);
 31  end;
 32  /
select * from  HR.EMP where rowid = 'AAAW1OAAGAAACErAAI';
select * from  HR.EMPLOYEES where rowid = 'AAAVPuAAFAAAADPAAA';
select * from  HR.NEW_EMPS where rowid = 'AAAW1XAAGAAACE7AAK';
select * from  HR.T where rowid = 'AAAXE9AAHAAAIACAAT';
select * from  HR.TT where rowid = 'AAAWTDAAIAAAA2CAFk';

From another session, you can monitor on what table the select is working at that moment.
select sid, action, username,  program 
  from v$session 
 where action like 'table: %'
/