3 Replies Latest reply on Nov 13, 2014 4:53 PM by rp0428

    Data Search in whole DB


      Hello Everyone,


      I am in middle of a Project where impact study for the entire DB is relevant. Hence I need to search a string in whole Database.

      I was trying with below codes. When I search with "all_tab_cols", it's taking taking endless hours, where I tried with user_tab_cols, it's taking 1+ Hour.

      Can anyone help me to optimize the query or suggest any better approach which saves my time of execution.

      Note: DB size is less than 1 GB



        ncount NUMBER; 

      vwhere VARCHAR2(1000) := ''; 

      vselect VARCHAR2(1000) := ' select count(1) from '; 

      --vsearchstr VARCHAR2(1000) := '<search_string>';


      FOR k IN (SELECT /*+ all_rows */ a.table_name 


                   FROM user_tab_cols a 

                  WHERE a.data_type LIKE '%CHAR%'

                  and a.table_name not like 'BIN$%') 



          vwhere := ' where ' || upper(k.column_name) || ' like upper(''%<search_string>%'') ';


      EXECUTE IMMEDIATE vselect || k.table_name || vwhere 

           INTO ncount  ;


         IF (ncount > 0) 


             dbms_output.put_line( k.table_name||' '||k.column_name);

          END IF;



          when others then

          dbms_output.put_line('Exception Block'|| ' ' || k.table_name||' '||k.column_name ||sqlerrm);    


        END LOOP; 





      Pradeep Narayanan

        • 1. Re: Data Search in whole DB

          I'm just going on record that I don't think this is a very good idea.


          But for what it's worth, I'd try the SQL/PLSQL forum as this question isn't SQL Developer specific.

          • 2. Re: Data Search in whole DB

            You need install the in-memory option and run Oracle Text over your data dictionary...... no, just kidding :-)


            But it does serve to indicate that's something very very wrong with the request...I mean seriously, you have a string and you've no idea what columns it might be contained in ? 


            Anyway.... if you really really must do it...then do ONE scan of each table, ie, build a string like:


            select count(*)

            from table

            where col1 like '%string%' or col2 like '%string%' etc etc etc...


            but before running that SQL...ask yourself if you are really asking the right question


            Just my $0.02 worth.




            • 3. Re: Data Search in whole DB

              Wrong forum!


              This question has NOTHING to do with sql developer. Please mark it ANSWERED and repost it in the SQL and PL/SQL forum

              SQL & PL/SQL


              The best way to 'save time' is: DON'T DO THAT!


              If your org doesn't document your tables and application they need to address that MAJOR deficiency first. It is just plain silly to search every character column of every table for some string.


              It should be VERY EASY for a human that knows the application and data to at least determine which tables and columns could POSSIBLY have that string in them. For example, a zip_code column isn't likely to have ANY string that you might be searching for.


              Before you repost you might want to remove that WHEN OTHERS junk code. If you don't you can expect several responders to jump on that as a bug. All that does is hide any real exception that might be happening.