Linux下磁碟信息導出

--將disk使用信息保存到資料庫中
create table disk_status(snap_id date,USED_STATUS varchar2(4000));
alter table disk_status add constraint pk_ds_snapid primary key(snap_id);

[oracle@data1 backup]$ more auto_collect_disk.sh
#!/bin/bash
rm /home/oracle/sql/backup/disk_status.lst
df>/home/oracle/sql/backup/disk_status.lst

DISK_STATUS=`cat /home/oracle/sql/backup/disk_status.lst`
echo $DISK_STATUS
sqlplus -s xxxx/yyyy<<!;
insert into disk_status values(trunc(sysdate),'$DISK_STATUS');
commit;
exit
!

[oracle@data1 oracle]$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.21703 installed on Mon Nov 22 09:46:47 2004)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
00 05 * * * /home/oracle/sql/backup/auto_collect_disk.sh >> /home/oracle/sql/backup/tmp.lst 2>&1

[oracle@www1 sql]$ more backup/auto_send_disk.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s xxxx/yyyy@zzzz<<!
set timing off
spool /home/oracle/sql/backup/disk_status.lst
set linesize 81
col used_status format a69
select to_char(snap_id,'yyyy-mm-dd') snap_id,used_status from disk_status where snap_id = trunc(sysdate);

set linesize 101
set pagesize 999
col "表空間名" format a12
col mbytes format 9999.99
col "已使用空間(M)" format 99999.99
col 使用比 format a8
col "空閑空間(M)" format 9999.99
col "最大塊(M)" format 9999.99
SELECT F.TABLESPACE_NAME "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') 使用比,
F.TOTAL_BYTES "空閑空間(M)",F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;

spool off
exit
!

mail -s "磁碟信息發布" ####@###.## < /home/oracle/sql/backup/disk_status.lst
rm /home/oracle/sql/backup/disk_status.lst

[oracle@www1 sql]$crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.32619 installed on Mon Nov 22 09:51:02 2004)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
30 05 * * * /home/oracle/sql/backup/auto_send_disk.sh >> /home/oracle/sql/backup/disk.lst 2>&1