DBA日常维护SQL脚本_自己编写的
查询碎片程度高（实际使用率小于30%）的表，也就是可以收缩的表
条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了
算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满
AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3
order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc
查询索引碎片的比例（索引删除行数除以索引总行数的百分比>30%即认为索引碎片大），也就是需要重建的索引
select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30;
集群因子clustering_factor高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
或
select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner
not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS')
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
根据sid查spid或根据spid查sid
select s.sid,s.serial#,p.spid,p.terminal,s.LOGON_TIME,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.action,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY
根据sid查看具体的sql语句(不要加条件v$session.status=’ ACTIVE’,比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from v$session时会发现另一个窗口在v$session.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from v$session而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。一个sid可能执行过很多个sql，所以有时需要的sql通过如下查不到是正常的，比如查询到某死锁源sid，通过如下查询可能只是个select语句，而真正引起死锁的sql却查不到，是因为可能这个sid持续了很长时间，这个sid之前执行的一些sql在v$sql可能已经被清除了。)
select username,sid,SERIAL#,LOGON_TIME,status,PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "客户端机器进程号",osuser,sql_text from v$session a,v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece;
或
select username,sid,SERIAL#,LOGON_TIME,status,sql_fulltext,PROGRAM,CLIENT_IDENTIFIER,machine,a.action,PROCESS "客户端机器进程号",osuser from v$session a,v$sql b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid
根据spid查询具体的sql语句(不要加条件v$session.status=’ ACTIVE’,比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from v$session时会发现另一个窗口在v$session.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from v$session而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。)
Select ss.SID,ss.SERIAL#,ss.LOGON_TIME,pr.SPID,sa.SQL_FULLTEXT,ss.machine, ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER,ss.action,ss.PROCESS "客户端机器进程号", ss.STATUS, ss.OSUSER,ss.status,ss.last_call_et,sa.sql_text
from v$process pr, v$session ss, v$sql sa
where pr.ADDR = ss.PADDR
and DECODE(ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)=sa.hash_value
and pr.spid=&spid
查看历史session_id的SQL来自哪个IP(当然这是个误解，都是历史的了，怎么可能还查到spid，其实查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)
DB_ora_29349.trc中出现如下
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726
通过表V$ACTIVE_SESSION_HISTORY来查，如下
select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807
查询上面的machine的IP是多少
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost'
通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可
[oracle@dwdb trace]$ netstat -anp |grep 17630
tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB
tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB
出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器
查询死锁堵塞的会话sid
最简单的一个SQL
select * from V$SESSION_BLOCKERS
select * from dba_waiters
最常用的一个SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话STATUS持续时间_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'
可以把两者SID放入v$session，发现LOGON_TIME字段FINAL_BLOCKING_SESSION比SID要早
BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID.
如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行
查询锁住的DDL对象
select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid
查询当前正在执行的sql
SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text
FROM v$process,v$session s,v$sql
WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value
查询正在执行的SCHEDULER_JOB
select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process where session_id=sid and paddr=addr
查询正在执行的dbms_job
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr
查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值
select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;
TOP 10 执行次数排序
select *
from (select executions,username,PARSING_USER_ID,sql_id,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)
where rownum <=5;
TOP 10 物理读排序（消耗IO排序，即最差性能SQL、低效SQL排序）
select *
from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)
where rownum <=5;
（不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
TOP 10 逻辑读排序（消耗内存排序）
select *
from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)
where rownum <=5;
（不要使用BUFFER_GETS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
TOP 10 CPU排序(单位秒=cpu_time/1000000)
select *
from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc)
where rownum <=5;
（不要使用CPU_TIME/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
查询等待事件
select event,sum(decode(wait_time,0,0,1)) "之前等待次数", sum(decode(wait_time,0,1,0)) "正在等待次数",count(*) from v$session_wait group by event order by 4 desc
查询当前正在消耗temp空间的sql语句
Select distinct se.username,
se.sid,
su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,
su.tablespace,
sql_text
from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr=se.saddr
and su.sqlhash=s.hash_value
and su.sqladdr=s.address
查询需要使用绑定变量的sql,10G以后推荐第二种
（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和HASH_VALUE也不变。）
第一种
select * from (
select count(*),sql_id, substr(sql_text,1,40)
from v$sql
group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10
第二种
count(1)>10表示类语句运行了10次以上
select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10)
查看数据文件可用百分比
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE
order by b.tablespace_name;
查看数据文件可用百分比（文件自增长的情况下）
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.file_id>4
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;
查看表空间可用百分比
select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by "% Free";
查看临时表空间使用率
SELECT temp_used.tablespace_name,round(total),used,
round(total - used) as "Free",
round(nvl(total-used, 0) * 100/total,1) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(decode(autoextensible,'YES',MAXBYTES,bytes))/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
或
SELECT a.tablespace_name, round(a.BYTES/1024/1024) total_M, round(a.bytes/1024/1024 - nvl(b.bytes/1024/1024, 0)) free_M,
round(b.bytes/1024/1024) used,round(b.using/1024/1024) using
FROM (SELECT tablespace_name, SUM (decode(autoextensible,'YES',MAXBYTES,bytes)) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes,sum(bytes_used) using FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
查询undo表空间使用情况
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status
查看ASM磁盘组使用率
select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" from gv$asm_diskgroup
统计每个用户使用表空间率
SELECT c.owner "用户",
a.tablespace_name "表空间名",
total/1024/1024 "表空间大小M",
free/1024/1024 "表空间剩余大小M",
( total - free )/1024/1024 "表空间使用大小M",
Round(( total - free ) / total, 4) * 100 "表空间总计使用率 %",
c.schemas_use/1024/1024 "用户使用表空间大小M",
round((schemas_use)/total,4)*100 "用户使用表空间率 %"
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by "用户","表空间名"
查看闪回区\快速恢复区空间使用率
select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE
或
select round(100*(a.space_used/space_limit),2)||'%' "已使用空间比例",a.* from v$recovery_file_dest a;
查看僵死进程，分两种（一种是会话不在的，另一种是会话标记为killed的但是会话还在的）
alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中
会话不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)
会话还在的，但是会话标记为killed
select * from v$process where addr in (select paddr from v$session where status='KILLED')
再根据上述结果中的SPID通过如下命令可以查看到process的启动时间
ps auxw|head -1;ps auxw|grep SPID
查看行迁移或行链接的表
select * From dba_tables where nvl(chain_cnt,0)<>0
chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
数据缓冲区命中率（百分比小于90就要加大db_cache_size）
SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
或
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,
round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';
共享池命中率（百分比小于90就要加大shared_pool_size）
以下两者应该都可以，看个人怎么理解
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;
查询归档日志切换频率
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 3 order by first_time,minutes;
或
select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from
v$loghist where first_time>sysdate-3 order by 1;
或
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);
查询lgwr进程写日志时每执行一次lgwr需要多少秒，在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'
查询没有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes)
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)
查询7天的db time（db time=db cpu+io time+wait time不包含空闲等待）
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') ||
to_char(END_INTERVAL_TIME, ' hh24:mi') date_time,
stat_name,
round((e_value - nvl(b_value, 0)) /
(extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
extract(minute from(end_interval_time - begin_interval_time)) * 60 +
extract(second from(end_interval_time - begin_interval_time))),
0) per_sec
from sysstat
where (e_value - nvl(b_value, 0)) > 0
and nvl(b_value, 0) > 0
查询产生热块较多的对象
x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;
导出AWR报告的SQL语句
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
查询某个SQL的执行计划
select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'
select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));
含顺序的
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));
不过要先创建xplan包，再执行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;
查询Rman的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
查询Rman备份集详细信息（未过期的，过期并已删除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1级',
0, 'Incr-0级',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', 'EXPIRED',
'ERROR')
"STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES/1024/1024/1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
查询Rman备份进度
SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE",
TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed,
TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining,
CONTEXT,target,SOFAR, TOTALWORK
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
查询执行过全表扫描的sql语句的SQL_ID和sql_fulltext
select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext
from gv$session_longops s,gv$sql v
where s.OPNAME = 'Table Scan'
and s.SQL_PLAN_OPERATION = 'TABLE ACCESS'
and s.SQL_PLAN_OPTIONS = 'FULL'
and s.sql_id=v.sql_id
order by s.LAST_UPDATE_TIME desc
查询死事务需要多长的回滚时间
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)
X$KTUXE表的一个重要功能是，可以获得无法通过v$transaction来观察的死事务信息，当一个数据库发生异常中断，或者进行延迟事务恢复时，数据库启动后，无法通过V$TRANSACTION来观察事务信息，但是X$KTUXE可以帮助我们获得这些信息。该表中的KTUXECFL代表了事务的Flag标记，通过这个标记可以找到那些Dead事务：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT(*)
------------------------ ----------
DEAD 1
NONE 2393
KTUXESIZ用来记录事务使用的回滚段块数，可以通过观察这个字段来评估恢复进度,例如如下事务回滚经过测算需要大约3小时：：
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086075
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086067
SQL> declare
l_start number;
l_end number;
begin
select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_lock.sleep(60);
select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2));
end;
/
time_H:3
把XXX用户下面的某些YYY表赋权给user,XXX\YYY要大写
set serveroutput on
--XXX要大写
declare tablename varchar2(200);
begin
for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop
tablename:=x.table_name;
dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user');
EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user';
end loop;
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26655292/viewspace-2134468/ ，如需转载，请注明出处，否则将追究法律责任。
