DBA Hub

📋Steps in this guide1/2

Temporary Undo in Oracle Database 12c Release 1 (12.1)

Place undo segments for global temporary tables in the temporary tablespace in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
19 views
1

Enable/Disable Temporary Undo

In Oracle 12c, undo segments for global temporary tables are still managed using the conventional undo tablespace by default. Temporary undo is enabled/disabled using the parameter at session or system level. There are some caveats associated with this. - The temporary undo functionality is only available if the parameter is set to 12.0.0 or higher. - Once a session has created some undo for a global temporary table, whether using conventional or temporary undo, any attempt to alter the parameter setting will be ignored for the lifetime of the session. No error is produced. - Temporary undo is the default for standby databases, so any alteration to the parameter is ignored on standby databases.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
-- Session level
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

-- System level
CONN sys@pdb1 AS SYSDBA

ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;
2

Monitor Temporary Undo

Regular undo activity is monitored using the view. In Oracle database 12c, the view has been added to perform a similar function, but for temporary undo. Create a global temporary table to test against. Make sure the test user can access the view. The following test creates a new session, loads some data into the GTT, then checks the redo and undo usage. This example is not using temporary undo. We can see the insert produces 302 blocks of undo, which is why the statistics show a redo size of 2.9 million bytes of redo, generated to protect the undo. The following test creates a new session, enables temporary undo, loads some data into the GTT, checks the redo and undo usage, then checks the temporary undo usage using the view. Here we can see only a single byte of conventional undo is generated, which results in approximately 1000 bytes of redo. The of the view shows us 321 blocks of temporary undo have been used. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CONN test/test@pdb1

DROP TABLE my_temp_table PURGE;

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

CONN sys@pdb1 AS SYSDBA

GRANT SELECT ON v_$tempundostat TO test;

CONN test/test@pdb1
SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
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;

1000000 rows created.

Statistics
----------------------------------------------------------
         46  recursive calls
      15346  db block gets
       2379  consistent gets
         16  physical reads
2944564  redo size
855  bytes sent via SQL*Net to client
        986  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

SET AUTOTRACE OFF

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
USED_UBLK  USED_UREC
---------- ----------
       302       6238
SQL>

CONN test/test@pdb1

-- Enable temporary undo.
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
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;

1000000 rows created.

Statistics
----------------------------------------------------------
         25  recursive calls
      15369  db block gets
       2348  consistent gets
         16  physical reads
1004  redo size
853  bytes sent via SQL*Net to client
        986  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

SET AUTOTRACE OFF

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
USED_UBLK  USED_UREC
---------- ----------
         1          1
SQL>

-- Check temporary undo usage.
SET LINESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

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

BEGIN_TIME           END_TIME                UNDOTSN   TXNCOUNT MAXCONCURRENCY MAXQUERYLEN MAXQUERYID
UNDOBLKCNT
EXTCNT    USCOUNT SSOLDERRCNT NOSPACEERRCNT    CON_ID
-------------------- -------------------- ---------- ---------- -------------- ----------- -------------
----------
---------- ---------- ----------- ------------- ----------
24-NOV-2014 15:11:09 23-NOV-2014 15:17:30          3          2              0           0
321
4          1           0           0           0

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!