RDBMS REDEFINITION

[Roshan]

Oracle 19c

Hello,

hopw you are fine.

is it possible to measure how much to increase UNDO? I am getting this error during RDBMS_REDEFINITION start.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5757
ORA-06512: at line 2


Regards,
Roshan

[ErmanArslansOracleBlog]

Yes. The following MOS Note will help you on that ->

IF: How to monitor Undo Tablespace Usage and the Free Space (Doc ID 1951400.1)

You will use all the following ;

 v$datafile a, v$tablespace b, dba_tablespaces , dba_free_space , dba_undo_extents  and all that.

A complete query is available in the NOTE:

with free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ) , a as ( select tablespace_name , sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb from dba_undo_extents where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') group by tablespace_name ) , undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb from dba_tablespaces ts join dba_data_files df using (tablespace_name) where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;