data dictionary

Want to search packages, views, table, functions, etc for an object name 'company_search' . What is the query from data dictionary?

Who is Participating?
johnsoneSenior Oracle DBACommented:
ALL_OBJECTS would only give you the list of objects you have access to.  DBA_OBJECTS is a better choice to search, it will give you all objects.
select * from all_objects where object_name = 'COMPANY_SEARCH';
Muhammad Ahmad ImranDatabase DeveloperCommented:
select * from all_objects where OBJECT_NAME = 'company_search'
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Are you asking if the object is used in source code?

There is a DBA_DEPENDENCIES view:
select owner, name, type from dba_dependencies where referenced_name='COMPANY_SEARCH';

That will work if the procedure code doesn't use dynamic SQL.  IF if might then:
select name from user_source where upper(text) like '%COMPANY_SEARCH%';
Naveen KumarProduction Manager / Application Support ManagerCommented:
i will second johnsone comment that while searching, you should query DBA_OBJECTS view.

So can you try the below 2 queries and you should get some output if such object exists from one of the queries.

select * from DBA_OBJECTS where object_name = 'COMPANY_SEARCH';  

--> this will help to get you the object if it is created with just company_search as the object name without quotes enclosing the name.


select * from DBA_OBJECTS where object_name = 'company_search';  --> this will help to get you the object if it is created with "company_search" as the object name with quotes enclosing the obect name to preserve its exact name in lower case letters.
This data is recorded in the systemtables and a full list can be found at:
The techonthenet link above is  is not a full list of the dictionary tables.
In fact those aren't tables at all, but views.

It is however, as stated in the link, a set of commonly used views.
However the critical views have already been mentioned above.
if you're looking for a procedure called "company_search"  you can look in dba_procedures/all_procedures to see where it is defined whether it is a stand alone procedure or defined within a package.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.