0

i have 3 read only user on 3 oracle databases db1, db2,db3 then when i log onto pl/sql on db1 with it, i can perform the query

 select 1 X from dual@db2 union select 2 X from dual@db3

then i tried the jdbc code

　stmt=con.createStatement();
 stmt.executeQuery("select 1 X from dual@db2 union select 2 X from dual@db3");

i got ORA-16000 database open read only access error

then i tried all kinds of things like:

 con.setReadOnly(true)
 stmt.execute("set transaction read only");//also set transaction read/write, set transaction isolation level ...
 con.setAutoCommit(true);

whatever i can think, all got the same error.

What's the difference between JDBC and oracle client used by PL/SQL? how can i get this work?

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
user999377
  • 71
  • 1
  • 6
  • How are you creating the connection? Are you using an XA driver, for example? Which of the databases are open in read-only mode? – Justin Cave Sep 15 '15 at 22:43
  • No, it's not XA driver, just the common jdbc driver, all the users i have to access all those databases are read only users, not sure how to open the database in read only mode as you said? – user999377 Sep 15 '15 at 23:04
  • con=DriverManager.getConnection(driver, url,user,pwd); the driver is just the ojdbc14.jar – user999377 Sep 15 '15 at 23:06
  • Based on the error, at least one of the databases was opened by the DBA in read-only mode. That's pretty unusual-- perhaps a standby database that was opened in read-only mode? What is the URL that you use to open the connection and which JDBC driver are you using? – Justin Cave Sep 15 '15 at 23:17
  • i think the driver is oracle.jdbc.driver.OracleDriver, url is some thing like jdbc:oracle:host:port:sid, yes all of those my user only has read only access, and DBA doesn't want our user to update, so why set transaction only will not cause this as a read only query? why sql plus works? how to avoid of this error without granting permission to our user? – user999377 Sep 16 '15 at 12:55
  • The error has nothing to do with the privileges that your user has been granted. It has to do with the way that the DBA opened the database. The DBA has at least one of the databases opened in read-only mode so that no user, regardless of privileges, can modify it. That is very unusual. Something about your application is trying to do something that is not compatible with a database in read-only mode. My guess is that it's trying to create a distributed transaction. But that's just a guess. – Justin Cave Sep 16 '15 at 13:01
  • how to disable the distributed transaction in code? the "set transaction read only" can't avoid this? but why the pl/sql works with same user? when i try with only one database link , it works in code. only when multiple links , it will error out. when you are saying "DBA open the db in read only mode" it means that db is in read only mode even if the user has permission? – user999377 Sep 16 '15 at 22:30
  • and when query with 1 link it works, i think 1 link will also need a distributed transaction right? what's the difference between 1 link and 2+ links? – user999377 Sep 16 '15 at 22:38

0 Answers0