Oracle数据库核心操作完全手册：运维、开发与调优必备

在Oracle数据库的日常使用中，无论是实例管理、表操作、权限控制，还是性能调优、故障排查，都离不开一系列高频且关键的操作。本文整合了Oracle数据库从基础操作到高级运维的核心SQL语句与流程，涵盖实例级别配置、表操作、权限角色、约束索引、锁机制、日志挖掘、备份恢复、SQL调优等十六大核心场景，适用于DBA、开发工程师及数据库运维人员，可作为日常工作的速查手册。
一、Oracle实例级操作

实例是Oracle数据库与操作系统交互的核心载体，本节涵盖实例相关的进程查询、系统级操作、环境配置及时间格式处理。
1. 查看Oracle进程用户信息

通过操作系统进程号（spid）关联Oracle会话，定位特定进程对应的数据库用户信息，常用于排查进程占用问题：

1. SELECT *
2. FROM v$session
3. WHERE paddr IN (SELECT addr FROM v$process WHERE spid = 423565); -- 替换为实际spid

复制代码

2. 系统级基础操作

• SQL输出重定向：将SQL执行结果保存到操作系统文件，便于日志留存与分析：
  1. SQL> spool /data/out.log   -- 开启输出，后续SQL结果写入指定文件
  2. SQL> spool off;           -- 关闭输出，结束日志写入

  复制代码
• 错误查看与系统交互：
  1. SQL> show error;           -- 查看上一条SQL的执行错误信息
  2. SQL> host ls -lt           -- 执行操作系统命令（Linux示例，Windows用host dir）
  3. SQL> @1.sql                -- 执行指定路径下的SQL脚本

  复制代码

3. SQL环境配置（set命令）

通过set命令优化SQL*Plus交互体验，适配不同查询场景：

1. SQL> set time on;          -- 显示执行时间戳
2. SQL> set timing on;        -- 显示SQL执行耗时
3. SQL> set autotrace on;     -- 开启执行计划与统计信息显示（需权限）
4. SQL> set autotrace off;    -- 关闭autotrace
5. SQL> set linesize 200;     -- 设置查询结果行宽（避免列换行）
6. SQL> set pagesize 10000;   -- 设置每页显示行数（适配大数据量查询）
7. SQL> set long 10000;       -- 设置长文本显示长度（适配LOB字段、长SQL文本）

复制代码

4. 时间格式处理

Oracle默认时间格式可能不符合业务需求，通过以下语句实现时间格式的转换与显示：

1. -- 查看默认系统时间
2. SQL> select sysdate from dual; -- dual为Oracle虚表，无实际数据，用于无表查询场景
4. -- 自定义时间格式（年月日 时分秒）
5. SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
7. -- 高精度时间（秒级保留6位小数）
8. SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ssxff') from dual;
10. -- 字符串与日期/数值互转
11. SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; -- 日期转字符串（2025-11-23）
12. SQL> select to_number('123') from dual;              -- 字符串转数值
13. SQL> select to_date('20251123','yyyymmdd') from dual;-- 字符串转日期

复制代码

二、表操作：基础与高级查询

表是Oracle数据存储的核心，本节涵盖表的创建、修改、数据操作及多表关联、复杂查询场景。
1. 基本表操作

（1）表结构管理

1. -- 创建表
2. SQL> create table student(sno number(6),sname varchar2(10),birthday date);
4. -- 新增列
5. SQL> alter table student add telephone varchar2(11);
7. -- 修改列属性
8. SQL> alter table transcript3 modify grade char(10);
10. -- 删除列（高危操作，需确认）
11. SQL> alter table student drop column telephone;
13. -- 临时修改会话日期格式（退出后失效）
14. SQL> alter session set nls_date_format='yyyymmdd';

复制代码

（2）数据操作与表复制

1. -- 插入数据
2. SQL> insert into student(sno,sname) values(4,'sunliu');
4. -- 复制表（含数据）
5. SQL> create table student2 as select * from student;
7. -- 复制表结构（不含数据）
8. SQL> create table student3 as select * from student where 1>2;
10. -- 清空表数据（无日志，不可恢复，慎用）
11. SQL> truncate table student4;
13. -- 删除表数据（有日志，可通过闪回恢复）
14. SQL> delete from student4; -- 注意：delete后需commit提交事务

复制代码

（3）数据查询技巧

1. -- 查询空值（不可用=null，需用is null）
2. SQL> select * from student where sname is null;
4. -- 忽略大小写查询
5. SQL> select * from student2 where upper(sname)='A'; -- upper()转大写，lower()转小写
7. -- 模糊查询（%匹配任意字符，_匹配单个字符）
8. SQL> select * from student2 where sname like 'A%'; -- 以A开头
10. -- 按长度查询
11. SQL> select * from student2 where length(sname)=4;
13. -- 分组统计与筛选
14. SQL> select sno,sum(score) from Transcript group by sno; -- 按学号求和
15. SQL> select sno,avg(score) from Transcript group by sno having(avg(score)>60); -- 筛选平均分>60的学生

复制代码

2. 多表查询

多表查询是业务开发的核心场景，支持内连接、外连接、全连接等多种关联方式：

1. -- 内连接（仅匹配两表共有关联数据）
2. SQL> select s.sno,s.sname,a.sno,a.zz from student s inner join address a on s.sno=a.sno;
3. SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno; -- 传统写法
5. -- 左外连接（保留左表所有数据，右表无匹配则为null）
6. SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno(+); -- 传统写法
7. SQL> select s.sno,s.sname,a.sno,a.zz from student s left outer join address a on s.sno=a.sno; -- 标准写法
9. -- 右外连接（保留右表所有数据）
10. SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno(+)=a.sno;
11. SQL> select s.sno,s.sname,a.sno,a.zz from student s right outer join address a on s.sno=a.sno;
13. -- 全连接（保留两表所有数据）
14. SQL> select s.sno,s.sname,a.sno,a.zz from student s full outer join address a on s.sno=a.sno;
16. -- 表与列重命名
17. SQL> rename student to newstudent; -- 重命名表
18. SQL> alter table newstudent rename column sno to newsno; -- 重命名列

复制代码

3. 复杂SQL执行顺序与场景

复杂SQL的执行顺序为：from → where → group by → having → select → order by，常见场景如下：

1. -- 存在性判断（exists比in更高效，尤其大数据量）
2. SQL> select * from student where exists(select * from address where zz='zhengzhou');
4. -- 查找重复数据
5. SQL> select * from student2 where xm in(select xm from student2 group by xm having(count(*)>1));
7. -- 行标识查询（rowid为数据物理地址，唯一标识行）
8. SQL> select a.*,rowid from student6 a;

复制代码

三、权限与角色管理

Oracle通过权限与角色实现精细化的访问控制，本节涵盖用户创建、权限分配、角色管理核心操作。
1. 用户与配置文件（Profile）

1. -- 创建用户（使用默认表空间）
2. SQL> create user test1 identified by test1;
4. -- 创建配置文件（限制登录失败次数与锁定时间）
5. SQL> create profile pro1 limit failed_login_attempts 2 password_lock_time 2;
7. -- 绑定用户与配置文件
8. SQL> alter user test1 profile pro1;
10. -- 创建用户时直接指定配置文件
11. SQL> create user test2 identified by test2 profile pro1;
13. -- 解锁用户
14. SQL> alter user test1 account unlock;
16. -- 修改配置文件（限制密码有效期与宽限期）
17. SQL> alter profile pro1 limit password_life_time 10 password_grace_time 2;

复制代码

2. 系统权限管理

系统权限控制用户对数据库的整体操作权限（如创建表、连接数据库）：

1. -- 授予基础连接与资源权限
2. SQL> GRANT CONNECT TO test;
3. SQL> GRANT RESOURCE TO test;
5. -- 授予创建表与无限制表空间权限
6. SQL> GRANT create table to test;
7. SQL> GRANT UNLIMITED TABLESPACE to test;
9. -- 授予创建任意表权限（高危，谨慎）
10. SQL> grant create any table to test;
12. -- 查看用户拥有的系统权限（sys/system用户执行）
13. SQL> select * from dba_sys_privs d where d.grantee='TEST1';
15. -- 权限继承（被授权用户可转授他人）
16. SQL> grant create session to test2 with admin option;
18. -- 收回权限
19. SQL> REVOKE create session FROM test1;

复制代码

3. 对象权限管理

对象权限控制用户对特定数据库对象（表、视图等）的操作权限：

1. -- 授予查询scott.emp表权限
2. SQL> GRANT SELECT ON SCOTT.EMP TO test1;
4. -- 授予更新scott.emp表权限
5. SQL> GRANT UPDATE ON SCOTT.EMP TO test1;
7. -- 授予所有操作权限
8. SQL> GRANT ALL ON SCOTT.EMP TO test1;
10. -- 权限继承（被授权用户可转授他人，9i及以上支持）
11. SQL> GRANT SELECT ON SCOTT.EMP TO test1 with grant option;
13. -- 收回对象权限
14. SQL> revoke select on scott.student from test2;

复制代码

4. 角色管理

角色是权限的集合，便于批量分配与管理：

1. -- 创建角色
2. SQL> CREATE ROLE role_name;
4. -- 为角色授予权限
5. SQL> grant create session, create any table, drop any table to role1;
7. -- 将角色授予用户
8. SQL> grant role1 to test2;
10. -- 查看角色相关信息
11. SQL> select * from dba_roles; -- 查看所有角色
12. SQL> select * from role_sys_privs; -- 查看角色的系统权限
13. SQL> select * from dba_tab_privs where grantee='DBA'; -- 查看角色的对象权限
15. -- 修改用户密码与删除用户
16. SQL> alter user test1 identified by test1; -- 修改密码
17. SQL> drop user test1 cascade; -- 删除用户（cascade级联删除用户所有对象）

复制代码

四、约束与索引管理

约束保证数据完整性，索引提升查询性能，本节涵盖约束创建、查询及索引空间统计。
1. 完整性约束

1. -- 非空约束（系统自动命名）
2. SQL> create table yg2(bh number not null,xm varchar2(8));
4. -- 检查约束（限制bh范围）
5. SQL> create table yg3(bh number(4) not null check (bh>0 and bh<10000), xm varchar2(8));
7. -- 唯一约束（自动创建索引，避免重复值）
8. SQL> create table yg4(bumenID number not null,bumenneiID number,xm varchar2(8),unique(bumenID,bumenneiID));
10. -- 默认值约束
11. SQL> create table gz_newYG(bh number(4),xm varchar2(8),gz number(10) default 1000 );
13. -- 主键约束（非空+唯一，自动创建索引，用户显式命名）
14. SQL> create table student(xh number(6) constraint code_pk primary key, xm varchar2(20));
16. -- 外键约束（参照主键表，保证数据一致性）
17. SQL> create table address (xh number(8),zz varchar2(50), foreign key(xh) references student(xh));
19. -- 禁用/启用主键约束
20. SQL> alter table user7 disable primary key;
21. SQL> alter table user7 enable primary key;

复制代码

2. 查询外键约束

1. -- 查询非主键、非唯一、非检查约束（即外键约束）
2. select * from user_constraints
3. where constraint_type <>'C' and constraint_type <>'P' and constraint_type <>'U';

复制代码

3. 索引空间与高度查询

查询索引的存储占用、高度等信息，评估索引性能：

1. select index_name,index_type,table_owner,table_name,compression,
2.        tablespace_name,blevel,leaf_blocks,num_rows,partitioned,visibility
3. from user_indexes
4. where table_owner='MESPRD' and table_name='SFC_BROOKNER_PARAM_LOG'
5. order by num_rows;
6. -- blevel：索引高度（0表示单级索引），leaf_blocks：叶子块数量，num_rows：索引行数

复制代码

五、Oracle锁机制

Oracle锁分为行级锁与表级锁，用于控制并发访问，避免数据冲突。
1. 行级锁

行级锁仅锁定被操作的行，不影响其他行，是并发场景的常用锁：

1. -- 锁定emp表中满足条件的行（默认行级锁，其他用户可查询但不可更新）
2. SQL> select * from emp for update;
4. -- 锁定行并设置等待时间（5秒未获取锁则报错）
5. SQL> select ... for update wait 5;
7. -- 锁定行不等待（未获取锁立即报错）
8. SQL> select ... for update nowait;
10. -- 查看锁信息（TM：表级锁，TX：行级锁）
11. SQL> select ADDR,KADDR,SID,TYpe,ID1 from v$lock where type='TM'or type='TX';
13. -- 解锁（提交或回滚事务）
14. SQL> rollback; -- 或commit;

复制代码

2. 表级锁

表级锁锁定整个表，限制其他用户对表的操作，适用于批量更新等场景：

1. -- 行级共享锁（允许其他用户查询、插入，禁止排他锁）
2. SQL> lock table emp in row share mode;
4. -- 共享锁（允许其他用户查询，禁止更新、删除）
5. SQL> lock table emp in share mode;
7. -- 行级排他锁（允许其他用户查询、插入，禁止共享锁）
8. SQL> lock table emp in row exclusive mode;
10. -- 共享行级排他锁（允许其他用户查询，禁止更新、共享锁）
11. SQL> lock table emp in share row exclusive mode;
13. -- 排他锁（禁止其他用户所有写操作，仅允许查询）
14. SQL> lock table emp in exclusive mode;

复制代码

六、进程与会话管理

1. 会话与进程查询

1. -- 查看SCOTT用户的当前会话数
2. SQL> select username from v$session where username='SCOTT';
4. -- 查看所有进程信息（sys/system用户）
5. SQL> select * from v$process;
7. -- 断开当前连接
8. SQL> disconnect;

复制代码

2. Oracle实例后台进程

Oracle实例的核心后台进程（如DBWR、LGWR）保障数据库正常运行，可通过以下语句查询：

1. -- 查看DBWR进程数量（sys用户）
2. SQL> show parameter db_wr;
4. -- 查看DBWR进程详情（PGA占用）
5. SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%DBW%';
7. -- 查看LGWR进程详情
8. SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%LGW%';
10. -- 查看统计信息相关参数
11. SQL> show parameter statistics;

复制代码

七、日志挖掘：LogMiner数据恢复与分析

LogMiner是Oracle自带的日志分析工具，可通过归档日志或在线日志挖掘数据变更（insert/update/delete），适用于数据恢复、审计等场景。核心流程：开启补充日志→创建字典→添加日志→分析→查看结果。
1. 开启补充日志

补充日志记录更多数据变更细节，是LogMiner分析的前提：

1. -- 查看补充日志状态（YES表示已开启）
2. select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
4. -- 开启补充日志
5. alter database add supplemental log data;

复制代码

2. 设置字典文件路径（需重启数据库）

字典文件用于解析日志中的对象名（如表、列），需提前配置路径：

1. -- 查看当前字典文件路径
2. show parameter utl_file_dir;
4. -- 设置路径（需重启数据库生效）
5. alter system set utl_file_dir='/oracle' scope=spfile;

复制代码

3. 创建数据字典文件

1. exec dbms_logmnr_d.build('dict.ora','/oracle'); -- 文件名+路径

复制代码

4. 建立日志分析列表

1. -- 添加第一个日志文件（new表示创建新列表）
2. execute dbms_logmnr.add_logfile(logfilename=>'/oracle/redo01.log',options=>dbms_logmnr.new);
4. -- 继续添加日志文件（addfile表示追加）
5. execute dbms_logmnr.add_logfile(logfilename=>'/oracle/redo02.log',options=>dbms_logmnr.addfile);
7. -- 移除日志文件
8. execute dbms_logmnr.remove_logfile(logfilename=>'/oracle/redo02.log');

复制代码

5. 启动分析

1. -- 无条件分析（全量日志）
2. exec dbms_logmnr.start_logmnr(DictFileName =>'/oracle/dict.ora');
4. -- 按SCN范围分析
5. exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',startscn=>123,endScn => 124);
7. -- 按时间范围分析
8. exec dbms_logmnr.start_logmnr(
9.   DictFileName=>'/oracle/dict.ora',
10.   starttime=> to_date('2025-11-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),
11.   endtime=> to_date('2025-11-23 01:00:00','YYYY-MM-DD HH24:MI:SS')
12. );
14. -- 仅分析已提交的事务
15. EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

复制代码

6. 查看分析结果

1. -- 查看所有变更记录（sql_redo：执行语句，sql_undo：回滚语句）
2. select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;
4. -- 筛选特定表的变更记录
5. select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents  
6. where seg_owner='TEST' and seg_name='TEST01';

复制代码

7. 结束分析

1. exec dbms_logmnr.end_logmnr;

复制代码

八、RMAN备份与恢复

RMAN（Recovery Manager）是Oracle专业的备份恢复工具，支持全库备份、归档日志备份、数据文件备份等。
1. 核心备份命令

1. -- 全库备份（指定标签、备份路径）
2. RMAN> backup tag itpux_db01_fullbak format '/backup/full/itpux_db01_full_%s_%p_%t' (database);
4. -- 归档日志备份
5. RMAN> backup tag itpux_db01_arch format '/backup/arch/itpux_db01_arch_%s_%p_%t' archivelog all;
7. -- 数据文件备份（指定数据文件号）
8. RMAN> backup tag LLL1_file format '/oracle/backup/full/LLL1_datafile_db01_%s_%p_%t_%c' (datafile 1,2,3);
10. -- 控制文件备份
11. RMAN> backup tag LLL1_ctl format '/oracle/backup/full/LLL1_ctl_db01_%s_%p_%t_%c' (current controlfile);
13. -- 参数文件（spfile）备份
14. RMAN> backup tag LLL1_pfile format '/oracle/backup/full/LLL1_pfile_db01_%s_%p_%t_%c' (spfile);

复制代码

2. 备份脚本参考

可通过编写RMAN脚本实现自动化备份，示例脚本可参考：RMAN自动化备份脚本
九、日志与闪回功能

1. 查看undo数据量

undo数据用于事务回滚与读一致性，查询近期undo生成量：

1. ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';
2. SELECT begin_time,
3.        end_time,
4.        (undoblks * (SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')) undo_bytes -- 总undo字节数
5. FROM v$undostat;

复制代码

2. 闪回日志与还原点

闪回功能基于归档日志与闪回日志，支持数据库、表级别的时间点恢复，前提是数据库处于归档模式。

1. -- 1. 查看归档模式
2. SQL> archive log list;
4. -- 2. 开启/关闭闪回功能
5. SQL> alter database flashback on;
6. SQL> alter database flashback off;
8. -- 3. 查看闪回状态
9. SQL> select flashback_on from v$database;
11. -- 4. 创建还原点（正常还原点）
12. SQL> CREATE RESTORE POINT restorepoint1;
14. -- 5. 创建担保还原点（用于drop后恢复，建议mount模式下创建）
15. SQL> create restore point before_drop guarantee flashback database;
17. -- 6. 查看还原点
18. SQL> select * from V$RESTORE_POINT;
20. -- 7. 数据库闪回（需mount模式，闪回后resetlogs打开）
21. SQL> shutdown immediate;
22. SQL> startup mount;
23. SQL> FLASHBACK database TO RESTORE POINT restorepoint1; -- 按还原点闪回
24. SQL> FLASHBACK DATABASE TO SCN 1121679; -- 按SCN闪回
25. SQL> FLASHBACK DATABASE TO TIMESTAMP to_date('2025-11-23 10:00:00','YYYY-MM-DD HH24:MI:SS'); -- 按时间闪回
26. SQL> alter database open resetlogs;
28. -- 8. RMAN模式下闪回
29. RMAN> FLASHBACK DATABASE TO SCN=1121679;
30. RMAN> FLASHBACK DATABASE TO TIME ="TO_DATE('2025-11-23 10:00:00','YYYY-MM-DD HH24:MI:SS')";

复制代码

十、表空间管理

表空间是Oracle数据存储的逻辑单位，本节涵盖表空间查询、权限分配与创建。
1. 查询大字段表与占用空间

1. -- 查询NCC用户下USERS表空间中含大字段（LOB）的表，按占用空间降序
2. select a.owner,a.table_name,a.column_name,a.tablespace_name,bytes/1024/1024/1024 as GB
3. from all_lobs a,dba_segments b
4. where a.owner='NCC' and
5.       a.tablespace_name='USERS' and
6.       a.segment_name=b.segment_name
7. order by b.bytes desc;

复制代码

2. 表空间容量统计（核心查询）

查询表空间总容量、已用容量、空闲容量及使用率，适配自动扩展场景：

1. select a.tablespace_name,
2.        round(maxtotal,4) as maxtotal_GB, -- 总容量（含自动扩展最大空间）
3.        round(total,4) as extended_Total_GB, -- 已扩展容量
4.        round((total-free),4) as usage_GB, -- 已用容量
5.        round((maxtotal-total+free),4) as free_GB, -- 实际空闲容量
6.        round((1-(total-free)/maxtotal)*100,4)||'%' as free_percent -- 空闲率
7. from
8. (select tablespace_name,
9.         SUM(bytes)/1024/1024/1024 as total,
10.         SUM(case autoextensible when 'YES' then maxbytes else bytes end )/1024/1024/1024 as maxtotal
11. from dba_data_files group by tablespace_name) a,
12. (select tablespace_name,sum(bytes)/1024/1024/1024 as free from dba_free_space group by tablespace_name) b
13. where a.tablespace_name = b.tablespace_name
14. order by free_percent desc;

复制代码

3. 授予用户表空间权限

1. -- 授予mes用户test_tbsp表空间无限制使用权限
2. alter user mes quota unlimited on test_tbsp;

复制代码

4. 表空间创建（常用格式）

1. create tablespace MES_PRD
2. datafile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.dbf' -- 数据文件路径
3. size 4000M autoextend on next 500M maxsize unlimited -- 初始4G，自动扩展每次500M，无上限
4. uniform size 1M -- 区大小1M
5. logging -- 开启日志
6. extent management local -- 本地管理表空间
7. segment space management auto; -- 自动段空间管理

复制代码

十一、SQL调优顾问（SQL Tuning Advisor）

SQL调优顾问是Oracle内置的性能优化工具，可自动分析SQL语句，提供索引创建、SQL重写、Profile绑定等优化建议。
1. 自动SQL调优任务（SYS用户）

1. -- （1）查看自动调优任务是否开启
2. select * from dba_autotask_client; -- 查看sql tuning advisor状态
4. -- （2）查看自动调优任务
5. select * from dba_advisor_tasks where task_name = 'SYS_AUTO_SQL_TUNING_TASK';
7. -- （3）查看任务参数（如执行时间限制）
8. select * from dba_advisor_parameters
9. where task_name like 'SYS_AUTO_SQL_TUNING%'
10.   and (parameter_name like '%TIME_LIMIT%');
12. -- （4）修改执行时间限制
13. -- 单条SQL最大分析时间（3小时）
14. begin
15. dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 10800);
16. end;
17. /
19. -- 整个任务最大执行时间（10小时）
20. begin
21. dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 36000);
22. end;
23. /
25. -- （5）手动执行自动调优任务
26. exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK');
28. -- （6）取消正在执行的任务
29. exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
31. -- （7）查看优化建议报告
32. set linesize 80 pagesize 0 long 100000
33. select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
35. -- （8）生成优化建议SQL脚本
36. set linesize 132 pagesize 0 long 100000
37. select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;

复制代码

2. 手动调优单个SQL（按SQL_ID）

1. -- （1）获取目标SQL的SQL_ID（从v$sql或AWR报告中查询）
2. select SQL_TEXT, SQL_FULLTEXT, SQL_ID
3. from v$sql
4. where SQL_TEXT like 'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%';
6. -- （2）创建调优任务（按SQL_ID）
7. declare
8. tune_task varchar2(30);
9. begin
10. tune_task := DBMS_SQLTUNE.create_tuning_task(
11.   sql_id =>'26vvbvs4mawf1', -- 替换为实际SQL_ID
12.   task_name => 'test_sql_tuning'
13. );
14. end;
15. /
17. -- （3）开启会话跟踪（可选，用于获取详细日志）
18. oradebug setmypid;
19. oradebug tracefile_name; -- 记录跟踪文件路径
21. -- （4）执行调优任务
22. exec dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning');
24. -- （5）查看任务状态（COMPLETED表示完成）
25. SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_sql_tuning';
27. -- （6）关闭跟踪
28. oradebug close_trace;
30. -- （7）查看优化建议
31. set long 1000000 longchunksize 1000000
32. set linesize 300 pagesize 0
33. select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;
35. -- （8）删除调优任务
36. BEGIN
37. DBMS_ADVISOR.DELETE_TASK(task_name => 'test_sql_tuning');
38. END;
39. /

复制代码

3. 调优并绑定SQL Profile（优化执行计划）

1. -- （1）创建调优任务（SYS用户）
2. variable sta_task VARCHAR2(100);
3. exec :sta_task:=dbms_sqltune.create_tuning_task(sql_id=>'2k22yt1p1ckbr',time_limit=>72000,task_name=>'MyTask1');
5. -- （2）执行任务
6. exec dbms_sqltune.execute_tuning_task(task_name=>'MyTask1');
8. -- （3）查看优化报告
9. select dbms_sqltune.report_tuning_task('MyTask1') from dual;
11. -- （4）接受建议，创建SQL Profile（绑定优化执行计划）
12. execute dbms_sqltune.accept_sql_profile(
13. task_name => 'MyTask1',task_owner =>'sys', replace => TRUE);
15. -- （5）查看SQL Profile
16. select a.name, a.task_id, a.created from dba_sql_profiles a, dba_advisor_log b
17. where a.task_id = b.task_id and b.task_name = 'MyTask1';
19. -- （6）删除SQL Profile
20. exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_017acc4af5ea0001'); -- 替换为实际Profile名称
22. -- （7）删除调优任务
23. exec dbms_sqltune.drop_tuning_task(task_name => 'MyTask1');

复制代码

十二、SQL语句跟踪

通过跟踪SQL执行过程，获取执行计划、绑定变量、耗时等信息，是SQL调优的核心手段。
1. 按SID和Serial#跟踪会话

1. -- （1）获取操作系统PID（Linux）
2. ps -ef|grep LOCAL=NO -- 查找目标用户的进程号（如24696）
4. -- （2）获取会话SID和Serial#
5. select s.SID,s.serial#,s.paddr,s.username,s.status,s.program,p.spid
6. from v$session s,v$process p
7. where s.paddr=p.addr and p.spid=24696; -- 替换为实际PID
9. -- （3）开启SQL跟踪
10. execute dbms_monitor.session_trace_enable(session_id =>191,serial_num => 319,plan_stat => 'all_executions');
12. -- （4）确认跟踪状态
13. select s.SID,s.serial#,s.sql_trace from v$session s where sid=191;
15. -- （5）前端执行目标操作（如查询、更新）
17. -- （6）生成可读报告（tkprof工具）
18. tkprof mesorcl_ora_24696.trc test.log sys=no; -- 跟踪文件→可读日志
20. -- （7）关闭跟踪
21. exec dbms_monitor.session_trace_disable(session_id =>191 , serial_num =>319 );
23. -- （8）确认关闭
24. select s.SID,s.serial#,s.sql_trace from v$session s where sid=191;

复制代码

2. 系统级跟踪

1. -- （1）开启系统级跟踪（sys用户）
2. alter system set sql_trace=true;
4. -- （2）跟踪文件路径
5. $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace
7. -- （3）生成可读报告
8. tkprof mesorcl_ora_8452.trc test2.log sys=no;
10. -- （4）关闭系统级跟踪
11. alter system set sql_trace=false;

复制代码

3. 获取最新执行的SQL

1. -- 按执行时间降序，查看最近执行的SQL
2. select a.machine,a.osuser,b.sql_text,b.sql_id,b.LAST_LOAD_TIME,a.prev_exec_start
3. from v$session a,v$sql b
4. where a.prev_sql_id=b.sql_id
5. order by b.last_load_time desc;

复制代码

十三、游标管理

游标是Oracle执行SQL语句的内存结构，合理配置游标参数可避免软解析，提升性能。
1. 游标相关查询

1. -- （1）当前所有会话使用的游标数与最大允许值
2. select sum(a.value) as current_cursors, b.name, c.value as max_open_cursor
3. from v$sesstat a, v$statname b, v$parameter c
4. where a.statistic# = b.statistic#
5.   and b.name = 'opened cursors current'
6.   and c.name= 'open_cursors'
7. group by b.name, c.value;
9. -- （2）使用游标数最多的会话
10. select max(a.value) as max_cursors, b.name
11. from v$sesstat a, v$statname b
12. where a.statistic# = b.statistic#
13.   and b.name = 'opened cursors current'
14. group by b.name;

复制代码

2. 游标核心参数

• open_cursors：每个会话最多同时打开的游标数（默认500，建议根据业务调整）。
  
• session_cached_cursor：每个会话最多缓存的已关闭游标数（默认50，缓存游标可避免重复软解析）。
  

十四、死锁处理

死锁是并发场景下的常见问题，需通过查询死锁进程、强制终止会话解决。

1. -- （1）查询死锁对应的SQL语句
2. select sql_text from v$sql where hash_value in
3. (select sql_hash_value from v$session where sid in
4. (select session_id from v$locked_object));
6. -- （2）查找死锁会话信息
7. select s.username, l.object_id, l.session_id sid,
8.        s.serial#, l.oracle_username, l.os_user_name, l.process
9. from v$locked_object l, v$session s
10. where l.session_id = s.sid;
12. -- （3）kill死锁会话（替换为实际sid和serial#）
13. SQL> alter system kill session '191,319';
15. -- （4）若kill会话失败，直接杀操作系统进程
16. -- 第一步：获取操作系统进程号
17. SQL> select pro.spid from v$session ses, v$process pro where ses.sid=191 and ses.paddr=pro.addr;
19. -- 第二步：操作系统层面kill进程（Linux）
20. $ kill -9 24696 -- 替换为实际spid

复制代码

十五、自动维护任务管理

Oracle自动维护任务包括统计信息收集、空间顾问、SQL调优顾问，可按需启用/禁用。
1. 查看自动维护任务

1. select * from dba_autotask_client;
2. -- 核心任务：
3. -- auto optimizer stats collection：自动统计信息收集
4. -- auto space advisor：自动空间顾问
5. -- sql tuning advisor：自动SQL调优顾问

复制代码

2. 启用/禁用所有任务

1. -- 禁用所有自动维护任务
2. begin
3.   EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;
4. end;
5. /
7. -- 启用所有自动维护任务
8. begin
9.   EXECUTE DBMS_AUTO_TASK_ADMIN.enable;
10. end;
11. /

复制代码

3. 启用/禁用特定任务

1. -- 禁用SQL调优顾问
2. BEGIN
3. dbms_auto_task_admin.disable(
4. client_name => 'sql tuning advisor',
5. operation => NULL,
6. window_name => NULL);
7. END;
8. /
10. -- 启用SQL调优顾问
11. BEGIN
12. dbms_auto_task_admin.enable(
13. client_name => 'sql tuning advisor',
14. operation => NULL,
15. window_name => NULL);
16. END;
17. /

复制代码

4. 维护窗口管理

1. -- （1）修改维护窗口属性（如修改周五窗口开始时间为11:40）
2. BEGIN
3.   dbms_scheduler.disable(name => 'FRIDAY_WINDOW'); -- 先禁用
4.   dbms_scheduler.set_attribute(
5.     name => 'FRIDAY_WINDOW',
6.     attribute => 'REPEAT_INTERVAL',
7.     value => 'freq=daily;byday=FRI;byhour=11;byminute=40; bysecond=0'
8.   );
9.   dbms_scheduler.enable(name => 'FRIDAY_WINDOW'); -- 启用
10. END;
11. /
13. -- （2）创建新维护窗口
14. BEGIN
15. dbms_scheduler.create_window(
16.                      window_name => 'EARLY_MORNING_WINDOW',
17.                      duration => numtodsinterval(1, 'hour'), -- 持续1小时
18.                      resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
19.                      repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
20. dbms_scheduler.add_window_group_member(
21.                      group_name => 'MAINTENANCE_WINDOW_GROUP',
22.                      window_list => 'EARLY_MORNING_WINDOW');
23. END;
24. /
26. -- （3）删除维护窗口
27. BEGIN
28. DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
29.        group_name => 'MAINTENANCE_WINDOW_GROUP',
30.        window_list => 'EARLY_MORNING_WINDOW');
31. DBMS_SCHEDULER.DROP_WINDOW(window_name => 'EARLY_MORNING_WINDOW');
32. END;
33. /

复制代码

十六、其他常用操作

1. orapwd工具（创建密码文件）

1. orapwd file=/oracle/app/oracle/product/12.2/db_1/dbs/orapwkssyorclprd password=1qaz%2WSX force=y
2. -- file：密码文件路径，需包含实例名（如kssyorclprd）
3. -- force=y：覆盖已存在的密码文件

复制代码

2. Oracle官方文档查询

通过百度搜索Oracle官方文档，精准定位关键字：

1. site:docs.oracle.com v$database  -- 搜索v$database视图文档
2. site:docs.oracle.com dba_scheduler_windows  -- 搜索维护窗口文档

复制代码

3. 查看统计信息收集时间

1. -- 查看表的最新统计信息收集时间
2. SQL> select table_name, LAST_ANALYZED from dba_tables where owner='MESPRD';
4. -- 查看索引的最新统计信息收集时间
5. SQL> select index_name, LAST_ANALYZED from dba_indexes where owner='MESPRD';

复制代码

总结

本文涵盖Oracle数据库从基础操作到高级运维的核心场景，所有SQL语句均经过实战验证，适用于Oracle 11g/12c/19c等主流版本。使用时需注意：

• 高危操作（如truncate、drop、kill session、闪回数据库）需提前备份数据，避免数据丢失；
  
• 权限相关操作需以sys或system用户执行，普通用户需具备对应权限；
  
• 调优、日志挖掘等操作建议在测试环境验证后，再应用到生产环境。
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

收藏一下   不知道什么时候能用到