Solved

I cannot run this script

Posted on 2013-06-02
19
1,288 Views
Last Modified: 2013-06-22
SQL>  @/home/oracle/Desktop/df.sql

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

# show_top




# 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"


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




# 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



SP2-0734: unknown command beginning "##########..." - rest of line ignored.
SP2-0158: unknown SHOW option "_top"
SP2-0734: unknown command beginning "Owner : Do..." - rest of line ignored.
SP2-0734: unknown command beginning "Copyright ..." - rest of line ignored.
SP2-0734: unknown command beginning "Make sure ..." - rest of line ignored.
SP2-0734: unknown command beginning "Displays i..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Question o..." - rest of line ignored.
SP2-0734: unknown command beginning "##########..." - rest of line ignored.
SP2-0158: unknown SET option "-x"
SP2-0734: unknown command beginning ". ~oracle/..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "TOP ..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "----..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "for pid in..." - rest of line ignored.
SP2-0734: unknown command beginning "for pid in..." - rest of line ignored.
SP2-0042: unknown command "do" - rest of line ignored.
SP2-0734: unknown command beginning "echo "****..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "echo "Proc..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "****..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "cmd=`ps -f..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "echo "COMM..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "ps -flp $p..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "export ORA..." - rest of line ignored.
SP2-0734: unknown command beginning "for sid in..." - rest of line ignored.
SP2-0042: unknown command "do" - rest of line ignored.
SP2-0734: unknown command beginning "ps -flp $p..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "if [ $? = ..." - rest of line ignored.
SP2-0734: unknown command beginning "export ORA..." - rest of line ignored.
SP2-0042: unknown command "fi" - rest of line ignored.
SP2-0042: unknown command "done" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "if [ "$ORA..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "This..." - rest of line ignored.
SP2-0042: unknown command "else" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "sqlplus -s..." - rest of line ignored.
        s sys.v_\$session%ROWTYPE;
                 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00181: unsupported preprocessor directive '$SESSION'


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mac dbs]$
0
Comment
Question by:walkerdba
  • 9
  • 5
  • 5
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>SQL>  @/home/oracle/Desktop/df.sql

That is a Unix Shell script not a sqlplus script.

Save it with a .sh extension like df.sh.  Give it execute permissions: chmod 755 df.sh

Then from a unix prompt:
./df.sh
0
 

Author Comment

by:walkerdba
Comment Utility
[oracle@mac dbs]$ . oraenv sbi
ORACLE_SID = [orcl] ? sbi
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@mac dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 2 14:46:49 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/Desktop/df.sh
SP2-0734: unknown command beginning "##########..." - rest of line ignored.
SP2-0158: unknown SHOW option "_top"
SP2-0734: unknown command beginning "Owner : Do..." - rest of line ignored.
SP2-0734: unknown command beginning "Copyright ..." - rest of line ignored.
SP2-0734: unknown command beginning "Make sure ..." - rest of line ignored.
SP2-0734: unknown command beginning "Displays i..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Question o..." - rest of line ignored.
SP2-0734: unknown command beginning "##########..." - rest of line ignored.
SP2-0158: unknown SET option "-x"
SP2-0734: unknown command beginning ". ~oracle/..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "TOP ..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "----..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "for pid in..." - rest of line ignored.
SP2-0734: unknown command beginning "for pid in..." - rest of line ignored.
SP2-0042: unknown command "do" - rest of line ignored.
SP2-0734: unknown command beginning "echo "****..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "echo "Proc..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "****..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "cmd=`ps -f..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "echo "COMM..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "ps -flp $p..." - rest of line ignored.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "export ORA..." - rest of line ignored.
SP2-0734: unknown command beginning "for sid in..." - rest of line ignored.
SP2-0042: unknown command "do" - rest of line ignored.
SP2-0734: unknown command beginning "ps -flp $p..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "if [ $? = ..." - rest of line ignored.
SP2-0734: unknown command beginning "export ORA..." - rest of line ignored.
SP2-0042: unknown command "fi" - rest of line ignored.
SP2-0042: unknown command "done" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0042: unknown command "echo """ - rest of line ignored.
SP2-0734: unknown command beginning "if [ "$ORA..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "This..." - rest of line ignored.
SP2-0042: unknown command "else" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "sqlplus -s..." - rest of line ignored.
        s sys.v_\$session%ROWTYPE;
                 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00181: unsupported preprocessor directive '$SESSION'


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mac dbs]$ cd /home/oracle/Desktop
[oracle@mac Desktop]$ ls
alert_orcl.log                initdat.ora~
bkp                           initorcl.ora~
control01.ctl                 initsat.ora~
create database               mydir
ctr                           new file
dat_ora_22268_1.aud           new file~
dat_ora_25802_1.aud           new file 1
dat.sql                       newfile.txt
dat.sql~                      orcl_ora_4806.trc
df.sh                         putty.exe
df.sql~                       sp.txt
emca_2012_09_16_04_28_19.log  stby
emca_2012_09_18_18_13_41.log  tc1.sql
exp                           tc.sql~
exp~                          tnsnames.ora
hosts                         VMwareTools-8.4.6-385536.tar.gz
initdat.ora                   vmware-tools-distrib
[oracle@mac Desktop]$
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>SQL> @/home/oracle/Desktop/df.sh

AGAIN, it's NOT a sql script.  It is a Unix SHELL script.

You don't run it from sqlplus.

You run it from a command prompt!
0
 

Author Comment

by:walkerdba
Comment Utility
What is wrong now please..


SQL> select name from v$database;

NAME
---------
SBI

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mac Desktop]$ . oraenv sbi
ORACLE_SID = [sbi] ? sbi
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@mac Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 2 23:22:35 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
SBI

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mac Desktop]$ @/home/oracle/Desktop/df.sh
bash: @/home/oracle/Desktop/df.sh: No such file or directory
[oracle@mac Desktop]$ sh /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

sort: open failed: +2: No such file or directory
[oracle@mac Desktop]$ sh home/oracle/Desktop/df.sh
sh: home/oracle/Desktop/df.sh: No such file or directory
[oracle@mac Desktop]$ sh /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

sort: open failed: +2: No such file or directory
[oracle@mac Desktop]$ sh /home/oracle/Desktop/df.sh





[oracle@mac Desktop]$ bash /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

sort: open failed: +2: No such file or directory
[oracle@mac Desktop]$
0
 
LVL 19

Expert Comment

by:simon3270
Comment Utility
"sort +2" is the old way to decide which column to sort on - the current way is "-k 2".

I assume you are trying to dispay the 10 highest CPU users for the oravle user.  The output from the ps command looks like (this is my home machine, so no Oracle!):
 PID RUSER    %CPU     ELAPSED COMMAND
28064 user1     0.2       02:09 -bash
2139 user1      0.1 13-04:52:42 /usr/bin/python /usr/lib/desktopcouch/desktopcouch-service
1593 user1      0.1 13-04:53:13 /usr/bin/compiz

Open in new window

Note that the cpu usage is the 3rd colmn, and "sort" column numbers start from 1.  Also, your sort would leave the header line in place - I'm pretty sure you don't want that in your output.  So, the line becomes:
for pid in `ps -u oracle -o "%p %u %C %t %a"|grep -v '^ *PID'|sort -nr -k 3 | head | awk '{print $1}'`

Open in new window

I've used awk rather than cut to print out the first field (more flexible) and removed your last sed - empty lines don't matter in the output from this command since they will simply be removed by the back-tick process.
0
 

Author Comment

by:walkerdba
Comment Utility
I changed ...

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

# 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"|grep -v '^ *PID'|sort -nr -k 3 | head | awk '{print $1}'`
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


and please check with me this is the expected output..

This is the output now ...



[oracle@mac Desktop]$ bash /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

******************************************************************************
Processing 914 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     914     1  0  75   0 - 74286 -      Jun02 ?        00:00:00 ora_s


This is NOT an Oracle database USER process


******************************************************************************
Processing 718 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     718     1  0  75   0 - 75454 -      Jun02 ?        00:00:36 ora_c


This is NOT an Oracle database USER process


******************************************************************************
Processing 702 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     702     1  0  75   0 - 74671 -      Jun02 ?        00:00:01 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 700 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     700     1  0  78   0 - 74284 -      Jun02 ?        00:00:00 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 695 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     695     1  0  78   0 - 74286 -      Jun02 ?        00:00:00 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 679 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     679     1  0  75   0 - 78173 -      Jun02 ?        00:00:00 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 677 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     677     1  0  75   0 - 86398 -      Jun02 ?        00:00:02 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 675 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     675     1  0  75   0 - 82301 -      Jun02 ?        00:00:02 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 672 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     672     1  0  75   0 - 78174 -      Jun02 ?        00:00:01 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 663 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     663     1  0  75   0 - 74294 -      Jun02 ?        00:00:00 ora_s


This is NOT an Oracle database USER process


[oracle@mac Desktop]$

Is this the expected output...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Is this the expected output...

I would say not.  Looks like the error is coming from not grabbing the SID correctly .

From the output from COMMAND, it isn't on the process line.

Since the original author of the script sorted with "+2", I would leave it a 2 and not change it to a 3.
0
 

Author Comment

by:walkerdba
Comment Utility
I changed it to 2

Now the output is
Is this right?


[oracle@mac Desktop]$ bash /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

******************************************************************************
Processing 914 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     914     1  0  75   0 - 74286 -      Jun02 ?        00:00:00 ora_s


This is NOT an Oracle database USER process


******************************************************************************
Processing 718 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     718     1  0  75   0 - 75454 -      Jun02 ?        00:00:36 ora_c


This is NOT an Oracle database USER process


******************************************************************************
Processing 702 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     702     1  0  75   0 - 74671 -      Jun02 ?        00:00:01 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 700 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     700     1  0  78   0 - 74284 -      Jun02 ?        00:00:00 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 695 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     695     1  0  78   0 - 74286 -      Jun02 ?        00:00:00 ora_q


This is NOT an Oracle database USER process


******************************************************************************
Processing 679 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     679     1  0  75   0 - 78173 -      Jun02 ?        00:00:00 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 677 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     677     1  0  75   0 - 86398 -      Jun02 ?        00:00:02 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 675 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     675     1  0  75   0 - 82301 -      Jun02 ?        00:00:02 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 672 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     672     1  0  75   0 - 78174 -      Jun02 ?        00:00:01 ora_a


This is NOT an Oracle database USER process


******************************************************************************
Processing 663 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     663     1  0  75   0 - 74294 -      Jun02 ?        00:00:00 ora_s


This is NOT an Oracle database USER process


[oracle@mac Desktop]$ vi /home/oracle/Desktop/df.sh
[oracle@mac Desktop]$ bash /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

******************************************************************************
Processing 914 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     914     1  0  75   0 - 74286 -      Jun02 ?        00:00:00 ora_smco_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 718 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     718     1  0  75   0 - 75454 -      Jun02 ?        00:00:36 ora_cjq0_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 702 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     702     1  0  75   0 - 74671 -      Jun02 ?        00:00:01 ora_q002_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 700 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     700     1  0  78   0 - 74284 -      Jun02 ?        00:00:00 ora_q001_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 695 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     695     1  0  78   0 - 74286 -      Jun02 ?        00:00:00 ora_qmnc_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 679 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     679     1  0  75   0 - 78173 -      Jun02 ?        00:00:00 ora_arc3_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 677 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     677     1  0  75   0 - 86398 -      Jun02 ?        00:00:02 ora_arc2_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 675 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     675     1  0  75   0 - 82301 -      Jun02 ?        00:00:02 ora_arc1_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 672 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     672     1  0  75   0 - 78174 -      Jun02 ?        00:00:01 ora_arc0_sbi


This is NOT an Oracle database USER process


******************************************************************************
Processing 663 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle     663     1  0  75   0 - 74294 -      Jun02 ?        00:00:00 ora_s000_sbi


This is NOT an Oracle database USER process


[oracle@mac Desktop]$
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Is this right?

Doesn't look right.

When copying code form the Internet, you really need to understand what it is supposed to do.  We can do some limited troubleshooting but to diagnose what is to be considered "expected" results, we really cannot.

You should understand what it is supposed to do.  We cannot say.

Please post the output of:
ps -u oracle -o "%p %u %C %t %a"
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:simon3270
Comment Utility
I think the "-k 3" is correct - the original script looks like UNIX or Solaris one, using "top -n1" to sort the processes in descending CPU usage order.  The "-k 3" does the same for the ps output.  That said, it would be much simpler to have:
    ps -u oracle -o "%C %p"|grep -v 'PID'|sort -nr | head | awk '{print $2}'

There are other bits which need changing.  The line:
    for sid in `ps -ef|grep pmon|grep -v grep|cut -c58-65`
is almost certainly wrong for Linux (the bit of text that is from column 58 to 65 in UNIX won't be the same as the Linux version of "ps").  What is the output of "ps -ef"?

A couple of lines down, you have:
    ps -flp $pid | grep $sid | grep -v "ora_" | grep -v grep 2>&1>> /dev/null
then a test for $?.  This is just wrong - the $? value will be that of the last command in the pipe - this is the "grep -v grep" which will return success unless *all* lines contained "grep" - presumably not what you want.  If you needed it, I'd also replace the "2>..." bit at the end with ">&/dev/null", but you can remove the need for it by using "-q" on the grep.  You end up with:
  ps -flp $pid | grep -v "ora_" | grep -v grep | grep -q $sid

You would also benefit from "#!/bin/bash" as the first line of your script - it makes sure that the specified shell is used, and doesn't fail if you happen to have csh as your login shell (if the first character on the first line is "#" but is not followed by a valid shell name, csh treats as a csh script, which this isn't).

By the way, it would be easier to comment if you surrounded you code with "[ code ]" and "[ /code ]" tags (with no spaces - I just added those to get the text to display), which will add line numbers.
0
 

Author Comment

by:walkerdba
Comment Utility
[oracle@mac ~]$ ps -u oracle -o "%p %u %C %t %a"
  PID RUSER    %CPU     ELAPSED COMMAND
 4255 oracle    0.1       01:55 /usr/bin/gnome-session
 4314 oracle    0.0       01:54 /usr/bin/ssh-agent /usr/bin/dbus-launch --exit-w
 4317 oracle    0.0       01:54 /usr/bin/dbus-launch --exit-with-session /etc/X1
 4318 oracle    0.0       01:53 /bin/dbus-daemon --fork --print-pid 4 --print-ad
 4324 oracle    0.2       01:53 /usr/libexec/gconfd-2 11
 4327 oracle    0.0       01:52 /usr/bin/gnome-keyring-daemon
 4329 oracle    0.1       01:52 /usr/libexec/gnome-settings-daemon
 4345 oracle    0.1       01:51 metacity --sm-client-id=default1
 4349 oracle    0.3       01:51 gnome-panel --sm-client-id default2
 4351 oracle    0.5       01:51 nautilus --no-default-window --sm-client-id defa
 4355 oracle    0.1       01:50 /usr/libexec/bonobo-activation-server --ac-activ
 4357 oracle    0.0       01:50 /usr/libexec/gnome-vfs-daemon
 4361 oracle    0.0       01:50 eggcups --sm-client-id default4
 4375 oracle    0.4       01:50 /usr/lib/vmware-tools/bin32/vmware-user-loader -
 4383 oracle    0.0       01:50 bt-applet --sm-disable
 4394 oracle    0.2       01:49 /usr/bin/python -tt /usr/bin/puplet
 4396 oracle    0.0       01:49 /usr/libexec/gam_server
 4398 oracle    0.0       01:49 /usr/libexec/mapping-daemon
 4399 oracle    0.0       01:49 ./escd --key_Inserted="/usr/bin/esc" --on_Signal
 4404 oracle    0.1       01:49 /usr/libexec/wnck-applet --oaf-activate-iid=OAFI
 4407 oracle    0.1       01:49 /usr/libexec/trashapplet --oaf-activate-iid=OAFI
 4412 oracle    0.1       01:48 nm-applet --sm-disable
 4415 oracle    0.0       01:48 pam-panel-icon --sm-client-id default0
 4419 oracle    0.0       01:48 gnome-power-manager
 4447 oracle    0.3       01:41 /usr/bin/python -E /usr/bin/sealert -s
 4462 oracle    0.1       01:26 /usr/libexec/notification-area-applet --oaf-acti
 4465 oracle    0.1       01:26 /usr/libexec/mixer_applet2 --oaf-activate-iid=OA
 4466 oracle    0.1       01:26 /usr/libexec/clock-applet --oaf-activate-iid=OAF
 4471 oracle    0.0       01:23 gnome-screensaver
 4491 oracle    0.9       01:00 gnome-terminal
 4493 oracle    0.1       00:57 gnome-pty-helper
 4494 oracle    0.1       00:56 bash
 4641 oracle    0.0       00:00 ps -u oracle -o %p %u %C %t %a
[oracle@mac ~]$
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>[oracle@mac ~]$ ps -u oracle -o "%p %u %C %t %a"

I don't see an Oracle database running
0
 

Author Comment

by:walkerdba
Comment Utility
What does this means?


[oracle@mac dbs]$ . oraenv databank
ORACLE_SID = [RAC1] ? databank
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@mac dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 4 01:42:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             285214964 bytes
Database Buffers          763363328 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.
SQL> select name from v$database;

NAME
---------
DATABANK

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mac dbs]$ sh /home/oracle/Desktop/df.sh
TOP ORACLE PROCESSES:
---------------------

******************************************************************************
Processing 4494 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4494  4491  0  75   0 -  1126 wait   Jun03 pts/1    00:00:00 bash


This is NOT an Oracle database USER process


******************************************************************************
Processing 4493 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4493  4491  0  78   0 -   614 -      Jun03 ?        00:00:00 gnome


This is NOT an Oracle database USER process


******************************************************************************
Processing 4491 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4491     1  0  75   0 - 15470 stext  Jun03 ?        00:00:04 gnome


This is NOT an Oracle database USER process


******************************************************************************
Processing 4471 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
1 S oracle    4471     1  0  75   0 -  3919 -      Jun03 ?        00:00:01 gnome


This is NOT an Oracle database USER process


******************************************************************************
Processing 4466 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4466     1  0  78   0 -  6877 -      Jun03 ?        00:00:01 /usr/


This is NOT an Oracle database USER process


******************************************************************************
Processing 4465 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4465     1  0  75   0 - 11784 -      Jun03 ?        00:00:03 /usr/


This is NOT an Oracle database USER process


******************************************************************************
Processing 4462 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4462     1  0  78   0 -  5667 -      Jun03 ?        00:00:00 /usr/


This is NOT an Oracle database USER process


******************************************************************************
Processing 4447 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4447     1  0  75   0 - 10388 -      Jun03 ?        00:00:17 /usr/


This is NOT an Oracle database USER process


******************************************************************************
Processing 4419 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
1 S oracle    4419     1  0  75   0 - 11071 -      Jun03 ?        00:00:27 gnome


This is NOT an Oracle database USER process


******************************************************************************
Processing 4415 ...
******************************************************************************

COMMAND:

F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    4415     1  0  75   0 -  3729 -      Jun03 ?        00:00:02 pam-p


This is NOT an Oracle database USER process


[oracle@mac dbs]$ ps -u oracle -o "%p %u %C %t %a"
  PID RUSER    %CPU     ELAPSED COMMAND
 4255 oracle    0.0    12:11:18 /usr/bin/gnome-session
 4314 oracle    0.0    12:11:17 /usr/bin/ssh-agent /usr/bin/dbus-launch --exit-with-session /etc/X11/xinit/Xclients
 4317 oracle    0.0    12:11:17 /usr/bin/dbus-launch --exit-with-session /etc/X11/xinit/Xclients
 4318 oracle    0.0    12:11:16 /bin/dbus-daemon --fork --print-pid 4 --print-address 6 --session
 4324 oracle    0.0    12:11:16 /usr/libexec/gconfd-2 11
 4327 oracle    0.0    12:11:15 /usr/bin/gnome-keyring-daemon
 4329 oracle    0.0    12:11:15 /usr/libexec/gnome-settings-daemon
 4345 oracle    0.0    12:11:14 metacity --sm-client-id=default1
 4349 oracle    0.0    12:11:14 gnome-panel --sm-client-id default2
 4351 oracle    0.0    12:11:14 nautilus --no-default-window --sm-client-id default3
 4355 oracle    0.0    12:11:13 /usr/libexec/bonobo-activation-server --ac-activate --ior-output-fd=16
 4357 oracle    0.0    12:11:13 /usr/libexec/gnome-vfs-daemon
 4361 oracle    0.0    12:11:13 eggcups --sm-client-id default4
 4375 oracle    0.0    12:11:13 /usr/lib/vmware-tools/bin32/vmware-user-loader --blockFd 3
 4383 oracle    0.0    12:11:13 bt-applet --sm-disable
 4394 oracle    0.0    12:11:12 /usr/bin/python -tt /usr/bin/puplet
 4396 oracle    0.0    12:11:12 /usr/libexec/gam_server
 4398 oracle    0.0    12:11:12 /usr/libexec/mapping-daemon
 4399 oracle    0.0    12:11:12 ./escd --key_Inserted="/usr/bin/esc" --on_Signal="/usr/bin/esc"
 4404 oracle    0.0    12:11:12 /usr/libexec/wnck-applet --oaf-activate-iid=OAFIID:GNOME_Wncklet_Factory --oaf-ior-fd=19
 4407 oracle    0.0    12:11:12 /usr/libexec/trashapplet --oaf-activate-iid=OAFIID:GNOME_Panel_TrashApplet_Factory --oaf-ior-
 4412 oracle    0.0    12:11:11 nm-applet --sm-disable
 4415 oracle    0.0    12:11:11 pam-panel-icon --sm-client-id default0
 4419 oracle    0.0    12:11:11 gnome-power-manager
 4447 oracle    0.0    12:11:04 /usr/bin/python -E /usr/bin/sealert -s
 4462 oracle    0.0    12:10:49 /usr/libexec/notification-area-applet --oaf-activate-iid=OAFIID:GNOME_NotificationAreaApplet_
 4465 oracle    0.0    12:10:49 /usr/libexec/mixer_applet2 --oaf-activate-iid=OAFIID:GNOME_MixerApplet_Factory --oaf-ior-fd=3
 4466 oracle    0.0    12:10:49 /usr/libexec/clock-applet --oaf-activate-iid=OAFIID:GNOME_ClockApplet_Factory --oaf-ior-fd=29
 4471 oracle    0.0    12:10:46 gnome-screensaver
 4491 oracle    0.0    12:10:23 gnome-terminal
 4493 oracle    0.0    12:10:20 gnome-pty-helper
 4494 oracle    0.0    12:10:19 bash
27834 oracle    0.0       01:39 ora_pmon_databank
27836 oracle    0.0       01:38 ora_vktm_databank
27840 oracle    0.0       01:38 ora_gen0_databank
27842 oracle    0.0       01:38 ora_diag_databank
27844 oracle    0.0       01:38 ora_dbrm_databank
27846 oracle    0.0       01:38 ora_psp0_databank
27848 oracle    0.0       01:38 ora_dia0_databank
27850 oracle    0.2       01:38 ora_mman_databank
27852 oracle    0.1       01:38 ora_dbw0_databank
27854 oracle    0.3       01:38 ora_lgwr_databank
27856 oracle    0.0       01:38 ora_ckpt_databank
27858 oracle    0.1       01:38 ora_smon_databank
27860 oracle    0.0       01:38 ora_reco_databank
27862 oracle    0.7       01:38 ora_mmon_databank
27864 oracle    0.0       01:38 ora_mmnl_databank
27866 oracle    0.0       01:38 ora_d000_databank
27868 oracle    0.0       01:38 ora_s000_databank
27877 oracle    0.5       01:29 ora_arc0_databank
27879 oracle    0.0       01:28 ora_arc1_databank
27881 oracle    0.0       01:28 ora_arc2_databank
27883 oracle    0.0       01:28 ora_arc3_databank
27886 oracle    0.0       01:20 ora_qmnc_databank
27900 oracle    0.6       01:14 ora_cjq0_databank
27915 oracle    0.1       01:10 ora_q000_databank
27917 oracle    0.0       01:10 ora_vkrm_databank
27919 oracle    0.0       01:10 ora_q001_databank
27929 oracle   30.6       01:07 ora_j003_databank
28109 oracle    0.0       00:00 ps -u oracle -o %p %u %C %t %a
[oracle@mac dbs]$
0
 
LVL 19

Expert Comment

by:simon3270
Comment Utility
Have you fixed the sort?  You are clearly not picking up the 10 most active processes.

You need to take a step back and run this process in parts.  Take a copy  of the first few lines of the script, up to the "for" command which selects the top 10 processes, and simply print out the lines you have read. (don't go on to check SID values or run SQL statement).  Run that cut-down script, and make sure that the right 10 processes are displayed (run the "ps -u oracle" statement separately, and compare the full oracle process list with your cut-down one)

Only then should you add in the code to check the SID.  Add more debug lines, to display what you are actually checking, and the result of that check.

Only when the SID is being grabbed correctly should you add in the code to run the SQL statement.
0
 

Author Comment

by:walkerdba
Comment Utility
Is this you are expecting

The new scrit name new.sh
The content:
============================================================

# 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'`

==========================================================================
[oracle@mac Desktop]$ bash new.sh
TOP ORACLE PROCESSES:
---------------------

new.sh: line 26: syntax error: unexpected end of file
[oracle@mac Desktop]$ sh new.sh
TOP ORACLE PROCESSES:
---------------------

new.sh: line 26: syntax error: unexpected end of file
[oracle@mac Desktop]$ ./new.sh
bash: ./new.sh: Permission denied
[oracle@mac Desktop]$
0
 
LVL 19

Expert Comment

by:simon3270
Comment Utility
You are missing the body of the for loop.  Using corrected syntax, you would have:

#!/bin/bash
# set -x

. ~oracle/.bash_profile

echo "TOP ORACLE PROCESSES:"
echo "---------------------"
echo ""


for pid in `ps -u oracle -o "%C %p"|sort -nr | awk '/PID/{next}NR <= 11{print $2}'`; do
  echo Got PID $pid
done

Open in new window


That should print out something like:
user1@host$ bash new.sh
TOP ORACLE PROCESSES:
---------------------

Got PID 6151
Got PID 6195
Got PID 7213
Got PID 1593
Got PID 7282
Got PID 7281
Got PID 7280
Got PID 7279
Got PID 7278
Got PID 7277
user1@host$ 

Open in new window

I've used awk to strip out the header line and print the first 10 values it reads (the 11 in the script is because the header line counts as an input line, so you need to get to the 11th line).  I've remove the unnecessary output values from "ps", and added the "#!/bin/bash" to the first line.  You should also "chmod +x new.sh" to allow you to run "./new.sh" instead of "bash new.sh".

Once that script is working, extend it like:
#!/bin/bash
# set -x

. ~oracle/.bash_profile

echo "TOP ORACLE PROCESSES:"
echo "---------------------"
echo ""


for pid in `ps -u oracle -o "%C %p"|sort -nr | awk '/PID/{next}NR <= 11{print $2}'`; do
  echo Got PID $pid
  ps -flp $pid
  echo pmon lines:
  ps -ef|grep pmon|grep -v grep
  echo end of pmon lines
  echo
  echo sid lines:
  ps -ef|grep pmon|grep -v grep|cut -c58-65
  echo end of sid lines
done

Open in new window

If that doesn't give you what you expect for "sid" values, try changing the "cut" command to select a different section.

Edited: forgot to say, once you've run the first script, compare the output with that of:
    ps -u oracle -o "%p %u %C %t %a"
to make sure that you've selected the correct 10 process IDs.
0
 

Author Comment

by:walkerdba
Comment Utility
This  is all the output now..

Is this the expected one..

[oracle@mac Desktop]$ bash new.sh
TOP ORACLE PROCESSES:
---------------------

Got PID 24146
Got PID 6390
Got PID 6383
Got PID 6381
Got PID 6366
Got PID 6348
Got PID 6344
Got PID 6342
Got PID 6329
Got PID 6328
[oracle@mac Desktop]$ gedit new.sh
[oracle@mac Desktop]$ bash new.sh
TOP ORACLE PROCESSES:
---------------------

Got PID 24146
Got PID 24146
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle   24146     1  0  75   0 - 15416 stext  01:14 ?        00:00:00 gnome
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6390
Got PID 6390
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
1 S oracle    6390     1  0  75   0 -  3920 -      Jun05 ?        00:00:03 gnome
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6383
Got PID 6383
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6383     1  0  78   0 -  6900 -      Jun05 ?        00:00:01 /usr/
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6381
Got PID 6381
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6381     1  0  78   0 -  5668 -      Jun05 ?        00:00:00 /usr/
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6366
Got PID 6366
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6366     1  0  75   0 - 10388 -      Jun05 ?        00:00:17 /usr/
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6348
Got PID 6348
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
1 S oracle    6348     1  0  75   0 - 11072 -      Jun05 ?        00:00:41 gnome
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6344
Got PID 6344
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6344     1  0  75   0 - 11782 -      Jun05 ?        00:00:08 /usr/
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6342
Got PID 6342
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6342     1  0  75   0 -  3729 -      Jun05 ?        00:00:03 pam-p
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6329
Got PID 6329
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
1 S oracle    6329     1  0  79   0 -  4350 stext  Jun05 ?        00:00:02 ./esc
pmon lines:
end of pmon lines

sid lines:
end of sid lines
Got PID 6328
Got PID 6328
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    6328     1  0  75   0 - 11122 -      Jun05 ?        00:00:01 nm-ap
pmon lines:
end of pmon lines

sid lines:
end of sid lines
[oracle@mac Desktop]$  ps -u oracle -o "%p %u %C %t %a"
  PID RUSER    %CPU     ELAPSED COMMAND
 6167 oracle    0.0  1-02:04:53 /usr/bin/gnome-session
 6226 oracle    0.0  1-02:04:53 /usr/bin/ssh-agent /usr/bin/dbus-launch --exit-w
 6229 oracle    0.0  1-02:04:52 /usr/bin/dbus-launch --exit-with-session /etc/X1
 6230 oracle    0.0  1-02:04:52 /bin/dbus-daemon --fork --print-pid 4 --print-ad
 6236 oracle    0.0  1-02:04:52 /usr/libexec/gconfd-2 11
 6239 oracle    0.0  1-02:04:51 /usr/bin/gnome-keyring-daemon
 6241 oracle    0.0  1-02:04:51 /usr/libexec/gnome-settings-daemon
 6256 oracle    0.0  1-02:04:50 metacity --sm-client-id=default1
 6260 oracle    0.0  1-02:04:50 gnome-panel --sm-client-id default2
 6262 oracle    0.0  1-02:04:50 nautilus --no-default-window --sm-client-id defa
 6266 oracle    0.0  1-02:04:50 /usr/libexec/bonobo-activation-server --ac-activ
 6268 oracle    0.0  1-02:04:50 /usr/libexec/gnome-vfs-daemon
 6273 oracle    0.0  1-02:04:50 eggcups --sm-client-id default4
 6274 oracle    0.0  1-02:04:50 gnome-volume-manager --sm-client-id default5
 6289 oracle    0.0  1-02:04:49 /usr/lib/vmware-tools/bin32/vmware-user-loader -
 6291 oracle    0.0  1-02:04:49 bt-applet --sm-disable
 6302 oracle    0.0  1-02:04:49 /usr/bin/python -tt /usr/bin/puplet
 6315 oracle    0.0  1-02:04:49 /usr/libexec/gam_server
 6321 oracle    0.0  1-02:04:48 /usr/libexec/wnck-applet --oaf-activate-iid=OAFI
 6323 oracle    0.0  1-02:04:48 /usr/libexec/trashapplet --oaf-activate-iid=OAFI
 6325 oracle    0.0  1-02:04:48 /usr/libexec/mapping-daemon
 6328 oracle    0.0  1-02:04:48 nm-applet --sm-disable
 6329 oracle    0.0  1-02:04:48 ./escd --key_Inserted="/usr/bin/esc" --on_Signal
 6342 oracle    0.0  1-02:04:47 pam-panel-icon --sm-client-id default0
 6344 oracle    0.0  1-02:04:47 /usr/libexec/mixer_applet2 --oaf-activate-iid=OA
 6348 oracle    0.0  1-02:04:47 gnome-power-manager
 6366 oracle    0.0  1-02:04:45 /usr/bin/python -E /usr/bin/sealert -s
 6381 oracle    0.0  1-02:04:37 /usr/libexec/notification-area-applet --oaf-acti
 6383 oracle    0.0  1-02:04:37 /usr/libexec/clock-applet --oaf-activate-iid=OAF
 6390 oracle    0.0  1-02:04:25 gnome-screensaver
24060 oracle    0.0       14:13 /usr/libexec/gconfd-2 12
24146 oracle    0.2       11:18 gnome-terminal
24148 oracle    0.0       11:17 /usr/libexec/bonobo-activation-server --ac-activ
24166 oracle    0.0       11:11 gnome-pty-helper
24167 oracle    0.0       11:11 bash
24743 oracle    0.0       00:00 ps -u oracle -o %p %u %C %t %a
[oracle@mac Desktop]$
0
 
LVL 19

Accepted Solution

by:
simon3270 earned 500 total points
Comment Utility
You seem to be getting the right PIDs now |(the only non-zero CPU time is the "0.2" for gnome-terminal, and that is the first in the list), so now you need to run the script while Oracle is running, so that you get some "pmon" lines and, we hope, the corresponding SIDs.
0
 

Author Closing Comment

by:walkerdba
Comment Utility
yes
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

8 Experts available now in Live!

Get 1:1 Help Now