Dear Experts,
DB Version : 11.2.0.4
OS Version : OEL 6.6 x86-64
I have a requirement in my environment.
I have a production database which has a dblink which connects to a remote database.
We use this dblink to fetch some data from inside our custom application whenever information is required.
I will mention then production db as P1 and remote as R below.
So currently the connectivity is like below.
P1 ---> R
(P1 has synonyms pointing to tables in R)
Now I have another new database, say it as P2.
I need to get the same data from R to P2. But there is no connection from P2 to R and no possibility of getting connection according to information security dept.
But they will give connection from P2 to P1.
I created a dblink from P2 to P1. (I connected to the schema in P1 which contains the synonyms to R).
I created synonyms in P2 which selects from synonym in P1. It works well.
I am able to fetch and see the data.
Hence it is like a casacaded db link. (Don't know whether anybody of you came across this scenario).
P2 ---> P1 ---> R
----------------------------------------------------------------------------------------------------------
According to Oracle, even a SELECT to a database link is considered as a transaction in the remote database and hence you need to finish the transaction by issuing commit/rollback.
----------------------------------------------------------------------------------------------------------
Now my real question/doubt is that, when I select/fetch the data from P2 and issue a commit/rollback to finish the transaction, whether it commits the transaction in P1 only or will it
be done both in P1 and R ?????
I want help/opinion you experts here regarding this and then only I can give green flag for this approach in the real production environment.
Thanks & Regards,
Mahesh.