oracle 相关操作 - 《编程日志》

死锁相关问题
查看前台正在发生的sql
数据表占空间大小
查看表空间碎片大小
查看表空间占磁盘大小
查看表的大小，倒叙排
查看表空间物理文件名称及大小
查看oracle表空间使用率
查看temp表空间实际使用磁盘大小
查看session使用回滚字段
查看临时表空间大小，及使用临时表空间语句
监控表空间 I/O 比例
性能最差的sql
读磁盘数超过100次的sql
查找消耗资源比较的sql
执行最频繁的sql
查询使用CPU多的用户
查询oracle执行过的语句及用户

死锁相关问题

1.1 查看有没有死锁生成，并生成kill session语句

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
 注意： 应当注意对于 sid 在 100 以下的应当谨慎，
 可能该进程对应某个application，如对应某个事务,可以 kill

1.2 查看导致死锁的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;

1.3 查看谁锁了谁

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;

查看前台正在发生的sql

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 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 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))                         剩余空间,
             sum(nvl(a.bytes,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;

查看表的大小，倒叙排

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,round(sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))),2) FSFI
    from dba_free_space
    group by tablespace_name order by 1;

查看oracle表空间使用率

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
————————————————
版权声明：本文为CSDN博主「小小工匠」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/yangshangwei/article/details/52449489

查看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, 0) / 1024 / 1024, 2) /
             round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
             2) as "Used_Rate(%)"
  from SYS.V_$TEMP_SPACE_HEADER f,
       DBA_TEMP_FILES           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.sid,
        s.serial#,
        s.username 用户名,
        t.status,
        t.cr_get,
        t.phy_io,
        t.used_ublk,
        t.noundo,
        substr(s.program, 1, 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;

查看临时表空间大小，及使用临时表空间语句

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;

监控表空间 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;

性能最差的sql

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;

查询使用CPU多的用户

select a.sid,spid,status,substr(a.program,1,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;

查询oracle执行过的语句及用户

select a.USERNAME 登录Oracle用户名,
a.MACHINE 计算机名,
SQL_TEXT,
b.FIRST_LOAD_TIME,
b.SQL_FULLTEXT
from v$sqlarea b, v$session a
where a.sql_hash_value = b.hash_value
and b.FIRST_LOAD_TIME between ‘2016-11-01/09:24:47’ and
‘2016-11-31/09:24:47’
order by b.FIRST_LOAD_TIME desc;

查看DML 死锁会话对象
select sid,
blocking_session,
LOGON_TIME,
sql_id,
status,
event,
seconds_in_wait,
state,
BLOCKING_SESSION_STATUS
from v$session
where event like ‘enq%’
and state = ‘WAITING’
and BLOCKING_SESSION_STATUS = ‘VALID’

附录：
https://blog.csdn.net/yangshangwei/article/details/52449489
https://blog.csdn.net/yangshangwei/article/details/52917132