2

More than a question, its an information sharing post.

I have come across a situation today where i needed to look for a sting in the entire database of an application with no idea of, which table/column it belongs to.

Below is a PL/SQL block i wrote and used to help my propose. Hope its helps others to with a similar requirement.

Declare
  i   NUMBER := 0;
  counter_intable NUMBER :=0;
 BEGIN
     FOR rec IN (
        select 
             'select count(*) ' || 
             ' from '||table_name||
             ' where '||column_name||' like''%732-851%'' ' as sql_command
        from user_tab_columns
        where data_type='VARCHAR2'
     )
     LOOP
        execute immediate rec.sql_command into counter_intable;
        IF counter_intable != 0 THEN
            i := i + 1;
            DBMS_OUTPUT.put_line ('Match found using command ::' || rec.sql_command);
            DBMS_OUTPUT.put_line ('count ::' || counter_intable);
        END IF;

     END LOOP;

     DBMS_OUTPUT.put_line ('total commands matched :: ' || i);
 END;

replace your string at the place of : 732-851 in the code block

Vivek Gupta
  • 2,534
  • 3
  • 15
  • 28
  • Compare the performance of your PL/SQL block with my answer. – Lalit Kumar B Feb 03 '15 at 12:43
  • 1
    A very similar solution is already posted as an answer to [this question](http://stackoverflow.com/q/208493/266304). – Alex Poole Feb 03 '15 at 12:59
  • Duh! I forgot, few days back even I added same answer there which I posted here. I don't see specific question, not sure should I remove my answer. – Lalit Kumar B Feb 03 '15 at 13:27
  • 1
    Well we have a very heavy database, with the volume of around 2.5 TB, and the PL/SQL block is completing way before then the SQL statement on it. – Vivek Gupta Feb 04 '15 at 08:06
  • Hay Alex, hope i would have found it yesterday, would have saved me some time and effort, as i am not a DBA and had to look up for syntaxes and other details again and again on internet. :( thanks for sharing. – Vivek Gupta Feb 04 '15 at 08:08
  • well theorecically you can also simply "grep" Oracles datafiles. Then, based on the position of the string in datafile, you can find extent and a segment of the table. – ibre5041 Feb 04 '15 at 08:24

1 Answers1

1

Why PL/SQL? You could do the same in SQL using xmlsequence.

For example, I want to search for the value 'KING' -

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

SQL>

You could search for any data type values, please read SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Nice solution, however I don't think it will work on a database having more than - let's say 100MB of data (which is very little for an Oracle database). – Wernfried Domscheit Feb 03 '15 at 14:24
  • Well, I tried on a database with 1.2 TB data. Could you please elaborate your point. I am interested to do some performance tests, if your point is really valid. – Lalit Kumar B Feb 03 '15 at 14:29
  • It was just an assumption from my side. Usually when an XML becomes bigger than such numbers then any DOM operations (like XPath query or Stylesheet transformation) do not work anymore due to memory overflow. Apparently Oracle is smart enough in order to limit the XML already by the WHERE clause. – Wernfried Domscheit Feb 03 '15 at 14:34
  • I believe each rowset will be returned for each row match. Thus, there shouldn't be any memory overflow due to the *xml*. I would certainly test it and let you know sooner or later as and when I have a concrete test case to prove my point. But your point is worth considerable, thanks for your time and effort. – Lalit Kumar B Feb 03 '15 at 14:56