This discussion is archived
5 Replies Latest reply: Sep 4, 2012 7:36 AM by kgronau RSS

Oracle Gateway plus materialized view

MiledMoukhtafi Newbie
Currently Being Moderated
Dear Sir,

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.
  • 1. Re: Oracle Gateway plus materialized view
    kgronau Guru
    Currently Being Moderated
    Could you please be more specific what you mean with "oracle gateway server". Do you have an Oracle database installed on this machine as well or is it just the Oracle gateway software?
  • 2. Re: Oracle Gateway plus materialized view
    MiledMoukhtafi Newbie
    Currently Being Moderated
    Dear sir,

    oracle gateway server: Server have Oracle gateway software only. and i'm asking how i can create MV on this server.
    or what is the litest oracle instance i can install on it.


    regards,
  • 3. Re: Oracle Gateway plus materialized view
    kgronau Guru
    Currently Being Moderated
    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.
  • 4. Re: Oracle Gateway plus materialized view
    MiledMoukhtafi Newbie
    Currently Being Moderated
    Dear Sir,

    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.
  • 5. Re: Oracle Gateway plus materialized view
    kgronau Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points