Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how to run script in oracle real time

Posted on 2013-06-01
3
501 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

840 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