脚本：检查表空间剩余可用是否超过了特定阀值
Code：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
[oracle@ora12c - allah:~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 18 17:56:01 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> set head off term off feed off verify off SQL> col pct_free format 999 SQL> select (f.bytes/a.bytes)*100 pct_free,'% free',a.tablespace_name || ',' from 2 ( 3 select nvl(sum(bytes),0) bytes,x.tablespace_name from dba_free_space y,dba_tablespaces x where x.tablespace_name=y.tablespace_name(+) and x.contents != 'TEMPORARY' and x.status != 'READ ONLY' and x. 4 tablespace_name not like 'UNDO%' group by x.tablespace_name 5 ) f, 6 ( 7 select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name 8 ) a 9 where a.tablespace_name = f.tablespace_name 10 order by 1; 0 % free SYSTEM, 6 % free SYSAUX, 83 % free IRONS, 99 % free ALLAH, 99 % free ADAMHUAN, 99 % free USERS, SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ cat check_tablespace_usage.sh #!/bin/bash if [ $# -ne 2 ] then echo "Usage: $0 SID threshold" exit 1 fi crit_var=$( sqlplus -s <<EOF system/oracle set head off term off feed off verify off col pct_free format 999 select (f.bytes/a.bytes)*100 pct_free,'% free',a.tablespace_name || ',' from ( select nvl(sum(bytes),0) bytes,x.tablespace_name from dba_free_space y,dba_tablespaces x where x.tablespace_name=y.tablespace_name(+) and x.contents != 'TEMPORARY' and x.status != 'READ ONLY' and x.tablespace_name not like 'UNDO%' group by x.tablespace_name ) f, ( select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name ) a where a.tablespace_name = f.tablespace_name and (f.bytes/a.bytes)*100 <= $2 order by 1; exit; EOF ) if [ "$crit_var" = "" ] then echo "space ok." else echo "space is not ok." echo $crit_var fi exit 0 [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ sh check_tablespace_usage.sh allah 10 space is not ok. 0 % free SYSTEM, 6 % free SYSAUX, [oracle@ora12c - allah:~]$ sh check_tablespace_usage.sh allah 90 space is not ok. 0 % free SYSTEM, 6 % free SYSAUX, 83 % free IRONS, [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ |
————————————————————
Ending。
