Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Checking the status of disk usage on OS with the help of external table

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

10:32 AM By Bugra Canbakal 2 Comments


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 PL/SQL
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.

Oracle PL/SQL
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.

Oracle PL/SQL
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.

Oracle PL/SQL
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.

Share this:

  • LinkedIn
  • Twitter

Filed Under: Oracle Tagged With: database üstünden disk durumu, hpunix disk usage, linux disk usage, oracle.external table

Comments

  1. AvatarDinçer Kurnaz says

    Monday August 12th, 2013 at 02:01 PM

    Güzel bir blog yazısı olmuş saol

  2. AvatarBugra Canbakal says

    Tuesday August 13th, 2013 at 07:03 AM

    Merhaba begendiginize sevindim

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.