拓扑园

  • O&M
    • Universal部署
    • PHP+VUE+Laravel相关
  • Oracle性能优化
  • Oracle项目案例
    • Oracle近期项目案例(目录)
    • Oracle实战问题解析(目录)
    • Oracle数据库名变更流程(2种方式)
    • Oracle数据库目录更换流程(使用Oracle的clone工具)
    • Oracle数据库迁移方案(目录)
    • 标准化文档系列
  • Oracle基础知识
    • LLL的Oracle培训(分类)
    • LLL的docker培训(分类)
    • 标准化文档系列--(分类)
    • Oracle核心经典分析(分类)
    • 图灵小队----(分类并包含以下文章)
    • --MySQL8.0/Oracle/Memcached/Redis等安装配置于RHEL/OL6/7/8.X系列-运行环境最优配置
    • --PG安装配置于RHEL/9X系列-运行环境最优配置
    • --自动维护任务详解-开启、关闭信息统计收集(统计信息)
    • --图灵小队—Oracle/PostgreSQL下创建一个用户测试表(自行定义数据行)
    • --图灵小队-Oracle存储过程导出表的明细_UTL_FILE(文章)
    • --图灵小队-Oracle数据库删除/卸载操作指南(文章)
    • --图灵小队-Oracle常用性能查询SQL语句(文章)
    • --图灵小队-Oracle数据库上线前检查(文章)
    • --图灵小队-Oracle常用SQL语句(文章)
    • --图灵小队—Linux/Oracle脚本/MySQL合集(持续更新)
    • --图灵小队-Oracle技巧记录(文章)
    • ADG
    • RAC
    • ASM
    • OGG
    • RMAN
    • EXPDP/IMPDP
    • 工厂数据导入导出系列
  • MySQL
    • MySQL数据库规范
    • MySQL项目案例
    • MySQL安装配置
    • MYSQL集群项目
    • MySQL常见处理
    • MySQL-Sysbench专题
    • MySQL-Percona Toolkit专题
  • Linux
    • Shell编程
    • kubernetes
    • docker
    • Linux
    • PHP
    • Nginx
    • haproxy
    • mail
    • 网站
    • 域名
    • 网址收藏
  • 数据中心
    • 新框架系统集合
    • 工作文档
    • EBS数据文件扩容
    • VMware虚拟化
    • EBS系列
    • 大数据
    • SVN
    • zabbix
    • SAP
    • 备份相关
    • FC交换机
    • SVN
  • K-Studing
    • D8-Python学习
    • Oracle/MySQl等面试题
    • LG-MySQL
    • LG-Docker/K8S
    • LG-PostgreSQL
    • LG-ORACLE_BBED
    • LG-ORACLE
    • LG-Elasticsearch(ES)+ELK
    • Oracle-19C-OCP
    • WERN_ORACLE培训
    • redis数据库
    • Nginx培训学习系列
  • 其他
    • 外研英语4年级下册-听力
    • 影视系列
    • 如何使用iTunes软件通过抓包下载旧版本的ios的app
天高任鸟飞
Oracle/MySQL数据库恢复/数据迁移/生产规范报告技术交流:TEL:18562510581(微信同号);加微信入群
  1. 首页
  2. Oracle基础知识
  3. 图灵小队
  4. 正文

图灵小队—Oracle常用SQL语句

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

目录

  • 一、关于实例级别
    • 1、查看oracle进程用户信息
    • 2、关于系统级别
    • 3、set命令
    • 4、关于时间格式
  • 二、关于表操作:
    • 1、基本表操作
    • 2、关于多表查询:
    • 3、复杂SQL:   
  • 三、关于权限和角色
    • 1、权限--用户及参数:
    • 2、权限--系统权限:
    • 3、权限--对象权限:
    • 4、角色管理:
  • 四、关于约束和索引
    • 1、完整性约束:
    • 2、查询是否有外键约束:
    • 3、查看此表的表索引占据空间大小,以及索引高度:
  • 五、关于锁
    • 1、锁-行级锁:
    • 2、锁-表级锁:
  • 六、关于进程
    • 1、会话,进程:
    • 2、ORACLE实例的后台进程
  • 七、关于日志挖掘——关于补充日志和logminer数据挖掘
    • 1、开启补充日志
    • 2、设置一个字典文件路径(需要重启 DB):
    • 3、创建一个数据字典文件
    • 4、建立日志分析列表
      • (1)添加第一个日志
      • (2)继续添加日志
      • (3)移除日志
    • 5、启动分析
      • (1)无条件分析
      • (2)有条件分析
      • (3)可以使用COMMITTED_DATA_ONLY参数来只显示那些被提交的事务:
    • 6、查看日志分析结果
    • 7、结束分析
  • 八、关于rman及脚本
    • 1、rman备份功能
    • 2、脚本
  • 九、关于日志
    • 1、查看近来数据库生成的撤销数据量(undo):
    • 2、闪回日志恢复功能、还原点创建功能
  • 十、关于表空间查询脚本
    • 1、查询大字段的表,查询用户下最大表占用空间
    • 2、常用表空间查询语句(LLL总结)
    • 3、对某个用户授予某个表空间的访问权限
    • 4、创建表空间格式常用(LLL总结)
  • 十一、关于SQL 调优顾问的使用
    • 1、手动执行sys下的SQL_TUNING_TASK任务并调优
      • (1)查看clientname为sql tuning advisor是否开启
      • (2)查看sql优化任务,对应上述优化建议
      • (3)查看自动优化任务的参数
      • (4)修改优化执行时间
      • (5)手动执行sql优化任务
      • (6)取消正在进行的sql_tuning任务
      • (7)查看优化建议
      • (8)生成自动调优建议的sql
    • 2、SQL自动优化步骤(手动执行单个sql语句或id)
      • (1)获取sql_id
      • (2)创建调优任务
      • (3)登录sql,获取当前会会话的跟踪文件
      • (4)手动运行SQL调优顾问
      • (5)查看任务执行状态(完成/执行中/异常)
      • (6)关闭跟踪
      • (7)查看调优建议内容
      • (8)删除任务
      • (9)查看执行进度
    • 3、SQL自动优化步骤--方法2-流程和2一样,但多了profile建议
      • (1)根据sql查询sqlid
      • (2)为SQL_id创建一个STA(SQL Tuning advisor)分析任务(使用SYS用户执行)
      • (4)查询优化任务创建与执行的情况
      • (5)--查看任务优化报告(优化的详细内容)
      • (6)接受建议的 SQL 概要文件,即创建SQL_Profle
      • (7)查看第5步创建起来的SQL_Profile信息
      • (8)再次执行SQLID为‘dza1z9nnup2wb’的语句
      • (9)删除SQL_Profile(删除执行计划)
      • (10)删除优化任务
  • 十二、跟踪SQL语句
    • 1、根据根据sid和serial#跟踪语句
      • (1)获取当前用户登录mes时,操作系统的PID,通过新增查看。
      • (2)获取登录用户的sid和searial#
      • (3)进一步根据pid确认sid和searial#--sid=191,serial#=319
      • (4)开启SQL跟踪
      • (5)确认会话跟踪
      • (6)跟踪目录$LOG获取
      • (7)前端执行查看--记工查询
      • (8)tkprof分析trc
      • (9)关闭跟踪
      • (10)确认关闭跟踪
    • 2、设置系统跟踪查看
      • (1)登录sys,设置追踪打开
      • (2)查看目录
      • (3)找到最新的
      • (4)生成可读文档
    • 3、获取最新的sql执行语句
      • (1)方法1:
  • 十三、关于游标
    • 1、关于游标及游标缓存
      • (1)当前所有session使用的游标数和可以打开的最大游标打开数
      • (2)当前session中,使用游标数最大色session会话
      • (3)V$OPEN_CURSOR,
      • (4)$SESSION_CACHED_CURSOR
      • (5)关于参数open_cursor和session_cached_cursor
  • 十四、关于死锁
    • 1、死锁无法杀掉问题解决
      • (1)用dba用户执行以下语句,可以查看到被死锁的语句
      • (2)查找死锁的进程:
      • (3)通过SQL kill 进程:
      • (4)如果还不能解决(直接杀系统进程):查到这个进程
  • 十五、关于自动优化器
    • 1、查看自动维护任务
    • 2、启用或禁用所有自动维护任务的所有窗口
      • (1)禁用
      • (2)启用
    • 3、启用或禁止某个自动维护任务的所有窗口
      • (1)如:禁用所有的sql tuning advisor任务
      • (2)启用所有的sql tuning advisor任务(如:sql tuning advisor)
    • 4、启用或禁用某个自动维护任务的某个维护窗
      • (1)禁用(如:sql tuning advisor的周一窗口)
      • (2)启用(如:sql tuning advisor的周一窗口)
    • 5、维护窗口属性:
      • (1)修改维护窗口的属性
      • --举例:修改周五的窗口属性(修改value值,每周五11点40开始)
      • (2)启用维护窗口:
      • (3)创建新窗口:
      • (4)删除窗口:
    • 6、立即停止正在运行的任务
      • (1)查看运行的job
      • (2)停止job
      • (3)停止job2
  • 十六、其他
    • 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;

                        
本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: 暂无
最后更新:2023年11月10日

admin

这个人很懒,什么都没留下

打赏 点赞
< 上一篇
下一篇 >

文章评论

您需要 登录 之后才可以评论

COPYRIGHT © 2022 拓扑园. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

鲁ICP备2021020523号

鲁ICP备2021020523号