分类 ORACLE 下的文章

概述

数据库的性能瓶颈通常是由于某些sql语句效率不高,在大数据量或者高并发的情况下,会拖慢整体性能。要找到这些语句可以通过查找报告中的“SQL Statistics”章节,此章节按各种资源分别列出对资源消耗最严重的SQL语句,并显示它们所占统计期内全部资源的比例,这给出我们调优指南。例如在一个系统中,CPU资源是系统性能瓶颈所在,那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中,调优的目标应该是physical IOs最多的SQL语句。

awr报告中SQL优化主要查看如下内容:

SQL ordered by Elapsed Time
总时间消耗最长的sql语句

SQL ordered by CPU TimeCPU
总耗时最长的sql语句

SQL ordered by Gets
读取缓存最多的sql语句

常见sql调优方法:

1、根据查询条件创建索引。
2、用exists、not exists替代in和not in。
3、用表连接代替exists 。
4、减少order by 、union的使用,这两个关键字都很耗性能。
5、避免使用distinct,效率较低。
6、select 避免使用* 。
7、减少访问数据库的次数,绑定变量,预处理。
8、用where子句代替having子句。
9、避免在索引列上使用is null 和is not null。将会使索引失效。
10、避免在索引列上使用计算。如select…from pay where money*10>100

Cache Sizes
shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析(或编译)后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。

Load Profile
显示数据库负载概况,将之与基线数据比较才具有更多的意义,如果每秒或每事务的负载变化不大,说明应用运行比较稳定。单个的报告数据只说明应用的负载情况,绝大多数据并没有一个所谓“正确”的值,然而Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
Redo size:每秒/每事务产生的redo大小(单位字节),可标志数据库任务的繁重程序。
Logical reads:每秒/每事务逻辑读的块数
Block changes:每秒/每事务修改的块数
Physical reads:每秒/每事务物理读的块数
Physical writes:每秒/每事务物理写的块数
User calls:每秒/每事务用户call次数
Parses:SQL解析的次数
Hard parses:其中硬解析的次数,硬解析太多,说明SQL重用率不高。
Sorts:每秒/每事务的排序次数
Logons:每秒/每事务登录的次数
Executes:每秒/每事务SQL执行次数
Transactions:每秒事务数
Blocks changed per Read:表示逻辑读用于修改数据块的比例
Recursive Call:递归调用占所有操作的比率
Rollback per transaction:每事务的回滚率
Rows per Sort:每次排序的行数
注:
Oracle的硬解析和软解析
  提到软解析(soft parse)和硬解析(hard parse),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:
  1、语法检查(syntax check)
  检查此sql的拼写是否语法。
  2、语义检查(semantic check)
  诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
  3、对sql语句进行解析(parse)
  利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。
  4、执行sql,返回结果(execute and return)
  其中,软、硬解析就发生在第三个过程里。
  Oracle利用内部的hash算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;
  假设存在,则将此sql与cache中的进行比较;
  假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
  诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。

Instance Efficiency Percentages (Target 100%)

本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。
Buffer Nowait表示在内存获得数据的未等待比例。
buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG BUFFER。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率,当应用程序调用SQL或存储过程时,Oracle检查Library Cache确定是否存在解析过的版本,如果存在,Oracle立即执行语句;如果不存在,Oracle解析此语句,并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析,增加CPU消耗,降低性能。如果library hit ratio低于90%,可能需要调大shared pool区。
Latch Hit:Latch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL,或者Library Cache太小,可使用绑定变更或调大Shared Pool解决。
Parse CPU to Parse Elapsd:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。
Non-Parse CPU :SQL实际运行时间/(SQL实际运行时间+SQL解析时间),太低表示解析消耗时间过多。
Execute to Parse:是语句执行与分析的比例,如果要SQL重用率高,则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
In-memory Sort:在内存中排序的比率,如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。
Soft Parse:软解析的百分比(softs/softs+hards),近似当作sql在共享区的命中率,太低则需要调整应用使用绑定变量。

Shared Pool Statistics
Memory Usage %:对于一个已经运行一段时间的数据库来说,共享池内存使用率,应该稳定在75%-90%间,如果太小,说明Shared Pool有浪费,而如果高于90,说明共享池中有争用,内存不足。
SQL with executions>1:执行次数大于1的sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。
Memory for SQL w/exec>1:执行次数大于1的SQL消耗内存的占比。

Top 5 Timed Events
显示了系统中最严重的5个等待,按所占等待时间的比例倒序列示。当我们调优时,总希望观察到最显著的效果,因此应当从这里入手确定我们下一步做什么。例如如果‘buffer busy wait’是较严重的等待事件,我们应当继续研究报告中Buffer Wait和File/Tablespace IO区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待,就需要察看详细的LATCH统计识别哪些LATCH产生的问题。
在这里,log file parallel write是相对比较多的等待,占用了7%的CPU时间。
通常,在没有问题的数据库中,CPU time总是列在第一个。

版本号说明

Oracle 数据库的发行版本号(release number)一般由五位数字组成

第一位数字是我们最常提到的一个大版本标识
第二位数字代表数据库维护版本发行号
第三位数字代表了 Oracle Application Server (OracleAS)的发行版,对于 Oracle database 软件而言这一位总是为 0
第四位数字代表了某个组件的发行版本号。
第五位数字代表了平台相关发行版本号。

补丁类型介绍

Release
标准产品发布。如 Oracle Database 10g Release 2
Patch Set Release
PSR:这是在主版本号上发布的补丁集,修复了较多的 Bug
Patch Set Update
PSU:Oracle 选取在每个季度用户下载数量最多,并且得到验证具有较低风险的补丁放入到每个季度的 PSU 中,修复比较严重的一些问题,包含每个季度的 CPU,是累积型的。
Critical Patch Update
CPU 补丁:每季度发布一次,用来修复安全方面的些补丁,是累积型的。目前(2012 年 10 月)已经更名
为 Security Patch Update (SPU)
Interim Patch/One-Off Patch 小补丁
Merged Patch 合并的补丁:当几个小补丁之间有冲突,不能同时安装的时候,需要提供这种 Merged Patch。补丁冲突主要是由于 2个或者多个补丁修改同一个文件,但是修改的内容是不同的。
BundlePatch(BP)
补丁集,修复多个 Bug。
Diagnostic Patch
诊断补丁:这类补丁不是用来解决问题的,而是用来寻找问题的原因的。这类补丁只在 Oracle 技术支持部门要求安装时,才需要安装。在得到需要的诊断信息后 ,应立即卸载这一补丁。
Composite Patch
Composite Patche 的改进包括减少补丁安装时间,减少回滚以前应用的overlay patches的需要。 新的Composite Patches 格式,使以前PSU应用的overlay patches和新安装的PSU并存成为可能。

准备工作
数据备份:expdp、rman、冷备

windows下补丁升级
aix下补丁升级
linux下补丁升级
rac集群环境下补丁升级
详见:

操作思路

安装、测试、对比

环境检查

SQL> show parameter session;
SQL> show parameter processes;
alter system set processes = 3000 scope = spfile;
alter system set sessions=3800 scope=spfile;

SQL> show parameter sharing ---->有三种不同的状态
SQL> alter session set cursor_sharing=exact; 通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
SQL> alter session set cursor_sharing=similar;similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
SQL> alter session set cursor_sharing=force; force是在任何情况下,无条件重用SQL。
------------------------------创建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
drop tablespace soe including contents and datafiles;

测试前后分别取一次快照
安装swingbench测试软件,直接解压缩即可运行。
unzip -x swingbench25919.zip
 
注意要按照java8
yum -y install java-1.8.0-openjdk*
进入swingbench/bin目录执行oewizard导入1G测试数据,并修改数据库连接名和DBA密码
数据导完之后在该目录下运行swingbench执行测试,修改数据库连接名,用户连接数设置为300,测试时间设置为10分钟
修改Distributed Controls用于测试过程中搜集测试监控信息,修改完之后测试连接是否正常,并可以统计主机的cpu  disk IO 信息
参考信息
SwingBench下载:
http://www.dominicgiles.com/downloads.html
参考相关文章:
http://www.linuxidc.com/Linux/2016-04/130297.htm
http://blog.csdn.net/xiaofan23z/article/details/7978998
链接:https://pan.baidu.com/s/1o4CL0lENfmuEEFj-VwvDGQ
提取码:bwwh

----------------查询某块盘的wwid
[root@mesdg ~]#scsi_id -g -u /dev/sdi
@查看系统类型@查看数据库版本号@查看数据库名称
uname -a
select * from v$version; 或者 sqlplus -v
select instance_name,status from v$instance;
-------------------查询系统版本号
[root@db1 ~]# lsb_release -a
--------------------查看数据库的创建时间
select created from v$database;

----------------查看Dbid
select dbid from v$database;
----------------查看数据量
select sum(bytes/1024/1024/1024) from dba_segments;
----------------查看归档量
select sum(a.BLOCK_SIZE*a.BLOCKS)/1024/1024/1024 USE_G from v$archived_log a where a.DELETED='NO';
---------------查看有多少个非系统用户
select username,to_char(created,'yy-mm-dd hh24:mi:ss') from dba_users order by 2;
---------------总空间查询:
select sum(bytes/1024/1024) from dba_data_files;
----------------查看控制文件
select name from v$controlfile;
@查看数据库的安装路径vi .bash_profile或者vi .profile
@备份方法 多少长时间会发生一次定时备份 备份的大小 备份路径
crontab –l
du -sg * 以G的大小显示

例如:cat /u01/script/expdp_db.sh
cd /backup/expdp_*
ls –l
----------------查看表空间和表空间的大小
col file_name for a45
set linesize 200
Select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;

@查看数据库的归档路径@查看数据库的文件路径@查看sga @查看pga @查看cpu
archive log list;
select name from v$datafile;
show parameter sga
show parameter pga
show parameter cpu_count
------------------删除备份:
delete backupset completed before 'sysdate-1' tag='FULLDB';
delete backupset completed before 'sysdate-1' tag='ARCHIVELOG';
delete archivelog until time 'sysdate-1';
delete FULLDB time between “to_data('2017-08-18 23:10','yyyy-mm-dd hh24:mi:ss')"and to_date('2017-08-19 22:42' ,'yyyy-mm-dd hh24:mi:ss')";
backup archivelog all format '/rman/arch_%d_%T_%s_%p' delete input; 归档日志备份后防止空间暴涨在归档目录下删除已经归档的日志
------------------查看数据库启动到了那一个阶段
SQL>Select open_mode from v$database
-------------------用raw2创建一个名为niudg的磁盘组
idle> create diskgroup niudg external redundancy disk'/dev/raw/raw2';
-------------------创建表空间
sys@STONEDB> create tablespace niuts datafile'+NIUDG'size 2000M autoextend on;
-------------------创建表t1
sys@STONEDB> create table t1 tablespace niuts as select* from dba_objects;
sys@STONEDB> insert into t1 select* from t1;往表里面插入大量数据,重复执行3-4次
idle> alter diskgroup niudg add disk'/dev/raw/raw3' name r3; 给niudg加盘raw3命名为r3
-------------------开启归档
SQL> alter database archivelog;
--------------------切归档
idle>alter system archive log current;
-------------------修改数据库登陆密码
[oracle@rh5 dbs]$ orapwd file=orapworcl password=abc123 force=y
--------------------修改用户sys密码为oracle
sys@ORCL> alter user sys identified by oracle;
------------------创建用户密码为stone
sys@ORCL> create user stone identified by stone password expire;
-------------------锁定这个用户
sys@ORCL> alter user stone account lock;
------------------解锁
sys@ORCL> alter user USR_YSJ account unlock;

------------------创建数据表空间
create tablespace NIU DATAFILE '/home/oracle/app/oradata/node1/system02.dbf' size 10M extent management local segment space management auto;
------------------查询创建的数据表空间
SQL> col file_name format a50
SQL> set linesize 366
SQL> select file_name,tablespace_name,autoextensible,bytes from dba_data_files where tablespace_name='oditablespace'ORDER BY substr(file_name,-12);

-----------------创建回滚段表空间
create undo tablespace undotbs2 datafile '/home/oracle/app/oradata/node1/nudotbs2.dbf' size 10M;
-----查询刚建的undo表空间
select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2' order by substr(file_name,-12);
-------给表空间添加10g的数据文件,以可扩展的方式每次可扩展1个g;
SQL> alter tablespace YXY_DATA add datafile '/oradata/orcl/yxy_data08.dbf' size 10G autoextend on next 1G;

-------------------------查询undo的大小
SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) 8 / 1024) AS "Size M",COUNT() Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS;
-----------------------------查看undo信息
set lines 200 pages 1000
col TBS_NAME for a30
col "TBS_USED(G)" for 99999999999
col "TBS_TOTAL(G)" for 99999999999
col "TBS_FREE(G)" for 99999999999
select a.tablespace_name "TBS_NAME",
round((b.bytes/1024/1024/1024),2) "TBS_TOTAL(G)",
round((a.bytes/1024/1024/1024),2) "TBS_USED(G)",
(round((b.bytes/1024/1024/1024),2)-round((a.bytes/1024/1024/1024),2)) "TBS_FREE(G)",
round((round((a.bytes/1024/1024/1024),2))/(round((b.bytes/1024/1024/1024),2))*100,2) "USE_PERT(%)"
from sys.sm$ts_used a,(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
and a.tablespace_name like 'UNDO%'
order by 1,5;

------创建临时表空间
create temporary tablespace NIU_LS tempfile '/home/oracle/app/oradata/node1/tmp_niu.dbf' size 10M;
------查询创建的临时表空间
select file_name,bytes,autoextensible from dba_temp_files where tablespace_name='NIU_LS';
SQL> alter tablespace YXY_DATA add datafile '/oradata/orcl/yxy_data08.dbf' size 10G autoextend on;
--------扩展临时表空间:
方法一、增大临时文件大小:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
方法二、将临时数据文件设为自动扩展:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件:
alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;
自动生成数据文件名称
alter tablespace AAA_IDX add datafile '+DATADG' size 10G autoextend on next 300M;

--------修改默认临时表空间
alter database default temporary tablespace temp1;
--------所有用户的默认临时表空间都将切换为新的临时表空间:
select username,temporary_tablespace,default_ from dba_users;
--------更改某一用户的临时表空间:
alter user scott temporary tablespace temp;
---------删除临时表空间
1,删除临时表空间的一个数据文件:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
SQL> alter database datafile '+DATA/oaecology/datafile/users.280.1005750973' offline drop;
删除表空间及数据文件:
SQL> drop tablespace ECOLOG including contents and datafiles cascade constraint;
2,删除临时表空间(彻底删除):
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
--------查看数据文件1
set lines 200
col file_name for a60
select FILE_NAME,

   TABLESPACE_NAME,
   BYTES / 1024 / 1024,
   MAXBYTES / 1024 / 1024,
   AUTOEXTENSIBLE,
   INCREMENT_BY

from dba_data_files
order by 1;
--------查看数据文件2
select FILE_NAME,

   TABLESPACE_NAME,
   BYTES / 1024 / 1024,
   MAXBYTES / 1024 / 1024,
   AUTOEXTENSIBLE,
   INCREMENT_BY

from dba_data_files
union
select FILE_NAME,

   TABLESPACE_NAME,
   BYTES / 1024 / 1024,
   MAXBYTES / 1024 / 1024,
   AUTOEXTENSIBLE,
   INCREMENT_BY

from dba_temp_files
order by 1;
-----------------查看数据文件状态
set lines 200
col file_name for a60
select FILE_NAME,FILE_ID,status from dba_data_files;
select name from v$datafile where name like '%sysaux%';

-----------------查询log
set lines 200
col MEMBER for a50
select * from v$logfile;
select * from v$log;

-----查看表空间使用率一
set lines 200
col "tbsname" for a
col "allocated" for 999999999.99
col "used" for 999999999.99
col "free" for 999999999.99
col "extend" for 999999999.99
col "totalfree%" for 999999999.99
select b.TablespaceName "tbsname",

   round(sum(b.UsedByte) / 1024 / 1024) "allocated",
   round((sum(b.UsedByte) - sum(a.FreeByte)) / 1024 / 1024) "used",
   round(sum(a.FreeByte) / 1024 / 1024) "free",
   round(sum(b.ExtensibleByte) / 1024 / 1024) "extend",
   round(sum(a.FreeByte + b.ExtensibleByte) * 100 / sum(b.UsedByte + b.ExtensibleByte), 2) "totalfree%"

from (select sum(bytes) FreeByte,

           count(*) Extend,
           file_id FileID,
           tablespace_name TablespaceName
      from dba_free_space
     group by file_id, tablespace_name
    union all
    select sum(bytes_free) FreeByte,
           count(*) Extend,
           file_id FileID,
           tablespace_name TablespaceName
      from v$temp_space_header
     group by file_id, tablespace_name) a,
   (select decode(autoextensible,
                  'YES',
                  decode(sign(maxbytes - bytes), 1, maxbytes - bytes, 0),
                  0) ExtensibleByte,
           bytes UsedByte,
           file_id FileID,
           tablespace_name TablespaceName
      from dba_data_files
    union all
    select decode(autoextensible,
                  'YES',
                  decode(sign(maxbytes - bytes), 1, maxbytes - bytes, 0),
                  0) ExtensibleByte,
           bytes UsedByte,
           file_id FileID,
           tablespace_name TablespaceName
      from dba_temp_files) b

where b.FileID = a.FileID(+)
and b.TablespaceName = a.TablespaceName(+)
group by b.TablespaceName
order by 6;

-------查看表空间使用率二
col "tbs_name" for a20
col "tbs_used%" for a15
col "tbs_size(M)" for 999999999.99
col "tbs_used(M)" for 999999999.99
SELECT UPPER(F.TABLESPACE_NAME) "tbs_name",

    D.TOT_GROOTTE_MB "tbs_size(M)",
    D.TOT_GROOTTE_MB-F.TOTAL_BYTES "tbs_used(M)",
      TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "tbs_used%",
      F.TOTAL_BYTES "tbs_free(M)"

FROM (SELECT TABLESPACE_NAME,

            ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
            ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
     FROM SYS.DBA_FREE_SPACE
     GROUP BY TABLESPACE_NAME) F,
    (SELECT DD.TABLESPACE_NAME,
            ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
     FROM SYS.DBA_DATA_FILES DD
     GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDER BY 2 DESC;

-------------------------------------检查表空间三:
SELECT tablespace_name, sum_m as "sum(M) " , trunc(sum_free) as "sum_free(M) ",to_char(100*sum_free/sum_m, '99.99') || '%' AS pct_free
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS ts_name, sum(bytes/1024/1024) AS sum_free FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = ts_name (+)
Union all
SELECT tablespace_name , sum_m as "sum(M) " , trunc(sum_free) as "sum_free(M) " , to_char(100*sum_free/sum_m, '99.99') || '%' AS pct_free
FROM (select tablespace_name, sum(bytes)/1024/1024 AS sum_m from dba_temp_files group by tablespace_name),
(select tablespace_name AS ts_name, sum(BYTES_FREE /1024/1024) AS sum_free from v$temp_space_header group by tablespace_name)
Where tablespace_name = ts_name (+)
order by pct_free;

-----------------------------------------表空间检查四
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,999') "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
ORDER BY 7;

----------等待事件检查
set pages 100
col event for a60
col p1 for 9999999999999999999
col p2 for 9999999999999999999
col p3 for 9999999999999999999
select sid,event,p1,p2,p3
from v$session
where event not like 'SQL*N%'
and event not like 'rdbms%'
and event not like 'single%'
and event not like 'pmon%'
and event not like 'smon%';
------------失效对象检查
SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';

-----------长事务检查
select sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where
totalwork != 0
and sofar <> totalwork;

------------行锁检查
select s1.INST_ID,

   s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
   ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
   s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where l1.INST_ID = s1.INST_ID
and s1.INST_ID = s2.INST_ID
and s2.INST_ID = l2.INST_ID
and s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

---------------紧急情况查杀用户会话进程

查看是否正确:

ps -ef |grep LOCAL=NO|grep -v grep| awk '{print $2}'|more

执行批量kill

ps -ef |grep LOCAL=NO|grep -v grep| awk '{print $2}'|xargs kill -9

----------------审计数据库清理
SQL> conn / as sysdba
SQL> alter system set audit_trail = none scope=spfile;
SQL> TRUNCATE TABLE SYS.AUD$;

--------------修改用户名和密码
1、更改用户名scott为s1
SQL> update user$ set name='S1' where user#=83;
已更新 1 行。
SQL> commit;
提交完成。
3、修改密码
alter user S1 identified by 123456; 此处注意密码有特殊符号的密码要加双引号
4,验证方法
conn S1/123456

-------------创建用户并指定表空间和临时表空间
create tablespace sjjzftzsj DATAFILE '+DATA/coreora/datafile/sjjzftzsj1.dbf' size 1G extent management local segment space management auto;
create temporary tablespace sjjzftzsj_temp tempfile '+DATA/coreora/datafile/tmp_sjjzftzsj1.dbf' size 1G;
create user sjjzftzsj identified by "yuAnR*6!%W" account unlock default tablespace sjjzftzsj temporary tablespace sjjzftzsj_temp;

----------------查询数据库所有用户
SQL> select username from dba_users;
----------------查询和这个用户所拥有的权限
SQL> select * from dba_sys_privs t where t.grantee='USR_PUBLIC';

----------------赋予用户conn,csession,view的权限
grant connect, resource to szpt_eval;
grant create session to szpt_eval;
grant create any view to szpt_eval;
-------------权限
select * from dba_role_privs where granted_role='DBA';
grant create any view to zd_rc7shares;
grant connect, resource to zd_rc7shares;
grant create session to zd_rc7shares;
grant alter to zd_rc7shares;
grant update to zd_rc7shares;
grant select on A.T1 to B; 把A用户中表t1的select权限给用户B;
revoke dba from CUSTOMIZATIONUSR 回收权限

----------------查看Dbid
select dbid from v$database;
----------------查看数据量
select sum(bytes/1024/1024/1024) from dba_segments;
-----------------查看有多少个非系统用户
select username,to_char(created,'yy-mm-dd hh24:mi:ss') from dba_users order by 2;
-----------------总空间查询:
select sum(bytes/1024/1024) from dba_data_files;
----------------查看数据库的安装路径
vi .bash_profile或者vi .profile
------------------备份方法 多少长时间会发生一次定时备份 备份的大小 备份路径
crontab –l
du -sg * 以G的大小显示
例如:cat /u01/script/expdp_db.sh
cd /backup/expdp_*
ls –l
------------------查看表空间和表空间的大小
col file_name for a45
set linesize 200
Select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;
-------------------@查看数据库的归档路径@查看数据库的文件路径@查看sga @查看pga @查看cpu
archive log list;
select name from v$datafile;
show parameter sga
show parameter pga
show parameter cpu_count
---------------------删除备份:
delete backupset completed before 'sysdate-1' tag='FULLDB';
delete backupset completed before 'sysdate-1' tag='ARCHIVELOG';
delete archivelog until time 'sysdate-1';
delete FULLDB time between “to_data('2017-08-18 23:10','yyyy-mm-dd hh24:mi:ss')"and to_date('2017-08-19 22:42' ,'yyyy-mm-dd hh24:mi:ss')";
backup archivelog all format '/rman/arch_%d_%T_%s_%p' delete input; 归档日志备份后防止空间暴涨在归档目录下删除已经归档的日志
--------------------查看数据库启动到了那一个阶段
SQL>Select open_mode from v$database
---------------------查看实例信息
col HOST_NAME for a30
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUS from v$instance;
---------------查看和修改会话连接数
SQL> show parameter session;
SQL> show parameter processes;
select value from v$parameter where name = 'processes'; -------查询数据库允许的最大连接数:

select value from v$parameter where name = 'sessions'; --原则上是进程数量控制了client的连接数量 ,会话数应该大于进程数
alter system set processes = 3000 scope = spfile;
alter system set sessions=3800 scope=spfile;
select count(*) from v$process; 查询数据库当前进程的连接数
select count(*) from v$session; 查看数据库当前会话的连接数:
---------------游标有关的参数
SQL> show parameter open_cursors;
alter system set open_cursors=2000 scope=both;
SQL> show parameter session_cached_cursors;
alter system set session_cached_cursors=300 scope=spfile;
SQL> show parameter sharing ---->有三种不同的状态
SQL> alter session set cursor_sharing=exact; 通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
SQL> alter session set cursor_sharing=similar;similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
SQL> alter session set cursor_sharing=force; force是在任何情况下,无条件重用SQL。

---------------查看用户或角色所拥有的角色
select * from dba_role_privs where GRANTEE='ZD_RC7SHARES';
--------------查看某个用户的默认表空间,临时表空间
select username,default_tablespace,temporary_tablespace
from dba_users
where username='EXTCLOUD_OA';

-------------锁问题
SQL> alter user Ft_ysbdts account lock;锁住某个用户
SQL> alter user Ft_ysbdts account unlock;解锁

-------------创建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

---------------------root用户起停一下集群
路径为grid_home

/u01/app/grid_home/11.2.0/bin/crsctl start cluster -all

$ crsctl status res -t
lsnrctl status listener_scan1

-----------------------查询表名大于32位的表
select table_name from dba_tables where length(table_name)>32;
-----------------------查询所有表名:
select t.table_name from user_tables t;
-----------------------查询所有字段名:
select t.column_name from user_col_comments t;
-----------------------查询指定表的所有字段名
select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';
-----------------------查询指定表的所有字段名和字段说明:
select t.column_name, t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';
-----------------------查询所有表的表名和表说明:
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;
----------------------查询模糊表名的表名和表说明:
select t.table_name from user_tables t where t.table_name like 'BIZ_DICT%';
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like 'BIZ_DICT%';

----------------------查询表的数据条数、表名、中文表名
col COMMENTS for a20
set linesize 200
select a.num_rows, a.TABLE_NAME, b.COMMENTS
from user_tables a,length(table_name)>32, user_tab_comments b
WHERE a.TABLE_NAME = b.TABLE_NAME
order by TABLE_NAME;

-----------------------------查看某个用户数据量的大小
SQL> select owner,sum(bytes)/1024/1024/1024 G from dba_segments where owner='RMC' group by owner;
---------------查询表名最大字符数
select max(lengthb(name)) from sys.obj$;
---------------用匿名块查询相关的job信息
col SUBSTR(WHAT,1,50) for a20
set linesize 200
select job,substr(what,1,50),broken from dba_jobs;
-------------------查询表+用户名超过32位字符的表和用户
col OWNER for a20
set linesize 200
select owner,table_name,length(owner||'.'||table_name) from dba_tables where length(owner||'.'||table_name) > 32;

---------------------查询表名和对象名在10到15之间的
SQL> select owner,table_name from dba_tables where length(owner) > 10 and length(table_name) > 15 order by 1,2;
---------------------查询用户名大于10的用户
SQL> select username from dba_users where length(username) > 10;
----------------------查询某些对象的表名有多少个字符
select length(owner),owner,length(table_name) length_tab,table_name
from dba_tables
where owner in ('EXCHANGEUSR', 'CUSTOMIZATIONUSR', 'SZPT EVALUATION' )
and length(table_name) > 15
order by 1,3;
--------------------查询某些对象和对应的表名有多时候字符
SQL> select length(owner),owner,length(table_name) length_tab,table_name
from dba_tables where owner in ('EXCHANGEUSR', 'CUSTOMIZATIONUSR', 'SZPT EVALUATION')
and table_name in ('CONF-IRM BEFORE BUGFIX ','CONTACTS_MEMBER_DD','EMPLOYEE_MECHAT_BINDING')
order by 1,3;
--------------------查询SZPT_PRD用户对应的RS_OTHER_PERSON_BASEINFO表用多少个字符
select owner,table_name,length(owner||table_name) from dba_tables where owner='SZPT_PRD' and table_name='RS_OTHER_PERSON_BASEINFO';

---------------------查询SZPT_PRD用户中是否有RS_OTHER_PERSON_BASEINFO这个表
select owner,table_name from dba_tables where owner='SZPT_PRD' and table_name='RS_OTHER_PERSON_BASEINFO';

----------------------trc删除
正常情况trc文件被删除以后空间是无法立刻释放的要通过echo转义一下然后rm
echo “” > ecdb1_j001_2879.trc
rm ecdb1_j001_2879.trc
----------------清理5天前的trace日志
find . -name "*.trc" -mtime +5 -exec rm {} ;
find . -mtime +7 -name “*.xml”-exec ls -lrt {} ;
find . -mtime +7 -name "*.xml"|xargs rm -f 删除7天前的xml文件

--------------------------------修改文件使sql里面的导入行出现对应的实例名

vi /u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/glogin.sql

 set sqlprompt ‘_user”@”_connect_identifier> 

----------------------查看某个用户是否有连接
SQL> select sid,SERIAL#,username,module,machine,program,status from v$session where username='KCES' order by status;

----------------------查看opacth版本
opatch lspatches
opatch lsinventory

----------------------查看asm磁盘
set lines 200 pages 1000
col PATH for a20
col NAME for a20
SQL> select GROUP_NUMBER, NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup;
SQL> select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, MODE_STATUS, STATE, REDUNDANCY, TOTAL_MB, FREE_MB, PATH, NAME from v$asm_disk;

-------------创建这个用户并给这个用户创建视图和连接的权限
create user szu_imp identified by "imp@szu#2019";
grant create any view to szu_imp;
grant connect, resource to szu_imp;
grant alter on mdm.gy_user to mdm;
grant update on szpt_prd.gy_user to mdm;

revoke update on szpt_prd.gy_user from mdm;

----------------查看监听方式
srvctl status scan 查看监听再那个实例上面和监听的名字
lsnrclt status lsnrctl_name 查看这个监听对应的ip和状态

--------------一个系统有两个实例,要求a实例上面的a_user用户对实例b上面的b_user用户有select的权限
SYS@zhfwdb1> conn usr_xsfw/"Wisedu@Szu#xSfW_2018"
源端:zhfwdb1
grant create database link to usr_xsfw;
目标端:kfptdb1
create user user_link identified by userlink;
grant select any table to user_link;
源端usr_xsfw用户创建:zhfwdb1
create database link usr_xsfw_dblink
? connect to user_link
? identified by userlink
? using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.55.250)(PORT = 1521)) ###此IP为scanIP
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = KFPTDB))
?)';
--------确认已经解锁
select username,account_status from dba_users where username in ('MMYY','MMZW');
select username,account_status from dba_users where username in ('MMYY');

----------查看被锁对象
set lin 180 pages 49999
col USERNAME for a15
col owner for a15
col OBJECT_NAME for a35
col PROGRAM for a35
col PROCESS for a15
select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
from gv$locked_object a, dba_objects b,gv$session c
where a.object_id=b.object_id and c.sid=a.session_id;
----------查询这个用户是否被锁
select account_status,profile from dba_users where username='USR_ApI';
-----------查询这个数据库用户的密码策略(限制)
col LIMIT format a50
set linesize 366
select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';
SYS@kfptdb1> select * from dba_profiles;

-----------解锁这个用户
alter user usr_api account unlock;

------------------------------dblink信息查询
col DB_LINK for a40
col USERNAME for a20
col OWNER for a20
col HOST for a30
set linesize 200
select * from dba_db_links;

col OWNER for a20
col OBJECT_NAME for a30
set linesize 200
select owner,object_name from dba_objects where object_type='DATABASE LINK';

tnsping 一下你连接字符串中的sid,可查dblink是否是通的
-----------------------------------补丁信息查询
opatch lspatches
opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory

-------------------------------查看数据数据字典对象
set line 150
col ACTION_TIME for a30
col ACTION for a8
col NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a20
select * from dba_registry_history;

-------------------------------------显示sql实例名
vi /u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/glogin.sql
set sqlprompt '_user"@"_connect_identifier> '

-------------------需求:要求aa用户对bb用户下所有的表有可读权限,并且bb用户下会不停的添加新表
创建procedure。bb用户创建

create or replace procedure new_table_grant_procedure(v_tabname IN VARCHAR2)
is
sqlstr VARCHAR2(200);
begin

sqlstr := 'grant select on ' || v_tabname ||' to aa';
execute immediate sqlstr;

end;
/

创建TRIGGER。bb用户创建

CREATE OR REPLACE TRIGGER new_table_grant_tri
after create
ON SCHEMA
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
lv_job NUMBER;
BEGIN
IF ora_dict_obj_type='TABLE' THEN
DBMS_JOB.SUBMIT(lv_job,'new_table_grant_procedure('''||ora_dict_obj_name||''');');

 COMMIT;

end if;
END;
/

---------------查看某个表或者视图是那个用户下的
select owner from dba_objects where object_name='USR_JWBIZ';
select owner from dba_views where VIEW_NAME='VIEW_JP_PK';
--------------给usr_MKS用户可以查询view_jp_xs视图的权限
grant select on APEX_030200.WWV_FLOW_SHORTCUTS to STONE;
语法:
grant select on username.viewname to user;
查询这个角色的内容:
select * from STONE.WWV_FLOW_SHORTCUTS;

----------查看被锁对象
set lin 180 pages 49999
col USERNAME for a15
col owner for a15
col OBJECT_NAME for a35
col PROGRAM for a35
col PROCESS for a15
select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
from gv$locked_object a, dba_objects b,gv$session c
where a.object_id=b.object_id and c.sid=a.session_id;

-----------------------查看那些对象被锁
set lin 180 pages 49999
col locked_object for a50
select * from v$locked_object;

-------------查询那些用户被锁
set line 400
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE from dba_users;

select 'alter user '||username||' account unlock;' from dba_users where ACCOUNT_STATUS='LOCKED(TIMED)';
alter user system account unlock;
alter user USR_AMP account unlock;

---------------------创建触发器(若审计没开,可创建触发器追踪用户被锁原因)
create table logon_error(AUTHENTICATED_IDENTITY varchar2(200),HOST varchar2(200),ip_address varchar2(200),"sysdate" date);

CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN

INSERT INTO logon_error VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'),sys_context('userenv', 'ip_address'),SYSDATE);
COMMIT;

END IF;
END;
/
查询触发器监控结果:
set pages 130;
set lines 1300;
col AUTHENTICATED_IDENTITY for a20;
col HOST for a20;
col IP_ADDRESS for a20;
select * from logon_error;

---------------查找会话sid,spid
select a.spid,b.sid,b.serial#,b.username
from v$process a,v$session b
where a.addr=b.paddr and b.username='ADQSTD';

select s.username,s.sid,s.serial#,p.SPID from v$session s,v$process p
where s.paddr=p.addr and s.username='ADQSTD'
-------------------查询USR_JWBIZ用户下对应的那个sid和sql_id被锁了
select sid,username,sql_id,blocking_session,event,wait_time from gv$session where username='USR_YSJ' and wait_class<>'Idle';
------------kill某个session
alter system kill session 'sid,serial#'
alter system disconnect session '4027,16553' immediate;
-------------查询这个sid对象的sql语句
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=4208);
SYS@kfptdb1> select sql_text from v$sql where sql_id='4pkbh0qg8ppfn';

-------------------查找某表被锁住
set pages 130;
set lines 1300;
col object_name for a20;
col machine for a20;
col program for a20;
col killid for a30;
col os_pid format a20;

select object_name ,machine ,s.program ,
s.sid||','||s.serial# as killid,
p.spid as os_pid ,
s.sql_address,
l.locked_mode,
s.username,
s.process,
s.sql_id
from v$locked_object l,
dba_objects o ,v$session s ,v$process p
where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr
order by 1;

---------------查看某个表是那个用户下的
select owner from dba_objects where object_name='WWV_FLOW_SHORTCUTS';
---------------查看这用户属于那个表空间
select username,default_tablespace from dba_users where username='USR_ZSJ';
select username,default_tablespace from dba_users;
---------------查看这用户是否在这个数据库
select username from dba_users where username='USR_ZSJ';
----------------查看这个用户下的表有多大
SYS@zhfwdb1> select count(*) from USR_YJSZJ_TEST.T_XJGL_XSXX_XSJCXX;
----------------查看表空间对应的数据文件
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

--------------查看对象的游标句柄对应的存储过程(适用所有对象)
col KGLNAOBJ for a25
col KGLNAOWN for a10
select addr,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from x$kglob where KGLHDOBJ=upper('P_JW_INIT_XKLCXS');
----------------查询关联的对应存储过程的对象的句柄的会话是有哪些
select a.sid,a.username,a.program,b.addr,b.kglpnadr,b.kglpnuse,b.kglpnses,b.kglpnhdl,b.kglpnlck,b.kglpnmod,b.kglpnreq from v$session
a,x$kglpn b where a.saddr=b.kglpnuse and b.kglpnhdl = '000000A74D378790' and b.kglpnmod<>0;

--------------------查询某个sql_id在做什么操作
select sql_text from v$sql where sql_id='8n0zftk5gn1x9';
--------------------查询某个sql_id的执行计划
select * from table(dbms_xplan.display_cursor('d6qvuf4b5zqrt'));
-----------------------查询这个sql是那个用户执行的
set line 500
select USERNAME,MACHINE from v$session where sql_id='8n0zftk5gn1x9';
-------------------------查看这个sql的执行时间
select sid,status,SQL_CHILD_NUMBER,BLOCKING_SESSION,SQL_EXEC_START from v$session where sql_id='8n0zftk5gn1x9' order by SQL_EXEC_START;
--------------------------通过执行语句查出sql_id
SYS@zhfwdb1> select sql_id from v$sqlarea where sql_text like '%SELECT DISTINCT yh.yhid FROM T_GGGL_DB_LSYH yh%';

----------------查询sid对应的阻塞者,sql_id,等待事件
select sid,serial#,sql_id,event,blocking_session from gv$session where sid=3577;

------------------------等待事件查询
select event,count(*) from v$session group by event;

--------------------------------------------------------------P1RAW值,确定row cache的segment
select count(*),P1RAW from v$session where event = 'latch: row cache objects' group by P1RAW;

----------------------------------------查询这几张表是否有大字段
set lines 200 pages 333
col data_type for a30
SELECT distinct a.OWNER,a.TABLE_NAME,a.data_type,(select sum(BYTES/1024/1024) from dba_segments b where a.TABLE_NAME = b.SEGMENT_NAME) M FROM all_tab_columns a WHERE a.TABLE_NAME in ('T_GGGL_DB_LSYH','V_XX_RZLB_GLDX','t_pygl_pyjh_pyjhxx','t_xjgl_xsxx_xsjcxx') and data_type in ('BLOB','CLOB','LONG') order by 4 desc;

---------------------------------查看回滚段(dba_rollback_segs)
select owner,segment_id,segment_name,tablespace_name,status from dba_rollback_segs;
select segment_name,status,tablespace_name from dba_rollback_segs;
SELECT

   kqrsttxt PARAMETER,   

kqrstcid CACHE#,

   kqrstcln "Lchild#",   
   kqrstgrq "DCGets",   
   l.gets   "LGets",   
   l.misses "Misses"  

FROM X$KQRST, V$LATCH_CHILDREN l
WHERE l.addr='000000A74D378790' and l.child#=KQRSTCLN
ORDER BY 1,2
;

-------------------------------------查询数据库重启的时间
SYS@zhfwdb1> select inst_id,STARTUP_TIME from gv$instance;


set line 500
col cache# head "Cache|no" form 999
col parameter head "Parameter" form a25
col type head "Type" form a12
col subordinate# head "Sub|ordi|nate" form 9999
col rcgets head "Cache|Gets" form 999999999999
col rcmisses head "Cache|Misses" form 999999999999
col rcmodifications head "Cache|Modifica|tions" form 999999999999
col rcflushes head "Cache|Flushes" form 999999999999
col kqrstcln head "Child#" form 999
col lagets head "Latch|Gets" form 999999999999
col lamisses head "Latch|Misses" form 999999999999
col laimge head "Latch|Immediate|gets" form 999999999999
select dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,

decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#, 
dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln, 
la.gets lagets, la.misses lamisses, la.immediate_gets laimge

from x$kqrst dc,

v$latch_children     la

where dc.inst_id = userenv('instance')
and la.child# = dc.kqrstcln
and la.name = 'row cache objects'
order by rcgets desc
/

------------------直接在asmcmd里面使用cp复制命令
cp +data_dg/ZHFWDBSTD/ARCHIVELOG/2019_01_15/thread_2_seq_264523.15702.997645445 +data_dg/ZHFWDBSTD/ARCHIVELOG/1-15/thread_2_seq_264523
ASMCMD> cp SYSAUX.260.894187589 /home/grid/sysaux.dbf

--------------删除表(删除用户USR_JWBIZ下的表T_XK_TYKCS)
SYS@zhfwdb1> drop table USR_JWBIZ.T_XK_TYKCS purge;

-------------查询某个时间点的scn号
select timestamp_to_scn(to_timestamp('15-JAN-19 16.00.00','dd-mm-yy hh24.mi.ss')) from dual;

--------------根据scn号查出某个时间点
select to_char(scn_to_timestamp(12576123563836),'yyyy-mm-dd hh24:mi:ss') from dual;

--------------查询T_CJ_CJPM这个表是否在回收站
select OWNER,OBJECT_NAME,ORIGINAL_NAME,DROPTIME from dba_recyclebin where ORIGINAL_NAME='T_CJ_CJPM';
------------------查询回收站文件数量
SYS@kfptdb1> select count(0) from dba_recyclebin;
--------------清空回收站
purge dba_RECYCLEBIN;
purge RECYCLEBIN;

---------------查询表的大小
select count(*) from USR_JWBIZ.t_cj_cjpm;
---------------查询实例下除系统用户外所有用户下面的表行数
select sum(num_rows) from dba_tables where owner in (select username from dba_users where username not in ('SYS','SYSTEM'));

---------------查询T_CJ_CJPM这个表最后是什么时间做的ddl操作
SELECT TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS') FROM DBA_OBJECTS WHERE OBJECT_NAME='T_CJ_CJPM';
select TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP from dba_objects where object_name='T_CJ_CJPM';

------------------------查询获得系统每天的归档日志的大小
select GROUP#,BYTES/1024/1024 from v$log;
set lin 180 pages 49999
col DAY for a30
col COUNT_NUMBER for a30
select max (first_time) max_first_time,

       to_char (first_time, 'yyyy-mm-dd') day,     
                  count (recid) count_number,  
                  count (recid) * 512 size_mb   
             from v$log_history  
      group by to_char (first_time, 'yyyy-mm-dd')    
    order by 1;

----------------------查询这个USR_PUBLIC用户为什么频繁的被锁,以及是谁在连接这个用户导致的被锁(审计打开的情况下可以查询)
col SESSIONID for 99999999999
col userid for a10
col userhost for a10
col comment$text for a110
col spare1 for a10
col ntimestamp# for a25
set linesize 500
col terminal for a5
select sessionid,userid,userhost,comment$text,spare1,cast ((from_tz(ntimestamp#,'00:00') at local) as date) ntimestamp#,returncode,terminal from sys.aud$ where returncode in (28000,1017) and userid='USR_API' order by ntimestamp#;
如有报错 查看报错信息
SYS@kfptdb1> !oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

---------------------查询审计是否关闭 (db状态为开启,none状态为关闭)
USR_PUBLIC@kfptdb1> show parameter audit_trail;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB

-------------------关闭和开启审计
SQL> alter system set audit_sys_operations=FALSE scope=spfile;
SQL> alter system set audit_trail=none scope=spfile;
SQL> alter system set audit_sys_operations=TRUE scope=spfile;--审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> shut immediate;
SQL>startup
------------------------清空审计表数据
SQL> truncate table SYS.AUD$;

--------------------------查询触发器状态
SYS@zhfwdb> SET LINES 200 PAGES 9999
SET VERIFY OFF
COLUMN parameter FORMAT a37
COLUMN description FORMAT a30 WORD_WRAPPED
COLUMN session_value FORMAT a10
COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter,

   a.ksppdesc AS description,
SYS@zhfwdb> SYS@zhfwdb> SYS@zhfwdb> SYS@zhfwdb> SYS@zhfwdb> SYS@zhfwdb> SYS@zhfwdb>   2    3     b.ksppstvl AS session_value,
   c.ksppstvl AS instance_value

FROM x$ksppi a,

   x$ksppcv b,
   x$ksppsv c

WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%system_trig_enabled%' ESCAPE '/'
ORDER BY a.ksppinm; 4 5 6 7 8 9 10 11

PARAMETER DESCRIPTION SESSION_VA INSTANCE_V
------------------------------------- ------------------------------ ---------- ----------
_system_trig_enabled are system triggers enabled TRUE TRUE

-------------------------------关闭触发器
alter system set "_system_trig_enabled"=false scope=spfile;

-----------------------------solaris系统ntp关闭/启动/重新启动
svcadm disable ntp
svcadm enable ntp
svcadm restart ntp

---------------------------创建服务名(创建services)
1、创建services
srvctl add service -d db_name -s srv_epm1 -r instance_name -a instance_name -P basic
srvctl add service -d db_name -s srv_epm1 -r instance_name -a instance_name -P basic -e select -m basic -z 180 -w 5
例如:
srvctl add service -d oaecolog -s svoaecolog -r oaecolog1 -a oaecolog2 -P basic -e select -m basic -z 180 -w 5
TFA-policy 选项为basic 或preconnect

例如:
1,手工添加service

su - oracle

[oracle@dtydb3 ~]$ srvctl add service -d epmdb -s srv_epm1 -r epmdb1 -a epmdb2 -P basic -e select -m basic -z 180 -w 5

2,查看service状态和属性

sys@EPMDB1(10.4.124.233)> select name, failover_method, failover_type, goal, clb_goal from dba_services;

NAME FAILOVER_M FAILOVER_TYPE GOAL CLB_G
-------------------- ---------- ---------------------------------------------------------------- ------------ -----
SYS$BACKGROUND NONE SHORT
SYS$USERS NONE SHORT
epmdb LONG
srv_epm1 BASIC SELECT NONE LONG

[grid@dtydb3 ~]$ crsctl status resource ora.epmdb.srv_epm1.svc -t

NAME TARGET STATE SERVER STATE_DETAILS

Cluster Resources

ora.epmdb.srv_epm1.svc

  1        ONLINE  ONLINE       dtydb3                                       

[grid@dtydb3 ~]$ srvctl status service -d epmdb
Service srv_epm1 is running on instance(s) epmdb1
[grid@dtydb3 ~]$ srvctl config service -d epmdb -s srv_epm1
Service name: srv_epm1
Service is enabled
Server pool: epmdb_srv_epm1
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: epmdb1
Available instances: epmdb2

[grid@dtydb3 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-JUN-2012 15:22:46

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 04-MAY-2012 14:36:04
Uptime 40 days 0 hr. 46 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/11.2.0/grid/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/dtydb3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.124.243)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.124.233)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "epmdb" has 1 instance(s).
Instance "epmdb1", status READY, has 1 handler(s) for this service...
Service "srv_epm1" has 1 instance(s).
Instance "epmdb1", status READY, has 1 handler(s) for this service...
[grid@dtydb3 ~]$

3,修改服务,如果创建不正常的话
修改服务

---srvctl modify service -d epmdb -s srv_epm1 -r epmdb2 -a epmdb1 -P basic -e select -m basic -z 100 -w 2

--删除服务

srvctl remove service -d epmdb -s srv_epm1 -f

4,TAF测试

tnsname.ora配置
tydb_srv_epm1 =
? (DESCRIPTION =
??? (LOAD_BALANCE = NO)
??? (ADDRESS = (PROTOCOL = TCP)(HOST = db1-vip)(PORT = 1521))
??? (ADDRESS = (PROTOCOL = TCP)(HOST = db2_vip)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVICE_NAME = srv_epm1)
??? )
? )

关闭实例1
shutdown immediate

sqlplus system/oracle@tydb_srv_epm1

SQL> select instance_name from v$instance;

INSTANCE_NAME

epmdb2

成功切换到第二个节点

恢复还原

重启实例1 startup
切换service到实例1
su - oracle

srvctl relocate service -d epmdb -s srv_epm1 -i epmdb2 -t epmdb1

-------------查询索引的创建语句
set linesize 180
set pages 999
set long 90000
select dbms_metadata.get_ddl('INDEX','索引名','用户名') from dual ;

set linesize 180
set pages 999
set long 90000
select dbms_metadata.get_ddl('INDEX','MODEVIEWLOG_27142_OPERATETYPE','ECOLOGY') from dual;
例如:
CREATE INDEX "ECOLOGY"."MODEVIEWLOG_27142_OPERATETYPE" ON "ECOLOGY"."MODEVIEWL
OG_27142" ("RELATEDID", "OPERATETYPE", "OPERATEUSERID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "ECOLOGY"

------------------查询表的创建时间
SQL> select created from dba_objects where object_name in ('MODEDATASHARE_27142_SET_IN','MODEDATASHARE_27142_SOURCEID','MODEDATASHARE_27142_TYPE','M
ODEVIEWLOG_27142_OPERATETYPE','PK_MODEDATASHARE_27142','PK_MODEDATASHARE_27142_SET','PK_MODEVIEWLOG_27142');

CREATED

19-APR-19
19-APR-19
19-APR-19
19-APR-19
19-APR-19
19-APR-19
19-APR-19

7 rows selected.
--------------查询表的创建语句
set linesize 180
set pages 999
set long 90000
select dbms_metadata.get_ddl('INDEX','表名','用户名') from dual ;

----------------------------------------dblink创建(迁移)
源端:
create user link identified by userlink;
grant select any table to link;
grant connect, resource to link;
grant create any view to link;
grant select on dictionary to link;

目标端:
create public database link sclink connect to link identified by userlink USING 'oasc'
tns文件添加:

[oracle@oadb01 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin

oasc=
(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.51.18.100 )(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = ecology)
)

)

删除
drop public database link SCLINK;
!oerr ora 12169
查询dblink创建语句:
select dbms_metadata.get_ddl('DB_LINK','SCLINK','PUBLIC') from dual;

select * from dba_users@sclink;
select db_link from dba_db_links;
select JOB,NEXT_DATE,NEXT_SEC,FAILURES,BROKEN from dba_jobs;
alter session set events '10046 trace name context forever,level 12';

---------------------------查看回收站是否有这个表:

select OWNER,OBJECT_NAME,ORIGINAL_NAME,DROPTIME from dba_recyclebin where ORIGINAL_NAME='T_CJ_CJPM';

--------------------------有关scn查询
set serverout on
declare
v_autorollover_date date;
v_target_compat number;
v_RSL number;
v_hr_in_scn number;
v_hr_in_sec number;
v_t4 number;
v_max_cmpat number;
v_isenabled boolean;
v_current_compat number;
begin
dbms_scn.GETCURRENTSCNPARAMS(v_RSL,v_hr_in_scn,v_hr_in_sec,v_current_compat,v_max_cmpat);
dbms_scn.GETSCNAUTOROLLOVERPARAMS(v_autorollover_date,v_target_compat,v_isenabled);
dbms_output.put_line('Current SCN compatibility:'||v_current_compat);
dbms_output.put_line('Current SCN RATE:'||round((v_hr_in_scn/v_hr_in_sec)/1024)||'k');
if(v_isenabled) then
dbms_output.put_line('AUTO SCN compatibility rollover is ENABLED!!!');
dbms_output.put_line('AUTO rollover time:'||to_char(v_autorollover_date,'YYYY/MM/DD'));
dbms_output.put_line('AUTO rollover target value:'||v_target_compat );
else
dbms_output.put_line('AUTO SCN compatibility rollover is DISABLED!!!');
end if;
end;
/

------------------------------------------------------------------------------------------------------------------------------DG有关的
1.查看最早归档及归档应用情况

select thread#,sequence#,archived,applied,deleted,status from v$archived_log order by 2 asc;

2.查看是否有逻辑软件占用,OGG/DSG,查看捕获进程情况

SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;
3.select process,status,thread#,sequence# from v$managed_standby;
4,select group#,thread#,status,SEQUENCE# from v$log;
5,select status,thread# from v$standby_log;
6,select * from v$archive_gap;主备值需要一样
7,select max(sequence#) from v$log;
archive log list;要和sequence#值有对应的上的就可以
8,select switchover_status from v$database;
9,set numwidth 30
select current_scn from v$database;

SQL> select name,value from V$diag_info where name in('Diag Trace','Diag Alert');

col file_name for a45
set linesize 200

启停dg应用
alter database recover managed standby database cancel ;
alter database recover managed standby database using current logfile disconnect from session;

启停dg
show parameter log_archive_dest_state_=defer
show parameter log_archive_dest_ 查看指向
log_archive_dest_state_2=defer 修改状态
alter system set log_archive_dest_state_2=defer scope=both sid='*';
alter system set log_archive_dest_state_2='enable' scope=both sid='*';

传密码文件:
scp orapwzhfwdb1 db2:/u01/app/oracle/product/11.2.0/db_1/dbs/o
orapwzhfwdb1

查看最大日志数(主备是否一致)
select thread#,max(sequence#) from v$log_history group by thread#;
alter system archive log current;
查看dg进程是否正常
select inst_id,dest_id,status,error from GV$ARCHIVE_DEST WHERE DEST_ID<=3;、

检查是否存在GAP
SQL> SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

GV$ARCHIVED_LOG
select INST_ID,NAME,SEQUENCE#,CREATOR,STANDBY_DEST,ARCHIVED,APPLIED,STATUS from gv$archived_log order by 1,3;

GV$ARCHIVE_DEST

---------------------------Switchover切换步骤---------------------------------------------------------------

  1. 在主库,查询switchover_status 列确认是否可行

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------- ---------- ---------- -------------------- -------------------- ----------
PRIMARY uniquepdg READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

如果是to standby,那么可行;
如果是session active,可加 with session shutdown;

  1. 在主库执行switchover切换
    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (with session shutdown);
  2. 重启之前的主库
    SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

  1. 在备库,确认是否可完成
    SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------- ---------- ---------- -------------------- -------------------- ----------
PHYSICAL STANDBY uniquesdg READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO_PRIMARY

如果是session active,可加 with session shutdown 如果是 to primary,直接运行

  1. 切换备库为主库
    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (with session shutdown);
  2. 完成切换,打开主库对外服务
    SQL> ALTER DATABASE OPEN;
  3. 在现备库重新启动到只读模式并启用实时日志应用服务
    SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

---------------------------Failover切换步骤---------------------------------------------------------------

  1. 在备库执行failover
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
  2. 在备库切换为主库
    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
  1. 完成切换,打开主库对外服务
    SQL> ALTER DATABASE OPEN;

-- 查看ADG设置
set lines 200 pages 999
select open_mode,protection_mode,protection_level,switchover#,database_role,switchover_status from v$database;
select DBID, DB_UNIQUE_NAME,open_mode,protection_mode,protection_level,switchover#,database_role,switchover_status from v$database;

-- 通过查询备库状态 V$MANAGED_STANDBY:


SELECT PROCESS, STATUS, CLIENT_PROCESS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
-- col PROCESS for a10
-- col STATUS for a15
-- col CLIENT_PID for a10
-- col PID for a10

------------------------------------------ 查看归档信息和GAP
SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
select * from V$LOG_HISTORY;
select * from v$archive_log;
select * from V$ARCHIVE_GAP;

-------------------------------------------检查备库已经接收到的 sequence# 号
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

--------------------------------------------dg的主备库开启与停止

DG的开启,首先开启standby数据库,然后再开启primary数据库。

1、使用ORACLE用户登陆standby数据库主机,再将备机开启到管理模式
$sqlplus “/as sysdba”
SQL>startup mount --启动ORACLE数据库到MOUNT状态
SQL>alter database recover managed standby database disconnect from session; --开启STANDBY数据库REDO应用
2、使用ORACLE用户登陆primary数据库主机将数据库开启
$sqlplus “/as sysdba”
SQL>startup

DG 的关闭,首先关闭primary数据库,然后关闭standby数据库

1,使用ORACLE用户登陆primary数据库主机将数据库关闭
$sqlplus “/as sysdba”
SQL>shutdown immediate
2,使用ORACLE用户登陆standby数据库主机将数据库关闭
$sqlplus “/as sysdba”
SQL>alter database recover managed standby database cancel; --取消STANDBY数据库REDO应用
SQL>shutdown immediate

check,查看归档是否及时应用
先检查备库的归档日志同步情况
SQL> SELECT NAME,applied FROM v$archived_log;
如果发现全部都是YES,证明归档日志已经在同步。

----------------------------------------------物化视图
查询某用户的物化视图
select * from user_mviews;
创建物化视图
create materialized view VW_PLAN_VOUCHER
refresh force on demand
start with to_date('13-03-2020 14:56:43', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
as
select * from vw_plan_voucher@lhxq19;
删除物化视图
drop materialized view VW_PLAN_VOUCHER;

expdp数据迁移
1、确定需要迁移的用户及表
2、确定源端数据导出是否有足够的空间
3、确定目标端是否有足够的空间上传及导入备份文件
4、确定目标端表空间和用户是否需要调整
估算导入时间,操作注意事项。 表空间的可扩展大小,至少应该达到expdp估算空间未压缩大小的2倍。

常用语句:
1、创建表空间
create tablespace XXX logging datafile '/datafile/XXX.dbf' size 500m autoextend on next 50m extent management local (最大大小未设置的情况下,默认32G,设置不限制大小,不推荐,maxsize unlimited)
2、手动添加数据文件
alter tablespace add datafile '/datafile/XXX1.dbf' size 5000m
3、创建目录
create direactory XXX as '/datafile';
导入
impdp system/XXXX directory=xxx dumpfile= XXXX.dmp logfile=XXXX.log exclude=STATISTICSE;
导出
expdp system/XXXX directory=xxxx network_link=expdp2019 dumpfile=xxx.dmp logfile=xxx.log
注意导入的时候,如果非必要,排除统计信息,建设干扰