Global Temporary Table support from Standby

One of the primary use cases for physical standby is running read only workloads. 

But read only workloads are not 100% read only and may require the final results to be written. 

In this case, we can use the new features, the ability to read/write global temporary tables for physical standby (ADG configuration) version 12c. 

1. Configuration.

TEMP_UNDO_ENABLED changes the behavior of undo generated by temporary tables. By default undo generated by temporary tables goes to the undo tablespace just like DML generated against regular tables. Since the standby is in read only mode changes to the undo data files can only come from the primary. Since temporary tablespace use temp files not regular data files they are not under the same read only restricts as regular data or undo files. With the temp_undo_enabled set to true undo information is stored in the temporary tablespace instead of the undo tablespace.
Change the temp_undo_enabled parameter to true at the system level.

[STANDBY]:

SQL> alter system set temp_undo_enabled=true scope=both; 

2. Creation of structures.

With changing the temp_undo_enabled allows DML against temp tables it doesn’t allow changes against the data dictionary and creating temp tables does require data dictionary changes. So temp tables still must be created on the primary. 

[PRIMARY]:
SQL> 

CREATE GLOBAL TEMPORARY TABLE SUNOWER.TBL_QUOTES (NSYST NUMBER, DT_TRADING DATE, COD_TOOL VARCHAR2(30 BYTE)) ON COMMIT PRESERVE ROWS NOCACHE;

CREATE UNIQUE INDEX SUNOWER.PK_QUOTES ON SUNOWER.TBL_QUOTES(NSYST);

ALTER TABLE SUNOWER.TBL_QUOTES ADD (CONSTRAINT PK_QUOTES PRIMARY KEY(NSYST) USING INDEX SUNOWER.PK_QUOTES ENABLE VALIDATE);

CREATE SEQUENCE SUNOWER.TBL_QUOTES_SEQ START WITH 1 MAXVALUE 999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE GLOBAL;

CREATE OR REPLACE TRIGGER SUNOWER.TBL_QUOTES_TRG BEFORE INSERT ON SUNOWER.TBL_QUOTES REFERENCING NEW AS New OLD AS Old FOR EACH ROW

BEGIN

  :new.NSYST := TBL_QUOTES_SEQ.nextval;

END TBL_QUOTES_TRG;

/

3. Testing

On the standby we now have a temporary table that we can manipulate how we like.

[STANDBY]:

SQL> 

alter session set nls_date_format='mm.dd.yyyy hh24:mi:ss';

insert into TBL_QUOTES (DT_TRADING, COD_TOOL) values ('11.11.2022 02:51:51', 'SBER');

insert into TBL_QUOTES (DT_TRADING, COD_TOOL) values ('11.23.2022 14:23:11', 'GAZP');

insert into TBL_QUOTES (DT_TRADING, COD_TOOL) values ('11.28.2022 17:34:31', 'ROSN');

commit;

select * from TBL_QUOTES;