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