As Tom indicated, this won't be quick. Tom noted a couple of different optimizations that would make it less inefficient, such as looking for all the columns in a particular table at once rather than doing a table scan for each column.
From a business perspective, what is the problem you're trying to sovle? It seems highly unusual that you would want to scan all character columns in all tables for a particular string with any sort of frequency. Why is it that you are searching for a string but you don't know where the string exists?
the user will just type the keyword( value in the coumn) he required and it should search all the tables and columns of the table in the database and i have to show this in the front ent in the table format. here the user will analyse the information based on the search .
it is just like the google search we does( type the keyword in free text) it will display the result.
so for that i have to search entire table and columns in the whole database.
please if any one provides me the solution it will be help full for me.
but here it is mandatory for me to search all the table and columns. here different users will update data in different tables and also uploads the document in the tables. so in order to analyze the data i have to go through all the tables and columns .
is there any other solutions. it is just like we are investigating and analyzing some thing based on the input string and output it display( just like linking from one record to another record and analyzing and making decisions etc).
No easy way out on this one, I'm afraid. Oracle Text simply isn't designed to do this.
If you want to use Oracle Text, you will simply have to figure out which tables and columns the search term could appear in.
Then you'll either have to create lots of text indexes on each table involved (probably using the multicolumn datastore) and search each individually, or you'll have to figure out some sort of huge view across all the tables, and use the user datastore to create a single index which spans all these columns (you'd have to create a "shadow" table with one row per row of the view in order to have something to create the index on).
If you want to search across multiple columns within just one table, then, as previously stated, you can use Oracle Text with a multi_coumn_datastore. There are plenty of examples in the online documentation and this forum. In the future, please start a new thread for your own question, instead of hijacking an old thread. You can provide a link to a related old thread.