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

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())

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)  # 发送邮件

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 <= 5
            """,
    )
    r = []
    for i in cursor:
        r.append(i)


    connection.close()
    if len(r) == 0:
        sys.exit()
    return  r




css = '''
<style>
    table {
        border-collapse: collapse; 
        width: 100%; 
        margin: 20px 0; 
    } 
    th, 
    td { 
        padding: 8px; 
        text-align: left; 
        border: 1px solid #ddd; 
    } 
    th { 
        background-color: #f2f2f2;
        color: #444; 
    } 
    tr:nth-child(odd) { 
        background-color: #f9f9f9;
    }
</style>              
'''


html_text = '<h3>表空间使用率（Top5）</h3>'


html_text += '<p></p>'
html_text += '''
<table>
<thead>
        <tr>
            <th>表空间</th>
            <th>已使用</th>
            <th>使用率</th>
        </tr>
</thead>
<tbody>


'''


init_oracle(oci_path=r"D:\app\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2")
r = table_space()


for record in r:
    ts = record[0]
    size_used = record[3]
    usage = record[5]
    html_text += '<tr>\n'
    html_text += '<td>' + ts + '</td>\n'
    html_text += '<td>' + str(size_used) + 'G</td>\n'
    html_text += '<td>' + usage.strip() + '%</td>\n'
    html_text += '</tr>\n'


html_text += '''
</tbody>
</table>
'''


#print(html_text)
#sys.exit()


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


# HTML邮件正文
msg.attach(MIMEText(css+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)  # 发送邮件