Noetix: Disabling Material Category (XXK_MTL_CAT) caching

This is slightly more complicated than the other "removal of caching" blog posts I've done earlier as there are multiple views (one for each structure) which all need to be updated and replaced at the same time. I've adopted a fairly simple approach;

• Remove any backup (_BK) views
• Create a new view to replace the root XXK_MTL_CAT view
• Go through the ALL_VIEWS system view looking for the structure-specific views and then individually replace each of them with a view based on the new XXK_MTL_CAT view created above
• Rebuild all the objects that have been invalidated by this change

Here is the script (you'll notice the * instead of the important piece of SQL, more details on that later);

@utlspon xu6_2012_replace_xxk_mtl_cat_view

BEGIN
  for v_Data in (SELECT av.view_name
                   FROM ALL_VIEWS av
                  WHERE av.owner = USER
                    and av.view_name LIKE 'XXK_MTL_CAT_%BK') loop
    execute immediate 'drop view ' || v_Data.view_name; -- Remove an existing backup view if it exists
  end loop;
END;
/

RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/

CREATE OR REPLACE VIEW XXK_MTL_CAT AS

*

/

begin
  for v_view in (select av.*
                   from all_views av
                  where av.owner = USER
                    and av.view_name like 'XXK_MTL_CAT_%') loop
    if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
        end if;
  end loop;
end;
/

begin
  dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/

COMMIT;

@utlspoff

*- Now Noetix owns the code for this bit and as it's going to be distinct to every individual system (as it includes references to ID's) you'll need to do a bit of detective work and work it out yourself. I'll tell you how below.


First of all do a search for the object N_KFF_Mtl_Cat in all the .SQL files in your install directory. You shouldn't find many, the one you're after is named something like N_KFF_Mtl_Cat_pkg.sql, open the file and then scroll down to the line;

INSERT  /*+ APPEND */ INTO N_KFF_Mtl_Cat

You'll see that that the basis for this insert statement is a select statement. You need to take this select statement, take the columns in the table and re-format the SQL to return all the columns as specified in table.

When you've done this you'll notice that the column in the source table STRUCTURE_ID is mapping to both the STRUCTURE_ID and STRUCTURE_NAME in the view. If you scroll down quite a bit further you'll notice that there is also an UPDATE statement;

UPDATE N_KFF_Mtl_Cat dtbl
   SET dtbl.STRUCTURE_NAME =

You need to extract this UPDATE statement and put it into the view you're building (as the STRUCTURE_NAME) column.

In the end you'll have a piece of SQL that you can drop into the script above. If everything works when you run the script there will be no (well, the same number as you started with!) invalid views.

Noetix: Comparing NOETIX_SYS Schemas

As I'm currently working on upgrading our Noetix installation from 6.0.2 to 6.1 I've been writing some SQL to do a comparison between our new 6.1 build (on a test server) and our current production 6.1 environment. At the moment the only things I'm comparing are the views (looking for new and removed), and columns looking for new and removed and where the source code has changed.

In order to use this script you need to have created a database link in your test environment called NOETIX_TEST that points to your production environment.

The SQL is;

declare
  v_Version varchar2(10) := '0.9.5';
  -- 0.9.2 Added last updated by, last updated date columns to the column source comparrison
  -- 0.9.3 Removed A$ column from the REMOVED/ADDED query, "Query position" no longer a separate line in report,
  --       additional highlighting of viewname added, Local/Remote DB name now used rather than PROD and TEST
  -- 0.9.4 Adding in a check for WHERE clause changes
  -- 0.9.5 For some reason when a column changed type (i.e. COL to EXPR) I'd decided not to report it. Fixed.

  v_ShowDetails         varchar2(1) := 'T';
  v_ShowDangerItemsOnly varchar2(1) := 'F';

  v_LocalDB  V$DATABASE.NAME%TYPE;
  v_RemoteDB V$DATABASE.NAME%TYPE;

  v_ViewCount   integer;
  v_ChangeCount integer;
  v_OldViewName n_views.view_name%TYPE;
begin
  dbms_output.put_line('Noetix Schema Comparison ' ||
                       TO_CHAR(SYSDATE, 'DD-MON-YYYY') || ' (V' ||
                       v_Version || ')');
  dbms_output.put_line('---------------------------------------------');
  for v_Database in (SELECT NAME FROM V$DATABASE@NOETIX_TEST) loop
    v_RemoteDB := v_Database.name;
    dbms_output.put_line('(Remote) Database = ' || v_Database.name);
  end loop;
  for v_Database in (SELECT NAME FROM V$DATABASE) loop
    v_LocalDB := v_Database.name;
    dbms_output.put_line('(Local) Database = ' || v_Database.name);
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line('SECTION 001- CHECKING VIEWS');
  dbms_output.put_line('---------------------------');
  if v_ShowDangerItemsOnly = 'F' then
    dbms_output.put_line('New Views');
    dbms_output.put_line('---------');
    v_ChangeCount := 0;
    for v_LocalView in (SELECT DISTINCT nv.View_Name
                          FROM N_Views NV
                         WHERE EXISTS
                         (SELECT 'X'
                                  FROM All_Views AV
                                 WHERE av.Owner = USER
                                   AND av.view_name = UPPER(nv.View_name))
                         ORDER BY nv.View_Name) loop
      SELECT COUNT(*)
        INTO v_ViewCount
        FROM N_Views@NOETIX_TEST NV
       WHERE EXISTS (SELECT 'X'
                FROM All_Views@NOETIX_TEST AV
               WHERE av.Owner = USER
                 AND av.view_name = UPPER(nv.View_name))
         AND nv.view_name = v_LocalView.view_name;
      if v_ViewCount = 0 then
        if v_ShowDetails = 'T' then
          dbms_output.put_line(v_LocalView.view_name);
        end if;
        v_ChangeCount := v_ChangeCount + 1;
      end if;
    end loop;
    dbms_output.put_line('-- Total New View Count = ' ||
                         TO_CHAR(v_ChangeCount));
    dbms_output.put_line(' ');
  end if;
  dbms_output.put_line('Removed Views');
  dbms_output.put_line('-------------');
  v_ChangeCount := 0;
  for v_RemoteView in (SELECT DISTINCT nv.View_Name
                         FROM N_Views@NOETIX_TEST NV
                        WHERE EXISTS
                        (SELECT 'X'
                                 FROM All_Views@NOETIX_TEST AV
                                WHERE av.Owner = USER
                                  AND av.view_name = UPPER(nv.View_name))
                        ORDER BY nv.View_Name) loop
    SELECT COUNT(*)
      INTO v_ViewCount
      FROM N_Views NV
     WHERE EXISTS (SELECT 'X'
              FROM All_Views AV
             WHERE av.Owner = USER
               AND av.view_name = UPPER(nv.View_name))
       AND nv.view_name = v_RemoteView.view_name;
    if v_ViewCount = 0 then
      if v_ShowDetails = 'T' then
        dbms_output.put_line(v_RemoteView.view_name);
      end if;
      v_ChangeCount := v_ChangeCount + 1;
    end if;
  end loop;
  dbms_output.put_line('-- Total Removed View Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');
  dbms_output.put_line('SECTION 002- CHECKING COLUMNS');
  dbms_output.put_line('-----------------------------');
  dbms_output.put_line('Views With Column Changes');
  dbms_output.put_line('-------------------------');
  v_ChangeCount := 0;
  v_OldViewname := '@';
  for v_Column in (select atc.table_name view_name,
                          'ADDED' change_type,
                          atc.column_name
                     from all_tab_columns ATC
                    where atc.owner = user
                      and atc.COLUMN_NAME NOT LIKE 'A$%'
                      and atc.COLUMN_NAME <> 'Z$$_________________________'
                      and atc.TABLE_NAME IN
                          (SELECT UPPER(nv.view_name)
                             FROM N_Views@NOETIX_TEST nv
                            WHERE NVL(nv.omit_flag, 'N') = 'N')
                      AND NOT EXISTS
                    (SELECT 'X'
                             FROM all_tab_columns@NOETIX_TEST ATC1
                            WHERE ATC.OWNER = ATC1.OWNER
                              AND ATC.TABLE_NAME = ATC1.TABLE_Name
                              AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
                      AND v_ShowDangerItemsOnly = 'F'
                   UNION
                   select atc.table_name view_name,
                          'REMOVED',
                          atc.column_name
                     from all_tab_columns@NOETIX_TEST ATC
                    where atc.owner = user
                      and atc.COLUMN_NAME NOT LIKE 'A$%'
                      and atc.COLUMN_NAME <> 'Z$$_________________________'
                      and atc.TABLE_NAME IN
                          (SELECT UPPER(nv.view_name)
                             FROM N_Views nv
                            WHERE NVL(nv.omit_flag, 'N') = 'N')
                      AND NOT EXISTS
                    (SELECT 'X'
                             FROM all_tab_columns ATC1
                            WHERE ATC.OWNER = ATC1.OWNER
                              AND ATC.TABLE_NAME = ATC1.TABLE_Name
                              AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
                    ORDER BY 1, 2, 3) loop
    v_ChangeCount := v_ChangeCount + 1;
    if v_OldViewname <> v_Column.view_name then
      v_OldViewname := v_Column.view_name;
      dbms_output.put_line(v_Column.view_name);
    end if;
    if v_ShowDetails = 'T' then
      dbms_output.put_line('  ' || INITCAP(v_Column.Change_Type) || ' ' ||
                           v_Column.Column_Name);
    end if;
  end loop;
  dbms_output.put_line('-- Total Column Changes Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');
  dbms_output.put_line('Columns With Modified Source');
  dbms_output.put_line('----------------------------');
  dbms_output.put_line('NOTE: This is a comparison of the Noetix tables, not the view source themselves');
  v_ChangeCount := 0;
  v_OldViewname := '@';
  for v_Column in (SELECT nv.View_Name Test_View_Name,
                          nv.query_position Test_Query_Position,
                          nv.column_name Test_column_name,
                          nv.column_type Test_Column_Type,
                          NVCT.last_updated_by Test_Last_Updated_By,
                          NVCT.Last_update_Date Test_Last_Udpated_Date,
                          NVL(nv.table_alias, '') || '.' ||
                          Replace(Replace(Replace(nv.column_expression,
                                                  '  ',
                                                  ' '),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Test_Column_Detail,
                          nv1.View_Name Prod_View_Name,
                          nv1.query_position Prod_Query_Position,
                          nv1.column_name Prod_column_name,
                          nv1.column_type Prod_Column_Type,
                          NVCT1.last_updated_by Prod_Last_Updated_By,
                          NVCT1.Last_update_Date Prod_Last_Udpated_Date,
                          NVL(nv1.table_alias, '') || '.' ||
                          Replace(Replace(Replace(nv1.column_expression,
                                                  '  ',
                                                  ' '),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Prod_Column_Detail,
                          Replace(Replace(Replace(nv1.column_expression,
                                                  '  ',
                                                  ''),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') || ' > ' ||
                          Replace(Replace(Replace(nv.column_expression,
                                                  '  ',
                                                  ''),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Change_Pattern
                     FROM N_View_Columns NV
                     JOIN N_View_Column_Templates NVCT
                       ON NV.T_COLUMN_ID = NVCT.t_Column_Id
                     JOIN n_view_columns@NOETIX_TEST NV1
                       ON nv1.view_name = nv.view_name
                      AND nv1.column_name = nv.column_name
                      AND nv1.query_position = nv.query_position
                      AND UPPER(nv1.View_name) IN
                          (SELECT AV.VIEW_NAME
                             FROM All_Views AV
                            WHERE AV.Owner = USER)
                      AND NVL(NV1.OMIT_FLAG, 'N') = 'N'
                      AND NVL(nv1.column_expression, 'NULL') NOT LIKE
                          '%Copyright Noetix Corporation%'
                     JOIN N_View_Column_Templates@NOETIX_TEST NVCT1
                       ON NV1.column_label = nvct1.column_label
                      AND nv1.view_label = nvct1.view_label
                      AND nv1.query_position = nvct1.query_position
                    WHERE 1 = 1
                      AND UPPER(NV.View_Name) NOT LIKE '%_BASE'
                      AND nv.column_type NOT IN ('GENEXPR')
                      AND NVL(NV.OMIT_FLAG, 'N') = 'N'
                      AND NVL(nv.column_expression, 'NULL') NOT LIKE
                          '%Copyright Noetix Corporation%'
                      AND UPPER(nv.View_name) IN
                          (SELECT AV.VIEW_NAME
                             FROM All_Views@NOETIX_TEST AV
                            WHERE AV.Owner = USER)
                      AND NVL(lower(CASE
                                      WHEN nv1.COLUMN_TYPE = 'EXPR' THEN
                                       ''
                                      ELSE
                                       NVL(nv1.table_alias, '') || '.'
                                    END || Replace(Replace(Replace(nv1.column_expression,
                                                                   ' ',
                                                                   ''),
                                                           chr(13),
                                                           ''),
                                                   chr(10),
                                                   '')),
                              'NULL') <> NVL(lower(CASE
                                                     WHEN nv.COLUMN_TYPE = 'EXPR' THEN
                                                      ''
                                                     ELSE
                                                      NVL(nv.table_alias, '') || '.'
                                                   END || Replace(Replace(Replace(nv.column_expression,
                                                                                  ' ',
                                                                                  ''),
                                                                          chr(13),
                                                                          ''),
                                                                  chr(10),
                                                                  '')),
                                             'NULL')
                    ORDER BY nv.View_Name, nv.query_position, nv.column_name) loop
    --if v_Column.Test_Column_Type = v_Column.Prod_Column_Type then
      v_ChangeCount := v_ChangeCount + 1;
      if v_OldViewname <> v_Column.Test_View_Name then
        v_OldViewname := v_Column.Test_View_Name;
        dbms_output.put_line('===== ' || UPPER(v_Column.Test_View_Name) ||
                             ' =====');
      end if;
      dbms_output.put_line('  ' || v_Column.Test_Column_Name || ' (' ||
                           v_Column.Test_Column_Type || ' Query Position ' ||
                           v_Column.Test_Query_Position || ')');
      if v_ShowDetails = 'T' then
        dbms_output.put_line('    ' || v_LocalDB || '=' ||
                             v_Column.Test_Column_Detail || ' (' ||
                             v_Column.Test_Last_updated_By || ', ' ||
                             TO_CHAR(v_Column.Test_Last_Udpated_Date,
                                     'DD-MON-YYYY') || ')');
        dbms_output.put_line('    ' || v_RemoteDB || '=' ||
                             v_Column.Prod_Column_Detail || ' (' ||
                             v_Column.Prod_Last_updated_By || ', ' ||
                             TO_CHAR(v_Column.Prod_Last_Udpated_Date,
                                     'DD-MON-YYYY') || ')');
      end if;
    --end if;
  end loop;
  dbms_output.put_line('-- Total Modified Column Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line('');
  dbms_output.put_line('SECTION 003- CHECKING VIEW COMPOSITION');
  dbms_output.put_line('--------------------------------------');
  dbms_output.put_line('Changed Where Clause Conditions');
  dbms_output.put_line('------------------------------');
  v_ChangeCount := 0;
  for v_WhereChange in (select 'Added' change_direction,
                               NVW.view_name,
                               nvw.query_position,
                               nvw.where_clause_position,
                               nvw.where_clause,
                               nvwt.last_updated_by,
                               nvwt.last_update_date
                          FROM N_View_Wheres NVW
                          JOIN n_View_Where_Templates NVWT
                            ON NVW.View_Label = NVWT.View_Label
                           AND nvw.query_position = nvwt.query_position
                           AND nvw.where_clause_position =
                               nvwt.where_clause_position
                          JOIN All_Views AV
                            ON AV.owner = USER
                           AND AV.VIEW_NAME = UPPER(NVW.view_name)
                         WHERE 1 = 1
                           AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
                           AND NVL(NVW.Omit_Flag, 'N') = 'N'
                           AND NOT EXISTS
                         (SELECT 'X'
                                  FROM N_View_Wheres@NOETIX_TEST NVW_R
                                 WHERE NVW.VIEW_NAME = NVW_R.view_name
                                   AND NVW.query_position =
                                       NVW_R.query_position
                                   AND NVW.where_clause = NVW_R.where_clause)
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views@NOETIX_TEST AV1
                                 WHERE AV1.owner = USER
                                   AND AV1.VIEW_NAME = UPPER(NVW.view_name))
                        UNION
                        SELECT 'Removed',
                               NVW.view_name,
                               nvw.query_position,
                               nvw.where_clause_position,
                               nvw.where_clause,
                               nvwt.last_updated_by,
                               nvwt.last_update_date
                          FROM N_View_Wheres@NOETIX_TEST NVW
                          JOIN n_View_Where_Templates@NOETIX_TEST NVWT
                            ON NVW.View_Label = NVWT.View_Label
                           AND nvw.query_position = nvwt.query_position
                           AND nvw.where_clause_position =
                               nvwt.where_clause_position
                         WHERE 1 = 1
                           AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
                           AND NVL(NVW.Omit_Flag, 'N') = 'N'
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views@NOETIX_TEST AV
                                 WHERE AV.owner = USER
                                   AND AV.VIEW_NAME = UPPER(NVW.view_name))
                           AND NOT EXISTS
                         (SELECT 'X'
                                  FROM N_View_Wheres NVW_R
                                 WHERE NVW.VIEW_NAME = NVW_R.view_name
                                   AND NVW.query_position =
                                       NVW_R.query_position
                                   AND NVW.where_clause = NVW_R.where_clause)
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views AV1
                                 WHERE AV1.owner = USER
                                   AND AV1.VIEW_NAME = UPPER(NVW.view_name))
                         ORDER BY 2, 3, 4, 5) loop
    v_ChangeCount := v_ChangeCount + 1;
    if v_OldViewname <> v_WhereChange.View_Name then
      v_OldViewname := v_WhereChange.View_Name;
      dbms_output.put_line('===== ' || UPPER(v_WhereChange.View_Name) ||
                           ' =====');
    end if;
    if v_ShowDetails = 'T' then
      dbms_output.put_line('    Query Position ' ||
                           v_WhereChange.Query_Position || ' ' ||
                           InitCap(v_WhereChange.Change_Direction) || ': ' ||
                           v_WhereChange.Where_Clause || ' (Position = ' ||
                           TO_CHAR(v_WhereChange.Where_Clause_Position) || ', ' ||
                           v_WhereChange.Last_updated_By || ', ' ||
                           TO_CHAR(v_WhereChange.Last_Update_Date,
                                   'DD-MON-YYYY') || ')');
    end if;
  end loop;
  dbms_output.put_line('-- Total Where Clause Changes = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');

  rollback;
end;

This code is available here (via Google Docs).

NOTE: I've updated the SQL to do some additional comparisons and format the comparisons completely differently.

Noetix: Identifying Un-used Columns For Removal

This blog post covers a description of an application (source code provided) that identifies columns which are not required (i.e. all values of the column in the view are the same) and generates a script that somone who has completed the NoetixViews Customization Certification course can include in the Generation Scripts.


The first thing to note is how this works; it opens every view doing a MIN/MAX on every column. It has been tested on a 16GB database and works fine - of course the main requirement of an application like this is to have enough TEMP tablespace in order to work. This is the reason the columns are dealt with individually rather than just opening each view and doing a MIN/MAX on every column in the view in a single hit - this was blowing out the 5GB of TEMP tablespace we had available on the test system.

The application is a simple console application:

All it does is process the views in the database using the SQL:

SELECT DISTINCT UPPER(v.VIEW_NAME) VIEWNAME
  FROM n_views v
 WHERE EXISTS
 (SELECT 'x'
          FROM all_views av
         WHERE av.OWNER = USER
           AND av.VIEW_NAME = UPPER(v.VIEW_NAME))
   AND NVL(v.OMIT_FLAG, 'N') = 'N'
   AND NVL(v.Special_Process_Code, 'X') NOT IN ('LOV', 'BASEVIEW')
   AND (UPPER(v.VIEW_NAME) = '%VIEWNAME%' OR '%VIEWNAME%' = 'ALL')
ORDER BY UPPER(v.VIEW_NAME)

This gives a list of all the views in the database. The %VIEWNAME% is replaced with the first argument on the command line (optional). If nothing is specified then it is replaced with ALL.

Views which are omitted by default are automatically excluded as are all views which are of type LOV (list of values) or BASEVIEW.

Once the list of views has been obtained then the list of columns in each view is retrieved using the SQL:

 SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
  FROM ALL_TAB_COLUMNS ATC
 WHERE ATC.OWNER = USER
   AND ATC.TABLE_NAME = '%VIEWNAME%'
   AND ATC.COLUMN_NAME NOT LIKE 'A$%'
   AND ATC.COLUMN_NAME NOT LIKE 'Z$%'
   AND ATC.DATA_TYPE NOT IN ('ROWID', 'LONG')
   AND INSTR(ATC.COLUMN_NAME, '$') = 0

The list of  columns excludes the A$ and Z$ columns that are managed by Noetix and columns which contain a "$" symbol (flexfields) as if you want to remove these you can disable them in the e-Business Suite itself.

Once the appliction has the list of columns it looks through them building the SQL:

SELECT 1 AS IGNORE
   ,TO_CHAR(MIN()) AS MIN
   ,TO_CHAR(MAX()) AS MAX
FROM

The MIN and MAX values are then compared, if they are the same when an SQL file is created for the suppression of the field (for future reference the MIN and MAX values returned are stored in the file).

As you can imagine when accessing the GL views this query can take quite a while - for our 16GB database processing all the views in our relatively simple configuration will take about a week (running multiple processes has gotten this down to 2-3 days).

This isn't quick - but it is effective - we are removing around 3,000 columns from the views. That's 3,000 less columns in the help and for our end users to "accidentally" pick the wrong one from!

Source Code: here (Visual Studio 2010, 20kb Zip Archive)
Build Environment: VS2010 + Oracle Client

Oracle PL/SQL: Searching All VARCHAR2 Fields In A Schema

How frustrating is that? I've entered the data into the Oracle Front-end, the workflow has kicked off, an email has been sent, and *somewhere* in all this mess is a record of an error e-mail being received.

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!).