Checking the status of disk usage on OS with the help of external table

1.PURPOSE AND SCOPE

At this artichle i am going to explain how to check disk capacity and usage while using external table option.

Ps: I just test it on HP-UX and Linux servers.

2.PRACTICE

2.1. First of all we need to play output of df command. Our purpose is to eliminate space and tab on out and we will change them to comma.And also at script we are not going to take first line of output and we need to eliminate % character.After we eliminate % character we can save this coloum as a number at table and create some alert metrics for ourself. On the below i am going to provide normal df -Pk output and new version of it to save our table as like i talked about it.And i am going to save this output to df.log for other steps.

[oracle@orcl ~]$ df -Pk
Filesystem 1024-blocks Used Available Capacity Mounted on
/dev/mapper/VolGroup00-LogVol00 16473460 14497148 1125984 93% /
/dev/sda1 101086 27737 68130 29% /boot
tmpfs 831028 396756 434272 48% /dev/shm

[oracle@orcl ~] df -Pk | tr -s " " | sed 's/ /, /g' | sed '1 s/, / /g' | tr -d '%' | sed -n '1!p'  > /home/oracle/df.log
[oracle@orcl ~]$ cat /home/oracle/df.log
/dev/mapper/VolGroup00-LogVol00, 16473460, 14497140, 1125992, 93, /
/dev/sda1, 101086, 27737, 68130, 29, /boot
tmpfs, 831028, 396756, 434272, 48, /dev/shm

2.2. I installed file to /home/oracle directory, to read this file we need to create directory on database side and we need to provide grants to our user which is going to use external table to check disk output.

SQL> CREATE DIRECTORY home AS '/home/oracle';
Directory created.
SQL> GRANT READ ON DIRECTORY home to bcanbakal;
Grant succeeded.
GRANT WRITE ON DIRECTORY home to bcanbakal;
Grant succeeded.

2.3. And now we can create our external table.

SQL> show user
USER is "BCANBAKAL"
SQL> CREATE TABLE OS_TS (
 FILESYSTEM VARCHAR2(50 BYTE),
 BLOCKS NUMBER,
 USED NUMBER,
 AVALIABLE NUMBER,
 CAPACITY NUMBER,
 MOUNTED VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY home
 ACCESS PARAMETERS (
FIELDS TERMINATED BY ','
(FILESYSTEM,
BLOCKS,
USED,
AVALIABLE,
CAPACITY,
MOUNTED)
 )
 LOCATION ('df.log')
)
REJECT LIMIT UNLIMITED;
Table created.

2.4. Seems like our table created without any problem , lets check it out.

SQL> show user
USER is "BCANBAKAL"
SQL> select * from os_ts;
FILESYSTEM                                         BLOCKS     USED       AVALIABLE  CAPACITY   MOUNTED
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------------
/dev/mapper/VolGroup00-LogVol00                    16473460   14497140   1125992    93         /
/dev/sda1                                          101086     27737      68130      29         /boot
tmpfs                                              831028     396756     434272     48         /dev/shm

2.5. If you set some crontab job to for df output , you can see up-to-date data from your database.