Home » SQL & PL/SQL » SQL & PL/SQL » how to know any table is being used by any mviews
how to know any table is being used by any mviews [message #307429] Tue, 18 March 2008 20:05 Go to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member

Hi,
Please help me in finding if any perticular table is being used in any mviews.

There are views all_mviews/user_mviews/dba_mviews. the column 'query' is there that contains the script of the mview. But as the type is long I cannot use any function like substr/instr function or cannot use this column in the where clause also.

Regards,

Sudipta
Re: how to know any table is being used by any mviews [message #307437 is a reply to message #307429] Tue, 18 March 2008 21:29 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have a look at DBA_DEPENDENCIES
Re: how to know any table is being used by any mviews [message #307783 is a reply to message #307429] Wed, 19 March 2008 20:07 Go to previous messageGo to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member

Hi,
Thanks for your reply. DBA_dependencies could not help me much. Is there any function for long datatype like dbms_lob

Regards,

Sudipta
Re: how to know any table is being used by any mviews [message #307811 is a reply to message #307783] Thu, 20 March 2008 01:13 Go to previous message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why dba_dependencies don't help?
SQL> create MATERIALIZED VIEW mv as select * from t;

Materialized view created.

SQL> select referenced_owner, referenced_name 
  2  from dba_dependencies
  3  where owner='MICHEL' and name='MV';
REFERENCED_OWNER               REFERENCED_NAME
------------------------------ ------------------------
MICHEL                         T
MICHEL                         MV

2 rows selected.


Regards
Michel
Previous Topic: Need detail records but also need sum in same query
Next Topic: Performance Issue in Query ?
Goto Forum:
  


Current Time: Wed Nov 13 06:39:55 CST 2024