Googling The Oracle Dictionary

This doesn't sound to have much to do with Oracle Streams, but it has helped me to speed up my understanding of a few internals and to quickly get to a better reading of traces. "It" is actually a procedure. I've named it findString. It detects patterns stored in Oracle tables. It works with VARCHAR2 only but you can easily make it work with NUMBER or whatever you need... Obviously, don't to use it on anything that is not a small TEST database or with non-selective patterns.

Remember, it's just a tool, it won't explain anything about how things work. Keep also in mind that some data can be hold in sessions and that this procedure won't find them because it doesn't read temporary data. Enough talk, here is the code:

create or replace procedure findString(pattern varchar2)
is
  cnt number:=0;
begin
  for i in (select c.owner, c.table_name, c.column_name
              from DBA_TAB_COLS c, dba_tables t
             where c.data_type in ('VARCHAR2','NVARCHAR2')
               and c.owner=t.owner
               and c.table_name=t.table_name) loop
     begin
       execute immediate 'select count(*) from "'
                  ||i.owner||'"."'||i.table_name||
                  '" where "'||I.column_name||'" like :x'
               into cnt using pattern;
     exception when others then
        dbms_output.put_line('Error in in "'||i.owner||
                      '"."'||i.table_name||'" column "'||
                      i.column_name||'"');
        dbms_output.put_line(DBMS_UTILITY.
                                FORMAT_ERROR_STACK);
     end;
     if (cnt>0) then
         dbms_output.put_line('Pattern Detected in "'||
                      i.owner||'"."'||i.table_name||
                      '" column "'||i.column_name||'"');
     end if;
  end loop;
end;
/

To use it with SQL*Plus, set serveroutput to on and pass your search pattern in the parameter, like this:

set serveroutput on
exec findString('%ONEWAY%TABLE%')

Pattern Detected in "SYSTEM"."LOGMNRC_GTLO" column "LVL0NAME"
Pattern Detected in "SYSTEM"."LOGMNR_OBJ$" column "NAME"
Pattern Detected in "SYS"."OBJ$" column "NAME"
Pattern Detected in "SYS"."HISTGRM$" column "EPVALUE"
Pattern Detected in "SYS"."STREAMS$_RULES" column "OBJECT_NAME"
Pattern Detected in "SYS"."STREAMS$_RULES" column "RULE_CONDITION"
Pattern Detected in "SYS"."STREAMS$_RULES" column "RULE_NAME"
Pattern Detected in "SYS"."WRH$_SEG_STAT_OBJ" column "BASE_OBJECT_NAME"
Pattern Detected in "SYS"."WRH$_SEG_STAT_OBJ" column "OBJECT_NAME"