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!

score 30 · Accepted Answer · answered Jun 24 '13 at 06:22

30

You can use the function dbms_metadata.get_ddl:

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

answered Jun 24 '13 at 06:22

Marco Baldelli

3,638
1
22
29

9

Use `dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'VIEW', 'SCHEMA')` if you need to specify a different schema. – Ben Page Jul 17 '15 at 09:50

score 9 · Answer 2 · answered May 19 '16 at 17:52

9

I ended up running:

select * from all_mviews where mview_name = ‘YOUR_MV_NAME’;

answered May 19 '16 at 17:52

manyways

4,386
2
23
19

score 7 · Answer 3 · answered Jun 24 '13 at 06:20

7

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

answered Jun 24 '13 at 06:20

Petr Pribyl

3,425
1
20
22

1

Note that this will only show you views that your user owns (USER_MVIEWS) – Eric Kramer Jan 27 '22 at 15:18

score 6 · Answer 4 · answered Jun 22 '17 at 11:00

6

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

answered Jun 22 '17 at 11:00

Icaro

2,527
1
15
9

How to view the original script of a materialized view?

4 Answers4