Skip to Main Content
  • Questions
  • ROWTYPE declaration throws ORA-16000 error in standby database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kostya.

Asked: November 07, 2019 - 6:04 pm UTC

Answered by: Connor McDonald - Last updated: March 27, 2020 - 4:26 am UTC

Category: PL/SQL - Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 100+ times

You Asked

Hi Tom,

Could you please clarify, why I get the following error in standby database.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select log_mode, open_mode, database_role from v$database;

LOG_MODE     OPEN_MODE            DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> DECLARE
  tst_row  dual%ROWTYPE;
BEGIN
  SET TRANSACTION READ ONLY;
END;
/
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


Thanks.

and we said...

Looks like a bug. We try to compile the type definition and temporarily store it in a table (which of course is not permitted)

Reproduced in 18 and 19 as well. Even if using temporary undo or a global temporary table, you'll still get it.

I've got some more info for you. Assuming you are planning on doing some "real" work, ie, DML etc...then you can redirect your anonymous block to the primary, for example:

SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

SQL> alter session enable adg_redirect_plsql;

Session altered.

SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /

PL/SQL procedure successfully completed.




Is this answer out of date? If it is, please let us know via a Review

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database