# 创建用户
def create_user(ora_cursor, user, password, default_tbs, profile='default'):
    ora_cursor.execute(
        "create user " + user + " identified by " + password + ' default tablespace ' + default_tbs + " profile " + profile
    )
    print("user " + user + " created succesfully!")
    ora_cursor.close()

# 修改密码
def alter_user_passwd(ora_cursor, user, password):
    ora_cursor.execute(
        "alter user " + user + " identified by " + password 
    )
    print("user " + user + "'s password altered successfully!")
    ora_cursor.close()

# 创建表空间(默认autoextend off)
def create_tbs(ora_cursor, tbs_name, data_file, size, extend='autoextend off'):
    ora_cursor.execute(
        "create tablespace " + tbs_name + " datafile '" + data_file + "' size " + str(size) + " G " + extend
    )
    print("Tablespace " + tbs_name + " created successfully!")
    ora_cursor.close()

# 添加数据文件(默认autoextend off)
def extend_tbs(ora_cursor, tbs_name, data_file, size, extend='autoextend off'):
    ora_cursor.execute(
        "alter tablespace " + tbs_name + " add datafile '" + data_file + "' size " + str(size) + " G " + extend
    )
    print("Tablespace " + tbs_name + " extended " + str(size) + "G successfully!")
    ora_cursor.close()

# kill 用户会话
def kill_session(ora_cursor, username):
    ora_cursor.execute(
        "select spid from v$process a, v$session b where a.addr = b.paddr and b.username = '" + username + "'"
    )
    result = ora_cursor.fetchall()
    if len(result) != 0:
        for spid_tpl in result:
            for spid in spid_tpl:
                os.system("kill -9 " + str(spid))
        print("process have been killed!")
    else:
        print("User " + username + " has not connected yet...")
    ora_cursor.close()

import time
import cx_Oracle
import matplotlib.pyplot as plt
import numpy as np
import operator


def session_count(ora_cursor, dict):
    ora_cursor.execute(
        "select count(*) from v$session"
    )
    date = time.strftime('%X', time.localtime())
    dict[date] = int(cur_ora.fetchall()[0][0])
    # print(cur_ora.fetchall()[0])
    cur_ora.close()


# 取当前时间
today_str = time.strftime('%Y-%m-%d', time.localtime())
conn_info = {
    'username': 'test',
    'passwd': 'oracle',
    'tns_name': 'test',
    'mode': 0
}
count_dict = {}

while True:
    # 取当前时间，与之前时间作比较，如果是当天数据，则记录到count_dict中，否则就进行统计
    after_str = time.strftime('%Y-%m-%d', time.localtime())
    if today_str == after_str:
        cur_ora = conn_cursor(conn_info)
        session_count(cur_ora, count_dict)
        print(count_dict)
        # 每隔一小时执行一次
        time.sleep(3600)
    else:
        # 对字典按照时间进行排序，并转换为元组列表
        sortedDict = sorted(count_dict.items(), key=operator.itemgetter(0), reverse=False)
        # 将时间、会话数量分别放到两个列表中
        x = [s[0] for s in sortedDict]
        y = [s[1] for s in sortedDict]
        # 根据会话数量绘图
        plt.plot(range(len(y)), y)
        # 设置标题和刻度值
        plt.title("Total session count")
        plt.xlabel("Time")
        plt.ylabel("session count")
        ax = plt.gca()
        ax.set_xticks(np.linspace(0, 24, 24))
        ax.set_xticklabels(x)
        plt.xticks(rotation=30, size=8)
        plt.show()

from bs4 import BeautifulSoup
import matplotlib.pyplot as plt


def get_top_events(awr_html):
    with open(awr_html) as fileobj:
        bsobj = BeautifulSoup(fileobj, "html.parser")

    # 获取目标table的<tr>
    tab_tr = bsobj.find('table', {'summary': "This table displays top 10 wait events by total wait time"}).findAll('tr')

    # 获取列名
    tab_col = [i.get_text() for i in tab_tr[0]]

    # 获取每行的值
    list_row = []
    for tr in tab_tr[1:]:
        td = tr.findAll('td')
        row = [item.get_text() for item in td]
        list_row.append(row)

    # 将Event名称与各项指标值放到字典中
    result_dict = {}
    n = 0
    while n < len(list_row):
        result_dict[list_row[n][0]] = list_row[n][1:]
        n += 1

    return result_dict


html = '/PycharmProjects/Oracle/awrrpt_1_12_14.html'
result = get_top_events(html)

# 生成柱状图并设置标签
x = [x for x in result.keys()]
y = [float(result[a][1]) for a in x]
plt.bar(range(len(y)), y, tick_label=x)
plt.title("Total Wait Time (sec)")
plt.xticks(rotation=30, size=8)
plt.xlabel("EVENTS")
plt.ylabel("WAIT SECS")
plt.show()