Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE your_table ( value ) AS
SELECT 'DUM_EI_AO_L_5864_Al Meena Tower' FROM DUAL UNION ALL
SELECT 'EI_AE_L_5864_Al radha Tower' FROM DUAL
2 rows affected

SELECT value,
       SUBSTR( replaced_value, first_separator + 1, second_separator - first_separator - 1 )
         AS second_term
FROM   (
  SELECT value,
         replaced_value,
         INSTR( replaced_value, '_', 1, 1 ) AS first_separator,
         INSTR( replaced_value, '_', 1, 2 ) AS second_separator
  FROM   (
    SELECT value,
           REPLACE(
             REPLACE(
               value,
               'PRJ_'
             ),
             'DUM_'
           ) AS replaced_value
    FROM   your_table
  )
)


VALUE
SECOND_TERM


DUM_EI_AO_L_5864_Al Meena Tower
AO

EI_AE_L_5864_Al radha Tower
AE


SELECT value,
       REGEXP_SUBSTR( value, '(DUM_|PRJ_)?.*?_(.*?)_', 1, 1, NULL, 2 ) AS second_term
FROM   your_table


VALUE
SECOND_TERM


DUM_EI_AO_L_5864_Al Meena Tower
AO

EI_AE_L_5864_Al radha Tower
AE

VALUE	SECOND_TERM
DUM_EI_AO_L_5864_Al Meena Tower	AO
EI_AE_L_5864_Al radha Tower	AE