Solved

how to run script in oracle real time

Posted on 2013-06-01
3
499 Views
Last Modified: 2013-06-02
In real time how we will run this script

Script to find out the top processes, sql information,

lock and Wait information



###############################################################################

# show_top

# Owner : Don Dohm , Neeraj

# Copyright orcl-experts.info

# Make sure to setup the right Oracle Home. We are assuming that the oracle home is /data01/home/oracle and .bash_profile is present

# Displays information about oracle processes appearing in the "top" connamd"

# Question or Comments : livedba@orcl-experts.info

################################################################################




# set -x




. ~oracle/.bash_profile




echo "TOP ORACLE PROCESSES:"

echo "---------------------"

echo ""




# for pid in `top -n1 | grep " oracle " | grep -v " top" | grep -v ^$ | cut -f1 -d' '|sed 's/^ //'`




for pid in `ps -u oracle -o "%p %u %C %t %a"|sed 's/^ //'|sort -nr +2 | head | cut -f1 -d' '|sed '/^$/d'`

do

   echo "******************************************************************************"

   echo "Processing $pid ..."

   echo "******************************************************************************"

   echo ""




#   cmd=`ps -flp $pid`

   echo "COMMAND:"

   echo ""

   ps -flp $pid

   echo ""




   export ORACLE_SID="UNKNOWN"




   for sid in `ps -ef|grep pmon|grep -v grep|cut -c58-65`

   do

      ps -flp $pid | grep $sid | grep -v "ora_" | grep -v grep 2>&1>> /dev/null

      if [ $? = 0 ]; then

         export ORACLE_SID=$sid

      fi

   done




   echo ""




   if [ "$ORACLE_SID" = "UNKNOWN" ]; then

      echo "This is NOT an Oracle database USER process"

   else

      sqlplus -s / as sysdba << EOF




      rem -----------------------------------------------------------------------

      rem Lookup database details for a given Unix process id

      rem -----------------------------------------------------------------------




      set echo off;

      set feed off;

      set verify off;

      set trimspool on;

      set pages 0;




      set serveroutput on size 50000

      -- accept 1 prompt 'Enter Unix process id: '




      DECLARE

        v_sid number;

        s sys.v_\$session%ROWTYPE;

        p sys.v_\$process%ROWTYPE;

      BEGIN

        begin

          select sid into v_sid

          from   sys.v_\$process p, sys.v_\$session s

          where  p.addr     = s.paddr

            and  (p.spid    = $pid

             or   s.process = '$pid');

        exception

          when no_data_found then

            dbms_output.put_line('Unable to find process id $pid!!!');

            return;

          when others then

            dbms_output.put_line(sqlerrm);

            return;

        end;




        select * into s from sys.v_\$session where sid  = v_sid;

        select * into p from sys.v_\$process where addr = s.paddr;




        dbms_output.put_line('=====================================================================');

        dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);

        dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);

        dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);

        dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);

        dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);

        dbms_output.put_line('Ora User    : '|| s.username);

        dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);

        dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));

        dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));

        dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');

        dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));

        dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));




        dbms_output.put_line('Current SQL statement:');

        for c1 in ( select * from sys.v_\$sqltext

                    where HASH_VALUE = s.sql_hash_value order by piece) loop

          dbms_output.put_line(chr(9)||c1.sql_text);

        end loop;




        dbms_output.put_line('Previous SQL statement:');

        for c1 in ( select * from sys.v_\$sqltext

                    where HASH_VALUE = s.prev_hash_value order by piece) loop

          dbms_output.put_line(chr(9)||c1.sql_text);

        end loop;




        dbms_output.put_line('Session Waits:');

        for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop

          dbms_output.put_line(chr(9)||c1.state||': '||c1.event);

        end loop;




      --  dbms_output.put_line('Connect Info:');

      --  for c1 in ( select * from sys.v_\$session_connect_info where sid = s.sid) loop

      --    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);

      --  end loop;




        dbms_output.put_line('Locks:');

        for c1 in ( select /*+ ordered */

                decode(l.type,

                -- Long locks

                            'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',

                            'UL', 'PLS USR LOCK',

                -- Short locks

                            'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',

                            'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',

                            'CU', 'CURSOR BIND ',

                            'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',

                            'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',

                            'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',

                            'FI', 'SGA OPN FILE',

                            'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',

                            'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',

                            'LS', 'LOG SWITCH  ',

                            'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',

                            'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',

                            'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',

                            'RW', 'ROW WAIT    ',

                            'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',

                            'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',

                            'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',

                            'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',

                            'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',

                            'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',

                            'TYPE='||l.type) type,

             decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                             4, 'S',    5, 'RSX',  6, 'X',

                             to_char(l.lmode) ) lmode,

             decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                               4, 'S', 5, 'RSX', 6, 'X',

                               to_char(l.request) ) lrequest,

             decode(l.type, 'MR', o.name,

                            'TD', o.name,

                            'TM', o.name,

                            'RW', 'FILE#='||substr(l.id1,1,3)||

                                  ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,

                            'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,

                            'WL', 'REDO LOG FILE#='||l.id1,

                            'RT', 'THREAD='||l.id1,

                            'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),

                            'ID1='||l.id1||' ID2='||l.id2) objname

             from  sys.v_\$lock l, sys.obj\$ o

             where sid   = s.sid

               and l.id1 = o.obj#(+) ) loop

          dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);

        end loop;







        dbms_output.put_line('=====================================================================');




      END;

      /




      exit;

EOF







   fi




   echo ""

   echo ""




done




set +x
0
Comment
Question by:walkerdba
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39213716
What do you mean real-time?

Looks like whenever you run it, it displays what is current.
0
 

Author Comment

by:walkerdba
ID: 39213736
how we execute it the script.
0
 

Author Closing Comment

by:walkerdba
ID: 39214322
ok
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Python variable _ manually assigned 9 64
AD LDAP LDS 3 48
su - oracle could not open session 6 50
database upgrade 8 39
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now