– 查进程
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
;