图灵小队—Oracle常用SQL语句

2021年9月6日 1606点热度 0人点赞 0条评论

一、关于实例级别
二、关于表操作：
三、关于权限和角色
四、关于约束和索引
五、关于锁
- 1、锁-行级锁：
- 2、锁-表级锁：
六、关于进程
- 1、会话，进程：
- 2、ORACLE实例的后台进程
七、关于日志挖掘——关于补充日志和logminer数据挖掘
八、关于rman及脚本
- 1、rman备份功能
- 2、脚本
九、关于日志
- 1、查看近来数据库生成的撤销数据量（undo)：
- 2、闪回日志恢复功能、还原点创建功能
十、关于表空间查询脚本
十一、关于SQL 调优顾问的使用
十二、跟踪SQL语句
十三、关于游标
- 1、关于游标及游标缓存
十四、关于死锁
- 1、死锁无法杀掉问题解决
十五、关于自动优化器
十六、其他
- 2、百度直接搜索orcle文档
- 3、查看表、索引等统计信息最新的统计时间

一、关于实例级别

1、查看oracle进程用户信息

SELECT *
FROM v$session
WHERE paddr IN (SELECT addr FROM v$process WHERE spid = 423565);

2、关于系统级别

SQL> spool /data/out.log   --spool 执行后，会输出spool之后所有的sql语句，到操作系统中的指定文件
SQL> spool  off;           --关闭spool输出
SQL> show error;           --上一次执行sql，出现的错误
SQL> host ls -lt           --查看系统操作
SQL> @1.sql                --执行脚本

3、set命令

SQL>set time on;
SQL>set timing on;
SQL>set autotrace on;
SQL>set autotrace off;
SQL>set linesize 200;
SQL>set pagesize 10000;
SQL>set long 10000;

4、关于时间格式

SQL> select sysdate from dual;                                            --dual是一张神奇的表
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;             --将系统时间以年月日方式显示
SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ssxff') from dual;     --将系统时间以年月日展示且秒显示到小数点后6位
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;                      --2020-02-26——把数值转换为字符
SQL> select to_number('123') from dual;                                   --123——字符转换为数值
SQL> select to_date('20211112','yyyymmdd') from dual;                     --12-NOV-21——把数值转换为日期

二、关于表操作：

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';                                --更改当前会话的时间显示格式（30-AUG-19)为20190830，退出当前sql会话后，自动回复30-AUG-19格式 
SQL> select * from student where sname is null;—                                  --查询当前表中空值的列，不能使用sname = null，需要使用sname is null
SQL>create table student2 as select * from student;                               --复制表（复制student表所有结构及内容到student2） 
SQL>create table student3 as select * from student where 1>2;                     --复制表结构，不复制内容 
SQL>truncate table student4;                                                      --清空表数据，表结构不变，清空时不写入日志，所以无法还原，慎用 
SQL>delete table student4;                                                        --删除表数据，清空时，写入日志，可以还原 
SQL>select * from student2 where  upper(sname)='A';                               --查找sname为大写的值 
SQL>select * from student2 where sname like 'A%';                                 --查询列sname中以A开头的 
SQL>select * from student2 where length(sname)=4;                                 --查询sname中长度为4的行， 
SQL>select * from student2 order by sno desc;                                     --按sno降序插叙 
SQL>select * from student2 order by sno asc,birthday;                             --别名使用，如果中间没有空格，可以直接使用别名；如果别名中间有空格，需要加双引号 
SQL>select sno,sum(score) from Transcript group by sno;                           --查出每个学生的成绩总和
SQL>select km,avg(score) from Transcript group by km;                             --查询每门课程（KM）的平均分数
SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>60);     --查询（按照sno分组，即针对每个人），平均分数大于60的学生的学号和平均成绩
SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>(select avg(score) from Transcript));  --查询（按照sno分组，即针对每个人），平均分数大于所有学生平均成绩的学生的学号和平均分数
SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>60) order by sno desc;                 --查询（按照sno分组，即针对每个人），平均分数大于60的学生的学号和平均成绩，并按照学号的降序排列（说明了几个谓词的顺序）
SQL>insert into student(sno,sname) values(4,'sunliu');

2、关于多表查询：

SQL> select s.sno,s.sname,a.sno,a.zz from student s inner join address a on s.sno=a.sno;        --连接表方法1
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno;                --连接表方法2
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno(+);             --左外连接1
SQL> select s.sno,s.sname,a.sno,a.zz from student s left outer join address a on s.sno=a.sno;   --左外连接2
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno(+)=a.sno; 右          --外连接1
SQL> select s.sno,s.sname,a.sno,a.zz from student s right outer join address a on s.sno=a.sno;  --右外连接2
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:

from——where——group by——having——select——order by

SQL>select * from student where exists(select * from address where zz='zhengzhou');              --EXISTS的使用
SQL>select xm,count(*) from student2 group by xm;                                                --group by
QL>select * from student2 where xm in(select xm  from student2 group by xm having(count(*)>1));  --having
SQL>select a.*,rowid from student6 a;                                                            --rowid的应用

三、关于权限和角色

1、权限--用户及参数：

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;                                              --把用户的参数文件改为新的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;                                  --授予用户connect权限
SQL>GRANT RESOURCE TO test;                                 --授予用户resource权限
SQL>GRANT create table to test；                            --授予用户创建表权限
SQL>GRANT UNLIMITED TABLESPACE to test;                     --授予用户无限制表空间访问
SQL>grant create any table to test;                         --授予创建任何表的用户
SQL>select * from dba_sys_privs d where d.grantee='TEST1';  --查看用户拥有的权限
SQL> grant create session to test2 with admin option;       --权限继承(GRANT 最后增加with admin option）
SQL>REVOKE create session FROM test1;                       --收回权限

3、权限--对象权限：

SQL>revoke select on scott.student from test2               --收回对象权限
SQL>GRANT SELECT ON SCOTT.EMP TO test1;                     --允许用户查询 EMP 表的记录
SQL>GRANT UPDATE ON SCOTT.EMP TO test1;                     --允许用户更新 EMP 表中的记录
SQL>GRANT ALL ON SCOTT.EMP TO test1;                        --允许用户插入、删除、更新和查询EMP 表中的记录
SQL>GRANT SELECT ON SCOTT.EMP TO test1 with grant option;   --继承grant 对象权限（9i以及之前是无此功能的）

4、角色管理：

SQL>CREATE ROLE role_name;                                             --创建角色
SQL>grant role1 to test2;                                              --将角色给用户
SQL>grant create session ,create any table, drop any table to role1;   --授予角色权限
SQL>select * from dba_sys_privs d where d.grantee='CONNECT';           --查询用户具有的系统权限
SQL>select * from dba_roles;                                           --查看有多少角色
SQL>select * from role_sys_privs;                                       --查看当前用户的角色具有的系统权限
SQL>select * from dba_sys_privs d where d.grantee='TEST1';              --查看用户具有的系统权限：（system或sys用户查询）
SQL>select * from dba_tab_privs where rownum<20 and grantee='DBA';      --查看角色具有的对象权限；
SQL>select * from dba_tab_privs where rownum<20 and grantee='TEST1';    --查看用户具有的对象权限；
SQL>select * from dba_tab_privs where rownum<20 and grantee='TEST1';    --更改用户口令，删除用户
SQL> alter user test1 identified by test1；                             --更改用户口令
SQL> drop user test1 cascade;                                           --删除用户

四、关于约束和索引

1、完整性约束：

SQL> create table yg2(bh number not null,xm varchar2(8));                                                    --查询约束（只有一个not null约束，名称是系统根据规则随机产生
SQL> create table yg3(bh number(4) not null check (bh>0 and bh<10000), xm varchar2(8));                      --check约束
SQL> create table yg4(bumenID number not null,bumenneiID number,xm varchar2(8),unique(bumenID,bumenneiID));  --unique约束（会自动创建索引）
SQL> create table gz_newYG(bh number(4),xm varchar2(8),gz number(10) default 1000 );                         --default约束
SQL>create table student(xh number(6) constraint code_pk primary key , xm varchar2(20));                     --primary key示例(此列不允许为空，而且ORACLE会自动为主键列创建索引，这里的 primary key约束由用户显式起名)：
SQL>create table address (xh number(8) ,zz varchar2(50) , foreign key(xh) references student(xh));           --foreign key示例(实现两个表之间参照与被参照的关系，外键只能取主键已经有的值，这里的foreign key由系统自动起名):
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;

五、关于锁

1、锁-行级锁：

SQL>select * from emp for update;                                             --锁定emp表，不可以update,但可以insert into;
SQL>select * from v$lock;                                                     --查看视图，TM表级锁，TX行级锁
SQL>select ADDR,KADDR,SID,TYpe,ID1 from v$lock where type='TM'or type='TX';
SQL>select ... for update wait 5；                                            --锁定一张表或某几行，只等待5秒钟，如果超过5秒钟仍被其他人占用锁定，就不进行等待了
SQL>select ... for update nowait ；                                           --锁定一张表或某几行，等待，被其他人占用锁定，就返回错误。
SQL>rollback;                                                                 --解锁

2、锁-表级锁：

SQL>lock table emp in row share mode;                                         --行级共享
SQL>lock table emp in share mode;                                             --共享锁
SQL>lock table epm in row exclusive mode;                                     --行级排他锁
SQL>lock table emp in share row exclusive mode;                               --共享航机排他锁
SQL>lock table emp in exclusive mode                                          --排他锁

六、关于进程

1、会话，进程：

SQL> select username from v$session where username='SCOTT';  --查看用户SCOTT的会话数
SQL>select * from v$procession;                              --sys或system可以查看
SQL>disconnect                                               --断开连接；

2、ORACLE实例的后台进程

SQL>show paramter db_wr;                                                      --查看DBWR的数量【只有sys用户可以查看】
SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%DBW%';   --查看DBWR的进程
SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%LGW%';   --查看LGWR的进程
SQL> show parameter statistics;

七、关于日志挖掘——关于补充日志和logminer数据挖掘

1、开启补充日志

在需要分析的数据库中开启补充日志。

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
alter database add supplemental log data;

2、设置一个字典文件路径（需要重启 DB）：

show parameter utl_file_dir     
alter system set utl_file_dir='/oracle' scope=spfile;

3、创建一个数据字典文件

exec dbms_logmnr_d.build('dict.ora','/oracle');

4、建立日志分析列表

（1）添加第一个日志

execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.new);

（2）继续添加日志

execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile);

（3）移除日志

execute dbms_logmnr.remove_logfile(logfilename=>'日志文件');

5、启动分析

（1）无条件分析

exec dbms_logmnr.start_logmnr(DictFileName =>'/oracle/dict.ora');

（2）有条件分析

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('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime=> to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');

（3）可以使用COMMITTED_DATA_ONLY参数来只显示那些被提交的事务：

EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

（4）有条件分析的选项：

scn:  startscn,endScn 
time: starttime,endtime

6、查看日志分析结果

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、结束分析

dbms_logmnr.end_logmnr;

八、关于rman及脚本

1、rman备份功能

（1）RMAN> backup tag itpux_db01_fullbak format '/backup/full/itpux_db01_full_%s_%p_%t' (database);    --手动备份数据库全库
（2）RMAN> backup tag itpux_db01_arch format '/backup/arch/itpux_db01_arch_%s_%p_%t' archivelog all;   --手动备份归档日志
（3）备份脚本：rman备份脚本
（4）备机各种单句
backup tag LLL1_full format '/oracle/backup/full/LLL1_full_db01_%s_%p_%t_%c' (database); 
backup tag LLL1_file format '/oracle/backup/full/LLL1_datafile_db01_%s_%p_%t_%c' (datafile 1,2,3); 
backup tag LLL1_ctl format '/oracle/backup/full/LLL1_ctl_db01_%s_%p_%t_%c' (current controlfile); 
backup tag LLL1_pfile format '/oracle/backup/full/LLL1_pfile_db01_%s_%p_%t_%c' (spfile);

2、脚本

见：https://www.topunix.com/post-6082.html

九、关于日志

1、查看近来数据库生成的撤销数据量（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
FROM v$undostat;

2、闪回日志恢复功能、还原点创建功能

（1）SQL>archive log list;                                               --闪回前提，必须要归档模式
（2）SQL> alter database flashback on/off                                --闪回启停
（3）SQL> select flashback_on from v$database;                           --闪回查询： 
（4）SQL> CREATE RESTORE POINT restorepoint1;                            --正常还原点的创建： 
（5）SQL> select * from dictionary where table_name like '%RESTORE%';    --从字典查看还原点信息 
（6）SQL> select * from V$RESTORE_POINT;                                 --查看还原点： 
（7）SQL> FLASHBACK database TO RESTORE POINT restorepoint1;             --闪回值还原点：
（8）SQL> FLASHBACK  DATABASE  [<database_name>] TO SCN <system_change_number>            --sqlplus下基于SCN 闪回
（9）SQL> FLASHBACK  DATABASE  [<database_name>] TO  TIMESTMP <system_timestamp_value>    --sqlplus下基于时间戳闪回
（10）SQL> FLASHBACK DATABASE [<database_name>] TO RESTORE   POINT  <restore_point_name>  --sqlplus下基于还原点闪回
（11）RMAN> FLASHBACK DATABASE TO SCN=1121679;                                                      --RMAN 下基于SCN闪回
（12）RMAN> FLASHBACK DATABASE TO TIME ="TO_DATE('2013-10-10 19:25:21','YYYY-MM-DD HH24:MI:SS')";   --RMAN 下基于时间闪回
（13）RMAN> FLASHBACK DATABASE TO SEQUENCE=56 THREAD=1;                                             --RMAN 下基于归档序号
（14）SQL> create restore point before_drop guarantee flashback database;                           --担保还原点的创建（建议mount下）
（15）SQL>alter database open read only;                                                            --建议更改数据库先为只读，确认好了后，在resetlogs打开，否则前面的保存点就无法再使用了。

十、关于表空间查询脚本

1、查询大字段的表，查询用户下最大表占用空间

select a.owner,a.table_name,a.column_name,a.tablespace_name,bytes/1024/1024/1024 
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、常用表空间查询语句（LLL总结）

查询最大表空间容量、已扩展在用容量、已使用容量、空闲容量、使用百分比

解析：表空间容量定义

--表空间的总容量等于所有数据文件的maxbytes（自动扩展)+bytes（非自动扩展) ，定义为maxtotal(数据来源：dba_data_files)

--表空间的扩展的容量等于所有数据文件的bytes之和，定义为total (数据来源:dba_data_files)

--表空间的已扩展的容量中有一部分空闲容量，定义为free（数据来源:dba_free_space中的free容量）

--表空间的实际空闲容量等于maxtotal-total+free的容量

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+maxbytes 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、对某个用户授予某个表空间的访问权限

alter user mes quota unlimited on test_tbsp;

4、创建表空间格式常用（LLL总结）

create tablespace MES_PRD 
datafile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.dbf' 
size 4000M autoextend on next 500M maxsize unlimited 
uniform size 1M logging extent management local segment space management auto;

十一、关于SQL 调优顾问的使用

1、手动执行sys下的SQL_TUNING_TASK任务并调优

（1）查看clientname为sql tuning advisor是否开启

select * from dba_autotask_client;

如果开启，就会随下述窗口执行

select * from dba_scheduler_windows;

（2）查看sql优化任务，对应上述优化建议

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%' or parameter_name like '%TIME_LIMIT%');

默认自动优化任务是1200秒（20分钟）。如果超时了，就会自动停止。

（4）修改优化执行时间

①每条sql最大时间--Time limit per statement in a SQL Tuning Set

设置10800s=3小时。

begin
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 10800);
end;

②整个分析任务执行时间--The maximum time that an analysis can execute

设置36000s=10小时。

begin 
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 36000); 
end;

（5）手动执行sql优化任务

SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK');

（6）取消正在进行的sql_tuning任务

 exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK');

（7）查看优化建议

set linesize 80 pagesize 0 long 100000  --注意，此句必须要加上，否则下面显示不出来，
select dbms_auto_sqltune.report_auto_tuing_task from dual;

或:

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）

登录访问用户账号，如:mes_prd

（1）获取sql_id

从v$sql或awr中（通过开发提供的开发语句，或awr慢的语句）

select SQL_TEXT, SQL_FULLTEXT, SQL_ID
from v$sql
where SQL_TEXT like
'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%';

或者通过如下方法：

①获取确定登录用户的系统进程，

ps -ef|grep LOCAL=NO --比如新登录的，则进程号最大，如：

②再通过如下语句得到sql_id

select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid,s.sql_id 
from v$session s,v$process p 
where s.paddr=p.addr and p.spid=17408;

（2）创建调优任务

①方法1：根据sql_id

declare
tune_task varchar2(30);
tune_sql clob;
begin
tune_task :=DBMS_SQLTUNE.create_tuning_task(
  sql_id =>'26vvbvs4mawf1',
  task_name => 'test_sql_tuning'
);
end;

②方法2：根据sql_text

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'select b.logtime, a.name, a.cardid, b.amount, b.goods,b.score
                 from lll_member a, lll_sales b
                 where a.region  = ''SDS''
                 and a.recommend = ''LX''
                 and b.logtime   > to_date(''2016-12-15'', ''yyyy-mm-dd'')
                 and a.cardid    = b.cardid
                 order by b.logtime';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                 sql_text    =>my_sqltext, 
                 user_name   => 'MES_PRD',  
                 scope       =>'COMPREHENSIVE',  
                 time_limit  => 600, 
                 task_name   =>'test_sql_tuning',
                 description => 'Task to tune a query on a specified table');
END;
/

--SQL 单引号变双引号

（3）登录sql，获取当前会会话的跟踪文件

 oradebug setmypid
 oradebug tracefile_name
 获取到文件:/oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_ora_75586.trc

（4）手动运行SQL调优顾问

exec dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning');

（5）查看任务执行状态（完成/执行中/异常）

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;

或查看上述文件

less  /oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_ora_75586.trc

（8）删除任务

BEGIN
DBMS_ADVISOR.DELETE_TASK(
task_name => 'test_sql_tuning'
);
END;

（9）查看执行进度

-- 查看SQL Tuning Advisor的进展（task执行很久）---好像执行完成才能看到？
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';

3、SQL自动优化步骤--方法2-流程和2一样，但多了profile建议

（1）根据sql查询sqlid

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创建一个STA（SQL Tuning advisor）分析任务（使用SYS用户执行）

variable sta_task VARCHAR2(100);
exec :sta_task:=dbms_sqltune.create_tuning_task(sql_id=>'2k22yt1p1ckbr',time_limit=>72000,task_name=>'MyTask1');

（3）执行上面创建的STA（需要一定的时间）

exec dbms_sqltune.execute_tuning_task(task_name=>'MyTask1');

（4）查询优化任务创建与执行的情况

select * from dba_advisor_tasks a, dba_advisor_log b
where a.task_id = b.task_id
and a.task_name = 'MyTask1';

（5）--查看任务优化报告（优化的详细内容）

select dbms_sqltune.report_tuning_task('MyTask1') from dual;
---select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;

（6）接受建议的 SQL 概要文件,即创建SQL_Profle

execute dbms_sqltune.accept_sql_profile(
task_name => 'MyTask1',task_owner =>'sys', replace => TRUE);

（7）查看第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';

（8）再次执行SQLID为‘dza1z9nnup2wb’的语句

--执行时间由原来的50秒降为1秒
--查看执行计划，执行计划中包含下面信息，说明是使用了创建的SQL_Profile及索引所起到的效果

（9）删除SQL_Profile（删除执行计划）

exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_017acc4af5ea0001');

（10）删除优化任务

exec dbms_sqltune.drop_tuning_task(task_name => 'MyTask1');

----------------------------------------------利用提前建立的存储过程-------------------------------------------
create or replace procedure p_create_sqltuning_task(p_sql_id varchar2) is
v_tuning_task varchar2(30);
（STA）根据sql语句查询sqlid后进行STA分析
2021年7月22日 13:33
分区 数据库性能调整 的第 1 页 
v_tuning_task varchar2(30);
v_sql_id v$session.sql_id%type;
begin
v_sql_id := p_sql_id;
v_tuning_task := dbms_sqltune.create_tuning_task(sql_id => v_sql_id);
dbms_sqltune.execute_tuning_task(v_tuning_task);
dbms_output.put_line('This Tuning task name is : ' || v_tuning_task);
dbms_output.put_line('-------------Please using follow command query SQL tuning report!------------');
dbms_output.put_line('set linesize 200 pagesize 9999');
dbms_output.put_line('set long 100000');
dbms_output.put_line('select dbms_sqltune.report_tuning_task(''' ||
v_tuning_task || ''') from dual;');
end;
--查询sqlid
select SQL_TEXT, SQL_FULLTEXT, SQL_ID
from v$sql
where SQL_TEXT like
'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%'
--执行存储过程获取STA建议
SYS@racdb> exec p_create_sqltuning_task(' 2k22yt1p1ckbr')
This Tuning task name is : TASK_76380
-------------Please using follow command query SQL tuning report!------------
set linesize 200 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_76380') from dual;
PL/SQL procedure successfully completed.
--查看STA建议
set linesize 500 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_76556') from dual;
--删除任务
exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_76380');
exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_%');

十二、跟踪SQL语句

1、根据根据sid和serial#跟踪语句

（1）获取当前用户登录mes时，操作系统的PID，通过新增查看。

ps -ef|grep LOCAL=NO --24696

（2）获取登录用户的sid和searial#

select * from v$session where username='MES_PRD';

（3）进一步根据pid确认sid和searial#--sid=191,serial#=319

select s.SADDR,SID,s.serial#,s.paddr,s.username,s.status,s.machine,s.program,s.prev_SQL_ID,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=25608;

（4）开启SQL跟踪

execute dbms_monitor.session_trace_enable(session_id =>191,serial_num => 319,plan_stat => 'all_executions');

（5）确认会话跟踪

select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=24696;

（6）跟踪目录$LOG获取

mesorcl_ora_24696.trc

（7）前端执行查看--记工查询

（8）tkprof分析trc

tkprof mesorcl_ora_24696.trc test.log sys=no

（9）关闭跟踪

exec dbms_monitor.session_trace_disable(session_id =>191 , serial_num =>319 );

也可以关闭所有会话跟踪

exec dbms_monitor.session_trace_disable();

（10）确认关闭跟踪

select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=24696;

2、设置系统跟踪查看

（1）登录sys，设置追踪打开

alter system set sql_trace=true;

（2）查看目录

$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace

（3）找到最新的

mesorcl_ora_8452.trc等

（4）生成可读文档

tkprof mesorcl_ora_25624.trc test2.log sys=no

3、获取最新的sql执行语句

（1）方法1：

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;

十三、关于游标

1、关于游标及游标缓存

（1）当前所有session使用的游标数和可以打开的最大游标打开数

select sum(a.value), b.name,c.value 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 ;

（2）当前session中，使用游标数最大色session会话

select max(a.value),b.name from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current' group by b.name;

（3）V$OPEN_CURSOR，

当前Session缓存的游标，可能包含已经打开的缓存和关闭的缓存。但不是打开的正在使用的游标

V$OPEN_CURSOR中显示的当前Session游标缓存中游标，如果要精确查询当前Session打开的游标总数，需要从V$ SESSTAT中查询。

（4）$SESSION_CACHED_CURSOR

当前Session已经关闭并被缓存的游标。

（5）关于参数open_cursor和session_cached_cursor

open_cursors：每个session（会话）最多能同时打开cursor（游标）数

session_cached_cursor：每个session（会话）最多可以缓存已关闭cursor（游标）数

若要理解参数open_cursors和session_cached_cursor的作用，最好先弄清楚oracle是如何执行每个sql语句的。

通过上图，可以看出两点：

两个参数之间没有任何关系，相互之间不会有任何影响。
两个参数有着相同的作用：让后续相同的sql语句不再打开游标，从而避免软解析过程来提供应用程序的效率。

十四、关于死锁

1、死锁无法杀掉问题解决

（1）用dba用户执行以下语句，可以查看到被死锁的语句

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）查找死锁的进程：

sqlplus "/as sysdba"
select s.username,
l.object_id,
l.session_id sid,
s.serial# 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）通过SQL kill 进程：

SQL>alter system kill session 'sid,serial#';

（4）如果还不能解决（直接杀系统进程）：查到这个进程

SQL>select pro.spid from v$session ses, v$process pro where ses.sid=<sid号> and ses.paddr=pro.addr;

$kill -9 pro.spid

十五、关于自动优化器

1、查看自动维护任务

SQL>select * from dba_autotask_client;

auto optimizer stats collection --自动优化器统计收集
auto space advisor              --自动分段顾问
sql tuning advisor              --自动SQL调整顾问

2、启用或禁用所有自动维护任务的所有窗口

（1）禁用

begin
  EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;
end;
/

（2）启用

begin
  EXECUTE DBMS_AUTO_TASK_ADMIN.enable;
end;
/

3、启用或禁止某个自动维护任务的所有窗口

（1）如：禁用所有的sql tuning advisor任务

BEGIN
 dbms_auto_task_admin.disable(
 client_name => 'sql tuning advisor',
 operation => NULL,
 window_name => NULL);
END;

（2）启用所有的sql tuning advisor任务（如：sql tuning advisor）

BEGIN
 dbms_auto_task_admin.enable(
 client_name => 'sql tuning advisor',
 operation => NULL, 
 window_name => NULL);

4、启用或禁用某个自动维护任务的某个维护窗

缺省情况下，所有维护任务在所有预定义的维护窗口都运行。可以对某一个维护窗口启用或禁用自动化任务。

（1）禁用（如：sql tuning advisor的周一窗口）

BEGIN
  dbms_auto_task_admin.disable(
  client_name => 'sql tuning advisor',
  operation => NULL,
  window_name => 'MONDAY_WINDOW');
END;

（2）启用（如：sql tuning advisor的周一窗口）

BEGIN
  dbms_auto_task_admin.enable(
  client_name => 'sql tuning advisor',
  operation => NULL,
  window_name => 'MONDAY_WINDOW');
END;

5、维护窗口属性：

可是使用DBMS_SCHEDULER 包来修改窗口属性。

（1）修改维护窗口的属性

步骤1—首先禁用维护窗口

BEGIN
  dbms_scheduler.disable
  (
     name => 'SATURDAY_WINDOW'
  );
END;
/

步骤2：修改

--举例：修改周六的窗口属性（修改value,间隔4小时进行一次）

begin
dbms_scheduler.set_attribute
 (
     name => 'SATURDAY_WINDOW',
     attribute => 'DURATION',
     value => numtodsinterval(4, 'hour')
 );
end;
/

--举例：修改周五的窗口属性（修改value值，每周五11点40开始）

begin
  dbms_scheduler.set_attribute
  (
    name => 'FRIDAY_WINDOW',
    attribute => 'REPEAT_INTERVAL',
    value => 'freq=daily;byday=FRI;byhour=11;byminute=40; bysecond=0'
  );
end;
/

（2）启用维护窗口：

BEGIN
dbms_scheduler.enable
 (
      name => 'SATURDAY_WINDOW'
 );
END;
/

对于当前打开的窗口，你需要首先禁用，然后修改再启用，配置立即生效，如果你不通过这三个过程来修改属性，属性是不会生效的，直到下一次窗口打开。

（3）创建新窗口：

BEGIN
dbms_scheduler.create_window(
                     window_name => 'EARLY_MORNING_WINDOW',
                     duration => numtodsinterval(1, 'hour'),
                     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;
/

（4）删除窗口：

BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
       group_name => 'MAINTENANCE_WINDOW_GROUP',
       window_list => 'EARLY_MORNING_WINDOW');
END; 
/

6、立即停止正在运行的任务

（1）查看运行的job

SQL>SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;
SELECT * FROM DBA_JOBS_RUNNING;

（2）停止job

 dbms_job.broken(jobid,true);

（3）停止job2

SQL>exec dbms_scheduler.stop_job('job_name名称'，force=>true);

十六、其他

1、orapwd使用方式

orapwd file=/oracle/app/oracle/product/12.2/db_1/dbs/orapwkssyorclprd password=1qaz%2WSX force=y

其中owrpwkssyorclprd中的kssyorclprd是实例名。如果数据库名是kssyorcl，实例名是kssyorclprd，则此处是kssyorclprd。

2、百度直接搜索orcle文档

site:docs.oracle.com v$database

site:docs.oracle.com  dba_scheduler_windows等

3、查看表、索引等统计信息最新的统计时间

注：查看表的统计信息收集时间

SQL>select LAST_ANALYZED  from dba_tables;
SQL>select LAST_ANALYZED  from dba_indexes;