本文转载自https://blog.csdn.net/yangshangwei/article/details/52449489
死锁后的解决办法
如果死锁不能自动释放，就需要我们手工的 kill session
生成Kill Session语句
查看有无死锁对象，如有 kill session
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
如果有，会返回类似与如下的信息：
alter system kill session '761,876';
.....
kill session：
执行 alter system kill session ‘761,876’(sid 为 761);
注意： 应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以 kill
查看导致死锁的 sql
SELECT s.sid,q.sql_text
FROM v$sqltext q,v$session s
WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的
ORDER BY piece;
执行后，输入对应的sid即可查看对应的sql.
查看谁锁了谁
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
FROM v$lock l1,v$session s1,v$lock l2,v$session s2
WHERE 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;
或者
推荐这个，因为使用的是 v$locked_object
SELECT LPAD(' ',DECODE(l.xidusn,3,0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id,xidusn DESC;
V$LOCKED_OBJECT只能报发生等待的表级锁,不能报发生等待的行级锁。
这里写图片描述
ORA-00054 资源正忙，要求指定 NOWAIT
演示：
select * from emp for update ;--通过for update 获取一个排它锁
sql>select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l,v$session s,v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
对象名称 SID SERIAL# 系统进程号
EMP 1411 8865 32720
在另外一个会话中执行
ALTER SYSTEM KILL SESSION '1411,8865';
查询绑定变量使用的实际值
1,sql还在shared pool中，没有被aged out 替换sql ID 值即可
select sql_id,name,datatype_string,last_captured,value_string
from v$sql_bind_capture where sql_id = '7nqt558g5gmyr' order by LAST_CAPTURED,
POSITION;
2.请自行替换sql_id，此时是从awr中查询(sql 被 aged out 出 shared pool)
select instance_number,
sql_id,
name,
datatype_string,
last_captured,
value_string
from dba_hist_sqlbind
where sql_id = 'fahv8x6ngrb50'
order by LAST_CAPTURED,POSITION;
监控事例的等待
select event,sum(decode(wait_Time,1)) "Prev",
sum(decode(wait_Time,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4 ;
回滚段的争用情况
select name,waits,gets,waits / gets "Ratio"
from v$rollstat a,v$rollname b
where a.usn = b.usn;
查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r,v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
查看控制文件
SELECT NAME FROM v$controlfile;
查看日志文件
SELECT MEMBER FROM v$logfile;
select user_name,sql_text
from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program
from v$session
where status='ACTIVE'));
数据表占用空间大小情况
select segment_name,tablespace_name,bytes,blocks
from user_segments
where segment_type = 'TABLE'
ORDER BY bytes DESC,blocks DESC;
查看表空间碎片大小
select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))),2) FSFI
from dba_free_space
group by tablespace_name order by 1;
查看表空间占用磁盘情况
select
b.file_id 文件ID号,b.tablespace_name 表空间名,b.bytes 字节数,(b.bytes-sum(nvl(a.bytes,0))) 已使用,sum(nvl(a.bytes,0)) 剩余空间,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
查看表的大小，倒序排列
每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。
段（segments）的定义：如果创建一个堆组织表，则该表就是一个段
SELECT SEGMENT_NAME,SUM(BYTES) / 1024 / 1024 MBYTESE
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME
order by MBYTESE desc;
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024),0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
查看Oracle 表空间使用率
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
SPACE - NVL (FREE_SPACE,0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE,0) / SPACE) 100,2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 1024),2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE,0) / SPACE 100,2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE,0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
ROUND (SUM (BYTES_USED) / (1024 1024),2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 1024 1024) "表空间大小(G)",
free / (1024 1024 1024) "表空间剩余大小(G)",
(total - free) / (1024 1024 1024) "表空间使用大小(G)",
round((total - free) / 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
WHERE a.tablespace_name = b.tablespace_name
查看Temp 表空间实际使用磁盘大小
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 / 1024,2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used,0)) / 1024 / 1024,
2) "Free MB",
round(nvl(p.bytes_used,0) / 1024 / 1024,2) "Used MB",
round((round(nvl(p.bytes_used,2) /
round((f.bytes_free + f.bytes_used) / 1024 / 1024,2)) * 100,
2) as "UsedRate(%)"
from SYS.V$TEMP_SPACE_HEADER f,
DBA_TEMPFILES d,
SYS.V$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;
查看session使用回滚段
SELECT r.name 回滚段名,
s.serial#,
s.username 用户名,
t.status,
t.cr_get,
t.phy_io,
t.usedublk,
t.noundo,
substr(s.program,78) 操作程序
FROM sys.v$session s,sys.v$transaction t,sys.v$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io;
查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID,segtype,blocks * 8 / 1000 "MB",sql_text
from v$sort_usage sort,v$session sess,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.sql_ADDRESS
order by blocks desc;
Temp表空间上进程的查询
select a.tablespace,b.sid,b.serial#,a.blocks,c.sql_text
from v$sort_usage a,v$session b,v$sqltext c
where a.session_addr = b.saddr
and b.sql_address = c.address
order by a.tablespace,c.address,c.piece;
查看SGA区剩余可用内存
select name,
sgasize/1024/1024 "Allocated(M)",
bytes/1024 "*空间(K)",
round(bytes/sgasize100,2) "**空间百分比(%)"
from (select sum(bytes) sgasize from sys.v$sgastat) s,sys.v$sgastat f
where f.name = 'free memory';
监控表空间I/O比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
监控SGA命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a,v$sysstat b,v$sysstat c
where a.statistic# = 38 and
b.statistic# = 39 and
c.statistic# = 40 ;
监控 SGA 中字典缓冲区的命中率
select parameter,Getmisses,getmisses/(gets+getmisses)100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter,getmisses ;
监控 SGA **享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins",sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name,misses,immediate_gets,immediate_misses,
Decode(gets,misses/gets100) ratio1,
Decode(immediate_gets+immediate_misses,
immediate_misses/(immediate_gets+immediate_misses)100) ratio2
FROM v$latch WHERE name IN ('redo allocation','redo copy');
监控内存和硬盘的排序比率，最好使它小于 .10
SELECT name,value
FROM v$sysstat
WHERE name IN ('sorts (memory)','sorts (disk)') ;
监控字典缓冲区
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE ;
非系统用户建在SYSTEM表空间中的表
SELECT owner,table_name
FROM DBA_TABLES
WHERE tablespace_name in('SYSTEM','USER_DATA') AND
owner NOT IN('SYSTEM','SYS','OUTLN','ORDSYS','MDSYS','SCOTT','HOSTEAC');
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM<100;
读磁盘数超100次的sql
select * from sys.v_$sqlarea where disk_reads>100;
查找消耗资源比较的sql语句
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username,se.sid;
最频繁执行的sql
select * from sys.v_$sqlarea where executions>100;
select a.sid,spid,status,substr(a.program,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc;
当前每个会话使用的对象数
SELECT a.sid,s.terminal,s.program,count(a.sid)
FROM V$ACCESS a,V$SESSION s
WHERE a.owner <> 'SYS'AND s.sid = a.sid
GROUP BY a.sid,s.program
ORDER BY count(a.sid) ;
查看数据库库对象
SELECT owner,object_type,COUNT(*) count#
FROM all_objects
GROUP BY owner,status;
查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product,6) = 'Oracle';
查看数据库的创建日期和归档方式
SELECT created,log_mode,log_mode FROM v$database;
检查角色和权限设置
根据用户名进行授权的对象级特权
select b.owner || '.' || b.table_name obj,
b.privilege what_granted,
b.grantable,
a.username
from sys.dba_users a,sys.dba_tab_privs b
where a.username = b.grantee
order by 1,2,3;
根据被授权人进行授权的对象级特权
Select owner || '.' || table_name obj,
privilege what_granted,
grantable,
grantee
from sys.dba_tab_privs
where not exists (select 'x' from sys.dba_users where username = grantee)
order by 1,3;
根据用户名进行授予的系统级特权
select b.privilege what_granted,b.admin_option,a.username
from sys.dba_users a,sys.dba_sys_privs b
where a.username = b.grantee
order by 1,2;
根据被授权人进行授予的系统级特权
select privilege what_granted,admin_option,grantee
from sys.dba_sys_privs
where not exists (select 'x' from sys.dba_users where username = grantee)
order by 1,2;
根据用户名授予的角色
select b.granted_role ||
decode(admin_option,'YES',' (With Admin Option)',null) what_granted,sys.dba_role_privs b
where a.username = b.grantee
order by 1;
根据被授权人授予的角色
select granted_role ||
decode(admin_option,
grantee
from sys.dba_role_privs
where not exists (select 'x' from sys.dba_users where username = grantee)
order by 1;
用户名及已被授予的相应权限
select a.username,
b.granted_role ||
decode(admin_option,null) what_granted
from sys.dba_users a,sys.dba_role_privs b
where a.username = b.grantee
UNION
select a.username,
b.privilege ||
decode(admin_option,sys.dba_sys_privs b
where a.username = b.grantee
UNION
select a.username,
b.table_name || '-' || b.privilege ||
decode(grantable,' (With Grant Option)',sys.dba_tab_privs b
where a.username = b.grantee
order by 1;
Select username,profile,default_tablespace,temporary_tablespace,created
from sys.dba_users
order by username;
等待事件V$视图
在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。
马上该谁等待–查询V$SESSION_WAIT / V$SESSION
select event,
sum(decode(wait_time,0)) "Waiting Now",1)) "PrevIoUs Waits",
count() "Total"
from v$session_wait
group by event
order by count();
马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT
SELECT /+ ordered /
sid,event,owner,segment_name,segment_type,p1,p2,p3
FROM v$session_wait sw,dba_extents de
WHERE de.file_id = sw.p1
AND sw.p2 between de.block_id and de.block_id + de.blocks - 1
AND (event = 'buffer busy waits' OR event = 'write complete waits')
AND p1 IS NOT null
ORDER BY event,sid;
谁在等待 - 最后10 个等待数–查询V$SESSION_WAIT_HISTORY
SELECT /+ ordered /
sid,sid;
查找P1,P2,P3代表什么–查询 V$EVENT_NAME
select event#,parameter1 p1,parameter2 p2,parameter3 p3
from v$event_name
where name in ('buffer busy waits','write complete waits');
会话开始后的所有等待数–查询 V$SESSION_EVENT
select sid,total_waits,time_waited,event_id
from v$session_event
where time_waited > 0
order by time_waited;
类的所有会话等待数–查询V$SESSION_WAIT_CLASS
select sid,wait_class,total_waits from v$session_wait_class;
系统启动后的所有等待数–查询V$SYSTEM_EVENT
select event,event_id
from v$system_event
where time_waited > 0
order by time_waited;
类的系统等待数–查询V$SYSTEM_WAIT_CLASS
select wait_class,total_waits
from v$system_wait_class
order by total_waits desc;
类的系统等待数–查询V$ACTIVE_SESSION_HISTORY
–In the query below,the highest count session is leader in non-idle wait events.
select session_id,count(1)
from v$active_session_history
group by session_id
order by 2;
–In the query below,find the sql for the leader in non-idle wait events.
select c.sql_id,a.sql_text
from v$sql a,
(select sql_id,count(1)
from v$active_session_history b
where sql_id is not null
group by sql_id order by 2 desc) c
where rownum <= 5
order by rownum;
自动工作量仓库(AWR) 的基本信息
自动工作量仓库(AWR)在默认情况下，仓库用小时填充，保留期是7天。
AWR使用多少空间
sql> Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
SM/AWR Server Manageability - Automatic Workload Repository 215616
sql>
系统上最原始的AWR信息是什么？
sql> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
20-OCT-16 12.04.49.088829000 AM -04:00
什么是AWR信息的保留期？
sql> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
31
将AWR信息的保留期更改为15天？
sql> EXEC dbms_stats.alter_stats_history_retention(15);
PL/sql 过程已成功完成。
开启sql跟踪后，会生成一个trace文件，通过初始化参数user_dump_dest配置其所在目录，该参数的值可以通过下面方法获取到：
select name,value from v$parameter where name = 'user_dump_dest'
trace文件的名字是独立于版本和平台的，在大部分常见的平台下，命名结构如下：
{instance name}{process name}{process id}.trc
1）instance name
初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。
2）process name
产生跟踪文件进程的名字的小写值。对于专有服务器进程，使用ora，对于共享服务器进程，可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程，可以通过v$px_process视图server_name列获得，对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。
3）process id
操作系统层面的进程标记。这个值可以通过v$process视图的spid列获取。
select s.SID,
s.SERVER,
lower(case
when s.SERVER in ('DEDICATED','SHARED') then
i.INSTANCENAME || '' || nvl(pp.SERVERNAME,nvl(ss.NAME,'ora')) || '' ||
p.SPID || '.trc'
else
null
end) as trace_file_name
from v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
where s.PADDR = p.ADDR
and s.SID = pp.SID(+)
and s.PADDR = ss.PADDR(+)
and s.TYPE = 'USER'
and s.SID = 'your sid'
order by s.SID
将上面的’your sid’替换为你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session视图中得到，或者直接查询当前session的sid：
select userenv('sid') from dual
或者
select sid from v$mystat a where rownum=1 ;
将路径（user_dump_dest）和文件名结合在一起，我们就得到了trace文件的完整路径。
而在Oracel 11g中，查询当前会话生成的trace文件则非常简单：
select value from v$diag_info where name = 'Default Trace File'
原文链接：https://www.f2er.com/oracle/205798.html