undo_tablespace - ORACLEAGENT BLOG

Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.

Undo tablespace check undo tablespace:

SQL> show parameter undo_tablespace;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

Check to undo retention:

SQL> show parameter undo_retention;

NAME                                    TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer            900

Check to undo management:

SQL> show parameter undo_management;

NAME                                     TYPE                   VALUE
------------------------------------ ----------- ------------------------------
undo_management                          string                  AUTO

create new undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORACLEAGENT/undo2.dbf' size 100m;

Tablespace created.

change undo tablespace

SQL> alter system set undo_tablespace='undotbs2' scope=spfile; 
System altered.

SQL> show parameter undo_tablespace;

NAME                                   TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes
 Database mounted.
Database opened.

SQL> show parameter undo_tablespace;

NAME                                   TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string           undotbs2

change undo_retention

SQL> show parameter undo_retention;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer             900

SQL> alter system set undo_retention=920;
System altered.

SQL> show parameter undo_retention; 

NAME                                    TYPE           VALUE
------------------------------------ ----------- ------------------------------
undo_retention                        integer           920 

check db block size

SQL> show parameter db_block_size;

NAME                                     TYPE              VALUE
------------------------------------ ----------- ------------------------------
db_block_size                            integer           8192

change undo management { auto | manual }

SQL> show parameter undo_management;
 NAME                                  TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_management                       string             MANUAL

SQL> alter system set undo_management='auto' scope=spfile; 
System altered.

SQL> shut immediate; 
Database closed.
Database dismounted. 
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes 
Database mounted.
Database opened.

SQL> show parameter undo_management;

NAME                                   TYPE            VALUE
------------------------------------ ----------- ------------------------------
undo_management                        string         AUTO

Undo stages:

SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, 
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;

TABLESPACE                      STATUS    SUM_IN_MB   COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1                        EXPIRED      1399.25     200
UNDOTBS1                        UNEXPIRED    168         21
UNDOTBS2                        EXPIRED      .375        6
UNDOTBS2                        UNEXPIRED     5.4375     42

Active, expired and unexpired transaction space usage in Undo Tablespace:-
ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.
UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.
Free blocks : At the time of creating new undo tablespace shows empty blocks

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: undo_tablespace

Undo tablespace check undo tablespace:

Undo stages:

Active, expired and unexpired transaction space usage in Undo Tablespace:-