Find tables and views used by specific view in Oracle database

Article for: IBM Db2

Query below lists Query below lists:

(A) all referenced tables or views accessible to the current user in Oracle database by the specific view

(B) all referenced tables or views by the specific view in Oracle database

Query was executed under the Oracle12c Database version.

Query

A. Tables accessible to the current user

select owner as view_schema,
       name as view_name,
       referenced_owner as referenced_schema,
       referenced_name as referenced_name,
       referenced_type
from sys.all_dependencies
where type='VIEW'
      and referenced_type in ('TABLE','VIEW')
      -- and owner = 'SCHEMA_NAME' -- put schema name here
      -- and name = 'VIEW NAME'  -- put view name here
order by owner,
         view_name;

B. If you have privilege on dba_dependencies

select owner as view_schema,
       name as view_name,
       referenced_owner as referenced_schema,
       referenced_name as referenced_name,
       referenced_type
from sys.dba_dependencies
where type='VIEW'
      and referenced_type in ('TABLE','VIEW')
      -- and owner = 'SCHEMA_NAME' -- put schema name here
      -- and name = 'VIEW NAME'  -- put view name here
order by owner,
         view_name;

Columns

  • view_schema - view's schema name
  • view_name - view's name
  • referenced_schema - schema of the referenced object
  • referenced_name - name of the referenced object
  • referenced_type - Type of the referenced object
    • TABLE
    • VIEW

Rows

  • One row represents single reference from view to other view/table
  • Scope of rows: represent all references
  • Ordered by schema_name, view_name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.