Oracle 数据库巡检报告-基于原生sql查询结果-存入mysql后生成巡检报告

原创 于 2025-12-19 10:41:46 发布 · 694 阅读 · 18 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #oracle #sql

dbinspection.html

<!--
#Create by：ember.zhang
#为什么不问问神奇的海螺呢丶
 -->
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{{ report_data.title }}</title>
    <style>
        /* 基础样式（保留原有，增强响应式） */
        :root {
            --primary: #165DFF;
            --secondary: #36CFC9;
            --success: #00B42A;
            --warning: #FF7D00;
            --danger: #F53F3F;
            --dark: #1D2129;
            --dark-2: #4E5969;
            --dark-3: #86909C;
            --light-1: #F2F3F5;
            --light-2: #F7F8FA;
            --light-3: #FFFFFF;
        }
        
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif;
        }
        
        body {
            background-color: #F5F7FA;
            color: var(--dark);
            line-height: 1.6;
            padding: 10px;
        }
        
        .container {
            max-width: 100%;
            margin: 0 auto;
            padding: 15px;
        }
        
        /* 标题样式 */
        .page-title {
            text-align: center;
            margin-bottom: 20px;
            padding-bottom: 15px;
            border-bottom: 1px solid #E5E6EB;
        }
        
        .page-title h1 {
            font-size: 24px;
            font-weight: 700;
            color: var(--dark);
            margin-bottom: 10px;
        }
        
        .page-title p {
            color: var(--dark-3);
            font-size: 13px;
            line-height: 1.5;
        }
        
        /* 卡片样式 */
        .card {
            background-color: var(--light-3);
            border-radius: 8px;
            box-shadow: 0 2px 12px rgba(0, 0, 0, 0.05);
            margin-bottom: 20px;
            overflow: hidden;
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        
        .card-header {
            padding: 12px 15px;
            background-color: var(--light-2);
            border-bottom: 1px solid #E5E6EB;
        }
        
        .card-header h2 {
            font-size: 16px;
            font-weight: 600;
            color: var(--dark);
            margin: 0;
        }
        
        .card-body {
            padding: 15px;
        }
        
        /* 表格样式（重点优化：表头冻结） */
        .table-wrapper {
            overflow-x: auto;
            overflow-y: auto; /* 纵向滚动 */
            -webkit-overflow-scrolling: touch;
            margin-bottom: 15px;
            border-radius: 6px;
            border: 1px solid #E5E6EB;
            max-height: 500px; /* 表格最大高度，超出则滚动 */
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
            min-width: 1200px; /* 保证表格最小宽度，避免挤压 */
        }
        
        .data-table th, 
        .data-table td {
            padding: 8px 10px;
            text-align: left;
            border-bottom: 1px solid #E5E6EB;
            font-size: 12px; /* 缩小字体，增加列数容纳度 */
            white-space: nowrap; /* 关键列不换行，非关键列单独设置换行 */
            vertical-align: top;
        }
        
        .data-table th {
            background-color: var(--light-2);
            font-weight: 600;
            color: var(--dark-2);
            position: sticky;
            top: 0; /* 表头固定在容器顶部 */
            z-index: 10; /* 避免被内容覆盖 */
            box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05); /* 增加阴影区分表头和内容 */
        }
        
        .data-table tr:hover {
            background-color: var(--light-1);
        }
        
        .data-table tr.danger td {
            background-color: rgba(245, 63, 63, 0.05);
            color: var(--danger);
        }
        
        .data-table tr.warning td {
            background-color: rgba(255, 125, 0, 0.05);
            color: var(--warning);
        }
        
        /* 长文本列允许换行 */
        .data-table td.long-text {
            white-space: pre-wrap;
            word-break: break-all;
            max-width: 300px;
        }
        
        .text-danger {
            color: var(--danger) !important;
            font-weight: 600;
        }
        
        .text-warning {
            color: var(--warning) !important;
            font-weight: 600;
        }
        
        /* 指标卡片 */
        .metrics {
            display: flex;
            flex-wrap: wrap;
            gap: 10px;
            margin-bottom: 15px;
        }
        
        .metric-card {
            flex: 1 1 150px;
            background-color: var(--light-3);
            border-radius: 6px;
            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.05);
            padding: 12px;
            min-width: 120px;
        }
        
        .metric-card h3 {
            font-size: 12px;
            color: var(--dark-3);
            margin-bottom: 8px;
        }
        
        .metric-value {
            font-size: 20px;
            font-weight: bold;
            color: var(--dark);
            margin-bottom: 3px;
        }
        
        .metric-unit {
            font-size: 11px;
            color: var(--dark-3);
        }
        
        .metric-card.warning .metric-value {
            color: var(--warning);
        }
        
        .metric-card.danger .metric-value {
            color: var(--danger);
        }
        
        /* SQL样式（重点优化） */
        .sql-block {
            background-color: #F5F7FA;
            padding: 12px;
            margin-top: 8px;
            border-radius: 4px;
            font-family: "Consolas", "Monaco", monospace;
            font-size: 11px;
            line-height: 1.5;
            overflow-x: auto;
            white-space: pre;
            border: 1px solid #E5E6EB;
        }
        
        .sql-collapsed {
            height: 60px;
            overflow: hidden;
            position: relative;
        }
        
        .sql-mask {
            position: absolute;
            bottom: 0;
            left: 0;
            right: 0;
            height: 30px;
            background: linear-gradient(to top, var(--light-3), transparent);
            display: block;
        }
        
        .sql-toggle-btn {
            border: none;
            background: var(--light-2);
            color: var(--primary);
            padding: 4px 8px;
            border-radius: 4px;
            font-size: 12px;
            cursor: pointer;
            transition: background 0.2s ease;
        }
        
        .sql-toggle-btn:hover {
            background: var(--light-1);
        }
        
        /* 慢SQL卡片列表 */
        .sql-card-list {
            display: flex;
            flex-direction: column;
            gap: 15px;
        }
        
        /* 选项卡样式 */
        .tabs {
            display: flex;
            flex-wrap: wrap;
            margin-bottom: 15px;
            border-bottom: 1px solid #E5E6EB;
            gap: 5px;
            overflow-x: auto;
            padding-bottom: 5px;
        }
        
        .tab {
            padding: 8px 12px;
            cursor: pointer;
            border-bottom: 2px solid transparent;
            transition: all 0.3s ease;
            font-weight: 500;
            white-space: nowrap;
            font-size: 13px;
        }
        
        .tab:hover {
            background-color: var(--light-1);
        }
        
        .tab.active {
            border-bottom-color: var(--primary);
            color: var(--primary);
        }
        
        .tab-content {
            display: none;
        }
        
        .tab-content.active {
            display: block;
        }
        
        /* 响应式补充 */
        @media (max-width: 768px) {
            .metrics {
                flex-direction: column;
            }
            
            .metric-card {
                width: 100%;
            }
            
            .page-title h1 {
                font-size: 20px;
            }
            
            .data-table th, .data-table td {
                padding: 6px 8px;
                font-size: 11px;
            }
            
            .sql-collapsed {
                height: 40px;
            }
            
            .sql-mask {
                height: 20px;
            }
            
            .table-wrapper {
                max-height: 300px; /* 移动端缩小表格最大高度 */
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <!-- 报告标题 -->
        <div class="page-title">
            <h1>{{ report_data.title }}</h1>
            <p>数据库IP: {{ report_data.tb_zdb.dbip }} | 数据库: {{ report_data.tb_zdb.dbname }} | 版本: {{ report_data.tb_zdb.dbversion }} | 生成时间: {{ generate_time }}</p>
        </div>
        
        <!-- 数据库基本信息 -->
        <div class="card">
            <div class="card-header">
                <h2>数据库基本信息</h2>
            </div>
            <div class="card-body">
                <div class="metrics">
                    <div class="metric-card">
                        <h3>数据库名称</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.dbname }}</div>
                    </div>
                    <div class="metric-card">
                        <h3>数据库类型</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.dbtype }}</div>
                    </div>
                    <div class="metric-card">
                        <h3>数据库版本</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.dbversion }}</div>
                    </div>
                    <div class="metric-card">
                        <h3>日志模式</h3>
                        <div class="metric-value {% if report_data.tb_zdb.logmode != 'ARCHIVELOG' %}text-warning{% endif %}">{{ report_data.tb_zdb.logmode }}</div>
                    </div>
                    <div class="metric-card">
                        <h3>数据库角色</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.dbrole }}</div>
                    </div>
                    <div class="metric-card">
                        <h3>启动时间</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.uptime }}</div>
                    </div>
                    {% if report_data.tb_zdb.israc == 1 %}
                    <div class="metric-card">
                        <h3>RAC实例数</h3>
                        <div class="metric-value">{{ report_data.tb_zdb.racnum }}</div>
                    </div>
                    {% endif %}
                </div>
                
                <!-- 数据库参数 -->
                <h3 style="font-size:14px;margin-bottom:10px;">关键数据库参数</h3>
                <div class="table-wrapper">
                    <table class="data-table">
                        <thead>
                            <tr>
                                <th style="min-width:120px;">参数名称</th>
                                <th style="min-width:100px;">参数值</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            {% for param in report_data.tb_zdb_parameter %}
                            <tr>
                                <td>{{ param.pname }}</td>
                                <td>{{ param.pvalue }}</td>
                                <td class="long-text">{{ param.description }}</td>
                            </tr>
                            {% endfor %}
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        
        <!-- 巡检结果 -->
        <div class="card">
            <div class="card-header">
                <h2>巡检详细结果</h2>
            </div>
            <div class="card-body">
                <!-- 定义标签页标题映射 -->
                {% set tab_config = {
                    'space': '表空间信息',
                    'asm': 'ASM磁盘组信息',
                    'user': '用户信息',
                    'slowsql': '慢SQL信息',
                    'inst': '实例信息',
                    'dblink': 'DBLink信息',
                    'parttable': '分区表信息',
                    'partindex': '分区索引信息',
                    'redolog': '重做日志信息'
                } %}
                
                <!-- 指标选择标签页 -->
                <div class="tabs">
                    {% for key, title in tab_config.items() %}
                    <div class="tab {% if loop.first %}active{% endif %}" data-tab="{{ key }}">{{ title }}</div>
                    {% endfor %}
                </div>
                
                <!-- 表空间信息 -->
                <div class="tab-content active" id="space">
                    <h3 style="font-size:14px;margin-bottom:10px;">表空间使用情况</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>表空间总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_space|length }}</div>
                        </div>
                        <div class="metric-card warning">
                            <h3>高使用率表空间(>90%)</h3>
                            <div class="metric-value">
                                {% set high_usage = [] %}
                                {% for ts in report_data.tb_zdb_space %}
                                    {% if ts.percent and ts.percent|replace('%', '')|float > 90 %}
                                        {% set _ = high_usage.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ high_usage|length }}
                            </div>
                        </div>
                        <div class="metric-card danger">
                            <h3>使用率超95%表空间</h3>
                            <div class="metric-value">
                                {% set critical_usage = [] %}
                                {% for ts in report_data.tb_zdb_space %}
                                    {% if ts.percent and ts.percent|replace('%', '')|float > 95 %}
                                        {% set _ = critical_usage.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ critical_usage|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:120px;">表空间名称</th>
                                    <th style="min-width:80px;">总大小</th>
                                    <th style="min-width:80px;">已使用</th>
                                    <th style="min-width:80px;">空闲</th>
                                    <th style="min-width:80px;">使用率</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for ts in report_data.tb_zdb_space %}
                                <tr class="{% if ts.percent and ts.percent|replace('%', '')|float > 95 %}danger{% elif ts.percent and ts.percent|replace('%', '')|float > 90 %}warning{% endif %}">
                                    <td>{{ ts.tablespace }}</td>
                                    <td>{{ ts.total }}</td>
                                    <td>{{ ts.used }}</td>
                                    <td>{{ ts.free }}</td>
                                    <td class="{% if ts.percent and ts.percent|replace('%', '')|float > 95 %}text-danger{% elif ts.percent and ts.percent|replace('%', '')|float > 90 %}text-warning{% endif %}">{{ ts.percent }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- ASM信息 -->
                <div class="tab-content" id="asm">
                    <h3 style="font-size:14px;margin-bottom:10px;">ASM磁盘组信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>ASM磁盘组总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_asm|length }}</div>
                        </div>
                        <div class="metric-card warning">
                            <h3>高使用率磁盘组(>80%)</h3>
                            <div class="metric-value">
                                {% set high_asm = [] %}
                                {% for asm in report_data.tb_zdb_asm %}
                                    {% if asm.percent and asm.percent|replace('%', '')|float > 80 %}
                                        {% set _ = high_asm.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ high_asm|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:120px;">磁盘组名称</th>
                                    <th style="min-width:80px;">总大小</th>
                                    <th style="min-width:80px;">已使用</th>
                                    <th style="min-width:80px;">空闲</th>
                                    <th style="min-width:80px;">使用率</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for asm in report_data.tb_zdb_asm %}
                                <tr class="{% if asm.percent and asm.percent|replace('%', '')|float > 90 %}danger{% elif asm.percent and asm.percent|replace('%', '')|float > 80 %}warning{% endif %}">
                                    <td>{{ asm.name }}</td>
                                    <td>{{ asm.total }}</td>
                                    <td>{{ asm.used }}</td>
                                    <td>{{ asm.free }}</td>
                                    <td class="{% if asm.percent and asm.percent|replace('%', '')|float > 90 %}text-danger{% elif asm.percent and asm.percent|replace('%', '')|float > 80 %}text-warning{% endif %}">{{ asm.percent }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- 用户信息 -->
                <div class="tab-content" id="user">
                    <h3 style="font-size:14px;margin-bottom:10px;">数据库用户信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>用户总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_user|length }}</div>
                        </div>
                        <div class="metric-card warning">
                            <h3>锁定用户数</h3>
                            <div class="metric-value">
                                {% set locked_users = [] %}
                                {% for user in report_data.tb_zdb_user %}
                                    {% if user.userstatus and 'LOCKED' in user.userstatus %}
                                        {% set _ = locked_users.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ locked_users|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:100px;">用户名</th>
                                    <th style="min-width:80px;">用户状态</th>
                                    <th style="min-width:100px;">创建时间</th>
                                    <th style="min-width:100px;">锁定时间</th>
                                    <th style="min-width:100px;">过期时间</th>
                                    <th style="min-width:100px;">密码修改时间</th>
                                    <th style="min-width:100px;">最后登录时间</th>
                                    <th style="min-width:120px;">默认表空间</th>
                                    <th style="min-width:120px;">临时表空间</th>
                                    <th style="min-width:80px;">表数量</th>
                                    <th style="min-width:80px;">索引数量</th>
                                    <th style="min-width:80px;">视图数量</th>
                                    <th style="min-width:80px;">函数数量</th>
                                    <th style="min-width:80px;">存储过程数</th>
                                    <th style="min-width:80px;">序列数量</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for user in report_data.tb_zdb_user %}
                                <tr class="{% if user.userstatus and 'LOCKED' in user.userstatus %}warning{% endif %}">
                                    <td>{{ user.username }}</td>
                                    <td class="{% if user.userstatus and 'LOCKED' in user.userstatus %}text-warning{% endif %}">{{ user.userstatus }}</td>
                                    <td>{{ user.createtime }}</td>
                                    <td>{{ user.locktime }}</td>
                                    <td>{{ user.expirytime }}</td>
                                    <td>{{ user.pwdmodifytime|default(user.createtime) }}</td>
                                    <td>{{ user.lastlogintime }}</td>
                                    <td>{{ user.dtablespace }}</td>
                                    <td>{{ user.ttablespace }}</td>
                                    <td>{{ user.tablenum }}</td>
                                    <td>{{ user.indexnum }}</td>
                                    <td>{{ user.viewnum }}</td>
                                    <td>{{ user.functionnum }}</td>
                                    <td>{{ user.procedurenum }}</td>
                                    <td>{{ user.sequencenum }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- 慢SQL信息（优化后） -->
                <div class="tab-content" id="slowsql">
                    <h3 style="font-size:14px;margin-bottom:10px;">慢SQL信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>慢SQL总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_slowsql|length }}</div>
                        </div>
                        <div class="metric-card danger">
                            <h3>高耗时SQL(>100秒)</h3>
                            <div class="metric-value">
                                {% set slow_sqls = [] %}
                                {% for sql in report_data.tb_zdb_slowsql %}
                                    {% if sql.totaltime and sql.totaltime|float > 100 %}
                                        {% set _ = slow_sqls.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ slow_sqls|length }}
                            </div>
                        </div>
                    </div>

                    <!-- 慢SQL卡片列表 -->
                    <div class="sql-card-list">
                        {% for sql in report_data.tb_zdb_slowsql %}
                        <div class="card" style="margin-bottom:0;">
                            <div class="card-body" style="padding:12px;">
                                <ul style="list-style: none; display: flex; flex-wrap: wrap; gap: 15px; margin-bottom: 10px;">
                                    <li style="min-width:120px;">
                                        <span style="color:var(--dark-3);font-size:12px;">SQL ID：</span>
                                        <span style="font-weight:500;">{{ sql.sqlid }}</span>
                                    </li>
                                    <li style="min-width:100px;">
                                        <span style="color:var(--dark-3);font-size:12px;">用户名：</span>
                                        <span>{{ sql.username }}</span>
                                    </li>
                                    <li style="min-width:100px;">
                                        <span style="color:var(--dark-3);font-size:12px;">执行次数：</span>
                                        <span>{{ sql.execcount }}</span>
                                    </li>
                                    <li style="min-width:120px;">
                                        <span style="color:var(--dark-3);font-size:12px;">总执行时间：</span>
                                        <span class="{% if sql.totaltime and sql.totaltime|float > 100 %}text-danger{% elif sql.totaltime and sql.totaltime|float > 50 %}text-warning{% endif %}">
                                            {{ sql.totaltime }}秒
                                        </span>
                                    </li>
                                    <li style="min-width:120px;">
                                        <span style="color:var(--dark-3);font-size:12px;">平均时间：</span>
                                        <span class="{% if sql.avgtime and sql.avgtime|float > 50 %}text-danger{% elif sql.avgtime and sql.avgtime|float > 20 %}text-warning{% endif %}">
                                            {{ sql.avgtime }}秒
                                        </span>
                                    </li>
                                    <li style="min-width:100px;">
                                        <span style="color:var(--dark-3);font-size:12px;">物理读：</span>
                                        <span class="{% if sql.reads and sql.reads|int > 100000 %}text-danger{% elif sql.reads and sql.reads|int > 50000 %}text-warning{% endif %}">
                                            {{ sql.reads }}
                                        </span>
                                    </li>
                                </ul>

                                <!-- SQL文本（折叠/展开） -->
                                <div style="position: relative;">
                                    <button class="sql-toggle-btn" onclick="toggleSql(this)">
                                        展开SQL
                                    </button>
                                    <div class="sql-block sql-collapsed">
                                        {{ sql.sqltext }}
                                        <!-- 折叠遮罩层 -->
                                        <div class="sql-mask"></div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        {% endfor %}
                    </div>
                </div>
                
                <!-- 实例信息 -->
                <div class="tab-content" id="inst">
                    <h3 style="font-size:14px;margin-bottom:10px;">数据库实例信息</h3>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:80px;">实例ID</th>
                                    <th style="min-width:100px;">实例名</th>
                                    <th style="min-width:100px;">主机名</th>
                                    <th style="min-width:100px;">运行时间</th>
                                    <th style="min-width:80px;">实例状态</th>
                                    <th style="min-width:80px;">数据库状态</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for inst in report_data.tb_zdb_inst %}
                                <tr class="{% if inst.inststatus and inst.inststatus != 'OPEN' %}danger{% endif %}">
                                    <td>{{ inst.instid }}</td>
                                    <td>{{ inst.instname }}</td>
                                    <td>{{ inst.hostname }}</td>
                                    <td>{{ inst.uptime }}</td>
                                    <td class="{% if inst.inststatus and inst.inststatus != 'OPEN' %}text-danger{% endif %}">{{ inst.inststatus }}</td>
                                    <td class="{% if inst.dbstatus and inst.dbstatus != 'ACTIVE' %}text-warning{% endif %}">{{ inst.dbstatus }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- DBLink信息 -->
                <div class="tab-content" id="dblink">
                    <h3 style="font-size:14px;margin-bottom:10px;">数据库链接(DBLink)信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>DBLink总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_dblink|length }}</div>
                        </div>
                        <div class="metric-card danger">
                            <h3>无效DBLink数</h3>
                            <div class="metric-value">
                                {% set invalid_dblinks = [] %}
                                {% for dblink in report_data.tb_zdb_dblink %}
                                    {% if dblink.valid and dblink.valid != 'YES' %}
                                        {% set _ = invalid_dblinks.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ invalid_dblinks|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:100px;">所有者</th>
                                    <th style="min-width:120px;">DBLink名称</th>
                                    <th style="min-width:100px;">用户名</th>
                                    <th style="min-width:150px;">主机</th>
                                    <th style="min-width:100px;">创建时间</th>
                                    <th style="min-width:80px;">有效性</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for dblink in report_data.tb_zdb_dblink %}
                                <tr class="{% if dblink.valid and dblink.valid != 'YES' %}danger{% endif %}">
                                    <td>{{ dblink.owner }}</td>
                                    <td>{{ dblink.dblink }}</td>
                                    <td>{{ dblink.username }}</td>
                                    <td class="long-text">{{ dblink.host }}</td>
                                    <td>{{ dblink.created }}</td>
                                    <td class="{% if dblink.valid and dblink.valid != 'YES' %}text-danger{% endif %}">{{ dblink.valid }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- 分区表信息 -->
                <div class="tab-content" id="parttable">
                    <h3 style="font-size:14px;margin-bottom:10px;">分区表信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>分区表总数</h3>
                            <div class="metric-value">
                                {% set unique_tables = {} %}
                                {% for item in report_data.tb_zdb_parttable %}
                                    {% if item.tablename %}
                                        {% set _ = unique_tables.__setitem__(item.tablename, true) %}   
                                    {% endif %}
                                {% endfor %}
                                {{ unique_tables|length }}
                            </div>
                        </div>
                        <div class="metric-card">
                            <h3>大型分区(>100万行)</h3>
                            <div class="metric-value">
                                {% set large_tables = [] %}
                                {% for table in report_data.tb_zdb_parttable %}
                                    {% if table.numrows and table.numrows|int > 1000000 %}
                                        {% set _ = large_tables.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ large_tables|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:100px;">所有者</th>
                                    <th style="min-width:120px;">表名</th>
                                    <th style="min-width:100px;">分区键</th>
                                    <th style="min-width:100px;">分区类型</th>
                                    <th style="min-width:100px;">间隔值</th>
                                    <th style="min-width:120px;">分区名称</th>
                                    <th style="min-width:100px;">上限值</th>
                                    <th style="min-width:120px;">表空间</th>
                                    <th style="min-width:80px;">分区大小</th>
                                    <th style="min-width:80px;">分区行数</th>
                                    <th style="min-width:100px;">统计时间</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for table in report_data.tb_zdb_parttable %}
                                <tr>
                                    <td>{{ table.username }}</td>
                                    <td>{{ table.tablename }}</td>
                                    <td>{{ table.partkey }}</td>
                                    <td>{{ table.parttype }}</td>
                                    <td>{{ table.interval }}</td>
                                    <td>{{ table.partname }}</td>
                                    <td class="long-text">{{ table.highvalue }}</td>
                                    <td>{{ table.tablespace }}</td>
                                    <td>{{ table.size }}</td>
                                    <td class="{% if table.numrows and table.numrows|int > 1000000 %}text-warning{% endif %}">{{ table.numrows }}</td>
                                    <td>{{ table.analyzed }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- 分区索引信息 -->
                <div class="tab-content" id="partindex">
                    <h3 style="font-size:14px;margin-bottom:10px;">分区索引信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>分区索引总数</h3>
                            <div class="metric-value">
                                {% set unique_index = {} %}
                                {% for item in report_data.tb_zdb_partindex %}
                                    {% if item.indexname %}
                                        {% set _ = unique_index.__setitem__(item.indexname, true) %}   
                                    {% endif %}
                                {% endfor %}
                                {{ unique_index|length }}
                            </div>
                        </div>
                        <div class="metric-card warning">
                            <h3>失效索引数</h3>
                            <div class="metric-value">
                                {% set invalid_indexes = [] %}
                                {% for idx in report_data.tb_zdb_partindex %}
                                    {% if idx.status and idx.status != 'USABLE' %}
                                        {% set _ = invalid_indexes.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ invalid_indexes|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:100px;">所有者</th>
                                    <th style="min-width:120px;">表名</th>
                                    <th style="min-width:120px;">索引名</th>
                                    <th style="min-width:100px;">索引类型</th>
                                    <th style="min-width:100px;">分区类型</th>
                                    <th style="min-width:120px;">分区名称</th>
                                    <th style="min-width:80px;">状态</th>
                                    <th style="min-width:120px;">表空间</th>
                                    <th style="min-width:100px;">上限值</th>
                                    <th style="min-width:100px;">统计时间</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for idx in report_data.tb_zdb_partindex %}
                                <tr class="{% if idx.status and idx.status != 'USABLE' %}warning{% endif %}">
                                    <td>{{ idx.username }}</td>
                                    <td>{{ idx.tablename }}</td>
                                    <td>{{ idx.indexname }}</td>
                                    <td>{{ idx.locality }}</td>
                                    <td>{{ idx.parttype }}</td>
                                    <td>{{ idx.partname }}</td>
                                    <td class="{% if idx.status and idx.status != 'USABLE' %}text-danger{% endif %}">{{ idx.status }}</td>
                                    <td>{{ idx.tablespace }}</td>
                                    <td class="long-text">{{ idx.highvalue }}</td>
                                    <td>{{ idx.analyzed }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
                
                <!-- 重做日志信息 -->
                <div class="tab-content" id="redolog">
                    <h3 style="font-size:14px;margin-bottom:10px;">重做日志信息</h3>
                    <div class="metrics">
                        <div class="metric-card">
                            <h3>重做日志组总数</h3>
                            <div class="metric-value">{{ report_data.tb_zdb_redolog|length }}</div>
                        </div>
                        <div class="metric-card warning">
                            <h3>未归档日志数</h3>
                            <div class="metric-value">
                                {% set unarchived = [] %}
                                {% for log in report_data.tb_zdb_redolog %}
                                    {% if log.archived and log.archived != 'YES' %}
                                        {% set _ = unarchived.append(1) %}
                                    {% endif %}
                                {% endfor %}
                                {{ unarchived|length }}
                            </div>
                        </div>
                    </div>
                    <div class="table-wrapper">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th style="min-width:80px;">日志组</th>
                                    <th style="min-width:200px;">文件路径</th>
                                    <th style="min-width:80px;">线程</th>
                                    <th style="min-width:80px;">序列</th>
                                    <th style="min-width:80px;">大小</th>
                                    <th style="min-width:80px;">状态</th>
                                    <th style="min-width:80px;">是否归档</th>
                                    <th style="min-width:100px;">首次时间</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for log in report_data.tb_zdb_redolog %}
                                <tr class="{% if log.status and log.status == 'CURRENT' %}warning{% elif log.archived and log.archived != 'YES' %}danger{% endif %}">
                                    <td>{{ log.group }}</td>
                                    <td class="long-text">{{ log.filepath }}</td>
                                    <td>{{ log.thread }}</td>
                                    <td>{{ log.sequence }}</td>
                                    <td>{{ log.size }}</td>
                                    <td class="{% if log.status and log.status == 'CURRENT' %}text-warning{% endif %}">{{ log.status }}</td>
                                    <td class="{% if log.archived and log.archived != 'YES' %}text-danger{% endif %}">{{ log.archived }}</td>
                                    <td>{{ log.firsttime }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script>
        // 标签页切换功能
        document.addEventListener('DOMContentLoaded', function() {
            const tabs = document.querySelectorAll('.tab');
            const tabContents = document.querySelectorAll('.tab-content');
            
            tabs.forEach(tab => {
                tab.addEventListener('click', () => {
                    // 移除所有标签的活动状态
                    tabs.forEach(t => t.classList.remove('active'));
                    // 添加当前标签的活动状态
                    tab.classList.add('active');
                    
                    // 隐藏所有内容
                    tabContents.forEach(content => content.classList.remove('active'));
                    // 显示对应内容
                    const tabId = tab.getAttribute('data-tab');
                    document.getElementById(tabId).classList.add('active');
                });
            });
        });

        // 折叠/展开SQL文本
        function toggleSql(btn) {
            const sqlBlock = btn.nextElementSibling;
            const mask = sqlBlock.querySelector('.sql-mask');
            
            if (sqlBlock.classList.contains('sql-collapsed')) {
                sqlBlock.classList.remove('sql-collapsed');
                sqlBlock.style.height = 'auto';
                mask.style.display = 'none';
                btn.textContent = '收起SQL';
            } else {
                sqlBlock.classList.add('sql-collapsed');
                sqlBlock.style.height = '60px';
                mask.style.display = 'block';
                btn.textContent = '展开SQL';
            }
        }
    </script>
</body>
</html>

dbinspection.py

# Create by：ember.zhang
# 为什么不问问神奇的海螺呢丶
import cx_Oracle
import pandas as pd
from sqlalchemy import create_engine,text
from datetime import datetime
from jinja2 import Environment, FileSystemLoader

cx_Oracle.init_oracle_client(lib_dir=r'D:\oracleclient\instantclient_23_8')
def oracle_inspection_to_mysql(oracle_config, oracle_sql, mysql_config, mysql_table):
    # 构建Oracle连接
    oracle_engine = create_engine(
    f"oracle+cx_oracle://{oracle_config['user']}:{oracle_config['password']}@{oracle_config['host']}:{oracle_config['port']}/?service_name={oracle_config['service_name']}"
    )
    df = pd.read_sql(oracle_sql, oracle_engine)
    # oracle_conn.close()
    if df.empty:
        print("无数据可处理！")
        return
    # 数据内容调整 
    # 添加固定列
    df["dbip"] = oracle_config["host"]
    df["checktime"] = datetime.now().strftime("%Y-%m-%d")

    #写入MySQL 
    mysql_engine = create_engine(
        f"mysql+pymysql://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}/{mysql_config['db']}"
    )
    df.to_sql(
        name=mysql_table,
        con=mysql_engine,
        if_exists="append",
        index=False,
        chunksize=1000
    )
#写入数据至mysql
def w_inspection_data(mysql_config,oracle_config):
    mysql_wz = create_engine(
        f"mysql+pymysql://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}/{mysql_config['db']}"
    )
    dbip = oracle_config["host"] 
    rsql=text("SELECT tablename,sourcesql FROM tb_dbinspectionconfig where dbtype='oracle' ")
    with mysql_wz.connect() as wz_conn:
        result = wz_conn.execute(rsql)
        for row in result:
            tablename = row.tablename
            sourcesql = row.sourcesql
            #检查目标表是否存在当天同一DBIP的数据
            check_sql = text(f"SELECT 1 FROM {tablename} WHERE checktime = current_date AND dbip = :dbip")
            check_result = wz_conn.execute(check_sql, { "dbip": dbip}).fetchone()
            #存在则删除对应数据
            if check_result:
                delete_sql = text(f"DELETE FROM {tablename} WHERE checktime = current_date AND dbip = :dbip")
                wz_conn.execute(delete_sql, {"dbip": dbip})
                wz_conn.commit()  # 提交删除事务
                print(f"已删除{tablename}表中 {dbip}的数据")
            #写入数据
            oracle_inspection_to_mysql(oracle_config, sourcesql, mysql_config, tablename)
#生成报告
def load_inspection(mysql_config, dbip):
    # 创建数据库引擎
    engine = create_engine(
        f"mysql+pymysql://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}/{mysql_config['db']}",
        pool_pre_ping=True,
        connect_args={"charset": "utf8mb4"}
    )
    # 定义查询SQL字典（带参数绑定）
    sql_queries = {
        "tb_zdb": "SELECT dbip, dbname, dbtype, dbversion, logmode, dbrole, dbuname, israc, racnum, uptime, checktime, inserttime FROM zapp.tb_zdb where checktime=current_date and dbip=:dbip;",
        "tb_zdb_asm": "SELECT name, used, total, `free`,     percent FROM zapp.tb_zdb_asm where checktime=current_date and dbip=:dbip;",
        "tb_zdb_dblink": "SELECT owner, dblink, username, host, created, valid FROM zapp.tb_zdb_dblink where checktime=current_date and dbip=:dbip;",
        "tb_zdb_inst": "SELECT instid, instname, hostname, uptime, inststatus, dbstatus FROM zapp.tb_zdb_inst where checktime=current_date and dbip=:dbip;",
        "tb_zdb_parameter": "SELECT pname, pvalue, description FROM zapp.tb_zdb_parameter where checktime=current_date and dbip=:dbip;",
        "tb_zdb_partindex": "SELECT username, tablename, indexname, locality, parttype, partname, status, tablespace, highvalue, analyzed FROM zapp.tb_zdb_partindex where checktime=current_date and dbip=:dbip;",
        "tb_zdb_parttable": "SELECT username, tablename, partkey, parttype, `interval`, subparttype, partname, highvalue, tablespace, `size`, numrows, analyzed FROM zapp.tb_zdb_parttable where checktime=current_date and dbip=:dbip;",
        "tb_zdb_redolog": "SELECT `group`, filepath, thread, `sequence`, `size`, status, archived, firsttime FROM zapp.tb_zdb_redolog where checktime=current_date and dbip=:dbip;",
        "tb_zdb_slowsql": "SELECT sqlid, username, instnum, execcount, totaltime, avgtime, cputime, iowait, `reads`, sqltext FROM zapp.tb_zdb_slowsql where checktime=current_date and dbip=:dbip;",
        "tb_zdb_space": "SELECT tablespace, used, total, `free`,  percent FROM zapp.tb_zdb_space where checktime=current_date and dbip=:dbip;",
        "tb_zdb_user": "SELECT username, userstatus, createtime, locktime, expirytime, changepstime, lastlogintime, dtablespace, ttablespace, tablenum, viewnum, functionnum, indexnum, procedurenum, triggernum, synonymnum, sequencenum, packagenum FROM zapp.tb_zdb_user where checktime=current_date and dbip=:dbip;"
    }
    
    # 存储所有表的数据
    inspection_data = {} 
    try:
        with engine.connect() as conn:
            # 读取每个表的数据（绑定dbip参数）
            for table_name, sql in sql_queries.items():
                # 执行SQL并绑定参数
                result = conn.execute(text(sql), {"dbip": dbip})
                # 获取列名
                columns = result.keys()
                # 转换为字典列表
                rows = [dict(zip(columns, row)) for row in result.fetchall()]
                if table_name == "tb_zdb" and rows:
                    # tb_zdb通常只有一条记录，取第一条
                    inspection_data[table_name] = rows[0]
                else:
                    # 其他表转为字典列表
                    inspection_data[table_name] = rows
        # 构造最终返回数据结构
        report_data = {
            "title": f"数据库巡检报告 - {inspection_data.get('tb_zdb', {}).get('dbname')}({dbip})",
            **inspection_data
        }
        generate_time=datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # env = Environment(loader=FileSystemLoader('E:/MagicConch/work/python/dbinspection/'))
        # template = env.get_template("dbinspection.html")
        env = Environment(loader=FileSystemLoader('/data/zz/zscript/dbinspection/'))
        template = env.get_template("dbinspection.html")
        html_content = template.render(
        report_data=report_data,
        generate_time=generate_time
        )
        #with open(f"E:/MagicConch/work/python/dbinspection/zdbinspectionreport/inspection_report_{dbip}_{generate_time.replace(' ', '').replace(':', '') }.html", "w", encoding="utf-8") as f:
        with open(f"/data/zz/zscript/dbinspection/zdbinspectionreport/inspection_report_{dbip}_{generate_time.replace(' ', '').replace(':', '') }.html", "w", encoding="utf-8") as f:
            f.write(html_content)
        print('数据库巡检报告已生成 '+generate_time)
     #   return report_data
        
    except Exception as e:
        print(f"生成巡检报告失败(DBIP: {dbip}): {str(e)}")
        raise
    finally:
        # 关闭引擎
        engine.dispose()


#30 8 * * * /usr/local/bin/python3 /data/zz/zscript/dbinspection/dbinspection.py >> /data/zz/zscript/dbinspection/run.log 2>&1

if __name__ == "__main__":
    oracle_55 = {
        "user": "system", "password": "z9m_xxxxx", "host": "10.12.8.55", "port": 1521, "service_name": "gemesdb"
    }
    oracle_61 = {
        "user": "system", "password": "Zeta%xxxxx#$", "host": "10.12.8.61", "port": 1521, "service_name": "edadb"
    }
    mysql_config = {
        "user": "root", "password": "root%xxxxx", "host": "10.12.8.120", "db": "zapp"
    }
    w_inspection_data(mysql_config,oracle_55)
    load_inspection(mysql_config, oracle_55["host"] )

    w_inspection_data(mysql_config,oracle_61)
    load_inspection(mysql_config, oracle_61["host"] )

zapp db相关表及数据

zapp.tb_dbinspectionconfig 基础配置表

-- zapp.tb_dbinspectionconfig definition 基础配置表

CREATE TABLE `tb_dbinspectionconfig` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `tablename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '目标表名',
  `sourcesql` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '源sql',
  `dbtype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库类型',
  `updatetime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库巡检配置sql';


INSERT INTO zapp.tb_dbinspectionconfig (tablename,sourcesql,dbtype,updatetime,inserttime) VALUES
	 ('tb_zdb_asm','SELECT 
    name ,
    round(total_mb/1024,2)||''GB''   AS total,
    round(FREE_MB/1024,2)||''GB''   AS free,
    to_char(round((total_mb -FREE_MB) /1024,2), ''990.99'') ||''GB''   AS used,
    to_char(round((total_mb -FREE_MB)/total_mb *100 ,2), ''990.99'') ||''%''   AS PERCENT
  FROM    v$asm_diskgroup_stat','oracle','2025-12-03 17:26:18','2025-12-02 10:54:48'),
	 ('tb_zdb','SELECT 
name AS dbname, 
''oracle'' AS dbtype,
version AS dbversion,
log_mode AS logmode, 
database_role AS dbrole,
(SELECT  LISTAGG(DB_UNIQUE_NAME, '', '') WITHIN GROUP (ORDER BY dest_id) AS dbuname FROM v$archive_dest WHERE  UPPER(db_unique_name) NOT IN ( SELECT UPPER(db_unique_name) FROM v$database) AND db_unique_name !=''NONE'') AS dbuname ,
CASE  WHEN  (SELECT  count(*)  FROM gv$instance) >1 THEN 1 ELSE 0 END AS israc ,
(SELECT  count(*)  FROM gv$instance) AS racnum,
TO_CHAR(startup_time, ''YYYY-MM-DD HH24:MI:SS'')  AS uptime
FROM v$database, v$instance','oracle','2025-12-02 13:14:16','2025-12-02 13:14:16'),
	 ('tb_zdb_inst','SELECT  inst_id AS instid ,
instance_name AS instname,
host_name AS hostname,
TO_CHAR(startup_time, ''YYYY-MM-DD HH24:MI:SS'') AS uptime,
status AS inststatus,
database_status AS  dbstatus  
FROM gv$instance
order by inst_id','oracle','2025-12-02 17:39:19','2025-12-02 13:16:31'),
	 ('tb_zdb_parameter','SELECT name AS pname, display_value AS pvalue, description  AS description
FROM v$parameter 
WHERE name IN (''memory_target'', 
''sga_target'', ''pga_aggregate_target'',''processes'', ''sessions'', ''db_block_size'',
''log_buffer'', ''shared_pool_size'', ''buffer_cache_size'',''java_pool_size'', 
''large_pool_size'',''db_recovery_file_dest'',''db_recovery_file_dest_size'')
','oracle','2025-12-02 14:13:46','2025-12-02 13:41:38'),
	 ('tb_zdb_user','WITH  z AS (
SELECT 
    owner ,
    NVL("TABLE", 0) AS tablenum,
    NVL("VIEW", 0) AS viewnum,
    NVL("FUNCTION", 0) AS functionnum,
    NVL("INDEX", 0) AS indexnum,
    NVL("PROCEDURE", 0) AS procedurenum,
    NVL("TRIGGER", 0) AS triggernum,
    NVL("SYNONYM", 0) AS synonymnum,
    NVL("SEQUENCE", 0) AS sequencenum,
    NVL("PACKAGE", 0) AS packagenum
FROM ( SELECT   owner,   object_type,   COUNT(*) AS cnt
    FROM all_objects 
    WHERE owner IN (SELECT username FROM dba_users WHERE account_status = ''OPEN'')   AND owner NOT IN (''SYSTEM'', ''SYS'')
    GROUP BY owner, object_type ) t
PIVOT (SUM(cnt)   
    FOR object_type IN (
        ''TABLE'' AS "TABLE", 
        ''VIEW'' AS "VIEW", 
        ''FUNCTION'' AS "FUNCTION", 
        ''INDEX'' AS "INDEX", 
        ''PROCEDURE'' AS "PROCEDURE", 
        ''TRIGGER'' AS "TRIGGER", 
        ''SYNONYM'' AS "SYNONYM", 
        ''SEQUENCE'' AS "SEQUENCE", 
        ''PACKAGE'' AS "PACKAGE" )) p)
SELECT 
a.username,
a.account_status AS userstatus, 
TO_CHAR(a.created, ''YYYY-MM-DD HH24:MI:SS'') AS createtime, 
TO_CHAR(a.lock_date, ''YYYY-MM-DD HH24:MI:SS'') AS  locktime,
TO_CHAR(a.expiry_date, ''YYYY-MM-DD HH24:MI:SS'') AS  expirytime,
TO_CHAR(a.PASSWORD_CHANGE_DATE, ''YYYY-MM-DD HH24:MI:SS'') AS changepstime,
TO_CHAR(a.LAST_LOGIN, ''YYYY-MM-DD HH24:MI:SS'') AS lastlogintime,
a.default_tablespace AS dtablespace,a.temporary_tablespace AS ttablespace,
z.tablenum,z.viewnum,z.functionnum,z.indexnum,z.procedurenum,z.triggernum,z.synonymnum,z.sequencenum,z.packagenum
FROM dba_users  a LEFT JOIN z ON a.username=z.owner 
ORDER BY a.account_status DESC ,  a.username','oracle','2025-12-02 17:37:55','2025-12-02 14:05:47'),
	 ('tb_zdb_space','SELECT
    dt.tablespace_name as tablespace, 
    to_char( round(dt.block_size * dtum.used_space /1024/1024/1024,2), ''990.99'')||''GB'' as used,
    round(dt.block_size * dtum.tablespace_size /1024/1024/1024,2)||''GB''  as total,
    round(dt.block_size * (dtum.tablespace_size - dtum.used_space) /1024/1024/1024,2)||''GB''  as free,
     to_char(round(dtum.used_space/dtum.tablespace_size *100 ,2), ''990.99'') ||''%''  percent
FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
WHERE dtum.tablespace_name = dt.tablespace_name
and dt.contents != ''TEMPORARY''
union
SELECT
    dt.tablespace_name as tablespace, 
    to_char(round((dt.tablespace_size  - dt.free_space) /1024/1024/1024,2), ''990.99'')||''GB''  as used,
    round(dt.tablespace_size /1024/1024/1024,2)||''GB''  as total,
    round(dt.free_space /1024/1024/1024,2)||''GB''  as free,
  to_char(round((dt.tablespace_size - dt.free_space) / dt.tablespace_size *100 ,2), ''990.99'') ||''%''    AS percent
FROM  dba_temp_free_space dt
order by percent DESC  ','oracle','2025-12-02 14:13:03','2025-12-02 14:13:03'),
	 ('tb_zdb_parttable','WITH z AS (
SELECT k.owner,k.name, LISTAGG(k.column_name, '', '') WITHIN GROUP (ORDER BY k.column_position) AS partition_key   
FROM  dba_part_key_columns k  WHERE k.owner  NOT IN (''AUDSYS'', ''MDSYS'', ''SYS'', ''SYSTEM'')
AND k.object_type = ''TABLE'' GROUP  BY k.owner,k.name  )
SELECT   t.owner AS username,
    t.table_name AS tablename,
    z.partition_key AS partkey,
    t.partitioning_type AS parttype,
    t.INTERVAL AS interval,
    t.SUBPARTITIONING_TYPE AS subparttype,
    p.partition_name AS partname,
    p.high_value AS highvalue,
    p.tablespace_name AS tablespace,
    ROUND(s.bytes / 1024 / 1024, 2)||''MB'' AS "size",
    p.num_rows AS numrows,
   TO_CHAR( p.last_analyzed , ''YYYY-MM-DD HH24:MI:SS'')    AS analyzed
    FROM dba_part_tables t   LEFT JOIN  dba_tab_partitions p ON t.owner=p.table_owner AND t.table_name =p.table_name
    LEFT JOIN z ON t.owner=z.owner AND t.table_name =z.name
    LEFT JOIN  dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name AND p.partition_name = s.partition_name
    WHERE t.owner   NOT IN (''AUDSYS'', ''MDSYS'', ''SYS'', ''SYSTEM'')
    order by t.owner,t.table_name,p.partition_name','oracle','2025-12-03 16:24:20','2025-12-02 14:18:44'),
	 ('tb_zdb_partindex','SELECT 
    pi.owner AS username,
    pi.table_name AS tablename,
    pi.index_name AS indexname,
    pi.locality AS locality, 
    pi.partitioning_type AS parttype,  
    ip.partition_name AS partname,
    ip.status AS status, 
    ip.tablespace_name AS tablespace, 
    ip.high_value  AS highvalue,
    TO_CHAR(ip.last_analyzed, ''YYYY-MM-DD HH24:MI:SS'')   AS analyzed
FROM 
    dba_part_indexes pi
JOIN 
    dba_ind_partitions ip 
    ON pi.owner = ip.index_owner 
    AND pi.index_name = ip.index_name
WHERE 
    pi.owner NOT IN (''AUDSYS'', ''MDSYS'', ''SYS'', ''SYSTEM'')
ORDER BY 
    pi.owner, pi.table_name, pi.index_name,  ip.partition_name','oracle','2025-12-02 14:20:56','2025-12-02 14:20:56'),
	 ('tb_zdb_redolog','SELECT 
    l.group# AS "group",
    l.member AS filepath, 
    lg.thread# AS thread,
    lg.sequence# AS "sequence",
    lg.bytes/1024/1024/1024||''GB'' AS "size",
    lg.status AS status,  
    lg.archived AS archived,  
    TO_CHAR(lg.first_time, ''YYYY-MM-DD HH24:MI:SS'')  AS firsttime
FROM 
    v$logfile l
JOIN 
    v$log lg ON l.group# = lg.group#
ORDER BY 
    lg.group#, l.member','oracle','2025-12-02 14:31:27','2025-12-02 14:31:27'),
	 ('tb_zdb_slowsql','WITH slow_sql AS (
    SELECT 
        dhs.dbid,
        dhs.sql_id AS sqlid, 
        SUM(nvl(dhs.executions_delta, 0)) AS execcount,
        ROUND(SUM(nvl(dhs.elapsed_time_delta, 0))/1000000, 3) AS totaltime,
        ROUND(CASE WHEN SUM(nvl(dhs.executions_delta, 0)) = 0 THEN 0 ELSE SUM(nvl(dhs.elapsed_time_delta, 0))/1000000/SUM(nvl(dhs.executions_delta, 0))  END, 3) AS avgtime,
        ROUND(SUM(nvl(dhs.cpu_time_delta, 0))/1000000, 3) AS cputime,
        ROUND(SUM(nvl(dhs.iowait_delta, 0))/1000000, 3) AS iowait,
        SUM(nvl(dhs.disk_reads_delta, 0)) AS reads, 
        dhs.parsing_schema_name AS username,
        COUNT(DISTINCT dhs.instance_number) AS instnum
    FROM dba_hist_sqlstat dhs
    JOIN dba_hist_snapshot dhsnap 
        ON dhs.snap_id = dhsnap.snap_id 
        AND dhs.dbid = dhsnap.dbid  
        AND dhs.instance_number = dhsnap.instance_number  
    WHERE 
       dhsnap.end_interval_time >= SYSDATE - 1  
       AND dhs.parsing_schema_name NOT IN (''SYS'', ''SYSTEM'', ''AUDSYS'', ''MDSYS'')  
       AND nvl(dhs.elapsed_time_delta, 0) > 0  
    GROUP BY dhs.dbid, dhs.sql_id, dhs.parsing_schema_name
) 
SELECT 
    a.sqlid, 
    a.username,
    a.instnum,
    a.execcount,
    a.totaltime,
    a.avgtime,
    a.cputime,
    a.iowait,
    a.reads,
    t.sql_text AS sqltext
FROM slow_sql a
JOIN dba_hist_sqltext t 
    ON a.dbid = t.dbid 
    AND a.sqlid = t.sql_id
WHERE a.execcount>1 and (a.totaltime > 60 OR a.avgtime > 5 )
ORDER BY a.totaltime DESC, a.avgtime DESC','oracle','2025-12-03 16:06:23','2025-12-02 14:37:51');
INSERT INTO zapp.tb_dbinspectionconfig (tablename,sourcesql,dbtype,updatetime,inserttime) VALUES
	 ('tb_zdb_dblink','SELECT 
    owner    ,
    db_link  AS dblink ,
    username  ,
    host,
    TO_CHAR(created,''YYYY-MM-DD HH24:MI:SS'') AS created ,
    valid  
FROM dba_db_links','oracle','2025-12-02 14:39:49','2025-12-02 14:39:49');

巡检信息记录表

-- zapp.tb_zdb definition

CREATE TABLE `tb_zdb` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库ip',
  `dbname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库名',
  `dbtype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库类型',
  `dbversion` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库版本',
  `logmode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志模式',
  `dbrole` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库角色',
  `dbuname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '关联-主/备-数据库名',
  `israc` int DEFAULT NULL COMMENT '是否是rac(1是0否)',
  `racnum` int DEFAULT NULL COMMENT 'rac 实例数',
  `uptime` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启动时间',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库信息';


-- zapp.tb_zdb_asm definition

CREATE TABLE `tb_zdb_asm` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '磁盘组名',
  `used` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用空间',
  `total` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '全部空间',
  `free` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '空闲空间',
  `percent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '使用率',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-ASM信息';


-- zapp.tb_zdb_dblink definition

CREATE TABLE `tb_zdb_dblink` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `owner` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '所有者',
  `dblink` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'dblink名',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '连接串',
  `created` datetime DEFAULT NULL COMMENT '创建时间',
  `valid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '是否有效',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-dblink信息';


-- zapp.tb_zdb_inst definition

CREATE TABLE `tb_zdb_inst` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `instid` int DEFAULT NULL COMMENT '实例id',
  `instname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '实例名',
  `hostname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机名',
  `uptime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '启动时间',
  `inststatus` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '实例状态',
  `dbstatus` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '数据库状态',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-实例信息';


-- zapp.tb_zdb_parameter definition

CREATE TABLE `tb_zdb_parameter` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `pname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '参数名称',
  `pvalue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '参数值',
  `description` varchar(100) DEFAULT NULL COMMENT '参数描述',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=589 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-参数信息';


-- zapp.tb_zdb_partindex definition

CREATE TABLE `tb_zdb_partindex` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `tablename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表名',
  `indexname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '索引名',
  `locality` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '索引类型',
  `parttype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区类型',
  `partname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区名',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '索引状态',
  `tablespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表空间',
  `highvalue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '上限值',
  `analyzed` datetime DEFAULT NULL COMMENT '最近一次统计信息时间',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4008 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-分区索引信息';


-- zapp.tb_zdb_parttable definition

CREATE TABLE `tb_zdb_parttable` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `tablename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表名',
  `partkey` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区列',
  `parttype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区类型',
  `interval` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '间隔值',
  `subparttype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '子分区类型',
  `partname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区名',
  `highvalue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '上限值',
  `tablespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表空间',
  `size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区大小',
  `numrows` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '分区行数',
  `analyzed` datetime DEFAULT NULL COMMENT '最近一次统计信息时间',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2246 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-分区表信息';


-- zapp.tb_zdb_redolog definition

CREATE TABLE `tb_zdb_redolog` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `group` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志组号',
  `filepath` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志文件路径',
  `thread` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '线程号',
  `sequence` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志序列号',
  `size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志大小',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日志状态',
  `archived` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '是否归档',
  `firsttime` datetime DEFAULT NULL COMMENT '首次写入时间',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=419 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-redolog信息';


-- zapp.tb_zdb_slowsql definition

CREATE TABLE `tb_zdb_slowsql` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `sqlid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'sql_id',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行用户',
  `instnum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '涉及实例数',
  `execcount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行次数',
  `totaltime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '总耗时_秒',
  `avgtime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '单次平均耗时_秒',
  `cputime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '总CPU耗时_秒',
  `iowait` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '总IO等待_秒',
  `reads` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '物理读次数',
  `sqltext` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT 'SQL文本',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=581 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-慢sql信息';


-- zapp.tb_zdb_space definition

CREATE TABLE `tb_zdb_space` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `tablespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表空间',
  `used` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用空间',
  `total` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '全部空间',
  `free` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '空闲空间',
  `percent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '使用率',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=860 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-表空间信息';


-- zapp.tb_zdb_user definition

CREATE TABLE `tb_zdb_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dbip` varchar(30) DEFAULT NULL COMMENT '数据库ip',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `userstatus` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户状态',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  `locktime` datetime DEFAULT NULL COMMENT '锁定日期',
  `expirytime` datetime DEFAULT NULL COMMENT '过期日期',
  `changepstime` datetime DEFAULT NULL COMMENT '密码修改时间',
  `lastlogintime` datetime DEFAULT NULL COMMENT '最后一次登录时间',
  `dtablespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '默认表空间',
  `ttablespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '临时表空间',
  `tablenum` int DEFAULT NULL COMMENT '表数量',
  `viewnum` int DEFAULT NULL COMMENT '视图数量',
  `functionnum` int DEFAULT NULL COMMENT '函数数量',
  `indexnum` int DEFAULT NULL COMMENT '索引数量',
  `procedurenum` int DEFAULT NULL COMMENT '过程数量',
  `triggernum` int DEFAULT NULL COMMENT '触发器数量',
  `synonymnum` int DEFAULT NULL COMMENT '同义词数量',
  `sequencenum` int DEFAULT NULL COMMENT '序列数量',
  `packagenum` int DEFAULT NULL COMMENT '包数量',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2449 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据库-用户-对象信息';
数据库账号密码过期，怎样做到提前提醒？
2007 年 ~ 2025 年，深耕 SAP 技术 18 年
09-01 1097
绝大多数主流数据库都能基于自身的密码有效期元数据，配合计划任务与通知渠道，在密码过期前按阈值发出提醒。常见做法是从系统视图里取到期时间或剩余天数，计算阈值窗口，比如 14 天与 3 天，写入一张待通知表或直接调用通知函数，通过邮件、Slack 或工单系统推送给账号所有者与数据库管理员。像 Oracle、Microsoft SQL Server、PostgreSQL、MySQL、Snowflake 等，都有原生或半原生的支持；
分布式时序数据库TimeLyre 9.2发布：原生多模态、高性能计算、极速时序回放分析...
weixin_40221173的博客
08-10 92
在当今数据驱动的世界中，多模态数据已经成为企业的重要资产。随着数据规模和多样性的不断增加，企业不仅需要高效存储和处理这些数据，更需要从中提取有价值的洞察。工业领域在处理海量设备时序数据的同时，还需要联动分析警报信息、设备关系、组织信息等关系数据或图数据；金融领域除了常见的行情和订单流时序数据外，还会采用地理信息、实时新闻...
数据库运维管理系统在AI方向的实践
雪辉博客
05-31 1112
AI技术正在深刻改变关系型数据库运维管理系统的形态和能力。通过智能监控、异常检测、自动优化、故障自愈、对话式助手等AI实践，数据库运维正从“经验驱动”向“智能驱动”转型。未来，AI将成为数据库运维不可或缺的核心引擎，助力企业实现高效、安全、智能的数据管理与业务创新。
java毕业设计-基于springboot+vue的小区物业服务管理系统平台设计和实现，基于springboot的小区物业综合服务管理系统平台设计和实现(源码+LW+部署文档+远程调试+代码讲解等)j
我的个人博客：https://bishe666.top
02-25 964
java毕业设计-基于springboot+vue的小区物业服务管理系统平台设计和实现，基于springboot的小区物业综合服务管理系统平台设计和实现(源码+LW+部署文档+远程调试+代码讲解等)j
谷粒商城项目总结
初级知识储备博客
06-21 7684
谷粒商城知识点汇总，包括运维、技术、测试等
大数据与机器学习：实践方法与行业案例.2.2　数据缓冲区
weixin_34376562的博客
05-02 321
2.2　数据缓冲区 数据缓冲区是处于生产环境和分析环境之间的中间区域，它是数据闭环中各个系统间的数据中转站，从各个系统接收原始数据，并将其暂存在对应的目录中。其他系统可以从数据缓冲区中获取需要的数据文件。 为了便于管理和迁移数据，我们规定存入数据缓冲区中的数据使用文本文件的格式，这样一来，数据缓冲区就可以使用一台或几台文件服务器实现。几乎所有的应用系统都...
面试总结1
qq_44760162的博客
04-25 8296
文章目录人事你的汇报对象是谁：对运维的理解：运维每天的工作：项目linux学到了啥：数据库的理解：zabbix 【5.0】话术：监控思路（监控指标）：zabbix模式：zabbix组成部分：zabbix监控项、触发器和模板三者间的关系zabbix模板：zabbix不常见的模板：zabbix服务器迁移，如何做？简述zabbix升级过程5.0版本的新特性：zabbix中的api应用Zabbix如何发现新机器？zabbix 自定义发现:zabbix 自定义监控:zabbix客户端是怎么进行批量安装的微信报警怎么做
java物联网第三天 智慧农业物联网
fegus的博客
06-04 9934
随着国内IT事业的兴起，越来越多的小伙伴也投身到了开发这个相对高薪的行业来。很多同学进入的方式都是零基础通过培训或者看视频自学，在工作一两年后，发现由于自己的基础太薄弱，想进一步提高自己的能力变得非常 困难。...
谷粒商城（三）
小草莓的博客
07-28 2227
高级篇需要的技术前提
精选资源
Oracle 简易巡检报告脚本
11-01
该脚本可能会调用oracle_dbchk_v3.3_utf8.sql这个SQL脚本文件，后者是一个SQL脚本文件，专门用于在Oracle数据库内部执行检查命令。此SQL脚本可能包含了一系列的SQL查询和逻辑判断语句，用于搜集数据库的运行状态、...
oracle数据库巡检记录表
08-31
需要注意的是，每次巡检完成后应填写详细的巡检报告，记录发现的问题及其解决方案，以便后续跟踪和改进。此外，随着业务需求和技术环境的变化，巡检内容和频率也需要适时调整，以适应新的挑战。
oracle 日常巡检脚本，一键生成所需新的html
02-08
运行SQL脚本，生成所有数据库相关信息的html文件，可利用此脚本生成巡检结果
MySQL自动化巡检报告-v1.0
雨花石
10-31 1060
作为dba，有时候我们会遇到大量的巡检工作，之前我们都是手动去给客户做巡检，那么面对如此重要但是很繁琐的工作，如何才能提高我们的巡检效率，或者换句话说有什么工具可以自动完成巡检工作 并且生成漂亮的巡检文档呢，基于以上因素，自己尝试使用go自己写一套mysql自动巡检报告，于是在利用自己闲暇时间，终于完成了一般巡检报告，这里我暂时称作mysql自动巡检v1.0版本，使用的语言是go语言其中使用gohtml、gocharts等工具，具体的就简
兰顿蚂蚁——CAD二次开发
12-15 2217
【代码】兰顿蚂蚁——CAD二次开发。
KingbaseES 面向应用程序的SQL开发：从原理到实战的深度探索
热门推荐
优快云博客专家，领域包括但不限于：AI、大数据、Python、架构师，有合作、课程、问题、疑惑请私信博主
12-14 1万+
KingbaseES数据库SQL开发实践解析 摘要：本文深入探讨国产数据库KingbaseES在SQL开发中的核心实践，重点分析四大关键技术：1) SQL处理机制，包括游标管理和绑定变量优化；2) 正则表达式的高级应用与性能优化；3) 多类型索引的选择与优化策略；4) 事务控制与锁机制。通过具体代码示例，展示了KingbaseES在金融、政务等关键领域的高性能SQL开发能力，包括事务隔离级别设置、死锁检测等高级特性。文章为开发者提供了从基础语法到高级优化的完整SQL开发指南，助力企业实现高效可靠的数据库应用
《Python实战小课：爬虫工具场景——开启数据抓取之旅》导读
2501_93253814的博客
12-15 2575
本文介绍了Python爬虫技术在三大场景中的应用：行业资讯爬取、学术文献摘要获取和电商评价收集。针对行业资讯，详细解析了从网页请求到数据存储的全流程；在学术文献方面，重点阐述了如何构建搜索请求和提取关键信息；对于电商评价，则说明了数据定位和清洗方法。文章还探讨了爬虫优化策略及反爬机制应对方案，为数据获取工作提供了实用指南。通过系统学习这些技术，读者可以提升数据采集能力，为商业决策、学术研究和市场分析提供有力支持。
【FastAPI】FastAPI依赖注入完全指南
最新发布
weixin_63434398的博客
12-18 1987
本文全面介绍FastAPI的依赖注入机制，主要包含两大注入方式：原生自动注入和Depends自定义注入。原生注入支持Request/Response等框架对象以及路径/查询参数等自动解析，而Depends则用于自定义依赖项注入。文章详细讲解依赖注入的核心思想、实现方式、生命周期管理、高级技巧，并通过完整认证系统案例展示实战应用。最后剖析Depends底层原理，并与Spring框架进行对比，帮助开发者彻底掌握FastAPI这一核心特性，实现代码解耦和高效开发。
nl2sql技术实现自动sql生成之Spring AI Alibaba Nl2sql
疾风sxp的博客
12-15 966
上一节我们在介绍了自然语言转sql的基本思路，今天我们通过实操来解决这一技术实现，通过Spring AI Alibaba开源的nl2sql框架进行这块技术的讲解。
实习面试题-Redis 面试题
CXY00000的博客
12-13 831
Redis主从复制实现原理分为三个阶段：1)全量同步阶段，从节点首次连接主节点时，主节点生成RDB快照并发送给从节点，同时将期间新写入命令暂存到replication buffer；2)命令传播阶段，主节点异步发送写命令给从节点；3)增量同步阶段，通过repl_backlog_buffer环形缓冲区实现断线后的部分数据同步。主从架构实现了读写分离和数据备份。 Redis集群采用哈希槽分片(16384个槽)、Gossip协议和去中心化设计。数据根据CRC16(key)%16384分配到不同节点，客户端通过MO
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

为什么不问问神奇的海螺呢丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值