Oracle Database Discussions

DB version: 19c
OS: RHEL 8.4

I was running the below add column command on PALLET_DETAIL table which is 400 GB in size (non-partitioned).
But, it eventually errored out with ORA-30036

SQL> alter table PKMS_MASTER.PALLET_DETAIL ADD(BRIDGED_DT DATE DEFAULT sysdate, BRIDGED_TS TIMESTAMP(6) DEFAULT systimestamp);

alter table PKMS_MASTER.PALLET_DETAIL ADD(BRIDGED_DT DATE DEFAULT sysdate, BRIDGED_TS TIMESTAMP(6) DEFAULT systimestamp)
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Its a standalone DB (not RAC).
Its in local Undo mode. The PDB is allocated roughly 159 GB of UNDO tablespace.
When the above ALTER TABLE command was hanging, I queried dba_undo_extents view and got the below output.
Please see the output of Query3.

What do the UNEXPIRED/ACTIVE/EXPIRED values mean ?
From the output of Query3, can we determine whether the PDB has sufficient UNDO tablespace ?

alter session set container = PDB_SALES;

select tablespace_name, sum(bytes/1024/1024/1024) GBBytes, sum(maxbytes/1024/1024/1024) mxBytesGB
from dba_data_files where tablespace_name like 'UNDO%' GROUP BY tablespace_name;

TABLESPACE_NAME                   GBBYTES  MXBYTESGB
------------------------------ ---------- ----------
UNDOTBS1                       158.999939 159.999924

SQL> show parameter undo_retention
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
undo_retention                                               integer     14400

SQL> select 14400/60/60 from dual;

14400/60/60
-----------
         4
SQL>

-- Query 3
-- Listing UNEXPIRED/ACTIVE/EXPIRED Extents of Undo Tablespace

SQL> select  tablespace_name,   
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",         
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by    tablespace_name, status;

TABLESPACE_NAME                STATUS    Extent Count Total Blocks TOTAL_SPACE
------------------------------ --------- ------------ ------------ -----------
UNDOTBS1                       UNEXPIRED        18588      1077464  8.22039795
UNDOTBS1                       ACTIVE           12993     16567896  126.403015
UNDOTBS1                       EXPIRED          10728       108040  .824279785

-- after few minutes

SQL> /
TABLESPACE_NAME                STATUS    Extent Count Total Blocks TOTAL_SPACE
------------------------------ --------- ------------ ------------ -----------
UNDOTBS1                       UNEXPIRED        23401      1637824  12.4956055
UNDOTBS1                       ACTIVE           13048     16560800  126.348877
UNDOTBS1                       EXPIRED           6375        63320  .483093262

col PROPERTY_NAME for a20
col property_value for a20
select property_name, property_value from database_properties where  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME        PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED   TRUE

1 row selected.

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
          70533