查看所有连接数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;