we have microsoft sql server, oracle gateway server (11G) and database server (10.2.0.5)
we already make the full setup, and it's working.
we create materialized view into oracle server to read a data from sql server, but the refresh of MV take long time so
we are planing to create the materialized view into oracle gateway server.
my question is:
what is the best solution to create MV into oracle gateway server?!!
i mean i don't want to download oracle database instance for only one MV.
thanks in advanced.
You can't create a MView within the gateway itself. The gateway is just a process that handles the connections to a foreign database only works with an Oracle database.
So you have to create the MView in your Oracle database (in your 10.2.0.5 db) using the gateway database link to the SQL Server.
When a MView refresh takes a long time it might be related to the amount of records you need to replicate.
you are right,
but th second part of my question is:
what is the smallest type of database i can install it on Oracle Gateway server to create the MV on it, so i can create synonym to MV into oracle database server (10.2.0.5 db)
thanks for your help.
Just to clarify: There's normally no need to install a database on the gateway server. You can create the mview directly in your 10.2.0.5 database.
If you really want to have another database that contains the mview then install on the gateway box the Oracle database using the standard edition. There's no need to activate Enterprise Manager or other stuff.
Regarding the sizing of the memory it really depends on the amount of data you need to pull through the gateway and you want to select using the synonym. So there's no generic answer about the sizing.