data dictionary

Posted on 2012-09-20
Last Modified: 2012-09-24
Want to search packages, views, table, functions, etc for an object name 'company_search' . What is the query from data dictionary?

Question by:mikensu12

    Expert Comment

    select * from all_objects where object_name = 'COMPANY_SEARCH';
    LVL 14

    Expert Comment

    select * from all_objects where OBJECT_NAME = 'company_search'
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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%';
    LVL 34

    Accepted Solution

    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.
    LVL 28

    Expert Comment

    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.
    LVL 5

    Expert Comment

    This data is recorded in the systemtables and a full list can be found at:
    LVL 73

    Expert Comment

    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.
    LVL 73

    Expert Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now