script：dba常用管理脚本收集(二)
sesion.sid =&sid
/
--------------------------------------------------------------------------------------------------------------------------------------------------
Sid.sql
select spid,sid,a.serial#,b.program,osuser,machine,process from v$session a,v$process b
where a.paddr=b.addr and b.spid in (&spid)
/
---------------------------------------------------------------------------------------------------------------------------------------------
Spid.sql
select spid,sid,a.serial#,b.program,osuser,machine,process,status from v$session a,v$process b
where a.paddr=b.addr and a.sid in (&sid)
/
----------------------------------------------------------------------------------------------------------------------------------------
SW.sql
col event for a34
set pages 100
set lines 130
select sid,event,p1,p2,p3 from v$session_wait where state='WAITING' and event not like '%messag%' order by event
/
select event,count(*) from v$session_wait group by event
/
----------------------------------------------------------------------------------------------------------------------------------------------
lops.sql
set line 150;
col target for a35
col EST_COMPLETION_TIME for a20
col SOFAR for a14
col sid_serial for a10
set pages 1000
SELECT /*+ rule */ a.sid||','||a.serial# "sid_serial", b.status, a.target || a.opname target , a.TOTALWORK, a.SOFAR SOFAR, a.TIME_REMAINING "TIME_REMAIN",
to_char(start_time+(sysdate-start_time) /(a.sofar/a.totalwork),'dd-mon-yy:hh:mi:ss') Est_completion_time,
round((a.sofar/a.totalwork)*100,3) pct_complete,
ELAPSED_SECONDS "ELAPS_SECS" FROM V$SESSION_LONGOPS a, V$SESSION b
where a.time_remaining > 0
and a.sid=b.sid and b.status='ACTIVE'
order by a.time_remaining desc
/
-----------------------------------------------------------------------------------------------------------------------------------------------
rollback_info.sql
SELECT * FROM V$FAST_START_TRANSACTIONS
/
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
top_sqls
SET LINESIZE 500
SET PAGESIZE 100
col sid_serial for a10
col sql_text for a30
col osuser for a10
SELECT *
FROM (SELECT /*+ rule */ sid||','||serial# sid_serial,Substr(a.sql_text,1,550) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) read_pr_exe,
a.buffer_gets buff_g,
a.disk_reads disk_r,
a.executions exec,
a.sorts,
a.address,osuser
FROM v$sqlarea a,v$session b
where a.address = b.sql_address and b.status = 'ACTIVE'
ORDER BY 3 DESC)
WHERE rownum <= &1
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
time_remaining.sql
SELECT SID,
DECODE (totalwork,
0, 0,
ROUND (100 * sofar / totalwork, 2)
) "Percent",
MESSAGE "Message", start_time, elapsed_seconds, time_remaining/60 "Time Remaining-Mins"
FROM v$session_longops WHERE time_remaining >
0 and sid=&sid
/
-----------------------------------------------------