I have a database with a table called ALT_SEARCH_DRIVERS. It has appropriate materialized view logs created on it.
I have a second database in which I want to create a fast refreshable MV that is a simple select from that table in the first database. Because the second database has a very poor network link to the first, I export the data from the first database, import it, thereby creating a table called ALT_SEARCH_DRIVERS in the second database. And then I want to turn that newly-created table into an MV with this command:
create materialized view alt_search_drivers
on prebuilt table
refresh fast on demand
as select * from alt_search_drivers@dblink
When I run that command, however, I get an ORA-32349, which says "cannot reference prebuilt table in definition query". So I've hunted around the place for explanations, and what that seems to mean is that my 'as select * from' line can't mention the same table name as the name of the MV I'm trying to create.
But how then do I create a materialized view called ALT_SEARCH_DRIVERS when the master table is actually called ALT_SEARCH_DRIVERS, too?
Could someone enlighten me, please?! Linux, 64-bit, Oracle 11.2