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.
|
1 2 3 4 5 6 7 8 9 10 11 |
[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.
|
1 2 3 4 5 6 |
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.
|
1 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 |
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.
|
1 2 3 4 5 6 7 8 |
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.
Güzel bir blog yazısı olmuş saol
Merhaba begendiginize sevindim