Oracle数据库进阶用法有哪些技巧和最佳实践？
摘要：Oracle数据库进阶用法个人知识库 Date: January 24, 2026 Version: 2.0 适用版本: Oracle 19c21c 状态: 正式发布 目录 Oracle 19c21c 新特性 高级 SQL 优化 PL
Oracle数据库进阶用法个人知识库
Date: January 24, 2026
Version: 2.0
适用版本: Oracle 19c/21c
状态: 正式发布
目录
Oracle 19c/21c 新特性
高级 SQL 优化
PL/SQL 高级编程
性能调优实战
安全管理进阶
高可用架构设计
云原生部署与运维
监控与诊断
最佳实践与案例
附录
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;
优化措施：
将聚合查询移到子查询中，减少连接的数据量
添加适当的索引：CREATE INDEX idx_emp_dept ON employees(department_id);
收集统计信息：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）
└── 本地存储
配置要点：
主备库均采用 RAC 集群架构
使用 Data Guard 进行实时数据同步
配置 Fast-Start Failover 实现自动切换
启用闪回数据库支持快速恢复
配置 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 官方资源
Oracle 官方文档
Oracle 技术网络
Oracle 大学
D.2 社区资源
Ask TOM
Oracle 论坛
GitHub Oracle 项目