前言Oracle DBA 的日常工作并不是简单地执行几条 SQL。数据库启动失败、会话阻塞、表空间告警、UNDO 异常、SQL 性能下降、归档堆积、备份失效、Data Guard 延迟这些问题最终都需要通过命令和动态性能视图定位。本文整理了 Oracle DBA 工作中使用频率较高的 100 条命令覆盖实例管理、参数配置、会话排查、锁分析、SQL 性能、表空间、用户权限、UNDO、临时表空间、RMAN、Data Guard、RAC、Data Pump 和操作系统诊断等场景。文中的用户名、路径、SID、SQL_ID、表空间名称和数据文件编号均为示例执行时需要根据实际环境替换。涉及删除、恢复、强制终止会话等操作时应先确认影响范围避免直接在生产环境中照搬执行。一、实例与数据库管理1. 以 SYSDBA 身份登录数据库sqlplus / as sysdba远程登录可以使用sqlplus sysorcl as sysdba2. 启动数据库STARTUP;该命令依次完成实例启动、控制文件加载和数据库打开。3. 启动数据库到 MOUNT 状态STARTUP MOUNT;MOUNT 状态通常用于数据库恢复、启用归档模式、修改数据文件路径等操作。4. 启动数据库到 NOMOUNT 状态STARTUP NOMOUNT;NOMOUNT 状态通常用于创建数据库、重建控制文件或恢复控制文件。5. 将数据库从 MOUNT 状态打开ALTER DATABASE OPEN;如果需要以只读方式打开ALTER DATABASE OPEN READ ONLY;6. 正常关闭数据库SHUTDOWN IMMEDIATE;生产环境通常优先使用IMMEDIATE它会回滚未提交事务并断开用户连接不需要等待所有会话主动退出。7. 查看实例状态SELECT instance_name, host_name, version, status, database_status, startup_time FROM v$instance;8. 查看数据库状态和角色SELECT name, open_mode, database_role, log_mode, protection_mode, switchover_status FROM v$database;9. 查看数据库是否启用归档模式ARCHIVE LOG LIST;也可以执行SELECT log_mode FROM v$database;10. 查看数据库数据文件总大小SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS datafile_gb FROM dba_data_files;该结果只统计永久数据文件不包括临时文件、控制文件、联机重做日志和归档日志。二、参数、控制文件和重做日志11. 查看数据库参数SHOW PARAMETER processes;也可以查询动态性能视图SELECT name, value, isdefault, issys_modifiable FROM v$parameter WHERE name processes;12. 在线修改数据库参数ALTER SYSTEM SET open_cursors 1000 SCOPEBOTH SID*;常用的SCOPE取值MEMORY只修改当前实例重启后失效SPFILE只修改参数文件重启后生效BOTH同时修改内存和 SPFILE13. 从 SPFILE 中删除参数ALTER SYSTEM RESET open_cursors SCOPESPFILE SID*;删除静态参数后通常需要重启实例。14. 根据 SPFILE 创建 PFILECREATE PFILE/tmp/initorcl.ora FROM SPFILE;该命令常用于备份当前实例参数或修改无法正常启动的 SPFILE。15. 根据 PFILE 创建 SPFILECREATE SPFILE FROM PFILE/tmp/initorcl.ora;RAC 环境应确认 SPFILE 是否位于 ASM 或共享存储中不能直接覆盖正在使用的错误位置。16. 查看控制文件位置SHOW PARAMETER control_files;也可以执行SELECT name FROM v$controlfile;17. 查看联机重做日志组和成员SELECT l.group#, l.thread#, l.sequence#, l.bytes / 1024 / 1024 AS size_mb, l.status, l.archived, f.member FROM v$log l JOIN v$logfile f ON l.group# f.group# ORDER BY l.thread#, l.group#, f.member;18. 手工切换联机重做日志ALTER SYSTEM SWITCH LOGFILE;该操作会结束当前日志组的写入并切换到下一个可用日志组。19. 手工执行检查点ALTER SYSTEM CHECKPOINT;检查点会推进控制文件和数据文件头中的检查点信息但不等于将所有脏块立即写完。20. 归档当前重做日志ALTER SYSTEM ARCHIVE LOG CURRENT;与SWITCH LOGFILE相比该命令会等待当前日志完成归档在备份和 Data Guard 运维中使用较多。三、会话、事务和锁排查21. 查看当前活动会话SELECT sid, serial#, username, status, machine, program, event, sql_id, last_call_et FROM v$session WHERE type USER AND status ACTIVE ORDER BY last_call_et DESC;22. 查看指定会话的详细信息SELECT sid, serial#, username, osuser, machine, program, module, action, status, event, wait_class, sql_id, prev_sql_id, logon_time FROM v$session WHERE sid 123;23. 按用户和程序统计连接数SELECT username, machine, program, status, COUNT(*) AS session_count FROM v$session WHERE type USER GROUP BY username, machine, program, status ORDER BY session_count DESC;该命令适合排查连接池连接泄漏、异常程序连接增长和数据库进程数不足。24. 查看长时间运行的操作SELECT sid, serial#, opname, target, sofar, totalwork, units, ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) AS progress_pct, elapsed_seconds, time_remaining FROM v$session_longops WHERE sofar totalwork ORDER BY start_time;并不是所有 SQL 都会出现在v$session_longops中通常大表扫描、备份恢复、统计信息收集等操作更容易被记录。25. 查看被阻塞的会话SELECT sid, serial#, username, blocking_session, event, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;26. 查看锁等待关系SELECT a.sid AS blocker_sid, b.sid AS waiter_sid, a.id1, a.id2, b.request, b.lmode FROM v$lock a JOIN v$lock b ON a.id1 b.id1 AND a.id2 b.id2 WHERE a.block 1 AND b.request 0;该查询可以快速找到持锁会话和等待会话之间的关系。27. 查看被锁定的对象SELECT s.sid, s.serial#, s.username, o.owner, o.object_name, o.object_type, l.locked_mode FROM v$locked_object l JOIN dba_objects o ON l.object_id o.object_id JOIN v$session s ON l.session_id s.sid ORDER BY s.sid;28. 强制终止会话ALTER SYSTEM KILL SESSION 123,4567 IMMEDIATE;其中123是 SID4567是 SERIAL#RAC 环境中可以指定实例ALTER SYSTEM KILL SESSION 123,4567,2 IMMEDIATE;29. 断开数据库会话ALTER SYSTEM DISCONNECT SESSION 123,4567 IMMEDIATE;如果希望等待当前事务完成后再断开ALTER SYSTEM DISCONNECT SESSION 123,4567 POST_TRANSACTION;30. 查看正在使用 UNDO 的事务SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec, ROUND( t.used_ublk * TO_NUMBER((SELECT value FROM v$parameter WHERE name db_block_size)) / 1024 / 1024, 2 ) AS undo_mb FROM v$transaction t JOIN v$session s ON t.ses_addr s.saddr ORDER BY t.used_ublk DESC;四、SQL 性能诊断31. 查看指定会话正在执行的 SQLSELECT s.sid, s.serial#, s.sql_id, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id AND s.sql_child_number q.child_number WHERE s.sid 123;32. 根据 SQL_ID 查看完整 SQL 文本SELECT sql_text FROM v$sqltext_with_newlines WHERE sql_id sql_id ORDER BY piece;33. 查看累计执行时间最高的 SQLSELECT * FROM ( SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds, ROUND( elapsed_time / NULLIF(executions, 0) / 1000000, 4 ) AS avg_elapsed_seconds, sql_text FROM v$sql WHERE executions 0 ORDER BY elapsed_time DESC ) WHERE ROWNUM 20;34. 查看 CPU 消耗最高的 SQLSELECT * FROM ( SELECT sql_id, executions, ROUND(cpu_time / 1000000, 2) AS cpu_seconds, ROUND( cpu_time / NULLIF(executions, 0) / 1000000, 4 ) AS avg_cpu_seconds, sql_text FROM v$sql WHERE executions 0 ORDER BY cpu_time DESC ) WHERE ROWNUM 20;35. 查看逻辑读最高的 SQLSELECT * FROM ( SELECT sql_id, executions, buffer_gets, ROUND( buffer_gets / NULLIF(executions, 0), 2 ) AS gets_per_exec, sql_text FROM v$sql WHERE executions 0 ORDER BY buffer_gets DESC ) WHERE ROWNUM 20;36. 查看物理读最高的 SQLSELECT * FROM ( SELECT sql_id, executions, disk_reads, ROUND( disk_reads / NULLIF(executions, 0), 2 ) AS reads_per_exec, sql_text FROM v$sql WHERE executions 0 ORDER BY disk_reads DESC ) WHERE ROWNUM 20;37. 使用 EXPLAIN PLAN 查看执行计划EXPLAIN PLAN FOR SELECT * FROM app_user.orders WHERE order_id 10001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);EXPLAIN PLAN展示的是优化器预估执行计划不一定等于 SQL 实际运行时使用的计划。38. 查看 SQL 实际执行计划SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id sql_id, cursor_child_no NULL, format ALLSTATS LAST PEEKED_BINDS OUTLINE ) );要查看准确的每一步实际行数SQL 执行时需要开启行源统计例如使用SELECT /* GATHER_PLAN_STATISTICS */ ...39. 查看 SQL 捕获到的绑定变量SELECT sql_id, child_number, name, position, datatype_string, value_string, last_captured FROM v$sql_bind_capture WHERE sql_id sql_id ORDER BY child_number, position;绑定变量不会在每次执行时都被捕获因此该视图中的值可能为空或不是最新值。40. 查看指定会话累计等待事件SELECT * FROM ( SELECT event, total_waits, time_waited, average_wait, max_wait FROM v$session_event WHERE sid 123 ORDER BY time_waited DESC ) WHERE ROWNUM 20;该结果是会话生命周期内的累计等待情况不只是当前 SQL 的等待数据。五、表空间和数据文件管理41. 查看永久表空间使用率并计算自动扩展上限SELECT d.tablespace_name, ROUND(d.bytes / 1024 / 1024 / 1024, 2) AS current_gb, ROUND( (d.bytes - NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2 ) AS used_gb, ROUND(NVL(f.bytes, 0) / 1024 / 1024 / 1024, 2) AS free_gb, ROUND( (d.bytes - NVL(f.bytes, 0)) / d.bytes * 100, 2 ) AS current_used_pct, ROUND(d.maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, ROUND( (d.maxbytes - d.bytes NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2 ) AS remaining_to_max_gb FROM ( SELECT tablespace_name, SUM(bytes) AS bytes, SUM( CASE WHEN autoextensible YES THEN maxbytes ELSE bytes END ) AS maxbytes FROM dba_data_files GROUP BY tablespace_name ) d LEFT JOIN ( SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name ) f ON d.tablespace_name f.tablespace_name ORDER BY current_used_pct DESC;42. 查看数据文件信息SELECT file_id, tablespace_name, file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_gb, autoextensible, ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, status FROM dba_data_files ORDER BY tablespace_name, file_id;43. 查看临时文件信息SELECT file_id, tablespace_name, file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_gb, autoextensible, ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, status FROM dba_temp_files ORDER BY tablespace_name, file_id;44. 为表空间增加数据文件ALTER TABLESPACE USERS ADD DATAFILE /u01/oradata/ORCL/users02.dbf SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 40G;执行前应确认文件系统剩余空间、Oracle 用户权限、数据库块大小和数据文件最大块数限制。45. 调整数据文件大小ALTER DATABASE DATAFILE /u01/oradata/ORCL/users02.dbf RESIZE 20G;缩小数据文件时如果目标位置之后仍存在已使用数据块会返回ORA-03297。46. 开启数据文件自动扩展ALTER DATABASE DATAFILE /u01/oradata/ORCL/users02.dbf AUTOEXTEND ON NEXT 1G MAXSIZE 40G;不建议无规划地设置为MAXSIZE UNLIMITED尤其是在文件系统空间有限的环境中。47. 将表空间设置为只读ALTER TABLESPACE ARCHIVE_DATA READ ONLY;恢复读写状态ALTER TABLESPACE ARCHIVE_DATA READ WRITE;48. 将表空间脱机或联机ALTER TABLESPACE APP_DATA OFFLINE IMMEDIATE;恢复联机ALTER TABLESPACE APP_DATA ONLINE;不要随意对SYSTEM、SYSAUX、当前 UNDO 和当前默认临时表空间执行脱机操作。49. 创建表空间CREATE TABLESPACE APP_DATA DATAFILE /u01/oradata/ORCL/app_data01.dbf SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 100G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;50. 删除表空间及其数据文件DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES;这是不可逆的高风险操作。执行前必须确认对象归属、备份状态和业务影响。六、段、对象、用户和权限51. 查看数据库中最大的段SELECT * FROM ( SELECT owner, segment_name, partition_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments ORDER BY bytes DESC ) WHERE ROWNUM 30;52. 查看各 Schema 占用空间SELECT owner, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments GROUP BY owner ORDER BY size_gb DESC;53. 查看指定表段的大小SELECT owner, segment_name, segment_type, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments WHERE owner APP_USER AND segment_name ORDERS GROUP BY owner, segment_name, segment_type;该查询只统计指定名称的段。如果表包含 LOB、分区和索引还需要分别统计相关段。54. 查看索引状态SELECT owner, index_name, table_name, index_type, status, visibility, tablespace_name, last_analyzed FROM dba_indexes WHERE owner APP_USER ORDER BY table_name, index_name;Oracle 11g 中如果视图不存在VISIBILITY列可以从查询中删除该列。55. 查看失效对象SELECT owner, object_type, object_name, status FROM dba_objects WHERE status INVALID ORDER BY owner, object_type, object_name;56. 编译指定 Schema 下的失效对象EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema APP_USER, compile_all FALSE );数据库升级或批量变更后也可以执行 Oracle 自带脚本?/rdbms/admin/utlrp.sql57. 创建数据库用户CREATE USER app_user IDENTIFIED BY StrongPassword_2026 DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp PROFILE default;在 Oracle 12c 及以上 CDB 环境中应先确认当前容器避免在 CDB 根容器中错误创建本地用户。58. 授予用户登录权限GRANT CREATE SESSION TO app_user;根据业务需要再授予对象创建权限不建议直接授予DBA角色。例如GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO app_user;59. 分配表空间配额ALTER USER app_user QUOTA 20G ON app_data;授予无限配额ALTER USER app_user QUOTA UNLIMITED ON app_data;60. 锁定、解锁或强制用户修改密码锁定用户ALTER USER app_user ACCOUNT LOCK;解锁用户ALTER USER app_user ACCOUNT UNLOCK;强制下次登录修改密码ALTER USER app_user PASSWORD EXPIRE;修改密码并解锁ALTER USER app_user IDENTIFIED BY NewPassword_2026 ACCOUNT UNLOCK;七、临时表空间、UNDO 和统计信息61. 查看临时表空间使用情况SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024 / 1024, 2) AS used_gb, ROUND(SUM(bytes_free) / 1024 / 1024 / 1024, 2) AS free_gb, ROUND( SUM(bytes_used) / NULLIF(SUM(bytes_used) SUM(bytes_free), 0) * 100, 2 ) AS used_pct FROM v$temp_space_header GROUP BY tablespace_name;62. 查看占用临时空间最多的会话SELECT s.sid, s.serial#, s.username, s.sql_id, u.tablespace, u.segtype, ROUND( u.blocks * t.block_size / 1024 / 1024, 2 ) AS temp_mb FROM v$tempseg_usage u JOIN v$session s ON u.session_addr s.saddr JOIN dba_tablespaces t ON u.tablespace t.tablespace_name ORDER BY temp_mb DESC;63. 查看临时段整体使用情况SELECT tablespace_name, current_users, used_blocks, free_blocks, ROUND(used_blocks * block_size / 1024 / 1024, 2) AS used_mb, ROUND(free_blocks * block_size / 1024 / 1024, 2) AS free_mb FROM v$sort_segment;64. 增加临时文件ALTER TABLESPACE TEMP ADD TEMPFILE /u01/oradata/ORCL/temp02.dbf SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;65. 调整临时文件大小ALTER DATABASE TEMPFILE /u01/oradata/ORCL/temp02.dbf RESIZE 30G;缩小临时文件前应确认当前临时段高水位和正在使用临时空间的会话。66. 查看 UNDO 区间状态SELECT tablespace_name, status, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_undo_extents GROUP BY tablespace_name, status ORDER BY tablespace_name, status;UNDO 区间常见状态ACTIVE正在被事务使用UNEXPIRED事务已结束但仍在保留期内EXPIRED可以被重新使用67. 查看占用 UNDO 最多的活动事务SELECT s.sid, s.serial#, s.username, s.sql_id, t.start_time, t.used_ublk, t.used_urec, ROUND( t.used_ublk * TO_NUMBER((SELECT value FROM v$parameter WHERE name db_block_size)) / 1024 / 1024, 2 ) AS undo_mb FROM v$transaction t JOIN v$session s ON t.ses_addr s.saddr ORDER BY undo_mb DESC;68. 查看和修改 UNDO_RETENTIONSHOW PARAMETER undo_retention;修改保留时间为 3600 秒ALTER SYSTEM SET undo_retention 3600 SCOPEBOTH;UNDO_RETENTION并不意味着所有 UNDO 一定会保留指定时间。当 UNDO 空间不足且未启用RETENTION GUARANTEE时未过期区间仍可能被覆盖。69. 收集 Schema 统计信息BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname APP_USER, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree DBMS_STATS.AUTO_DEGREE, cascade TRUE ); END; /70. 收集指定表的统计信息BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname APP_USER, tabname ORDERS, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree DBMS_STATS.AUTO_DEGREE, cascade TRUE, no_invalidate FALSE ); END; /生产环境收集大表统计信息时需要评估并行度、采样比例、执行窗口和执行计划变化风险。八、RMAN 备份和恢复以下命令在 RMAN 中执行。71. 登录 RMANrman target /远程连接示例rman target sysorcl72. 查看 RMAN 当前配置SHOW ALL;重点检查保留策略控制文件自动备份备份设备类型备份并行度归档日志删除策略73. 查看数据库文件结构REPORT SCHEMA;该命令可以查看数据文件编号、数据文件大小和所属表空间恢复单个数据文件时经常使用。74. 查看备份摘要LIST BACKUP SUMMARY;查看更详细的数据库备份LIST BACKUP OF DATABASE;75. 校验备份记录并清理失效记录CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP;EXPIRED表示 RMAN 仓库中有记录但实际备份文件无法找到不等于备份已经超过保留策略。76. 备份数据库和归档日志BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;是否使用压缩备份集应根据 CPU 资源、备份窗口和存储空间综合判断。77. 备份归档日志并删除已备份文件BACKUP ARCHIVELOG ALL DELETE INPUT;Data Guard 环境中必须结合归档日志删除策略避免归档日志尚未传输或应用就被删除。78. 删除超过保留策略的备份REPORT OBSOLETE; DELETE NOPROMPT OBSOLETE;执行删除前建议先运行REPORT OBSOLETE检查即将删除的备份范围。79. 校验数据库和归档日志BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;也可以验证现有备份是否能够被读取RESTORE DATABASE VALIDATE;VALIDATE不会真正恢复数据文件但可以检查备份片可读性和部分物理、逻辑损坏。80. 恢复单个数据文件假设需要恢复 7 号数据文件RUN { SQL ALTER DATABASE DATAFILE 7 OFFLINE; RESTORE DATAFILE 7; RECOVER DATAFILE 7; SQL ALTER DATABASE DATAFILE 7 ONLINE; }SYSTEM、当前 UNDO、控制文件和数据库非归档模式下的数据文件恢复处理流程可能不同不能直接套用该命令。九、Data Guard、RAC 和 PDB81. 查看 Data Guard 数据库角色和保护模式SELECT name, database_role, open_mode, protection_mode, protection_level, switchover_status FROM v$database;82. 查看归档目标状态SELECT dest_id, status, destination, target, archiver, process, transmit_mode, error FROM v$archive_dest_status WHERE status INACTIVE ORDER BY dest_id;如果ERROR列有内容应进一步检查网络、服务名、归档路径、密码文件和备库状态。83. 查看 Data Guard 日志缺口SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;该视图通常一次只显示当前需要处理的一个日志缺口修复后可能还会显示后续缺口。84. 查看备库日志应用进程SELECT process, status, thread#, sequence#, block#, blocks FROM v$managed_standby ORDER BY process;常见进程包括RFS接收主库日志MRP0日志应用协调进程ARCH归档进程85. 启动备库实时日志应用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;较新版本中即使不显式指定USING CURRENT LOGFILE也可能默认使用实时应用但在不同版本环境中应以实际行为为准。86. 停止备库日志应用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;进行备库维护、切换或恢复操作前经常需要先停止 MRP。87. 查看备库最后接收和应用的日志序列SELECT thread#, MAX(sequence#) AS last_received, MAX( CASE WHEN applied YES THEN sequence# END ) AS last_applied FROM v$archived_log GROUP BY thread# ORDER BY thread#;仅比较日志序列号不能完整反映延迟时间还应结合归档日志时间、v$dataguard_stats和业务恢复点综合判断。88. 查看 RAC 各实例状态SELECT inst_id, instance_name, host_name, version, status, database_status, startup_time FROM gv$instance ORDER BY inst_id;89. 统计 RAC 各实例会话数SELECT inst_id, username, status, COUNT(*) AS session_count FROM gv$session WHERE type USER GROUP BY inst_id, username, status ORDER BY inst_id, session_count DESC;该命令可以检查业务连接是否均衡分布在 RAC 各节点。90. 查看并打开 PDBOracle 12c 及以上版本可以执行SHOW PDBS;打开全部 PDBALTER PLUGGABLE DATABASE ALL OPEN;保存 PDB 打开状态ALTER PLUGGABLE DATABASE ALL SAVE STATE;查看当前容器SHOW CON_NAME;十、Scheduler、Data Pump、监听和操作系统诊断91. 查看 Scheduler 作业状态SELECT owner, job_name, enabled, state, last_start_date, last_run_duration, next_run_date, failure_count FROM dba_scheduler_jobs ORDER BY owner, job_name;92. 手工运行 Scheduler 作业BEGIN DBMS_SCHEDULER.RUN_JOB( job_name APP_USER.JOB_SYNC_DATA, use_current_session FALSE ); END; /设置为FALSE时作业在后台运行设置为TRUE时当前会话会等待作业执行完成。93. 强制停止 Scheduler 作业BEGIN DBMS_SCHEDULER.STOP_JOB( job_name APP_USER.JOB_SYNC_DATA, force TRUE ); END; /强制停止作业可能导致业务事务中断应先确认作业当前正在执行的内容。94. 创建 Data Pump 目录首先在操作系统中创建目录mkdir -p /backup/dump chown oracle:oinstall /backup/dump chmod 750 /backup/dump然后在数据库中创建目录对象CREATE OR REPLACE DIRECTORY DUMP_DIR AS /backup/dump; GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO app_user;Oracle 数据库不会自动创建操作系统目录。95. 使用 expdp 导出 Schemaexpdp systemorcl \ schemasAPP_USER \ directoryDUMP_DIR \ dumpfileapp_user_%U.dmp \ logfileapp_user_exp.log \ parallel4 \ filesize20G \ compressionall多文件并行导出时DUMPFILE中应包含%U。96. 使用 impdp 导入并映射 Schemaimpdp systemorcl \ directoryDUMP_DIR \ dumpfileapp_user_%U.dmp \ logfileapp_user_imp.log \ remap_schemaAPP_USER:APP_USER_TEST \ remap_tablespaceAPP_DATA:APP_DATA_TEST \ parallel4如果目标用户不存在应根据导出内容和导入方式确认是否需要提前创建用户、表空间和配额。97. 查看监听状态lsnrctl status查看监听支持的服务lsnrctl services启动和停止监听lsnrctl start lsnrctl stop98. 测试 Oracle 网络服务名tnsping orcltnsping只能验证客户端能否解析服务名并访问监听地址不能证明数据库用户一定可以成功登录。真正测试数据库连接应使用sqlplus app_userorcl99. 使用 ADRCI 查看告警日志查看诊断目录adrci execshow homes查看最近 100 行告警日志adrci execset homepath diag/rdbms/orcl/orcl; show alert -tail 100 -term持续跟踪告警日志adrci execset homepath diag/rdbms/orcl/orcl; show alert -tail -f其中homepath需要根据show homes的实际结果修改。100. 在操作系统中查找 Oracle 实例和高 CPU 进程查看服务器上的 Oracle 实例ps -ef | grep [o]ra_pmon查看 CPU 使用率最高的 Oracle 进程ps -eo pid,ppid,%cpu,%mem,etime,args \ --sort-%cpu | grep [o]ra_ | head -20拿到操作系统进程号后可以在数据库中反查会话SELECT p.spid, s.sid, s.serial#, s.username, s.status, s.event, s.sql_id, s.machine, s.program FROM v$process p JOIN v$session s ON p.addr s.paddr WHERE p.spid os_pid;写在最后Oracle DBA 真正需要掌握的并不是“记住多少条命令”而是知道每条命令应该在什么场景下执行、查询结果说明了什么、下一步应该验证什么。看到 CPU 高不能只查高 CPU SQL还要判断是 SQL 计算量大、解析频繁、并行失控还是大量会话被唤醒后争抢 CPU看到表空间使用率高也不能立即增加数据文件而应先区分真实业务增长、异常段膨胀、回收站占用、LOB 增长还是数据文件自动扩展配置不合理。命令只是入口判断路径才是 DBA 的核心能力。更多 Oracle、MySQL、PostgreSQL、SQL Server 数据库实战内容可以访问 DBA 学习平台ora100.com
Oracle DBA 应该掌握的 100 条命令（建议收藏）
前言Oracle DBA 的日常工作并不是简单地执行几条 SQL。数据库启动失败、会话阻塞、表空间告警、UNDO 异常、SQL 性能下降、归档堆积、备份失效、Data Guard 延迟这些问题最终都需要通过命令和动态性能视图定位。本文整理了 Oracle DBA 工作中使用频率较高的 100 条命令覆盖实例管理、参数配置、会话排查、锁分析、SQL 性能、表空间、用户权限、UNDO、临时表空间、RMAN、Data Guard、RAC、Data Pump 和操作系统诊断等场景。文中的用户名、路径、SID、SQL_ID、表空间名称和数据文件编号均为示例执行时需要根据实际环境替换。涉及删除、恢复、强制终止会话等操作时应先确认影响范围避免直接在生产环境中照搬执行。一、实例与数据库管理1. 以 SYSDBA 身份登录数据库sqlplus / as sysdba远程登录可以使用sqlplus sysorcl as sysdba2. 启动数据库STARTUP;该命令依次完成实例启动、控制文件加载和数据库打开。3. 启动数据库到 MOUNT 状态STARTUP MOUNT;MOUNT 状态通常用于数据库恢复、启用归档模式、修改数据文件路径等操作。4. 启动数据库到 NOMOUNT 状态STARTUP NOMOUNT;NOMOUNT 状态通常用于创建数据库、重建控制文件或恢复控制文件。5. 将数据库从 MOUNT 状态打开ALTER DATABASE OPEN;如果需要以只读方式打开ALTER DATABASE OPEN READ ONLY;6. 正常关闭数据库SHUTDOWN IMMEDIATE;生产环境通常优先使用IMMEDIATE它会回滚未提交事务并断开用户连接不需要等待所有会话主动退出。7. 查看实例状态SELECT instance_name, host_name, version, status, database_status, startup_time FROM v$instance;8. 查看数据库状态和角色SELECT name, open_mode, database_role, log_mode, protection_mode, switchover_status FROM v$database;9. 查看数据库是否启用归档模式ARCHIVE LOG LIST;也可以执行SELECT log_mode FROM v$database;10. 查看数据库数据文件总大小SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS datafile_gb FROM dba_data_files;该结果只统计永久数据文件不包括临时文件、控制文件、联机重做日志和归档日志。二、参数、控制文件和重做日志11. 查看数据库参数SHOW PARAMETER processes;也可以查询动态性能视图SELECT name, value, isdefault, issys_modifiable FROM v$parameter WHERE name processes;12. 在线修改数据库参数ALTER SYSTEM SET open_cursors 1000 SCOPEBOTH SID*;常用的SCOPE取值MEMORY只修改当前实例重启后失效SPFILE只修改参数文件重启后生效BOTH同时修改内存和 SPFILE13. 从 SPFILE 中删除参数ALTER SYSTEM RESET open_cursors SCOPESPFILE SID*;删除静态参数后通常需要重启实例。14. 根据 SPFILE 创建 PFILECREATE PFILE/tmp/initorcl.ora FROM SPFILE;该命令常用于备份当前实例参数或修改无法正常启动的 SPFILE。15. 根据 PFILE 创建 SPFILECREATE SPFILE FROM PFILE/tmp/initorcl.ora;RAC 环境应确认 SPFILE 是否位于 ASM 或共享存储中不能直接覆盖正在使用的错误位置。16. 查看控制文件位置SHOW PARAMETER control_files;也可以执行SELECT name FROM v$controlfile;17. 查看联机重做日志组和成员SELECT l.group#, l.thread#, l.sequence#, l.bytes / 1024 / 1024 AS size_mb, l.status, l.archived, f.member FROM v$log l JOIN v$logfile f ON l.group# f.group# ORDER BY l.thread#, l.group#, f.member;18. 手工切换联机重做日志ALTER SYSTEM SWITCH LOGFILE;该操作会结束当前日志组的写入并切换到下一个可用日志组。19. 手工执行检查点ALTER SYSTEM CHECKPOINT;检查点会推进控制文件和数据文件头中的检查点信息但不等于将所有脏块立即写完。20. 归档当前重做日志ALTER SYSTEM ARCHIVE LOG CURRENT;与SWITCH LOGFILE相比该命令会等待当前日志完成归档在备份和 Data Guard 运维中使用较多。三、会话、事务和锁排查21. 查看当前活动会话SELECT sid, serial#, username, status, machine, program, event, sql_id, last_call_et FROM v$session WHERE type USER AND status ACTIVE ORDER BY last_call_et DESC;22. 查看指定会话的详细信息SELECT sid, serial#, username, osuser, machine, program, module, action, status, event, wait_class, sql_id, prev_sql_id, logon_time FROM v$session WHERE sid 123;23. 按用户和程序统计连接数SELECT username, machine, program, status, COUNT(*) AS session_count FROM v$session WHERE type USER GROUP BY username, machine, program, status ORDER BY session_count DESC;该命令适合排查连接池连接泄漏、异常程序连接增长和数据库进程数不足。24. 查看长时间运行的操作SELECT sid, serial#, opname, target, sofar, totalwork, units, ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) AS progress_pct, elapsed_seconds, time_remaining FROM v$session_longops WHERE sofar totalwork ORDER BY start_time;并不是所有 SQL 都会出现在v$session_longops中通常大表扫描、备份恢复、统计信息收集等操作更容易被记录。25. 查看被阻塞的会话SELECT sid, serial#, username, blocking_session, event, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;26. 查看锁等待关系SELECT a.sid AS blocker_sid, b.sid AS waiter_sid, a.id1, a.id2, b.request, b.lmode FROM v$lock a JOIN v$lock b ON a.id1 b.id1 AND a.id2 b.id2 WHERE a.block 1 AND b.request 0;该查询可以快速找到持锁会话和等待会话之间的关系。27. 查看被锁定的对象SELECT s.sid, s.serial#, s.username, o.owner, o.object_name, o.object_type, l.locked_mode FROM v$locked_object l JOIN dba_objects o ON l.object_id o.object_id JOIN v$session s ON l.session_id s.sid ORDER BY s.sid;28. 强制终止会话ALTER SYSTEM KILL SESSION 123,4567 IMMEDIATE;其中123是 SID4567是 SERIAL#RAC 环境中可以指定实例ALTER SYSTEM KILL SESSION 123,4567,2 IMMEDIATE;29. 断开数据库会话ALTER SYSTEM DISCONNECT SESSION 123,4567 IMMEDIATE;如果希望等待当前事务完成后再断开ALTER SYSTEM DISCONNECT SESSION 123,4567 POST_TRANSACTION;30. 查看正在使用 UNDO 的事务SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec, ROUND( t.used_ublk * TO_NUMBER((SELECT value FROM v$parameter WHERE name db_block_size)) / 1024 / 1024, 2 ) AS undo_mb FROM v$transaction t JOIN v$session s ON t.ses_addr s.saddr ORDER BY t.used_ublk DESC;四、SQL 性能诊断31. 查看指定会话正在执行的 SQLSELECT s.sid, s.serial#, s.sql_id, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id AND s.sql_child_number q.child_number WHERE s.sid 123;32. 根据 SQL_ID 查看完整 SQL 文本SELECT sql_text FROM v$sqltext_with_newlines WHERE sql_id sql_id ORDER BY piece;33. 查看累计执行时间最高的 SQLSELECT * FROM ( SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds, ROUND( elapsed_time / NULLIF(executions, 0) / 1000000, 4 ) AS avg_elapsed_seconds, sql_text FROM v$sql WHERE executions 0 ORDER BY elapsed_time DESC ) WHERE ROWNUM 20;34. 查看 CPU 消耗最高的 SQLSELECT * FROM ( SELECT sql_id, executions, ROUND(cpu_time / 1000000, 2) AS cpu_seconds, ROUND( cpu_time / NULLIF(executions, 0) / 1000000, 4 ) AS avg_cpu_seconds, sql_text FROM v$sql WHERE executions 0 ORDER BY cpu_time DESC ) WHERE ROWNUM 20;35. 查看逻辑读最高的 SQLSELECT * FROM ( SELECT sql_id, executions, buffer_gets, ROUND( buffer_gets / NULLIF(executions, 0), 2 ) AS gets_per_exec, sql_text FROM v$sql WHERE executions 0 ORDER BY buffer_gets DESC ) WHERE ROWNUM 20;36. 查看物理读最高的 SQLSELECT * FROM ( SELECT sql_id, executions, disk_reads, ROUND( disk_reads / NULLIF(executions, 0), 2 ) AS reads_per_exec, sql_text FROM v$sql WHERE executions 0 ORDER BY disk_reads DESC ) WHERE ROWNUM 20;37. 使用 EXPLAIN PLAN 查看执行计划EXPLAIN PLAN FOR SELECT * FROM app_user.orders WHERE order_id 10001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);EXPLAIN PLAN展示的是优化器预估执行计划不一定等于 SQL 实际运行时使用的计划。38. 查看 SQL 实际执行计划SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id sql_id, cursor_child_no NULL, format ALLSTATS LAST PEEKED_BINDS OUTLINE ) );要查看准确的每一步实际行数SQL 执行时需要开启行源统计例如使用SELECT /* GATHER_PLAN_STATISTICS */ ...39. 查看 SQL 捕获到的绑定变量SELECT sql_id, child_number, name, position, datatype_string, value_string, last_captured FROM v$sql_bind_capture WHERE sql_id sql_id ORDER BY child_number, position;绑定变量不会在每次执行时都被捕获因此该视图中的值可能为空或不是最新值。40. 查看指定会话累计等待事件SELECT * FROM ( SELECT event, total_waits, time_waited, average_wait, max_wait FROM v$session_event WHERE sid 123 ORDER BY time_waited DESC ) WHERE ROWNUM 20;该结果是会话生命周期内的累计等待情况不只是当前 SQL 的等待数据。五、表空间和数据文件管理41. 查看永久表空间使用率并计算自动扩展上限SELECT d.tablespace_name, ROUND(d.bytes / 1024 / 1024 / 1024, 2) AS current_gb, ROUND( (d.bytes - NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2 ) AS used_gb, ROUND(NVL(f.bytes, 0) / 1024 / 1024 / 1024, 2) AS free_gb, ROUND( (d.bytes - NVL(f.bytes, 0)) / d.bytes * 100, 2 ) AS current_used_pct, ROUND(d.maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, ROUND( (d.maxbytes - d.bytes NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2 ) AS remaining_to_max_gb FROM ( SELECT tablespace_name, SUM(bytes) AS bytes, SUM( CASE WHEN autoextensible YES THEN maxbytes ELSE bytes END ) AS maxbytes FROM dba_data_files GROUP BY tablespace_name ) d LEFT JOIN ( SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name ) f ON d.tablespace_name f.tablespace_name ORDER BY current_used_pct DESC;42. 查看数据文件信息SELECT file_id, tablespace_name, file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_gb, autoextensible, ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, status FROM dba_data_files ORDER BY tablespace_name, file_id;43. 查看临时文件信息SELECT file_id, tablespace_name, file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_gb, autoextensible, ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_gb, status FROM dba_temp_files ORDER BY tablespace_name, file_id;44. 为表空间增加数据文件ALTER TABLESPACE USERS ADD DATAFILE /u01/oradata/ORCL/users02.dbf SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 40G;执行前应确认文件系统剩余空间、Oracle 用户权限、数据库块大小和数据文件最大块数限制。45. 调整数据文件大小ALTER DATABASE DATAFILE /u01/oradata/ORCL/users02.dbf RESIZE 20G;缩小数据文件时如果目标位置之后仍存在已使用数据块会返回ORA-03297。46. 开启数据文件自动扩展ALTER DATABASE DATAFILE /u01/oradata/ORCL/users02.dbf AUTOEXTEND ON NEXT 1G MAXSIZE 40G;不建议无规划地设置为MAXSIZE UNLIMITED尤其是在文件系统空间有限的环境中。47. 将表空间设置为只读ALTER TABLESPACE ARCHIVE_DATA READ ONLY;恢复读写状态ALTER TABLESPACE ARCHIVE_DATA READ WRITE;48. 将表空间脱机或联机ALTER TABLESPACE APP_DATA OFFLINE IMMEDIATE;恢复联机ALTER TABLESPACE APP_DATA ONLINE;不要随意对SYSTEM、SYSAUX、当前 UNDO 和当前默认临时表空间执行脱机操作。49. 创建表空间CREATE TABLESPACE APP_DATA DATAFILE /u01/oradata/ORCL/app_data01.dbf SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 100G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;50. 删除表空间及其数据文件DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES;这是不可逆的高风险操作。执行前必须确认对象归属、备份状态和业务影响。六、段、对象、用户和权限51. 查看数据库中最大的段SELECT * FROM ( SELECT owner, segment_name, partition_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments ORDER BY bytes DESC ) WHERE ROWNUM 30;52. 查看各 Schema 占用空间SELECT owner, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments GROUP BY owner ORDER BY size_gb DESC;53. 查看指定表段的大小SELECT owner, segment_name, segment_type, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_segments WHERE owner APP_USER AND segment_name ORDERS GROUP BY owner, segment_name, segment_type;该查询只统计指定名称的段。如果表包含 LOB、分区和索引还需要分别统计相关段。54. 查看索引状态SELECT owner, index_name, table_name, index_type, status, visibility, tablespace_name, last_analyzed FROM dba_indexes WHERE owner APP_USER ORDER BY table_name, index_name;Oracle 11g 中如果视图不存在VISIBILITY列可以从查询中删除该列。55. 查看失效对象SELECT owner, object_type, object_name, status FROM dba_objects WHERE status INVALID ORDER BY owner, object_type, object_name;56. 编译指定 Schema 下的失效对象EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema APP_USER, compile_all FALSE );数据库升级或批量变更后也可以执行 Oracle 自带脚本?/rdbms/admin/utlrp.sql57. 创建数据库用户CREATE USER app_user IDENTIFIED BY StrongPassword_2026 DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp PROFILE default;在 Oracle 12c 及以上 CDB 环境中应先确认当前容器避免在 CDB 根容器中错误创建本地用户。58. 授予用户登录权限GRANT CREATE SESSION TO app_user;根据业务需要再授予对象创建权限不建议直接授予DBA角色。例如GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO app_user;59. 分配表空间配额ALTER USER app_user QUOTA 20G ON app_data;授予无限配额ALTER USER app_user QUOTA UNLIMITED ON app_data;60. 锁定、解锁或强制用户修改密码锁定用户ALTER USER app_user ACCOUNT LOCK;解锁用户ALTER USER app_user ACCOUNT UNLOCK;强制下次登录修改密码ALTER USER app_user PASSWORD EXPIRE;修改密码并解锁ALTER USER app_user IDENTIFIED BY NewPassword_2026 ACCOUNT UNLOCK;七、临时表空间、UNDO 和统计信息61. 查看临时表空间使用情况SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024 / 1024, 2) AS used_gb, ROUND(SUM(bytes_free) / 1024 / 1024 / 1024, 2) AS free_gb, ROUND( SUM(bytes_used) / NULLIF(SUM(bytes_used) SUM(bytes_free), 0) * 100, 2 ) AS used_pct FROM v$temp_space_header GROUP BY tablespace_name;62. 查看占用临时空间最多的会话SELECT s.sid, s.serial#, s.username, s.sql_id, u.tablespace, u.segtype, ROUND( u.blocks * t.block_size / 1024 / 1024, 2 ) AS temp_mb FROM v$tempseg_usage u JOIN v$session s ON u.session_addr s.saddr JOIN dba_tablespaces t ON u.tablespace t.tablespace_name ORDER BY temp_mb DESC;63. 查看临时段整体使用情况SELECT tablespace_name, current_users, used_blocks, free_blocks, ROUND(used_blocks * block_size / 1024 / 1024, 2) AS used_mb, ROUND(free_blocks * block_size / 1024 / 1024, 2) AS free_mb FROM v$sort_segment;64. 增加临时文件ALTER TABLESPACE TEMP ADD TEMPFILE /u01/oradata/ORCL/temp02.dbf SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;65. 调整临时文件大小ALTER DATABASE TEMPFILE /u01/oradata/ORCL/temp02.dbf RESIZE 30G;缩小临时文件前应确认当前临时段高水位和正在使用临时空间的会话。66. 查看 UNDO 区间状态SELECT tablespace_name, status, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb FROM dba_undo_extents GROUP BY tablespace_name, status ORDER BY tablespace_name, status;UNDO 区间常见状态ACTIVE正在被事务使用UNEXPIRED事务已结束但仍在保留期内EXPIRED可以被重新使用67. 查看占用 UNDO 最多的活动事务SELECT s.sid, s.serial#, s.username, s.sql_id, t.start_time, t.used_ublk, t.used_urec, ROUND( t.used_ublk * TO_NUMBER((SELECT value FROM v$parameter WHERE name db_block_size)) / 1024 / 1024, 2 ) AS undo_mb FROM v$transaction t JOIN v$session s ON t.ses_addr s.saddr ORDER BY undo_mb DESC;68. 查看和修改 UNDO_RETENTIONSHOW PARAMETER undo_retention;修改保留时间为 3600 秒ALTER SYSTEM SET undo_retention 3600 SCOPEBOTH;UNDO_RETENTION并不意味着所有 UNDO 一定会保留指定时间。当 UNDO 空间不足且未启用RETENTION GUARANTEE时未过期区间仍可能被覆盖。69. 收集 Schema 统计信息BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname APP_USER, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree DBMS_STATS.AUTO_DEGREE, cascade TRUE ); END; /70. 收集指定表的统计信息BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname APP_USER, tabname ORDERS, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree DBMS_STATS.AUTO_DEGREE, cascade TRUE, no_invalidate FALSE ); END; /生产环境收集大表统计信息时需要评估并行度、采样比例、执行窗口和执行计划变化风险。八、RMAN 备份和恢复以下命令在 RMAN 中执行。71. 登录 RMANrman target /远程连接示例rman target sysorcl72. 查看 RMAN 当前配置SHOW ALL;重点检查保留策略控制文件自动备份备份设备类型备份并行度归档日志删除策略73. 查看数据库文件结构REPORT SCHEMA;该命令可以查看数据文件编号、数据文件大小和所属表空间恢复单个数据文件时经常使用。74. 查看备份摘要LIST BACKUP SUMMARY;查看更详细的数据库备份LIST BACKUP OF DATABASE;75. 校验备份记录并清理失效记录CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP;EXPIRED表示 RMAN 仓库中有记录但实际备份文件无法找到不等于备份已经超过保留策略。76. 备份数据库和归档日志BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;是否使用压缩备份集应根据 CPU 资源、备份窗口和存储空间综合判断。77. 备份归档日志并删除已备份文件BACKUP ARCHIVELOG ALL DELETE INPUT;Data Guard 环境中必须结合归档日志删除策略避免归档日志尚未传输或应用就被删除。78. 删除超过保留策略的备份REPORT OBSOLETE; DELETE NOPROMPT OBSOLETE;执行删除前建议先运行REPORT OBSOLETE检查即将删除的备份范围。79. 校验数据库和归档日志BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;也可以验证现有备份是否能够被读取RESTORE DATABASE VALIDATE;VALIDATE不会真正恢复数据文件但可以检查备份片可读性和部分物理、逻辑损坏。80. 恢复单个数据文件假设需要恢复 7 号数据文件RUN { SQL ALTER DATABASE DATAFILE 7 OFFLINE; RESTORE DATAFILE 7; RECOVER DATAFILE 7; SQL ALTER DATABASE DATAFILE 7 ONLINE; }SYSTEM、当前 UNDO、控制文件和数据库非归档模式下的数据文件恢复处理流程可能不同不能直接套用该命令。九、Data Guard、RAC 和 PDB81. 查看 Data Guard 数据库角色和保护模式SELECT name, database_role, open_mode, protection_mode, protection_level, switchover_status FROM v$database;82. 查看归档目标状态SELECT dest_id, status, destination, target, archiver, process, transmit_mode, error FROM v$archive_dest_status WHERE status INACTIVE ORDER BY dest_id;如果ERROR列有内容应进一步检查网络、服务名、归档路径、密码文件和备库状态。83. 查看 Data Guard 日志缺口SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;该视图通常一次只显示当前需要处理的一个日志缺口修复后可能还会显示后续缺口。84. 查看备库日志应用进程SELECT process, status, thread#, sequence#, block#, blocks FROM v$managed_standby ORDER BY process;常见进程包括RFS接收主库日志MRP0日志应用协调进程ARCH归档进程85. 启动备库实时日志应用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;较新版本中即使不显式指定USING CURRENT LOGFILE也可能默认使用实时应用但在不同版本环境中应以实际行为为准。86. 停止备库日志应用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;进行备库维护、切换或恢复操作前经常需要先停止 MRP。87. 查看备库最后接收和应用的日志序列SELECT thread#, MAX(sequence#) AS last_received, MAX( CASE WHEN applied YES THEN sequence# END ) AS last_applied FROM v$archived_log GROUP BY thread# ORDER BY thread#;仅比较日志序列号不能完整反映延迟时间还应结合归档日志时间、v$dataguard_stats和业务恢复点综合判断。88. 查看 RAC 各实例状态SELECT inst_id, instance_name, host_name, version, status, database_status, startup_time FROM gv$instance ORDER BY inst_id;89. 统计 RAC 各实例会话数SELECT inst_id, username, status, COUNT(*) AS session_count FROM gv$session WHERE type USER GROUP BY inst_id, username, status ORDER BY inst_id, session_count DESC;该命令可以检查业务连接是否均衡分布在 RAC 各节点。90. 查看并打开 PDBOracle 12c 及以上版本可以执行SHOW PDBS;打开全部 PDBALTER PLUGGABLE DATABASE ALL OPEN;保存 PDB 打开状态ALTER PLUGGABLE DATABASE ALL SAVE STATE;查看当前容器SHOW CON_NAME;十、Scheduler、Data Pump、监听和操作系统诊断91. 查看 Scheduler 作业状态SELECT owner, job_name, enabled, state, last_start_date, last_run_duration, next_run_date, failure_count FROM dba_scheduler_jobs ORDER BY owner, job_name;92. 手工运行 Scheduler 作业BEGIN DBMS_SCHEDULER.RUN_JOB( job_name APP_USER.JOB_SYNC_DATA, use_current_session FALSE ); END; /设置为FALSE时作业在后台运行设置为TRUE时当前会话会等待作业执行完成。93. 强制停止 Scheduler 作业BEGIN DBMS_SCHEDULER.STOP_JOB( job_name APP_USER.JOB_SYNC_DATA, force TRUE ); END; /强制停止作业可能导致业务事务中断应先确认作业当前正在执行的内容。94. 创建 Data Pump 目录首先在操作系统中创建目录mkdir -p /backup/dump chown oracle:oinstall /backup/dump chmod 750 /backup/dump然后在数据库中创建目录对象CREATE OR REPLACE DIRECTORY DUMP_DIR AS /backup/dump; GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO app_user;Oracle 数据库不会自动创建操作系统目录。95. 使用 expdp 导出 Schemaexpdp systemorcl \ schemasAPP_USER \ directoryDUMP_DIR \ dumpfileapp_user_%U.dmp \ logfileapp_user_exp.log \ parallel4 \ filesize20G \ compressionall多文件并行导出时DUMPFILE中应包含%U。96. 使用 impdp 导入并映射 Schemaimpdp systemorcl \ directoryDUMP_DIR \ dumpfileapp_user_%U.dmp \ logfileapp_user_imp.log \ remap_schemaAPP_USER:APP_USER_TEST \ remap_tablespaceAPP_DATA:APP_DATA_TEST \ parallel4如果目标用户不存在应根据导出内容和导入方式确认是否需要提前创建用户、表空间和配额。97. 查看监听状态lsnrctl status查看监听支持的服务lsnrctl services启动和停止监听lsnrctl start lsnrctl stop98. 测试 Oracle 网络服务名tnsping orcltnsping只能验证客户端能否解析服务名并访问监听地址不能证明数据库用户一定可以成功登录。真正测试数据库连接应使用sqlplus app_userorcl99. 使用 ADRCI 查看告警日志查看诊断目录adrci execshow homes查看最近 100 行告警日志adrci execset homepath diag/rdbms/orcl/orcl; show alert -tail 100 -term持续跟踪告警日志adrci execset homepath diag/rdbms/orcl/orcl; show alert -tail -f其中homepath需要根据show homes的实际结果修改。100. 在操作系统中查找 Oracle 实例和高 CPU 进程查看服务器上的 Oracle 实例ps -ef | grep [o]ra_pmon查看 CPU 使用率最高的 Oracle 进程ps -eo pid,ppid,%cpu,%mem,etime,args \ --sort-%cpu | grep [o]ra_ | head -20拿到操作系统进程号后可以在数据库中反查会话SELECT p.spid, s.sid, s.serial#, s.username, s.status, s.event, s.sql_id, s.machine, s.program FROM v$process p JOIN v$session s ON p.addr s.paddr WHERE p.spid os_pid;写在最后Oracle DBA 真正需要掌握的并不是“记住多少条命令”而是知道每条命令应该在什么场景下执行、查询结果说明了什么、下一步应该验证什么。看到 CPU 高不能只查高 CPU SQL还要判断是 SQL 计算量大、解析频繁、并行失控还是大量会话被唤醒后争抢 CPU看到表空间使用率高也不能立即增加数据文件而应先区分真实业务增长、异常段膨胀、回收站占用、LOB 增长还是数据文件自动扩展配置不合理。命令只是入口判断路径才是 DBA 的核心能力。更多 Oracle、MySQL、PostgreSQL、SQL Server 数据库实战内容可以访问 DBA 学习平台ora100.com
