Oraclue

Oracle internals, debugging and undocumented features

Script – How to find table inside PL/SQL

SELECT owner,
       object_type,
       object_name,
       object_id,
       status
  FROM sys.DBA_OBJECTS
 WHERE object_id IN
          (    SELECT object_id
                 FROM public_dependency
           CONNECT BY PRIOR object_id = referenced_object_id
           START WITH referenced_object_id =
                         (SELECT object_id
                            FROM sys.
                                  DBA_OBJECTS
                           WHERE     owner = ‘<owner_name>’
                            AND object_name ='<table>’
                            AND object_type = ‘TABLE’))

Advertisements

One response to “Script – How to find table inside PL/SQL

  1. Asif Momen April 13, 2011 at 9:09 am

    You can use DBA_DEPENDENCIES view to get the information:

    SQL> select owner, name, referenced_owner, type from dba_dependencies where REFERENCED_NAME = ‘T’;

    OWNER NAME REFERENCED_OWNER TYPE
    ———- ———- —————- —————–
    TEST TEST_PROC TEST PROCEDURE

    SQL>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: