Oracle Database Cheat Sheet

This blog post contains some random Oracle commands or SQL statements to have them at hand when they are needed.

Performance

# creating a table with a specific number of rows containing sample data  (max. around 5 billions on 12.2 (5 000 000 000))
# this table contains 1m rows and is about 160mb
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6 order by a1.object_id;
# delete all rows from a table except some (mainly for testing)
delete from t where rowid not in (select rowid from t fetch first 10 rows only);
# delete every other row
delete from t where rowid in (
    select rid from (select rowid as rid, row_number() over (order by rowid) as rn from t) where mod(rn, 2) = 1   -- delete odd rows; change to 0 for even rows
);
commit;
# create a plan without running the statement and show the plan
# only shows the plan Oracle would use
explain plan set statement_id ='st1' for select count(*) from t1;
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE','st1','ALL ALLSTATS'));
# run a query and show the plan (1)
# uses explain, so the actual plan might differ
set autotrace on -- to avoid output from the query: set autotrace traceonly
select count(*) from t;
# run a query and show the plan (2)
# shows the actual plan (from the correct child cursor) used to run the statement.
# LAST means only statistics of the last execution are shown (not sums)
set serverout off
alter session set statistics_level = all;
select count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null,'ALLSTATS LAST'));
/* useful format options: advanced outline hint_report(since 19) */
# shows the plans of all child cursors used for a statement where the SQL ID is known
# LAST means only statistics of the last execution are shown (not sums)
select * from table(dbms_xplan.display_cursor('sql_id', null, 'ALLSTATS LAST'));
# shows the plan of the last executed child cursor of a statement where the SQL ID is known
# LAST means only statistics of the last execution are shown (not sums)
variable sqlid varchar2(13 byte)
exec :sqlid := '73zvz39fjfnbf'
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(:sqlid,
  (select child_number from v$sql where sql_id=:sqlid order by last_active_time desc fetch first 1 rows only),
  'ALLSTATS LAST'));
# getting stats of the current session
set linesize 180
set pagesize 60
set trimspool on
set tab off
col name for a48
col event for a48
col stat_name for a48
col value for 999,999,999,999
select sn.name, ms.value from v$statname sn, v$mystat ms where ms.value!=0 and sn.statistic#=ms.statistic#;
select se.event, se.total_waits, se.total_timeouts, se.time_waited, se.max_wait
from v$session_event se
where se.sid=sys_context('userenv','sid'); /* time_waited and max_wait = hundredths of a second */
select tm.stat_name, tm.value 
from v$sess_time_model tm
where tm.sid=sys_context('userenv','sid') and tm.value!=0;
# find database segment based on file_id and block_id
SELECT  owner,segment_name,segment_type FROM  dba_extents WHERE  file_id=1 AND 41686 BETWEEN block_id AND block_id + blocks -1;
# show which db object a session is currently working on
select s.status, s.event, e.segment_type, e.owner, e.segment_name from v$session s, dba_extents e where s.p1=e.file_id and (s.p2 between block_id AND block_id + blocks -1) and s.sid=76;
# display bind variable values used in statements
select * from v$sql_bind_capture where sql_id='sql_id';
# or
select * from table(dbms_xplan.display_cursor('sql_id', null, 'ADVANCED'));
# use bind variables in sqlplus
variable B1 varchar2(32)
execute :B1 := 'S'
variable B2 nvarchar2(32)
execute :B2 := '6315223'
select :B1, :B2 from dual;
# use bind variables in SQL Developer
var v0 varchar2(32);
exec :v0 := '123';
select * from t where c1 = :v0;
# generate statement to set bind variables and values in SQL*Plus / SQLcl
select 'variable '||ltrim(name,':')||' '||DATATYPE_STRING from v$sql_bind_capture where sql_id='&sqlid' and child_number=0;
select 'exec '||name||' := '||case when datatype_string like 'VARCHAR%' then '''' end ||VALUE_STRING||case when datatype_string like 'VARCHAR%' then '''' end from v$sql_bind_capture where sql_id='&sqlid' and child_number=0;
# get file_no and block_no of table rows
select rownum, dbms_rowid.rowid_relative_fno(rowid) file_no, dbms_rowid.rowid_block_number(rowid) block_no from t;
# find a long running SQL statement
select sql_id,elapsed_time/1000000 elapsed_sec,sql_fulltext,last_active_time,parsing_schema_name from v$sql order by elapsed_sec desc;
# Run a Dictionary Integrity Check
exec dbms_hm.run_check('Dictionary Integrity Check', 'dict_int')
set long 10000
select dbms_hm.get_run_report('dict_int') from dual;
# Get database NLS parameters
select * from nls_database_parameters;
# create a 10046 trace (including wait events and bind variables)
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='trc1';
alter session set events '10046 trace name context forever, level 12';
< SQL Statement > z. B. select count(*) from dba_objects where owner='SYS';
alter session set events '10046 trace name context off';
Tracefile in $ORACLE_BASE/diag/rdbms/$SID/$SID/trace
# levels: 2=regular trace 4=regular+binds 8=regular+waits 12=regular+binds and waits
# or with dbms_monitor to trace another session
exec dbms_monitor.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>true, binds=>true)
exec dbms_monitor.session_trace_disable(session_id=>1234, serial_num=>1234)
# get dependant objects and their status
select o.owner,o.object_name,o.object_type,o.status from dba_dependencies d, dba_objects o where d.owner=o.owner and d.name=o.object_name and d.type=o.object_type and d.referenced_owner='USER1' and d.referenced_name='T' order by o.status;
# duration of last materialized view refresh
select round((last_refresh_end_time - last_refresh_date)*24*60) last_refresh_min from dba_mviews where mview_name='MV1';
# gather stats on all tables with stale stats
select 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',cascade=>true,no_invalidate=>false)' from dba_tab_statistics where owner='USER1' and stale
_stats='YES';
# gather stats on all indexes with stale stats
select 'exec dbms_stats.gather_index_stats('''||owner||''','''||index_name||''',no_invalidate=>false)' from dba_ind_statistics where owner='USER1' and stale_stats='YES';
# monitor long running sessions
select sofar*100/totalwork pct_done,opname,message from v$session_longops where sofar<>totalwork and totalwork<>0;
# disable the use of statistic feedback
alter session set "_optimizer_use_feedback"=false;
# disable dynamic sampling
alter session set optimizer_dynamic_sampling=0;
# SQL*Plus / SQLcl disable output of row data
set feed only
set feed on /* enable output of row data */

Optimizer Hints

These are some hints and how they can be used in SQL statements (see also here, here and here for some documentation, and here for a list of all hints of a specific Oracle release and patch level):

cardinality(@sel$1 t@sel$1 t2@sel$1 12345)  /* hint the optimizer to the number of rows a join produces */
cardinality(t 123)  /* hint the optimizer to the number of rows that are being returned from this table */
materialize /* avoid merging of CTEs into other statements, so the CTE is run only once */
use_nl(t t2)  /* use a nested loop join for these tables */
use_hash(t t2)  /* use a hash join for these tables */
use_merge(t t2)  /* use a sort-merge join for these tables */

opt_estimate

/*+ opt_estimate([<@qblock name>] <operation type> <p1> [<p2>] <row estimation>) */

Examples

• Using the cardinality hint to ‘correct’ or ‘simulate’ different join cardinality estimates

-- first we create two tables (t has 100 rows, t2 has 200 rows)
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=100 order by a1.object_id;
create table t2 tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=200 order by a1.object_id;
-- show the original explain plan and execution plan for a join
explain plan for select count(*) from t, t2 where t.object_name=t2.object_name;
select * from table(dbms_xplan.display);
select count(*) from t, t2 where t.object_name=t2.object_name;
select * from table(dbms_xplan.display_cursor(format=>'LAST +alias'));
-- now we tell the optimizer that we 'know' that the result of the join will be 12345 rows instead of 20000.
explain plan for select /*+ cardinality(@sel$1 t@sel$1 t2@sel$1 12345) */ count(*) from t, t2 where t.object_name=t2.object_name;
select * from table(dbms_xplan.display);
select /*+ cardinality(@sel$1 t@sel$1 t2@sel$1 12345) */ count(*) from t, t2 where t.object_name=t2.object_name;
select * from table(dbms_xplan.display_cursor(format=>'LAST +alias'));

SQL> -- first we create two tables (t has 100 rows, t2 has 200 rows)
SQL> create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=100 order by a1.object_id;

Table T created.

Elapsed: 00:00:01.232
SQL> create table t2 tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=200 order by a1.object_id;

Table T2 created.

Elapsed: 00:00:01.246
SQL> -- show the original explain plan and execution plan for a join
SQL> explain plan for select count(*) from t, t2 where t.object_name=t2.object_name;

Explained.

Elapsed: 00:00:00.004
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
_______________________________________________________________________________
Plan hash value: 373332881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    16 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    16 |            |          |
|*  2 |   HASH JOIN         |      | 20000 |   312K|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |   100 |   800 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |   200 |  1600 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_NAME"="T2"."OBJECT_NAME")

16 rows selected.

SQL_ID: ff5spha9pjuyu
Elapsed: 00:00:00.021
SQL> select count(*) from t, t2 where t.object_name=t2.object_name;

   COUNT(*)
___________
      20000

1 row selected.

SQL_ID: 5ta5v7dnz8tpn
Elapsed: 00:00:00.008
SQL> select * from table(dbms_xplan.display_cursor(format=>'LAST +alias'));

PLAN_TABLE_OUTPUT
_______________________________________________________________________________
SQL_ID  5ta5v7dnz8tpn, child number 0
-------------------------------------
select count(*) from t, t2 where t.object_name=t2.object_name

Plan hash value: 373332881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    16 |            |          |
|*  2 |   HASH JOIN         |      | 20000 |   312K|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |   100 |   800 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |   200 |  1600 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1
   4 - SEL$1 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_NAME"="T2"."OBJECT_NAME")


28 rows selected.

SQL_ID: a2jtn1cszkuy0
Elapsed: 00:00:00.024
SQL> -- now we tell the optimizer that we 'know' that the result of the join will be 12345 rows instead of 20000.
SQL> explain plan for select /*+ cardinality(@sel$1 t@sel$1 t2@sel$1 12345) */ count(*) from t, t2 where t.object_name=t2.object_name;

Explained.

Elapsed: 00:00:00.006
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
_______________________________________________________________________________
Plan hash value: 373332881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    16 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    16 |            |          |
|*  2 |   HASH JOIN         |      | 12345 |   192K|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |   100 |   800 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |   200 |  1600 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_NAME"="T2"."OBJECT_NAME")

16 rows selected.

SQL_ID: ff5spha9pjuyu
Elapsed: 00:00:00.029
SQL> select /*+ cardinality(@sel$1 t@sel$1 t2@sel$1 12345) */ count(*) from t, t2 where t.object_name=t2.object_name;

   COUNT(*)
___________
      20000

1 row selected.

SQL_ID: b7urq6xt1t000
Elapsed: 00:00:00.009
SQL> select * from table(dbms_xplan.display_cursor(format=>'LAST +alias'));

PLAN_TABLE_OUTPUT
_______________________________________________________________________________
SQL_ID  b7urq6xt1t000, child number 0
-------------------------------------
select /*+ cardinality(@sel$1 t@sel$1 t2@sel$1 12345) */ count(*) from
t, t2 where t.object_name=t2.object_name

Plan hash value: 373332881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    16 |            |          |
|*  2 |   HASH JOIN         |      | 12345 |   192K|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |   100 |   800 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |   200 |  1600 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1
   4 - SEL$1 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_NAME"="T2"."OBJECT_NAME")


29 rows selected.

SQL_ID: a2jtn1cszkuy0
Elapsed: 00:00:00.027
SQL>

Setting session parameters with a logon trigger

run as sysdba:
create or replace trigger sys.tr_trace
after logon on database
begin
  if (lower(user) = 'user1')  -- database user name
  --if (sys_context('userenv', 'host') = 'app_server_01')
  --if (sys_context('userenv', 'client_program_name') like 'oracle sql developer%')
  --if (sys_context('userenv', 'module') like 'oracle sql developer%')
  --if (sys_context('userenv', 'os_user') = 'user1')
  then
    execute immediate 'alter session set optimizer_dynamic_sampling=0';  -- avoid dynamic sampling statements in tracefile
    execute immediate 'alter session set statistics_level=all';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set tracefile_identifier=''10046_trace''';
    -- level 1(standard), level 4(binds), level 8(waits), level 12(binds & waits)
    execute immediate 'alter session set events ''10046 trace name context forever, level 1''';
  end if;
exception
  when others then
    null;
end;
/

alter trigger sys.tr_trace disable;
alter trigger sys.tr_trace enable;
drop trigger sys.tr_trace;

Alternative:

-- create as user1:
create or replace trigger user1.tr_stp
after logon on user1.schema
begin
  execute immediate 'alter session set sql_translation_profile=STP1';
exception
  when others then
    null;
end;
/

Gather system statistics

exec dbms_stats.gather_dictionary_stats
exec dbms_stats.gather_fixed_objects_stats
exec dbms_stats.gather_system_stats('NOWORKLOAD')
exec dbms_stats.gather_system_stats('INTERVAL',interval=>1440)  /* 24h */
or to gather workload statistics with manual start/stop:
exec dbms_stats.gather_system_stats('START')
exec dbms_stats.gather_system_stats('STOP')
/* after 24h the system statistics can be checked with: */
select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by pname;

Creating extended statistics

-- check if a extension already exists
select * from dba_stat_extensions where owner='USER1' and table_name='T1';
-- check to see if column usage information already exists 
select dbms_stats.report_col_usage('USER1', 'T') from dual;
exec dbms_stats.seed_col_usage(null,null,20); -- 20 seconds
-- run explain plan or run statement
select dbms_stats.report_col_usage('USER1', 'T') from dual;
-- create extended stats
select DBMS_STATS.CREATE_EXTENDED_STATS('USER1','T','(OBJECT_ID, OBJECT_NAME)') from dual;
exec dbms_stats.gather_table_stats('USER1','T')
-- evtl. create histograms
exec dbms_stats.gather_table_stats('USER1','T',method_opt=>'for columns SYS_STU7658I6BO6DV4TIRY9BH#7AB size 1024',cascade=>true,no_invalidate=>false)
-- to drop an extension
exec dbms_stats.drop_extended_stats('USER1','T','(OBJECT_ID, OBJECT_NAME)' )

Remove duplicate rows from a table

• For example if a primary key constraint cannot be created due to duplicate rows in a table
  • ORA-02437: cannot validate (USER1.PK1) – primary key violated
  • also check that the primary key columns contain values (not null)
• or if a unique key constraint cannot be added due to duplicate rows:
  • ORA-02299: cannot validate (USER1.UK1) – duplicate keys found
• from: asktom.oracle.com

-- adjust table and column names
delete from table1 where rowid in (
select rid from (select rowid rid, row_number() over (partition by col1 order by rowid) rn from table1) where rn <> 1
);
commit;

Remove rows that have no parent key

• Can be used if a foreign key constraint cannot be created due to:
  • ORA-02298: cannot validate (USER1.FK1) – parent keys not found

-- adjust table and column name
delete from child_table where col1 not in (select parent_col from parent_table);
commit;

Showing enabled Events

• Enabled Events can be shown systemwide, for another session or for the current session
• Currently works in SQL*Plus (not in SQLcl or SQL Developer)

# systemwide
oradebug setmypid
oradebug eventdump system

# in another session
--get the ospid / orapid of the other session
select p.pid orapid, p.sosid ospid, s.machine,s.osuser,s.module from v$process p, v$session s where p.addr=s.paddr and s.module='sqlplus.exe';
oradebug setospid 1234
or
oradebug setorapid 1234

oradebug eventdump session

# current session
oradebug setmypid
oradebug eventdump session

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
Statement processed.
SQL> alter session set events '10046 trace name context forever, level 1';

Session altered.

SQL> oradebug eventdump session
sql_trace level=1
SQL> alter session set events '10153 trace name context forever, level 1';

Session altered.

SQL> oradebug eventdump session
10153 trace name context forever, level 1
sql_trace level=1
SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> alter session set events '10153 trace name context off';

Session altered.

SQL> oradebug eventdump session
Statement processed.
SQL>

Parsing the listener.log to find anomalies

This Windows Powershell snippet can be used to parse a listener log and check what programs from what IP try to connect to the database

# Adjust the location of the listener.log file
# Read the Oracle listener log file as a single string (handles embedded newlines)
# Using Default (ANSI) encoding matches the typical Windows Oracle log format
$c = [System.IO.File]::ReadAllText(
    "C:\app\oracle\diag\tnslsnr\win4\listener\trace\listener.log",
    [System.Text.Encoding]::Default
)

# Extract all occurrences of PROGRAM=... followed by ADDRESS=...HOST=<IPv4>
# (?s) makes dot match newlines, so broken log entries are captured
$m = [regex]::Matches($c, '(?s)PROGRAM=([^)]+).*?ADDRESS=.*?HOST=([\d\.]+)')

# Hashtable to count unique (Program, HostIP) pairs
$h = @{}
foreach ($x in $m) {
    $k = "$($x.Groups[1].Value)|$($x.Groups[2].Value)"
    $h[$k] = $h[$k] + 1
}

# Convert hashtable to objects, split key, cast count to int, sort descending
$r = $h.GetEnumerator() | ForEach-Object {
    $p = $_.Key -split '\|'
    [PSCustomObject]@{
        HostIP  = $p[1]
        Count   = [int]$_.Value
        Program = $p[0]
    }
} | Sort-Object { $_.Count } -Descending

# Dynamically determine column widths
$maxIP  = ($r.HostIP | ForEach-Object { $_.Length } | Measure-Object -Maximum).Maximum
$maxCnt = ($r.Count | ForEach-Object { $_.ToString().Length } | Measure-Object -Maximum).Maximum
$padIP  = [Math]::Max($maxIP, "Client IP".Length) + 2
$padCnt = [Math]::Max($maxCnt, "Connections".Length) + 2

# Format string for aligned columns
$fmt = "{0,-$padIP} {1,-$padCnt} {2}"

# Output: header, separator, then data (all at once)
& {
    $fmt -f "Client IP", "Connections", "Program Name"
    $fmt -f ("-" * $padIP), ("-" * $padCnt), "------------"
    $r | ForEach-Object { $fmt -f $_.HostIP, $_.Count, $_.Program }
}

Client IP   Connections   Program Name
----------- ------------- ------------
11.1.1.169  2979          DBeaver 25?3?2 ? View sessions
11.1.1.169  125           SQLcl
11.1.1.151  97            JDBC Thin Client
11.1.1.151  81            S:\csharp_odp_test\bin\Release\OracleOdpNetApp.exe
11.1.1.169  66            JDBC Thin Client
11.1.1.169  18            SQL Developer
11.1.1.169  17            C:\sw\csharp_odp_test\bin\Release\OracleOdpNetApp.exe
11.1.1.169  14            DBeaver 25?3?4 ? View sessions
11.1.1.183  12            C:\Program?Files??x86?\Microsoft?Visual?Studio?9.0\Common7\IDE\devenv.exe
11.1.1.169  10            Oracle SQL Developer for VS Code/25.4.0
11.1.1.169  10            DBeaver 26?0?0 ? View sessions
11.1.1.183  9             JDBC Thin Client
11.1.1.169  8             SQLcl-MCP
11.1.1.169  6             Oracle SQL Developer for VS Code/25.4.1
11.1.1.169  6             C:\Windows\system32\odbcad32.exe
11.1.1.169  5             DBeaver 26?0?0 ? Main
11.1.1.169  5             DBeaver 26?0?0 ? Metadata
11.1.1.151  4             S:\csharp_odp_test\bin\Release\OracleOdpNetApp_single_100k.exe
11.1.1.169  4             DBeaver 25?3?2 ? Main
11.1.1.169  4             DBeaver 25?3?2 ? Metadata
11.1.1.169  3             DBeaver 25?3?4 ? Main
11.1.1.151  3             SQLcl
11.1.1.171  3             S:\csharp_odp_test\bin\Release\OracleOdpNetApp_single_100k.exe
11.1.1.183  2             C:\Windows\system32\odbcad32.exe
11.1.1.183  2             C:\Windows\syswow64\odbcad32.exe
11.1.1.169  2             Oracle SQL Developer for VS Code/25.2.2
11.1.1.169  2             DBeaver 25?3?4 ? Metadata
11.1.1.169  2             DBeaver 26?0?0 ? SQLEditor ?Script?1?sql?
11.1.1.151  2             C:\csharp_odp_test\bin\Release\OracleOdpNetApp.exe
11.1.1.169  1             Oracle SQL Developer for VS Code/25.3.1
11.1.1.169  1             DBeaver 25?3?2 ? SQLEditor ?Script?1?sql?
11.1.1.169  1             DBeaver 26?0?0 ? SQLEditor ?Script?2?sql?
11.1.1.171  1             S:\csharp_odp_test\bin\Release\OracleOdpNetApp.exe
11.1.1.169  1             DBeaver 25?3?4 ? SQLEditor ?Script?1?sql?
11.1.1.169  1             S:\csharp_odp_test\bin\Release\OracleOdpNetApp.exe
11.1.1.169  1             .\OracleOdpNetApp.exe
11.1.1.169  1             DBeaver 26?0?0 ? SQLEditor ?Script?3?sql?

To do the same on Linux we can use the following python snippet

python3 -c "
import re, collections
f = open('/u01/app/grid/diag/tnslsnr/lin1/listener/trace/listener.log', 'r', encoding='utf-8', errors='ignore')
c = collections.Counter()
matches = re.findall(r'(?s)PROGRAM=([^)]+).*?ADDRESS=.*?HOST=([\d\.]+)', f.read())
f.close()
[c.update({(m[1], m[0])}) for m in matches]
data = sorted(c.items(), key=lambda x: x[1], reverse=True)

# Column headers
h_ip = 'Client IP'
h_cnt = 'Connections'
h_prog = 'Program Name'

# Compute widths
w_ip = max(len(h_ip), max((len(ip) for (ip,_), _ in data), default=0))
w_cnt = max(len(h_cnt), max((len(str(cnt)) for _, cnt in data), default=0))
w_prog = max(len(h_prog), max((len(prog) for (_,prog), _ in data), default=0))

# Print table
print(f'{h_ip:<{w_ip}} {h_cnt:>{w_cnt}} {h_prog:<{w_prog}}')
print('-' * w_ip, '-' * w_cnt, '-' * w_prog)
for (ip, prog), cnt in data:
    print(f'{ip:<{w_ip}} {cnt:>{w_cnt}} {prog:<{w_prog}}')
"

Client IP  Connections Program Name
---------- ----------- -------------------------
11.1.1.172          38 Oracle REST Data Services
11.1.1.172           5 java
11.1.1.169           5 SQL Developer
11.1.1.169           3 SQLcl