#!/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!")