Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [Physical-1] SQL> INSERT INTO my_temp_table WITH data AS ( SELECT 1 AS id FROM dual CONNECT BY level < 10000 ) SELECT rownum, TO_CHAR(rownum) FROM data a, data b WHERE rownum <= 1000000; INSERT INTO my_temp_table |
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.
1 2 3 | [Primary] SQL> create user tempundo identified by "1" account unlock; User created. |
2.We authorize the user to connect to the database.
1 2 3 | [Primary] SQL> grant create session to tempundo; Grant succeeded. |
3.We authorize the user to create a temporary table.
1 2 3 | [Primary] SQL> grant create table to tempundo; Grant succeeded. |
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.
1 2 3 | [Physical] SQL> create table employees_yedek as select * from hr.employees; create table employees_yedek as select * from hr.employees |
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.
1 2 3 4 5 6 7 | [Primary] SQL> grant select on v_$tempundostat to tempundo; Grant succeeded. [Primary] SQL> grant select on v_$transaction to tempundo; Grant succeeded. |
5.We create the temporary table by switching to the TEMPUNDO user.
1 2 3 4 5 6 7 | [Primary] SQL> conn tempundo/1 Connected. [Primary] SQL> show user USER is "TEMPUNDO" |
1 2 3 | [Primary] SQL> create global temporary table my_temp_table (id number, description varchar2(20)) on commit delete rows; Table created. |
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.
1 2 3 4 5 | [Primary] SQL> SET AUTOTRACE ON STATISTICS; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report |
7.We make some adjustments to fix the error.
1 2 3 4 5 6 7 | [Primary] SQL> conn / as sysdba Connected. [Primary] SQL> show user USER is "SYS" |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [Primary] SQL> create role plustrace; Role created. [Primary] SQL> grant select on v_$sesstat to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$statname to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$session to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$mystat to plustrace; Grant succeeded. [Primary] SQL> grant plustrace to dba with admin option; Grant succeeded. [Primary] SQL> grant plustrace to tempundo; Grant succeeded. |
8.We switch to the TEMPUNDO user and open a trace.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> conn tempundo/1; Connected. [Primary] SQL> show user USER is "TEMPUNDO" [Primary] SQL> set autotrace on statistics; |
9. We do a Bulk insert to see the statistics.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | [Primary] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 22 recursive calls 15687 db block gets 2359 consistent gets 343 physical reads 3070280 redo size 861 bytes sent via SQL*Net to client 986 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
10.We see how much Undo is produced for this Redo produced.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [Primary] SQL> SELECT t.used_ublk, 2 t.used_urec 3 FROM v$transaction t, 4 v$session s 5 WHERE s.saddr = t.ses_addr 6 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); USED_UBLK USED_UREC ---------- ---------- 653 12476 USED_UBLK: Number of Undo Blocks Used USED_UREC: Number of Undo Records Used |
11. The TEMP_UNDO_ENABLED parameter is set to TRUE to see the REDO information resulting from the same test.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [Primary] SQL> conn / as sysdba Connected. [Primary] SQL> alter system set temp_undo_enabled=TRUE scope=both; System altered. [Primary] SQL> show parameter temp_undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean TRUE 12.We log in with the TEMPUNDO user and open a trace. [Primary] SQL> conn tempundo/1 Connected. [Primary] SQL> show user USER is "TEMPUNDO" [Primary] SQL> set autotrace on statistics |
13.We see the amount of Redo by making a bulk insert.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | [Primary] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 23 recursive calls 15365 db block gets 2357 consistent gets 16 physical reads 528 redo size 857 bytes sent via SQL*Net to client 986 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
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.
1 2 3 4 5 6 7 8 9 10 | [Physical] SQL> show parameter temp_undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE |
15. We see if the user and table created on the primary side are created on the Standby side.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [Physical] SQL> select username from dba_users where username='TEMPUNDO'; USERNAME -------------------------------------------------------------------------------- TEMPUNDO [Physical] SQL> select object_name from dba_objects where owner='TEMPUNDO'; OBJECT_NAME -------------------------------------------------------------------------------- MY_TEMP_TABLE |
16.We open the trace by connecting with the TEMPUNDO user.
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical] SQL> conn tempundo/1 Connected. [Physical] SQL> show user USER is "TEMPUNDO" [Physical] SQL> set autotrace on statistics |
17. We make bulk inserts in the same way
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | [Physical] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 23 recursive calls 15397 db block gets 2341 consistent gets 152 physical reads 0 redo size 857 bytes sent via SQL*Net to client 987 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
Since REDO is not produced, data can be inserted into this TEMPORARY table.
