PL/SQL筆記2_如何去撈取確查某View實際定義

方法1.(最全面)

select * 
FROM all_VIEWS
where VIEW_NAME  = '<view_name>';

方法2.

select DBMS_METADATA.GET_DDL ('VIEW','view_name','owner') from dual;

方法3.某view有reference到哪些table跟view呢?

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;

Ref:

Get Oracle View Definition with limited control

https://stackoverflow.com/questions/31104012/get-oracle-view-definition-with-limited-control

Find tables and views used by specific view in Oracle database

https://dataedo.com/kb/query/oracle/find-tables-used-by-specific-view