walkerdba
asked on
I cannot run this script
SQL> @/home/oracle/Desktop/df.s ql
########################## ########## ########## ########## ########## ########## ###
# 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('Unab le to find process id $pid!!!');
return;
when others then
dbms_output.put_line(sqler rm);
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('Fore ground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shad ow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Term inal : '|| 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('Stat us Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Logi n Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_ca ll_et/60/6 0/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('Latc h Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Curr ent 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('Prev ious 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('Sess ion Waits:');
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9 )||c1.stat e||': '||c1.event);
end loop;
-- dbms_output.put_line('Conn ect Info:');
-- for c1 in ( select * from sys.v_\$session_connect_in fo where sid = s.sid) loop
-- dbms_output.put_line(chr(9 )||': '||c1.network_service_bann er);
-- end loop;
dbms_output.put_line('Lock s:');
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]$
##########################
# 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('Unab
return;
when others then
dbms_output.put_line(sqler
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/
dbms_output.put_line('Fore
dbms_output.put_line('Shad
dbms_output.put_line('Term
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Stat
dbms_output.put_line('Tran
dbms_output.put_line('Logi
dbms_output.put_line('Last
dbms_output.put_line('Lock
dbms_output.put_line('Latc
dbms_output.put_line('Curr
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
end loop;
dbms_output.put_line('Prev
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
end loop;
dbms_output.put_line('Sess
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9
end loop;
-- dbms_output.put_line('Conn
-- for c1 in ( select * from sys.v_\$session_connect_in
-- dbms_output.put_line(chr(9
-- end loop;
dbms_output.put_line('Lock
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
'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
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]$
ASKER
[oracle@mac dbs]$ . oraenv sbi
ORACLE_SID = [orcl] ? sbi
The Oracle base for ORACLE_HOME=/u01/app/oracl e/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.s h
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.l og stby
emca_2012_09_18_18_13_41.l og tc1.sql
exp tc.sql~
exp~ tnsnames.ora
hosts VMwareTools-8.4.6-385536.t ar.gz
initdat.ora vmware-tools-distrib
[oracle@mac Desktop]$
ORACLE_SID = [orcl] ? sbi
The Oracle base for ORACLE_HOME=/u01/app/oracl
[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.s
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.l
emca_2012_09_18_18_13_41.l
exp tc.sql~
exp~ tnsnames.ora
hosts VMwareTools-8.4.6-385536.t
initdat.ora vmware-tools-distrib
[oracle@mac Desktop]$
>>SQL> @/home/oracle/Desktop/df.s h
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!
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!
ASKER
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/oracl e/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.s h
bash: @/home/oracle/Desktop/df.s h: 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]$
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/oracl
[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.s
bash: @/home/oracle/Desktop/df.s
[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:
[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]$
"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!):
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
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}'`
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.
ASKER
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('Unab le to find process id $pid!!!');
return;
when others then
dbms_output.put_line(sqler rm);
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('Fore ground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shad ow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Term inal : '|| 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('Stat us Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Logi n Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_ca ll_et/60/6 0/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('Latc h Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Curr ent 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('Prev ious 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('Sess ion Waits:');
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9 )||c1.stat e||': '||c1.event);
end loop;
-- dbms_output.put_line('Conn ect Info:');
-- for c1 in ( select * from sys.v_\$session_connect_in fo where sid = s.sid) loop
-- dbms_output.put_line(chr(9 )||': '||c1.network_service_bann er);
-- end loop;
dbms_output.put_line('Lock s:');
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...
##########################
# 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('Unab
return;
when others then
dbms_output.put_line(sqler
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/
dbms_output.put_line('Fore
dbms_output.put_line('Shad
dbms_output.put_line('Term
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Stat
dbms_output.put_line('Tran
dbms_output.put_line('Logi
dbms_output.put_line('Last
dbms_output.put_line('Lock
dbms_output.put_line('Latc
dbms_output.put_line('Curr
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
end loop;
dbms_output.put_line('Prev
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
end loop;
dbms_output.put_line('Sess
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9
end loop;
-- dbms_output.put_line('Conn
-- for c1 in ( select * from sys.v_\$session_connect_in
-- dbms_output.put_line(chr(9
-- end loop;
dbms_output.put_line('Lock
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
'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
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...
>>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.
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.
ASKER
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]$
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]$
>>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"
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"
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.
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.
ASKER
[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-dae mon
4329 oracle 0.1 01:52 /usr/libexec/gnome-setting s-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-activa tion-serve r --ac-activ
4357 oracle 0.0 01:50 /usr/libexec/gnome-vfs-dae mon
4361 oracle 0.0 01:50 eggcups --sm-client-id default4
4375 oracle 0.4 01:50 /usr/lib/vmware-tools/bin3 2/vmware-u ser-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-daemo n
4399 oracle 0.0 01:49 ./escd --key_Inserted="/usr/bin/e sc" --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-apple t --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 ~]$
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-dae
4329 oracle 0.1 01:52 /usr/libexec/gnome-setting
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-activa
4357 oracle 0.0 01:50 /usr/libexec/gnome-vfs-dae
4361 oracle 0.0 01:50 eggcups --sm-client-id default4
4375 oracle 0.4 01:50 /usr/lib/vmware-tools/bin3
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-daemo
4399 oracle 0.0 01:49 ./escd --key_Inserted="/usr/bin/e
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-
4465 oracle 0.1 01:26 /usr/libexec/mixer_applet2
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 ~]$
>>[oracle@mac ~]$ ps -u oracle -o "%p %u %C %t %a"
I don't see an Oracle database running
I don't see an Oracle database running
ASKER
What does this means?
[oracle@mac dbs]$ . oraenv databank
ORACLE_SID = [RAC1] ? databank
The Oracle base for ORACLE_HOME=/u01/app/oracl e/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-dae mon
4329 oracle 0.0 12:11:15 /usr/libexec/gnome-setting s-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-activa tion-serve r --ac-activate --ior-output-fd=16
4357 oracle 0.0 12:11:13 /usr/libexec/gnome-vfs-dae mon
4361 oracle 0.0 12:11:13 eggcups --sm-client-id default4
4375 oracle 0.0 12:11:13 /usr/lib/vmware-tools/bin3 2/vmware-u ser-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-daemo n
4399 oracle 0.0 12:11:12 ./escd --key_Inserted="/usr/bin/e sc" --on_Signal="/usr/bin/esc"
4404 oracle 0.0 12:11:12 /usr/libexec/wnck-applet --oaf-activate-iid=OAFIID: GNOME_Wnck let_Factor y --oaf-ior-fd=19
4407 oracle 0.0 12:11:12 /usr/libexec/trashapplet --oaf-activate-iid=OAFIID: GNOME_Pane l_TrashApp let_Factor y --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-apple t --oaf-activate-iid=OAFIID: GNOME_Noti ficationAr eaApplet_
4465 oracle 0.0 12:10:49 /usr/libexec/mixer_applet2 --oaf-activate-iid=OAFIID: GNOME_Mixe rApplet_Fa ctory --oaf-ior-fd=3
4466 oracle 0.0 12:10:49 /usr/libexec/clock-applet --oaf-activate-iid=OAFIID: GNOME_Cloc kApplet_Fa ctory --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]$
[oracle@mac dbs]$ . oraenv databank
ORACLE_SID = [RAC1] ? databank
The Oracle base for ORACLE_HOME=/u01/app/oracl
[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-dae
4329 oracle 0.0 12:11:15 /usr/libexec/gnome-setting
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-activa
4357 oracle 0.0 12:11:13 /usr/libexec/gnome-vfs-dae
4361 oracle 0.0 12:11:13 eggcups --sm-client-id default4
4375 oracle 0.0 12:11:13 /usr/lib/vmware-tools/bin3
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-daemo
4399 oracle 0.0 12:11:12 ./escd --key_Inserted="/usr/bin/e
4404 oracle 0.0 12:11:12 /usr/libexec/wnck-applet --oaf-activate-iid=OAFIID:
4407 oracle 0.0 12:11:12 /usr/libexec/trashapplet --oaf-activate-iid=OAFIID:
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-
4465 oracle 0.0 12:10:49 /usr/libexec/mixer_applet2
4466 oracle 0.0 12:10:49 /usr/libexec/clock-applet --oaf-activate-iid=OAFIID:
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]$
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.
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.
ASKER
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]$
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]$
You are missing the body of the for loop. Using corrected syntax, you would have:
That should print out something like:
Once that script is working, extend it like:
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.
#!/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
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$
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
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.
ASKER
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-dae mon
6241 oracle 0.0 1-02:04:51 /usr/libexec/gnome-setting s-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-activa tion-serve r --ac-activ
6268 oracle 0.0 1-02:04:50 /usr/libexec/gnome-vfs-dae mon
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/bin3 2/vmware-u ser-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-daemo n
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/e sc" --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-apple t --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-activa tion-serve r --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]$
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-dae
6241 oracle 0.0 1-02:04:51 /usr/libexec/gnome-setting
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-activa
6268 oracle 0.0 1-02:04:50 /usr/libexec/gnome-vfs-dae
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/bin3
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-daemo
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/e
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
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-
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-activa
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]$
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes
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