Solved

how to search a text in a schema

Posted on 2009-04-15
12
372 Views
Last Modified: 2013-12-18
I have 100 tables in my schema. I would like to search say word 'hello' in my schema.How can I search it quicly and effectively in my entire schema in all tables. Any sample code, resources, ideas, links highly appreciated. Thanks in advance.
0
Comment
Question by:gudii9
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 125 total points
Comment Utility
If you have TOAD, this can be done easily.
Search->Object Search and enter the Search Term.
Click the Schema's, Object Names, Source etc.
This can be done via script too.  Let me know if you need script.
 
HTH
0
 
LVL 18

Accepted Solution

by:
sventhan earned 250 total points
Comment Utility
-- found this from ee.
DECLARE
    v_search VARCHAR2(50) := 'find this string';
    v_dummy  NUMBER;
BEGIN
    DBMS_OUTPUT.put_line(RPAD('Table Owner', 35) || RPAD('Table Name', 35) || 'Column Name');
 
    FOR x IN (  SELECT owner, table_name, column_name
                  FROM all_tab_columns
                 WHERE data_type IN ('VARCHAR2', 'CHAR')  -- change needed
              ORDER BY owner, table_name, column_name)
    LOOP
        EXECUTE IMMEDIATE   'select count(*) from '
                         || x.owner
                         || '.'
                         || x.table_name
                         || ' where '
                         || x.column_name
                         || ' = '''
                         || v_search
                         || ''' and rownum = 1'
            INTO v_dummy;
 
        IF v_dummy = 1
        THEN
            DBMS_OUTPUT.put_line(RPAD(x.owner, 35) || RPAD(x.table_name, 35) || x.column_name);
        END IF;
    END LOOP;
END;
 

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
Comment Utility
"quickly"  ?

unless your tables are all small, you can't do this "quickly"

do you know what columns you want to search?  or do you want to search every column of every table?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
sventhan, I was just about to post that same code block  :)

I wrote it!

but other people have written similar loops too!  
0
 
LVL 18

Expert Comment

by:sventhan
Comment Utility
I agree, the whole credit should go to you. This piece of code should be a HOT could be saved in a place here in EE for the future use.
If its works please spilit the points with the owner of this code (sdstuber).
 
0
 
LVL 7

Author Comment

by:gudii9
Comment Utility

when i did like

 In TOAD,Search->Object Search and enter the Search Term.

Click the Schema's, Object Names, Source etc.

says
ORA-00942: table or view does not exist
Instead of searching for the data looks like searching for table name.Any specific options to select to search on data. Please advise
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
don't worry about the split,  I just thought it was funny to have my own code beat me:)

for the TOAD error, you were probably searching through objects you don't have privileges to do SELECT on.

the pl/sql block above should be safe because it uses the ALL_TAB_COLUMNS view which should only be populated with the tables you can query

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
let me clarify....don't worry about splitting points for code I posted in another question.
you can give me a split if I posted something else here that is helpful though!  

:)
0
 
LVL 14

Expert Comment

by:ajexpert
Comment Utility
Does this screen shot help you?
I have modified a bit in order to protect our organization settings

Object-Search-TOAD.jpg
0
 
LVL 18

Expert Comment

by:sventhan
Comment Utility
Sounds Great SD.
In order to make it work this in TOAD you should be having access to dba data dictionary views.  Please ask your dba to grant you "SELECT_CATALOG_ROLE". Grant this role to allow users SELECT privileges on data dictionary views.
If you have a user with DBA role/privileage use that one for the search.
Did you try the code snippet that I've posted? It worked great for me and other people here in ee.
0
 
LVL 7

Author Closing Comment

by:gudii9
Comment Utility
thank you very much
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
glad we could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now