| Global Temp Table and UNDO Generation [message #668416] |
Fri, 23 February 2018 06:05  |
 |
muthukrish104
Messages: 82
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 #668424 is a reply to message #668416] |
Fri, 23 February 2018 07:58  |
John Watson
Messages: 8965
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'); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
