Automatic Temporary Tablespace and undo tablespace Shrink in Oracle21c

Automatic Temporary Tablespace Shrink

Temporary tablespaces are used for storing temporary data and Users will notice high temporary tablespace usage when using sorts, hash joins and query transformations and DBA needs to manually size the temporary tablespace.

The automatic temporary tablespace helps to take care of below operations automatically

• Shrink temporary tablespace to reclaim unused space
• Grow temporary tablespace based upon high temp usage

You can run below query to identify the free space

SQL> SELECT * FROM dba_temp_free_space;

Or

SQL> SELECT TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

Automatic Undo  Tablespace Shrink

Oracle Database creates and manages the information needed to roll back, or undo, changes before they are committed. These undo records are stored in the undo segments in an undo tabs pace.

An undo tablespace is used for undo management, it helps to undo data or rollback any transaction. In 11g Oracle introduced automatic undo management it helps to manage undo segments in a database. The UNDO_MANAGEMENT parameter is set to AUTO or null enables automatic undo management and UNDO_TABLESPACE specifies the name of the undo tablespace.

Use UNDO_RETENTION initialization parameter to specify minimum undo retention period in seconds and you will see better results with a fixed-size undo tablespace when using Automatic tuning of undo retention. The Undo Advisor can help you estimate the capacity and you can access through Oracle Enterprise Manager or using DBMS_ADVISOR or DBMS_UNDO_ADV package.

The undo tablespace can grow large and the easy way to reclaim the space from undo tablespace is to create a new undo tablespace and set the database with new undo tablespace and drop the old undo tablespace.

SQL> CREATE UNDO TABLESPACE UNDO2 DATAFILE '/u01/oradata/ORCL/undo02.dbf' SIZE 5G AUTOEXTEND ON ;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDO2;
SQL> DROP TABLESPACE UNDO1 INCLUDING CONTENTS AND DATAFILES;

Oracle 21c introduces automated way to recover the space, this feature shrinks the undo tablespace by dropping the expired segments and extents, also it performs the data file shrink if possible. Note that data file shrink is based upon allocated extents.

This feature takes care of below operations automatically

• Recovery space from transactions that are not active
• Allow large transactions to run successfully by recovering space from expired undo

Please see the example for using DBMS_ADVISOR for UNDO.

DECLARE
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.EXECUTE_TASK(tname);
END;
/

Where

TARGET_OBJECTS is the undo tablespace of the system
START_SNAPSHOT Starting snapshot in the AWR to perform analysis
END_SNAPSHOT Ending snapshot in the AWR to perform analysis
BEGIN_TIME_SEC The beginning time of the period and now.
END_TIME_SEC The ending time of the period and now

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE