How to view the original script of a materialized view?

Question

I am interested in moving a materialized view from one db to the other, regardless, I also need to change one of the columns. How can I view the original script that build the MV? I am running TOAD, but cannot seem to find the original script.

Thanks in advance!

Solution 1

You can use the function dbms_metadata.get_ddl:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME') from dual;

Solution 2

I ended up running:

select * from all_mviews where mview_name = ‘YOUR_MV_NAME’;

Solution 3

select query from user_mviews
  where mview_name = 'your materialized view';

Solution 4

If you use Oracle SQL Developer you just have to go to the "view" or "materialized view" node of the navigation tree

View more solutions

Comments

[Ben Page]

Use dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'VIEW', 'SCHEMA') if you need to specify a different schema.

[Eric Kramer]

Note that this will only show you views that your user owns (USER_MVIEWS)