Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to search a text in a schema

Posted on 2009-04-15
12
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 500 total points
ID: 24150106
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 1000 total points
ID: 24150185
-- 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 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 24150243
"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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24150260
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
ID: 24150332
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
ID: 24150338

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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24150465
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 74

Expert Comment

by:sdstuber
ID: 24150478
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
ID: 24150519
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
ID: 24150773
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
ID: 31570558
thank you very much
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24220027
glad we could help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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ā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

715 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