This is based on one of my posting in OTN.
Database version and directory setups:
Now, the OS setups:
Now the External Table setup:
Now if you want it in a flexible way:
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>
- [oracle@localhost saubhik]$
- [oracle@localhost saubhik]$ ## My Operating System.
- [oracle@localhost saubhik]$ uname -a
- 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
- [oracle@localhost saubhik]$ pwd
- /home/oracle/saubhik
- [oracle@localhost saubhik]$ cat free_space.sh
- #This shell script is call by External Table's PREPROCESSOR claus.
- # As per the external table definitin the file named as "frespace.txt" is passed as argument 1 ($1) to this script.
- #!/bin/bash
- # Reading the output of the file into a varriable
- a=`/bin/cat $1`
- # Listing the space informations
- /bin/df -Pkh $a
- exit
- [oracle@localhost saubhik]$
- [oracle@localhost saubhik]$ cat freespace.txt
- /home
- [oracle@localhost saubhik]$
- SQL>
- SQL> SHOW USER
- USER is "SCOTT"
- 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> -- Dropping the table before creating. You may not ned this.
- SQL> DROP TABLE freespace_ext PURGE;
- Table dropped.
- SQL> --Creating the table.
- SQL> ed
- Wrote file afiedt.buf
- 1 CREATE TABLE "SCOTT"."FREESPACE_EXT"
- 2 ( "FILESYSTEM" VARCHAR2(50 BYTE),
- 3 "FSIZE" VARCHAR2(20 BYTE),
- 4 "USED" VARCHAR2(20 BYTE),
- 5 "FREE" VARCHAR2(20 BYTE),
- 6 "USE_PER" VARCHAR2(20 BYTE),
- 7 "MOUNTPOINT" VARCHAR2(20 BYTE)
- 8 )
- 9 ORGANIZATION EXTERNAL
- 10 ( TYPE ORACLE_LOADER
- 11 DEFAULT DIRECTORY "SAUBHIK"
- 12 ACCESS PARAMETERS
- 13 ( RECORDS DELIMITED BY NEWLINE
- 14 PREPROCESSOR SAUBHIK: 'free_space.sh'
- 15 skip 2
- 16 badfile SAUBHIK:'freespace_ext%a_%p.bad'
- 17 logfile SAUBHIK:'freespace_ext%a_%p.log'
- 18 fields terminated by whitespace lrtrim
- 19 missing field values are null (filesystem,
- 20 fsize ,
- 21 used ,
- 22 free ,
- 23 use_per ,
- 24 mountpoint)
- 25 )
- 26 LOCATION
- 27 ( "SAUBHIK":'freespace.txt'
- 28 )
- 29 )
- 30 REJECT LIMIT UNLIMITED
- 31* PARALLEL 2
- SQL> /
- Table created.
- SQL> -- A sample testing. Our /home/oracle/freespace.txt contains /home. So it should report the usages of that.
- SQL> SELECT * FROM freespace_ext;
- FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT
- -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
- Filesystem Size Used Avail Use% Mounted
- /dev/mapper/VolGroup00-LogVol04 2.0G 104M 1.8G 6% /home
- SQL>
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