CODEWITHSUNDEEP
oracle-databaseoracle11goracle10goracle-sqldeveloper
Giancarlo
Giancarlo

Reputation: 179

query to get all tables in a materialized view

Good afternoon friends,

a query is there any way (select * from) to visualize which tables form a materialized view? ex:

CREATE MATERIALIZED VIEW table_vm
 
  REFRESH COMPLETE ON COMMIT
  as
SELECT * FROM table1;
UNION ALL
SELECT * FROM table2;

I would like to output something like this:

view name | table name table_m | Table 1 table_m | table 2 tabla_m | table 3 .... ....

Thank you so much,

I would appreciate any information.

Upvotes: 0

Views: 2717

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

One option would be

SQL> create table t1 ( c1 number, c2 varchar2(1) ) ;

Table created.

SQL>  create table  t2 ( c1 number , c3 varchar2(1) ) ;

Table created.

SQL> create table t3  ( c1 number , c3 varchar2(1) ) ;

Table created.

SQL> create materialized view mv1 refresh complete on demand as
  2  select a.c1 , b.c3 as c2, c.c3
  3  from t1 a inner join t2 b on a.c1 = b.c1
  4   left join t3 c on a.c1 = c.c1 ;

Materialized view created.

SQL> select name as mv, listagg(referenced_name || ' - ' || referenced_type , '|' ) 
 within group ( order by referenced_name ) as list_dep
 from dba_dependencies where name='MV1' and name != referenced_name 
 group by name

MV                             LIST_DEP
------------------------------ --------------------------------------------------
MV1                            T1 - TABLE|T2 - TABLE|T3 - TABLE

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6084

You can use the view DBA_DEPENDENCIES to view any dependencies for an object compiled into the database. Querying that view with the name of your materialized view should list all of the tables as well as any other dependencies the materialized view relies on. REFERENCED_OWNER and REFERENCED_NAME will hold the values of the tables being used by the materialized view.

SELECT *
  FROM dba_dependencies
 WHERE owner = 'OWNER_OF_MV' AND name = 'TABLE_MV';

Upvotes: 2

Related Questions