转 python 自动监控表空间，并自动添加数据文件

侯志清-江西南昌

 

python 自动监控表空间，并自动添加数据文件

#!/usr/bin/python
import os
import time
import linecache

# 定义记录日志文件
def rlog(log):
        LTIME=time.strftime('%Y-%m-%d %H:%M:%S')
        f=open('/tmp/poracle.log','a')
        f.write("\033[32;1m"+LTIME+' '+log+"\n\033[0m")
        f.close()
def wlog(log):
        LTIME=time.strftime('%Y-%m-%d %H:%M:%S')
        f=open('/tmp/poracle.log','a')
        f.write("\033[31;1m"+LTIME+' '+log+"\n\033[0m")
        f.close()

#定义sql
def sql(sql):
        f=open('/tmp/tmp.sql','w')
        f.write("spool /tmp/output.txt\n")
        f.write(sql+"\n")
        f.write("spool off\n")
        f.write("exit\n")
        f.close()
#构造查询表空间的SQL语句
sql('''set linesize 500 pagesize 100;
       select a.tablespace_name "tablespace",
       trunc(a.total) "allocated(M)",
       trunc(a.total-b.free)  "Used (M)",
       trunc(b.free) " free space(M)",
       ceil((1-b.free/a.total)*100) "Usage %"
   from 
        (select tablespace_name, sum(nvl(bytes,0))/1024/1024 total from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(nvl(bytes,0))/1024/1024 free from dba_free_space group by tablespace_name) b
   where 
        a.tablespace_name=b.tablespace_name 
   order by 3 desc;''')

#将结果输出到/tmp/output.txt
try:
        os.system("sqlplus / as sysdba @/tmp/tmp.sql")
except:
        pass

#格式化 /tmp/ouput.txt到/tmp/outtmp.txt

i=3
fl=open('/tmp/outtmp.txt','a')
while True:
        try:
                line=linecache.getlines('/tmp/output.txt')[i]
                if len(str.strip(line))==0:
                        fl.close()
                        break
                else:
                        fl.write(line)
                        i += 1
        except:
                break

os.remove('/tmp/output.txt')
os.remove('/tmp/tmp.sql')



#获取数据文件目录
sql('select name from v$datafile where file#=1;')

try:
        os.system("sqlplus / as sysdba @/tmp/tmp.sql")
except:
        pass
linecache.clearcache()
path=os.path.dirname(str.strip(linecache.getline(r'/tmp/output.txt',4)))
os.remove('/tmp/output.txt')
os.remove('/tmp/tmp.sql')


#如果表空间大于95%，则构造添加数据文件SQL语句
ftmp=open('/tmp/outtmp.txt','r')
f=open('/tmp/tmp.sql','a')
for line in ftmp:
        dat_in = line.split()
        if int(dat_in[4])>95:
                if dat_in[0]=="SYSTEM" or dat_in[0]=="SYSAUX" or dat_in[0]=='UNDOTBS1':
                        pass
                else:
                        wlog(dat_in[0]+" Now is "+dat_in[4]+"% outoff 95%")
                        f.write("alter tablespace "+dat_in[0] + " add datafile '"+path+"/"+dat_in[0]+time.strftime('%Y%m%d%H%M%S')+".dbf' size 20G autoextend on;\n")
f.write("exit\n")
f.close()
ftmp.close()
os.remove('/tmp/outtmp.txt')

#添加数据文件到表空间

try:
        CMD=os.system("sqlplus / as sysdba @/tmp/tmp.sql")
except:
        pass
os.remove('/tmp/tmp.sql')

if CMD==0:
        rlog("add datafile to tablespace success!")
else:
        wlog("add datafile to tablespace faild!")