Monday , September 25 2023

Oracle Temporary Tables and Active Data Guard

In today’s post, I will describe insert into oracle  temporary tables and Active Data Guard. We will try to insert into temporary tables on active data guard. Active Data Guard is a physical standby database that we can use only read-only.

Insert into the Temporary table is also not allowed, as it does not allow any operation to be performed on it that will generate Redo. Because Temporary Tablespace is used during insert into the Temporary table, a Redo is not produced, but because Undo Tablespace is used for Undo, it internally generates it in Oracle Redo.

Therefore, insert into the Global Temporary table is not allowed in versions prior to 12c. The following error is received when trying to enter data.

If we try to enter data we get the following error:

ERROR at line 1:

ORA-16000: database open for read-only access

In 12c this is resolved with the TEMP_UNDO_ENABLED parameter. Operations that use a Temporary Tablespace use a Temporary Undo Tablespace. Therefore, Redo is not created and we can insert into Temporary Tables.

We can use this parameter in both Primary and Physical Standby mode. When we use it on the primary side, Redo production is reduced by hundreds of times, while Redo is not produced on the Active Data Guard side.

In Active Data Guard, this parameter is enable by default.

Let’s Test.

1.We create a user in the primary database.

2.We authorize the user to connect to the database.

3.We authorize the user to create a temporary table.

The reason for granting the CREATE TABLE authorization is that it cannot create another table other than the temporary table, since it is already a Read-only database. When trying to create, an error is received as follows.

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database or pluggable database open for read-only access

4.We authorize the objects that the user can query.

5.We create the temporary table by switching to the TEMPUNDO user.

The reason why I write ON COMMIT DELETE ROWS while creating the table is because I don’t want the data in the table to be deleted at the end of the session.

6. We turn on autotrace to see statistics.

7.We make some adjustments to fix the error.


8.We switch to the TEMPUNDO user and open a trace.

9. We do a Bulk insert to see the statistics.

10.We see how much Undo is produced for this Redo produced.

11. The TEMP_UNDO_ENABLED parameter is set to TRUE to see the REDO information resulting from the same test.

13.We see the amount of Redo by making a bulk insert.

As you can see, when the TEMP_UNDO_ENABLED parameter is FALSE, 3070280 bytes REDO is produced in the same bulk insert, while 528 bytes REDO is produced when the parameter is set to TRUE.

14. The main effect of the parameter is seen in Active Data Guard Physical Standby Database. The current value of the parameter is queried.

15.  We see if the user and table created on the primary side are created on the Standby side.

16.We open the trace by connecting with the TEMPUNDO user.

17. We make bulk inserts in the same way

Since REDO is not produced, data can be inserted into this TEMPORARY table.




Leave a Reply

Your email address will not be published. Required fields are marked *