磁盘管理之查看磁盘信息
-- 你可以定时用 df -h 的结果打入一个外部日志文件，然后，利用Oracle的“外部表”去访问这个“外部日志文件”就可以了，例如：
-- Step 1：写一脚本，定时将磁盘信息打入日志文件：
[oracle@localhost ~]$ more /data/obase/tbs_info/disk_info.sh
#!/bin/bash
#
source /home/oracle/.bash_profile
cd /data/obase/tbs_info/
echo "-------- Disk Used Info of Server: 10.4.1.12 --------" > /data/obase/tbs_info/disk_info.log
echo "-------- DateTime: `date` -------" >> /data/obase/tbs_info/disk_info.log
df -h >> /data/obase/tbs_info/disk_info.log
-- Step 2：用crontab 定时执行脚本(例如：每个小时的59分的时候执行一次（每小时执行一次)：
[oracle@localhost ~]$ crontab -e
59 * * * * /data/obase/tbs_info/disk_info.sh > /data/obase/tbs_info/disk_info.log
-- Step 2：创建TBS_INFO目录,并授权lym用户读、写权限。
CREATE DIRECTORY TBS_INFO AS '/data/obase/tbs_info/';
GRANT READ,WRITE ON DIRECTORY TBS_INFO TO LYM;
-- Step 3：监控各数据库服务器磁盘空间使用情况：
select dbms_metadata.get_ddl('TABLE','DISK_INFO') FROM DUAL;
---------------------------------------------------------
-- 3.1 外部表的创建语句：
CREATE TABLE "LYM"."DISK_INFO"
(
"TEXT" VARCHAR2(150)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "TBS_INFO"
ACCESS PARAMETERS
( records delimited by newline
nobadfile
nodiscardfile
nologfile
)
LOCATION
( 'disk_info.log'
)
)
REJECT LIMIT UNLIMITED
-- 3.2. 创建监控各数据库服务器磁盘空间的函数
CREATE OR REPLACE FUNCTION disk_info_func
RETURN VARCHAR2
AS
/******************************************************************************
** 功能：监控Oracle数据库服务器(10.4.1.12、10.4.1.13、10.4.1.21)
** 各磁盘空间的使用情况，如果其占用空间超过其总空间的80%，将获取其磁盘空间信息!
****************************************************************************/
v_cnt12 NUMBER(18,0);
v_cnt13 NUMBER(18,0);
v_cnt21 NUMBER(18,0);
v_disk_info VARCHAR2(4000);
v_disk_info_all VARCHAR2(4000);
BEGIN
SELECT count(*) AS cnt
INTO v_cnt12
FROM disk_info@tdw12_lym
WHERE ( ( text LIKE '%8_\%%' escape '\' OR text LIKE '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text LIKE '%/dev/sdb%' )
OR ( ( text like '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text NOT LIKE '%/dev/sdb%' );
SELECT count(*) AS cnt
INTO v_cnt13
FROM disk_info@tdw13_lym
WHERE ( ( text LIKE '%8_\%%' escape '\' OR text LIKE '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text LIKE '%/dev/sdb%' )
OR ( ( text like '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text NOT LIKE '%/dev/sdb%' );
SELECT count(*) AS cnt
INTO v_cnt21
FROM disk_info
WHERE ( ( text LIKE '%8_\%%' escape '\' OR text LIKE '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text LIKE '%/dev/sdb%' )
OR ( ( text like '%9_\%%' escape '\' OR text LIKE '%100\%%' escape '\' )
AND text NOT LIKE '%/dev/sdb%' );
v_disk_info := '';
v_disk_info_all := '';
IF v_cnt12 > 0 THEN
FOR i12 IN ( SELECT text as disk_info FROM disk_info@tdw12_lym ORDER BY rowid ) LOOP
v_disk_info := i12.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_info||chr(10);
END LOOP;
END IF;
IF v_cnt13 > 0 THEN
FOR i13 IN ( SELECT text as disk_info FROM disk_info@tdw13_lym ORDER BY rowid ) LOOP
v_disk_info := i13.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_info||chr(10);
END LOOP;
END IF;
IF v_cnt21 > 0 THEN
FOR i21 IN ( SELECT text as disk_info FROM disk_info ORDER BY rowid ) LOOP
v_disk_info := i21.disk_info;
v_disk_info_all := v_disk_info_all||v_disk_i
