Oracle 18c

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

CREATE TABLE test_data ( value ) AS
SELECT 'LOCKED ENTITY: ACCOUNT
LOCKED BY USER: ops
LOCKED AT: 31/05/2004 11:47
CUST NOTES: <Please enter explanation here>
Customer notes are entered here.

UNLOCKED ENTITY: ACCOUNT
UNLOCKED BY USER: ops
UNLOCKED AT: 31/05/2004 11:49
UNLOCK NOTES: <Please enter explanation here>
Test

LOCKED ENTITY: USER
LOCKED BY USER: ops
LOCKED AT: 31/05/2004 11:50
LOCK NOTES: <Please enter explanation here>
Test

UNLOCKED ENTITY: USER
UNLOCKED BY USER: ops
UNLOCKED AT: 24/08/2009 16:47
UNLOCKED NOTES: <Please enter explanation here>' FROM DUAL;
1 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
WITH note_bounds ( value, start_pos, end_pos ) AS (
  SELECT REPLACE( value, CHR(13) ),
         1,
         INSTR( REPLACE( value, CHR(13) ), CHR(10) || CHR(10), 1 )
  FROM   test_data
UNION ALL
  SELECT value,
         end_pos + 2,
         INSTR( value, CHR(10) || CHR(10), end_pos + 2 )
  FROM   note_bounds
  WHERE  end_pos > 0
),
notes ( note ) AS (
  SELECT CASE end_pos
         WHEN 0
         THEN SUBSTR( value, start_pos )
         ELSE SUBSTR( value, start_pos, end_pos - start_pos )
         END
  FROM   note_bounds
),
note_data_bounds ( note, sep1, end1, sep2, end2, sep3, end3, sep4 ) AS (
  SELECT note,
         INSTR( note, ':', 1, 1 ),
         INSTR( note, CHR(10), 1, 1 ),
         INSTR( note, ':', 1, 2 ),
         INSTR( note, CHR(10), 1, 2 ),
         INSTR( note, ':', 1, 3 ),
         INSTR( note, CHR(10), 1, 3 ),
         INSTR( note, ':', 1, 5 )
  FROM   notes
)
SELECT SUBSTR( note, 1, sep1 - 8 ) AS action,
       SUBSTR( note, sep1 + 2, end1 - sep1 - 2 ) AS entity,
       SUBSTR( note, sep2 + 2, end2 - sep2 - 2 ) AS actor,
       TO_DATE( SUBSTR( note, sep3 + 2, end3 - sep3 - 2 ), 'DD/MM/YYYY HH24:MI' ) AS datetime,
       SUBSTR( note, sep4 + 2 ) AS notes
FROM   note_data_bounds;


ACTION
ENTITY
ACTOR
DATETIME
NOTES


LOCKED
ACCOUNT
ops
2004-05-31 11:47:00
<Please enter explanation here>
Customer notes are entered here.

UNLOCKED
ACCOUNT
ops
2004-05-31 11:49:00
<Please enter explanation here>
Test

LOCKED
USER
ops
2004-05-31 11:50:00
<Please enter explanation here>
Test

UNLOCKED
USER
ops
2009-08-24 16:47:00
<Please enter explanation here>

ACTION	ENTITY	ACTOR	DATETIME	NOTES
LOCKED	ACCOUNT	ops	2004-05-31 11:47:00	<Please enter explanation here> Customer notes are entered here.
UNLOCKED	ACCOUNT	ops	2004-05-31 11:49:00	<Please enter explanation here> Test
LOCKED	USER	ops	2004-05-31 11:50:00	<Please enter explanation here> Test
UNLOCKED	USER	ops	2009-08-24 16:47:00	<Please enter explanation here>