Re: Question about VIEW table

chuckh_at_ix.netcom.com (Chuck Hamilton ) wrote:

>In <DC13MH.1D3_at_ncifcrf.gov> Fong_at_avpavp02.ncifcrf.gov writes:
>>
>>
>>Is there a way to tell waht is the base table name of a view table?
 

>Check out the following two tables....
 

>SYS.DBA_VIEWS, and USER_VIEWS.
>--
>><> Chuck Hamilton <><
>><> chuckh_at_ix.netcom.com <><

I would suggest the all_dependencies view in the catalog. The *_VIEWS tables will give you the original text of the query, but the all_dependencies table will allow you to get the table names without parsing the query text. For example (run as user SYS):

SQL> desc all_dependencies

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 NAME                            NOT NULL VARCHAR2(30)
 TYPE                                     VARCHAR2(12)
 REFERENCED_OWNER                         VARCHAR2(30)
 REFERENCED_NAME                 NOT NULL VARCHAR2(30)
 REFERENCED_TYPE                          VARCHAR2(12)
 REFERENCED_LINK_NAME                     VARCHAR2(128)
 

SQL> select referenced_owner, referenced_name, referenced_type   2 from all_dependencies
  3 where owner = 'SYS'
  4 and name = 'ALL_OBJECTS'
  5 and type = 'VIEW';  

REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_T
------------------------------ ------------------------------ ------------
SYS                            USER$                          TABLE
SYS                            OBJ$                           TABLE
SYS                            OBJAUTH$                       TABLE
 

This shows that the view ALL_OBJECTS is based on the USER$, OBJ$, and OBJAUTH$ table. Additionally, if the view was built on a view or used PL/SQL calls in its definition, this would be recorded in this table as well.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Jul 22 1995 - 00:00:00 CEST