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