#!C:\Users\viviliving\AppData\Local\Programs\Python\Python37-32\python
# -*- coding: utf-8 -*-
# -*- coding: gbk -*-
import sys
import cx_Oracle
import os
import numpy as np  
import matplotlib.pyplot as plt  
#os.environ['NLS_LANG']='"SIMPLIFIED CHINESE_CHINA.ZHS16GBK"'
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
os.environ['PATH']='E:\instantclientwin32_jb51\instantclient_11_2;%path%'
os.environ['TNS_ADMIN']= 'E:\instantclientwin32_jb51\instantclient_11_2'
os.environ['ORACLE_BASE'] = 'E:\instantclientwin32_jb51\instantclient_11_2'
os.environ['ORACLE_HOME'] = 'E:\instantclientwin32_jb51\instantclient_11_2'
c = ['silver','r','b','g'] 
plt.rcParams['font.sans-serif'] = ['SimHei']
#plt.figure(figsize=(6,2))
#fig, ax = plt.subplots(1,1,figsize=(4,5))
fig, ax = plt.subplots()
username=input("oracle user:")
passname=input("oracle pass:")
rac1ip=input("oracle ip(rac1):")
servicename=input("oracle service:")
timeint=input("interval:")
 
conn=cx_Oracle.connect(username,passname,rac1ip+"/"+servicename)
curs=conn.cursor()
sql="select t.status, sum(t.blocks) * 8 / 1024    from dba_undo_extents t  group by t.status"
undototal="select   sum(bytes) / 1024 / 1024 b2 from dba_data_files where tablespace_name like 'UNDOTBS1%'"
curs.execute(undototal)
result=curs.fetchone()
undototalm=result[0]
while(True):
    curs.execute(sql)
    x=[]
    y=[]
    group_labels=[]
    group_labels.append("totalundo")
    y.append(undototalm)

    for row_data in curs:
        print(row_data[0])
        print(row_data[1])
        group_labels.append(row_data[0])
        y.append(row_data[1])

    x = np.arange(1, len(y)+1, 1)  
    ax.cla() 
    for ll_row in range(len(y)):
        ax.bar(x[ll_row],y[ll_row],fc=c[ll_row],width=0.6) 

    plt.xlabel("undo状态")
    plt.ylabel("空间量(M)")
    plt.title('undo',color='blue',  loc='left')
    plt.xticks(x, group_labels, rotation=0,color='k')
    for xx,yy in zip(x,y):
            ax.text(xx+0.05,yy+0.05,'%d' %yy, ha='center',va='bottom',color='k',rotation=0)
            
#    ax.legend()
    plt.pause(int(timeint))