ROWTYPE declaration throws ORA-16000 error in standby database

May 2024 ยท 2 minute read

I have the following pl/sql:

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 

Could somebody explain why I get ORA-16000 here?

4

1 Answer

In the end, this question was answered here.

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. 

But unfortunately the ADG_REDIRECT_PLSQL command was introduced only in Oracle 19c. See this.

10.2.2.2 Running Top-level PL/SQL Operations on Active Data Guard Standby Databases

Top-level PL/SQL blocks that you run on Active Data Guard standby databases can be redirected to and run on the primary database, if they do not contain bind variables.

To redirect top-level PL/SQL operations that are run on a standby to the primary, configure automatic redirection using the following command on the standby database:

 ALTER SESSION ENABLE ADG_REDIRECT_PLSQL; 

You can configure automatic redirection for top-level PL/SQL operations only at the session level.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobHFuYWaGeH2Oq6awrKmlsm6wxJyjmqqRqbawuoytn6unp6h6sL7AZmhvaGBleqa%2B0aipZqGeYsC1rc2dmbJllJbBoq7ArJw%3D