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

#!/usr/bin/python

importosimporttimeimportlinecache#定义记录日志文件

defrlog(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()defwlog(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()

#定义sqldefsql(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.txttry:

os.system("sqlplus / as sysdba @/tmp/tmp.sql")except:pass

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

i=3fl=open('/tmp/outtmp.txt','a')whileTrue:try:

line=linecache.getlines('/tmp/output.txt')[i]if len(str.strip(line))==0:

fl.close()break

else:

fl.write(line)

i+= 1

except:breakos.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:passlinecache.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 inftmp:

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:passos.remove('/tmp/tmp.sql')if CMD==0:

rlog("add datafile to tablespace success!")else:

wlog("add datafile to tablespace faild!")