Re: Search for text

On Wed, 15 Feb 2006, hpuxrac wrote:

> Conceivably ( but a very bad idea ) on a unix system you could use a
> command such as grep to tear into the database files and look for a
> word.

On the contrary, that sounds like a fine idea. It certainly would be faster than the PL/SQL suggestion that was made. Using this method you are very likely to find the word you are looking for intact in a block in one of the files.

For each of the database files, you can:

strings <file> | grep <string>

When you find the file, there are a variety of ways you can narrow down the block that contains the string, and thus determine the table that contains it. If you are very lucky, maybe there is only one table in the datafile. Check dba_extents.

Otherwise, the method that comes to mind would be to use dd. Determine if the string appears in the beginning half or end half of the file:

% dd if=<file> bs=<oracle blocksize> blocks=<half the blocks in the file> | strings | grep <string> % dd if=<file> bs=<oracle blocksize> skip=<half the blocks in the file> | strings | grep <string>

Then depending on which half it is in, start to narrow down which block it is in by using the skip and blocks arguments to scan the two quarters it could be in, then the two eights, and so on until you find the block it is in. Eventually you will find the string with a command something like:

% dd if=<file> bs=<oracle blocksize> skip=27346 blocks=1 | strings | grep <string>

Where 27346 is just an example offset. Then you can just query dba_extents:

SQL> select owner, segment_name, segment_type

      from dba_extents
      where 27346 between block_id and (block_id+blocks-1)

I'm sure someone will follow up with an even more efficient way than I have suggested. Good luck!

--
Jeremiah Wilton
ORA-600 Consulting
Emergencies - Seminars - Hiring
http://www.ora-600.net



> On Wed, 15 Feb 2006, ross.oneill_at_gmail.com wrote:

> 

>> There is a spelling mistake in one of the apps I am maintaining and
>> I want to go into the database and fix the spelling mistake.
>> Unfortunately I do not have access to the source code of the app.
>> Is it possible to search the entire database for text.