Oracle 21c

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

WITH data AS (SELECT '00000001' shift_id,  TO_TIMESTAMP('2022-05-17 08:00:00', 'YYYY-MM-DD HH24:MI:SS') timestamp_oracle, '0001' type_ FROM DUAL UNION ALL
              SELECT '00000001', TO_TIMESTAMP('2022-05-17 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0002' FROM DUAL UNION ALL
              SELECT '00000001', TO_TIMESTAMP('2022-05-17 09:15:00', 'YYYY-MM-DD HH24:MI:SS'), '0003' FROM DUAL UNION ALL
              SELECT '00000001', TO_TIMESTAMP('2022-05-17 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0002' FROM DUAL UNION ALL
              SELECT '00000001', TO_TIMESTAMP('2022-05-17 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0003' FROM DUAL UNION ALL
              SELECT '00000001', TO_TIMESTAMP('2022-05-17 15:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0004' FROM DUAL),
CALC AS (SELECT d.*,
                EXTRACT(DAY FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 24 * 60 +
                EXTRACT(HOUR FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 60 +  
                EXTRACT(MINUTE FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) tm
  FROM data d)
  SELECT shift_id,
         TRUNC(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end))/ 60) || ':' ||
         MOD(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end)), 60) tot_tm
    from calc
   GROUP BY shift_id;

SHIFT_ID	TOT_TM
00000001	5:45