Oracle 详细命令手册
大约 8 分钟
Oracle 详细命令手册
配套知识库：Oracle 知识库总览 ｜ 适用：Oracle 11g/12c/19c 说明：本手册覆盖日常运维高频命令：实例管理、监听、SQL 查询、表空间、用户权限、RMAN 备份恢复、数据泵、AWR 调优、RAC/DG/OGG 管理。
一、连接与实例管理
# 连接（先设置环境变量）
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
# sqlplus 连接方式
sqlplus / as sysdba # 本地系统管理员
sqlplus sys/密码 as sysdba # 密码登录
sqlplus scott/tiger@orcl # 通过网络服务名
sqlplus scott/tiger@//10.0.0.10:1521/orclpdb1 # 简化连接串
# 启动/关闭
sqlplus / as sysdba
SQL> startup # 启动（nomount→mount→open）
SQL> startup mount # 只挂载（用于恢复/归档切换）
SQL> alter database open; # 打开数据库
SQL> shutdown immediate # 正常关闭（等事务完成）
SQL> shutdown abort # 强制关闭（需实例恢复）
SQL> alter database mount; # 挂载
# 实例状态查询
SQL> select status from v$instance; # OPEN/MOUNT/STARTED
SQL> select instance_name, version, host_name from v$instance;
SQL> show parameter db_name
SQL> show parameter sga_target
SQL> show parameter pga_aggregate_target
SQL> select name, open_mode from v$database; # 数据库打开模式
SQL> select sysdate from dual; # 当前时间（连通性测试）二、监听与网络
# 监听管理
lsnrctl start # 启动监听
lsnrctl stop # 停止
lsnrctl status # 状态（服务注册情况）
lsnrctl services # 查看注册服务
lsnrctl reload # 重载 listener.ora
# 配置文件
# $ORACLE_HOME/network/admin/listener.ora （监听端）
# $ORACLE_HOME/network/admin/tnsnames.ora （客户端）
# tnsnames.ora 示例：
# ORCL =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.10)(PORT = 1521))
# (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))
# )
# 测试
tnsping orcl # 解析测试
telnet 10.0.0.10 1521 # 端口连通性
sqlplus scott/tiger@orcl # 实际连接测试
# 修改端口/动态注册
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.10)(PORT=1521))';
SQL> alter system register;三、表空间与数据文件
-- 查看表空间
SELECT tablespace_name, status, contents FROM dba_tablespaces;
SELECT file_name, tablespace_name, bytes/1024/1024 MB, autoextensible FROM dba_data_files;
SELECT name, total1, free1 FROM v$tablespace, ...; -- 空间使用概览
-- 创建表空间
CREATE TABLESPACE app_data
DATAFILE '/u01/app/oracle/oradata/orcl/app01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 32G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- 临时表空间
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 1G;
-- 扩容数据文件（在线）
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/app01.dbf' RESIZE 5G;
ALTER DATABASE DATAFILE '...' AUTOEXTEND ON NEXT 500M MAXSIZE 32G;
-- 添加数据文件
ALTER TABLESPACE app_data ADD DATAFILE '/u01/app/oracle/oradata/orcl/app02.dbf' SIZE 2G;
-- 删除/离线表空间
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE app_data OFFLINE NORMAL; -- 离线
-- 撤销表空间（UNDO）
CREATE UNDO TABLESPACE undo2 DATAFILE '...' SIZE 2G;
ALTER SYSTEM SET undo_tablespace=undo2;
DROP TABLESPACE undo1 INCLUDING CONTENTS AND DATAFILES;
-- 查看空间使用（Top 段）
SELECT owner, segment_name, segment_type, sum(bytes)/1024/1024 MB
FROM dba_segments GROUP BY owner, segment_name, segment_type
ORDER BY MB DESC FETCH FIRST 10 ROWS ONLY;四、用户与权限
-- 创建用户
CREATE USER app IDENTIFIED BY "App@123456"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON app_data;
ALTER USER app IDENTIFIED BY NewPass123;
ALTER USER app ACCOUNT LOCK / UNLOCK;
DROP USER app CASCADE; -- 级联删除
-- 权限
GRANT CONNECT, RESOURCE TO app; -- 角色
GRANT CREATE SESSION TO app;
GRANT CREATE TABLE TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON scott.emp TO app;
GRANT SELECT ANY TABLE TO dba_user;
REVOKE SELECT ON scott.emp FROM app;
-- 角色
CREATE ROLE read_only;
GRANT SELECT ON scott.emp TO read_only;
GRANT read_only TO app;
SELECT * FROM dba_role_privs WHERE grantee='APP';
SELECT * FROM dba_tab_privs WHERE grantee='APP';
SELECT * FROM dba_sys_privs WHERE grantee='APP';
-- 口令策略
SELECT profile, resource_name, limit FROM dba_profiles WHERE resource_type='PASSWORD';
ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;五、SQL 常用查询
-- 会话
SELECT sid, serial#, username, status, machine, program FROM v$session WHERE username IS NOT NULL;
SELECT sid, serial#, sql_id, wait_class, event FROM v$session WHERE status='ACTIVE';
-- 杀会话
ALTER SYSTEM KILL SESSION '123,456';
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;
-- 锁与阻塞
SELECT s.sid, s.serial#, s.username, l.type, l.mode, l.block
FROM v$lock l JOIN v$session s ON l.sid = s.sid WHERE l.block=1;
-- 阻塞树
SELECT sid, serial#, username, blocking_session FROM v$session WHERE blocking_session IS NOT NULL;
-- 对象
SELECT owner, object_name, object_type, status FROM dba_objects WHERE status='INVALID';
-- 编译失效对象
BEGIN DBMS_UTILITY.COMPILE_SCHEMA('APP'); END;
/
-- 索引
SELECT index_name, table_name, uniqueness, status FROM dba_indexes WHERE table_name='EMP';
CREATE INDEX idx_emp_ename ON scott.emp(ename);
CREATE BITMAP INDEX idx_emp_sex ON scott.emp(sex); -- 低基数
DROP INDEX idx_emp_ename;
-- 统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP', CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('APP');
SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE owner='APP';
-- 执行计划
EXPLAIN PLAN FOR SELECT * FROM scott.emp WHERE ename='SMITH';
SELECT * FROM table(dbms_xplan.display);
-- 实际执行计划
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-- 归档状态
ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;
ALTER SYSTEM ARCHIVE LOG CURRENT; -- 切换归档
ALTER DATABASE ARCHIVELOG; -- 开启归档（mount 状态下）
ALTER SYSTEM SET log_archive_dest_1='location=/u01/arch' scope=both;六、RMAN 备份与恢复
# 进入 RMAN
rman target /
rman target / catalog rman/xxx@rcat # 使用恢复目录
# RMAN 内命令
BACKUP DATABASE; # 全库备份
BACKUP DATABASE PLUS ARCHIVELOG; # 全库+归档（推荐）
BACKUP INCREMENTAL LEVEL 0 DATABASE; # 0 级增量
BACKUP INCREMENTAL LEVEL 1 DATABASE; # 1 级增量（累积/差异）
BACKUP ARCHIVELOG ALL DELETE INPUT; # 备份归档并删除已备份
BACKUP TABLESPACE app_data; # 表空间备份
BACKUP DATAFILE 4; # 数据文件备份
BACKUP CURRENT CONTROLFILE; # 控制文件
BACKUP SPFILE; # 参数文件
CONFIGURE CONTROLFILE AUTOBACKUP ON; # 控制文件自动备份
CONFIGURE RETENTION POLICY TO REDUNDANCY 2; # 保留策略
CONFIGURE BACKUP OPTIMIZATION ON;
LIST BACKUP; # 查看备份
LIST BACKUP OF DATABASE SUMMARY;
REPORT NEED BACKUP; # 报告需要备份的对象
# 全库恢复（最常用）
RESTORE DATABASE;
RECOVER DATABASE;
# 恢复到时间点
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('2026-08-30 10:00:00','YYYY-MM-DD HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;
# 数据文件恢复（在线，不关库）
RESTORE DATAFILE 5;
RECOVER DATAFILE 5;
# 验证
RESTORE DATABASE VALIDATE;
BACKUP VALIDATE CHECK LOGICAL DATABASE;七、数据泵（expdp / impdp）
# 先建目录对象并授权
mkdir -p /u01/dump
SQL> CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/u01/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO app;
# 导出
expdp app/密码@orclpdb1 directory=DUMP_DIR dumpfile=app_full.dmp schemas=app
expdp system/密码@orclpdb1 directory=DUMP_DIR dumpfile=emp.dmp tables=scott.emp
expdp system/密码 directory=DUMP_DIR dumpfile=full.dmp full=y parallel=4 compression=all
expdp app/密码 directory=DUMP_DIR dumpfile=app_inc.dmp schemas=app flashback_time=systimestamp
# 导入
impdp app/密码@orclpdb1 directory=DUMP_DIR dumpfile=app_full.dmp schemas=app
impdp system/密码 directory=DUMP_DIR dumpfile=app_full.dmp remap_schema=app:app2
impdp system/密码 directory=DUMP_DIR dumpfile=app_full.dmp remap_tablespace=app_data:app2_data
impdp system/密码 directory=DUMP_DIR dumpfile=emp.dmp tables=scott.emp table_exists_action=truncate
# 查看 job
impdp app/密码 attach=SYS_IMPORT_FULL_01 # 交互式监控
expdp app/密码 attach=SYS_EXPORT_SCHEMA_01八、闪回
-- 查询历史数据（闪回查询）
SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE);
SELECT * FROM emp AS OF SCN 1234567;
-- 闪回表
FLASHBACK TABLE emp TO BEFORE DROP; -- 回收站恢复
FLASHBACK TABLE emp TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
FLASHBACK TABLE emp TO SCN 1234567;
-- 回收站
SHOW RECYCLEBIN;
PURGE TABLE emp;
PURGE TABLESPACE app_data;
PURGE RECYCLEBIN;
-- 闪回数据库（需开启闪回日志）
SQL> ALTER DATABASE FLASHBACK ON;
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);
SQL> ALTER DATABASE OPEN RESETLOGS;九、AWR / ASH 调优
# 生成 AWR 报表（最常用）
sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt.sql
# 按提示：报告类型 html → 快照起止（查看快照列表输入）→ 输出文件名
# 也可直接生成当前到指定间隔：
SQL> @?/rdbms/admin/awrrpti.sql # 指定实例
# 手工快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
# AWR 基线/删除快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(100, 110);
# 查看 Top SQL（最近）
SELECT sql_id, elapsed_time, executions, cpu_time
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
SELECT sql_id, sql_text FROM v$sqlarea WHERE rownum <= 5 ORDER BY elapsed_time DESC;
# ASH 活动会话
SQL> @?/rdbms/admin/ashrpt.sql
# 等待事件
SELECT event, count(*) FROM v$session WHERE wait_class <> 'Idle' GROUP BY event ORDER BY 2 DESC;十、多租户（CDB/PDB）
-- 查看容器
SELECT name, open_mode FROM v$pdbs;
SHOW CON_NAME;
-- 切换容器
ALTER SESSION SET CONTAINER = pdb1;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- 创建/删除 PDB
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2adm IDENTIFIED BY "Pdb@123" FILE_NAME_CONVERT=('/pdbseed/','/pdb2/');
ALTER PLUGGABLE DATABASE pdb2 OPEN;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;
-- PDB 启动模式
ALTER PLUGGABLE DATABASE ALL OPEN; -- 全部打开
ALTER PLUGGABLE DATABASE ALL SAVE STATE; -- 保存状态（重启自动打开）十一、RAC 管理
# 集群资源
crsctl status resource -t # 资源树（ora.orcl.db、scan、vip）
crsctl status resource -t -f # 详细
crsctl start cluster / crsctl stop cluster # 整个集群
crsctl check cluster # 集群健康检查
crsctl check crs # CRS 状态
# srvctl 数据库管理
srvctl status database -d orcl # 数据库实例状态
srvctl start database -d orcl # 启动数据库
srvctl stop database -d orcl -o immediate
srvctl start instance -d orcl -i orcl1 # 单个实例
srvctl add service -d orcl -s app_svc -r "orcl1,orcl2"
srvctl status service -d orcl
# ASM
asmcmd ls +DATA # 查看 ASM 磁盘组内容
asmcmd lsdg # 磁盘组状态
SQL> SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;十二、DataGuard 管理
-- 主库
SQL> SELECT database_role, open_mode FROM v$database; -- PRIMARY
SQL> ALTER DATABASE SWITCHOVER TO STANDBY; -- 切换为备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; -- 切回主库
SQL> ALTER SYSTEM SWITCH LOGFILE;
-- 备库
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- 启动应用日志
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- 停止应用
-- 监控
SQL> SELECT dest_id, status, error FROM v$archive_dest;
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY 1 DESC;
SQL> SELECT name, value FROM v$dataguard_stats;
-- 查看主备是否一致（时间差）
SELECT * FROM v$dataguard_stats WHERE name LIKE '%lag%';十三、GoldenGate 常用命令
# 源端/目标端进程管理
ggsci # 进入 OGG 命令行
GGSCI> info all # 所有进程状态
GGSCI> start extract ext1 # 启动抽取进程
GGSCI> stop extract ext1
GGSCI> start replicat rep1 # 启动复制进程
GGSCI> stats extract ext1 # 统计
GGSCI> view report ext1 # 查看报告
# 常用配置（源端）
GGSCI> dblogin userid ogg@orcl, password ogg
GGSCI> add trandata scott.emp # 附加日志
GGSCI> add extract ext1, tranlog, begin now
GGSCI> add exttrail /u01/ogg/dirdat/lt, extract ext1
# 目标端
GGSCI> add replicat rep1, exttrail /u01/ogg/dirdat/lt
GGSCI> add checkpointtable ogg.checkpoint十四、故障排查速查
# 1. 实例起不来
tail -f $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log # 告警日志（最重要）
# 常见：ORA-01565 控制文件丢失、ORA-01157 数据文件脱机、ORA-19809 闪回区满
# 2. 监听连不上
lsnrctl status → tnsping → 检查 listener.ora/tnsnames.ora → 检查防火墙 1521
# 3. ORA-01555 快照过旧 → 增大 undo 表空间/UNDO_RETENTION
# 4. ORA-01653 表空间满 → 扩容数据文件或新增
# 5. ORA-00054 资源正忙 → 查 v$lock / v$session 阻塞会话
# 6. 归档满（%ORA-00257 archiver error）→ 清理/加大归档目录，ALTER SYSTEM ARCHIVE LOG CURRENT 测试
# 7. 日志切换慢 → 检查 redo 大小/磁盘 IO
# 8. 查看告警日志定位所有错误
tail -200 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log | grep -i ora-提示：生产环境操作前务必先做备份（RMAN 或数据泵）；ALTER DATABASE、DROP、KILL SESSION 等操作需评估影响。