Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    US
    Posts
    5

    Unanswered: Searching the entire database

    Hi all,

    I have a task to search the entire oracle database for a number of keywords. Does anyone have an idea regarding this?

    I have to search all the char, varchar and clob fields from all the tables in the database. Hope I made it clear. Please let me know if anyone did this kind of thing before or atleast have an idea.

    thanks much

  2. #2
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Cool

    Use the Oracle META text tool, at least that I think what the current name is. It was con text in earlier versions.
    HTH
    Peter

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    For a very long list of search tools checkout http://www.searchtools.com/tools/tools.html.

    I do not understand why anyone would full text index an entire database, since text indexing your primary and foreign key columns is not worthwhile and you can probably think of many other columns that should not be indexed.

    It also makes a very dangerous security situation. Consider you spend a great deal of time setting permissions on each table, so data is only available to authorized users. Then somene adds full text indexing on all tables so the "protected" data now appears in everyone's search results. Permissions are gone. You need to be careful to select a index/search tools that preserves with permissions.

    I went to a demo of Autonomy a few years ago. It looked very good and even worked with Oracle row level security. However, it cost $100,000 and up.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    actually, I suspect that he needs to do a one time search of the database to find columns that contain the keywords that he is looking for.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could with a privileged user like system do something like this

    select 'select '''||owner||'.'||table_name||'.'||column_name||''' , rowid from '||owner||'.'||table_name||' where '||column_name||'=''AAA'' union' from dba_tab_columns c where c.DATA_TYPE= 'VARCHAR2'

    1. include other datatypes you wish to search for
    2. Exclude users your not interested in
    3. Paste the output into a .sql file
    4. remove the last union
    5. run it in sqlplus or whatever else you run sql scripts from
    6. When run it will take a long time so you may wish to kick it off last thing at night. You may wish to split the query into smaller more manageable chunks.

    You could make it more sophisticated and get it to do the primary key for each table but that is trickier.

    Alan

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Yes, but I don't think he is looking for table or column names. He said he wants to search all CLOB and other string fields. That implies a data search, not a meta-data search.

    Will the original author please clarify?

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It is searching for data not metadata . It is doing an exact match but you could change it to use like. For clob stuff use the dbms_lob version of instr.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •