Is there a way to copy/move materialised view from one DB service name to another in Oracle?

so what I want is to create materialised view in my service but from the tables in another service of Oracle. But as the table names are bit large, I can't do it. So was thinking of work around to create materialised view in the same service only where the tables are and then move/copy the view to my service. Is there such way to do that?

• what do you call a "service"? Terms I find more appropriate are "database" or "schema"
• what does it mean that "table names are bit large"?
  • is it about long table names; for example, table emp has 3 characters in length. Yours have ... how many?
  • or did you mean to say that they contain many rows? emp in Scott's sample schema contains 14 rows. How many rows do you have?
  • Why does any of these prevent you from doing what you planned to?

Anyway: if it is about another database, then you'll have to use a database link. If it is about another schema, then the owner (of those tables) will have to grant select privilege to you.

You can create a materialized view in a "source" schema/database and access it from anywhere else, as long as you're in the same network. Database link (or, in a simpler case, grant) will enable you to do that.

Usually, we create materialized views in our own schema and access source tables which reside elsewhere. If materialized view contains a lot of data and its query takes a long time to execute, then refreshing might also take quite a long time. Oracle allows you to choose the way you'll refresh data (for example, once a day - at 02:00 at night because nobody is working at that time so it doesn't matter that refresh takes 2 hours, or ...).

If, for some reason, you can't do that, then create it where source tables are and access the materialized view itself over the database link (or by preceding its name with its owner's name, or create a synonym in your schema (or perhaps even a public synonym).