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>