Advice for dblinks between two prod DBs.

Hey all,

I've been reading through the Distributed Database Concepts part of the 10gR2 Admin Guide to get a better understanding of how to setup dblink(s), which I've avoided since 7.4 (8.0?) when I first messed with them. After a developer inquired about using dblinks for our new ERP, I'm thinking I have a need for them now. Here's my scenario:

• We're launching a new ERP system, which uses Oracle 10.2.0.2.0 EE (.3 if it comes out soon).
• New ERP is both client/server and n-tier and does not use binds.
• I do not want to use CURSOR_SHARING and all of it's happy caveats.
• With the shared pool pressure I'm seeing in the test ERP DB, I think the best place for our home-grown apps is another DB.
• Our home-grown apps require access to ERP data via ERP's API (views and packages in the ERP DB).
• Each home-grown app has it's own schema.
• All of home-grown app SQL is in DB packages (and some procedures and functions).
• I don't think there'll be any user accounts in home-grown app DB.

I have a million questions about distributed query and transaction performance, but first the setup. I want this to be usable, but flexible. My knee jerk says to create two dblinks for each app account -- one for queries, the other for DML. My main reason for this is security -- each dblink's remote account would need to be explicitly granted access to the specific ERP API views/packages needed. Is this overboard? One dblink for each app? Or one public dblink for all app accounts? <shudder>

I'm also looking for pointers to dblink hints and gotchas, like if I find an icky query running on the remote DB, how do I trace it back to the other DB (and, therefore, to the client)?. My search terms seem to be too generic to turn up anything of much use.

TIA!
Rich

--
http://www.freelists.org/webpage/oracle-l