oracle常用运维脚本(查进程、查表空间、查表大小、查锁对象情况)

– 查进程

select gvs.inst_id,to_char(nvl(gvs.prev_exec_start, gvs.logon_time),'yyyy-mm-dd hh24:mi:ss') exec_time,gvs.username,gvs.sid,gvs.serial#,gvp.spid,gvs.saddr,gvs.status,gvs.event,gvs.blocking_session_status,gvs.blocking_instance,gvs.blocking_session
,gvt.sql_id,gvt.hash_value,gvt.sql_text,gvt.sql_fulltext,gvs.osuser,gvs.machine,gvs.program,gvs.type
,'alter system kill session '''||gvs.sid||','||gvs.serial#||',@'||gvs.inst_id||''' immediate;'
from gv$session gvs
left join gv$sql gvt
on gvs.inst_id = gvt.inst_id
and nvl(gvs.sql_id, gvs.prev_sql_id) = gvt.sql_id
and gvt.child_number = 0
left join gv$process gvp
on gvs.inst_id = gvp.inst_id
and gvs.paddr = gvp.addr
where gvs.status = 'ACTIVE'
and gvs.username is not null
-- gvt.sql_text like upper('%ev_event%')
order by 2
;

– 查表空间

select b.tablespace_name,
cast(b.space as number(10,2)) as "SPACE(G)",
cast(b.space - a.free as number(10,2)) as "USED(G)",
cast(a.free as number(10,2)) as "FREE(G)",
cast((b.space - a.free) / b.space * 100 as number(10,2)) as "USED(%)"
from (select tablespace_name, sum(bytes)/1024/1024/1024 free from dba_free_space group by tablespace_name) a
right join (select tablespace_name, sum(bytes)/1024/1024/1024 space from dba_data_files group by tablespace_name) b
on a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

– 查表大小

select owner,tablespace_name,segment_name,segment_type,sum(bytes)/1024/1024 as "used(m)"
from dba_segments
where owner like 'I_L'
and segment_name = upper('EV_DUBIL_INT_DEAL_TX')
group by owner,tablespace_name,segment_name,segment_type
order by 5 desc
;

– 锁对象情况

select lo.locked_mode, lo.object_id, obj.object_name, obj.subobject_name, obj.object_type
,gvs.inst_id,to_char(nvl(gvs.prev_exec_start, gvs.logon_time),'yyyy-mm-dd hh24:mi:ss') exec_time,gvs.username,gvs.sid,gvs.serial#,gvp.spid,gvs.saddr,gvs.status
,gvt.sql_text,gvt.sql_fulltext,gvs.osuser,gvs.machine,gvs.program,gvs.type
,'alter system kill session '''||gvs.sid||','||gvs.serial#||',@'||gvs.inst_id||''' immediate;'
from gv$locked_object lo
join dba_objects obj
on lo.object_id = obj.object_id
join gv$session gvs
on lo.inst_id = gvs.inst_id and lo.session_id = gvs.sid
left join gv$sql gvt
on gvs.inst_id = gvt.inst_id
and nvl(gvs.sql_id, gvs.prev_sql_id) = gvt.sql_id
and gvt.child_number = 0
left join gv$process gvp
on gvs.inst_id = gvp.inst_id
and gvs.paddr = gvp.addr
;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值