to check details of id running the processes which are identified from ps listing

to check details of id running the processes which are identified from ps listing

select b.username,b.schemaname,b.osuser,b.process,b.machine,b.terminal from v$session b,v$process a where b.paddr=a.addr and a.spid='&1'

currently executing sqls for the users

Following gives the active SQL all current users are executing

select v.sid,
v.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$s s, v$session v
where v.sql_hash_value = s.hash_value
and v.sql_address = s.address
and v.username is not null

Entry for April 11, 2007

buffer gets for cpu util
disk reads for disk i/o
sorts for sorting in sqlarea

to get buffer gets higher than some large value
SQL> select a.buffer_gets,a.disk_reads,a.sorts,a.address from v$sqlarea a where a.buffer_gets>1000000;

use similar for getting the other address of interest for disk i/o or sorts

once the address is found for ex.070000002ADDE608 for the highest sorts of 284

BUFFER_GETS DISK_READS SORTS ADDRESS
----------- ---------- ---------- ----------------
1557764 1553482 284 070000002ADDE608
1405968 27827 1 070000002879F370
1407953 27862 0 07000000287D4330
6897100 2659578 0 0700000029F8D330



use
SQL> select SQL_TEXT,PIECE from v$sqltext where address='070000002ADDE608' order by PIECE;

SQL_TEXT PIECE

---------------------------------------------------------------- ----------
SELECT DQD_FIN_ID , DQD_DIS_NO , SUM(DQD_DEMAT_QTY) FROM DMAT_QT 0
Y_DIS_ELEC WHERE DQD_NPN = :B1 GROUP BY DQD_FIN_ID , DQD_DIS_NO 1
2

compressed export

ORACLE_SID=TEST1;export ORACLE_SID
export ORACLE_HOME=/oracle9i/product/9.2.0
set `date`
filename=/oracle9i/expTEST1full.$3$2$6
export filename
PIPEDIR=/oracle9i/pipedir;export PIPEDIR

/etc/mknod $PIPEDIR/pipeabc p

echo "nohup compress $filename.comp &" > write_pipeabc

sh write_pipeabc

sleep 5

date >> /oracle9i/pipedir/TEST1full.$3$2$6.out

echo ' Full Export of TEST1 is going on........... Please wait'

$ORACLE_HOME/bin/exp \'/ as sysdba \' buffer=1073741824 feedback=100000 file=$PIPEDIR/pipeabc \
statistics=none grants=Y direct=y \
indexes=Y rows=Y full=Y compress=Y 2>> /oracle9i/TEST1full.$3$2$6.out

getting the sql text of particular process

getting the sql text of particular process

1)pid obtained from OS commands
ps auxgw|grep oracle|head -5
1667200

2)v$session SQL_ADDRESS

use spid from v$process to join with paddr of v$session and addr in v$process to get the sql_address from v$session

select a.serial#,a.spid,b.sid,b.sql_address
from v$process a,v$session b
where a.addr=b.paddr and a.spid='1667200';

SERIAL# SPID SID SQL_ADDRESS
---------- ------------ ---------- ----------------
7 1667200 40 0700000020F9C268

3)Use the SQL_ADDRESS from previous entry to get the sqltext
select * from v$sqltext where address='0700000020F9C268';
ADDRESS HASH_VALUE COMMAND_TYPE PIECE SQL_TEXT
---------------- ---------- ------------ ---------- ----------------------------------------------------------------
0700000020F9C268 1739069764 47 0 begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end;

reading the oracle alert log daily

following script reads the entries for entire day to end of alert log

set `date`;
gret=`grep -n $1\ $2\ $3 /ora/admin/ORACLE_SID/bdump/alert_ORACLE_SID.log|awk -F: '{print $1}'|head -1`
dy=`cat /ora/admin/ORACLE_SID/bdump/alert_ORACLE_SID.log|wc -l`
di=`echo $dy-$gret|bc`
tail -$di /ora/admin/ORACLE_SID/bdump/alert_ORACLE_SID.log>filtemp
mailx -s "daily alert log" abs@dhdjh.com<filtemp