-- 查看表结构
DESC table_name;
-- 查询表数据
SELECT * FROM table_name WHERE condition;
-- 查看表统计信息
SELECT * FROM dba_tables WHERE table_name = 'TABLE_NAME';
1.2 空间管理
-- 表空间使用情况
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files;
-- 表空间碎片检测
SELECT tablespace_name, sum(bytes)/1024/1024 free_space_mb
FROM dba_free_space
GROUP BY tablespace_name;
2. 权限与会话
2.1 权限管理
-- 用户权限查询
SELECT * FROM dba_sys_privs WHERE grantee = 'USER_NAME';
2.2 会话监控
-- 当前活动会话
SELECT sid, serial#, username, status, machine FROM v$session;
-- 会话等待事件
SELECT sid, event, wait_time, state FROM v$session_wait;
3. 锁与资源
-- 锁信息查询
SELECT * FROM v$locked_object;
-- 资源限制状态
SELECT * FROM v$resource_limit WHERE resource_name IN ('processes','sessions');
二、故障排查
1. 数据库启动故障
1.1 启动步骤检查
-- 分阶段启动
STARTUP NOMOUNT; -- 检查参数文件
ALTER DATABASE MOUNT; -- 检查控制文件
ALTER DATABASE OPEN; -- 检查数据文件
1.2 关键日志分析
-- 查看告警日志路径
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
-- 检查ORA-600错误
SELECT * FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-00600%';
2. 数据恢复场景
2.1 文件丢失处理
-- 检查数据文件状态
SELECT file#, name, status FROM v$datafile WHERE status != 'ONLINE';
-- 离线后打开数据库
ALTER DATABASE DATAFILE '/path/file.dbf' OFFLINE;
ALTER DATABASE OPEN;
-- 高负载SQL
SELECT sql_id, executions, cpu_time/1e6 "CPU(s)"
FROM v$sqlarea
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;
-- 等待事件Top 5
SELECT event, total_waits, time_waited_micro/1e6 "Wait(s)"
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 5 ROWS ONLY;
3.2 历史性能分析
-- AWR中的TOP SQL（需诊断包许可）
SELECT sql_id, SUM(elapsed_time)/1e6 "Total(s)"
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY 2 DESC
FETCH FIRST 10 ROWS ONLY;
三、性能调优
1. SQL优化
1.1 执行计划分析
-- 生成执行计划
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 绑定变量捕获
SELECT * FROM v$sql_bind_capture WHERE sql_id = 'abc123';
1.2 索引优化
-- 索引使用统计
SELECT index_name, blevel, leaf_blocks
FROM dba_indexes
WHERE table_name = 'EMPLOYEES';
-- 缺失索引建议（需AWR）
SELECT table_name, sql_text
FROM dba_hist_sqlstat
WHERE executions > 1000
AND optimizer_cost > 10000;
2. 内存优化
2.1 缓冲区分析
-- 缓冲池命中率
SELECT (1 - (phy.value / (cur.value + con.value))) * 100 "Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads';
2.2 PGA管理
-- PGA使用情况
SELECT name, value/1024/1024 "MB"
FROM v$pgastat
WHERE name IN ('total PGA allocated','maximum PGA allocated');
四、高可用与集群（RAC）
1. 集群状态检查
-- 节点状态
SELECT inst_id, instance_name, host_name, status FROM gv$instance;
-- 服务分布
SELECT service_name, inst_id, count(*) sessions
FROM gv$session
GROUP BY service_name, inst_id;
2. 资源争用分析
-- 全局缓存等待
SELECT event, total_waits, time_waited_micro
FROM gv$system_event
WHERE event LIKE 'gc%'
ORDER BY time_waited_micro DESC;
-- 热点块检测
SELECT file#, block#, class#, count(*)
FROM gv$bh
GROUP BY file#, block#, class#
ORDER BY count(*) DESC
FETCH FIRST 5 ROWS ONLY;
五、备份与恢复
1. 备份状态监控
-- RMAN作业历史
SELECT start_time, end_time, input_type, status
FROM v$rman_status
WHERE start_time > SYSDATE -1;
2. 恢复验证
-- 数据文件恢复需求
SELECT file#, error, change#
FROM v$recover_file;
-- 闪回日志分析
SELECT oldest_flashback_scn, retention_target
FROM v$flashback_database_log;
六、高级工具
1. 诊断工具
-- ASH实时分析
SELECT sample_time, session_id, sql_id, event
FROM v$active_session_history
WHERE sample_time > SYSDATE -5/1440; -- 最近5分钟
-- SQL Monitor报告
SELECT dbms_sqltune.report_sql_monitor(sql_id => 'abc123')
FROM dual;
