查询oracle数据库里面的SQL语句,并做慢SQL的TOP5排序,并把TOP5的SQL作为附件邮件发给对应负责人
时间: 2024-04-14 08:29:17 浏览: 24
要查询Oracle数据库中的SQL语句并对慢SQL进行TOP5排序,然后将TOP5的SQL作为附件邮件发送给负责人,可以使用Python的cx_Oracle模块连接到Oracle数据库并执行相应的查询。以下是一个示例代码:
```python
import cx_Oracle
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
def send_email(subject, message, attachment):
sender_email = 'your_email@example.com'
receiver_email = 'receiver_email@example.com'
smtp_server = 'smtp.example.com'
smtp_port = 587
username = 'your_username'
password = 'your_password'
msg = MIMEMultipart()
msg['Subject'] = subject
msg['From'] = sender_email
msg['To'] = receiver_email
body = MIMEText(message)
msg.attach(body)
with open(attachment, 'rb') as file:
attachment_data = file.read()
attachment_mime = MIMEText(attachment_data, 'plain')
attachment_mime.add_header('Content-Disposition', f'attachment; filename="{attachment}"')
msg.attach(attachment_mime)
with smtplib.SMTP(smtp_server, smtp_port) as server:
server.starttls()
server.login(username, password)
server.send_message(msg)
def get_slow_sql_top5():
connection = cx_Oracle.connect('username/password@hostname:port/service_name')
cursor = connection.cursor()
cursor.execute('SELECT sql_text, elapsed_time FROM v$sql ORDER BY elapsed_time DESC')
slow_sql_top5 = []
count = 0
for sql_text, elapsed_time in cursor:
slow_sql_top5.append(f'SQL Text: {sql_text}\nElapsed Time: {elapsed_time}\n')
count += 1
if count >= 5:
break
cursor.close()
connection.close()
return '\n'.join(slow_sql_top5)
if __name__ == '__main__':
slow_sql = get_slow_sql_top5()
if slow_sql:
attachment_file = 'top5_slow_sql.txt'
with open(attachment_file, 'w') as file:
file.write(slow_sql)
send_email('Top 5 Slow SQL', 'Please check the attached file for top 5 slow SQL queries.', attachment_file)
```
请注意,上述代码中的连接字符串中的"username"、"password"、"hostname"、"port"和"service_name"需要根据你的实际情况进行修改,以正确连接到Oracle数据库。
此外,你还需要提供你自己的邮箱地址、接收邮件的邮箱地址、SMTP服务器地址、SMTP端口号、SMTP用户名和密码。
请确保你的Python环境中已经安装了cx_Oracle模块。你可以使用以下命令进行安装:
```
pip install cx_Oracle
```
这只是一个简单的示例,你可以根据你的需求来扩展和定制代码,例如添加异常处理、定时执行等。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)