在Oracle数据库的日常使用中，无论是实例管理、表操作、权限控制，还是性能调优、故障排查，都离不开一系列高频且关键的操作。本文整合了Oracle数据库从基础操作到高级运维的核心SQL语句与流程，涵盖实例级别配置、表操作、权限角色、约束索引、锁机制、日志挖掘、备份恢复、SQL调优等十六大核心场景，适用于DBA、开发工程师及数据库运维人员，可作为日常工作的速查手册。
一、Oracle实例级操作
实例是Oracle数据库与操作系统交互的核心载体，本节涵盖实例相关的进程查询、系统级操作、环境配置及时间格式处理。
1. 查看Oracle进程用户信息
通过操作系统进程号（spid）关联Oracle会话，定位特定进程对应的数据库用户信息，常用于排查进程占用问题：
SELECT * FROM v$session WHERE paddr IN (SELECT addr FROM v$process WHERE spid = 423565); -- 替换为实际spid 2. 系统级基础操作
- SQL输出重定向：将SQL执行结果保存到操作系统文件，便于日志留存与分析：
SQL> spool /data/out.log -- 开启输出，后续SQL结果写入指定文件 SQL> spool off; -- 关闭输出，结束日志写入 - 错误查看与系统交互：
SQL> show error; -- 查看上一条SQL的执行错误信息 SQL> host ls -lt -- 执行操作系统命令（Linux示例，Windows用host dir） SQL> @1.sql -- 执行指定路径下的SQL脚本
3. SQL环境配置（set命令）
通过set命令优化SQL*Plus交互体验，适配不同查询场景：
SQL> set time on; -- 显示执行时间戳 SQL> set timing on; -- 显示SQL执行耗时 SQL> set autotrace on; -- 开启执行计划与统计信息显示（需权限） SQL> set autotrace off; -- 关闭autotrace SQL> set linesize 200; -- 设置查询结果行宽（避免列换行） SQL> set pagesize 10000; -- 设置每页显示行数（适配大数据量查询） SQL> set long 10000; -- 设置长文本显示长度（适配LOB字段、长SQL文本） 4. 时间格式处理
Oracle默认时间格式可能不符合业务需求，通过以下语句实现时间格式的转换与显示：
-- 查看默认系统时间 SQL> select sysdate from dual; -- dual为Oracle虚表，无实际数据，用于无表查询场景 -- 自定义时间格式（年月日 时分秒） SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; -- 高精度时间（秒级保留6位小数） SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ssxff') from dual; -- 字符串与日期/数值互转 SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; -- 日期转字符串（2025-11-23） SQL> select to_number('123') from dual; -- 字符串转数值 SQL> select to_date('20251123','yyyymmdd') from dual;-- 字符串转日期 二、表操作：基础与高级查询
表是Oracle数据存储的核心，本节涵盖表的创建、修改、数据操作及多表关联、复杂查询场景。
1. 基本表操作
（1）表结构管理
-- 创建表 SQL> create table student(sno number(6),sname varchar2(10),birthday date); -- 新增列 SQL> alter table student add telephone varchar2(11); -- 修改列属性 SQL> alter table transcript3 modify grade char(10); -- 删除列（高危操作，需确认） SQL> alter table student drop column telephone; -- 临时修改会话日期格式（退出后失效） SQL> alter session set nls_date_format='yyyymmdd'; （2）数据操作与表复制
-- 插入数据 SQL> insert into student(sno,sname) values(4,'sunliu'); -- 复制表（含数据） SQL> create table student2 as select * from student; -- 复制表结构（不含数据） SQL> create table student3 as select * from student where 1>2; -- 清空表数据（无日志，不可恢复，慎用） SQL> truncate table student4; -- 删除表数据（有日志，可通过闪回恢复） SQL> delete from student4; -- 注意：delete后需commit提交事务 （3）数据查询技巧
-- 查询空值（不可用=null，需用is null） SQL> select * from student where sname is null; -- 忽略大小写查询 SQL> select * from student2 where upper(sname)='A'; -- upper()转大写，lower()转小写 -- 模糊查询（%匹配任意字符，_匹配单个字符） SQL> select * from student2 where sname like 'A%'; -- 以A开头 -- 按长度查询 SQL> select * from student2 where length(sname)=4; -- 分组统计与筛选 SQL> select sno,sum(score) from Transcript group by sno; -- 按学号求和 SQL> select sno,avg(score) from Transcript group by sno having(avg(score)>60); -- 筛选平均分>60的学生 2. 多表查询
多表查询是业务开发的核心场景，支持内连接、外连接、全连接等多种关联方式：
-- 内连接（仅匹配两表共有关联数据） SQL> select s.sno,s.sname,a.sno,a.zz from student s inner join address a on s.sno=a.sno; SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno; -- 传统写法 -- 左外连接（保留左表所有数据，右表无匹配则为null） SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno(+); -- 传统写法 SQL> select s.sno,s.sname,a.sno,a.zz from student s left outer join address a on s.sno=a.sno; -- 标准写法 -- 右外连接（保留右表所有数据） SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno(+)=a.sno; SQL> select s.sno,s.sname,a.sno,a.zz from student s right outer join address a on s.sno=a.sno; -- 全连接（保留两表所有数据） SQL> select s.sno,s.sname,a.sno,a.zz from student s full outer join address a on s.sno=a.sno; -- 表与列重命名 SQL> rename student to newstudent; -- 重命名表 SQL> alter table newstudent rename column sno to newsno; -- 重命名列 3. 复杂SQL执行顺序与场景
复杂SQL的执行顺序为：from → where → group by → having → select → order by，常见场景如下：
-- 存在性判断（exists比in更高效，尤其大数据量） SQL> select * from student where exists(select * from address where zz='zhengzhou'); -- 查找重复数据 SQL> select * from student2 where xm in(select xm from student2 group by xm having(count(*)>1)); -- 行标识查询（rowid为数据物理地址，唯一标识行） SQL> select a.*,rowid from student6 a; 三、权限与角色管理
Oracle通过权限与角色实现精细化的访问控制，本节涵盖用户创建、权限分配、角色管理核心操作。
1. 用户与配置文件（Profile）
-- 创建用户（使用默认表空间） SQL> create user test1 identified by test1; -- 创建配置文件（限制登录失败次数与锁定时间） SQL> create profile pro1 limit failed_login_attempts 2 password_lock_time 2; -- 绑定用户与配置文件 SQL> alter user test1 profile pro1; -- 创建用户时直接指定配置文件 SQL> create user test2 identified by test2 profile pro1; -- 解锁用户 SQL> alter user test1 account unlock; -- 修改配置文件（限制密码有效期与宽限期） SQL> alter profile pro1 limit password_life_time 10 password_grace_time 2; 2. 系统权限管理
系统权限控制用户对数据库的整体操作权限（如创建表、连接数据库）：
-- 授予基础连接与资源权限 SQL> GRANT CONNECT TO test; SQL> GRANT RESOURCE TO test; -- 授予创建表与无限制表空间权限 SQL> GRANT create table to test; SQL> GRANT UNLIMITED TABLESPACE to test; -- 授予创建任意表权限（高危，谨慎） SQL> grant create any table to test; -- 查看用户拥有的系统权限（sys/system用户执行） SQL> select * from dba_sys_privs d where d.grantee='TEST1'; -- 权限继承（被授权用户可转授他人） SQL> grant create session to test2 with admin option; -- 收回权限 SQL> REVOKE create session FROM test1; 3. 对象权限管理
对象权限控制用户对特定数据库对象（表、视图等）的操作权限：
-- 授予查询scott.emp表权限 SQL> GRANT SELECT ON SCOTT.EMP TO test1; -- 授予更新scott.emp表权限 SQL> GRANT UPDATE ON SCOTT.EMP TO test1; -- 授予所有操作权限 SQL> GRANT ALL ON SCOTT.EMP TO test1; -- 权限继承（被授权用户可转授他人，9i及以上支持） SQL> GRANT SELECT ON SCOTT.EMP TO test1 with grant option; -- 收回对象权限 SQL> revoke select on scott.student from test2; 4. 角色管理
角色是权限的集合，便于批量分配与管理：
-- 创建角色 SQL> CREATE ROLE role_name; -- 为角色授予权限 SQL> grant create session, create any table, drop any table to role1; -- 将角色授予用户 SQL> grant role1 to test2; -- 查看角色相关信息 SQL> select * from dba_roles; -- 查看所有角色 SQL> select * from role_sys_privs; -- 查看角色的系统权限 SQL> select * from dba_tab_privs where grantee='DBA'; -- 查看角色的对象权限 -- 修改用户密码与删除用户 SQL> alter user test1 identified by test1; -- 修改密码 SQL> drop user test1 cascade; -- 删除用户（cascade级联删除用户所有对象） 四、约束与索引管理
约束保证数据完整性，索引提升查询性能，本节涵盖约束创建、查询及索引空间统计。
1. 完整性约束
-- 非空约束（系统自动命名） SQL> create table yg2(bh number not null,xm varchar2(8)); -- 检查约束（限制bh范围） SQL> create table yg3(bh number(4) not null check (bh>0 and bh<10000), xm varchar2(8)); -- 唯一约束（自动创建索引，避免重复值） SQL> create table yg4(bumenID number not null,bumenneiID number,xm varchar2(8),unique(bumenID,bumenneiID)); -- 默认值约束 SQL> create table gz_newYG(bh number(4),xm varchar2(8),gz number(10) default 1000 ); -- 主键约束（非空+唯一，自动创建索引，用户显式命名） SQL> create table student(xh number(6) constraint code_pk primary key, xm varchar2(20)); -- 外键约束（参照主键表，保证数据一致性） SQL> create table address (xh number(8),zz varchar2(50), foreign key(xh) references student(xh)); -- 禁用/启用主键约束 SQL> alter table user7 disable primary key; SQL> alter table user7 enable primary key; 2. 查询外键约束
-- 查询非主键、非唯一、非检查约束（即外键约束） select * from user_constraints where constraint_type <>'C' and constraint_type <>'P' and constraint_type <>'U'; 3. 索引空间与高度查询
查询索引的存储占用、高度等信息，评估索引性能：
select index_name,index_type,table_owner,table_name,compression, tablespace_name,blevel,leaf_blocks,num_rows,partitioned,visibility from user_indexes where table_owner='MESPRD' and table_name='SFC_BROOKNER_PARAM_LOG' order by num_rows; -- blevel：索引高度（0表示单级索引），leaf_blocks：叶子块数量，num_rows：索引行数 五、Oracle锁机制
Oracle锁分为行级锁与表级锁，用于控制并发访问，避免数据冲突。
1. 行级锁
行级锁仅锁定被操作的行，不影响其他行，是并发场景的常用锁：
-- 锁定emp表中满足条件的行（默认行级锁，其他用户可查询但不可更新） SQL> select * from emp for update; -- 锁定行并设置等待时间（5秒未获取锁则报错） SQL> select ... for update wait 5; -- 锁定行不等待（未获取锁立即报错） SQL> select ... for update nowait; -- 查看锁信息（TM：表级锁，TX：行级锁） SQL> select ADDR,KADDR,SID,TYpe,ID1 from v$lock where type='TM'or type='TX'; -- 解锁（提交或回滚事务） SQL> rollback; -- 或commit; 2. 表级锁
表级锁锁定整个表，限制其他用户对表的操作，适用于批量更新等场景：
-- 行级共享锁（允许其他用户查询、插入，禁止排他锁） SQL> lock table emp in row share mode; -- 共享锁（允许其他用户查询，禁止更新、删除） SQL> lock table emp in share mode; -- 行级排他锁（允许其他用户查询、插入，禁止共享锁） SQL> lock table emp in row exclusive mode; -- 共享行级排他锁（允许其他用户查询，禁止更新、共享锁） SQL> lock table emp in share row exclusive mode; -- 排他锁（禁止其他用户所有写操作，仅允许查询） SQL> lock table emp in exclusive mode; 六、进程与会话管理
1. 会话与进程查询
-- 查看SCOTT用户的当前会话数 SQL> select username from v$session where username='SCOTT'; -- 查看所有进程信息（sys/system用户） SQL> select * from v$process; -- 断开当前连接 SQL> disconnect; 2. Oracle实例后台进程
Oracle实例的核心后台进程（如DBWR、LGWR）保障数据库正常运行，可通过以下语句查询：
-- 查看DBWR进程数量（sys用户） SQL> show parameter db_wr; -- 查看DBWR进程详情（PGA占用） SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%DBW%'; -- 查看LGWR进程详情 SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%LGW%'; -- 查看统计信息相关参数 SQL> show parameter statistics; 七、日志挖掘：LogMiner数据恢复与分析
LogMiner是Oracle自带的日志分析工具，可通过归档日志或在线日志挖掘数据变更（insert/update/delete），适用于数据恢复、审计等场景。核心流程：开启补充日志→创建字典→添加日志→分析→查看结果。
1. 开启补充日志
补充日志记录更多数据变更细节，是LogMiner分析的前提：
-- 查看补充日志状态（YES表示已开启） select SUPPLEMENTAL_LOG_DATA_MIN from v$database; -- 开启补充日志 alter database add supplemental log data; 2. 设置字典文件路径（需重启数据库）
字典文件用于解析日志中的对象名（如表、列），需提前配置路径：
-- 查看当前字典文件路径 show parameter utl_file_dir; -- 设置路径（需重启数据库生效） alter system set utl_file_dir='/oracle' scope=spfile; 3. 创建数据字典文件
exec dbms_logmnr_d.build('dict.ora','/oracle'); -- 文件名+路径 4. 建立日志分析列表
-- 添加第一个日志文件（new表示创建新列表） execute dbms_logmnr.add_logfile(logfilename=>'/oracle/redo01.log',options=>dbms_logmnr.new); -- 继续添加日志文件（addfile表示追加） execute dbms_logmnr.add_logfile(logfilename=>'/oracle/redo02.log',options=>dbms_logmnr.addfile); -- 移除日志文件 execute dbms_logmnr.remove_logfile(logfilename=>'/oracle/redo02.log'); 5. 启动分析
-- 无条件分析（全量日志） exec dbms_logmnr.start_logmnr(DictFileName =>'/oracle/dict.ora'); -- 按SCN范围分析 exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',startscn=>123,endScn => 124); -- 按时间范围分析 exec dbms_logmnr.start_logmnr( DictFileName=>'/oracle/dict.ora', starttime=> to_date('2025-11-23 00:00:00','YYYY-MM-DD HH24:MI:SS'), endtime=> to_date('2025-11-23 01:00:00','YYYY-MM-DD HH24:MI:SS') ); -- 仅分析已提交的事务 EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY); 6. 查看分析结果
-- 查看所有变更记录（sql_redo：执行语句，sql_undo：回滚语句） select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents; -- 筛选特定表的变更记录 select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents where seg_owner='TEST' and seg_name='TEST01'; 7. 结束分析
exec dbms_logmnr.end_logmnr; 八、RMAN备份与恢复
RMAN（Recovery Manager）是Oracle专业的备份恢复工具，支持全库备份、归档日志备份、数据文件备份等。
1. 核心备份命令
-- 全库备份（指定标签、备份路径） RMAN> backup tag itpux_db01_fullbak format '/backup/full/itpux_db01_full_%s_%p_%t' (database); -- 归档日志备份 RMAN> backup tag itpux_db01_arch format '/backup/arch/itpux_db01_arch_%s_%p_%t' archivelog all; -- 数据文件备份（指定数据文件号） RMAN> backup tag LLL1_file format '/oracle/backup/full/LLL1_datafile_db01_%s_%p_%t_%c' (datafile 1,2,3); -- 控制文件备份 RMAN> backup tag LLL1_ctl format '/oracle/backup/full/LLL1_ctl_db01_%s_%p_%t_%c' (current controlfile); -- 参数文件（spfile）备份 RMAN> backup tag LLL1_pfile format '/oracle/backup/full/LLL1_pfile_db01_%s_%p_%t_%c' (spfile); 2. 备份脚本参考
可通过编写RMAN脚本实现自动化备份，示例脚本可参考：RMAN自动化备份脚本
九、日志与闪回功能
1. 查看undo数据量
undo数据用于事务回滚与读一致性，查询近期undo生成量：
ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss'; SELECT begin_time, end_time, (undoblks * (SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')) undo_bytes -- 总undo字节数 FROM v$undostat; 2. 闪回日志与还原点
闪回功能基于归档日志与闪回日志，支持数据库、表级别的时间点恢复，前提是数据库处于归档模式。
-- 1. 查看归档模式 SQL> archive log list; -- 2. 开启/关闭闪回功能 SQL> alter database flashback on; SQL> alter database flashback off; -- 3. 查看闪回状态 SQL> select flashback_on from v$database; -- 4. 创建还原点（正常还原点） SQL> CREATE RESTORE POINT restorepoint1; -- 5. 创建担保还原点（用于drop后恢复，建议mount模式下创建） SQL> create restore point before_drop guarantee flashback database; -- 6. 查看还原点 SQL> select * from V$RESTORE_POINT; -- 7. 数据库闪回（需mount模式，闪回后resetlogs打开） SQL> shutdown immediate; SQL> startup mount; SQL> FLASHBACK database TO RESTORE POINT restorepoint1; -- 按还原点闪回 SQL> FLASHBACK DATABASE TO SCN 1121679; -- 按SCN闪回 SQL> FLASHBACK DATABASE TO TIMESTAMP to_date('2025-11-23 10:00:00','YYYY-MM-DD HH24:MI:SS'); -- 按时间闪回 SQL> alter database open resetlogs; -- 8. RMAN模式下闪回 RMAN> FLASHBACK DATABASE TO SCN=1121679; RMAN> FLASHBACK DATABASE TO TIME ="TO_DATE('2025-11-23 10:00:00','YYYY-MM-DD HH24:MI:SS')"; 十、表空间管理
表空间是Oracle数据存储的逻辑单位，本节涵盖表空间查询、权限分配与创建。
1. 查询大字段表与占用空间
-- 查询NCC用户下USERS表空间中含大字段（LOB）的表，按占用空间降序 select a.owner,a.table_name,a.column_name,a.tablespace_name,bytes/1024/1024/1024 as GB from all_lobs a,dba_segments b where a.owner='NCC' and a.tablespace_name='USERS' and a.segment_name=b.segment_name order by b.bytes desc; 2. 表空间容量统计（核心查询）
查询表空间总容量、已用容量、空闲容量及使用率，适配自动扩展场景：
select a.tablespace_name, round(maxtotal,4) as maxtotal_GB, -- 总容量（含自动扩展最大空间） round(total,4) as extended_Total_GB, -- 已扩展容量 round((total-free),4) as usage_GB, -- 已用容量 round((maxtotal-total+free),4) as free_GB, -- 实际空闲容量 round((1-(total-free)/maxtotal)*100,4)||'%' as free_percent -- 空闲率 from (select tablespace_name, SUM(bytes)/1024/1024/1024 as total, SUM(case autoextensible when 'YES' then maxbytes else bytes end )/1024/1024/1024 as maxtotal from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes)/1024/1024/1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by free_percent desc; 3. 授予用户表空间权限
-- 授予mes用户test_tbsp表空间无限制使用权限 alter user mes quota unlimited on test_tbsp; 4. 表空间创建（常用格式）
create tablespace MES_PRD datafile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.dbf' -- 数据文件路径 size 4000M autoextend on next 500M maxsize unlimited -- 初始4G，自动扩展每次500M，无上限 uniform size 1M -- 区大小1M logging -- 开启日志 extent management local -- 本地管理表空间 segment space management auto; -- 自动段空间管理 十一、SQL调优顾问（SQL Tuning Advisor）
SQL调优顾问是Oracle内置的性能优化工具，可自动分析SQL语句，提供索引创建、SQL重写、Profile绑定等优化建议。
1. 自动SQL调优任务（SYS用户）
-- （1）查看自动调优任务是否开启 select * from dba_autotask_client; -- 查看sql tuning advisor状态 -- （2）查看自动调优任务 select * from dba_advisor_tasks where task_name = 'SYS_AUTO_SQL_TUNING_TASK'; -- （3）查看任务参数（如执行时间限制） select * from dba_advisor_parameters where task_name like 'SYS_AUTO_SQL_TUNING%' and (parameter_name like '%TIME_LIMIT%'); -- （4）修改执行时间限制 -- 单条SQL最大分析时间（3小时） begin dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 10800); end; / -- 整个任务最大执行时间（10小时） begin dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 36000); end; / -- （5）手动执行自动调优任务 exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK'); -- （6）取消正在执行的任务 exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK'); -- （7）查看优化建议报告 set linesize 80 pagesize 0 long 100000 select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual; -- （8）生成优化建议SQL脚本 set linesize 132 pagesize 0 long 100000 select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual; 2. 手动调优单个SQL（按SQL_ID）
-- （1）获取目标SQL的SQL_ID（从v$sql或AWR报告中查询） select SQL_TEXT, SQL_FULLTEXT, SQL_ID from v$sql where SQL_TEXT like 'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%'; -- （2）创建调优任务（按SQL_ID） declare tune_task varchar2(30); begin tune_task := DBMS_SQLTUNE.create_tuning_task( sql_id =>'26vvbvs4mawf1', -- 替换为实际SQL_ID task_name => 'test_sql_tuning' ); end; / -- （3）开启会话跟踪（可选，用于获取详细日志） oradebug setmypid; oradebug tracefile_name; -- 记录跟踪文件路径 -- （4）执行调优任务 exec dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning'); -- （5）查看任务状态（COMPLETED表示完成） SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_sql_tuning'; -- （6）关闭跟踪 oradebug close_trace; -- （7）查看优化建议 set long 1000000 longchunksize 1000000 set linesize 300 pagesize 0 select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual; -- （8）删除调优任务 BEGIN DBMS_ADVISOR.DELETE_TASK(task_name => 'test_sql_tuning'); END; / 3. 调优并绑定SQL Profile（优化执行计划）
-- （1）创建调优任务（SYS用户） variable sta_task VARCHAR2(100); exec :sta_task:=dbms_sqltune.create_tuning_task(sql_id=>'2k22yt1p1ckbr',time_limit=>72000,task_name=>'MyTask1'); -- （2）执行任务 exec dbms_sqltune.execute_tuning_task(task_name=>'MyTask1'); -- （3）查看优化报告 select dbms_sqltune.report_tuning_task('MyTask1') from dual; -- （4）接受建议，创建SQL Profile（绑定优化执行计划） execute dbms_sqltune.accept_sql_profile( task_name => 'MyTask1',task_owner =>'sys', replace => TRUE); -- （5）查看SQL Profile select a.name, a.task_id, a.created from dba_sql_profiles a, dba_advisor_log b where a.task_id = b.task_id and b.task_name = 'MyTask1'; -- （6）删除SQL Profile exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_017acc4af5ea0001'); -- 替换为实际Profile名称 -- （7）删除调优任务 exec dbms_sqltune.drop_tuning_task(task_name => 'MyTask1'); 十二、SQL语句跟踪
通过跟踪SQL执行过程，获取执行计划、绑定变量、耗时等信息，是SQL调优的核心手段。
1. 按SID和Serial#跟踪会话
-- （1）获取操作系统PID（Linux） ps -ef|grep LOCAL=NO -- 查找目标用户的进程号（如24696） -- （2）获取会话SID和Serial# select s.SID,s.serial#,s.paddr,s.username,s.status,s.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=24696; -- 替换为实际PID -- （3）开启SQL跟踪 execute dbms_monitor.session_trace_enable(session_id =>191,serial_num => 319,plan_stat => 'all_executions'); -- （4）确认跟踪状态 select s.SID,s.serial#,s.sql_trace from v$session s where sid=191; -- （5）前端执行目标操作（如查询、更新） -- （6）生成可读报告（tkprof工具） tkprof mesorcl_ora_24696.trc test.log sys=no; -- 跟踪文件→可读日志 -- （7）关闭跟踪 exec dbms_monitor.session_trace_disable(session_id =>191 , serial_num =>319 ); -- （8）确认关闭 select s.SID,s.serial#,s.sql_trace from v$session s where sid=191; 2. 系统级跟踪
-- （1）开启系统级跟踪（sys用户） alter system set sql_trace=true; -- （2）跟踪文件路径 $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace -- （3）生成可读报告 tkprof mesorcl_ora_8452.trc test2.log sys=no; -- （4）关闭系统级跟踪 alter system set sql_trace=false; 3. 获取最新执行的SQL
-- 按执行时间降序，查看最近执行的SQL select a.machine,a.osuser,b.sql_text,b.sql_id,b.LAST_LOAD_TIME,a.prev_exec_start from v$session a,v$sql b where a.prev_sql_id=b.sql_id order by b.last_load_time desc; 十三、游标管理
游标是Oracle执行SQL语句的内存结构，合理配置游标参数可避免软解析，提升性能。
1. 游标相关查询
-- （1）当前所有会话使用的游标数与最大允许值 select sum(a.value) as current_cursors, b.name, c.value as max_open_cursor from v$sesstat a, v$statname b, v$parameter c where a.statistic# = b.statistic# and b.name = 'opened cursors current' and c.name= 'open_cursors' group by b.name, c.value; -- （2）使用游标数最多的会话 select max(a.value) as max_cursors, b.name from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current' group by b.name; 2. 游标核心参数
open_cursors：每个会话最多同时打开的游标数（默认500，建议根据业务调整）。session_cached_cursor：每个会话最多缓存的已关闭游标数（默认50，缓存游标可避免重复软解析）。
十四、死锁处理
死锁是并发场景下的常见问题，需通过查询死锁进程、强制终止会话解决。
-- （1）查询死锁对应的SQL语句 select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)); -- （2）查找死锁会话信息 select s.username, l.object_id, l.session_id sid, s.serial#, l.oracle_username, l.os_user_name, l.process from v$locked_object l, v$session s where l.session_id = s.sid; -- （3）kill死锁会话（替换为实际sid和serial#） SQL> alter system kill session '191,319'; -- （4）若kill会话失败，直接杀操作系统进程 -- 第一步：获取操作系统进程号 SQL> select pro.spid from v$session ses, v$process pro where ses.sid=191 and ses.paddr=pro.addr; -- 第二步：操作系统层面kill进程（Linux） $ kill -9 24696 -- 替换为实际spid 十五、自动维护任务管理
Oracle自动维护任务包括统计信息收集、空间顾问、SQL调优顾问，可按需启用/禁用。
1. 查看自动维护任务
select * from dba_autotask_client; -- 核心任务： -- auto optimizer stats collection：自动统计信息收集 -- auto space advisor：自动空间顾问 -- sql tuning advisor：自动SQL调优顾问 2. 启用/禁用所有任务
-- 禁用所有自动维护任务 begin EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE; end; / -- 启用所有自动维护任务 begin EXECUTE DBMS_AUTO_TASK_ADMIN.enable; end; / 3. 启用/禁用特定任务
-- 禁用SQL调优顾问 BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / -- 启用SQL调优顾问 BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / 4. 维护窗口管理
-- （1）修改维护窗口属性（如修改周五窗口开始时间为11:40） BEGIN dbms_scheduler.disable(name => 'FRIDAY_WINDOW'); -- 先禁用 dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily;byday=FRI;byhour=11;byminute=40; bysecond=0' ); dbms_scheduler.enable(name => 'FRIDAY_WINDOW'); -- 启用 END; / -- （2）创建新维护窗口 BEGIN dbms_scheduler.create_window( window_name => 'EARLY_MORNING_WINDOW', duration => numtodsinterval(1, 'hour'), -- 持续1小时 resource_plan => 'DEFAULT_MAINTENANCE_PLAN', repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0'); dbms_scheduler.add_window_group_member( group_name => 'MAINTENANCE_WINDOW_GROUP', window_list => 'EARLY_MORNING_WINDOW'); END; / -- （3）删除维护窗口 BEGIN DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER( group_name => 'MAINTENANCE_WINDOW_GROUP', window_list => 'EARLY_MORNING_WINDOW'); DBMS_SCHEDULER.DROP_WINDOW(window_name => 'EARLY_MORNING_WINDOW'); END; / 十六、其他常用操作
1. orapwd工具（创建密码文件）
orapwd file=/oracle/app/oracle/product/12.2/db_1/dbs/orapwkssyorclprd password=1qaz%2WSX force=y -- file：密码文件路径，需包含实例名（如kssyorclprd） -- force=y：覆盖已存在的密码文件 2. Oracle官方文档查询
通过百度搜索Oracle官方文档，精准定位关键字：
site:docs.oracle.com v$database -- 搜索v$database视图文档 site:docs.oracle.com dba_scheduler_windows -- 搜索维护窗口文档 3. 查看统计信息收集时间
-- 查看表的最新统计信息收集时间 SQL> select table_name, LAST_ANALYZED from dba_tables where owner='MESPRD'; -- 查看索引的最新统计信息收集时间 SQL> select index_name, LAST_ANALYZED from dba_indexes where owner='MESPRD'; 总结
本文涵盖Oracle数据库从基础操作到高级运维的核心场景，所有SQL语句均经过实战验证，适用于Oracle 11g/12c/19c等主流版本。使用时需注意：
- 高危操作（如truncate、drop、kill session、闪回数据库）需提前备份数据，避免数据丢失；
- 权限相关操作需以sys或system用户执行，普通用户需具备对应权限；
- 调优、日志挖掘等操作建议在测试环境验证后，再应用到生产环境。
