How to move 200 materialized views without having to re-create in the new home

materialized-viewmigrationoracle

I am moving a database with 200 materialized views into a new server. All of them are 'fast refresh' materialized views , means they have corresponding materialized view logs, in this case sitting on another database.

I have spent countless hours tuning my 200 materialized views and would hate to re-create them in the new server (database).

Is there a way to move them without having to re-create them ?

Best Answer

If you are moving the database (backup on old server and restore on new server) the MVs will come along.

Are you creating an empty database on the new server? If so you could use Data Pump to export the MVs from the old and import them into the new.

I would opt for using a full RMAN (or filesystem) backup and restore over Data Pump.