Toad World® Forums

MVIEW SCRIPT not 'materialized view query'


#1

In viewing a MVIEW in the schema brower there is SCRIPT and ‘materialized view query’ tab.

I am trying to get the script from the oracle tables.

Most online help uses

– get mview text from data dictionary
select dbms_metadata.get_ddl (‘materialized_view’, mview_name, owner)
into v_mview_sql
from dba_mviews
where owner = v_mview_owner and mview_name = v_mview_name;

But that gives you the ‘materialized view query’ not the SCRIPT.

I need to check this two parameters in many MVs.

START WITH TO_DATE(‘27-Jul-2015 11:21:29’,‘dd-mon-yyyy hh24:mi:ss’)

NEXT SYSDATE+9999/24

As to can see NEXT SYSDATE+9999/24 is for 400+ days so I need to

see the two Parameters.

THANKS


#2

There is no one query that you can execute to get a script for a MView. Toad runs several queries and then it has to put the pieces together to create the script.

NEXT and START_WITH can be found in DBA_SNAPSHOTS.