Find data in Oracle 7.2.3

[oaklandar]

I need to make a report but not sure where the data is located. I did a command to find all my tables:
select * from all_tables

Anyway to do a search to find information in my Oracle 7.2.3 database?

I need to find three words and there are over 150 tables to search. Please advise.

 

[carp]

If you know what the words are related to, you might be able to narrow it down a bit with
SELECT table_name, column_name
FROM all_tab_columns
WHERE datatype IN ('CHAR', 'VARCHAR2');
But if you have a lot of tables, you are looking for the proverbial needle in a haystack!

 

[carp]

If you know what the words are related to, you might be able to narrow it down a bit with

SELECT table_name, column_name
FROM all_tab_columns
WHERE data_type IN ('CHAR', 'VARCHAR2');

But if you have a lot of tables, you are looking for the proverbial needle in a haystack!
Let us know if you need more assistance - we can probably come up with a dynamic script you could use in a pinch.

 

[SantaMufasa]

Oaklandar,

I have some followup questions before I propose a solution for you:

1) Are your target words in the table names or in row data?

2) Are you looking for a single string of three words, or are you looking for three separate strings, one word each?

3) As far as you know, are there any LONG or CHAR columns in any of the candidate tables?

4) Is it possible that the target strings are in tables owned by SYS or SYSTEM?

Let me know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:48 (10Feb05) UTC (aka "GMT" and "Zulu"),
@ 08:48 (10Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[oaklandar]

1) Are your target words in the table names or in row data?
In Row data type

2) Are you looking for a single string of three words, or are you looking for three separate strings, one word each?
Three separate strings such as: San Francisco Bay
and the other one will be: AB/3DK/000/19934

3) As far as you know, are there any LONG or CHAR columns in any of the candidate tables?
Prob CHAR I think.

4) Is it possible that the target strings are in tables owned by SYS or SYSTEM
Not sure of this answer.

Thanks!!

 

[SantaMufasa]

Oaklander,

I have begun working on a script that should resolve your need. Unfortunately, I must leave presently for my monthly "Utah Oracle Users Group" planning meeting. I won't be back until mid-afternoon (Mountain Time). If you are willing to wait, I shall work on the script upon my return. If Carp, or other contributors resolve your need in the meantime, then wonderful.

I'll check on thread status as soon as I return.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:05 (10Feb05) UTC (aka "GMT" and "Zulu"),
@ 10:05 (10Feb05) Mountain Time
Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[oaklandar]

Thanks!!!

 

[roche786]

Did anyone come up with the answer for the above question?