from time to time I do a search in USER_SOURCE to look for the occurrence of a tablename or another word that I have written years ago.
I query the db like this:
select*from USER_SOURCE where instr(text,‘HATSCHI_BRATSCHI’) > 0;
HATSCHI_BRATSCHI is the expression I am looking for. I would like to have such a search in more advanced form (e.g. not case sensitive, include DBA_SOURCE …) in the search menue of the T.O.A.D., for instance a “Find in source” option.
Btw. in my recent install of TOAD 10.1 the schema browser complains constantly about datatype is not supported and refuses working.
Has anybody noticed a similar behaviour too or am I doing something wrong?
ORACLE is still 9iR2
Oh , how could I have overlooked this “search source” option.
Thank you Jeff and Wim.
Thank you Wim for the “upper” recommendation.
I have to confess that I do not use the object search very much because of 2 annoying circumstances:
There is no “select ALL” / “select NONE” / “restrict to favorites” option for the schemas box, if you have 250 schemas and you do not know where your expression could be, you have to click at least 250 times. An option ALL/NONE for the object classes you want to search also would be useful too in my opinion (“search object names” box).
Performance:
select * from dba_source where instr(lower(text),‘b_erbu’)>0
comes back after approximatly 2.5s, the same search done via object search I did cancel after 7 minutes. “Processing results…” message in the statusline did not disappear btw.
Anyway it does not matter anyhow. Because I am too lazy to write one simple line of SQL the TOAD needs not to be enhanced again.
I guess you did not try Right-Clicking in the "Schema to Search" or other boxes?
You should see a right click menu with those options. Remember in Toad if you
don't see something try right-clicking.
Right click on the lust of schemas box, select all, select none, invert
selection, select all except sys/system .....
Toad has lots of (hidden) options on the right click menus.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
lust of schemas box Norm - what's on your mind ????
Ah yes, I was hoping no-one would notice that slight slip up. My
spelling chequer certainly didn't!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Thanks for pointing out this utility, which I hadn’t used before.
However, when I set up search criteria and press the Search button, I get “ORA-00942: table or view does not exist”.
Any ideas what’s missing ? I’m using Toad 9.7.2.5
Cheers
Jason
Thanks for pointing out this utility, which I hadn’t used before.
However, when I set up search criteria and press the Search button, I get “ORA-00942: table or view does not exist”.
Any ideas what’s missing ? I’m using Toad 9.7.2.5
Cheers
Jason
ORA-99555 database administrator too old ;-(
Thanks everybody for remembering me.
Due to the spool SQL feature in TOAD I was able to isolate the slow object search query:
Select DISTINCT allsrc.owner, allsrc.TYPE result_type,
‘Source Search’ object_type, allsrc.NAME object_name
FROM SYS.DBA_SOURCE allsrc, SYS.DBA_OBJECTS allo
WHERE INSTR (UPPER(allsrc.text), ‘SOME STRING’) <> 0
AND allo.owner = allsrc.owner
AND allo.object_name = allsrc.NAME
AND allo.object_type = allsrc.TYPE
AND allsrc.TYPE IN (‘PACKAGE BODY’)
AND allo.status = ‘VALID’;
I was searching for only valid code.
Without the restriction to VALID (or invalid) code the query performs two hundred(!) times better with our DB. So this option should be set to “both” if you want to do a fast PL/SQL code search.
We have around 20k tables and approximatly 2 millions lines of source.
Just my 2 cents for the files…
Ed - Thanks, that’s a great tip!
When I got the SQL up, I just ran it in the editor, which revealed I didn’t have access to SYS.ALL_JOBS (wierd, as I DO have access to SYS.DBA_JOBS). This is easily worked round by unticking 'Jobs’in the ‘Search object names’ list.
Thanks for your help
Jason
