查询oracle用户名，机器名，锁表对象
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
from v$locked_object l, all_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid
order by sid, s.serial#;
查询导致锁表的SQL语句
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 b.sql_text
from v$session a, v$sql b
where a.sid = 6 --session_id
and a.SQL_ADDRESS = b.ADDRESS(+);
锁表查询
select count(*) from v$locked_object;
select * from v$locked_object;
查询哪个表被锁
select b.owner, b.object_name, a.session_id, a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id;
查询哪个session引起的锁表
select b.username, b.sid, b.serial#, b.logon_time
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;
杀掉进程
--3028:SID,15898:SERIAL#
alter system kill session '3028,15898';
显示正在等待锁的所有会话
select * from dba_waiters;
查询表空间使用情况（最常用）
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;
查看空间占用多的表或索引
SELECT
segment_name 对象,
segment_type 对象类型,
bytes / 1024 / 1024 MB,
tablespace_name 表空间名称
FROM
user_segments
ORDER BY
bytes DESC;
注意：查看ins用户下的表的空间要使用ins用户登录数据库
表空间不足如何处理？
1、查看表在哪个表空间
select tablespace_name,table_name from user_tables where table_name='TB_BASE_USER';
2、获取用户的默认表空间
select username,default_tablespace from dba_users where username='SCOTT'
3、找出该表空间对应的数据文件及路径
select * from dba_data_files t where t.tablespace_name = 'USERS'
4、查看表空间使用情况
5、方案一：扩展表空间
alter database datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF' resize 500M
--自动增长
alter database datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF' autoextend onnext 50m maxsize 500m;
--增加数据文件
alter tablespace tablespace_name add datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF' size 500M
6、方案二：移动至其他表空间
alter table ins.app_iops_dev_daily_nj move tablespace TBS_OSSMOB_ANDROID;
alter index index_name rebuild tablespace tablespace_name;--如果有索引的话必须重建索引
表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resize datafile，一般数据文件不要超过2G
查找使用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;
Oracle数据库查询效率低的SQL
检查低效率的语句
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 > 1 --1表示执行次数超过一次
and buffer_gets > 0
and (buffer_gets - disk_reads) / buffer_gets < 0.8 -- disk_reads是读硬盘的数量
order by 4 desc;
检查执行时间7月1日以后超过30秒的语句
select *
from v$session_longops s
where s.start_time > to_date('2017-07-01','yyyy-mm-dd')
and s.LAST_UPDATE_TIME - s.START_TIME > 30/1440/60
order by start_time;
检查目前打开的游标情况
select sql_text, count(1) from v$open_cursor where user_name = 'X' group by sql_text order by count(1) desc
检查指定时间的语句情况
select * from v$sql s where first_load_time < '2017-07-01/00:00:00' order by s.EXECUTIONS desc;
监控当前数据库谁在运行什么SQL语句
select osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address = b.address order by address, 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;
查看消耗资源最多的sql
select hash_value, executions, buffer_gets, disk_reads, parse_calls
from v$sqlarea
where buffer_gets > 10000000
or disk_reads > 1000000
order by buffer_gets + 100 * disk_reads desc;
最占用资源的查询
select b.username username,
a.disk_reads reads,
a.executions exec,
a.disk_reads / 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.disk_reads > 100000
order by a.disk_reads desc;
查看某条sql语句的资源消耗
select hash_value, buffer_gets, disk_reads, executions, parse_calls
from v$sqlarea
where hash_value = 228801498
and address = hextoraw('cbd8e4b0');
查询会话执行的实际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 b.username username,
a.buffer_gets gets,
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;
使用频率最高的5个查询语句
select sql_text, executions
from (select sql_text,
executions,
rank() over(order by executions desc) exec_rank
from v$sql)
where exec_rank <= 5;
消耗磁盘读取最多的sql
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;
Oracle数据库查询当前连接会话数
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;
等待最多的用户
SELECT
s.sid,
s.username,
sum(a.wait_time + a.time_waited) total_wait_time
FROM
v$active_session_history a,
v$session s
WHERE
a.sample_time BETWEEN sysdate - 30 / 2880
AND sysdate
GROUP BY
s.sid,
s.username
ORDER BY
total_wait_time DESC;
具有最高等待的对象
select o.owner,
o.object_name,
o.object_type,
a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, dba_objects o
where a.sample_time between sysdate - 30 / 2880 and sysdate
and a.current_obj# = o.object_id
group by o.owner, o.object_name, o.object_type, a.event
order by total_wait_time desc;
select a.session_id,
s.osuser,
s.machine,
s.program,
o.owner,
o.object_name,
o.object_type,
a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, dba_objects o, v$session s
where a.sample_time between sysdate - 30 / 2880 and sysdate
and a.current_obj# = o.object_id
and a.session_id = s.sid
group by o.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
order by total_wait_time desc;
Oracle数据库查看各用户资源占用的SQL语句
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 #;