4.Linux monitor tablespace usage
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
先賦予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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2021925/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Shell] Linux monitor tablespace usageLinux
- [Oracle Script] check tablespace usage infoOracle
- [Oracle Script] check temp tablespace usageOracle
- Oracle 11g tablespace usageOracle
- [Shell] Monitor filesystem usage & delete expire filedelete
- How to Monitor UGA, PGA and Cursor Usage Per SessionSession
- 5.Monitor filesystem usage & delete expire filedelete
- Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over TimeSession
- Oracle10g新增的檢視dba_tablespace_usage_metricsOracle
- The DBA_TABLESPACE_USAGE_METRICS View Needs to be Rebuilt in 10g_738101.1ViewUI
- 【Mysql】為MySQL增加執行緒記憶體監控 (MySQL Thread Memory Usage Monitor)MySql執行緒記憶體thread
- Oracle11g新增檢視查詢表空間使用率DBA_TABLESPACE_USAGE_METRICSOracle
- dnsjava usageDNSJava
- 使用SQL指令碼檢視錶空間使用率和使用dba_tablespace_usage_metrics檢視的區別SQL指令碼
- costume GIT usage for meGit
- solaris 10 disksuite usageUI
- table type usage sample:
- tablespace 大檔案,undo,temp tablespace
- Table Monitor
- [Oracle Script] Temporary Sort UsageOracle
- [Oracle Script] Rollback Segment UsageOracle
- BlockRecover Restrictions and Usage NotesBloCREST
- Clear Case usage tips
- Oracle runInstaller 's UsageOracle
- Oracle NUMA usage recommendationOracle
- db2 sms tablespace 不支援large tablespaceDB2
- [Shell] monitor filesystem
- 2788647047_monitor
- [Oracle Script] Undo Usage Per statusOracle
- [Oracle Script] Undo Usage Per sessionOracleSession
- V$sort_usage, where is the definition?
- Tablespace Space Script
- remap_tablespaceREM
- The SYSAUX Tablespace (40)UX
- Postman的Monitor功能Postman
- 【SQL】Oracle SQL monitorSQLOracle
- Health Monitor簡介
- Monitor ASM DG IOASM