Scripts

1) Find out whether Oracle software installed and database created 32Bit or 64Bit ? Oracle-Software-and-db-created_32bit-or-64bit.pdf

2) Extract the DDL using DBMS_METADATA.GET_DDL Function To extract the DDL from dbms_metadata

CPU Usage

ps -eo pcpu,pid,user,args | sort -k 1 -r | head -30

List processes by mem (KB) usage:
ps -e -orss=,pid,user,args= | sort -b -k1,1n | pr -TW$COLUMNS

List processes by % cpu usage:
ps -e -o pcpu,cpu,nice,state,pid,user,cputime,args –sort pcpu | sed ‘/^ 0.0 /d’
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -30

List all threads for a particular process:
ps -C firefox-bin -L -o pid,tid,pcpu,state

List elapsed wait time for particular process IDs
ps -p 1,$$ -o etime=

IOStat

iostat -d -x 5 3

Housekeeping Scripts

for i in `ls -ltr |grep “Jun 22” |awk ‘{print $9}’`; do rm -f $i; done

for i in `ls -lrt |grep “trc” |grep “Jun 19″|awk ‘{print $9}’`; do rm -f $i ; done

ls -ltr cdmp_20110703221255
for i in `ls -lrt |grep “cdmp” |grep “Jun”|awk ‘{print $9}’`; do rm -rf  $i ; done

for i in `ls -lrt |grep “trc” | grep “Nov” |awk ‘{print $9}’`; do rm -rf  $i ; done

for i in `ls -lrt | grep “trm” | grep “Nov” |awk ‘{print $9}’`; do rm -rf  $i ; done

$ find -name “EXP*” -mtime +3 -print -exec rm {} \;

cd trace
ls -lrt|grep ^d|awk ‘{print $9}’ > cdmp.txt
cat cdmp.txt
ls -ld cdmp_20110706043635

for i in `cat cdmp.txt`; do rm -rf $i; done
df -h .

find . -size +102400k

Nohup

nohup sqlplus “/as sysdba” @<file_name.sql>  > nohup_1.out 2>&1 &

nohup sqlplus “/as sysdba” @table_stat.sql > /tmp/table_stat.lst 2>&1 &

MS-excel trick

(1) If you want ABC to have single quote before and after with comma, use excel sheet

First copy that in any cell ABC then in adjacent cell use formula

=”‘”&A1&”‘,” now the new value would be ‘ABC’,

(2) Extracting the Last word or letter in a cell

=MID(A1,1+FIND(“~”,SUBSTITUTE(A1,” “,”~”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))),255)

Author: Tom @ www.atlaspm.com

Search,find and replace (vi command)

:1,$s/old_string/new_string/g

:1.$s/12\/16\/2011/12\/17\/2011/g –> this is used if date has be replace which has “/”

changing: /oradata/app to /oracle/oradata/app

:1,$s/<old-string>/<new-string>/g

:1,$s/\/oradata\/app/\/oracle\/oradata\/app/g

0    – (Zero) Move cursor to the begining of the line
$    – Move cursor to the end of the line
1G  – Move cursor to the first line of the file
G    – Move cursor to the end of the file
nG  – Move to nth line of the file
:n    – Move to nth line of the file

CRONTAB – entry

0,30 21-2 * * * ssh racserver “/u01/oracle/scripts/stat.sh ORCL raheel@raheelINC.com” >> /u01/oracle/scripts/stat.log 2>&1

minute         0-59
hour           0-23
day of month   1-31
month          1-12
day of week    0-7 (0 or 7 is Sun, or use names)

stty erase ^h

Have you ever seen this “SELECT * FOR^?^?^?” when using backspace on SQLPLUS prompt ?

The solution is to use stty erase command at OS prompt as

oracle@racSolaris:> stty erase ^? (type stty erase then press ctrl+v then use backspace)

CPU Performance

PROMPT **** CPU ****
PROMPT *** Script: Unknown Source ****
PROMPT CPU PERFORMANCE
PROMPT ===============
set timing on;
DECLARE
c  NUMBER := 1;
pi NUMBER := 3.142;
r  NUMBER := DBMS_RANDOM.RANDOM;
BEGIN
FOR i IN 1 .. 10000000 LOOP
c := pi*(r**2) + (MOD(r,c)*pi+i);
END LOOP;
END;
/
set timing off;

output
======

**** CPU ****

*** Script: Unknown Source ****

CPU PERFORMANCE
===============
PL/SQL procedure successfully completed.

Elapsed: 00:00:07.00

Find No. of CPU cores used by Oracle

PROMPT NUMBER OF CORES USED BY ORACLE
PROMPT ==============================
column NAME FORMAT A30 heading “CPU Count”
COLUMN VALUE FORMAT A17 JUSTIFY RIGHT heading “Number of Cores”
column DESCRIPTION FORMAT A100 heading “Description”
PROMPT
select ksppinm name, ksppstvl value, ksppdesc description
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ksppinm like ‘%cpu_count%’
order by name;
PROMPT

output
======

NUMBER OF CORES USED BY ORACLE

CPU Count Number of Cores Description
——— ————— ————————————-
cpu_count 8               number of CPUs for this instance

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s