Oracle数据库进阶用法个人知识库

Oracle数据库进阶用法个人知识库

Date: January 24, 2026
Version: 2.0
适用版本: Oracle 19c/21c
状态: 正式发布

目录

  1. Oracle 19c/21c 新特性

  2. 高级 SQL 优化

  3. PL/SQL 高级编程

  4. 性能调优实战

  5. 安全管理进阶

  6. 高可用架构设计

  7. 云原生部署与运维

  8. 监控与诊断

  9. 最佳实践与案例

  10. 附录

Oracle 19c/21c 新特性

1.1 自动化管理增强

自动索引（Automatic Indexing）

功能描述：数据库自动创建、维护和删除索引


-- 启用自动索引
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

-- 查看自动索引状态
SELECT index_name, status, auto_index 
FROM user_indexes 
WHERE auto_index = 'YES';

-- 禁用特定表的自动索引
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', 'FALSE');

自适应执行计划（Adaptive Execution Plans）

功能描述：运行时根据实际数据分布调整执行计划


-- 启用自适应执行计划
ALTER SESSION SET OPTIMIZER_ADAPTIVE_REPORTING_ONLY = FALSE;

-- 查看执行计划统计信息
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'ADAPTIVE'));

1.2 JSON 支持增强

原生 JSON 数据类型


-- 创建包含JSON列的表
CREATE TABLE customers (
    id NUMBER PRIMARY KEY,
    customer_data JSON
);

-- JSON数据插入
INSERT INTO customers VALUES (1, 
    '{"name": "John Doe", "email": "john@example.com", "address": {"city": "New York"}}'
);

-- JSON数据查询
SELECT 
    c.id,
    c.customer_data.name,
    c.customer_data.address.city
FROM customers c;

JSON 关系视图（21c 新特性）


-- 创建JSON关系视图
CREATE OR REPLACE VIEW customers_json_view AS
SELECT 
    JSON_OBJECT(
        'id' VALUE id,
        'name' VALUE name,
        'email' VALUE email,
        'address' VALUE JSON_OBJECT(
            'street' VALUE street,
            'city' VALUE city,
            'zip' VALUE zip_code
        )
    ) AS customer_json
FROM customers_relational;

1.3 安全增强特性

原生区块链表（21c 新特性）


-- 创建区块链表
CREATE TABLE transactions_blockchain (
    transaction_id NUMBER,
    user_id NUMBER,
    amount NUMBER,
    transaction_date TIMESTAMP
) ORGANIZATION BLOCKCHAIN
HASHING USING SHA256
VERSION 1
NO DROP UNTIL 30 DAYS IDLE
NO DELETE;

数据脱敏（Data Redaction）


-- 创建脱敏策略
BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema => 'HR',
        object_name => 'EMPLOYEES',
        column_name => 'SSN',
        policy_name => 'REDACT_SSN',
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => 'VVVF,ssn,VVV-XX-XXXX',
        expression => '1=1'
    );
END;
/

高级 SQL 优化

2.1 执行计划分析

EXPLAIN PLAN 详解


-- 生成执行计划
EXPLAIN PLAN FOR
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看详细执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST'));

SQL Trace 和 TKPROF


-- 启用SQL Trace
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET TIMED_STATISTICS = TRUE;

-- 执行SQL语句
SELECT * FROM employees WHERE department_id = 10;

-- 禁用SQL Trace
ALTER SESSION SET SQL_TRACE = FALSE;

-- 使用TKPROF分析跟踪文件
tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc output=trace_analysis.txt explain=system/manager

2.2 索引优化策略

索引类型选择指南

索引类型 适用场景 优点 缺点
B 树索引 高基数列，等值查询 查询速度快，支持范围查询 维护成本高
位图索引 低基数列，OLAP 系统 存储空间小，AND/OR 查询高效 不适合高并发 DML
函数索引 函数或表达式查询 加速函数查询 维护成本高
反向键索引 序列生成的主键 避免热点块竞争 不支持范围查询
降序索引 降序排序查询 优化降序排序 维护成本略高

索引创建最佳实践


-- 创建B树索引
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- 创建函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

-- 重建索引
ALTER INDEX idx_emp_dept REBUILD ONLINE;

-- 监控索引使用
ALTER INDEX idx_emp_dept MONITORING USAGE;

-- 查看索引使用情况
SELECT index_name, monitoring, used 
FROM v$object_usage;

2.3 SQL 优化技巧

子查询优化


-- 低效的相关子查询
SELECT e.employee_id, e.name
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.employee_id = e.employee_id 
    AND o.order_date > SYSDATE - 30
);

-- 优化为连接查询
SELECT DISTINCT e.employee_id, e.name
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
WHERE o.order_date > SYSDATE - 30;

分页查询优化


-- 传统分页（低效）
SELECT * FROM (
    SELECT /*+ FIRST_ROWS(20) */ 
        e.*, 
        ROWNUM AS rn
    FROM employees e
    ORDER BY e.employee_id
) 
WHERE rn BETWEEN 21 AND 40;

-- 优化分页（21c新特性）
SELECT * FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

批量操作优化


-- 使用BULK COLLECT批量获取数据
DECLARE
    TYPE emp_type IS TABLE OF employees%ROWTYPE;
    v_employees emp_type;
BEGIN
    SELECT * BULK COLLECT INTO v_employees
    FROM employees
    WHERE department_id = 10;
    
    -- 批量处理数据
    FORALL i IN 1..v_employees.COUNT
        UPDATE employees 
        SET salary = salary * 1.1
        WHERE employee_id = v_employees(i).employee_id;
END;
/

PL/SQL 高级编程

3.1 存储过程优化

模块化设计模式


-- 包规范定义
CREATE OR REPLACE PACKAGE employee_mgmt IS
    -- 类型定义
    TYPE emp_rec_type IS RECORD (
        emp_id employees.employee_id%TYPE,
        emp_name employees.name%TYPE,
        salary employees.salary%TYPE
    );
    
    TYPE emp_table_type IS TABLE OF emp_rec_type INDEX BY PLS_INTEGER;
    
    -- 过程声明
    PROCEDURE get_employees_by_dept(
        p_dept_id IN departments.department_id%TYPE,
        p_emp_table OUT emp_table_type
    );
    
    PROCEDURE update_employee_salary(
        p_emp_id IN employees.employee_id%TYPE,
        p_new_salary IN employees.salary%TYPE
    );
    
    FUNCTION calculate_annual_salary(
        p_monthly_salary IN employees.salary%TYPE
    ) RETURN NUMBER;
END employee_mgmt;
/

-- 包体实现
CREATE OR REPLACE PACKAGE BODY employee_mgmt IS
    PROCEDURE get_employees_by_dept(
        p_dept_id IN departments.department_id%TYPE,
        p_emp_table OUT emp_table_type
    ) IS
    BEGIN
        SELECT employee_id, name, salary
        BULK COLLECT INTO p_emp_table
        FROM employees
        WHERE department_id = p_dept_id;
    END;
    
    PROCEDURE update_employee_salary(
        p_emp_id IN employees.employee_id%TYPE,
        p_new_salary IN employees.salary%TYPE
    ) IS
    BEGIN
        UPDATE employees
        SET salary = p_new_salary,
            last_update_date = SYSDATE
        WHERE employee_id = p_emp_id;
        
        IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
        END IF;
    END;
    
    FUNCTION calculate_annual_salary(
        p_monthly_salary IN employees.salary%TYPE
    ) RETURN NUMBER IS
    BEGIN
        RETURN p_monthly_salary * 12;
    END;
END employee_mgmt;
/

3.2 异常处理机制

高级异常处理


CREATE OR REPLACE PROCEDURE complex_business_process(
    p_param1 IN NUMBER,
    p_param2 IN VARCHAR2
) IS
    -- 自定义异常
    invalid_input EXCEPTION;
    business_rule_violation EXCEPTION;
    PRAGMA EXCEPTION_INIT(invalid_input, -20001);
    PRAGMA EXCEPTION_INIT(business_rule_violation, -20002);
    
    v_result NUMBER;
BEGIN
    -- 参数验证
    IF p_param1 IS NULL OR p_param1 <= 0 THEN
        RAISE invalid_input;
    END IF;
    
    -- 业务逻辑处理
    BEGIN
        -- 调用其他过程
        some_other_procedure(p_param1, p_param2);
        
        -- 计算结果
        v_result := calculate_complex_value(p_param1);
        
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- 处理数据不存在的情况
            LOG_ERROR('Data not found for parameter: ' || p_param1);
            RAISE;
            
        WHEN OTHERS THEN
            -- 记录错误并重新抛出
            LOG_ERROR('Error in business process: ' || SQLERRM);
            RAISE;
    END;
    
    -- 提交事务
    COMMIT;
    
EXCEPTION
    WHEN invalid_input THEN
        LOG_ERROR('Invalid input parameter: ' || p_param1);
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Invalid input parameters');
        
    WHEN business_rule_violation THEN
        LOG_ERROR('Business rule violated: ' || SQLERRM);
        ROLLBACK;
        RAISE;
        
    WHEN OTHERS THEN
        LOG_ERROR('Unhandled exception: ' || SQLERRM);
        ROLLBACK;
        RAISE;
END;
/

3.3 SQL 宏（21c 新特性）

Scalar 宏定义


-- 创建Scalar宏
CREATE OR REPLACE FUNCTION clip(
    p_value NUMBER,
    p_min NUMBER,
    p_max NUMBER
) RETURN NUMBER SQL_MACRO SCALAR IS
BEGIN
    RETURN q'{
        CASE 
            WHEN p_value < p_min THEN p_min
            WHEN p_value > p_max THEN p_max
            ELSE p_value
        END
    }';
END;
/

-- 使用Scalar宏
SELECT 
    employee_id,
    salary,
    clip(salary, 3000, 10000) AS clipped_salary
FROM employees;

Table 宏定义


-- 创建Table宏
CREATE OR REPLACE FUNCTION get_employees_by_salary_range(
    p_min_salary NUMBER,
    p_max_salary NUMBER
) RETURN VARCHAR2 SQL_MACRO TABLE IS
BEGIN
    RETURN q'{
        SELECT e.*
        FROM employees e
        WHERE e.salary BETWEEN p_min_salary AND p_max_salary
    }';
END;
/

-- 使用Table宏
SELECT * FROM get_employees_by_salary_range(5000, 8000);

性能调优实战

4.1 AWR 报告分析

生成 AWR 报告


-- 手动生成AWR报告
@?/rdbms/admin/awrrpt.sql

-- 生成特定快照范围的AWR报告
SELECT snap_id, begin_interval_time 
FROM dba_hist_snapshot 
ORDER BY snap_id DESC;

-- 生成AWR对比报告
@?/rdbms/admin/awrddrpt.sql

关键指标分析


-- 查看TOP SQL
SELECT 
    sql_id,
    elapsed_time_total/1000000 AS elapsed_time_sec,
    executions_total,
    elapsed_time_total/executions_total/1000000 AS avg_time_sec,
    sql_text
FROM dba_hist_sqlstat
WHERE elapsed_time_total > 0
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

-- 等待事件分析
SELECT 
    event,
    total_waits,
    time_waited_micro/1000000 AS time_waited_sec,
    average_waiter_count
FROM dba_hist_system_event
WHERE event NOT IN ('SQL*Net message to client', 'SQL*Net message from client')
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

4.2 ADDM 自动诊断

运行 ADDM 分析


-- 创建ADDM任务
DECLARE
    v_task_id VARCHAR2(30);
BEGIN
    v_task_id := DBMS_ADDM.ANALYZE_DB(
        start_snap_id => &start_snap,
        end_snap_id => &end_snap
    );
    DBMS_OUTPUT.PUT_LINE('ADDM Task ID: ' || v_task_id);
END;
/

-- 查看ADDM报告
SELECT DBMS_ADDM.GET_REPORT('&task_id') FROM DUAL;

自动 SQL 调优


-- 启用自动SQL调优
BEGIN
    DBMS_AUTO_TASK_ADMIN.ENABLE(
        client_name => 'sql tuning advisor',
        operation => NULL,
        window_name => NULL
    );
END;
/

-- 手动运行SQL调优顾问
DECLARE
    v_task_id VARCHAR2(30);
BEGIN
    v_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => '&sql_id',
        scope => 'COMPREHENSIVE',
        time_limit => 300
    );
    
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task_id);
    
    DBMS_OUTPUT.PUT_LINE('Tuning Task ID: ' || v_task_id);
END;
/

-- 查看调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_id') FROM DUAL;

4.3 统计信息管理

收集统计信息


-- 收集表统计信息
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'HR',
        tabname => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        degree => 8,
        cascade => TRUE
    );
END;
/

-- 收集模式统计信息
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname => 'HR',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        degree => 8,
        cascade => TRUE
    );
END;
/

-- 锁定统计信息
BEGIN
    DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

4.4 内存管理优化

SGA 和 PGA 配置


-- 查看当前内存配置
SELECT 
    name,
    value/1024/1024 AS size_mb,
    isdefault
FROM v$parameter
WHERE name IN ('sga_max_size', 'sga_target', 'pga_aggregate_target');

-- 自动内存管理配置
ALTER SYSTEM SET memory_target = 8G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 12G SCOPE=SPFILE;

-- 手动内存管理配置
ALTER SYSTEM SET sga_target = 6G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

安全管理进阶

5.1 细粒度审计（FGA）

配置 FGA 策略


-- 创建FGA策略
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema => 'HR',
        object_name => 'EMPLOYEES',
        policy_name => 'AUDIT_SALARY_ACCESS',
        audit_condition => 'salary > 10000',
        audit_column => 'SALARY',
        handler_schema => 'AUDIT_ADMIN',
        handler_module => 'LOG_SALARY_ACCESS',
        enable => TRUE
    );
END;
/

-- 创建审计处理函数
CREATE OR REPLACE PROCEDURE LOG_SALARY_ACCESS(
    p_schema_name VARCHAR2,
    p_table_name VARCHAR2,
    p_policy_name VARCHAR2
) IS
BEGIN
    INSERT INTO audit_logs(
        event_time,
        event_type,
        schema_name,
        table_name,
        os_user,
        db_user,
        ip_address
    ) VALUES (
        SYSTIMESTAMP,
        'SALARY_ACCESS',
        p_schema_name,
        p_table_name,
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    );
END;
/

-- 查看审计数据
SELECT * FROM dba_fga_audit_trail;

5.2 统一审计（Unified Auditing）

启用统一审计


-- 检查统一审计状态
SELECT VALUE FROM v$option WHERE PARAMETER = 'Unified Auditing';

-- 启用统一审计（需要重启数据库）
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET AUDIT_TRAIL=DB,UNIFIED SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

创建统一审计策略


-- 创建审计策略
CREATE AUDIT POLICY secure_hr_policy
    PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
    ACTIONS DELETE ON hr.employees,
            UPDATE ON hr.employees
    ROLES hr_manager;

-- 启用审计策略
AUDIT POLICY secure_hr_policy;

-- 查看审计数据
SELECT * FROM unified_audit_trail;

5.3 透明数据加密（TDE）

配置 TDE


-- 创建加密钱包
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/orcl/wallet' 
IDENTIFIED BY "StrongPassword123";

-- 打开加密钱包
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
IDENTIFIED BY "StrongPassword123";

-- 设置主加密密钥
ADMINISTER KEY MANAGEMENT SET KEY 
IDENTIFIED BY "StrongPassword123" WITH BACKUP;

-- 创建加密表空间
CREATE TABLESPACE encrypted_ts
    DATAFILE '/u01/app/oracle/oradata/orcl/encrypted_ts01.dbf' SIZE 100M
    ENCRYPTION USING 'AES256'
    DEFAULT STORAGE(ENCRYPT);

5.4 虚拟私有数据库（VPD）

配置 VPD 策略


-- 创建VPD策略函数
CREATE OR REPLACE FUNCTION secure_salary_data(
    p_schema IN VARCHAR2,
    p_table IN VARCHAR2
) RETURN VARCHAR2 IS
    v_predicate VARCHAR2(4000);
BEGIN
    -- 根据用户角色设置不同的访问条件
    IF SYS_CONTEXT('USERENV', 'CURRENT_USER') = 'HR_MANAGER' THEN
        -- HR经理可以查看所有数据
        v_predicate := '1=1';
    ELSIF SYS_CONTEXT('USERENV', 'CURRENT_USER') = 'DEPARTMENT_MANAGER' THEN
        -- 部门经理只能查看本部门数据
        v_predicate := 'department_id = (SELECT department_id FROM employees WHERE employee_id = ' || 
                      SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') || ')';
    ELSE
        -- 普通员工不能查看薪资数据
        v_predicate := '1=0';
    END IF;
    
    RETURN v_predicate;
END;
/

-- 应用VPD策略
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema => 'HR',
        object_name => 'EMPLOYEES',
        policy_name => 'SECURE_SALARY_POLICY',
        function_schema => 'SECURITY_ADMIN',
        policy_function => 'secure_salary_data',
        statement_types => 'SELECT, INSERT, UPDATE, DELETE'
    );
END;
/

高可用架构设计

6.1 RAC 集群架构

RAC 核心组件


-- 查看RAC集群信息
SELECT * FROM v$cluster;

-- 查看RAC节点信息
SELECT 
    inst_id,
    instance_name,
    host_name,
    status
FROM gv$instance;

-- 查看RAC服务配置
SELECT 
    name,
    network_name,
    failover_method,
    failover_type,
    enabled
FROM dba_services;

Cache Fusion 优化


-- 查看全局缓存统计信息
SELECT 
    inst_id,
    gc_cr_block_received,
    gc_current_block_received,
    gc_block_time
FROM gv$sysstat
WHERE name IN ('gc cr blocks received', 'gc current blocks received');

-- 查看等待事件
SELECT 
    inst_id,
    event,
    total_waits,
    time_waited
FROM gv$system_event
WHERE event LIKE 'gc%'
ORDER BY time_waited DESC;

6.2 Data Guard 配置

Data Guard 架构设计


-- 查看Data Guard配置
SELECT 
    name,
    database_role,
    protection_mode,
    protection_level,
    switchover_status
FROM v$database;

-- 查看备库状态
SELECT 
    process,
    status,
    thread#,
    sequence#,
    block#,
    blocks
FROM v$managed_standby;

-- 查看日志传输状态
SELECT 
    dest_id,
    destination,
    status,
    error,
    archived_thread#,
    archived_seq#
FROM v$archive_dest_status;

切换和故障转移操作


-- 主库切换到备库（Switchover）
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- 备库切换到主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- 故障转移（Failover）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

6.3 MAA 架构设计

Maximum Availability Architecture


-- 配置Fast-Start Failover
ALTER SYSTEM SET DG_BROKER_START=TRUE;

-- 启用Fast-Start Failover
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- 配置闪回数据库
ALTER DATABASE FLASHBACK ON;

-- 设置闪回保留时间
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

云原生部署与运维

7.1 容器化部署

Kubernetes 部署配置


# Oracle数据库Kubernetes部署配置
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: oracle-db
spec:
  serviceName: "oracle"
  replicas: 1
  selector:
    matchLabels:
      app: oracle-db
  template:
    metadata:
      labels:
        app: oracle-db
    spec:
      containers:
      - name: oracle
        image: oracle/database:19.3.0-ee
        ports:
        - containerPort: 1521
        env:
        - name: ORACLE_SID
          value: ORCLCDB
        - name: ORACLE_PDB
          value: ORCLPDB1
        - name: ORACLE_PWD
          valueFrom:
            secretKeyRef:
              name: oracle-secret
              key: password
        volumeMounts:
        - name: oracle-data
          mountPath: /opt/oracle/oradata
  volumeClaimTemplates:
  - metadata:
      name: oracle-data
    spec:
      accessModes: [ "ReadWriteOnce" ]
      resources:
        requests:
          storage: 100Gi

7.2 自动化运维脚本

数据库健康检查脚本


#!/bin/bash
# Oracle数据库健康检查脚本

ORACLE_SID="ORCLCDB"
ORACLE_HOME="/u01/app/oracle/product/19.3.0/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH

# 检查数据库状态
db_status=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
SELECT status FROM v\$instance;
EOF
)

if [ "$db_status" = "OPEN" ]; then
    echo "Database is running normally"
else
    echo "Database status: $db_status"
    # 发送告警通知
    send_alert "Database status abnormal: $db_status"
fi

# 检查表空间使用率
tablespace_usage=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
SELECT tablespace_name, 
       ROUND((used_space/total_space)*100,2) AS usage_pct
FROM (
    SELECT 
        tablespace_name,
        SUM(bytes)/1024/1024 AS total_space,
        SUM(bytes - decode(autoextensible, 'YES', maxbytes, bytes))/1024/1024 AS used_space
    FROM dba_data_files
    GROUP BY tablespace_name
)
WHERE ROUND((used_space/total_space)*100,2) > 85;
EOF
)

if [ -n "$tablespace_usage" ]; then
    echo "High tablespace usage detected:"
    echo "$tablespace_usage"
    send_alert "High tablespace usage: $tablespace_usage"
fi

# 检查备份状态
backup_status=$(sqlplus -s / as sysdba << EOF
set heading off feedback off pagesize 0
SELECT CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'OK' ELSE 'FAIL' END 
FROM v\$rman_backup_job_details
WHERE status = 'COMPLETED';
EOF
)

if [ "$backup_status" != "OK" ]; then
    send_alert "Database backup failed or not completed in last 24 hours"
fi

监控与诊断

8.1 实时监控查询

会话和锁监控


-- 查看当前会话
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.status,
    s.logon_time
FROM v$session s
WHERE s.type = 'USER'
ORDER BY s.logon_time DESC;

-- 查看锁等待
SELECT 
    l.session_id,
    l.oracle_username,
    l.os_user_name,
    o.object_name,
    l.locked_mode,
    l.blocking_session
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
ORDER BY l.blocking_session DESC;

-- 终止问题会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

性能实时监控


-- 查看当前等待事件
SELECT 
    event,
    COUNT(*) AS session_count,
    SUM(seconds_in_wait) AS total_wait_time
FROM v$session_wait
WHERE event NOT IN ('SQL*Net message to client', 'SQL*Net message from client')
GROUP BY event
ORDER BY total_wait_time DESC;

-- 查看TOP SQL执行情况
SELECT 
    sql_id,
    sql_text,
    elapsed_time/1000000 AS elapsed_time_sec,
    cpu_time/1000000 AS cpu_time_sec,
    disk_reads,
    buffer_gets,
    executions
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

8.2 诊断事件配置

启用诊断事件


-- 启用SQL跟踪事件
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 启用等待事件跟踪
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

-- 启用错误跟踪
ALTER SYSTEM SET EVENTS 'ORA-600 trace name errorstack, level 3';

-- 查看当前启用的事件
SELECT * FROM v$event_info;

最佳实践与案例

9.1 性能优化案例

案例一：慢查询优化

问题描述：某报表查询执行时间超过 30 秒


-- 原查询（低效）
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS emp_count,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

-- 优化后查询
SELECT 
    d.department_name,
    NVL(emp_stats.emp_count, 0) AS emp_count,
    NVL(emp_stats.avg_salary, 0) AS avg_salary
FROM departments d
LEFT JOIN (
    SELECT 
        department_id,
        COUNT(employee_id) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) emp_stats ON d.department_id = emp_stats.department_id
ORDER BY avg_salary DESC;

优化措施：

  1. 将聚合查询移到子查询中，减少连接的数据量

  2. 添加适当的索引：CREATE INDEX idx_emp_dept ON employees(department_id);

  3. 收集统计信息：DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

优化效果：查询时间从 30 秒降低到 1.2 秒

案例二：分区表设计

业务场景：订单表数据量超过 1 亿行，查询性能下降


-- 创建分区表
CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    amount NUMBER,
    status VARCHAR2(20)
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION orders_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
    PARTITION orders_future VALUES LESS THAN (MAXVALUE)
);

-- 本地索引
CREATE INDEX idx_orders_customer ON orders(customer_id) LOCAL;

-- 查询特定分区
SELECT * FROM orders PARTITION (orders_2024)
WHERE customer_id = 12345;

9.2 高可用案例

案例：RAC+Data Guard 双活架构


生产机房（主库RAC集群）
├── Node 1（实例1）
├── Node 2（实例2）
└── 共享存储

同城灾备机房（备库RAC集群）
├── Node 3（实例3）
├── Node 4（实例4）
└── 共享存储

异地灾备机房（物理备库）
├── Node 5（实例5）
└── 本地存储

配置要点：

  1. 主备库均采用 RAC 集群架构

  2. 使用 Data Guard 进行实时数据同步

  3. 配置 Fast-Start Failover 实现自动切换

  4. 启用闪回数据库支持快速恢复

  5. 配置 GoldenGate 实现零数据丢失

附录

A. 常用 SQL 脚本

A.1 性能监控脚本


-- 系统整体性能
SELECT 
    'CPU使用率' AS metric,
    ROUND((1 - (SELECT value FROM v$sysstat WHERE name = 'idle time') / 
          (SELECT value FROM v$sysstat WHERE name = 'CPU used by this session')) * 100, 2) AS value
FROM dual
UNION ALL
SELECT 
    '内存使用率' AS metric,
    ROUND((SELECT (sga_size + pga_size) / (sga_max_size + pga_max_size) * 100 
           FROM (SELECT 
                   SUM(value) AS sga_size,
                   SUM(CASE WHEN name = 'sga_max_size' THEN value ELSE 0 END) AS sga_max_size
                 FROM v$parameter 
                 WHERE name IN ('sga_target', 'sga_max_size')),
                 (SELECT 
                   SUM(value) AS pga_size,
                   SUM(CASE WHEN name = 'pga_aggregate_target' THEN value ELSE 0 END) AS pga_max_size
                 FROM v$parameter 
                 WHERE name IN ('pga_aggregate_target'))) * 100, 2) AS value
FROM dual;

-- TOP SQL监控
SELECT 
    sql_id,
    sql_text,
    elapsed_time/1000000 AS elapsed_time_sec,
    cpu_time/1000000 AS cpu_time_sec,
    disk_reads,
    buffer_gets,
    executions,
    rows_processed
FROM v$sql
WHERE elapsed_time > 0
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

A.2 维护脚本


-- 表空间扩展监控
SELECT 
    tablespace_name,
    file_name,
    bytes/1024/1024 AS current_size_mb,
    maxbytes/1024/1024 AS max_size_mb,
    autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'
AND (bytes/maxbytes) > 0.9;

-- 无效对象检查
SELECT 
    owner,
    object_type,
    COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;

-- 重建无效对象
BEGIN
    FOR rec IN (
        SELECT owner, object_name, object_type
        FROM dba_objects
        WHERE status = 'INVALID'
        AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
    ) LOOP
        IF rec.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';
        ELSE
            EXECUTE IMMEDIATE 'ALTER ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ' COMPILE';
        END IF;
    END LOOP;
END;
/

B. 常用数据字典视图

B.1 性能相关视图

视图名称 用途
V$SYSSTAT 系统统计信息
V$SESSTAT 会话统计信息
V$SYSTEM_EVENT 系统等待事件
V$SESSION_WAIT 会话等待事件
V$SQL SQL 执行统计
V$SQL_PLAN SQL 执行计划
V$LOCK 锁信息
V$SESSION 会话信息

B.2 存储相关视图

视图名称 用途
DBA_DATA_FILES 数据文件信息
DBA_TABLESPACES 表空间信息
DBA_FREE_SPACE 空闲空间信息
DBA_SEGMENTS 段信息
DBA_EXTENTS 区信息

B.3 安全相关视图

视图名称 用途
DBA_USERS 用户信息
DBA_ROLES 角色信息
DBA_SYS_PRIVS 系统权限
DBA_TAB_PRIVS 对象权限
DBA_AUDIT_TRAIL 审计信息

C. 常用 PL/SQL 包

C.1 管理包

包名称 用途
DBMS_STATS 统计信息管理
DBMS_SQLTUNE SQL 调优
DBMS_ADDM 自动诊断
DBMS_SCHEDULER 任务调度
DBMS_BACKUP_RESTORE 备份恢复

C.2 安全包

包名称 用途
DBMS_FGA 细粒度审计
DBMS_REDACT 数据脱敏
DBMS_RLS 行级安全
DBMS_CRYPTO 数据加密

D. 联系方式和资源

D.1 官方资源

D.2 社区资源

posted @ 2026-01-24 09:09  MufeeSama  阅读(0)  评论(0)    收藏  举报