locked
Cannot Execute SELECT query on READ ONLY Oracle database RRS feed

  • Question

  • User592604931 posted

    I've created a connection to a read only Oracle database but when trying to execute a very simple SELECT statement it generates the following error:

    "database open for read-only access"

    I've tried a datareader and dataadapter so its looking like a specific issue or protocol with the database that I am unaware of.

    Any help with this most appreciated.

    Thursday, January 20, 2011 6:01 AM

Answers

  • User269602965 posted

    Oracle requires that your Oracle connection user schema specifically have SELECT privileges on the schema and table you wish to SELECT FROM.

    If the connection user schema name is not the same as the data schema name,

    you will have to append the data schema name to the table name,

    Such as   SELECT * FROM MyDataSchemaName.MyDataTableName

    +++++++

    Read-only Mode has specific tablespace requirements as well.

    From Oracle Documentation.

    Opening a Database in Read-Only Mode

    Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

    If a query against a database in read-only mode uses temporary tablespace, for example to do disk sorts, then the issuer of the query must have a locally managed tablespace assigned as the default temporary tablespace. Otherwise, the query will fail. This is explained in "Creating a Locally Managed Temporary Tablespace".

    ++++++++

    If you are the database DBA, you can enable read-write mode with:

    ALTER DATABASE OPEN READ WRITE;
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 23, 2011 3:01 PM