oracle12c - Oracle 12c - Find if temporary objects created before TEMP_UNDO_ENABLED is set

Question

Database : Oracle 12c (12.1.0.2) - Enterprise Edition with RAC

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...

Note 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.

question from:https://stackoverflow.com/questions/65911669/oracle-12c-find-if-temporary-objects-created-before-temp-undo-enabled-is-set

Answer 1

There is no holistic way to do this satisfying all cases. Posting some options I got as answer elsewhere:

Assumptions : Both options work only if:

• Only GTT is concerned (excluding WITH and other temporary objects)
• COMMIT/ROLLBACK has not already been done including from SAVEPOINTS or other methods

Option 1 : Use v$tempseg_usage, to check if any segment created in DATA, instead of TEMP_UNDO

select count(*)
  from v$tempseg_usage
 where contents = 'TEMPORARY'
   and segtype = 'DATA'
   and session_addr =
       (select saddr
          from v$session
         where sid = sys_context('userenv', 'sid'));

Option 2 : Use gv$transaction as below, ubafil = 0 if for temp_undo, else ubafil = undo tablespace file id:

select count(*)
  from gv$transaction
 where ses_addr = (select saddr
                     from v$session
                    where sid = sys_context('userenv', 'sid'))
   and ubafil <> 0;

On other note for thought, I still think, there should have been a parameter or an indication elsewhere that simply indicates the setting of TEMP_UNDO_ENABLED has not had an effect, within the scope of a SESSION, not having to touch views that would otherwise be considered as administrative.

I'm open to answers if someone finds a better approach.