Oracle表锁 连接数 Session 表空间 SQL

查看所有连接数sql:

select count(1) s,machine from v$session group by machine;

查看所有占用连接数的用户：

select sid, osuser, machine, num_curs from (
select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where o.sid=s.sid 
group by o.sid, osuser, machine
order by num_curs desc)

根据sid查询SQL:

select q.sql_text
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid = 7057;

查看表锁sql：

select t2.username,
t2.sid,
t2.serial#,
t3.object_name,
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.LOGON_TIME,
t2.COMMAND,
t2.LOCKWAIT,
t2.SADDR,
t2.PADDR,
t2.TADDR,
t2.SQL_ADDRESS,
t1.LOCKED_MODE
from v$locked_object t1, v$session t2, dba_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time;

杀掉表锁：

alter system kill session '46,55050';

杀掉所有的表锁sql：

declare cursor mycur is   
select b.sid,b.serial#   
　　from v$locked_object a,v$session b   
　　where a.session_id = b.sid group by b.sid,b.serial#;   
   
begin   
  for cur in mycur   
    loop     
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');   
     end loop;   
end;

查看活动状态的连接：

select count(1) from v$session where status ='ACTIVE'

查询历史SESSION sql:

select SAMPLE_TIME,session_id,sql_id,event,p1,BLOCKING_SESSION from 
v$active_session_history where event = 'enq: TX - row lock contention'
    and sample_time > to_timestamp('2022-04-17 16:10:00','yyyy-mm-dd hh24:mi:ss')
    and sample_time < to_timestamp('2022-04-17 17:30:00','yyyy-mm-dd hh24:mi:ss')

根据sql_id查询执行的SQL：

select sql_id,sql_text from v$sql where sql_id='daqu6smsdsz6d'

可以查到SQL的锁表：

select l.session_id sid,
s.serial#,
case l.locked_mode 
      when 2 then 'Row-S 行共享(RS)'
        when 3 then 'Row-X 行独占(RX)'
          when 4 then 'Share 共享锁(S)'
            when 5 then 'S/Row-X 共享行独占(SRX)'
              when 6 then 'exclusive 独占(X)'
    else '0,1'
end as 锁模式,
l.oracle_username 登录用户,
s.user#,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
a.sql_text 锁SQL,
a.action，
s.logon_time 登录数据库时间，
s.username 数据源账户,
o.object_name,
s.osuser 客户端操作系统用户名,
s.PROGRAM 客户端应用程序,
s.COMMAND,
s.LOCKWAIT 正在等待的锁信息,
s.SADDR,
s.PADDR,
s.TADDR,
s.SQL_ADDRESS,
s.row_wait_row#,
s.row_wait_block#,
s.row_wait_obj#,
s.row_wait_file#
from v$sqlarea a, v$session s, v$locked_object l,
dba_objects o
where l.session_id = s.sid
and s.prev_sql_addr = a.address
and o.object_id=l.object_id
order by sid, s.serial#;

根据Sid查sql_id

select session_id,sql_id,sample_id,event,sample_time from v$active_session_history where session_id=6;

根据sql_id查SQL

select * from v$sqltext where sql_id='fj611d6j6cbpq' order by piece;

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 ;

查询表空间物理文件路径:

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
where tablespace_name='BYDMESTDLC';

查询表空间物理文件路径:

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;