How to find to which schema or procedure, table and column a particular value belongs to?

Question

I have a 9 digit number, say "234234234", is there a way to find or check its appearance in my database, like in which particular schema or procedure does it fall? and list out all the tables and columns which has that value in pl/sql developer

Answer 1

This query only searches in stored objects that user is allowed to access (procedure, function, package, ...). You could refer to this

Not sure if there is one way to search for that value in all database table.

SELECT * 
FROM all_source
WHERE text LIKE '%234234234%';
    --AND owner = 'SCHEMA_NAME';

Answer 2

The below block identifies the given string's presence across all the tables in your DB.

declare
    num_rows number;
    sql_text varchar2(250);
    sql_info varchar2(100);
begin
    dbms_output.enable(1000000);
    for x in (select table_name, column_name from dba_tab_columns
               where data_type in ('VARCHAR','VARCHAR2','CHAR')
                 and owner <> 'SYSTEM')
    loop
         sql_text:='select count(*) into :num_rows from SYSTEM.'||x.table_name||' where '||x.column_name||' like ''%234234234%''';
         -- dbms_output.put_line (sql_text);
         execute immediate sql_text into num_rows;
         if num_rows>0 
         then 
              sql_info:='Table: '||x.table_name||' contains the string';
              dbms_output.put_line (sql_info);
         end if;
    end loop;
end;
/