Oracle external table to read the pre-processed file output from a os process monitoring command and perform aggregation or analytics to get more intelligence

Reference: https://asktom.oracle.com/Misc/oramag/preprocess-external-tables.html

YouTube:

Step1) Ensure you have the file which we wanted to process using oracle external table is ready already

[oracle@vcentos79-oracle-sa1 OSpsstat]$ pwd /home/oracle/dba/OSpsstat [oracle@vcentos79-oracle-sa1 OSpsstat]$ ls -altr ... -rw-r--r--.  1 oracle oinstall  24526 Feb 13 23:03 tmp_st4_02 -rw-r--r--.  1 oracle oinstall  73604 Feb 13 23:03 processed_ps2.out drwxr-xr-x.  2 oracle oinstall   4096 Feb 14 00:02 . -rw-r--r--.  1 oracle oinstall    160 Feb 14 00:02 OSPSW_20515.bad -rw-r--r--.  1 oracle oinstall  19293 Feb 14 00:07 OSPSW_20515.log [oracle@vcentos79-oracle-sa1 OSpsstat]$ wc -l processed_ps2.out 752 processed_ps2.out [oracle@vcentos79-oracle-sa1 OSpsstat]$

Step2) Frame external table command from the output of the file present in os

Example:

create table indata1

(

    cust_id     number,

    cust_name    varchar2(20),

    credit_limit number(10)

)

organization external

(

    type oracle_loader

    default directory etl_dir

    access parameters

    (

       records delimited by newline

       fields terminated by ","

    )

location ('indata1.txt')

)

/

Actual:

create directory ospsw_data as '/home/oracle/dba/OSpsstat';

create table ospsw

(

sampltm date,

osusr varchar2(50),

ospid number,

osppid number,

prprty number,

pcpu varchar2(10),

pmem varchar2(10),

vsz number,

rss number,

wchain varchar2(50),

processstat varchar2(3),

processstart varchar2(20),

processdur varchar2(20),

processcmd varchar2(1000)

)

organization external

(

    type oracle_loader

    default directory ospsw_data

    access parameters

    (

       records delimited by newline

       fields terminated by ","

       (

        sampltm date "dd/mon/yyyy hh24:mi:ss",

        osusr char(50),

        ospid char(10),

        osppid char(10),

        prprty char(10),

        pcpu char(20),

        pmem char(20),

        vsz char(20),

        rss char(20),

        wchain char(50),

        processstat char(3),

        processstart char(20),

        processdur char(20),

        processcmd char(1000)

       )

    )

location ('processed_ps2.out')

)

/

Output:

SQL> drop table ospsw; Table dropped. SQL> create table ospsw ( sampltm date, osusr varchar2(50), ospid number, osppid number, prprty number, pcpu varchar2(10), pmem varchar2(10), vsz number, rss number, wchain varchar2(50), processstat varchar2(3), processstart varchar2(20), processdur varchar2(20), processcmd varchar2(1000) ) organization external (     type oracle_loader     default directory ospsw_data     access parameters     (        records delimited by newline        fields terminated by ","        (         sampltm date "dd/mon/yyyy hh24:mi:ss",         osusr char(50),         ospid char(10),         osppid char(10),         prprty char(10),   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32          pcpu char(20),         pmem char(20),         vsz char(20),         rss char(20),         wchain char(50),         processstat char(3),         processstart char(20),         processdur char(20),         processcmd char(1000)        )     ) location ('processed_ps2.out') ) / 33   34   35   36   37   38   39   40   41   42   43   44   45 Table created. SQL> select count(1) from ospsw;   COUNT(1) ----------        752 SQL>

step 3) Start querying the data and start to perform analytical/aggregation functions

SQL> select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1; SAMPLTM             ,SUM(TO_NUMBER(PCPU)),SUM(TO_NUMBER(PMEM)),  SUM(VSZ) --------------------,--------------------,--------------------,---------- 13/FEB/2025 22:44:42,                  .3,                 3.2,   7884632 13/FEB/2025 22:45:12,                  .3,                 3.2,   7884632 13/FEB/2025 22:45:42,                  .3,                 3.2,   7884632 13/FEB/2025 22:46:12,                  .3,                 3.2,   7884632 SQL>

Errors to be aware:

SQL> create table ospsw ( sampltm date, osusr varchar2(50), ospid number, osppid number, prprty number, pcpu number(5,4), pmem number(5,4), vsz number, rss number, wchain varchar2(50),   2    3    4    5    6    7    8    9   10   11   12   13  processstat varchar2(3), processstart varchar2(20), processdur varchar2(20), processcmd varchar2(1000) ) organization external (     type oracle_loader  14   15   16   17   18   19   20   21      default directory ospsw_data     access parameters     (        records delimited by newline  22   23   24   25         fields terminated by ","        (  26   27          sampltm date "dd/mon/yyyy hh24:mi:ss",  28          osusr char(50),         ospid number,  29   30          osppid number,  31          prprty number,         pcpu char(10),         pmem char(10),         vsz number,         rss number,  32   33   34   35   36          wchain char(50),         processstat char(3),         processstart char(20),         processdur char(20),         processcmd char(1000)        )     ) location ('processed_ps2.out') ) / 37   38   39   40   41   42   43   44   45 Table created. SQL> select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1; select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, convert_error, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned" KUP-01008: the bad identifier was: number KUP-01007: at line 6 column 15

This is because of usage of number data type in the access delimiter definition which isn’t accepted by oracle it seems.