表空间 使用 oracle 查询 表级锁 锁表 SQL语句 数据库 等待最多 实际SQL 会话 连接数 死锁 进程

--查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       　　D.TOT_GROOTTE_MB "表空间大小(M)",
       　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
                       D.TOT_GROOTTE_MB * 100,
                       2),
                 '990.99') || '%' "使用比",
       　　F.TOTAL_BYTES "空闲空间(M)",
       　　F.MAX_BYTES "最大块(M)" 　　FROM (SELECT TABLESPACE_NAME,
                                             　　ROUND(SUM(BYTES) /
                                                     (1024 * 1024),
                                                     2) 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(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 　　FROM SYS.DBA_DATA_FILES DD 　　GROUP BY DD.TABLESPACE_NAME) D 　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 　　ORDER BY 1;

--查找当前表级锁的SQL

select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;
--杀掉锁表进程

alter system kill session '436,35123';

--监控当前数据库谁在运行什么SQL语句

select osuser, username, sql_text
  from v$session a, v$sqltext b
 where a.sql_address = b.address
 order by address, piece;

--找使用CPU多的用户session

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;

--查看死锁信息
SELECT (SELECT username FROM v$session WHERE SID = a.SID) blocker,
       a.SID,
       'is blocking',
       (SELECT username FROM v$session WHERE SID = b.SID) blockee,
       b.SID
  FROM v$lock a, v$lock b
 WHERE a.BLOCK = 1
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2;

--查询当前连接会话数
select s.value, s.sid, a.username
  from v$sesstat S, v$statname N, v$session A
 where n.statistic# = s.statistic#
   and name = 'session pga memory'
   and s.sid = a.sid
 order by s.value;
--查询会话执行的实际SQL
SELECT a.SID, a.username, s.sql_text
  FROM v$session a, v$sqltext s
 WHERE a.sql_address = s.address
   AND a.sql_hash_value = s.hash_value
   AND a.status = 'ACTIVE'
 ORDER BY a.username, a.SID, s.piece;
--等待最多的SQL
SELECT a.program,
       a.session_id,
       a.user_id,
       d.username,
       s.sql_text,
       SUM(a.wait_time + a.time_waited) total_wait_time
  FROM v$active_session_history a, v$sqlarea s, dba_users d
 WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
   AND a.sql_id = s.sql_id
   AND a.user_id = d.user_id
 GROUP BY a.program, a.session_id, a.user_id, s.sql_text, d.username;

 

 --查看被锁的表
SELECT p.spid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
  FROM v$process p, v$session a, v$locked_object b, all_objects c
 WHERE p.addr = a.paddr
   AND a.process = b.process
   AND c.object_id = b.object_id
   AND B.ORACLE_USERNAME='BICP_HA';
--查看是哪个进程锁的
SELECT sid, serial#, username, osuser
  FROM v$session
 where username = 'BICP_HA';
--杀掉这个进程  'sid,serial#';
alter system kill session '158,5874';