(SOLVED) ORA-16000 error on connection with Read/Write access

Toad Data Point & Toad Intelligence Central
1

Toad Data Point 4.3, Oracle client 11G (R2):

My company has different connections based on what data needs to be accessed. (Separate regions will be restricted to certain connects (ie North, Southwest) to be able to access data from buildings in those areas. I was given read/write access to North but when i try running an UPDATE query it throws: Oracle Database Error: ORA-16000: database or pluggable database open for read-only access

I'm not sure of what steps need to be done to ensure an oracle connection in Toad data point has the proper setup to distinguish it as read/write instead of read only. Here is what I did (based on googling/toad install reference).

  • Toad data point 4.3 is installed as typical install (not read only mode (it has the symbols below the table dropdown to create a table and others)
  • Connections were added through ODBC data sources (both user dsn and system dsn) (I made sure to keep read only connection unchecked)

Is there anything i have to do (preferences wise) in Toad data point to fix this or is it more setup like ODBC. I'm at my wits end with this and would truly appreciate any help/pointers anyone could give.

2

Couple of questions/comments.

  • You may need to speak with the DBA to verify if the database instance you're connected to has been configured at the database level for read-only access.. e.g. that database could be a standby database, etc. The error suggests that the database was defined to be read-only, so there's nothing in TDP that will get around it if this is so. The DBA can lift the read-only restriction, or authorize you to use another database where you do have DML capabilties.

  • You mention ODBC, but this is not the preferred way to connect to the database. Can you not be authorized to use the Oracle client to connect? Again, the DBA should be able to help you with this.

3

I was setting it up that way because I was following the company documentation. After some experimenting, it seems that the ODBC setup had no impact, (i was connect directly through Oracle in Toad, if my terminoligy is correct).

As it happens, the issue was with my tnsnames.ora file. the host/port was wrong. I got a copy from another user that fixed the issue instantly. (I think the Read/write access was still pointing to a read-only "address")