I just work my way from inside out
SQL> create or replace directory adump as '/u01/app/oracle/admin/db192/adump';
Directory created.
SQL>
SQL> create table et
2 (str varchar2(4000))
3 ORGANIZATION EXTERNAL
4 ( type oracle_loader
5 default directory adump
6 access parameters
7 ( records delimited by newline fields missing field values are null ( str position(1:4000) ) )
8 location ('db192_ora_6826_20190219000719844365733343.aud'));
Table created.
SQL>
SQL> select * from et;
STR
----------------------------------------------------------------------------------------------------------------------------------
Audit file /u01/app/oracle/admin/db192/adump/db192_ora_6826_20190219000719844365733343.aud
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
Build label: RDBMS_19.2.0.0.0_LINUX.X64_190204
ORACLE_HOME: /u01/app/oracle/product/19.2.0/dbhome_1
System name: Linux
Node name: db192.localdomain
Release: 4.1.12-94.2.1.el7uek.x86_64
Version: #2 SMP Wed Apr 26 15:32:38 PDT 2017
Machine: x86_64
Instance name: db192
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 6826, image: oracle@db192.localdomain (TNS V1-V3)
Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '268'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[3] '100'
Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '266'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[2] '44'
Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '266'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[2] '44'
Tue Feb 19 00:07:19 2019 -08:00
LENGTH : '335'
ACTION :[75] 'select lower(user) || '@'||lower(instance_name) global_name
from v$instance'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'
Tue Feb 19 00:07:34 2019 -08:00
LENGTH : '295'
ACTION :[33] 'alter pluggable database all open'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
USERHOST:[17] 'db192.localdomain'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[3] '227'
Tue Feb 19 00:07:41 2019 -08:00
LENGTH : '301'
ACTION :[39] 'alter pluggable database all save state'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '2928453766'
SESSIONID:[10] '4294967295'
...
114 rows selected.
So I know that I can see the raw data. Then I take the inner most part of the query
SQL>
SQL> select str
2 from
3 (
4 select str, rownum r,
5 max( case when str like 'Instance name: %' then substr(str,16) end) over () iname,
6 case when str like '___ ___ __ __:__:__ ____' then rownum end isdate
7 from et
8 )
9 where isdate is not null
10 or str like 'ACTION : %'
11 or str like 'DATABASE USER: %'
12 or str like 'PRIVILEGE : %'
13 or str like 'CLIENT USER: %'
14 or str like 'CLIENT TERMINAL: %'
15 or str like 'STATUS: %';
no rows selected
That looks like where the problem is. So I match up the criteria with my raw data, and see that the spacing has changed. So I adjust...
SQL>
SQL>
SQL> select str
2 from
3 (
4 select str, rownum r,
5 max( case when str like 'Instance name: %' then substr(str,16) end) over () iname,
6 case when str like '___ ___ __ __:__:__ ____' then rownum end isdate
7 from et
8 )
9 where isdate is not null
10 or str like 'ACTION :%'
11 or str like 'DATABASE USER:%'
12 or str like 'PRIVILEGE :%'
13 or str like 'CLIENT USER:%'
14 or str like 'CLIENT TERMINAL:%'
15 or str like 'STATUS:%';
STR
----------------------------------------------------------------------------------------------------------------------------------
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[75] 'select lower(user) || '@'||lower(instance_name) global_name
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[33] 'alter pluggable database all open'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[39] 'alter pluggable database all save state'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
ACTION :[34] 'alter session set container = pdb1'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
42 rows selected.
SQL>
SQL>
and things are looking the better.
The rest is left as a user exercise :-)