🔒 Locked

oracle question

Started by djsteffey Apr 19, 2009 at 11:04 PM 7 replies 800+ views

Original Post

165

Apr 19, 2009 at 11:04 PM

how can i search an entire oracle database for a particular string or pattern? i dont know what table or what column the string will be in.

kyoryu

Apr 19, 2009 at 11:51 PM

Quote:
Original post by ncsu121978
how can i search an entire oracle database for a particular string or pattern? i dont know what table or what column the string will be in.

Then you either need to figure that out, or have a problem with your db schema.

djsteffey

Author

165

djsteffey

Apr 20, 2009 at 12:11 AM

maybe you misunderstood.....

i want to know the table_name and column_name that contains the given string or pattern. this could be multiple places in the database that will contain this string and i want to know all the locations.

SriLumpa

Apr 20, 2009 at 12:33 AM

You can find the existing tables/columns in all_tab_columns.
http://en.wikipedia.org/wiki/Oracle_metadata#Example_2:_finding_columns
Then, for each of the column which is of the interesting type, you select everything that looks like your string.

I hope for you that this is a one-time need, and that your base is small :)
Good luck !

kyoryu

Apr 20, 2009 at 12:49 AM

Quote:
Original post by ncsu121978
maybe you misunderstood.....

i want to know the table_name and column_name that contains the given string or pattern. this could be multiple places in the database that will contain this string and i want to know all the locations.

So, let me get this straight.

You have a number of tables, table1..tablen

Each has a number of columns, column1..columnn

You want to find a string. It will exist as a row in a table, in a column. But you don't know which table and column.

Is this accurate?

djsteffey

Author

165

djsteffey

Apr 20, 2009 at 1:05 AM

kyoryu...that is correct.
the database is not my own and i don't nkow where everything in it is stored. So i am looking for a known specific string that is in the database but am unsure of its location. I want to know its location.

Zahlman

257

Zahlman

Apr 20, 2009 at 2:59 AM

And what are you going to do once you find this string?

Codeka

155

Codeka

Apr 20, 2009 at 2:59 AM

What you want is full-text indexing, I believe. Kind of like how google works or Windows Desktop Search or something like that. If you're using Java there's Lucene, or if you're using .NET, theres, um, Lucene.NET. I don't believe Oracle has full-text indexing built in, but maybe they do (SQL Server does, for example)

_Kami_

Apr 20, 2009 at 5:46 AM

or you can dump the entire database into a text file and run grep on it.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.