[Shell] Linux monitor tablespace usage

linux monitor tablespace


1.create database link
先賦予create database link的許可權
SQL> grant create database link to mon;


Grant succeeded.


SQL> create database link yangxu connect to system identified by xxxxx_123 using 'yangxu';


Database link created.


2.create table
SQL> create table mon_tablespace(
  2  dbid varchar2(20),
  3  dbname varchar2(20),
  4  tablespace_name varchar2(24),
  5  total_size int,         
  6  free_size int,
  7  use_size int,
  8  usage number(5,2),
  9  mon_date date
  10  );


Table created




3.create view
查詢相關資訊
SQL> grant create view to mon;


Grant succeeded.


SQL> create view v_mon_tablespace
  2  as
  3  select 
  4  (select dbid from v$database) dbid,
  5  (select name from v$database) dbname,
  6  x.tablespace_name,y.bytes total_size,x.bytes free_size,(y.bytes-x.bytes) use_size,round((y.bytes-x.bytes)/y.bytes,4)*100 usage,
  7  sysdate as mon_date
  8  from 
  9  (select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) x,
 10  (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) y
 11  where x.tablespace_name=y.tablespace_name
 12  order by  7 desc;




4.Shell script
[mon@oracle6 source]$ cat mon_tablespace 
#!/bin/bash
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=xxxxxdb
DATETIME=`date +"%Y/%m/%d %H:%M:%S"`


$ORACLE_HOME/bin/sqlplus -s mon/mon << EOF >> /home/mon/log/mon_tablespace.log
insert into mon_tablespace select * from v_mon_tablespace;
commit;
exit;
EOF


STRS="topaz yangxu standby abcd"
for STR in $STRS
do
$ORACLE_HOME/bin/sqlplus -s mon/mon << EOF >> /home/mon/log/mon_tablespace.log
insert into mon_tablespace
select 
(select dbid from v\$database@$STR) dbid,
(select name from v\$database@$STR) dbname,
x.tablespace_name,y.bytes total_size,x.bytes free_size,(y.bytes-x.bytes) use_size,round((y.bytes-x.bytes)/y.bytes,4)*100 usage,
sysdate  mon_date
from 
(select tablespace_name,sum(bytes) bytes from dba_free_space@$STR group by tablespace_name) x,
(select tablespace_name,sum(bytes) bytes from dba_data_files@$STR group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
order by  7 desc;
commit;
exit
EOF
  #echo "STR:$STR"
done


5.crontab定時任務
[mon@oracle6 source]$ crontab -l
55 7,16 * * * /home/mon/source/mon_tablespace > /dev/null 2>&1