Database monitoring scripts

ACTIVE session report :

 set linesize 200
 col Username for a10
 col OSuser for a15
 col Machine for a31
 col Spid for a10
 col Program for a30
select
 S.Username,
 S.OSuser,
 S.Machine,
 S.STATUS,
 P.Spid,
 S.Program,
 S.SERIAL#,
 S.SID
 from V$PROCESS P, V$SESSION S
 where P.Addr = S.Paddr
 -- and s.Audsid > 8
 order by S.STATUS;
set linesize 200
 col Username for a10
 col OSuser for a15
 col Machine for a17
 col Spid for a10
 col Program for a30
spool sess.lst
 select
 S.Username,
 S.OSuser,
 S.Machine,
 S.STATUS,
 P.Spid,
 s.process client_PID,
 S.Program,
 S.SERIAL#,
 S.SID
 from V$PROCESS P, V$SESSION S
 where P.Addr = S.Paddr
 -- and s.Audsid > 8
 and s.process='&enter_process'
 order by S.Username;
 spool off
Active Session by SQL_ID:

 set linesize 150
 col Username for a10
 col OSuser for a8
 col Machine for a25
 col Spid for a10
 col Program for a30
select
 S.Username,
 S.OSuser,
 S.sql_hash_value,
 s.sql_id,
 P.Spid,
 S.Program,
 S.SERIAL#,
 s.status,
 S.SID
 ,s.logon_time
 ,s.client_info
 from V$PROCESS P, V$SESSION S
 where P.Addr = S.Paddr
 and s.Audsid > 8
Active Longops:
 set linesize 150
 col sql_text for a50
select  sid, to_char(START_TIME,'MM-DD-YY:HH:MM:SS') , to_char(TIME_REMAINING,'MM-DD-YY:HH:MM:SS') , LAST_UPDATE_TIME, SQL_HASH_VALUE, sql_text
 FROM v$session_longops a, v$sql b
 WHERE username='SYSADM'
 AND totalwork > 0
 and sofar <> totalwork
 and a.sql_hash_value=b.hash_value
 and sid=&enter_sid
 ;
 --    and s.Audsid > 8
Pull SQL info :
select sql_text,sql_id,hash_value from v$sqltext
 where sql_text like '%&1%'
 order by piece
 /
Advertisements

Author: Abdul H Khan

Trying to be cloudy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s