SQL> select hiredate from emp order by 1;

HIREDATE
----------
17.12.1980    *  these records
20.02.1981    *  satisfy
22.02.1981    *  the condition
02.04.1981
01.05.1981
09.06.1981
08.09.1981
28.09.1981
17.11.1981
03.12.1981
03.12.1981
09.12.1982
23.12.1982
12.01.1983

14 rows selected.

SQL> DECLARE
  2    l_str VARCHAR2(500);
  3    l_cnt NUMBER := 0;
  4  BEGIN
  5    FOR cur_r IN (SELECT u.table_name, u.column_name
  6                  FROM user_tab_columns u
  7                  WHERE u.column_name = 'HIREDATE'
  8                 )
  9    LOOP
 10      l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
 11              ' WHERE ' || cur_r.column_name || ' < date ''1981-04-01''';
 12
 13     EXECUTE IMMEDIATE (l_str) INTO l_cnt;
 14
 15      IF l_cnt > 0 THEN
 16        dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
 17      END IF;
 18    END LOOP;
 19  END;
 20  /
3 : EMP

PL/SQL procedure successfully completed.

SQL>

with t as (
           SELECT  u.table_name,
                   u.column_name,
                   extractvalue(
                                xmltype(
                                        dbms_xmlquery.getxml(
                                                             'select count(*) cnt from "' || u.table_name ||
                                                             '" where "' || u.column_name ||
                                                             '" < date ''2009-01-01'' and rownum = 1'
                                                            )
                                       ),
                               '/ROWSET/ROW/CNT'
                              ) cnt
             FROM  user_tab_columns u
             WHERE u.column_name = 'HIREDATE'
          )
select  table_name,
        column_name
  from  t
  where cnt = '1'
/

TABLE_NAME                     COLUMN_NAME
------------------------------ -------------
EMP                            HIREDATE
EMP1                           HIREDATE

SQL>