Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to run script in oracle real time

Posted on 2013-06-01
3
Medium Priority
?
512 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
[X]
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
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

618 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