Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
  col1 VARCHAR2(20),
  col2 VARCHAR2(20),
  col3 VARCHAR2(20)
);
INSERT INTO table_name (col1, col2, col3) VALUES ('abc', 'tom@example.com', 'tom@notexample.com');
1 rows affected

DECLARE
  match_count     INTEGER;
  v_owner         VARCHAR2(255)  := user; -- 'GWEB';
  v_data_type     VARCHAR2(255)  := 'VARCHAR2';
  v_search_string VARCHAR2(4000) :='tom@example.com';
BEGIN
  FOR t IN (
    SELECT table_name,
           column_name
    FROM   all_tab_cols
    WHERE  owner       = v_owner
    AND    data_type   = v_data_type
    AND    data_length >= LENGTH(v_search_string)
  )
  LOOP
    EXECUTE IMMEDIATE 
        'SELECT COUNT(*)'
      || ' FROM "'||V_OWNER||'"."'||t.table_name||'"'
      || ' WHERE "'||t.column_name||'" = :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;
  END LOOP;
END;
/
1 rows affected

dbms_output:
TABLE_NAME COL2 1