I have a script

#!/bin/sh
DATE1=`date +"%y""%m""%d""%H""%M"`
tmpfile=/tmp/KILL$DATE1.log
sqlplus system/manager<<EOF
set linesize 500
set pagesize 1000
set heading off
set feedback off
spool $tmpfile
select
'ALTER system kill session '''|| s.sid || ',' || s.serial# ||''';' Kill,'kill -9 '||p.spid "KILL OS PID"
from v\$session s,v\$process p where s.paddr=p.addr and s.status='INACTIVE'
spool off
EOF

which produces two columns of values like this :

ALTER system kill session '1663,6809'; kill -9 12018
ALTER system kill session '1731,7947'; kill -9 14073
ALTER system kill session '1921,52004'; kill -9 6066

Now, I want to take the first statement ALTER system kill session '1663,6809'; in sqlplus and run all the commands and after running th e same in oracle, I want to kill all the OS associated OS process using the 2nd column kill 09 12018 . how can I acheive this ?.. is there anyway I can modify the above script to take the output and kill the sessions at the database level and OS level?


please guide seniors ?


Kai

Dear Friends,

Any updates


Kai

Hi,

you can run both commands from within sqlplus.
You have to set a ! in front of the OS command.
This looks like:
ALTER system kill session '1663,6809'; !kill -9 12018

Both can be executed by changing your script this way, that it will execute $tempfile at the end.

This should look like this:
-------------begin---------------------------------------
#!/bin/sh
DATE1=`date +"%y""%m""%d""%H""%M"`
tmpfile=/tmp/KILL$DATE1.log
sqlplus system/manager<<EOF
set linesize 500
set pagesize 1000
set heading off
set feedback off
spool $tmpfile
select
'ALTER system kill session '''|| s.sid || ',' || s.serial# ||''';' Kill,'!kill -9 '||p.spid "KILL OS PID"
from v\$session s,v\$process p where s.paddr=p.addr and s.status='INACTIVE'
spool off
@$tmpfile
EOF
--------------------end--------------

1 members found this post helpful.

Hai pal,

am getting error



SQL> ALTER system kill session '1764,60569'; ! kill -9 12151
2 /
ALTER system kill session '1764,60569'; ! kill -9 12151
*
ERROR at line 1:
ORA-00911: invalid character



kai

Please, put your code in "Code:" blocks, i.e. put it inside [CODE][/CODE] tags.