To return the output from a linux/unix command to Oracle as table data, one can use an external table with a "preprocessor". The table DDL below creates such a table, the fields in this example had to be listed a second time as I wanted to specify a date format mask for one of the fields, otherwise they would not all need to be listed again.
CREATE TABLE sar (
sample_time date,
user_cpu NUMBER,
nice_cpu NUMBER,
system_cpu NUMBER,
iowait_cpu NUMBER,
steal_cpu NUMBER,
idle_cpu NUMBER
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY rik_ora_dat
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR rik_ora_dat:'sar.sh'
FIELDS TERMINATED BY ","
(sample_time DATE mask 'YYYY-MM-DD HH:MI:SS AM'
,user_cpu
,nice_cpu
,system_cpu
,iowait_cpu
,steal_cpu
,idle_cpu
)
)
LOCATION('pi.apache.access_logs.141211.csv2')
);
The script sar.sh referred to above is shown below. The script could be much simpler than the one below, but the one below made for a simpler external table definition (no SKIP, no conditionality, etc).
#!/bin/bash
#
export S_TIME_FORMAT=ISO
/usr/bin/sar | /bin/sed 's/ */ /g' | /bin/awk '
BEGIN {
OFS=","
}
NR == 1 {
sar_date = $4
}
/all/ && !/Average/ {
print sar_date " " $1 " " $2,$4,$5,$6,$7,$8,$9
}
'
With the above in place, then the linux sar command can be queried thus:
SQL> set lines 132 pages 60
SQL> ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI';
Session altered.
SQL> SELECT *
2 FROM sar;
SAMPLE_TIME USER_CPU NICE_CPU SYSTEM_CPU IOWAIT_CPU STEAL_CPU IDLE_CPU
---------------- ---------- ---------- ---------- ---------- ---------- ----------
12/12/2014 13:40 3.35 0 2.16 .31 0 94.17
12/12/2014 13:50 2.29 0 1.88 .02 0 95.82
12/12/2014 14:00 2.12 0 1.62 .05 0 96.21
12/12/2014 14:10 2.12 0 1.82 .07 0 95.99
12/12/2014 14:20 2.03 .33 3.01 1.07 0 93.56
12/12/2014 14:30 2 0 1.61 .04 0 96.36
12/12/2014 14:40 2.03 0 1.79 .01 0 96.17
12/12/2014 14:50 2.03 0 1.72 .02 0 96.23
12/12/2014 15:00 5.52 0 2.14 .19 0 92.15
12/12/2014 15:10 35.37 0 3.92 .3 0 60.41
12/12/2014 15:20 21.1 0 4.74 .42 0 73.74
12/12/2014 15:30 4.13 0 1.83 .03 0 94.01
12/12/2014 15:40 5.74 0 1.91 .02 0 92.33
12/12/2014 15:50 2.31 0 1.79 .02 0 95.88
12/12/2014 16:00 9.71 0 1.92 .06 0 88.31
12/12/2014 16:10 19.79 0 2.94 .21 0 77.06
12/12/2014 16:20 6.05 0 2.08 .04 0 91.83
12/12/2014 16:30 3.74 0 1.69 .01 0 94.56
12/12/2014 16:40 7.17 0 2.07 .02 0 90.74
12/12/2014 16:50 5.02 0 2.01 .02 0 92.95
12/12/2014 17:00 3.32 0 1.73 .02 0 94.93
12/12/2014 17:10 7.32 0 2.29 .11 0 90.28
12/12/2014 17:20 3.75 0 2.15 .07 0 94.03
12/12/2014 17:30 2.91 0 2.28 .07 0 94.74
12/12/2014 17:40 2.68 0 2.12 .04 0 95.16
12/12/2014 17:50 2.9 0 2.03 .02 0 95.05
12/12/2014 18:00 2.69 0 1.77 .01 0 95.53
27 rows selected.
SQL>