数据库分析

• 查看被锁住的session

SELECT
  s.sid,  -- session_id
  s.serial#, --   serial# 是序列号
  s.username,  -- 用户(表空间)
  s.logon_time,  -- 登录时间
  o.owner,
  o.object_name
FROM
    v$session s,  -- v$Session 是session视图
    v$locked_object l,
    dba_objects o
WHERE
    s.sid = l.session_id
    and l.object_id = o.object_id
ORDER BY
    s.logon_time;

• 查看阻塞(blocking)他人语句

select
	decode(l.block, 1, 'blocking') blocking,  -- block(是否阻塞他人) 0不阻塞  1阻塞
	decode(l.request, 0, 'null', 'blocked') blocked,   -- request(是否被阻塞) 0没被阻塞 1-6被阻塞
	l.request,
	s.sid, s.username, s.logon_time, s.machine,
	sq.sql_id, sq.sql_text, sq.hash_value, sq.module
from v$lock l, v$session s, v$sql sq
where 1 = 1
and l.sid = s.sid
and s.prev_sql_id = sq.sql_id
and l.block ='1';

• 查看被阻塞(blocked)语句

select
	decode(l.block, 1, 'blocking') blocking,  -- block(是否阻塞他人) 0不阻塞  1阻塞
	decode(l.request, 0, 'null', 'blocked') blocked,   -- request(是否被阻塞) 0没被阻塞 1-6被阻塞
	l.request,
	s.sid, s.machine, s.username, s.logon_time,
	sq.sql_id, sq.sql_text, sq.hash_value, sq.module
from v$lock l, v$session s, v$sql sq
where 1 = 1
and l.type ='TX'
and l.sid = s.sid
and s.sql_id = sq.sql_id;

• 查看表空间占用率

SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

• 查看用户所属表空间

select  username,default_tablespace from  dba_users   where  username= 'username';