Undo tablespace is used for the rollback of the transaction when commit or rollback occurs during the transaction.
The system will give preference to the DML operations against the undo retention low threshold may not be achieved.
If you want to must achieve the undo retention threshold then you have to use the clause RETENTION GUARANTEE while creating the undo tablespace.
By default, it is a NO GURANTEE state.
Parameter
UNDO_MANAGEMENT = AUTO/MANUAL (manually or automatic managment) default is automatic
UNDO_TABLESPACE = Name of UNDO table-space
Create undo tablespace in the database
CREATE UNDO TABLESPACE undotbs01 DATAFILE 'D:oradataundo01.dbf' SIZE 200M REUSE AUTOEXTEND ON;
Add datafile in undo tablespace
ALTER TABLESPACE undotbs01 ADD DATAFILE 'D:oradataundo01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Drop the undo tablespace
DROP TABLESPACE undotbs_01;
Switch undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02;
--Unassign the undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = '';
Check whether the guarantee minimum threshold is maintained or not for undo tablespace.
SELECT tablespace_name, retention FROM dba_tablespaces;
Make undo TABLESPACE to GUARANTEE mode
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
Default is no guarantee mode
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
Check the Optimal Value for the parameter UNDO RETENTION
For Calculating the Undo retention parameter based on the UNDO Size, then run the following query to get Undo retention parameter value.
Formula:Optimal Undo Retention = ACTUAL UNDO SIZE / (DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC)
Query:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
Get the optimal size of undo tablespace according to the undo retention parameter then you will fire the following query
Formula:Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_ESC
Query:
Check the Optimal Size for UNDO table space needed
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
Change/Alter the value of UNDO RETENTION
SHOW PARAMETER UNDO_RETENTION
ALTER SYSTEM SET UNDO_RETENTION = 2400 scope=both;