Weekly script #2 pid_from_sid.sql

A fairly easy script today that isn’t really hard to work out for yourself but I find it useful to have this script kicking around for when you find high resource using Oracle server process and you want to identify the offending SQL.

set pages 1000;
set lines 200;
col username for a10;

select b.status,a.inst_id,a.PID "Ora PID",a.SPID "Server PID",
a.LATCHWAIT,b.Program, b.Username,b.Osuser,b.machine,
b.terminal,a.terminal,b.Process "Client Process"
from gv$process a,gv$session b where a.addr=b.paddr and b.sid=&SID

See you next week!

Le Paz


Featured Image -- 530

Stephen & Grace


Stephen and Grace’s wedding which I was at on Friday.

Originally posted on RADIOMOTHS:

Stephen and Grace got married last Friday.

Here’s a selection of photos I managed to take. Alcohol + Manual focus don’t particularly go hand in hand…

View original

Weekly Script #1 – ssh_key_scp.sh

Since I dont post on here to often I will try and post a weekly script that I find useful day-to-day.

First up is a short simple script to scp your private key to a defined server. Usage is pretty simple call the script with the username@hostname

e.g ./ssh_key_scp.sh username@hostname

If no ssh key already exists the script gives you the option to generate one.

# Andrew Paz
# 06/01/15


if [ ! -f ~/.ssh/id_rsa.pub ];then
echo "Private key not found at $KEY"
while true; do
read -p "Do you wish to generate a private key?" yn
case $yn in
[Yy]* ) ssh-keygen -t rsa; break;;
[Nn]* ) exit;;
* ) echo "Please answer yes or no!";;


if [ -z $1 ];then
echo "Please specify user@host as the first parameter of this script"

echo "Putting your key on $1... "

ssh -q $1 "mkdir ~/.ssh 2>/dev/null; chmod 700 ~/.ssh; echo "$KEYCODE" >> ~/.ssh/authorized_keys; chmod 644 ~/.ssh/authorized_keys"

echo "done :D Key Uploaded!"


Thats it for this week, will post another next week.


A better Oracle DAC startserver.sh

Just want to share my Oracle DAC 10g startserver.sh.

startserver.sh is already provides as part of the DAC 10g install. I have added a couple of extra java parameters to allow a larger heap size and garbage collection method

. ./config.sh

$JAVA -server -Xms2048m -Xmx2048m -XX:+UseParNewGC -XX:ErrorFile='./hs_err_pid.log' -cp $DACCLASSPATH com.siebel.etl.net.QServer

-Xms and -Xmx give the dac server application 2GB of heap size to play with.

-XX:+UseParNewGC forces JVM to use parallel young generation GC with the concurrent GC.

-XX:ErrorFile should your dac server crash with a JVM error the stacktrace/dump will be stored in the specified file.

Until I feel like posting again ;)

Fixing ORA-00838: Specified value of MEMORY_TARGET is too small

I can never remember how to do this so its getting dumped here.

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1172M
SQL> create pfile='/tmp/pfile.bkp' from spfile;
File created.

$ vi /tmp/pfile.backup


Start sqlplus, and import the pfile settings into the spfile

SQL> create spfile from pfile='/tmp/pfile.bkp';
File created.

Ye’ should be golden!