After 4 hours of trying to track down the problem (we actually have 3 emails being received - all "address not found") every 3 minutes ... for the past month and a half. We've got 48,000 emails in the Inbox at the moment. It takes 15 minutes to open in Outlook.
Where do you start?
Now I know the email address that is being used. I also know that the problem persists despite the server being taken down (for backup) every week therefore it *must* be stored somewhere in the database.
The following script runs through every single table in the database that contains a VARCHAR2 column and tries to find a specific string. It takes a while against an Oracle 11i schema (best to leave overnight ... maybe over a weekend if you have that many modules installed!).
declare c_SEARCHTEXT constant varchar2(255) := 'SEARCH TEXT GOES IN HERE'; cursor c_Tables is select distinct atc.owner, atc.table_name from all_tab_columns atc where data_type = 'VARCHAR2' and DATA_LENGTH >= length(c_SEARCHTEXT) and not exists (select 'X' from all_views av where av.owner = atc.owner and av.view_name = atc.table_name); cursor c_Columns (p_Owner varchar2, p_TableName varchar2) is select distinct column_name from all_tab_columns where owner = p_Owner and data_type = 'VARCHAR2' and DATA_LENGTH >= length(c_SEARCHTEXT) and table_name = p_TableName; TYPE cv_typ IS REF CURSOR; cv cv_typ; record_count integer; v_SQL varchar2(8124); begin for v_Table in c_Tables loop for v_Columns in c_Columns(v_table.owner, v_table.table_name) loop v_SQL := 'select count(*) ' || chr(13) || 'from ' || v_table.owner || '.' || v_Table.table_name || chr(13) || 'where upper(' || v_Columns.column_name || ') like upper(''%' || c_SEARCHTEXT || '%'')' || chr(13); open cv for v_SQL; fetch cv into record_count; if record_count > 0 then dbms_output.put_Line(v_Table.table_name || '.' || v_Columns.column_name || '***** FOUND *****'); end if; close cv; end loop; end loop; end;
Now this is unoptimised so it will be sloooooooow. You can always change the initial select to prioritise schemas you are interested in, or add in a "length" check to make sure data of the correct length exists, but the biggest saving will be replacing the "dbms_output" call with something that will send you e-mail messages when it finds something (rather than waiting until the end when it's done!).