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))