Archive May 2011

startSqlTrace.sh : start sql traces

#!/bin/bash
echo password:
read SYSPASS
sqlplus system/$SYSPASS@WITRDB << FIN
alter system set sql_trace=true;
exit
FIN

stopSqlTrace.sh : stop sql traces

#!/bin/bash
echo password:
read SYSPASS
sqlplus system/$SYSPASS@WITRDB << FIN
alter system set sql_trace=false;
exit
FIN

If I have system access to oracle DB, and want to find out the service name

> sqlplus system
password: XXXX
SQL> select sys_context(‘userenv’,’instance_name’) from dual;

output

SYS_CONTEXT(‘USERENV’,’INSTANCE_NAME’)
————————————————–
MY_INSTANCE_NAME

Now, to find out SID

SQL> select sys_context(‘userenv’,’sid’) from dual;

================================================= 19/06/2011
use tkprof
1. Login with oracle user
2. Sqlplus system/xxxx@SHEMA
3. alter system set sql_trace=true;
4. show parameter user_dump_dest;
4. Cd
5. tkprof xxxxx.trc /tmp/myAnalyse.out explain=user/pass@SHEMA sort=execpu
6. ne pas oublier : alter system set sql_trace=false;