用PYTHON写一个监控oracle数据库事务锁定情况，并自动解锁长时间被锁定的事务，同时将具体锁表的信息及执行结果邮件反馈运维人员

以下是一个使用Python编写的示例程序，用于监控Oracle数据库的事务锁定情况，并自动解锁长时间被锁定的事务，同时将锁表的信息及执行结果通过邮件反馈给运维人员。请确保您已经安装了Python的cx_Oracle库和smtplib库，并提供了相应的邮件配置信息（SMTP服务器、发件人、收件人等）。

import cx_Oracle
import smtplib
from email.mime.text import MIMEText

# Oracle数据库连接信息
db_username = "your_db_username"
db_password = "your_db_password"
db_host = "your_db_host"
db_port = "your_db_port"
db_sid = "your_db_sid"

# SMTP邮件配置信息
smtp_server = "your_smtp_server"
smtp_port = "your_smtp_port"
smtp_username = "your_smtp_username"
smtp_password = "your_smtp_password"
sender_email = "your_sender_email"
receiver_email = "your_receiver_email"


def get_locked_transactions():
    # 连接到Oracle数据库
    dsn = cx_Oracle.makedsn(db_host, db_port, db_sid)
    connection = cx_Oracle.connect(db_username, db_password, dsn)

    # 查询被锁定的事务
    cursor = connection.cursor()
    cursor.execute("SELECT s.sid, s.serial#, s.username, s.osuser, l.owner, l.object_name "
                   "FROM v$locked_object lo, dba_objects l, v$session s "
                   "WHERE lo.object_id = l.object_id AND lo.session_id = s.sid")
    locked_transactions = cursor.fetchall()

    # 关闭数据库连接
    cursor.close()
    connection.close()

    return locked_transactions


def unlock_transactions(locked_transactions):
    # 连接到Oracle数据库
    dsn = cx_Oracle.makedsn(db_host, db_port, db_sid)
    connection = cx_Oracle.connect(db_username, db_password, dsn)

    # 解锁长时间被锁定的事务
    cursor = connection.cursor()
    for sid, serial, _, _, owner, object_name in locked_transactions:
        try:
            # 执行解锁操作
            cursor.execute(f"ALTER SYSTEM KILL SESSION '{sid}, {serial}'")

            # 发送解锁成功的邮件反馈
            send_email(f"Transaction Lock Released - {owner}.{object_name}", "Transaction lock has been released successfully.")

        except Exception as e:
            # 发送解锁失败的邮件反馈
            send_email(f"Failed to Release Transaction Lock - {owner}.{object_name}", str(e))

    # 关闭数据库连接
    cursor.close()
    connection.close()


def send_email(subject, body):
    # 构建邮件内容
    message = MIMEText(body)
    message["Subject"] = subject
    message["From"] = sender_email
    message["To"] = receiver_email

    # 发送邮件
    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.login(smtp_username, smtp_password)
        server.sendmail(sender_email, receiver_email, message.as_string())


def main():
    locked_transactions = get_locked_transactions()

    if len(locked_transactions) > 0:
        # 发送被锁定事务的邮件提醒
        subject = "Locked Transactions Detected"
        body = "The following transactions are locked:\n\n"
        for sid, serial, username, osuser, owner, object_name in locked_transactions:
            body += f"SID: {sid}, Serial#: {serial}\n"
            body += f"Username: {username}, OS User: {osuser}\n"
            body += f"Locked Object: {owner}.{object_name}\n\n"
        send_email(subject, body)

        # 解锁长时间被锁定的事务
        unlock_transactions(locked_transactions)


if __name__ == "__main__":
    main()

请注意，上述代码只是一个示例，您需要根据实际情况进行适当的修改和调整。特别是数据库连接信息、SMTP邮件配置信息以及邮件的内容格式等都需要根据您的环境进行配置。

希望这个示例能对您有所帮助！如果您有更多问题，请随时提问。