【邮件Oracle 表空间使用率监控

监控告警一般使用邮件、短信、微信、钉钉接收告警，邮件作为免费方案一直不受重视。如果企业内部有邮件系统，使用 SMTP 发送告警邮件是非常方便的。

首先配置好手机的邮件客户端，比如网易的邮件大师，或激活邮件系统的 POP3/SMTP/IMAP 服务，这样就可以使用第三方的邮件客户端了。

先发送一封测试邮件；

import smtplib
from email.mime.text import MIMEText


# 邮件内容  
sender_email = "s1@example.com" # 你的邮箱地址  
password = "password" # 你的邮箱密码或应用专用密码  
receiver_email = "r1@example.com" # 接收者的邮箱地址  


msg = MIMEText("这是邮件的内容")
msg['Subject'] = "测试邮件"
msg['From'] = sender_email
msg['To'] = receiver_email


# 连接到SMTP服务器
smtp_server = '192.168.1.1' # 替换为你的SMTP服务器地址  
smtp_port = 25 # 默认的SMTP端口


with smtplib.SMTP(smtp_server, smtp_port) as server:
#server.starttls() # 启用TLS加密
server.login(sender_email, password)
server.sendmail(sender_email, receiver_email, msg.as_string())

测试邮件发送没有问题，可以再试试 Markdown 文本格式：

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import mistune
from pprint import pprint
import sys


# 发件人邮箱账号
email = 's1@example.com'
password = 'password'


# 收件人邮箱地址
to_email = 't1@example.com'


# Markdown文本
markdown_text = """


# H1
## H2
### H3
#### H4
##### H5
###### H6




Alt-H1
======


Alt-H2
------




**粗体文本**
*斜体文本*




无序列表


- 项目一
- 项目二
- 项目三


有序列表


1. 项目一
2. 项目二
3. 项目三




[链接风格1](https://www.google.com)


[链接风格2](https://www.google.com "Google's Homepage")


[链接风格3][参考]


[链接风格4 - 本地文件](../blob/master/LICENSE)


[链接风格5][1]


[链接风格6]


参考链接放在最后


[参考]: https://www.google.com
[1]: https://www.google.com
[链接风格6]: https://www.google.com


Inline-style:
![alt text](https://raw.githubusercontent.com/hyang0/ip_notes/main/img/IP.ico "Logo Title Text 1")


Reference-style:
![alt text][logo]


[logo]: https://raw.githubusercontent.com/hyang0/ip_notes/main/img/IP.ico "Logo Title Text 2"


"""


# 将Markdown转换为HTML
html_text = mistune.markdown(markdown_text)
pprint(html_text)


# sys.exit()


# 创建消息对象
msg = MIMEMultipart()
msg['From'] = email
msg['To'] = to_email
msg['Subject'] = 'Markdown邮件'


# 邮件正文
#body = '这是一封通过Python SMTP库发送的测试邮件。'
#msg.attach(MIMEText(body, 'plain'))


# HTML邮件正文
msg.attach(MIMEText(html_text, 'html'))


# 连接到SMTP服务器
with smtplib.SMTP('192.168.1.1', 25) as smtp:
# smtp.starttls() # 启用TLS加密
smtp.login(email, password) # 登录到你的邮箱账号
smtp.send_message(msg) # 发送邮件



测试发现普通 Markdown 文本转换成 HTML 没有问题，但表格转换不太正常。最终表空间需要使用表格展现，表格的 markdown 转 html 实现不了，只能手写 html 样式。

代码如下：

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from pprint import pprint
import cx_Oracle
import sys




# 发件人邮箱账号
email = 'hyang0@example.com'
password = 'password'


# 收件人邮箱地址
to_email = 't1@example.com'
# 设置收件人邮箱列表
receivers = ["r1@example.com", "r2@example.com"]
# 设置抄送人邮箱列表
#cc = ["cc1@example.com", "cc2@example.com"]


# 创建消息对象
msg = MIMEMultipart()
msg['From'] = email
msg['To'] = ', '.join(receivers)
# msg['Cc'] = ', '.join(cc)
msg['Subject'] = '【HIS】表空间监控'




def init_oracle(oci_path):
'''初始化 Oracle 驱动'''
try:
if sys.platform.startswith("darwin"):
# lib_dir = os.path.join(os.environ.get("HOME"), "Downloads",
# "instantclient_19_8")
lib_dir = oci_path
cx_Oracle.init_oracle_client(lib_dir=lib_dir)
elif sys.platform.startswith("win32"):
lib_dir=oci_path
cx_Oracle.init_oracle_client(lib_dir=lib_dir)
except Exception as err:
print("Whoops!")
print(err)
sys.exit(1)




def table_space():
''''返回SQL查询结果'''
connection = cx_Oracle.connect(user="test", password="password",
dsn="192.168.1.1/orcl")


cursor = connection.cursor()
cursor.execute("""
select t.* from (
select UPPER(F.TABLESPACE_NAME) as "表空间名称",
ROUND(D.AVAILB_BYTES, 2) as "表空间大小(G)",
ROUND(D.MAX_BYTES, 2) as "最终表空间大小(G)",
ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) as "已使用空间(G)",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) D.AVAILB_BYTES * 100,
2),
'999.99') as "使用比",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) D.MAX_BYTES * 100, 2),
'999.99') as "最终使用比",
ROUND(F.USED_BYTES, 6) as "空闲空间(G)",
ROUND(D.MAX_BYTES - D.AVAILB_BYTES, 2) as "最终空闲可用空间(G)"


from (select TABLESPACE_NAME,
ROUND(sum(BYTES) (1024 * 1024 * 1024), 6) USED_BYTES,
ROUND(max(BYTES) (1024 * 1024 * 1024), 6) MAX_BYTES
from SYS.DBA_FREE_SPACE
group by TABLESPACE_NAME) F,
(select DD.TABLESPACE_NAME,
ROUND(sum(DD.BYTES) (1024 * 1024 * 1024), 6) AVAILB_BYTES,
ROUND(sum(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))
(1024 * 1024 * 1024),
6) MAX_BYTES
from SYS.DBA_DATA_FILES DD
group by DD.TABLESPACE_NAME) D
where D.TABLESPACE_NAME = F.TABLESPACE_NAME
order by 6 desc
) t where rownum