Version : Oracle 12c (12.1.0.2) - EE 2-node RAC, active DG
I'm trying to reduce REDO and archive logs generated for my application and measure using V$SYSSTAT and corresponding archive logs using DBA_HIST* views.
In my application code on DB side, I'm using the session level setting of TEMP_UNDO_ENABLED to direct UNDO for gtt into temporary tablespace. The specific feature noted here.
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
INSERT INTO my_gtt VALUES...
I see the documentation has this quote:
..if the session already has temporary objects using regular undo, setting this parameter will have no effect
If I use a pure database session, I can ascertain that since no other temporary tables have been created/used before setting the parameter, the REDO logs generated are minimal. I can use a simple (select value from V$SYSSTAT where name= 'redo size') to see the difference.
However the actual application (Java) triggers this code through a JDBC session. As such, I'm unable to ascertain if before the call to 'ALTER SESSION..' there were any GTT or other temporary objects previously created/used in the session. The consequence of this is, if say a GTT was already used, then the call to 'ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE' simply ignores the setting without an indication. The code will continue logging UNDO & REDO in the normal tablespace, which is unintended.
Is there any way to query if this parameter TEMP_UNDO_ENABLED is already set/unset within the session, so that before I do a ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE I'll know for sure this 'will' or 'will not' have an effect?
Thanks in advance for inputs.