Home » RDBMS Server » Server Administration » Global Temp Table and UNDO Generation (oracle 12c Windows 2012)
Global Temp Table and UNDO Generation [message #668416] Fri, 23 February 2018 06:05 Go to next message
muthukrish104
Messages: 62
Registered: November 2017
Member
Hi All,

We came to know about the new feature of 12c version, that we can set the Undo Generation within the TEMP Segment for the GLOBAL TEMP TABLES.

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

And the query to monitor the usage of that is here:

SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;

When we tried this with a simple Global Temp tables.

Create temporary table t1 as select * from user_tables;

This one using the default method, ie-using the UNDO TABLESPACE instead of TEMP SEGMENT.

But, if we try this DML. Then it is using the TEMP SEGMENT, what could be the problem.

DROP TABLE my_temp_table PURGE;

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;


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;




Kindly help us.

Regards
M.Krish
Re: Global Temp Table and UNDO Generation [message #668417 is a reply to message #668416] Fri, 23 February 2018 06:09 Go to previous messageGo to next message
BlackSwan
Messages: 26289
Registered: January 2009
Location: SoCal
Senior Member
I don't see any problem.
I think that you suffer from Compulsive Tuning Disorder.
Did you start a new session to test with?
Re: Global Temp Table and UNDO Generation [message #668424 is a reply to message #668416] Fri, 23 February 2018 07:58 Go to previous messageGo to next message
John Watson
Messages: 7673
Registered: January 2010
Location: Global Village
Senior Member
I find it more helpful to compare the undo and redo generated with and without temp undo enabled:
select name,value from v$mystat natural join v$statname 
where name in ('redo size','undo change vector size');
Re: Global Temp Table and UNDO Generation [message #668453 is a reply to message #668424] Mon, 26 February 2018 03:28 Go to previous message
muthukrish104
Messages: 62
Registered: November 2017
Member
Hi John, Blackswan,

Thanks for the replies.

Yes, we tried both the way.

The actual scenario is, inserting 4 lacs rows in to a TEMP TABLE. The actual table size is 1.5 GB.

Thanks and regards
M.Krish
Previous Topic: ORA-14511: cannot perform operation on a partitioned object
Next Topic: ORA-16038, ORA-00312, ORA-16014
Goto Forum:
  


Current Time: Wed Nov 21 06:48:23 CST 2018