Adding Mount Point Details in Database for report

1) Create a Directory in database

mkdir directory

2) Give permission to Directory

chmod 775 directory/

Sqlplus / as sysdba

create or replace directory exec_dir as '/home/oracle/checks/directory';

Create a Table in Database:

CREATE TABLE df  ( "FILESYSTEM" VARCHAR2(100),    "BLOCKS" NUMBER, "USED" NUMBER,  "AVAILABLE" NUMBER, "CAPACITY" VARCHAR2(10),    "MOUNT" VARCHAR2(100) ) ORGANIZATION external (   TYPE oracle_loader DEFAULT DIRECTORY exec_dir ACCESS PARAMETERS    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII preprocessor  exec_dir:'run_df.sh' READSIZE 1048576 SKIP 1 FIELDS TERMINATED BY WHITESPACE LDRTRIM      REJECT ROWS WITH ALL NULL FIELDS ( "FILESYSTEM" CHAR(255) TERMINATED BY WHITESPACE,       "BLOCKS" CHAR(255) TERMINATED BY WHITESPACE, "USED" CHAR(255) TERMINATED BY WHITESPACE,        "AVAILABLE" CHAR(255) TERMINATED BY WHITESPACE, "CAPACITY" CHAR(255) TERMINATED BY WHITESPACE,       "MOUNT" CHAR(255) TERMINATED BY WHITESPACE ) ) location ( exec_dir:'run_df.sh' ) ) ;

3) Creata the script in a directory created above

vi run_df.sh

#/bin/bash

/bin/df -Pl

4) Give Permission to script

chmod 775 run_df.sh

5) Query the Mount Point Details from Database

SET LINES 333

SET PAGES 666

col "MOUNT POINT NAME" format a16

SELECT MOUNT "MOUNT POINT NAME", ROUND(BLOCKS/1024/1024) "TOTAL_GB", ROUND(USED/1024/1024) "USED_GB", ROUND(AVAILABLE/1024/1024) "AVAIABLE_GB", CAPACITY "PERCENTAGE FULL" FROM df ORDER BY TOTAL_GB ;

Cheers