Tuesday, March 10, 2015

Listing Operating system disk usages with Oracle External Table PREPROCESSOR feature

This is based on one of my posting in OTN.
Database version and directory setups:

    SQL> SHOW user  
    USER is "SYS"  
    SQL>  
    SQL> -- My database version.  
    SQL> SELECT * FROM v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> --Creating directory object.  
    SQL> CREATE OR REPLACE DIRECTORY saubhik AS '/home/oracle/saubhik';  
      
    Directory created.  
    SQL> GRANT READ, WRITE, EXECUTE ON DIRECTORY saubhik to scott;  
      
    Grant succeeded.  
      
    SQL>
    
    Now, the OS setups:
    1. [oracle@localhost saubhik]$  
    2. [oracle@localhost saubhik]$ ## My Operating System.  
    3. [oracle@localhost saubhik]$ uname -a  
    4. Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
    5. [oracle@localhost saubhik]$ pwd  
    6. /home/oracle/saubhik  
    7. [oracle@localhost saubhik]$ cat free_space.sh  
    8. #This shell script is call by External Table's PREPROCESSOR claus.  
    9. # As per the external table definitin the file named as "frespace.txt" is passed as argument 1 ($1) to this script.  
    10. #!/bin/bash  
    11.   
    12. # Reading the output of the file into a varriable  
    13. a=`/bin/cat $1`  
    14.   
    15. # Listing the space informations  
    16. /bin/df -Pkh $a  
    17. exit  
    18. [oracle@localhost saubhik]$  
    19. [oracle@localhost saubhik]$ cat freespace.txt  
    20. /home  
    21. [oracle@localhost saubhik]$  
    Now the External Table setup:
    1. SQL>  
    2. SQL> SHOW USER  
    3. USER is "SCOTT"  
    4. SQL> --My database version.  
    5. SQL> SELECT * FROM v$version;  
    6.   
    7. BANNER  
    8. --------------------------------------------------------------------------------  
    9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    10. PL/SQL Release 11.2.0.1.0 - Production  
    11. CORE    11.2.0.1.0      Production  
    12. TNS for Linux: Version 11.2.0.1.0 - Production  
    13. NLSRTL Version 11.2.0.1.0 - Production  
    14.   
    15. SQL> -- Dropping the table before creating. You may not ned this.  
    16. SQL> DROP TABLE freespace_ext PURGE;  
    17.   
    18. Table dropped.  
    19.   
    20. SQL> --Creating the table.  
    21. SQL> ed  
    22. Wrote file afiedt.buf  
    23.   
    24.   1   CREATE TABLE "SCOTT"."FREESPACE_EXT"  
    25.   2     (       "FILESYSTEM" VARCHAR2(50 BYTE),  
    26.   3     "FSIZE" VARCHAR2(20 BYTE),  
    27.   4     "USED" VARCHAR2(20 BYTE),  
    28.   5     "FREE" VARCHAR2(20 BYTE),  
    29.   6     "USE_PER" VARCHAR2(20 BYTE),  
    30.   7     "MOUNTPOINT" VARCHAR2(20 BYTE)  
    31.   8     )  
    32.   9     ORGANIZATION EXTERNAL  
    33. 10      ( TYPE ORACLE_LOADER  
    34. 11        DEFAULT DIRECTORY "SAUBHIK"  
    35. 12        ACCESS PARAMETERS  
    36. 13        ( RECORDS DELIMITED BY NEWLINE  
    37. 14             PREPROCESSOR SAUBHIK: 'free_space.sh'  
    38. 15             skip 2  
    39. 16             badfile SAUBHIK:'freespace_ext%a_%p.bad'  
    40. 17             logfile SAUBHIK:'freespace_ext%a_%p.log'  
    41. 18             fields terminated by whitespace lrtrim  
    42. 19             missing field values are null (filesystem,  
    43. 20                                            fsize ,  
    44. 21                                            used ,  
    45. 22                                            free ,  
    46. 23                                            use_per ,  
    47. 24                                            mountpoint)  
    48. 25                                               )  
    49. 26        LOCATION  
    50. 27         ( "SAUBHIK":'freespace.txt'  
    51. 28         )  
    52. 29      )  
    53. 30     REJECT LIMIT UNLIMITED  
    54. 31*   PARALLEL 2  
    55. SQL> /  
    56.   
    57. Table created.  
    58.   
    59. SQL> -- A sample testing. Our /home/oracle/freespace.txt contains /home. So it should report the usages of that.  
    60. SQL> SELECT * FROM freespace_ext;  
    61.   
    62. FILESYSTEM                                         FSIZE                USED                 FREE                 USE_PER              MOUNTPOINT  
    63. -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------  
    64. Filesystem                                         Size                 Used                 Avail                Use%                 Mounted  
    65. /dev/mapper/VolGroup00-LogVol04                    2.0G                 104M                 1.8G                 6%                   /home  
    66.   
    67. SQL>  
    Now if you want it in a flexible way:
      SQL> SQL> SHOW USER USER is "SCOTT" SQL> --Creating a pipeline table function to get the free space. The mount point passed as an argument. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE freespace_typ AS OBJECT 2 ( filesystem VARCHAR2(50), 3 fsize VARCHAR2(20), 4 used VARCHAR2(20), 5 free VARCHAR2(20), 6 use_per VARCHAR2(20), 7 mountpoint VARCHAR2(20) 8* ) SQL> / Type created. SQL> CREATE OR REPLACE TYPE freespace_tbl_typ AS TABLE OF freespace_typ; 2 / Type created. SQL> ed Wrote file afiedt.buf 1 ---My wrapper function. 2 CREATE OR REPLACE 3 FUNCTION get_free_space 4 ( 5 pi_path VARCHAR2) 6 RETURN freespace_tbl_typ PIPELINED 7 AS 8 v_file_handle utl_file.file_type; 9 v_dir_name VARCHAR2(50):='SAUBHIK'; 10 v_max_linesize INTEGER :=32767; 11 v_file_name VARCHAR2(50):='freespace.txt'; 12 v_write_buffer VARCHAR2(4000); 13 BEGIN 14 v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize); 15 v_write_buffer:=pi_path; 16 utl_file.put_line(v_file_handle,v_write_buffer,TRUE); 17 utl_file.fclose(v_file_handle); 18 FOR i IN 19 (SELECT filesystem, 20 fsize , 21 used , 22 free , 23 use_per , 24 mountpoint 25 FROM freespace_ext 26 ) 27 LOOP 28 PIPE ROW (freespace_typ(i.filesystem,i.fsize,i.used,i.free,i.use_per,i.mountpoint)); 29 END LOOP; 30 RETURN; 31* END get_free_space; SQL> / Function created. SQL> SELECT * FROM TABLE(get_free_space('/')); FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('/u01')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 /dev/mapper/VolGroup00-LogVol04 2.0G 104M 1.8G 6% /home /dev/mapper/VolGroup00-LogVol03 5.7G 851M 4.6G 16% /tmp /dev/sda1 99M 13M 82M 14% /boot tmpfs 1.5G 719M 782M 48% /dev/shm 7 rows selected. SQL>

    No comments :

    Post a Comment