[Oracle] 在Linux系统上同时监控多个Oracle数据库表空间的方法

create tablespace tbsmonitor datafile '/opt/u01/app/oradata/tbsmonitor/tsmonitor.dbf' size 50M autoextend on;

create user tsmonitor identified by I11m8cb default tablespace tsmonitor;

grant resource to tbsmonitor;
grant create session to tbsmonitor;
grant create table to tbsmonitor;
grant select on dba_data_files to tbsmonitor;
grant select on dba_free_space to tbsmonitor;

DATABASE1 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.21.1)(PORT=1521))
    (CONNECT_DATA=(SID= database1)))
DATABASE2 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.21.2)(PORT=1521))
    (CONNECT_DATA=(SID= database2)))
DATABASE3 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.21.3)(PORT=1521))
    (CONNECT_DATA=(SID= database3)))

10.1.21.2 database1
10.1.21.3 database2
10.1.21.4 database3

create database link TO_DATABASE1
 connect to TSMONITOR identified by I11m08cb
 using 'DATABASE1';
create database link TO_DATABASE2
 connect to TSMONITOR identified by I11m08cb
 using 'DATABASE2';
create database link TO_DATABASE3
 connect to TSMONITOR identified by I11m08cb
 using 'DATABASE3';

create table tbsmonitor.tbsmonitor
(
 ipaddress  VARCHAR2(200),
 instancename  VARCHAR2(200),
 tablespace_name VARCHAR2(200),
 datafile_count NUMBER,
 size_mb   NUMBER,
 free_mb   NUMBER,
 used_mb   NUMBER,
 maxfree   NUMBER,
 pct_used  NUMBER,
 pct_free  NUMBER,
 time    DATE
) tablespace tbsmonitor;

#!/bin/bash
#FileName: tbsmonitor.sh
#CreateDate:2016-01-1
#version:1.0
#Discription:take the basic information to insert into the table tbs_usage
# Author:FUZHOU HOT
#Email:15980219172@139.com
ORACLE_SID= tbsmonitor
ORACLE_BASE=/opt/u01/app
ORACLE_HOME=/opt/u01/app/oracle
PATH=$ORACLE_HOME/bin:$PATH;export PATH
export ORACLE_SID ORACLE_BASE ORACLE_HOME
date>>/opt/u01/app/oracle/tbsmonitor.sh
sqlplus sys/I11m08cb as sysdba <<EOF >> /opt/u01/app/oracle/tbsmonitor.log 2>&1
@/opt/u01/app/oracle/tbsmonitor/ tbsmonitor.sql;
@/opt/u01/app/oracle/tbsmonitor/database1.sql;
@/opt/u01/app/oracle/tbsmonitor/database2.sql;
@/opt/u01/app/oracle/tbsmonitor/database3.sql;
EOF
echo >> /opt/u01/app/oracle/ tbsmonitor.log

/opt/u01/app/oracle/tbsmonitor/database1.sql; /opt/u01/app/oracle/tbsmonitor/database2.sql;
/opt/u01/app/oracle/tbsmonitor/database3.sql;
/opt/u01/app/oracle/tbsmonitor/ tbsmonitor.sql;

insert into tsmonitor.tbsmonitor SELECT utl_inaddr.get_host_address('DATABASE1') ipaddress,
(select instance_name from v$instance) instancename,
df.tablespace_name,
COUNT(*) datafile_count,
ROUND(SUM(df.BYTES) / 1048576) size_mb,
ROUND(SUM(free.BYTES) / 1048576, 2) free_mb,
ROUND(SUM(df.BYTES) / 1048576 - SUM(free.BYTES) / 1048576, 2) used_mb,
ROUND(MAX(free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free,sysdate time
FROM dba_data_files@TO_DATABASE1 df,
(SELECT tablespace_name,
file_id,
SUM(BYTES) BYTES,
MAX(BYTES) maxbytes
FROM dba_free_space@TO_DATABASE1
GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 6;

SELECT IPADDRESS ,
  Instancename,
  tablespace_name,
  datafile_count,
  size_mb "表空间大小(M)",
  used_mb "已使用空间(M)",
  TO_CHAR(ROUND((used_mb) / size_mb * 100,
       2),
    '990.99') "使用比",
   free_mb "空闲空间(M)"
FROM tbsmonitor. tbsmonitor order by "使用比" desc

select a.tablespace_name,(b.used_mb-a.used_mb) increase,a.ipaddress from
(select * from tsmonitor.tbs_usage where to_char(time,'yyyy-mm-dd')='2016-01-04') a,
(select * from tsmonitor.tbs_usage where to_char(time,'yyyy-mm-dd')='2016-01-08') b
where a.tablespace_name=b.tablespace_name and a.IPADDRESS=b.IPADDRESS order by increase desc
select * from tbsmonitor. tbsmonitor where ipaddress='10.1.21.2' and to_char(time,'yyyy-mm-dd')='2016-01-08'