需要管理员用户下执行(sys/sysdba)
select * from v$lock where lmode > 0 and type in ('TM','TX');
select * from v$session where username = 'PROD_JCJ'
select sid,serial#,username from v$session where sid = '13313';
alter system kill session '15489,47476';
alter system kill session '16215,2972';
alter system kill session '2545,51671';
alter system kill session '609,63963';
alter system kill session '6415,45951';
alter system kill session '2306,64117';
select * from v$locked_object a, dba_objects b where b.object_id = a.object_id
select b.owner,b.object_name,a.session_id,a.locked_mode
from dba_objects b, v$locked_object a
where b.object_id = a.object_id
and b.object_name='PER_ALL_ASSIGNMENTS_F';
select * from v$parameter where name='processes';
select count(*) from v$process;
select value from v$parameter where name = 'processes'
select * from V$diag_Info;
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid and s.prev_sql_addr = a.address
order by sid,s.serial#;
SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,
ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC
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 se.SID, ses.username, ses.osuser, n.NAME, se.VALUE
from v$statname n, v$sesstat se, v$session ses
where n.statistic# = se.statistic# and
se.sid = ses.sid and
ses.username is not null and
n.name in ('CPU used by this session',
'db block gets',
'consistent gets',
'physical reads',
'free buffer requested',
'table scans (long tables)',
'table scan rows gotten',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'session uga memory max' ,
'session pga memory max')
order by sid, n.statistic#;
select b.username username,a.buffer_gets reads,
a.executions exec,a.buffer_gets/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.buffer_gets > 100000
order by a.buffer_gets desc;
select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;
select disk_reads,sql_text
from (select sql_text,disk_reads,
dense_rank() over
(order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <=5;
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
dense_rank() over
(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank<=5;
select * from V$diag_Info;
select sid,event,p1,p1text from v$session_wait;
select event,count(*) from v$session_event group by event order by count(*) desc ;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步